<?php
use Index\Data\IDbConnection;
use Index\Data\Migration\IDbMigration;

final class AddModeratorNotesTable_20230724_201010 implements IDbMigration {
    public function migrate(IDbConnection $conn): void {
        $conn->execute('
            CREATE TABLE msz_users_modnotes (
                note_id      INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
                user_id      INT(10) UNSIGNED NOT NULL,
                author_id    INT(10) UNSIGNED     NULL DEFAULT NULL,
                note_created TIMESTAMP        NOT NULL DEFAULT current_timestamp(),
                note_title   VARCHAR(255)     NOT NULL,
                note_body    TEXT             NOT NULL,
                PRIMARY KEY (note_id),
                KEY users_modnotes_user_foreign   (user_id),
                KEY users_modnotes_author_foreign (author_id),
                KEY users_modnotes_created_index  (note_created),
                CONSTRAINT users_modnotes_user_foreign
                    FOREIGN KEY (user_id)
                    REFERENCES msz_users (user_id)
                    ON UPDATE CASCADE
                    ON DELETE CASCADE,
                CONSTRAINT users_modnotes_author_foreign
                    FOREIGN KEY (author_id)
                    REFERENCES msz_users (user_id)
                    ON UPDATE CASCADE
                    ON DELETE SET NULL
            ) ENGINE=InnoDB COLLATE=utf8mb4_bin
        ');

        // migrate existing notes
        $conn->execute('
            INSERT INTO msz_users_modnotes (user_id, author_id, note_created, note_title, note_body)
            SELECT user_id, issuer_id, warning_created, warning_note, COALESCE(warning_note_private, "")
            FROM msz_user_warnings
            WHERE warning_type = 0
        ');

        // delete notes from the warnings table
        $conn->execute('DELETE FROM msz_user_warnings WHERE warning_type = 0');

        // for good measure update silences to bans since i forgot to do that as a migration
        $conn->execute('UPDATE msz_user_warnings SET warning_type = 3 WHERE warning_type = 2');
    }
}