-- =====================================================
-- SISTEMA DE INTEROPERABILIDADE MULTI-MERCHANT
-- Permite que pontos de uma campanha sejam resgatados em merchants diferentes
-- =====================================================

-- =====================================================
-- 1. TABELA: campaign_merchant_authorizations
-- Autorizações entre merchants para partilha de campanhas
-- =====================================================

CREATE TABLE IF NOT EXISTS campaign_merchant_authorizations (
    authorization_id SERIAL PRIMARY KEY,
    campaign_id INTEGER NOT NULL,
    issuer_merchant_id INTEGER NOT NULL, -- Merchant que criou a campanha (emissor)
    redeemer_merchant_id INTEGER NOT NULL, -- Merchant autorizado a aceitar resgates
    is_active BOOLEAN DEFAULT TRUE,
    authorized_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    revoked_at TIMESTAMP,
    revoked_by INTEGER REFERENCES users(user_id),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Constraints
    UNIQUE(campaign_id, redeemer_merchant_id),
    CONSTRAINT fk_campaign FOREIGN KEY (campaign_id) 
        REFERENCES campaigns(campaign_id) ON DELETE CASCADE,
    CONSTRAINT fk_issuer_merchant FOREIGN KEY (issuer_merchant_id) 
        REFERENCES merchants(merchant_id) ON DELETE CASCADE,
    CONSTRAINT fk_redeemer_merchant FOREIGN KEY (redeemer_merchant_id) 
        REFERENCES merchants(merchant_id) ON DELETE CASCADE,
    CONSTRAINT chk_different_merchants CHECK (issuer_merchant_id != redeemer_merchant_id)
);

-- Índices para performance
CREATE INDEX IF NOT EXISTS idx_campaign_auth_campaign_id 
    ON campaign_merchant_authorizations(campaign_id);
CREATE INDEX IF NOT EXISTS idx_campaign_auth_issuer_merchant_id 
    ON campaign_merchant_authorizations(issuer_merchant_id);
CREATE INDEX IF NOT EXISTS idx_campaign_auth_redeemer_merchant_id 
    ON campaign_merchant_authorizations(redeemer_merchant_id);
CREATE INDEX IF NOT EXISTS idx_campaign_auth_is_active 
    ON campaign_merchant_authorizations(is_active);

-- =====================================================
-- 2. TABELA: merchant_settlements
-- Obrigações financeiras entre merchants (liquidação)
-- =====================================================

CREATE TABLE IF NOT EXISTS merchant_settlements (
    settlement_id SERIAL PRIMARY KEY,
    settlement_number VARCHAR(50) UNIQUE, -- Número automático (ex: SET-20241104-0001)
    
    -- Redemption relacionado
    redemption_id VARCHAR(64), -- ID do resgate que gerou esta obrigação
    purchase_id VARCHAR(64), -- ID da compra relacionada
    
    -- Merchants envolvidos
    issuer_merchant_id INTEGER NOT NULL, -- Merchant que emitiu os pontos (deve pagar)
    redeemer_merchant_id INTEGER NOT NULL, -- Merchant que aceitou o resgate (deve receber)
    
    -- Campanha relacionada
    campaign_id INTEGER REFERENCES campaigns(campaign_id) ON DELETE SET NULL,
    
    -- Valores financeiros
    points_redeemed INTEGER NOT NULL, -- Pontos resgatados
    amount_mt NUMERIC(14,2) NOT NULL, -- Valor em MT (points_redeemed / 10)
    
    -- Status da liquidação
    status VARCHAR(20) DEFAULT 'pending', -- pending, processed, cancelled, failed
    settlement_date DATE, -- Data prevista para liquidação (T+1 ou configurável)
    processed_at TIMESTAMP, -- Data/hora em que foi processada
    processed_by INTEGER REFERENCES users(user_id), -- Quem processou (Maktubia)
    
    -- Informações de liquidação
    payment_method VARCHAR(50), -- Método de pagamento usado
    transaction_reference VARCHAR(100), -- Referência da transação bancária
    notes TEXT,
    
    -- Auditoria
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Constraints
    CONSTRAINT fk_issuer_merchant FOREIGN KEY (issuer_merchant_id) 
        REFERENCES merchants(merchant_id) ON DELETE CASCADE,
    CONSTRAINT fk_redeemer_merchant FOREIGN KEY (redeemer_merchant_id) 
        REFERENCES merchants(merchant_id) ON DELETE CASCADE,
    CONSTRAINT chk_settlement_different_merchants CHECK (issuer_merchant_id != redeemer_merchant_id),
    CONSTRAINT chk_settlement_status CHECK (status IN ('pending', 'processed', 'cancelled', 'failed')),
    CONSTRAINT chk_settlement_amount_positive CHECK (amount_mt > 0)
);

-- Índices para performance
CREATE INDEX IF NOT EXISTS idx_settlement_issuer_merchant_id 
    ON merchant_settlements(issuer_merchant_id);
CREATE INDEX IF NOT EXISTS idx_settlement_redeemer_merchant_id 
    ON merchant_settlements(redeemer_merchant_id);
CREATE INDEX IF NOT EXISTS idx_settlement_campaign_id 
    ON merchant_settlements(campaign_id);
CREATE INDEX IF NOT EXISTS idx_settlement_status 
    ON merchant_settlements(status);
CREATE INDEX IF NOT EXISTS idx_settlement_settlement_date 
    ON merchant_settlements(settlement_date);
CREATE INDEX IF NOT EXISTS idx_settlement_redemption_id 
    ON merchant_settlements(redemption_id);
CREATE INDEX IF NOT EXISTS idx_settlement_purchase_id 
    ON merchant_settlements(purchase_id);

-- =====================================================
-- 3. FUNÇÃO: Gerar número automático de settlement
-- =====================================================

CREATE OR REPLACE FUNCTION generate_settlement_number()
RETURNS VARCHAR(50) AS $$
DECLARE
    new_number VARCHAR(50);
    date_str VARCHAR(8);
    counter INTEGER;
BEGIN
    date_str := TO_CHAR(CURRENT_DATE, 'YYYYMMDD');
    counter := 1;
    
    LOOP
        new_number := 'SET-' || date_str || '-' || LPAD(counter::TEXT, 4, '0');
        
        IF NOT EXISTS (SELECT 1 FROM merchant_settlements WHERE settlement_number = new_number) THEN
            RETURN new_number;
        END IF;
        
        counter := counter + 1;
        
        IF counter > 9999 THEN
            RAISE EXCEPTION 'Não foi possível gerar número único de settlement';
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- =====================================================
-- 4. TRIGGER: Atualizar updated_at automaticamente
-- =====================================================

CREATE OR REPLACE FUNCTION update_settlement_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_update_settlement_updated_at
    BEFORE UPDATE ON merchant_settlements
    FOR EACH ROW
    EXECUTE FUNCTION update_settlement_updated_at();

-- =====================================================
-- 5. VIEW: Resumo de obrigações pendentes por merchant
-- =====================================================

CREATE OR REPLACE VIEW merchant_settlements_summary AS
SELECT 
    issuer_merchant_id,
    redeemer_merchant_id,
    COUNT(*) as pending_count,
    SUM(amount_mt) as total_amount_mt_pending,
    MIN(settlement_date) as earliest_settlement_date,
    MAX(settlement_date) as latest_settlement_date
FROM merchant_settlements
WHERE status = 'pending'
GROUP BY issuer_merchant_id, redeemer_merchant_id;

-- =====================================================
-- 6. VIEW: Histórico de liquidações processadas
-- =====================================================

CREATE OR REPLACE VIEW merchant_settlements_history AS
SELECT 
    s.*,
    m1.name as issuer_merchant_name,
    m2.name as redeemer_merchant_name,
    c.campaign_name,
    c.campaign_number
FROM merchant_settlements s
LEFT JOIN merchants m1 ON s.issuer_merchant_id = m1.merchant_id
LEFT JOIN merchants m2 ON s.redeemer_merchant_id = m2.merchant_id
LEFT JOIN campaigns c ON s.campaign_id = c.campaign_id
WHERE s.status = 'processed'
ORDER BY s.processed_at DESC;