-- A small note on the function naming scheme:
--   edit_*      -> revision insertion abstraction functions
--   *_notify    -> functions issuing a PgSQL NOTIFY statement
--   notify_*    -> functions creating entries in the notifications table
--   user_*      -> functions to manage users and sessions
--   update_*    -> functions to update a cache
--   *_update    ^  (I should probably rename these to
--   *_calc      ^   the update_* scheme for consistency)
-- I like to keep the nouns in functions singular, in contrast to the table
-- naming scheme where nouns are always plural. But I'm not very consistent
-- with that, either.


-- strip_bb_tags(text) - simple utility function to aid full-text searching
CREATE OR REPLACE FUNCTION strip_bb_tags(t text) RETURNS text AS $$
  SELECT regexp_replace(t, '\[(?:url=[^\]]+|/?(?:spoiler|quote|raw|code|url))\]', ' ', 'gi');
$$ LANGUAGE sql IMMUTABLE;

-- Wrapper around to_tsvector() and strip_bb_tags(), implemented in plpgsql and
-- with an associated cost function to make it opaque to the query planner and
-- ensure the query planner realizes that this function is _slow_.
CREATE OR REPLACE FUNCTION bb_tsvector(t text) RETURNS tsvector AS $$
BEGIN
  RETURN to_tsvector('english', strip_bb_tags(t));
END;
$$ LANGUAGE plpgsql IMMUTABLE COST 500;

-- BUG: Since this isn't a full bbcode parser, [spoiler] tags inside [raw] or [code] are still considered spoilers.
CREATE OR REPLACE FUNCTION strip_spoilers(t text) RETURNS text AS $$
  -- The website doesn't require the [spoiler] tag to be closed, the outer replace catches that case.
  SELECT regexp_replace(regexp_replace(t, '\[spoiler\].*?\[/spoiler\]', ' ', 'ig'), '\[spoiler\].*', ' ', 'i');
$$ LANGUAGE sql IMMUTABLE;


-- Assigns a score to the relevance of a substring match, intended for use in
-- an ORDER BY clause. Exact matches are ordered first, prefix matches after
-- that, and finally a normal substring match. Not particularly fast, but
-- that's to be expected of naive substring searches.
-- Pattern must be escaped for use as a LIKE pattern.
CREATE OR REPLACE FUNCTION substr_score(str text, pattern text) RETURNS integer AS $$
SELECT CASE
  WHEN str ILIKE      pattern      THEN 0
  WHEN str ILIKE      pattern||'%' THEN 1
  WHEN str ILIKE '%'||pattern||'%' THEN 2
  ELSE 3
END;
$$ LANGUAGE SQL;


-- update_vncache(id) - updates some c_* columns in the vn table
CREATE OR REPLACE FUNCTION update_vncache(integer) RETURNS void AS $$
  UPDATE vn SET
    c_released = COALESCE((
      SELECT MIN(r.released)
        FROM releases r
        JOIN releases_vn rv ON r.id = rv.id
       WHERE rv.vid = $1
         AND r.type <> 'trial'
         AND r.hidden = FALSE
         AND r.released <> 0
      GROUP BY rv.vid
    ), 0),
    c_olang = ARRAY(
      SELECT lang
        FROM releases_lang
       WHERE id = (
        SELECT r.id
          FROM releases_vn rv
          JOIN releases r ON rv.id = r.id
         WHERE r.released > 0
           AND NOT r.hidden
           AND rv.vid = $1
         ORDER BY r.released
         LIMIT 1
       )
    ),
    c_languages = ARRAY(
      SELECT rl.lang
        FROM releases_lang rl
        JOIN releases r ON r.id = rl.id
        JOIN releases_vn rv ON r.id = rv.id
       WHERE rv.vid = $1
         AND r.type <> 'trial'
         AND r.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer
         AND r.hidden = FALSE
      GROUP BY rl.lang
      ORDER BY rl.lang
    ),
    c_platforms = ARRAY(
      SELECT rp.platform
        FROM releases_platforms rp
        JOIN releases r ON rp.id = r.id
        JOIN releases_vn rv ON rp.id = rv.id
       WHERE rv.vid = $1
        AND r.type <> 'trial'
        AND r.released <= TO_CHAR('today'::timestamp, 'YYYYMMDD')::integer
        AND r.hidden = FALSE
      GROUP BY rp.platform
      ORDER BY rp.platform
    )
  WHERE id = $1;
$$ LANGUAGE sql;



-- recalculate vn.c_popularity
CREATE OR REPLACE FUNCTION update_vnpopularity() RETURNS void AS $$
BEGIN
  -- the following queries only update rows with popularity > 0, so make sure to reset all rows first
  UPDATE vn SET c_popularity = NULL;
  CREATE OR REPLACE TEMP VIEW tmp_pop1 (uid, vid, rank) AS
      SELECT v.uid, v.vid, count(*)::real ^ 0.36788
        FROM votes v
        JOIN votes v2 ON v.uid = v2.uid AND v2.vote < v.vote
        JOIN users u ON u.id = v.uid AND NOT ign_votes
    GROUP BY v.vid, v.uid;
  CREATE OR REPLACE TEMP VIEW tmp_pop2 (vid, win) AS
    SELECT vid, sum(rank) FROM tmp_pop1 GROUP BY vid;
  UPDATE vn SET c_popularity = s1.win/(SELECT MAX(win) FROM tmp_pop2) FROM tmp_pop2 s1 WHERE s1.vid = vn.id;
  RETURN;
END;
$$ LANGUAGE plpgsql;



-- recalculate tags_vn_inherit
CREATE OR REPLACE FUNCTION tag_vn_calc() RETURNS void AS $$
BEGIN
  DROP INDEX IF EXISTS tags_vn_inherit_tag_vid;
  TRUNCATE tags_vn_inherit;
  -- populate tags_vn_inherit
  INSERT INTO tags_vn_inherit
    -- all votes for all tags, including votes inherited by child tags
    -- (also includes meta tags, because they could have a normal tag as parent)
    WITH RECURSIVE tags_vn_all(lvl, tag, vid, uid, vote, spoiler, meta) AS (
        SELECT 15, tag, vid, uid, vote, spoiler, false
        FROM tags_vn
       WHERE NOT ignore
      UNION ALL
        SELECT lvl-1, tp.parent, ta.vid, ta.uid, ta.vote, ta.spoiler, t.meta
        FROM tags_vn_all ta
        JOIN tags_parents tp ON tp.tag = ta.tag
        JOIN tags t ON t.id = tp.parent
        WHERE t.state = 2
          AND ta.lvl > 0
    )
    -- grouped by (tag, vid)
    SELECT tag, vid, COUNT(uid) AS users, AVG(vote)::real AS rating,
           (CASE WHEN AVG(spoiler) > 1.3 THEN 2 WHEN AVG(spoiler) > 0.7 THEN 1 ELSE 0 END)::smallint AS spoiler
    FROM (
      -- grouped by (tag, vid, uid), so only one user votes on one parent tag per VN entry (also removing meta tags)
      SELECT tag, vid, uid, MAX(vote)::real, AVG(spoiler)::real
      FROM tags_vn_all
      WHERE NOT meta
      GROUP BY tag, vid, uid
    ) AS t(tag, vid, uid, vote, spoiler)
    GROUP BY tag, vid
    HAVING AVG(vote) > 0;
  -- recreate index
  CREATE INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid);
  -- and update the VN count in the tags table
  UPDATE tags SET c_items = (SELECT COUNT(*) FROM tags_vn_inherit WHERE tag = id);
  RETURN;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;



-- recalculate traits_chars
CREATE OR REPLACE FUNCTION traits_chars_calc() RETURNS void AS $$
BEGIN
  TRUNCATE traits_chars;
  INSERT INTO traits_chars (tid, cid, spoil)
    -- all char<->trait links of the latest revisions, including chars inherited from child traits
    -- (also includes meta traits, because they could have a normal trait as parent)
    WITH RECURSIVE traits_chars_all(lvl, tid, cid, spoiler, meta) AS (
        SELECT 15, tid, ct.id, spoil, false
        FROM chars_traits ct
        JOIN chars c ON c.id = ct.id
       WHERE NOT c.hidden
      UNION ALL
        SELECT lvl-1, tp.parent, tc.cid, tc.spoiler, t.meta
        FROM traits_chars_all tc
        JOIN traits_parents tp ON tp.trait = tc.tid
        JOIN traits t ON t.id = tp.parent
        WHERE t.state = 2
          AND tc.lvl > 0
    )
    -- now grouped by (tid, cid) and with meta traits filtered out
    SELECT tid, cid, (CASE WHEN AVG(spoiler) > 1.3 THEN 2 WHEN AVG(spoiler) > 0.7 THEN 1 ELSE 0 END)::smallint AS spoiler
    FROM traits_chars_all
    WHERE NOT meta
    GROUP BY tid, cid;
  -- and update the VN count in the tags table
  UPDATE traits SET c_items = (SELECT COUNT(*) FROM traits_chars WHERE tid = id);
  RETURN;
END;
$$ LANGUAGE plpgsql;






----------------------------------------------------------
--           revision insertion abstraction             --
----------------------------------------------------------

-- The two functions below are utility functions used by the item-specific functions in editfunc.sql

-- create temporary table for generic revision info, and returns the chid of the revision being edited (or NULL).
CREATE OR REPLACE FUNCTION edit_revtable(xtype dbentry_type, xitemid integer, xrev integer) RETURNS integer AS $$
DECLARE
  ret integer;
  x record;
BEGIN
  BEGIN
    CREATE TEMPORARY TABLE edit_revision (
      type dbentry_type NOT NULL,
      itemid integer,
      requester integer,
      ip inet,
      comments text,
      ihid boolean,
      ilock boolean
    );
  EXCEPTION WHEN duplicate_table THEN
    TRUNCATE edit_revision;
  END;
  SELECT INTO x id, ihid, ilock FROM changes c WHERE type = xtype AND itemid = xitemid AND rev = xrev;
  INSERT INTO edit_revision (type, itemid, ihid, ilock) VALUES (xtype, xitemid, COALESCE(x.ihid, FALSE), COALESCE(x.ilock, FALSE));
  RETURN x.id;
END;
$$ LANGUAGE plpgsql;



CREATE OR REPLACE FUNCTION edit_commit() RETURNS edit_rettype AS $$
DECLARE
  ret edit_rettype;
  xtype dbentry_type;
BEGIN
  SELECT type INTO xtype FROM edit_revision;
  SELECT itemid INTO ret.itemid FROM edit_revision;
  -- figure out revision number
  SELECT MAX(rev)+1 INTO ret.rev FROM changes WHERE type = xtype AND itemid = ret.itemid;
  SELECT COALESCE(ret.rev, 1) INTO ret.rev;
  -- insert DB item
  IF ret.itemid IS NULL THEN
    CASE xtype
      WHEN 'v' THEN INSERT INTO vn        DEFAULT VALUES RETURNING id INTO ret.itemid;
      WHEN 'r' THEN INSERT INTO releases  DEFAULT VALUES RETURNING id INTO ret.itemid;
      WHEN 'p' THEN INSERT INTO producers DEFAULT VALUES RETURNING id INTO ret.itemid;
      WHEN 'c' THEN INSERT INTO chars     DEFAULT VALUES RETURNING id INTO ret.itemid;
      WHEN 's' THEN INSERT INTO staff     DEFAULT VALUES RETURNING id INTO ret.itemid;
    END CASE;
  END IF;
  -- insert change
  INSERT INTO changes (type, itemid, rev, requester, ip, comments, ihid, ilock)
    SELECT type, ret.itemid, ret.rev, requester, ip, comments, ihid, ilock FROM edit_revision RETURNING id INTO ret.chid;
  RETURN ret;
END;
$$ LANGUAGE plpgsql;



-- Check for stuff to be done when an item has been changed
CREATE OR REPLACE FUNCTION edit_committed(xtype dbentry_type, xedit edit_rettype) RETURNS void AS $$
DECLARE
  xoldchid integer;
BEGIN
  SELECT id INTO xoldchid FROM changes WHERE type = xtype AND itemid = xedit.itemid AND rev = xedit.rev-1;

  -- Set producers.rgraph to NULL and notify when:
  -- 1. There's a new producer entry with some relations
  -- 2. The producer name/type/language has changed
  -- 3. The producer relations have been changed
  IF xtype = 'p' THEN
    IF -- 1.
         (xoldchid IS NULL AND EXISTS(SELECT 1 FROM producers_relations_hist WHERE chid = xedit.chid))
      OR (xoldchid IS NOT NULL AND (
        -- 2.
           EXISTS(SELECT 1 FROM producers_hist p1, producers_hist p2 WHERE (p2.name <> p1.name OR p2.type <> p1.type OR p2.lang <> p1.lang) AND p1.chid = xoldchid AND p2.chid = xedit.chid)
        -- 3.
        OR EXISTS(SELECT pid, relation FROM producers_relations_hist WHERE chid = xoldchid   EXCEPT SELECT pid, relation FROM producers_relations_hist WHERE chid = xedit.chid)
        OR EXISTS(SELECT pid, relation FROM producers_relations_hist WHERE chid = xedit.chid EXCEPT SELECT pid, relation FROM producers_relations_hist WHERE chid = xoldchid)
      ))
    THEN
      UPDATE producers SET rgraph = NULL WHERE id = xedit.itemid;
      NOTIFY relgraph; -- This notify is not done by the producer_relgraph_notify trigger for new entries or if rgraph was already NULL
    END IF;
  END IF;

  -- Set vn.rgraph to NULL and notify when:
  -- 1. There's a new vn entry with some relations
  -- 2. The vn title has changed
  -- 3. The vn relations have been changed
  IF xtype = 'v' THEN
    IF -- 1.
         (xoldchid IS NULL AND EXISTS(SELECT 1 FROM vn_relations_hist WHERE chid = xedit.chid))
      OR (xoldchid IS NOT NULL AND (
        -- 2.
           EXISTS(SELECT 1 FROM vn_hist v1, vn_hist v2 WHERE v2.title <> v1.title AND v1.chid = xoldchid AND v2.chid = xedit.chid)
        -- 3.
        OR EXISTS(SELECT vid, relation, official FROM vn_relations_hist WHERE chid = xoldchid   EXCEPT SELECT vid, relation, official FROM vn_relations_hist WHERE chid = xedit.chid)
        OR EXISTS(SELECT vid, relation, official FROM vn_relations_hist WHERE chid = xedit.chid EXCEPT SELECT vid, relation, official FROM vn_relations_hist WHERE chid = xoldchid)
      ))
    THEN
      UPDATE vn SET rgraph = NULL WHERE id = xedit.itemid;
      NOTIFY relgraph;
    END IF;
  END IF;

  -- Set c_search to NULL and notify when
  -- 1. A new VN entry is created
  -- 2. The vn title/original/alias has changed
  IF xtype = 'v' THEN
    IF -- 1.
       xoldchid IS NULL OR
       -- 2.
       EXISTS(SELECT 1 FROM vn_hist v1, vn_hist v2 WHERE (v2.title <> v1.title OR v2.original <> v1.original OR v2.alias <> v1.alias) AND v1.chid = xoldchid AND v2.chid = xedit.chid)
    THEN
      UPDATE vn SET c_search = NULL WHERE id = xedit.itemid;
      NOTIFY vnsearch;
    END IF;
  END IF;

  -- Set related vn.c_search columns to NULL and notify when
  -- 1. A new release is created
  -- 2. A release has been hidden or unhidden
  -- 3. The release title/original has changed
  -- 4. The releases_vn table differs from a previous revision
  IF xtype = 'r' THEN
    IF -- 1.
       xoldchid IS NULL OR
       -- 2.
       EXISTS(SELECT 1 FROM changes c1, changes c2 WHERE c1.ihid IS DISTINCT FROM c2.ihid AND c1.id = xedit.chid AND c2.id = xoldchid) OR
       -- 3.
       EXISTS(SELECT 1 FROM releases_hist r1, releases_hist r2 WHERE (r2.title <> r1.title OR r2.original <> r1.original) AND r1.chid = xoldchid AND r2.chid = xedit.chid) OR
       -- 4.
       EXISTS(SELECT vid FROM releases_vn_hist WHERE chid = xoldchid   EXCEPT SELECT vid FROM releases_vn_hist WHERE chid = xedit.chid) OR
       EXISTS(SELECT vid FROM releases_vn_hist WHERE chid = xedit.chid EXCEPT SELECT vid FROM releases_vn_hist WHERE chid = xoldchid)
    THEN
      UPDATE vn SET c_search = NULL WHERE id IN(SELECT vid FROM releases_vn_hist WHERE chid IN(xedit.chid, xoldchid));
      NOTIFY vnsearch;
    END IF;
  END IF;

  -- Call update_vncache() for related VNs when a release has been created or edited
  -- (This could be made more specific, but update_vncache() is fast enough that it's not worth the complexity)
  IF xtype = 'r' THEN
    PERFORM update_vncache(vid) FROM (
      SELECT DISTINCT vid FROM releases_vn_hist WHERE chid IN(xedit.chid, xoldchid)
    ) AS v(vid);
  END IF;

  -- Call notify_dbdel() if an entry has been deleted
  -- Call notify_listdel() if a vn/release entry has been deleted
  IF xoldchid IS NOT NULL
     AND EXISTS(SELECT 1 FROM changes WHERE id = xoldchid AND NOT ihid)
     AND EXISTS(SELECT 1 FROM changes WHERE id = xedit.chid AND ihid)
  THEN
    PERFORM notify_dbdel(xtype, xedit);
    IF xtype = 'v' OR xtype = 'r' THEN
      PERFORM notify_listdel(xtype, xedit);
    END IF;
  END IF;

  -- Call notify_dbedit() if a non-hidden entry has been edited
  IF xoldchid IS NOT NULL AND EXISTS(SELECT 1 FROM changes WHERE id = xedit.chid AND NOT ihid)
  THEN
    PERFORM notify_dbedit(xtype, xedit);
  END IF;
END;
$$ LANGUAGE plpgsql;



----------------------------------------------------------
--                  trigger functions                   --
----------------------------------------------------------


-- keep the c_* columns in the users table up to date
CREATE OR REPLACE FUNCTION update_users_cache() RETURNS TRIGGER AS $$
BEGIN
  IF TG_TABLE_NAME = 'votes' THEN
    IF TG_OP = 'INSERT' THEN
      UPDATE users SET c_votes = c_votes + 1 WHERE id = NEW.uid;
    ELSE
      UPDATE users SET c_votes = c_votes - 1 WHERE id = OLD.uid;
    END IF;
  ELSIF TG_TABLE_NAME = 'changes' THEN
    IF TG_OP = 'INSERT' THEN
      UPDATE users SET c_changes = c_changes + 1 WHERE id = NEW.requester;
    ELSE
      UPDATE users SET c_changes = c_changes - 1 WHERE id = OLD.requester;
    END IF;
  ELSIF TG_TABLE_NAME = 'tags_vn' THEN
    IF TG_OP = 'INSERT' THEN
      UPDATE users SET c_tags = c_tags + 1 WHERE id = NEW.uid;
    ELSE
      UPDATE users SET c_tags = c_tags - 1 WHERE id = OLD.uid;
    END IF;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';



-- the stats_cache table
CREATE OR REPLACE FUNCTION update_stats_cache() RETURNS TRIGGER AS $$
DECLARE
  unhidden boolean;
  hidden boolean;
BEGIN
  IF TG_OP = 'INSERT' THEN
    IF TG_TABLE_NAME = 'users' THEN
      UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
    ELSE
      IF TG_TABLE_NAME = 'threads_posts' THEN
        IF EXISTS(SELECT 1 FROM threads WHERE id = NEW.tid AND threads.hidden = FALSE) THEN
          UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
        END IF;
      ELSE
        UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
      END IF;
    END IF;

  ELSIF TG_OP = 'UPDATE' THEN
    IF TG_TABLE_NAME IN('tags', 'traits') THEN
      unhidden := OLD.state <> 2 AND NEW.state = 2;
      hidden := OLD.state = 2 AND NEW.state <> 2;
    ELSE
      unhidden := OLD.hidden AND NOT NEW.hidden;
      hidden := NOT unhidden;
    END IF;
    IF unhidden THEN
      IF TG_TABLE_NAME = 'threads' THEN
        UPDATE stats_cache SET count = count+NEW.count WHERE section = 'threads_posts';
      END IF;
      UPDATE stats_cache SET count = count+1 WHERE section = TG_TABLE_NAME;
    ELSIF hidden THEN
      IF TG_TABLE_NAME = 'threads' THEN
        UPDATE stats_cache SET count = count-NEW.count WHERE section = 'threads_posts';
      END IF;
      UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME;
    END IF;

  ELSIF TG_OP = 'DELETE' AND TG_TABLE_NAME = 'users' THEN
    UPDATE stats_cache SET count = count-1 WHERE section = TG_TABLE_NAME;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE 'plpgsql';



-- insert rows into anime for new vn_anime.aid items
-- (this is a BEFORE trigger)
CREATE OR REPLACE FUNCTION vn_anime_aid() RETURNS trigger AS $$
BEGIN
  IF NOT EXISTS(SELECT 1 FROM anime WHERE id = NEW.aid) THEN
    INSERT INTO anime (id) VALUES (NEW.aid);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;



-- For each row in rlists, there should be at least one corresponding row in
-- vnlists for at least one of the VNs linked to that release.
-- 1. When a row is deleted from vnlists, also remove all rows from rlists that
--    would otherwise not have a corresponding row in vnlists
-- 2. When a row is inserted to rlists and there is not yet a corresponding row
--    in vnlists, add a row in vnlists (with status=unknown) for each vn linked
--    to the release.
CREATE OR REPLACE FUNCTION update_vnlist_rlist() RETURNS trigger AS $$
BEGIN
  -- 1.
  IF TG_TABLE_NAME = 'vnlists' THEN
    DELETE FROM rlists WHERE uid = OLD.uid AND rid IN(SELECT rv.id
      -- fetch all related rows in rlists
      FROM releases_vn rv
      JOIN rlists rl ON rl.rid = rv.id
     WHERE rv.vid = OLD.vid AND rl.uid = OLD.uid
       -- and test for a corresponding row in vnlists
       AND NOT EXISTS(
        SELECT 1
          FROM releases_vn rvi
          JOIN vnlists vl ON vl.vid = rvi.vid AND uid = OLD.uid
         WHERE rvi.id = rv.id
       ));

  -- 2.
  ELSE
   INSERT INTO vnlists (uid, vid) SELECT NEW.uid, rv.vid
     -- all VNs linked to the release
      FROM releases_vn rv
     WHERE rv.id = NEW.rid
       -- but only if there are no corresponding rows in vnlists yet
       AND NOT EXISTS(
        SELECT 1
          FROM releases_vn rvi
          JOIN vnlists vl ON vl.vid = rvi.vid
         WHERE rvi.id = NEW.rid AND vl.uid = NEW.uid
       );
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;



-- Send a notify whenever anime info should be fetched
CREATE OR REPLACE FUNCTION anime_fetch_notify() RETURNS trigger AS $$
  BEGIN NOTIFY anime; RETURN NULL; END;
$$ LANGUAGE plpgsql;



-- 1. Send a notify when vn.rgraph is set to NULL, and there are related entries in vn_relations
-- 2. Set rgraph to NULL when c_languages or c_released has changed
CREATE OR REPLACE FUNCTION vn_relgraph_notify() RETURNS trigger AS $$
BEGIN
  IF EXISTS(SELECT 1 FROM vn_relations WHERE id = NEW.id) THEN
    -- 1.
    IF NEW.rgraph IS NULL THEN
      NOTIFY relgraph;
    -- 2.
    ELSE
      UPDATE vn SET rgraph = NULL WHERE id = NEW.id;
    END IF;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;


-- Send a notify when producers.rgraph is set to NULL, and there are related entries in producers_relations
CREATE OR REPLACE FUNCTION producer_relgraph_notify() RETURNS trigger AS $$
BEGIN
  IF EXISTS(SELECT 1 FROM producers_relations WHERE id = NEW.id) THEN
    NOTIFY relgraph;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;



-- NOTIFY on insert into changes/posts/tags/trait
CREATE OR REPLACE FUNCTION insert_notify() RETURNS trigger AS $$
BEGIN
  IF TG_TABLE_NAME = 'changes' THEN
    NOTIFY newrevision;
  ELSIF TG_TABLE_NAME = 'threads_posts' THEN
    NOTIFY newpost;
  ELSIF TG_TABLE_NAME = 'tags' THEN
    NOTIFY newtag;
  ELSIF TG_TABLE_NAME = 'traits' THEN
    NOTIFY newtrait;
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;



-- Send a vnsearch notification when the c_search column is set to NULL.
CREATE OR REPLACE FUNCTION vn_vnsearch_notify() RETURNS trigger AS $$
  BEGIN NOTIFY vnsearch; RETURN NULL; END;
$$ LANGUAGE plpgsql;




----------------------------------------------------------
--                notification functions                --
----------------------------------------------------------


-- called on INSERT INTO threads_posts
CREATE OR REPLACE FUNCTION notify_pm() RETURNS trigger AS $$
BEGIN
  INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
    SELECT 'pm', 't', tb.iid, t.id, NEW.num, t.title, NEw.uid
      FROM threads t
      JOIN threads_boards tb ON tb.tid = t.id
     WHERE t.id = NEW.tid
       AND tb.type = 'u'
       AND tb.iid <> NEW.uid -- don't notify when posting in your own board
       AND NOT EXISTS( -- don't notify when you haven't read an earlier post in the thread yet
         SELECT 1
           FROM notifications n
          WHERE n.uid = tb.iid
            AND n.ntype = 'pm'
            AND n.iid = t.id
            AND n.read IS NULL
       );
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;



-- called when an entry has been deleted
CREATE OR REPLACE FUNCTION notify_dbdel(xtype dbentry_type, xedit edit_rettype) RETURNS void AS $$
  INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
    SELECT DISTINCT 'dbdel'::notification_ntype, xtype::text::notification_ltype, h.requester, xedit.itemid, xedit.rev, x.title, h2.requester
      FROM changes h
      -- join info about the deletion itself
      JOIN changes h2 ON h2.id = xedit.chid
      -- Fetch the latest name/title of the entry
      -- this method may look a bit unintuitive, but it's way faster than doing LEFT JOINs
      JOIN (  SELECT v.title FROM vn v WHERE xtype = 'v' AND v.id = xedit.itemid
        UNION SELECT r.title FROM releases r WHERE xtype = 'r' AND r.id = xedit.itemid
        UNION SELECT p.name  FROM producers p WHERE xtype = 'p' AND p.id = xedit.itemid
        UNION SELECT c.name  FROM chars c WHERE xtype = 'c' AND c.id = xedit.itemid
        UNION SELECT sa.name FROM staff s JOIN staff_alias sa ON sa.aid = s.aid WHERE xtype = 's' AND s.id = xedit.itemid
      ) x(title) ON true
     WHERE h.type = xtype AND h.itemid = xedit.itemid
       AND h.requester <> 1 -- exclude Multi
       AND h.requester <> h2.requester; -- exclude the user who deleted the entry
$$ LANGUAGE sql;



-- Called when a non-deleted item has been edited.
CREATE OR REPLACE FUNCTION notify_dbedit(xtype dbentry_type, xedit edit_rettype) RETURNS void AS $$
  INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
    SELECT DISTINCT 'dbedit'::notification_ntype, xtype::text::notification_ltype, h.requester, xedit.itemid, xedit.rev, x.title, h2.requester
      FROM changes h
      -- join info about the edit itself
      JOIN changes h2 ON h2.id = xedit.chid
      -- Fetch the latest name/title of the entry
      JOIN (  SELECT v.title FROM vn v WHERE xtype = 'v' AND v.id = xedit.itemid
        UNION SELECT r.title FROM releases r WHERE xtype = 'r' AND r.id = xedit.itemid
        UNION SELECT p.name  FROM producers p WHERE xtype = 'p' AND p.id = xedit.itemid
        UNION SELECT c.name  FROM chars c WHERE xtype = 'c' AND c.id = xedit.itemid
        UNION SELECT sa.name FROM staff s JOIN staff_alias sa ON sa.aid = s.aid WHERE xtype = 's' AND s.id = xedit.itemid
      ) x(title) ON true
     WHERE h.type = xtype AND h.itemid = xedit.itemid
       AND h.requester <> h2.requester -- exclude the user who edited the entry
       -- exclude users who don't want this notify
       AND NOT EXISTS(SELECT 1 FROM users_prefs up WHERE uid = h.requester AND key = 'notify_nodbedit');
$$ LANGUAGE sql;



-- called when a VN/release entry has been deleted
CREATE OR REPLACE FUNCTION notify_listdel(xtype dbentry_type, xedit edit_rettype) RETURNS void AS $$
  INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
    SELECT DISTINCT 'listdel'::notification_ntype, xtype::text::notification_ltype, u.uid, xedit.itemid, xedit.rev, x.title, c.requester
      -- look for users who should get this notify
      FROM (
              SELECT uid FROM votes   WHERE xtype = 'v' AND vid = xedit.itemid
        UNION SELECT uid FROM vnlists WHERE xtype = 'v' AND vid = xedit.itemid
        UNION SELECT uid FROM wlists  WHERE xtype = 'v' AND vid = xedit.itemid
        UNION SELECT uid FROM rlists  WHERE xtype = 'r' AND rid = xedit.itemid
      ) u
      -- fetch info about this edit
      JOIN changes c ON c.id = xedit.chid
      JOIN (
              SELECT title FROM vn       WHERE xtype = 'v' AND id = xedit.itemid
        UNION SELECT title FROM releases WHERE xtype = 'r' AND id = xedit.itemid
      ) x ON true
     WHERE c.requester <> u.uid;
$$ LANGUAGE sql;


-- called on INSERT INTO threads_posts when (NEW.num = 1)
CREATE OR REPLACE FUNCTION notify_announce() RETURNS trigger AS $$
BEGIN
  INSERT INTO notifications (ntype, ltype, uid, iid, subid, c_title, c_byuser)
    SELECT 'announce', 't', up.uid, t.id, 1, t.title, NEw.uid
      FROM threads t
      JOIN threads_boards tb ON tb.tid = t.id
      -- get the users who want this announcement
      JOIN users_prefs up ON up.key = 'notify_announce'
     WHERE t.id = NEW.tid
       AND tb.type = 'an' -- announcement board
       AND NOT t.hidden;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;




----------------------------------------------------------
--                    user management                   --
----------------------------------------------------------
-- XXX: These functions run with the permissions of the 'vndb' user.


-- Returns the raw scrypt parameters (N, r, p and salt) for this user, in order
-- to create an encrypted pass. Returns NULL if this user does not have a valid
-- password.
CREATE OR REPLACE FUNCTION user_getscryptargs(integer) RETURNS bytea AS $$
  SELECT
    CASE WHEN length(passwd) = 46 THEN substring(passwd from 1 for 14) ELSE NULL END
  FROM users WHERE id = $1
$$ LANGUAGE SQL SECURITY DEFINER;


-- Create a new session for this user (uid, scryptpass, token)
CREATE OR REPLACE FUNCTION user_login(integer, bytea, bytea) RETURNS boolean AS $$
  INSERT INTO sessions (uid, token) SELECT $1, $3 FROM users
   WHERE length($2) = 46 AND length($3) = 20
     AND id = $1 AND passwd = $2
  RETURNING true
$$ LANGUAGE SQL SECURITY DEFINER;


CREATE OR REPLACE FUNCTION user_logout(integer, bytea) RETURNS void AS $$
  DELETE FROM sessions WHERE uid = $1 AND token = $2
$$ LANGUAGE SQL SECURITY DEFINER;


CREATE OR REPLACE FUNCTION user_update_lastused(integer, bytea) RETURNS void AS $$
  UPDATE sessions SET lastused = NOW() WHERE uid = $1 AND token = $2
$$ LANGUAGE SQL SECURITY DEFINER;


CREATE OR REPLACE FUNCTION user_isloggedin(integer, bytea) RETURNS timestamptz AS $$
  SELECT lastused FROM sessions WHERE uid = $1 AND token = $2
$$ LANGUAGE SQL SECURITY DEFINER;


CREATE OR REPLACE FUNCTION user_emailexists(text) RETURNS boolean AS $$
  SELECT true FROM users WHERE lower(mail) = lower($1) LIMIT 1
$$ LANGUAGE SQL SECURITY DEFINER;


CREATE OR REPLACE FUNCTION user_isvalidtoken(integer, bytea) RETURNS boolean AS $$
  SELECT true FROM users WHERE id = $1 AND passwd = $2 AND length($2) = 20
$$ LANGUAGE SQL SECURITY DEFINER;


-- Replace password with a reset token. args: email, token. Returns: user id.
-- Doesn't work for usermods, otherwise an attacker could use this function to
-- gain access to all user's emails by obtaining a reset token of a usermod.
-- Ideally Postgres itself would send the user an email so that the application
-- calling this function doesn't even get the token, and thus can't get access
-- to someone's account. But alas, that'd require a separate process.
CREATE OR REPLACE FUNCTION user_resetpass(text, bytea) RETURNS integer AS $$
  UPDATE users SET passwd = $2 WHERE lower(mail) = lower($1) AND length($2) = 20 AND perm & 128 = 0 RETURNING id;
$$ LANGUAGE SQL SECURITY DEFINER;


-- Changes the user's password and invalidates all existing sessions. args: uid, old_pass_or_token, new_pass
CREATE OR REPLACE FUNCTION user_setpass(integer, bytea, bytea) RETURNS boolean AS $$
  WITH upd(id) AS (
    UPDATE users SET passwd = $3 WHERE id = $1 AND passwd = $2 AND length($2) IN(20,46) AND length($3) = 46 RETURNING id
  ), del AS( -- Not referenced, but still guaranteed to run
    DELETE FROM sessions WHERE uid IN(SELECT id FROM upd)
  )
  SELECT true FROM upd
$$ LANGUAGE SQL SECURITY DEFINER;


-- Internal function, used to verify whether user ($2 with session $3) is
-- allowed to access data from user $1.
CREATE OR REPLACE FUNCTION user_isauth(integer, integer, bytea) RETURNS boolean AS $$
  SELECT true FROM users
   WHERE id = $2
     AND EXISTS(SELECT 1 FROM sessions WHERE uid = $2 AND token = $3)
     AND ($2 = $1 OR perm & 128 = 128)
$$ LANGUAGE SQL;


-- uid of user email to get, uid currently logged in, session token of currently logged in.
-- Ensures that only the user itself or a useradmin can get someone's email address.
CREATE OR REPLACE FUNCTION user_getmail(integer, integer, bytea) RETURNS text AS $$
  SELECT mail FROM users WHERE id = $1 AND user_isauth($1, $2, $3)
$$ LANGUAGE SQL SECURITY DEFINER;


CREATE OR REPLACE FUNCTION user_setmail(integer, integer, bytea, text) RETURNS void AS $$
  UPDATE users SET mail = $4 WHERE id = $1 AND user_isauth($1, $2, $3)
$$ LANGUAGE SQL SECURITY DEFINER;


CREATE OR REPLACE FUNCTION user_setperm(integer, integer, bytea, integer) RETURNS void AS $$
  UPDATE users SET perm = $4 WHERE id = $1 AND user_isauth(-1, $2, $3)
$$ LANGUAGE SQL SECURITY DEFINER;


CREATE OR REPLACE FUNCTION user_admin_setpass(integer, integer, bytea, bytea) RETURNS void AS $$
  WITH upd(id) AS (
    UPDATE users SET passwd = $4 WHERE id = $1 AND user_isauth(-1, $2, $3) AND length($4) = 46 RETURNING id
  )
  DELETE FROM sessions WHERE uid IN(SELECT id FROM upd)
$$ LANGUAGE SQL SECURITY DEFINER;
