CREATE SCHEMA sylph;
CREATE GROUP sylph_web;
GRANT USAGE ON LANGUAGE SQL TO ALL;
GRANT USAGE ON LANGUAGE SQL TO ALL;

---------------------------------------------------------------------
-- banned IPs table                                                --
---------------------------------------------------------------------
CREATE TABLE sylph.banned_ips (
  id            SERIAL PRIMARY KEY,
  ts_created    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ip            VARCHAR(16) UNIQUE NOT NULL 
);

-- set table permissions
GRANT SELECT,INSERT,UPDATE,DELETE 
   ON TABLE sylph.banned_ips 
   TO GROUP sylph_web;

---------------------------------------------------------------------
-- banned terms table                                              --
---------------------------------------------------------------------
CREATE TABLE sylph.banned_terms (
  id            SERIAL PRIMARY KEY,
  ts_created    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  content       TEXT UNIQUE NOT NULL 
);

-- set table permissions
GRANT SELECT,INSERT,UPDATE,DELETE 
   ON TABLE sylph.banned_terms
   TO GROUP sylph_web;

---------------------------------------------------------------------
-- tags table                                                      --
---------------------------------------------------------------------
CREATE TABLE sylph.tags (
  id            SERIAL PRIMARY KEY,
  name          VARCHAR(64) NOT NULL,
  ts_created    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- set table permissions
GRANT SELECT,INSERT,UPDATE,DELETE 
   ON TABLE sylph.tags
   TO GROUP sylph_web;

---------------------------------------------------------------------
-- users                                                           --
---------------------------------------------------------------------
CREATE TABLE sylph.users (
  id            SERIAL PRIMARY KEY,

  -- timestamps 
  ts_created    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ts_updated    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  name          VARCHAR(32) UNIQUE NOT NULL,

  -- flags
  is_anonymous  BOOLEAN NOT NULL DEFAULT true,
  is_admin      BOOLEAN NOT NULL DEFAULT false,
  is_locked     BOOLEAN NOT NULL DEFAULT false,

  -- attributes
  pass_hash     VARCHAR(64) NOT NULL DEFAULT '',
  full_name     TEXT NOT NULL DEFAULT '',
  email         TEXT NOT NULL DEFAULT '',
  web_site      TEXT NOT NULL DEFAULT ''
);

-- create indices
CREATE INDEX in_users_is_anonymous ON sylph.users(is_anonymous);

-- add user function(name, pass_hash, full_name, email, web_site)
CREATE FUNCTION sylph.add_user(char, char, char, char, char)
RETURNS BIGINT AS '
  INSERT INTO sylph.users(name, is_anonymous, pass_hash, full_name, email, web_site) VALUES ($1, false, $2, $3, $4, $5);
  SELECT CURRVAL(''users_id_seq'');
' LANGUAGE SQL;

-- add anon user function(name, pass_hash, web_site)
CREATE FUNCTION sylph.add_anon_user(char, char, char)
RETURNS BIGINT AS '
  INSERT INTO sylph.users(name, is_anonymous, pass_hash, web_site) 
    VALUES ($1, true, $2, $3);
  SELECT CURRVAL(''users_id_seq'');
' LANGUAGE SQL;

-- set table permissions
GRANT SELECT,INSERT,UPDATE,DELETE 
   ON TABLE sylph.users
   TO GROUP sylph_web;


---------------------------------------------------------------------
-- post types                                                      --
---------------------------------------------------------------------
CREATE TABLE sylph.post_types (
  id            SERIAL PRIMARY KEY,
  name          VARCHAR(16) UNIQUE NOT NULL
);

-- set table permissions
GRANT SELECT,INSERT,UPDATE,DELETE 
   ON TABLE sylph.post_types
   TO GROUP sylph_web;

-- add post types
BEGIN TRANSACTION;
  INSERT INTO sylph.post_types(name) VALUES ('news');
  INSERT INTO sylph.post_types(name) VALUES ('article');
  INSERT INTO sylph.post_types(name) VALUES ('software');
  INSERT INTO sylph.post_types(name) VALUES ('comment');
  INSERT INTO sylph.post_types(name) VALUES ('link');
END;

---------------------------------------------------------------------
-- posts (articles, news, comments, etc)                           --
---------------------------------------------------------------------
CREATE TABLE sylph.posts (
  id            SERIAL PRIMARY KEY,

  type_id       INTEGER NOT NULL
                CONSTRAINT fk_posts_post_type REFERENCES sylph.post_types(id)
                ON UPDATE CASCADE ON DELETE CASCADE,
  
  user_id       INTEGER NOT NULL 
                CONSTRAINT fk_posts_user REFERENCES sylph.users(id)
                ON UPDATE CASCADE ON DELETE CASCADE,
  
  -- timestamps
  ts_created    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ts_updated    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  -- flags
  is_visible    BOOLEAN NOT NULL DEFAULT false,
  is_closed     BOOLEAN NOT NULL DEFAULT false,

  -- content
  title         VARCHAR(100) NOT NULL DEFAULT '',
  content       TEXT NOT NULL DEFAULT ''
);

-- create indices
CREATE INDEX in_posts_post_type ON sylph.posts(type_id);
CREATE INDEX in_posts_post_user ON sylph.posts(user_id);
CREATE INDEX in_posts_post_is_visible ON sylph.posts(is_visible);

-- create functions
CREATE FUNCTION sylph.add_post(char, char, boolean, char, char) 
RETURNS BIGINT AS '
  INSERT INTO sylph.posts(type_id,user_id,is_visible,title,content)
       VALUES (
         (SELECT id FROM sylph.post_types WHERE name = $1),
         (SELECT id FROM sylph.users WHERE name = $2),
         $3, $4, $5);
  SELECT CURRVAL(''posts_id_seq'');
' LANGUAGE SQL;

-- set table permissions
GRANT SELECT,INSERT,UPDATE,DELETE 
   ON TABLE sylph.posts
   TO GROUP sylph_web;


---------------------------------------------------------------------
-- user comments                                                   --
---------------------------------------------------------------------
CREATE TABLE sylph.comments (
  post_id       INTEGER NOT NULL 
                CONSTRAINT fk_comments_post REFERENCES sylph.posts(id)
                ON UPDATE CASCADE ON DELETE CASCADE,
  comment_id    INTEGER NOT NULL 
                CONSTRAINT fk_comments_comment REFERENCES sylph.posts(id)
                ON UPDATE CASCADE ON DELETE CASCADE
);

-- create indices
CREATE INDEX in_comments_post ON sylph.comments(post_id);
CREATE INDEX in_comments_comment ON sylph.comments(comment_id);

-- set table permissions
GRANT SELECT,INSERT,UPDATE,DELETE 
   ON TABLE sylph.comments
   TO GROUP sylph_web;


---------------------------------------------------------------------
-- post tags                                                       --
---------------------------------------------------------------------
CREATE TABLE sylph.post_tags (
  post_id       INTEGER NOT NULL
                CONSTRAINT fk_post_tags_post REFERENCES sylph.posts(id)
                ON UPDATE CASCADE ON DELETE CASCADE,
  tag_id        INTEGER NOT NULL 
                CONSTRAINT fk_post_tags_tag REFERENCES sylph.tags(id)
                ON UPDATE CASCADE ON DELETE CASCADE
);

-- create indices
CREATE INDEX in_post_tags_post ON sylph.post_tags(post_id);
CREATE INDEX in_post_tags_tag ON sylph.post_tags(tag_id);

-- set table permissions
GRANT SELECT,INSERT,UPDATE,DELETE 
   ON TABLE sylph.post_tags
   TO GROUP sylph_web;


---------------------------------------------------------------------
-- software versions                                               --
---------------------------------------------------------------------
CREATE TABLE sylph.versions (
  id            SERIAL PRIMARY KEY,

  post_id       INTEGER NOT NULL
                CONSTRAINT fk_post_tags_post REFERENCES sylph.posts(id)
                ON UPDATE CASCADE ON DELETE CASCADE,

  -- basic attributes
  name          VARCHAR(32) NOT NULL,
  ts_created    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  -- flags
  is_visible    BOOLEAN NOT NULL DEFAULT true,
  has_rubygem   BOOLEAN NOT NULL DEFAULT false,

  -- urls
  url_download  TEXT NOT NULL DEFAULT '',
  url_signature TEXT NOT NULL DEFAULT '',
  url_changelog TEXT NOT NULL DEFAULT ''
);

-- create indices
CREATE INDEX in_versions_post ON sylph.versions(post_id);

-- set table permissions
GRANT SELECT,INSERT,UPDATE,DELETE 
   ON TABLE sylph.versions
   TO GROUP sylph_web;


---------------------------------------------------------------------
-- views                                                           --
---------------------------------------------------------------------

-- links view
CREATE VIEW sylph.links AS 
  SELECT id, content AS link, title
    FROM sylph.posts
   WHERE type_id = (SELECT id FROM sylph.post_types WHERE name = 'link')
     AND is_visible = true;

-- news view
CREATE VIEW sylph.news AS 
  SELECT id, user_id, title, content,
         (SELECT COUNT(*) 
            FROM sylph.comments
           WHERE post_id = p.id) AS num_comments
    FROM sylph.posts p
   WHERE type_id = (SELECT id FROM sylph.post_types WHERE name = 'news')
     AND is_visible = true;

-- articles view
CREATE VIEW sylph.articles AS
  SELECT id, user_id, title, content,
         (SELECT COUNT(*) 
            FROM sylph.comments
           WHERE post_id = p.id) AS num_comments
    FROM sylph.posts p
   WHERE type_id = (SELECT id FROM sylph.post_types WHERE name = 'article')
     AND is_visible = true;

-- software view
CREATE VIEW sylph.software AS
  SELECT id, user_id, title, content,
         (SELECT COUNT(*) 
            FROM sylph.comments
           WHERE post_id = p.id) AS num_comments
    FROM sylph.posts p
   WHERE type_id = (SELECT id FROM sylph.post_types WHERE name = 'article')
     AND is_visible = true;

-- set permissions
GRANT SELECT 
  ON TABLE sylph.links, sylph.news, sylph.articles, sylph.software
  TO GROUP sylph_web;

---------------------------------------------------------------------
-- functions                                                       --
---------------------------------------------------------------------

-- add link (user, title, URL)
CREATE FUNCTION sylph.add_link(char, char, char) RETURNS BIGINT AS '
  SELECT sylph.add_post(''link'', $1, true, $2, $3);
' LANGUAGE SQL;

-- add news (user, title, content)
CREATE FUNCTION sylph.add_news(char, char, char) RETURNS BIGINT AS '
  SELECT sylph.add_post(''news'', $1, false, $2, $3);
' LANGUAGE SQL;

-- add article (user, title, content)
CREATE FUNCTION sylph.add_article(char, char, char) RETURNS BIGINT AS '
  SELECT sylph.add_post(''article'', $1, false, $2, $3);
' LANGUAGE SQL;

-- add comment (post_id, user, content)
CREATE FUNCTION sylph.add_comment(integer, char, char) 
RETURNS BIGINT AS '
  SELECT sylph.add_post(''comment'', $2, true, ''comment'', $3);
  INSERT INTO sylph.comments(post_id, comment_id) VALUES (
    $1, 
    (SELECT sylph.add_post(''comment'', $2, true, ''comment'', $3))
  );
  SELECT CURRVAL(''posts_id_seq'');
' LANGUAGE SQL;

-- add software (user, title, description)
CREATE FUNCTION sylph.add_software(char, char, char) RETURNS BIGINT AS '
  SELECT sylph.add_post(''software'', $1, true, $2, $3);
' LANGUAGE SQL;

-- add software version (software, version)
CREATE FUNCTION sylph.add_version(char, char) RETURNS BIGINT AS '
  INSERT INTO versions(post_id, name) VALUES (
    (SELECT id 
       FROM sylph.posts 
      WHERE type_id = (SELECT id 
                         FROM sylph.post_types 
                        WHERE name = ''software'')
        AND is_visible = true
        AND title = $1), 
    $2
  );
  SELECT CURRVAL(''versions_id_seq'');
' LANGUAGE SQL;

-- set permissions
GRANT EXECUTE
  ON FUNCTION sylph.add_link, sylph.add_news, sylph.add_article, sylph.add_comment, sylph.add_software, sylph.add_version
  TO GROUP sylph_web;