-- This file should be parsable by the brep-migrate utility. To decrease the
-- parser complexity, the following restrictions are placed:
--
-- * comments must start with -- at the beginning of the line (ignoring
--   leading spaces)
-- * only CREATE and DROP statements for FUNCTION, TYPE and FOREIGN TABLE
-- * function bodies must be defined using $$-quoted strings
-- * strings other then function bodies must be quoted with ' or "
-- * statements must end with ";\n"
--

-- There is no need to drop to_tsvector() explicitly, as we can rely on "DROP
-- TYPE IF EXISTS weighted_text CASCADE" statement below, which will drop all
-- objects that depend on this type. Moreover this DROP FUNCTION statement will
-- fail for old versions of PostgreSQL (experienced for 9.2.14) with error:
-- type "weighted_text" does not exist.
--
-- DROP FUNCTION IF EXISTS to_tsvector(IN document weighted_text);
--
DROP FUNCTION IF EXISTS search_packages(IN query tsquery, INOUT name CITEXT);
DROP FUNCTION IF EXISTS search_latest_packages(IN query tsquery);
DROP FUNCTION IF EXISTS latest_package(INOUT name CITEXT);
DROP FUNCTION IF EXISTS latest_packages();

DROP TYPE IF EXISTS weighted_text CASCADE;
CREATE TYPE weighted_text AS (a TEXT, b TEXT, c TEXT, d TEXT);

-- Return the latest versions of internal packages as a set of package rows.
--
CREATE FUNCTION
latest_packages()
RETURNS SETOF package AS $$
  SELECT p1.*
  FROM package p1 LEFT JOIN package p2 ON (
    p1.internal_repository IS NOT NULL AND p1.name = p2.name AND
    p2.internal_repository IS NOT NULL AND
    (p1.version_epoch < p2.version_epoch OR
     p1.version_epoch = p2.version_epoch AND
     (p1.version_canonical_upstream < p2.version_canonical_upstream OR
      p1.version_canonical_upstream = p2.version_canonical_upstream AND
      (p1.version_canonical_release < p2.version_canonical_release OR
       p1.version_canonical_release = p2.version_canonical_release AND
       p1.version_revision < p2.version_revision))))
  WHERE
    p1.internal_repository IS NOT NULL AND p2.name IS NULL;
$$ LANGUAGE SQL STABLE;

-- Find the latest version of an internal package having the specified name.
-- Return a single row containing the package id, empty row set if the package
-- not found.
--
CREATE FUNCTION
latest_package(INOUT name CITEXT,
               OUT version_epoch INTEGER,
               OUT version_canonical_upstream TEXT,
               OUT version_canonical_release TEXT,
               OUT version_revision INTEGER)
RETURNS SETOF record AS $$
  SELECT name, version_epoch, version_canonical_upstream,
         version_canonical_release, version_revision
  FROM latest_packages()
  WHERE name = latest_package.name;
$$ LANGUAGE SQL STABLE;

-- Search for the latest version of an internal packages matching the specified
-- search query. Return a set of rows containing the package id and search
-- rank. If query is NULL, then match all packages and return 0 rank for
-- all rows.
--
CREATE FUNCTION
search_latest_packages(IN query tsquery,
                       OUT name CITEXT,
                       OUT version_epoch INTEGER,
                       OUT version_canonical_upstream TEXT,
                       OUT version_canonical_release TEXT,
                       OUT version_revision INTEGER,
                       OUT rank real)
RETURNS SETOF record AS $$
  SELECT name, version_epoch, version_canonical_upstream,
         version_canonical_release, version_revision,
         CASE
	   WHEN query IS NULL THEN 0
-- Weight mapping:           D     C    B    A
	   ELSE ts_rank_cd('{0.05, 0.2, 0.9, 1.0}', search_index, query)
	 END AS rank
  FROM latest_packages()
  WHERE query IS NULL OR search_index @@ query;
$$ LANGUAGE SQL STABLE;

-- Search for packages matching the search query and having the specified name.
-- Return a set of rows containing the package id and search rank. If query
-- is NULL, then match all packages and return 0 rank for all rows.
--
CREATE FUNCTION
search_packages(IN query tsquery,
                INOUT name CITEXT,
                OUT version_epoch INTEGER,
                OUT version_canonical_upstream TEXT,
                OUT version_canonical_release TEXT,
                OUT version_revision INTEGER,
                OUT rank real)
RETURNS SETOF record AS $$
  SELECT name, version_epoch, version_canonical_upstream,
         version_canonical_release, version_revision,
         CASE
	   WHEN query IS NULL THEN 0
-- Weight mapping:           D     C    B    A
	   ELSE ts_rank_cd('{0.05, 0.2, 0.9, 1.0}', search_index, query)
	 END AS rank
  FROM package
  WHERE
  internal_repository IS NOT NULL AND name = search_packages.name AND
  (query IS NULL OR search_index @@ query);
$$ LANGUAGE SQL STABLE;

-- Parse weighted_text to tsvector.
--
CREATE FUNCTION
to_tsvector(IN document weighted_text)
RETURNS tsvector AS $$
  SELECT
    CASE
      WHEN document IS NULL THEN NULL
      ELSE
        setweight(to_tsvector(document.a), 'A') ||
	setweight(to_tsvector(document.b), 'B') ||
	setweight(to_tsvector(document.c), 'C') ||
	setweight(to_tsvector(document.d), 'D')
    END
$$ LANGUAGE SQL IMMUTABLE;