-- Script para adicionar coluna merchant_id às tabelas campaigns e purchases na VPS
-- Execute: sudo -u postgres psql -d maktubia_points -f deploy/fix-campaigns-purchases-merchant-id.sql

-- Adicionar merchant_id à tabela campaigns se não existir
DO $$ 
BEGIN
    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;
END $$;

-- Adicionar merchant_id à tabela purchases se não existir
DO $$ 
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='purchases' AND column_name='merchant_id'
    ) THEN
        ALTER TABLE purchases ADD COLUMN merchant_id INTEGER REFERENCES merchants(merchant_id) ON DELETE SET NULL;
        RAISE NOTICE '✅ Coluna merchant_id adicionada à tabela purchases';
    ELSE
        RAISE NOTICE 'ℹ️  Coluna merchant_id já existe na tabela purchases';
    END IF;
END $$;

-- Criar índices se não existirem
CREATE INDEX IF NOT EXISTS idx_campaigns_merchant_id ON campaigns(merchant_id);
CREATE INDEX IF NOT EXISTS idx_purchases_merchant_id ON purchases(merchant_id);

-- Verificar estrutura
SELECT 
    'campaigns' as tabela,
    column_name, 
    data_type, 
    is_nullable
FROM information_schema.columns 
WHERE table_name = 'campaigns' AND column_name = 'merchant_id'
UNION ALL
SELECT 
    'purchases' as tabela,
    column_name, 
    data_type, 
    is_nullable
FROM information_schema.columns 
WHERE table_name = 'purchases' AND column_name = 'merchant_id';