BEGIN;

--
-- Roles, privileges and policies for the GEMMA database
--
SET search_path TO public, gemma, gemma_waterway, gemma_fairway;

-- We do not want any users to be able to create any objects
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

--
-- Primary GEMMA roles (SRS table 3)
--
CREATE ROLE waterway_user;
CREATE ROLE waterway_admin IN ROLE waterway_user;
CREATE ROLE sys_admin CREATEROLE BYPASSRLS IN ROLE waterway_admin;

--
-- Privileges for waterway_user
--
GRANT USAGE ON SCHEMA gemma, gemma_waterway, gemma_fairway TO waterway_user;
GRANT SELECT ON ALL TABLES IN SCHEMA gemma_waterway, gemma_fairway
      TO waterway_user;
GRANT SELECT ON templates, user_templates, user_profiles TO waterway_user;

--
-- Extended privileges for waterway_admin
--
GRANT INSERT, UPDATE ON ALL TABLES IN SCHEMA gemma_waterway, gemma_fairway
      TO waterway_admin;
-- TODO: will there ever be UPDATEs or can we drop that due to historicisation?
GRANT INSERT, UPDATE ON templates, user_templates TO waterway_admin;
GRANT SELECT ON responsibility_areas TO waterway_admin;

--
-- Extended privileges for sys_admin
--
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA gemma
      TO sys_admin;

--
-- RLS policies for waterway_user
--

-- Staging area
CREATE FUNCTION create_hide_staging_policy() RETURNS void AS $$
DECLARE the_table varchar;
BEGIN
    FOREACH the_table IN ARRAY ARRAY[
        'bottlenecks', 'sounding_results']
    -- TODO: add all relevant tables here
    LOOP
        EXECUTE format('CREATE POLICY hide_staging ON %I '
            'FOR SELECT TO waterway_user USING (staging_done)', the_table);
        EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', the_table);
    END LOOP;
END;
$$ LANGUAGE plpgsql;
SELECT create_hide_staging_policy();
DROP FUNCTION create_hide_staging_policy;

CREATE POLICY see_yourself ON user_profiles FOR SELECT TO waterway_user
       USING (username = current_user);
ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;

CREATE POLICY own_templates ON templates FOR SELECT TO waterway_user
       USING (id IN(SELECT template_id FROM user_templates
                    WHERE username = current_user));
ALTER TABLE templates ENABLE ROW LEVEL SECURITY;

--
-- RLS policies for waterway_admin
--

-- Staging area
-- TODO: add all relevant tables here
CREATE POLICY responsibility_area ON bottlenecks FOR ALL TO waterway_admin
       USING (ST_Within(area, (SELECT area FROM responsibility_areas
           WHERE country = (SELECT country FROM user_profiles))));
CREATE POLICY responsibility_area ON sounding_results FOR ALL TO waterway_admin
       USING (ST_Within(area, (SELECT area FROM responsibility_areas
           WHERE country = (SELECT country FROM user_profiles))));

CREATE POLICY manage_templates ON templates FOR ALL TO waterway_admin
       USING (id IN(SELECT template_id FROM user_templates ut
                    JOIN user_profiles p ON ut.username = p.username
                    WHERE p.country = (SELECT country FROM user_profiles)));

COMMIT;