CREATE TABLE waterway.fairway_marks (
    geom geography(POINT, 4326) NOT NULL,
    datsta varchar,
    datend varchar,
    persta varchar,
    perend varchar,
    objnam varchar,
    nobjnm varchar,
    inform varchar,
    ninfom varchar,
    scamin int,
    picrep varchar,
    txtdsc varchar,
    sordat varchar,
    sorind varchar
);

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

CREATE TABLE waterway.fairway_marks_bcnlat_dirimps (
    fm_bcnlat_id int REFERENCES waterway.fairway_marks_bcnlat,
    dirimp smallint REFERENCES dirimps,
    PRIMARY KEY (fm_bcnlat_id, dirimp)
);


CREATE TABLE waterway.fairway_marks_boycar (
    id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    colour varchar,
    colpat varchar,
    conrad int,
    marsys int,
    boyshp int,
    catcam int
) INHERITS (waterway.fairway_marks);
CREATE UNIQUE INDEX fairway_marks_boycar_distinct_rows
    ON waterway.fairway_marks_boycar
    ((CAST((geom,
            datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
            scamin, picrep, txtdsc, sordat, sorind,
            0, colour, colpat, conrad, marsys, boyshp, catcam
        ) AS waterway.fairway_marks_boycar)
    ));

CREATE TABLE waterway.fairway_marks_boylat (
    id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    colour varchar,
    colpat varchar,
    conrad int,
    marsys int,
    boyshp int,
    catlam int
) INHERITS (waterway.fairway_marks);
CREATE UNIQUE INDEX fairway_marks_boylat_distinct_rows
    ON waterway.fairway_marks_boylat
    ((CAST((geom,
            datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
            scamin, picrep, txtdsc, sordat, sorind,
            0, colour, colpat, conrad, marsys, boyshp, catlam
        ) AS waterway.fairway_marks_boylat)
    ));


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