DROP POLICY same_country ON waterway.gauge_measurements;
DROP POLICY same_country ON waterway.waterway_profiles;

DO LANGUAGE plpgsql
$do$
DECLARE
    the_table varchar;
    condition CONSTANT text = $$
        (location).country_code =
            (SELECT country FROM users.list_users
                WHERE username = current_user)
        $$;
BEGIN
    FOREACH the_table IN ARRAY ARRAY[
        'gauge_measurements',
        'waterway_profiles']
    LOOP
        EXECUTE format($$
            CREATE POLICY same_country_insert ON waterway.%I
                FOR INSERT TO waterway_admin
                WITH CHECK (%s)
            $$, the_table, condition);
        EXECUTE format($$
            CREATE POLICY same_country_select ON waterway.%I
                FOR SELECT TO waterway_admin
                USING (staging_done OR %s)
            $$, the_table, condition);
        EXECUTE format($$
            CREATE POLICY same_country_update ON waterway.%I
                FOR UPDATE TO waterway_admin
                USING (%s)
            $$, the_table, condition);
        EXECUTE format($$
            CREATE POLICY same_country_delete ON waterway.%I
                FOR DELETE TO waterway_admin
                USING (%s)
            $$, the_table, condition);
    END LOOP;
END;
$do$;


DROP POLICY responsibility_area ON waterway.bottlenecks;
DROP POLICY responsibility_area ON waterway.sounding_results;
DROP POLICY responsibility_area ON waterway.fairway_dimensions;

DO LANGUAGE plpgsql
$do$
DECLARE
    the_table varchar;
    condition CONSTANT text = $$
        (SELECT ST_Covers(a, ST_Transform(CAST(area AS geometry), ST_SRID(a)))
            FROM users.current_user_area_utm() AS a (a))
        $$;
BEGIN
    FOREACH the_table IN ARRAY ARRAY[
        'fairway_dimensions',
        'bottlenecks',
        'sounding_results']
    LOOP
        EXECUTE format($$
            CREATE POLICY responsibility_area_insert ON waterway.%I
                FOR INSERT TO waterway_admin
                WITH CHECK (%s)
            $$, the_table, condition);
        -- In many cases it is more efficient to check for "staging_done" to
        -- prevent the more expensive checks for read only access (which is
        -- allowed for all users, when staging is done).
        EXECUTE format($$
            CREATE POLICY responsibility_area_select ON waterway.%I
                FOR SELECT TO waterway_admin
                USING (staging_done OR %s)
            $$, the_table, condition);
        EXECUTE format($$
            CREATE POLICY responsibility_area_update ON waterway.%I
                FOR UPDATE TO waterway_admin
                USING (%s)
            $$, the_table, condition);
        EXECUTE format($$
            CREATE POLICY responsibility_area_delete ON waterway.%I
                FOR DELETE TO waterway_admin
                USING (%s)
            $$, the_table, condition);
    END LOOP;
END;
$do$;