ALTER TABLE employee
    DROP CONSTRAINT fk_employee_on_portal_user;

ALTER TABLE hotel
    DROP CONSTRAINT fk_hotel_on_ps1;

ALTER TABLE hotel
    DROP CONSTRAINT fk_hotel_on_ps2;

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

CREATE TABLE ps
(
    id              BIGINT NOT NULL,
    employee_number VARCHAR(255),
    first_name      VARCHAR(255),
    last_name       VARCHAR(255),
    email           VARCHAR(255),
    phone           VARCHAR(255),
    portal_user_id  BIGINT,
    CONSTRAINT pk_ps PRIMARY KEY (id)
);

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

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

ALTER TABLE ps
    ADD CONSTRAINT FK_PS_ON_PORTAL_USER FOREIGN KEY (portal_user_id) REFERENCES portal_user (id);

ALTER TABLE employee
    DROP COLUMN portal_user_id;

ALTER TABLE time_adjustment
    ALTER COLUMN employee_sig TYPE text USING (employee_sig::text);

ALTER TABLE time_adjustment
    ALTER COLUMN manager_sig TYPE text USING (manager_sig::text);

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

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