CREATE TABLE IF NOT EXISTS  class (
    class_id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    prio INT DEFAULT 0,
    m_code VARCHAR(4) NOT NULL COMMENT '{"text":3, "number":1}',
    owner_id BIGINT NOT NULL,
    data_state VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    create_user_id BIGINT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_user_id BIGINT,
    update_time TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (class_id),
    FOREIGN KEY (owner_id) REFERENCES PREFIXconfig.owner(owner_id),
    FOREIGN KEY (create_user_id) REFERENCES PREFIXsystem.user(user_id),
    FOREIGN KEY (update_user_id) REFERENCES PREFIXsystem.user(user_id),
    CONSTRAINT unique_m_code UNIQUE (m_code, owner_id)
);

CREATE TABLE IF NOT EXISTS  teacher (
    teacher_id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(10) NOT NULL,
    m_code VARCHAR(4) NOT NULL COMMENT '{"text":3, "number":1}',
    foreground VARCHAR(50) DEFAULT '',
    background VARCHAR(50) DEFAULT '',
    owner_id BIGINT NOT NULL,
    data_state VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    create_user_id BIGINT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_user_id BIGINT,
    update_time TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (teacher_id),
    FOREIGN KEY (owner_id) REFERENCES PREFIXconfig.owner(owner_id),
    FOREIGN KEY (create_user_id) REFERENCES PREFIXsystem.user(user_id),
    FOREIGN KEY (update_user_id) REFERENCES PREFIXsystem.user(user_id),
    CONSTRAINT unique_m_code UNIQUE (m_code, owner_id),
    CONSTRAINT unique_m_code UNIQUE (code, owner_id, data_state)
);

CREATE TABLE IF NOT EXISTS  hour (
     hour_id BIGINT NOT NULL AUTO_INCREMENT,
     name VARCHAR(100) NOT NULL,
     start_time INT NOT NULL,
     end_time INT NOT NULL,
     owner_id BIGINT NOT NULL,
     data_state VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
     create_user_id BIGINT,
     create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
     update_user_id BIGINT,
     update_time TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     PRIMARY KEY (hour_id),
     FOREIGN KEY (owner_id) REFERENCES PREFIXconfig.owner(owner_id),
     FOREIGN KEY (create_user_id) REFERENCES PREFIXsystem.user(user_id),
     FOREIGN KEY (update_user_id) REFERENCES PREFIXsystem.user(user_id)
);

CREATE TABLE IF NOT EXISTS  timetable (
     timetable_id BIGINT NOT NULL AUTO_INCREMENT,
     name VARCHAR(100) NOT NULL,
     m_code VARCHAR(6) NOT NULL COMMENT '{"text":5, "number":1}',
     start_date BIGINT NOT NULL,
     end_date BIGINT DEFAULT NULL,
     owner_id BIGINT NOT NULL,
     data_state VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
     create_user_id BIGINT,
     create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
     update_user_id BIGINT,
     update_time TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
     PRIMARY KEY (timetable_id),
     FOREIGN KEY (owner_id) REFERENCES PREFIXconfig.owner(owner_id),
     FOREIGN KEY (create_user_id) REFERENCES PREFIXsystem.user(user_id),
     FOREIGN KEY (update_user_id) REFERENCES PREFIXsystem.user(user_id),
     CONSTRAINT unique_m_code UNIQUE (m_code, owner_id)
);

CREATE TABLE day (
                     day_id BIGINT NOT NULL AUTO_INCREMENT,
                     name varchar(100) NOT NULL,
                     m_code varchar(4) NOT NULL COMMENT '{"text":3, "number":1}',
                     sequence INT DEFAULT 0,
                     system_tag varchar(3) NOT NULL,
                     owner_id BIGINT NOT NULL,
                     data_state VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
                     create_user_id BIGINT,
                     create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                     update_user_id BIGINT,
                     update_time TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                     PRIMARY KEY (day_id),
                     FOREIGN KEY (owner_id) REFERENCES PREFIXconfig.owner(owner_id),
                     FOREIGN KEY (create_user_id) REFERENCES PREFIXsystem.user(user_id),
                     FOREIGN KEY (update_user_id) REFERENCES PREFIXsystem.user(user_id),
                     CONSTRAINT unique_m_code UNIQUE (m_code, owner_id)
);

CREATE TABLE IF NOT EXISTS  event (
    event_id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL DEFAULT '',
    timetable_id BIGINT NOT NULL,
    class_id BIGINT NOT NULL,
    day_id BIGINT NOT NULL,
    hour_id BIGINT NOT NULL,
    owner_id BIGINT NOT NULL,
    data_state VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    create_user_id BIGINT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_user_id BIGINT,
    update_time TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (event_id),
    FOREIGN KEY (timetable_id) REFERENCES timetable(timetable_id),
    FOREIGN KEY (hour_id) REFERENCES hour(hour_id),
    FOREIGN KEY (owner_id) REFERENCES PREFIXconfig.owner(owner_id),
    FOREIGN KEY (create_user_id) REFERENCES PREFIXsystem.user(user_id),
    FOREIGN KEY (update_user_id) REFERENCES PREFIXsystem.user(user_id),
    FOREIGN KEY (day_id) REFERENCES day(day_id),
    FOREIGN KEY (class_id) REFERENCES class(class_id)
);

CREATE TABLE IF NOT EXISTS  key_event_teacher (
    key_id BIGINT NOT NULL AUTO_INCREMENT,
    event_id BIGINT NOT NULL,
    teacher_id BIGINT NOT NULL,
    PRIMARY KEY (key_id),
    FOREIGN KEY (event_id) REFERENCES event(event_id),
    FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id)
);

CREATE TABLE IF NOT EXISTS  unavailability (
    unavailability_id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) DEFAULT '',
    teacher_id BIGINT NOT NULL,
    start_datetime BIGINT NOT NULL,
    end_datetime BIGINT NOT NULL,
    owner_id BIGINT NOT NULL,
    data_state VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    create_user_id BIGINT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_user_id BIGINT,
    update_time TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (unavailability_id),
    FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id),
    FOREIGN KEY (owner_id) REFERENCES PREFIXconfig.owner(owner_id),
    FOREIGN KEY (create_user_id) REFERENCES PREFIXsystem.user(user_id),
    FOREIGN KEY (update_user_id) REFERENCES PREFIXsystem.user(user_id)
);

CREATE TABLE action (
                        action_id BIGINT NOT NULL AUTO_INCREMENT,
                        name varchar(100) NOT NULL,
                        info varchar(100) NOT NULL,
                        m_code varchar(4) NOT NULL COMMENT '{"text":4, "number":1}',
                        owner_id BIGINT NOT NULL,
                        data_state VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
                        create_user_id BIGINT,
                        create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        update_user_id BIGINT,
                        update_time TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                        PRIMARY KEY (action_id),
                        FOREIGN KEY (owner_id) REFERENCES PREFIXconfig.owner(owner_id),
                        FOREIGN KEY (create_user_id) REFERENCES PREFIXsystem.user(user_id),
                        FOREIGN KEY (update_user_id) REFERENCES PREFIXsystem.user(user_id),
                        CONSTRAINT unique_m_code UNIQUE (m_code, owner_id)
);

ALTER TABLE replacement ADD action_id BIGINT DEFAULT NULL AFTER teacher_id;
ALTER TABLE replacement ADD FOREIGN KEY (action_id) REFERENCES action(action_id);
ALTER TABLE replacement DROP COLUMN type;

CREATE TABLE IF NOT EXISTS  replacement (
    replacement_id BIGINT NOT NULL AUTO_INCREMENT,
    unavailability_id BIGINT NOT NULL,
    event_id BIGINT NOT NULL,
    teacher_id BIGINT DEFAULT NULL,
    action_id BIGINT DEFAULT NULL,
    info VARCHAR(100) DEFAULT '',
    owner_id BIGINT NOT NULL,
    data_state VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
    create_user_id BIGINT,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_user_id BIGINT,
    update_time TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (replacement_id),
    FOREIGN KEY (unavailability_id) REFERENCES unavailability(unavailability_id),
    FOREIGN KEY (event_id) REFERENCES event(event_id),
    FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id),
    FOREIGN KEY (action_id) REFERENCES action(action_id),
    FOREIGN KEY (owner_id) REFERENCES PREFIXconfig.owner(owner_id),
    FOREIGN KEY (create_user_id) REFERENCES PREFIXsystem.user(user_id),
    FOREIGN KEY (update_user_id) REFERENCES PREFIXsystem.user(user_id)
);

