<?php

require_once __DIR__ . '/../config/database.php';

global $pdo;

function dbColumnExists(PDO $pdo, string $table, string $column): bool
{
    $stmt = $pdo->prepare(
        "SELECT COUNT(*)
         FROM INFORMATION_SCHEMA.COLUMNS
         WHERE TABLE_SCHEMA = DATABASE()
           AND TABLE_NAME = :table_name
           AND COLUMN_NAME = :column_name"
    );
    $stmt->execute([
        'table_name' => $table,
        'column_name' => $column,
    ]);
    return ((int) $stmt->fetchColumn()) > 0;
}

function dbIndexExists(PDO $pdo, string $table, string $index): bool
{
    $stmt = $pdo->prepare(
        "SELECT COUNT(*)
         FROM INFORMATION_SCHEMA.STATISTICS
         WHERE TABLE_SCHEMA = DATABASE()
           AND TABLE_NAME = :table_name
           AND INDEX_NAME = :index_name"
    );
    $stmt->execute([
        'table_name' => $table,
        'index_name' => $index,
    ]);
    return ((int) $stmt->fetchColumn()) > 0;
}

function normalizeMigrationUsername(string $email, int $id): string
{
    $local = strtolower(trim(explode('@', $email, 2)[0] ?? 'user'));
    $local = preg_replace('/[^a-z0-9._-]/', '', $local) ?: 'user';
    $local = trim($local, '._-') ?: 'user';
    $local = substr($local, 0, 24);
    return $local . $id;
}

$pdo->beginTransaction();

try {
    if (!dbColumnExists($pdo, 'users', 'username')) {
        $pdo->exec("ALTER TABLE users ADD COLUMN username VARCHAR(64) NULL AFTER name");
    }

    $users = $pdo->query("SELECT id, email, username FROM users ORDER BY id ASC")->fetchAll(PDO::FETCH_ASSOC);
    $seen = [];
    $updateUsername = $pdo->prepare("UPDATE users SET username = :username WHERE id = :id");

    foreach ($users as $user) {
        $id = (int) $user['id'];
        $current = strtolower(trim((string) ($user['username'] ?? '')));
        $candidate = $current !== '' ? $current : normalizeMigrationUsername((string) ($user['email'] ?? ''), $id);
        $base = $candidate;
        $suffix = 1;

        while (isset($seen[$candidate])) {
            $candidate = substr($base, 0, 58) . $suffix;
            $suffix++;
        }

        $seen[$candidate] = true;
        if ($current !== $candidate) {
            $updateUsername->execute([
                'username' => $candidate,
                'id' => $id,
            ]);
        }
    }

    if (!dbIndexExists($pdo, 'users', 'users_username_unique')) {
        $pdo->exec("ALTER TABLE users ADD UNIQUE KEY users_username_unique (username)");
    }

    $pdo->exec("ALTER TABLE inboxes MODIFY COLUMN type VARCHAR(20) NOT NULL DEFAULT 'guest'");

    if (!dbColumnExists($pdo, 'inboxes', 'is_primary')) {
        $pdo->exec("ALTER TABLE inboxes ADD COLUMN is_primary TINYINT(1) NOT NULL DEFAULT 0 AFTER type");
        $pdo->exec("UPDATE inboxes SET is_primary = 1 WHERE type = 'user' AND user_id IS NOT NULL");
    }

    if (!dbIndexExists($pdo, 'inboxes', 'inboxes_user_active_type_idx')) {
        $pdo->exec("ALTER TABLE inboxes ADD INDEX inboxes_user_active_type_idx (user_id, is_active, type)");
    }

    if (!dbIndexExists($pdo, 'inboxes', 'inboxes_email_address_idx')) {
        $pdo->exec("ALTER TABLE inboxes ADD INDEX inboxes_email_address_idx (email_address)");
    }

    $pdo->commit();

    header('Content-Type: application/json; charset=utf-8');
    echo json_encode([
        'ok' => true,
        'message' => 'username_alias_upgrade_completed',
    ], JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES);
} catch (Throwable $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }

    http_response_code(500);
    header('Content-Type: application/json; charset=utf-8');
    echo json_encode([
        'ok' => false,
        'message' => 'username_alias_upgrade_failed',
        'error' => $e->getMessage(),
    ], JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES);
}
