risk_control_platform/sql/risk_supervision_v2.sql

-- ============================================================
-- 监督抽检 / 平台客诉 模块升级 v2
-- 1) 抽检表 +8 字段
-- 2) 客诉表 +6 字段
-- 3) 4 个新字典
-- 4) 案件状态:已闭环 -> 归档
-- ============================================================

USE risk_control_platform;

-- ============== 1. 抽检表加字段 ==============
ALTER TABLE `risk_supervision_inspection`
    ADD COLUMN `product_barcode`   VARCHAR(100) DEFAULT NULL COMMENT '抽检商品条码'        AFTER `product_name`,
    ADD COLUMN `lot_no`            VARCHAR(100) DEFAULT NULL COMMENT '抽检商品批号'        AFTER `product_barcode`,
    ADD COLUMN `production_date`   DATE         DEFAULT NULL COMMENT '抽检商品生产日期'     AFTER `lot_no`,
    ADD COLUMN `supplier`          VARCHAR(200) DEFAULT NULL COMMENT '抽检商品供应商'       AFTER `production_date`,
    ADD COLUMN `sample_quantity`   INT          DEFAULT NULL COMMENT '抽样数量'             AFTER `supplier`,
    ADD COLUMN `detect_org`        VARCHAR(200) DEFAULT NULL COMMENT '检测机构'             AFTER `sample_quantity`,
    ADD COLUMN `fail_item`         VARCHAR(50)  DEFAULT NULL COMMENT '不合格项目(字典)'     AFTER `detect_org`,
    ADD COLUMN `fail_value`        VARCHAR(200) DEFAULT NULL COMMENT '不合格指标数值'       AFTER `fail_item`;

-- ============== 2. 客诉表加字段 ==============
ALTER TABLE `risk_supervision_complaint`
    ADD COLUMN `product_barcode`   VARCHAR(100) DEFAULT NULL COMMENT '客诉商品条码'        AFTER `complainant_phone`,
    ADD COLUMN `product_name`      VARCHAR(200) DEFAULT NULL COMMENT '客诉商品名称'        AFTER `product_barcode`,
    ADD COLUMN `lot_no`            VARCHAR(100) DEFAULT NULL COMMENT '客诉商品批号'        AFTER `product_name`,
    ADD COLUMN `production_date`   DATE         DEFAULT NULL COMMENT '客诉商品生产日期'     AFTER `lot_no`,
    ADD COLUMN `supplier`          VARCHAR(200) DEFAULT NULL COMMENT '客诉商品供应商'       AFTER `production_date`,
    ADD COLUMN `quantity`          INT          DEFAULT NULL COMMENT '客诉数量'             AFTER `supplier`;

-- ============== 3. 字典:不合格项目 ==============
INSERT IGNORE INTO sys_dict_type (dict_name, dict_type, remark, create_by, create_time, update_by, update_time)
VALUES ('不合格项目', 'risk_supervision_fail_item', '抽检不合格项目字典', 1, NOW(), 1, NOW());

INSERT IGNORE INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, remark, create_by, create_time, update_by, update_time) VALUES
(1, '农药残留',  '农药残留',  'risk_supervision_fail_item', '', 'danger', 'N', '', 1, NOW(), 1, NOW()),
(2, '兽药残留',  '兽药残留',  'risk_supervision_fail_item', '', 'danger', 'N', '', 1, NOW(), 1, NOW()),
(3, '重金属超标','重金属超标','risk_supervision_fail_item', '', 'danger', 'N', '', 1, NOW(), 1, NOW()),
(4, '微生物超标','微生物超标','risk_supervision_fail_item', '', 'danger', 'N', '', 1, NOW(), 1, NOW()),
(5, '添加剂超标','添加剂超标','risk_supervision_fail_item', '', 'danger', 'N', '', 1, NOW(), 1, NOW()),
(6, '感官指标',  '感官指标',  'risk_supervision_fail_item', '', 'warning','N', '', 1, NOW(), 1, NOW()),
(7, '标签标识',  '标签标识',  'risk_supervision_fail_item', '', 'warning','N', '', 1, NOW(), 1, NOW()),
(8, '其他',      '其他',      'risk_supervision_fail_item', '', 'info',   'N', '', 1, NOW(), 1, NOW());

-- ============== 4. 字典:控制措施 ==============
INSERT IGNORE INTO sys_dict_type (dict_name, dict_type, remark, create_by, create_time, update_by, update_time)
VALUES ('控制措施', 'risk_supervision_control_measure', '风险控制措施字典', 1, NOW(), 1, NOW());

INSERT IGNORE INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, remark, create_by, create_time, update_by, update_time) VALUES
(1, '立即下架涉事产品、暂停销售',     '立即下架涉事产品、暂停销售',     'risk_supervision_control_measure', '', 'danger',  'N', '', 1, NOW(), 1, NOW()),
(2, '通知门店停止销售并下架',         '通知门店停止销售并下架',         'risk_supervision_control_measure', '', 'danger',  'N', '', 1, NOW(), 1, NOW()),
(3, '暂停同批次产品销售',             '暂停同批次产品销售',             'risk_supervision_control_measure', '', 'warning',  'N', '', 1, NOW(), 1, NOW()),
(4, '通知供应商召回同批次产品',       '通知供应商召回同批次产品',       'risk_supervision_control_measure', '', 'warning',  'N', '', 1, NOW(), 1, NOW()),
(5, '开展门店全面排查',               '开展门店全面排查',               'risk_supervision_control_measure', '', 'primary',  'N', '', 1, NOW(), 1, NOW()),
(6, '暂不处置,继续观察',             '暂不处置,继续观察',             'risk_supervision_control_measure', '', 'info',     'N', '', 1, NOW(), 1, NOW());

-- ============== 5. 字典:复检结果 ==============
INSERT IGNORE INTO sys_dict_type (dict_name, dict_type, remark, create_by, create_time, update_by, update_time)
VALUES ('复检结果', 'risk_supervision_review_result', '异议复检结果', 1, NOW(), 1, NOW());

INSERT IGNORE INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, remark, create_by, create_time, update_by, update_time) VALUES
(1, '合格',    '合格',    'risk_supervision_review_result', '', 'success', 'N', '', 1, NOW(), 1, NOW()),
(2, '仍不合格','仍不合格','risk_supervision_review_result', '', 'danger',  'N', '', 1, NOW(), 1, NOW());

-- ============== 6. 字典:调查报告原因 ==============
INSERT IGNORE INTO sys_dict_type (dict_name, dict_type, remark, create_by, create_time, update_by, update_time)
VALUES ('调查报告原因', 'risk_supervision_investigate_reason', '调查报告原因字典', 1, NOW(), 1, NOW());

INSERT IGNORE INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, remark, create_by, create_time, update_by, update_time) VALUES
(1, '生产环节',     '生产环节',     'risk_supervision_investigate_reason', '', 'primary', 'N', '', 1, NOW(), 1, NOW()),
(2, '储存环节',     '储存环节',     'risk_supervision_investigate_reason', '', 'primary', 'N', '', 1, NOW(), 1, NOW()),
(3, '运输环节',     '运输环节',     'risk_supervision_investigate_reason', '', 'primary', 'N', '', 1, NOW(), 1, NOW()),
(4, '门店陈列储存', '门店陈列储存', 'risk_supervision_investigate_reason', '', 'primary', 'N', '', 1, NOW(), 1, NOW()),
(5, '其他环节',     '其他环节',     'risk_supervision_investigate_reason', '', 'info',    'N', '', 1, NOW(), 1, NOW());

-- ============== 7. 案件状态字典:补 "归档" ==============
INSERT IGNORE INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, remark, create_by, create_time, update_by, update_time)
VALUES (3, '归档', '归档', 'risk_supervision_case_status', '', 'success', 'N', '案件闭环后归档', 1, NOW(), 1, NOW());

-- ============================================================
-- 完成
-- ============================================================