all-project/docs/设计文档/database/04-venue-reservation-module.sql

-- =============================================
-- 场地预约管理模块数据库脚本
-- 
-- 模块: 场地预约管理 (venue-reservation)
-- 创建时间: 2025-07-01
-- 描述: 包含场地信息管理、预约申请、审批流程等功能
-- =============================================

-- 设置字符集和时区
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =============================================
-- 1. 数据表结构设计
-- =============================================

-- 1.1 场地信息表
DROP TABLE IF EXISTS `hq_venue_info`;
CREATE TABLE `hq_venue_info` (
  `id` bigint(20) NOT NULL COMMENT '场地ID',
  `tenant_id` varchar(20) DEFAULT '000000' COMMENT '租户编号',
  `venue_name` varchar(100) NOT NULL COMMENT '场地名称',
  `venue_code` varchar(50) NOT NULL COMMENT '场地编码',
  `venue_location` varchar(200) NOT NULL COMMENT '场地位置',
  `venue_capacity` int(11) NOT NULL COMMENT '场地容量',
  `venue_type` varchar(20) NOT NULL COMMENT '场地类型',
  `venue_status` char(1) NOT NULL DEFAULT '1' COMMENT '场地状态(0-维修中 1-可用)',
  `venue_description` text COMMENT '场地描述',
  `venue_images` varchar(500) COMMENT '场地图片',
  `venue_manager_id` bigint(20) COMMENT '场地管理员ID',
  `venue_manager_name` varchar(30) COMMENT '场地管理员姓名',
  `create_dept` bigint(20) DEFAULT NULL COMMENT '创建部门',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `deleted` tinyint(4) DEFAULT '0' COMMENT '删除标志(0代表存在 1代表删除)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_venue_code` (`venue_code`),
  KEY `idx_venue_type` (`venue_type`),
  KEY `idx_venue_status` (`venue_status`),
  KEY `idx_venue_manager` (`venue_manager_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='场地信息表';

-- 1.2 场地预约表
DROP TABLE IF EXISTS `hq_venue_reservation`;
CREATE TABLE `hq_venue_reservation` (
  `id` bigint(20) NOT NULL COMMENT '预约ID',
  `tenant_id` varchar(20) DEFAULT '000000' COMMENT '租户编号',
  `venue_id` bigint(20) NOT NULL COMMENT '场地ID',
  `venue_name` varchar(100) NOT NULL COMMENT '场地名称',
  `applicant_id` bigint(20) NOT NULL COMMENT '申请人ID',
  `applicant_name` varchar(30) NOT NULL COMMENT '申请人姓名',
  `applicant_phone` varchar(20) NOT NULL COMMENT '联系方式',
  `reservation_date` date NOT NULL COMMENT '预约日期',
  `start_time` datetime NOT NULL COMMENT '开始时间',
  `end_time` datetime NOT NULL COMMENT '结束时间',
  `reservation_purpose` varchar(200) NOT NULL COMMENT '预约用途',
  `participant_count` int(11) NOT NULL COMMENT '参与人数',
  `special_requirements` text COMMENT '特殊需求',
  `reservation_status` char(1) NOT NULL DEFAULT '0' COMMENT '预约状态(0-待审批 1-已批准 2-已拒绝)',
  `approver_id` bigint(20) COMMENT '审批人ID',
  `approver_name` varchar(30) COMMENT '审批人姓名',
  `approve_time` datetime COMMENT '审批时间',
  `approve_remark` varchar(500) COMMENT '审批意见',
  `create_dept` bigint(20) DEFAULT NULL COMMENT '创建部门',
  `create_by` bigint(20) DEFAULT NULL COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` bigint(20) DEFAULT NULL COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `deleted` tinyint(4) DEFAULT '0' COMMENT '删除标志(0代表存在 1代表删除)',
  PRIMARY KEY (`id`),
  KEY `idx_venue_id` (`venue_id`),
  KEY `idx_applicant_id` (`applicant_id`),
  KEY `idx_reservation_date` (`reservation_date`),
  KEY `idx_reservation_status` (`reservation_status`),
  KEY `idx_time_range` (`start_time`, `end_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='场地预约表';

-- =============================================
-- 2. 字典类型配置
-- =============================================

-- 2.1 场地类型字典
INSERT INTO sys_dict_type (dict_id, tenant_id, dict_name, dict_type, create_dept, create_by, create_time, update_by, update_time, remark) 
VALUES (1777777777777777701, '000000', '场地类型', 'hq_venue_type', 103, 1, NOW(), 1, NOW(), '场地类型字典');

-- 2.2 预约状态字典
INSERT INTO sys_dict_type (dict_id, tenant_id, dict_name, dict_type, create_dept, create_by, create_time, update_by, update_time, remark) 
VALUES (1777777777777777702, '000000', '预约状态', 'hq_reservation_status', 103, 1, NOW(), 1, NOW(), '预约状态字典');

-- =============================================
-- 3. 字典数据配置
-- =============================================

-- 3.1 场地类型字典数据
INSERT INTO sys_dict_data (dict_code, tenant_id, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, create_dept, create_by, create_time, update_by, update_time, remark) 
VALUES (1777777777777777711, '000000', 1, '会议室', 'meeting_room', 'hq_venue_type', '', '', 'N', 103, 1, NOW(), 1, NOW(), '会议室');

INSERT INTO sys_dict_data (dict_code, tenant_id, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, create_dept, create_by, create_time, update_by, update_time, remark) 
VALUES (1777777777777777712, '000000', 2, '教室', 'classroom', 'hq_venue_type', '', '', 'N', 103, 1, NOW(), 1, NOW(), '教室');

INSERT INTO sys_dict_data (dict_code, tenant_id, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, create_dept, create_by, create_time, update_by, update_time, remark) 
VALUES (1777777777777777713, '000000', 3, '运动场', 'sports_field', 'hq_venue_type', '', '', 'N', 103, 1, NOW(), 1, NOW(), '运动场');

INSERT INTO sys_dict_data (dict_code, tenant_id, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, create_dept, create_by, create_time, update_by, update_time, remark) 
VALUES (1777777777777777714, '000000', 4, '礼堂', 'auditorium', 'hq_venue_type', '', '', 'N', 103, 1, NOW(), 1, NOW(), '礼堂');

INSERT INTO sys_dict_data (dict_code, tenant_id, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, create_dept, create_by, create_time, update_by, update_time, remark) 
VALUES (1777777777777777715, '000000', 5, '实验室', 'laboratory', 'hq_venue_type', '', '', 'N', 103, 1, NOW(), 1, NOW(), '实验室');

INSERT INTO sys_dict_data (dict_code, tenant_id, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, create_dept, create_by, create_time, update_by, update_time, remark) 
VALUES (1777777777777777716, '000000', 6, '其他', 'other', 'hq_venue_type', '', '', 'N', 103, 1, NOW(), 1, NOW(), '其他');

-- 3.2 预约状态字典数据
INSERT INTO sys_dict_data (dict_code, tenant_id, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, create_dept, create_by, create_time, update_by, update_time, remark) 
VALUES (1777777777777777721, '000000', 1, '待审批', '0', 'hq_reservation_status', '', 'warning', 'N', 103, 1, NOW(), 1, NOW(), '待审批');

INSERT INTO sys_dict_data (dict_code, tenant_id, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, create_dept, create_by, create_time, update_by, update_time, remark) 
VALUES (1777777777777777722, '000000', 2, '已批准', '1', 'hq_reservation_status', '', 'success', 'N', 103, 1, NOW(), 1, NOW(), '已批准');

INSERT INTO sys_dict_data (dict_code, tenant_id, dict_sort, dict_label, dict_value, dict_type, css_class, list_class, is_default, create_dept, create_by, create_time, update_by, update_time, remark) 
VALUES (1777777777777777723, '000000', 3, '已拒绝', '2', 'hq_reservation_status', '', 'danger', 'N', 103, 1, NOW(), 1, NOW(), '已拒绝');

-- =============================================
-- 4. 菜单权限配置
-- =============================================

-- 4.1 后勤管理菜单(如果不存在)
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_dept, create_by, create_time, update_by, update_time, remark) 
VALUES (2100, '后勤管理', 0, 5, 'hq', NULL, '', 1, 0, 'M', '0', '0', '', 'logistics', 103, 1, NOW(), 1, NOW(), '后勤管理目录') 
ON DUPLICATE KEY UPDATE menu_name='后勤管理';

-- 4.2 场地管理菜单
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_dept, create_by, create_time, update_by, update_time, remark) 
VALUES (2110, '场地管理', 2100, 1, 'venue', 'hq/venue/index', '', 1, 0, 'C', '0', '0', 'hq:venue:list', 'venue', 103, 1, NOW(), 1, NOW(), '场地管理菜单');

-- 4.3 场地管理按钮权限
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_dept, create_by, create_time, update_by, update_time, remark) 
VALUES (2111, '场地查询', 2110, 1, '', '', '', 1, 0, 'F', '0', '0', 'hq:venue:query', '#', 103, 1, NOW(), 1, NOW(), '');

INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_dept, create_by, create_time, update_by, update_time, remark) 
VALUES (2112, '场地新增', 2110, 2, '', '', '', 1, 0, 'F', '0', '0', 'hq:venue:add', '#', 103, 1, NOW(), 1, NOW(), '');

INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_dept, create_by, create_time, update_by, update_time, remark) 
VALUES (2113, '场地修改', 2110, 3, '', '', '', 1, 0, 'F', '0', '0', 'hq:venue:edit', '#', 103, 1, NOW(), 1, NOW(), '');

INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_dept, create_by, create_time, update_by, update_time, remark) 
VALUES (2114, '场地删除', 2110, 4, '', '', '', 1, 0, 'F', '0', '0', 'hq:venue:remove', '#', 103, 1, NOW(), 1, NOW(), '');

-- 4.4 场地预约菜单
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_dept, create_by, create_time, update_by, update_time, remark) 
VALUES (2120, '场地预约', 2100, 2, 'reservation', 'hq/venue/reservation', '', 1, 0, 'C', '0', '0', 'hq:venue:reservation:list', 'calendar', 103, 1, NOW(), 1, NOW(), '场地预约菜单');

-- 4.5 预约按钮权限
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_dept, create_by, create_time, update_by, update_time, remark) 
VALUES (2121, '预约查询', 2120, 1, '', '', '', 1, 0, 'F', '0', '0', 'hq:venue:reservation:query', '#', 103, 1, NOW(), 1, NOW(), '');

INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_dept, create_by, create_time, update_by, update_time, remark) 
VALUES (2122, '预约新增', 2120, 2, '', '', '', 1, 0, 'F', '0', '0', 'hq:venue:reservation:add', '#', 103, 1, NOW(), 1, NOW(), '');

INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_dept, create_by, create_time, update_by, update_time, remark) 
VALUES (2123, '预约修改', 2120, 3, '', '', '', 1, 0, 'F', '0', '0', 'hq:venue:reservation:edit', '#', 103, 1, NOW(), 1, NOW(), '');

INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_dept, create_by, create_time, update_by, update_time, remark) 
VALUES (2124, '预约审批', 2120, 4, '', '', '', 1, 0, 'F', '0', '0', 'hq:venue:reservation:approve', '#', 103, 1, NOW(), 1, NOW(), '');

-- 4.6 场地统计菜单
INSERT INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, query_param, is_frame, is_cache, menu_type, visible, status, perms, icon, create_dept, create_by, create_time, update_by, update_time, remark) 
VALUES (2130, '使用统计', 2100, 3, 'statistics', 'hq/venue/statistics', '', 1, 0, 'C', '0', '0', 'hq:venue:statistics:view', 'chart', 103, 1, NOW(), 1, NOW(), '场地使用统计');

-- =============================================
-- 5. 示例数据插入
-- =============================================

-- 5.1 示例场地数据
INSERT INTO hq_venue_info VALUES 
(1777777777777777777, '000000', '大会议室A', 'MEETING_A001', '行政楼3楼301', 50, 'meeting_room', '1', '配备投影仪、音响设备、空调的大型会议室，适合重要会议和培训活动。', '', 1, 'admin', 103, 1, NOW(), 1, NOW(), 0),
(1777777777777777778, '000000', '多媒体教室B', 'CLASSROOM_B002', '教学楼2楼203', 80, 'classroom', '1', '现代化多媒体教室，配备智能黑板、投影设备，适合各类教学活动。', '', NULL, NULL, 103, 1, NOW(), 1, NOW(), 0),
(1777777777777777779, '000000', '学术报告厅', 'AUDITORIUM_C003', '图书馆1楼', 200, 'auditorium', '1', '大型学术报告厅，配备专业音响、灯光设备，适合学术讲座和大型活动。', '', NULL, NULL, 103, 1, NOW(), 1, NOW(), 0),
(1777777777777777780, '000000', '体育馆', 'SPORTS_D004', '体育中心', 500, 'sports_field', '0', '综合体育馆，包含篮球场、羽毛球场等，目前正在维修中。', '', NULL, NULL, 103, 1, NOW(), 1, NOW(), 0),
(1777777777777777781, '000000', '小会议室C', 'MEETING_C005', '行政楼2楼205', 15, 'meeting_room', '1', '小型会议室，适合部门内部会议和小组讨论。', '', 1, 'admin', 103, 1, NOW(), 1, NOW(), 0);

-- 5.2 示例预约数据
INSERT INTO hq_venue_reservation VALUES 
(1888888888888888888, '000000', 1777777777777777777, '大会议室A', 1, 'admin', '13800138000', '2025-07-05', '2025-07-05 09:00:00', '2025-07-05 11:00:00', '部门工作会议', 20, '需要准备茶水和资料', '1', 1, 'admin', NOW(), '会议安排合理，同意使用', 103, 1, NOW(), 1, NOW(), 0),
(1888888888888888889, '000000', 1777777777777777778, '多媒体教室B', 1, 'admin', '13800138001', '2025-07-06', '2025-07-06 14:00:00', '2025-07-06 16:00:00', '新员工培训', 30, '需要准备培训资料和笔记本', '0', NULL, NULL, NULL, NULL, 103, 1, NOW(), 1, NOW(), 0),
(1888888888888888890, '000000', 1777777777777777779, '学术报告厅', 1, 'admin', '13800138002', '2025-07-07', '2025-07-07 19:00:00', '2025-07-07 21:00:00', '学术讲座活动', 150, '需要话筒和投影设备', '0', NULL, NULL, NULL, NULL, 103, 1, NOW(), 1, NOW(), 0);

-- =============================================
-- 6. 数据完整性约束
-- =============================================

-- 恢复外键检查
SET FOREIGN_KEY_CHECKS = 1;

-- =============================================
-- 脚本执行完成
-- =============================================

SELECT '场地预约管理模块数据库初始化完成！' AS result;
SELECT '包含以下内容：' AS info;
SELECT '1. 场地信息表 (hq_venue_info)' AS item1;
SELECT '2. 场地预约表 (hq_venue_reservation)' AS item2;
SELECT '3. 字典类型和数据配置' AS item3;
SELECT '4. 菜单权限配置' AS item4;
SELECT '5. 示例数据' AS item5;