AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/1.0.110.sql

-- 1.0.110.sql
-- S8-OVERVIEW-DEMO-DATA-COVERAGE-SEED-1
-- 为 S8 overview 演示补 6 条覆盖 seed：让 S2/S3/S5 三张卡 + QDC.QUALITY/DELIVERY/INVENTORY 三桶 + 6 个 period
-- (today / this_week / this_month / last_24h / last_7d / last_30d) 全部有数据；
-- 全部挂到现有 in_progress 订单（SO-2026-017/018/019）+ 现有部门（D-QA / D-PROD / D-PURCHASE / D-WAREHOUSE），
-- **不使用 D-UNASSIGNED**。
-- 执行入口：AutoVersionUpdate.UseAutoVersionUpdate()，csproj Version=1.0.110 主节点首次启动时触发。
-- 幂等性：
--   * 主表 INSERT ... ON DUPLICATE KEY UPDATE 走唯一键 (tenant_id, factory_id, exception_code)；
--     重复执行只刷新 updated_at/updated_by，不刷新 created_at，不破坏时间窗覆盖。
--   * timeline INSERT ... SELECT ... WHERE NOT EXISTS (exception_id, action_code='CREATE')，防重 6 条。
-- 安全边界：不新增 exception_type；不动 watch_rule；不改 ado_s8_order_flow_stage；
--           不动现有 16 条 ORDER_FLOW_KPI_OVERTIME_DERIVED；source_rule_code 全部为 S8_OVERVIEW_DEMO_COVERAGE_SEED，
--           rollback 可一键 UPDATE is_deleted=1 WHERE source_rule_code='S8_OVERVIEW_DEMO_COVERAGE_SEED'。
-- 2026-05-16

-- 1) 6 条 seed 主表 upsert
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, sla_deadline, timeout_flag,
   created_at, is_deleted, created_by,
   related_object_code, module_code, exception_type_code, 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,                                     -- tenant_id（与现有 16 条派生异常对齐）
  1,                                     -- factory_id
  s.exception_code,
  s.title,
  s.description,
  s.stage_code,                          -- scene_code = stage_code
  'AUTO_WATCH',
  'NEW',
  s.severity,
  0.00,
  'P3',
  d.RecID,                               -- occurrence_dept_id
  d.RecID,                               -- responsible_dept_id（与 occurrence 同值）
  DATE_ADD(s.created_at, INTERVAL IFNULL(t.sla_minutes, 240) MINUTE),
  0,                                     -- timeout_flag
  s.created_at,
  0,                                     -- is_deleted
  'system:s8_overview_demo_coverage_seed',
  s.related_object_code,
  s.stage_code,                          -- module_code = stage_code
  s.type_code,
  JSON_OBJECT(
    'seedPurpose',     'S8_OVERVIEW_DEMO_COVERAGE',
    'stage',           s.stage_code,
    'typeCode',        s.type_code,
    'orderCode',       s.related_object_code,
    'deptCode',        s.dept_code,
    'periodCoverage',  s.period_coverage,
    'source',          'S8_OVERVIEW_DEMO_COVERAGE_SEED'),
  CONCAT('S8_OVERVIEW_DEMO_COVERAGE:', s.stage_code, ':', s.type_code, ':', s.seq),
  s.created_at,
  'S8_OVERVIEW_DEMO_COVERAGE_SEED',
  'SALES_ORDER',
  CAST(o.id AS CHAR),
  1,                                     -- consecutive_hit_count
  0,                                     -- consecutive_miss_count
  s.stage_code,
  NULL,                                  -- order_flow_code = NULL（S2/S3/S5 不属于 ORDER_FLOW 5 节点）
  'DATE'
FROM (
  SELECT 'EX-DEMO-COV-S2-01' AS exception_code, '01' AS seq,
         'S2' AS stage_code, 'MFG_QUALITY_ABNORMAL' AS type_code, 'SERIOUS' AS severity,
         'SO-2026-019' AS related_object_code, 'D-QA' AS dept_code,
         CAST('2026-05-16 08:00:00' AS DATETIME) AS created_at,
         '订单 SO-2026-019 制造协同出现批量质量异常 [演示覆盖]' AS title,
         '用于 S8 overview 演示覆盖：S2 制造协同 + QDC.QUALITY；不代表运行时规则命中。' AS description,
         'today,last_24h,this_week,this_month,last_7d,last_30d' AS period_coverage
  UNION ALL SELECT 'EX-DEMO-COV-S3-01', '01',
         'S3', 'SUPPLIER_DELIVERY_DELAY_WARNING', 'FOLLOW',
         'SO-2026-018', 'D-PURCHASE',
         CAST('2026-05-16 09:30:00' AS DATETIME),
         '订单 SO-2026-018 供应商交付延期预警 [演示覆盖]',
         '用于 S8 overview 演示覆盖：S3 供应协同 + QDC.DELIVERY；不代表运行时规则命中。',
         'today,last_24h,this_week,this_month,last_7d,last_30d'
  UNION ALL SELECT 'EX-DEMO-COV-S5-01', '01',
         'S5', 'MATERIAL_STOCK_ABNORMAL', 'FOLLOW',
         'SO-2026-018', 'D-WAREHOUSE',
         CAST('2026-05-16 06:00:00' AS DATETIME),    -- 5/16 凌晨：覆盖 today/last_24h
         '订单 SO-2026-018 物料库存水位异常 [演示覆盖]',
         '用于 S8 overview 演示覆盖：S5 物料仓储 + QDC.INVENTORY；不代表运行时规则命中。',
         'today,last_24h,this_week,this_month,last_7d,last_30d'
  UNION ALL SELECT 'EX-DEMO-COV-S2-02', '02',
         'S2', 'EQUIP_FAULT', 'SERIOUS',
         'SO-2026-019', 'D-PROD',
         CAST('2026-05-12 10:00:00' AS DATETIME),    -- 本周二：覆盖 this_week/last_7d/this_month/last_30d
         '订单 SO-2026-019 关键设备停机故障 [演示覆盖]',
         '用于 S8 overview 演示覆盖：S2 设备故障，让 D-PROD 出现 SERIOUS；不代表运行时规则命中。',
         'this_week,this_month,last_7d,last_30d'
  UNION ALL SELECT 'EX-DEMO-COV-S3-02', '02',
         'S3', 'SUPPLIER_ETA_ISSUE', 'FOLLOW',
         'SO-2026-017', 'D-PURCHASE',
         CAST('2026-05-12 11:30:00' AS DATETIME),
         '订单 SO-2026-017 供应商未回复 ETA [演示覆盖]',
         '用于 S8 overview 演示覆盖：S3 供应商 ETA 异常（不在 QDC whitelist，仅点亮 S3 卡）。',
         'this_week,this_month,last_7d,last_30d'
  UNION ALL SELECT 'EX-DEMO-COV-S5-02', '02',
         'S5', 'INVENTORY_TURNOVER_ABNORMAL', 'FOLLOW',
         'SO-2026-018', 'D-WAREHOUSE',
         CAST('2026-05-03 09:00:00' AS DATETIME),    -- 本月初：覆盖 this_month/last_30d
         '订单 SO-2026-018 库存周转率异常 [演示覆盖]',
         '用于 S8 overview 演示覆盖：S5 库存周转 + QDC.INVENTORY；不代表运行时规则命中。',
         'this_month,last_30d'
) s
JOIN DepartmentMaster d
  ON d.Department = s.dept_code AND d.factory_ref_id = 1 AND d.IsActive = 1
LEFT JOIN ado_s8_exception_type t
  ON t.type_code = s.type_code AND t.tenant_id = 0 AND t.factory_id = 0
LEFT JOIN ado_s8_order_flow_order o
  ON o.order_code = s.related_object_code AND o.is_deleted = 0
ON DUPLICATE KEY UPDATE
  updated_at = NOW(),
  updated_by = 'system:s8_overview_demo_coverage_seed';

-- 2) 写 timeline（CREATE 动作，按 (exception_id, action_code) 去重）
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',
  'S8 overview demo coverage seed',
  e.created_at
FROM ado_s8_exception e
WHERE e.source_rule_code = 'S8_OVERVIEW_DEMO_COVERAGE_SEED'
  AND NOT EXISTS (
    SELECT 1 FROM ado_s8_exception_timeline tl
    WHERE tl.exception_id = e.id AND tl.action_code = 'CREATE'
  );

-- 验证 SQL（注释，仅人工核对）：
-- SELECT COUNT(*) FROM ado_s8_exception WHERE is_deleted=0 AND source_rule_code='S8_OVERVIEW_DEMO_COVERAGE_SEED';
-- SELECT stage_code, severity, COUNT(*) FROM ado_s8_exception WHERE is_deleted=0
--   GROUP BY stage_code, severity ORDER BY stage_code, severity;
-- SELECT COUNT(*) FROM ado_s8_exception_timeline WHERE action_remark='S8 overview demo coverage seed';