CREATE TABLE {{TABLE_PREFIX}}bans (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    username VARCHAR(200),
    ip VARCHAR(255),
    email VARCHAR(80),
    message VARCHAR(255),
    expire INT(10) UNSIGNED,
    ban_creator INT(10) UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
  ) ENGINE = MyISAM CHARACTER SET utf8;

CREATE TABLE {{TABLE_PREFIX}}categories (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    cat_name VARCHAR(80) NOT NULL DEFAULT 'New Category',
    disp_position INT(10) NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
  ) ENGINE = MyISAM CHARACTER SET utf8;

CREATE TABLE {{TABLE_PREFIX}}censoring (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    search_for VARCHAR(60) NOT NULL DEFAULT '',
    replace_with VARCHAR(60) NOT NULL DEFAULT '',
    PRIMARY KEY (id)
  ) ENGINE = MyISAM CHARACTER SET utf8;

CREATE TABLE {{TABLE_PREFIX}}config (
    conf_name VARCHAR(255) NOT NULL DEFAULT '',
    conf_value TEXT,
    PRIMARY KEY (conf_name)
  ) ENGINE = MyISAM CHARACTER SET utf8;

CREATE TABLE {{TABLE_PREFIX}}extensions (
    id VARCHAR(50) NOT NULL DEFAULT '',
    title VARCHAR(255) NOT NULL DEFAULT '',
    version VARCHAR(25) NOT NULL DEFAULT '',
    description TEXT,
    author VARCHAR(50) NOT NULL DEFAULT '',
    uninstall TEXT,
    uninstall_note TEXT,
    disabled TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY(id)
  ) ENGINE = MyISAM CHARACTER SET utf8;

CREATE TABLE {{TABLE_PREFIX}}extension_hooks (
    id VARCHAR(50) NOT NULL DEFAULT '',
    extension_id VARCHAR(50) NOT NULL DEFAULT '',
    code TEXT,
    installed INT(10) UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY(id, extension_id)
  ) ENGINE = MyISAM CHARACTER SET utf8;

CREATE TABLE {{TABLE_PREFIX}}forum_perms (
    group_id INT(10) NOT NULL DEFAULT 0,
    forum_id INT(10) NOT NULL DEFAULT 0,
    read_forum TINYINT(1) NOT NULL DEFAULT 1,
    post_replies TINYINT(1) NOT NULL DEFAULT 1,
    post_topics TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (group_id, forum_id)
  ) ENGINE = MyISAM CHARACTER SET utf8;

CREATE TABLE {{TABLE_PREFIX}}forums (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    forum_name VARCHAR(80) NOT NULL DEFAULT 'New forum',
    forum_desc TEXT,
    redirect_url VARCHAR(100),
    moderators TEXT,
    num_topics MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT 0,
    num_posts MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT 0,
    last_post INT(10) UNSIGNED,
    last_post_id INT(10) UNSIGNED,
    last_poster VARCHAR(200),
    sort_by TINYINT(1) NOT NULL DEFAULT 0,
    disp_position INT(10) NOT NULL DEFAULT 0,
    cat_id INT(10) UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
  ) ENGINE = MyISAM CHARACTER SET utf8;

CREATE TABLE {{TABLE_PREFIX}}groups (
    g_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    g_title VARCHAR(50) NOT NULL DEFAULT '',
    g_user_title VARCHAR(50),
    g_moderator TINYINT(1) NOT NULL DEFAULT 0,
    g_mod_edit_users TINYINT(1) NOT NULL DEFAULT 0,
    g_mod_rename_users TINYINT(1) NOT NULL DEFAULT 0,
    g_mod_change_passwords TINYINT(1) NOT NULL DEFAULT 0,
    g_mod_ban_users TINYINT(1) NOT NULL DEFAULT 0,
    g_read_board TINYINT(1) NOT NULL DEFAULT 1,
    g_view_users TINYINT(1) NOT NULL DEFAULT 1,
    g_post_replies TINYINT(1) NOT NULL DEFAULT 1,
    g_post_topics TINYINT(1) NOT NULL DEFAULT 1,
    g_edit_posts TINYINT(1) NOT NULL DEFAULT 1,
    g_delete_posts TINYINT(1) NOT NULL DEFAULT 1,
    g_delete_topics TINYINT(1) NOT NULL DEFAULT 1,
    g_set_title TINYINT(1) NOT NULL DEFAULT 1,
    g_search TINYINT(1) NOT NULL DEFAULT 1,
    g_search_users TINYINT(1) NOT NULL DEFAULT 1,
    g_edit_subjects_interval SMALLINT(6) NOT NULL DEFAULT 300,
    g_post_flood SMALLINT(6) NOT NULL DEFAULT 30,
    g_search_flood SMALLINT(6) NOT NULL DEFAULT 30,
    PRIMARY KEY (g_id)
  ) ENGINE = MyISAM CHARACTER SET utf8;

CREATE TABLE {{TABLE_PREFIX}}online (
    user_id INT(10) UNSIGNED NOT NULL DEFAULT 1,
    ident VARCHAR(200) NOT NULL DEFAULT '',
    logged INT(10) UNSIGNED NOT NULL DEFAULT 0,
    idle TINYINT(1) NOT NULL DEFAULT 0,
    csrf_token VARCHAR(40) NOT NULL DEFAULT '',
    prev_url VARCHAR(255)
  ) TYPE=HEAP;

CREATE TABLE {{TABLE_PREFIX}}posts (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    poster VARCHAR(200) NOT NULL DEFAULT '',
    poster_id INT(10) UNSIGNED NOT NULL DEFAULT 1,
    poster_ip VARCHAR(15),
    poster_email VARCHAR(80),
    message TEXT,
    hide_smilies TINYINT(1) NOT NULL DEFAULT 0,
    posted INT(10) UNSIGNED NOT NULL DEFAULT 0,
    edited INT(10) UNSIGNED,
    edited_by VARCHAR(200),
    topic_id INT(10) UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
  ) ENGINE = MyISAM CHARACTER SET utf8;

CREATE TABLE {{TABLE_PREFIX}}ranks (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    rank VARCHAR(50) NOT NULL DEFAULT '',
    min_posts MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
  ) ENGINE = MyISAM CHARACTER SET utf8;

CREATE TABLE {{TABLE_PREFIX}}reports (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    post_id INT(10) UNSIGNED NOT NULL DEFAULT 0,
    topic_id INT(10) UNSIGNED NOT NULL DEFAULT 0,
    forum_id INT(10) UNSIGNED NOT NULL DEFAULT 0,
    reported_by INT(10) UNSIGNED NOT NULL DEFAULT 0,
    created INT(10) UNSIGNED NOT NULL DEFAULT 0,
    message TEXT,
    zapped INT(10) UNSIGNED,
    zapped_by INT(10) UNSIGNED,
    PRIMARY KEY (id)
  ) ENGINE = MyISAM CHARACTER SET utf8;

CREATE TABLE {{TABLE_PREFIX}}subscriptions (
    user_id INT(10) UNSIGNED NOT NULL DEFAULT 0,
    topic_id INT(10) UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (user_id, topic_id)
  ) ENGINE = MyISAM CHARACTER SET utf8;

CREATE TABLE {{TABLE_PREFIX}}topics (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    poster VARCHAR(200) NOT NULL DEFAULT '',
    subject VARCHAR(255) NOT NULL DEFAULT '',
    posted INT(10) UNSIGNED NOT NULL DEFAULT 0,
    first_post_id INT(10) UNSIGNED NOT NULL DEFAULT 0,
    last_post INT(10) UNSIGNED NOT NULL DEFAULT 0,
    last_post_id INT(10) UNSIGNED NOT NULL DEFAULT 0,
    last_poster VARCHAR(200),
    num_views MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT 0,
    num_replies MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT 0,
    closed TINYINT(1) NOT NULL DEFAULT 0,
    sticky TINYINT(1) NOT NULL DEFAULT 0,
    moved_to INT(10) UNSIGNED,
    forum_id INT(10) UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
  ) ENGINE = MyISAM CHARACTER SET utf8;

CREATE TABLE {{TABLE_PREFIX}}users (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    group_id INT(10) UNSIGNED NOT NULL DEFAULT 4,
    username VARCHAR(200) NOT NULL DEFAULT '',
    password VARCHAR(40) NOT NULL DEFAULT '',
    salt VARCHAR(12),
    email VARCHAR(80) NOT NULL DEFAULT '',
    title VARCHAR(50),
    realname VARCHAR(40),
    url VARCHAR(100),
    jabber VARCHAR(80),
    icq VARCHAR(12),
    msn VARCHAR(80),
    aim VARCHAR(30),
    yahoo VARCHAR(30),
    location VARCHAR(30),
    signature TEXT,
    disp_topics TINYINT(3) UNSIGNED,
    disp_posts TINYINT(3) UNSIGNED,
    email_setting TINYINT(1) NOT NULL DEFAULT 1,
    save_pass TINYINT(1) NOT NULL DEFAULT 1,
    notify_with_post TINYINT(1) NOT NULL DEFAULT 0,
    auto_notify TINYINT(1) NOT NULL DEFAULT 0,
    show_smilies TINYINT(1) NOT NULL DEFAULT 1,
    show_img TINYINT(1) NOT NULL DEFAULT 1,
    show_img_sig TINYINT(1) NOT NULL DEFAULT 1,
    show_avatars TINYINT(1) NOT NULL DEFAULT 1,
    show_sig TINYINT(1) NOT NULL DEFAULT 1,
    timezone FLOAT NOT NULL DEFAULT 0,
    dst TINYINT(1) NOT NULL DEFAULT 0,
    time_format INT(10) UNSIGNED NOT NULL DEFAULT 0,
    date_format INT(10) UNSIGNED NOT NULL DEFAULT 0,
    language VARCHAR(25) NOT NULL DEFAULT 'English',
    style VARCHAR(25) NOT NULL DEFAULT 'Oxygen',
    num_posts INT(10) UNSIGNED NOT NULL DEFAULT 0,
    last_post INT(10) UNSIGNED,
    registered INT(10) UNSIGNED NOT NULL DEFAULT 0,
    registration_ip VARCHAR(15) NOT NULL DEFAULT '0.0.0.0',
    last_visit INT(10) UNSIGNED NOT NULL DEFAULT 0,
    admin_note VARCHAR(30),
    activate_string VARCHAR(80),
    activate_key VARCHAR(8),
    PRIMARY KEY (id)
  ) ENGINE = MyISAM CHARACTER SET utf8;

ALTER TABLE {{TABLE_PREFIX}}online ADD UNIQUE INDEX {{TABLE_PREFIX}}online_user_id_ident_idx(user_id,ident(25));
ALTER TABLE {{TABLE_PREFIX}}online ADD INDEX {{TABLE_PREFIX}}online_user_id_idx(user_id);
ALTER TABLE {{TABLE_PREFIX}}posts ADD INDEX {{TABLE_PREFIX}}posts_topic_id_idx(topic_id);
ALTER TABLE {{TABLE_PREFIX}}posts ADD INDEX {{TABLE_PREFIX}}posts_multi_idx(poster_id, topic_id);
ALTER TABLE {{TABLE_PREFIX}}posts ADD FULLTEXT {{TABLE_PREFIX}}posts_message_idx(message);
ALTER TABLE {{TABLE_PREFIX}}reports ADD INDEX {{TABLE_PREFIX}}reports_zapped_idx(zapped);
ALTER TABLE {{TABLE_PREFIX}}topics ADD INDEX {{TABLE_PREFIX}}topics_forum_id_idx(forum_id);
ALTER TABLE {{TABLE_PREFIX}}topics ADD INDEX {{TABLE_PREFIX}}topics_moved_to_idx(moved_to);
ALTER TABLE {{TABLE_PREFIX}}topics ADD INDEX {{TABLE_PREFIX}}topics_last_post_idx(last_post);
ALTER TABLE {{TABLE_PREFIX}}topics ADD INDEX {{TABLE_PREFIX}}topics_first_post_id_idx(first_post_id);
ALTER TABLE {{TABLE_PREFIX}}topics ADD FULLTEXT {{TABLE_PREFIX}}topics_subject_idx(subject);
ALTER TABLE {{TABLE_PREFIX}}users ADD INDEX {{TABLE_PREFIX}}users_registered_idx(registered);
ALTER TABLE {{TABLE_PREFIX}}users ADD INDEX {{TABLE_PREFIX}}users_username_idx(username(8));

INSERT INTO {{TABLE_PREFIX}}config ( conf_name, conf_value ) VALUES
  ('o_cur_version', '{{PUN_VERSION}}'),
  ('o_board_title', 'My PunBB Forum'),
  ('o_board_desc', 'Unfortunately no one can be told what PunBB is - you have to see it for yourself.'),
  ('o_default_timezone', '0'),
  ('o_time_format', 'H:i:s'),
  ('o_date_format', 'Y-m-d'),
  ('o_check_for_updates', '0'),
  ('o_timeout_visit', '1800'),
  ('o_timeout_online', '300'),
  ('o_redirect_delay', '1'),
  ('o_show_version', '0'),
  ('o_show_user_info', '1'),
  ('o_show_post_count', '1'),
  ('o_signatures', '1'),
  ('o_smilies', '1'),
  ('o_smilies_sig', '1'),
  ('o_make_links', '1'),
  ('o_default_lang', 'English'),
  ('o_default_style', 'Oxygen'),
  ('o_default_user_group', '3'),
  ('o_topic_review', '15'),
  ('o_disp_topics_default', '30'),
  ('o_disp_posts_default', '25'),
  ('o_indent_num_spaces', '4'),
  ('o_quickpost', '1'),
  ('o_users_online', '1'),
  ('o_censoring', '0'),
  ('o_ranks', '1'),
  ('o_show_dot', '0'),
  ('o_topic_views', '1'),
  ('o_quickjump', '1'),
  ('o_gzip', '0'),
  ('o_additional_navlinks', ''),
  ('o_report_method', '0'),
  ('o_regs_report', '0'),
  ('o_mailing_list', '{{ENANO_ADMIN_EMAIL}}'),
  ('o_avatars', '0'),
  ('o_avatars_dir', 'img/avatars'),
  ('o_avatars_width', '60'),
  ('o_avatars_height', '60'),
  ('o_avatars_size', '10240'),
  ('o_search_all_forums', '1'),
  ('o_sef', 'Default'),
  ('o_admin_email', '{{ENANO_ADMIN_EMAIL}}'),
  ('o_webmaster_email', '{{ENANO_ADMIN_EMAIL}}'),
  ('o_subscriptions', '1'),
  ('o_smtp_host', NULL),
  ('o_smtp_user', NULL),
  ('o_smtp_pass', NULL),
  ('o_smtp_ssl', '0'),
  ('o_regs_allow', '1'),
  ('o_regs_verify', '0'),
  ('o_announcement', '0'),
  ('o_announcement_heading', 'Sample announcement'),
  ('o_announcement_message', '<p>Enter your announcement here.</p>'),
  ('o_rules', '0'),
  ('o_rules_message', 'Enter your rules here.'),
  ('o_maintenance', '0'),
  ('o_maintenance_message', 'The forums are temporarily down for maintenance. Please try again in a few minutes.<br />\\n<br />\\n/Administrator'),
  ('p_message_bbcode', '1'),
  ('p_message_img_tag', '1'),
  ('p_message_all_caps', '1'),
  ('p_subject_all_caps', '1'),
  ('p_sig_all_caps', '1'),
  ('p_sig_bbcode', '1'),
  ('p_sig_img_tag', '0'),
  ('p_sig_length', '400'),
  ('p_sig_lines', '4'),
  ('p_allow_banned_email', '1'),
  ('p_allow_dupe_email', '0'),
  ('p_force_guest_email', '1');
  
INSERT INTO {{TABLE_PREFIX}}groups (g_title, g_user_title, g_moderator, g_mod_edit_users, g_mod_rename_users, g_mod_change_passwords, g_mod_ban_users, g_read_board, g_view_users, g_post_replies, g_post_topics, g_edit_posts, g_delete_posts, g_delete_topics, g_set_title, g_search, g_search_users, g_edit_subjects_interval, g_post_flood, g_search_flood) VALUES
  ('Administrators', 'Administrator', 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0),
  ('Guest', NULL, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0),
  ('Members', NULL, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1, 1, 0, 1, 1, 300, 60, 30),
  ('Moderators', 'Moderator', 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 0, 0, 0);

INSERT INTO {{TABLE_PREFIX}}forums (forum_name, forum_desc, num_topics, num_posts, last_post, last_post_id, last_poster, disp_position, cat_id) VALUES('Test forum', 'This is just a test forum', 1, 1, {{NOW}}, 1, 'Enano', 1, 1);
INSERT INTO {{TABLE_PREFIX}}topics (poster, subject, posted, first_post_id, last_post, last_post_id, last_poster, forum_id) VALUES('Enano', 'Test post', {{NOW}}, 1, {{NOW}}, 1, 'Enano', 1);
INSERT INTO {{TABLE_PREFIX}}posts (poster, poster_id, poster_ip, message, posted, topic_id) VALUES('Enano', 2, '127.0.0.1', 'If you are looking at this (which I guess you are), the install of PunBB appears to have worked! Now log in and head over to the administration control panel to configure your forum.', {{NOW}}, 1);
INSERT INTO {{TABLE_PREFIX}}ranks (rank, min_posts) VALUES('New member', 0);
INSERT INTO {{TABLE_PREFIX}}ranks (rank, min_posts) VALUES('Member', 10);
INSERT INTO {{TABLE_PREFIX}}users (id, group_id, username, password, email) VALUES(1, 2, 'Guest', 'Guest', 'Guest');