# Consider dropping these tables after March 2024
CREATE TABLE IF NOT EXISTS `tbd_pre_work_schedule_job_history`
(
    `id`                         BIGINT PRIMARY KEY AUTO_INCREMENT,
    `employee_number`            INT(7)     NOT NULL,
    `job_title_id`               INT(13),
    `job_title_name`             VARCHAR(100),
    `employment_status_id`       INT,
    `employment_status_name`     VARCHAR(60),
    `job_category_id`            INT,
    `job_category_name`          VARCHAR(60),
    `joined_date`                DATE,
    `probation_end_date`         DATE,
    `date_of_permanency`         DATE,
    `effective_date`             DATE,
    `event_id`                   INT(6),
    `subunit_id`                 INT(6),
    `subunit_name`               VARCHAR(100),
    `location_id`                INT,
    `location_name`              VARCHAR(110),
    `comment`                    VARCHAR(250),
    `has_contract_details`       TINYINT UNSIGNED    DEFAULT 0,
    `contract_start_date`        DATE,
    `contract_end_date`          DATE,
    `contract_attachment_id`     INT,
    `work_shift_id`              int unsigned        DEFAULT NULL,
    `work_shift_name`            VARCHAR(250),
    `modified_time`              datetime            DEFAULT NULL,
    `modified_by_id`             BIGINT(20)          DEFAULT NULL,
    `modified_by_name`           VARCHAR(100)        DEFAULT NULL,
    `cost_centre_id`             BIGINT              DEFAULT NULL,
    `cost_centre_name`           VARCHAR(60)         DEFAULT NULL,
    `eeo_job_category`           int        null,
    `eeo_job_category_name`      VARCHAR(45)         DEFAULT null,
    `flsa_exempt`                int        null,
    `pay_policy_id`              INT                 default null,
    `pay_policy_name`            VARCHAR(255)        default null,
    `readonly`                   TINYINT(1) NOT NULL DEFAULT 0,
    `notified_on_effective_date` TINYINT(1) NOT NULL DEFAULT 1
) ENGINE = InnoDB
  DEFAULT CHARSET = UTF8;

CREATE TABLE IF NOT EXISTS `tbd_post_work_schedule_job_history`
(
    `id`                         BIGINT PRIMARY KEY AUTO_INCREMENT,
    `employee_number`            INT(7)     NOT NULL,
    `job_title_id`               INT(13),
    `job_title_name`             VARCHAR(100),
    `employment_status_id`       INT,
    `employment_status_name`     VARCHAR(60),
    `job_category_id`            INT,
    `job_category_name`          VARCHAR(60),
    `joined_date`                DATE,
    `probation_end_date`         DATE,
    `date_of_permanency`         DATE,
    `effective_date`             DATE,
    `event_id`                   INT(6),
    `subunit_id`                 INT(6),
    `subunit_name`               VARCHAR(100),
    `location_id`                INT,
    `location_name`              VARCHAR(110),
    `comment`                    VARCHAR(250),
    `has_contract_details`       TINYINT UNSIGNED    DEFAULT 0,
    `contract_start_date`        DATE,
    `contract_end_date`          DATE,
    `contract_attachment_id`     INT,
    `work_schedule_id`           int unsigned        DEFAULT NULL,
    `work_schedule_name`         VARCHAR(250),
    `modified_time`              datetime            DEFAULT NULL,
    `modified_by_id`             BIGINT(20)          DEFAULT NULL,
    `modified_by_name`           VARCHAR(100)        DEFAULT NULL,
    `cost_centre_id`             BIGINT              DEFAULT NULL,
    `cost_centre_name`           VARCHAR(60)         DEFAULT NULL,
    `eeo_job_category`           int        null,
    `eeo_job_category_name`      VARCHAR(45)         DEFAULT null,
    `flsa_exempt`                int        null,
    `pay_policy_id`              INT                 default null,
    `pay_policy_name`            VARCHAR(255)        default null,
    `readonly`                   TINYINT(1) NOT NULL DEFAULT 0,
    `notified_on_effective_date` TINYINT(1) NOT NULL DEFAULT 1
) ENGINE = InnoDB
  DEFAULT CHARSET = UTF8;

CREATE TABLE `tbd_employee_work_week`
(
    `emp_number`       INT(11) NOT NULL,
    `day_id`           INT(11) NOT NULL,
    `hours_per_day`    DECIMAL(7, 5) DEFAULT NULL,
    `shift_start_time` TIME          DEFAULT NULL,
    `shift_end_time`   TIME          DEFAULT NULL,
    `lunch_start_time` TIME          DEFAULT NULL,
    `lunch_end_time`   TIME          DEFAULT NULL,
    `day_type_id`      INT(11) NOT NULL,
    PRIMARY KEY (`emp_number`, `day_id`)
) engine = innodb
  DEFAULT charset = utf8;
