<?php
declare(strict_types=1);

require_once __DIR__ . '/external_players_lib.php';

function mocker_external_players_migrate(PDO $pdo): void
{
    $cols = $pdo->query('PRAGMA table_info(users)')->fetchAll(PDO::FETCH_ASSOC);
    $have = [];
    foreach ($cols as $c) {
        $have[(string) ($c['name'] ?? '')] = true;
    }
    if (!isset($have['external_player_id'])) {
        $pdo->exec('ALTER TABLE users ADD COLUMN external_player_id INTEGER');
    }
    if (!isset($have['external_synced_at'])) {
        $pdo->exec('ALTER TABLE users ADD COLUMN external_synced_at INTEGER');
    }
    $pdo->exec('CREATE UNIQUE INDEX IF NOT EXISTS idx_users_external_player_id ON users(external_player_id)');

    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS app_meta (
            meta_key TEXT PRIMARY KEY,
            meta_value TEXT NOT NULL
        )'
    );
}

function mocker_app_meta_get(PDO $pdo, string $key): ?string
{
    $st = $pdo->prepare('SELECT meta_value FROM app_meta WHERE meta_key = :k LIMIT 1');
    $st->execute([':k' => $key]);
    $row = $st->fetch(PDO::FETCH_ASSOC);
    if (!$row) {
        return null;
    }

    return (string) ($row['meta_value'] ?? '');
}

function mocker_app_meta_set(PDO $pdo, string $key, string $value): void
{
    $st = $pdo->prepare(
        'INSERT INTO app_meta (meta_key, meta_value) VALUES (:k, :v)
         ON CONFLICT(meta_key) DO UPDATE SET meta_value = excluded.meta_value'
    );
    $st->execute([':k' => $key, ':v' => $value]);
}

function mocker_external_players_synced_count(PDO $pdo): int
{
    return (int) $pdo->query(
        'SELECT COUNT(*) FROM users WHERE external_player_id IS NOT NULL AND external_player_id > 0'
    )->fetchColumn();
}

function mocker_external_players_normalize_email(int $externalId, string $email): string
{
    $email = trim($email);
    if ($email !== '' && filter_var($email, FILTER_VALIDATE_EMAIL)) {
        return $email;
    }

    return 'rx' . $externalId . '@players.imported.local';
}

function mocker_external_players_email_available(PDO $pdo, string $email, int $externalId): bool
{
    $email = trim($email);
    if ($email === '') {
        return true;
    }
    $st = $pdo->prepare(
        'SELECT id FROM users WHERE email = :e COLLATE NOCASE
         AND (external_player_id IS NULL OR external_player_id != :eid) LIMIT 1'
    );
    $st->execute([':e' => $email, ':eid' => $externalId]);

    return $st->fetch() === false;
}

function mocker_external_players_resolve_email(PDO $pdo, int $externalId, string $email): string
{
    $email = mocker_external_players_normalize_email($externalId, $email);
    if (mocker_external_players_email_available($pdo, $email, $externalId)) {
        return $email;
    }

    return mocker_external_players_normalize_email($externalId, '');
}

function mocker_external_players_normalize_username(PDO $pdo, int $externalId, string $username): string
{
    $username = trim($username);
    if ($username === '') {
        $username = 'player_' . $externalId;
    }
    if (strlen($username) > 48) {
        $username = substr($username, 0, 48);
    }

    $st = $pdo->prepare(
        'SELECT id FROM users WHERE username = :u COLLATE NOCASE AND (external_player_id IS NULL OR external_player_id != :eid) LIMIT 1'
    );
    $st->execute([':u' => $username, ':eid' => $externalId]);
    if ($st->fetch()) {
        $username = substr($username, 0, 40) . '_' . $externalId;
    }

    return $username;
}

/**
 * @param array<string, mixed> $row
 * @return 'inserted'|'updated'|'skipped'
 */
function mocker_external_player_upsert_row(PDO $pdo, array $row): string
{
    $externalId = (int) ($row['id'] ?? 0);
    if ($externalId <= 0) {
        return 'skipped';
    }

    $validations = is_array($row['validations'] ?? null) ? $row['validations'] : [];
    $username = mocker_external_players_normalize_username($pdo, $externalId, (string) ($row['username'] ?? ''));
    $email = mocker_external_players_resolve_email($pdo, $externalId, (string) ($row['email'] ?? ''));
    $phone = trim((string) ($row['phone'] ?? ''));
    $coins = max(0, (int) round((float) ($row['point'] ?? 0)));
    $createdAt = mocker_external_players_to_unix($row['created_at'] ?? null);
    if ($createdAt <= 0) {
        $createdAt = time();
    }
    $lastSeenAt = mocker_external_players_to_unix($row['last_seen_at'] ?? null);
    $isActive = (int) ($row['status'] ?? 0) === 1 ? 1 : 0;
    $tgUser = trim((string) ($validations['telegram_username'] ?? ''));
    $tgVerifiedAt = mocker_external_players_to_unix($validations['telegram_verified_at'] ?? null);
    $now = time();

    $find = $pdo->prepare('SELECT id FROM users WHERE external_player_id = :eid LIMIT 1');
    $find->execute([':eid' => $externalId]);
    $existing = $find->fetch(PDO::FETCH_ASSOC);

    if ($existing) {
        require_once __DIR__ . '/user_auth_lib.php';
        $apiPasswordHash = mocker_extract_password_hash_from_api_row($row);

        $params = [
            ':u' => $username,
            ':e' => $email,
            ':ph' => $phone,
            ':c' => $coins,
            ':active' => $isActive,
            ':tg' => $tgUser,
            ':sync' => $now,
            ':eid' => $externalId,
        ];
        $sets = [
            'username = :u',
            'email = :e',
            'phone = :ph',
            'coins = :c',
            'is_active = :active',
            'telegram_username = :tg',
            'external_synced_at = :sync',
        ];
        if ($apiPasswordHash !== null) {
            $sets[] = 'password_hash = :p';
            $sets[] = 'password_imported = 1';
            $sets[] = 'must_change_password = 1';
            $params[':p'] = $apiPasswordHash;
        }
        if ($createdAt > 0) {
            $sets[] = 'created_at = CASE WHEN created_at > 0 THEN created_at ELSE :ca END';
            $params[':ca'] = $createdAt;
        }
        if ($lastSeenAt > 0) {
            $sets[] = 'last_seen_at = :ls';
            $params[':ls'] = $lastSeenAt;
        }
        if ($tgVerifiedAt > 0) {
            $sets[] = 'telegram_verified_at = :tgv';
            $params[':tgv'] = $tgVerifiedAt;
        }
        $sql = 'UPDATE users SET ' . implode(', ', $sets) . ' WHERE external_player_id = :eid';
        $upd = $pdo->prepare($sql);
        try {
            $upd->execute($params);
        } catch (PDOException $e) {
            if ($e->getCode() !== '23000' && !str_contains($e->getMessage(), 'UNIQUE')) {
                throw $e;
            }
            $params[':e'] = mocker_external_players_normalize_email($externalId, '');
            $params[':u'] = mocker_external_players_normalize_username($pdo, $externalId, $username . '_' . $externalId);
            $sets[0] = 'username = :u';
            $sets[1] = 'email = :e';
            $sql = 'UPDATE users SET ' . implode(', ', $sets) . ' WHERE external_player_id = :eid';
            $pdo->prepare($sql)->execute($params);
        }

        return 'updated';
    }

    $claim = $pdo->prepare(
        'SELECT id FROM users WHERE email = :e COLLATE NOCASE
         AND (external_player_id IS NULL OR external_player_id = 0) LIMIT 1'
    );
    $claim->execute([':e' => $email]);
    $localRow = $claim->fetch(PDO::FETCH_ASSOC);
    if ($localRow) {
        $localId = (int) ($localRow['id'] ?? 0);
        if ($localId > 0) {
            $claimUpd = $pdo->prepare(
                'UPDATE users SET
                    username = :u, email = :e, phone = :ph, coins = :c, is_active = :active,
                    telegram_username = :tg, external_player_id = :eid, external_synced_at = :sync
                 WHERE id = :id'
            );
            $claimUpd->execute([
                ':u' => $username,
                ':e' => $email,
                ':ph' => $phone,
                ':c' => $coins,
                ':active' => $isActive,
                ':tg' => $tgUser,
                ':eid' => $externalId,
                ':sync' => $now,
                ':id' => $localId,
            ]);

            return 'updated';
        }
    }

    require_once __DIR__ . '/user_auth_lib.php';
    $apiPasswordHash = mocker_extract_password_hash_from_api_row($row);
    if ($apiPasswordHash !== null) {
        $hash = $apiPasswordHash;
        $passwordImported = 1;
    } else {
        $hash = mocker_migration_initial_password_hash($username);
        $passwordImported = 0;
    }
    $bind = [
        ':u' => $username,
        ':e' => $email,
        ':p' => $hash,
        ':mcp' => 1,
        ':pi' => $passwordImported,
        ':ph' => $phone,
        ':c' => $coins,
        ':ca' => $createdAt,
        ':ls' => $lastSeenAt > 0 ? $lastSeenAt : 0,
        ':active' => $isActive,
        ':eid' => $externalId,
        ':sync' => $now,
        ':tg' => $tgUser,
        ':tgv' => $tgVerifiedAt > 0 ? $tgVerifiedAt : 0,
    ];
    try {
        mocker_external_player_execute_insert($pdo, $bind);
    } catch (PDOException $e) {
        if ($e->getCode() === '23000' || str_contains($e->getMessage(), 'UNIQUE')) {
            $bind[':e'] = mocker_external_players_normalize_email($externalId, '');
            $bind[':u'] = mocker_external_players_normalize_username($pdo, $externalId, $username . '_' . $externalId);
            mocker_external_player_execute_insert($pdo, $bind);
        } else {
            throw $e;
        }
    }

    return 'inserted';
}

/** @param array<string, mixed> $bind */
function mocker_external_player_execute_insert(PDO $pdo, array $bind): void
{
    require_once __DIR__ . '/user_auth_lib.php';
    mocker_user_auth_migrate($pdo);
    $sql = 'INSERT INTO users (
        username, email, password_hash, phone, coins, created_at, last_seen_at, is_active,
        external_player_id, external_synced_at, telegram_username, telegram_verified_at,
        must_change_password, password_imported
    ) VALUES (
        :u, :e, :p, :ph, :c, :ca, :ls, :active, :eid, :sync, :tg, :tgv, :mcp, :pi
    )';
    $st = $pdo->prepare($sql);
    $st->execute($bind);
}

/**
 * @param array{id?: string, username?: string, telegram_username?: string, ip?: string} $filters
 * @return array{
 *   pagination: array{current_page: int, last_page: int, per_page: int, total: int},
 *   page_stats: array{inserted: int, updated: int, skipped: int},
 *   synced_total: int
 * }
 */
function mocker_external_players_sync_page(PDO $pdo, int $page, array $filters = []): array
{
    mocker_external_players_migrate($pdo);
    $fetched = null;
    $fetchErr = null;
    for ($attempt = 0; $attempt < 4; $attempt++) {
        try {
            $fetched = mocker_external_players_fetch_page($page, $filters);
            break;
        } catch (Throwable $e) {
            $fetchErr = $e;
            if ($attempt < 3) {
                usleep(500000 * ($attempt + 1));
            }
        }
    }
    if (!is_array($fetched)) {
        throw $fetchErr ?? new RuntimeException('external_fetch_failed');
    }
    $stats = ['inserted' => 0, 'updated' => 0, 'skipped' => 0];

    $pdo->beginTransaction();
    try {
        foreach ($fetched['rows'] as $row) {
            try {
                $result = mocker_external_player_upsert_row($pdo, $row);
                if (isset($stats[$result])) {
                    $stats[$result]++;
                }
            } catch (Throwable $rowErr) {
                $stats['skipped']++;
                error_log('external_player_upsert row ' . (int) ($row['id'] ?? 0) . ': ' . $rowErr->getMessage());
            }
        }
        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }

    $pg = $fetched['pagination'];
    mocker_app_meta_set($pdo, 'external_players_remote_total', (string) (int) ($pg['total'] ?? 0));
    mocker_app_meta_set($pdo, 'external_players_last_page', (string) (int) ($pg['last_page'] ?? 1));
    mocker_app_meta_set($pdo, 'external_players_last_synced_page', (string) $page);
    mocker_app_meta_set($pdo, 'external_players_last_sync_at', (string) time());

    $syncedTotal = mocker_external_players_synced_count($pdo);
    $remoteTotal = max(0, (int) ($pg['total'] ?? 0));
    $lastPageNum = max(1, (int) ($pg['last_page'] ?? 1));
    $complete = mocker_external_players_sync_is_complete($syncedTotal, $remoteTotal, $page, $lastPageNum);

    return [
        'pagination' => $pg,
        'page_stats' => $stats,
        'synced_total' => $syncedTotal,
        'sync_complete' => $complete,
        'remote_total' => $remoteTotal,
    ];
}

function mocker_external_players_refresh_remote_meta(PDO $pdo): void
{
    if (!mocker_external_players_enabled()) {
        return;
    }
    try {
        $probe = mocker_external_players_fetch_page(1, []);
        $pg = $probe['pagination'];
        $total = max(0, (int) ($pg['total'] ?? 0));
        $lastPage = max(1, (int) ($pg['last_page'] ?? 1));
        if ($total > 0) {
            mocker_app_meta_set($pdo, 'external_players_remote_total', (string) $total);
        }
        if ($lastPage > 0) {
            mocker_app_meta_set($pdo, 'external_players_last_page', (string) $lastPage);
        }
    } catch (Throwable $e) {
        error_log('external_players_refresh_remote_meta: ' . $e->getMessage());
    }
}

function mocker_external_players_sync_is_complete(int $synced, int $remoteTotal, int $lastSyncedPage, int $lastPage): bool
{
    if ($remoteTotal < 100) {
        return false;
    }
    if ($synced >= $remoteTotal) {
        return true;
    }
    if ($lastPage <= 1) {
        return false;
    }
    if ($lastSyncedPage > $lastPage) {
        return false;
    }
    if ($lastSyncedPage < $lastPage) {
        return false;
    }

    return $synced >= $remoteTotal;
}

function mocker_external_players_meta_trustworthy(int $remoteTotal, int $lastPage, int $synced): bool
{
    if ($lastPage <= 1 || $remoteTotal < 100) {
        return false;
    }
    if ($synced > 0 && $remoteTotal < $synced) {
        return false;
    }

    return true;
}

/** Reset progress when pagination meta changed or all pages were visited but DB is still short. */
function mocker_external_players_sync_reset_progress(PDO $pdo): void
{
    mocker_external_players_migrate($pdo);
    mocker_app_meta_set($pdo, 'external_players_last_synced_page', '0');
}

function mocker_external_players_fix_stale_sync_progress(PDO $pdo, int $synced, int $remoteTotal, int $lastSyncedPage, int $lastPage): int
{
    $lastSyncedPage = max(0, $lastSyncedPage);
    $lastPage = max(0, $lastPage);
    $needsReset = false;
    if ($lastPage > 1 && $lastSyncedPage > $lastPage) {
        $needsReset = true;
    }
    if ($lastPage > 1 && $lastSyncedPage >= $lastPage && $remoteTotal >= 100 && $synced < $remoteTotal) {
        $needsReset = true;
    }
    if ($needsReset) {
        mocker_app_meta_set($pdo, 'external_players_last_synced_page', '0');
        return 0;
    }

    return $lastSyncedPage;
}

function mocker_external_players_sync_status(PDO $pdo, bool $refreshRemote = true): array
{
    mocker_external_players_migrate($pdo);
    $synced = mocker_external_players_synced_count($pdo);
    $remoteTotal = (int) (mocker_app_meta_get($pdo, 'external_players_remote_total') ?? '0');
    $lastPage = (int) (mocker_app_meta_get($pdo, 'external_players_last_page') ?? '0');
    $lastSyncedPage = (int) (mocker_app_meta_get($pdo, 'external_players_last_synced_page') ?? '0');
    $lastSyncAt = (int) (mocker_app_meta_get($pdo, 'external_players_last_sync_at') ?? '0');

    if ($refreshRemote && mocker_external_players_enabled()) {
        $metaLooksBroken = $remoteTotal < 500 || $lastPage <= 1 || ($synced > 0 && $remoteTotal < $synced);
        if ($metaLooksBroken) {
            mocker_external_players_refresh_remote_meta($pdo);
            $remoteTotal = (int) (mocker_app_meta_get($pdo, 'external_players_remote_total') ?? '0');
            $lastPage = (int) (mocker_app_meta_get($pdo, 'external_players_last_page') ?? '0');
        }
    }

    if (mocker_external_players_meta_trustworthy($remoteTotal, $lastPage, $synced)) {
        $lastSyncedPage = mocker_external_players_fix_stale_sync_progress($pdo, $synced, $remoteTotal, $lastSyncedPage, $lastPage);
    }

    $metaOk = mocker_external_players_meta_trustworthy($remoteTotal, $lastPage, $synced);
    $complete = $metaOk && mocker_external_players_sync_is_complete($synced, $remoteTotal, $lastSyncedPage, $lastPage);
    $missing = $metaOk ? max(0, $remoteTotal - $synced) : max(0, $synced > 0 ? 1 : 0);

    $resumePage = 1;
    if (!$complete) {
        if ($metaOk && $missing > 0) {
            $resumePage = 1;
        } elseif ($metaOk) {
            $resumePage = max(1, $lastSyncedPage + 1);
            if ($resumePage > $lastPage) {
                $resumePage = 1;
            }
        }
    }

    return [
        'externalEnabled' => mocker_external_players_enabled(),
        'syncedCount' => $synced,
        'remoteTotal' => $remoteTotal,
        'lastPage' => $lastPage,
        'lastSyncedPage' => $lastSyncedPage,
        'resumePage' => $resumePage,
        'missingCount' => $missing,
        'metaTrustworthy' => $metaOk,
        'needsRescan' => !$metaOk || ($lastSyncedPage === 0 && $synced > 0 && !$complete),
        'lastSyncAt' => $lastSyncAt,
        'syncComplete' => $complete,
    ];
}
