CREATE TABLE IF NOT EXISTS sys_dept (
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
uuid VARCHAR(64) NOT NULL,
|
version BIGINT NOT NULL DEFAULT 1,
|
create_id BIGINT NULL,
|
create_time DATETIME NULL,
|
update_id BIGINT NULL,
|
update_time DATETIME NULL,
|
del_flag INT NOT NULL DEFAULT 0,
|
parent_id BIGINT NOT NULL DEFAULT 0,
|
dept_name VARCHAR(128) NOT NULL,
|
sort_order INT NOT NULL DEFAULT 0,
|
UNIQUE KEY uk_sys_dept_uuid (uuid)
|
);
|
|
CREATE TABLE IF NOT EXISTS sys_user (
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
uuid VARCHAR(64) NOT NULL,
|
version BIGINT NOT NULL DEFAULT 1,
|
create_id BIGINT NULL,
|
create_time DATETIME NULL,
|
update_id BIGINT NULL,
|
update_time DATETIME NULL,
|
del_flag INT NOT NULL DEFAULT 0,
|
username VARCHAR(64) NOT NULL,
|
password_hash VARCHAR(255) NOT NULL,
|
real_name VARCHAR(64) NULL,
|
mobile VARCHAR(32) NULL,
|
dept_id BIGINT NULL,
|
status INT NOT NULL DEFAULT 1,
|
UNIQUE KEY uk_sys_user_uuid (uuid),
|
UNIQUE KEY uk_sys_user_username (username)
|
);
|
|
CREATE TABLE IF NOT EXISTS sys_role (
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
uuid VARCHAR(64) NOT NULL,
|
version BIGINT NOT NULL DEFAULT 1,
|
create_id BIGINT NULL,
|
create_time DATETIME NULL,
|
update_id BIGINT NULL,
|
update_time DATETIME NULL,
|
del_flag INT NOT NULL DEFAULT 0,
|
role_code VARCHAR(64) NOT NULL,
|
role_name VARCHAR(128) NOT NULL,
|
UNIQUE KEY uk_sys_role_uuid (uuid),
|
UNIQUE KEY uk_sys_role_code (role_code)
|
);
|
|
CREATE TABLE IF NOT EXISTS sys_user_role (
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
uuid VARCHAR(64) NOT NULL,
|
version BIGINT NOT NULL DEFAULT 1,
|
create_id BIGINT NULL,
|
create_time DATETIME NULL,
|
update_id BIGINT NULL,
|
update_time DATETIME NULL,
|
del_flag INT NOT NULL DEFAULT 0,
|
user_id BIGINT NOT NULL,
|
role_id BIGINT NOT NULL,
|
UNIQUE KEY uk_sys_user_role_uuid (uuid),
|
KEY idx_user_role (user_id, role_id)
|
);
|
|
CREATE TABLE IF NOT EXISTS appointment_order (
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
uuid VARCHAR(64) NOT NULL,
|
version BIGINT NOT NULL DEFAULT 1,
|
create_id BIGINT NULL,
|
create_time DATETIME NULL,
|
update_id BIGINT NULL,
|
update_time DATETIME NULL,
|
del_flag INT NOT NULL DEFAULT 0,
|
order_no VARCHAR(64) NOT NULL,
|
visit_type VARCHAR(32) NOT NULL,
|
visited_user_id BIGINT NOT NULL,
|
visited_user_name VARCHAR(64) NULL,
|
visit_reason_code VARCHAR(64) NULL,
|
visitor_org VARCHAR(255) NULL,
|
visit_start_date DATE NOT NULL,
|
visit_end_date DATE NOT NULL,
|
status VARCHAR(32) NOT NULL,
|
holiday_flag TINYINT(1) NOT NULL DEFAULT 0,
|
key_area_flag TINYINT(1) NOT NULL DEFAULT 0,
|
submitter_open_id VARCHAR(128) NULL,
|
reject_reason VARCHAR(512) NULL,
|
UNIQUE KEY uk_appointment_order_uuid (uuid),
|
UNIQUE KEY uk_order_no (order_no),
|
KEY idx_visited_user (visited_user_id),
|
KEY idx_status (status)
|
);
|
|
CREATE TABLE IF NOT EXISTS appointment_visitor (
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
uuid VARCHAR(64) NOT NULL,
|
version BIGINT NOT NULL DEFAULT 1,
|
create_id BIGINT NULL,
|
create_time DATETIME NULL,
|
update_id BIGINT NULL,
|
update_time DATETIME NULL,
|
del_flag INT NOT NULL DEFAULT 0,
|
order_id BIGINT NOT NULL,
|
name VARCHAR(64) NOT NULL,
|
id_card_cipher VARCHAR(512) NULL,
|
mobile VARCHAR(32) NULL,
|
driver_name VARCHAR(64) NULL,
|
plate_no VARCHAR(32) NULL,
|
UNIQUE KEY uk_appointment_visitor_uuid (uuid),
|
KEY idx_order (order_id)
|
);
|
|
CREATE TABLE IF NOT EXISTS approval_instance (
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
uuid VARCHAR(64) NOT NULL,
|
version BIGINT NOT NULL DEFAULT 1,
|
create_id BIGINT NULL,
|
create_time DATETIME NULL,
|
update_id BIGINT NULL,
|
update_time DATETIME NULL,
|
del_flag INT NOT NULL DEFAULT 0,
|
order_id BIGINT NOT NULL,
|
current_node_code VARCHAR(64) NULL,
|
status VARCHAR(32) NOT NULL,
|
finished_at DATETIME NULL,
|
UNIQUE KEY uk_approval_instance_uuid (uuid),
|
KEY idx_order (order_id)
|
);
|
|
CREATE TABLE IF NOT EXISTS approval_task (
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
uuid VARCHAR(64) NOT NULL,
|
version BIGINT NOT NULL DEFAULT 1,
|
create_id BIGINT NULL,
|
create_time DATETIME NULL,
|
update_id BIGINT NULL,
|
update_time DATETIME NULL,
|
del_flag INT NOT NULL DEFAULT 0,
|
instance_id BIGINT NOT NULL,
|
node_code VARCHAR(64) NOT NULL,
|
group_key VARCHAR(128) NOT NULL,
|
assignee_user_id BIGINT NOT NULL,
|
task_status VARCHAR(32) NOT NULL,
|
comment VARCHAR(512) NULL,
|
UNIQUE KEY uk_approval_task_uuid (uuid),
|
KEY idx_instance (instance_id),
|
KEY idx_assignee_status (assignee_user_id, task_status)
|
);
|
|
CREATE TABLE IF NOT EXISTS biz_visit_reason (
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
uuid VARCHAR(64) NOT NULL,
|
version BIGINT NOT NULL DEFAULT 1,
|
create_id BIGINT NULL,
|
create_time DATETIME NULL,
|
update_id BIGINT NULL,
|
update_time DATETIME NULL,
|
del_flag INT NOT NULL DEFAULT 0,
|
reason_code VARCHAR(64) NOT NULL,
|
reason_name VARCHAR(128) NOT NULL,
|
sort_order INT NOT NULL DEFAULT 0,
|
enabled INT NOT NULL DEFAULT 1,
|
UNIQUE KEY uk_biz_visit_reason_uuid (uuid),
|
UNIQUE KEY uk_reason_code (reason_code)
|
);
|
|
CREATE TABLE IF NOT EXISTS biz_field_config (
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
uuid VARCHAR(64) NOT NULL,
|
version BIGINT NOT NULL DEFAULT 1,
|
create_id BIGINT NULL,
|
create_time DATETIME NULL,
|
update_id BIGINT NULL,
|
update_time DATETIME NULL,
|
del_flag INT NOT NULL DEFAULT 0,
|
field_code VARCHAR(64) NOT NULL,
|
field_name VARCHAR(128) NOT NULL,
|
visible INT NOT NULL DEFAULT 1,
|
required_flag INT NOT NULL DEFAULT 0,
|
sort_order INT NOT NULL DEFAULT 0,
|
UNIQUE KEY uk_biz_field_config_uuid (uuid),
|
UNIQUE KEY uk_field_code (field_code)
|
);
|
|
CREATE TABLE IF NOT EXISTS biz_approval_node_config (
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
uuid VARCHAR(64) NOT NULL,
|
version BIGINT NOT NULL DEFAULT 1,
|
create_id BIGINT NULL,
|
create_time DATETIME NULL,
|
update_id BIGINT NULL,
|
update_time DATETIME NULL,
|
del_flag INT NOT NULL DEFAULT 0,
|
node_code VARCHAR(64) NOT NULL,
|
node_name VARCHAR(128) NOT NULL,
|
enabled INT NOT NULL DEFAULT 1,
|
sort_order INT NOT NULL DEFAULT 0,
|
UNIQUE KEY uk_biz_approval_node_uuid (uuid),
|
UNIQUE KEY uk_node_code (node_code)
|
);
|
|
CREATE TABLE IF NOT EXISTS biz_op_log (
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
uuid VARCHAR(64) NOT NULL,
|
version BIGINT NOT NULL DEFAULT 1,
|
create_id BIGINT NULL,
|
create_time DATETIME NULL,
|
update_id BIGINT NULL,
|
update_time DATETIME NULL,
|
del_flag INT NOT NULL DEFAULT 0,
|
operator_user_id BIGINT NULL,
|
module VARCHAR(64) NOT NULL,
|
action VARCHAR(64) NOT NULL,
|
resource_type VARCHAR(64) NULL,
|
resource_id VARCHAR(128) NULL,
|
detail_json TEXT NULL,
|
op_time DATETIME NOT NULL,
|
trace_id VARCHAR(64) NULL,
|
UNIQUE KEY uk_biz_op_log_uuid (uuid),
|
KEY idx_op_time (op_time)
|
);
|
|
CREATE TABLE IF NOT EXISTS qrcode_ticket (
|
id BIGINT AUTO_INCREMENT PRIMARY KEY,
|
uuid VARCHAR(64) NOT NULL,
|
version BIGINT NOT NULL DEFAULT 1,
|
create_id BIGINT NULL,
|
create_time DATETIME NULL,
|
update_id BIGINT NULL,
|
update_time DATETIME NULL,
|
del_flag INT NOT NULL DEFAULT 0,
|
order_id BIGINT NOT NULL,
|
payload TEXT NOT NULL,
|
ticket_status VARCHAR(32) NOT NULL,
|
used_at DATETIME NULL,
|
UNIQUE KEY uk_qrcode_ticket_uuid (uuid),
|
KEY idx_order (order_id)
|
);
|
|
-- 默认部门
|
INSERT INTO sys_dept (uuid, version, parent_id, dept_name, sort_order, del_flag)
|
VALUES (REPLACE(UUID(), '-', ''), 1, 0, '总公司', 0, 0);
|
|
SET @dept_id = LAST_INSERT_ID();
|
|
-- 角色
|
INSERT INTO sys_role (uuid, version, role_code, role_name, del_flag) VALUES
|
(REPLACE(UUID(), '-', ''), 1, 'ADMIN', '系统管理员', 0),
|
(REPLACE(UUID(), '-', ''), 1, 'HANDLER', '经办人', 0),
|
(REPLACE(UUID(), '-', ''), 1, 'DEPT_LEADER', '部门负责人', 0),
|
(REPLACE(UUID(), '-', ''), 1, 'VISIT_DEPT', '访客主管部门', 0),
|
(REPLACE(UUID(), '-', ''), 1, 'VISIT_DEPT_LEADER', '访客主管部门领导', 0),
|
(REPLACE(UUID(), '-', ''), 1, 'KEY_DEPT_LEADER', '要害管理部门领导', 0);
|
|
-- 管理员用户（密码：Admin@123456aA，BCrypt）
|
INSERT INTO sys_user (uuid, version, username, password_hash, real_name, mobile, dept_id, status, del_flag)
|
VALUES (
|
REPLACE(UUID(), '-', ''), 1,
|
'admin',
|
'$2a$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
|
'管理员', '13800000000', @dept_id, 1, 0
|
);
|
|
SET @admin_id = LAST_INSERT_ID();
|
|
INSERT INTO sys_user (uuid, version, username, password_hash, real_name, mobile, dept_id, status, del_flag)
|
VALUES (
|
REPLACE(UUID(), '-', ''), 1,
|
'handler',
|
'$2a$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
|
'经办人A', '13800000001', @dept_id, 1, 0
|
);
|
SET @handler_id = LAST_INSERT_ID();
|
|
INSERT INTO sys_user (uuid, version, username, password_hash, real_name, mobile, dept_id, status, del_flag)
|
VALUES (
|
REPLACE(UUID(), '-', ''), 1,
|
'leader',
|
'$2a$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
|
'部门领导', '13800000002', @dept_id, 1, 0
|
);
|
SET @leader_id = LAST_INSERT_ID();
|
|
-- 角色绑定：admin=ADMIN；handler=HANDLER；leader=DEPT_LEADER + HANDLER 便于测试
|
INSERT INTO sys_user_role (uuid, version, user_id, role_id, del_flag)
|
SELECT REPLACE(UUID(), '-', ''), 1, @admin_id, id, 0 FROM sys_role WHERE role_code = 'ADMIN';
|
|
INSERT INTO sys_user_role (uuid, version, user_id, role_id, del_flag)
|
SELECT REPLACE(UUID(), '-', ''), 1, @handler_id, id, 0 FROM sys_role WHERE role_code = 'HANDLER';
|
|
INSERT INTO sys_user_role (uuid, version, user_id, role_id, del_flag)
|
SELECT REPLACE(UUID(), '-', ''), 1, @leader_id, id, 0 FROM sys_role WHERE role_code = 'DEPT_LEADER';
|
|
INSERT INTO sys_user_role (uuid, version, user_id, role_id, del_flag)
|
SELECT REPLACE(UUID(), '-', ''), 1, @leader_id, id, 0 FROM sys_role WHERE role_code = 'HANDLER';
|
|
-- 来访事由示例
|
INSERT INTO biz_visit_reason (uuid, version, reason_code, reason_name, sort_order, enabled, del_flag)
|
VALUES (REPLACE(UUID(), '-', ''), 1, 'BUSINESS', '商务洽谈', 1, 1, 0);
|
|
-- 预约字段示例
|
INSERT INTO biz_field_config (uuid, version, field_code, field_name, visible, required_flag, sort_order, del_flag)
|
VALUES
|
(REPLACE(UUID(), '-', ''), 1, 'visitorOrg', '来访单位', 1, 0, 1, 0),
|
(REPLACE(UUID(), '-', ''), 1, 'idCard', '身份证号', 1, 1, 2, 0);
|
|
-- 审批节点（默认全开，按 sort 流转）
|
INSERT INTO biz_approval_node_config (uuid, version, node_code, node_name, enabled, sort_order, del_flag)
|
VALUES
|
(REPLACE(UUID(), '-', ''), 1, 'LEVEL1_HANDLER', '经办人（一级）', 1, 10, 0),
|
(REPLACE(UUID(), '-', ''), 1, 'LEVEL2_DEPT_LEADER', '部门负责人（二级）', 1, 20, 0),
|
(REPLACE(UUID(), '-', ''), 1, 'LEVEL3_VISIT_DEPT', '访客主管部门（三级）', 0, 30, 0),
|
(REPLACE(UUID(), '-', ''), 1, 'LEVEL4_VISIT_DEPT_LEADER', '访客主管部门领导（四级）', 1, 40, 0),
|
(REPLACE(UUID(), '-', ''), 1, 'LEVEL5_KEY_DEPT_LEADER', '要害管理部门领导（五级）', 1, 50, 0);
|
