-- This is Free Software under GNU Affero General Public License v >= 3.0
-- without warranty, see README.md and license for details.

-- SPDX-License-Identifier: AGPL-3.0-or-later
-- License-Filename: LICENSES/AGPL-3.0.txt

-- Copyright (C) 2019 by via donau
--   – Österreichische Wasserstraßen-Gesellschaft mbH
-- Software engineering by Intevation GmbH

-- Author(s):
--  * Sascha Wilde <sascha.wilde@intevation.de>

DROP TABLE users.responsibility_areas CASCADE;

ALTER TABLE waterway.stretches SET SCHEMA users;
ALTER TABLE waterway.stretch_countries SET SCHEMA users;

ALTER TABLE users.stretch_countries
    RENAME COLUMN stretches_id TO stretch_id;
ALTER TABLE users.stretch_countries
    RENAME COLUMN country_code TO country;

ALTER TABLE internal.user_profiles
    ADD CONSTRAINT user_profiles_country_fkey
    FOREIGN KEY (country) REFERENCES countries;

--
-- geoserver view:

CREATE OR REPLACE VIEW waterway.stretches_geoserver AS
    SELECT
        s.id,
        s.name,
        (s.stretch).lower::varchar as lower,
        (s.stretch).upper::varchar as upper,
        s.area::Geometry(MULTIPOLYGON, 4326),
        s.objnam,
        s.nobjnam,
        s.date_info,
        s.source_organization,
        (SELECT string_agg(country, ', ')
            FROM users.stretch_countries
            WHERE stretch_id = s.id) AS countries,
        s.staging_done,
        min(g.gm_measuredate) AS gm_measuredate,
        min(g.gm_n_14d) AS gm_n_14d,
        max(g.forecast_accuracy_3d) AS forecast_accuracy_3d,
        max(g.forecast_accuracy_1d) AS forecast_accuracy_1d
    FROM users.stretches s
        LEFT JOIN waterway.gauges_geoserver g
            ON isrs_fromtext(g.isrs_code) <@ s.stretch
    GROUP BY s.id;

--
-- auth:

GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA users TO sys_admin;


--
-- manage_users:

CREATE OR REPLACE FUNCTION users.current_user_area_utm()
    RETURNS geometry
    AS $$
        DECLARE utm_area geometry;
        BEGIN
            SELECT ST_Union(ST_Transform(area::geometry, z))
                INTO STRICT utm_area
                FROM (SELECT area,
                        best_utm(ST_Collect(area::geometry) OVER ()) AS z
                    FROM users.stretches st
                        JOIN users.stretch_countries stc
                            ON stc.stretch_id = st.id
                    WHERE country = (SELECT country
                        FROM users.list_users
                        WHERE username = current_user)) AS st;
    RETURN utm_area;
        END;
$$
    LANGUAGE plpgsql
    STABLE PARALLEL SAFE;

CREATE OR REPLACE FUNCTION internal.create_user() RETURNS trigger
AS $$
BEGIN
    IF NEW.map_extent IS NULL
    THEN
        NEW.map_extent = ST_Extent(CAST(area AS geometry))
            FROM users.stretches st
                JOIN users.stretch_countries stc ON stc.stretch_id = st.id
            WHERE stc.country = NEW.country;
    END IF;
    INSERT INTO internal.user_profiles (
        username, country, map_extent, email_address)
        VALUES (NEW.username, NEW.country, NEW.map_extent, NEW.email_address);
    EXECUTE format(
        'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L',
        NEW.username,
        NEW.rolname,
        internal.check_password(NEW.pw));

    -- Do not leak new password
    NEW.pw = '';
    RETURN NEW;
END;
$$
    LANGUAGE plpgsql
    SECURITY DEFINER;


--
-- search_functions:

CREATE OR REPLACE FUNCTION search_stretches(search_string text) RETURNS jsonb
  LANGUAGE plpgsql STABLE PARALLEL SAFE
  AS $$
DECLARE
  _result jsonb;
BEGIN
  SELECT COALESCE(json_agg(r),'[]')
    INTO _result
    FROM (SELECT id,
                 objnam AS name,
                 ST_AsGeoJSON(ST_Envelope(area::geometry))::json AS geom,
                 'stretch' AS type
            FROM users.stretches
            WHERE objnam ILIKE '%' || search_string || '%'
               OR nobjnam ILIKE '%' || search_string || '%'
          ORDER BY name) r;
  RETURN _result;
END;
$$;