CREATE TYPE publication_type AS ENUM (
  'emotion',
  'plan',
  'picture'
);

CREATE TYPE publication_visibility AS ENUM (
  'public',
  'account',
  'group',
  'following'
);

CREATE TABLE publication (
  publication_id   BIGSERIAL              NOT NULL PRIMARY KEY,
  account_id       INT REFERENCES account (account_id) ON UPDATE CASCADE ON DELETE CASCADE,
  datetime         TIMESTAMP              NOT NULL DEFAULT now(),
  update           VARCHAR(140),
  location         POINT                  NOT NULL,
  postgis_location GEOMETRY               NOT NULL,
  type             publication_type       NOT NULL DEFAULT 'emotion',
  visibility       publication_visibility NOT NULL DEFAULT 'public',
  emoji_id         SMALLINT REFERENCES emoji (emoji_id)
);


CREATE OR REPLACE FUNCTION xt_update_postgis_location()
  RETURNS TRIGGER AS
  $BODY$
  BEGIN
    NEW.postgis_location = st_makepoint(NEW.location [1], NEW.location [0], 3857);
    RETURN NEW;
  END;
  $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

CREATE TRIGGER trigger_update_postgis_location
BEFORE INSERT
ON publication
FOR EACH ROW
EXECUTE PROCEDURE xt_update_postgis_location();


CREATE INDEX idx_publication_st_location
ON publication
USING GIST
(postgis_location);

CREATE INDEX idx_publication_datetime
ON publication
USING BTREE (datetime);

CREATE OR REPLACE FUNCTION x_find_publications(dateStart TIMESTAMP, dateEnd TIMESTAMP, minLng FLOAT, minLat FLOAT,
                                               maxLng    FLOAT, maxLat FLOAT)
  RETURNS TABLE(publication_id BIGINT,
  datetime TIMESTAMP,
  account_id INT,
  update VARCHAR(140),
  latitude FLOAT,
  longitude FLOAT,
  emoji VARCHAR(12),
  image_url VARCHAR(500)) AS
  $BODY$

  SELECT
    p.publication_id,
    p.datetime,
    p.account_id,
    p.update,
    p.location [0] AS latitude,
    p.location [1] AS longitude,
    e.emoji,
    ei.url
  FROM publication p
    LEFT JOIN emoji e ON p.emoji_id = e.emoji_id
    LEFT JOIN emoji_icon ei ON p.emoji_id = ei.emoji_id AND ei.modifier = 0
  WHERE
    p.datetime BETWEEN dateStart AND dateEnd AND
    p.postgis_location @ST_MakeEnvelope (minLng, minLat, maxLng, maxLat, 3857)


  $BODY$
LANGUAGE SQL VOLATILE
COST 1000;


CLUSTER publication USING idx_publication_st_location;

ANALYZE publication;