-- MySQL建表语句（从Oracle转换）
-- 系统管理表

-- 部门表
CREATE TABLE sys_dept (
    dept_id INT NOT NULL AUTO_INCREMENT,
    parent_id INT NULL,
    ancestors VARCHAR(50) NULL,
    dept_code VARCHAR(50) NULL,
    dept_name VARCHAR(600) NOT NULL,
    order_num INT NULL,
    leader VARCHAR(30) NULL,
    phone VARCHAR(20) NULL,
    email VARCHAR(50) NULL,
    status CHAR(1) NULL,
    del_flag CHAR(1) NULL,
    create_by VARCHAR(100) NULL,
    create_time DATETIME NULL,
    update_by VARCHAR(100) NULL,
    update_time DATETIME NULL,
    PRIMARY KEY (dept_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';

-- 用户表
CREATE TABLE sys_user (
    user_id INT NOT NULL AUTO_INCREMENT,
    user_code VARCHAR(100) NULL,
    dept_id INT NULL,
    user_name VARCHAR(100) NULL,
    nick_name VARCHAR(30) NOT NULL,
    user_type CHAR(2) NULL,
    email VARCHAR(50) NULL,
    phonenumber VARCHAR(20) NULL,
    sex CHAR(1) NULL,
    avatar VARCHAR(300) NULL,
    password VARCHAR(100) NULL,
    status CHAR(1) NULL,
    del_flag CHAR(1) NULL,
    login_ip VARCHAR(128) NULL,
    login_date DATETIME NULL,
    create_by VARCHAR(100) NULL,
    create_time DATETIME NULL,
    update_by VARCHAR(100) NULL,
    update_time DATETIME NULL,
    remark VARCHAR(600) NULL,
    PRIMARY KEY (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

-- 岗位表
CREATE TABLE sys_post (
    post_id INT NOT NULL AUTO_INCREMENT,
    post_code VARCHAR(64) NULL,
    post_name VARCHAR(50) NOT NULL,
    post_sort INT NULL,
    status CHAR(1) NULL,
    del_flag CHAR(1) NULL,
    create_by VARCHAR(100) NULL,
    create_time DATETIME NULL,
    update_by VARCHAR(100) NULL,
    update_time DATETIME NULL,
    remark VARCHAR(600) NULL,
    PRIMARY KEY (post_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='岗位表';

-- 角色表
CREATE TABLE sys_role (
    role_id INT NOT NULL AUTO_INCREMENT,
    role_name VARCHAR(90) NOT NULL,
    role_key VARCHAR(200) NULL,
    role_sort INT NULL,
    data_scope CHAR(1) NULL,
    menu_check_strictly INT NULL,
    dept_check_strictly INT NULL,
    status CHAR(1) NULL,
    del_flag CHAR(1) NULL,
    create_by VARCHAR(100) NULL,
    create_time DATETIME NULL,
    update_by VARCHAR(100) NULL,
    update_time DATETIME NULL,
    remark VARCHAR(600) NULL,
    PRIMARY KEY (role_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色表';

-- 菜单表
CREATE TABLE sys_menu (
    menu_id INT NOT NULL AUTO_INCREMENT,
    menu_name VARCHAR(120) NOT NULL,
    parent_id INT NULL,
    order_num INT NULL,
    path VARCHAR(200) NULL,
    component VARCHAR(300) NULL,
    is_frame INT NULL,
    is_cache INT NULL,
    menu_type CHAR(1) NULL,
    visible CHAR(1) NULL,
    status CHAR(1) NULL,
    del_flag CHAR(1) NULL,
    perms VARCHAR(100) NULL,
    icon VARCHAR(100) NULL,
    create_by VARCHAR(100) NULL,
    create_time DATETIME NULL,
    update_by VARCHAR(100) NULL,
    update_time DATETIME NULL,
    remark VARCHAR(600) NULL,
    PRIMARY KEY (menu_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='菜单表';

-- 用户角色关联表
CREATE TABLE sys_user_role (
    user_id INT NOT NULL,
    role_id INT NOT NULL,
    PRIMARY KEY (user_id, role_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户角色关联表';

-- 角色菜单关联表
CREATE TABLE sys_role_menu (
    role_id INT NOT NULL,
    menu_id INT NOT NULL,
    PRIMARY KEY (role_id, menu_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色菜单关联表';

-- 角色部门关联表
CREATE TABLE sys_role_dept (
    role_id INT NOT NULL,
    dept_id INT NOT NULL,
    PRIMARY KEY (role_id, dept_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色部门关联表';

-- 用户岗位关联表
CREATE TABLE sys_user_post (
    user_id INT NOT NULL,
    post_id INT NOT NULL,
    PRIMARY KEY (user_id, post_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户岗位关联表';

-- 操作日志表
CREATE TABLE sys_oper_log (
    oper_id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(120) NOT NULL,
    business_type INT NULL,
    method VARCHAR(120) NULL,
    request_method VARCHAR(120) NULL,
    operator_type INT NULL,
    oper_name VARCHAR(50) NULL,
    dept_name VARCHAR(120) NULL,
    oper_url VARCHAR(300) NULL,
    oper_ip VARCHAR(128) NULL,
    oper_location VARCHAR(300) NULL,
    oper_param TEXT NULL,
    json_result TEXT NULL,
    status CHAR(1) NULL,
    error_msg TEXT NULL,
    oper_time DATETIME NULL,
    PRIMARY KEY (oper_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='操作日志表';

-- 字典类型表
CREATE TABLE sys_dict_type (
    dict_id INT NOT NULL AUTO_INCREMENT,
    dict_name VARCHAR(200) NOT NULL,
    dict_type VARCHAR(50) NULL,
    status CHAR(1) NULL,
    create_by VARCHAR(100) NULL,
    create_time DATETIME NULL,
    update_by VARCHAR(100) NULL,
    update_time DATETIME NULL,
    remark VARCHAR(600) NULL,
    PRIMARY KEY (dict_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='字典类型表';

-- 字典数据表
CREATE TABLE sys_dict_data (
    dict_code INT NOT NULL AUTO_INCREMENT,
    dict_sort INT NULL,
    dict_label VARCHAR(100) NOT NULL,
    dict_value VARCHAR(100) NOT NULL,
    dict_type VARCHAR(100) NULL,
    css_class VARCHAR(100) NULL,
    list_class VARCHAR(100) NULL,
    is_default CHAR(1) NULL,
    status CHAR(1) NULL,
    create_by VARCHAR(100) NULL,
    create_time DATETIME NULL,
    update_by VARCHAR(100) NULL,
    update_time DATETIME NULL,
    remark VARCHAR(600) NULL,
    PRIMARY KEY (dict_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='字典数据表';

-- 参数配置表
CREATE TABLE sys_config (
    config_id INT NOT NULL AUTO_INCREMENT,
    config_name VARCHAR(100) NOT NULL,
    config_key VARCHAR(100) NULL,
    config_value VARCHAR(100) NULL,
    config_type CHAR(1) NULL,
    create_by VARCHAR(100) NULL,
    create_time DATETIME NULL,
    update_by VARCHAR(100) NULL,
    update_time DATETIME NULL,
    remark VARCHAR(600) NULL,
    PRIMARY KEY (config_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='参数配置表';

-- 登录日志表
CREATE TABLE sys_logininfor (
    info_id INT NOT NULL AUTO_INCREMENT,
    user_name VARCHAR(50) NOT NULL,
    ipaddr VARCHAR(128) NULL,
    login_location VARCHAR(300) NULL,
    browser VARCHAR(50) NULL,
    os VARCHAR(50) NULL,
    status CHAR(1) NULL,
    msg VARCHAR(600) NULL,
    login_time DATETIME NULL,
    PRIMARY KEY (info_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='登录日志表';

-- 定时任务表
CREATE TABLE sys_job (
    job_id INT NOT NULL AUTO_INCREMENT,
    job_name VARCHAR(120) NOT NULL,
    job_group VARCHAR(120) NULL,
    invoke_target VARCHAR(500) NULL,
    cron_expression VARCHAR(300) NULL,
    misfire_policy VARCHAR(20) NULL,
    concurrent CHAR(1) NULL,
    status CHAR(1) NULL,
    create_by VARCHAR(100) NULL,
    create_time DATETIME NULL,
    update_by VARCHAR(100) NULL,
    update_time DATETIME NULL,
    remark VARCHAR(600) NULL,
    PRIMARY KEY (job_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='定时任务表';

-- 定时任务日志表
CREATE TABLE sys_job_log (
    job_log_id INT NOT NULL AUTO_INCREMENT,
    job_name VARCHAR(120) NOT NULL,
    job_group VARCHAR(120) NULL,
    invoke_target VARCHAR(500) NULL,
    job_message VARCHAR(500) NULL,
    status CHAR(1) NULL,
    exception_info TEXT NULL,
    create_time DATETIME NULL,
    PRIMARY KEY (job_log_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='定时任务日志表';

-- 通知公告表
CREATE TABLE sys_notice (
    notice_id INT NOT NULL AUTO_INCREMENT,
    notice_title VARCHAR(120) NOT NULL,
    notice_type CHAR(1) NULL,
    notice_content TEXT NULL,
    status CHAR(1) NULL,
    create_by VARCHAR(100) NULL,
    create_time DATETIME NULL,
    update_by VARCHAR(100) NULL,
    update_time DATETIME NULL,
    remark VARCHAR(600) NULL,
    PRIMARY KEY (notice_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通知公告表';

-- 用户组表
CREATE TABLE sys_group (
    group_id INT NOT NULL AUTO_INCREMENT,
    group_code VARCHAR(50) NOT NULL,
    group_name VARCHAR(120) NOT NULL,
    group_describe VARCHAR(200) NOT NULL,
    status CHAR(1) NULL,
    create_by VARCHAR(100) NULL,
    create_time DATETIME NULL,
    PRIMARY KEY (group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户组表';

-- 用户组成员表
CREATE TABLE sys_group_person (
    group_code VARCHAR(50) NOT NULL,
    person_code VARCHAR(120) NOT NULL,
    order_id INT NULL,
    PRIMARY KEY (group_code, person_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户组成员表';

-- 用户选择表
CREATE TABLE sys_user_choice (
	id INT NOT NULL AUTO_INCREMENT,
    choice_code VARCHAR(50) NOT NULL,
    user_code VARCHAR(100) NULL,
    order_id INT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户选择表';

-- 代码生成业务表
CREATE TABLE gen_table (
    table_id INT NOT NULL AUTO_INCREMENT,
    table_name VARCHAR(120) NOT NULL,
    table_comment VARCHAR(500) NULL,
    sub_table_name VARCHAR(120) NULL,
    sub_table_fk_name VARCHAR(120) NULL,
    class_name VARCHAR(100) NULL,
    tpl_category VARCHAR(200) NULL,
    package_name VARCHAR(100) NULL,
    module_name VARCHAR(30) NULL,
    business_name VARCHAR(30) NULL,
    function_name VARCHAR(50) NULL,
    function_author VARCHAR(50) NULL,
    gen_type CHAR(1) NULL,
    gen_path VARCHAR(200) NULL,
    options VARCHAR(1000) NULL,
    status CHAR(1) NULL,
    create_by VARCHAR(100) NULL,
    create_time DATETIME NULL,
    update_by VARCHAR(100) NULL,
    update_time DATETIME NULL,
    remark VARCHAR(600) NULL,
    PRIMARY KEY (table_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='代码生成业务表';

-- 代码生成业务表字段
CREATE TABLE gen_table_column (
    column_id INT NOT NULL AUTO_INCREMENT,
    table_id INT NOT NULL,
    column_name VARCHAR(200) NOT NULL,
    column_comment VARCHAR(500) NOT NULL,
    column_type VARCHAR(100) NULL,
    java_type VARCHAR(500) NULL,
    java_field VARCHAR(200) NULL,
    is_pk CHAR(1) NULL,
    is_increment CHAR(1) NULL,
    is_required CHAR(1) NULL,
    is_insert CHAR(1) NULL,
    is_edit CHAR(1) NULL,
    is_list CHAR(1) NULL,
    is_query CHAR(1) NULL,
    query_type VARCHAR(200) NULL,
    html_type VARCHAR(200) NULL,
    dict_type VARCHAR(200) NULL,
    sort INT NULL,
    create_by VARCHAR(100) NULL,
    create_time DATETIME NULL,
    update_by VARCHAR(100) NULL,
    update_time DATETIME NULL,
    remark VARCHAR(600) NULL,
    PRIMARY KEY (column_id, table_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='代码生成业务表字段';

-- Quartz定时任务相关表

-- 存储每一个已配置的jobDetail的详细信息
CREATE TABLE qrtz_job_details (
    sched_name VARCHAR(120) NOT NULL,
    job_name VARCHAR(200) NOT NULL,
    job_group VARCHAR(200) NOT NULL,
    description VARCHAR(250) NULL,
    job_class_name VARCHAR(250) NOT NULL,
    is_durable VARCHAR(1) NOT NULL,
    is_nonconcurrent VARCHAR(1) NOT NULL,
    is_update_data VARCHAR(1) NOT NULL,
    requests_recovery VARCHAR(1) NOT NULL,
    job_data BLOB NULL,
    PRIMARY KEY (sched_name, job_name, job_group)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Quartz任务详情表';

-- 存储已配置的Trigger的信息
CREATE TABLE qrtz_triggers (
    sched_name VARCHAR(120) NOT NULL,
    trigger_name VARCHAR(200) NOT NULL,
    trigger_group VARCHAR(200) NOT NULL,
    job_name VARCHAR(200) NOT NULL,
    job_group VARCHAR(200) NOT NULL,
    description VARCHAR(250) NULL,
    next_fire_time BIGINT NULL,
    prev_fire_time BIGINT NULL,
    priority BIGINT NULL,
    trigger_state VARCHAR(16) NOT NULL,
    trigger_type VARCHAR(8) NOT NULL,
    start_time BIGINT NOT NULL,
    end_time BIGINT NULL,
    calendar_name VARCHAR(200) NULL,
    misfire_instr SMALLINT NULL,
    job_data BLOB NULL,
    PRIMARY KEY (sched_name, trigger_name, trigger_group),
    FOREIGN KEY (sched_name, job_name, job_group) 
        REFERENCES qrtz_job_details(sched_name, job_name, job_group)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Quartz触发器表';

-- 存储简单的Trigger，包括重复次数、间隔以及已触发的次数
CREATE TABLE qrtz_simple_triggers (
    sched_name VARCHAR(120) NOT NULL,
    trigger_name VARCHAR(200) NOT NULL,
    trigger_group VARCHAR(200) NOT NULL,
    repeat_count INT NOT NULL,
    repeat_interval BIGINT NOT NULL,
    times_triggered INT NOT NULL,
    PRIMARY KEY (sched_name, trigger_name, trigger_group),
    FOREIGN KEY (sched_name, trigger_name, trigger_group) 
        REFERENCES qrtz_triggers(sched_name, trigger_name, trigger_group)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Quartz简单触发器表';

-- 存储Cron Trigger，包括Cron表达式和时区信息
CREATE TABLE qrtz_cron_triggers (
    sched_name VARCHAR(120) NOT NULL,
    trigger_name VARCHAR(200) NOT NULL,
    trigger_group VARCHAR(200) NOT NULL,
    cron_expression VARCHAR(120) NOT NULL,
    time_zone_id VARCHAR(80),
    PRIMARY KEY (sched_name, trigger_name, trigger_group),
    FOREIGN KEY (sched_name, trigger_name, trigger_group) 
        REFERENCES qrtz_triggers(sched_name, trigger_name, trigger_group)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Quartz Cron触发器表';

-- 存储Trigger作为Blob类型存储(用于Quartz用户用JDBC创建他们自己定制的Trigger类型，JobStore并不知道如何存储实例的时候)
CREATE TABLE qrtz_blob_triggers (
    sched_name VARCHAR(120) NOT NULL,
    trigger_name VARCHAR(200) NOT NULL,
    trigger_group VARCHAR(200) NOT NULL,
    blob_data BLOB NULL,
    PRIMARY KEY (sched_name, trigger_name, trigger_group),
    FOREIGN KEY (sched_name, trigger_name, trigger_group) 
        REFERENCES qrtz_triggers(sched_name, trigger_name, trigger_group)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Quartz Blob触发器表';

-- 以Blob类型存储Quartz的日历信息，quartz可配置一个日历来指定一个时间范围
CREATE TABLE qrtz_calendars (
    sched_name VARCHAR(120) NOT NULL,
    calendar_name VARCHAR(200) NOT NULL,
    calendar BLOB NOT NULL,
    PRIMARY KEY (sched_name, calendar_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Quartz日历表';

-- 存储已暂停的Trigger组的信息
CREATE TABLE qrtz_paused_trigger_grps (
    sched_name VARCHAR(120) NOT NULL,
    trigger_group VARCHAR(200) NOT NULL,
    PRIMARY KEY (sched_name, trigger_group)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Quartz暂停触发器组表';

-- 存储已触发Trigger的相关状态信息，以及相关联Job的执行信息
CREATE TABLE qrtz_fired_triggers (
    sched_name VARCHAR(120) NOT NULL,
    entry_id VARCHAR(95) NOT NULL,
    trigger_name VARCHAR(200) NOT NULL,
    trigger_group VARCHAR(200) NOT NULL,
    instance_name VARCHAR(200) NOT NULL,
    fired_time BIGINT NOT NULL,
    sched_time BIGINT NOT NULL,
    priority BIGINT NOT NULL,
    state VARCHAR(16) NOT NULL,
    job_name VARCHAR(200) NULL,
    job_group VARCHAR(200) NULL,
    is_nonconcurrent VARCHAR(1) NULL,
    requests_recovery VARCHAR(1) NULL,
    PRIMARY KEY (sched_name, entry_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Quartz已触发触发器表';

-- 存储所有的Scheduler的状态信息，假如是在集群中，可以看到其他的Scheduler实例
CREATE TABLE qrtz_scheduler_state (
    sched_name VARCHAR(120) NOT NULL,
    instance_name VARCHAR(200) NOT NULL,
    last_checkin_time BIGINT NOT NULL,
    checkin_interval BIGINT NOT NULL,
    PRIMARY KEY (sched_name, instance_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Quartz调度器状态表';

-- 存储程序的悲观锁的信息(假如使用了悲观锁)
CREATE TABLE qrtz_locks (
    sched_name VARCHAR(120) NOT NULL,
    lock_name VARCHAR(40) NOT NULL,
    PRIMARY KEY (sched_name, lock_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Quartz锁表';

-- 存储Trigger的其他属性信息
CREATE TABLE qrtz_simprop_triggers (
    sched_name VARCHAR(120) NOT NULL,
    trigger_name VARCHAR(200) NOT NULL,
    trigger_group VARCHAR(200) NOT NULL,
    str_prop_1 VARCHAR(512) NULL,
    str_prop_2 VARCHAR(512) NULL,
    str_prop_3 VARCHAR(512) NULL,
    int_prop_1 INT NULL,
    int_prop_2 INT NULL,
    long_prop_1 BIGINT NULL,
    long_prop_2 BIGINT NULL,
    dec_prop_1 DECIMAL(13,4) NULL,
    dec_prop_2 DECIMAL(13,4) NULL,
    bool_prop_1 VARCHAR(1) NULL,
    bool_prop_2 VARCHAR(1) NULL,
    PRIMARY KEY (sched_name, trigger_name, trigger_group),
    FOREIGN KEY (sched_name, trigger_name, trigger_group) 
        REFERENCES qrtz_triggers(sched_name, trigger_name, trigger_group)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Quartz属性触发器表';

-- 创建Quartz相关索引
CREATE INDEX idx_qrtz_j_req_recovery ON qrtz_job_details(sched_name, requests_recovery);
CREATE INDEX idx_qrtz_j_grp ON qrtz_job_details(sched_name, job_group);

CREATE INDEX idx_qrtz_t_j ON qrtz_triggers(sched_name, job_name, job_group);
CREATE INDEX idx_qrtz_t_jg ON qrtz_triggers(sched_name, job_group);
CREATE INDEX idx_qrtz_t_c ON qrtz_triggers(sched_name, calendar_name);
CREATE INDEX idx_qrtz_t_g ON qrtz_triggers(sched_name, trigger_group);
CREATE INDEX idx_qrtz_t_state ON qrtz_triggers(sched_name, trigger_state);
CREATE INDEX idx_qrtz_t_n_state ON qrtz_triggers(sched_name, trigger_name, trigger_group, trigger_state);
CREATE INDEX idx_qrtz_t_n_g_state ON qrtz_triggers(sched_name, trigger_group, trigger_state);
CREATE INDEX idx_qrtz_t_next_fire_time ON qrtz_triggers(sched_name, next_fire_time);
CREATE INDEX idx_qrtz_t_nft_st ON qrtz_triggers(sched_name, trigger_state, next_fire_time);
CREATE INDEX idx_qrtz_t_nft_misfire ON qrtz_triggers(sched_name, misfire_instr, next_fire_time);
CREATE INDEX idx_qrtz_t_nft_st_misfire ON qrtz_triggers(sched_name, misfire_instr, next_fire_time, trigger_state);
CREATE INDEX idx_qrtz_t_nft_st_misfire_grp ON qrtz_triggers(sched_name, misfire_instr, next_fire_time, trigger_group, trigger_state);

CREATE INDEX idx_qrtz_ft_trig_inst_name ON qrtz_fired_triggers(sched_name, instance_name);
CREATE INDEX idx_qrtz_ft_inst_job_req_rcvry ON qrtz_fired_triggers(sched_name, instance_name, requests_recovery);
CREATE INDEX idx_qrtz_ft_j_g ON qrtz_fired_triggers(sched_name, job_name, job_group);
CREATE INDEX idx_qrtz_ft_jg ON qrtz_fired_triggers(sched_name, job_group);
CREATE INDEX idx_qrtz_ft_t_g ON qrtz_fired_triggers(sched_name, trigger_name, trigger_group);
CREATE INDEX idx_qrtz_ft_tg ON qrtz_fired_triggers(sched_name, trigger_group);

-- 文件管理表
CREATE TABLE fms_photo (
    photo_id INT NOT NULL AUTO_INCREMENT,
    file_name VARCHAR(600) NOT NULL,
    photo_path VARCHAR(100) NOT NULL,
    photo_url VARCHAR(100) NOT NULL,
    original_name VARCHAR(600) NULL,
    upload_name VARCHAR(100) NULL,
    upload_dept VARCHAR(50) NULL,
    upload_time DATETIME NULL,
    status INT NULL,
    PRIMARY KEY (photo_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='图片管理表';

CREATE TABLE fms_files (
    file_id INT NOT NULL AUTO_INCREMENT,
    file_title VARCHAR(120) NULL,
    file_describe VARCHAR(300) NULL,
    file_name VARCHAR(600) NOT NULL,
    file_path VARCHAR(100) NOT NULL,
    file_url VARCHAR(100) NOT NULL,
    file_suffix VARCHAR(30) NULL,
    original_name VARCHAR(600) NULL,
    upload_name VARCHAR(100) NULL,
    upload_dept VARCHAR(50) NULL,
    upload_time DATETIME NULL,
    status INT NULL,
    PRIMARY KEY (file_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='文件管理表';

-- 考试系统表

-- 考试类型表
CREATE TABLE exam_type (
    type_id INT NOT NULL AUTO_INCREMENT,
    parent_id INT NULL,
    ancestors VARCHAR(50) NULL,
    type_code VARCHAR(50) NULL,
    type_name VARCHAR(120) NOT NULL,
    order_id INT NULL,
    status CHAR(1) NULL,
    create_by VARCHAR(100) NULL,
    create_time DATETIME NULL,
    update_by VARCHAR(100) NULL,
    update_time DATETIME NULL,
    PRIMARY KEY (type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考试类型表';

-- 题库表
CREATE TABLE exam_questions_bank (
    bank_id INT NOT NULL AUTO_INCREMENT,
    bank_code VARCHAR(50) NOT NULL,
    bank_name VARCHAR(120) NOT NULL,
    bank_describe VARCHAR(300) NULL,
    bank_version VARCHAR(60) NULL,
    exam_type INT NULL,
    online_date DATETIME NULL,
    picture_url VARCHAR(600) NULL,
    radio_score INT NULL,
    choice_score INT NULL,
    judge_score INT NULL,
    status INT NULL,
    create_by VARCHAR(100) NULL,
    create_dept INT NULL,
    create_time DATETIME NULL,
    PRIMARY KEY (bank_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='题库表';

-- 题库图片表
CREATE TABLE exam_bank_picture (
    photo_id INT NOT NULL AUTO_INCREMENT,
    photo_code VARCHAR(200) NOT NULL,
    photo_path VARCHAR(200) NULL,
    photo_url VARCHAR(200) NULL,
    file_name VARCHAR(60) NULL,
    original_name VARCHAR(200) NULL,
    create_by VARCHAR(100) NULL,
    create_time DATETIME NULL,
    status INT NULL,
    PRIMARY KEY (photo_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='题库图片表';

-- 题目内容表
CREATE TABLE exam_questions_content (
    questions_code VARCHAR(50) NOT NULL,
    questions_content TEXT NOT NULL,
    PRIMARY KEY (questions_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='题目内容表';

-- 题目属性表
CREATE TABLE exam_questions_property (
    bank_code VARCHAR(50) NOT NULL,
    questions_id INT NOT NULL,
    questions_code VARCHAR(50) NOT NULL,
    questions_title VARCHAR(4000) NULL,
    questions_type INT NULL,
    questions_score INT NULL,
    rate_number INT NULL,
    right_answer VARCHAR(20) NULL,
    answer_analyse VARCHAR(3000) NULL,
    status INT NULL,
    create_by VARCHAR(100) NULL,
    create_dept INT NULL,
    create_time DATETIME NULL,
    PRIMARY KEY (questions_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='题目属性表';

-- 题目答案表
CREATE TABLE exam_questions_answer (
    questions_code VARCHAR(50) NOT NULL,
    option_code CHAR(1) NOT NULL,
    option_describe VARCHAR(600) NOT NULL,
    is_right INT NULL,
    order_id INT NULL,
    PRIMARY KEY (questions_code, option_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='题目答案表';

-- 题目表
CREATE TABLE exam_questions (
    bank_code VARCHAR(50) NOT NULL,
    questions_id INT NOT NULL,
    questions_code VARCHAR(50) NOT NULL,
    questions_title VARCHAR(4000) NULL,
    questions_content TEXT NULL,
    questions_type INT NULL,
    questions_score INT NULL,
    rate_number INT NULL,
    right_answer VARCHAR(20) NULL,
    answer_analyse VARCHAR(3000) NULL,
    status INT NULL,
    create_by VARCHAR(100) NULL,
    create_dept INT NULL,
    create_time DATETIME NULL,
    PRIMARY KEY (questions_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='题目表';

-- 考试任务管理表
CREATE TABLE exam_task_manager (
    exam_id INT NOT NULL AUTO_INCREMENT,
    exam_code VARCHAR(50) NOT NULL,
    exam_name VARCHAR(120) NOT NULL,
    exam_describe VARCHAR(300) NULL,
    build_type CHAR(1) NULL,
    force_done CHAR(1) NULL,
    exam_bank VARCHAR(600) NULL,
    exam_bank_text VARCHAR(1200) NULL,
    picture_url VARCHAR(200) NULL,
    start_time DATETIME NULL,
    end_time DATETIME NULL,
    exam_duration VARCHAR(30) NULL,
    status INT NULL,
    create_by VARCHAR(100) NULL,
    create_dept INT NULL,
    create_time DATETIME NULL,
    PRIMARY KEY (exam_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考试任务管理表';

-- 考试任务图片表
CREATE TABLE exam_task_picture (
    photo_id INT NOT NULL AUTO_INCREMENT,
    photo_code VARCHAR(200) NOT NULL,
    photo_path VARCHAR(200) NULL,
    photo_url VARCHAR(200) NULL,
    file_name VARCHAR(60) NULL,
    original_name VARCHAR(200) NULL,
    create_by VARCHAR(100) NULL,
    create_time DATETIME NULL,
    status INT NULL,
    PRIMARY KEY (photo_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考试任务图片表';

-- 考试任务题目关联表
CREATE TABLE exam_task_questions (
    exam_code VARCHAR(50) NOT NULL,
    questions_code VARCHAR(50) NOT NULL,
    questions_number INT NULL,
    PRIMARY KEY (exam_code, questions_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考试任务题目关联表';

-- 考试任务用户组关联表
CREATE TABLE exam_task_group (
    exam_code VARCHAR(50) NOT NULL,
    group_code VARCHAR(50) NOT NULL,
    order_id INT NULL,
    PRIMARY KEY (exam_code, group_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考试任务用户组关联表';

-- 考试任务人员表
CREATE TABLE exam_task_person (
    exam_code VARCHAR(50) NOT NULL,
    user_code VARCHAR(120) NOT NULL,
    task_code VARCHAR(50) NOT NULL,
    start_time DATETIME NULL,
    end_time DATETIME NULL,
    status INT NULL,
    PRIMARY KEY (exam_code, user_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考试任务人员表';

-- 用户考试成绩表
CREATE TABLE exam_user_score (
    exam_code VARCHAR(50) NOT NULL,
    user_code VARCHAR(120) NOT NULL,
    start_time DATETIME NULL,
    end_time DATETIME NULL,
    questions_score DECIMAL(8,2) NULL,
    exam_score DECIMAL(8,2) NULL,
    exam_number INT NULL,
    answered_number INT NULL,
    not_answered INT NULL,
    status INT NULL,
    PRIMARY KEY (exam_code, user_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户考试成绩表';

-- 考试答题记录表
CREATE TABLE exam_task_answer (
    exam_code VARCHAR(50) NOT NULL,
    user_code VARCHAR(100) NOT NULL,
    questions_code VARCHAR(50) NOT NULL,
    questions_number INT NOT NULL,
    questions_type INT NOT NULL,
    questions_answer VARCHAR(20) NULL,
    is_mark CHAR(1) NULL,
    is_current CHAR(1) NULL,
    PRIMARY KEY (exam_code, questions_code, user_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考试答题记录表';

-- 考试完成答案表
CREATE TABLE exam_finish_answer (
    exam_code VARCHAR(50) NOT NULL,
    user_code VARCHAR(100) NOT NULL,
    questions_code VARCHAR(50) NOT NULL,
    exam_score DECIMAL(8,2) NULL,
    right_answer VARCHAR(20) NULL,
    questions_answer VARCHAR(20) NULL,
    questions_score INT NULL,
    PRIMARY KEY (exam_code, questions_code, user_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='考试完成答案表';