DROP TABLE IF EXISTS application.locations CASCADE;
CREATE TABLE application.locations (
    location_id       BIGSERIAL           PRIMARY KEY
  , name              VARCHAR(256)        NOT NULL
  , lat               DOUBLE PRECISION    NOT NULL
  , long              DOUBLE PRECISION    NOT NULL
  , population        BIGINT              NOT NULL
  , location          GEOMETRY            NOT NULL
);
ALTER TABLE application.locations OWNER TO nuke_su;

CREATE TYPE application.t_location AS (
    location_id       BIGINT
  , location_name     VARCHAR(256) 
  , lat               DOUBLE PRECISION   
  , lon               DOUBLE PRECISION    
  , population        BIGINT              
  , location          GEOMETRY
  , distance          DOUBLE PRECISION
  , total             BIGINT
);

DROP FUNCTION IF EXISTS get_nearest_locations (
    i_lat               DOUBLE PRECISION
  , i_lon               DOUBLE PRECISION
  , i_population        BIGINT
  , i_limit             BIGINT
);
CREATE OR REPLACE FUNCTION get_nearest_locations (
    i_lat               DOUBLE PRECISION
  , i_lon               DOUBLE PRECISION
  , i_population        BIGINT
  , i_limit             BIGINT
)
RETURNS SETOF application.t_location
SECURITY DEFINER AS
$_$
    DECLARE
        REC RECORD;
        v_sql TEXT;
        v_return application.t_location;
    BEGIN
	    
        v_sql := '
            SELECT
                  a.location_id                 AS location_id
                , a.name                        AS location_name
                , a.lat                         AS lat
                , a.long                        AS lon
                , a.population                  AS population
                , a.location                    AS location
                , (SELECT ST_distance(
                        location,ST_GeomFromText(
                            ''POINT(' || i_lon || ' ' || i_lat ||')'', 4326))) as distance
            FROM application.locations a
            WHERE TRUE'
            || COALESCE(' AND a.population > '||i_population, '') ||
            ' ORDER BY distance ASC' 
            ' LIMIT ' || i_limit || ';';

        FOR REC IN
            EXECUTE v_sql
        LOOP
            v_return.location_id                    := REC.location_id;
            v_return.location_name                  := REC.location_name;
            v_return.lat                            := REC.lat;
            v_return.lon                            := REC.lon;
            v_return.location                       := REC.location;
            v_return.population                     := REC.population;
            v_return.distance                       := REC.distance;
            
            RETURN NEXT v_return;
        END LOOP;
    END;
$_$
LANGUAGE PLPGSQL;