-- Cannot alter type of a column used in a policy definition
DROP POLICY responsibility_area_insert ON waterway.fairway_dimensions;
DROP POLICY responsibility_area_select ON waterway.fairway_dimensions;
DROP POLICY responsibility_area_update ON waterway.fairway_dimensions;
DROP POLICY responsibility_area_delete ON waterway.fairway_dimensions;

ALTER TABLE waterway.fairway_dimensions
    ALTER area TYPE geography(MULTIPOLYGON, 4326)
        USING ST_Multi(CAST(area AS geometry));

-- Re-create policies
DO LANGUAGE plpgsql
$do$
DECLARE
    the_table CONSTANT varchar = 'fairway_dimensions';
    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
    EXECUTE format($$
        CREATE POLICY responsibility_area_insert ON waterway.%I
            FOR INSERT TO waterway_admin
            WITH CHECK (%s)
        $$, the_table, condition);
    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;
$do$;