-- ----------------------------
-- AEGIS 系统数据库脚本
-- ----------------------------

-- 设置连接字符集为utf8mb4
SET NAMES utf8mb4;
SET CHARACTER_SET_CLIENT = utf8mb4;
SET CHARACTER_SET_CONNECTION = utf8mb4;
SET CHARACTER_SET_RESULTS = utf8mb4;

USE `aegis`;

-- ----------------------------
-- 资源模块 - 队伍管理相关表
-- ----------------------------

-- 1、应急队伍表
DROP TABLE IF EXISTS aegis.`resource_team`;
CREATE TABLE `resource_team` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '队伍ID',
  `team_name` varchar(100) NOT NULL COMMENT '队伍名称',
  `team_type` varchar(32) DEFAULT NULL COMMENT '队伍类型',
  `belong_org_name` varchar(128) DEFAULT NULL COMMENT '所属单位名称（交通委/公安/消防/医疗/社会企业等）',
  `belong_org_type` varchar(32) DEFAULT NULL COMMENT '所属单位类型：transportation/police/firefighting/medical/emergency/company/other',
  `external_flag` char(1) DEFAULT 'Y' COMMENT '是否外部单位：Y-外部联动单位，N-本系统内部单位',
  `status` varchar(16) DEFAULT 'available' COMMENT '状态：available-可用，busy-占用，offline-停用',
  `capabilities` varchar(500) DEFAULT NULL COMMENT '能力标签（JSON数组）',
  `contact_person` varchar(64) DEFAULT NULL COMMENT '联系人',
  `contact_phone` varchar(20) DEFAULT NULL COMMENT '联系电话',
  `contact_address` varchar(255) DEFAULT NULL COMMENT '联系地址',
  `location_name` varchar(128) DEFAULT NULL COMMENT '驻扎点/常驻位置名称（如XX收费站）',
  `longitude` decimal(10,7) DEFAULT NULL COMMENT '经度',
  `latitude` decimal(10,7) DEFAULT NULL COMMENT '纬度',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_team_name` (`team_name`),
  KEY `idx_status` (`status`),
  KEY `idx_org_type` (`belong_org_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='应急队伍表';

-- 2、队伍成员表
DROP TABLE IF EXISTS aegis.`resource_team_member`;
CREATE TABLE `resource_team_member` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '成员ID',
  `team_id` bigint(20) NOT NULL COMMENT '队伍ID',
  `user_id` bigint(20) DEFAULT NULL COMMENT '关联的系统用户ID',
  `member_name` varchar(64) NOT NULL COMMENT '成员姓名',
  `member_code` varchar(50) DEFAULT NULL COMMENT '成员编号',
  `id_card` varchar(18) DEFAULT NULL COMMENT '身份证号',
  `phone` varchar(20) DEFAULT NULL COMMENT '联系电话',
  `position` varchar(64) DEFAULT NULL COMMENT '职位/角色',
  `specialty` varchar(200) DEFAULT NULL COMMENT '专长',
  `status` varchar(16) DEFAULT 'active' COMMENT '状态：active-在岗，leave-请假，retired-退休',
  `join_date` date DEFAULT NULL COMMENT '加入日期',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_team_id` (`team_id`),
  KEY `idx_member_name` (`member_name`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='队伍成员表';

-- 3、队伍操作日志表
DROP TABLE IF EXISTS aegis.`resource_team_log`;
CREATE TABLE `resource_team_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '日志ID',
  `team_id` bigint(20) NOT NULL COMMENT '队伍ID',
  `action` varchar(32) NOT NULL COMMENT '操作类型：create/update/member_add/member_update/member_remove/status_change/requisition_start/requisition_end/dispatch_assign/dispatch_release',
  `from_status` varchar(16) DEFAULT NULL COMMENT '原状态',
  `to_status` varchar(16) DEFAULT NULL COMMENT '新状态',
  `member_id` bigint(20) DEFAULT NULL COMMENT '成员ID（操作成员时）',
  `requisition_id` bigint(20) DEFAULT NULL COMMENT '征用ID（操作征用时）',
  `dispatch_id` bigint(20) DEFAULT NULL COMMENT '调度ID（操作调度时）',
  `event_id` bigint(20) DEFAULT NULL COMMENT '事件ID（操作事件时）',
  `diff` text COMMENT '变更内容（JSON格式）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `operator_id` bigint(20) DEFAULT NULL COMMENT '操作人ID',
  `operator_name` varchar(64) DEFAULT NULL COMMENT '操作人姓名',
  `request_id` varchar(64) DEFAULT NULL COMMENT '请求ID（用于追踪）',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_team_id` (`team_id`),
  KEY `idx_action` (`action`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='队伍操作日志表';

-- 4、队伍所属单位类型字典
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '队伍所属单位类型', 'team_org_type', '0', 'admin', sysdate(), '资源队伍所属单位类型'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'team_org_type');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '交通委', 'transportation', 'team_org_type', '', 'primary', 'N', '0', 'admin', sysdate(), '交通运输委员会'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'team_org_type' AND dict_value = 'transportation');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '公安', 'police', 'team_org_type', '', 'info', 'N', '0', 'admin', sysdate(), '公安机关'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'team_org_type' AND dict_value = 'police');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 3, '消防救援', 'firefighting', 'team_org_type', '', 'danger', 'N', '0', 'admin', sysdate(), '消防救援机构'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'team_org_type' AND dict_value = 'firefighting');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 4, '医疗急救', 'medical', 'team_org_type', '', 'success', 'N', '0', 'admin', sysdate(), '医疗卫生/急救机构'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'team_org_type' AND dict_value = 'medical');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 5, '应急管理', 'emergency', 'team_org_type', '', 'warning', 'N', '0', 'admin', sysdate(), '应急管理部门'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'team_org_type' AND dict_value = 'emergency');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 6, '社会企业', 'company', 'team_org_type', '', 'primary', 'N', '0', 'admin', sysdate(), '社会企业/志愿组织'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'team_org_type' AND dict_value = 'company');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 7, '其他', 'other', 'team_org_type', '', 'default', 'N', '0', 'admin', sysdate(), '其他单位'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'team_org_type' AND dict_value = 'other');

-- ----------------------------
-- 预案管理模块
-- ----------------------------

DROP TABLE IF EXISTS aegis.`plan`;
CREATE TABLE `plan` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '预案ID',
  `plan_no` varchar(64) NOT NULL COMMENT '预案编号',
  `plan_name` varchar(200) NOT NULL COMMENT '预案名称',
  `plan_type` varchar(32) DEFAULT NULL COMMENT '预案类型：overall/special',
  `event_type` varchar(64) DEFAULT NULL COMMENT '适用事件类型',
  `event_level` varchar(16) DEFAULT NULL COMMENT '适用事件级别',
  `version` varchar(32) DEFAULT NULL COMMENT '版本号',
  `status` varchar(32) DEFAULT 'draft' COMMENT '状态：draft/approving/published/archived',
  `is_current` tinyint(1) DEFAULT 1 COMMENT '是否当前版本',
  `description` text COMMENT '预案描述',
  `creator_id` bigint(20) DEFAULT NULL COMMENT '创建人ID',
  `approver_id` bigint(20) DEFAULT NULL COMMENT '审批人ID',
  `approval_time` datetime DEFAULT NULL COMMENT '审批时间',
  `publish_time` datetime DEFAULT NULL COMMENT '发布时间',
  `usage_count` int(11) DEFAULT 0 COMMENT '使用次数',
  `last_used_at` datetime DEFAULT NULL COMMENT '最后使用时间',
  `average_rating` decimal(10,2) DEFAULT NULL COMMENT '平均评分',
  `referenced_workbench` varchar(32) DEFAULT NULL COMMENT '最近引用来源：dispatch/disposal',
  `last_feedback_summary` varchar(500) DEFAULT NULL COMMENT '最近反馈摘要',
  `change_log` text COMMENT '变更说明',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志',
  PRIMARY KEY (`id`),
  KEY `idx_plan_no` (`plan_no`),
  KEY `idx_plan_status` (`status`),
  KEY `idx_plan_event_type` (`event_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='应急预案主表';

DROP TABLE IF EXISTS aegis.`plan_version`;
CREATE TABLE `plan_version` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '版本记录ID',
  `plan_id` bigint(20) NOT NULL COMMENT '关联预案ID',
  `version` varchar(32) NOT NULL COMMENT '版本号',
  `parent_plan_id` bigint(20) DEFAULT NULL COMMENT '父版本预案ID',
  `change_log` text COMMENT '变更说明',
  `snapshot` json DEFAULT NULL COMMENT '预案快照JSON',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_plan_version_plan` (`plan_id`),
  KEY `idx_plan_version_parent` (`parent_plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='预案版本记录表';

DROP TABLE IF EXISTS aegis.`plan_approval`;
CREATE TABLE `plan_approval` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '审批记录ID',
  `plan_id` bigint(20) NOT NULL COMMENT '预案ID',
  `approver_id` bigint(20) DEFAULT NULL COMMENT '审批人ID',
  `approver_name` varchar(64) DEFAULT NULL COMMENT '审批人姓名',
  `approval_status` varchar(16) DEFAULT NULL COMMENT '审批状态：approved/rejected',
  `approval_comment` varchar(500) DEFAULT NULL COMMENT '审批意见',
  `approval_time` datetime DEFAULT NULL COMMENT '审批时间',
  `approval_order` int(11) DEFAULT 1 COMMENT '审批顺序',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志',
  PRIMARY KEY (`id`),
  KEY `idx_plan_approval_plan` (`plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='预案审批记录表';

DROP TABLE IF EXISTS aegis.`plan_participant`;
CREATE TABLE `plan_participant` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '模板ID',
  `plan_id` bigint(20) NOT NULL COMMENT '预案ID',
  `participant_type` varchar(32) NOT NULL COMMENT '执行对象类型：TEAM_MEMBER/EXPERT',
  `reference_team_id` bigint(20) DEFAULT NULL COMMENT '队伍ID（团队成员）',
  `reference_member_id` bigint(20) DEFAULT NULL COMMENT '队伍成员ID',
  `reference_expert_id` bigint(20) DEFAULT NULL COMMENT '专家ID',
  `display_name` varchar(200) DEFAULT NULL COMMENT '展示名称',
  `role` varchar(200) DEFAULT NULL COMMENT '角色/职责',
  `contact_snapshot` text COMMENT '联系方式快照（JSON）',
  `sort_order` int(11) DEFAULT 0 COMMENT '排序',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志',
  PRIMARY KEY (`id`),
  KEY `idx_plan_participant_plan` (`plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='预案执行对象模板表';

DROP TABLE IF EXISTS aegis.`plan_step`;
CREATE TABLE `plan_step` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '行动项模板ID',
  `plan_id` bigint(20) NOT NULL COMMENT '预案ID',
  `step_name` varchar(200) NOT NULL COMMENT '行动项名称',
  `step_order` int(11) DEFAULT NULL COMMENT '顺序',
  `description` text COMMENT '行动项描述',
  `suggested_participant_id` bigint(20) DEFAULT NULL COMMENT '建议执行方模板ID',
  `estimated_duration` int(11) DEFAULT NULL COMMENT '预计耗时（分钟）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志',
  PRIMARY KEY (`id`),
  KEY `idx_plan_step_plan` (`plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='预案行动项模板表';

DROP TABLE IF EXISTS aegis.`plan_step_resource`;
CREATE TABLE `plan_step_resource` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '关联ID',
  `plan_id` bigint(20) NOT NULL COMMENT '预案ID',
  `step_id` bigint(20) NOT NULL COMMENT '行动项ID',
  `resource_type` varchar(32) NOT NULL COMMENT '资源类型：material/equipment/vehicle',
  `resource_ref_id` bigint(20) DEFAULT NULL COMMENT '资源ID',
  `suggested_quantity` int(11) DEFAULT NULL COMMENT '建议数量',
  `required` tinyint(1) DEFAULT 0 COMMENT '是否必需',
  `usage_note` varchar(500) DEFAULT NULL COMMENT '使用说明',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志',
  PRIMARY KEY (`id`),
  KEY `idx_plan_step_resource_plan` (`plan_id`),
  KEY `idx_plan_step_resource_step` (`step_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='行动项资源模板表';

DROP TABLE IF EXISTS aegis.`plan_usage`;
CREATE TABLE `plan_usage` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '使用记录ID',
  `plan_id` bigint(20) NOT NULL COMMENT '预案ID',
  `dispatch_task_id` bigint(20) DEFAULT NULL COMMENT '调度任务ID',
  `source` varchar(32) DEFAULT NULL COMMENT '来源：dispatch/disposal',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注/反馈',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志',
  PRIMARY KEY (`id`),
  KEY `idx_plan_usage_plan` (`plan_id`),
  KEY `idx_plan_usage_dispatch` (`dispatch_task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='预案使用记录表';

-- ----------------------------
-- 资源模块 - 仓库管理相关表
-- ----------------------------

-- 1、仓库表
DROP TABLE IF EXISTS aegis.`resource_warehouse`;
CREATE TABLE `resource_warehouse` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '仓库ID',
  `warehouse_code` varchar(50) NOT NULL COMMENT '仓库编码',
  `warehouse_name` varchar(100) NOT NULL COMMENT '仓库名称',
  `warehouse_type` varchar(32) DEFAULT NULL COMMENT '仓库类型（危化装备/排水设备/交通物资等）',
  `belong_org_name` varchar(128) DEFAULT NULL COMMENT '所属单位名称',
  `belong_org_type` varchar(32) DEFAULT NULL COMMENT '所属单位类型（交通委/应急局/市政/企业等）',
  `address` varchar(500) DEFAULT NULL COMMENT '详细地址',
  `longitude` decimal(10,7) DEFAULT NULL COMMENT '经度（WGS-84）',
  `latitude` decimal(10,7) DEFAULT NULL COMMENT '纬度（WGS-84）',
  `manager` varchar(64) DEFAULT NULL COMMENT '负责人',
  `contact_phone` varchar(20) DEFAULT NULL COMMENT '联系电话',
  `capacity` decimal(10,2) DEFAULT NULL COMMENT '容量（平方米或立方米）',
  `status` char(1) DEFAULT '0' COMMENT '状态（0正常 1停用）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_warehouse_code` (`warehouse_code`),
  KEY `idx_warehouse_name` (`warehouse_name`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='仓库表';

-- ----------------------------
-- 资源模块 - 车辆管理相关表
-- ----------------------------

-- 1、车辆表
DROP TABLE IF EXISTS aegis.`resource_vehicle`;
CREATE TABLE `resource_vehicle` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '车辆ID',
  `vehicle_name` varchar(100) NOT NULL COMMENT '车辆名称',
  `plate_no` varchar(32) DEFAULT NULL COMMENT '车牌号/编号',
  `vehicle_type` varchar(32) DEFAULT NULL COMMENT '车辆类型',
  `team_id` bigint(20) DEFAULT NULL COMMENT '所属队伍ID',
  `status` varchar(16) DEFAULT 'available' COMMENT '状态：available-可用，busy-占用，maintenance-检修，offline-停用，scrap-报废',
  `capabilities` varchar(500) DEFAULT NULL COMMENT '能力标签（JSON数组）',
  `device_id` varchar(64) DEFAULT NULL COMMENT '定位设备ID',
  `device_provider` varchar(32) DEFAULT NULL COMMENT '设备提供商/协议类型',
  `gps_lat` decimal(10,7) DEFAULT NULL COMMENT '最新定位纬度',
  `gps_lng` decimal(10,7) DEFAULT NULL COMMENT '最新定位经度',
  `last_seen_at` datetime DEFAULT NULL COMMENT '最后定位时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_plate_no` (`plate_no`),
  KEY `idx_team_id` (`team_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='车辆表';

-- ----------------------------
-- 资源模块 - 专家管理相关表
-- ----------------------------

-- 1、专家表
DROP TABLE IF EXISTS aegis.`resource_expert`;
CREATE TABLE `resource_expert` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '专家ID',
  `expert_name` varchar(64) NOT NULL COMMENT '专家姓名',
  `expert_code` varchar(50) DEFAULT NULL COMMENT '专家编号',
  `id_card` varchar(18) DEFAULT NULL COMMENT '身份证号',
  `organization` varchar(200) DEFAULT NULL COMMENT '所属单位',
  `specialties` varchar(500) DEFAULT NULL COMMENT '专长领域（JSON数组）',
  `phone` varchar(20) DEFAULT NULL COMMENT '联系电话',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `sys_user_id` bigint(20) DEFAULT NULL COMMENT '关联系统用户ID',
  `status` varchar(16) DEFAULT 'active' COMMENT '状态：active-启用，inactive-停用，blacklisted-黑名单',
  `rating` int(1) DEFAULT 5 COMMENT '评分（1-5）',
  `capabilities` varchar(500) DEFAULT NULL COMMENT '能力标签（JSON数组）',
  `contacts` text COMMENT '联系方式（JSON格式）',
  `location_name` varchar(255) DEFAULT NULL COMMENT '常驻地点名称/地址描述',
  `longitude` decimal(10,7) DEFAULT NULL COMMENT '经度',
  `latitude` decimal(10,7) DEFAULT NULL COMMENT '纬度',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_expert_code` (`expert_code`),
  UNIQUE KEY `uk_resource_expert_sys_user` (`sys_user_id`),
  KEY `idx_expert_name` (`expert_name`),
  KEY `idx_status` (`status`),
  KEY `idx_rating` (`rating`),
  CONSTRAINT `fk_resource_expert_sys_user` FOREIGN KEY (`sys_user_id`) REFERENCES `sys_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='专家表';

-- ----------------------------
-- 资源模块 - 物资分类管理相关表
-- ----------------------------

-- 1、物资分类表
DROP TABLE IF EXISTS aegis.`resource_material_category`;
CREATE TABLE `resource_material_category` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '分类ID',
  `parent_id` bigint(20) DEFAULT 0 COMMENT '父分类ID（0表示顶级）',
  `ancestors` varchar(500) DEFAULT '' COMMENT '祖级列表',
  `category_code` varchar(50) NOT NULL COMMENT '分类编码',
  `category_name` varchar(100) NOT NULL COMMENT '分类名称',
  `order_num` int(4) DEFAULT 0 COMMENT '显示顺序',
  `status` char(1) DEFAULT '0' COMMENT '状态（0正常 1停用）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_category_code` (`category_code`),
  KEY `idx_parent_id` (`parent_id`),
  KEY `idx_category_name` (`category_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='物资分类表';

-- ----------------------------
-- 资源模块 - 物资管理相关表
-- ----------------------------

-- 1、物资表
DROP TABLE IF EXISTS aegis.`resource_material`;
CREATE TABLE `resource_material` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '物资ID',
  `material_code` varchar(50) NOT NULL COMMENT '物资编码',
  `material_name` varchar(200) NOT NULL COMMENT '物资名称',
  `category_id` bigint(20) DEFAULT NULL COMMENT '分类ID',
  `unit` varchar(16) DEFAULT NULL COMMENT '单位',
  `spec` varchar(200) DEFAULT NULL COMMENT '规格',
  `brand` varchar(100) DEFAULT NULL COMMENT '品牌',
  `batch_no` varchar(64) DEFAULT NULL COMMENT '批次号',
  `expire_date` date DEFAULT NULL COMMENT '过期日期',
  `reusable` tinyint(1) DEFAULT 0 COMMENT '是否可重复使用：0-否，1-是',
  `recycle_policy` varchar(200) DEFAULT NULL COMMENT '回收政策',
  `sterilize_required` tinyint(1) DEFAULT 0 COMMENT '是否需要消毒：0-否，1-是',
  `threshold` decimal(10,2) DEFAULT 0.00 COMMENT '报警阈值',
  `locked_qty` decimal(10,2) DEFAULT 0.00 COMMENT '锁定数量',
  `used_qty` decimal(10,2) DEFAULT 0.00 COMMENT '已使用数量',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_material_code` (`material_code`),
  KEY `idx_category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='物资表';

-- 2、物资库存表
DROP TABLE IF EXISTS aegis.`resource_material_inventory`;
CREATE TABLE `resource_material_inventory` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '库存ID',
  `material_id` bigint(20) NOT NULL COMMENT '物资ID',
  `warehouse_id` bigint(20) DEFAULT NULL COMMENT '仓库ID',
  `total_qty` decimal(10,2) DEFAULT 0.00 COMMENT '总数量',
  `available_qty` decimal(10,2) DEFAULT 0.00 COMMENT '可用数量',
  `locked_qty` decimal(10,2) DEFAULT 0.00 COMMENT '锁定数量',
  `used_qty` decimal(10,2) DEFAULT 0.00 COMMENT '已使用数量',
  `version` int(11) DEFAULT 1 COMMENT '版本号（乐观锁）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_material_warehouse` (`material_id`, `warehouse_id`),
  KEY `idx_material_id` (`material_id`),
  KEY `idx_warehouse_id` (`warehouse_id`),
  KEY `idx_inventory_alert` (`warehouse_id`, `material_id`, `available_qty`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='物资库存表';

-- 3、物资使用记录表
DROP TABLE IF EXISTS aegis.`resource_material_usage`;
CREATE TABLE `resource_material_usage` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '使用记录ID',
  `material_id` bigint(20) NOT NULL COMMENT '物资ID',
  `warehouse_id` bigint(20) DEFAULT NULL COMMENT '仓库ID',
  `event_id` bigint(20) DEFAULT NULL COMMENT '事件ID',
  `task_id` bigint(20) DEFAULT NULL COMMENT '任务ID',
  `qty` decimal(10,2) NOT NULL COMMENT '数量',
  `action` varchar(16) NOT NULL COMMENT '操作类型：lock-占用，unlock-释放，consume-消耗，return-归还',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_material_id` (`material_id`),
  KEY `idx_warehouse_id` (`warehouse_id`),
  KEY `idx_event_id` (`event_id`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_action_created_at` (`action`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='物资使用记录表';

-- ----------------------------
-- 资源模块 - 专家专长字典数据
-- ----------------------------

-- 1、创建专家专长字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '专家专长', 'expert_specialty', '0', 'admin', sysdate(), '专家专长领域列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'expert_specialty');

-- 2、添加专家专长字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '应急救援', 'emergency_rescue', 'expert_specialty', '', 'primary', 'N', '0', 'admin', sysdate(), '应急救援'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'expert_specialty' AND dict_value = 'emergency_rescue');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '医疗救护', 'medical_rescue', 'expert_specialty', '', 'success', 'N', '0', 'admin', sysdate(), '医疗救护'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'expert_specialty' AND dict_value = 'medical_rescue');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 3, '消防', 'fire_fighting', 'expert_specialty', '', 'danger', 'N', '0', 'admin', sysdate(), '消防'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'expert_specialty' AND dict_value = 'fire_fighting');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 4, '通信', 'communication', 'expert_specialty', '', 'info', 'N', '0', 'admin', sysdate(), '通信'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'expert_specialty' AND dict_value = 'communication');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 5, '电力', 'power', 'expert_specialty', '', 'warning', 'N', '0', 'admin', sysdate(), '电力'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'expert_specialty' AND dict_value = 'power');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 6, '水利', 'water_conservancy', 'expert_specialty', '', 'primary', 'N', '0', 'admin', sysdate(), '水利'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'expert_specialty' AND dict_value = 'water_conservancy');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 7, '地质', 'geology', 'expert_specialty', '', 'success', 'N', '0', 'admin', sysdate(), '地质'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'expert_specialty' AND dict_value = 'geology');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 8, '气象', 'meteorology', 'expert_specialty', '', 'info', 'N', '0', 'admin', sysdate(), '气象'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'expert_specialty' AND dict_value = 'meteorology');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 9, '交通', 'transportation', 'expert_specialty', '', 'warning', 'N', '0', 'admin', sysdate(), '交通'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'expert_specialty' AND dict_value = 'transportation');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 10, '建筑', 'construction', 'expert_specialty', '', 'primary', 'N', '0', 'admin', sysdate(), '建筑'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'expert_specialty' AND dict_value = 'construction');

-- ----------------------------
-- 资源模块 - 专家状态字典数据
-- ----------------------------

-- 1、创建专家状态字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '专家状态', 'expert_status', '0', 'admin', sysdate(), '专家状态列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'expert_status');

-- 2、添加专家状态字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '启用', 'active', 'expert_status', '', 'success', 'Y', '0', 'admin', sysdate(), '专家启用状态'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'expert_status' AND dict_value = 'active');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '停用', 'inactive', 'expert_status', '', 'info', 'N', '0', 'admin', sysdate(), '专家停用状态'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'expert_status' AND dict_value = 'inactive');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 3, '黑名单', 'blacklisted', 'expert_status', '', 'danger', 'N', '0', 'admin', sysdate(), '专家黑名单状态'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'expert_status' AND dict_value = 'blacklisted');

-- ----------------------------
-- 资源模块 - 装备管理字典数据
-- ----------------------------

-- 1、创建装备状态字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '装备状态', 'equipment_status', '0', 'admin', sysdate(), '装备状态列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'equipment_status');

-- 2、添加装备状态字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '可用', 'available', 'equipment_status', '', 'success', 'Y', '0', 'admin', sysdate(), '装备可用状态'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'equipment_status' AND dict_value = 'available');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '占用', 'busy', 'equipment_status', '', 'warning', 'N', '0', 'admin', sysdate(), '装备占用状态'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'equipment_status' AND dict_value = 'busy');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 3, '维修', 'repair', 'equipment_status', '', 'info', 'N', '0', 'admin', sysdate(), '装备维修状态'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'equipment_status' AND dict_value = 'repair');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 4, '报废', 'scrap', 'equipment_status', '', 'danger', 'N', '0', 'admin', sysdate(), '装备报废状态（终态）'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'equipment_status' AND dict_value = 'scrap');

-- 3、创建装备类型字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '装备类型', 'equipment_type', '0', 'admin', sysdate(), '装备类型列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'equipment_type');

-- 4、添加装备类型字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '通信设备', 'communication', 'equipment_type', '', 'info', 'Y', '0', 'admin', sysdate(), '通信类装备'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'equipment_type' AND dict_value = 'communication');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '救援设备', 'rescue', 'equipment_type', '', 'danger', 'N', '0', 'admin', sysdate(), '救援类装备'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'equipment_type' AND dict_value = 'rescue');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 3, '医疗设备', 'medical', 'equipment_type', '', 'success', 'N', '0', 'admin', sysdate(), '医疗类装备'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'equipment_type' AND dict_value = 'medical');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 4, '工程设备', 'engineering', 'equipment_type', '', 'warning', 'N', '0', 'admin', sysdate(), '工程类装备'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'equipment_type' AND dict_value = 'engineering');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 5, '运输设备', 'transportation', 'equipment_type', '', 'primary', 'N', '0', 'admin', sysdate(), '运输类装备'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'equipment_type' AND dict_value = 'transportation');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 6, '监测设备', 'monitoring', 'equipment_type', '', 'info', 'N', '0', 'admin', sysdate(), '监测类装备'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'equipment_type' AND dict_value = 'monitoring');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 7, '其他', 'other', 'equipment_type', '', '', 'N', '0', 'admin', sysdate(), '其他类型装备'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'equipment_type' AND dict_value = 'other');

-- ----------------------------
-- 资源模块 - 仓库管理字典数据
-- ----------------------------

-- 1、创建仓库状态字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '仓库状态', 'warehouse_status', '0', 'admin', sysdate(), '仓库状态列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'warehouse_status');

-- 2、添加仓库状态字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '正常', '0', 'warehouse_status', '', 'success', 'Y', '0', 'admin', sysdate(), '仓库正常状态'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'warehouse_status' AND dict_value = '0');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '停用', '1', 'warehouse_status', '', 'danger', 'N', '0', 'admin', sysdate(), '仓库停用状态'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'warehouse_status' AND dict_value = '1');

-- 3、创建仓库类型字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '仓库类型', 'warehouse_type', '0', 'admin', sysdate(), '仓库类型列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'warehouse_type');

-- 4、添加仓库类型字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '中心仓库', 'center', 'warehouse_type', '', 'primary', 'Y', '0', 'admin', sysdate(), '中心仓库'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'warehouse_type' AND dict_value = 'center');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '临时仓库', 'temporary', 'warehouse_type', '', 'warning', 'N', '0', 'admin', sysdate(), '临时仓库'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'warehouse_type' AND dict_value = 'temporary');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 3, '应急仓库', 'emergency', 'warehouse_type', '', 'danger', 'N', '0', 'admin', sysdate(), '应急仓库'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'warehouse_type' AND dict_value = 'emergency');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 4, '储备仓库', 'reserve', 'warehouse_type', '', 'info', 'N', '0', 'admin', sysdate(), '储备仓库'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'warehouse_type' AND dict_value = 'reserve');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 5, '其他', 'other', 'warehouse_type', '', '', 'N', '0', 'admin', sysdate(), '其他类型仓库'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'warehouse_type' AND dict_value = 'other');

-- ----------------------------
-- 资源模块 - 装备管理相关表
-- ----------------------------

-- 1、装备表
DROP TABLE IF EXISTS aegis.`resource_equipment`;
CREATE TABLE `resource_equipment` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '装备ID',
  `equipment_code` varchar(50) NOT NULL COMMENT '装备编码',
  `equipment_name` varchar(200) NOT NULL COMMENT '装备名称',
  `equipment_type` varchar(64) DEFAULT NULL COMMENT '装备类型',
  `brand` varchar(100) DEFAULT NULL COMMENT '品牌',
  `model` varchar(100) DEFAULT NULL COMMENT '型号',
  `team_id` bigint(20) DEFAULT NULL COMMENT '所属队伍ID',
  `warehouse_id` bigint(20) DEFAULT NULL COMMENT '所属仓库ID',
  `status` varchar(16) DEFAULT 'available' COMMENT '状态：available-可用，busy-占用，repair-维修，scrap-报废',
  `capabilities` varchar(500) DEFAULT NULL COMMENT '能力标签（JSON数组）',
  `purchase_date` date DEFAULT NULL COMMENT '采购日期',
  `warranty_date` date DEFAULT NULL COMMENT '保修到期日期',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_equipment_code` (`equipment_code`),
  KEY `idx_equipment_name` (`equipment_name`),
  KEY `idx_team_id` (`team_id`),
  KEY `idx_warehouse_id` (`warehouse_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='装备表';

-- 2、装备操作日志表
DROP TABLE IF EXISTS aegis.`resource_equipment_log`;
CREATE TABLE `resource_equipment_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '日志ID',
  `equipment_id` bigint(20) NOT NULL COMMENT '装备ID',
  `action` varchar(32) NOT NULL COMMENT '操作类型：assign/release/send_repair/finish_repair/scrap',
  `from_status` varchar(16) DEFAULT NULL COMMENT '原状态',
  `to_status` varchar(16) DEFAULT NULL COMMENT '新状态',
  `dispatch_id` bigint(20) DEFAULT NULL COMMENT '调度ID（操作调度时）',
  `event_id` bigint(20) DEFAULT NULL COMMENT '事件ID（操作事件时）',
  `diff` text COMMENT '变更内容（JSON格式）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `operator_id` bigint(20) DEFAULT NULL COMMENT '操作人ID',
  `operator_name` varchar(64) DEFAULT NULL COMMENT '操作人姓名',
  `request_id` varchar(64) DEFAULT NULL COMMENT '请求ID（用于追踪）',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_equipment_id` (`equipment_id`),
  KEY `idx_action` (`action`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_action_created_at` (`action`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='装备操作日志表';

-- ----------------------------
-- 资源模块 - 供应商管理相关表
-- ----------------------------

-- 1、供应商表
DROP TABLE IF EXISTS aegis.`resource_supplier`;
CREATE TABLE `resource_supplier` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '供应商ID',
  `supplier_code` varchar(50) NOT NULL COMMENT '供应商编码',
  `supplier_name` varchar(200) NOT NULL COMMENT '供应商名称',
  `credit_code` varchar(64) DEFAULT NULL COMMENT '统一社会信用代码',
  `category` varchar(64) DEFAULT NULL COMMENT '供应商分类',
  `area` varchar(100) DEFAULT NULL COMMENT '所在区域',
  `contact_person` varchar(64) DEFAULT NULL COMMENT '联系人',
  `contact_phone` varchar(20) DEFAULT NULL COMMENT '联系电话',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `address` varchar(500) DEFAULT NULL COMMENT '地址',
  `status` varchar(16) DEFAULT 'active' COMMENT '状态：active-启用，inactive-停用，blacklisted-黑名单',
  `rating` int(1) DEFAULT 5 COMMENT '评分（1-5星）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_supplier_code` (`supplier_code`),
  KEY `idx_supplier_name` (`supplier_name`),
  KEY `idx_category` (`category`),
  KEY `idx_status` (`status`),
  KEY `idx_rating` (`rating`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='供应商表';

-- 2、供应商资质表
DROP TABLE IF EXISTS aegis.`resource_supplier_license`;
CREATE TABLE `resource_supplier_license` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '资质ID',
  `supplier_id` bigint(20) NOT NULL COMMENT '供应商ID',
  `license_type` varchar(100) NOT NULL COMMENT '资质类型',
  `license_no` varchar(100) DEFAULT NULL COMMENT '资质编号',
  `issuing_authority` varchar(200) DEFAULT NULL COMMENT '发证机关',
  `valid_from` date DEFAULT NULL COMMENT '有效期开始日期',
  `valid_to` date DEFAULT NULL COMMENT '有效期结束日期',
  `attachment_url` varchar(500) DEFAULT NULL COMMENT '附件URL（文件ID）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_supplier_id` (`supplier_id`),
  KEY `idx_license_type` (`license_type`),
  KEY `idx_valid_to` (`valid_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='供应商资质表';

-- ----------------------------
-- 资源模块 - 摄像头管理相关表
-- ----------------------------

-- 摄像头表
DROP TABLE IF EXISTS aegis.`camera`;
CREATE TABLE `camera` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '摄像头ID',
  `camera_code` varchar(64) NOT NULL COMMENT '摄像头编码/设备ID',
  `camera_name` varchar(100) NOT NULL COMMENT '摄像头名称',
  `protocol` varchar(32) DEFAULT NULL COMMENT '协议：rtsp/gb28181/rtmp/onvif',
  `org_name` varchar(100) DEFAULT NULL COMMENT '所属机构',
  `longitude` decimal(10,7) DEFAULT NULL COMMENT '经度',
  `latitude` decimal(10,7) DEFAULT NULL COMMENT '纬度',
  `address` varchar(200) DEFAULT NULL COMMENT '地址',
  `status` varchar(16) DEFAULT 'offline' COMMENT '状态：online/offline',
  `extra_config` text COMMENT '扩展配置（JSON字符串）',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_protocol` (`protocol`),
  KEY `idx_status` (`status`),
  KEY `idx_org_name` (`org_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='摄像头表';

-- ----------------------------
-- 资源模块 - 采购管理相关表
-- ----------------------------

-- 1、采购申请表
DROP TABLE IF EXISTS aegis.`resource_procurement`;
CREATE TABLE `resource_procurement` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '采购ID',
  `procurement_no` varchar(50) NOT NULL COMMENT '采购编号',
  `title` varchar(200) NOT NULL COMMENT '采购标题',
  `urgency` varchar(16) DEFAULT 'normal' COMMENT '紧急程度：low-低，normal-普通，high-高，urgent-紧急',
  `status` varchar(16) DEFAULT 'draft' COMMENT '状态：draft-草稿，submitted-已提交，approved-已审核，rejected-已驳回，in_progress-进行中，received-已入库',
  `awarded_supplier_id` bigint(20) DEFAULT NULL COMMENT '中标供应商ID',
  `awarded_at` datetime DEFAULT NULL COMMENT '中标时间',
  `approver_id` bigint(20) DEFAULT NULL COMMENT '审核人ID',
  `approver_name` varchar(64) DEFAULT NULL COMMENT '审核人姓名',
  `approved_at` datetime DEFAULT NULL COMMENT '审核时间',
  `approval_comment` varchar(500) DEFAULT NULL COMMENT '审核意见',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_procurement_no` (`procurement_no`),
  KEY `idx_status` (`status`),
  KEY `idx_urgency` (`urgency`),
  KEY `idx_awarded_supplier_id` (`awarded_supplier_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='采购申请表';

-- 2、采购明细表
DROP TABLE IF EXISTS aegis.`resource_procurement_item`;
CREATE TABLE `resource_procurement_item` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '明细ID',
  `procurement_id` bigint(20) NOT NULL COMMENT '采购申请ID',
  `category` varchar(16) NOT NULL COMMENT '分类：material-物资，equipment-装备',
  `spec` varchar(500) DEFAULT NULL COMMENT '规格描述',
  `qty` decimal(10,2) NOT NULL COMMENT '数量',
  `unit` varchar(16) DEFAULT NULL COMMENT '单位',
  `is_reusable` tinyint(1) DEFAULT 0 COMMENT '是否可重复使用：0-否，1-是',
  `target_material_id` bigint(20) DEFAULT NULL COMMENT '目标物资ID（入库时关联）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_procurement_id` (`procurement_id`),
  KEY `idx_category` (`category`),
  KEY `idx_target_material_id` (`target_material_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='采购明细表';

-- 3、供应商报价表
DROP TABLE IF EXISTS aegis.`resource_procurement_quote`;
CREATE TABLE `resource_procurement_quote` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '报价ID',
  `procurement_id` bigint(20) NOT NULL COMMENT '采购申请ID',
  `supplier_id` bigint(20) NOT NULL COMMENT '供应商ID',
  `total_price` decimal(10,2) NOT NULL COMMENT '总价',
  `eta` varchar(100) DEFAULT NULL COMMENT '预计交货时间',
  `note` varchar(500) DEFAULT NULL COMMENT '报价说明',
  `is_awarded` tinyint(1) DEFAULT 0 COMMENT '是否中标：0-未中标，1-已中标',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_procurement_id` (`procurement_id`),
  KEY `idx_supplier_id` (`supplier_id`),
  KEY `idx_is_awarded` (`is_awarded`),
  UNIQUE KEY `uk_procurement_supplier` (`procurement_id`, `supplier_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='供应商报价表';

-- ----------------------------
-- 资源模块 - 征用管理相关表
-- ----------------------------

-- 1、征用申请表
DROP TABLE IF EXISTS aegis.`resource_requisition`;
CREATE TABLE `resource_requisition` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '征用ID',
  `team_id` bigint(20) NOT NULL COMMENT '队伍ID',
  `requisition_no` varchar(50) NOT NULL COMMENT '征用编号',
  `reason` varchar(500) DEFAULT NULL COMMENT '征用原因',
  `status` varchar(16) DEFAULT 'draft' COMMENT '状态：draft-草稿，approved-已审批，active-进行中，ended-已结束，rejected-已驳回',
  `start_at` datetime DEFAULT NULL COMMENT '开始时间',
  `end_at` datetime DEFAULT NULL COMMENT '结束时间',
  `approver_id` bigint(20) DEFAULT NULL COMMENT '审批人ID',
  `approver_name` varchar(64) DEFAULT NULL COMMENT '审批人姓名',
  `approved_at` datetime DEFAULT NULL COMMENT '审批时间',
  `approval_comment` varchar(500) DEFAULT NULL COMMENT '审批意见',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_requisition_no` (`requisition_no`),
  KEY `idx_team_id` (`team_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='征用申请表';

-- 2、征用协议表
DROP TABLE IF EXISTS aegis.`resource_requisition_agreement`;
CREATE TABLE `resource_requisition_agreement` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '协议ID',
  `requisition_id` bigint(20) NOT NULL COMMENT '征用ID',
  `service_scope` varchar(500) DEFAULT NULL COMMENT '服务范围',
  `billing_type` varchar(16) DEFAULT 'hour' COMMENT '计费方式：hour-按小时，day-按天，task-按任务',
  `unit_price` decimal(10,2) DEFAULT NULL COMMENT '单价',
  `currency` varchar(16) DEFAULT 'CNY' COMMENT '币种',
  `effective_from` datetime DEFAULT NULL COMMENT '生效开始时间',
  `effective_to` datetime DEFAULT NULL COMMENT '生效结束时间',
  `terms` text COMMENT '协议条款',
  `attachment_url` varchar(500) DEFAULT NULL COMMENT '附件URL（文件ID）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_requisition_id` (`requisition_id`),
  KEY `idx_effective_from` (`effective_from`),
  KEY `idx_effective_to` (`effective_to`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='征用协议表';

-- 3、补偿记录表
DROP TABLE IF EXISTS aegis.`resource_requisition_compensation`;
CREATE TABLE `resource_requisition_compensation` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '补偿ID',
  `requisition_id` bigint(20) NOT NULL COMMENT '征用ID',
  `workload` decimal(10,2) DEFAULT NULL COMMENT '工作量（小时/天/任务数）',
  `workload_unit` varchar(16) DEFAULT NULL COMMENT '工作量单位：hour/day/task',
  `unit_price` decimal(10,2) NOT NULL COMMENT '单价',
  `amount` decimal(10,2) NOT NULL COMMENT '金额',
  `currency` varchar(16) DEFAULT 'CNY' COMMENT '币种',
  `invoice_no` varchar(100) DEFAULT NULL COMMENT '发票号',
  `paid_at` datetime DEFAULT NULL COMMENT '支付时间',
  `status` varchar(16) DEFAULT 'pending' COMMENT '状态：pending-待结算，settled-已结算',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_requisition_id` (`requisition_id`),
  KEY `idx_status` (`status`),
  KEY `idx_paid_at` (`paid_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='补偿记录表';

-- ----------------------------
-- 通知模块 - 通知管理相关表
-- ----------------------------

-- 1、通知消息表
DROP TABLE IF EXISTS aegis.`sys_notification`;
CREATE TABLE `sys_notification` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '通知ID',
  `title` varchar(200) NOT NULL COMMENT '通知标题',
  `content` text COMMENT '通知内容',
  `template_id` bigint(20) DEFAULT NULL COMMENT '模板ID（可选）',
  `channel` varchar(16) NOT NULL DEFAULT 'station' COMMENT '通知渠道：station-站内消息，sms-短信，email-邮件，push-推送',
  `receiver_id` bigint(20) NOT NULL COMMENT '接收者用户ID',
  `receiver_name` varchar(64) DEFAULT NULL COMMENT '接收者姓名（冗余字段）',
  `sender_id` bigint(20) DEFAULT NULL COMMENT '发送者用户ID（系统通知为NULL）',
  `sender_name` varchar(64) DEFAULT NULL COMMENT '发送者姓名（冗余字段）',
  `business_type` varchar(64) DEFAULT NULL COMMENT '业务类型：plan_publish-预案发布，plan_approve-预案审批，inventory_alert-库存报警等',
  `business_id` bigint(20) DEFAULT NULL COMMENT '业务ID（如预案ID、事件ID等）',
  `status` varchar(16) DEFAULT 'pending' COMMENT '通知状态：pending-待发送，sent-已发送，read-已读，failed-发送失败',
  `read_time` datetime DEFAULT NULL COMMENT '已读时间',
  `send_time` datetime DEFAULT NULL COMMENT '发送时间',
  `expire_time` datetime DEFAULT NULL COMMENT '过期时间（可选）',
  `priority` varchar(16) DEFAULT 'normal' COMMENT '优先级：low-低，normal-普通，high-高，urgent-紧急',
  `extra_data` text COMMENT '扩展数据（JSON格式）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  PRIMARY KEY (`id`),
  KEY `idx_receiver_id` (`receiver_id`),
  KEY `idx_status` (`status`),
  KEY `idx_channel` (`channel`),
  KEY `idx_business` (`business_type`, `business_id`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通知消息表';

-- 2、通知模板表
DROP TABLE IF EXISTS aegis.`sys_notification_template`;
CREATE TABLE `sys_notification_template` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '模板ID',
  `template_code` varchar(64) NOT NULL COMMENT '模板编码（唯一）',
  `template_name` varchar(100) NOT NULL COMMENT '模板名称',
  `business_type` varchar(64) NOT NULL COMMENT '业务类型：plan_publish-预案发布，plan_approve-预案审批等',
  `channel` varchar(16) NOT NULL COMMENT '通知渠道：station-站内消息，sms-短信，email-邮件',
  `title_template` varchar(200) NOT NULL COMMENT '标题模板（支持变量：${变量名}）',
  `content_template` text NOT NULL COMMENT '内容模板（支持变量：${变量名}）',
  `variables` text COMMENT '模板变量说明（JSON格式）',
  `status` char(1) DEFAULT '0' COMMENT '状态（0正常 1停用）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_template_code` (`template_code`),
  KEY `idx_business_type` (`business_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通知模板表';

-- 3、通知渠道配置表
DROP TABLE IF EXISTS aegis.`sys_notification_channel_config`;
CREATE TABLE `sys_notification_channel_config` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '配置ID',
  `channel` varchar(16) NOT NULL COMMENT '通知渠道：sms-短信，email-邮件',
  `config_key` varchar(64) NOT NULL COMMENT '配置键',
  `config_value` text COMMENT '配置值（加密存储）',
  `description` varchar(200) DEFAULT NULL COMMENT '配置说明',
  `status` char(1) DEFAULT '0' COMMENT '状态（0启用 1停用）',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_channel_key` (`channel`, `config_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通知渠道配置表';

-- ----------------------------
-- 文件模块 - 文件管理相关表
-- ----------------------------

-- 文件信息表
DROP TABLE IF EXISTS aegis.`sys_file_info`;
CREATE TABLE `sys_file_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '文件ID',
  `file_path` varchar(500) NOT NULL COMMENT '文件存储路径（相对路径，如：/profile/upload/2025/11/05/test_123456.pdf）',
  `original_name` varchar(255) NOT NULL COMMENT '原始文件名',
  `file_size` bigint(20) NOT NULL COMMENT '文件大小（字节）',
  `file_type` varchar(64) DEFAULT NULL COMMENT '文件类型（扩展名，如jpg、pdf）',
  `mime_type` varchar(128) DEFAULT NULL COMMENT 'MIME类型',
  `upload_user_id` bigint(20) DEFAULT NULL COMMENT '上传者用户ID',
  `upload_user_name` varchar(64) DEFAULT NULL COMMENT '上传者姓名（冗余字段）',
  `file_category` varchar(64) DEFAULT NULL COMMENT '文件分类：document-文档，image-图片，video-视频等',
  `status` varchar(16) DEFAULT 'normal' COMMENT '文件状态：normal-正常，deleted-已删除',
  `download_count` int(11) DEFAULT 0 COMMENT '下载次数',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  PRIMARY KEY (`id`),
  KEY `idx_file_path` (`file_path`),
  KEY `idx_upload_user` (`upload_user_id`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_status` (`status`),
  KEY `idx_file_category` (`file_category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文件信息表';

-- ----------------------------
-- 应急调度模块 - 值守接警相关表
-- ----------------------------

-- 1、值班排班表
DROP TABLE IF EXISTS aegis.`duty_shift`;
CREATE TABLE `duty_shift` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '排班ID',
  `shift_date` date NOT NULL COMMENT '排班日期',
  `shift_type` varchar(32) DEFAULT NULL COMMENT '班次类型：day-白班，night-夜班，all-全天',
  `start_time` time DEFAULT NULL COMMENT '开始时间',
  `end_time` time DEFAULT NULL COMMENT '结束时间',
  `status` varchar(16) DEFAULT 'active' COMMENT '状态：active-有效，cancelled-已取消',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  PRIMARY KEY (`id`),
  KEY `idx_shift_date` (`shift_date`),
  KEY `idx_shift_type` (`shift_type`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='值班排班表';

-- 1.1、排班成员关联表
DROP TABLE IF EXISTS aegis.`duty_shift_member`;
CREATE TABLE `duty_shift_member` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '关联ID',
  `shift_id` bigint(20) NOT NULL COMMENT '排班ID',
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `role` varchar(32) DEFAULT NULL COMMENT '角色：leader-组长，member-成员',
  `phone` varchar(20) DEFAULT NULL COMMENT '联系电话（冗余字段，记录排班时的联系方式）',
  `check_in_time` datetime DEFAULT NULL COMMENT '签到时间',
  `check_out_time` datetime DEFAULT NULL COMMENT '签退时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_shift_id` (`shift_id`),
  KEY `idx_user_id` (`user_id`),
  UNIQUE KEY `uk_shift_user` (`shift_id`, `user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='排班成员关联表';

-- 2、警情主表
DROP TABLE IF EXISTS aegis.`duty_alert`;
CREATE TABLE `duty_alert` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '警情ID',
  `alert_no` varchar(50) NOT NULL COMMENT '警情编号（唯一）',
  `source_channel` varchar(32) DEFAULT NULL COMMENT '来源渠道（字典：alert_source_channel）',
  `caller_number` varchar(20) DEFAULT NULL COMMENT '来电号码（电话渠道使用：122、12345、110）',
  `source_ext_info` text COMMENT '来源扩展信息（JSON格式，不同渠道存储不同信息：转办渠道-转办单位/转办人/转办时间，监测渠道-设备ID/监测数据，媒体渠道-媒体来源/链接/发布时间等）',
  `summary` varchar(1000) DEFAULT NULL COMMENT '警情摘要',
  `initial_level` varchar(16) DEFAULT NULL COMMENT '初判等级：level1-一级，level2-二级，level3-三级，level4-四级',
  `status` varchar(32) DEFAULT 'draft' COMMENT '警情状态：draft-草稿，pending_review-待审核，reviewed-已审核，transferred-已转警，converted-已转事件，closed-已关闭（审核详情见duty_alert_log，转警详情见duty_alert_transfer，转事件详情见duty_alert_convert）',
  `occurred_at` datetime DEFAULT NULL COMMENT '发生时间',
  `location` varchar(255) DEFAULT NULL COMMENT '发生地点',
  `longitude` decimal(10,7) DEFAULT NULL COMMENT '经度',
  `latitude` decimal(10,7) DEFAULT NULL COMMENT '纬度',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_alert_no` (`alert_no`),
  KEY `idx_source_channel` (`source_channel`),
  KEY `idx_status` (`status`),
  KEY `idx_occurred_at` (`occurred_at`),
  KEY `idx_caller_number` (`caller_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='警情主表';

-- 3、警情操作日志表
DROP TABLE IF EXISTS aegis.`duty_alert_log`;
CREATE TABLE `duty_alert_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '日志ID',
  `alert_id` bigint(20) NOT NULL COMMENT '警情ID',
  `action` varchar(32) NOT NULL COMMENT '操作类型：alert_create/alert_update/alert_close/alert_review_submit/alert_review_approve/alert_review_reject/transfer_apply/transfer_approve/transfer_reject/transfer_feedback/transfer_processed/convert_apply/convert_approve/convert_reject/convert_event_created 等',
  `payload` text COMMENT '操作详情（JSON格式）',
  `operator_id` bigint(20) DEFAULT NULL COMMENT '操作人ID',
  `operator_name` varchar(64) DEFAULT NULL COMMENT '操作人姓名',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_alert_id` (`alert_id`),
  KEY `idx_action` (`action`),
  KEY `idx_operator_id` (`operator_id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='警情操作日志表';

-- 4、转警记录表
DROP TABLE IF EXISTS aegis.`duty_alert_transfer`;
CREATE TABLE `duty_alert_transfer` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '转警记录ID',
  `alert_id` bigint(20) NOT NULL COMMENT '警情ID',
  `target_org` varchar(200) NOT NULL COMMENT '目标单位名称',
  `contact` varchar(64) NOT NULL COMMENT '联系人姓名',
  `mobile` varchar(20) NOT NULL COMMENT '联系电话',
  `transfer_desc` varchar(500) DEFAULT NULL COMMENT '转警说明/原因',
  `transfer_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '转警时间',
  `feedback` text COMMENT '回执反馈内容',
  `feedback_time` datetime DEFAULT NULL COMMENT '反馈时间',
  `status` varchar(32) DEFAULT 'pending_review' COMMENT '转警状态：pending_review-待审核，pending_feedback-待反馈，feedback_received-已反馈，processed-已处理，rejected-已驳回',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_alert_id` (`alert_id`),
  KEY `idx_status` (`status`),
  KEY `idx_transfer_time` (`transfer_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='转警记录表';

-- 5、转事件记录表
DROP TABLE IF EXISTS aegis.`duty_alert_convert`;
CREATE TABLE `duty_alert_convert` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '转事件记录ID',
  `alert_id` bigint(20) NOT NULL COMMENT '警情ID',
  `event_id` bigint(20) DEFAULT NULL COMMENT '事件ID（审核通过后创建事件时填充）',
  `convert_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '转事件时间',
  `convert_reason` varchar(500) DEFAULT NULL COMMENT '转事件原因/说明',
  `operator_id` bigint(20) DEFAULT NULL COMMENT '操作人ID',
  `operator_name` varchar(64) DEFAULT NULL COMMENT '操作人姓名',
  `review_required` tinyint(1) DEFAULT 0 COMMENT '是否需要审核：0-否，1-是',
  `review_status` varchar(16) DEFAULT 'none' COMMENT '审核状态：none-无需审核，pending-待审核，approved-已审核，rejected-已驳回',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_alert_id` (`alert_id`),
  KEY `idx_event_id` (`event_id`),
  KEY `idx_operator_id` (`operator_id`),
  KEY `idx_convert_time` (`convert_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='转事件记录表';

-- ----------------------------
-- 应急调度模块 - 事件管理相关表
-- ----------------------------

-- 1、事件主表
DROP TABLE IF EXISTS aegis.`event`;
CREATE TABLE `event` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '事件ID',
  `event_no` varchar(50) NOT NULL COMMENT '事件编号（唯一）',
  `title` varchar(200) NOT NULL COMMENT '事件标题',
  `type` varchar(64) NOT NULL COMMENT '事件类型（字典值）',
  `level` varchar(32) NOT NULL COMMENT '事件级别：I/II/III/IV',
  `status` varchar(32) NOT NULL DEFAULT 'draft' COMMENT '事件状态：draft-草稿，pending-待处理，processing-处理中，completed-已完成，archived-归档',
  `important_flag` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否重点事件：0-否，1-是',
  `important_by` varchar(64) DEFAULT NULL COMMENT '标记人为重点的操作人',
  `important_time` datetime DEFAULT NULL COMMENT '标记为重点的时间',
  `occurred_at` datetime NOT NULL COMMENT '事件发生时间',
  `location` varchar(255) DEFAULT NULL COMMENT '地点描述',
  `longitude` decimal(10,7) DEFAULT NULL COMMENT '经度',
  `latitude` decimal(10,7) DEFAULT NULL COMMENT '纬度',
  `source` varchar(64) DEFAULT NULL COMMENT '来源渠道：manual-人工录入，alert-接警转入，other-预留',
  `alert_id` bigint(20) DEFAULT NULL COMMENT '接警记录ID（若来自接警转换）',
  `summary` text COMMENT '事件摘要',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_event_no` (`event_no`),
  KEY `idx_type` (`type`),
  KEY `idx_level` (`level`),
  KEY `idx_status` (`status`),
  KEY `idx_occurred_at` (`occurred_at`),
  KEY `idx_alert_id` (`alert_id`),
  CONSTRAINT `fk_event_alert` FOREIGN KEY (`alert_id`) REFERENCES `duty_alert` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='事件主表';

-- 2、事件日志表
DROP TABLE IF EXISTS aegis.`event_log`;
CREATE TABLE `event_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '日志ID',
  `event_id` bigint(20) NOT NULL COMMENT '事件ID',
  `action` varchar(64) NOT NULL COMMENT '操作类型：event_created/event_updated/status_changed/dispatch_linked/report_submitted等',
  `payload` text COMMENT '操作详情（JSON格式）',
  `operator_id` bigint(20) DEFAULT NULL COMMENT '操作人ID',
  `operator_name` varchar(64) DEFAULT NULL COMMENT '操作人姓名',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_event_id` (`event_id`),
  KEY `idx_action` (`action`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_event_log_event` FOREIGN KEY (`event_id`) REFERENCES `event` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='事件日志表';

-- ----------------------------
-- 应急调度模块 - 上报模板管理
-- ----------------------------

-- 上报模板表
DROP TABLE IF EXISTS aegis.`report_template`;
CREATE TABLE `report_template` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '模板ID',
  `name` varchar(50) NOT NULL COMMENT '模板名称',
  `description` varchar(200) DEFAULT NULL COMMENT '模板描述',
  `fields` json DEFAULT NULL COMMENT '字段配置（JSON格式，支持嵌套结构：groups, lists, fields）',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_del_flag` (`del_flag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='上报模板表';

-- 省厅上报记录表
DROP TABLE IF EXISTS aegis.`event_province_report`;
CREATE TABLE `event_province_report` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '上报记录ID',
  `event_id` bigint(20) NOT NULL COMMENT '事件ID（外键关联event.id）',
  `template_id` bigint(20) NOT NULL COMMENT '使用的模板ID（外键关联report_template.id）',
  `report_type` varchar(16) NOT NULL DEFAULT 'initial' COMMENT '上报类型：initial-首次上报，followup-续报',
  `report_sequence` int NOT NULL DEFAULT 1 COMMENT '上报序号（第几次上报，从1开始）',
  `fields` json DEFAULT NULL COMMENT '动态字段数据（JSON格式，根据模板fields结构存储）',
  `status` varchar(32) NOT NULL DEFAULT 'pending' COMMENT '上报状态：pending-待上报，submitting-上报中，success-成功，failed-失败，retry-重试中',
  `request_payload` json DEFAULT NULL COMMENT '请求数据（发送给省厅的完整请求体，JSON格式）',
  `response_payload` json DEFAULT NULL COMMENT '响应数据（省厅返回的响应体，JSON格式）',
  `retry_count` int DEFAULT 0 COMMENT '重试次数（最多3次）',
  `last_retry_time` datetime DEFAULT NULL COMMENT '最后重试时间',
  `error_message` varchar(500) DEFAULT NULL COMMENT '错误信息（失败时记录）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  PRIMARY KEY (`id`),
  KEY `idx_event_id` (`event_id`),
  KEY `idx_template_id` (`template_id`),
  KEY `idx_status` (`status`),
  KEY `idx_report_type` (`report_type`),
  KEY `idx_create_time` (`create_time`),
  CONSTRAINT `fk_province_report_event` FOREIGN KEY (`event_id`) REFERENCES `event` (`id`),
  CONSTRAINT `fk_province_report_template` FOREIGN KEY (`template_id`) REFERENCES `report_template` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='省厅上报记录表';

-- ----------------------------
-- 资源模块 - 物资管理字典数据
-- ----------------------------

-- 1、创建物资单位字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '物资单位', 'material_unit', '0', 'admin', sysdate(), '物资单位列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'material_unit');

-- 2、添加物资单位字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '个', 'piece', 'material_unit', '', '', 'Y', '0', 'admin', sysdate(), '个'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'material_unit' AND dict_value = 'piece');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '件', 'item', 'material_unit', '', '', 'N', '0', 'admin', sysdate(), '件'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'material_unit' AND dict_value = 'item');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 3, '箱', 'box', 'material_unit', '', '', 'N', '0', 'admin', sysdate(), '箱'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'material_unit' AND dict_value = 'box');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 4, '包', 'pack', 'material_unit', '', '', 'N', '0', 'admin', sysdate(), '包'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'material_unit' AND dict_value = 'pack');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 5, '升', 'liter', 'material_unit', '', '', 'N', '0', 'admin', sysdate(), '升'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'material_unit' AND dict_value = 'liter');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 6, '公斤', 'kilogram', 'material_unit', '', '', 'N', '0', 'admin', sysdate(), '公斤'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'material_unit' AND dict_value = 'kilogram');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 7, '吨', 'ton', 'material_unit', '', '', 'N', '0', 'admin', sysdate(), '吨'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'material_unit' AND dict_value = 'ton');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 8, '米', 'meter', 'material_unit', '', '', 'N', '0', 'admin', sysdate(), '米'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'material_unit' AND dict_value = 'meter');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 9, '平方米', 'square_meter', 'material_unit', '', '', 'N', '0', 'admin', sysdate(), '平方米'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'material_unit' AND dict_value = 'square_meter');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 10, '立方米', 'cubic_meter', 'material_unit', '', '', 'N', '0', 'admin', sysdate(), '立方米'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'material_unit' AND dict_value = 'cubic_meter');

-- 3、创建是否可重复使用字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '是否可重复使用', 'material_reusable', '0', 'admin', sysdate(), '是否可重复使用列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'material_reusable');

-- 4、添加是否可重复使用字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '否', '0', 'material_reusable', '', '', 'Y', '0', 'admin', sysdate(), '不可重复使用'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'material_reusable' AND dict_value = '0');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '是', '1', 'material_reusable', '', '', 'N', '0', 'admin', sysdate(), '可重复使用'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'material_reusable' AND dict_value = '1');

-- 5、创建是否需要消毒字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '是否需要消毒', 'material_sterilize_required', '0', 'admin', sysdate(), '是否需要消毒列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'material_sterilize_required');

-- 6、添加是否需要消毒字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '否', '0', 'material_sterilize_required', '', '', 'Y', '0', 'admin', sysdate(), '不需要消毒'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'material_sterilize_required' AND dict_value = '0');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '是', '1', 'material_sterilize_required', '', '', 'N', '0', 'admin', sysdate(), '需要消毒'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'material_sterilize_required' AND dict_value = '1');

-- 7、创建库存调整原因字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '库存调整原因', 'inventory_adjustment_reason', '0', 'admin', sysdate(), '库存调整原因列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'inventory_adjustment_reason');

-- 8、添加库存调整原因字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '盘点', 'physical_count', 'inventory_adjustment_reason', '', 'primary', 'N', '0', 'admin', sysdate(), '物理盘点差异'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'inventory_adjustment_reason' AND dict_value = 'physical_count');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '损失', 'loss', 'inventory_adjustment_reason', '', 'danger', 'N', '0', 'admin', sysdate(), '库存损失'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'inventory_adjustment_reason' AND dict_value = 'loss');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 3, '损坏', 'damage', 'inventory_adjustment_reason', '', 'warning', 'N', '0', 'admin', sysdate(), '物资损坏'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'inventory_adjustment_reason' AND dict_value = 'damage');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 4, '盗窃', 'theft', 'inventory_adjustment_reason', '', 'danger', 'N', '0', 'admin', sysdate(), '盗窃损失'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'inventory_adjustment_reason' AND dict_value = 'theft');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 5, '发现', 'found', 'inventory_adjustment_reason', '', 'success', 'N', '0', 'admin', sysdate(), '发现未登记物资'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'inventory_adjustment_reason' AND dict_value = 'found');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 6, '过期', 'expired', 'inventory_adjustment_reason', '', 'warning', 'N', '0', 'admin', sysdate(), '物资过期'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'inventory_adjustment_reason' AND dict_value = 'expired');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 7, '淘汰', 'obsolete', 'inventory_adjustment_reason', '', 'info', 'N', '0', 'admin', sysdate(), '物资淘汰'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'inventory_adjustment_reason' AND dict_value = 'obsolete');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 8, '更正', 'correction', 'inventory_adjustment_reason', '', '', 'N', '0', 'admin', sysdate(), '数据更正'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'inventory_adjustment_reason' AND dict_value = 'correction');

-- ----------------------------
-- 资源模块 - 供应商管理字典配置
-- ----------------------------

-- 1、创建供应商状态字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '供应商状态', 'supplier_status', '0', 'admin', sysdate(), '供应商状态列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'supplier_status');

-- 2、添加供应商状态字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '启用', 'active', 'supplier_status', '', 'success', 'Y', '0', 'admin', sysdate(), '供应商启用状态'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'supplier_status' AND dict_value = 'active');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '停用', 'inactive', 'supplier_status', '', 'info', 'N', '0', 'admin', sysdate(), '供应商停用状态'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'supplier_status' AND dict_value = 'inactive');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 3, '黑名单', 'blacklisted', 'supplier_status', '', 'danger', 'N', '0', 'admin', sysdate(), '供应商黑名单状态'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'supplier_status' AND dict_value = 'blacklisted');

-- 3、创建供应商分类字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '供应商分类', 'supplier_category', '0', 'admin', sysdate(), '供应商分类列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'supplier_category');

-- 4、添加供应商分类字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '设备供应商', 'equipment', 'supplier_category', '', 'primary', 'Y', '0', 'admin', sysdate(), '设备类供应商'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'supplier_category' AND dict_value = 'equipment');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '物资供应商', 'material', 'supplier_category', '', 'success', 'N', '0', 'admin', sysdate(), '物资类供应商'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'supplier_category' AND dict_value = 'material');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 3, '服务供应商', 'service', 'supplier_category', '', 'info', 'N', '0', 'admin', sysdate(), '服务类供应商'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'supplier_category' AND dict_value = 'service');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 4, '其他', 'other', 'supplier_category', '', '', 'N', '0', 'admin', sysdate(), '其他类型供应商'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'supplier_category' AND dict_value = 'other');

-- 5、创建供应商所在区域字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '供应商所在区域', 'supplier_area', '0', 'admin', sysdate(), '供应商所在区域列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'supplier_area');

-- 6、添加供应商所在区域字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '华东', 'east', 'supplier_area', '', 'primary', 'Y', '0', 'admin', sysdate(), '华东地区'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'supplier_area' AND dict_value = 'east');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '华南', 'south', 'supplier_area', '', 'success', 'N', '0', 'admin', sysdate(), '华南地区'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'supplier_area' AND dict_value = 'south');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 3, '华北', 'north', 'supplier_area', '', 'info', 'N', '0', 'admin', sysdate(), '华北地区'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'supplier_area' AND dict_value = 'north');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 4, '西南', 'southwest', 'supplier_area', '', 'warning', 'N', '0', 'admin', sysdate(), '西南地区'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'supplier_area' AND dict_value = 'southwest');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 5, '西北', 'northwest', 'supplier_area', '', '', 'N', '0', 'admin', sysdate(), '西北地区'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'supplier_area' AND dict_value = 'northwest');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 6, '东北', 'northeast', 'supplier_area', '', '', 'N', '0', 'admin', sysdate(), '东北地区'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'supplier_area' AND dict_value = 'northeast');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 7, '华中', 'central', 'supplier_area', '', '', 'N', '0', 'admin', sysdate(), '华中地区'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'supplier_area' AND dict_value = 'central');

-- ----------------------------
-- 应急调度模块 - 调度任务相关表
-- ----------------------------

-- 1、调度任务主表
DROP TABLE IF EXISTS aegis.`dispatch_task`;
CREATE TABLE `dispatch_task` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '任务ID',
  `task_no` varchar(64) NOT NULL COMMENT '任务编号（唯一）',
  `event_id` bigint(20) DEFAULT NULL COMMENT '关联事件ID',
  `name` varchar(200) NOT NULL COMMENT '任务名称',
  `status` varchar(32) NOT NULL DEFAULT 'draft' COMMENT '任务状态：draft/published/in_progress/closed',
  `plan_template_id` bigint(20) DEFAULT NULL COMMENT '预案模板ID',
  `description` text COMMENT '任务描述',
  `assignee_id` bigint(20) DEFAULT NULL COMMENT '责任人/调度员ID',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_dispatch_task_no` (`task_no`),
  KEY `idx_dispatch_task_event` (`event_id`),
  KEY `idx_dispatch_task_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='调度任务表';

-- 2、调度任务行动项表
DROP TABLE IF EXISTS aegis.`dispatch_task_step`;
CREATE TABLE `dispatch_task_step` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '行动项ID',
  `task_id` bigint(20) NOT NULL COMMENT '任务ID',
  `step_order` int(11) DEFAULT NULL COMMENT '步骤顺序',
  `title` varchar(200) DEFAULT NULL COMMENT '标题',
  `description` text COMMENT '行动项描述',
  `status` varchar(32) DEFAULT 'pending' COMMENT '状态：pending/in_progress/completed/failed',
  `executor_id` bigint(20) DEFAULT NULL COMMENT '执行方参与记录ID（引用 dispatch_task_participant.id）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  PRIMARY KEY (`id`),
  KEY `idx_dispatch_step_task` (`task_id`),
  KEY `idx_dispatch_step_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='调度任务行动项表';

-- 3、调度任务参与方表
DROP TABLE IF EXISTS aegis.`dispatch_task_participant`;
CREATE TABLE `dispatch_task_participant` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '参与方ID',
  `task_id` bigint(20) NOT NULL COMMENT '任务ID',
  `person_id` bigint(20) DEFAULT NULL COMMENT '人员ID（引用资源模块）',
  `team_id` bigint(20) DEFAULT NULL COMMENT '所属队伍ID',
  `person_snapshot` text COMMENT '人员快照（JSON）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  PRIMARY KEY (`id`),
  KEY `idx_dispatch_participant_task` (`task_id`),
  KEY `idx_dispatch_participant_person` (`person_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='调度任务参与方表';

-- 4、调度任务处置记录表
DROP TABLE IF EXISTS aegis.`dispatch_task_disposal`;
CREATE TABLE `dispatch_task_disposal` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '处置记录ID',
  `task_id` bigint(20) NOT NULL COMMENT '任务ID',
  `step_id` bigint(20) DEFAULT NULL COMMENT '关联行动项ID',
  `participant_id` bigint(20) DEFAULT NULL COMMENT '参与方ID',
  `description` text COMMENT '处置描述',
  `outcome` varchar(32) DEFAULT NULL COMMENT '处置结果：success/partial/failed',
  `attachment_ids` text COMMENT '附件ID列表（JSON）',
  `location_snapshot` text COMMENT '位置快照（JSON）',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  PRIMARY KEY (`id`),
  KEY `idx_dispatch_disposal_task` (`task_id`),
  KEY `idx_dispatch_disposal_participant` (`participant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='调度任务处置记录表';

-- 5、调度任务与资源 usage 关联表
DROP TABLE IF EXISTS aegis.`dispatch_resource_usage_link`;
CREATE TABLE `dispatch_resource_usage_link` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '关联ID',
  `task_id` bigint(20) NOT NULL COMMENT '任务ID',
  `step_id` bigint(20) DEFAULT NULL COMMENT '关联的行动项ID（可选，null表示任务级资源，非null表示行动项级资源）',
  `resource_type` varchar(32) NOT NULL COMMENT '资源类型：equipment/material/vehicle',
  `resource_id` bigint(20) DEFAULT NULL COMMENT '资源ID',
  `usage_record_id` bigint(20) DEFAULT NULL COMMENT '资源模块 usage 记录ID',
  `status` varchar(32) DEFAULT NULL COMMENT '调拨状态快照',
  `note` text COMMENT '备注/上下文信息',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  PRIMARY KEY (`id`),
  KEY `idx_dispatch_resource_task` (`task_id`),
  KEY `idx_dispatch_resource_step` (`step_id`),
  KEY `idx_dispatch_resource_type` (`resource_type`),
  KEY `idx_dispatch_resource_usage` (`usage_record_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='调度任务与资源使用关联表';

-- 6、调度任务操作日志表
DROP TABLE IF EXISTS aegis.`dispatch_task_log`;
CREATE TABLE `dispatch_task_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '日志ID',
  `task_id` bigint(20) NOT NULL COMMENT '任务ID',
  `action` varchar(64) NOT NULL COMMENT '操作类型',
  `payload` text COMMENT '操作详情（JSON）',
  `operator_id` bigint(20) DEFAULT NULL COMMENT '操作人ID',
  `operator_name` varchar(64) DEFAULT NULL COMMENT '操作人姓名',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
  KEY `idx_dispatch_log_task` (`task_id`),
  KEY `idx_dispatch_log_action` (`action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='调度任务操作日志表';

-- ----------------------------
-- 资源模块 - 采购管理字典数据
-- ----------------------------

-- 1、创建采购状态字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '采购状态', 'procurement_status', '0', 'admin', sysdate(), '采购申请状态列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'procurement_status');

-- 2、添加采购状态字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '草稿', 'draft', 'procurement_status', '', 'info', 'N', '0', 'admin', sysdate(), '草稿状态'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'procurement_status' AND dict_value = 'draft');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '已提交', 'submitted', 'procurement_status', '', 'primary', 'N', '0', 'admin', sysdate(), '已提交审核'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'procurement_status' AND dict_value = 'submitted');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 3, '已审核', 'approved', 'procurement_status', '', 'success', 'N', '0', 'admin', sysdate(), '已审核通过'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'procurement_status' AND dict_value = 'approved');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 4, '已驳回', 'rejected', 'procurement_status', '', 'warning', 'N', '0', 'admin', sysdate(), '已驳回'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'procurement_status' AND dict_value = 'rejected');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 5, '进行中', 'in_progress', 'procurement_status', '', '', 'N', '0', 'admin', sysdate(), '采购进行中'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'procurement_status' AND dict_value = 'in_progress');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 6, '已入库', 'received', 'procurement_status', '', 'success', 'N', '0', 'admin', sysdate(), '已入库'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'procurement_status' AND dict_value = 'received');

-- 3、创建紧急程度字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '紧急程度', 'procurement_urgency', '0', 'admin', sysdate(), '采购申请紧急程度列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'procurement_urgency');

-- 4、添加紧急程度字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '低', 'low', 'procurement_urgency', '', 'info', 'N', '0', 'admin', sysdate(), '低紧急程度'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'procurement_urgency' AND dict_value = 'low');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '普通', 'normal', 'procurement_urgency', '', 'primary', 'Y', '0', 'admin', sysdate(), '普通紧急程度'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'procurement_urgency' AND dict_value = 'normal');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 3, '高', 'high', 'procurement_urgency', '', 'warning', 'N', '0', 'admin', sysdate(), '高紧急程度'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'procurement_urgency' AND dict_value = 'high');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 4, '紧急', 'urgent', 'procurement_urgency', '', 'danger', 'N', '0', 'admin', sysdate(), '紧急'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'procurement_urgency' AND dict_value = 'urgent');

-- ----------------------------
-- 应急调度模块 - 菜单
-- ----------------------------
INSERT INTO aegis.sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark
)
SELECT
  3303, '调度工作台', 3300, 3, 'dispatch/workbench',
  'emergency/dispatch/workbench/index', '', 'DispatchWorkbench',
  1, 0, 'C', '0', '0', 'emergency:dispatch:workbench:view', 'guide',
  'admin', SYSDATE(), '', NULL, '调度工作台菜单'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3303);

INSERT INTO aegis.sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark
)
SELECT
  3304, '处置工作台', 3300, 4, 'disposal/console',
  'emergency/disposal/console', '', 'DisposalConsole',
  1, 0, 'C', '0', '0', 'emergency:disposal:list', 'form',
  'admin', SYSDATE(), '', NULL, '处置工作台（执行端）'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3304);

-- 5、创建采购明细分类字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '采购明细分类', 'procurement_item_category', '0', 'admin', sysdate(), '采购明细分类列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'procurement_item_category');

-- 6、添加采购明细分类字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '物资', 'material', 'procurement_item_category', '', 'primary', 'N', '0', 'admin', sysdate(), '物资类'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'procurement_item_category' AND dict_value = 'material');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '装备', 'equipment', 'procurement_item_category', '', 'success', 'N', '0', 'admin', sysdate(), '装备类'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'procurement_item_category' AND dict_value = 'equipment');

-- ----------------------------
-- 资源模块 - 征用管理字典数据
-- ----------------------------

-- 1、创建征用状态字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '征用状态', 'requisition_status', '0', 'admin', sysdate(), '征用申请状态列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'requisition_status');

-- 2、添加征用状态字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '草稿', 'draft', 'requisition_status', '', 'info', 'N', '0', 'admin', sysdate(), '草稿状态'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'requisition_status' AND dict_value = 'draft');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '已审批', 'approved', 'requisition_status', '', 'success', 'N', '0', 'admin', sysdate(), '已审批通过'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'requisition_status' AND dict_value = 'approved');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 3, '进行中', 'active', 'requisition_status', '', 'primary', 'N', '0', 'admin', sysdate(), '征用进行中'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'requisition_status' AND dict_value = 'active');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 4, '已结束', 'ended', 'requisition_status', '', 'success', 'N', '0', 'admin', sysdate(), '征用已结束'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'requisition_status' AND dict_value = 'ended');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 5, '已驳回', 'rejected', 'requisition_status', '', 'warning', 'N', '0', 'admin', sysdate(), '已驳回'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'requisition_status' AND dict_value = 'rejected');

-- 3、创建计费方式字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '计费方式', 'requisition_billing_type', '0', 'admin', sysdate(), '征用协议计费方式列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'requisition_billing_type');

-- 4、添加计费方式字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '按小时', 'hour', 'requisition_billing_type', '', 'primary', 'N', '0', 'admin', sysdate(), '按小时计费'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'requisition_billing_type' AND dict_value = 'hour');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '按天', 'day', 'requisition_billing_type', '', 'primary', 'N', '0', 'admin', sysdate(), '按天计费'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'requisition_billing_type' AND dict_value = 'day');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 3, '按任务', 'task', 'requisition_billing_type', '', 'primary', 'N', '0', 'admin', sysdate(), '按任务计费'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'requisition_billing_type' AND dict_value = 'task');

-- 5、创建工作量单位字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '工作量单位', 'requisition_workload_unit', '0', 'admin', sysdate(), '补偿记录工作量单位列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'requisition_workload_unit');

-- 6、添加工作量单位字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '小时', 'hour', 'requisition_workload_unit', '', 'primary', 'N', '0', 'admin', sysdate(), '小时'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'requisition_workload_unit' AND dict_value = 'hour');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '天', 'day', 'requisition_workload_unit', '', 'primary', 'N', '0', 'admin', sysdate(), '天'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'requisition_workload_unit' AND dict_value = 'day');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 3, '任务', 'task', 'requisition_workload_unit', '', 'primary', 'N', '0', 'admin', sysdate(), '任务'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'requisition_workload_unit' AND dict_value = 'task');

-- 7、创建补偿状态字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '补偿状态', 'requisition_compensation_status', '0', 'admin', sysdate(), '补偿记录状态列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'requisition_compensation_status');

-- 8、添加补偿状态字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '待结算', 'pending', 'requisition_compensation_status', '', 'warning', 'N', '0', 'admin', sysdate(), '待结算'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'requisition_compensation_status' AND dict_value = 'pending');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '已结算', 'settled', 'requisition_compensation_status', '', 'success', 'N', '0', 'admin', sysdate(), '已结算'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'requisition_compensation_status' AND dict_value = 'settled');

-- 9、创建币种字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '币种', 'currency', '0', 'admin', sysdate(), '货币类型列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'currency');

-- 10、添加币种字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '人民币', 'CNY', 'currency', '', 'primary', 'Y', '0', 'admin', sysdate(), '人民币'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'currency' AND dict_value = 'CNY');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '美元', 'USD', 'currency', '', 'primary', 'N', '0', 'admin', sysdate(), '美元'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'currency' AND dict_value = 'USD');

-- ----------------------------
-- 应急调度模块 - 值守接警字典数据
-- ----------------------------

-- 1、创建警情来源渠道字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '警情来源渠道', 'alert_source_channel', '0', 'admin', sysdate(), '警情来源渠道列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'alert_source_channel');

-- 2、添加警情来源渠道字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '122交通事故报警', '122', 'alert_source_channel', '', 'danger', 'Y', '0', 'admin', sysdate(), '122交通事故报警电话'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'alert_source_channel' AND dict_value = '122');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '12345市民热线', '12345', 'alert_source_channel', '', 'primary', 'N', '0', 'admin', sysdate(), '12345市民服务热线'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'alert_source_channel' AND dict_value = '12345');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 3, '110报警电话', '110', 'alert_source_channel', '', 'warning', 'N', '0', 'admin', sysdate(), '110报警电话'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'alert_source_channel' AND dict_value = '110');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 4, '上级转办', 'superior', 'alert_source_channel', '', 'info', 'N', '0', 'admin', sysdate(), '上级部门转办'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'alert_source_channel' AND dict_value = 'superior');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 5, '下级上报', 'subordinate', 'alert_source_channel', '', 'info', 'N', '0', 'admin', sysdate(), '下级部门上报'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'alert_source_channel' AND dict_value = 'subordinate');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 6, '其他部门转办', 'other_dept', 'alert_source_channel', '', 'info', 'N', '0', 'admin', sysdate(), '其他部门转办'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'alert_source_channel' AND dict_value = 'other_dept');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 7, '系统监测', 'monitor', 'alert_source_channel', '', 'success', 'N', '0', 'admin', sysdate(), '系统自动监测'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'alert_source_channel' AND dict_value = 'monitor');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 8, '媒体舆情', 'media', 'alert_source_channel', '', 'warning', 'N', '0', 'admin', sysdate(), '媒体舆情'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'alert_source_channel' AND dict_value = 'media');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 9, '手工录入', 'manual', 'alert_source_channel', '', '', 'N', '0', 'admin', sysdate(), '手工录入'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'alert_source_channel' AND dict_value = 'manual');

-- 3、创建班次类型字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '班次类型', 'duty_shift_type', '0', 'admin', sysdate(), '值班排班班次类型列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'duty_shift_type');

-- 4、添加班次类型字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '白班', 'day', 'duty_shift_type', '', 'primary', 'N', '0', 'admin', sysdate(), '白班'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'duty_shift_type' AND dict_value = 'day');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '夜班', 'night', 'duty_shift_type', '', 'info', 'N', '0', 'admin', sysdate(), '夜班'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'duty_shift_type' AND dict_value = 'night');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 3, '全天', 'all', 'duty_shift_type', '', 'success', 'N', '0', 'admin', sysdate(), '全天'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'duty_shift_type' AND dict_value = 'all');

-- 5、创建排班状态字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '排班状态', 'duty_shift_status', '0', 'admin', sysdate(), '值班排班状态列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'duty_shift_status');

-- 6、添加排班状态字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '有效', 'active', 'duty_shift_status', '', 'success', 'Y', '0', 'admin', sysdate(), '排班有效状态'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'duty_shift_status' AND dict_value = 'active');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '已取消', 'cancelled', 'duty_shift_status', '', 'danger', 'N', '0', 'admin', sysdate(), '排班已取消状态'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'duty_shift_status' AND dict_value = 'cancelled');

-- 7、创建排班成员角色字典类型（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '排班成员角色', 'duty_shift_member_role', '0', 'admin', sysdate(), '排班成员角色列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'duty_shift_member_role');

-- 8、添加排班成员角色字典数据（如果不存在）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '组长', 'leader', 'duty_shift_member_role', '', 'warning', 'N', '0', 'admin', sysdate(), '排班组长'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'duty_shift_member_role' AND dict_value = 'leader');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '成员', 'member', 'duty_shift_member_role', '', 'primary', 'Y', '0', 'admin', sysdate(), '排班成员'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'duty_shift_member_role' AND dict_value = 'member');

-- ----------------------------
-- 应急调度模块 - 菜单初始化
-- ----------------------------

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark
)
SELECT
  3300, '应急调度', 0, 1, 'duty', NULL, '', '',
  1, 0, 'M', '0', '0', '', 'guide',
  'admin', sysdate(), '', NULL, '应急调度目录'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3300);

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark
)
SELECT
  3301, '排班管理', 3300, 1, 'shift', 'duty/shift/index', '', 'DutyShift',
  1, 0, 'C', '0', '0', 'emergency:duty:shift', 'date',
  'admin', sysdate(), '', NULL, '值守接警排班管理'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3301);

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark
)
SELECT
  3302, '接警工作台', 3300, 2, 'alert/console', 'duty/alert/console', '', 'DutyAlertConsole',
  1, 0, 'C', '0', '0', 'emergency:duty:console', 'phone',
  'admin', sysdate(), '', NULL, '接警工作台（静态版）'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3302);

-- 数据报送目录与页面
INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark
)
SELECT
  3350, '数据报送', 0, 2, 'data-reporting', NULL, '', '',
  1, 0, 'M', '0', '0', '', 'international',
  'admin', sysdate(), '', NULL, '数据报送一级目录'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3350);

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark
)
SELECT
  3351, '上级报送', 3350, 1, 'province', 'emergency/report/province/index', '', 'EmergencyProvinceReporting',
  1, 0, 'C', '0', '0', 'emergency:data:province', 'upload',
  'admin', sysdate(), '', NULL, '上级报送列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3351);

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark
)
SELECT
  3352, '模板配置', 3350, 2, 'templates', 'emergency/report/template/index', '', 'EmergencyTemplateConfig',
  1, 0, 'C', '0', '0', 'emergency:data:template', 'form',
  'admin', sysdate(), '', NULL, '上级报送模板配置'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3352);

-- 预案管理菜单
INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark
)
SELECT
  3360, '预案管理', 0, 3, 'plan', 'emergency/plan/index', '', 'PlanCenter',
  1, 0, 'C', '0', '0', 'emergency:plan:list', 'documentation',
  'admin', sysdate(), '', NULL, '预案管理'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3360);

-- 预案按钮权限（用于权限控制，不在导航菜单显示）
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark)
SELECT 3364, '预案新增', 3360, 1, '#', NULL, '', '',
  1, 0, 'F', '0', '0', 'emergency:plan:add', '#',
  'admin', sysdate(), '', NULL, '预案新增按钮'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3364);

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark)
SELECT 3365, '预案编辑', 3360, 2, '#', NULL, '', '',
  1, 0, 'F', '0', '0', 'emergency:plan:edit', '#',
  'admin', sysdate(), '', NULL, '预案编辑按钮'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3365);

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark)
SELECT 3366, '预案删除', 3360, 3, '#', NULL, '', '',
  1, 0, 'F', '0', '0', 'emergency:plan:remove', '#',
  'admin', sysdate(), '', NULL, '预案删除按钮'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3366);

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark)
SELECT 3367, '预案发布', 3360, 4, '#', NULL, '', '',
  1, 0, 'F', '0', '0', 'emergency:plan:publish', '#',
  'admin', sysdate(), '', NULL, '预案发布按钮'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3367);

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark)
SELECT 3363, '预案查询', 3360, 5, '#', NULL, '', '',
  1, 0, 'F', '0', '0', 'emergency:plan:query', '#',
  'admin', sysdate(), '', NULL, '预案查询按钮'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3363);

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark)
SELECT 3368, '执行对象查看', 3360, 6, '#', NULL, '', '',
  1, 0, 'F', '0', '0', 'emergency:plan:participant:list', '#',
  'admin', sysdate(), '', NULL, '执行对象列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3368);

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark)
SELECT 3369, '执行对象新增', 3360, 7, '#', NULL, '', '',
  1, 0, 'F', '0', '0', 'emergency:plan:participant:add', '#',
  'admin', sysdate(), '', NULL, '执行对象新增'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3369);

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark)
SELECT 3370, '执行对象编辑', 3360, 8, '#', NULL, '', '',
  1, 0, 'F', '0', '0', 'emergency:plan:participant:edit', '#',
  'admin', sysdate(), '', NULL, '执行对象编辑'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3370);

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark)
SELECT 3371, '执行对象删除', 3360, 9, '#', NULL, '', '',
  1, 0, 'F', '0', '0', 'emergency:plan:participant:remove', '#',
  'admin', sysdate(), '', NULL, '执行对象删除'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3371);

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark)
SELECT 3372, '行动项查看', 3360, 10, '#', NULL, '', '',
  1, 0, 'F', '0', '0', 'emergency:plan:step:list', '#',
  'admin', sysdate(), '', NULL, '行动项查看'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3372);

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark)
SELECT 3373, '行动项新增', 3360, 11, '#', NULL, '', '',
  1, 0, 'F', '0', '0', 'emergency:plan:step:add', '#',
  'admin', sysdate(), '', NULL, '行动项新增'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3373);

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark)
SELECT 3374, '行动项编辑', 3360, 12, '#', NULL, '', '',
  1, 0, 'F', '0', '0', 'emergency:plan:step:edit', '#',
  'admin', sysdate(), '', NULL, '行动项编辑'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3374);

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark)
SELECT 3375, '行动项删除', 3360, 13, '#', NULL, '', '',
  1, 0, 'F', '0', '0', 'emergency:plan:step:remove', '#',
  'admin', sysdate(), '', NULL, '行动项删除'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3375);

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark)
SELECT 3376, '资源查看', 3360, 14, '#', NULL, '', '',
  1, 0, 'F', '0', '0', 'emergency:plan:resource:list', '#',
  'admin', sysdate(), '', NULL, '资源查看'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3376);

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark)
SELECT 3377, '资源新增', 3360, 15, '#', NULL, '', '',
  1, 0, 'F', '0', '0', 'emergency:plan:resource:add', '#',
  'admin', sysdate(), '', NULL, '资源新增'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3377);

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark)
SELECT 3378, '资源编辑', 3360, 16, '#', NULL, '', '',
  1, 0, 'F', '0', '0', 'emergency:plan:resource:edit', '#',
  'admin', sysdate(), '', NULL, '资源编辑'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3378);

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark)
SELECT 3379, '资源删除', 3360, 17, '#', NULL, '', '',
  1, 0, 'F', '0', '0', 'emergency:plan:resource:remove', '#',
  'admin', sysdate(), '', NULL, '资源删除'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3379);

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark)
SELECT 3380, '版本查看', 3360, 18, '#', NULL, '', '',
  1, 0, 'F', '0', '0', 'emergency:plan:version:list', '#',
  'admin', sysdate(), '', NULL, '版本查看'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3380);

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark)
SELECT 3382, '版本恢复', 3360, 19, '#', NULL, '', '',
  1, 0, 'F', '0', '0', 'emergency:plan:version:restore', '#',
  'admin', sysdate(), '', NULL, '版本恢复'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3382);

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark)
SELECT 3386, '审批查看', 3360, 20, '#', NULL, '', '',
  1, 0, 'F', '0', '0', 'emergency:plan:approval:list', '#',
  'admin', sysdate(), '', NULL, '审批记录查看'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3386);

INSERT INTO sys_menu (
  menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
  is_frame, is_cache, menu_type, visible, status, perms, icon,
  create_by, create_time, update_by, update_time, remark)
SELECT 3384, '使用记录查看', 3360, 21, '#', NULL, '', '',
  1, 0, 'F', '0', '0', 'emergency:plan:usage:list', '#',
  'admin', sysdate(), '', NULL, '使用记录查看'
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE menu_id = 3384);

-- 将预案管理菜单及其所有按钮权限分配给管理员角色（role_id = 1）
-- 如果管理员角色ID不是1，请先查询：SELECT role_id FROM sys_role WHERE role_key = 'admin';
INSERT INTO sys_role_menu (role_id, menu_id)
SELECT 1, menu_id
FROM sys_menu
WHERE (menu_id = 3360 OR parent_id = 3360)
  AND NOT EXISTS (
    SELECT 1 FROM sys_role_menu rm 
    WHERE rm.role_id = 1 AND rm.menu_id = sys_menu.menu_id
  );

-- 修复菜单顺序冲突：将 menu_id = 3000 移到 order_num = 4（如果存在且冲突）
UPDATE sys_menu
SET order_num = 4,
    update_time = sysdate()
WHERE menu_id = 3000 
  AND parent_id = 0 
  AND order_num = 3;

-- 确保预案管理菜单状态正确
UPDATE sys_menu
SET status = '0',
    visible = '0',
    update_time = sysdate()
WHERE menu_id = 3360 OR parent_id = 3360;

-- ----------------------------
-- 菜单顺序调整：确保预案管理位于数据报送之后、资源管理之前
-- ----------------------------

-- 1. 确保"数据报送"菜单 order_num = 2
UPDATE sys_menu
SET order_num = 2,
    update_time = sysdate()
WHERE menu_id = 3350 
  AND parent_id = 0
  AND order_num != 2;

-- 2. 确保"预案管理"菜单 order_num = 3
UPDATE sys_menu
SET order_num = 3,
    update_time = sysdate()
WHERE menu_id = 3360 
  AND parent_id = 0
  AND order_num != 3;

-- 3. 将"资源管理"相关菜单移到 order_num = 4 或更高（如果存在）
--    查找并更新所有包含"资源"的顶级菜单
UPDATE sys_menu
SET order_num = 4,
    update_time = sysdate()
WHERE parent_id = 0
  AND menu_id NOT IN (3300, 3350, 3360)  -- 排除应急调度、数据报送、预案管理
  AND (menu_name LIKE '%资源%' OR menu_name LIKE '%resource%' OR menu_name LIKE '%Resource%')
  AND order_num <= 3;

-- 4. 将所有其他可能冲突的顶级菜单移到 order_num = 5 或更高
--    先标记资源管理菜单，然后处理其他冲突菜单
UPDATE sys_menu
SET order_num = 5,
    update_time = sysdate()
WHERE parent_id = 0
  AND menu_id NOT IN (3300, 3350, 3360)
  AND order_num <= 3
  AND order_num > 0
  AND NOT (menu_name LIKE '%资源%' OR menu_name LIKE '%resource%' OR menu_name LIKE '%Resource%');

-- ----------------------------
-- 知识库模块 - 知识管理相关表
-- ----------------------------

-- 1、知识库分类表
DROP TABLE IF EXISTS aegis.`knowledge_category`;
CREATE TABLE `knowledge_category` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '分类ID',
  `name` varchar(100) NOT NULL COMMENT '分类名称（如：法律法规、政策规定、标准规范）',
  `code` varchar(50) DEFAULT NULL COMMENT '分类代码',
  `parent_id` bigint(20) DEFAULT NULL COMMENT '父分类ID（支持多级分类）',
  `sort_order` int(11) DEFAULT 0 COMMENT '排序',
  `description` varchar(500) DEFAULT NULL COMMENT '描述',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_parent` (`parent_id`),
  KEY `idx_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='知识库分类表';

-- 2、知识库文档表
DROP TABLE IF EXISTS aegis.`knowledge_document`;
CREATE TABLE `knowledge_document` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '文档ID',
  `title` varchar(200) NOT NULL COMMENT '文档标题',
  `category_id` bigint(20) DEFAULT NULL COMMENT '分类ID',
  `file_id` bigint(20) DEFAULT NULL COMMENT '文件ID（关联文件表file_info.id）',
  `file_name` varchar(255) DEFAULT NULL COMMENT '原始文件名',
  `file_size` bigint(20) DEFAULT NULL COMMENT '文件大小（字节）',
  `file_type` varchar(50) DEFAULT NULL COMMENT '文件类型（PDF、DOC、DOCX等）',
  `tags` varchar(500) DEFAULT NULL COMMENT '标签（逗号分隔，如：交通,应急,法规）',
  `keywords` varchar(500) DEFAULT NULL COMMENT '关键词（逗号分隔，用于搜索和自动调取）',
  `summary` varchar(1000) DEFAULT NULL COMMENT '摘要/描述',
  `view_count` int(11) DEFAULT 0 COMMENT '查看次数',
  `download_count` int(11) DEFAULT 0 COMMENT '下载次数',
  `is_featured` tinyint(1) DEFAULT 0 COMMENT '是否推荐',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_category` (`category_id`),
  KEY `idx_title` (`title`),
  KEY `idx_keywords` (`keywords`),
  KEY `idx_file_id` (`file_id`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='知识库文档表';

-- ----------------------------
-- 案例库模块 - 案例管理相关表
-- ----------------------------

-- 1、案例表
DROP TABLE IF EXISTS aegis.`case`;
CREATE TABLE `case` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '案例ID',
  `case_no` varchar(64) DEFAULT NULL COMMENT '案例编号',
  `title` varchar(200) NOT NULL COMMENT '案例标题',
  `event_type` varchar(64) DEFAULT NULL COMMENT '事件类型（字典值）',
  `event_level` varchar(10) DEFAULT NULL COMMENT '事件级别（I/II/III/IV）',
  `location` varchar(200) DEFAULT NULL COMMENT '发生地点',
  `occurred_time` datetime DEFAULT NULL COMMENT '发生时间',
  `description` varchar(2000) DEFAULT NULL COMMENT '案例描述',
  `disposal_summary` varchar(2000) DEFAULT NULL COMMENT '处置摘要',
  `tags` varchar(500) DEFAULT NULL COMMENT '标签（逗号分隔）',
  `is_typical` tinyint(1) DEFAULT 0 COMMENT '是否典型案例',
  `view_count` int(11) DEFAULT 0 COMMENT '查看次数',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_event_type` (`event_type`),
  KEY `idx_title` (`title`),
  KEY `idx_is_typical` (`is_typical`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='案例表';

-- 2、事件类型字典（如果不存在）
INSERT INTO sys_dict_type (dict_name, dict_type, status, create_by, create_time, remark)
SELECT '事件类型', 'event_type', '0', 'admin', sysdate(), '事件类型列表'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_type WHERE dict_type = 'event_type');

-- 事件类型字典数据（示例）
INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 1, '交通事故', 'traffic_accident', 'event_type', '', 'danger', 'Y', '0', 'admin', sysdate(), '交通事故'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'event_type' AND dict_value = 'traffic_accident');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 2, '自然灾害', 'natural_disaster', 'event_type', '', 'warning', 'N', '0', 'admin', sysdate(), '自然灾害'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'event_type' AND dict_value = 'natural_disaster');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 3, '公共卫生事件', 'public_health', 'event_type', '', 'info', 'N', '0', 'admin', sysdate(), '公共卫生事件'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'event_type' AND dict_value = 'public_health');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 4, '社会安全事件', 'social_security', 'event_type', '', 'danger', 'N', '0', 'admin', sysdate(), '社会安全事件'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'event_type' AND dict_value = 'social_security');

INSERT INTO sys_dict_data (dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, status, create_by, create_time, remark)
SELECT 5, '其他', 'other', 'event_type', '', '', 'N', '0', 'admin', sysdate(), '其他事件类型'
WHERE NOT EXISTS (SELECT 1 FROM sys_dict_data WHERE dict_type = 'event_type' AND dict_value = 'other');