-- 檔案名稱: wov.sql
-- 程式說明: 女聲資料庫定義檔
-- 程式作者: 依瑪貓 imacat <imacat@mail.imacat.idv.tw>
-- 初稿日期: 2004-10-16
-- 版權字樣: 版權所有 (c) 2004-2014 依瑪貓
-- Set PGCLIENTENCODING=utf8 before restoring it

SET NAMES 'utf8';

START TRANSACTION;


--
-- Table structure for table "mtime"
--

CREATE TABLE mtime (
  tabname varchar(16) NOT NULL PRIMARY KEY,
  mtime timestamp NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON mtime TO nobody;


--
-- Function definition for function "eschtml"
--
-- integer eschtml(source text)
CREATE FUNCTION eschtml(source text) RETURNS text AS '
  DECLARE
    result text;
  BEGIN
    result := source;
    result := replace(result, ''&'', ''&amp;'');
    result := replace(result, ''<'', ''&lt;'');
    result := replace(result, ''>'', ''&gt;'');
    result := replace(result, ''"'', ''&quot;'');
    return result;
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION eschtml(source text) TO nobody;


--
-- Table structure for table "country"
--

CREATE TABLE country (
  sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
  id char(2) NOT NULL UNIQUE CHECK (position(' ' in id) = 0),
  name_en varchar(64) NOT NULL CHECK (name_en != ''),
  name_zhtw varchar(32) CHECK (name_zhtw IS NULL OR name_zhtw != ''),
  special boolean NOT NULL DEFAULT FALSE,
  created timestamp NOT NULL DEFAULT now(),
  createdby int NOT NULL,
  updated timestamp NOT NULL DEFAULT now(),
  updatedby int NOT NULL
);
GRANT SELECT, INSERT, UPDATE, DELETE ON country TO nobody;


--
-- Table structure for table "users"
--

CREATE TABLE users (
  sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
  id varchar(32) NOT NULL UNIQUE CHECK (char_length(id) >= 3),
  passwd char(32) NOT NULL,
  name varchar(32) NOT NULL CHECK (name != ''),
  disabled boolean NOT NULL DEFAULT FALSE,
  deleted boolean NOT NULL DEFAULT FALSE,
  lang varchar(5) CHECK (lang IS NULL OR lang != ''),
  visits smallint NOT NULL DEFAULT 0 CHECK (visits >= 0),
  visited timestamp,
  ip inet,
  host varchar(128),
  ct char(2) REFERENCES country (id) ON UPDATE CASCADE DEFERRABLE,
  created timestamp NOT NULL DEFAULT now(),
  createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
  updated timestamp NOT NULL DEFAULT now(),
  updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE
);
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO nobody;

CREATE VIEW users_list AS
  SELECT
    users.sn AS sn,
    users.id AS id,
    users.name AS name,
    CASE WHEN users.disabled THEN '停用'
      ELSE ''
    END AS disabled,
    CASE WHEN users.deleted THEN '已刪'
      ELSE ''
    END AS deleted,
    CASE WHEN users.lang IS NULL THEN '（無）'
      ELSE CASE users.lang
        WHEN 'en' THEN '英文'
        WHEN 'zh-tw' THEN '繁體中文'
        WHEN 'zh-cn' THEN '簡體中文'
        WHEN 'ja' THEN '日文'
        WHEN 'de' THEN '德文'
        WHEN 'es' THEN '西班牙文'
        ELSE users.lang
      END
    END AS lang,
    users.visits AS visits,
    CASE WHEN users.visited IS NULL THEN '（無）'
      ELSE to_char(users.visited, 'YYYY-MM-DD HH:MI:SS')
    END AS visited,
    CASE WHEN users.ip IS NULL THEN '（無）'
      ELSE host(users.ip)
    END AS ip,
    CASE WHEN users.host IS NULL THEN '（無）'
      ELSE users.host
    END AS host,
    CASE WHEN users.ct IS NULL THEN '（無）'
      ELSE ct.name_zhtw
    END AS ct,
    to_char(users.created, 'YYYY-MM-DD HH:MI:SS') AS created,
    createdby.name AS createdby,
    to_char(users.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
    updatedby.name AS updatedby
  FROM users
    LEFT JOIN country AS ct ON users.ct = ct.id
    LEFT JOIN users AS createdby ON users.createdby = createdby.sn
    LEFT JOIN users AS updatedby ON users.updatedby = updatedby.sn
  ORDER BY id;
GRANT SELECT ON users_list TO nobody;

-- INSERT INTO users (sn, id, passwd, name, disabled, deleted, lang, visits, visited, ip, host, created, createdby, updated, updatedby) VALUES (923153018, 'imacat', 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', '依瑪貓', FALSE, FALSE, NULL, 0, NULL, NULL, NULL, now(), 923153018, now(), 923153018);
-- INSERT INTO users (sn, id, passwd, name, disabled, deleted, lang, visits, visited, ip, host, created, createdby, updated, updatedby) VALUES (460376330, 'mandy', 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', '小招', FALSE, FALSE, NULL, 0, NULL, NULL, NULL, now(), 923153018, now(), 923153018);
-- INSERT INTO users (sn, id, passwd, name, disabled, deleted, lang, visits, visited, ip, host, created, createdby, updated, updatedby) VALUES (723676436, 'guestbook', 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx', '留言本', FALSE, FALSE, NULL, 0, NULL, NULL, NULL, now(), 923153018, now(), 923153018);

--
-- Fixing the country table
--

ALTER TABLE country ADD FOREIGN KEY (createdby) REFERENCES users ON UPDATE CASCADE DEFERRABLE;
ALTER TABLE country ADD FOREIGN KEY (updatedby) REFERENCES users ON UPDATE CASCADE DEFERRABLE;
CREATE VIEW country_list AS
  SELECT
    country.sn AS sn,
    country.id AS id,
    COALESCE(country.name_zhtw, country.name_en) AS title,
    CASE WHEN country.special THEN '特殊'
      ELSE ''
    END AS special,
    to_char(country.created, 'YYYY-MM-DD HH:MI:SS') AS created,
    createdby.name AS createdby,
    to_char(country.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
    updatedby.name AS updatedby
  FROM country
    LEFT JOIN users AS createdby ON country.createdby = createdby.sn
    LEFT JOIN users AS updatedby ON country.updatedby = updatedby.sn
  ORDER BY id;
GRANT SELECT ON country_list TO nobody;


--
-- Table structure for table "groups"
--

CREATE TABLE groups (
  sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
  id varchar(16) NOT NULL UNIQUE CHECK (char_length(id) >= 3),
  dsc varchar(64) NOT NULL CHECK (dsc != ''),
  created timestamp NOT NULL DEFAULT now(),
  createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
  updated timestamp NOT NULL DEFAULT now(),
  updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE
);
GRANT SELECT, INSERT, UPDATE, DELETE ON groups TO nobody;

CREATE VIEW groups_list AS
  SELECT
    groups.sn AS sn,
    groups.id AS id,
    groups.dsc AS dsc,
    to_char(groups.created, 'YYYY-MM-DD HH:MI:SS') AS created,
    createdby.name AS createdby,
    to_char(groups.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
    updatedby.name AS updatedby
  FROM groups
    LEFT JOIN users AS createdby ON groups.createdby = createdby.sn
    LEFT JOIN users AS updatedby ON groups.updatedby = updatedby.sn
  ORDER BY id;
GRANT SELECT ON groups_list TO nobody;

-- INSERT INTO groups (sn, id, dsc, created, createdby, updated, updatedby) VALUES (553229108, 'root', '總管理員', now(), 923153018, now(), 923153018);
-- INSERT INTO groups (sn, id, dsc, created, createdby, updated, updatedby) VALUES (802339805, 'guests', '暱名訪客', now(), 923153018, now(), 923153018);
-- INSERT INTO groups (sn, id, dsc, created, createdby, updated, updatedby) VALUES (958210993, 'users', '已登入使用者', now(), 923153018, now(), 923153018);
-- INSERT INTO groups (sn, id, dsc, created, createdby, updated, updatedby) VALUES (329685674, 'admin', '所有網站管理員', now(), 923153018, now(), 923153018);
-- INSERT INTO groups (sn, id, dsc, created, createdby, updated, updatedby) VALUES (157696540, 'acctman', '帳號管理員', now(), 923153018, now(), 923153018);
-- INSERT INTO groups (sn, id, dsc, created, createdby, updated, updatedby) VALUES (390105230, 'editor', '網站編輯', now(), 923153018, now(), 923153018);


--
-- Table structure for table "usermem"
--

CREATE TABLE usermem (
  sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
  grp int NOT NULL REFERENCES groups ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE,
  member int NOT NULL REFERENCES users ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE,
  created timestamp NOT NULL DEFAULT now(),
  createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
  updated timestamp NOT NULL DEFAULT now(),
  updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
  UNIQUE (grp, member)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON usermem TO nobody;

CREATE VIEW usermem_list AS
  SELECT
    usermem.sn AS sn,
    groups.id || ' (' || groups.dsc || ')' AS grp,
    members.id || ' (' || members.name || ')' AS member,
    to_char(usermem.created, 'YYYY-MM-DD HH:MI:SS') AS created,
    createdby.name AS createdby,
    to_char(usermem.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
    updatedby.name AS updatedby
  FROM usermem
    LEFT JOIN groups ON usermem.grp = groups.sn
    LEFT JOIN users AS members ON usermem.member = members.sn
    LEFT JOIN users AS createdby ON usermem.createdby = createdby.sn
    LEFT JOIN users AS updatedby ON usermem.updatedby = updatedby.sn
  ORDER BY grp, member;
GRANT SELECT ON usermem_list TO nobody;

-- INSERT INTO usermem (sn, grp, member, created, createdby, updated, updatedby) VALUES (593684712, 553229108, 923153018, now(), 923153018, now(), 923153018);


--
-- Table structure for table "groupmem"
--

CREATE TABLE groupmem (
  sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
  grp int NOT NULL REFERENCES groups ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE,
  member int NOT NULL REFERENCES groups ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE CHECK (member != grp),
  created timestamp NOT NULL DEFAULT now(),
  createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
  updated timestamp NOT NULL DEFAULT now(),
  updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
  UNIQUE (grp, member)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON groupmem TO nobody;

CREATE VIEW groupmem_list AS
  SELECT
    groupmem.sn AS sn,
    groups.id || ' (' || groups.dsc || ')' AS grp,
    members.id || ' (' || members.dsc || ')' AS member,
    to_char(groupmem.created, 'YYYY-MM-DD HH:MI:SS') AS created,
    createdby.name AS createdby,
    to_char(groupmem.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
    updatedby.name AS updatedby
  FROM groupmem
    LEFT JOIN groups ON groupmem.grp = groups.sn
    LEFT JOIN groups AS members ON groupmem.member = members.sn
    LEFT JOIN users AS createdby ON groupmem.createdby = createdby.sn
    LEFT JOIN users AS updatedby ON groupmem.updatedby = updatedby.sn
  ORDER BY grp, member;
GRANT SELECT ON groupmem_list TO nobody;

-- INSERT INTO groupmem (sn, grp, member, created, createdby, updated, updatedby) VALUES (569742102, 329685674, 157696540, now(), 923153018, now(), 923153018);
-- INSERT INTO groupmem (sn, grp, member, created, createdby, updated, updatedby) VALUES (859385977, 329685674, 390105230, now(), 923153018, now(), 923153018);


--
-- Table structure for table "scptpriv"
--

CREATE TABLE scptpriv (
  sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
  script varchar(64) NOT NULL CHECK (script != ''),
  grp int NOT NULL REFERENCES groups ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE,
  created timestamp NOT NULL DEFAULT now(),
  createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
  updated timestamp NOT NULL DEFAULT now(),
  updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
  UNIQUE (script, grp)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON scptpriv TO nobody;

CREATE VIEW scptpriv_list AS
  SELECT
    scptpriv.sn AS sn,
    scptpriv.script AS script,
    groups.dsc AS grp,
    to_char(scptpriv.created, 'YYYY-MM-DD HH:MI:SS') AS created,
    createdby.name AS createdby,
    to_char(scptpriv.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
    updatedby.name AS updatedby
  FROM scptpriv
    LEFT JOIN groups ON scptpriv.grp = groups.sn
    LEFT JOIN users AS createdby ON scptpriv.createdby = createdby.sn
    LEFT JOIN users AS updatedby ON scptpriv.updatedby = updatedby.sn
  ORDER BY script, grp;
GRANT SELECT ON scptpriv_list TO nobody;


--
-- Table structure for table "userpref"
--

CREATE TABLE userpref (
  sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
  usr int REFERENCES users ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE,
  domain varchar(64) CHECK (domain IS NULL OR domain != ''),
  name varchar(16) NOT NULL CHECK (name != ''),
  value varchar(255) NOT NULL,
  created timestamp NOT NULL DEFAULT now(),
  createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
  updated timestamp NOT NULL DEFAULT now(),
  updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
  UNIQUE (usr, domain, name)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON userpref TO nobody;

CREATE VIEW userpref_list AS
  SELECT
    userpref.sn AS sn,
    CASE WHEN userpref.usr IS NOT NULL THEN users.name
      ELSE '所有人'
    END AS usr,
    CASE WHEN userpref.domain IS NOT NULL THEN userpref.domain
      ELSE '所有地方'
    END AS domain,
    userpref.name AS name,
    userpref.value AS value,
    to_char(userpref.created, 'YYYY-MM-DD HH:MI:SS') AS created,
    createdby.name AS createdby,
    to_char(userpref.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
    updatedby.name AS updatedby
  FROM userpref LEFT JOIN users ON userpref.usr = users.sn
    LEFT JOIN users AS createdby ON userpref.createdby = createdby.sn
    LEFT JOIN users AS updatedby ON userpref.updatedby = updatedby.sn
  ORDER BY domain, usr, name;
GRANT SELECT ON userpref_list TO nobody;


--
-- Function definitions for table "guestbook"
--

-- integer guestbook_oldlen(date timestamp, ip inet, hostname text, name text, identity text, location text, email text, url text, message text, updated timestamp, updatedby_arg integer)
CREATE FUNCTION guestbook_oldlen(date timestamp, ip inet, hostname text, name text, identity text, location text, email text, url text, message text, updated timestamp, updatedby_arg integer) RETURNS integer AS '
  DECLARE
    row record;
    len integer;
  BEGIN
    -- <record></record>: 19, sn: 30 + 1, date: 54 + 1
    len := 105;
    len := len + octet_length(host(ip)) + 22;
    IF hostname IS NOT NULL THEN
      len := len + octet_length(hostname) + 24;
    END IF;
    IF name IS NOT NULL THEN
      len := len + octet_length(eschtml(name)) + 24;
    END IF;
    IF identity IS NOT NULL THEN
      len := len + octet_length(eschtml(identity)) + 28;
    END IF;
    IF location IS NOT NULL THEN
      len := len + octet_length(eschtml(location)) + 28;
    END IF;
    IF email IS NOT NULL THEN
      len := len + octet_length(eschtml(email)) + 25;
    END IF;
    IF url IS NOT NULL THEN
      len := len + octet_length(eschtml(url)) + 23;
    END IF;
    IF message IS NOT NULL THEN
      len := len + octet_length(eschtml(message)) + 27;
    END IF;
    IF updated != date THEN
      len := len + 58;
      IF updatedby_arg = 923153018 THEN
        len := len + 35;
      ELSIF updatedby_arg = 460376330 THEN
        len := len + 34;
      ELSE
        SELECT INTO row name FROM users WHERE sn=updatedby_arg;
        len := len + octet_length(row.name) + 29;
      END IF;
    END IF;
    RETURN len;
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION guestbook_oldlen(date timestamp, ip inet, hostname text, name text, identity text, location text, email text, url text, message text, updated timestamp, updatedby_arg integer) TO nobody;
-- integer guestbook_oldlen(sn_arg integer)
CREATE FUNCTION guestbook_oldlen(sn_arg integer) RETURNS integer AS '
  DECLARE
    row record;
  BEGIN
    IF sn_arg IS NULL THEN
      RETURN NULL;
    END IF;
    SELECT INTO row * FROM guestbook WHERE sn=sn_arg;
    IF NOT FOUND THEN
      RETURN NULL;
    END IF;
    RETURN guestbook_oldlen(row.created, row.ip, row.host, row.name, row.identity, row.location, row.email, row.url, row.message, row.updated, row.updatedby);
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION guestbook_oldlen(sn_arg integer) TO nobody;


--
-- Table structure for table "guestbook"
--

CREATE TABLE guestbook (
  sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
  name varchar(32) CHECK (name IS NULL OR name != ''),
  identity varchar(64) CHECK (identity IS NULL OR identity != ''),
  location varchar(64) CHECK (location IS NULL OR location != ''),
  email varchar(64) CHECK (email IS NULL OR email != ''),
  url varchar(128) CHECK (url IS NULL OR (url != '' AND url != 'http://')),
  message text NOT NULL CHECK (message != ''),
  hid boolean NOT NULL DEFAULT FALSE,
  ip inet NOT NULL,
  host varchar(255) CHECK (host IS NULL OR host != ''),
  ct char(2) NOT NULL REFERENCES country (id) ON UPDATE CASCADE DEFERRABLE,
  pageno int NOT NULL,
  oldpageno int,
  created timestamp NOT NULL DEFAULT now(),
  createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
  updated timestamp NOT NULL DEFAULT now(),
  updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE
);
GRANT SELECT, INSERT, UPDATE, DELETE ON guestbook TO nobody;

CREATE VIEW guestbook_list AS
  SELECT
    '/cgi-bin/guestbook.cgi?pageno=' || guestbook.pageno
      || '#msg' || guestbook.sn
    AS _viewurl,
    guestbook.sn AS sn,
    to_char(guestbook.created, 'YYYY-MM-DD') AS date,
    COALESCE(guestbook.name, '（未設定）') AS name,
    COALESCE(guestbook.identity, '（未設定）') AS identity,
    COALESCE(guestbook.location, '（未設定）') AS location,
    COALESCE(guestbook.email, '（未設定）') AS email,
    COALESCE(guestbook.url, '（未設定）') AS url,
    guestbook.message AS message,
    CASE WHEN guestbook.hid THEN '隱藏'
      ELSE '秀出'
    END AS hid,
    host(guestbook.ip) AS ip,
    COALESCE(guestbook.host, '（不可考）') AS host,
    COALESCE(country.name_zhtw, country.name_en) AS ct,
    guestbook.pageno AS pageno,
    guestbook.oldpageno AS oldpageno,
    to_char(guestbook.created, 'YYYY-MM-DD HH:MI:SS') AS created,
    createdby.name AS createdby,
    to_char(guestbook.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
    updatedby.name AS updatedby
  FROM guestbook
    LEFT JOIN country ON guestbook.ct = country.id
    LEFT JOIN users AS createdby ON guestbook.createdby = createdby.sn
    LEFT JOIN users AS updatedby ON guestbook.updatedby = updatedby.sn
  ORDER BY guestbook.created DESC;
GRANT SELECT ON guestbook_list TO nobody;

CREATE VIEW guestbook_public AS
  SELECT
    sn AS sn,
    extract(epoch FROM created) AS date,
    name AS name,
    identity AS identity,
    location AS location,
    email AS email,
    url AS url,
    message AS message,
    pageno AS pageno,
    oldpageno AS oldpageno
  FROM guestbook
  WHERE NOT hid
  ORDER BY guestbook.created DESC;
GRANT SELECT ON guestbook_public TO nobody;


--
-- Table structure for table "pages"
--

CREATE TABLE pages (
  sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
  path varchar(64) NOT NULL UNIQUE CHECK (path != ''),
  ord smallint NOT NULL DEFAULT 5000 CHECK (ord >= 0 AND ord < 10000),
  title varchar(128) NOT NULL CHECK (title != ''),
  title_en varchar(128) NOT NULL CHECK (title_en != ''),
  body text NOT NULL CHECK (body != ''),
  kw varchar(128) NOT NULL CHECK (kw != ''),
  html boolean NOT NULL DEFAULT FALSE,
  hid boolean NOT NULL DEFAULT FALSE,
  created timestamp NOT NULL DEFAULT now(),
  createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
  updated timestamp NOT NULL DEFAULT now(),
  updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE
);
GRANT SELECT, INSERT, UPDATE, DELETE ON pages TO nobody;

CREATE VIEW pages_list AS
  SELECT
    pages.path AS _viewurl,
    pages.sn AS sn,
    pages.path AS path,
    pages.ord AS ord,
    pages.title AS title,
    pages.title_en AS title_en,
    pages.body AS body,
    pages.kw AS kw,
    CASE WHEN pages.html THEN 'HTML'
      ELSE '純文字'
    END AS html,
    CASE WHEN pages.hid THEN '隱藏'
      ELSE '秀出'
    END AS hid,
    to_char(pages.created, 'YYYY-MM-DD HH:MI:SS') AS created,
    createdby.name AS createdby,
    to_char(pages.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
    updatedby.name AS updatedby
  FROM pages
    LEFT JOIN users AS createdby ON pages.createdby = createdby.sn
    LEFT JOIN users AS updatedby ON pages.updatedby = updatedby.sn
  ORDER BY path;
GRANT SELECT ON pages_list TO nobody;


--
-- Function definitions for table "links"
--

-- boolean linkcat_id_unique(id_arg text, sn_arg integer, parent_arg integer);
CREATE FUNCTION linkcat_id_unique(id_arg text, sn_arg integer, parent_arg integer) RETURNS boolean AS '
  BEGIN
    IF parent_arg IS NULL THEN
      -- TODO: 2019/3/9 Removed schema "public"?  Added or it will not work on restore.  Not knowing why.
      PERFORM * FROM public.linkcat
        WHERE id=id_arg AND sn!=sn_arg AND parent IS NULL;
      RETURN NOT FOUND;
    ELSE
      -- TODO: 2019/3/9 Removed schema "public"?  Added or it will not work on restore.  Not knowing why.
      PERFORM * FROM public.linkcat
        WHERE id=id_arg AND sn!=sn_arg AND parent=parent_arg;
      RETURN NOT FOUND;
    END IF;
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION linkcat_id_unique(id_arg text, sn_arg integer, parent_arg integer) TO nobody;

-- numeric linkcat_fullord(parent_arg integer, ord_arg integer);
CREATE FUNCTION linkcat_fullord(parent_arg integer, ord_arg integer) RETURNS numeric AS '
  DECLARE
    row record;
    sn_loop integer;
    ord_loop numeric;
  BEGIN
    sn_loop := parent_arg;
    ord_loop := ord_arg;
    WHILE sn_loop IS NOT NULL LOOP
      SELECT INTO row parent, ord FROM linkcat WHERE sn=sn_loop;
      IF NOT FOUND THEN
        RETURN NULL;
      END IF;
      sn_loop := row.parent;
      ord_loop := row.ord + ord_loop / 100;
    END LOOP;
    RETURN ord_loop;
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION linkcat_fullord(parent_arg integer, ord_arg integer) TO nobody;
-- numeric linkcat_fullord(sn_arg integer);
CREATE FUNCTION linkcat_fullord(sn_arg integer) RETURNS numeric AS '
  BEGIN
    RETURN linkcat_fullord(sn_arg, 0);
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION linkcat_fullord(sn_arg integer) TO nobody;

-- boolean linkcat_isshown(sn_arg integer, hid_arg boolean, parent_arg integer);
CREATE FUNCTION linkcat_isshown(sn_arg integer, hid_arg boolean, parent_arg integer) RETURNS boolean AS '
  DECLARE
    row record;
    sn_loop integer;
  BEGIN
    IF hid_arg THEN
      RETURN FALSE;
    END IF;
    -- Check if we are hidden by our ancestors
    sn_loop := parent_arg;
    WHILE sn_loop IS NOT NULL LOOP
      SELECT INTO row parent, hid FROM linkcat WHERE sn=sn_loop;
      IF row.hid THEN
        RETURN FALSE;
      END IF;
      sn_loop = row.parent;
    END LOOP;
    -- Check if we have childs
    PERFORM links.sn FROM links
      INNER JOIN linkcatz ON linkcatz.link=links.sn
      WHERE NOT links.hid AND linkcatz.cat=sn_arg;
    IF FOUND THEN
      RETURN TRUE;
    END IF;
    -- Check if we have shown child categories
    PERFORM sn FROM linkcat WHERE parent=sn_arg AND linkcat_isshown(sn, hid, parent);
    IF FOUND THEN
      RETURN TRUE;
    END IF;
    RETURN FALSE;
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION linkcat_isshown(sn_arg integer, hid_arg boolean, parent_arg integer) TO nobody;
-- boolean linkcat_isshown(sn_arg integer);
CREATE FUNCTION linkcat_isshown(sn_arg integer) RETURNS boolean AS '
  DECLARE
    row record;
  BEGIN
    SELECT INTO row parent, hid FROM linkcat WHERE sn=sn_arg;
    IF NOT FOUND THEN
      RETURN NULL;
    END IF;
    RETURN linkcat_isshown(sn_arg, row.hid, row.parent);
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION linkcat_isshown(sn_arg integer) TO nobody;
-- boolean linkcat_isshown_preview(sn_arg integer, hid_arg boolean, parent_arg integer);
CREATE FUNCTION linkcat_isshown_preview(sn_arg integer, hid_arg boolean, parent_arg integer) RETURNS boolean AS '
  DECLARE
    row record;
    sn_loop integer;
  BEGIN
    IF hid_arg THEN
      RETURN FALSE;
    END IF;
    -- Check if we are hidden by our ancestors
    sn_loop := parent_arg;
    WHILE sn_loop IS NOT NULL LOOP
      SELECT INTO row parent, hid FROM linkcat WHERE sn=sn_loop;
      IF row.hid THEN
        RETURN FALSE;
      END IF;
      sn_loop = row.parent;
    END LOOP;
    RETURN TRUE;
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION linkcat_isshown_preview(sn_arg integer, hid_arg boolean, parent_arg integer) TO nobody;

-- text linkcat_path(sn_arg integer, id_arg text, parent_arg integer);
CREATE FUNCTION linkcat_path(sn_arg integer, id_arg text, parent_arg integer) RETURNS text AS '
  DECLARE
    row record;
    sn_loop integer;
    path text;
  BEGIN
    PERFORM sn FROM linkcat
      WHERE parent=sn_arg
      AND linkcat_isshown(sn, hid, parent);
    IF FOUND THEN
      path := ''/'' || id_arg || ''/'';
    ELSE
      path := ''/'' || id_arg || ''.html'';
    END IF;
    sn_loop := parent_arg;
    WHILE sn_loop IS NOT NULL LOOP
      SELECT INTO row parent, id FROM linkcat WHERE sn=sn_loop;
      path := ''/'' || row.id || path;
      sn_loop := row.parent;
    END LOOP;
    RETURN path;
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION linkcat_path(sn_arg integer, id_arg text, parent_arg integer) TO nobody;
-- text linkcat_path(sn_arg integer);
CREATE FUNCTION linkcat_path(sn_arg integer) RETURNS text AS '
  DECLARE
    row record;
  BEGIN
    IF sn_arg IS NULL THEN
      RETURN NULL;
    END IF;
    SELECT INTO row parent, id FROM linkcat WHERE sn=sn_arg;
    IF NOT FOUND THEN
      RETURN NULL;
    END IF;
    RETURN linkcat_path(sn_arg, row.id, row.parent);
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION linkcat_path(sn_arg integer) TO nobody;

-- boolean linkcat_ischild(parent_arg integer, child_arg integer);
CREATE FUNCTION linkcat_ischild(parent_arg integer, child_arg integer) RETURNS boolean AS '
  DECLARE
    row record;
    sn_loop integer;
  BEGIN
    sn_loop := child_arg;
    WHILE sn_loop IS NOT NULL LOOP
      SELECT INTO row parent FROM linkcat WHERE sn=sn_loop;
      IF NOT FOUND THEN
        RETURN FALSE;
      END IF;
      IF row.parent = parent_arg THEN
        RETURN TRUE;
      END IF;
      sn_loop := row.parent;
    END LOOP;
    RETURN FALSE;
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION linkcat_ischild(parent_arg integer, child_arg integer) TO nobody;

-- text linkcat_fulltitle(sn_arg integer);
CREATE FUNCTION linkcat_fulltitle(sn_arg integer) RETURNS text AS '
  DECLARE
    row record;
    sn_loop integer;
    title_full text;
  BEGIN
    IF sn_arg IS NULL THEN
      RETURN NULL;
    END IF;
    SELECT INTO row * FROM linkcat WHERE sn=sn_arg;
    IF NOT FOUND THEN
      RETURN NULL;
    END IF;
    title_full := row.title;
    WHILE row.parent IS NOT NULL LOOP
      sn_loop := row.parent;
      SELECT INTO row * FROM linkcat WHERE sn=sn_loop;
      title_full := row.title || '' / '' || title_full;
    END LOOP;
    RETURN title_full;
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION linkcat_fulltitle(sn_arg integer) TO nobody;
-- text linkcat_fulltitle(parent_arg integer, title_arg text);
CREATE FUNCTION linkcat_fulltitle(parent_arg integer, title_arg text) RETURNS text AS '
  BEGIN
    IF parent_arg IS NULL THEN
      RETURN title_arg;
    END IF;
    RETURN linkcat_fulltitle(parent_arg) || '' / '' || title_arg;
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION linkcat_fulltitle(parent_arg integer, title_arg text) TO nobody;


--
-- Table structure for table "linkcat"
--

CREATE TABLE linkcat (
  sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
  parent int REFERENCES linkcat ON UPDATE CASCADE DEFERRABLE CHECK (parent IS NULL OR (parent != sn AND NOT linkcat_ischild(sn, parent))),
  id varchar(8) NOT NULL CHECK (char_length(id) >= 2 AND linkcat_id_unique(id, sn, parent)),
  ord smallint NOT NULL DEFAULT 50 CHECK (ord >= 0 AND ord < 100),
  title varchar(128) NOT NULL CHECK (title != ''),
  title_en varchar(128) CHECK (title_en IS NULL OR title_en != ''),
  kw varchar(128) NOT NULL CHECK (kw != ''),
  hid boolean NOT NULL DEFAULT FALSE,
  created timestamp NOT NULL DEFAULT now(),
  createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
  updated timestamp NOT NULL DEFAULT now(),
  updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE
);
GRANT SELECT, INSERT, UPDATE, DELETE ON linkcat TO nobody;

CREATE VIEW linkcat_list AS
  SELECT
    '/links' || linkcat_path(linkcat.sn, linkcat.id, linkcat.parent) AS _viewurl,
    linkcat.sn AS sn,
    linkcat.id AS id,
    linkcat.ord AS ord,
    linkcat_fulltitle(linkcat.parent, linkcat.title) AS title,
    linkcat.title_en AS title_en,
    linkcat.kw AS kw,
    CASE WHEN linkcat.hid THEN '隱藏'
      ELSE '秀出'
    END AS hid,
    to_char(linkcat.created, 'YYYY-MM-DD HH:MI:SS') AS created,
    createdby.name AS createdby,
    to_char(linkcat.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
    updatedby.name AS updatedby
  FROM linkcat
    LEFT JOIN users AS createdby ON linkcat.createdby = createdby.sn
    LEFT JOIN users AS updatedby ON linkcat.updatedby = updatedby.sn
  ORDER BY linkcat_fullord(linkcat.parent, linkcat.ord), id;
GRANT SELECT ON linkcat_list TO nobody;


--
-- Table structure for table "links"
--

CREATE TABLE links (
  sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
  title varchar(96) NOT NULL CHECK (title != ''),
  title_2ln varchar(96) CHECK (title_2ln IS NULL OR title_2ln != ''),
  url varchar(128) NOT NULL UNIQUE CHECK (url != '' AND url != 'http://'),
  email varchar(64) CHECK (email IS NULL OR email != ''),
  icon varchar(128) CHECK (icon IS NULL OR (icon != '' AND icon != 'http://')),
  addr varchar(128) CHECK (addr IS NULL OR addr != ''),
  tel varchar(48) CHECK (tel IS NULL OR tel != ''),
  fax varchar(32) CHECK (fax IS NULL OR fax != ''),
  dsc varchar(256) NOT NULL CHECK (dsc != ''),
  hid boolean NOT NULL DEFAULT FALSE,
  created timestamp NOT NULL DEFAULT now(),
  createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
  updated timestamp NOT NULL DEFAULT now(),
  updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE
);
GRANT SELECT, INSERT, UPDATE, DELETE ON links TO nobody;

CREATE VIEW links_list AS
  SELECT
    links.url AS _viewurl,
    links.sn AS sn,
    links.title AS title,
    links.title_2ln AS title_2ln,
    links.url AS url,
    links.url AS _urlcheck,
    links.icon AS _imgsrc,
    links.email AS email,
    links.addr AS addr,
    links.tel AS tel,
    links.fax AS fax,
    links.dsc AS dsc,
    CASE WHEN links.hid THEN '隱藏'
      ELSE '秀出'
    END AS hid,
    to_char(links.created, 'YYYY-MM-DD HH:MI:SS') AS created,
    createdby.name AS createdby,
    to_char(links.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
    updatedby.name AS updatedby
  FROM links
    LEFT JOIN users AS createdby ON links.createdby = createdby.sn
    LEFT JOIN users AS updatedby ON links.updatedby = updatedby.sn
  ORDER BY links.title;
GRANT SELECT ON links_list TO nobody;


--
-- Table structure for table "linkcatz"
--

CREATE TABLE linkcatz (
  sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
  cat int NOT NULL REFERENCES linkcat ON UPDATE CASCADE DEFERRABLE,
  link int NOT NULL REFERENCES links ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE,
  created timestamp NOT NULL DEFAULT now(),
  createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
  updated timestamp NOT NULL DEFAULT now(),
  updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
  UNIQUE (cat, link)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON linkcatz TO nobody;

CREATE VIEW linkcatz_list AS
  SELECT
    linkcatz.sn AS sn,
    linkcat_fulltitle(linkcat.parent, linkcat.title) AS cat,
    links.title AS link,
    to_char(linkcatz.created, 'YYYY-MM-DD HH:MI:SS') AS created,
    createdby.name AS createdby,
    to_char(linkcatz.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
    updatedby.name AS updatedby
  FROM linkcatz
    LEFT JOIN linkcat ON linkcatz.cat = linkcat.sn
    LEFT JOIN links ON linkcatz.link = links.sn
    LEFT JOIN users AS createdby ON linkcatz.createdby = createdby.sn
    LEFT JOIN users AS updatedby ON linkcatz.updatedby = updatedby.sn
  ORDER BY linkcat_fullord(linkcat.parent, linkcat.ord), linkcat.id, link;
GRANT SELECT ON linkcatz_list TO nobody;



--
-- Function definitions for table "newslets"
--

-- integer newslet_textno(no integer);
CREATE FUNCTION newslet_textno(no integer) RETURNS text AS '
  BEGIN
    IF no = 1 THEN
      RETURN ''創刊號'';
    ELSE
      RETURN ''第'' || lpad(cast(no AS text), 3, ''0'') || ''期'';
    END IF;
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION newslet_textno(no integer) TO nobody;


--
-- Table structure for table "newslets"
--

CREATE TABLE newslets (
  sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
  no smallint NOT NULL UNIQUE CHECK (no > 0),
  date date NOT NULL DEFAULT CURRENT_DATE,
  title varchar(32) NOT NULL CHECK (title != ''),
  cred_t text NOT NULL CHECK (cred_t != ''),
  cred_h text NOT NULL CHECK (cred_h != ''),
  kw varchar(64) NOT NULL CHECK (kw != ''),
  hid boolean NOT NULL DEFAULT FALSE,
  created timestamp NOT NULL DEFAULT now(),
  createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
  updated timestamp NOT NULL DEFAULT now(),
  updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE
);
GRANT SELECT, INSERT, UPDATE, DELETE ON newslets TO nobody;

CREATE VIEW newslets_list AS
  SELECT
    '/newsletters/wov' || lpad(cast(newslets.no AS text), 4, '0') || '.html'
      AS _viewurl,
    newslets.sn AS sn,
    CASE newslets.no WHEN 1 THEN '創刊號'
      ELSE '第' || lpad(cast(newslets.no AS text), 3, '0') || '期'
    END AS no,
    to_char(newslets.date, 'YYYY-MM-DD') AS date,
    newslets.title AS title,
    newslets.cred_h AS cred_h,
    newslets.cred_t AS cred_t,
    newslets.kw AS kw,
    CASE WHEN newslets.hid THEN '隱藏'
      ELSE '秀出'
    END AS hid,
    to_char(newslets.created, 'YYYY-MM-DD HH:MI:SS') AS created,
    createdby.name AS createdby,
    to_char(newslets.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
    updatedby.name AS updatedby
  FROM newslets
    LEFT JOIN users AS createdby ON newslets.createdby = createdby.sn
    LEFT JOIN users AS updatedby ON newslets.updatedby = updatedby.sn
  ORDER BY newslets.no DESC;
GRANT SELECT ON newslets_list TO nobody;


--
-- Table structure for table "nlarts"
--

CREATE TABLE nlarts (
  sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
  newslet int NOT NULL REFERENCES newslets ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE,
  ord smallint NOT NULL DEFAULT 1 CHECK (ord > 0 AND ord < 100),
  title varchar(32) NOT NULL CHECK (title != ''),
  author varchar(16) NOT NULL CHECK (author != ''),
  body_t text NOT NULL CHECK (body_t != ''),
  body_h text NOT NULL CHECK (body_h != ''),
  hid boolean NOT NULL DEFAULT FALSE,
  created timestamp NOT NULL DEFAULT now(),
  createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
  updated timestamp NOT NULL DEFAULT now(),
  updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
  UNIQUE (newslet, ord)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON nlarts TO nobody;

CREATE VIEW nlarts_list AS
  SELECT
    '/newsletters/wov' || lpad(cast(newslets.no AS text), 4, '0') || '.html'
      AS _viewurl,
    nlarts.sn AS sn,
    CASE newslets.no WHEN 1 THEN '創刊號'
      ELSE '第' || lpad(cast(newslets.no AS text), 3, '0') || '期 '
    END || ' ' || to_char(newslets.date, 'YYYY.MM.DD')
    AS newslet,
    nlarts.ord AS ord,
    nlarts.title AS title,
    nlarts.author AS author,
    nlarts.body_t AS body_t,
    nlarts.body_h AS body_h,
    CASE WHEN nlarts.hid THEN '隱藏'
      ELSE '秀出'
    END AS hid,
    to_char(nlarts.created, 'YYYY-MM-DD HH:MI:SS') AS created,
    createdby.name AS createdby,
    to_char(nlarts.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
    updatedby.name AS updatedby
  FROM nlarts
    LEFT JOIN newslets ON nlarts.newslet = newslets.sn
    LEFT JOIN users AS createdby ON nlarts.createdby = createdby.sn
    LEFT JOIN users AS updatedby ON nlarts.updatedby = updatedby.sn
  ORDER BY newslets.no DESC, nlarts.ord;
GRANT SELECT ON nlarts_list TO nobody;


--
-- VIEW: Search
--
CREATE VIEW search_list AS
    (SELECT
      'pages' AS section,
      pages.path AS path,
      pages.title || ' ' || pages.title_en AS title,
      null AS author,
      to_char(pages.updated, 'YYYY-MM-DD') AS date,
      null AS newsletter,
      null AS nlpath,
      pages.body AS body,
      pages.kw AS kw,
      pages.html AS html,
      1 AS piority
    FROM pages
    WHERE NOT pages.hid
      AND pages.path NOT LIKE '/errors/%')
  UNION
    (SELECT
      'newsletters' AS section,
      '/newsletters/wov' || lpad(cast(newslets.no AS text), 4, '0')
        || '.html#art' || lpad(cast(nlarts.ord AS text), 2, '0')
      AS path,
      nlarts.title AS title,
      nlarts.author AS author,
      to_char(newslets.date, 'YYYY-MM-DD') AS date,
      CASE newslets.no WHEN 1 THEN '創刊號'
        ELSE '第' || lpad(cast(newslets.no AS text), 3, '0') || '期'
      END || ' ' || newslets.title || ' ' || to_char(newslets.date, 'YYYY.MM.DD')
      AS newsletter,
      '/newsletters/wov' || lpad(cast(newslets.no AS text), 4, '0')
        || '.html' AS nlpath,
      nlarts.body_h AS body,
      null AS kw,
      TRUE AS html,
      0 AS piority
    FROM nlarts
      LEFT JOIN newslets ON nlarts.newslet = newslets.sn
    WHERE NOT newslets.hid
      AND NOT nlarts.hid)
  UNION
    (SELECT
      'links' AS section,
      links.url AS path,
      links.title AS title,
      null AS author,
      to_char(links.updated, 'YYYY-MM-DD') AS date,
      null AS newsletter,
      null AS nlpath,
      links.dsc AS body,
      CASE WHEN links.title_2ln IS NULL THEN '' ELSE links.title_2ln END
        || '
' || CASE WHEN links.url IS NULL THEN '' ELSE links.url END
        || '
' || CASE WHEN links.email IS NULL THEN '' ELSE links.email END
        || '
' || CASE WHEN links.addr IS NULL THEN '' ELSE links.addr END
        || '
' || CASE WHEN links.tel IS NULL THEN '' ELSE links.tel END
        || '
' || CASE WHEN links.fax IS NULL THEN '' ELSE links.fax END
      AS kw,
      FALSE AS html,
      1 AS piority
    FROM links
    WHERE NOT links.hid)
  UNION
    (SELECT
      'guestbook' AS section,
      '/cgi-bin/guestbook.cgi?pageno=' || guestbook.pageno
        || '#msg' || guestbook.sn
      AS path,
      null AS title,
      guestbook.name AS author,
      to_char(guestbook.created, 'YYYY-MM-DD') AS date,
      null AS newsletter,
      null AS nlpath,
      guestbook.message AS body,
      CASE WHEN guestbook.identity IS NULL THEN '' ELSE guestbook.identity END
        || '
' || CASE WHEN guestbook.location IS NULL THEN '' ELSE guestbook.location END
        || '
' || CASE WHEN guestbook.email IS NULL THEN '' ELSE guestbook.email END
        || '
' || CASE WHEN guestbook.url IS NULL THEN '' ELSE guestbook.url END
      AS kw,
      FALSE AS html,
      1 AS piority
    FROM guestbook
    WHERE NOT guestbook.hid)
  ORDER BY piority, date DESC, title;
GRANT SELECT ON search_list TO nobody;


--
-- Function definitions for table "accttrx"
--

-- boolean acctsubj_ischild(parent_arg integer, sn_arg integer, code_arg text);
CREATE FUNCTION acctsubj_ischild(parent_arg integer, sn_arg integer, code_arg text) RETURNS boolean AS '
  DECLARE
    row record;
  BEGIN
    -- First level subjects have no parent
    IF char_length(code_arg) = 1 THEN
      IF parent_arg IS NULL THEN
        RETURN TRUE;
      ELSE
        RETURN FALSE;
      END IF;
    END IF;
    -- Second level subjects and below must have a parent
    IF parent_arg IS NULL THEN
      RETURN FALSE;
    END IF;
    -- A subject must not belong to itself
    IF parent_arg IS NOT DISTINCT FROM sn_arg THEN
      RETURN FALSE;
    END IF;
    -- TODO: 2019/3/9 Removed schema "public"?  Added or it will not work on restore.  Not knowing why.
    SELECT INTO row code FROM public.acctsubj WHERE sn=parent_arg;
    -- parent is guarenteed by FOREIGN KEY constraint, so we do not check it.
    -- This helps when importing data from the dump files since we cannot help
    -- the data order in the dump files.
    IF NOT FOUND THEN
      RETURN TRUE;
    END IF;
    -- The code of the parent must match the preceding part of our code
    IF row.code = substring(code_arg FROM 1 FOR char_length(code_arg) - 1) THEN
      RETURN TRUE;
    END IF;
    RETURN FALSE;
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION acctsubj_ischild(parent_arg integer, sn_arg integer, code_arg text) TO nobody;

-- text acctsubj_fulltitle(sn_arg integer);
CREATE FUNCTION acctsubj_fulltitle(sn_arg integer) RETURNS text AS '
  DECLARE
    row record;
    sn_loop integer;
    title_full text;
  BEGIN
    IF sn_arg IS NULL THEN
      RETURN NULL;
    END IF;
    SELECT INTO row * FROM acctsubj WHERE sn=sn_arg;
    IF NOT FOUND THEN
      RETURN NULL;
    END IF;
    title_full := row.title;
    WHILE row.parent IS NOT NULL LOOP
      sn_loop := row.parent;
      SELECT INTO row * FROM acctsubj WHERE sn=sn_loop;
      title_full := row.title || '' / '' || title_full;
    END LOOP;
    RETURN title_full;
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION acctsubj_fulltitle(sn_arg integer) TO nobody;
-- text acctsubj_fulltitle(parent_arg integer, title_arg text);
CREATE FUNCTION acctsubj_fulltitle(parent_arg integer, title_arg text) RETURNS text AS '
  BEGIN
    IF parent_arg IS NULL THEN
      RETURN title_arg;
    END IF;
    RETURN acctsubj_fulltitle(parent_arg) || '' / '' || title_arg;
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION acctsubj_fulltitle(parent_arg integer, title_arg text) TO nobody;
-- text acctsubj_codetitle(sn_arg integer);
CREATE FUNCTION acctsubj_codetitle(sn_arg integer) RETURNS text AS '
  DECLARE
    row record;
  BEGIN
    IF sn_arg IS NULL THEN
      RETURN NULL;
    END IF;
    SELECT INTO row * FROM acctsubj WHERE sn=sn_arg;
    IF NOT FOUND THEN
      RETURN NULL;
    END IF;
    RETURN row.code || '' '' || row.title;
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION acctsubj_codetitle(sn_arg integer) TO nobody;

-- timestamp acctsubj_recent(subj_arg integer);
CREATE FUNCTION acctsubj_recent(subj_arg integer) RETURNS timestamp AS '
  DECLARE
    row record;
  BEGIN
    IF subj_arg IS NULL THEN
      RETURN NULL;
    END IF;
    SELECT INTO row updated FROM acctrecs WHERE subj=subj_arg
      ORDER BY updated DESC LIMIT 1;
    IF NOT FOUND THEN
      RETURN NULL;
    END IF;
    RETURN row.updated;
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION acctsubj_recent(subj_arg integer) TO nobody;

-- boolean acctsubj_islastlv(sn_arg integer);
CREATE FUNCTION acctsubj_islastlv(sn_arg integer) RETURNS boolean AS '
  BEGIN
    PERFORM sn FROM acctsubj WHERE parent=sn_arg LIMIT 1;
    RETURN NOT FOUND;
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION acctsubj_islastlv(sn_arg integer) TO nobody;

-- boolean accttrx_has_subj(sn_arg integer, code_arg text);
CREATE FUNCTION accttrx_has_subj(sn_arg integer, code_arg text) RETURNS boolean AS '
  BEGIN
    PERFORM acctrecs.sn FROM acctrecs
      LEFT JOIN acctsubj ON acctrecs.subj=acctsubj.sn
      WHERE acctrecs.trx=sn_arg
        AND acctsubj.code LIKE code_arg || ''%''
      LIMIT 1;
    RETURN FOUND;
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION accttrx_has_subj(sn_arg integer, code_arg text) TO nobody;

-- integer acctsum_debit(year_arg integer, month_arg integer, code_here text);
CREATE FUNCTION acctsum_debit(year_arg integer, month_arg integer, code_here text) RETURNS integer AS '
  DECLARE
    row record;
  BEGIN
    SELECT INTO row sum(acctrecs.amount) AS sum
      FROM acctrecs
        LEFT JOIN accttrx ON acctrecs.trx = accttrx.sn
        LEFT JOIN acctsubj ON acctrecs.subj = acctsubj.sn
      WHERE NOT acctrecs.credit
        AND acctsubj.code LIKE code_here || ''%''
        AND extract(year FROM accttrx.date) = year_arg
        AND extract(month FROM accttrx.date) = month_arg;
    IF row.sum IS NULL THEN
      RETURN 0;
    END IF;
    RETURN row.sum;
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION acctsum_debit(year_arg integer, month_arg integer, code_here text) TO nobody;
-- integer acctsum_credit(year_arg integer, month_arg integer, code_here text);
CREATE FUNCTION acctsum_credit(year_arg integer, month_arg integer, code_here text) RETURNS integer AS '
  DECLARE
    row record;
  BEGIN
    SELECT INTO row sum(acctrecs.amount) AS sum
      FROM acctrecs
        LEFT JOIN accttrx ON acctrecs.trx = accttrx.sn
        LEFT JOIN acctsubj ON acctrecs.subj = acctsubj.sn
      WHERE acctrecs.credit
        AND acctsubj.code LIKE code_here || ''%''
        AND extract(year FROM accttrx.date) = year_arg
        AND extract(month FROM accttrx.date) = month_arg;
    IF row.sum IS NULL THEN
      RETURN 0;
    END IF;
    RETURN row.sum;
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION acctsum_credit(year_arg integer, month_arg integer, code_here text) TO nobody;
-- integer acctsum_balance(year_arg integer, month_arg integer, code_here text);
CREATE FUNCTION acctsum_balance(year_arg integer, month_arg integer, code_here text) RETURNS integer AS '
  DECLARE
    row record;
  BEGIN
    SELECT INTO row sum(CASE WHEN acctrecs.credit THEN -acctrecs.amount ELSE acctrecs.amount END) AS sum
      FROM acctrecs
        LEFT JOIN accttrx ON acctrecs.trx = accttrx.sn
        LEFT JOIN acctsubj ON acctrecs.subj = acctsubj.sn
      WHERE acctsubj.code LIKE code_here || ''%''
        AND extract(year FROM accttrx.date) * 100 + extract(month FROM accttrx.date) <= year_arg * 100 + month_arg;
    IF row.sum IS NULL THEN
      RETURN 0;
    END IF;
    RETURN row.sum;
  END
' LANGUAGE plpgsql;
GRANT EXECUTE ON FUNCTION acctsum_balance(year_arg integer, month_arg integer, code_here text) TO nobody;


--
-- Table structure for table "acctsubj"
--

CREATE TABLE acctsubj (
  sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
  parent int REFERENCES acctsubj ON UPDATE CASCADE DEFERRABLE CHECK (acctsubj_ischild(parent, sn, code)),
  code varchar(5) NOT NULL UNIQUE CHECK (code != ''),
  title varchar(32) NOT NULL CHECK (title != ''),
  created timestamp NOT NULL DEFAULT now(),
  createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
  updated timestamp NOT NULL DEFAULT now(),
  updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE
);
GRANT SELECT, INSERT, UPDATE, DELETE ON acctsubj TO nobody;

CREATE VIEW acctsubj_list AS
  SELECT
    acctsubj.sn AS sn,
    acctsubj.code AS code,
    acctsubj_fulltitle(acctsubj.parent, acctsubj.title) AS title,
    to_char(acctsubj.created, 'YYYY-MM-DD HH:MI:SS') AS created,
    createdby.name AS createdby,
    to_char(acctsubj.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
    updatedby.name AS updatedby
  FROM acctsubj
    LEFT JOIN users AS createdby ON acctsubj.createdby = createdby.sn
    LEFT JOIN users AS updatedby ON acctsubj.updatedby = updatedby.sn
  ORDER BY acctsubj.code;
GRANT SELECT ON acctsubj_list TO nobody;

CREATE VIEW acctsubj_lastlv_list AS
  SELECT
    acctsubj.sn AS sn,
    acctsubj.code AS code,
    acctsubj_fulltitle(acctsubj.parent, acctsubj.title) AS title,
    to_char(acctsubj.created, 'YYYY-MM-DD HH:MI:SS') AS created,
    createdby.name AS createdby,
    to_char(acctsubj.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
    updatedby.name AS updatedby
  FROM acctsubj
    LEFT JOIN users AS createdby ON acctsubj.createdby = createdby.sn
    LEFT JOIN users AS updatedby ON acctsubj.updatedby = updatedby.sn
  WHERE acctsubj_islastlv(acctsubj.sn)
  ORDER BY acctsubj.code;
GRANT SELECT ON acctsubj_lastlv_list TO nobody;


--
-- Table structure for table "accttrx"
--

CREATE TABLE accttrx (
  sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
  date date NOT NULL DEFAULT CURRENT_DATE,
  ord smallint NOT NULL DEFAULT 1 CHECK (ord > 0 AND ord < 100),
  note varchar(128) CHECK (note IS NULL OR note != ''),
  created timestamp NOT NULL DEFAULT now(),
  createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
  updated timestamp NOT NULL DEFAULT now(),
  updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE
);
GRANT SELECT, INSERT, UPDATE, DELETE ON accttrx TO nobody;

CREATE VIEW accttrx_list AS
  SELECT
    accttrx.sn AS sn,
    to_char(accttrx.date, 'YYYY-MM-DD') AS date,
    to_char(accttrx.date, 'YYYYMMDD') || lpad(cast(accttrx.ord AS text), 2, '0') AS num,
    accttrx.note AS note,
    to_char(accttrx.created, 'YYYY-MM-DD HH:MI:SS') AS created,
    createdby.name AS createdby,
    to_char(accttrx.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
    updatedby.name AS updatedby
  FROM accttrx
    LEFT JOIN users AS createdby ON accttrx.createdby = createdby.sn
    LEFT JOIN users AS updatedby ON accttrx.updatedby = updatedby.sn
  ORDER BY accttrx.date DESC, accttrx.ord DESC;
GRANT SELECT ON accttrx_list TO nobody;


--
-- Table structure for table "acctrecs"
--

CREATE TABLE acctrecs (
  sn int NOT NULL PRIMARY KEY CHECK (sn >= 100000000 AND sn <= 999999999),
  trx int NOT NULL REFERENCES accttrx ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE,
  credit boolean NOT NULL,
  ord smallint NOT NULL DEFAULT 1 CHECK (ord > 0 AND ord < 100),
  subj int NOT NULL REFERENCES acctsubj ON UPDATE CASCADE DEFERRABLE,
  summary varchar(128) CHECK (summary IS NULL OR summary != ''),
  amount int NOT NULL CHECK (amount > 0),
  created timestamp NOT NULL DEFAULT now(),
  createdby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
  updated timestamp NOT NULL DEFAULT now(),
  updatedby int NOT NULL REFERENCES users ON UPDATE CASCADE DEFERRABLE,
  UNIQUE (trx, credit, ord)
);
GRANT SELECT, INSERT, UPDATE, DELETE ON acctrecs TO nobody;

CREATE VIEW acctrecs_list AS
  SELECT
    acctrecs.sn AS sn,
    to_char(accttrx.date, 'YYYY-MM-DD') AS date,
    to_char(accttrx.date, 'YYYYMMDD') || lpad(cast(accttrx.ord AS text), 2, '0') AS trx,
    CASE WHEN credit THEN '貸' ELSE '借' END AS credit,
    acctrecs.ord AS ord,
    acctsubj.code || ' ' || acctsubj.title AS subj,
    acctrecs.summary AS summary,
    acctrecs.amount AS amount,
    to_char(acctrecs.created, 'YYYY-MM-DD HH:MI:SS') AS created,
    createdby.name AS createdby,
    to_char(acctrecs.updated, 'YYYY-MM-DD HH:MI:SS') AS updated,
    updatedby.name AS updatedby
  FROM acctrecs
    LEFT JOIN accttrx ON acctrecs.trx = accttrx.sn
    LEFT JOIN acctsubj ON acctrecs.subj = acctsubj.sn
    LEFT JOIN users AS createdby ON acctrecs.createdby = createdby.sn
    LEFT JOIN users AS updatedby ON acctrecs.updatedby = updatedby.sn
  ORDER BY accttrx.date DESC, accttrx.ord DESC, CASE WHEN credit THEN 2 ELSE 1 END, acctrecs.ord;
GRANT SELECT ON acctrecs_list TO nobody;


--
-- Accounting reports
--
CREATE VIEW acctrep_cash_list AS
  SELECT
    TRUE AS _sel,
    'accttrx.cgi?form=cur&sn=' || accttrx.sn AS _selurl,
    acctsubj.code AS _subj,
    accttrx.date AS _date,
    acctrecs.trx AS _trx,
    to_char(accttrx.date, 'YYYY-MM-DD') AS date,
    acctsubj.code || ' ' || acctsubj.title AS subj,
    acctrecs.summary AS summary,
    CASE WHEN credit THEN acctrecs.amount ELSE 0 END AS income,
    CASE WHEN credit THEN 0 ELSE acctrecs.amount END AS expense,
    0 AS balance
  FROM acctrecs
    LEFT JOIN accttrx ON acctrecs.trx = accttrx.sn
    LEFT JOIN acctsubj ON acctrecs.subj = acctsubj.sn
  ORDER BY accttrx.date, accttrx.ord, CASE WHEN credit THEN 1 ELSE 2 END, acctrecs.ord;
GRANT SELECT ON acctrep_cash_list TO nobody;

CREATE VIEW acctrep_ledger_list AS
  SELECT
    TRUE AS _sel,
    'accttrx.cgi?form=cur&sn=' || accttrx.sn AS _selurl,
    acctsubj.code AS _subj,
    accttrx.date AS _date,
    to_char(accttrx.date, 'YYYY-MM-DD') AS date,
    acctsubj.code || ' ' || acctsubj.title AS subj,
    acctrecs.summary AS summary,
    CASE WHEN credit THEN 0 ELSE acctrecs.amount END AS debit,
    CASE WHEN credit THEN acctrecs.amount ELSE 0 END AS credit,
    0 AS balance
  FROM acctrecs
    LEFT JOIN accttrx ON acctrecs.trx = accttrx.sn
    LEFT JOIN acctsubj ON acctrecs.subj = acctsubj.sn
  ORDER BY accttrx.date, accttrx.ord, CASE WHEN credit THEN 2 ELSE 1 END, acctrecs.ord;
GRANT SELECT ON acctrep_ledger_list TO nobody;

CREATE VIEW acctrep_search_list AS
  SELECT
    'accttrx.cgi?form=cur&sn=' || accttrx.sn AS _selurl,
    to_char(accttrx.date, 'YYYY-MM-DD') AS date,
    to_char(accttrx.date, 'YYYYMMDD') || lpad(cast(accttrx.ord AS text), 2, '0') AS trxno,
    acctsubj.code || ' ' || acctsubj.title AS subj,
    acctrecs.summary AS summary,
    CASE WHEN credit THEN 0 ELSE acctrecs.amount END AS debit,
    CASE WHEN credit THEN acctrecs.amount ELSE 0 END AS credit,
    accttrx.note AS note
  FROM acctrecs
    LEFT JOIN accttrx ON acctrecs.trx = accttrx.sn
    LEFT JOIN acctsubj ON acctrecs.subj = acctsubj.sn
  ORDER BY accttrx.date, accttrx.ord, CASE WHEN credit THEN 2 ELSE 1 END, acctrecs.ord;
GRANT SELECT ON acctrep_search_list TO nobody;


COMMIT;