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

final class UpdateUserAgentStorage_20230721_121854 implements IDbMigration {
    public function migrate(IDbConnection $conn): void {
        // convert user agent fields to BLOB and add field for client info storage
        $conn->execute('
            ALTER TABLE msz_login_attempts
                CHANGE COLUMN attempt_user_agent attempt_user_agent TEXT NOT NULL COLLATE "utf8mb4_bin" AFTER attempt_created,
                ADD COLUMN attempt_client_info TEXT NULL DEFAULT NULL AFTER attempt_user_agent
        ');
        $conn->execute('
            ALTER TABLE msz_sessions
                CHANGE column session_user_agent session_user_agent TEXT NOT NULL COLLATE "utf8mb4_bin" AFTER session_ip_last,
                ADD COLUMN session_client_info TEXT NULL DEFAULT NULL AFTER session_user_agent
        ');

        // make sure all existing fields have client info fields filled
        $updateLoginAttempts = $conn->prepare('UPDATE msz_login_attempts SET attempt_client_info = ? WHERE attempt_user_agent = ?');
        $selectLoginAttempts = $conn->query('SELECT DISTINCT attempt_user_agent FROM msz_login_attempts');
        while($selectLoginAttempts->next()) {
            $updateLoginAttempts->reset();
            $userAgent = $selectLoginAttempts->getString(0);
            $updateLoginAttempts->addParameter(1, json_encode(ClientInfo::parse($userAgent)));
            $updateLoginAttempts->addParameter(2, $userAgent);
            $updateLoginAttempts->execute();
        }

        $updateSessions = $conn->prepare('UPDATE msz_sessions SET session_client_info = ? WHERE session_user_agent = ?');
        $selectSessions = $conn->query('SELECT DISTINCT session_user_agent FROM msz_sessions');
        while($selectSessions->next()) {
            $updateSessions->reset();
            $userAgent = $selectSessions->getString(0);
            $updateSessions->addParameter(1, json_encode(ClientInfo::parse($userAgent)));
            $updateSessions->addParameter(2, $userAgent);
            $updateSessions->execute();
        }

        // make client info fields NOT NULL
        $conn->execute('
            ALTER TABLE msz_login_attempts
                CHANGE COLUMN attempt_client_info attempt_client_info TEXT NOT NULL COLLATE "utf8mb4_bin" AFTER attempt_user_agent
        ');
        $conn->execute('
            ALTER TABLE msz_sessions
                CHANGE COLUMN session_client_info session_client_info TEXT NOT NULL COLLATE "utf8mb4_bin" AFTER session_user_agent
        ');
    }
}