# there is no content storage in DB, only metadatas

CREATE TABLE pages (
    id INTEGER UNSIGNED PRIMARY KEY NOT NULL,
    title VARCHAR(255) NOT NULL,
    uri VARCHAR(255) NOT NULL,
    revision INTEGER NOT NULL DEFAULT '0',
    date_create INTEGER NOT NULL,
    date_update INTEGER NOT NULL,
    year INTEGER NOT NULL, # useful for date searching
    month INTEGER NOT NULL,
    day INTEGER NOT NULL,
    parent INTEGER NOT NULL DEFAULT '0', # Parent page for this page (yeah)
    lang VARCHAR(255) NOT NULL, # 2 letters ISO 639-1 code
    allow_comments INTEGER NOT NULL DEFAULT 0, # -1 = not allowed, 0 = follow config, 1 = allowed
    allow_edit INTEGER NOT NULL DEFAULT 0, # -1 = not allowed, 0 = follow config, 1 = allowed
    nb_comments INTEGER NOT NULL DEFAULT '0',
    nb_pages INTEGER NOT NULL DEFAULT '0',
    nb_medias INTEGER NOT NULL DEFAULT '0',
    status INTEGER NOT NULL DEFAULT '0' # 0 = offline, 1 = online
);

CREATE INDEX page_ymd ON pages (year, month, day);
CREATE INDEX page_parent ON pages (parent);
CREATE INDEX page_lang ON pages (lang);
CREATE INDEX date ON pages (date_update, date_create);

CREATE TABLE revisions (
    page INTEGER NOT NULL, # page id
    id INTEGER NOT NULL, # revision id (beginning at 1 for each page)
    date INTEGER NOT NULL,
    author_name TEXT,
    author_url TEXT,
    comment TEXT,
    changed INTEGER SIGNED NOT NULL,
    PRIMARY KEY(page, id)
);

CREATE INDEX revision_date ON revisions (date);

CREATE TABLE comments (
    id INTEGER UNSIGNED PRIMARY KEY NOT NULL,
    title VARCHAR(255) NOT NULL,
    date INTEGER UNSIGNED NOT NULL,
    author_name VARCHAR(255) NOT NULL,
    author_url VARCHAR(255) NOT NULL,
    parent INTEGER NOT NULL, # parent id
    context INTEGER NOT NULL, # parent context : 0 = comment, 1 = page, 2 = media
    lang VARCHAR(255) NOT NULL, # 2 letters ISO 639-1 code
    nb_comments INTEGER NOT NULL DEFAULT '0',
    unread INTEGER NOT NULL DEFAULT '1'
);

CREATE INDEX comment_parent_context ON comments (parent, context);

CREATE TABLE medias (
    id INTEGER UNSIGNED PRIMARY KEY NOT NULL,
    title VARCHAR(255) NOT NULL,
    extension VARCHAR(255) NOT NULL, # file extension
    description TEXT NOT NULL,
    page INTEGER NOT NULL, # page id
    allow_comments INTEGER NOT NULL,
    lang VARCHAR(255) NULL, # 2 letters ISO 639-1 code, NULL if not applicable
    nb_comments INTEGER NOT NULL DEFAULT '0'
);

CREATE INDEX media_page ON medias (page);

CREATE TABLE config (
    key VARCHAR(255) PRIMARY KEY NOT NULL,
    type VARCHAR(10) NOT NULL,
    value TEXT NOT NULL
);

INSERT INTO config (key, type, value) VALUES ('login', 'string', 'admin');
INSERT INTO config (key, type, value) VALUES ('password', 'string', 'abcd');
INSERT INTO config (key, type, value) VALUES ('title', 'string', 'Mon site !');
INSERT INTO config (key, type, value) VALUES ('default_lang', 'string', 'en');