DROP DATABASE IF EXISTS pca_cms;

CREATE DATABASE pca_cms;

USE pca_cms;

DROP table IF EXISTS writers;

CREATE table writers (
  username    varchar(16) PRIMARY KEY,
  password    char(40) NOT NULL,
  full_name   text
);

DROP table IF EXISTS stories;

CREATE table stories (
  id          int PRIMARY KEY auto_increment,
  writer      varchar(16) NOT NULL,            # FOREIGN KEY writers.username
  page        varchar(16) NOT NULL,            # FOREIGN KEY pages.code
  headline    text,
  story_text  text,
  picture     text,
  created     int,
  modified    int,
  published   int
);

DROP table IF EXISTS pages;

CREATE table pages (
  code        varchar(16) PRIMARY KEY,
  description text
);

DROP table IF EXISTS writer_permissions;

CREATE table writer_permissions (
  writer      varchar(16) NOT NULL,            # FOREIGN KEY writers.username
  page        varchar(16) NOT NULL             # FOREIGN KEY pages.code
);

DROP table IF EXISTS keywords;

CREATE table keywords (
  story       int NOT NULL,                    # FOREIGN KEY stories.id
  keyword     varchar(32) NOT NULL,
  weight      int NOT NULL
);

insert into pages (code, description)
           values ('pca', 'PayCheck Arena');

insert into pages (code, description)
           values ('warcraft', 'News from Warcraft world!');

insert into pages (code, description)
           values ('cs', 'News from Counter-Strike world!');

insert into pages (code, description)
           values ('quake', 'News from Quake world!');

insert into pages (code, description)
           values ('aarmy', 'News from Americas Army world!');

insert into pages (code, description)
           values ('bfield', 'News from BattleField world!');



insert into stories 
    (id, writer, page, headline, created, modified, published,
     story_text, picture)
values (1, 'fritz', 'pca', 'PCA Up & Running', 976451129, 976458734, 976458754,
    'Finally, the PayCheck Arena started it\'s testing phase... Feel free to test the system and send us a feedback! With respect, your PCA staff!', '');