SET search_path TO import;

-- Easy way to grab the main title of a vn/release

ALTER TABLE vn ADD COLUMN title text;
UPDATE vn SET title = COALESCE(vo.latin, vo.title) FROM vn_titles vo WHERE vo.id = vn.id AND vo.lang = vn.olang;
COMMENT ON COLUMN vn.title IS 'Main entry title, derived from vn_titles';

ALTER TABLE releases ADD COLUMN title text;
UPDATE releases SET title = COALESCE(ro.latin, ro.title) FROM releases_titles ro WHERE ro.id = releases.id AND ro.lang = releases.olang;
COMMENT ON COLUMN releases.title IS 'Main entry title, derived from releases_titles';


-- For compatibility with old queries

CREATE VIEW releases_lang AS SELECT id, lang, mtl FROM releases_titles;
COMMENT ON VIEW releases_lang IS 'Same information as releases_titles, provided for compatibility with older queries.';


-- By rampaa <https://vndb.org/t12800.5>

CREATE OR REPLACE FUNCTION get_child_tags(tag_id vndbid) RETURNS TABLE(tag vndbid) AS $$
WITH RECURSIVE child_tags(tag) AS (
  SELECT tp.id FROM tags_parents tp WHERE tp.parent = tag_id
  UNION ALL
  SELECT tp.id FROM tags_parents tp, child_tags ct WHERE tp.parent = ct.tag GROUP BY tp.id
) SELECT tag FROM child_tags
$$ LANGUAGE sql;

CREATE OR REPLACE FUNCTION get_child_traits(trait_id vndbid) RETURNS TABLE(trait vndbid) AS $$
WITH RECURSIVE child_traits(trait) AS (
  SELECT tp.id FROM traits_parents tp WHERE tp.parent = trait_id
  UNION ALL
  SELECT tp.id FROM traits_parents tp, child_traits ct WHERE tp.parent = ct.trait GROUP BY tp.id
) SELECT trait FROM child_traits
$$ LANGUAGE sql;



-- Based on sql/func.sql:tag_vn_calc()

CREATE MATERIALIZED VIEW tags_vn_inherit (tag, vid, rating, spoiler) AS
    -- Group votes to generate a list of directly-upvoted (vid, tag) pairs.
    -- This is essentually the same as the tag listing on VN pages.
    WITH RECURSIVE t_avg(tag, vid, vote, spoiler) AS (
        SELECT tv.tag, tv.vid, AVG(tv.vote)::real, CASE WHEN COUNT(tv.spoiler) = 0 THEN MIN(t.defaultspoil) ELSE AVG(tv.spoiler)::real END
          FROM tags_vn tv
          JOIN tags t ON t.id = tv.tag
          LEFT JOIN users u ON u.id = tv.uid
         WHERE NOT tv.ignore AND (u.id IS NULL OR u.perm_tag)
         GROUP BY tv.tag, tv.vid
        HAVING AVG(tv.vote) > 0
    -- Add parent tags
    ), t_all(lvl, tag, vid, vote, spoiler) AS (
        SELECT 15, * FROM t_avg
        UNION ALL
        SELECT ta.lvl-1, tp.parent, ta.vid, ta.vote, ta.spoiler
          FROM t_all ta
          JOIN tags_parents tp ON tp.id = ta.tag
         WHERE ta.lvl > 0
    )
    -- Merge
    SELECT tag, vid, AVG(vote)
         , (CASE WHEN MIN(spoiler) > 1.3 THEN 2 WHEN MIN(spoiler) > 0.4 THEN 1 ELSE 0 END)::smallint
      FROM t_all
     WHERE tag IN(SELECT id FROM tags WHERE searchable)
     GROUP BY tag, vid;

CREATE UNIQUE INDEX tags_vn_inherit_tag_vid ON tags_vn_inherit (tag, vid);
COMMENT ON MATERIALIZED VIEW tags_vn_inherit IS 'Aggregated votes from tags_vn, includes parent tags.';