/*
cat schema.sql | sqlite3 RainMaker.db
*/

CREATE TABLE `ModType` (
  `id`   INTEGER NOT NULL,
  `name` TEXT UNIQUE NOT NULL
);

INSERT INTO `ModType` (`id`, `name`) VALUES( 1, 'Article');
INSERT INTO `ModType` (`id`, `name`) VALUES( 2, 'Render');
INSERT INTO `ModType` (`id`, `name`) VALUES( 3, 'Rating');
INSERT INTO `ModType` (`id`, `name`) VALUES( 4, 'Keyword');
INSERT INTO `ModType` (`id`, `name`) VALUES( 5, 'Footnote');
INSERT INTO `ModType` (`id`, `name`) VALUES( 6, 'Picture');
INSERT INTO `ModType` (`id`, `name`) VALUES( 7, 'Song');
INSERT INTO `ModType` (`id`, `name`) VALUES( 8, 'Video');
INSERT INTO `ModType` (`id`, `name`) VALUES( 9, 'Collection');
INSERT INTO `ModType` (`id`, `name`) VALUES(10, 'Script');
INSERT INTO `ModType` (`id`, `name`) VALUES(11, 'Stylesheet');
INSERT INTO `ModType` (`id`, `name`) VALUES(12, 'Contact');
INSERT INTO `ModType` (`id`, `name`) VALUES(13, 'ShortCode');
/*
INSERT INTO `ModType` (`id`, `name`) VALUES(0, '');
*/

CREATE TABLE `RelType` (
  `id`   INTEGER NOT NULL,
  `name` TEXT UNIQUE NOT NULL
);

INSERT INTO `RelType` (`id`, `name`) VALUES( 1, 'leadPhoto');
INSERT INTO `RelType` (`id`, `name`) VALUES( 2, 'backgroundSong');
INSERT INTO `RelType` (`id`, `name`) VALUES( 4, 'embedded');
INSERT INTO `RelType` (`id`, `name`) VALUES( 5, 'hasFootnote');
INSERT INTO `RelType` (`id`, `name`) VALUES( 6, 'contentRating');
INSERT INTO `RelType` (`id`, `name`) VALUES( 7, 'inCollection');
INSERT INTO `RelType` (`id`, `name`) VALUES( 8, 'hasKeyword');
INSERT INTO `RelType` (`id`, `name`) VALUES( 9, 'mentioned');
INSERT INTO `RelType` (`id`, `name`) VALUES(10, 'requires');
/*
INSERT INTO `RelType` (`id`, `name`) VALUES(0, '');
*/

CREATE TABLE `RenderType` (
  `id`   INTEGER PRIMARY KEY AUTOINCREMENT,
  `name` TEXT    UNIQUE NOT NULL
);

INSERT INTO `RenderType` (`id`, `name`) VALUES (1, 'singlePageApp');
INSERT INTO `RenderType` (`id`, `name`) VALUES (2, 'htmlDocument');
INSERT INTO `RenderType` (`id`, `name`) VALUES (3, 'htmlFragment');
INSERT INTO `RenderType` (`id`, `name`) VALUES (4, 'plainText');
/*
INSERT INTO `RenderType` (`id`, `name`) VALUES (0, '');
*/

CREATE TABLE `CollectionType` (
  `id`   INTEGER PRIMARY KEY AUTOINCREMENT,
  `name` TEXT    UNIQUE NOT NULL
);

INSERT INTO `CollectionType` (`id`, `name`) VALUES (1, 'Volume');
INSERT INTO `CollectionType` (`id`, `name`) VALUES (2, 'Related');
INSERT INTO `CollectionType` (`id`, `name`) VALUES (3, 'Gallery');
INSERT INTO `CollectionType` (`id`, `name`) VALUES (4, 'Playlist');
INSERT INTO `CollectionType` (`id`, `name`) VALUES (5, 'RssFeed');
/*
INSERT INTO `CollectionType` (`id`, `name`) VALUES (0, '');
*/

CREATE TABLE `MimeType` (
  `id`        INTEGER PRIMARY KEY AUTOINCREMENT,
  `name`      TEXT UNIQUE NOT NULL,
  `extension` TEXT NOT NULL DEFAULT ''
);

INSERT INTO `MimeType` (`id`, `name`, `extension`) VALUES (1, 'image/png',  'png');
INSERT INTO `MimeType` (`id`, `name`, `extension`) VALUES (2, 'image/gif',  'gif');
INSERT INTO `MimeType` (`id`, `name`, `extension`) VALUES (3, 'image/jpeg', 'jpg');
INSERT INTO `MimeType` (`id`, `name`, `extension`) VALUES (4, 'audio/mpeg', 'mp3');
INSERT INTO `MimeType` (`id`, `name`, `extension`) VALUES (5, 'video/mp4',  'mp4');
/*
INSERT INTO `MimeType` (`id`, `name`) VALUES (0, '');
*/

CREATE TABLE `Rating` (
  `id`   INTEGER PRIMARY KEY AUTOINCREMENT,
  `name` TEXT    UNIQUE NOT NULL
);

INSERT INTO `Rating` (`id`, `name`) VALUES (1, 'general');
INSERT INTO `Rating` (`id`, `name`) VALUES (2, 'mature');
INSERT INTO `Rating` (`id`, `name`) VALUES (3, 'restricted');
INSERT INTO `Rating` (`id`, `name`) VALUES (4, '14 years');
INSERT INTO `Rating` (`id`, `name`) VALUES (5, 'safe for kids');

CREATE TABLE `EmailOpt` (
  `id`   INTEGER PRIMARY KEY AUTOINCREMENT,
  `name` TEXT    UNIQUE NOT NULL
);

INSERT INTO `EmailOpt` (`id`, `name`) VALUES (1, 'Once');
INSERT INTO `EmailOpt` (`id`, `name`) VALUES (2, 'Always');
INSERT INTO `EmailOpt` (`id`, `name`) VALUES (3, 'Never');

CREATE TABLE `ModRel` (
  `parentTypeId` INTEGER  NOT NULL,
  `parentId`     INTEGER  NOT NULL,
  `childTypeId`  INTEGER  NOT NULL,
  `childId`      INTEGER  NOT NULL,
  `relTypeId`    INTEGER  NOT NULL,
  `created`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `comments`     TEXT     NOT NULL DEFAULT '',
  FOREIGN KEY(`parentTypeId`) REFERENCES `modType` (`id`),
  FOREIGN KEY(`childTypeId`)  REFERENCES `modType` (`id`),
  FOREIGN KEY(`relTypeId`)    REFERENCES `relType` (`id`)
);

CREATE TABLE `Article` (
  `id`          INTEGER PRIMARY KEY AUTOINCREMENT,
  `filename`    TEXT     UNIQUE NOT NULL,
  `title`       TEXT     NOT NULL DEFAULT '',
  `description` TEXT     NOT NULL DEFAULT '',
  `created`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `published`   DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `content`     TEXT     NOT NULL DEFAULT '',
  `onload`      TEXT     NOT NULL DEFAULT '',
  `style`       TEXT     NOT NULL DEFAULT '',
  `priority`    REAL     NOT NULL DEFAULT 0.5
);

CREATE TABLE `Render` (
  `id`        INTEGER PRIMARY KEY AUTOINCREMENT,
  `articleId` INTEGER NOT NULL DEFAULT 0,
  `typeId`    INTEGER NOT NULL DEFAULT 0,
  `content`   TEXT    NOT NULL,
  FOREIGN KEY(`articleId`) REFERENCES `Article`    (`id`),
  FOREIGN KEY(`typeId`)    REFERENCES `RenderType` (`id`)
);

CREATE TABLE `Keyword` (
  `id`   INTEGER PRIMARY KEY AUTOINCREMENT,
  `name` TEXT    UNIQUE NOT NULL
);

CREATE TABLE `Footnote` (
  `id`      INTEGER PRIMARY KEY AUTOINCREMENT,
  `content` TEXT    NOT NULL
);

CREATE TABLE `Collection` (
  `id`          INTEGER PRIMARY KEY AUTOINCREMENT,
  `typeId`      INTEGER  NOT NULL,
  `filename`    TEXT     UNIQUE NOT NULL,
  `title`       TEXT     NOT NULL,
  `description` TEXT     NOT NULL DEFAULT '',
  `created`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(`typeId`) REFERENCES `CollectionType` (`id`)
);

CREATE TABLE `Picture` (
  `id`          INTEGER PRIMARY KEY AUTOINCREMENT,
  `filename`    TEXT    NOT NULL,
  `hash`        TEXT    UNIQUE NOT NULL,
  `mimeTypeId`  INTEGER NOT NULL,
  `title`       TEXT    NOT NULL DEFAULT '',
  `description` TEXT    NOT NULL DEFAULT '',
  `width`       INTEGER NOT NULL,
  `height`      INTEGER NOT NULL,
  FOREIGN KEY(`mimeTypeId`) REFERENCES `MimeType` (`id`)
);

CREATE TABLE `Song` (
  `id`         INTEGER PRIMARY KEY AUTOINCREMENT,
  `filename`   TEXT    NOT NULL,
  `hash`       TEXT    UNIQUE NOT NULL,
  `mimeTypeId` INTEGER NOT NULL,
  `title`      TEXT    NOT NULL DEFAULT '',
  `artist`     TEXT    NOT NULL DEFAULT '',
  `album`      TEXT    NOT NULL DEFAULT '',
  `label`      TEXT    NOT NULL DEFAULT '',
  `released`   INTEGER NOT NULL DEFAULT 0,
  `duration`   TIME    NOT NULL DEFAULT '00:00:00',
  FOREIGN KEY(`mimeTypeId`) REFERENCES `MimeType` (`id`)
);

CREATE TABLE `Video` (
  `id`         INTEGER PRIMARY KEY AUTOINCREMENT,
  `filename`   TEXT    NOT NULL,
  `hash`       TEXT    UNIQUE NOT NULL,
  `mimeTypeId` INTEGER NOT NULL,
  `title`      TEXT    NOT NULL DEFAULT '',
  `studio`     TEXT    NOT NULL DEFAULT '',
  `released`   INTEGER NOT NULL DEFAULT 0,
  `duration`   TIME    NOT NULL DEFAULT '00:00:00',
  FOREIGN KEY(`mimeTypeId`) REFERENCES `MimeType` (`id`)
);

CREATE TABLE `Contact` (
  `id`          INTEGER PRIMARY KEY AUTOINCREMENT,
  `username`    TEXT    UNIQUE NOT NULL,
  `displayName` TEXT    NOT NULL DEFAULT '',
  `email`       TEXT    NOT NULL DEFAULT '',
  `website`     TEXT    NOT NULL DEFAULT '',
  `vCard`       TEXT    NOT NULL DEFAULT '',
  `hCard`       TEXT    NOT NULL DEFAULT '',
  `token`       TEXT    NOT NULL DEFAULT '',
  `emailOptId`  INTEGER NOT NULL DEFAULT 1,
  FOREIGN KEY(`emailOptId`) REFERENCES `EmailOpt` (`id`)
);

CREATE TABLE `ShortCode` (
  `id`      INTEGER PRIMARY KEY AUTOINCREMENT,
  `find`    TEXT NOT NULL,
  `replace` TEXT NOT NULL
);

CREATE TABLE `Script` (
  `id`       INTEGER PRIMARY KEY AUTOINCREMENT,
  `filename` TEXT    UNIQUE NOT NULL
);

INSERT INTO `Script` (`id`, `filename`) VALUES ( 1, 'prototype/String');
INSERT INTO `Script` (`id`, `filename`) VALUES ( 2, 'prototype/Number');
INSERT INTO `Script` (`id`, `filename`) VALUES ( 3, 'prototype/Array');
INSERT INTO `Script` (`id`, `filename`) VALUES ( 4, 'prototype/Object');
INSERT INTO `Script` (`id`, `filename`) VALUES ( 5, 'prototype/Element');
INSERT INTO `Script` (`id`, `filename`) VALUES ( 6, 'module/Clipboard');
INSERT INTO `Script` (`id`, `filename`) VALUES ( 7, 'module/Core');
INSERT INTO `Script` (`id`, `filename`) VALUES ( 8, 'module/CardFile');
INSERT INTO `Script` (`id`, `filename`) VALUES ( 9, 'module/Cursor');
INSERT INTO `Script` (`id`, `filename`) VALUES (10, 'module/Http');
INSERT INTO `Script` (`id`, `filename`) VALUES (11, 'module/InfiniteScroll');
INSERT INTO `Script` (`id`, `filename`) VALUES (12, 'module/Intercom');
INSERT INTO `Script` (`id`, `filename`) VALUES (13, 'module/IntercomStation');
INSERT INTO `Script` (`id`, `filename`) VALUES (14, 'module/Jukebox');
INSERT INTO `Script` (`id`, `filename`) VALUES (15, 'module/Menu');
INSERT INTO `Script` (`id`, `filename`) VALUES (16, 'module/Modal');
INSERT INTO `Script` (`id`, `filename`) VALUES (17, 'module/Page');
INSERT INTO `Script` (`id`, `filename`) VALUES (18, 'module/Paginator');
INSERT INTO `Script` (`id`, `filename`) VALUES (19, 'module/Pictures');
INSERT INTO `Script` (`id`, `filename`) VALUES (20, 'module/ScrollTable');
INSERT INTO `Script` (`id`, `filename`) VALUES (21, 'module/Serializeable');
INSERT INTO `Script` (`id`, `filename`) VALUES (22, 'module/ShortCodes');
INSERT INTO `Script` (`id`, `filename`) VALUES (23, 'module/SoundFX');
INSERT INTO `Script` (`id`, `filename`) VALUES (24, 'module/TagRite');
INSERT INTO `Script` (`id`, `filename`) VALUES (25, 'module/TaskList');
INSERT INTO `Script` (`id`, `filename`) VALUES (26, 'module/Time');
INSERT INTO `Script` (`id`, `filename`) VALUES (27, 'module/Tree');
INSERT INTO `Script` (`id`, `filename`) VALUES (28, 'module/VideoJukebox');
INSERT INTO `Script` (`id`, `filename`) VALUES (29, 'module/Widget');
INSERT INTO `Script` (`id`, `filename`) VALUES (30, 'effect/FlipCard');
INSERT INTO `Script` (`id`, `filename`) VALUES (31, 'effect/FlyThrough');
INSERT INTO `Script` (`id`, `filename`) VALUES (32, 'effect/Magnifier');
INSERT INTO `Script` (`id`, `filename`) VALUES (33, 'effect/Slideshow');
INSERT INTO `Script` (`id`, `filename`) VALUES (34, 'tag/Binary');
INSERT INTO `Script` (`id`, `filename`) VALUES (35, 'tag/Blink');
INSERT INTO `Script` (`id`, `filename`) VALUES (36, 'tag/Brutal');
INSERT INTO `Script` (`id`, `filename`) VALUES (37, 'tag/Bucketfish');
INSERT INTO `Script` (`id`, `filename`) VALUES (38, 'tag/Console');
INSERT INTO `Script` (`id`, `filename`) VALUES (39, 'tag/Elapsed');
INSERT INTO `Script` (`id`, `filename`) VALUES (40, 'tag/Emoticon');
INSERT INTO `Script` (`id`, `filename`) VALUES (41, 'tag/Fire');
INSERT INTO `Script` (`id`, `filename`) VALUES (42, 'tag/Gif');
INSERT INTO `Script` (`id`, `filename`) VALUES (43, 'tag/Googie');
INSERT INTO `Script` (`id`, `filename`) VALUES (44, 'tag/MoonPhase');
INSERT INTO `Script` (`id`, `filename`) VALUES (45, 'tag/MultiCol');
INSERT INTO `Script` (`id`, `filename`) VALUES (46, 'tag/Muster');
INSERT INTO `Script` (`id`, `filename`) VALUES (47, 'tag/PageList');
INSERT INTO `Script` (`id`, `filename`) VALUES (48, 'tag/Page');
INSERT INTO `Script` (`id`, `filename`) VALUES (49, 'tag/Pic');
INSERT INTO `Script` (`id`, `filename`) VALUES (50, 'tag/Readout');
INSERT INTO `Script` (`id`, `filename`) VALUES (51, 'tag/Slideshow');
INSERT INTO `Script` (`id`, `filename`) VALUES (52, 'tag/Spectrum');
INSERT INTO `Script` (`id`, `filename`) VALUES (53, 'tag/Spoiler');
INSERT INTO `Script` (`id`, `filename`) VALUES (54, 'tag/Spring');
INSERT INTO `Script` (`id`, `filename`) VALUES (55, 'tag/Updated');
INSERT INTO `Script` (`id`, `filename`) VALUES (56, 'tag/Wave');
/*
INSERT INTO `Script` (`id`, `filename`) VALUES (0, '');
*/

INSERT INTO `ModRel` (`parentTypeId`, `parentId`, `childTypeId`, `childId`, `relTypeId`, `comments`) VALUES
  (10, 0, 10, 0, 10, 'Child requires parent.'), (10, 0, 10, 0, 10, 'Child requires parent.'), (10, 0, 10, 0, 10, 'Child requires parent.'),
  (10, 0, 10, 0, 10, 'Child requires parent.'), (10, 0, 10, 0, 10, 'Child requires parent.'), (10, 0, 10, 0, 10, 'Child requires parent.'),
  (10, 0, 10, 0, 10, 'Child requires parent.'), (10, 0, 10, 0, 10, 'Child requires parent.'), (10, 0, 10, 0, 10, 'Child requires parent.');

CREATE TABLE `Stylesheet` (
  `id`       INTEGER PRIMARY KEY AUTOINCREMENT,
  `filename` TEXT    UNIQUE NOT NULL
);

INSERT INTO `Stylesheet` (`id`, `filename`) VALUES ( 1, 'layout');
INSERT INTO `Stylesheet` (`id`, `filename`) VALUES ( 2, 'article');
INSERT INTO `Stylesheet` (`id`, `filename`) VALUES ( 3, 'form');
INSERT INTO `Stylesheet` (`id`, `filename`) VALUES ( 4, 'table');
INSERT INTO `Stylesheet` (`id`, `filename`) VALUES ( 5, 'cardfile');
INSERT INTO `Stylesheet` (`id`, `filename`) VALUES ( 6, 'menu');
INSERT INTO `Stylesheet` (`id`, `filename`) VALUES ( 7, 'modal');
INSERT INTO `Stylesheet` (`id`, `filename`) VALUES ( 8, 'text-fx');
INSERT INTO `Stylesheet` (`id`, `filename`) VALUES ( 9, 'jukebox');
INSERT INTO `Stylesheet` (`id`, `filename`) VALUES (10, 'animation');
/*
INSERT INTO `Stylesheet` (`id`, `filename`) VALUES (0, '');
*/
