misuzu/database/2023_07_26_175936_add_new_bans_table.php

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

final class AddNewBansTable_20230726_175936 implements DbMigration {
    public function migrate(DbConnection $conn): void {
        $conn->execute('
            CREATE TABLE msz_users_bans (
                ban_id             INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                user_id            INT(10) UNSIGNED NOT NULL,
                mod_id             INT(10) UNSIGNED     NULL DEFAULT NULL,
                ban_severity       TINYINT(4)       NOT NULL,
                ban_reason_public  TEXT             NOT NULL,
                ban_reason_private TEXT             NOT NULL,
                ban_created        TIMESTAMP        NOT NULL DEFAULT current_timestamp(),
                ban_expires        TIMESTAMP            NULL DEFAULT NULL,
                PRIMARY KEY (ban_id),
                KEY users_bans_user_foreign (user_id),
                KEY users_bans_mod_foreign (mod_id),
                KEY users_bans_created_index (ban_created),
                KEY users_bans_expires_index (ban_expires),
                KEY users_bans_severity_index (ban_severity),
                CONSTRAINT users_bans_users_foreign
                    FOREIGN KEY (user_id)
                    REFERENCES msz_users (user_id)
                    ON UPDATE CASCADE
                    ON DELETE CASCADE,
                CONSTRAINT users_bans_mod_foreign
                    FOREIGN KEY (mod_id)
                    REFERENCES msz_users (user_id)
                    ON UPDATE CASCADE
                    ON DELETE SET NULL
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin
        ');

        $conn->execute('
            INSERT INTO msz_users_bans (user_id, mod_id, ban_severity, ban_reason_public, ban_reason_private, ban_created, ban_expires)
            SELECT user_id, issuer_id, 0, warning_note, COALESCE(warning_note_private, ""), warning_created, warning_duration
            FROM msz_user_warnings
            WHERE warning_type = 3
        ');

        $conn->execute('DELETE FROM msz_user_warnings WHERE warning_type = 3');
    }
}