<?php

declare(strict_types=1);

namespace PpPhp\Core\Licensing;

use PpPhp\Core\Auth\OwnerCredentials;
use PpPhp\Core\DB;

/**
 * The MAIN licence server's side of primary↔backup replication.
 *
 * A backup server is a hot read-replica: it holds the same signing key and a
 * copy of the licence data, so it can keep answering customers' daily renewals
 * when the main server is unreachable. It never issues licences — the main
 * stays the single writer, so the two can never disagree about seats.
 *
 * Bootstrapping a backup by hand meant copying the signing key and the database
 * across, which is exactly the credential mess that made setup painful. Instead:
 *
 *   1. On the main, the owner generates a one-time PAIRING CODE.
 *   2. The backup, on install, sends that code to the main and receives
 *      everything it needs in one authenticated reply: the signing key, the
 *      owner password hash, the configuration, a persistent SYNC SECRET, and a
 *      first snapshot of the licence data.
 *   3. From then on the backup re-pulls a fresh snapshot every 15 minutes using
 *      its sync secret. The signing key is NOT re-sent on those pulls.
 *
 * The pairing code is high-entropy, single-use and short-lived; the sync secret
 * is per-backup and never expires until the peer is revoked. Everything travels
 * over HTTPS. The signing key crossing the wire is inherent to any HA setup with
 * offline-verifiable leases — a second server that can sign must hold the key —
 * so it is guarded by the code, not avoided.
 */
final class LicenseSync
{
    /** How long a freshly generated pairing code stays valid. */
    public const PAIR_TTL = 3600;                 // 1 hour

    private LicenseKeys $keys;

    public function __construct(?LicenseKeys $keys = null)
    {
        $this->keys = $keys ?? new LicenseKeys();
    }

    // ── Schema ────────────────────────────────────────────────────────

    public static function ensureSchema(): void
    {
        static $done = false;
        if ($done) return;
        $done = true;

        LicenseServer::ensureSchema();            // base pp_license* tables

        DB::Exec("CREATE TABLE IF NOT EXISTS `pp_license_peers` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `label` VARCHAR(190) NOT NULL DEFAULT '',
            `status` VARCHAR(20) NOT NULL DEFAULT 'pending',
            `pairing_hash` CHAR(64) NULL,
            `sync_secret_hash` CHAR(64) NULL,
            `fingerprint` TEXT NULL,
            `host` VARCHAR(190) NOT NULL DEFAULT '',
            `url` VARCHAR(190) NOT NULL DEFAULT '',
            `last_ip` VARCHAR(64) NOT NULL DEFAULT '',
            `paired_at` DATETIME NULL,
            `last_sync_at` DATETIME NULL,
            `created_at` DATETIME NOT NULL,
            `expires_at` DATETIME NULL,
            UNIQUE KEY `uniq_pairing` (`pairing_hash`),
            KEY `idx_secret` (`sync_secret_hash`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

        // Self-heal: the `url` column (a paired backup's public address) was added
        // so a backup auto-joins the client-facing endpoint list — no manual mirror
        // entry. Older peer tables predate it.
        if (!DB::FetchAll("SHOW COLUMNS FROM `pp_license_peers` LIKE 'url'")) {
            DB::Exec("ALTER TABLE `pp_license_peers` ADD COLUMN `url` VARCHAR(190) NOT NULL DEFAULT '' AFTER `host`");
        }
    }

    /** A backup's public address, sanitised to a bare https origin. */
    private static function cleanUrl(string $u): string
    {
        $u = trim($u);
        if ($u === '' || !preg_match('#^https?://[^\s/]+#i', $u)) return '';
        return rtrim($u, '/');
    }

    // ── Pairing (owner action on the main) ────────────────────────────

    /** Readable one-time pairing code: PAIR-XXXXX-XXXXX-XXXXX-XXXXX. */
    public static function newCode(): string
    {
        $alphabet = '23456789ABCDEFGHJKLMNPQRSTUVWXYZ';
        $g = [];
        for ($i = 0; $i < 4; $i++) {
            $s = '';
            for ($j = 0; $j < 5; $j++) $s .= $alphabet[random_int(0, 31)];
            $g[] = $s;
        }
        return 'PAIR-' . implode('-', $g);
    }

    /** Create a pending pairing and return its code (shown once to the owner). */
    public function createPairing(string $label = ''): array
    {
        self::ensureSchema();
        $code = self::newCode();
        DB::Insert('pp_license_peers', [
            'label'        => substr(trim($label), 0, 190),
            'status'       => 'pending',
            'pairing_hash' => hash('sha256', $code),
            'created_at'   => self::now(),
            'expires_at'   => self::future(self::PAIR_TTL),
        ]);
        return ['id' => (int) DB::LastInsertId(), 'code' => $code, 'ttl' => self::PAIR_TTL];
    }

    /**
     * A backup redeems a pairing code → full bootstrap payload.
     *
     * @throws LicenseException with a message safe to show
     */
    public function redeemPairing(string $code, array $request, string $ip): array
    {
        self::ensureSchema();
        if (!$this->keys->exists()) {
            throw new LicenseException('The main server has no signing key yet — create it first.', 400);
        }
        $rows = DB::FetchAll(
            "SELECT * FROM `pp_license_peers` WHERE `pairing_hash` = ? AND `status` = 'pending'",
            [hash('sha256', trim($code))]
        );
        $peer = $rows[0] ?? null;
        if (!$peer) {
            throw new LicenseException('That pairing code is not valid, or it has already been used.', 403);
        }
        if (self::epoch($peer['expires_at']) < time()) {
            throw new LicenseException('That pairing code has expired. Generate a new one on the main server.', 403);
        }

        $syncSecret  = bin2hex(random_bytes(32));
        $fingerprint = self::cleanFingerprint($request['fingerprint'] ?? []);
        DB::Update('pp_license_peers', [
            'status'           => 'active',
            'pairing_hash'     => null,
            'sync_secret_hash' => hash('sha256', $syncSecret),
            'fingerprint'      => (string) json_encode($fingerprint),
            'host'             => substr((string) ($fingerprint['host'] ?? ''), 0, 190),
            // The backup reports its own public URL so it can auto-join the mesh
            // handed to clients — no manual mirror entry on the main.
            'url'              => substr(self::cleanUrl((string) ($request['backupUrl'] ?? '')), 0, 190),
            'last_ip'          => substr($ip, 0, 64),
            'paired_at'        => self::now(),
            'last_sync_at'     => self::now(),
        ], ['id' => (int) $peer['id']]);

        return [
            'syncSecret'  => $syncSecret,
            'signingKey'  => [
                'secret' => base64_encode($this->keys->secretKey()),
                'public' => $this->keys->publicKeyB64(),
            ],
            'config'      => self::configSnapshot(),
            'snapshot'    => self::dataSnapshot(),
        ];
    }


    /**
     * Remove per-side clone state from replicated catalogue rows.
     * @param array<int,array<string,mixed>>|null $rows
     * @return array<int,array<string,mixed>>|null
     */
    private static function stripCloneState(?array $rows): ?array
    {
        if ($rows === null) return null;
        foreach ($rows as $i => $r) {
            unset($r['fingerprint'], $r['cloned_at'], $r['clone_bytes']);
            $rows[$i] = $r;
        }
        return $rows;
    }

    // ── Ongoing sync ──────────────────────────────────────────────────

    /** A backup pulls the current state. No signing key on this path. */
    public function sync(string $syncSecret, string $ip): array
    {
        $this->peerFor($syncSecret, $ip);

        return [
            'keyId'    => $this->keys->exists() ? $this->keys->keyId() : '',
            'config'   => self::configSnapshot(),
            'snapshot' => self::dataSnapshot(),
        ];
    }


    /**
     * Authenticate a paired backup and stamp its last-seen. Shared by every
     * backup-facing endpoint so one revocation path covers all of them.
     *
     * @return array<string,mixed> the peer row
     */
    private function peerFor(string $syncSecret, string $ip): array
    {
        self::ensureSchema();
        $rows = DB::FetchAll(
            "SELECT * FROM `pp_license_peers` WHERE `sync_secret_hash` = ?",
            [hash('sha256', trim($syncSecret))]
        );
        $peer = $rows[0] ?? null;
        if (!$peer) {
            throw new LicenseException('This backup server is not paired with the main server. Re-pair it.', 401);
        }
        if (($peer['status'] ?? '') === 'suspended') {
            throw new LicenseException('This backup has been suspended on the main server.', 403);
        }
        if (($peer['status'] ?? '') !== 'active') {
            throw new LicenseException('This backup was revoked on the main server. Re-pair it.', 401);
        }
        DB::Update('pp_license_peers', ['last_sync_at' => self::now(), 'last_ip' => substr($ip, 0, 64)], ['id' => (int) $peer['id']]);
        return $peer;
    }

    // ── Phase 4 · data-plane cloning (backup PULLS) ────────────────────
    //
    // The direction here is deliberate and worth stating, because the obvious
    // reading is backwards. The data lives on the main, so "the main pushes it"
    // sounds right — but the main holds only `sync_secret_hash`, never the secret,
    // so it has no way to authenticate ITSELF to a backup. The credential only
    // works in the direction it was issued: backup → main. Pulling therefore needs
    // no new secret, no signing scheme, and no inbound endpoint on a machine that
    // could not verify the caller anyway. It also reuses the one network path
    // already proven to work through the hosts' egress rules (see bug-035).

    /**
     * What the main currently holds, so the backup can decide what to fetch.
     *
     * Returns the LIVE fingerprint of each active database. The backup compares
     * these against its OWN stored fingerprints — the main's `changed` flag is
     * about the main's local bookkeeping and is meaningless here.
     *
     * @return array{databases:array<int,array{db_name:string,fingerprint:string}>}
     */
    public function dbCloneManifest(string $syncSecret, string $ip): array
    {
        $this->peerFor($syncSecret, $ip);

        $out = [];
        foreach ((new \PpPhp\Core\Databases\ManagedDatabaseService())->cloneCandidates() as $c) {
            $out[] = [
                'db_name'     => (string) $c['db_name'],
                'fingerprint' => (string) $c['fingerprint'],
                // Per-table digests let the backup ask for ONLY what differs.
                // Small: ~70 bytes per table, so a whole estate still fits in one
                // response and the decision costs no extra round trip.
                'tables'      => (array) ($c['tables'] ?? []),
            ];
        }
        return ['databases' => $out];
    }

    // ── On-disk file replication (virtual DBs + project trees) ─────────

    /**
     * Manifest of one file set for a paired backup: {base, mode, files:{rel:{sha256,size}}}.
     * Authenticated by the sync secret, and served ONLY by the main (a backup has
     * nothing authoritative to hand out).
     */
    public function fileSetManifest(string $syncSecret, string $ip, string $set): array
    {
        $this->peerFor($syncSecret, $ip);
        $this->assertMainServing();
        if (!\PpPhp\Core\Replication\ReplicationSets::isValid($set)) {
            throw new LicenseException('Unknown replication set.', 400);
        }
        return \PpPhp\Core\Replication\ReplicationSets::manifest($set);
    }

    /**
     * One file (or byte range) from a set, gzipped + base64'd. Snapshot sets are
     * always returned whole as a VACUUM-consistent copy; copy sets may be ranged
     * so a large upload streams without loading it all into memory. The path is
     * vetted by ReplicationSets so it can never escape the set root.
     *
     * @return array{set:string,path:string,gz:string,bytes:int,offset:int,total:int,eof:bool}
     */
    public function fileSetChunk(string $syncSecret, string $ip, string $set, string $path, int $offset = 0, int $len = 0): array
    {
        $this->peerFor($syncSecret, $ip);
        $this->assertMainServing();
        if (!\PpPhp\Core\Replication\ReplicationSets::isValid($set)) {
            throw new LicenseException('Unknown replication set.', 400);
        }
        $rel = \PpPhp\Core\Replication\ReplicationSets::safeRel($path);
        if ($rel === null) {
            throw new LicenseException('Illegal path.', 400);
        }

        $mode  = \PpPhp\Core\Replication\ReplicationSets::mode($set);
        $whole = ($mode === 'snapshot') || ($offset <= 0 && $len <= 0);

        if ($whole) {
            $data = \PpPhp\Core\Replication\ReplicationSets::readWhole($set, $rel);
            if ($data === null) {
                throw new LicenseException('File is unavailable or too large to replicate in one piece.', 404);
            }
            return ['set' => $set, 'path' => $rel, 'gz' => base64_encode((string) gzencode($data, 6)),
                    'bytes' => strlen($data), 'offset' => 0, 'total' => strlen($data), 'eof' => true];
        }

        $abs   = \PpPhp\Core\Replication\ReplicationSets::absOf($set, $rel);
        $total = ($abs !== null && is_file($abs)) ? (int) filesize($abs) : 0;
        $data  = \PpPhp\Core\Replication\ReplicationSets::readRange($set, $rel, $offset, $len);
        if ($data === null) {
            throw new LicenseException('File range is unavailable.', 404);
        }
        $end = max(0, $offset) + strlen($data);
        return ['set' => $set, 'path' => $rel, 'gz' => base64_encode((string) gzencode($data, 6)),
                'bytes' => strlen($data), 'offset' => max(0, $offset), 'total' => $total, 'eof' => ($end >= $total)];
    }

    /** File replication is answered only by the authoritative main. */
    private function assertMainServing(): void
    {
        if (ServerRole::isBackup()) {
            throw new LicenseException('This is a backup server — file replication is pulled from the main.', 403);
        }
    }

    /**
     * Hand one database's dump to a paired backup, gzipped and base64'd.
     *
     * The fingerprint is taken BEFORE the dump and returned with it, so the backup
     * records the state it actually received. Taking it afterwards would let a
     * write that landed mid-dump be recorded as already-cloned and then skipped.
     *
     * @return array{db_name:string,fingerprint:string,gz:string,bytes:int,tables:int,rows:int}
     */
    public function dbCloneDump(string $syncSecret, string $ip, string $dbName, ?array $tables = null, array $drop = []): array
    {
        $this->peerFor($syncSecret, $ip);
        $this->assertMainServing();

        $dbName = trim($dbName);
        if ($dbName === '') throw new LicenseException('No database was named.', 400);

        $svc = new \PpPhp\Core\Databases\ManagedDatabaseService();
        $row = DB::FetchAll(
            "SELECT `id` FROM `pp_managed_databases` WHERE `db_name` = ? AND `status` = 'active'",
            [$dbName]
        )[0] ?? null;
        if (!$row) throw new LicenseException('No active managed database named "' . $dbName . '".', 404);

        $conn = $svc->connectionFor((int) $row['id']);
        if ($conn === null) throw new LicenseException('The main server cannot reach "' . $dbName . '" itself.', 503);

        // Digests BEFORE the dump, so what the backup records matches what it got.
        $digests = \PpPhp\Core\Databases\TableDigest::of($conn);
        $fp = \PpPhp\Core\Databases\TableDigest::fold($digests);
        $d  = \PpPhp\Core\Databases\DbCloner::dump($conn, $tables, $drop);
        if (empty($d['success'])) throw new LicenseException((string) $d['message'], 500);

        return [
            'db_name'     => $dbName,
            'fingerprint' => $fp,
            'digests'     => $digests,
            'partial'     => $tables !== null,
            'gz'          => base64_encode((string) gzencode((string) $d['sql'], 6)),
            'bytes'       => (int) $d['bytes'],
            'tables'      => (int) $d['tables'],
            'rows'        => (int) $d['rows'],
        ];
    }

    // ── Snapshots ─────────────────────────────────────────────────────

    /**
     * Everything a backup needs to answer renewals identically to the main, plus
     * the control-plane tables that make it a usable console during an outage.
     *
     * Keys are ADDITIVE: a backup running older code simply ignores the ones it
     * does not know, and `applySnapshot()` only touches a table whose key is
     * present — so a newer backup talking to an older main never wipes anything.
     *
     * TWO THINGS DELIBERATELY DO NOT TRAVEL:
     *  • `pp_db_servers.admin_pass` — panel API tokens. Each side runs on its own
     *    hosting account and holds its own credential; replicating them would mean
     *    compromising one server hands over both panels. Metadata only.
     *  • `pp_visitors` — high volume, no operational value on a replica.
     */
    public static function dataSnapshot(): array
    {
        $snap = [
            'licenses'  => DB::FetchAll('SELECT * FROM `pp_licenses`') ?: [],
            'instances' => DB::FetchAll('SELECT * FROM `pp_license_instances`') ?: [],
        ];

        // Managed-database control plane. Servers carry their identifying fields
        // so the backup can MATCH them to its own rows — ids are per-side and are
        // never trusted across the wire (see LicenseMirror::applySnapshot).
        $snap['dbServers'] = self::tableOrNull(
            'SELECT `id`, `label`, `host`, `port`, `is_platform`, `grant_host`, `status`,
                    `driver`, `api_port`, `db_host`, `name_prefix`, `admin_user`
               FROM `pp_db_servers`'
        );
        // Clone state (`fingerprint`, `cloned_at`, `clone_bytes`) describes what THIS
        // side has physically restored, so — like `server_id` — it is a per-side
        // fact and must not travel. Shipping it would let the main's fingerprint
        // overwrite the backup's, after which the backup would compare equal, skip
        // every database forever, and report itself current while holding nothing.
        $snap['managedDatabases'] = self::stripCloneState(
            self::tableOrNull('SELECT * FROM `pp_managed_databases`')
        );

        // Accounts, so the owner can actually sign in to the backup during an
        // outage and see the same people. Password hashes travel here; the
        // channel is HTTPS + the pairing-established sync secret, the same one
        // already trusted with the signing key at pairing time.
        $snap['users'] = self::tableOrNull('SELECT * FROM `users`');
        $snap['trialRequests'] = self::tableOrNull('SELECT * FROM `temporary_access_requests`');

        // Licence PLANS + Owner-pushed ADVISORIES — sensitive, owner-authored data
        // that must survive shifting to a backup / new server. Straight copies: no
        // per-side columns, so the backup ends up with the exact same plans your
        // customers are on and the exact notices/patches queued for them. tableOrNull
        // keeps it forward-safe — an older main that lacks a table sends null and
        // never wipes a newer backup's copy.
        $snap['plans'] = self::tableOrNull('SELECT * FROM `pp_license_plans`');
        $snap['advisories'] = self::tableOrNull('SELECT * FROM `pp_advisories`');

        // Virtual-database CATALOGUE — who owns / is assigned / shares each pool
        // database, plus per-user count quotas. The SQLite files themselves are
        // heavy and travel on the file-replication channel (ReplicationSets 'vdb');
        // here we replicate only the small ownership/sharing rows so the backup's
        // console shows the same databases against the same people.
        $snap['virtualDbs']      = self::tableOrNull('SELECT * FROM `pp_virtual_databases`');
        $snap['virtualDbShares'] = self::tableOrNull('SELECT * FROM `pp_virtual_db_shares`');
        $snap['virtualDbQuotas'] = self::tableOrNull('SELECT * FROM `pp_virtual_db_quotas`');

        // PROJECTS catalogue — the dashboard lists projects from this table, and
        // each project's on-disk tree travels on the file channel ('projects').
        // The per-project DB connection (incl. password) rides the same trusted
        // HTTPS + sync-secret channel that already carries user password hashes and
        // the signing key. project_catalog is presentation state (pins/colours);
        // project_vcs_settings is the git remote config.
        $snap['projects']       = self::tableOrNull('SELECT * FROM `projects`');
        $snap['projectCatalog'] = self::tableOrNull('SELECT * FROM `project_catalog`');
        $snap['projectVcs']     = self::tableOrNull('SELECT * FROM `project_vcs_settings`');

        // PROJECT VERSION HISTORY + DEPLOYMENT config, so a backup keeps the owner's
        // restorable checkpoints and FTP/DB deploy profiles instead of losing them
        // until failover (these FK into `projects` and were being wiped by its
        // cascade with nothing to restore them). tableOrNull keeps it forward-safe —
        // a main without the deploy/version tables sends null and wipes nothing.
        $snap['projectVersions']     = self::tableOrNull('SELECT * FROM `project_versions`');
        $snap['deployConnections']   = self::tableOrNull('SELECT * FROM `deploy_connections`');
        $snap['deployDbConnections'] = self::tableOrNull('SELECT * FROM `deploy_db_connections`');
        $snap['deployBuilds']        = self::tableOrNull('SELECT * FROM `deploy_builds`');
        $snap['deployUploads']       = self::tableOrNull('SELECT * FROM `deploy_uploads`');

        return $snap;
    }

    /**
     * Read a table, or null when it does not exist on this install.
     *
     * null means "I have nothing to say about this table" and the consumer skips
     * it entirely — which is what stops a main that predates a table from
     * emptying it on a newer backup.
     *
     * @return array<int,array<string,mixed>>|null
     */
    private static function tableOrNull(string $sql): ?array
    {
        try {
            $rows = DB::FetchAll($sql);
            return is_array($rows) ? $rows : [];
        } catch (\Throwable $e) {
            return null;
        }
    }

    /** Config the backup mirrors so it behaves and looks like the main. */
    public static function configSnapshot(): array
    {
        // A backup receives the WHOLE mesh (every active peer + any manual mirror),
        // so it answers clients with the same endpoint list the main does — and a
        // fresh backup needs nothing configured by hand.
        $cfg = [
            'primary'       => LicenseConfig::primary(),
            'mirrors'       => LicenseConfig::distributableBackups(),
            'requireHttps'  => LicenseConfig::requireHttps(),
            'ownerMode'     => OwnerCredentials::mode(),
            'ownerHash'     => OwnerCredentials::distributableHash(),
            'deriveVersion' => OwnerDerivation::version(),
        ];

        // The public URL of the update host, so a backup's update feed can read
        // the signed index with zero manual setup. Only the READ side travels —
        // never the FTP credentials (a backup never publishes).
        if (class_exists('\PpPhp\Core\Update\UpdateHostConfig')) {
            $pub = \PpPhp\Core\Update\UpdateHostConfig::publicUrl();
            if ($pub !== '') {
                $cfg['updateHost'] = ['publicUrl' => $pub, 'channel' => \PpPhp\Core\Update\UpdateHostConfig::channel()];
            }
        }

        return $cfg;
    }

    // ── Peer management (owner UI) ────────────────────────────────────

    public function peers(): array
    {
        self::ensureSchema();
        return DB::FetchAll("SELECT * FROM `pp_license_peers` ORDER BY `id` DESC") ?: [];
    }

    public function revokePeer(int $id): bool
    {
        self::ensureSchema();
        return DB::Update('pp_license_peers', ['status' => 'revoked', 'sync_secret_hash' => null, 'pairing_hash' => null], ['id' => $id]) !== false;
    }

    /** Temporarily stop a backup from syncing, keeping its credentials so it can resume. */
    public function suspendPeer(int $id): bool
    {
        self::ensureSchema();
        return DB::Update('pp_license_peers', ['status' => 'suspended'], ['id' => $id]) !== false;
    }

    /** Re-enable a suspended backup. Only works while it still holds a sync secret. */
    public function resumePeer(int $id): bool
    {
        self::ensureSchema();
        $rows = DB::FetchAll('SELECT `sync_secret_hash` FROM `pp_license_peers` WHERE `id` = ?', [$id]);
        if (empty($rows) || empty($rows[0]['sync_secret_hash'])) return false;   // revoked → must re-pair
        return DB::Update('pp_license_peers', ['status' => 'active'], ['id' => $id]) !== false;
    }

    /** Remove a peer row entirely — for tidying up failed or old pairings. */
    public function deletePeer(int $id): bool
    {
        self::ensureSchema();
        return DB::Query('DELETE FROM `pp_license_peers` WHERE `id` = ?', [$id]) !== false;
    }

    /**
     * Drop a paired backup's advertised ADDRESS from the mesh without unpairing it
     * — the pairing (sync secret) is untouched, so the backup keeps syncing; only
     * the (stale/broken) URL clients were handed is cleared. Matches on the cleaned
     * URL, so `…/public` and `…/public/` both hit. Returns the number cleared.
     */
    public function forgetPeerAddress(string $url): int
    {
        self::ensureSchema();
        $u = self::cleanUrl($url);
        if ($u === '') return 0;
        try {
            $res = DB::Query("UPDATE `pp_license_peers` SET `url` = '' WHERE `url` IN (?, ?)", [$u, $u . '/']);
            return $res === false ? 0 : $res->rowCount();
        } catch (\Throwable $e) {
            return 0;
        }
    }

    /** Drop expired, never-redeemed pending pairings. Cheap housekeeping. */
    public function prunePending(): void
    {
        try {
            DB::Query("DELETE FROM `pp_license_peers` WHERE `status` = 'pending' AND `expires_at` < ?", [self::now()]);
        } catch (\Throwable $e) {
        }
    }

    // ── helpers ───────────────────────────────────────────────────────

    private static function cleanFingerprint(mixed $raw): array
    {
        if (!is_array($raw)) return [];
        $out = [];
        foreach (['host', 'root', 'os', 'php', 'srv', 'db'] as $k) {
            if (isset($raw[$k]) && is_scalar($raw[$k])) $out[$k] = substr((string) $raw[$k], 0, 190);
        }
        return $out;
    }

    private static function now(): string
    {
        $t = DB::FetchOne('SELECT NOW()');
        return is_string($t) && $t !== '' ? $t : date('Y-m-d H:i:s');
    }

    private static function future(int $seconds): string
    {
        $t = DB::FetchOne('SELECT DATE_ADD(NOW(), INTERVAL ? SECOND)', [$seconds]);
        return is_string($t) && $t !== '' ? $t : date('Y-m-d H:i:s', time() + $seconds);
    }

    private static function epoch(mixed $mysqlDatetime): int
    {
        $s = trim((string) ($mysqlDatetime ?? ''));
        if ($s === '') return 0;
        $t = DB::FetchOne('SELECT UNIX_TIMESTAMP(?)', [$s]);
        return (int) $t;
    }
}
