-- Script SQL para garantir que TODOS os establishments tenham merchants relacionados
-- Este script deve ser executado diretamente no PostgreSQL

-- 1. Verificar quantos establishments não têm merchants
SELECT 
    COUNT(*) as total_without_merchants
FROM establishments e
LEFT JOIN merchants m ON e.est_id = m.establishment_id
WHERE e.est_id IS NOT NULL 
AND m.merchant_id IS NULL;

-- 2. Criar merchants para todos os establishments que não têm
-- Primeiro, buscar um user_id padrão
DO $$
DECLARE
    default_user_id INTEGER;
    est_record RECORD;
    created_count INTEGER := 0;
BEGIN
    -- Buscar primeiro usuário disponível
    SELECT user_id INTO default_user_id 
    FROM users 
    ORDER BY user_id 
    LIMIT 1;
    
    IF default_user_id IS NULL THEN
        RAISE NOTICE 'Nenhum usuário encontrado!';
        RETURN;
    END IF;
    
    -- Criar merchants para todos os establishments sem merchants
    FOR est_record IN 
        SELECT 
            e.est_id,
            e.name,
            e.created_by
        FROM establishments e
        LEFT JOIN merchants m ON e.est_id = m.establishment_id
        WHERE e.est_id IS NOT NULL 
        AND m.merchant_id IS NULL
        ORDER BY e.est_id
    LOOP
        BEGIN
            INSERT INTO merchants (establishment_id, user_id)
            VALUES (
                est_record.establishment_id,
                COALESCE(est_record.created_by, default_user_id)
            );
            created_count := created_count + 1;
            RAISE NOTICE 'Criado merchant para establishment_id % (%)', est_record.establishment_id, est_record.name;
        EXCEPTION
            WHEN OTHERS THEN
                RAISE NOTICE 'Erro ao criar merchant para establishment_id %: %', est_record.establishment_id, SQLERRM;
        END;
    END LOOP;
    
    RAISE NOTICE 'Total de merchants criados: %', created_count;
END $$;

-- 3. Verificação final
SELECT 
    COUNT(*) as total_establishments,
    COUNT(m.merchant_id) as total_with_merchants,
    COUNT(*) - COUNT(m.merchant_id) as total_without_merchants
FROM establishments e
LEFT JOIN merchants m ON e.est_id = m.establishment_id
WHERE e.est_id IS NOT NULL;