DROP FUNCTION discipline_concat(text, text);
DROP FUNCTION cardtype_concat(text, text);
DROP FUNCTION set_concat(text, text);

DROP FUNCTION discipline_fix(text);
DROP FUNCTION cardtype_fix(text);
DROP FUNCTION set_fix(text);

DROP AGGREGATE cardtype_string text;
DROP AGGREGATE set_string text;
DROP AGGREGATE discipline_string text;

DROP FUNCTION cardname_string(text, text);
DROP FUNCTION cost_string(text, boolean);
DROP FUNCTION column_to_upper(text, boolean);

DROP VIEW crypt;
DROP VIEW library;

CREATE FUNCTION cardtype_concat(text, text)
  RETURNS text
  AS
    'SELECT $1 || \', \' || $2 AS RESULT'
  LANGUAGE 'sql';

CREATE FUNCTION cardtype_fix(text)
  RETURNS text
  AS
    'SELECT substring($1 from 2 for char_length($1)) AS RESULT'
  LANGUAGE 'sql';

CREATE FUNCTION discipline_concat(text, text)
  RETURNS text
  AS
    'SELECT $1 || \', \' || $2 AS RESULT'
  LANGUAGE 'sql';

CREATE FUNCTION discipline_fix(text)
  RETURNS text
  AS
    'SELECT substring($1 from 3 for char_length($1)) AS RESULT'
  LANGUAGE 'sql';

CREATE FUNCTION set_concat(text, text)
  RETURNS text
  AS
    'SELECT $1 || \', \' || $2 AS RESULT'
  LANGUAGE 'sql';

CREATE FUNCTION set_fix(text)
  RETURNS text
  AS
    'SELECT substring($1 from 3 for char_length($1)) AS RESULT'
  LANGUAGE 'sql';

CREATE AGGREGATE discipline_string
(
  SFUNC = discipline_concat,
  BASETYPE = text,
  STYPE = text,
  FINALFUNC = discipline_fix,
  INITCOND = ''
);

CREATE AGGREGATE cardtype_string
(
  SFUNC = cardtype_concat,
  BASETYPE = text,
  STYPE = text,
  FINALFUNC = cardtype_fix,
  INITCOND = ''
);

CREATE AGGREGATE set_string
(
  SFUNC = set_concat,
  BASETYPE = text,
  STYPE = text,
  FINALFUNC = set_fix,
  INITCOND = ''
);

CREATE FUNCTION column_to_upper(text, boolean)
  RETURNS text
  AS
    'DECLARE
      discipline ALIAS FOR $1;
      superior ALIAS FOR $2;
    BEGIN
      IF superior THEN
        RETURN upper(discipline);
      END IF;

      RETURN discipline;
    END;'
  LANGUAGE 'plpgsql';

CREATE FUNCTION cost_string(text, boolean)
  RETURNS text
  AS
    'DECLARE
      cost ALIAS FOR $1;
      pool ALIAS FOR $2;
    BEGIN
      IF pool THEN
        RETURN cost || '' pool'';
      END IF;

      RETURN cost || '' blood'';
    END;'
  LANGUAGE 'plpgsql';

CREATE FUNCTION cardname_string(text, text)
  RETURNS text
  AS
    'DECLARE
      cardname ALIAS FOR $1;
      alternate ALIAS FOR $2;
    BEGIN
      IF alternate ISNULL THEN
        RETURN cardname;
      END IF;

      RETURN cardname || ''/'' || alternate;
    END;'
  LANGUAGE 'plpgsql';

CREATE VIEW crypt
AS
  SELECT 
         t_cards.card_id AS CardID,
         cardname_string(c.cardname, cards_names.cardname) AS Cardname,
         t_cardtypes.cardtype AS Cardtype,
         t_sets.set AS Set,
         t_sects.sect AS Sect,
         t_clans.clan AS Clan,
         cards_costs.cost AS Cost,
         cards_capacities.capacity AS Capacity,
         t_titles.title AS Title,
         t_disciplines.discipline AS Discipline,
         cards_cardtexts.cardtext AS Cardtext
  FROM (SELECT card_id 
        FROM cards_cardtypes 
        WHERE cardtype_id = (SELECT cardtype_id
                             FROM cardtypes
                             WHERE cardtype = 'Vampire')) AS t_cards
    INNER JOIN
    (
      SELECT ct.card_id, c.cardtype
      FROM cards_cardtypes ct
        INNER JOIN cardtypes c USING (cardtype_id)
      WHERE c.cardtype_id = ct.cardtype_id
    ) AS t_cardtypes USING (card_id)
    INNER JOIN cards c USING (card_id)
    LEFT JOIN cards_names USING (card_id)
    INNER JOIN cards_cardtexts USING (card_id)
    INNER JOIN cards_capacities USING (card_id)
    LEFT JOIN cards_costs USING (card_id)
    LEFT JOIN
    (
      SELECT card_id, discipline_string(column_to_upper(d.discipline_abbr, cd.superior)) AS discipline
      FROM cards_disciplines cd
       INNER JOIN disciplines d USING (discipline_id)
      WHERE cd.discipline_id = d.discipline_id
      GROUP BY card_id
    ) AS t_disciplines USING (card_id)
    LEFT JOIN
    (
      SELECT ct.card_id, t.title
      FROM cards_titles ct
       INNER JOIN titles t USING (title_id)
      WHERE ct.title_id = t.title_id
    ) AS t_titles USING (card_id)
    INNER JOIN
    (
      SELECT cs.card_id, set_string(s.set_abbr) AS set
      FROM cards_sets cs
        INNER JOIN sets s USING (set_id)
      WHERE cs.set_id = s.set_id
      GROUP BY card_id
    ) AS t_sets USING (card_id)
    INNER JOIN
    (
      SELECT cs.card_id, s.sect
      FROM cards_sects cs
        INNER JOIN sects s USING (sect_id)
      WHERE cs.sect_id = s.sect_id
    ) AS t_sects USING (card_id)
    INNER JOIN
    (
      SELECT cc.card_id, c.clan
      FROM cards_clans cc
        INNER JOIN clans c USING (clan_id)
      WHERE cc.clan_id = c.clan_id
    ) AS t_clans USING (card_id);