-- Script para criar/atualizar estrutura completa de campanhas
-- Execute: sudo -u postgres psql -d maktubia_points -f create-campaigns-full-schema.sql

-- Adicionar colunas faltantes na tabela campaigns
DO $$ 
BEGIN
    -- Número da campanha (atribuição automática)
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='campaign_number'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN campaign_number VARCHAR(50) UNIQUE;
        CREATE INDEX IF NOT EXISTS idx_campaigns_campaign_number ON campaigns(campaign_number);
    END IF;
    
    -- Nome da campanha
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='campaign_name'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN campaign_name VARCHAR(80);
    END IF;
    
    -- Nome do sponsor
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='sponsor_name'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN sponsor_name VARCHAR(80);
    END IF;
    
    -- Plafond global (total de pontos por acumular)
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='total_points_limit'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN total_points_limit INTEGER;
    END IF;
    
    -- Pontos acumulados até agora
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='points_accumulated'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN points_accumulated INTEGER DEFAULT 0;
    END IF;
    
    -- Validade início
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='valid_from'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN valid_from TIMESTAMP;
    END IF;
    
    -- Validade fim (já existe como valid_until, mas vamos garantir)
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='valid_until'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN valid_until TIMESTAMP;
    END IF;
    
    -- Prazo de resgate (deadline)
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='redemption_deadline'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN redemption_deadline DATE;
    END IF;
    
    -- Prémio - Bem (descrição)
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='reward_description'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN reward_description VARCHAR(120);
    END IF;
    
    -- Prémio - Valor em MT
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='reward_value_mt'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN reward_value_mt NUMERIC(14,2) DEFAULT 0;
    END IF;
    
    -- Prémio - Pontos necessários para resgate (custo)
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='reward_points_cost'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN reward_points_cost INTEGER;
    END IF;
    
    -- Stock do prémio
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='reward_stock'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN reward_stock INTEGER DEFAULT 0;
    END IF;
    
    -- Stock resgatado
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='reward_stock_redeemed'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN reward_stock_redeemed INTEGER DEFAULT 0;
    END IF;
    
    -- Status da campanha (Rascunho, Activo, Parado, Cancelado, Concluído, Expirado)
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='status'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN status VARCHAR(20) DEFAULT 'Rascunho';
    END IF;
    
    -- Taxa de acumulação (ex: 1 MT = 10 pts)
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='accumulation_rate'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN accumulation_rate NUMERIC(10,4);
    END IF;
    
    -- Limite diário por cliente
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='daily_limit_per_client'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN daily_limit_per_client INTEGER;
    END IF;
    
    -- Limite por transação
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='transaction_limit'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN transaction_limit INTEGER;
    END IF;
    
    -- Limite por campanha (total por cliente)
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='campaign_limit_per_client'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN campaign_limit_per_client INTEGER;
    END IF;
    
    -- Elegibilidade - Apenas clientes novos
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='new_customers_only'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN new_customers_only BOOLEAN DEFAULT FALSE;
    END IF;
    
    -- Elegibilidade - Segmento VIP
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='vip_only'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN vip_only BOOLEAN DEFAULT FALSE;
    END IF;
    
    -- Elegibilidade - Localização (JSON com IDs de localizações permitidas)
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='allowed_locations'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN allowed_locations JSONB;
    END IF;
    
    -- Elegibilidade - Método de pagamento (JSON com métodos permitidos)
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='allowed_payment_methods'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN allowed_payment_methods JSONB;
    END IF;
    
    -- Comunicação - Notificações push
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='notify_push'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN notify_push BOOLEAN DEFAULT FALSE;
    END IF;
    
    -- Comunicação - SMS
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='notify_sms'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN notify_sms BOOLEAN DEFAULT FALSE;
    END IF;
    
    -- Comunicação - E-mail
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='notify_email'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN notify_email BOOLEAN DEFAULT FALSE;
    END IF;
    
    -- Comunicação - WhatsApp
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='notify_whatsapp'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN notify_whatsapp BOOLEAN DEFAULT FALSE;
    END IF;
    
    -- Orçamento de comunicação (créditos)
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='communication_budget'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN communication_budget NUMERIC(10,2) DEFAULT 0;
    END IF;
    
    -- Créditos de comunicação utilizados
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='communication_credits_used'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN communication_credits_used NUMERIC(10,2) DEFAULT 0;
    END IF;
    
    -- Pontos a expirar (por campanha)
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='points_expiry_days'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN points_expiry_days INTEGER;
    END IF;
    
    -- Observações/Notas
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns 
        WHERE table_name='campaigns' AND column_name='notes'
    ) THEN
        ALTER TABLE campaigns ADD COLUMN notes TEXT;
    END IF;
END $$;

-- Criar tabela de histórico de pontos acumulados por cliente na campanha
CREATE TABLE IF NOT EXISTS campaign_points_accumulation (
    id SERIAL PRIMARY KEY,
    campaign_id INTEGER REFERENCES campaigns(campaign_id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(user_id) ON DELETE CASCADE,
    establishment_id INTEGER REFERENCES establishments(establishment_id) ON DELETE CASCADE,
    points_accumulated INTEGER DEFAULT 0,
    points_daily INTEGER DEFAULT 0,
    last_daily_reset TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(campaign_id, user_id)
);

-- Criar índices para performance
CREATE INDEX IF NOT EXISTS idx_campaign_points_campaign_id ON campaign_points_accumulation(campaign_id);
CREATE INDEX IF NOT EXISTS idx_campaign_points_user_id ON campaign_points_accumulation(user_id);
CREATE INDEX IF NOT EXISTS idx_campaign_points_establishment_id ON campaign_points_accumulation(establishment_id);

-- Criar tabela de resgates de prémios
CREATE TABLE IF NOT EXISTS campaign_rewards_redemptions (
    redemption_id SERIAL PRIMARY KEY,
    campaign_id INTEGER REFERENCES campaigns(campaign_id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(user_id) ON DELETE CASCADE,
    points_used INTEGER NOT NULL,
    reward_description VARCHAR(120),
    reward_value_mt NUMERIC(14,2),
    status VARCHAR(20) DEFAULT 'pending', -- pending, completed, cancelled
    redeemed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP,
    notes TEXT
);

CREATE INDEX IF NOT EXISTS idx_rewards_redemption_campaign_id ON campaign_rewards_redemptions(campaign_id);
CREATE INDEX IF NOT EXISTS idx_rewards_redemption_user_id ON campaign_rewards_redemptions(user_id);
CREATE INDEX IF NOT EXISTS idx_rewards_redemption_status ON campaign_rewards_redemptions(status);

-- Criar função para gerar número automático de campanha
CREATE OR REPLACE FUNCTION generate_campaign_number()
RETURNS VARCHAR(50) AS $$
DECLARE
    new_number VARCHAR(50);
    counter INTEGER := 1;
BEGIN
    LOOP
        new_number := 'CAMP-' || TO_CHAR(CURRENT_DATE, 'YYYYMMDD') || '-' || LPAD(counter::TEXT, 4, '0');
        
        -- Verificar se já existe
        IF NOT EXISTS (SELECT 1 FROM campaigns WHERE campaign_number = new_number) THEN
            RETURN new_number;
        END IF;
        
        counter := counter + 1;
        
        -- Prevenir loop infinito
        IF counter > 9999 THEN
            RAISE EXCEPTION 'Não foi possível gerar número único de campanha';
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Criar função para atualizar status da campanha baseado em condições
CREATE OR REPLACE FUNCTION update_campaign_status()
RETURNS TRIGGER AS $$
BEGIN
    -- Se plafond atingido, marcar como Concluída
    IF NEW.total_points_limit IS NOT NULL AND NEW.points_accumulated >= NEW.total_points_limit THEN
        NEW.status := 'Concluída';
    END IF;
    
    -- Se stock esgotado, parar campanha
    IF NEW.reward_stock > 0 AND NEW.reward_stock_redeemed >= NEW.reward_stock THEN
        IF NEW.status = 'Activo' THEN
            NEW.status := 'Parado';
        END IF;
    END IF;
    
    -- Se validade expirada, marcar como Expirado
    IF NEW.valid_until IS NOT NULL AND NEW.valid_until < NOW() THEN
        IF NEW.status IN ('Activo', 'Rascunho') THEN
            NEW.status := 'Expirado';
        END IF;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Criar trigger para atualizar status automaticamente
DROP TRIGGER IF EXISTS trigger_update_campaign_status ON campaigns;
CREATE TRIGGER trigger_update_campaign_status
    BEFORE UPDATE ON campaigns
    FOR EACH ROW
    EXECUTE FUNCTION update_campaign_status();

-- Verificar estrutura criada
SELECT 
    column_name,
    data_type,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_name = 'campaigns'
ORDER BY ordinal_position;