risk_control_platform/sql/risk_supervision.sql

-- ============================================================
-- 监督抽检 / 平台客诉 模块 - 数据库升级脚本(幂等)
-- 适用: MySQL 5.7+ / 8.0
-- 表: 5 张(抽检单 / 案件主表 / 案件节点 / 客诉主表 / 客诉节点)
-- 字典: 6 个(inspection_result / inspection_status / risk_level /
--              complaint_source / case_status / complaint_status)
-- 说明: 使用 INFORMATION_SCHEMA 兼容低版本 MySQL,所有 IF NOT EXISTS
--       风格的 DDL,支持重复执行不报错
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================
-- 1. 字典(6 个)
-- ============================================================

-- 1.1 抽检结果
INSERT INTO sys_dict_type (dict_name, dict_type, status, remark, create_by, create_time, update_by, update_time)
SELECT '抽检结果', 'risk_supervision_inspection_result', '0', '门店抽检结果(合格/不合格)', 'admin', NOW(), 'admin', NOW()
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'risk_supervision_inspection_result');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, remark, create_by, create_time, update_by, update_time)
SELECT 1, '抽检合格', '抽检合格', 'risk_supervision_inspection_result', '', 'success', 'Y', '0', '', 'admin', NOW(), 'admin', NOW()
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'risk_supervision_inspection_result' AND dict_value = '抽检合格');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, remark, create_by, create_time, update_by, update_time)
SELECT 2, '抽检不合格', '抽检不合格', 'risk_supervision_inspection_result', '', 'danger', 'N', '0', '', 'admin', NOW(), 'admin', NOW()
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'risk_supervision_inspection_result' AND dict_value = '抽检不合格');

-- 1.2 抽检审核状态
INSERT INTO sys_dict_type (dict_name, dict_type, status, remark, create_by, create_time, update_by, update_time)
SELECT '抽检审核状态', 'risk_supervision_inspection_status', '0', '门店抽检单审核状态', 'admin', NOW(), 'admin', NOW()
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'risk_supervision_inspection_status');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, remark, create_by, create_time, update_by, update_time)
SELECT 1, '待审核', '待审核', 'risk_supervision_inspection_status', '', 'warning', 'Y', '0', '', 'admin', NOW(), 'admin', NOW()
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'risk_supervision_inspection_status' AND dict_value = '待审核');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, remark, create_by, create_time, update_by, update_time)
SELECT 2, '已审核', '已审核', 'risk_supervision_inspection_status', '', 'success', 'N', '0', '', 'admin', NOW(), 'admin', NOW()
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'risk_supervision_inspection_status' AND dict_value = '已审核');

-- 1.3 风险等级
INSERT INTO sys_dict_type (dict_name, dict_type, status, remark, create_by, create_time, update_by, update_time)
SELECT '风险等级', 'risk_supervision_risk_level', '0', '案件风险等级', 'admin', NOW(), 'admin', NOW()
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'risk_supervision_risk_level');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, remark, create_by, create_time, update_by, update_time)
SELECT 1, '高风险', '高风险', 'risk_supervision_risk_level', '', 'danger', 'N', '0', '', 'admin', NOW(), 'admin', NOW()
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'risk_supervision_risk_level' AND dict_value = '高风险');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, remark, create_by, create_time, update_by, update_time)
SELECT 2, '中风险', '中风险', 'risk_supervision_risk_level', '', 'warning', 'N', '0', '', 'admin', NOW(), 'admin', NOW()
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'risk_supervision_risk_level' AND dict_value = '中风险');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, remark, create_by, create_time, update_by, update_time)
SELECT 3, '低风险', '低风险', 'risk_supervision_risk_level', '', 'info', 'N', '0', '', 'admin', NOW(), 'admin', NOW()
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'risk_supervision_risk_level' AND dict_value = '低风险');

-- 1.4 客诉来源
INSERT INTO sys_dict_type (dict_name, dict_type, status, remark, create_by, create_time, update_by, update_time)
SELECT '客诉来源', 'risk_supervision_complaint_source', '0', '平台客诉来源', 'admin', NOW(), 'admin', NOW()
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'risk_supervision_complaint_source');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, remark, create_by, create_time, update_by, update_time)
SELECT 1, '消费者投诉', '消费者投诉', 'risk_supervision_complaint_source', '', 'primary', 'Y', '0', '', 'admin', NOW(), 'admin', NOW()
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'risk_supervision_complaint_source' AND dict_value = '消费者投诉');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, remark, create_by, create_time, update_by, update_time)
SELECT 2, '媒体舆情', '媒体舆情', 'risk_supervision_complaint_source', '', 'warning', 'N', '0', '', 'admin', NOW(), 'admin', NOW()
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'risk_supervision_complaint_source' AND dict_value = '媒体舆情');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, remark, create_by, create_time, update_by, update_time)
SELECT 3, '12315平台', '12315平台', 'risk_supervision_complaint_source', '', 'danger', 'N', '0', '', 'admin', NOW(), 'admin', NOW()
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'risk_supervision_complaint_source' AND dict_value = '12315平台');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, remark, create_by, create_time, update_by, update_time)
SELECT 4, '其他来源', '其他来源', 'risk_supervision_complaint_source', '', 'info', 'N', '0', '', 'admin', NOW(), 'admin', NOW()
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'risk_supervision_complaint_source' AND dict_value = '其他来源');

-- 1.5 案件状态
INSERT INTO sys_dict_type (dict_name, dict_type, status, remark, create_by, create_time, update_by, update_time)
SELECT '案件状态', 'risk_supervision_case_status', '0', '不合格案件处理状态', 'admin', NOW(), 'admin', NOW()
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'risk_supervision_case_status');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, remark, create_by, create_time, update_by, update_time)
SELECT 1, '处置中', '处置中', 'risk_supervision_case_status', '', 'warning', 'Y', '0', '', 'admin', NOW(), 'admin', NOW()
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'risk_supervision_case_status' AND dict_value = '处置中');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, remark, create_by, create_time, update_by, update_time)
SELECT 2, '已闭环', '已闭环', 'risk_supervision_case_status', '', 'success', 'N', '0', '', 'admin', NOW(), 'admin', NOW()
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'risk_supervision_case_status' AND dict_value = '已闭环');

-- 1.6 客诉状态
INSERT INTO sys_dict_type (dict_name, dict_type, status, remark, create_by, create_time, update_by, update_time)
SELECT '客诉状态', 'risk_supervision_complaint_status', '0', '平台客诉处理状态', 'admin', NOW(), 'admin', NOW()
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'risk_supervision_complaint_status');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, remark, create_by, create_time, update_by, update_time)
SELECT 1, '处置中', '处置中', 'risk_supervision_complaint_status', '', 'warning', 'Y', '0', '', 'admin', NOW(), 'admin', NOW()
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'risk_supervision_complaint_status' AND dict_value = '处置中');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, remark, create_by, create_time, update_by, update_time)
SELECT 2, '已闭环', '已闭环', 'risk_supervision_complaint_status', '', 'success', 'N', '0', '', 'admin', NOW(), 'admin', NOW()
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'risk_supervision_complaint_status' AND dict_value = '已闭环');

-- ============================================================
-- 2. 业务表(5 张)
-- ============================================================

-- 2.1 门店抽检单
CREATE TABLE IF NOT EXISTS `risk_supervision_inspection` (
    `id`               BIGINT          NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `inspection_code`  VARCHAR(50)     NOT NULL COMMENT '抽检单号 JC+yyyyMMdd+4位序号',
    `store_id`         BIGINT          DEFAULT NULL COMMENT '门店ID',
    `store_name`       VARCHAR(200)    DEFAULT NULL COMMENT '门店名称',
    `inspection_time`  DATETIME        DEFAULT NULL COMMENT '抽检时间',
    `product_name`     VARCHAR(200)    DEFAULT NULL COMMENT '抽检商品',
    `inspection_result` VARCHAR(20)    DEFAULT NULL COMMENT '抽检结果(字典:抽检合格/抽检不合格)',
    `fail_reason`      VARCHAR(1000)   DEFAULT NULL COMMENT '不合格原因',
    `status`           VARCHAR(20)     DEFAULT '待审核' COMMENT '状态(字典:待审核/已审核)',
    `inspector_id`     BIGINT          DEFAULT NULL COMMENT '抽检人ID',
    `inspector_name`   VARCHAR(50)     DEFAULT NULL COMMENT '抽检人姓名',
    `remark`           VARCHAR(500)    DEFAULT NULL COMMENT '备注',
    `tenant_id`        VARCHAR(20)     DEFAULT '000000' COMMENT '租户ID',
    `create_dept`      BIGINT          DEFAULT NULL COMMENT '创建部门',
    `create_by`        BIGINT          DEFAULT NULL COMMENT '创建人',
    `create_time`      DATETIME        DEFAULT NULL COMMENT '创建时间',
    `update_by`        BIGINT          DEFAULT NULL COMMENT '更新人',
    `update_time`      DATETIME        DEFAULT NULL COMMENT '更新时间',
    `del_flag`         CHAR(1)         DEFAULT '0' COMMENT '删除标志(0正常 1删除)',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_inspection_code` (`inspection_code`),
    KEY `idx_inspection_time` (`inspection_time`),
    KEY `idx_status_result` (`status`, `inspection_result`),
    KEY `idx_store_id` (`store_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='监督抽检 - 门店抽检单';

-- 2.2 不合格案件主表
CREATE TABLE IF NOT EXISTS `risk_supervision_case` (
    `id`               BIGINT          NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `case_code`        VARCHAR(50)     NOT NULL COMMENT '案件编号 AJ+yyyyMMdd+4位序号',
    `inspection_id`    BIGINT          DEFAULT NULL COMMENT '关联抽检单ID',
    `inspection_code`  VARCHAR(50)     DEFAULT NULL COMMENT '冗余抽检单号',
    `store_id`         BIGINT          DEFAULT NULL COMMENT '涉事门店ID',
    `store_name`       VARCHAR(200)    DEFAULT NULL COMMENT '涉事门店',
    `product_name`     VARCHAR(200)    DEFAULT NULL COMMENT '不合格产品',
    `current_node`     INT             DEFAULT 1 COMMENT '当前节点(1-5)',
    `node_name`        VARCHAR(50)     DEFAULT NULL COMMENT '当前节点名称(冗余)',
    `risk_level`       VARCHAR(20)     DEFAULT NULL COMMENT '风险等级(字典:高/中/低风险)',
    `risk_desc`        VARCHAR(2000)   DEFAULT NULL COMMENT '风险描述',
    `control_measures` VARCHAR(2000)   DEFAULT NULL COMMENT '控制措施',
    `handler_id`       BIGINT          DEFAULT NULL COMMENT '处理人ID',
    `handler_name`     VARCHAR(50)     DEFAULT NULL COMMENT '处理人',
    `status`           VARCHAR(20)     DEFAULT '处置中' COMMENT '状态(字典:处置中/已闭环)',
    `remark`           VARCHAR(500)    DEFAULT NULL COMMENT '备注',
    `tenant_id`        VARCHAR(20)     DEFAULT '000000' COMMENT '租户ID',
    `create_dept`      BIGINT          DEFAULT NULL COMMENT '创建部门',
    `create_by`        BIGINT          DEFAULT NULL COMMENT '创建人',
    `create_time`      DATETIME        DEFAULT NULL COMMENT '创建时间',
    `update_by`        BIGINT          DEFAULT NULL COMMENT '更新人',
    `update_time`      DATETIME        DEFAULT NULL COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_case_code` (`case_code`),
    KEY `idx_inspection_id` (`inspection_id`),
    KEY `idx_status_node` (`status`, `current_node`),
    KEY `idx_store_id` (`store_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='监督抽检 - 不合格案件主表';

-- 2.3 案件节点处理记录
CREATE TABLE IF NOT EXISTS `risk_supervision_case_node` (
    `id`               BIGINT          NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `case_id`          BIGINT          NOT NULL COMMENT '案件ID',
    `node_code`        INT             NOT NULL COMMENT '节点编号 1-5',
    `node_name`        VARCHAR(50)     DEFAULT NULL COMMENT '节点名称',
    `opinion`          VARCHAR(2000)   DEFAULT NULL COMMENT '当前节点处理意见',
    `attachment_url`   VARCHAR(500)    DEFAULT NULL COMMENT '凭证附件URL(MinIO)',
    `attachment_name`  VARCHAR(200)    DEFAULT NULL COMMENT '凭证附件原始文件名',
    `handler_id`       BIGINT          DEFAULT NULL COMMENT '处理人ID',
    `handler_name`     VARCHAR(50)     DEFAULT NULL COMMENT '处理人',
    `handle_time`      DATETIME        DEFAULT NULL COMMENT '处理时间',
    `tenant_id`        VARCHAR(20)     DEFAULT '000000' COMMENT '租户ID',
    `create_dept`      BIGINT          DEFAULT NULL COMMENT '创建部门',
    `create_by`        BIGINT          DEFAULT NULL COMMENT '创建人',
    `create_time`      DATETIME        DEFAULT NULL COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `idx_case_id_node` (`case_id`, `node_code`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='监督抽检 - 案件节点处理记录';

-- 2.4 平台客诉主表
CREATE TABLE IF NOT EXISTS `risk_supervision_complaint` (
    `id`                BIGINT          NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `complaint_code`    VARCHAR(50)     NOT NULL COMMENT '客诉编号 TS+yyyyMMdd+4位序号',
    `source`            VARCHAR(50)     DEFAULT NULL COMMENT '客诉来源(字典)',
    `complainant_name`  VARCHAR(50)     DEFAULT NULL COMMENT '投诉人姓名',
    `complainant_phone` VARCHAR(20)     DEFAULT NULL COMMENT '投诉人电话',
    `store_id`          BIGINT          DEFAULT NULL COMMENT '涉事门店ID',
    `store_name`        VARCHAR(200)    DEFAULT NULL COMMENT '涉事门店',
    `transfer_time`     DATETIME        DEFAULT NULL COMMENT '移交时间',
    `complaint_content` TEXT            COMMENT '客诉内容',
    `attachment_url`    VARCHAR(500)    DEFAULT NULL COMMENT '附件URL(MinIO)',
    `attachment_name`   VARCHAR(200)    DEFAULT NULL COMMENT '附件原始文件名',
    `current_node`      INT             DEFAULT 1 COMMENT '当前节点(1-7)',
    `node_name`         VARCHAR(50)     DEFAULT NULL COMMENT '当前节点名称',
    `status`            VARCHAR(20)     DEFAULT '处置中' COMMENT '状态(字典:处置中/已闭环)',
    `remark`            VARCHAR(500)    DEFAULT NULL COMMENT '备注',
    `tenant_id`         VARCHAR(20)     DEFAULT '000000' COMMENT '租户ID',
    `create_dept`       BIGINT          DEFAULT NULL COMMENT '创建部门',
    `create_by`         BIGINT          DEFAULT NULL COMMENT '创建人',
    `create_time`       DATETIME        DEFAULT NULL COMMENT '创建时间',
    `update_by`         BIGINT          DEFAULT NULL COMMENT '更新人',
    `update_time`       DATETIME        DEFAULT NULL COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_complaint_code` (`complaint_code`),
    KEY `idx_status_node` (`status`, `current_node`),
    KEY `idx_transfer_time` (`transfer_time`),
    KEY `idx_store_id` (`store_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='监督抽检 - 平台客诉主表';

-- 2.5 客诉节点处理记录
CREATE TABLE IF NOT EXISTS `risk_supervision_complaint_node` (
    `id`               BIGINT          NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `complaint_id`     BIGINT          NOT NULL COMMENT '客诉ID',
    `node_code`        INT             NOT NULL COMMENT '节点编号 1-7',
    `node_name`        VARCHAR(50)     DEFAULT NULL COMMENT '节点名称',
    `opinion`          VARCHAR(2000)   DEFAULT NULL COMMENT '当前环节处理意见',
    `attachment_url`   VARCHAR(500)    DEFAULT NULL COMMENT '凭证附件URL(MinIO)',
    `attachment_name`  VARCHAR(200)    DEFAULT NULL COMMENT '凭证附件原始文件名',
    `handler_id`       BIGINT          DEFAULT NULL COMMENT '处理人ID',
    `handler_name`     VARCHAR(50)     DEFAULT NULL COMMENT '处理人',
    `handle_time`      DATETIME        DEFAULT NULL COMMENT '处理时间',
    `tenant_id`        VARCHAR(20)     DEFAULT '000000' COMMENT '租户ID',
    `create_dept`      BIGINT          DEFAULT NULL COMMENT '创建部门',
    `create_by`        BIGINT          DEFAULT NULL COMMENT '创建人',
    `create_time`      DATETIME        DEFAULT NULL COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `idx_complaint_id_node` (`complaint_id`, `node_code`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='监督抽检 - 客诉节点处理记录';

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- 完成 - 监督抽检 / 平台客诉 模块升级
-- ============================================================