risk_control_platform/sql/risk_food_safety_campaign.sql

-- =============================================================
-- 风控品控平台 · 食品安全宣传增量脚本 (2026-07-08)
-- 文件: risk_food_safety_campaign.sql
-- 适用: 已有环境升级 / 新环境增量应用
-- 幂等: 所有 DDL/DML 均使用条件判断,可重复执行
-- =============================================================

-- 1. 食品安全宣传活动表
CREATE TABLE IF NOT EXISTS `risk_food_safety_campaign` (
  `id`                       BIGINT       NOT NULL AUTO_INCREMENT       COMMENT '主键ID',
  `campaign_code`            VARCHAR(50)  NOT NULL                       COMMENT '活动编号(CF+年月日+4位序号)',
  `campaign_name`            VARCHAR(200) NOT NULL                       COMMENT '活动名称',
  `campaign_type`            VARCHAR(50)  NOT NULL                       COMMENT '活动类型(字典:risk_food_safety_campaign_type)',
  `start_time`               DATETIME     NOT NULL                       COMMENT '开始时间',
  `end_time`                 DATETIME     NOT NULL                       COMMENT '结束时间',
  `expected_participants`    INT          DEFAULT 0                      COMMENT '预计参与人数',
  `actual_participants`      INT          DEFAULT 0                      COMMENT '实际参与人数',
  `location`                 VARCHAR(200) DEFAULT NULL                   COMMENT '活动地点',
  `description`              VARCHAR(2000) DEFAULT NULL                  COMMENT '活动描述',
  `principal_id`             BIGINT       DEFAULT NULL                   COMMENT '负责人ID',
  `principal_name`           VARCHAR(50)  DEFAULT NULL                   COMMENT '负责人姓名',
  `status`                   VARCHAR(20)  NOT NULL DEFAULT '未开始'      COMMENT '状态(未开始/进行中/已结束)',
  `start_real_time`          DATETIME     DEFAULT NULL                   COMMENT '实际开始时间(点击"活动开始"时填充)',
  `end_real_time`            DATETIME     DEFAULT NULL                   COMMENT '实际结束时间(点击"活动结束"时填充)',
  `summary`                  VARCHAR(2000) DEFAULT NULL                  COMMENT '活动总结',
  `remark`                   VARCHAR(1000) DEFAULT NULL                  COMMENT '备注',
  `tenant_id`                VARCHAR(20)  NOT NULL DEFAULT '000000'      COMMENT '租户ID',
  `create_dept`              BIGINT       DEFAULT NULL                   COMMENT '创建部门',
  `create_by`                BIGINT       DEFAULT NULL                   COMMENT '创建人',
  `create_time`              DATETIME     DEFAULT CURRENT_TIMESTAMP      COMMENT '创建时间',
  `update_by`                BIGINT       DEFAULT NULL                   COMMENT '更新人',
  `update_time`              DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `del_flag`                 CHAR(1)      DEFAULT '0'                    COMMENT '删除标志(0存在 1删除)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_campaign_code` (`campaign_code`),
  KEY `idx_status` (`status`),
  KEY `idx_campaign_type` (`campaign_type`),
  KEY `idx_start_time` (`start_time`),
  KEY `idx_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='食品安全·宣传活动表';

-- 2. 食品安全宣传资料表
CREATE TABLE IF NOT EXISTS `risk_food_safety_material` (
  `id`                  BIGINT       NOT NULL AUTO_INCREMENT       COMMENT '主键ID',
  `material_code`       VARCHAR(50)  NOT NULL                       COMMENT '资料编号(MT+年月日+4位序号)',
  `material_name`       VARCHAR(200) NOT NULL                       COMMENT '资料名称',
  `material_type`       VARCHAR(50)  NOT NULL                       COMMENT '资料类型(字典:risk_food_safety_material_type)',
  `material_summary`    VARCHAR(1000) DEFAULT NULL                  COMMENT '资料概述',
  `cover_url`           VARCHAR(500) DEFAULT NULL                   COMMENT '资料封面URL(MinIO)',
  `cover_name`          VARCHAR(200) DEFAULT NULL                   COMMENT '资料封面原始文件名',
  `attachment_url`      VARCHAR(500) DEFAULT NULL                   COMMENT '资料附件URL(MinIO)',
  `attachment_name`     VARCHAR(200) DEFAULT NULL                   COMMENT '资料附件原始文件名',
  `campaign_id`         BIGINT       DEFAULT NULL                   COMMENT '关联活动ID(可空)',
  `uploader_id`         BIGINT       DEFAULT NULL                   COMMENT '上传人ID',
  `uploader_name`       VARCHAR(50)  DEFAULT NULL                   COMMENT '上传人姓名',
  `tenant_id`           VARCHAR(20)  NOT NULL DEFAULT '000000'      COMMENT '租户ID',
  `create_dept`         BIGINT       DEFAULT NULL                   COMMENT '创建部门',
  `create_by`           BIGINT       DEFAULT NULL                   COMMENT '创建人',
  `create_time`         DATETIME     DEFAULT CURRENT_TIMESTAMP      COMMENT '创建时间',
  `update_by`           BIGINT       DEFAULT NULL                   COMMENT '更新人',
  `update_time`         DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `del_flag`            CHAR(1)      DEFAULT '0'                    COMMENT '删除标志(0存在 1删除)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_material_code` (`material_code`),
  KEY `idx_material_type` (`material_type`),
  KEY `idx_campaign_id` (`campaign_id`),
  KEY `idx_tenant` (`tenant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='食品安全·宣传资料表';

-- 3. 字典类型:活动类型(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 (12210, '000000', '食品安全活动类型', 'risk_food_safety_campaign_type', 103, 1, NOW(), 1, NOW(), '食品安全宣传活动类型')
ON DUPLICATE KEY UPDATE `update_time` = NOW();

-- 4. 字典类型:活动状态(3 项)
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 (12211, '000000', '食品安全活动状态', 'risk_food_safety_campaign_status', 103, 1, NOW(), 1, NOW(), '食品安全宣传活动状态')
ON DUPLICATE KEY UPDATE `update_time` = NOW();

-- 5. 字典类型:资料类型(3 项)
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 (12212, '000000', '食品安全资料类型', 'risk_food_safety_material_type', 103, 1, NOW(), 1, NOW(), '食品安全宣传资料类型')
ON DUPLICATE KEY UPDATE `update_time` = NOW();

-- 6. 字典数据:活动类型(5 项)
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', '食品安全基础培训', '食品安全基础培训', 'risk_food_safety_campaign_type', '', 'primary', 'N', 103, 1, NOW(), '食品安全基础培训'
WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'risk_food_safety_campaign_type' 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', '各工作场景专项培训', '各工作场景专项培训', 'risk_food_safety_campaign_type', '', 'success', 'N', 103, 1, NOW(), '各工作场景专项培训'
WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'risk_food_safety_campaign_type' 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', '商品标签标识规范培训', '商品标签标识规范培训', 'risk_food_safety_campaign_type', '', 'warning', 'N', 103, 1, NOW(), '商品标签标识规范培训'
WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'risk_food_safety_campaign_type' 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 4, '000000', '客诉重点防范方面培训', '客诉重点防范方面培训', 'risk_food_safety_campaign_type', '', 'info', 'N', 103, 1, NOW(), '客诉重点防范方面培训'
WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'risk_food_safety_campaign_type' 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 5, '000000', '检测技能及其他专项培训', '检测技能及其他专项培训', 'risk_food_safety_campaign_type', '', 'danger', 'N', 103, 1, NOW(), '检测技能及其他专项培训'
WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'risk_food_safety_campaign_type' AND `dict_value` = '检测技能及其他专项培训');

-- 7. 字典数据:活动状态(3 项)
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', '未开始', '未开始', 'risk_food_safety_campaign_status', '', 'info', 'N', 103, 1, NOW(), '活动创建初始状态'
WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'risk_food_safety_campaign_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', '进行中', '进行中', 'risk_food_safety_campaign_status', '', 'primary', 'N', 103, 1, NOW(), '点击开始活动后状态变更为进行中'
WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'risk_food_safety_campaign_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', '已结束', '已结束', 'risk_food_safety_campaign_status', '', 'success', 'N', 103, 1, NOW(), '点击活动结束并填写总结后状态变更为已结束'
WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'risk_food_safety_campaign_status' AND `dict_value` = '已结束');

-- 8. 字典数据:资料类型(3 项)
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', '图文海报类', '图文海报类', 'risk_food_safety_material_type', '', 'primary', 'N', 103, 1, NOW(), '图文海报类宣传资料'
WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'risk_food_safety_material_type' 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', '培训手册', '培训手册', 'risk_food_safety_material_type', '', 'success', 'N', 103, 1, NOW(), '培训手册类宣传资料'
WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'risk_food_safety_material_type' 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', '视频音频类', '视频音频类', 'risk_food_safety_material_type', '', 'warning', 'N', 103, 1, NOW(), '视频音频类宣传资料'
WHERE NOT EXISTS (SELECT 1 FROM `sys_dict_data` WHERE `dict_type` = 'risk_food_safety_material_type' AND `dict_value` = '视频音频类');

-- 9. 验证
SELECT 'risk_food_safety_campaign' AS tbl, COUNT(*) AS cnt FROM `risk_food_safety_campaign`
UNION ALL
SELECT 'risk_food_safety_material', COUNT(*) FROM `risk_food_safety_material`
UNION ALL
SELECT 'risk_food_safety_campaign_type', COUNT(*) FROM `sys_dict_data` WHERE `dict_type` = 'risk_food_safety_campaign_type'
UNION ALL
SELECT 'risk_food_safety_campaign_status', COUNT(*) FROM `sys_dict_data` WHERE `dict_type` = 'risk_food_safety_campaign_status'
UNION ALL
SELECT 'risk_food_safety_material_type', COUNT(*) FROM `sys_dict_data` WHERE `dict_type` = 'risk_food_safety_material_type';