CREATE SEQUENCE IF NOT EXISTS back_pay_seq START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE IF NOT EXISTS bonus_seq START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE IF NOT EXISTS employee_seq START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE IF NOT EXISTS hotel_seq START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE IF NOT EXISTS mileage_seq START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE IF NOT EXISTS new_hire_seq START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE IF NOT EXISTS null_seq START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE IF NOT EXISTS per_diem_seq START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE IF NOT EXISTS portal_user_seq START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE IF NOT EXISTS propane_seq START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE IF NOT EXISTS pto_seq START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE IF NOT EXISTS store_seq START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE IF NOT EXISTS target_order_seq START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE IF NOT EXISTS termination_seq START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE IF NOT EXISTS time_adjustment_seq START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE IF NOT EXISTS uniform_seq START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE IF NOT EXISTS user_role_seq START WITH 1 INCREMENT BY 1;

CREATE SEQUENCE IF NOT EXISTS work_ticket_seq START WITH 1 INCREMENT BY 1;

CREATE TABLE back_pay
(
    id             BIGINT NOT NULL,
    portal_user_id BIGINT,
    employee_id    BIGINT,
    store_id       BIGINT,
    pay_missed     VARCHAR(255),
    sig            text,
    CONSTRAINT pk_backpay PRIMARY KEY (id)
);

CREATE TABLE bonus
(
    id             BIGINT NOT NULL,
    employee_id    BIGINT,
    portal_user_id BIGINT,
    comments       VARCHAR(255),
    sig            text,
    CONSTRAINT pk_bonus PRIMARY KEY (id)
);

CREATE TABLE dust_mop
(
    id BIGINT NOT NULL,
    CONSTRAINT pk_dustmop PRIMARY KEY (id)
);

CREATE TABLE employee
(
    id                   BIGINT NOT NULL,
    dtype                VARCHAR(31),
    employee_first       VARCHAR(255),
    employee_middle      VARCHAR(255),
    employee_last        VARCHAR(255),
    employee_second_last VARCHAR(255),
    employee_number      INTEGER,
    phone1               VARCHAR(255),
    phone2               VARCHAR(255),
    ssn                  VARCHAR(255),
    dob                  date,
    gender               SMALLINT,
    email                VARCHAR(255),
    portal_user_id       BIGINT,
    district             SMALLINT,
    CONSTRAINT pk_employee PRIMARY KEY (id)
);

CREATE TABLE hotel
(
    id             BIGINT NOT NULL,
    employee_id    BIGINT,
    portal_user_id BIGINT,
    store_id       BIGINT,
    check_in       date,
    check_out      date,
    num_of_rooms   INTEGER,
    num_of_beds    INTEGER,
    ps1_id         BIGINT,
    ps2_id         BIGINT,
    newps          VARCHAR(255),
    hotel_reason   SMALLINT,
    work_ticket    VARCHAR(255),
    notes          VARCHAR(255),
    CONSTRAINT pk_hotel PRIMARY KEY (id)
);

CREATE TABLE mileage
(
    id             BIGINT NOT NULL,
    comments       VARCHAR(255),
    employee_id    BIGINT,
    portal_user_id BIGINT,
    CONSTRAINT pk_mileage PRIMARY KEY (id)
);

CREATE TABLE new_hire
(
    id                 BIGINT NOT NULL,
    employee_id        BIGINT,
    portal_user_id     BIGINT,
    store_id           BIGINT,
    hire_type          VARCHAR(255),
    num_work_days      INTEGER,
    pay_rate           DOUBLE PRECISION,
    num_of_hours       VARCHAR(255),
    preferred_language VARCHAR(255),
    comments           VARCHAR(255),
    position           SMALLINT,
    first_day_worked   date,
    CONSTRAINT pk_newhire PRIMARY KEY (id)
);

CREATE TABLE per_diem
(
    id                BIGINT NOT NULL,
    store_id          BIGINT,
    first_hotel_night date,
    last_hotel_night  date,
    arrival_date      date,
    departure_date    date,
    reason_pay_missed VARCHAR(255),
    employee_id       BIGINT,
    portal_user_id    BIGINT,
    CONSTRAINT pk_perdiem PRIMARY KEY (id)
);

CREATE TABLE portal_user
(
    id          BIGINT NOT NULL,
    employee_id BIGINT,
    permission  SMALLINT,
    user_name   VARCHAR(255),
    password    VARCHAR(255),
    district    SMALLINT,
    user_first  VARCHAR(255),
    user_last   VARCHAR(255),
    email       VARCHAR(255),
    CONSTRAINT pk_portaluser PRIMARY KEY (id)
);

CREATE TABLE propane
(
    id BIGINT NOT NULL,
    CONSTRAINT pk_propane PRIMARY KEY (id)
);

CREATE TABLE pto
(
    id             BIGINT NOT NULL,
    absence_from   date,
    absence_to     date,
    hours          INTEGER,
    department     SMALLINT,
    comments       VARCHAR(255),
    sig            text,
    employee_id    BIGINT,
    portal_user_id BIGINT,
    CONSTRAINT pk_pto PRIMARY KEY (id)
);

CREATE TABLE repair
(
    id BIGINT NOT NULL,
    CONSTRAINT pk_repair PRIMARY KEY (id)
);

CREATE TABLE store
(
    id        BIGINT NOT NULL,
    banner    VARCHAR(255),
    name      VARCHAR(255),
    district  VARCHAR(255),
    store_num VARCHAR(255),
    city      VARCHAR(255),
    state     VARCHAR(255),
    CONSTRAINT pk_store PRIMARY KEY (id)
);

CREATE TABLE target_order
(
    id             BIGINT NOT NULL,
    employee_id    BIGINT,
    portal_user_id BIGINT,
    store_id       BIGINT,
    comments       VARCHAR(255),
    CONSTRAINT pk_targetorder PRIMARY KEY (id)
);

CREATE TABLE termination
(
    id              BIGINT NOT NULL,
    quit_reason     VARCHAR(255),
    last_day_worked date,
    eligible_rehire BOOLEAN,
    two_weeks_given BOOLEAN,
    warnings_given  BOOLEAN,
    term_type       SMALLINT,
    employee_id     BIGINT,
    portal_user_id  BIGINT,
    CONSTRAINT pk_termination PRIMARY KEY (id)
);

CREATE TABLE time_adjustment
(
    id             BIGINT NOT NULL,
    reason_missed  VARCHAR(255),
    manager_sig    VARCHAR(255),
    employee_id    BIGINT,
    portal_user_id BIGINT,
    employee_sig   text,
    CONSTRAINT pk_timeadjustment PRIMARY KEY (id)
);

CREATE TABLE uniform
(
    id             BIGINT NOT NULL,
    employee_id    BIGINT,
    portal_user_id BIGINT,
    quantity       INTEGER,
    total          DOUBLE PRECISION,
    size           SMALLINT,
    sig            text,
    CONSTRAINT pk_uniform PRIMARY KEY (id)
);

CREATE TABLE user_role
(
    id   BIGINT NOT NULL,
    role VARCHAR(255),
    CONSTRAINT pk_userrole PRIMARY KEY (id)
);

CREATE TABLE work_ticket
(
    id                     BIGINT NOT NULL,
    current_location_id    BIGINT,
    project_location_id    BIGINT,
    employee_id            BIGINT,
    type_of_work           SMALLINT,
    is_billable            VARCHAR(255),
    notes                  VARCHAR(255),
    equipment_needed       VARCHAR(255),
    supply_order_date      date,
    project_start_date     date,
    project_end_date       date,
    order_number           VARCHAR(255),
    supply_order_submitted BOOLEAN,
    CONSTRAINT pk_workticket PRIMARY KEY (id)
);

ALTER TABLE back_pay
    ADD CONSTRAINT FK_BACKPAY_ON_EMPLOYEE FOREIGN KEY (employee_id) REFERENCES employee (id);

ALTER TABLE back_pay
    ADD CONSTRAINT FK_BACKPAY_ON_PORTAL_USER FOREIGN KEY (portal_user_id) REFERENCES portal_user (id);

ALTER TABLE back_pay
    ADD CONSTRAINT FK_BACKPAY_ON_STORE FOREIGN KEY (store_id) REFERENCES store (id);

ALTER TABLE bonus
    ADD CONSTRAINT FK_BONUS_ON_EMPLOYEE FOREIGN KEY (employee_id) REFERENCES employee (id);

ALTER TABLE bonus
    ADD CONSTRAINT FK_BONUS_ON_PORTAL_USER FOREIGN KEY (portal_user_id) REFERENCES portal_user (id);

ALTER TABLE employee
    ADD CONSTRAINT FK_EMPLOYEE_ON_PORTAL_USER FOREIGN KEY (portal_user_id) REFERENCES portal_user (id);

ALTER TABLE hotel
    ADD CONSTRAINT FK_HOTEL_ON_EMPLOYEE FOREIGN KEY (employee_id) REFERENCES employee (id);

ALTER TABLE hotel
    ADD CONSTRAINT FK_HOTEL_ON_PORTAL_USER FOREIGN KEY (portal_user_id) REFERENCES portal_user (id);

ALTER TABLE hotel
    ADD CONSTRAINT FK_HOTEL_ON_PS1 FOREIGN KEY (ps1_id) REFERENCES employee (id);

ALTER TABLE hotel
    ADD CONSTRAINT FK_HOTEL_ON_PS2 FOREIGN KEY (ps2_id) REFERENCES employee (id);

ALTER TABLE hotel
    ADD CONSTRAINT FK_HOTEL_ON_STORE FOREIGN KEY (store_id) REFERENCES store (id);

ALTER TABLE mileage
    ADD CONSTRAINT FK_MILEAGE_ON_EMPLOYEE FOREIGN KEY (employee_id) REFERENCES employee (id);

ALTER TABLE mileage
    ADD CONSTRAINT FK_MILEAGE_ON_PORTAL_USER FOREIGN KEY (portal_user_id) REFERENCES portal_user (id);

ALTER TABLE new_hire
    ADD CONSTRAINT FK_NEWHIRE_ON_EMPLOYEE FOREIGN KEY (employee_id) REFERENCES employee (id);

ALTER TABLE new_hire
    ADD CONSTRAINT FK_NEWHIRE_ON_PORTAL_USER FOREIGN KEY (portal_user_id) REFERENCES portal_user (id);

ALTER TABLE new_hire
    ADD CONSTRAINT FK_NEWHIRE_ON_STORE FOREIGN KEY (store_id) REFERENCES store (id);

ALTER TABLE per_diem
    ADD CONSTRAINT FK_PERDIEM_ON_EMPLOYEE FOREIGN KEY (employee_id) REFERENCES employee (id);

ALTER TABLE per_diem
    ADD CONSTRAINT FK_PERDIEM_ON_PORTAL_USER FOREIGN KEY (portal_user_id) REFERENCES portal_user (id);

ALTER TABLE per_diem
    ADD CONSTRAINT FK_PERDIEM_ON_STORE FOREIGN KEY (store_id) REFERENCES store (id);

ALTER TABLE portal_user
    ADD CONSTRAINT FK_PORTALUSER_ON_EMPLOYEE FOREIGN KEY (employee_id) REFERENCES employee (id);

ALTER TABLE pto
    ADD CONSTRAINT FK_PTO_ON_EMPLOYEE FOREIGN KEY (employee_id) REFERENCES employee (id);

ALTER TABLE pto
    ADD CONSTRAINT FK_PTO_ON_PORTAL_USER FOREIGN KEY (portal_user_id) REFERENCES portal_user (id);

ALTER TABLE target_order
    ADD CONSTRAINT FK_TARGETORDER_ON_EMPLOYEE FOREIGN KEY (employee_id) REFERENCES employee (id);

ALTER TABLE target_order
    ADD CONSTRAINT FK_TARGETORDER_ON_PORTAL_USER FOREIGN KEY (portal_user_id) REFERENCES portal_user (id);

ALTER TABLE target_order
    ADD CONSTRAINT FK_TARGETORDER_ON_STORE FOREIGN KEY (store_id) REFERENCES store (id);

ALTER TABLE termination
    ADD CONSTRAINT FK_TERMINATION_ON_EMPLOYEE FOREIGN KEY (employee_id) REFERENCES employee (id);

ALTER TABLE termination
    ADD CONSTRAINT FK_TERMINATION_ON_PORTAL_USER FOREIGN KEY (portal_user_id) REFERENCES portal_user (id);

ALTER TABLE time_adjustment
    ADD CONSTRAINT FK_TIMEADJUSTMENT_ON_EMPLOYEE FOREIGN KEY (employee_id) REFERENCES employee (id);

ALTER TABLE time_adjustment
    ADD CONSTRAINT FK_TIMEADJUSTMENT_ON_PORTAL_USER FOREIGN KEY (portal_user_id) REFERENCES portal_user (id);

ALTER TABLE uniform
    ADD CONSTRAINT FK_UNIFORM_ON_EMPLOYEE FOREIGN KEY (employee_id) REFERENCES employee (id);

ALTER TABLE uniform
    ADD CONSTRAINT FK_UNIFORM_ON_PORTAL_USER FOREIGN KEY (portal_user_id) REFERENCES portal_user (id);

ALTER TABLE work_ticket
    ADD CONSTRAINT FK_WORKTICKET_ON_CURRENT_LOCATION FOREIGN KEY (current_location_id) REFERENCES store (id);

ALTER TABLE work_ticket
    ADD CONSTRAINT FK_WORKTICKET_ON_EMPLOYEE FOREIGN KEY (employee_id) REFERENCES employee (id);

ALTER TABLE work_ticket
    ADD CONSTRAINT FK_WORKTICKET_ON_PROJECT_LOCATION FOREIGN KEY (project_location_id) REFERENCES store (id);