--
-- categories
--
DROP TABLE IF EXISTS cats CASCADE;
CREATE TABLE cats (
  id            SERIAL PRIMARY KEY,
  name          TEXT UNIQUE NOT NULL 
                CHECK(name = LOWER(name))
);


--
-- tags
--
DROP TABLE IF EXISTS tags CASCADE;
CREATE TABLE tags (
  id            SERIAL PRIMARY KEY,
  name          TEXT UNIQUE NOT NULL 
                CHECK(name = LOWER(name))
);

--
-- objects
--
DROP TABLE IF EXISTS objs CASCADE;
CREATE TABLE objs (
  id            SERIAL PRIMARY KEY,

  user_id       TEXT NOT NULL DEFAULT CURRENT_USER,

  cat_id        INTEGER NOT NULL REFERENCES cats(id)
                ON DELETE CASCADE,

  alias         TEXT NOT NULL DEFAULT ''
                CHECK (alias = '' OR (alias = LOWER(alias) AND alias ~ E'^[\\w_-]+$')),

  title         TEXT NOT NULL DEFAULT '',
  body          TEXT NOT NULL DEFAULT '',

  created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  posted_at     TIMESTAMP DEFAULT NULL
);

--
-- object tags
--
DROP TABLE IF EXISTS obj_tags CASCADE;
CREATE TABLE obj_tags (
  obj_id        INTEGER REFERENCES objs(id)
                ON DELETE CASCADE,
  tag_id        INTEGER REFERENCES tags(id)
                ON DELETE CASCADE,

  UNIQUE(obj_id, tag_id)
);

--
-- object attributes
--
DROP TABLE IF EXISTS obj_attrs CASCADE;
CREATE TABLE obj_attrs (
  obj_id        INTEGER NOT NULL REFERENCES objs(id),
  name          TEXT NOT NULL 
                CHECK(name = LOWER(name)),
  value         TEXT NOT NULL,

  UNIQUE(obj_id, name)
);


-----------------------------------------------------------------------
-- array/hash functions                                              --
-----------------------------------------------------------------------

--
-- create a "hash" from an array of keys and an array of data
--
CREATE OR REPLACE FUNCTION array_zip(
  keys        TEXT[],
  data        TEXT[]
) RETURNS TEXT[] AS $_$
  DECLARE 
    ret       TEXT[] = '{}';
    i         INTEGER;
  BEGIN
    FOR i IN 1..array_upper(data, 1) LOOP
      ret := ret || keys[i] || data[i];
    END LOOP;

    RETURN ret;
  END;
$_$ LANGUAGE 'plpgsql';

--
-- get the specified keys from the given "hash".
--
CREATE OR REPLACE FUNCTION array_get(
  data          TEXT[],
  keys          TEXT[]
) RETURNS TEXT[] AS $_$
  DECLARE 
    ret         TEXT[] = '{}';
    i           INTEGER;
    j           INTEGER;
    o           INTEGER;  
    num_pairs   INTEGER;
    found       BOOLEAN;
  BEGIN
    -- get the number of item pairs
    num_pairs := array_upper(data, 1) / 2;
    
    -- loop over all keys
    FOR i IN 1..array_upper(keys, 1) LOOP
      found := FALSE;

      FOR j IN 0..num_pairs LOOP
        -- calculate key offset
        o := 2 * j + 1;

        IF NOT found AND keys[i] = data[o] THEN
          -- found matching key, add data to results
          found := TRUE;

          -- calculate data offset
          o := 2 * j + 2;

          -- add data to results
          ret := ret || data[o];
        END IF;
      END LOOP;

      -- if we didn't find the key, then add null to result
      IF NOT found THEN
        ret := ret || NULL;
      END IF;
    END LOOP;

    -- return results
    RETURN ret;
  END;
$_$ LANGUAGE 'plpgsql';

--
-- return the elements in a that aren't in b
--
CREATE OR REPLACE FUNCTION array_diff(
  a           TEXT[],
  b           TEXT[]
) RETURNS TEXT[] AS $_$
  DECLARE
    ret       TEXT[] = '{}';
    i         INTEGER;
  BEGIN
    FOR i IN 1..array_upper(a, 1) LOOP
      IF ARRAY[a[i]] <@ b THEN
        NULL;
      ELSE
        ret := ret || a[i];
      END IF;
    END LOOP;

    -- RAISE INFO 'returning %', ret;

    RETURN ret;
  END;
$_$ LANGUAGE 'plpgsql';


-----------------------------------------------------------------------
-- routing functions                                                 --
-----------------------------------------------------------------------

--
-- URL routing regular expressions
--
DROP TABLE IF EXISTS route_matches;
CREATE TABLE route_matches (
  -- matching order
  pos         SERIAL UNIQUE NOT NULL,

  -- default key/value pairs
  defaults    TEXT[],

  -- matching regex
  re          TEXT NOT NULL,

  -- matching regex keys
  keys        TEXT[] NOT NULL,

  -- allowed http methods
  methods     TEXT[]
);

-- populate route_matches
INSERT INTO route_matches(defaults, re, keys, methods) VALUES (
  --
  -- match /type/year/month/day/alias.format (mode = view)
  --

  -- defaults
  ARRAY['mode', 'view'],

  -- match regex
  E'^/([a-z][\\w_-]+)/(\\d{4})/(\\d{2})/(\\d{2})/([a-z][\\w_-]+)\\.([a-z][\\w_-]+)$',

  -- keys to extract from regex
  ARRAY['type', 'year', 'month', 'day', 'post', 'format'],

  -- allowed http methods
  ARRAY['get', 'head']
), (
  --
  -- match /type/year/month/day/alias (mode = view, format = html)
  --

  -- defaults
  ARRAY['mode', 'view', 'format', 'html'],

  -- match regex
  E'^/([a-z][\\w_-]+)/(\\d{4})/(\\d{2})/(\\d{2})/([a-z][\\w_-]+)/?$',
  
  -- keys to extract from regex
  ARRAY['type', 'year', 'month', 'day', 'post'],

  -- allowed http methods
  ARRAY['get', 'head']
), (
  --
  -- match /type/year/month/day (mode = list, format = html)
  --

  -- defaults
  ARRAY['mode', 'list', 'format', 'html'],

  -- match regex
  E'^/([a-z][\\w_-]+)/(\\d{4})/(\\d{2})/(\\d{2})/?$',

  -- keys to extract from regex
  ARRAY['type', 'year', 'month', 'day'],

  -- allowed http methods
  ARRAY['get', 'head']
), (
  --
  -- match /type/year/month (mode = list, format = html)
  --

  -- defaults
  ARRAY['mode', 'list', 'format', 'html'],

  -- match regex
  E'^/([a-z][\\w_-]+)/(\\d{4})/(\\d{2})/?$',

  -- keys to extract from regex
  ARRAY['type', 'year', 'month'],

  -- allowed http methods
  ARRAY['get', 'head']
), (
  --
  -- match /type/year (mode = list, format = html)
  --

  -- defaults
  ARRAY['mode', 'list', 'format', 'html'],

  -- match regex
  E'^/([a-z][\\w_-]+)/(\\d{4})/?$',

  -- keys to extract from regex
  ARRAY['type', 'year'],

  -- allowed http methods
  ARRAY['get', 'head']
), (
  --
  -- match /type/alias.format (mode = view)
  --

  -- defaults
  ARRAY['mode', 'view'],

  -- match regex
  E'^/([a-z][\\w_-]+)/([a-z][\\w_-]+)\\.([a-z][\\w_-]+)$',

  -- keys to extract from regex
  ARRAY['type', 'post', 'format'],

  -- allowed http methods
  ARRAY['get', 'head']
), (
  --
  -- match type, key (view, format = html)
  --

  -- defaults
  ARRAY['mode', 'view', 'format', 'html'],

  -- match regex
  E'^/([a-z][\\w_-]+)/([a-z][\\w_-]+)/?$',

  -- keys to extract from regex
  ARRAY['type', 'post'],

  -- allowed http methods
  ARRAY['get', 'head']
), (
  --
  -- match /alias.format (mode = view, format = html)
  --

  -- defaults
  ARRAY['mode', 'view'],

  -- match regex
  E'^/([a-z][\\w_-]+)\\.([a-z][\\w_-]+)$',

  -- keys to extract from regex
  ARRAY['post', 'format'],

  -- allowed http methods
  ARRAY['get', 'head']
), (
  --
  -- match /type/id/_mode.format
  --

  -- defaults
  NULL,

  -- match regex
  E'^/([a-z][\\w_-]+)/(\\d+)/_([a-z][\\w_-]+)\\.([a-z][\\w_-]+)$',

  -- keys to extract from regex
  ARRAY['type', 'id', 'mode', 'format'],

  -- allowed http methods
  ARRAY['get', 'post']
), (
  --
  -- match /type/id/_mode (format = html)
  --

  -- defaults
  ARRAY['format', 'html'],

  -- match regex
  E'^/([a-z][\\w_-]+)/(\\d+)/_([a-z][\\w_-]+)$',

  -- keys to extract from regex
  ARRAY['type', 'id', 'mode', 'format'],

  -- allowed http methods
  ARRAY['get', 'post']
), (
  --
  -- match /type/_mode.format
  --

  -- defaults
  NULL,

  -- match regex
  E'^/([a-z][\\w_-]+)/_([a-z]\\w+)\\.([a-z][\\w_-]+)$',

  -- keys to extract from regex
  ARRAY['type', 'mode', 'format'],

  -- allowed http methods
  ARRAY['get', 'post']
), (
  --
  -- match /type/_mode (format = html)
  --

  -- defaults
  ARRAY['format', 'html'],

  -- match regex
  E'^/([a-z][\\w_-]+)/_([a-z][\\w_-]+)$',

  -- keys to extract from regex
  ARRAY['type', 'mode', 'format'],

  -- allowed http methods
  ARRAY['get', 'post']
);

--
-- route URL to action
--
CREATE OR REPLACE FUNCTION route_url(
  -- full query path
  query_path  TEXT,

  -- query parameters
  http_method TEXT
) RETURNS TEXT[] AS $_$
  DECLARE
    -- working path
    path            TEXT;

    -- return data
    ret             TEXT[];

    -- match data
    md              TEXT[];

    -- match type or key (item or list)
    re_dunno        TEXT = E'^/([\\w_-]+)/?$';

    r           RECORD;
  BEGIN
    -- lower-case query path
    path := LOWER(query_path);
    
    -- iterate through possible route matches and try each one
    FOR r IN SELECT * 
               FROM route_matches 
              WHERE methods IS NULL 
                 OR LOWER(http_method) = ANY(methods) 
              ORDER BY pos LOOP
      -- try route matche
      md := regexp_matches(path, r.re);

      -- if route matches, then apply defaults and return it
      IF md IS NOT NULL THEN
        -- add defaults to result
        IF r.defaults IS NOT NULL THEN
          ret := ret || r.defaults;
        END IF;

        -- add keys from md to results
        IF r.keys IS NOT NULL THEN
          ret := ret || array_zip(r.keys, md);
        END IF;

        -- return results
        RETURN ret;
      END IF;
    END LOOP;

    -- path could be alias or type, check and see
    md := regexp_matches(path, re_dunno);
    IF md IS NOT NULL THEN
      ret := ret || ARRAY['format', 'html'];

      IF (SELECT name FROM post_types WHERE name = md[1]) IS NOT NULL THEN
        -- is a type
        ret := ret || ARRAY['mode', 'list', 'type', md[1]];
      ELSE
        -- is an alias (maybe)
        ret := ret || ARRAY['mode', 'view', 'post', md[1]];
      END IF;
    ELSE
      -- unknown
      ret := ret || ARRAY['mode', '404'];
    END IF;

    RETURN ret;
  END;
$_$ LANGUAGE 'plpgsql';

-----------------------------------------------------------------------
-- template/filter functions                                         --
-----------------------------------------------------------------------

--
-- apply filters in given string to value.
--
CREATE OR REPLACE FUNCTION apply_filters(
  val           TEXT,
  filters       TEXT
) RETURNS TEXT AS $_$
  DECLARE
    filter_prefix   CONSTANT TEXT = 'fn_';

    -- return value
    ret             TEXT;

    -- query string
    sql             TEXT;

    -- tmp vars
    f               TEXT;
    r               RECORD;
  BEGIN
    -- build initial query
    sql = '''' || replace(val, '''', '''''') || '''';

    -- loop over each filter and apply it
    FOR r IN SELECT * FROM regexp_split_to_table(filters, E'\\|') LOOP
      -- get filter from this row
      f := r.regexp_split_to_table; 
      IF f IS NOT NULL AND LENGTH(f) > 0 THEN
        -- wrap current sql with this filter
        sql := filter_prefix || f || '(' || sql || ')';
      END IF;
    END LOOP;

    -- build and execute query
    sql := 'SELECT ' || sql;
    RAISE INFO 'filter sql = %', sql;
    EXECUTE sql INTO ret;

    -- return result
    RETURN ret;
  END;
$_$ LANGUAGE 'plpgsql';

--
-- apply given argument "hash" to specified template.
--
CREATE OR REPLACE FUNCTION apply_template(
  -- template text
  template      TEXT,
  args          TEXT[]
) RETURNS TEXT AS $_$
  DECLARE
    -- return text
    ret         TEXT;

    -- match data
    md          TEXT;

    i           INTEGER;
    o           INTEGER;

    num_pairs   INTEGER;

    k           TEXT;
    v           TEXT;
    dv          TEXT;

    r           RECORD;
  BEGIN
    -- copy input template
    ret := template;

    -- get the number of key/value pairs
    num_pairs := array_upper(args, 1) / 2;
  
    -- iterate over replacement data
    FOR i IN 0..num_pairs LOOP
      -- get key
      o := 2 * i + 1;
      k := LOWER(args[o]);

      -- get value
      o := 2 * i + 2;
      v := args[o];

      -- md := regexp_matches(ret, E'$\\{(' || LOWER(args[o]) || ')(|[\w_-]+)*\\}');
      -- EXIT WHEN md IS NULL;

      FOR r IN SELECT DISTINCT regexp_matches(ret, E'(\\$\\{(' || k || E')(\\|[\\w_-]+)*\\})', 'g') AS matches LOOP
        IF r.matches[3] IS NOT NULL AND LENGTH(r.matches[3]) > 0 THEN
          -- apply filters to value
          dv := apply_filters(v, r.matches[3]);
        ELSE
          -- no filters, just grab value
          dv := v;
        END IF;
        
        -- replace matches
        ret := replace(ret, r.matches[1], dv);
      END LOOP;
    END LOOP;

    -- return result
    RETURN ret;
  END;
$_$ LANGUAGE 'plpgsql';

-----------------------------------------------------------------------
-- filter functions                                                  --
-----------------------------------------------------------------------

--
-- return the length of the given string
--
CREATE OR REPLACE FUNCTION fn_length(
  str     TEXT
) RETURNS TEXT AS $_$
  BEGIN
    RETURN LENGTH(str);
  END;
$_$ LANGUAGE 'plpgsql';

--
-- return the md5 hex digest of the given string
--
CREATE OR REPLACE FUNCTION fn_md5(
  str     TEXT
) RETURNS TEXT AS $_$
  BEGIN
    RETURN MD5(str);
  END;
$_$ LANGUAGE 'plpgsql';

--
-- base64-encode the given string
--
CREATE OR REPLACE FUNCTION fn_base64(
  str     TEXT
) RETURNS TEXT AS $_$
  BEGIN
    RETURN ENCODE(str, 'base64');
  END;
$_$ LANGUAGE 'plpgsql';

--
-- strip leading and trailing whitespace from the given string
--
CREATE OR REPLACE FUNCTION fn_strip(
  str     TEXT
) RETURNS TEXT AS $_$
  BEGIN
    RETURN regexp_replace(str, E'^\\s+|\\s+$', '', 'g');
  END;
$_$ LANGUAGE 'plpgsql';

--
-- html-escape the given string
--
CREATE OR REPLACE FUNCTION fn_h(
  str     TEXT
) RETURNS TEXT AS $_$
  BEGIN
    RETURN REPLACE(
      REPLACE(
        REPLACE(
          REPLACE(
            REPLACE(str, '&', '&amp;'), 
            '<', '&lt;'
          ), '>', '&gt;'
        ), '''', '&apos;'
      ), '"', '&quot;'
    );
  END;
$_$ LANGUAGE 'plpgsql';

--
-- remove extraneous whitepsace from the given string
--
CREATE OR REPLACE FUNCTION fn_squeeze(
  str     TEXT
) RETURNS TEXT AS $_$
  BEGIN
  END;
$_$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION add_missing_tags(
  in_tags       TEXT[]
) RETURNS VOID AS $_$
  DECLARE
    existing_tags   TEXT[];
    new_tags        TEXT[];
    r               RECORD;
    i               INTEGER;
  BEGIN
    -- get list of existing tags
    FOR r IN SELECT name FROM tags WHERE name = ANY(in_tags) LOOP
      existing_tags = existing_tags || r.name;
    END LOOP;
    
    -- get new tags
    new_tags := array_diff(in_tags, existing_tags);

    -- iterate over missing tags and add each one
    IF new_tags IS NOT NULL AND array_upper(new_tags, 1) > 0 THEN
      FOR i IN 1..array_upper(new_tags, 1) LOOP
        INSERT INTO tags(name) VALUES (new_tags[i]);
      END LOOP;
    END IF;

    RETURN;
  END;
$_$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION add_obj(
  in_cat        TEXT,
  in_alias      TEXT,
  in_title      TEXT,
  in_body       TEXT,
  in_tags       TEXT[],
  in_attrs      TEXT[]
) RETURNS INTEGER AS $_$
  DECLARE
    ret         INTEGER;

    num_pairs   INTEGER;
    o           INTEGER;

    k           TEXT;
    v           TEXT;

    r           RECORD;
    ignored     RECORD;
  BEGIN
    -- create new object
    INSERT INTO objs(cat_id, alias, title, body) 
         VALUES ((SELECT id FROM cats WHERE name = in_cat), in_alias, in_title, in_body) 
      RETURNING id INTO r;
    ret := r.id;

    -- add tags (FIXME)
    IF in_tags IS NOT NULL THEN
      PERFORM add_missing_tags(in_tags);
      FOR r IN SELECT id FROM tags WHERE name = ANY(in_tags) LOOP
        INSERT INTO obj_tags(obj_id, tag_id) VALUES (ret, r.id);
      END LOOP;
    END IF;

    IF in_attrs IS NOT NULL THEN
      -- get the number of key/value pairs
      num_pairs := array_upper(in_attrs, 1) / 2;

      -- loop over key/value pairs
      FOR i IN 0..num_pairs LOOP
        -- get key
        o := 2 * i + 1;
        k := in_attrs[o];

        -- get value
        o := 2 * i + 2;
        v := in_attrs[o];

        -- add object attribute
        INSERT INTO obj_attrs(obj_id, name, value) VALUES (obj_id, k, v);
      END LOOP;
    END IF;

    -- return new object id
    RETURN ret;
  END;
$_$ LANGUAGE 'plpgsql';