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);