-- ***************************************************************************************************
--
-- Seagull CMS source code
-- (C)2006 by umop ap!sdn (aka E. Lee Gagnon)
-- 
-- Module: build_tables.sql
--
-- When installing the CMS, run these MySQL commands to generate the tables that the software needs.
-- Note: this file assumes the users table already exists and contains the necessary fields. If it does
-- not exist, then you will need to run the commands in the build_users_table.sql module before your
-- CMS based site will function.
--
-- ***************************************************************************************************

CREATE TABLE articles
             (article_id          INT,
			  user_id             INT,               -- who posted it
			  cat_id              INT,               -- category, or zero if none
			  article_title       VARCHAR(255),
			  article_subtitle    VARCHAR(255),
			  picture_url         VARCHAR(255),
			  pic_caption         VARCHAR(255),
			  abstract            TEXT,              -- "abstract" field may contain full article text,
			  INDEX(abstract(1)),                    -- or may contain only the first paragraph in which
			  full_text           TEXT,              -- case the "fulltext" field contains the remainder
			  INDEX(full_text(1)),                   -- of the article.
              full_link_text      VARCHAR(255),      -- Example: "click here to read more about X."
			  when_posted         DATETIME
			 );
ALTER TABLE articles ADD (views BIGINT);
ALTER TABLE articles ADD (ip_addr VARCHAR(15));
CREATE TABLE categories
             (cat_id              INT,
			  parent_id           INT,               -- zero if this is not a sub-category, else the 
			                                         -- cat_id of the parent category. Unlimited nesting.
			  cat_name            VARCHAR(255),
			  cat_descr           VARCHAR(255)
			 );
CREATE TABLE comments
             (comment_id          INT,
			  article_id          INT,
			  user_id             INT,               -- zero if guest post
			  user_name           VARCHAR(255),      -- so guests can specify a username
			  email               VARCHAR(255),      -- so guests can be required to give their email
			  ip_addr             VARCHAR(15),       -- so admins can ban trolls
			  when_posted         DATETIME,
              content             TEXT,              
              INDEX(content(1)),
              CONSTRAINT comment_id_pk PRIMARY KEY (comment_id)
			 );
CREATE TABLE permissions
             (permission_id       INT,
              permission_desc     VARCHAR(255)
             );
CREATE TABLE user_permissions
             (user_id             INT,
              permission_id       INT
             );
INSERT INTO permissions (permission_id, permission_desc) VALUES (1, 'ADMIN');
INSERT INTO permissions (permission_id, permission_desc) VALUES (2, 'POST_ARTICLES');
INSERT INTO permissions (permission_id, permission_desc) VALUES (3, 'POST_COMMENTS');
INSERT INTO permissions (permission_id, permission_desc) VALUES (4, 'EDIT_OWN_COMMENTS');
INSERT INTO permissions (permission_id, permission_desc) VALUES (5, 'EDIT_OWN_ARTICLES');
INSERT INTO permissions (permission_id, permission_desc) VALUES (6, 'MODERATE_COMMENTS');
INSERT INTO permissions (permission_id, permission_desc) VALUES (7, 'EDIT_ALL_ARTICLES');
INSERT INTO permissions (permission_id, permission_desc) VALUES (8, 'VIEW_FULL_TEXT');
INSERT INTO permissions (permission_id, permission_desc) VALUES (9, 'SEE_EMAIL_ADDRESSES');
INSERT INTO permissions (permission_id, permission_desc) VALUES (10, 'POST_HTML');
INSERT INTO permissions (permission_id, permission_desc) VALUES (11, 'MANAGE_USER_ACCOUNTS');
INSERT INTO permissions (permission_id, permission_desc) VALUES (12, 'MANAGE_CATEGORIES');
-- Admin permission automatically includes all others
INSERT INTO user_permissions (user_id, permission_id) VALUES (1, 1);