-- 1.0.111.sql
-- S8-EXCEPTION-LIFECYCLE-DEMO-SEED-1
-- 为 S8 overview 卡片驱动「闭环率 / 平均处理时间」演示能力，将 22 条 active 异常的一部分切到 CLOSED / IN_PROGRESS：
--   * CLOSED 8 条：覆盖 S1/S2/S3/S4/S5/S6/S7 七个 stage，含 SERIOUS 与 FOLLOW 两档；处理时长 4/6/8/12/18/24/36/72h
--   * IN_PROGRESS 5 条：覆盖 S1/S4/S5/S6 四个 stage；assigned_at = created_at + 1h
--   * 其余 9 条保持 NEW
-- 同时为受影响异常补写 timeline ASSIGN / CLOSE 动作行，CLOSE 仅写给 CLOSED 行。
-- 执行入口：AutoVersionUpdate.UseAutoVersionUpdate()，csproj Version=1.0.111 主节点首次启动时触发。
-- 幂等性：
--   * UPDATE 通过 exception_code IN (...) + source_rule_code IN (...) + 目标状态 != 当前 status 防重；
--     closed_at 用 created_at + INTERVAL N HOUR 固定生成，不用 NOW()，保证多次执行 closed_at 不漂移；
--     重复执行后已是 CLOSED 的行不再被 SET（WHERE status <> 'CLOSED'），avg/closeRate 稳定。
--   * timeline INSERT ... WHERE NOT EXISTS (action_code, action_remark='S8 lifecycle demo seed') 防重。
-- 安全边界：
--   * 不动 source_rule_code / exception_code / severity / stage_code / module_code / responsible_dept_id /
--     is_deleted / timeout_flag / sla_deadline；
--   * WHERE 严格限制 source_rule_code IN ('ORDER_FLOW_KPI_OVERTIME_DERIVED','S8_OVERVIEW_DEMO_COVERAGE_SEED')；
--   * 仅 13 条按 exception_code 显式列入，不会扩散到其它行；
--   * 无 DELETE；无 ALTER；无全表 UPDATE；不动 ado_s8_order_flow_*；不动 watch_rule。
-- Rollback: 如需回滚，可执行：
--   UPDATE ado_s8_exception SET status='NEW', assigned_at=NULL, closed_at=NULL
--    WHERE exception_code IN ('EX-DRV-SO-2026-009-OR','EX-DEMO-COV-S2-02','EX-DEMO-COV-S3-02',
--                              'EX-DRV-SO-2026-001-MP','EX-DRV-SO-2026-002-MP','EX-DEMO-COV-S5-02',
--                              'EX-DRV-SO-2026-003-BP','EX-DRV-SO-2026-011-FA',
--                              'EX-DRV-SO-2026-015-OR','EX-DRV-SO-2026-004-PD','EX-DRV-SO-2026-006-MP',
--                              'EX-DEMO-COV-S5-01','EX-DRV-SO-2026-007-BP')
--      AND source_rule_code IN ('ORDER_FLOW_KPI_OVERTIME_DERIVED','S8_OVERVIEW_DEMO_COVERAGE_SEED');
--   DELETE FROM ado_s8_exception_timeline WHERE action_remark='S8 lifecycle demo seed';
-- 2026-05-16

-- 1) CLOSED 8 条：assigned_at = created_at + 1h，closed_at = created_at + Nh
--    幂等：WHERE status <> 'CLOSED' 避免重复执行覆盖 assigned_at（NULL→1h→1h，但若被人工修改过则保留）。
UPDATE ado_s8_exception
SET assigned_at = CASE
        WHEN assigned_at IS NULL THEN DATE_ADD(created_at, INTERVAL 1 HOUR)
        ELSE assigned_at
    END,
    closed_at = CASE exception_code
        WHEN 'EX-DRV-SO-2026-009-OR' THEN DATE_ADD(created_at, INTERVAL 36 HOUR)
        WHEN 'EX-DEMO-COV-S2-02'     THEN DATE_ADD(created_at, INTERVAL 24 HOUR)
        WHEN 'EX-DEMO-COV-S3-02'     THEN DATE_ADD(created_at, INTERVAL  6 HOUR)
        WHEN 'EX-DRV-SO-2026-001-MP' THEN DATE_ADD(created_at, INTERVAL 12 HOUR)
        WHEN 'EX-DRV-SO-2026-002-MP' THEN DATE_ADD(created_at, INTERVAL 18 HOUR)
        WHEN 'EX-DEMO-COV-S5-02'     THEN DATE_ADD(created_at, INTERVAL  8 HOUR)
        WHEN 'EX-DRV-SO-2026-003-BP' THEN DATE_ADD(created_at, INTERVAL  4 HOUR)
        WHEN 'EX-DRV-SO-2026-011-FA' THEN DATE_ADD(created_at, INTERVAL 72 HOUR)
        ELSE closed_at
    END,
    status     = 'CLOSED',
    updated_at = NOW(),
    updated_by = 'system:s8_lifecycle_demo_seed'
WHERE is_deleted = 0
  AND status     <> 'CLOSED'
  AND source_rule_code IN ('ORDER_FLOW_KPI_OVERTIME_DERIVED','S8_OVERVIEW_DEMO_COVERAGE_SEED')
  AND exception_code IN (
    'EX-DRV-SO-2026-009-OR',  -- S1 SERIOUS  36h
    'EX-DEMO-COV-S2-02',      -- S2 SERIOUS  24h
    'EX-DEMO-COV-S3-02',      -- S3 FOLLOW    6h
    'EX-DRV-SO-2026-001-MP',  -- S4 FOLLOW   12h
    'EX-DRV-SO-2026-002-MP',  -- S4 FOLLOW   18h
    'EX-DEMO-COV-S5-02',      -- S5 FOLLOW    8h
    'EX-DRV-SO-2026-003-BP',  -- S6 FOLLOW    4h
    'EX-DRV-SO-2026-011-FA'   -- S7 FOLLOW   72h
  );

-- 2) IN_PROGRESS 5 条：assigned_at = created_at + 1h；closed_at 保持 NULL。
--    幂等：WHERE status NOT IN ('IN_PROGRESS','CLOSED') 避免覆盖已闭环或多次刷新 assigned_at。
UPDATE ado_s8_exception
SET assigned_at = CASE
        WHEN assigned_at IS NULL THEN DATE_ADD(created_at, INTERVAL 1 HOUR)
        ELSE assigned_at
    END,
    closed_at  = NULL,
    status     = 'IN_PROGRESS',
    updated_at = NOW(),
    updated_by = 'system:s8_lifecycle_demo_seed'
WHERE is_deleted = 0
  AND status NOT IN ('IN_PROGRESS','CLOSED')
  AND source_rule_code IN ('ORDER_FLOW_KPI_OVERTIME_DERIVED','S8_OVERVIEW_DEMO_COVERAGE_SEED')
  AND exception_code IN (
    'EX-DRV-SO-2026-015-OR',  -- S1 SERIOUS
    'EX-DRV-SO-2026-004-PD',  -- S1 SERIOUS
    'EX-DRV-SO-2026-006-MP',  -- S4 FOLLOW
    'EX-DEMO-COV-S5-01',      -- S5 FOLLOW
    'EX-DRV-SO-2026-007-BP'   -- S6 FOLLOW
  );

-- 3) timeline ASSIGN：所有 IN_PROGRESS / CLOSED 共 13 条均写一行 ASSIGN，from_status='NEW' → to_status='IN_PROGRESS'。
INSERT INTO ado_s8_exception_timeline
  (exception_id, action_code, action_label, from_status, to_status,
   operator_id, operator_name, action_remark, created_at)
SELECT e.id, 'ASSIGN', '分派', 'NEW', 'IN_PROGRESS',
       NULL, 'system', 'S8 lifecycle demo seed', e.assigned_at
FROM ado_s8_exception e
WHERE e.is_deleted = 0
  AND e.source_rule_code IN ('ORDER_FLOW_KPI_OVERTIME_DERIVED','S8_OVERVIEW_DEMO_COVERAGE_SEED')
  AND e.exception_code IN (
    'EX-DRV-SO-2026-009-OR','EX-DEMO-COV-S2-02','EX-DEMO-COV-S3-02',
    'EX-DRV-SO-2026-001-MP','EX-DRV-SO-2026-002-MP','EX-DEMO-COV-S5-02',
    'EX-DRV-SO-2026-003-BP','EX-DRV-SO-2026-011-FA',
    'EX-DRV-SO-2026-015-OR','EX-DRV-SO-2026-004-PD','EX-DRV-SO-2026-006-MP',
    'EX-DEMO-COV-S5-01','EX-DRV-SO-2026-007-BP'
  )
  AND NOT EXISTS (
    SELECT 1 FROM ado_s8_exception_timeline tl
    WHERE tl.exception_id = e.id
      AND tl.action_code  = 'ASSIGN'
      AND tl.action_remark = 'S8 lifecycle demo seed'
  );

-- 4) timeline CLOSE：仅 8 条 CLOSED 写一行 CLOSE，from_status='IN_PROGRESS' → to_status='CLOSED'。
INSERT INTO ado_s8_exception_timeline
  (exception_id, action_code, action_label, from_status, to_status,
   operator_id, operator_name, action_remark, created_at)
SELECT e.id, 'CLOSE', '闭环', 'IN_PROGRESS', 'CLOSED',
       NULL, 'system', 'S8 lifecycle demo seed', e.closed_at
FROM ado_s8_exception e
WHERE e.is_deleted = 0
  AND e.status     = 'CLOSED'
  AND e.source_rule_code IN ('ORDER_FLOW_KPI_OVERTIME_DERIVED','S8_OVERVIEW_DEMO_COVERAGE_SEED')
  AND e.exception_code IN (
    'EX-DRV-SO-2026-009-OR','EX-DEMO-COV-S2-02','EX-DEMO-COV-S3-02',
    'EX-DRV-SO-2026-001-MP','EX-DRV-SO-2026-002-MP','EX-DEMO-COV-S5-02',
    'EX-DRV-SO-2026-003-BP','EX-DRV-SO-2026-011-FA'
  )
  AND NOT EXISTS (
    SELECT 1 FROM ado_s8_exception_timeline tl
    WHERE tl.exception_id = e.id
      AND tl.action_code  = 'CLOSE'
      AND tl.action_remark = 'S8 lifecycle demo seed'
  );

-- 验证 SQL（注释，仅人工核对）：
-- SELECT status, COUNT(*) FROM ado_s8_exception WHERE is_deleted=0 GROUP BY status;
-- SELECT stage_code, COUNT(*) total, SUM(status='CLOSED') closed_cnt,
--        ROUND(SUM(status='CLOSED')*100.0/COUNT(*), 1) close_rate,
--        ROUND(AVG(CASE WHEN closed_at IS NOT NULL THEN TIMESTAMPDIFF(MINUTE, created_at, closed_at) END)/60, 2) avg_hours
--   FROM ado_s8_exception WHERE is_deleted=0 GROUP BY stage_code ORDER BY stage_code;
-- SELECT action_code, to_status, COUNT(*) FROM ado_s8_exception_timeline
--  WHERE action_remark='S8 lifecycle demo seed' GROUP BY action_code, to_status;