risk_control_platform/sql/risk_quality_inspection.sql

-- =============================================================
-- 风控品控平台 · 品控巡检增量脚本 (2026-07-07)
-- 文件: risk_quality_inspection.sql
-- 适用: 已有环境升级 / 新环境增量应用
-- 幂等: 所有 ALTER/INSERT 均使用 INFORMATION_SCHEMA 条件判断,可重复执行
-- =============================================================

-- 1. risk_inspection_task 加 biz_type 列(风控/品控区分)
SET @sql = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'risk_inspection_task'
       AND COLUMN_NAME  = 'biz_type') = 0,
  'ALTER TABLE `risk_inspection_task` ADD COLUMN `biz_type` VARCHAR(20) NOT NULL DEFAULT ''risk'' COMMENT ''业务类型(risk风控/quality品控)'' AFTER `tenant_id`',
  'SELECT 1'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 2. 加 idx_biz_type 索引
SET @sql = (SELECT IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
     WHERE TABLE_SCHEMA = DATABASE()
       AND TABLE_NAME   = 'risk_inspection_task'
       AND INDEX_NAME   = 'idx_biz_type') = 0,
  'ALTER TABLE `risk_inspection_task` ADD KEY `idx_biz_type` (`biz_type`)',
  'SELECT 1'));
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 3. 老数据迁移(将所有 biz_type IS NULL 的行改为 'risk')
UPDATE `risk_inspection_task`
SET `biz_type` = 'risk'
WHERE `biz_type` IS NULL OR `biz_type` = '';

-- 4. 新增字典类型:品控巡检类型
INSERT INTO `sys_dict_type` (`dict_id`, `tenant_id`, `dict_name`, `dict_type`, `create_dept`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES (12100, '000000', '品控巡检类型', 'quality_inspection_type', 103, 1, NOW(), 1, NOW(), '品控巡检任务类型字典')
ON DUPLICATE KEY UPDATE `update_time` = NOW();

-- 5. 新增字典类型:品控项目大类
INSERT INTO `sys_dict_type` (`dict_id`, `tenant_id`, `dict_name`, `dict_type`, `create_dept`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`)
VALUES (12101, '000000', '品控项目大类', 'quality_project_category', 103, 1, NOW(), 1, NOW(), '品控巡检项目大类字典(环境/设备/卫生/温度等)')
ON DUPLICATE KEY UPDATE `update_time` = NOW();

-- 6. 字典数据:品控巡检类型(对应 UI 文档中"园区巡检打卡/门店闭店检查/打样设施设备检查")
INSERT INTO `sys_dict_data` (`dict_sort`, `tenant_id`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `create_dept`, `create_by`, `create_time`, `remark`)
VALUES
  (1, '000000', '园区巡检打卡', '园区巡检打卡', 'quality_inspection_type', '', 'primary', 'N', 103, 1, NOW(), '园区内日常巡检'),
  (2, '000000', '门店闭店检查', '门店闭店检查', 'quality_inspection_type', '', 'success', 'N', 103, 1, NOW(), '门店闭店前安全检查'),
  (3, '000000', '打样设施设备检查', '打样设施设备检查', 'quality_inspection_type', '', 'info',    'N', 103, 1, NOW(), '打样设备日常点检'),
  (4, '000000', '食品安全专项', '食品安全专项', 'quality_inspection_type', '', 'warning', 'N', 103, 1, NOW(), '食品安全专项检查')
ON DUPLICATE KEY UPDATE `update_time` = NOW();

-- 7. 字典数据:品控项目大类(对应 UI 打卡详情中"环境检查/设备检查/卫生检查/温度检查")
INSERT INTO `sys_dict_data` (`dict_sort`, `tenant_id`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `create_dept`, `create_by`, `create_time`, `remark`)
VALUES
  (1, '000000', '环境检查', '环境检查', 'quality_project_category', '', 'primary', 'N', 103, 1, NOW(), '园区/门店环境'),
  (2, '000000', '设备检查', '设备检查', 'quality_project_category', '', 'success', 'N', 103, 1, NOW(), '设施设备状态'),
  (3, '000000', '卫生检查', '卫生检查', 'quality_project_category', '', 'info',    'N', 103, 1, NOW(), '现场卫生状况'),
  (4, '000000', '温度检查', '温度检查', 'quality_project_category', '', 'warning', 'N', 103, 1, NOW(), '温度合规')
ON DUPLICATE KEY UPDATE `update_time` = NOW();

-- 8. 字典数据:打卡状态(全局,与风控共用;若已存在则 noop)
INSERT INTO `sys_dict_data` (`dict_sort`, `tenant_id`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `create_dept`, `create_by`, `create_time`, `remark`)
SELECT 1, '000000', '已打卡', '已打卡', 'inspection_status', '', 'success', 'N', 103, 1, NOW(), '已打卡'
WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'inspection_status' AND `dict_value` = '已打卡');

INSERT INTO `sys_dict_data` (`dict_sort`, `tenant_id`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `create_dept`, `create_by`, `create_time`, `remark`)
SELECT 2, '000000', '未打卡', '未打卡', 'inspection_status', '', 'danger', 'N', 103, 1, NOW(), '未打卡'
WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'inspection_status' AND `dict_value` = '未打卡');

INSERT INTO `sys_dict_data` (`dict_sort`, `tenant_id`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `create_dept`, `create_by`, `create_time`, `remark`)
SELECT 3, '000000', '已逾期', '已逾期', 'inspection_status', '', 'warning', 'N', 103, 1, NOW(), '已逾期'
WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'inspection_status' AND `dict_value` = '已逾期');

-- 9. 字典数据:打卡结果(全局)
INSERT INTO `sys_dict_data` (`dict_sort`, `tenant_id`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `create_dept`, `create_by`, `create_time`, `remark`)
SELECT 1, '000000', '正常', '正常', 'inspection_result', '', 'success', 'N', 103, 1, NOW(), '打卡正常'
WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'inspection_result' AND `dict_value` = '正常');

INSERT INTO `sys_dict_data` (`dict_sort`, `tenant_id`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `create_dept`, `create_by`, `create_time`, `remark`)
SELECT 2, '000000', '异常', '异常', 'inspection_result', '', 'danger', 'N', 103, 1, NOW(), '打卡异常'
WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'inspection_result' AND `dict_value` = '异常');

-- 10. 字典数据:打卡方式
INSERT INTO `sys_dict_data` (`dict_sort`, `tenant_id`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `create_dept`, `create_by`, `create_time`, `remark`)
SELECT 1, '000000', '常规', '常规', 'check_method', '', 'primary', 'N', 103, 1, NOW(), '常规打卡'
WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'check_method' AND `dict_value` = '常规');

INSERT INTO `sys_dict_data` (`dict_sort`, `tenant_id`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `create_dept`, `create_by`, `create_time`, `remark`)
SELECT 2, '000000', '扫码', '扫码', 'check_method', '', 'success', 'N', 103, 1, NOW(), '扫码打卡'
WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'check_method' AND `dict_value` = '扫码');

-- 11. 字典数据:巡检周期
INSERT INTO `sys_dict_data` (`dict_sort`, `tenant_id`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `create_dept`, `create_by`, `create_time`, `remark`)
SELECT 1, '000000', '每日', '每日', 'inspection_cycle', '', 'primary', 'N', 103, 1, NOW(), '每日执行'
WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'inspection_cycle' AND `dict_value` = '每日');

INSERT INTO `sys_dict_data` (`dict_sort`, `tenant_id`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `create_dept`, `create_by`, `create_time`, `remark`)
SELECT 2, '000000', '每周', '每周', 'inspection_cycle', '', 'success', 'N', 103, 1, NOW(), '每周执行'
WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'inspection_cycle' AND `dict_value` = '每周');

INSERT INTO `sys_dict_data` (`dict_sort`, `tenant_id`, `dict_label`, `dict_value`, `dict_type`, `css_class`, `list_class`, `is_default`, `create_dept`, `create_by`, `create_time`, `remark`)
SELECT 3, '000000', '每月', '每月', 'inspection_cycle', '', 'info', 'N', 103, 1, NOW(), '每月执行'
WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'inspection_cycle' AND `dict_value` = '每月');

-- 12. 验证
SELECT 'biz_type' AS col, COUNT(*) AS rows_count FROM `risk_inspection_task` WHERE `biz_type` = 'quality'
UNION ALL
SELECT 'biz_type_risk', COUNT(*) FROM `risk_inspection_task` WHERE `biz_type` = 'risk'
UNION ALL
SELECT 'quality_inspection_type_dict', COUNT(*) FROM `sys_dict_type` WHERE `dict_type` = 'quality_inspection_type'
UNION ALL
SELECT 'quality_project_category_dict', COUNT(*) FROM `sys_dict_type` WHERE `dict_type` = 'quality_project_category';