CREATE TABLE waterway.fairway_marks_daymar_new (
    colour varchar,
    colpat varchar,
    condtn int,
    topshp int
) INHERITS (waterway.fairway_marks);

CREATE TABLE waterway.fairway_marks_daymar_hydro (
    id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
) INHERITS (waterway.fairway_marks_daymar_new);
CREATE UNIQUE INDEX fairway_marks_daymar_hydro_distinct_rows
    ON waterway.fairway_marks_daymar_hydro
    ((CAST((validity, last_found, geom,
            datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
            scamin, picrep, txtdsc, sordat, sorind,
            colour, colpat, condtn, topshp, 0
        ) AS waterway.fairway_marks_daymar_hydro)
    ));

CREATE TABLE waterway.fairway_marks_daymar_ienc (
    orient double precision,
    id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
) INHERITS (waterway.fairway_marks_daymar_new);
CREATE UNIQUE INDEX fairway_marks_daymar_ienc_distinct_rows
    ON waterway.fairway_marks_daymar_ienc
    ((CAST((validity, last_found, geom,
            datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
            scamin, picrep, txtdsc, sordat, sorind,
            colour, colpat, condtn, topshp, orient, 0
        ) AS waterway.fairway_marks_daymar_ienc)
    ));

-- Assume all features not being definitely IENC features are HYDRO features
INSERT INTO waterway.fairway_marks_daymar_hydro
    OVERRIDING USER VALUE
    SELECT validity, last_found, geom,
            datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
            scamin, picrep, txtdsc, sordat, sorind,
            colour, colpat, condtn, topshp, id
        FROM waterway.fairway_marks_daymar
        WHERE orient IS NULL AND id NOT IN(
            SELECT fm_daymar_id FROM waterway.fairway_marks_daymar_dirimps);

INSERT INTO waterway.fairway_marks_daymar_ienc
    SELECT validity, last_found, geom,
            datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
            scamin, picrep, txtdsc, sordat, sorind,
            colour, colpat, condtn, topshp, orient, id
        FROM waterway.fairway_marks_daymar
        WHERE orient IS NOT NULL OR id IN(
            SELECT fm_daymar_id FROM waterway.fairway_marks_daymar_dirimps);
SELECT setval(
        pg_get_serial_sequence('waterway.fairway_marks_daymar_ienc', 'id'),
        max(id))
    FROM waterway.fairway_marks_daymar_ienc;

-- Let foreign key constraint point to new table
ALTER TABLE waterway.fairway_marks_daymar_dirimps
    DROP CONSTRAINT fairway_marks_daymar_dirimps_fm_daymar_id_fkey,
    ADD FOREIGN KEY (fm_daymar_id)
        REFERENCES waterway.fairway_marks_daymar_ienc;

-- Finally
DROP TABLE waterway.fairway_marks_daymar;
ALTER TABLE waterway.fairway_marks_daymar_new RENAME TO fairway_marks_daymar;

GRANT SELECT on ALL tables in schema waterway TO waterway_user;
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway
    TO waterway_admin;