misuzu/database/2023_01_07_023235_initial_structure_ndx.php

<?php
use Index\Db\DbConnection;
use Index\Db\Migration\DbMigration;

// Switching to the Index migration system!!!!!!

final class InitialStructureNdx_20230107_023235 implements DbMigration {
    public function migrate(DbConnection $conn): void {
        $hasMszTrack = false;

        // check if the old migrations table exists
        $tables = $conn->query('SHOW TABLES');
        while($tables->next())
            if($tables->getString(0) === 'msz_migrations') {
                $hasMszTrack = true;
                break;
            }

        // nuke it and bail
        if($hasMszTrack) {
            $conn->execute('DROP TABLE msz_migrations');
            return;
        }

        $conn->execute('
            CREATE TABLE msz_roles (
                role_id          INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                role_hierarchy   INT(11)          NOT NULL DEFAULT 1,
                role_name        VARCHAR(255)     NOT NULL,
                role_title       VARCHAR(64)               DEFAULT NULL,
                role_description TEXT                      DEFAULT NULL,
                role_hidden      TINYINT(1)       NOT NULL DEFAULT 0,
                role_can_leave   TINYINT(1)       NOT NULL DEFAULT 0,
                role_colour      INT(11)                   DEFAULT NULL,
                role_created     TIMESTAMP        NOT NULL DEFAULT current_timestamp(),
                PRIMARY KEY (role_id),
                KEY roles_hierarchy_index (role_hierarchy),
                KEY roles_hidden_index    (role_hidden)
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_users (
                user_id                  INT(10) UNSIGNED    NOT NULL AUTO_INCREMENT,
                username                 VARCHAR(255)        NOT NULL,
                password                 VARCHAR(255)                 DEFAULT NULL,
                email                    VARCHAR(255)        NOT NULL,
                register_ip              VARBINARY(16)       NOT NULL,
                last_ip                  VARBINARY(16)       NOT NULL,
                user_super               TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,
                user_country             CHAR(2)             NOT NULL DEFAULT \'XX\',
                user_colour              INT(11)                      DEFAULT NULL,
                user_created             TIMESTAMP           NOT NULL DEFAULT current_timestamp(),
                user_active              TIMESTAMP           NULL     DEFAULT NULL,
                user_deleted             TIMESTAMP           NULL     DEFAULT NULL,
                display_role             INT(10) UNSIGNED             DEFAULT NULL,
                user_totp_key            CHAR(26)                     DEFAULT NULL,
                user_about_content       TEXT                         DEFAULT NULL,
                user_about_parser        TINYINT(4)          NOT NULL DEFAULT 0,
                user_signature_content   TEXT                         DEFAULT NULL,
                user_signature_parser    TINYINT(4)          NOT NULL DEFAULT 0,
                user_birthdate           DATE                         DEFAULT NULL,
                user_background_settings TINYINT(4)                   DEFAULT 0,
                user_title               VARCHAR(64)                  DEFAULT NULL,
                PRIMARY KEY (user_id),
                UNIQUE KEY users_username_unique      (username),
                UNIQUE KEY users_email_unique         (email),
                KEY        users_display_role_foreign (display_role),
                KEY        users_indices              (user_country, user_created, user_active, user_deleted, user_birthdate),
                CONSTRAINT users_display_role_foreign
                    FOREIGN KEY (display_role)
                    REFERENCES msz_roles (role_id)
                    ON DELETE SET NULL
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_audit_log (
                user_id     INT(10) UNSIGNED          DEFAULT NULL,
                log_action  VARCHAR(50)      NOT NULL,
                log_params  TEXT             NOT NULL,
                log_created TIMESTAMP        NOT NULL DEFAULT current_timestamp(),
                log_ip      VARBINARY(16)             DEFAULT NULL,
                log_country CHAR(2)          NOT NULL DEFAULT \'XX\',
                KEY audit_log_user_id_foreign (user_id),
                KEY audit_log_created_index (log_created),
                CONSTRAINT audit_log_user_id_foreign
                    FOREIGN KEY (user_id)
                    REFERENCES msz_users (user_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_auth_tfa (
                user_id     INT(10) UNSIGNED NOT NULL,
                tfa_token   CHAR(32)         NOT NULL,
                tfa_created TIMESTAMP        NOT NULL DEFAULT current_timestamp(),
                UNIQUE KEY auth_tfa_token_unique  (tfa_token),
                KEY        auth_tfa_user_foreign  (user_id),
                KEY        auth_tfa_created_index (tfa_created),
                CONSTRAINT auth_tfa_user_foreign
                    FOREIGN KEY (user_id)
                    REFERENCES msz_users (user_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_changelog_changes (
                change_id      INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                user_id        INT(10) UNSIGNED          DEFAULT NULL,
                change_action  INT(10) UNSIGNED          DEFAULT NULL,
                change_created TIMESTAMP        NOT NULL DEFAULT current_timestamp(),
                change_log     VARCHAR(255)     NOT NULL,
                change_text    TEXT                      DEFAULT NULL,
                PRIMARY KEY (change_id),
                KEY changes_user_foreign  (user_id),
                KEY changes_action_index  (change_action),
                KEY changes_created_index (change_created),
                CONSTRAINT changes_user_id_foreign
                    FOREIGN KEY (user_id)
                    REFERENCES msz_users (user_id)
                    ON DELETE SET NULL
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_changelog_tags (
                tag_id          INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                tag_name        VARCHAR(255)     NOT NULL,
                tag_description TEXT                      DEFAULT NULL,
                tag_created     TIMESTAMP        NOT NULL DEFAULT current_timestamp(),
                tag_archived    TIMESTAMP        NULL     DEFAULT NULL,
                PRIMARY KEY (tag_id),
                UNIQUE KEY tag_name     (tag_name),
                KEY        tag_archived (tag_archived)
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_changelog_change_tags (
                change_id INT(10) UNSIGNED NOT NULL,
                tag_id    INT(10) UNSIGNED NOT NULL,
                UNIQUE KEY change_tag_unique  (change_id, tag_id),
                KEY        tag_id_foreign_key (tag_id),
                CONSTRAINT change_id_foreign_key
                    FOREIGN KEY (change_id)
                    REFERENCES msz_changelog_changes (change_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE,
                CONSTRAINT tag_id_foreign_key
                    FOREIGN KEY (tag_id)
                    REFERENCES msz_changelog_tags (tag_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_comments_categories (
                category_id      INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                category_name    VARCHAR(255)     NOT NULL,
                owner_id         INT(10) UNSIGNED          DEFAULT NULL,
                category_created TIMESTAMP        NOT NULL DEFAULT current_timestamp(),
                category_locked  TIMESTAMP        NULL     DEFAULT NULL,
                PRIMARY KEY (category_id),
                UNIQUE KEY comments_categories_name_unique   (category_name),
                KEY        comments_categories_locked_index  (category_locked),
                KEY        comments_categories_owner_foreign (owner_id),
                CONSTRAINT comments_categories_owner_foreign
                    FOREIGN KEY (owner_id)
                    REFERENCES msz_users (user_id)
                    ON DELETE SET NULL
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_comments_posts (
                comment_id       INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                category_id      INT(10) UNSIGNED NOT NULL,
                user_id          INT(10) UNSIGNED          DEFAULT NULL,
                comment_reply_to INT(10) UNSIGNED          DEFAULT NULL,
                comment_text     TEXT             NOT NULL,
                comment_created  TIMESTAMP        NOT NULL DEFAULT current_timestamp(),
                comment_pinned   TIMESTAMP        NULL     DEFAULT NULL,
                comment_edited   TIMESTAMP        NULL     DEFAULT NULL ON UPDATE current_timestamp(),
                comment_deleted  TIMESTAMP        NULL     DEFAULT NULL,
                PRIMARY KEY (comment_id),
                KEY comments_posts_category_foreign (category_id),
                KEY comments_posts_user_foreign     (user_id),
                KEY comments_posts_reply_id         (comment_reply_to),
                KEY comments_posts_dates            (comment_created, comment_pinned, comment_edited, comment_deleted),
                CONSTRAINT comments_posts_category_foreign
                    FOREIGN KEY (category_id)
                    REFERENCES msz_comments_categories (category_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE,
                CONSTRAINT comments_posts_user_foreign
                    FOREIGN KEY (user_id)
                    REFERENCES msz_users (user_id)
                    ON DELETE SET NULL
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_comments_votes (
                comment_id   INT(10) UNSIGNED NOT NULL,
                user_id      INT(10) UNSIGNED NOT NULL,
                comment_vote TINYINT(4)       NOT NULL DEFAULT 0,
                UNIQUE KEY comments_vote_unique       (comment_id, user_id),
                KEY        comments_vote_user_foreign (user_id),
                KEY        comments_vote_index        (comment_vote),
                CONSTRAINT comment_vote_id
                    FOREIGN KEY (comment_id)
                    REFERENCES msz_comments_posts (comment_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE,
                CONSTRAINT comment_vote_user
                    FOREIGN KEY (user_id)
                    REFERENCES msz_users (user_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_config (
                config_name varchar(100) NOT NULL,
                config_value blob NOT NULL DEFAULT \'\',
                PRIMARY KEY (config_name)
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_emoticons (
                emote_id        INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                emote_order     MEDIUMINT(9)     NOT NULL DEFAULT 0,
                emote_hierarchy INT(11)          NOT NULL DEFAULT 0,
                emote_url       VARCHAR(255)     NOT NULL,
                PRIMARY KEY (emote_id),
                UNIQUE KEY emotes_url       (emote_url),
                KEY        emotes_order     (emote_order),
                KEY        emotes_hierarchy (emote_hierarchy)
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_emoticons_strings (
                emote_id           INT(10) UNSIGNED NOT NULL,
                emote_string_order MEDIUMINT(9)     NOT NULL DEFAULT 0,
                emote_string       VARCHAR(50) CHARACTER SET ascii COLLATE ascii_general_nopad_ci NOT NULL,
                UNIQUE KEY string_unique        (emote_string),
                KEY        string_emote_foreign (emote_id),
                KEY        string_order_key     (emote_string_order),
                CONSTRAINT string_emote_foreign
                    FOREIGN KEY (emote_id)
                    REFERENCES msz_emoticons (emote_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_forum_categories (
                forum_id           INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                forum_order        INT(10) UNSIGNED NOT NULL DEFAULT 1,
                forum_parent       INT(10) UNSIGNED NOT NULL DEFAULT 0,
                forum_name         VARCHAR(255)     NOT NULL,
                forum_type         TINYINT(4)       NOT NULL DEFAULT 0,
                forum_description  TEXT                      DEFAULT NULL,
                forum_icon         VARCHAR(50)               DEFAULT NULL,
                forum_colour       INT(10) UNSIGNED          DEFAULT NULL,
                forum_link         VARCHAR(255)              DEFAULT NULL,
                forum_link_clicks  INT(10) UNSIGNED          DEFAULT NULL,
                forum_created      TIMESTAMP        NOT NULL DEFAULT current_timestamp(),
                forum_archived     TINYINT(1)       NOT NULL DEFAULT 0,
                forum_hidden       TINYINT(1)       NOT NULL DEFAULT 0,
                forum_count_topics INT(10) UNSIGNED NOT NULL DEFAULT 0,
                forum_count_posts  INT(10) UNSIGNED NOT NULL DEFAULT 0,
                PRIMARY KEY (forum_id),
                KEY forum_order_index       (forum_order),
                KEY forum_parent_index      (forum_parent),
                KEY forum_type_index        (forum_type),
                KEY forum_link_clicks_index (forum_link_clicks),
                KEY forum_hidden_index      (forum_hidden)
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_forum_permissions (
                user_id           INT(10) UNSIGNED          DEFAULT NULL,
                role_id           INT(10) UNSIGNED          DEFAULT NULL,
                forum_id          INT(10) UNSIGNED NOT NULL,
                forum_perms_allow INT(10) UNSIGNED NOT NULL DEFAULT 0,
                forum_perms_deny  INT(10) UNSIGNED NOT NULL DEFAULT 0,
                UNIQUE KEY forum_permissions_unique (user_id, role_id, forum_id),
                KEY        forum_permissions_forum_id (forum_id),
                KEY        forum_permissions_role_id (role_id),
                CONSTRAINT forum_permissions_forum_id_foreign
                    FOREIGN KEY (forum_id)
                    REFERENCES msz_forum_categories (forum_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE,
                CONSTRAINT forum_permissions_role_id_foreign
                    FOREIGN KEY (role_id)
                    REFERENCES msz_roles (role_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE,
                CONSTRAINT forum_permissions_user_id_foreign
                    FOREIGN KEY (user_id)
                    REFERENCES msz_users (user_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_forum_topics (
                topic_id          INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                forum_id          INT(10) UNSIGNED NOT NULL,
                user_id           INT(10) UNSIGNED          DEFAULT NULL,
                topic_type        TINYINT(4)       NOT NULL DEFAULT 0,
                topic_title       VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
                topic_count_views INT(10) UNSIGNED NOT NULL DEFAULT 0,
                topic_created     TIMESTAMP        NOT NULL DEFAULT current_timestamp(),
                topic_bumped      TIMESTAMP        NOT NULL DEFAULT current_timestamp(),
                topic_deleted     TIMESTAMP        NULL     DEFAULT NULL,
                topic_locked      TIMESTAMP        NULL     DEFAULT NULL,
                PRIMARY KEY (topic_id),
                KEY          topics_forum_id_foreign (forum_id),
                KEY          topics_user_id_foreign  (user_id),
                KEY          topics_type_index       (topic_type),
                KEY          topics_created_index    (topic_created),
                KEY          topics_bumped_index     (topic_bumped),
                KEY          topics_deleted_index    (topic_deleted),
                KEY          topics_locked_index     (topic_locked),
                FULLTEXT KEY topics_fulltext         (topic_title),
                CONSTRAINT topics_forum_id_foreign
                    FOREIGN KEY (forum_id)
                    REFERENCES msz_forum_categories (forum_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE,
                CONSTRAINT topics_user_id_foreign
                    FOREIGN KEY (user_id)
                    REFERENCES msz_users (user_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_forum_topics_track (
                user_id         INT(10) UNSIGNED NOT NULL,
                topic_id        INT(10) UNSIGNED NOT NULL,
                forum_id        INT(10) UNSIGNED NOT NULL,
                track_last_read TIMESTAMP        NOT NULL DEFAULT current_timestamp(),
                UNIQUE KEY topics_track_unique           (user_id, topic_id),
                KEY        topics_track_topic_id_foreign (topic_id),
                KEY        topics_track_user_id_foreign  (user_id),
                KEY        topics_track_forum_id_foreign (forum_id),
                KEY        forum_track_last_read         (track_last_read),
                CONSTRAINT topics_track_forum_id_foreign
                    FOREIGN KEY (forum_id)
                    REFERENCES msz_forum_categories (forum_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE,
                CONSTRAINT topics_track_topic_id_foreign
                    FOREIGN KEY (topic_id)
                    REFERENCES msz_forum_topics (topic_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE,
                CONSTRAINT topics_track_user_id_foreign
                    FOREIGN KEY (user_id)
                    REFERENCES msz_users (user_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_forum_posts (
                post_id                INT(10) UNSIGNED    NOT NULL AUTO_INCREMENT,
                topic_id               INT(10) UNSIGNED    NOT NULL,
                forum_id               INT(10) UNSIGNED    NOT NULL,
                user_id                INT(10) UNSIGNED             DEFAULT NULL,
                post_ip                VARBINARY(16)       NOT NULL,
                post_text              MEDIUMTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
                post_parse             TINYINT(4) UNSIGNED NOT NULL DEFAULT 0,
                post_display_signature TINYINT(4) UNSIGNED NOT NULL DEFAULT 1,
                post_created           TIMESTAMP           NOT NULL DEFAULT current_timestamp(),
                post_edited            TIMESTAMP           NULL     DEFAULT NULL,
                post_deleted           TIMESTAMP           NULL     DEFAULT NULL,
                PRIMARY KEY (post_id),
                KEY          posts_topic_id_foreign        (topic_id),
                KEY          posts_forum_id_foreign        (forum_id),
                KEY          posts_user_id_foreign         (user_id),
                KEY          posts_created_index           (post_created),
                KEY          posts_deleted_index           (post_deleted),
                KEY          posts_parse_index             (post_parse),
                KEY          posts_edited_index            (post_edited),
                KEY          posts_display_signature_index (post_display_signature),
                KEY          posts_ip_index                (post_ip),
                FULLTEXT KEY posts_fulltext                (post_text),
                CONSTRAINT posts_forum_id_foreign
                    FOREIGN KEY (forum_id)
                    REFERENCES msz_forum_categories (forum_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE,
                CONSTRAINT posts_topic_id_foreign
                    FOREIGN KEY (topic_id)
                    REFERENCES msz_forum_topics (topic_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE,
                CONSTRAINT posts_user_id_foreign
                    FOREIGN KEY (user_id)
                    REFERENCES msz_users (user_id)
                    ON DELETE SET NULL
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_login_attempts (
                user_id            INT(10) UNSIGNED          DEFAULT NULL,
                attempt_success    TINYINT(1)       NOT NULL,
                attempt_ip         VARBINARY(16)    NOT NULL,
                attempt_country    CHAR(2)          NOT NULL DEFAULT \'XX\',
                attempt_created    TIMESTAMP        NOT NULL DEFAULT current_timestamp(),
                attempt_user_agent VARCHAR(255)     NOT NULL DEFAULT \'\',
                KEY login_attempts_user_id_foreign (user_id),
                KEY login_attempts_created_index   (attempt_created),
                KEY login_attempts_success_index   (attempt_success),
                KEY login_attempts_ip_index        (attempt_ip),
                CONSTRAINT login_attempts_user_id_foreign
                    FOREIGN KEY (user_id)
                    REFERENCES msz_users (user_id)
                    ON DELETE SET NULL
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_news_categories (
                category_id          INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                category_name        VARCHAR(255)     NOT NULL,
                category_description TEXT             NOT NULL,
                category_is_hidden   TINYINT(1)       NOT NULL DEFAULT 0,
                category_created     TIMESTAMP        NOT NULL DEFAULT current_timestamp(),
                PRIMARY KEY (category_id),
                KEY news_categories_is_hidden_index (category_is_hidden)
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_news_posts (
                post_id            INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                category_id        INT(10) UNSIGNED NOT NULL,
                user_id            INT(10) UNSIGNED          DEFAULT NULL,
                comment_section_id INT(10) UNSIGNED          DEFAULT NULL,
                post_is_featured   TINYINT(1)       NOT NULL DEFAULT 0,
                post_title         VARCHAR(255)     NOT NULL,
                post_text          TEXT             NOT NULL,
                post_scheduled     TIMESTAMP        NOT NULL DEFAULT current_timestamp(),
                post_created       TIMESTAMP        NOT NULL DEFAULT current_timestamp(),
                post_updated       TIMESTAMP        NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
                post_deleted       TIMESTAMP        NULL     DEFAULT NULL,
                PRIMARY KEY (post_id),
                KEY          news_posts_category_id_foreign (category_id),
                KEY          news_posts_user_id_foreign     (user_id),
                KEY          news_posts_comment_section     (comment_section_id),
                KEY          news_posts_featured_index      (post_is_featured),
                KEY          news_posts_scheduled_index     (post_scheduled),
                KEY          news_posts_created_index       (post_created),
                KEY          news_posts_updated_index       (post_updated),
                KEY          news_posts_deleted_index       (post_deleted),
                FULLTEXT KEY news_posts_fulltext            (post_title, post_text),
                CONSTRAINT news_posts_category_id_foreign
                    FOREIGN KEY (category_id)
                    REFERENCES msz_news_categories (category_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE,
                CONSTRAINT news_posts_comment_section
                    FOREIGN KEY (comment_section_id)
                    REFERENCES msz_comments_categories (category_id)
                    ON DELETE SET NULL
                    ON UPDATE CASCADE,
                CONSTRAINT news_posts_user_id_foreign
                    FOREIGN KEY (user_id)
                    REFERENCES msz_users (user_id)
                    ON DELETE SET NULL
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_permissions (
                user_id               INT(10) UNSIGNED          DEFAULT NULL,
                role_id               INT(10) UNSIGNED          DEFAULT NULL,
                general_perms_allow   INT(10) UNSIGNED NOT NULL DEFAULT 0,
                general_perms_deny    INT(10) UNSIGNED NOT NULL DEFAULT 0,
                user_perms_allow      INT(10) UNSIGNED NOT NULL DEFAULT 0,
                user_perms_deny       INT(10) UNSIGNED NOT NULL DEFAULT 0,
                changelog_perms_allow INT(10) UNSIGNED NOT NULL DEFAULT 0,
                changelog_perms_deny  INT(10) UNSIGNED NOT NULL DEFAULT 0,
                news_perms_allow      INT(10) UNSIGNED NOT NULL DEFAULT 0,
                news_perms_deny       INT(10) UNSIGNED NOT NULL DEFAULT 0,
                forum_perms_allow     INT(10) UNSIGNED NOT NULL DEFAULT 0,
                forum_perms_deny      INT(10) UNSIGNED NOT NULL DEFAULT 0,
                comments_perms_allow  INT(10) UNSIGNED NOT NULL DEFAULT 0,
                comments_perms_deny   INT(10) UNSIGNED NOT NULL DEFAULT 0,
                UNIQUE KEY permissions_user_id_unique (user_id),
                UNIQUE KEY permissions_role_id_unique (role_id),
                CONSTRAINT permissions_role_id_foreign
                    FOREIGN KEY (role_id)
                    REFERENCES msz_roles (role_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE,
                CONSTRAINT permissions_user_id_foreign
                    FOREIGN KEY (user_id)
                    REFERENCES msz_users (user_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_profile_fields (
                field_id    INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                field_order INT(11)          NOT NULL DEFAULT 0,
                field_key   VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
                field_title VARCHAR(50)      NOT NULL,
                field_regex VARCHAR(255)     NOT NULL,
                PRIMARY KEY (field_id),
                UNIQUE KEY profile_fields_key_unique (field_key),
                KEY        profile_fields_order_key  (field_order)
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_profile_fields_formats (
                format_id      INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                field_id       INT(10) UNSIGNED NOT NULL DEFAULT 0,
                format_regex   VARCHAR(255)              DEFAULT NULL,
                format_link    VARCHAR(255)              DEFAULT NULL,
                format_display VARCHAR(255)     NOT NULL DEFAULT \'%s\',
                PRIMARY KEY (format_id),
                KEY profile_field_format_field_foreign (field_id),
                CONSTRAINT profile_field_format_field_foreign
                    FOREIGN KEY (field_id)
                    REFERENCES msz_profile_fields (field_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_profile_fields_values (
                field_id    INT(10) UNSIGNED NOT NULL,
                user_id     INT(10) UNSIGNED NOT NULL,
                format_id   INT(10) UNSIGNED NOT NULL,
                field_value VARCHAR(255)     NOT NULL,
                PRIMARY KEY (field_id, user_id),
                KEY profile_fields_values_format_foreign (format_id),
                KEY profile_fields_values_user_foreign   (user_id),
                KEY profile_fields_values_value_key      (field_value),
                CONSTRAINT profile_fields_values_field_foreign
                    FOREIGN KEY (field_id)
                    REFERENCES msz_profile_fields (field_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE,
                CONSTRAINT profile_fields_values_format_foreign
                    FOREIGN KEY (format_id)
                    REFERENCES msz_profile_fields_formats (format_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE,
                CONSTRAINT profile_fields_values_user_foreign
                    FOREIGN KEY (user_id)
                    REFERENCES msz_users (user_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_sessions (
                session_id           INT(10) UNSIGNED    NOT NULL AUTO_INCREMENT,
                user_id              INT(10) UNSIGNED    NOT NULL,
                session_key          BINARY(64)          NOT NULL,
                session_ip           VARBINARY(16)       NOT NULL,
                session_ip_last      VARBINARY(16)                DEFAULT NULL,
                session_user_agent   VARCHAR(255)        NOT NULL,
                session_country      CHAR(2)             NOT NULL DEFAULT \'XX\',
                session_expires      TIMESTAMP           NOT NULL DEFAULT (current_timestamp() + INTERVAL 1 MONTH),
                session_expires_bump TINYINT(3) UNSIGNED NOT NULL DEFAULT 1,
                session_created      TIMESTAMP           NOT NULL DEFAULT current_timestamp(),
                session_active       TIMESTAMP           NULL     DEFAULT NULL,
                PRIMARY KEY (session_id),
                UNIQUE KEY sessions_key_unique      (session_key),
                KEY        sessions_user_id_foreign (user_id),
                KEY        sessions_expires_index   (session_expires),
                KEY        sessions_created_index   (session_created),
                CONSTRAINT sessions_user_id_foreign
                    FOREIGN KEY (user_id)
                    REFERENCES msz_users (user_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_users_password_resets (
                user_id           INT(10) UNSIGNED NOT NULL,
                reset_ip          VARBINARY(16)    NOT NULL,
                reset_requested   TIMESTAMP        NOT NULL DEFAULT current_timestamp(),
                verification_code CHAR(12) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
                UNIQUE KEY users_password_resets_user_unique   (user_id,reset_ip),
                UNIQUE KEY users_password_resets_token_unique  (verification_code),
                KEY        users_password_resets_created_index (reset_requested),
                CONSTRAINT msz_users_password_resets_user_id_foreign
                    FOREIGN KEY (user_id)
                    REFERENCES msz_users (user_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_user_roles (
                user_id INT(10) UNSIGNED NOT NULL,
                role_id INT(10) UNSIGNED NOT NULL,
                UNIQUE KEY user_roles_unique          (user_id, role_id),
                KEY        user_roles_role_id_foreign (role_id),
                CONSTRAINT user_roles_role_id_foreign
                    FOREIGN KEY (role_id)
                    REFERENCES msz_roles (role_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE,
                CONSTRAINT user_roles_user_id_foreign
                    FOREIGN KEY (user_id)
                    REFERENCES msz_users (user_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');

        $conn->execute('
            CREATE TABLE msz_user_warnings (
                warning_id           INT(10) UNSIGNED    NOT NULL AUTO_INCREMENT,
                user_id              INT(10) UNSIGNED    NOT NULL,
                user_ip              VARBINARY(16)       NOT NULL,
                issuer_id            INT(10) UNSIGNED             DEFAULT NULL,
                issuer_ip            VARBINARY(16)       NOT NULL,
                warning_created      TIMESTAMP           NOT NULL DEFAULT current_timestamp(),
                warning_duration     TIMESTAMP           NULL     DEFAULT NULL,
                warning_type         TINYINT(3) UNSIGNED NOT NULL,
                warning_note         VARCHAR(255)        NOT NULL,
                warning_note_private TEXT                         DEFAULT NULL,
                PRIMARY KEY (warning_id),
                KEY user_warnings_user_foreign   (user_id),
                KEY user_warnings_issuer_foreign (issuer_id),
                KEY user_warnings_created_index  (warning_created),
                KEY user_warnings_duration_index (warning_duration),
                KEY user_warnings_type_index     (warning_type),
                KEY user_warnings_user_ip_index  (user_ip),
                CONSTRAINT user_warnings_issuer_foreign
                    FOREIGN KEY (issuer_id)
                    REFERENCES msz_users (user_id)
                    ON DELETE SET NULL
                    ON UPDATE CASCADE,
                CONSTRAINT user_warnings_user_foreign
                    FOREIGN KEY (user_id)
                    REFERENCES msz_users (user_id)
                    ON DELETE CASCADE
                    ON UPDATE CASCADE
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');
    }
}