-- =====================================================
-- TABELA: campaign_participations
-- Rastreia participação de usuários em campanhas
-- =====================================================

CREATE TABLE IF NOT EXISTS campaign_participations (
    participation_id SERIAL PRIMARY KEY,
    campaign_id VARCHAR(64) NOT NULL REFERENCES campaigns(campaign_id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(user_id) ON DELETE CASCADE,
    participated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'active', -- active, started, redeemed, completed, cancelled
    points_spent INTEGER DEFAULT 0, -- Pontos gastos (para Exchange)
    points_earned INTEGER DEFAULT 0, -- Pontos ganhos (para Auto)
    referral_code VARCHAR(100), -- Código de indicação (para Referral)
    voucher_code VARCHAR(100), -- Código de voucher (para Voucher)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(campaign_id, user_id)
);

-- Índices para performance
CREATE INDEX IF NOT EXISTS idx_campaign_participations_campaign_id ON campaign_participations(campaign_id);
CREATE INDEX IF NOT EXISTS idx_campaign_participations_user_id ON campaign_participations(user_id);
CREATE INDEX IF NOT EXISTS idx_campaign_participations_status ON campaign_participations(status);
CREATE INDEX IF NOT EXISTS idx_campaign_participations_referral_code ON campaign_participations(referral_code) WHERE referral_code IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_campaign_participations_voucher_code ON campaign_participations(voucher_code) WHERE voucher_code IS NOT NULL;

-- Comentários
COMMENT ON TABLE campaign_participations IS 'Rastreia participação de usuários em campanhas';
COMMENT ON COLUMN campaign_participations.status IS 'Status da participação: active, started, redeemed, completed, cancelled';
COMMENT ON COLUMN campaign_participations.points_spent IS 'Pontos gastos pelo usuário (para campanhas Exchange)';
COMMENT ON COLUMN campaign_participations.points_earned IS 'Pontos ganhos pelo usuário (para campanhas Auto)';
COMMENT ON COLUMN campaign_participations.referral_code IS 'Código de indicação único (para campanhas Referral)';
COMMENT ON COLUMN campaign_participations.voucher_code IS 'Código de voucher único (para campanhas Voucher)';