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

-- ──────────────────────────────────────────────────────────────────────
-- S0-CONTRACT-REVIEW-CYCLE-PI-CONFIG-SYNC-1
-- 1. 主表 S0ContractReviewCycle 现有 10 行 stage_code 从 CR1-CR5 → 语义编码
--    （opinion_review/feedback/second_review/leader_opinion/sign）。
--    并在 std_hours=0 时填初值（8/12/8/10/2），不覆盖用户已配置的非零值。
-- 2. 新建 S0ContractReviewCycleBreakdown 下钻配置表。
-- 3. 新建 S0ContractReviewCycleSyncFlag 工厂级同步开关表。
-- 4. seed 意见反馈下钻 6 行（3 组 × 2 现有工厂）。
--
-- 幂等：
--   - UPDATE WHERE 子句迁移后不匹配 → 第二次执行空操作；
--   - std_hours 附加 AND std_hours=0 守卫，保留用户后续编辑；
--   - CREATE TABLE IF NOT EXISTS；
--   - INSERT IGNORE 跳过 (factory, parent_stage, group) 唯一冲突。
-- ──────────────────────────────────────────────────────────────────────

-- 1a. PI 初值填入（仅当 std_hours=0 时；保留用户已配置的非零值）
UPDATE `S0ContractReviewCycle` SET `std_hours` = 8  WHERE `stage_code` = 'CR1' AND `std_hours` = 0;
UPDATE `S0ContractReviewCycle` SET `std_hours` = 12 WHERE `stage_code` = 'CR2' AND `std_hours` = 0;
UPDATE `S0ContractReviewCycle` SET `std_hours` = 8  WHERE `stage_code` = 'CR3' AND `std_hours` = 0;
UPDATE `S0ContractReviewCycle` SET `std_hours` = 10 WHERE `stage_code` = 'CR4' AND `std_hours` = 0;
UPDATE `S0ContractReviewCycle` SET `std_hours` = 2  WHERE `stage_code` = 'CR5' AND `std_hours` = 0;

-- 1b. stage_code 语义化迁移（无条件，迁移后再跑空操作）
UPDATE `S0ContractReviewCycle` SET `stage_code` = 'opinion_review' WHERE `stage_code` = 'CR1';
UPDATE `S0ContractReviewCycle` SET `stage_code` = 'feedback'       WHERE `stage_code` = 'CR2';
UPDATE `S0ContractReviewCycle` SET `stage_code` = 'second_review'  WHERE `stage_code` = 'CR3';
UPDATE `S0ContractReviewCycle` SET `stage_code` = 'leader_opinion' WHERE `stage_code` = 'CR4';
UPDATE `S0ContractReviewCycle` SET `stage_code` = 'sign'           WHERE `stage_code` = 'CR5';

-- 2a. 下钻配置表
CREATE TABLE IF NOT EXISTS `S0ContractReviewCycleBreakdown` (
  `rec_id`            BIGINT       NOT NULL AUTO_INCREMENT,
  `company_ref_id`    BIGINT       NOT NULL,
  `factory_ref_id`    BIGINT       NOT NULL,
  `domain_code`       VARCHAR(50)  NULL,
  `parent_stage_code` VARCHAR(50)  NOT NULL,
  `group_code`        VARCHAR(50)  NOT NULL,
  `group_name`        VARCHAR(200) NOT NULL,
  `std_hours`         DECIMAL(6,2) NOT NULL DEFAULT 0,
  `order_no`          INT          NOT NULL DEFAULT 1,
  `is_active`         TINYINT(1)   NOT NULL DEFAULT 1,
  `create_user`       VARCHAR(100) NULL,
  `create_time`       DATETIME     NOT NULL,
  `update_user`       VARCHAR(100) NULL,
  `update_time`       DATETIME     NULL,
  PRIMARY KEY (`rec_id`),
  UNIQUE KEY `uk_S0CRCBreakdown_factory_parent_group` (`factory_ref_id`, `parent_stage_code`, `group_code`),
  KEY `idx_S0CRCBreakdown_factory_parent` (`factory_ref_id`, `parent_stage_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='合同评审周期下钻配置（按 parent_stage_code 分组）';

-- 2b. 工厂级同步开关表
CREATE TABLE IF NOT EXISTS `S0ContractReviewCycleSyncFlag` (
  `rec_id`          BIGINT       NOT NULL AUTO_INCREMENT,
  `company_ref_id`  BIGINT       NOT NULL,
  `factory_ref_id`  BIGINT       NOT NULL,
  `domain_code`     VARCHAR(50)  NULL,
  `is_sync_enabled` TINYINT(1)   NOT NULL DEFAULT 0,
  `create_user`     VARCHAR(100) NULL,
  `create_time`     DATETIME     NOT NULL,
  `update_user`     VARCHAR(100) NULL,
  `update_time`     DATETIME     NULL,
  PRIMARY KEY (`rec_id`),
  UNIQUE KEY `uk_S0CRCSyncFlag_factory` (`factory_ref_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='合同评审周期同步开关（工厂级一行）';

-- 3. 意见反馈下钻 seed（3 组 × 2 现有工厂 = 6 行），INSERT IGNORE 跳过唯一冲突
INSERT IGNORE INTO `S0ContractReviewCycleBreakdown` (
  `company_ref_id`, `factory_ref_id`, `domain_code`, `parent_stage_code`,
  `group_code`, `group_name`, `std_hours`, `order_no`, `is_active`,
  `create_user`, `create_time`
) VALUES
  (1329900200001, 1329900200002, NULL, 'feedback', 'market',        '市场部',     4.80, 1, 1, 'system', NOW()),
  (1329900200001, 1329900200002, NULL, 'feedback', 'tech_presales', '技术售前组', 4.20, 2, 1, 'system', NOW()),
  (1329900200001, 1329900200002, NULL, 'feedback', 'planning',      '综合计划',   3.00, 3, 1, 'system', NOW()),
  (1329900200001, 1329900200003, NULL, 'feedback', 'market',        '市场部',     4.80, 1, 1, 'system', NOW()),
  (1329900200001, 1329900200003, NULL, 'feedback', 'tech_presales', '技术售前组', 4.20, 2, 1, 'system', NOW()),
  (1329900200001, 1329900200003, NULL, 'feedback', 'planning',      '综合计划',   3.00, 3, 1, 'system', NOW());