-- AIDOP-S6-L1-KPI-CONTRACT-RESOLUTION-1 Phase 1（方案 B：恢复 L1=订单 / L2=工单 分层）。
-- 提交时 rename 为正式 1.0.<next>.sql 并注册 csproj Copy（不预占版本号）。
-- 表列：ado_smart_ops_kpi_calc_config 为 PascalCase（EnableUnderLine=false）；kpi_master 亦 PascalCase；
--       ado_s9_kpi_value_l1_day / ado_smart_ops_kpi_atomic_day 为 snake（显式 SQL 建）。
-- 幂等：配置 INSERT ... WHERE NOT EXISTS；数据修正为条件 UPDATE/DELETE（可重复执行）。
-- 租户：工单 L2 KPI 计算落 AIDOP 运行租户 797403760988229。

-- ═════════ 1) 工单 L2 KPI 迁 CONFIG_SQL（S6_L2_002 满足率 / S6_L2_003 人效）═════════
-- 读已准备好的 dwd（数据准备由 S6MdpSyncTransformService 保留），与 legacy 逐项等价。

-- S6_L2_002 工单制造满足率（日；dwd 存 0~1 逐单 rate → ×100）
INSERT INTO ado_smart_ops_kpi_calc_config
  (`TenantId`,`MetricCode`,`ModuleCode`,`VersionNo`,`CalcEngineType`,`DataSourceCode`,`SqlScript`,`TimeoutSeconds`,`PublishStatus`,`IsCurrent`,`Remark`,`CreatedBy`,`CreatedAt`)
SELECT 797403760988229,'S6_L2_002','S6',1,'LEGACY_CODE',NULL,NULL,30,'PUBLISHED',0,'legacy 回滚基线：均值-of-逐单(报工/计划)×100','system',NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ado_smart_ops_kpi_calc_config WHERE `MetricCode`='S6_L2_002' AND `TenantId`=797403760988229 AND `VersionNo`=1);

INSERT INTO ado_smart_ops_kpi_calc_config
  (`TenantId`,`MetricCode`,`ModuleCode`,`VersionNo`,`CalcEngineType`,`DataSourceCode`,`SqlScript`,`TimeoutSeconds`,`PublishStatus`,`IsCurrent`,`Remark`,`CreatedBy`,`CreatedAt`)
SELECT 797403760988229,'S6_L2_002','S6',2,'CONFIG_SQL','MDP_LOCAL',
'SELECT ROUND(AVG(fulfillment_rate) * 100, 4) AS metric_value, COUNT(*) AS numerator_value
FROM dwd_t8_work_order_mfg_fulfillment
WHERE tenant_id = @tenant_id AND factory_id = @factory_id AND biz_date = @biz_date AND source_ztid = @ztid
  AND fulfillment_rate IS NOT NULL',
30,'PUBLISHED',1,'CONFIG_SQL：读 dwd 逐单 fulfillment_rate(0~1) 均值×100，1:1 复刻 legacy','system',NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ado_smart_ops_kpi_calc_config WHERE `MetricCode`='S6_L2_002' AND `TenantId`=797403760988229 AND `VersionNo`=2);

-- S6_L2_003 工单制造人效（月；完成工单数/生产人数，headcount 空/0→NO_DATA 保留 NO_HEADCOUNT）
INSERT INTO ado_smart_ops_kpi_calc_config
  (`TenantId`,`MetricCode`,`ModuleCode`,`VersionNo`,`CalcEngineType`,`DataSourceCode`,`SqlScript`,`TimeoutSeconds`,`PublishStatus`,`IsCurrent`,`Remark`,`CreatedBy`,`CreatedAt`)
SELECT 797403760988229,'S6_L2_003','S6',1,'LEGACY_CODE',NULL,NULL,30,'PUBLISHED',0,'legacy 回滚基线：完成工单数/生产人数','system',NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ado_smart_ops_kpi_calc_config WHERE `MetricCode`='S6_L2_003' AND `TenantId`=797403760988229 AND `VersionNo`=1);

INSERT INTO ado_smart_ops_kpi_calc_config
  (`TenantId`,`MetricCode`,`ModuleCode`,`VersionNo`,`CalcEngineType`,`DataSourceCode`,`SqlScript`,`TimeoutSeconds`,`PublishStatus`,`IsCurrent`,`Remark`,`CreatedBy`,`CreatedAt`)
SELECT 797403760988229,'S6_L2_003','S6',2,'CONFIG_SQL','MDP_LOCAL',
'SELECT ROUND(done_count / NULLIF(production_headcount, 0), 4) AS metric_value,
       done_count AS numerator_value, production_headcount AS denominator_value,
       denominator_status AS result_message
FROM dwd_t8_work_order_mfg_efficiency
WHERE tenant_id = @tenant_id AND factory_id = @factory_id
  AND biz_month = DATE_FORMAT(@biz_date, ''%Y-%m'') AND source_ztid = @ztid',
30,'PUBLISHED',1,'CONFIG_SQL：读 dwd 月行 done_count/生产人数，1:1 复刻 legacy','system',NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ado_smart_ops_kpi_calc_config WHERE `MetricCode`='S6_L2_003' AND `TenantId`=797403760988229 AND `VersionNo`=2);

-- ═════════ 2) 修正租户 0 的 S6_L1_001/002 元数据：工单(1.0.178所写)→订单 ═════════
-- 从既有订单口径租户 1300000000001 同码行拷贝，保证全租户 L1 统一订单口径。
UPDATE ado_smart_ops_kpi_master t0
JOIN ado_smart_ops_kpi_master src
  ON src.`MetricCode` = t0.`MetricCode` AND src.`TenantId` = 1300000000001
SET t0.`MetricName` = src.`MetricName`, t0.`Unit` = src.`Unit`, t0.`Direction` = src.`Direction`,
    t0.`Formula` = src.`Formula`, t0.`CalcRule` = src.`CalcRule`, t0.`Description` = src.`Description`,
    t0.`UpdatedAt` = NOW()
WHERE t0.`TenantId` = 0 AND t0.`MetricCode` IN ('S6_L1_001','S6_L1_002')
  AND t0.`MetricName` NOT LIKE '订单%';

-- ═════════ 3) 清理 atomic_day 中被误写的 S6 排程别名（配合代码删别名，#5/#6）═════════
-- 含 S6_L1_003（订单制造人效同属被 S2 排程别名污染，同一 bug）。
DELETE FROM ado_smart_ops_kpi_atomic_day
WHERE metric_code IN ('S6_L1_001','S6_L1_002','S6_L1_003');

-- ═════════ 4) 隔离 l1_day 的 S6_L1_001/002 历史工单值（误落位，#7/#8）═════════
-- is_deleted=1 隔离（保留行可追溯），看板读 is_deleted=0 → L1 呈现 NO_DATA；
-- 工单历史不搬（按决策 L2 从下次刷新起累积）。
UPDATE ado_s9_kpi_value_l1_day
SET is_deleted = 1, update_time = NOW()
WHERE module_code = 'S6' AND metric_code IN ('S6_L1_001','S6_L1_002') AND is_deleted = 0;