-- ============================================================================
-- Migration 014 — Propagacao do snapshot do agente para tbl_Faturamento via TRIGGER
--                + Flag agente_snap_override em tbl_Pedido
-- ============================================================================
-- PRD:  docs/PRDs/PRD-0006-propagacao-snapshot-agente/PRD-0006-propagacao-snapshot-agente.md
-- ADR:  docs/ADRs/ADR-0005-trigger-propagacao-snapshot-faturamento.md
-- Card: INT#5 (roadmap) — Passo 2
-- Data: 08/05/2026
-- Decidido por: Giovanny Porto (Stakeholder Leader) em 08/05/2026
--
-- ============================================================================
-- ESCOPO
-- ============================================================================
-- 1. ALTER TABLE tbl_Faturamento ADD 3 colunas snapshot do agente.
-- 2. ALTER TABLE tbl_Pedido      ADD coluna agente_snap_override (flag override manual).
-- 3. CREATE TRIGGER trg_agente_snapshot_faturamento_bi (BEFORE INSERT em tbl_Faturamento).
-- 4. CREATE INDEX em tbl_Faturamento.agente_cod_snap (relatorios filtram por agente).
--
-- ESCOPO: ALTER em 2 tabelas COMPARTILHADAS com newportaltefnet/ApuraComercial.
-- Autorizacao explicita do Stakeholder Leader em 08/05/2026 para o ALTER nas 2 tabelas.
-- Procedures pr_generate_standart / pr_generate_premium / variantes _rev permanecem
-- INTACTAS. Zero risco de regressao em codigo legado nao testado ha anos (ADR-0005 D-13).
--
-- ============================================================================
-- DECISAO ARQUITETURAL — POR QUE TRIGGER (ADR-0005)
-- ============================================================================
-- 3 alternativas analisadas em sessao 08/05/2026 com o Stakeholder Leader:
--
-- (A) ALTER PROCEDURE pr_generate_standart / pr_generate_premium / *_rev
--     Vantagem: snapshot consistente desde o nascimento da linha.
--     Desvantagem: alto risco em legado (procedures nao testadas ha anos);
--                  4+ procedures alteradas multiplica vetores de erro;
--                  se quebrar, faturamento mensal inteiro para.
--     Veredito: descartada.
--
-- (B) UPDATE pos-CALL no PHP (gerar_faturamento.php)
--     Vantagem: zero alteracao em procedure; reversivel em segundos.
--     Desvantagem CRITICA: erro silencioso por dependencia humana
--                         (caller futuro esquece UPDATE -> snapshot perdido sem ninguem
--                          notar ate alguem reclamar de comissao errada meses depois);
--                         janela de NULL nao-teorica (UPDATE pode falhar por timeout/lock).
--     Veredito: descartada (Stakeholder Leader apontou a fragilidade em 08/05/2026).
--
-- (C) TRIGGER BEFORE INSERT em tbl_Faturamento  <-- ESCOLHIDA
--     Vantagem: atomicidade real (mesma transacao do INSERT);
--               a prova de caller (qualquer rota que insira recebe snapshot);
--               resiliente a refactor das procedures (atua no NEW.* independente da forma);
--               zero alteracao em procedure legada;
--               rollback simetrico (DROP TRIGGER + DROP COLUMN em 2 comandos).
--     Mitigacoes: COMMENT em colunas apontando pro trigger;
--                 documentacao no PRD-0006 e CLAUDE.md da pasta faturamento;
--                 entrada no inventario de objetos DB.
--
-- ============================================================================

-- ----------------------------------------------------------------------------
-- 0) Workaround sql_mode strict (MySQL 8 + dados legados com '0000-00-00')
-- ----------------------------------------------------------------------------
-- Linhas legadas em tbl_Pedido tem date_solic_cancel='0000-00-00' (formato pre-strict).
-- ALTER TABLE em MySQL 8 com sql_mode strict (ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,
-- NO_ZERO_IN_DATE,NO_ZERO_DATE) recusa a operacao com erro 1292.
-- Solucao: relaxar sql_mode SO PARA ESTA SESSAO. Nao afeta nada fora desta migration.
-- Padrao registrado em memory: project_descompasso_schema_mamp_remoto + ambiente_mamp_local.

SET SESSION sql_mode = '';

-- ----------------------------------------------------------------------------
-- 1) ALTER TABLE tbl_Faturamento — adicionar 3 colunas snapshot do agente
-- ----------------------------------------------------------------------------
-- Tipos espelham EXATAMENTE tbl_Pedido.agente_*_snap (definidos em Migration 002 -> 008).
-- COMMENT em cada coluna aponta pro trigger (defesa contra "esquecimento" por dev novo).

ALTER TABLE tbl_Faturamento
  ADD COLUMN agente_cod_snap INT NULL DEFAULT NULL
    COMMENT '[satelite:agente] Snapshot do agente vinculado ao pedido pai. Populado por trigger trg_agente_snapshot_faturamento_bi (ADR-0005). FK soft -> tbl_Representante.id (idNatureza=3)',
  ADD COLUMN agente_pct_snap DECIMAL(8,4) NULL DEFAULT NULL
    COMMENT '[satelite:agente] Snapshot do percentual do agente. Populado por trigger trg_agente_snapshot_faturamento_bi (ADR-0005)',
  ADD COLUMN agente_modalidade_snap ENUM('pct_venda','pct_comissao','tabela_preco') NULL DEFAULT NULL
    COMMENT '[satelite:agente] Snapshot da modalidade do agente. Populado por trigger trg_agente_snapshot_faturamento_bi (ADR-0005)',
  ADD INDEX idx_faturamento_agente_cod_snap (agente_cod_snap);

-- ----------------------------------------------------------------------------
-- 2) ALTER TABLE tbl_Pedido — adicionar flag de override manual
-- ----------------------------------------------------------------------------
-- Flag setada por UI#13 (insertPedido.php) quando o operador tpUser='Tef.Net' escolhe
-- agente diferente do padrao da revenda. Default 'N' (heranca normal). Override 'S'.
-- Visivel apenas para tpUser='Tef.Net' (D-08 do PRD-0006).
-- Padrao S/N (em vez de TINYINT) por consistencia com newportaltefnet (senha_provisoria etc).

ALTER TABLE tbl_Pedido
  ADD COLUMN agente_snap_override CHAR(1) NOT NULL DEFAULT 'N'
    COMMENT '[satelite:agente] S=snapshot escolhido manualmente diferente do padrao da revenda (UI#13). Default N. Visibilidade restrita a tpUser=Tef.Net (D-08 PRD-0006).';

-- ----------------------------------------------------------------------------
-- 3) CREATE TRIGGER trg_agente_snapshot_faturamento_bi
-- ----------------------------------------------------------------------------
-- BEFORE INSERT em tbl_Faturamento (unico momento em MySQL onde SET NEW.* funciona).
-- Le snapshot do pedido pai via NEW.pedido_id e popula NEW.agente_*_snap.
--
-- Defesa em profundidade:
--   * IF NEW.pedido_id IS NOT NULL  -> gate seguro contra linhas orfas
--   * IF NEW.agente_cod_snap IS NULL -> caller que envia explicitamente sobrescreve
--                                       (overrideable; util para backfill controlado)
--   * LIMIT 1 -> garantia tecnica (pedido_id e FK soft, pode duplicar na pratica)
--
-- IMPORTANTE — RELACIONAMENTO tbl_Faturamento.pedido_id -> tbl_Pedido.cod_Pedido
-- (PK de tbl_Pedido e cod_Pedido, NAO id). Confirmado em probe 08/05/2026:
--   SELECT COLUMN_KEY FROM information_schema.COLUMNS
--   WHERE TABLE_SCHEMA='tefnet_erp' AND TABLE_NAME='tbl_Pedido' AND COLUMN_KEY='PRI';
--   -> retorna apenas cod_Pedido (auto_increment).
--
-- Pedidos pre-AG#4 com agente_cod_snap=NULL em tbl_Pedido -> SELECT retorna NULL ->
-- coluna em tbl_Faturamento fica NULL. Comportamento desejado (apuracao ignora).
--
-- Naming: trg_<namespace>_<funcao>_<tabela>_<tipo>
--   trg_                         -> prefixo padrao MySQL para trigger
--   agente_                      -> namespace satelite (ADR-0004)
--   snapshot_faturamento         -> funcao do trigger
--   _bi                          -> sufixo "before insert"

-- ----------------------------------------------------------------------------
-- IMPORTANTE — POR QUE O TRIGGER USA `SET ... IFNULL(...)` EM VEZ DE BEGIN/END/IF
-- ----------------------------------------------------------------------------
-- O runner (sql/migrate.php) usa PDO::exec(), que NAO suporta a instrucao DELIMITER
-- do mysql client. Sem DELIMITER, o ';' interno de BEGIN/END/IF e interpretado como
-- fim de statement, e o SQL quebra com erro 1327 "Undeclared variable: NEW".
--
-- Solucao funcionalmente equivalente: trigger com UM unico statement SET,
-- usando IFNULL(NEW.x, (SELECT...)) para implementar o "if NULL then look up".
-- Vantagens: sem DELIMITER, sem BEGIN/END, sem IF/THEN — 1 statement so;
-- engolido por PDO::exec sem problema.
-- Custo: 3 lookups separados em tbl_Pedido por linha em vez de 1 — todos por
-- PK indexada (cod_Pedido), microssegundos cada. Desprezivel.
--
-- Comportamento:
--   * Se NEW.agente_cod_snap ja vier preenchido -> IFNULL devolve o valor existente
--     (caller que envia explicitamente sobrescreve; overrideable, igual ao plano original).
--   * Se NEW.pedido_id IS NULL -> SELECT nao acha match -> retorna NULL ->
--     IFNULL(NULL, NULL) = NULL. Coluna em tbl_Faturamento fica NULL (esperado).
--   * Se pedido pre-AG#4 (snapshot NULL em tbl_Pedido) -> IFNULL(NULL, NULL) = NULL.
-- ----------------------------------------------------------------------------

DROP TRIGGER IF EXISTS trg_agente_snapshot_faturamento_bi;

CREATE TRIGGER trg_agente_snapshot_faturamento_bi
BEFORE INSERT ON tbl_Faturamento
FOR EACH ROW
SET
    NEW.agente_cod_snap = IFNULL(
        NEW.agente_cod_snap,
        (SELECT p.agente_cod_snap FROM tbl_Pedido p WHERE p.cod_Pedido = NEW.pedido_id LIMIT 1)
    ),
    NEW.agente_pct_snap = IFNULL(
        NEW.agente_pct_snap,
        (SELECT p.agente_pct_snap FROM tbl_Pedido p WHERE p.cod_Pedido = NEW.pedido_id LIMIT 1)
    ),
    NEW.agente_modalidade_snap = IFNULL(
        NEW.agente_modalidade_snap,
        (SELECT p.agente_modalidade_snap FROM tbl_Pedido p WHERE p.cod_Pedido = NEW.pedido_id LIMIT 1)
    );

-- ============================================================================
-- VERIFICACAO POS-MIGRATION (rodar manualmente apos `php sql/migrate.php up`)
-- ============================================================================
-- 1) Conferir colunas em tbl_Faturamento:
--    SHOW COLUMNS FROM tbl_Faturamento LIKE 'agente_%';
--    -> deve retornar: agente_cod_snap, agente_pct_snap, agente_modalidade_snap
--
-- 2) Conferir coluna em tbl_Pedido:
--    SHOW COLUMNS FROM tbl_Pedido LIKE 'agente_snap_override';
--    -> deve retornar: agente_snap_override (CHAR(1) NOT NULL DEFAULT 'N')
--
-- 3) Conferir trigger criado:
--    SHOW TRIGGERS LIKE 'tbl_Faturamento';
--    -> deve listar: trg_agente_snapshot_faturamento_bi (BEFORE INSERT)
--
-- 4) Smoke teste (opcional, se houver pedido de teste com snapshot):
--    INSERT INTO tbl_Faturamento (pedido_id, ...) VALUES (<id_pedido_com_agente>, ...);
--    SELECT agente_cod_snap, agente_pct_snap, agente_modalidade_snap
--    FROM tbl_Faturamento WHERE id = LAST_INSERT_ID();
--    -> 3 colunas devem estar populadas conforme tbl_Pedido do pedido_id.