misuzu/database/2024_01_30_233734_create_messages_table.php

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

final class CreateMessagesTable_20240130_233734 implements DbMigration {
    public function migrate(DbConnection $conn): void {
        $conn->execute('
            CREATE TABLE msz_messages (
                msg_id BINARY(8) NOT NULL,
                msg_owner_id INT(10) UNSIGNED NOT NULL,
                msg_author_id INT(10) UNSIGNED NULL DEFAULT NULL,
                msg_recipient_id INT(10) UNSIGNED NULL DEFAULT NULL,
                msg_reply_to BINARY(8) NULL DEFAULT NULL,
                msg_title TINYTEXT NOT NULL COLLATE "utf8mb4_unicode_520_ci",
                msg_body TEXT NOT NULL COLLATE "utf8mb4_unicode_520_ci",
                msg_parser TINYINT(3) UNSIGNED NOT NULL,
                msg_created TIMESTAMP NOT NULL DEFAULT current_timestamp(),
                msg_sent TIMESTAMP NULL DEFAULT NULL,
                msg_read TIMESTAMP NULL DEFAULT NULL,
                msg_deleted TIMESTAMP NULL DEFAULT NULL,
                PRIMARY KEY (msg_id, msg_owner_id),
                KEY messages_owner_foreign (msg_owner_id),
                KEY messages_author_foreign (msg_author_id),
                KEY messages_recipient_foreign (msg_recipient_id),
                KEY messages_reply_to_index (msg_reply_to),
                KEY messages_created_index (msg_created),
                KEY messages_sent_index (msg_sent),
                KEY messages_read_index (msg_read),
                KEY messages_deleted_index (msg_deleted),
                CONSTRAINT messages_owner_foreign
                    FOREIGN KEY (msg_owner_id)
                    REFERENCES msz_users (user_id)
                    ON UPDATE CASCADE
                    ON DELETE CASCADE,
                CONSTRAINT messages_author_foreign
                    FOREIGN KEY (msg_author_id)
                    REFERENCES msz_users (user_id)
                    ON UPDATE CASCADE
                    ON DELETE SET NULL,
                CONSTRAINT messages_recipient_foreign
                    FOREIGN KEY (msg_recipient_id)
                    REFERENCES msz_users (user_id)
                    ON UPDATE CASCADE
                    ON DELETE SET NULL
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin;
        ');
    }
}