<?php

declare(strict_types=1);

define('ROOT_PATH', dirname(__DIR__, 2));
require_once ROOT_PATH . '/app/core/Autoloader.php';
\App\Core\Autoloader::register();
$bootstrap = new \App\Core\Bootstrap();
$bootstrap->init();

use App\Core\Database;

try {
    $db = Database::getConnection();

    // 1. Fetch all existing guest records from the database
    $stmt = $db->query('SELECT id, workspace_id, guest_code FROM guests');
    $guests = $stmt->fetchAll(\PDO::FETCH_ASSOC);

    echo "Found " . count($guests) . " guest records in database.\n";

    $updatedCount = 0;
    $charset = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    $charsetLen = strlen($charset) - 1;

    $usedCodes = [];

    foreach ($guests as $g) {
        $wId = (int) $g['workspace_id'];
        if (!isset($usedCodes[$wId])) {
            $usedCodes[$wId] = [];
        }
    }

    $updateStmt = $db->prepare('UPDATE guests SET guest_code = :new_code WHERE id = :id');

    foreach ($guests as $g) {
        $id  = (int) $g['id'];
        $wId = (int) $g['workspace_id'];
        $oldCode = $g['guest_code'];

        // Generate unique 5-character code for this workspace
        $newCode = '';
        for ($loop = 0; $loop < 100; $loop++) {
            $candidate = '';
            for ($i = 0; $i < 5; $i++) {
                $candidate .= $charset[random_int(0, $charsetLen)];
            }

            if (!in_array($candidate, $usedCodes[$wId], true)) {
                $checkStmt = $db->prepare('SELECT id FROM guests WHERE workspace_id = :wId AND guest_code = :code AND id != :id LIMIT 1');
                $checkStmt->execute(['wId' => $wId, 'code' => $candidate, 'id' => $id]);
                if ($checkStmt->fetch() === false) {
                    $newCode = $candidate;
                    $usedCodes[$wId][] = $newCode;
                    break;
                }
            }
        }

        if ($newCode === '') {
            echo "FAILED to generate unique code for guest ID {$id}\n";
            continue;
        }

        $updateStmt->execute(['new_code' => $newCode, 'id' => $id]);
        $updatedCount++;
        echo "Updated Guest ID {$id} (Workspace {$wId}): {$oldCode} -> {$newCode}\n";
    }

    echo "\n----------------------------------------\n";
    echo "Migration Complete!\n";
    echo "Total Guest Records Updated: {$updatedCount}\n";

    // 2. Verification check
    $verifyStmt = $db->query('SELECT guest_code FROM guests');
    $allCodes = $verifyStmt->fetchAll(\PDO::FETCH_COLUMN);

    $invalidCount = 0;
    foreach ($allCodes as $code) {
        if (!preg_match('/^[A-Z0-9]{5}$/', (string) $code)) {
            $invalidCount++;
            echo "VERIFICATION FAIL: Invalid code found in database: '{$code}'\n";
        }
    }

    if ($invalidCount === 0) {
        echo "VERIFICATION SUCCESS: 100% of persisted guest_code values match ^[A-Z0-9]{5}$\n";
    } else {
        echo "VERIFICATION ERROR: {$invalidCount} invalid codes remain!\n";
    }

} catch (\Throwable $e) {
    echo "Migration error: " . $e->getMessage() . "\n";
}
