CREATE TABLE "acl" (
    "acl_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "page_id" INTEGER NOT NULL DEFAULT '0' ,
    "privilege" VARCHAR(10) NOT NULL DEFAULT '' ,
    "list" TEXT NOT NULL
);
 
CREATE UNIQUE INDEX "acl_idx_page_id" ON "acl" ("page_id", "privilege");
 
 
CREATE TABLE "auth_token" (
    "auth_token_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "selector" CHARACTER(12) NOT NULL DEFAULT '' ,
    "token" CHARACTER(64) NOT NULL DEFAULT '' ,
    "user_id" INTEGER NOT NULL DEFAULT '0' ,
    "token_expires" DATETIME NULL
);
 
CREATE INDEX "auth_token_idx_user_id" ON "auth_token" ("user_id");
 
CREATE UNIQUE INDEX "auth_token_idx_selector" ON "auth_token" ("selector");
 
 
CREATE TABLE "cache" (
    "cache_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "hash" CHARACTER(40) NOT NULL DEFAULT '' ,
    "method" VARCHAR(20) NOT NULL DEFAULT '' ,
    "query" VARCHAR(255) NOT NULL DEFAULT '' ,
    "cache_lang" VARCHAR(5) NOT NULL DEFAULT '' ,
    "cache_time" DATETIME NULL
);
 
CREATE INDEX "cache_idx_hash" ON "cache" ("hash");
 
CREATE INDEX "cache_idx_cache_time" ON "cache" ("cache_time");
 
 
CREATE TABLE "category" (
    "category_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "parent_id" INTEGER NOT NULL DEFAULT '0' ,
    "category_lang" VARCHAR(5) NOT NULL DEFAULT '' ,
    "category" VARCHAR(255) NOT NULL DEFAULT '' ,
    "category_description" TEXT NOT NULL
);
 
CREATE UNIQUE INDEX "category_idx_category" ON "category" ("category_lang", "category");
 
 
CREATE TABLE "category_assignment" (
    "assignment_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "category_id" INTEGER NOT NULL DEFAULT '0' ,
    "object_type_id" INTEGER NOT NULL DEFAULT '0' ,
    "object_id" INTEGER NOT NULL DEFAULT '0'
);
 
CREATE INDEX "category_assignment_idx_object_type_id" ON "category_assignment" ("object_type_id");
 
CREATE INDEX "category_assignment_idx_object_id" ON "category_assignment" ("object_id");
 
CREATE INDEX "category_assignment_idx_category_id" ON "category_assignment" ("category_id");
 
CREATE UNIQUE INDEX "category_assignment_idx_assignment" ON "category_assignment" ("category_id", "object_type_id", "object_id");
 
 
CREATE TABLE "config" (
    "config_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "config_name" VARCHAR(100) NOT NULL DEFAULT '' ,
    "config_value" TEXT NULL
);
 
CREATE UNIQUE INDEX "config_idx_config_name" ON "config" ("config_name");
 
 
CREATE TABLE "external_link" (
    "link_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "page_id" INTEGER NOT NULL DEFAULT '0' ,
    "link" TEXT NOT NULL
);
 
CREATE INDEX "external_link_idx_page_id" ON "external_link" ("page_id");
 
 
CREATE TABLE "file" (
    "file_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "page_id" INTEGER NOT NULL DEFAULT '0' ,
    "user_id" INTEGER NOT NULL DEFAULT '0' ,
    "file_name" VARCHAR(255) NOT NULL DEFAULT '' ,
    "file_lang" VARCHAR(5) NOT NULL DEFAULT '' ,
    "file_description" VARCHAR(255) NOT NULL DEFAULT '' ,
    "caption" TEXT NULL  ,
    "author" VARCHAR(255) NOT NULL DEFAULT '' ,
    "source" VARCHAR(255) NOT NULL DEFAULT '' ,
    "source_url" VARCHAR(255) NOT NULL DEFAULT '' ,
    "license_id" INTEGER NOT NULL DEFAULT '0' ,
    "created" DATETIME NULL  ,
    "modified" DATETIME NULL  ,
    "file_size" INTEGER NOT NULL DEFAULT '0' ,
    "picture_w" INTEGER NOT NULL DEFAULT '0' ,
    "picture_h" INTEGER NOT NULL DEFAULT '0' ,
    "file_ext" VARCHAR(10) NOT NULL DEFAULT '' ,
    "mime_type" VARCHAR(255) NOT NULL DEFAULT '' ,
    "file_hash" CHARACTER(40) NOT NULL DEFAULT '' ,
    "deleted" TINYINT NULL DEFAULT '0'
);
 
CREATE INDEX "file_idx_user_id" ON "file" ("user_id");
 
CREATE INDEX "file_idx_page_id_2" ON "file" ("page_id", "created");
 
CREATE UNIQUE INDEX "file_idx_page_id" ON "file" ("page_id", "file_name");
 
CREATE INDEX "file_idx_file_name" ON "file" ("file_name");
 
CREATE INDEX "file_idx_file_hash" ON "file" ("file_hash");
 
CREATE INDEX "file_idx_deleted" ON "file" ("deleted");
 
 
CREATE TABLE "file_link" (
    "file_link_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "page_id" INTEGER NOT NULL DEFAULT '0' ,
    "file_id" INTEGER NOT NULL DEFAULT '0'
);
 
CREATE INDEX "file_link_idx_page_id" ON "file_link" ("page_id");
 
CREATE INDEX "file_link_idx_file_id" ON "file_link" ("file_id");
 
 
CREATE TABLE "log" (
    "log_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "log_time" DATETIME NULL  ,
    "level" TINYINT NOT NULL DEFAULT '0' ,
    "user_id" INTEGER NOT NULL DEFAULT '0' ,
    "ip" VARCHAR(45) NOT NULL DEFAULT '' ,
    "message" TEXT NOT NULL
);
 
CREATE INDEX "log_idx_user_id" ON "log" ("user_id");
 
CREATE INDEX "log_idx_time" ON "log" ("log_time");
 
CREATE INDEX "log_idx_level" ON "log" ("level");
 
CREATE INDEX "log_idx_ip" ON "log" ("ip");
 
 
CREATE TABLE "menu" (
    "menu_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "user_id" INTEGER NOT NULL DEFAULT '0' ,
    "page_id" INTEGER NOT NULL DEFAULT '0' ,
    "menu_lang" VARCHAR(5) NOT NULL DEFAULT '' ,
    "menu_title" VARCHAR(255) NOT NULL DEFAULT '' ,
    "menu_position" SMALLINT NOT NULL DEFAULT '0'
);
 
CREATE INDEX "menu_idx_user_id" ON "menu" ("user_id");
 
CREATE INDEX "menu_idx_page_id" ON "menu" ("page_id");
 
CREATE UNIQUE INDEX "menu_idx_menu" ON "menu" ("user_id", "page_id", "menu_lang");
 
CREATE INDEX "menu_idx_lang" ON "menu" ("menu_lang");
 
 
CREATE TABLE "page" (
    "page_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "version_id" INTEGER NOT NULL DEFAULT '1' ,
    "owner_id" INTEGER NOT NULL DEFAULT '0' ,
    "user_id" INTEGER NOT NULL DEFAULT '0' ,
    "tag" VARCHAR(255) NOT NULL DEFAULT '' ,
    "title" VARCHAR(255) NOT NULL DEFAULT '' ,
    "menu_tag" VARCHAR(255) NOT NULL DEFAULT '' ,
    "depth" INTEGER NOT NULL DEFAULT '0' ,
    "parent_id" INTEGER NOT NULL DEFAULT '0' ,
    "created" DATETIME NULL  ,
    "modified" DATETIME NULL  ,
    "body" TEXT NOT NULL  ,
    "body_r" TEXT NOT NULL  ,
    "body_toc" TEXT NOT NULL  ,
    "formatting" VARCHAR(20) NOT NULL DEFAULT 'wacko' ,
    "edit_note" VARCHAR(255) NOT NULL DEFAULT '' ,
    "minor_edit" TINYINT NULL DEFAULT '0' ,
    "page_size" INTEGER NOT NULL DEFAULT '0' ,
    "license_id" INTEGER NOT NULL DEFAULT '0' ,
    "reviewed" TINYINT NOT NULL DEFAULT '0' ,
    "reviewed_time" DATETIME NULL  ,
    "reviewer_id" INTEGER NOT NULL DEFAULT '0' ,
    "ip" VARCHAR(45) NOT NULL DEFAULT '' ,
    "latest" TINYINT NULL DEFAULT '1' ,
    "handler" VARCHAR(30) NOT NULL DEFAULT 'page' ,
    "comment_on_id" INTEGER NOT NULL DEFAULT '0' ,
    "comments" INTEGER NOT NULL DEFAULT '0' ,
    "files" INTEGER NOT NULL DEFAULT '0' ,
    "revisions" INTEGER NOT NULL DEFAULT '0' ,
    "hits" INTEGER NOT NULL DEFAULT '0' ,
    "theme" VARCHAR(20) NULL  ,
    "page_lang" VARCHAR(5) NOT NULL DEFAULT '' ,
    "commented" DATETIME NULL  ,
    "description" VARCHAR(255) NOT NULL DEFAULT '' ,
    "keywords" VARCHAR(255) NOT NULL DEFAULT '' ,
    "footer_comments" TINYINT NULL  ,
    "footer_files" TINYINT NULL  ,
    "hide_toc" TINYINT NULL  ,
    "hide_index" TINYINT NULL  ,
    "tree_level" TINYINT NOT NULL DEFAULT '0' ,
    "show_menu_tag" TINYINT NOT NULL DEFAULT '0' ,
    "allow_rawhtml" TINYINT NULL  ,
    "disable_safehtml" TINYINT NULL  ,
    "typografica" TINYINT NULL  ,
    "noindex" TINYINT NULL DEFAULT '0' ,
    "deleted" TINYINT NULL DEFAULT '0'
);
 
CREATE INDEX "page_idx_user_id" ON "page" ("user_id");
 
CREATE INDEX "page_idx_title" ON "page" ("title");
 
CREATE UNIQUE INDEX "page_idx_tag" ON "page" ("tag");
 
CREATE INDEX "page_idx_reviewed" ON "page" ("reviewed");
 
CREATE INDEX "page_idx_owner_id" ON "page" ("owner_id");
 
CREATE INDEX "page_idx_modified" ON "page" ("modified");
 
CREATE INDEX "page_idx_minor_edit" ON "page" ("minor_edit");
 
CREATE INDEX "page_idx_depth" ON "page" ("depth");
 
CREATE INDEX "page_idx_deleted" ON "page" ("deleted");
 
CREATE INDEX "page_idx_created" ON "page" ("created");
 
CREATE INDEX "page_idx_comment_on_id" ON "page" ("comment_on_id");
 
CREATE INDEX "page_idx_commented" ON "page" ("commented");
 
 
DELIMITER ;;
CREATE TRIGGER page_ai AFTER INSERT ON page
    BEGIN
        INSERT INTO page_fts (rowid, title, body)
        VALUES (new.id, new.title, new.body);
    END;;
CREATE TRIGGER page_ad AFTER DELETE ON page
    BEGIN
        INSERT INTO page_fts (page_fts, rowid, title, body)
        VALUES ('delete', old.id, old.title, old.body);
    END;;
CREATE TRIGGER page_au AFTER UPDATE ON page BEGIN INSERT INTO page_fts (rowid, title, body)
        VALUES (new.page_id, new.title, new.body); END;;
 
DELIMITER ;
 
CREATE VIRTUAL TABLE page_fts USING fts5(
        page_id,
        owner_id,
        user_id,
        created, 
        modified,
        tag,
        title, 
        body,comment_on_id, page_lang,
        page_size UNINDEXED,
        comments UNINDEXED,
        content='page', 
        content_rowid='page_id');
 
 
CREATE TABLE "page_link" (
    "link_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "from_page_id" INTEGER NOT NULL DEFAULT '0' ,
    "to_page_id" INTEGER NOT NULL DEFAULT '0' ,
    "to_tag" VARCHAR(255) NOT NULL DEFAULT ''
);
 
CREATE INDEX "page_link_idx_to" ON "page_link" ("to_tag");
 
CREATE INDEX "page_link_idx_from_tag" ON "page_link" ("from_page_id", "to_tag");
 
CREATE INDEX "page_link_idx_from_page_id" ON "page_link" ("from_page_id");
 
 
CREATE TABLE "referrer" (
    "referrer_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "page_id" INTEGER NOT NULL DEFAULT '0' ,
    "referrer" VARCHAR(2083) NOT NULL DEFAULT '' ,
    "referrer_time" DATETIME NULL  ,
    "ip" VARCHAR(45) NOT NULL DEFAULT '' ,
    "user_agent" TEXT NULL
);
 
CREATE INDEX "referrer_idx_referrer_time" ON "referrer" ("referrer_time");
 
CREATE INDEX "referrer_idx_page_id" ON "referrer" ("page_id");
 
 
CREATE TABLE "revision" (
    "revision_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "page_id" INTEGER NOT NULL DEFAULT '0' ,
    "version_id" INTEGER NOT NULL DEFAULT '0' ,
    "owner_id" INTEGER NOT NULL DEFAULT '0' ,
    "user_id" INTEGER NOT NULL DEFAULT '0' ,
    "tag" VARCHAR(255) NOT NULL DEFAULT '' ,
    "title" VARCHAR(255) NOT NULL DEFAULT '' ,
    "menu_tag" VARCHAR(255) NOT NULL DEFAULT '' ,
    "created" DATETIME NULL  ,
    "modified" DATETIME NULL  ,
    "body" TEXT NOT NULL  ,
    "body_r" TEXT NOT NULL  ,
    "formatting" VARCHAR(20) NOT NULL DEFAULT '' ,
    "edit_note" VARCHAR(255) NOT NULL DEFAULT '' ,
    "minor_edit" TINYINT NULL DEFAULT '0' ,
    "page_size" INTEGER NOT NULL DEFAULT '0' ,
    "reviewed" TINYINT NOT NULL DEFAULT '0' ,
    "reviewed_time" DATETIME NULL  ,
    "reviewer_id" INTEGER NOT NULL DEFAULT '0' ,
    "latest" TINYINT NULL DEFAULT '0' ,
    "ip" VARCHAR(45) NOT NULL DEFAULT '' ,
    "handler" VARCHAR(30) NOT NULL DEFAULT 'page' ,
    "comment_on_id" INTEGER NOT NULL DEFAULT '0' ,
    "page_lang" VARCHAR(5) NOT NULL DEFAULT '' ,
    "description" VARCHAR(255) NOT NULL DEFAULT '' ,
    "keywords" VARCHAR(255) NOT NULL DEFAULT '' ,
    "deleted" TINYINT NULL DEFAULT '0'
);
 
CREATE INDEX "revision_idx_version_id" ON "revision" ("version_id");
 
CREATE INDEX "revision_idx_user_id" ON "revision" ("user_id");
 
CREATE INDEX "revision_idx_tag" ON "revision" ("tag");
 
CREATE INDEX "revision_idx_reviewed" ON "revision" ("reviewed");
 
CREATE INDEX "revision_idx_page_id" ON "revision" ("page_id");
 
CREATE INDEX "revision_idx_owner_id" ON "revision" ("owner_id");
 
CREATE INDEX "revision_idx_modified" ON "revision" ("modified");
 
CREATE INDEX "revision_idx_minor_edit" ON "revision" ("minor_edit");
 
CREATE INDEX "revision_idx_deleted" ON "revision" ("deleted");
 
CREATE INDEX "revision_idx_comment_on_id" ON "revision" ("comment_on_id");
 
 
CREATE TABLE "user" (
    "user_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "user_name" VARCHAR(80) NOT NULL DEFAULT '' ,
    "real_name" VARCHAR(80) NOT NULL DEFAULT '' ,
    "password" VARCHAR(255) NOT NULL DEFAULT '' ,
    "email" VARCHAR(254) NOT NULL DEFAULT '' ,
    "account_status" TINYINT NOT NULL DEFAULT '0' ,
    "account_type" TINYINT NOT NULL DEFAULT '0' ,
    "enabled" TINYINT NOT NULL DEFAULT '1' ,
    "signup_time" DATETIME NULL  ,
    "change_password" VARCHAR(64) NOT NULL DEFAULT '' ,
    "user_ip" VARCHAR(45) NOT NULL DEFAULT '' ,
    "email_confirm" VARCHAR(64) NOT NULL DEFAULT '' ,
    "last_visit" DATETIME NULL  ,
    "last_mark" DATETIME NULL  ,
    "login_count" INTEGER NOT NULL DEFAULT '0' ,
    "password_request_count" SMALLINT NOT NULL DEFAULT '0' ,
    "failed_login_count" SMALLINT NOT NULL DEFAULT '0' ,
    "total_pages" INTEGER NOT NULL DEFAULT '0' ,
    "total_revisions" INTEGER NOT NULL DEFAULT '0' ,
    "total_comments" INTEGER NOT NULL DEFAULT '0' ,
    "total_uploads" INTEGER NOT NULL DEFAULT '0'
);
 
CREATE UNIQUE INDEX "user_idx_user_name" ON "user" ("user_name");
 
CREATE INDEX "user_idx_signup_time" ON "user" ("signup_time");
 
CREATE INDEX "user_idx_enabled" ON "user" ("enabled");
 
CREATE INDEX "user_idx_account_type" ON "user" ("account_type");
 
 
CREATE TABLE "user_setting" (
    "setting_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "user_id" INTEGER NOT NULL DEFAULT '0' ,
    "theme" VARCHAR(20) NULL  ,
    "user_lang" VARCHAR(5) NOT NULL DEFAULT '' ,
    "list_count" INTEGER NOT NULL DEFAULT '50' ,
    "menu_items" INTEGER NOT NULL DEFAULT '5' ,
    "dont_redirect" TINYINT NULL  ,
    "send_watchmail" TINYINT NULL  ,
    "show_files" TINYINT NULL  ,
    "show_comments" TINYINT NOT NULL DEFAULT '1' ,
    "doubleclick_edit" TINYINT NOT NULL DEFAULT '1' ,
    "show_spaces" TINYINT NOT NULL DEFAULT '1' ,
    "autocomplete" TINYINT NOT NULL DEFAULT '0' ,
    "numerate_links" TINYINT NOT NULL DEFAULT '1' ,
    "diff_mode" TINYINT NOT NULL DEFAULT '3' ,
    "notify_minor_edit" TINYINT NOT NULL DEFAULT '1' ,
    "notify_page" TINYINT NOT NULL DEFAULT '2' ,
    "notify_comment" TINYINT NOT NULL DEFAULT '1' ,
    "allow_intercom" TINYINT NOT NULL DEFAULT '0' ,
    "allow_massemail" TINYINT NOT NULL DEFAULT '0' ,
    "hide_lastsession" TINYINT NULL  ,
    "validate_ip" TINYINT NULL  ,
    "noid_pubs" TINYINT NOT NULL DEFAULT '0' ,
    "session_length" TINYINT NULL  ,
    "timezone" VARCHAR(100) NOT NULL DEFAULT 'UTC' ,
    "date_preference" VARCHAR(10) NULL DEFAULT 'default' ,
    "sorting_comments" TINYINT NOT NULL DEFAULT '0' ,
    "comments_offset" TINYINT NOT NULL DEFAULT '0'
);
 
CREATE UNIQUE INDEX "user_setting_idx_user_id" ON "user_setting" ("user_id");
 
CREATE INDEX "user_setting_idx_send_watchmail" ON "user_setting" ("send_watchmail");
 
 
CREATE TABLE "usergroup" (
    "group_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "group_name" VARCHAR(100) NOT NULL DEFAULT '' ,
    "description" VARCHAR(255) NOT NULL DEFAULT '' ,
    "moderator_id" INTEGER NOT NULL DEFAULT '0' ,
    "created" DATETIME NULL  ,
    "is_system" TINYINT NOT NULL DEFAULT '0' ,
    "open" TINYINT NOT NULL DEFAULT '0' ,
    "active" TINYINT NOT NULL DEFAULT '0'
);
 
CREATE UNIQUE INDEX "usergroup_idx_name" ON "usergroup" ("group_name");
 
 
CREATE TABLE "usergroup_member" (
    "group_member_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "group_id" INTEGER NOT NULL DEFAULT '0' ,
    "user_id" INTEGER NOT NULL DEFAULT '0'
);
 
CREATE UNIQUE INDEX "usergroup_member_idx_group_id" ON "usergroup_member" ("group_id", "user_id");
 
 
CREATE TABLE "watch" (
    "watch_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "user_id" INTEGER NOT NULL DEFAULT '0' ,
    "page_id" INTEGER NOT NULL DEFAULT '0' ,
    "comment_id" INTEGER NOT NULL DEFAULT '0' ,
    "pending" TINYINT NOT NULL DEFAULT '0' ,
    "watch_time" DATETIME NULL
);
 
CREATE INDEX "watch_idx_user_id" ON "watch" ("user_id");
 
CREATE INDEX "watch_idx_page_id" ON "watch" ("page_id");
 
 
CREATE TABLE "word" (
    "word_id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "word" VARCHAR(255) NOT NULL DEFAULT '' ,
    "replacement" VARCHAR(255) NOT NULL DEFAULT ''
);