eduepos-group/docs/inspect-test-data.sql

-- 门店巡检与考核 测试数据（严格遵循 PRD 业务规则，仅使用实际表存在的列）
-- 运行库：eduepos。可重复执行（先清理同名/同期测试数据再写入）。
-- 规则引用：I02-R01 结束>开始；I02-R02/R03 至少1指标1门店；I02-R04 发布时开始不早于当前；
--          I03-R04 加权总分=Σ(得分×权重/100)；I03-R05 及格=总分≥及格线；
--          I04-R02 截止=提交+7天；I04-R03 复检不通过生成下一轮；
--          I05-R01 综合=巡检×0.6+自动×0.4；I05-R03 等级 优秀≥90/良好≥80/合格≥60/不合格<60。

SET NAMES utf8mb4;

-- ============ 0. 清理既有测试数据（幂等） ============
DELETE FROM bu_inspect_task_item
WHERE TASK_ID IN (SELECT TASK_ID FROM bu_inspect_task
                  WHERE PLAN_ID IN (SELECT PLAN_ID FROM bu_inspect_plan WHERE PLAN_NAME IN ('2026年8月食品安全常规巡检','暑期卫生专项巡检（草稿）')));
DELETE FROM bu_rectification
WHERE PLAN_ID IN (SELECT PLAN_ID FROM bu_inspect_plan WHERE PLAN_NAME IN ('2026年8月食品安全常规巡检','暑期卫生专项巡检（草稿）'));
DELETE FROM bu_inspect_task
WHERE PLAN_ID IN (SELECT PLAN_ID FROM bu_inspect_plan WHERE PLAN_NAME IN ('2026年8月食品安全常规巡检','暑期卫生专项巡检（草稿）'));
DELETE FROM bu_inspect_plan_store
WHERE PLAN_ID IN (SELECT PLAN_ID FROM bu_inspect_plan WHERE PLAN_NAME IN ('2026年8月食品安全常规巡检','暑期卫生专项巡检（草稿）'));
DELETE FROM bu_inspect_plan_indicator
WHERE PLAN_ID IN (SELECT PLAN_ID FROM bu_inspect_plan WHERE PLAN_NAME IN ('2026年8月食品安全常规巡检','暑期卫生专项巡检（草稿）'));
DELETE FROM bu_inspect_plan WHERE PLAN_NAME IN ('2026年8月食品安全常规巡检','暑期卫生专项巡检（草稿）');
DELETE FROM bu_assess_result WHERE PERIOD_CODE = '2026-08';

-- ============ 1. 已发布计划：2026年8月食品安全常规巡检 ============
-- 常规巡检(1)、每周(2)、08-01~09-30、下次执行08-22、3家门店、已发布(1)
INSERT INTO bu_inspect_plan
  (PLAN_NAME, PLAN_TYPE, CYCLE_TYPE, START_DATE, END_DATE, NEXT_EXECUTE_DATE,
   INSPECTOR_ID, INSPECTOR_NAME, AUTO_COLLECT_FLAG, STORE_COUNT, STATUS,
   PUBLISH_DATE, FINISH_DATE, RMK, CREATE_DATE)
VALUES
  ('2026年8月食品安全常规巡检', 1, 2, '2026-08-01', '2026-09-30', '2026-08-22',
   1, '管理员', 1, 3, 1,
   '2026-07-31 09:00:00', NULL, '每周常规巡检，覆盖7项考核指标、3家门店', NOW());
SET @plan1 = LAST_INSERT_ID();

-- ============ 2. 草稿计划：暑期卫生专项巡检（未发布，权重未达100%符合草稿状态） ============
INSERT INTO bu_inspect_plan
  (PLAN_NAME, PLAN_TYPE, CYCLE_TYPE, START_DATE, END_DATE, NEXT_EXECUTE_DATE,
   INSPECTOR_ID, INSPECTOR_NAME, AUTO_COLLECT_FLAG, STORE_COUNT, STATUS,
   PUBLISH_DATE, FINISH_DATE, RMK, CREATE_DATE)
VALUES
  ('暑期卫生专项巡检（草稿）', 2, 3, '2026-08-15', '2026-09-15', NULL,
   1, '管理员', 1, 2, 0,
   NULL, NULL, '草稿计划：仅勾选人工巡检指标，权重合计30%（未达发布要求100%）', NOW());
SET @plan2 = LAST_INSERT_ID();

-- ============ 3. 计划关联指标 ============
-- I02-R02：至少1个指标。计划1关联全部7项（权重和=100，满足发布校验）；计划2仅3项人工指标
INSERT INTO bu_inspect_plan_indicator
  (PLAN_ID, INDICATOR_ID, INDICATOR_NAME, INDICATOR_TYPE, MAX_SCORE, PASS_SCORE, WEIGHT, SCORE_RULE, SORT, STATUS, CREATE_DATE)
SELECT @plan1, INDICATOR_ID, INDICATOR_NAME, INDICATOR_TYPE, MAX_SCORE, PASS_SCORE, WEIGHT, SCORE_RULE, SORT, 1, NOW()
FROM bu_inspect_indicator WHERE STATUS = 1;

INSERT INTO bu_inspect_plan_indicator
  (PLAN_ID, INDICATOR_ID, INDICATOR_NAME, INDICATOR_TYPE, MAX_SCORE, PASS_SCORE, WEIGHT, SCORE_RULE, SORT, STATUS, CREATE_DATE)
SELECT @plan2, INDICATOR_ID, INDICATOR_NAME, INDICATOR_TYPE, MAX_SCORE, PASS_SCORE, WEIGHT, SCORE_RULE, SORT, 1, NOW()
FROM bu_inspect_indicator WHERE INDICATOR_CODE IN ('HYGIENE','SERVICE','PACKAGING');

-- ============ 4. 计划关联门店（I02-R03：至少1家门店） ============
INSERT INTO bu_inspect_plan_store (PLAN_ID, STORE_ID, STORE_NAME, STORE_BELONGTO, PROVINCE, CITY, STATUS, CREATE_DATE)
SELECT @plan1, STORE_ID, STORE_NAME, STORE_BELONGTO, PROVINCE, CITY, 1, NOW()
FROM bu_store WHERE STORE_ID IN (1,2,3);

INSERT INTO bu_inspect_plan_store (PLAN_ID, STORE_ID, STORE_NAME, STORE_BELONGTO, PROVINCE, CITY, STATUS, CREATE_DATE)
SELECT @plan2, STORE_ID, STORE_NAME, STORE_BELONGTO, PROVINCE, CITY, 1, NOW()
FROM bu_store WHERE STORE_ID IN (1,2);

-- ============ 5. 生成巡检任务（I03-R01：每个门店每个执行日期一条任务） ============
-- 任务日期 2026-08-01（计划首个执行日）
-- T1 门店1 特色美食店666：已提交、不及格（卫生/服务 2项不达标）
INSERT INTO bu_inspect_task
  (PLAN_ID, PLAN_NAME, STORE_ID, STORE_NAME, TASK_DATE, INSPECTOR_ID, INSPECTOR_NAME,
   TOTAL_SCORE, MAX_SCORE, PASS_SCORE, PASS_FLAG, UNPASS_ITEM_COUNT,
   START_DATE, SUBMIT_DATE, CANCEL_DATE, STATUS, RMK, CREATE_DATE)
VALUES
  (@plan1, '2026年8月食品安全常规巡检', 1, '特色美食店666', '2026-08-01', 1, '管理员',
   72.25, 100.00, 60.00, 0, 2,
   '2026-08-01 09:30:00', '2026-08-01 11:00:00', NULL, 3, '卫生、服务未达标，已自动生成整改', NOW());
SET @t1 = LAST_INSERT_ID();

-- T2 门店2 水饺店铺：已提交、不及格（卫生/服务/包装 3项不达标）
INSERT INTO bu_inspect_task
  (PLAN_ID, PLAN_NAME, STORE_ID, STORE_NAME, TASK_DATE, INSPECTOR_ID, INSPECTOR_NAME,
   TOTAL_SCORE, MAX_SCORE, PASS_SCORE, PASS_FLAG, UNPASS_ITEM_COUNT,
   START_DATE, SUBMIT_DATE, CANCEL_DATE, STATUS, RMK, CREATE_DATE)
VALUES
  (@plan1, '2026年8月食品安全常规巡检', 2, '水饺店铺', '2026-08-01', 1, '管理员',
   70.00, 100.00, 60.00, 0, 3,
   '2026-08-01 09:40:00', '2026-08-01 11:20:00', NULL, 3, '卫生、服务、包装未达标，已自动生成整改', NOW());
SET @t2 = LAST_INSERT_ID();

-- T3 门店3 烤肉拌饭：已提交、及格（全部达标）
INSERT INTO bu_inspect_task
  (PLAN_ID, PLAN_NAME, STORE_ID, STORE_NAME, TASK_DATE, INSPECTOR_ID, INSPECTOR_NAME,
   TOTAL_SCORE, MAX_SCORE, PASS_SCORE, PASS_FLAG, UNPASS_ITEM_COUNT,
   START_DATE, SUBMIT_DATE, CANCEL_DATE, STATUS, RMK, CREATE_DATE)
VALUES
  (@plan1, '2026年8月食品安全常规巡检', 3, '烤肉拌饭', '2026-08-01', 1, '管理员',
   81.25, 100.00, 60.00, 1, 0,
   '2026-08-01 10:00:00', '2026-08-01 11:40:00', NULL, 3, '全部指标达标', NOW());
SET @t3 = LAST_INSERT_ID();

-- ============ 6. 任务评分明细（I03-R02 自动采集预填；I03-R03 人工打分；I03-R04 加权） ============
-- T1 门店1：自动项全80分；人工 卫生50(不达标) 服务45(不达标) 包装85
INSERT INTO bu_inspect_task_item
  (TASK_ID, PLAN_ID, STORE_ID, PLAN_INDICATOR_ID, INDICATOR_ID, SCORE_LEVEL,
   INDICATOR_CODE, INDICATOR_NAME, INDICATOR_TYPE, DATA_SOURCE,
   WEIGHT, MAX_SCORE, PASS_SCORE, RAW_VALUE, RAW_VALUE_TEXT, SCORE, WEIGHTED_SCORE, PASS_FLAG,
   PICTURE_URLS, RMK, SORT, STATUS, CREATE_DATE)
SELECT
  @t1, @plan1, 1, pi.PLAN_INDICATOR_ID, pi.INDICATOR_ID, 2,
  i.INDICATOR_CODE, i.INDICATOR_NAME, i.INDICATOR_TYPE, i.DATA_SOURCE,
  pi.WEIGHT, pi.MAX_SCORE, pi.PASS_SCORE,
  CASE i.INDICATOR_CODE WHEN 'COOK_TIME' THEN 15 WHEN 'GOOD_RATE' THEN 92 WHEN 'REJECT_RATE' THEN 1.2 WHEN 'OVERTIME_RATE' THEN 2.5 END,
  CASE i.INDICATOR_CODE WHEN 'COOK_TIME' THEN '15分钟' WHEN 'GOOD_RATE' THEN '92%' WHEN 'REJECT_RATE' THEN '1.2%' WHEN 'OVERTIME_RATE' THEN '2.5%' END,
  CASE i.INDICATOR_CODE WHEN 'COOK_TIME' THEN 80 WHEN 'GOOD_RATE' THEN 80 WHEN 'REJECT_RATE' THEN 80 WHEN 'OVERTIME_RATE' THEN 80
                        WHEN 'HYGIENE' THEN 50 WHEN 'SERVICE' THEN 45 WHEN 'PACKAGING' THEN 85 END,
  ROUND(CASE i.INDICATOR_CODE WHEN 'COOK_TIME' THEN 80 WHEN 'GOOD_RATE' THEN 80 WHEN 'REJECT_RATE' THEN 80 WHEN 'OVERTIME_RATE' THEN 80
                             WHEN 'HYGIENE' THEN 50 WHEN 'SERVICE' THEN 45 WHEN 'PACKAGING' THEN 85 END * pi.WEIGHT / 100, 2),
  CASE WHEN (CASE i.INDICATOR_CODE WHEN 'COOK_TIME' THEN 80 WHEN 'GOOD_RATE' THEN 80 WHEN 'REJECT_RATE' THEN 80 WHEN 'OVERTIME_RATE' THEN 80
                                  WHEN 'HYGIENE' THEN 50 WHEN 'SERVICE' THEN 45 WHEN 'PACKAGING' THEN 85 END) >= pi.PASS_SCORE THEN 1 ELSE 0 END,
  NULL,
  CASE i.INDICATOR_CODE WHEN 'HYGIENE' THEN '厨房地面有油污' WHEN 'SERVICE' THEN '服务态度需提升' WHEN 'PACKAGING' THEN '包装不规范' END,
  pi.SORT, 1, NOW()
FROM bu_inspect_plan_indicator pi
JOIN bu_inspect_indicator i ON i.INDICATOR_ID = pi.INDICATOR_ID
WHERE pi.PLAN_ID = @plan1 AND pi.STATUS = 1;

-- T2 门店2：自动项全80分；人工 卫生50(不达标) 服务45(不达标) 包装40(不达标)
INSERT INTO bu_inspect_task_item
  (TASK_ID, PLAN_ID, STORE_ID, PLAN_INDICATOR_ID, INDICATOR_ID, SCORE_LEVEL,
   INDICATOR_CODE, INDICATOR_NAME, INDICATOR_TYPE, DATA_SOURCE,
   WEIGHT, MAX_SCORE, PASS_SCORE, RAW_VALUE, RAW_VALUE_TEXT, SCORE, WEIGHTED_SCORE, PASS_FLAG,
   PICTURE_URLS, RMK, SORT, STATUS, CREATE_DATE)
SELECT
  @t2, @plan1, 2, pi.PLAN_INDICATOR_ID, pi.INDICATOR_ID, 2,
  i.INDICATOR_CODE, i.INDICATOR_NAME, i.INDICATOR_TYPE, i.DATA_SOURCE,
  pi.WEIGHT, pi.MAX_SCORE, pi.PASS_SCORE,
  CASE i.INDICATOR_CODE WHEN 'COOK_TIME' THEN 15 WHEN 'GOOD_RATE' THEN 92 WHEN 'REJECT_RATE' THEN 1.2 WHEN 'OVERTIME_RATE' THEN 2.5 END,
  CASE i.INDICATOR_CODE WHEN 'COOK_TIME' THEN '15分钟' WHEN 'GOOD_RATE' THEN '92%' WHEN 'REJECT_RATE' THEN '1.2%' WHEN 'OVERTIME_RATE' THEN '2.5%' END,
  CASE i.INDICATOR_CODE WHEN 'COOK_TIME' THEN 80 WHEN 'GOOD_RATE' THEN 80 WHEN 'REJECT_RATE' THEN 80 WHEN 'OVERTIME_RATE' THEN 80
                        WHEN 'HYGIENE' THEN 50 WHEN 'SERVICE' THEN 45 WHEN 'PACKAGING' THEN 40 END,
  ROUND(CASE i.INDICATOR_CODE WHEN 'COOK_TIME' THEN 80 WHEN 'GOOD_RATE' THEN 80 WHEN 'REJECT_RATE' THEN 80 WHEN 'OVERTIME_RATE' THEN 80
                             WHEN 'HYGIENE' THEN 50 WHEN 'SERVICE' THEN 45 WHEN 'PACKAGING' THEN 40 END * pi.WEIGHT / 100, 2),
  CASE WHEN (CASE i.INDICATOR_CODE WHEN 'COOK_TIME' THEN 80 WHEN 'GOOD_RATE' THEN 80 WHEN 'REJECT_RATE' THEN 80 WHEN 'OVERTIME_RATE' THEN 80
                                  WHEN 'HYGIENE' THEN 50 WHEN 'SERVICE' THEN 45 WHEN 'PACKAGING' THEN 40 END) >= pi.PASS_SCORE THEN 1 ELSE 0 END,
  NULL,
  CASE i.INDICATOR_CODE WHEN 'HYGIENE' THEN '食材未分类存放' WHEN 'SERVICE' THEN '态度冷淡' WHEN 'PACKAGING' THEN '外卖包装破损' END,
  pi.SORT, 1, NOW()
FROM bu_inspect_plan_indicator pi
JOIN bu_inspect_indicator i ON i.INDICATOR_ID = pi.INDICATOR_ID
WHERE pi.PLAN_ID = @plan1 AND pi.STATUS = 1;

-- T3 门店3：自动项全80分；人工 卫生85 服务80 包装90（全部及格）
INSERT INTO bu_inspect_task_item
  (TASK_ID, PLAN_ID, STORE_ID, PLAN_INDICATOR_ID, INDICATOR_ID, SCORE_LEVEL,
   INDICATOR_CODE, INDICATOR_NAME, INDICATOR_TYPE, DATA_SOURCE,
   WEIGHT, MAX_SCORE, PASS_SCORE, RAW_VALUE, RAW_VALUE_TEXT, SCORE, WEIGHTED_SCORE, PASS_FLAG,
   PICTURE_URLS, RMK, SORT, STATUS, CREATE_DATE)
SELECT
  @t3, @plan1, 3, pi.PLAN_INDICATOR_ID, pi.INDICATOR_ID, 2,
  i.INDICATOR_CODE, i.INDICATOR_NAME, i.INDICATOR_TYPE, i.DATA_SOURCE,
  pi.WEIGHT, pi.MAX_SCORE, pi.PASS_SCORE,
  CASE i.INDICATOR_CODE WHEN 'COOK_TIME' THEN 15 WHEN 'GOOD_RATE' THEN 92 WHEN 'REJECT_RATE' THEN 1.2 WHEN 'OVERTIME_RATE' THEN 2.5 END,
  CASE i.INDICATOR_CODE WHEN 'COOK_TIME' THEN '15分钟' WHEN 'GOOD_RATE' THEN '92%' WHEN 'REJECT_RATE' THEN '1.2%' WHEN 'OVERTIME_RATE' THEN '2.5%' END,
  CASE i.INDICATOR_CODE WHEN 'COOK_TIME' THEN 80 WHEN 'GOOD_RATE' THEN 80 WHEN 'REJECT_RATE' THEN 80 WHEN 'OVERTIME_RATE' THEN 80
                        WHEN 'HYGIENE' THEN 85 WHEN 'SERVICE' THEN 80 WHEN 'PACKAGING' THEN 90 END,
  ROUND(CASE i.INDICATOR_CODE WHEN 'COOK_TIME' THEN 80 WHEN 'GOOD_RATE' THEN 80 WHEN 'REJECT_RATE' THEN 80 WHEN 'OVERTIME_RATE' THEN 80
                             WHEN 'HYGIENE' THEN 85 WHEN 'SERVICE' THEN 80 WHEN 'PACKAGING' THEN 90 END * pi.WEIGHT / 100, 2),
  CASE WHEN (CASE i.INDICATOR_CODE WHEN 'COOK_TIME' THEN 80 WHEN 'GOOD_RATE' THEN 80 WHEN 'REJECT_RATE' THEN 80 WHEN 'OVERTIME_RATE' THEN 80
                                  WHEN 'HYGIENE' THEN 85 WHEN 'SERVICE' THEN 80 WHEN 'PACKAGING' THEN 90 END) >= pi.PASS_SCORE THEN 1 ELSE 0 END,
  NULL,
  CASE i.INDICATOR_CODE WHEN 'HYGIENE' THEN '卫生状况良好' WHEN 'SERVICE' THEN '服务热情' WHEN 'PACKAGING' THEN '包装规范' END,
  pi.SORT, 1, NOW()
FROM bu_inspect_plan_indicator pi
JOIN bu_inspect_indicator i ON i.INDICATOR_ID = pi.INDICATOR_ID
WHERE pi.PLAN_ID = @plan1 AND pi.STATUS = 1;

-- ============ 7. 整改记录（I04-R01 不及格项生成整改；I04-R02 截止=提交+7天） ============
-- R1 门店1-卫生 第1轮：复检不通过(5) → 生成第2轮
INSERT INTO bu_rectification
  (TASK_ID, TASK_ITEM_ID, PLAN_ID, STORE_ID, STORE_NAME, INDICATOR_ID, INDICATOR_NAME,
   RECT_ROUND, PARENT_RECT_ID, INSPECT_SCORE, PASS_SCORE, NOTICE_CONTENT, DEADLINE_DATE, START_DATE,
   FEEDBACK_CONTENT, FEEDBACK_PICTURES, FEEDBACK_DATE,
   RECHECK_SCORE, RECHECK_RESULT, RECHECK_PICTURES, RECHECK_RMK, RECHECK_USER_ID, RECHECK_USER_NAME, RECHECK_DATE,
   URGE_COUNT, LAST_URGE_DATE, STATUS, CREATE_DATE)
VALUES
  (@t1, (SELECT TASK_ITEM_ID FROM bu_inspect_task_item WHERE TASK_ID=@t1 AND INDICATOR_CODE='HYGIENE'),
   @plan1, 1, '特色美食店666', (SELECT INDICATOR_ID FROM bu_inspect_indicator WHERE INDICATOR_CODE='HYGIENE'), '卫生评分',
   1, NULL, 50, 60, '指标【卫生评分】未达标，请完成整改', '2026-08-08 00:00:00', '2026-08-02 10:00:00',
   '已全面清洁厨房，更换防滑垫', NULL, '2026-08-04 14:00:00',
   50, 0, NULL, '厨房仍不达标，储物间有杂物', 1, '管理员', '2026-08-05 09:00:00',
   1, '2026-08-03 10:00:00', 5, NOW());
SET @r1 = LAST_INSERT_ID();

-- R2 门店1-卫生 第2轮：待整改(1)（I04-R03 复检不通过自动生成下一轮）
INSERT INTO bu_rectification
  (TASK_ID, TASK_ITEM_ID, PLAN_ID, STORE_ID, STORE_NAME, INDICATOR_ID, INDICATOR_NAME,
   RECT_ROUND, PARENT_RECT_ID, INSPECT_SCORE, PASS_SCORE, NOTICE_CONTENT, DEADLINE_DATE, START_DATE,
   FEEDBACK_CONTENT, FEEDBACK_PICTURES, FEEDBACK_DATE,
   RECHECK_SCORE, RECHECK_RESULT, RECHECK_PICTURES, RECHECK_RMK, RECHECK_USER_ID, RECHECK_USER_NAME, RECHECK_DATE,
   URGE_COUNT, LAST_URGE_DATE, STATUS, CREATE_DATE)
VALUES
  (@t1, (SELECT TASK_ITEM_ID FROM bu_inspect_task_item WHERE TASK_ID=@t1 AND INDICATOR_CODE='HYGIENE'),
   @plan1, 1, '特色美食店666', (SELECT INDICATOR_ID FROM bu_inspect_indicator WHERE INDICATOR_CODE='HYGIENE'), '卫生评分',
   2, @r1, 50, 60, '指标【卫生评分】仍未达标，请继续整改', '2026-08-28 00:00:00', NULL,
   NULL, NULL, NULL,
   NULL, NULL, NULL, NULL, NULL, NULL, NULL,
   0, NULL, 1, NOW());

-- R3 门店1-服务 第1轮：整改中(2)
INSERT INTO bu_rectification
  (TASK_ID, TASK_ITEM_ID, PLAN_ID, STORE_ID, STORE_NAME, INDICATOR_ID, INDICATOR_NAME,
   RECT_ROUND, PARENT_RECT_ID, INSPECT_SCORE, PASS_SCORE, NOTICE_CONTENT, DEADLINE_DATE, START_DATE,
   FEEDBACK_CONTENT, FEEDBACK_PICTURES, FEEDBACK_DATE,
   RECHECK_SCORE, RECHECK_RESULT, RECHECK_PICTURES, RECHECK_RMK, RECHECK_USER_ID, RECHECK_USER_NAME, RECHECK_DATE,
   URGE_COUNT, LAST_URGE_DATE, STATUS, CREATE_DATE)
VALUES
  (@t1, (SELECT TASK_ITEM_ID FROM bu_inspect_task_item WHERE TASK_ID=@t1 AND INDICATOR_CODE='SERVICE'),
   @plan1, 1, '特色美食店666', (SELECT INDICATOR_ID FROM bu_inspect_indicator WHERE INDICATOR_CODE='SERVICE'), '服务态度',
   1, NULL, 45, 60, '指标【服务态度】未达标，请完成整改', '2026-08-28 00:00:00', '2026-08-02 11:00:00',
   NULL, NULL, NULL,
   NULL, NULL, NULL, NULL, NULL, NULL, NULL,
   0, NULL, 2, NOW());

-- R4 门店2-包装 第1轮：待复检(3)（门店已提交反馈，等待复检）
INSERT INTO bu_rectification
  (TASK_ID, TASK_ITEM_ID, PLAN_ID, STORE_ID, STORE_NAME, INDICATOR_ID, INDICATOR_NAME,
   RECT_ROUND, PARENT_RECT_ID, INSPECT_SCORE, PASS_SCORE, NOTICE_CONTENT, DEADLINE_DATE, START_DATE,
   FEEDBACK_CONTENT, FEEDBACK_PICTURES, FEEDBACK_DATE,
   RECHECK_SCORE, RECHECK_RESULT, RECHECK_PICTURES, RECHECK_RMK, RECHECK_USER_ID, RECHECK_USER_NAME, RECHECK_DATE,
   URGE_COUNT, LAST_URGE_DATE, STATUS, CREATE_DATE)
VALUES
  (@t2, (SELECT TASK_ITEM_ID FROM bu_inspect_task_item WHERE TASK_ID=@t2 AND INDICATOR_CODE='PACKAGING'),
   @plan1, 2, '水饺店铺', (SELECT INDICATOR_ID FROM bu_inspect_indicator WHERE INDICATOR_CODE='PACKAGING'), '包装规范',
   1, NULL, 40, 60, '指标【包装规范】未达标，请完成整改', '2026-08-28 00:00:00', '2026-08-02 10:30:00',
   '已更换为环保密封包装', NULL, '2026-08-06 15:00:00',
   NULL, NULL, NULL, NULL, NULL, NULL, NULL,
   0, NULL, 3, NOW());

-- R5 门店2-卫生 第1轮：复检通过(4)
INSERT INTO bu_rectification
  (TASK_ID, TASK_ITEM_ID, PLAN_ID, STORE_ID, STORE_NAME, INDICATOR_ID, INDICATOR_NAME,
   RECT_ROUND, PARENT_RECT_ID, INSPECT_SCORE, PASS_SCORE, NOTICE_CONTENT, DEADLINE_DATE, START_DATE,
   FEEDBACK_CONTENT, FEEDBACK_PICTURES, FEEDBACK_DATE,
   RECHECK_SCORE, RECHECK_RESULT, RECHECK_PICTURES, RECHECK_RMK, RECHECK_USER_ID, RECHECK_USER_NAME, RECHECK_DATE,
   URGE_COUNT, LAST_URGE_DATE, STATUS, CREATE_DATE)
VALUES
  (@t2, (SELECT TASK_ITEM_ID FROM bu_inspect_task_item WHERE TASK_ID=@t2 AND INDICATOR_CODE='HYGIENE'),
   @plan1, 2, '水饺店铺', (SELECT INDICATOR_ID FROM bu_inspect_indicator WHERE INDICATOR_CODE='HYGIENE'), '卫生评分',
   1, NULL, 50, 60, '指标【卫生评分】未达标，请完成整改', '2026-08-28 00:00:00', '2026-08-02 09:00:00',
   '已按要求完成卫生整改', NULL, '2026-08-05 16:00:00',
   75, 1, NULL, '整改到位', 1, '管理员', '2026-08-07 10:00:00',
   0, NULL, 4, NOW());

-- R6 门店2-服务 第1轮：已超期(6)（截止日期已过且未完成整改）
INSERT INTO bu_rectification
  (TASK_ID, TASK_ITEM_ID, PLAN_ID, STORE_ID, STORE_NAME, INDICATOR_ID, INDICATOR_NAME,
   RECT_ROUND, PARENT_RECT_ID, INSPECT_SCORE, PASS_SCORE, NOTICE_CONTENT, DEADLINE_DATE, START_DATE,
   FEEDBACK_CONTENT, FEEDBACK_PICTURES, FEEDBACK_DATE,
   RECHECK_SCORE, RECHECK_RESULT, RECHECK_PICTURES, RECHECK_RMK, RECHECK_USER_ID, RECHECK_USER_NAME, RECHECK_DATE,
   URGE_COUNT, LAST_URGE_DATE, STATUS, CREATE_DATE)
VALUES
  (@t2, (SELECT TASK_ITEM_ID FROM bu_inspect_task_item WHERE TASK_ID=@t2 AND INDICATOR_CODE='SERVICE'),
   @plan1, 2, '水饺店铺', (SELECT INDICATOR_ID FROM bu_inspect_indicator WHERE INDICATOR_CODE='SERVICE'), '服务态度',
   1, NULL, 45, 60, '指标【服务态度】未达标，请完成整改', '2026-08-03 00:00:00', NULL,
   NULL, NULL, NULL,
   NULL, NULL, NULL, NULL, NULL, NULL, NULL,
   0, NULL, 6, NOW());

-- ============ 8. 月度考核结果（I05-R01 综合=巡检×0.6+自动×0.4；I05-R03 等级；I05-R02 排名） ============
-- 门店3 综合80.75 → 良好(2) 第1名；门店1 综合75.35 → 合格(3) 第2名；门店2 综合74.00 → 合格(3) 第3名
INSERT INTO bu_assess_result
  (STORE_ID, STORE_NAME, STORE_BELONGTO, PROVINCE, CITY,
   PERIOD_TYPE, PERIOD_CODE, PERIOD_START_DATE, PERIOD_END_DATE,
   INSPECT_SCORE, AUTO_SCORE, TOTAL_SCORE, ASSESS_LEVEL, RANK_NO,
   TASK_COUNT, FINISH_TASK_COUNT, RECT_COUNT, OVERTIME_RECT_COUNT,
   SUMMARY_DATE, STATUS, CREATE_DATE)
VALUES
  (3, '烤肉拌饭', 1, 14, 15, 2, '2026-08', '2026-08-01', '2026-08-31',
   81.25, 80.00, 80.75, 2, 1, 1, 1, 0, 0, '2026-08-31 23:59:59', 1, NOW()),
  (1, '特色美食店666', 2, 18, 19, 2, '2026-08', '2026-08-01', '2026-08-31',
   72.25, 80.00, 75.35, 3, 2, 1, 1, 3, 0, '2026-08-31 23:59:59', 1, NOW()),
  (2, '水饺店铺', 1, 18, 20, 2, '2026-08', '2026-08-01', '2026-08-31',
   70.00, 80.00, 74.00, 3, 3, 1, 1, 3, 1, '2026-08-31 23:59:59', 1, NOW());

-- ============ 9. 汇总校验 ============
SELECT '--- 计划 ---' AS label;
SELECT PLAN_ID, PLAN_NAME, PLAN_TYPE, CYCLE_TYPE, START_DATE, END_DATE, NEXT_EXECUTE_DATE, STORE_COUNT, STATUS FROM bu_inspect_plan WHERE PLAN_ID IN (@plan1,@plan2);
SELECT '--- 计划1 指标权重合计 ---';
SELECT ROUND(SUM(WEIGHT),0) AS weight_total FROM bu_inspect_plan_indicator WHERE PLAN_ID=@plan1 AND STATUS=1;
SELECT '--- 任务 ---';
SELECT TASK_ID, STORE_NAME, TASK_DATE, TOTAL_SCORE, MAX_SCORE, PASS_SCORE, PASS_FLAG, UNPASS_ITEM_COUNT, STATUS FROM bu_inspect_task WHERE PLAN_ID=@plan1 ORDER BY TASK_ID;
SELECT '--- 任务1 明细（应7条，加权和=72.25） ---';
SELECT COUNT(*) AS item_cnt, ROUND(SUM(WEIGHTED_SCORE),2) AS wsum, SUM(PASS_FLAG) AS pass_cnt FROM bu_inspect_task_item WHERE TASK_ID=@t1;
SELECT '--- 整改状态分布 ---';
SELECT STATUS, COUNT(*) AS cnt FROM bu_rectification WHERE PLAN_ID=@plan1 GROUP BY STATUS ORDER BY STATUS;
SELECT '--- 考核排名 ---';
SELECT RANK_NO, STORE_NAME, INSPECT_SCORE, AUTO_SCORE, TOTAL_SCORE, ASSESS_LEVEL FROM bu_assess_result WHERE PERIOD_CODE='2026-08' ORDER BY RANK_NO;