CREATE TABLE waterway.fairway_marks_bcnlat_new (
    colour varchar,
    colpat varchar,
    condtn int,
    bcnshp int,
    catlam int
) INHERITS (waterway.fairway_marks);

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

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

-- Assume all features are IENC features, since there is currently no known
-- data source for HYDRO features
INSERT INTO waterway.fairway_marks_bcnlat_ienc
    SELECT validity, last_found, geom,
            datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
            scamin, picrep, txtdsc, sordat, sorind,
            colour, colpat, condtn, bcnshp, catlam, id
        FROM waterway.fairway_marks_bcnlat;
SELECT setval(
        pg_get_serial_sequence('waterway.fairway_marks_bcnlat_ienc', 'id'),
        max(id))
    FROM waterway.fairway_marks_bcnlat_ienc;

-- Let foreign key constraint point to new table
ALTER TABLE waterway.fairway_marks_bcnlat_dirimps
    DROP CONSTRAINT fairway_marks_bcnlat_dirimps_fm_bcnlat_id_fkey,
    ADD FOREIGN KEY (fm_bcnlat_id)
        REFERENCES waterway.fairway_marks_bcnlat_ienc;

-- Finally
DROP TABLE waterway.fairway_marks_bcnlat;
ALTER TABLE waterway.fairway_marks_bcnlat_new RENAME TO fairway_marks_bcnlat;

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