-- Script para verificar TODOS os establishments e encontrar os que têm dados
-- Execute: sudo -u postgres psql -d maktubia_points -f deploy/check-all-establishments.sql

\echo '📋 Verificando TODOS os establishments (ordenados por created_at DESC):'
\echo ''

SELECT 
    establishment_id,
    est_id,
    name,
    type,
    CASE 
        WHEN image_url IS NULL OR image_url = '' THEN 'NULL'
        ELSE 'Tem valor'
    END as image_url_status,
    CASE 
        WHEN image_secondary IS NULL OR image_secondary = '' THEN 'NULL'
        ELSE 'Tem valor'
    END as image_secondary_status,
    CASE 
        WHEN address IS NULL OR address = '' THEN 'NULL'
        ELSE 'Tem valor'
    END as address_status,
    CASE 
        WHEN phone IS NULL OR phone = '' THEN 'NULL'
        ELSE 'Tem valor'
    END as phone_status,
    CASE 
        WHEN email IS NULL OR email = '' THEN 'NULL'
        ELSE 'Tem valor'
    END as email_status,
    CASE 
        WHEN color IS NULL OR color = '' THEN 'NULL'
        ELSE 'Tem valor'
    END as color_status,
    created_at
FROM establishments 
ORDER BY created_at DESC
LIMIT 20;

\echo ''
\echo '📊 Estabelecimentos com dados (primeiros 10 que têm pelo menos um campo preenchido):'
\echo ''

SELECT 
    establishment_id,
    est_id,
    name,
    type,
    image_url,
    image_secondary,
    address,
    phone,
    email,
    color,
    qr_code,
    created_at
FROM establishments 
WHERE 
    (image_url IS NOT NULL AND image_url != '') OR
    (image_secondary IS NOT NULL AND image_secondary != '') OR
    (address IS NOT NULL AND address != '') OR
    (phone IS NOT NULL AND phone != '') OR
    (email IS NOT NULL AND email != '') OR
    (color IS NOT NULL AND color != '')
ORDER BY created_at DESC
LIMIT 10;

\echo ''
\echo '📈 Total de estabelecimentos:'
SELECT COUNT(*) as total_establishments FROM establishments;

\echo ''
\echo '📈 Estabelecimentos com dados:'
SELECT COUNT(*) as establishments_with_data 
FROM establishments 
WHERE 
    (image_url IS NOT NULL AND image_url != '') OR
    (image_secondary IS NOT NULL AND image_secondary != '') OR
    (address IS NOT NULL AND address != '') OR
    (phone IS NOT NULL AND phone != '') OR
    (email IS NOT NULL AND email != '') OR
    (color IS NOT NULL AND color != '');

\echo ''
\echo '✅ Verificação concluída!'