CREATE TEMP TABLE base_views (name, def) AS VALUES (
    'fairway_marks_tmpl', $$
        SELECT id,
            lower(validity) AS valid_from,
            COALESCE(upper(validity), current_timestamp) AS valid_to,
            geom,
            datsta,
            datend,
            persta,
            perend,
            objnam,
            nobjnm,
            inform,
            ninfom,
            scamin,
            picrep,
            txtdsc,
            sordat,
            sorind,
            %s
        FROM waterway.fairway_marks_%s
    $$);

-- Settings common to all fairway marks layers
UPDATE sys_admin.published_services SET
        srid = 4326,
        key_column = 'id',
        wmst_attribute = 'valid_from',
        wmst_end_attribute = 'valid_to'
    WHERE schema = 'waterway' AND name LIKE 'fairway_marks_%';

-- Add SQL view definition for fairway marks layers
UPDATE sys_admin.published_services SET view_def = format(
        (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
        'colour, colpat, conrad, marsys, boyshp, catcam', 'boycar')
    WHERE schema = 'waterway' AND name = 'fairway_marks_boycar';

UPDATE sys_admin.published_services SET view_def = format(
        (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
        'colour, colpat, conrad, marsys, boyshp', 'boysaw')
    WHERE schema = 'waterway' AND name = 'fairway_marks_boysaw';

UPDATE sys_admin.published_services SET view_def = format(
        (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
        'colour, colpat, conrad, marsys, boyshp, catspm', 'boyspp')
    WHERE schema = 'waterway' AND name = 'fairway_marks_boyspp';

UPDATE sys_admin.published_services SET view_def = format(
        (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
        $$colour, condtn, orient, catlit, exclit, litchr, litvis,
        mltylt, sectr1, sectr2, siggrp, sigper, sigseq, status$$,
        'lights')
    WHERE schema = 'waterway' AND name = 'fairway_marks_lights';

UPDATE sys_admin.published_services SET view_def = format(
        (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
        'condtn, siggrp, catrtb, radwal', 'rtpbcn')
    WHERE schema = 'waterway' AND name = 'fairway_marks_rtpbcn';

UPDATE sys_admin.published_services SET view_def = format(
        (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
        'colour, colpat, condtn, topshp', 'topmar')
    WHERE schema = 'waterway' AND name = 'fairway_marks_topmar';

UPDATE sys_admin.published_services SET view_def = format(
        (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
        $$condtn, marsys, orient, status, addmrk, catnmk,
        disipd, disipu, disbk1, disbk2, fnctnm, bnkwtw,
            string_agg(CAST(dirimp AS varchar), ',') AS dirimp$$,
        $$notmrk LEFT JOIN waterway.fairway_marks_notmrk_dirimps
            ON id = fm_notmrk_id GROUP BY id$$)
    WHERE schema = 'waterway' AND name = 'fairway_marks_notmrk';

UPDATE sys_admin.published_services SET view_def = format(
        (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
        'colour, colpat, condtn, bcnshp, catlam', 'bcnlat_hydro')
    WHERE schema = 'waterway' AND name = 'fairway_marks_bcnlat_hydro';

UPDATE sys_admin.published_services SET view_def = format(
        (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
        $$colour, colpat, condtn, bcnshp, catlam,
            string_agg(CAST(dirimp AS varchar), ',') AS dirimp$$,
        $$bcnlat_ienc LEFT JOIN waterway.fairway_marks_bcnlat_dirimps
            ON id = fm_bcnlat_id GROUP BY id$$)
    WHERE schema = 'waterway' AND name = 'fairway_marks_bcnlat_ienc';

UPDATE sys_admin.published_services SET view_def = format(
        (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
        'colour, colpat, conrad, marsys, boyshp, catlam', 'boylat_hydro')
    WHERE schema = 'waterway' AND name = 'fairway_marks_boylat_hydro';

UPDATE sys_admin.published_services SET view_def = format(
        (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
        'colour, colpat, conrad, marsys, boyshp, catlam', 'boylat_ienc')
    WHERE schema = 'waterway' AND name = 'fairway_marks_boylat_ienc';

UPDATE sys_admin.published_services SET view_def = format(
        (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
        'colour, colpat, condtn, topshp', 'daymar_hydro')
    WHERE schema = 'waterway' AND name = 'fairway_marks_daymar_hydro';

UPDATE sys_admin.published_services SET view_def = format(
        (SELECT def FROM base_views WHERE name = 'fairway_marks_tmpl'),
        $$colour, colpat, condtn, topshp, orient,
            string_agg(CAST(dirimp AS varchar), ',') AS dirimp$$,
        $$daymar_ienc LEFT JOIN waterway.fairway_marks_daymar_dirimps
            ON id = fm_daymar_id GROUP BY id$$)
    WHERE schema = 'waterway' AND name = 'fairway_marks_daymar_ienc';