-- 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) 2018, 2019 by via donau
--   – Österreichische Wasserstraßen-Gesellschaft mbH
-- Software engineering by Intevation GmbH

-- Author(s):
--  * Sascha L. Teichmann <sascha.teichmann@intevation.de>
--  * Tom Gottfried <tom@intevation.de>

CREATE OR REPLACE FUNCTION best_utm(g geography) RETURNS integer AS
$$
DECLARE
  center geometry;
BEGIN
  -- Centroid should be calculated on geography to get accurate results
  -- from lon/lat coordinates, but the respective PostGIS function returns
  -- POINT(-NaN NaN) for some invalid polygons, while the calculation on
  -- geometry seems to give reasonable approximations in this context.
  SELECT ST_Centroid(CAST(g AS geometry)) INTO center;

  RETURN
    CASE WHEN ST_Y(center) > 0 THEN
    32600
  ELSE
    32700
  END + floor((ST_X(center)+180)/6)::int + 1;
END;
$$
LANGUAGE plpgsql
IMMUTABLE;

CREATE OR REPLACE FUNCTION utm_covers(g geography) RETURNS boolean AS
$$
DECLARE
  user_area geometry;
  utm integer;
BEGIN
    SELECT area::geometry FROM users.responsibility_areas INTO user_area
    WHERE country = users.current_user_country();
    SELECT best_utm(user_area) INTO utm;
    RETURN ST_Covers(
      ST_Transform(user_area, utm),
      ST_Transform(g::geometry, utm));
END;
$$
LANGUAGE plpgsql
STABLE;