-- Script para corrigir foreign key da tabela campaigns na VPS
-- Remove constraint antiga de est_id e garante que merchant_id existe
-- Execute: sudo -u postgres psql -d maktubia_points -f deploy/fix-campaigns-foreign-key.sql

-- Verificar e remover constraint antiga de est_id se existir
DO $$ 
DECLARE
    constraint_name_var TEXT;
BEGIN
    -- Buscar constraint de foreign key para est_id
    SELECT tc.constraint_name INTO constraint_name_var
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage kcu 
        ON tc.constraint_name = kcu.constraint_name
    WHERE tc.table_name = 'campaigns'
        AND tc.constraint_type = 'FOREIGN KEY'
        AND kcu.column_name = 'est_id'
    LIMIT 1;

    -- Se encontrou constraint, remover
    IF constraint_name_var IS NOT NULL THEN
        EXECUTE 'ALTER TABLE campaigns DROP CONSTRAINT IF EXISTS ' || constraint_name_var;
        RAISE NOTICE '✅ Constraint antiga de est_id removida: %', constraint_name_var;
    ELSE
        RAISE NOTICE 'ℹ️  Nenhuma constraint de est_id encontrada';
    END IF;
END $$;

-- Garantir que merchant_id existe e tem constraint correta
DO $$ 
BEGIN
    -- Adicionar merchant_id se não existir
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='merchant_id'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN merchant_id INTEGER REFERENCES merchants(merchant_id) ON DELETE CASCADE;
        RAISE NOTICE '✅ Coluna merchant_id adicionada à tabela campaigns';
    ELSE
        RAISE NOTICE 'ℹ️  Coluna merchant_id já existe na tabela campaigns';
    END IF;
    
    -- Verificar se constraint de merchant_id existe
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.table_constraints tc
        JOIN information_schema.key_column_usage kcu 
            ON tc.constraint_name = kcu.constraint_name
        WHERE tc.table_name = 'campaigns'
            AND tc.constraint_type = 'FOREIGN KEY'
            AND kcu.column_name = 'merchant_id'
    ) THEN
        ALTER TABLE campaigns 
        ADD CONSTRAINT campaigns_merchant_id_fkey 
        FOREIGN KEY (merchant_id) REFERENCES merchants(merchant_id) ON DELETE CASCADE;
        RAISE NOTICE '✅ Constraint de merchant_id adicionada';
    ELSE
        RAISE NOTICE 'ℹ️  Constraint de merchant_id já existe';
    END IF;
END $$;

-- Criar índice se não existir
CREATE INDEX IF NOT EXISTS idx_campaigns_merchant_id ON campaigns(merchant_id);

-- Verificar estrutura
SELECT 
    column_name, 
    data_type, 
    is_nullable,
    column_default
FROM information_schema.columns 
WHERE table_name = 'campaigns' 
AND column_name IN ('merchant_id', 'est_id', 'establishment_id')
ORDER BY column_name;