AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/1.0.533.verify.sql

-- 1.0.533.verify.sql — 每条 SELECT 都必须返回 1
--
-- ⚠️ 本项目的 AutoVersionUpdate 按分号切分本文件，并把【每一个切片】都当断言、要求返回 1。
--    因此：① 不得放"仅供排查"的 informational 查询（它返回结果集不是 1）；
--          ② 文件必须以最后一条断言的分号结尾，**末尾不能留纯注释块** ——
--             尾部注释会被切成多出来的一条语句，同样导致迁移判失败、后端拒绝启动。
--    需要看状态分布请另跑独立只读查询，不要写进 verify。
--
-- 结构断言（①~⑤）迁移后即可全过；数据断言（⑥~⑰）在表为空时也成立（COUNT=0），
-- transform 跑过之后才真正有判别力。

-- ── ① 表存在 ────────────────────────────────────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`TABLES`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'dwd_product_design_kpi') = 1;

-- ── ② Unique Key 正确：必须是 (tenant_id, sales_order_id)，且 tenant_id 为首列 ──────
SELECT (SELECT GROUP_CONCAT(`COLUMN_NAME` ORDER BY `SEQ_IN_INDEX`)
          FROM `information_schema`.`STATISTICS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'dwd_product_design_kpi'
           AND `INDEX_NAME` = 'uk_dwd_product_design_kpi') = 'tenant_id,sales_order_id';

-- ── ③ tenant_id / sales_order_id 必须 NOT NULL ─────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`COLUMNS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'dwd_product_design_kpi'
           AND `COLUMN_NAME` IN ('tenant_id','sales_order_id')
           AND `IS_NULLABLE` = 'NO') = 2;

-- ── ④ actual_hours 类型正确（decimal，不能是整型/字符串）──────────────────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`COLUMNS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'dwd_product_design_kpi'
           AND `COLUMN_NAME` = 'actual_hours' AND `DATA_TYPE` = 'decimal') = 1;

-- ── ⑤ 本批不得引入 TargetHours / 红黄绿相关列 ──────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `information_schema`.`COLUMNS`
         WHERE `TABLE_SCHEMA` = DATABASE() AND `TABLE_NAME` = 'dwd_product_design_kpi'
           AND `COLUMN_NAME` IN ('target_hours','kpi_status','yellow_threshold',
                                 'red_threshold','achievement_rate')) = 0;

-- ── ⑥ 无非法租户 ───────────────────────────────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `dwd_product_design_kpi`
         WHERE `tenant_id` IS NULL OR `tenant_id` <= 0 OR `sales_order_id` <= 0) = 0;

-- ── ⑦ calculation_status 取值只允许 6 种 ───────────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `dwd_product_design_kpi`
         WHERE `calculation_status` NOT IN
           ('OK','NOT_STARTED','IN_PROGRESS','END_UNKNOWN','INVALID_NO_START','INVALID_TIME_SEQUENCE')) = 0;

-- ── ⑧ OK 行：start/end 都有值、end > start、actual_hours 有值且与时间差一致 ────────
SELECT (SELECT COUNT(*) FROM `dwd_product_design_kpi`
         WHERE `calculation_status` = 'OK'
           AND (`product_design_start_time` IS NULL
             OR `product_design_end_time` IS NULL
             OR `product_design_end_time` <= `product_design_start_time`
             OR `actual_hours` IS NULL
             OR ABS(`actual_hours` - ROUND(TIMESTAMPDIFF(SECOND,
                    `product_design_start_time`, `product_design_end_time`) / 3600.0, 2)) > 0.005)) = 0;

-- ── ⑨ END_UNKNOWN 行：end 必须 NULL（严禁用 update_time 伪造历史 End）─────────────
SELECT (SELECT COUNT(*) FROM `dwd_product_design_kpi`
         WHERE `calculation_status` = 'END_UNKNOWN' AND `product_design_end_time` IS NOT NULL) = 0;

-- ── ⑩ END_UNKNOWN 行：actual_hours 必须 NULL，且必须确有已下达日计划佐证 ───────────
SELECT (SELECT COUNT(*) FROM `dwd_product_design_kpi`
         WHERE `calculation_status` = 'END_UNKNOWN'
           AND (`actual_hours` IS NOT NULL
             OR `product_design_start_time` IS NULL
             OR `has_released_schedule` <> 1)) = 0;

-- ── ⑪ IN_PROGRESS 行：start 有值、end 为 NULL、且【无】已下达日计划佐证 ────────────
--     这一条是 END_UNKNOWN / IN_PROGRESS 不被混淆的结构性保证
SELECT (SELECT COUNT(*) FROM `dwd_product_design_kpi`
         WHERE `calculation_status` = 'IN_PROGRESS'
           AND (`product_design_start_time` IS NULL
             OR `product_design_end_time` IS NOT NULL
             OR `actual_hours` IS NOT NULL
             OR `has_released_schedule` <> 0)) = 0;

-- ── ⑫ NOT_STARTED 行：start / end / hours 全 NULL ──────────────────────────────────
SELECT (SELECT COUNT(*) FROM `dwd_product_design_kpi`
         WHERE `calculation_status` = 'NOT_STARTED'
           AND (`product_design_start_time` IS NOT NULL
             OR `product_design_end_time` IS NOT NULL
             OR `actual_hours` IS NOT NULL)) = 0;

-- ── ⑬ INVALID_* 行：一律不得给出 actual_hours ──────────────────────────────────────
SELECT (SELECT COUNT(*) FROM `dwd_product_design_kpi`
         WHERE `calculation_status` IN ('INVALID_NO_START','INVALID_TIME_SEQUENCE')
           AND `actual_hours` IS NOT NULL) = 0;

-- ── ⑭ Start 必须原样取自 Stage-1 DWD，不得自行重算 ─────────────────────────────────
SELECT (SELECT COUNT(*) FROM `dwd_product_design_kpi` p
          LEFT JOIN `dwd_order_review_kpi` k
            ON k.`tenant_id` = p.`tenant_id` AND k.`sales_order_id` = p.`sales_order_id`
         WHERE IFNULL(p.`product_design_start_time`, '1900-01-01')
            <> IFNULL(k.`review_end_time`, '1900-01-01')) = 0;

-- ── ⑮ End 必须等于该订单 FIRST SUCCESS 释放事件的 action_end_time（不是 LAST/MAX）──
SELECT (SELECT COUNT(*) FROM `dwd_product_design_kpi` p
         WHERE p.`product_design_end_time` IS NOT NULL
           AND p.`product_design_end_time` <> (
                 SELECT MIN(e.`action_end_time`) FROM `mdp_std_action_event` e
                  WHERE e.`tenant_id` = p.`tenant_id`
                    AND e.`biz_id` = p.`sales_order_id`
                    AND e.`action_code` = 'S2_DAILY_PLAN_RELEASE'
                    AND e.`biz_type` = 'crm_seorder'
                    AND e.`action_status` = 'SUCCESS')) = 0;

-- ── ⑯ Event Identity 一致：被采纳事件必须真属于同租户同订单 ────────────────────────
SELECT (SELECT COUNT(*) FROM `dwd_product_design_kpi` p
         WHERE p.`release_action_run_id` IS NOT NULL
           AND NOT EXISTS (
                 SELECT 1 FROM `mdp_std_action_event` e
                  WHERE e.`action_run_id` = p.`release_action_run_id`
                    AND e.`tenant_id` = p.`tenant_id`
                    AND e.`biz_id` = p.`sales_order_id`
                    AND e.`action_code` = 'S2_DAILY_PLAN_RELEASE'
                    AND e.`action_status` = 'SUCCESS')) = 0;

-- ── ⑰ 订单身份必须来自 mdp_std_so（MDP-only，不依赖任何源业务表）──────────────────
SELECT (SELECT COUNT(*) FROM `dwd_product_design_kpi` p
         WHERE NOT EXISTS (
                 SELECT 1 FROM `mdp_std_so` s
                  WHERE s.`tenant_id` = p.`tenant_id` AND s.`order_id` = p.`sales_order_id`)) = 0;