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

CREATE TABLE district
(
    id            BIGINT NOT NULL,
    district_name VARCHAR(255),
    CONSTRAINT pk_district PRIMARY KEY (id)
);

CREATE TABLE portal_user_district
(
    portal_user_id BIGINT NOT NULL,
    district_id    BIGINT NOT NULL
);

ALTER TABLE store
    ADD district_id BIGINT;

ALTER TABLE portal_user_district
    ADD CONSTRAINT uc_portal_user_district_district UNIQUE (district_id);

ALTER TABLE store
    ADD CONSTRAINT FK_STORE_ON_DISTRICT FOREIGN KEY (district_id) REFERENCES district (id);

ALTER TABLE work_ticket
    ADD CONSTRAINT FK_WORKTICKET_ON_PORTAL_USER FOREIGN KEY (portal_user_id) REFERENCES portal_user (id);

ALTER TABLE portal_user_district
    ADD CONSTRAINT fk_porusedis_on_district FOREIGN KEY (district_id) REFERENCES district (id);

ALTER TABLE portal_user_district
    ADD CONSTRAINT fk_porusedis_on_portal_user FOREIGN KEY (portal_user_id) REFERENCES portal_user (id);

ALTER TABLE employee
    DROP COLUMN district;

ALTER TABLE portal_user
    DROP COLUMN district;

ALTER TABLE store
    DROP COLUMN district;