-- We need a wrapper procedure with owner rights for
-- the refresh, as (from the PGSQL manual): "REFRESH MATERIALIZED VIEW
-- completely replaces the contents of a materialized view. To execute
-- this command you must be the owner of the materialized view.""

CREATE OR REPLACE PROCEDURE sys_admin.update_dqr_stats()
LANGUAGE plpgsql AS $$
BEGIN
    EXECUTE 'REFRESH MATERIALIZED VIEW waterway.dqr_bottleneck_stats';
    EXECUTE 'REFRESH MATERIALIZED VIEW waterway.dqr_gauge_stats';
END;
$$ SECURITY DEFINER;

GRANT EXECUTE ON PROCEDURE sys_admin.update_dqr_stats() TO sys_admin;

-- Config update statement
INSERT INTO sys_admin.stats_updates
    VALUES ('Data quality report',
            'CALL sys_admin.update_dqr_stats();');