CREATE TABLE back_pay_back_pay_dates
(
    back_pay_id       BIGINT NOT NULL,
    back_pay_dates_id BIGINT NOT NULL
);

CREATE TABLE back_pay_date
(
    id           BIGINT NOT NULL,
    date         date,
    punch_in     TIMESTAMP WITHOUT TIME ZONE,
    punch_out    TIMESTAMP WITHOUT TIME ZONE,
    left_lunch   TIMESTAMP WITHOUT TIME ZONE,
    return_lunch TIMESTAMP WITHOUT TIME ZONE,
    CONSTRAINT pk_backpaydate PRIMARY KEY (id)
);

CREATE TABLE bonus_bonuses
(
    bonus_id   BIGINT NOT NULL,
    bonuses_id BIGINT NOT NULL
);

CREATE TABLE bonus_date
(
    id       BIGINT NOT NULL,
    date     date,
    store_id BIGINT,
    bonus    DOUBLE PRECISION,
    CONSTRAINT pk_bonusdate PRIMARY KEY (id)
);

CREATE TABLE mileage_date
(
    id           BIGINT NOT NULL,
    date         date,
    location1_id BIGINT,
    location2_id BIGINT,
    rt_ow        SMALLINT,
    CONSTRAINT pk_mileagedate PRIMARY KEY (id)
);

CREATE TABLE mileage_mileage_dates
(
    mileage_id       BIGINT NOT NULL,
    mileage_dates_id BIGINT NOT NULL
);

CREATE TABLE time_adjust_break
(
    id           BIGINT NOT NULL,
    date         date,
    punch_in     TIMESTAMP WITHOUT TIME ZONE,
    punch_out    TIMESTAMP WITHOUT TIME ZONE,
    break_amount DOUBLE PRECISION,
    CONSTRAINT pk_timeadjustbreak PRIMARY KEY (id)
);

CREATE TABLE time_adjustment_breaks
(
    time_adjustment_id BIGINT NOT NULL,
    breaks_id          BIGINT NOT NULL
);

ALTER TABLE back_pay_back_pay_dates
    ADD CONSTRAINT uc_back_pay_back_pay_dates_backpaydates UNIQUE (back_pay_dates_id);

ALTER TABLE bonus_bonuses
    ADD CONSTRAINT uc_bonus_bonuses_bonuses UNIQUE (bonuses_id);

ALTER TABLE mileage_mileage_dates
    ADD CONSTRAINT uc_mileage_mileage_dates_mileagedates UNIQUE (mileage_dates_id);

ALTER TABLE time_adjustment_breaks
    ADD CONSTRAINT uc_time_adjustment_breaks_breaks UNIQUE (breaks_id);

ALTER TABLE bonus_date
    ADD CONSTRAINT FK_BONUSDATE_ON_STORE FOREIGN KEY (store_id) REFERENCES store (id);

ALTER TABLE mileage_date
    ADD CONSTRAINT FK_MILEAGEDATE_ON_LOCATION1 FOREIGN KEY (location1_id) REFERENCES store (id);

ALTER TABLE mileage_date
    ADD CONSTRAINT FK_MILEAGEDATE_ON_LOCATION2 FOREIGN KEY (location2_id) REFERENCES store (id);

ALTER TABLE back_pay_back_pay_dates
    ADD CONSTRAINT fk_bacpaybacpaydat_on_back_pay FOREIGN KEY (back_pay_id) REFERENCES back_pay (id);

ALTER TABLE back_pay_back_pay_dates
    ADD CONSTRAINT fk_bacpaybacpaydat_on_back_pay_date FOREIGN KEY (back_pay_dates_id) REFERENCES back_pay_date (id);

ALTER TABLE bonus_bonuses
    ADD CONSTRAINT fk_bonbon_on_bonus FOREIGN KEY (bonus_id) REFERENCES bonus (id);

ALTER TABLE bonus_bonuses
    ADD CONSTRAINT fk_bonbon_on_bonus_date FOREIGN KEY (bonuses_id) REFERENCES bonus_date (id);

ALTER TABLE mileage_mileage_dates
    ADD CONSTRAINT fk_milmildat_on_mileage FOREIGN KEY (mileage_id) REFERENCES mileage (id);

ALTER TABLE mileage_mileage_dates
    ADD CONSTRAINT fk_milmildat_on_mileage_date FOREIGN KEY (mileage_dates_id) REFERENCES mileage_date (id);

ALTER TABLE time_adjustment_breaks
    ADD CONSTRAINT fk_timadjbre_on_time_adjust_break FOREIGN KEY (breaks_id) REFERENCES time_adjust_break (id);

ALTER TABLE time_adjustment_breaks
    ADD CONSTRAINT fk_timadjbre_on_time_adjustment FOREIGN KEY (time_adjustment_id) REFERENCES time_adjustment (id);

ALTER TABLE back_pay
    ALTER COLUMN sig TYPE text USING (sig::text);