-- =====================================================================================
-- S8-DEMO-DATA-SEED-1 / STEP 2：UATAdminA Golden Demo Dataset（Order Flow 12 表）
--
-- 目标 scope ：tenant=838257186181189 (UATTEST_CHL) / factory=838257186320453
-- 源  模板   ：tenant=797403760988230 (AIDOP-Demo) / factory=797403760988231 —— 只读，绝不修改
--
-- 设计要点（S8-DEMO-DATA-SEED-1 STEP 1 拍板）：
--   D1-A  SO-2026-012 定义为「采购延期但后续追回工期」对照单，actual_cycle_days=30
--   D2-A  snapshot 按 Golden Roster 重算（total_orders=5 / total_customers=5），不复制 PPT 的 105
--   D3    KPI 口径：PO=green 占比、WO=AVG(achievement_rate)
--   D4-A  日期整体平移 +96 天（源业务窗 2026-04-15~05-18 → 2026-07-20~08-22，落入 Q3 且不含未来日期）
--   客户身份统一取 UAT S0 CustMaster 主数据，不沿用源 Demo 的「电气设备厂A/B/D/E」
--
-- ID 段：UAT 专用固定段 13299093xxxxx ~ 13299094xxxxx（全库未占用），无 MAX(id)+1
-- 幂等：每条 INSERT 均带 NOT EXISTS(id) 守卫；012 修正为对固定值的 UPDATE（天然幂等）
-- 系统审计列 created_at 固定写 '2026-08-27 00:00:00'（确定性，非 NOW()）；
--   这 6 张表无业务日期列，且经代码核查不参与任何页面过滤，故不做业务平移。
-- =====================================================================================

-- ---------------------------------------------------------------------------
-- 1) 订单主表：5 条 Golden Order（客户身份改绑 UAT CustMaster）
-- ---------------------------------------------------------------------------
INSERT INTO `ado_s8_order_flow_order`
  (`id`,`tenant_id`,`factory_id`,`order_code`,`product_name`,`product_line`,`customer_code`,`customer_name`,
   `customer_type`,`region`,`priority`,`workflow_status`,`current_order_flow_code`,`release_at`,
   `target_cycle_days`,`actual_cycle_days`,`response_minutes`,`processing_minutes`,`total_loss_minutes`,
   `scenario_code`,`data_source`,`created_at`,`updated_at`,`is_deleted`)
SELECT 1329909300000 + m.n, 838257186181189, 838257186320453,
       s.`order_code`, s.`product_name`, s.`product_line`,
       m.cust_code, m.cust_name,
       s.`customer_type`, s.`region`, s.`priority`, s.`workflow_status`, s.`current_order_flow_code`,
       DATE_ADD(s.`release_at`, INTERVAL 96 DAY),
       s.`target_cycle_days`,
       CASE WHEN s.`order_code` = 'SO-2026-012' THEN 30.0 ELSE s.`actual_cycle_days` END,
       s.`response_minutes`, s.`processing_minutes`, s.`total_loss_minutes`,
       'DEMO', 'SEED', '2026-08-27 00:00:00', NULL, 0
FROM `ado_s8_order_flow_order` s
JOIN (
  SELECT 'SO-2026-001' AS order_code, 1 AS n, '10000000'     AS cust_code, '瑞贝德'         AS cust_name UNION ALL
  SELECT 'SO-2026-002',               2,      '10000002',                  '通广'                        UNION ALL
  SELECT 'SO-2026-012',               3,      '10000004',                  '岛包'                        UNION ALL
  SELECT 'SO-2026-019',               4,      'UATA-A01-CUS',              'A套UAT客户'                  UNION ALL
  SELECT 'SO-2026-020',               5,      'CUST0005',                  'TJRQWKQXGFYXGS'
) m ON m.order_code = s.`order_code`
WHERE s.`tenant_id` = 797403760988230 AND s.`factory_id` = 797403760988231 AND s.`is_deleted` = 0
  AND NOT EXISTS (SELECT 1 FROM `ado_s8_order_flow_order` x WHERE x.`id` = 1329909300000 + m.n);

-- ---------------------------------------------------------------------------
-- 2) 阶段表：5 单 × 5 阶段 = 25 行
-- ---------------------------------------------------------------------------
INSERT INTO `ado_s8_order_flow_stage`
  (`id`,`order_id`,`order_code`,`order_flow_code`,`order_flow_name`,`owner_dept`,`sort_no`,
   `planned_days`,`actual_days`,`target_at`,`actual_start_at`,`actual_end_at`,`status`,
   `node_variance_days`,`cumulative_variance_days`,`scenario_code`,`data_source`,
   `tenant_id`,`factory_id`,`created_at`,`updated_at`,`is_deleted`)
SELECT 1329909310000 + (m.n - 1) * 5 + s.`sort_no`,
       1329909300000 + m.n, s.`order_code`, s.`order_flow_code`, s.`order_flow_name`, s.`owner_dept`, s.`sort_no`,
       s.`planned_days`, s.`actual_days`,
       DATE_ADD(s.`target_at`,        INTERVAL 96 DAY),
       DATE_ADD(s.`actual_start_at`,  INTERVAL 96 DAY),
       DATE_ADD(s.`actual_end_at`,    INTERVAL 96 DAY),
       s.`status`, s.`node_variance_days`, s.`cumulative_variance_days`,
       'DEMO', 'SEED', 838257186181189, 838257186320453, '2026-08-27 00:00:00', NULL, 0
FROM `ado_s8_order_flow_stage` s
JOIN (
  SELECT 'SO-2026-001' AS order_code, 1 AS n UNION ALL SELECT 'SO-2026-002', 2 UNION ALL
  SELECT 'SO-2026-012', 3 UNION ALL SELECT 'SO-2026-019', 4 UNION ALL SELECT 'SO-2026-020', 5
) m ON m.order_code = s.`order_code`
WHERE s.`tenant_id` = 797403760988230 AND s.`is_deleted` = 0
  AND NOT EXISTS (SELECT 1 FROM `ado_s8_order_flow_stage` x
                  WHERE x.`id` = 1329909310000 + (m.n - 1) * 5 + s.`sort_no`);

-- ---------------------------------------------------------------------------
-- 3) 子步骤：5 单 × 5 = 25 行（仅 ORDER_REVIEW_PLAN_CALC 建模）
-- ---------------------------------------------------------------------------
INSERT INTO `ado_s8_order_flow_substep`
  (`id`,`order_id`,`order_code`,`order_flow_code`,`substep_code`,`substep_name`,`pi_hours`,`actual_hours`,
   `status`,`sort_no`,`scenario_code`,`data_source`,`tenant_id`,`factory_id`,`created_at`,`updated_at`,`is_deleted`)
SELECT 1329909320000 + (m.n - 1) * 5 + s.`sort_no`,
       1329909300000 + m.n, s.`order_code`, s.`order_flow_code`, s.`substep_code`, s.`substep_name`,
       s.`pi_hours`, s.`actual_hours`, s.`status`, s.`sort_no`,
       'DEMO', 'SEED', 838257186181189, 838257186320453, '2026-08-27 00:00:00', NULL, 0
FROM `ado_s8_order_flow_substep` s
JOIN (
  SELECT 'SO-2026-001' AS order_code, 1 AS n UNION ALL SELECT 'SO-2026-002', 2 UNION ALL
  SELECT 'SO-2026-012', 3 UNION ALL SELECT 'SO-2026-019', 4 UNION ALL SELECT 'SO-2026-020', 5
) m ON m.order_code = s.`order_code`
WHERE s.`tenant_id` = 797403760988230 AND s.`is_deleted` = 0
  AND NOT EXISTS (SELECT 1 FROM `ado_s8_order_flow_substep` x
                  WHERE x.`id` = 1329909320000 + (m.n - 1) * 5 + s.`sort_no`);

-- ---------------------------------------------------------------------------
-- 4) 子步骤单元：5 单 × 15 = 75 行
-- ---------------------------------------------------------------------------
INSERT INTO `ado_s8_order_flow_substep_unit`
  (`id`,`substep_id`,`order_id`,`order_code`,`order_flow_code`,`substep_code`,`unit_code`,`unit_name`,
   `pi_hours`,`actual_hours`,`status`,`sort_no`,`scenario_code`,`data_source`,
   `tenant_id`,`factory_id`,`created_at`,`updated_at`,`is_deleted`)
SELECT 1329909330000 + (m.n - 1) * 15 + u.rn,
       1329909320000 + (m.n - 1) * 5 + sub.`sort_no`,
       1329909300000 + m.n, u.`order_code`, u.`order_flow_code`, u.`substep_code`, u.`unit_code`, u.`unit_name`,
       u.`pi_hours`, u.`actual_hours`, u.`status`, u.`sort_no`,
       'DEMO', 'SEED', 838257186181189, 838257186320453, '2026-08-27 00:00:00', NULL, 0
FROM (
  SELECT t.*, ROW_NUMBER() OVER (PARTITION BY t.`order_code` ORDER BY t.`id`) AS rn
  FROM `ado_s8_order_flow_substep_unit` t
  WHERE t.`tenant_id` = 797403760988230 AND t.`is_deleted` = 0
) u
JOIN `ado_s8_order_flow_substep` sub ON sub.`id` = u.`substep_id`
JOIN (
  SELECT 'SO-2026-001' AS order_code, 1 AS n UNION ALL SELECT 'SO-2026-002', 2 UNION ALL
  SELECT 'SO-2026-012', 3 UNION ALL SELECT 'SO-2026-019', 4 UNION ALL SELECT 'SO-2026-020', 5
) m ON m.order_code = u.`order_code`
WHERE NOT EXISTS (SELECT 1 FROM `ado_s8_order_flow_substep_unit` x
                  WHERE x.`id` = 1329909330000 + (m.n - 1) * 15 + u.rn);

-- ---------------------------------------------------------------------------
-- 5) 产品设计图纸：5 单 × 7 = 35 行（4 个业务日期列平移）
-- ---------------------------------------------------------------------------
INSERT INTO `ado_s8_order_flow_product_design_drawing`
  (`id`,`tenant_id`,`factory_id`,`order_code`,`drawing_no`,`product_type`,`product_quantity`,`responsible_person`,
   `planned_start_date`,`planned_end_date`,`actual_start_date`,`actual_end_date`,
   `kpi_days`,`actual_days`,`is_achieved`,`status`,`sort_no`,`is_deleted`,`create_time`,`update_time`)
SELECT 1329909340000 + (m.n - 1) * 7 + d.rn,
       838257186181189, 838257186320453,
       d.`order_code`, d.`drawing_no`, d.`product_type`, d.`product_quantity`, d.`responsible_person`,
       DATE_ADD(d.`planned_start_date`, INTERVAL 96 DAY),
       DATE_ADD(d.`planned_end_date`,   INTERVAL 96 DAY),
       DATE_ADD(d.`actual_start_date`,  INTERVAL 96 DAY),
       DATE_ADD(d.`actual_end_date`,    INTERVAL 96 DAY),
       d.`kpi_days`, d.`actual_days`, d.`is_achieved`, d.`status`, d.`sort_no`,
       0, '2026-08-27 00:00:00', NULL
FROM (
  SELECT t.*, ROW_NUMBER() OVER (PARTITION BY t.`order_code` ORDER BY t.`id`) AS rn
  FROM `ado_s8_order_flow_product_design_drawing` t
  WHERE t.`tenant_id` = 797403760988230 AND t.`is_deleted` = 0
) d
JOIN (
  SELECT 'SO-2026-001' AS order_code, 1 AS n UNION ALL SELECT 'SO-2026-002', 2 UNION ALL
  SELECT 'SO-2026-012', 3 UNION ALL SELECT 'SO-2026-019', 4 UNION ALL SELECT 'SO-2026-020', 5
) m ON m.order_code = d.`order_code`
WHERE NOT EXISTS (SELECT 1 FROM `ado_s8_order_flow_product_design_drawing` x
                  WHERE x.`id` = 1329909340000 + (m.n - 1) * 7 + d.rn);

-- ---------------------------------------------------------------------------
-- 6a) 材料采购透视 —— baseline 行 48 条（PPT 第 4 页真值参考基线，order_id IS NULL）
--     语义 = BUSINESS/PPT REFERENCE BASELINE，非 20 单聚合 → 原样复制
-- ---------------------------------------------------------------------------
INSERT INTO `ado_s8_order_flow_procurement_pivot`
  (`id`,`order_id`,`order_code`,`material_code`,`supplier_code`,`spec_code`,`cycle_days`,`status`,
   `scenario_code`,`data_source`,`tenant_id`,`factory_id`,`created_at`,`updated_at`,`is_deleted`,
   `impact_count`,`kit_rate`)
SELECT 1329909350000 + p.rn, NULL, NULL,
       p.`material_code`, p.`supplier_code`, p.`spec_code`, p.`cycle_days`, p.`status`,
       p.`scenario_code`, 'SEED', 838257186181189, 838257186320453, '2026-08-27 00:00:00', NULL, 0,
       p.`impact_count`, p.`kit_rate`
FROM (
  SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.`id`) AS rn
  FROM `ado_s8_order_flow_procurement_pivot` t
  WHERE t.`tenant_id` = 797403760988230 AND t.`is_deleted` = 0 AND t.`order_id` IS NULL
) p
WHERE NOT EXISTS (SELECT 1 FROM `ado_s8_order_flow_procurement_pivot` x
                  WHERE x.`id` = 1329909350000 + p.rn);

-- 6b) 材料采购透视 —— 订单级行 5 单 × 48 = 240 条
INSERT INTO `ado_s8_order_flow_procurement_pivot`
  (`id`,`order_id`,`order_code`,`material_code`,`supplier_code`,`spec_code`,`cycle_days`,`status`,
   `scenario_code`,`data_source`,`tenant_id`,`factory_id`,`created_at`,`updated_at`,`is_deleted`,
   `impact_count`,`kit_rate`)
SELECT 1329909360000 + (m.n - 1) * 48 + p.rn,
       1329909300000 + m.n, p.`order_code`,
       p.`material_code`, p.`supplier_code`, p.`spec_code`, p.`cycle_days`, p.`status`,
       p.`scenario_code`, 'SEED', 838257186181189, 838257186320453, '2026-08-27 00:00:00', NULL, 0,
       p.`impact_count`, p.`kit_rate`
FROM (
  SELECT t.*, ROW_NUMBER() OVER (PARTITION BY t.`order_code` ORDER BY t.`id`) AS rn
  FROM `ado_s8_order_flow_procurement_pivot` t
  WHERE t.`tenant_id` = 797403760988230 AND t.`is_deleted` = 0 AND t.`order_id` IS NOT NULL
) p
JOIN (
  SELECT 'SO-2026-001' AS order_code, 1 AS n UNION ALL SELECT 'SO-2026-002', 2 UNION ALL
  SELECT 'SO-2026-012', 3 UNION ALL SELECT 'SO-2026-019', 4 UNION ALL SELECT 'SO-2026-020', 5
) m ON m.order_code = p.`order_code`
WHERE NOT EXISTS (SELECT 1 FROM `ado_s8_order_flow_procurement_pivot` x
                  WHERE x.`id` = 1329909360000 + (m.n - 1) * 48 + p.rn);

-- ---------------------------------------------------------------------------
-- 7) 本体生产 · 工序明细：baseline 5 + 订单级 20 = 25 行
--    baseline 语义 = MANUFACTURING_DETAIL_FIXTURE 参考基线 → 原样复制
-- ---------------------------------------------------------------------------
INSERT INTO `ado_s8_order_flow_manufacturing_process`
  (`id`,`order_id`,`order_code`,`process_code`,`process_name`,`pi_days`,`actual_days`,`cycle_status`,
   `plan_qty`,`achievement_rate`,`achievement_status`,`sort_no`,`scenario_code`,`data_source`,
   `tenant_id`,`factory_id`,`created_at`,`updated_at`,`is_deleted`)
SELECT 1329909380000 + p.rn, NULL, NULL,
       p.`process_code`, p.`process_name`, p.`pi_days`, p.`actual_days`, p.`cycle_status`,
       p.`plan_qty`, p.`achievement_rate`, p.`achievement_status`, p.`sort_no`,
       p.`scenario_code`, 'SEED', 838257186181189, 838257186320453, '2026-08-27 00:00:00', NULL, 0
FROM (
  SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.`sort_no`) AS rn
  FROM `ado_s8_order_flow_manufacturing_process` t
  WHERE t.`tenant_id` = 797403760988230 AND t.`is_deleted` = 0 AND t.`order_id` IS NULL
) p
WHERE NOT EXISTS (SELECT 1 FROM `ado_s8_order_flow_manufacturing_process` x
                  WHERE x.`id` = 1329909380000 + p.rn);

INSERT INTO `ado_s8_order_flow_manufacturing_process`
  (`id`,`order_id`,`order_code`,`process_code`,`process_name`,`pi_days`,`actual_days`,`cycle_status`,
   `plan_qty`,`achievement_rate`,`achievement_status`,`sort_no`,`scenario_code`,`data_source`,
   `tenant_id`,`factory_id`,`created_at`,`updated_at`,`is_deleted`)
SELECT 1329909380100 + (m.n - 1) * 5 + p.`sort_no`,
       1329909300000 + m.n, p.`order_code`,
       p.`process_code`, p.`process_name`, p.`pi_days`, p.`actual_days`, p.`cycle_status`,
       p.`plan_qty`, p.`achievement_rate`, p.`achievement_status`, p.`sort_no`,
       p.`scenario_code`, 'SEED', 838257186181189, 838257186320453, '2026-08-27 00:00:00', NULL, 0
FROM `ado_s8_order_flow_manufacturing_process` p
JOIN (
  SELECT 'SO-2026-001' AS order_code, 1 AS n UNION ALL SELECT 'SO-2026-002', 2 UNION ALL
  SELECT 'SO-2026-012', 3 UNION ALL SELECT 'SO-2026-019', 4 UNION ALL SELECT 'SO-2026-020', 5
) m ON m.order_code = p.`order_code`
WHERE p.`tenant_id` = 797403760988230 AND p.`is_deleted` = 0 AND p.`order_id` IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `ado_s8_order_flow_manufacturing_process` x
                  WHERE x.`id` = 1329909380100 + (m.n - 1) * 5 + p.`sort_no`);

-- ---------------------------------------------------------------------------
-- 8) 本体生产 · 损失因素：baseline 5 + 订单级 20 = 25 行
-- ---------------------------------------------------------------------------
INSERT INTO `ado_s8_order_flow_manufacturing_loss_factor`
  (`id`,`order_id`,`order_code`,`factor_code`,`factor_name`,`count_value`,`ratio_pct`,`loss_hours`,
   `sort_no`,`scenario_code`,`data_source`,`tenant_id`,`factory_id`,`created_at`,`updated_at`,`is_deleted`)
SELECT 1329909390000 + f.rn, NULL, NULL,
       f.`factor_code`, f.`factor_name`, f.`count_value`, f.`ratio_pct`, f.`loss_hours`, f.`sort_no`,
       f.`scenario_code`, 'SEED', 838257186181189, 838257186320453, '2026-08-27 00:00:00', NULL, 0
FROM (
  SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.`sort_no`) AS rn
  FROM `ado_s8_order_flow_manufacturing_loss_factor` t
  WHERE t.`tenant_id` = 797403760988230 AND t.`is_deleted` = 0 AND t.`order_id` IS NULL
) f
WHERE NOT EXISTS (SELECT 1 FROM `ado_s8_order_flow_manufacturing_loss_factor` x
                  WHERE x.`id` = 1329909390000 + f.rn);

INSERT INTO `ado_s8_order_flow_manufacturing_loss_factor`
  (`id`,`order_id`,`order_code`,`factor_code`,`factor_name`,`count_value`,`ratio_pct`,`loss_hours`,
   `sort_no`,`scenario_code`,`data_source`,`tenant_id`,`factory_id`,`created_at`,`updated_at`,`is_deleted`)
SELECT 1329909390100 + (m.n - 1) * 5 + f.`sort_no`,
       1329909300000 + m.n, f.`order_code`,
       f.`factor_code`, f.`factor_name`, f.`count_value`, f.`ratio_pct`, f.`loss_hours`, f.`sort_no`,
       f.`scenario_code`, 'SEED', 838257186181189, 838257186320453, '2026-08-27 00:00:00', NULL, 0
FROM `ado_s8_order_flow_manufacturing_loss_factor` f
JOIN (
  SELECT 'SO-2026-001' AS order_code, 1 AS n UNION ALL SELECT 'SO-2026-002', 2 UNION ALL
  SELECT 'SO-2026-012', 3 UNION ALL SELECT 'SO-2026-019', 4 UNION ALL SELECT 'SO-2026-020', 5
) m ON m.order_code = f.`order_code`
WHERE f.`tenant_id` = 797403760988230 AND f.`is_deleted` = 0 AND f.`order_id` IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `ado_s8_order_flow_manufacturing_loss_factor` x
                  WHERE x.`id` = 1329909390100 + (m.n - 1) * 5 + f.`sort_no`);

-- ---------------------------------------------------------------------------
-- 9) 本体生产 · 操作员表现：baseline 3 + 订单级 12 = 15 行
-- ---------------------------------------------------------------------------
INSERT INTO `ado_s8_order_flow_manufacturing_operator`
  (`id`,`order_id`,`order_code`,`operator_code`,`operator_name`,`avg_hours`,`status`,`sort_no`,
   `scenario_code`,`data_source`,`tenant_id`,`factory_id`,`created_at`,`updated_at`,`is_deleted`)
SELECT 1329909400000 + o.rn, NULL, NULL,
       o.`operator_code`, o.`operator_name`, o.`avg_hours`, o.`status`, o.`sort_no`,
       o.`scenario_code`, 'SEED', 838257186181189, 838257186320453, '2026-08-27 00:00:00', NULL, 0
FROM (
  SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.`sort_no`) AS rn
  FROM `ado_s8_order_flow_manufacturing_operator` t
  WHERE t.`tenant_id` = 797403760988230 AND t.`is_deleted` = 0 AND t.`order_id` IS NULL
) o
WHERE NOT EXISTS (SELECT 1 FROM `ado_s8_order_flow_manufacturing_operator` x
                  WHERE x.`id` = 1329909400000 + o.rn);

INSERT INTO `ado_s8_order_flow_manufacturing_operator`
  (`id`,`order_id`,`order_code`,`operator_code`,`operator_name`,`avg_hours`,`status`,`sort_no`,
   `scenario_code`,`data_source`,`tenant_id`,`factory_id`,`created_at`,`updated_at`,`is_deleted`)
SELECT 1329909400100 + (m.n - 1) * 3 + o.`sort_no`,
       1329909300000 + m.n, o.`order_code`,
       o.`operator_code`, o.`operator_name`, o.`avg_hours`, o.`status`, o.`sort_no`,
       o.`scenario_code`, 'SEED', 838257186181189, 838257186320453, '2026-08-27 00:00:00', NULL, 0
FROM `ado_s8_order_flow_manufacturing_operator` o
JOIN (
  SELECT 'SO-2026-001' AS order_code, 1 AS n UNION ALL SELECT 'SO-2026-002', 2 UNION ALL
  SELECT 'SO-2026-012', 3 UNION ALL SELECT 'SO-2026-019', 4 UNION ALL SELECT 'SO-2026-020', 5
) m ON m.order_code = o.`order_code`
WHERE o.`tenant_id` = 797403760988230 AND o.`is_deleted` = 0 AND o.`order_id` IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `ado_s8_order_flow_manufacturing_operator` x
                  WHERE x.`id` = 1329909400100 + (m.n - 1) * 3 + o.`sort_no`);

-- ---------------------------------------------------------------------------
-- 10) 总装发货 · 主门禁：baseline 5 + 订单级 15 = 20 行
--     baseline 语义 = 清洁 PPT 基线（planned=actual、variance=0、全 green）→ 原样复制
-- ---------------------------------------------------------------------------
INSERT INTO `ado_s8_order_flow_final_assembly_gate`
  (`id`,`order_id`,`order_code`,`gate_code`,`gate_name`,`planned_offset_days`,`actual_offset_days`,
   `variance_days`,`status`,`impacted_order_count`,`risk_order_count`,`top_risk_type`,`top_risk_label`,
   `owner_side_text`,`sort_no`,`scenario_code`,`data_source`,`tenant_id`,`factory_id`,
   `created_at`,`updated_at`,`is_deleted`)
SELECT 1329909410000 + g.rn, NULL, NULL,
       g.`gate_code`, g.`gate_name`, g.`planned_offset_days`, g.`actual_offset_days`,
       g.`variance_days`, g.`status`, g.`impacted_order_count`, g.`risk_order_count`,
       g.`top_risk_type`, g.`top_risk_label`, g.`owner_side_text`, g.`sort_no`,
       g.`scenario_code`, 'SEED', 838257186181189, 838257186320453, '2026-08-27 00:00:00', NULL, 0
FROM (
  SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.`sort_no`) AS rn
  FROM `ado_s8_order_flow_final_assembly_gate` t
  WHERE t.`tenant_id` = 797403760988230 AND t.`is_deleted` = 0 AND t.`order_id` IS NULL
) g
WHERE NOT EXISTS (SELECT 1 FROM `ado_s8_order_flow_final_assembly_gate` x
                  WHERE x.`id` = 1329909410000 + g.rn);

INSERT INTO `ado_s8_order_flow_final_assembly_gate`
  (`id`,`order_id`,`order_code`,`gate_code`,`gate_name`,`planned_offset_days`,`actual_offset_days`,
   `variance_days`,`status`,`impacted_order_count`,`risk_order_count`,`top_risk_type`,`top_risk_label`,
   `owner_side_text`,`sort_no`,`scenario_code`,`data_source`,`tenant_id`,`factory_id`,
   `created_at`,`updated_at`,`is_deleted`)
SELECT 1329909410100 + (m.n - 1) * 5 + (g.`sort_no` / 10),
       1329909300000 + m.n, g.`order_code`,
       g.`gate_code`, g.`gate_name`, g.`planned_offset_days`, g.`actual_offset_days`,
       g.`variance_days`, g.`status`, g.`impacted_order_count`, g.`risk_order_count`,
       g.`top_risk_type`, g.`top_risk_label`, g.`owner_side_text`, g.`sort_no`,
       g.`scenario_code`, 'SEED', 838257186181189, 838257186320453, '2026-08-27 00:00:00', NULL, 0
FROM `ado_s8_order_flow_final_assembly_gate` g
JOIN (
  SELECT 'SO-2026-001' AS order_code, 1 AS n UNION ALL SELECT 'SO-2026-002', 2 UNION ALL
  SELECT 'SO-2026-012', 3 UNION ALL SELECT 'SO-2026-019', 4 UNION ALL SELECT 'SO-2026-020', 5
) m ON m.order_code = g.`order_code`
WHERE g.`tenant_id` = 797403760988230 AND g.`is_deleted` = 0 AND g.`order_id` IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `ado_s8_order_flow_final_assembly_gate` x
                  WHERE x.`id` = 1329909410100 + (m.n - 1) * 5 + (g.`sort_no` / 10));

-- ---------------------------------------------------------------------------
-- 11) 总装发货 · 并行准备：baseline 4 + 订单级 12 = 16 行
-- ---------------------------------------------------------------------------
INSERT INTO `ado_s8_order_flow_final_assembly_parallel`
  (`id`,`order_id`,`order_code`,`prep_code`,`prep_name`,`kitting_rate`,`status`,
   `impacted_order_count`,`risk_order_count`,`top_risk_type`,`top_risk_label`,`owner_side_text`,`sort_no`,
   `scenario_code`,`data_source`,`tenant_id`,`factory_id`,`created_at`,`updated_at`,`is_deleted`)
SELECT 1329909420000 + p.rn, NULL, NULL,
       p.`prep_code`, p.`prep_name`, p.`kitting_rate`, p.`status`,
       p.`impacted_order_count`, p.`risk_order_count`, p.`top_risk_type`, p.`top_risk_label`,
       p.`owner_side_text`, p.`sort_no`,
       p.`scenario_code`, 'SEED', 838257186181189, 838257186320453, '2026-08-27 00:00:00', NULL, 0
FROM (
  SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.`sort_no`) AS rn
  FROM `ado_s8_order_flow_final_assembly_parallel` t
  WHERE t.`tenant_id` = 797403760988230 AND t.`is_deleted` = 0 AND t.`order_id` IS NULL
) p
WHERE NOT EXISTS (SELECT 1 FROM `ado_s8_order_flow_final_assembly_parallel` x
                  WHERE x.`id` = 1329909420000 + p.rn);

INSERT INTO `ado_s8_order_flow_final_assembly_parallel`
  (`id`,`order_id`,`order_code`,`prep_code`,`prep_name`,`kitting_rate`,`status`,
   `impacted_order_count`,`risk_order_count`,`top_risk_type`,`top_risk_label`,`owner_side_text`,`sort_no`,
   `scenario_code`,`data_source`,`tenant_id`,`factory_id`,`created_at`,`updated_at`,`is_deleted`)
SELECT 1329909420100 + (m.n - 1) * 4 + (p.`sort_no` / 10),
       1329909300000 + m.n, p.`order_code`,
       p.`prep_code`, p.`prep_name`, p.`kitting_rate`, p.`status`,
       p.`impacted_order_count`, p.`risk_order_count`, p.`top_risk_type`, p.`top_risk_label`,
       p.`owner_side_text`, p.`sort_no`,
       p.`scenario_code`, 'SEED', 838257186181189, 838257186320453, '2026-08-27 00:00:00', NULL, 0
FROM `ado_s8_order_flow_final_assembly_parallel` p
JOIN (
  SELECT 'SO-2026-001' AS order_code, 1 AS n UNION ALL SELECT 'SO-2026-002', 2 UNION ALL
  SELECT 'SO-2026-012', 3 UNION ALL SELECT 'SO-2026-019', 4 UNION ALL SELECT 'SO-2026-020', 5
) m ON m.order_code = p.`order_code`
WHERE p.`tenant_id` = 797403760988230 AND p.`is_deleted` = 0 AND p.`order_id` IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM `ado_s8_order_flow_final_assembly_parallel` x
                  WHERE x.`id` = 1329909420100 + (m.n - 1) * 4 + (p.`sort_no` / 10));

-- =====================================================================================
-- 12) SO-2026-012 对照单重算：采购延期 +2 天 → 本体生产追回 2 天 → 总装追回 1 天
--     stage 实际 5/3/16/4/2 = 30（planned 5/3/14/6/3 = 31 = target_cycle_days，不变）
--     全部为对固定值的 UPDATE，天然幂等；只作用于 UAT scope，源租户不受影响。
-- =====================================================================================

-- 12.1 阶段：BODY_PRODUCTION 6→4，FINAL_ASSEMBLY 3→2，同步 variance 链与实际日期
UPDATE `ado_s8_order_flow_stage`
SET `actual_days` = 4.0, `node_variance_days` = -2.0, `cumulative_variance_days` = 0.0,
    `actual_start_at` = '2026-08-13 09:00:00', `actual_end_at` = '2026-08-17 09:00:00'
WHERE `tenant_id` = 838257186181189 AND `factory_id` = 838257186320453
  AND `order_id` = 1329909300003 AND `order_flow_code` = 'BODY_PRODUCTION';

UPDATE `ado_s8_order_flow_stage`
SET `actual_days` = 2.0, `node_variance_days` = -1.0, `cumulative_variance_days` = -1.0,
    `actual_start_at` = '2026-08-17 09:00:00', `actual_end_at` = '2026-08-19 09:00:00'
WHERE `tenant_id` = 838257186181189 AND `factory_id` = 838257186320453
  AND `order_id` = 1329909300003 AND `order_flow_code` = 'FINAL_ASSEMBLY_DELIVERY';

-- 12.2 工序明细：按既有算法 actual[i] = round(baseline_actual[i] × A / 6.8, 3)，A=4.0；
--      末位守恒尾差修正；plan_qty = round(baseline_plan × A/6.8)；
--      cycle_status = ≤pi green / ≤pi×1.2 yellow / 否则 red（achievement_* 为工序固定属性，不随 A 变）
UPDATE `ado_s8_order_flow_manufacturing_process`
SET `actual_days` = 1.412, `plan_qty` = 26, `cycle_status` = 'red'
WHERE `tenant_id` = 838257186181189 AND `order_id` = 1329909300003 AND `process_code` = 'P10';
UPDATE `ado_s8_order_flow_manufacturing_process`
SET `actual_days` = 0.471, `plan_qty` = 2, `cycle_status` = 'green'
WHERE `tenant_id` = 838257186181189 AND `order_id` = 1329909300003 AND `process_code` = 'P20';
UPDATE `ado_s8_order_flow_manufacturing_process`
SET `actual_days` = 1.765, `plan_qty` = 9, `cycle_status` = 'green'
WHERE `tenant_id` = 838257186181189 AND `order_id` = 1329909300003 AND `process_code` = 'P30';
UPDATE `ado_s8_order_flow_manufacturing_process`
SET `actual_days` = 0.352, `plan_qty` = 1, `cycle_status` = 'green'
WHERE `tenant_id` = 838257186181189 AND `order_id` = 1329909300003 AND `process_code` = 'P40';
UPDATE `ado_s8_order_flow_manufacturing_process`
SET `actual_days` = 4.000, `cycle_status` = 'green'
WHERE `tenant_id` = 838257186181189 AND `order_id` = 1329909300003 AND `process_code` = 'TOTAL';

-- 12.3 损失因素：count = round(baseline_count × A/6.8)，loss_hours = round(baseline_hours × A/6.8, 2)
UPDATE `ado_s8_order_flow_manufacturing_loss_factor`
SET `count_value` = 5, `loss_hours` = 5.29
WHERE `tenant_id` = 838257186181189 AND `order_id` = 1329909300003 AND `factor_code` = 'MATERIAL';
UPDATE `ado_s8_order_flow_manufacturing_loss_factor`
SET `count_value` = 4, `loss_hours` = 2.65
WHERE `tenant_id` = 838257186181189 AND `order_id` = 1329909300003 AND `factor_code` = 'EQUIPMENT';
UPDATE `ado_s8_order_flow_manufacturing_loss_factor`
SET `count_value` = 2, `loss_hours` = 2.00
WHERE `tenant_id` = 838257186181189 AND `order_id` = 1329909300003 AND `factor_code` = 'QUALITY';
UPDATE `ado_s8_order_flow_manufacturing_loss_factor`
SET `count_value` = 7, `loss_hours` = 5.44
WHERE `tenant_id` = 838257186181189 AND `order_id` = 1329909300003 AND `factor_code` = 'EFFICIENCY';
UPDATE `ado_s8_order_flow_manufacturing_loss_factor`
SET `count_value` = 18, `loss_hours` = 4.41
WHERE `tenant_id` = 838257186181189 AND `order_id` = 1329909300003 AND `factor_code` = 'SUBTOTAL';

-- 12.4 操作员表现：avg_hours = round(baseline_hours × A/6.8, 2)（status 为操作员固定属性）
UPDATE `ado_s8_order_flow_manufacturing_operator`
SET `avg_hours` = 11.76
WHERE `tenant_id` = 838257186181189 AND `order_id` = 1329909300003 AND `operator_code` = 'OP_ZHANG';
UPDATE `ado_s8_order_flow_manufacturing_operator`
SET `avg_hours` = 5.88
WHERE `tenant_id` = 838257186181189 AND `order_id` = 1329909300003 AND `operator_code` = 'OP_LI';
UPDATE `ado_s8_order_flow_manufacturing_operator`
SET `avg_hours` = 4.71
WHERE `tenant_id` = 838257186181189 AND `order_id` = 1329909300003 AND `operator_code` = 'OP_WANG';

-- 12.5 总装门禁：scale = stage.actual_days / 3.0 = 2/3；末位守恒修正 = stage.actual_days
--      variance = actual - planned（全为负 = 提前，status green）
UPDATE `ado_s8_order_flow_final_assembly_gate`
SET `actual_offset_days` = 0.400, `variance_days` = -0.200, `status` = 'green',
    `impacted_order_count` = 0, `risk_order_count` = 0, `top_risk_type` = NULL, `top_risk_label` = NULL
WHERE `tenant_id` = 838257186181189 AND `order_id` = 1329909300003 AND `gate_code` = 'ASSEMBLY_COMPLETION';
UPDATE `ado_s8_order_flow_final_assembly_gate`
SET `actual_offset_days` = 0.800, `variance_days` = -0.400, `status` = 'green',
    `impacted_order_count` = 0, `risk_order_count` = 0, `top_risk_type` = NULL, `top_risk_label` = NULL
WHERE `tenant_id` = 838257186181189 AND `order_id` = 1329909300003 AND `gate_code` = 'QUALITY_RELEASE';
UPDATE `ado_s8_order_flow_final_assembly_gate`
SET `actual_offset_days` = 1.200, `variance_days` = -0.600, `status` = 'green',
    `impacted_order_count` = 0, `risk_order_count` = 0, `top_risk_type` = NULL, `top_risk_label` = NULL
WHERE `tenant_id` = 838257186181189 AND `order_id` = 1329909300003 AND `gate_code` = 'PACKAGING_COMPLETION';
UPDATE `ado_s8_order_flow_final_assembly_gate`
SET `actual_offset_days` = 1.600, `variance_days` = -0.800, `status` = 'green',
    `impacted_order_count` = 0, `risk_order_count` = 0, `top_risk_type` = NULL, `top_risk_label` = NULL
WHERE `tenant_id` = 838257186181189 AND `order_id` = 1329909300003 AND `gate_code` = 'GOODS_HANDOVER';
UPDATE `ado_s8_order_flow_final_assembly_gate`
SET `actual_offset_days` = 2.000, `variance_days` = -1.000, `status` = 'green',
    `impacted_order_count` = 0, `risk_order_count` = 0, `top_risk_type` = NULL, `top_risk_label` = NULL
WHERE `tenant_id` = 838257186181189 AND `order_id` = 1329909300003 AND `gate_code` = 'SHIPMENT_CONFIRMATION';

-- 12.6 SO-2026-001 源缺陷修正：MATERIAL_PURCHASE 标注 actual_days=13，
--      但其自身日期跨度（+96 后 2026-07-30 → 2026-08-11）实为 12 天，
--      且该单其余 4 阶段的天数与日期跨度均自洽、5 阶段日期跨度合计 30 = actual_cycle_days=30。
--      故 13 为源模板笔误；在 UAT 副本内校正为 12，使 stage 合计 = 订单周期（源租户不改）。
--      连带修正 variance 链：nv = 12-14 = -2.0；cv = 2.0 + (-2.0) = 0.0（由 red 阶段回正）。
UPDATE `ado_s8_order_flow_stage`
SET `actual_days` = 12.0, `node_variance_days` = -2.0, `cumulative_variance_days` = 0.0, `status` = 'green'
WHERE `tenant_id` = 838257186181189 AND `factory_id` = 838257186320453
  AND `order_id` = 1329909300001 AND `order_flow_code` = 'MATERIAL_PURCHASE';

UPDATE `ado_s8_order_flow_stage`
SET `cumulative_variance_days` = -1.0
WHERE `tenant_id` = 838257186181189 AND `factory_id` = 838257186320453
  AND `order_id` = 1329909300001 AND `order_flow_code` = 'BODY_PRODUCTION';

UPDATE `ado_s8_order_flow_stage`
SET `cumulative_variance_days` = -1.0
WHERE `tenant_id` = 838257186181189 AND `factory_id` = 838257186320453
  AND `order_id` = 1329909300001 AND `order_flow_code` = 'FINAL_ASSEMBLY_DELIVERY';

-- =====================================================================================
-- 13) 链路聚合快照：按 Golden Roster 实算（D2-A），不复制 PPT 的 total_orders=105
--     total_orders / total_customers / 三项 avg / 5 阶段 JSON 全部由 UAT 数据聚合得出
-- =====================================================================================
INSERT INTO `ado_s8_order_flow_snapshot`
  (`id`,`tenant_id`,`factory_id`,`snapshot_code`,`scope_code`,`total_orders`,`total_customers`,
   `avg_response_minutes`,`avg_processing_minutes`,`avg_loss_minutes`,`stage_snapshots_json`,
   `scenario_code`,`data_source`,`created_at`,`updated_at`,`is_deleted`)
SELECT 1329909370001, 838257186181189, 838257186320453,
       'CHAIN_AGGREGATE_BASELINE', 'BASELINE_PPT',
       agg.total_orders, agg.total_customers,
       agg.avg_r, agg.avg_p, agg.avg_l,
       js.stage_json,
       'DEMO', 'SEED', '2026-08-27 00:00:00', NULL, 0
FROM (
  SELECT COUNT(*) AS total_orders,
         COUNT(DISTINCT `customer_code`) AS total_customers,
         ROUND(AVG(`response_minutes`), 2)   AS avg_r,
         ROUND(AVG(`processing_minutes`), 2) AS avg_p,
         ROUND(AVG(`total_loss_minutes`), 2) AS avg_l
  FROM `ado_s8_order_flow_order`
  WHERE `tenant_id` = 838257186181189 AND `factory_id` = 838257186320453 AND `is_deleted` = 0
) agg
CROSS JOIN (
  SELECT CAST(CONCAT('[', GROUP_CONCAT(j ORDER BY sn SEPARATOR ','), ']') AS CHAR) AS stage_json
  FROM (
    SELECT MIN(st.`sort_no`) AS sn,
           JSON_OBJECT(
             'orderFlowCode',  st.`order_flow_code`,
             'orderFlowName',  MIN(st.`order_flow_name`),
             'kpiAvgDays',     CAST(ROUND(AVG(st.`planned_days`), 1) AS DECIMAL(10,1)),
             'actualAvgDays',  CAST(ROUND(AVG(st.`actual_days`), 1) AS DECIMAL(10,1)),
             'onTimeRate',     CAST(ROUND(100 * SUM(st.`actual_days` IS NOT NULL AND st.`actual_days` <= st.`planned_days`)
                                          / NULLIF(SUM(st.`actual_days` IS NOT NULL), 0), 0) AS SIGNED),
             'green',          CAST(SUM(st.`status` = 'green')   AS SIGNED),
             'yellow',         CAST(SUM(st.`status` = 'yellow')  AS SIGNED),
             'red',            CAST(SUM(st.`status` = 'red')     AS SIGNED),
             'pending',        CAST(SUM(st.`status` = 'pending') AS SIGNED)
           ) AS j
    FROM `ado_s8_order_flow_stage` st
    WHERE st.`tenant_id` = 838257186181189 AND st.`factory_id` = 838257186320453 AND st.`is_deleted` = 0
    GROUP BY st.`order_flow_code`
  ) t
) js
WHERE NOT EXISTS (SELECT 1 FROM `ado_s8_order_flow_snapshot` x WHERE x.`id` = 1329909370001);