-- Enano - an open-source CMS capable of wiki functions, Drupal-like sidebar blocks, and everything in between
-- Version 1.0 (Banshee)
-- Copyright (C) 2006-2007 Dan Fuhry

-- This program is Free Software; you can redistribute and/or modify it under the terms of the GNU General Public License
-- as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.

-- This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied
-- warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for details.

-- schema.sql - MySQL installation schema. Firebird version in development.

CREATE TABLE {{TABLE_PREFIX}}categories(
  page_id varchar(64),
  namespace varchar(64),
  category_id varchar(64)
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

CREATE TABLE {{TABLE_PREFIX}}comments(
  comment_id int(12) NOT NULL auto_increment,
  page_id text,
  namespace text,
  subject text,
  comment_data text,
  name text,
  approved tinyint(1) default 1,
  user_id mediumint(8) NOT NULL DEFAULT -1,
  time int(12) NOT NULL DEFAULT 0,
  PRIMARY KEY ( comment_id )
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

CREATE TABLE {{TABLE_PREFIX}}config(
  config_name varchar(63),
  config_value text
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

CREATE TABLE {{TABLE_PREFIX}}logs(
  log_type varchar(16),
  action varchar(16),
  time_id int(12) NOT NULL default '0',
  date_string varchar(63),
  page_id text,
  namespace text,
  page_text text,
  char_tag varchar(40),
  author varchar(63),
  edit_summary text,
  minor_edit tinyint(1)
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

CREATE TABLE {{TABLE_PREFIX}}page_text(
  page_id varchar(63),
  namespace varchar(16) NOT NULL default 'Article',
  page_text text,
  char_tag varchar(63)
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

CREATE TABLE {{TABLE_PREFIX}}pages(
  page_order int(8),
  name varchar(127),
  urlname varchar(63),
  namespace varchar(16) NOT NULL default 'Article',
  special tinyint(1) default '0',
  visible tinyint(1) default '1',
  comments_on tinyint(1) default '1',
  protected tinyint(1) NOT NULL DEFAULT 0,
  wiki_mode tinyint(1) NOT NULL DEFAULT 2,
  delvotes int(10) NOT NULL default 0,
  password varchar(40) NOT NULL DEFAULT '',
  delvote_ips text NOT NULL
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

CREATE TABLE {{TABLE_PREFIX}}session_keys(
  session_key varchar(32),
  salt varchar(32),
  user_id mediumint(8),
  auth_level tinyint(1) NOT NULL default '0',
  source_ip varchar(10) default '0x7f000001',
  time bigint(15) default '0'
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

CREATE TABLE {{TABLE_PREFIX}}themes(
  theme_id varchar(63),
  theme_name text,
  theme_order smallint(5) NOT NULL default '1',
  default_style varchar(63) NOT NULL DEFAULT '',
  enabled tinyint(1) NOT NULL default '1'
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

CREATE TABLE {{TABLE_PREFIX}}users(
  user_id mediumint(8) NOT NULL auto_increment,
  username text,
  password varchar(255),
  email text,
  real_name text,
  user_level tinyint(1) NOT NULL default 2,
  theme varchar(64) NOT NULL default 'bleu.css',
  style varchar(64) NOT NULL default 'default',
  signature text,
  reg_time int(11) NOT NULL DEFAULT 0,
  account_active tinyint(1) NOT NULL DEFAULT 0,
  activation_key varchar(40) NOT NULL DEFAULT 0,
  old_encryption tinyint(1) NOT NULL DEFAULT 0,
  temp_password text,
  temp_password_time int(12) NOT NULL DEFAULT 0,
  user_coppa tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY  (user_id)
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

CREATE TABLE {{TABLE_PREFIX}}users_extra(
  user_id mediumint(8) NOT NULL,
  user_aim varchar(63),
  user_yahoo varchar(63),
  user_msn varchar(255),
  user_xmpp varchar(255),
  user_homepage text,
  user_location text,
  user_job text,
  user_hobbies text,
  email_public tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY ( user_id ) 
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

CREATE TABLE {{TABLE_PREFIX}}banlist(
  ban_id mediumint(8) NOT NULL auto_increment,
  ban_type tinyint(1),
  ban_value varchar(64),
  is_regex tinyint(1) DEFAULT 0,
  reason text,
  PRIMARY KEY ( ban_id ) 
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

CREATE TABLE {{TABLE_PREFIX}}files(
  file_id int(12) NOT NULL auto_increment,
  time_id int(12) NOT NULL,
  page_id varchar(63) NOT NULL,
  filename varchar(127) default NULL,
  size bigint(15) NOT NULL,
  mimetype varchar(63) default NULL,
  file_extension varchar(8) default NULL,
  file_key varchar(32) NOT NULL,
  PRIMARY KEY (file_id) 
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

CREATE TABLE {{TABLE_PREFIX}}buddies(
  buddy_id int(15) NOT NULL auto_increment,
  user_id mediumint(8),
  buddy_user_id mediumint(8),
  is_friend tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (buddy_id) 
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

CREATE TABLE {{TABLE_PREFIX}}privmsgs(
  message_id int(15) NOT NULL auto_increment,
  message_from varchar(63),
  message_to varchar(255),
  date int(12),
  subject varchar(63),
  message_text text,
  folder_name varchar(63),
  message_read tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY  (message_id) 
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

CREATE TABLE {{TABLE_PREFIX}}sidebar(
  item_id smallint(3) NOT NULL auto_increment,
  item_order smallint(3) NOT NULL DEFAULT 0,
  item_enabled tinyint(1) NOT NULL DEFAULT 1,
  sidebar_id smallint(3) NOT NULL DEFAULT 1,
  block_name varchar(63) NOT NULL,
  block_type tinyint(1) NOT NULL DEFAULT 0,
  block_content text,
  PRIMARY KEY ( item_id )
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

CREATE TABLE {{TABLE_PREFIX}}hits(
  hit_id bigint(20) NOT NULL auto_increment,
  username varchar(63) NOT NULL,
  time int(12) NOT NULL DEFAULT 0,
  page_id varchar(63),
  namespace varchar(63),
  PRIMARY KEY ( hit_id ) 
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

CREATE TABLE {{TABLE_PREFIX}}search_index(
  word varbinary(64) NOT NULL,
  page_names text,
  PRIMARY KEY ( word ) 
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

CREATE TABLE {{TABLE_PREFIX}}groups(
  group_id mediumint(5) UNSIGNED NOT NULL auto_increment,
  group_name varchar(64),
  group_type tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY ( group_id ),
  system_group tinyint(1) NOT NULL DEFAULT 0 
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

CREATE TABLE {{TABLE_PREFIX}}group_members(
  member_id int(12) UNSIGNED NOT NULL auto_increment,
  group_id mediumint(5) UNSIGNED NOT NULL,
  user_id int(12) NOT NULL,
  is_mod tinyint(1) NOT NULL DEFAULT 0,
  pending tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY ( member_id ) 
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

CREATE TABLE {{TABLE_PREFIX}}acl(
  rule_id int(12) UNSIGNED NOT NULL auto_increment,
  target_type tinyint(1) UNSIGNED NOT NULL,
  target_id int(12) UNSIGNED NOT NULL,
  page_id varchar(255),
  namespace varchar(24),
  rules text,
  PRIMARY KEY ( rule_id ) 
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

CREATE TABLE {{TABLE_PREFIX}}search_cache(
  search_id int(15) NOT NULL auto_increment,
  search_time int(11) NOT NULL,
  query text,
  results longblob,
  PRIMARY KEY ( search_id )
) CHARACTER SET `utf8` COLLATE `utf8_bin`;

INSERT INTO {{TABLE_PREFIX}}config(config_name, config_value) VALUES
  ('site_name', '{{SITE_NAME}}'),
  ('main_page', 'Main_Page'),
  ('site_desc', '{{SITE_DESC}}'),
  ('wiki_mode', '{{WIKI_MODE}}'),
  ('wiki_edit_notice', '0'),
  ('sflogo_enabled', '0'),
  ('sflogo_groupid', ''),
  ('sflogo_type', '1'),
  ('w3c_vh32', '0'),
  ('w3c_vh40', '0'),
  ('w3c_vh401', '0'),
  ('w3c_vxhtml10', '0'),
  ('w3c_vxhtml11', '0'),
  ('w3c_vcss', '0'),
  ('approve_comments', '0'),
  ('enable_comments', '1'),
  ('plugin_SpecialAdmin.php', '1'),
  ('plugin_SpecialPageFuncs.php', '1'),
  ('plugin_SpecialUserFuncs.php', '1'),
  ('plugin_SpecialCSS.php', '1'),
  ('copyright_notice', '{{COPYRIGHT}}'),
  ('wiki_edit_notice_text', '== Why can I edit this page? ==\n\nEveryone can edit almost any page in this website. This concept is called a wiki. It gives everyone the opportunity to make a change for the best. While some spam and vandalism may occur, it is believed that most contributions will be legitimate and helpful.\n\nFor security purposes, a history of all page edits is kept, and administrators are able to restore vandalized or spammed pages with just a few clicks.'),
  ('cache_thumbs', '{{ENABLE_CACHE}}'),
  ('max_file_size', '256000'),('enano_version', '{{VERSION}}'),( 'allowed_mime_types', 'cbf:len=168;crc=c3dcad3f;data=0[1],1[4],0[3],1[1],0[2],1[1],0[11],1[1],0[7],1[1],0[9],1[1],0[6],1[3],0[10],1[1],0[2],1[2],0[1],1[1],0[1],1[2],0[6],1[3],0[1],1[1],0[2],1[4],0[1],1[2],0[3],1[1],0[4],1[2],0[26],1[5],0[6],1[2],0[2],1[1],0[4],1[1],0[10],1[2],0[1],1[1],0[6]|end' ),
  ('contact_email', '{{ADMIN_EMAIL}}'),
  ('powered_btn', '1');

INSERT INTO {{TABLE_PREFIX}}page_text(page_id, namespace, page_text, char_tag) VALUES
  ('Main_Page', 'Article', '=== Enano has been successfully installed! ===\n\nYou have finished installing Enano on this server. Congratulations!', '');

INSERT INTO {{TABLE_PREFIX}}pages(page_order, name, urlname, namespace, special, visible, comments_on, protected, delvotes, delvote_ips) VALUES
  (NULL, 'Main Page', 'Main_Page', 'Article', 0, 1, 1, 1, 0, '');

INSERT INTO {{TABLE_PREFIX}}themes(theme_id, theme_name, theme_order, default_style, enabled) VALUES
  ('oxygen', 'Oxygen', 1, 'bleu.css', 1),
  ('stpatty', 'St. Patty', 2, 'shamrock.css', 1);

INSERT INTO {{TABLE_PREFIX}}users(user_id, username, password, email, real_name, user_level, theme, style, signature, reg_time, account_active) VALUES
  (1, 'Anonymous', 'invalid-pass-hash', 'anonspam@enanocms.org', 'None', 1, 'oxygen', 'bleu', '', 0, 0),
  (2, '{{ADMIN_USER}}', '{{ADMIN_PASS}}', '{{ADMIN_EMAIL}}', '{{REAL_NAME}}', 9, 'oxygen', 'bleu', '', UNIX_TIMESTAMP(), 1);
  
INSERT INTO {{TABLE_PREFIX}}users_extra(user_id) VALUES
  (2);

INSERT INTO {{TABLE_PREFIX}}groups(group_id,group_name,group_type,system_group) VALUES(1, 'Everyone', 3, 1),
  (2,'Administrators',3,1),
  (3,'Moderators',3,1);

INSERT INTO {{TABLE_PREFIX}}group_members(group_id,user_id,is_mod) VALUES(2, 2, 1);

INSERT INTO {{TABLE_PREFIX}}acl(target_type,target_id,page_id,namespace,rules) VALUES(1,2,NULL,NULL,'read=4;post_comments=4;edit_comments=4;edit_page=4;view_source=4;mod_comments=4;history_view=4;history_rollback=4;history_rollback_extra=4;protect=4;rename=4;clear_logs=4;vote_delete=4;vote_reset=4;delete_page=4;set_wiki_mode=4;password_set=4;password_reset=4;mod_misc=4;edit_cat=4;even_when_protected=4;upload_files=4;upload_new_version=4;create_page=4;php_in_pages={{ADMIN_EMBED_PHP}};edit_acl=4;'),
  (1,3,NULL,NULL,'read=4;post_comments=4;edit_comments=4;edit_page=4;view_source=4;mod_comments=4;history_view=4;history_rollback=4;history_rollback_extra=4;protect=4;rename=3;clear_logs=2;vote_delete=4;vote_reset=4;delete_page=4;set_wiki_mode=2;password_set=2;password_reset=2;mod_misc=2;edit_cat=4;even_when_protected=4;upload_files=2;upload_new_version=3;create_page=3;php_in_pages=2;edit_acl=2;');

INSERT INTO {{TABLE_PREFIX}}sidebar(item_id, item_order, sidebar_id, block_name, block_type, block_content) VALUES
  (1, 1, 1, 'Navigation', 1, '[[Main Page|Home]]'),
  (2, 2, 1, 'Tools', 1, '[[$NS_SPECIAL$CreatePage|Create a page]]\n[[$NS_SPECIAL$UploadFile|Upload file]]\n[[$NS_SPECIAL$SpecialPages|Special pages]]\n{if auth_admin}\n[[$NS_SPECIAL$EditSidebar|Edit the sidebar]]\n$ADMIN_LINK$\n{/if}'),
  (3, 3, 1, '$USERNAME$', 1, '[[$NS_USER$$USERNAME$|User page]]\n[[$NS_SPECIAL$Contributions/$USERNAME$|My Contributions]]\n{if user_logged_in}\n[[$NS_SPECIAL$Preferences|Preferences]]\n[[$NS_SPECIAL$PrivateMessages|Private messages]]\n[[$NS_SPECIAL$Usergroups|Group control panel]]\n$THEME_LINK$\n{/if}\n{if user_logged_in}\n$LOGOUT_LINK$\n{else}\n[[$NS_SPECIAL$Register|Create an account]]\n$LOGIN_LINK$\n[[$NS_SPECIAL$Login/$NS_SPECIAL$PrivateMessages|Private messages]]\n{/if}'),
  (4, 4, 1, 'Search', 1, '<div class="slideblock2" style="padding: 0px;"><form action="$SCRIPTPATH$/$NS_SPECIAL$Search" method="get" style="padding: 0; margin: 0;"><p><input name="q" alt="Search box" type="text" size="10" style="width: 70%" /> <input type="submit" value="Go" style="width: 20%" /></p></form></div>'),
  (5, 2, 2, 'Links', 4, 'Links');