-- 1.0.106.sql
-- S8-ORDER-FLOW-KPI-OVERTIME-EXCEPTION-SEED-1：派生 ORDER_FLOW KPI 超期阶段为 ado_s8_exception 事件。
-- 解决问题：order-execution 页面 yellow/red 阶段已是业务事实，但未进入 ado_s8_exception，
--           导致 S8 overview / delivery / production / supply / exception list / dept serious card 空态。
--           本脚本以幂等方式：(A) 新增 3 条阶段缺口 type；(B) 派生 16 条 exception；(C) 派生 16 条 timeline。
-- 执行入口：AutoVersionUpdate.UseAutoVersionUpdate()，在 csproj Version=1.0.106 主节点首次启动时触发。
-- 幂等性：
--   - exception_type：唯一键 (tenant_id, factory_id, type_code) + ON DUPLICATE KEY UPDATE
--   - exception：唯一键 (tenant_id, factory_id, exception_code) + ON DUPLICATE KEY UPDATE
--   - timeline：无唯一键 → INSERT ... SELECT WHERE NOT EXISTS 防重
-- 安全边界：
--   - 不改 ado_s8_order_flow_stage / 不改 order-execution UI / 不启用 watch_rule
--   - 不写 is_deleted 到 exception_type 与 timeline（实证表无该列）
--   - exception 派生的 created_at 用 LEAST(COALESCE(actual_end_at, target_at, NOW()), NOW())
--     防止 BODY_PRODUCTION / FINAL_ASSEMBLY_DELIVERY 阶段 actual_end_at 晚于 DB NOW 制造未来异常
-- 拍板依据：S8-ORDER-FLOW-KPI-OVERTIME-SYNC-DECISION-1 回执（2026-05-14）
-- 2026-05-14


-- =====================================================================
-- A) 3 条 exception_type idempotent upsert
-- =====================================================================
-- 字段映射来源：S8-ORDER-FLOW-KPI-OVERTIME-SYNC-DECISION-1 §3.2
-- 选 id：baseId(1329908100000) + 46/47/48；DB 已确认空闲，且与 PARENT_*(100..104) 不冲突
INSERT INTO ado_s8_exception_type
  (id, tenant_id, factory_id, type_code, type_name, scene_code, severity_default, sla_minutes,
   owner_role_code, escalate_role_code, stats_mode, monitoring_category_key, mobile_visible, icon,
   enabled, sort_no, remark, parent_id, is_group, created_at, updated_at)
VALUES
  (1329908100046, 0, 0, 'PRODUCT_DESIGN_DELAY',          '产品设计延期',     'S1', 'SERIOUS', 240,
   'ROLE_ORDER_PLANNER', 'ROLE_ORDER_PLANNER', 'ALL', 'PRODUCT_DESIGN',          1, NULL,
   1, 113, '[S8-ORDER-FLOW-KPI-OVERTIME-SEED-1] 产品设计阶段 KPI 超期；ORDER_FLOW 派生事件使用。',
   1329908100101, 0, '2026-05-14 00:00:00', NULL),

  (1329908100047, 0, 0, 'BODY_PRODUCTION_DELAY_WARNING', '本体生产延期预警', 'S6', 'FOLLOW',  120,
   'ROLE_PRODUCTION_PLANNER', 'ROLE_PRODUCTION_PLANNER', 'ALL', 'BODY_PRODUCTION',     1, NULL,
   1, 614, '[S8-ORDER-FLOW-KPI-OVERTIME-SEED-1] 本体生产阶段 KPI 超期预警；ORDER_FLOW 派生事件使用。',
   1329908100103, 0, '2026-05-14 00:00:00', NULL),

  (1329908100048, 0, 0, 'DELIVERY_DELAY_WARNING',        '总装发货延期预警', 'S7', 'FOLLOW',  240,
   'ROLE_WH_OUTBOUND', 'ROLE_WH_OUTBOUND', 'ALL', 'FINAL_ASSEMBLY_DELIVERY',           1, NULL,
   1, 713, '[S8-ORDER-FLOW-KPI-OVERTIME-SEED-1] 总装发货阶段 KPI 超期预警；ORDER_FLOW 派生事件使用。',
   1329908100104, 0, '2026-05-14 00:00:00', NULL)
ON DUPLICATE KEY UPDATE
  type_name               = VALUES(type_name),
  scene_code              = VALUES(scene_code),
  severity_default        = VALUES(severity_default),
  sla_minutes             = VALUES(sla_minutes),
  owner_role_code         = VALUES(owner_role_code),
  escalate_role_code      = VALUES(escalate_role_code),
  stats_mode              = VALUES(stats_mode),
  monitoring_category_key = VALUES(monitoring_category_key),
  mobile_visible          = VALUES(mobile_visible),
  enabled                 = VALUES(enabled),
  sort_no                 = VALUES(sort_no),
  remark                  = VALUES(remark),
  parent_id               = VALUES(parent_id),
  is_group                = VALUES(is_group),
  updated_at              = NOW();


-- =====================================================================
-- B) 16 条 ado_s8_exception 派生 upsert
-- =====================================================================
-- 数据来源：ado_s8_order_flow_stage（status IN 'yellow'/'red'）+ ado_s8_order_flow_order JOIN
-- 幂等键：(tenant_id=1, factory_id=1, exception_code='EX-DRV-{order_code}-{flow_short}')
INSERT INTO ado_s8_exception
  (tenant_id, factory_id, exception_code, title, description,
   scene_code, source_type, status, severity, priority_score, priority_level,
   occurrence_dept_id, responsible_dept_id, responsible_group_id, assignee_id, reporter_id,
   sla_deadline, timeout_flag, created_at, is_deleted, created_by,
   process_node_code, related_object_code, module_code,
   active_flow_instance_id, active_flow_biz_type,
   exception_type_code, source_data_source_id, source_rule_id, source_payload,
   dedup_key, last_detected_at, source_rule_code, source_object_type, source_object_id,
   consecutive_hit_count, consecutive_miss_count,
   stage_code, order_flow_code, rule_mechanism)
SELECT
  1                                                       AS tenant_id,
  1                                                       AS factory_id,
  CONCAT('EX-DRV-', s.order_code, '-',
    CASE s.order_flow_code
      WHEN 'ORDER_REVIEW_PLAN_CALC'    THEN 'OR'
      WHEN 'PRODUCT_DESIGN'            THEN 'PD'
      WHEN 'MATERIAL_PURCHASE'         THEN 'MP'
      WHEN 'BODY_PRODUCTION'           THEN 'BP'
      WHEN 'FINAL_ASSEMBLY_DELIVERY'   THEN 'FA'
    END)                                                  AS exception_code,
  CASE
    WHEN s.order_code = 'SO-2026-001' AND s.order_flow_code = 'MATERIAL_PURCHASE'
      THEN '订单 SO-2026-001 材料采购阶段累计偏差关注（节点本身未超 KPI）'
    ELSE CONCAT('订单 ', s.order_code, ' ',
      CASE s.order_flow_code
        WHEN 'ORDER_REVIEW_PLAN_CALC'  THEN '评审/排产/测算'
        WHEN 'PRODUCT_DESIGN'          THEN '产品设计'
        WHEN 'MATERIAL_PURCHASE'       THEN '材料采购'
        WHEN 'BODY_PRODUCTION'         THEN '本体生产'
        WHEN 'FINAL_ASSEMBLY_DELIVERY' THEN '总装发货'
      END,
      ' 超 KPI ', CAST(s.node_variance_days AS CHAR), ' 天')
  END                                                     AS title,
  NULL                                                    AS description,
  CASE s.order_flow_code
    WHEN 'ORDER_REVIEW_PLAN_CALC'    THEN 'S1'
    WHEN 'PRODUCT_DESIGN'            THEN 'S1'
    WHEN 'MATERIAL_PURCHASE'         THEN 'S4'
    WHEN 'BODY_PRODUCTION'           THEN 'S6'
    WHEN 'FINAL_ASSEMBLY_DELIVERY'   THEN 'S7'
  END                                                     AS scene_code,
  'AUTO_WATCH'                                            AS source_type,
  'NEW'                                                   AS status,
  CASE s.status WHEN 'red' THEN 'SERIOUS' WHEN 'yellow' THEN 'FOLLOW' END
                                                          AS severity,
  0                                                       AS priority_score,
  'P3'                                                    AS priority_level,
  CASE WHEN s.order_flow_code IN ('BODY_PRODUCTION','FINAL_ASSEMBLY_DELIVERY')
       THEN 2 ELSE 10 END                                 AS occurrence_dept_id,
  CASE WHEN s.order_flow_code IN ('BODY_PRODUCTION','FINAL_ASSEMBLY_DELIVERY')
       THEN 2 ELSE 10 END                                 AS responsible_dept_id,
  NULL                                                    AS responsible_group_id,
  NULL                                                    AS assignee_id,
  NULL                                                    AS reporter_id,
  DATE_ADD(LEAST(COALESCE(s.actual_end_at, s.target_at, NOW()), NOW()),
           INTERVAL et.sla_minutes MINUTE)                AS sla_deadline,
  0                                                       AS timeout_flag,
  LEAST(COALESCE(s.actual_end_at, s.target_at, NOW()), NOW())
                                                          AS created_at,
  0                                                       AS is_deleted,
  'system:order_flow_kpi_derive'                          AS created_by,
  NULL                                                    AS process_node_code,
  s.order_code                                            AS related_object_code,
  CASE s.order_flow_code
    WHEN 'ORDER_REVIEW_PLAN_CALC'    THEN 'S1'
    WHEN 'PRODUCT_DESIGN'            THEN 'S1'
    WHEN 'MATERIAL_PURCHASE'         THEN 'S4'
    WHEN 'BODY_PRODUCTION'           THEN 'S6'
    WHEN 'FINAL_ASSEMBLY_DELIVERY'   THEN 'S7'
  END                                                     AS module_code,
  NULL                                                    AS active_flow_instance_id,
  NULL                                                    AS active_flow_biz_type,
  et.type_code                                            AS exception_type_code,
  NULL                                                    AS source_data_source_id,
  NULL                                                    AS source_rule_id,
  JSON_OBJECT(
    'source',                 'ORDER_FLOW_KPI_OVERTIME_DERIVED',
    'orderCode',              s.order_code,
    'orderId',                o.id,
    'orderFlowCode',          s.order_flow_code,
    'status',                 s.status,
    'plannedDays',            s.planned_days,
    'actualDays',             s.actual_days,
    'nodeVarianceDays',       s.node_variance_days,
    'cumulativeVarianceDays', s.cumulative_variance_days,
    'targetAt',               DATE_FORMAT(s.target_at,      '%Y-%m-%d %H:%i:%s'),
    'actualEndAt',            DATE_FORMAT(s.actual_end_at,  '%Y-%m-%d %H:%i:%s')
  )                                                       AS source_payload,
  CONCAT('ORDER_FLOW_KPI_OVERTIME:', s.order_code, ':', s.order_flow_code)
                                                          AS dedup_key,
  LEAST(COALESCE(s.actual_end_at, s.target_at, NOW()), NOW())
                                                          AS last_detected_at,
  'ORDER_FLOW_KPI_OVERTIME_DERIVED'                       AS source_rule_code,
  'SALES_ORDER'                                           AS source_object_type,
  CAST(o.id AS CHAR)                                      AS source_object_id,
  1                                                       AS consecutive_hit_count,
  0                                                       AS consecutive_miss_count,
  CASE s.order_flow_code
    WHEN 'ORDER_REVIEW_PLAN_CALC'    THEN 'S1'
    WHEN 'PRODUCT_DESIGN'            THEN 'S1'
    WHEN 'MATERIAL_PURCHASE'         THEN 'S4'
    WHEN 'BODY_PRODUCTION'           THEN 'S6'
    WHEN 'FINAL_ASSEMBLY_DELIVERY'   THEN 'S7'
  END                                                     AS stage_code,
  s.order_flow_code                                       AS order_flow_code,
  'DATE'                                                  AS rule_mechanism
FROM ado_s8_order_flow_stage s
JOIN ado_s8_order_flow_order o
  ON o.id = s.order_id
 AND o.is_deleted = 0
 AND o.tenant_id = 1
 AND o.factory_id = 1
JOIN ado_s8_exception_type et
  ON et.tenant_id = 0
 AND et.factory_id = 0
 AND et.is_group = 0
 AND et.enabled = 1
 AND et.type_code =
   CASE s.order_flow_code
     WHEN 'ORDER_REVIEW_PLAN_CALC'    THEN 'ORDER_DUE_DATE_DELAY'
     WHEN 'PRODUCT_DESIGN'            THEN 'PRODUCT_DESIGN_DELAY'
     WHEN 'MATERIAL_PURCHASE'         THEN 'PURCHASE_EXECUTION_DELAY'
     WHEN 'BODY_PRODUCTION'           THEN 'BODY_PRODUCTION_DELAY_WARNING'
     WHEN 'FINAL_ASSEMBLY_DELIVERY'   THEN 'DELIVERY_DELAY_WARNING'
   END
WHERE s.is_deleted = 0
  AND s.tenant_id  = 1
  AND s.factory_id = 1
  AND s.status IN ('yellow','red')
ON DUPLICATE KEY UPDATE
  title               = VALUES(title),
  scene_code          = VALUES(scene_code),
  module_code         = VALUES(module_code),
  stage_code          = VALUES(stage_code),
  order_flow_code     = VALUES(order_flow_code),
  exception_type_code = VALUES(exception_type_code),
  severity            = VALUES(severity),
  source_payload      = VALUES(source_payload),
  last_detected_at    = VALUES(last_detected_at),
  source_rule_code    = VALUES(source_rule_code),
  source_object_type  = VALUES(source_object_type),
  source_object_id    = VALUES(source_object_id),
  related_object_code = VALUES(related_object_code),
  dedup_key           = VALUES(dedup_key),
  rule_mechanism      = VALUES(rule_mechanism),
  updated_at          = NOW();


-- =====================================================================
-- C) 16 条 ado_s8_exception_timeline 派生（NOT EXISTS 防重）
-- =====================================================================
-- timeline 表无唯一键，不能使用 ON DUPLICATE KEY UPDATE；使用 NOT EXISTS 子查询防重。
-- 关联键：exception_id（每个 source_rule_code='ORDER_FLOW_KPI_OVERTIME_DERIVED' 的 exception 同步 1 条 CREATE）
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,
  'CREATE',
  '派生',
  NULL,
  'NEW',
  NULL,
  'system',
  'ORDER_FLOW KPI 超期派生',
  e.created_at
FROM ado_s8_exception e
WHERE e.tenant_id  = 1
  AND e.factory_id = 1
  AND e.is_deleted = 0
  AND e.source_rule_code = 'ORDER_FLOW_KPI_OVERTIME_DERIVED'
  AND NOT EXISTS (
    SELECT 1
    FROM ado_s8_exception_timeline t
    WHERE t.exception_id = e.id
      AND t.action_code  = 'CREATE'
      AND t.action_remark = 'ORDER_FLOW KPI 超期派生'
  );


-- =====================================================================
-- 验收预期（阶段 4 跑此 SQL 后核对）
-- =====================================================================
-- 1) ado_s8_exception_type 新增 3 条（如已存在则 ON DUPLICATE 刷新 updated_at）
-- 2) ado_s8_exception 新增 16 条（red 6 + yellow 10，含 SO-2026-001 MATERIAL_PURCHASE 累计偏差边界项）
-- 3) ado_s8_exception_timeline 新增 16 条 CREATE 行
-- 4) 重跑此 SQL：A 段 ON DUPLICATE 0 row changed；B 段 ON DUPLICATE 0 row changed；C 段 NOT EXISTS 0 row affected