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

-- AIDOP-KPI-SQL-CONFIG-MIGRATION-EXISTING-1：其余已实现 KPI 的 SQL 配置化迁移种子。
-- 提交时 rename 为正式 1.0.<next>.sql 并注册 csproj Copy 条目（不预占版本号）。
-- 表列 PascalCase（EnableUnderLine=false）；租户=AIDOP 运行租户 797403760988229（S5/S6/S7 KPI 落库租户）。
-- 幂等：每条 INSERT ... WHERE NOT EXISTS（MetricCode+TenantId+VersionNo 唯一键）。
-- 引擎口径：
--   · 普通 KPI（S5_L1_002/003、S7_L1_001/002/003）：v1 LEGACY_CODE（回滚基线，IsCurrent=0）
--     + v2 CONFIG_SQL（迁移目标，IsCurrent=1，读已准备好的 dwd_*，与 legacy 逐项等价）。
--   · S5_L1_004：仅 v1 LEGACY_TVF（IsCurrent=1，登记 TVF 名/引擎；不建 CONFIG_SQL、不经只读执行器）。
-- CONFIG_SQL 结果契约：单行、metric_value 必需（NULL→NO_DATA）、可选 numerator/denominator/result_message。
-- 绑定参数由运行时注入：@tenant_id/@factory_id/@biz_date/@ztid（月度 KPI @biz_date=月末，用 DATE_FORMAT 取 YYYY-MM）。

-- ───────────────────────── S5_L1_002 物料上线满足率（日）─────────────────────────
INSERT INTO ado_smart_ops_kpi_calc_config
  (`TenantId`,`MetricCode`,`ModuleCode`,`VersionNo`,`CalcEngineType`,`DataSourceCode`,`SqlScript`,`TimeoutSeconds`,`PublishStatus`,`IsCurrent`,`Remark`,`CreatedBy`,`CreatedAt`)
SELECT 797403760988229,'S5_L1_002','S5',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`='S5_L1_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,'S5_L1_002','S5',2,'CONFIG_SQL','MDP_LOCAL',
'SELECT ROUND(AVG(fulfillment_rate) * 100, 4) AS metric_value, COUNT(*) AS numerator_value
FROM dwd_t8_material_online_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`='S5_L1_002' AND `TenantId`=797403760988229 AND `VersionNo`=2);

-- ───────────────────────── S5_L1_003 物料仓储人效（月）─────────────────────────
INSERT INTO ado_smart_ops_kpi_calc_config
  (`TenantId`,`MetricCode`,`ModuleCode`,`VersionNo`,`CalcEngineType`,`DataSourceCode`,`SqlScript`,`TimeoutSeconds`,`PublishStatus`,`IsCurrent`,`Remark`,`CreatedBy`,`CreatedAt`)
SELECT 797403760988229,'S5_L1_003','S5',1,'LEGACY_CODE',NULL,NULL,30,'PUBLISHED',0,'legacy 代码路径（回滚基线）：SUM(slzx)/仓管人数','system',NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ado_smart_ops_kpi_calc_config WHERE `MetricCode`='S5_L1_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,'S5_L1_003','S5',2,'CONFIG_SQL','MDP_LOCAL',
'SELECT ROUND(online_qty / NULLIF(warehouse_headcount, 0), 4) AS metric_value,
       online_qty AS numerator_value, warehouse_headcount AS denominator_value,
       denominator_status AS result_message
FROM dwd_t8_material_warehouse_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 月行 online_qty/仓管人数，headcount 空/0→NO_DATA(msg=NO_HEADCOUNT)','system',NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ado_smart_ops_kpi_calc_config WHERE `MetricCode`='S5_L1_003' AND `TenantId`=797403760988229 AND `VersionNo`=2);

-- ───────────────────────── S7_L1_001 订单发货周期（日）─────────────────────────
INSERT INTO ado_smart_ops_kpi_calc_config
  (`TenantId`,`MetricCode`,`ModuleCode`,`VersionNo`,`CalcEngineType`,`DataSourceCode`,`SqlScript`,`TimeoutSeconds`,`PublishStatus`,`IsCurrent`,`Remark`,`CreatedBy`,`CreatedAt`)
SELECT 797403760988229,'S7_L1_001','S7',1,'LEGACY_CODE',NULL,NULL,30,'PUBLISHED',0,'legacy 代码路径（回滚基线）：AVG(cycle_days)','system',NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ado_smart_ops_kpi_calc_config WHERE `MetricCode`='S7_L1_001' 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,'S7_L1_001','S7',2,'CONFIG_SQL','MDP_LOCAL',
'SELECT ROUND(AVG(cycle_days), 4) AS metric_value, COUNT(*) AS numerator_value
FROM dwd_t8_order_shipment_cycle
WHERE tenant_id = @tenant_id AND factory_id = @factory_id AND biz_date = @biz_date AND source_ztid = @ztid
  AND cycle_days IS NOT NULL',
30,'PUBLISHED',1,'CONFIG_SQL：读 dwd 逐单 cycle_days 均值（排除 null），1:1 复刻 legacy','system',NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ado_smart_ops_kpi_calc_config WHERE `MetricCode`='S7_L1_001' AND `TenantId`=797403760988229 AND `VersionNo`=2);

-- ───────────────────────── S7_L1_002 订单发货满足率（日）─────────────────────────
INSERT INTO ado_smart_ops_kpi_calc_config
  (`TenantId`,`MetricCode`,`ModuleCode`,`VersionNo`,`CalcEngineType`,`DataSourceCode`,`SqlScript`,`TimeoutSeconds`,`PublishStatus`,`IsCurrent`,`Remark`,`CreatedBy`,`CreatedAt`)
SELECT 797403760988229,'S7_L1_002','S7',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`='S7_L1_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,'S7_L1_002','S7',2,'CONFIG_SQL','MDP_LOCAL',
'SELECT ROUND(AVG(fulfillment_rate), 4) AS metric_value, COUNT(*) AS numerator_value
FROM dwd_t8_order_shipment_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(已×100) 均值，不再×100，1:1 复刻 legacy','system',NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ado_smart_ops_kpi_calc_config WHERE `MetricCode`='S7_L1_002' AND `TenantId`=797403760988229 AND `VersionNo`=2);

-- ───────────────────────── S7_L1_003 成品仓储人效（月）─────────────────────────
INSERT INTO ado_smart_ops_kpi_calc_config
  (`TenantId`,`MetricCode`,`ModuleCode`,`VersionNo`,`CalcEngineType`,`DataSourceCode`,`SqlScript`,`TimeoutSeconds`,`PublishStatus`,`IsCurrent`,`Remark`,`CreatedBy`,`CreatedAt`)
SELECT 797403760988229,'S7_L1_003','S7',1,'LEGACY_CODE',NULL,NULL,30,'PUBLISHED',0,'legacy 代码路径（回滚基线）：SUM(slzx)/仓管人数','system',NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ado_smart_ops_kpi_calc_config WHERE `MetricCode`='S7_L1_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,'S7_L1_003','S7',2,'CONFIG_SQL','MDP_LOCAL',
'SELECT ROUND(shipment_qty / NULLIF(warehouse_headcount, 0), 4) AS metric_value,
       shipment_qty AS numerator_value, warehouse_headcount AS denominator_value,
       denominator_status AS result_message
FROM dwd_t8_finished_warehouse_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 月行 shipment_qty/仓管人数，headcount 空/0→NO_DATA(msg=NO_HEADCOUNT)','system',NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ado_smart_ops_kpi_calc_config WHERE `MetricCode`='S7_L1_003' AND `TenantId`=797403760988229 AND `VersionNo`=2);

-- ───────────────────────── S5_L1_004 品类物料库存周转（保留 LEGACY_TVF）─────────────────────────
INSERT INTO ado_smart_ops_kpi_calc_config
  (`TenantId`,`MetricCode`,`ModuleCode`,`VersionNo`,`CalcEngineType`,`DataSourceCode`,`SqlScript`,`TimeoutSeconds`,`PublishStatus`,`IsCurrent`,`Remark`,`CreatedBy`,`CreatedAt`)
SELECT 797403760988229,'S5_L1_004','S5',1,'LEGACY_TVF','T8_TVF',NULL,60,'PUBLISHED',1,'保留 LEGACY_TVF：SQL Server TVF dbo.Rep_总账_存货_V3(@ztid,普通,正常,起期,止期)，D1/D2×30；不迁 CONFIG_SQL、不经只读执行器','system',NOW()
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM ado_smart_ops_kpi_calc_config WHERE `MetricCode`='S5_L1_004' AND `TenantId`=797403760988229 AND `VersionNo`=1);