server/install/mysql/init/exam_reserve_db.sql

create table t_system_property
(
    id          bigint       not null auto_increment,
    create_time bigint       not null comment '创建时间',
    update_time bigint       not null comment '更新时间',
    prop_key    varchar(50)  not null comment '属性键',
    prop_value  varchar(500) not null comment '属性值',
    org_id      bigint comment '所属机构ID（学校）',
    PRIMARY KEY (id),
    UNIQUE KEY IDX_01 (org_id, prop_key)
) comment '系统属性表';


create table t_org
(
    id          bigint      not null auto_increment,
    create_time bigint      not null comment '创建时间',
    update_time bigint      not null comment '更新时间',
    enable      bit(1)      not null comment '是否启用',
    name        varchar(50) not null comment '机构名称',
    PRIMARY KEY (id)
) comment '机构表';


create table t_user
(
    id          bigint      not null auto_increment,
    create_time bigint      not null comment '创建时间',
    update_time bigint      not null comment '更新时间',
    enable      bit(1)      not null comment '是否启用',
    role        varchar(20) not null comment '角色类型：学校管理员；教学点管理员',
    name        varchar(50) not null comment '姓名',
    login_name  varchar(50) not null comment '登录名',
    password    varchar(64) not null comment '密码',
    mobile      varchar(20) comment '联系方式',
    category_id bigint comment '所属教学点ID，角色为教学点管理员，必填',
    org_id      bigint      not null comment '所属机构ID（学校）',
    first_login bit(1)      comment '第一次修改密码标志',
    PRIMARY KEY (id),
    UNIQUE KEY IDX_01 (org_id, login_name),
    KEY IDX_02 (role),
    KEY IDX_03 (category_id)
) comment '用户表';


create table t_student
(
    id              bigint      not null auto_increment,
    create_time     bigint      not null comment '创建时间',
    update_time     bigint      not null comment '更新时间',
    org_id          bigint      not null comment '所属机构ID（学校）',
    name            varchar(50) not null comment '考生姓名',
    identity_number varchar(20) not null comment '证件号',
    student_code    varchar(20) not null comment '学号',
    password        varchar(64) not null comment '密码',
    photo_path      varchar(100) comment '头像相对路径',
    gender          varchar(10) comment '性别',
    open_id         varchar(50) comment '微信OID',
    uid             varchar(50) comment '微信UID',
    apply_task_id   bigint      not null comment '所属预约任务ID',
    category_id     bigint      not null comment '所属教学点ID',
    apply_number    int         not null comment '允许预约时段数量',
    apply_finished  bit(1)      not null comment '预约时段是否全部完成',
    PRIMARY KEY (id),
    UNIQUE KEY IDX_01 (apply_task_id, student_code),
    UNIQUE KEY IDX_02 (apply_task_id, open_id),
    KEY IDX_03 (category_id),
    KEY IDX_04 (identity_number)
) comment '考生表';


create table t_category
(
    id                bigint      not null auto_increment,
    create_time       bigint      not null comment '创建时间',
    update_time       bigint      not null comment '更新时间',
    enable            bit(1)      not null comment '是否启用',
    org_id            bigint      not null comment '所属机构ID（学校）',
    parent_id         bigint      not null default 0 comment '父级ID，顶级值为0',
    code              varchar(20) not null comment '分类代码',
    name              varchar(50) not null comment '分类名称',
    level             int         not null comment '层级，第一级值为1',
    capacity          int         null     default 0 comment '容量',
    self_apply_enable bit(1)      null     default 1 comment '针对教学点，是否开启考生自主预约',
    PRIMARY KEY (id),
    UNIQUE KEY IDX_01 (org_id, code),
    KEY IDX_02 (parent_id),
    KEY IDX_03 (level)
) comment '数据分类表';


create table t_exam_site
(
    id          bigint       not null auto_increment,
    create_time bigint       not null comment '创建时间',
    update_time bigint       not null comment '更新时间',
    enable      bit(1)       not null comment '是否启用',
    code        varchar(20)  not null comment '考点代码',
    name        varchar(100) not null comment '考点名称',
    category_id bigint       not null comment '所属教学点ID',
    capacity    int comment '机房容量',
    address     varchar(200) comment '考点地址',
    guide       longtext comment '考点指引',
    PRIMARY KEY (id),
    UNIQUE KEY IDX_01 (category_id, code)
) comment '考点表';


create table t_exam_room
(
    id           bigint       not null auto_increment,
    create_time  bigint       not null comment '创建时间',
    update_time  bigint       not null comment '更新时间',
    enable       bit(1)       not null comment '是否启用',
    code         varchar(20)  not null comment '考场代码',
    name         varchar(100) not null comment '考场名称',
    exam_site_id bigint       not null comment '所属考点ID',
    address      varchar(200) comment '考场地址',
    capacity     int comment '机房容量',
    PRIMARY KEY (id),
    UNIQUE KEY IDX_01 (exam_site_id, code)
) comment '考场表';


create table t_apply_task
(
    id                      bigint      not null auto_increment,
    create_time             bigint      not null comment '创建时间',
    update_time             bigint      not null comment '更新时间',
    enable                  bit(1)      not null comment '是否启用',
    org_id                  bigint      not null comment '所属机构ID（学校）',
    name                    varchar(50) not null comment '任务名称',
    allow_apply_days        int         not null comment '考前多少天，禁止考生自主预约',
    allow_apply_cancel_days int         not null comment '考前多少天，禁止考生自主取消预约',
    self_apply_start_time   bigint      not null comment '自主预约起始时间，只能预约本教学点下的考点',
    self_apply_end_time     bigint      not null comment '自主预约截止时间，只能预约本教学点下的考点',
    open_apply_start_time   bigint      not null comment '开放式预约起始时间，可以在不同教学点间预约',
    open_apply_end_time     bigint      not null comment '开放式预约截止时间，可以在不同教学点间预约',
    notice                  longtext comment '考试说明',
    PRIMARY KEY (id),
    KEY IDX_01 (org_id)
) comment '预约任务表';


create table t_time_period
(
    id            bigint not null auto_increment,
    create_time   bigint not null comment '创建时间',
    update_time   bigint not null comment '更新时间',
    apply_task_id bigint not null comment '所属预约任务ID',
    start_time    bigint not null comment '预约起始时间',
    end_time      bigint not null comment '预约截止时间',
    PRIMARY KEY (id),
    KEY IDX_01 (apply_task_id),
    KEY IDX_02 (start_time),
    KEY IDX_03 (end_time)
) comment '预约任务的时段表';


create table t_student_apply
(
    id             bigint not null auto_increment,
    create_time    bigint not null comment '创建时间',
    update_time    bigint not null comment '更新时间',
    exam_site_id   bigint not null comment '预约的考点ID',
    time_period_id bigint not null comment '预约时段ID',
    student_id     bigint not null comment '考生ID',
    cancel         bit(1) not null comment '是否取消预约',
    exam_room_id   bigint comment '预约的考场ID',
    ticket_number  varchar(50) comment '准考证号',
    seat_number    varchar(20) comment '座位号',
    operate_id     bigint comment '操作人ID',
    PRIMARY KEY (id),
    UNIQUE KEY IDX_01 (exam_site_id, time_period_id, student_id),
    KEY IDX_02 (student_id),
    KEY IDX_03 (exam_room_id),
    KEY IDX_04 (ticket_number)
) comment '考生预约记录表';


create table t_student_import_task
(
    id            bigint       not null auto_increment,
    create_time   bigint       not null comment '创建时间',
    update_time   bigint       not null comment '更新时间',
    operate_id    bigint       not null comment '操作人ID',
    apply_task_id bigint       not null comment '所属预约任务ID',
    type          varchar(20)  not null comment '导入类型',
    status        varchar(20)  not null comment '执行状态',
    file_path     varchar(200) not null comment '导入文件相对路径',
    message       text comment '执行结果信息',
    PRIMARY KEY (id),
    KEY IDX_01 (apply_task_id),
    KEY IDX_02 (type),
    KEY IDX_03 (status)
) comment '考生导入任务表';


create table t_operate_log
(
    id          bigint      not null auto_increment,
    create_time bigint      not null comment '创建时间',
    update_time bigint      not null comment '更新时间',
    operate_id  bigint      not null comment '操作人ID',
    event_type  varchar(20) not null comment '事件类型，如：考生取消预约、用户登录等',
    content     text comment '操作内容（JSON）',
    PRIMARY KEY (id),
    KEY IDX_01 (event_type),
    KEY IDX_02 (operate_id)
) comment '操作日志表';

create table t_async_task
(
    id               bigint       NOT NULL AUTO_INCREMENT,
    create_time      bigint       NOT NULL COMMENT '创建时间',
    update_time      bigint       NOT NULL COMMENT '更新时间',
    operate_id       bigint       NOT NULL COMMENT '操作人ID',
    type             varchar(50)  NOT NULL COMMENT '任务类型',
    status           varchar(10)  NOT NULL COMMENT '执行状态任务状态，INIT：未开始，RUNNING：进行中，FINISH：已完成',
    result           varchar(10)  NULL COMMENT '执行结果，SUCCESS：成功，ERROR：失败',
    import_file_name varchar(100) NULL COMMENT '导入文件名称',
    import_file_path varchar(500) NULL COMMENT '导入文件路径',
    export_file_path varchar(500) NULL COMMENT '导出文件路径',
    summary          text         NULL COMMENT '执行摘要',
    PRIMARY KEY (id),
    INDEX IDX_01 (type),
    INDEX IDX_02 (status),
    INDEX IDX_03 (result)
) COMMENT = '异步任务表';

create table t_student_course
(
    id          bigint      NOT NULL AUTO_INCREMENT,
    create_time bigint      NOT NULL COMMENT '创建时间',
    update_time bigint      NOT NULL COMMENT '更新时间',
    student_id  bigint      NOT NULL COMMENT '考生ID',
    course_code varchar(20) NOT NULL COMMENT '科目代码',
    course_name varchar(50) NULL COMMENT '科目名称',
    PRIMARY KEY (id),
    UNIQUE INDEX IDX_01 (course_code, student_id),
    INDEX IDX_02 (student_id)
) COMMENT = '考生科目表';


create table t_time_period_exam_site
(
    id             bigint NOT NULL AUTO_INCREMENT,
    create_time    bigint NOT NULL COMMENT '创建时间',
    update_time    bigint NOT NULL COMMENT '更新时间',
    operate_id     bigint NOT NULL COMMENT '操作人',
    exam_site_id   bigint NOT NULL COMMENT '考点ID',
    time_period_id bigint NOT NULL COMMENT '预约时段ID',
    enable         bit(1) NOT NULL COMMENT '是否开启',
    PRIMARY KEY (id),
    UNIQUE INDEX IDX_01 (exam_site_id, time_period_id),
    INDEX IDX_02 (time_period_id)
) COMMENT = '考点预约时段表';


-- 基础数据
insert into t_org(id, name, enable, create_time, update_time)
values (1, '广东开放大学', 1, unix_timestamp() * 1000, unix_timestamp() * 1000);

insert into t_system_property(org_id, prop_key, prop_value, create_time, update_time)
values (1, 'CATEGORY_LEVEL', '[{"level":1,"title":"城市"},{"level":2,"title":"教学点"}]', unix_timestamp() * 1000,
        unix_timestamp() * 1000);

insert into t_user (org_id, role, name, login_name, password, mobile, category_id, enable, create_time, update_time)
values (1, 'ADMIN', '学校管理员', 'admin', UPPER(SHA2('qmth87863577', 256)), null, null, 1, unix_timestamp() * 1000,
        unix_timestamp() * 1000);

-- 城市初始化
INSERT INTO t_category VALUES (1, 1717121654419, 1717121654419, b'1', 1, 0, '4401', '广州市', 1, 0,1);
INSERT INTO t_category VALUES (2, 1717121654419, 1717121654419, b'1', 1, 0, '4402', '韶关市', 1, 0,1);
INSERT INTO t_category VALUES (3, 1717121654419, 1717121654419, b'1', 1, 0, '4403', '深圳市', 1, 0,1);
INSERT INTO t_category VALUES (4, 1717121654419, 1717121654419, b'1', 1, 0, '4404', '珠海市', 1, 0,1);
INSERT INTO t_category VALUES (5, 1717121654419, 1717121654419, b'1', 1, 0, '4405', '汕头市', 1, 0,1);
INSERT INTO t_category VALUES (6, 1717121654419, 1717121654419, b'1', 1, 0, '4406', '佛山市', 1, 0,1);
INSERT INTO t_category VALUES (7, 1717121654419, 1717121654419, b'1', 1, 0, '4407', '江门市', 1, 0,1);
INSERT INTO t_category VALUES (8, 1717121654419, 1717121654419, b'1', 1, 0, '4408', '湛江市', 1, 0,1);
INSERT INTO t_category VALUES (9, 1717121654419, 1717121654419, b'1', 1, 0, '4409', '茂名市', 1, 0,1);
INSERT INTO t_category VALUES (10, 1717121654419, 1717121654419, b'1', 1, 0, '4412', '肇庆市', 1, 0,1);
INSERT INTO t_category VALUES (11, 1717121654419, 1717121654419, b'1', 1, 0, '4413', '惠州市', 1, 0,1);
INSERT INTO t_category VALUES (12, 1717121654419, 1717121654419, b'1', 1, 0, '4414', '梅州市', 1, 0,1);
INSERT INTO t_category VALUES (13, 1717121654419, 1717121654419, b'1', 1, 0, '4415', '汕尾市', 1, 0,1);
INSERT INTO t_category VALUES (14, 1717121654419, 1717121654419, b'1', 1, 0, '4416', '河源市', 1, 0,1);
INSERT INTO t_category VALUES (15, 1717121654419, 1717121654419, b'1', 1, 0, '4417', '阳江市', 1, 0,1);
INSERT INTO t_category VALUES (16, 1717121654419, 1717121654419, b'1', 1, 0, '4418', '清远市', 1, 0,1);
INSERT INTO t_category VALUES (17, 1717121654419, 1717121654419, b'1', 1, 0, '4419', '东莞市', 1, 0,1);
INSERT INTO t_category VALUES (18, 1717121654419, 1717121654419, b'1', 1, 0, '4420', '中山市', 1, 0,1);
INSERT INTO t_category VALUES (19, 1717121654419, 1717121654419, b'1', 1, 0, '4451', '潮州市', 1, 0,1);
INSERT INTO t_category VALUES (20, 1717121654419, 1717121654419, b'1', 1, 0, '4452', '揭阳市', 1, 0,1);
INSERT INTO t_category VALUES (21, 1717121654419, 1717121654419, b'1', 1, 0, '4453', '云浮市', 1, 0,1);