CREATE TABLE waterway.fairway_marks_new (
    validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL)
        CHECK (NOT isempty(validity)),
    last_found timestamp with time zone NOT NULL DEFAULT current_timestamp,
    LIKE waterway.fairway_marks
);

DO LANGUAGE plpgsql
$$
DECLARE table_suffix varchar;
BEGIN
    /* Re-write tables inheriting from fairway_marks to include new columns
       in correct position */
    FOREACH table_suffix IN ARRAY ARRAY[
        'bcnlat', 'boycar', 'boylat', 'boysaw', 'boyspp',
        'daymar', 'lights', 'rtpbcn', 'topmar', 'notmrk']
    LOOP
        EXECUTE format('CREATE TABLE waterway.fairway_marks_%s_new '
            '(LIKE waterway.fairway_marks_%1$s INCLUDING ALL) '
            'INHERITS (waterway.fairway_marks_new)', table_suffix);
        /* Drop index referring to wrong columns. See
           https://www.postgresql.org/message-id/flat/16272-6e32da020e9a9381%40postgresql.org
         */
        EXECUTE format('DROP INDEX waterway.fairway_marks_%s_new_row_idx',
            table_suffix);
        EXECUTE format('INSERT INTO waterway.fairway_marks_%s_new '
            'SELECT tstzrange(current_timestamp, NULL), current_timestamp, * '
            'FROM waterway.fairway_marks_%1$s', table_suffix);
        EXECUTE format('DROP TABLE waterway.fairway_marks_%s CASCADE',
            table_suffix);
        EXECUTE format('ALTER TABLE waterway.fairway_marks_%s_new '
            'RENAME TO fairway_marks_%1$s', table_suffix);
    END LOOP;

    /* Recreate foreign key constraints */
    FOREACH table_suffix IN ARRAY ARRAY['bcnlat', 'daymar', 'notmrk']
    LOOP
        EXECUTE format('ALTER TABLE waterway.fairway_marks_%1$s_dirimps '
            'ADD FOREIGN KEY (fm_%1$s_id) '
            'REFERENCES waterway.fairway_marks_%1$s', table_suffix);
    END LOOP;
END;
$$;

DROP TABLE waterway.fairway_marks;
ALTER TABLE waterway.fairway_marks_new RENAME TO fairway_marks;

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