<?php

declare(strict_types=1);

namespace PpPhp\Core\Databases;

use PpPhp\Core\DB;

/**
 * Owner-managed databases.
 *
 * Two tables:
 *  - pp_db_servers            registered MySQL servers the owner can provision on
 *                             (the built-in "platform" server + any external hosts).
 *  - pp_managed_databases     each provisioned database with its OWN dedicated
 *                             MySQL login (GRANTed to just that schema) and the
 *                             user it is assigned to.
 *
 * The service is self-contained: it opens its own PDO to the central database
 * (config/database.php) for the catalogue tables, and short-lived admin PDOs to
 * each target server when provisioning/dropping. Credentials never reach the
 * browser — controllers expose only public fields, and project creation injects
 * the assigned connection server-side (see ProjectController).
 */
class ManagedDatabaseService
{
    private ?\PDO $central = null;
    /** @var array<string,mixed>|null */
    private ?array $cfg = null;

    // ── schema ──────────────────────────────────────────────────────────

    /** @return list<string> CREATE TABLE statements for the installer + self-heal. */
    public static function schemaSql(): array
    {
        return [
            "CREATE TABLE IF NOT EXISTS `pp_db_servers` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `label` VARCHAR(120) NOT NULL,
                `host` VARCHAR(190) NOT NULL,
                `port` INT DEFAULT 3306,
                `admin_user` VARCHAR(120) DEFAULT NULL,
                `admin_pass` VARCHAR(255) DEFAULT NULL,
                `is_platform` TINYINT(1) DEFAULT 0,
                `grant_host` VARCHAR(120) DEFAULT '%',
                `status` ENUM('active','disabled') DEFAULT 'active',
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

            "CREATE TABLE IF NOT EXISTS `pp_managed_databases` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `server_id` INT NOT NULL,
                `label` VARCHAR(160) DEFAULT NULL,
                `db_name` VARCHAR(120) NOT NULL,
                `db_user` VARCHAR(120) NOT NULL,
                `db_pass` VARCHAR(255) DEFAULT NULL,
                `dedicated` TINYINT(1) DEFAULT 1,
                `assigned_to` INT DEFAULT NULL,
                `status` ENUM('active','suspended') DEFAULT 'active',
                `notes` VARCHAR(255) DEFAULT NULL,
                `created_by` INT DEFAULT NULL,
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                UNIQUE KEY `uniq_server_db` (`server_id`, `db_name`),
                INDEX (`assigned_to`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
        ];
    }

    /**
     * Schema-only entry point for callers that have no service instance and only
     * need the tables to exist — e.g. the replica applying a synced snapshot.
     */
    public static function ensureSchemaStatic(): void
    {
        try { (new self())->ensureSchema(); } catch (\Throwable $e) { /* best effort */ }
    }

    /** Create the tables if missing (idempotent) and seed the platform server row. */
    public function ensureSchema(): void
    {
        $pdo = $this->centralPdo();
        if ($pdo === null) return;
        foreach (self::schemaSql() as $sql) {
            try { $pdo->exec($sql); } catch (\Throwable $e) { /* ignore — best effort */ }
        }

        // Control-panel provisioning columns, added for servers registered before
        // the cPanel/DirectAdmin drivers existed. `driver` defaults to 'sql', so
        // every pre-existing row keeps behaving exactly as it did.
        foreach ([
            'driver'      => "VARCHAR(20) NOT NULL DEFAULT 'sql'",
            'api_port'    => 'INT NOT NULL DEFAULT 0',
            'db_host'     => "VARCHAR(190) NOT NULL DEFAULT ''",
            'name_prefix' => "VARCHAR(60) NOT NULL DEFAULT ''",
        ] as $col => $def) {
            try {
                $has = $pdo->query("SHOW COLUMNS FROM `pp_db_servers` LIKE " . $pdo->quote($col))->fetchAll();
                if (!$has) $pdo->exec("ALTER TABLE `pp_db_servers` ADD COLUMN `{$col}` {$def}");
            } catch (\Throwable $e) { /* older MySQL / no ALTER — additive only */ }
        }

        // Clone tracking (Phase 4). `fingerprint` is what lets an unchanged
        // database be skipped; the other two are for the owner's visibility.
        foreach ([
            'fingerprint' => "CHAR(64) NOT NULL DEFAULT ''",
            'cloned_at'   => 'DATETIME NULL',
            'clone_bytes' => 'BIGINT UNSIGNED NOT NULL DEFAULT 0',
        ] as $col => $def) {
            try {
                $has = $pdo->query("SHOW COLUMNS FROM `pp_managed_databases` LIKE " . $pdo->quote($col))->fetchAll();
                if (!$has) $pdo->exec("ALTER TABLE `pp_managed_databases` ADD COLUMN `{$col}` {$def}");
            } catch (\Throwable $e) { /* additive only */ }
        }
        // Seed the built-in platform server exactly once.
        try {
            $has = (int) $pdo->query("SELECT COUNT(*) FROM `pp_db_servers` WHERE `is_platform` = 1")->fetchColumn();
            if ($has === 0) {
                $cfg = $this->config();
                $st = $pdo->prepare("INSERT INTO `pp_db_servers` (`label`,`host`,`port`,`is_platform`,`grant_host`,`status`) VALUES (?,?,?,1,'%','active')");
                $st->execute(['Platform server', (string) ($cfg['host'] ?? '127.0.0.1'), (int) ($cfg['port'] ?? 3306)]);
            }
        } catch (\Throwable $e) { /* ignore */ }
    }

    // ── servers ─────────────────────────────────────────────────────────

    /** @return array<int,array<string,mixed>> registered servers (no admin passwords) + db counts */
    public function servers(): array
    {
        $this->ensureSchema();
        $pdo = $this->centralPdo();
        if ($pdo === null) return [];
        $rows = $pdo->query(
            "SELECT s.id, s.label, s.host, s.port, s.is_platform, s.grant_host, s.status, s.admin_user,
                    s.driver, s.name_prefix,
                    (SELECT COUNT(*) FROM `pp_managed_databases` d WHERE d.server_id = s.id) AS db_count
             FROM `pp_db_servers` s ORDER BY s.is_platform DESC, s.label ASC"
        )->fetchAll(\PDO::FETCH_ASSOC);
        return array_map(static function (array $r): array {
            return [
                'id'          => (int) $r['id'],
                'label'       => (string) $r['label'],
                'host'        => (string) $r['host'],
                'port'        => (int) $r['port'],
                'is_platform' => (int) $r['is_platform'] === 1,
                'grant_host'  => (string) $r['grant_host'],
                'driver'      => (string) ($r['driver'] ?? 'sql'),
                'name_prefix' => (string) ($r['name_prefix'] ?? ''),
                'status'      => (string) $r['status'],
                'admin_user'  => (string) ($r['admin_user'] ?? ''),
                'db_count'    => (int) $r['db_count'],
            ];
        }, $rows ?: []);
    }

    /** @return array<string,mixed>|null the raw server row (WITH admin creds) — internal use only */
    private function serverRow(int $id): ?array
    {
        $pdo = $this->centralPdo();
        if ($pdo === null) return null;
        $st = $pdo->prepare("SELECT * FROM `pp_db_servers` WHERE id = ?");
        $st->execute([$id]);
        $row = $st->fetch(\PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    /** Resolve the admin connection creds for a server (platform → config/database.php). */
    private function serverCreds(array $server): array
    {
        if ((int) ($server['is_platform'] ?? 0) === 1) {
            $cfg = $this->config();
            // The app's day-to-day account may be unprivileged (typical on cPanel).
            // If the owner has stored a dedicated privileged provisioning login on the
            // platform row, use THAT for create/grant; otherwise fall back to config.
            $override = trim((string) ($server['admin_user'] ?? '')) !== '';
            return [
                'host' => (string) ($cfg['host'] ?? '127.0.0.1'),
                'port' => (int) ($cfg['port'] ?? 3306),
                'user' => $override ? (string) $server['admin_user'] : (string) ($cfg['user'] ?? ''),
                'pass' => $override ? (string) ($server['admin_pass'] ?? '') : (string) ($cfg['pass'] ?? ''),
                'grant_host' => (string) ($server['grant_host'] ?? '%'),
            ];
        }
        return [
            'host' => (string) $server['host'],
            'port' => (int) $server['port'],
            'user' => (string) ($server['admin_user'] ?? ''),
            'pass' => (string) ($server['admin_pass'] ?? ''),
            'grant_host' => (string) ($server['grant_host'] ?? '%'),
        ];
    }

    /**
     * Test a server's admin credentials and report what it can actually do.
     * canCreateDb is verified with a live create/drop probe (most reliable);
     * canCreateUser is read from SHOW GRANTS.
     *
     * @return array{success:bool,message:string,canCreateUser:bool,canCreateDb:bool}
     */
    public function testServer(string $host, int $port, string $user, string $pass): array
    {
        try {
            $pdo = new \PDO(
                "mysql:host={$host};port={$port};charset=utf8mb4",
                $user, $pass,
                [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, \PDO::ATTR_TIMEOUT => 6]
            );
            $canCreateUser = false;
            try {
                foreach ($pdo->query('SHOW GRANTS')->fetchAll(\PDO::FETCH_COLUMN) as $g) {
                    if (stripos((string) $g, 'CREATE USER') !== false || stripos((string) $g, 'ALL PRIVILEGES ON *.*') !== false) {
                        $canCreateUser = true; break;
                    }
                }
            } catch (\Throwable $e) { /* SHOW GRANTS unavailable — assume limited */ }
            // Live probe: can this account actually CREATE (and DROP) a database?
            $canCreateDb = false;
            $probe = 'pp_probe_' . bin2hex(random_bytes(4));
            try {
                $pdo->exec("CREATE DATABASE IF NOT EXISTS `{$probe}`");
                $canCreateDb = true;
                try { $pdo->exec("DROP DATABASE `{$probe}`"); } catch (\Throwable $e) { /* leave it — rare */ }
            } catch (\Throwable $e) { $canCreateDb = false; }
            $msg = $canCreateDb
                ? ($canCreateUser ? 'Connected — can create databases and dedicated logins.' : 'Connected — can create databases (but not dedicated logins; databases will share this login).')
                : 'Connected, but this account cannot create databases. Use a MySQL user with the CREATE privilege.';
            return ['success' => true, 'message' => $msg, 'canCreateUser' => $canCreateUser, 'canCreateDb' => $canCreateDb];
        } catch (\Throwable $e) {
            return ['success' => false, 'message' => $this->friendlyConnError($e, $host, $port), 'canCreateUser' => false, 'canCreateDb' => false];
        }
    }

    /** Turn a raw PDO connect exception into a human, actionable message. */
    private function friendlyConnError(\Throwable $e, string $host, int $port): string
    {
        $raw = $e->getMessage();
        $code = ($e instanceof \PDOException && isset($e->errorInfo[1])) ? (int) $e->errorInfo[1] : 0;
        if ($code === 1045 || stripos($raw, 'access denied') !== false) {
            return "The username or password was rejected by {$host}. Double-check the admin user and password.";
        }
        if ($code === 2002 || stripos($raw, 'timed out') !== false || stripos($raw, 'refused') !== false || stripos($raw, 'no route') !== false || stripos($raw, "can't connect") !== false) {
            return "Couldn’t reach a MySQL server at {$host}:{$port}. Check the host and port, make sure the server allows remote connections, and that a firewall isn’t blocking it — many shared/cPanel hosts block remote port 3306 by default.";
        }
        if (stripos($raw, 'unknown mysql server host') !== false || stripos($raw, 'getaddrinfo') !== false || stripos($raw, 'name or service not known') !== false) {
            return "The host “{$host}” could not be resolved. Check the spelling of the hostname.";
        }
        return $raw;
    }

    /**
     * Store/replace the privileged provisioning credentials on a server row
     * (owner action). Verifies the login before saving. Works for the platform
     * row too — the app keeps using its normal config login for day-to-day work,
     * while provisioning uses this stored privileged account.
     *
     * @return array{success:bool,message:string,canCreateUser?:bool,canCreateDb?:bool}
     */
    public function setServerCredentials(int $id, string $user, string $pass, string $grantHost = ''): array
    {
        $this->ensureSchema();
        $pdo = $this->centralPdo();
        if ($pdo === null) return ['success' => false, 'message' => 'No central database.'];
        $srv = $this->serverRow($id);
        if (!$srv) return ['success' => false, 'message' => 'Server not found.'];
        if (trim($user) === '') return ['success' => false, 'message' => 'Enter a MySQL username.'];

        // Resolve where this server lives (platform → config host/port).
        $creds = $this->serverCreds($srv);
        $probe = $this->testServer((string) $creds['host'], (int) $creds['port'], $user, $pass);
        if (!$probe['success']) return ['success' => false, 'message' => 'Could not connect: ' . $probe['message']];
        if (empty($probe['canCreateDb'])) {
            return ['success' => false, 'message' => 'That account connected, but it still cannot create databases. Provisioning needs a user with the CREATE privilege.'];
        }
        $gh = ($grantHost !== '' && preg_match('/^[a-zA-Z0-9_.%-]+$/', $grantHost)) ? $grantHost : (string) ($srv['grant_host'] ?? '%');
        $st = $pdo->prepare("UPDATE `pp_db_servers` SET `admin_user` = ?, `admin_pass` = ?, `grant_host` = ? WHERE id = ?");
        $st->execute([$user, $pass, $gh ?: '%', $id]);
        return ['success' => true, 'message' => 'Provisioning credentials saved and verified.', 'canCreateUser' => (bool) $probe['canCreateUser'], 'canCreateDb' => true];
    }

    /** Register an external server (after a successful test). Returns the new id or 0. */
    /**
     * Register an external server.
     *
     * $driver/$apiPort/$dbHost/$prefix are appended rather than inserted into the
     * signature, so every existing caller keeps working and simply gets the
     * direct-SQL behaviour it already had.
     */
    public function addServer(
        string $label, string $host, int $port, string $user, string $pass, string $grantHost = '%',
        string $driver = 'sql', int $apiPort = 0, string $dbHost = '', string $prefix = ''
    ): int {
        $this->ensureSchema();
        $pdo = $this->centralPdo();
        if ($pdo === null) return 0;

        $driver = \PpPhp\Core\Databases\Drivers\DriverFactory::normalise($driver);
        $apiPort = $apiPort > 0 ? $apiPort : \PpPhp\Core\Databases\Drivers\DriverFactory::defaultPort($driver);

        $st = $pdo->prepare(
            "INSERT INTO `pp_db_servers`
                (`label`,`host`,`port`,`admin_user`,`admin_pass`,`is_platform`,`grant_host`,`status`,
                 `driver`,`api_port`,`db_host`,`name_prefix`)
             VALUES (?,?,?,?,?,0,?,'active',?,?,?,?)"
        );
        $st->execute([
            mb_substr($label !== '' ? $label : $host, 0, 120),
            $host, $port ?: 3306, $user, $pass,
            preg_match('/^[a-zA-Z0-9_.%-]+$/', $grantHost) ? $grantHost : '%',
            $driver, $apiPort, mb_substr($dbHost, 0, 190), mb_substr($prefix, 0, 60),
        ]);
        return (int) $pdo->lastInsertId();
    }

    /**
     * Test a server described by a driver + credentials, without registering it.
     * Used by the "Test" button before Add.
     *
     * @param array<string,mixed> $in {driver,host,port,api_port,user,pass,db_host,grant_host}
     */
    public function testServerSpec(array $in): array
    {
        $driver = \PpPhp\Core\Databases\Drivers\DriverFactory::normalise((string) ($in['driver'] ?? 'sql'));
        try {
            $d = \PpPhp\Core\Databases\Drivers\DriverFactory::make([
                'driver'     => $driver,
                'host'       => (string) ($in['host'] ?? ''),
                'port'       => (int) ($in['port'] ?? 3306),
                'api_port'   => (int) ($in['api_port'] ?? 0),
                'admin_user' => (string) ($in['user'] ?? ''),
                'admin_pass' => (string) ($in['pass'] ?? ''),
                'db_host'    => (string) ($in['db_host'] ?? ''),
                'grant_host' => (string) ($in['grant_host'] ?? '%'),
            ]);
            return $d->test() + ['driver' => $driver];
        } catch (\Throwable $e) {
            return ['success' => false, 'message' => $e->getMessage(), 'driver' => $driver,
                    'canCreateDb' => false, 'canCreateUser' => false, 'prefix' => '', 'maxNameLength' => 0];
        }
    }

    /**
     * Edit a registered external server.
     *
     * Two rules make this safe to expose:
     *   • a BLANK secret means "keep the stored one", so the existing token never
     *     has to be sent to the browser just to edit the label;
     *   • the new settings are TESTED before they are saved, so a typo cannot
     *     leave a working server broken.
     *
     * @param array<string,mixed> $in {label,driver,host,port,api_port,user,pass,db_host,grant_host}
     * @return array{success:bool,message:string,prefix?:string}
     */
    public function updateServer(int $id, array $in): array
    {
        $this->ensureSchema();
        $pdo = $this->centralPdo();
        if ($pdo === null) return ['success' => false, 'message' => 'No central database.'];

        $srv = $this->serverRow($id);
        if (!$srv) return ['success' => false, 'message' => 'Server not found.'];
        if ((int) ($srv['is_platform'] ?? 0) === 1) {
            return ['success' => false, 'message' => 'The platform server is configured from config/database.php — use “Set account” instead.'];
        }

        $driver  = \PpPhp\Core\Databases\Drivers\DriverFactory::normalise((string) ($in['driver'] ?? $srv['driver'] ?? 'sql'));
        $secret  = (string) ($in['pass'] ?? '');
        if ($secret === '') $secret = (string) ($srv['admin_pass'] ?? '');   // keep what we hold

        $spec = [
            'driver'     => $driver,
            'host'       => trim((string) ($in['host'] ?? $srv['host'])),
            'port'       => (int) ($in['port'] ?? $srv['port'] ?? 3306),
            'api_port'   => (int) ($in['api_port'] ?? $srv['api_port'] ?? 0),
            'user'       => trim((string) ($in['user'] ?? $srv['admin_user'])),
            'pass'       => $secret,
            'db_host'    => (string) ($in['db_host'] ?? $srv['db_host'] ?? ''),
            'grant_host' => (string) ($in['grant_host'] ?? $srv['grant_host'] ?? '%'),
        ];

        $test = $this->testServerSpec($spec);
        if (empty($test['success'])) {
            return ['success' => false, 'message' => 'Not saved — the new settings could not connect: ' . (string) $test['message']];
        }
        if (empty($test['canCreateDb'])) {
            return ['success' => false, 'message' => 'Not saved — ' . (string) $test['message']];
        }

        $st = $pdo->prepare(
            "UPDATE `pp_db_servers`
                SET `label` = ?, `host` = ?, `port` = ?, `admin_user` = ?, `admin_pass` = ?,
                    `grant_host` = ?, `driver` = ?, `api_port` = ?, `db_host` = ?, `name_prefix` = ?
              WHERE `id` = ? AND `is_platform` = 0"
        );
        $st->execute([
            mb_substr(trim((string) ($in['label'] ?? $srv['label'])) ?: $spec['host'], 0, 120),
            $spec['host'], $spec['port'] ?: 3306, $spec['user'], $spec['pass'],
            preg_match('/^[a-zA-Z0-9_.%-]+$/', (string) $spec['grant_host']) ? $spec['grant_host'] : '%',
            $driver,
            $spec['api_port'] ?: \PpPhp\Core\Databases\Drivers\DriverFactory::defaultPort($driver),
            mb_substr((string) $spec['db_host'], 0, 190),
            mb_substr((string) ($test['prefix'] ?? ''), 0, 60),
            $id,
        ]);

        return ['success' => true, 'message' => 'Server updated and verified.', 'prefix' => (string) ($test['prefix'] ?? '')];
    }

    /**
     * One server's editable settings for the edit form. Deliberately WITHOUT the
     * stored secret — a token should never travel back to a browser.
     *
     * @return array<string,mixed>|null
     */
    public function serverForEdit(int $id): ?array
    {
        $srv = $this->serverRow($id);
        if (!$srv || (int) ($srv['is_platform'] ?? 0) === 1) return null;
        return [
            'id'         => (int) $srv['id'],
            'label'      => (string) $srv['label'],
            'host'       => (string) $srv['host'],
            'port'       => (int) $srv['port'],
            'api_port'   => (int) ($srv['api_port'] ?? 0),
            'driver'     => \PpPhp\Core\Databases\Drivers\DriverFactory::normalise((string) ($srv['driver'] ?? 'sql')),
            'user'       => (string) ($srv['admin_user'] ?? ''),
            'db_host'    => (string) ($srv['db_host'] ?? ''),
            'grant_host' => (string) ($srv['grant_host'] ?? '%'),
            'db_host'    => (string) ($srv['db_host'] ?? ''),
            'prefix'     => (string) ($srv['name_prefix'] ?? ''),
            'hasSecret'  => trim((string) ($srv['admin_pass'] ?? '')) !== '',
        ];
    }

    /**
     * The stored secret for a server, for re-testing an edit where the field was
     * left blank. Server-side only — this value is never sent to a browser.
     */
    public function storedSecret(int $id): string
    {
        $srv = $this->serverRow($id);
        return ($srv && (int) ($srv['is_platform'] ?? 0) === 0) ? (string) ($srv['admin_pass'] ?? '') : '';
    }


    /**
     * The backup is a REPLICA — it must never originate a change.
     *
     * Replication overwrites the backup's rows from the primary on every sync, so
     * anything created here would be silently destroyed minutes later, and any
     * database it provisioned on its own panel would be orphaned with no
     * catalogue row pointing at it. Refusing up front turns a silent data-loss
     * bug into an obvious message.
     *
     * Reads stay open, so the owner can still inspect the whole estate from the
     * backup while the primary is down.
     *
     * SCOPE — this guards the replicated CATALOGUE (`pp_managed_databases`) only.
     * Registering a SERVER (`pp_db_servers`) is deliberately NOT guarded: each
     * side runs on its own hosting account and must hold its own panel address
     * and its own API credential, so those rows are per-side infrastructure that
     * replication must never carry. Blocking them stopped the secondary from
     * registering its own local panel at all.
     */
    private function assertWritable(): ?array
    {
        try {
            if (!\PpPhp\Core\Licensing\ServerRole::isBackup()) return null;
        } catch (\Throwable $e) {
            return null;                       // role unknown → behave as primary
        }
        $main = class_exists(\PpPhp\Core\Licensing\ServerRole::class) ? \PpPhp\Core\Licensing\ServerRole::mainUrl() : '';
        return ['success' => false, 'message' =>
            'This is a BACKUP server, so it mirrors the primary and cannot create or change databases — '
            . 'the next sync would overwrite anything done here. Make the change on the primary'
            . ($main !== '' ? ' (' . $main . ')' : '') . '. '
            . 'If the primary is permanently gone, promote this server first.'];
    }

    /**
     * Which managed databases actually need cloning right now.
     *
     * The whole point of the fingerprint: an idle database is skipped entirely,
     * so a nightly run over a large estate copies only what changed. Reading the
     * fingerprint is one `information_schema` query per database — no table scan.
     *
     * @return array<int,array{id:int,db_name:string,fingerprint:string,changed:bool}>
     */
    public function cloneCandidates(): array
    {
        // Replication is server-only; the digest/clone classes are stripped from
        // client bundles, so this method simply has nothing to offer there.
        if (!class_exists(\PpPhp\Core\Databases\TableDigest::class)) return [];
        $this->ensureSchema();
        $pdo = $this->centralPdo();
        if ($pdo === null) return [];
        $rows = $pdo->query("SELECT `id`,`db_name`,`fingerprint` FROM `pp_managed_databases` WHERE `status` = 'active'")
            ->fetchAll(\PDO::FETCH_ASSOC) ?: [];

        $out = [];
        foreach ($rows as $r) {
            $conn = $this->connectionFor((int) $r['id']);
            if ($conn === null) continue;
            // Exact per-table digests, not information_schema estimates — those are
            // blind to UPDATEs and cached for 24h (see TableDigest).
            // null = unreachable (skip, do not guess); [] = reachable but has no
            // tables yet, which is a perfectly normal new project and MUST still be
            // reported, or it vanishes from the run and the totals look wrong.
            $digests = TableDigest::read($conn);
            if ($digests === null) continue;
            $fp = TableDigest::fold($digests);
            $out[] = [
                'id'          => (int) $r['id'],
                'db_name'     => (string) $r['db_name'],
                'fingerprint' => $fp,
                'tables'      => $digests,
                'changed'     => $fp !== (string) ($r['fingerprint'] ?? ''),
            ];
        }
        return $out;
    }


    // ── Phase 4 · replica targets (backup side) ────────────────────────

    /**
     * Ensure a physical replica database exists on THIS server, and return a
     * connection to it.
     *
     * The mapping is persisted in `pp_db_replicas` because we cannot recompute it:
     * a control panel silently prepends the account name, so asking cPanel for
     * `replica_orders` yields `ppradhan_replica_orders`. Without the record, the
     * second run would either fail on "already exists" or create a duplicate under
     * a fresh name — so what the panel ACTUALLY created is stored the first time
     * and reused thereafter.
     *
     * `pp_db_replicas` is deliberately local-only and never replicated: it
     * describes physical storage on one machine.
     *
     * Returns a RESULT rather than null-on-failure: there are several distinct
     * ways this fails (no catalogue connection, no platform server row, the panel
     * refusing the create) and collapsing them into null threw away the only
     * information an operator needs. The panel's own message is passed through.
     *
     * @return array{success:bool,message:string,replica?:string,conn?:array<string,mixed>}
     */
    public function ensureReplica(string $sourceDbName): array
    {
        if (!class_exists(\PpPhp\Core\Databases\DbCloner::class)) {
            return ['success' => false, 'message' => 'Replication is not available on this edition.'];
        }
        $this->ensureSchema();
        $this->ensureReplicaSchema();

        $pdo = $this->centralPdo();
        if ($pdo === null) {
            return ['success' => false, 'message' => 'This server cannot reach its own catalogue database.'];
        }

        $st = $pdo->prepare('SELECT * FROM `pp_db_replicas` WHERE `source_db` = ?');
        $st->execute([$sourceDbName]);
        $known = $st->fetch(\PDO::FETCH_ASSOC) ?: null;

        // Reconnecting to a replica we already created needs only an ADDRESS, never
        // provisioning rights — so this path must not require a server that can
        // create databases, or an estate that has already been cloned would start
        // failing the moment panel credentials were rotated away.
        if ($known) {
            $addr = $this->platformServerRow();
            $ac   = $addr !== null ? $this->serverCreds($addr) : $this->config();
            $conn = [
                'host' => (string) (($addr['db_host'] ?? '') ?: ($ac['host'] ?? '127.0.0.1')),
                'port' => (int) (($addr['port'] ?? 0) ?: ($ac['port'] ?? 3306)),
                'dbname' => (string) $known['replica_db'],
                'user' => (string) $known['replica_user'],
                'pass' => (string) $known['replica_pass'],
            ];

            // The mapping row is bookkeeping, not proof. An owner can drop the
            // replica from phpMyAdmin at any time, and trusting the row would then
            // restore into a database that no longer exists. Verify, and if it has
            // gone, forget the mapping and provision a fresh one below.
            if ($this->schemaExists($conn)) {
                return ['success' => true, 'message' => 'Using the existing replica.',
                        'replica' => (string) $known['replica_db'], 'conn' => $conn];
            }
            $pdo->prepare('DELETE FROM `pp_db_replicas` WHERE `id` = ?')->execute([(int) $known['id']]);
        }

        $server = $this->replicaHostServer();
        if ($server === null) {
            return ['success' => false, 'message' =>
                'No server on this install can create databases. Register this server\'s own control panel '
                . '(cPanel/DirectAdmin) under Servers, or set a privileged provisioning login on the platform row.'];
        }
        $creds = $this->serverCreds($server);


        // Reserve room for the prefix this panel will prepend, so the final name
        // cannot overflow MySQL's 64-char limit.
        $desired = DbCloner::replicaName($sourceDbName, strlen((string) ($server['name_prefix'] ?? '')));
        $user    = substr('r_' . substr(md5($sourceDbName), 0, 10), 0, 16);
        $pass    = bin2hex(random_bytes(12));

        $driver = \PpPhp\Core\Databases\Drivers\DriverFactory::make($server);
        $made   = $driver->createDatabase($desired, $user, $pass);
        if (empty($made['success'])) {
            // Pass the panel's own words through — this is the failure that
            // actually happens in the field, and a generic message makes it
            // undiagnosable without server access.
            return ['success' => false, 'message' =>
                'The ' . $driver->id() . ' server refused to create "' . $desired . '": '
                . trim((string) ($made['message'] ?? 'no reason given')) . '.'];
        }

        // Store the names the panel ACTUALLY created, not the ones we asked for.
        // The driver contract uses camelCase `dbName`/`dbUser` (ProvisionDriver);
        // reading snake_case silently recorded the UNPREFIXED name we asked for,
        // which is precisely the mismatch this table exists to prevent.
        $actualDb   = (string) (($made['dbName'] ?? '') ?: $desired);
        $actualUser = (string) (($made['dbUser'] ?? '') ?: $user);

        $ins = $pdo->prepare('INSERT INTO `pp_db_replicas`
            (`source_db`,`replica_db`,`replica_user`,`replica_pass`,`server_id`,`created_at`)
            VALUES (?,?,?,?,?,UTC_TIMESTAMP())');
        $ins->execute([$sourceDbName, $actualDb, $actualUser, $pass, (int) $server['id']]);

        return ['success' => true,
                'message' => 'Created ' . $actualDb . ' on ' . (string) ($server['label'] ?? 'this server') . '.',
                'replica' => $actualDb, 'conn' => [
            'host' => (string) ($server['db_host'] ?: $creds['host']),
            'port' => (int) ($server['port'] ?: $creds['port']),
            'dbname' => $actualDb, 'user' => $actualUser, 'pass' => $pass,
        ]];
    }


    /**
     * Which server should physically hold the replicas?
     *
     * NOT simply the platform row. On shared hosting that row is a plain `sql`
     * server using the app's day-to-day account, which typically cannot
     * `CREATE DATABASE` at all (MySQL 1044 — the same trap as bug-026). The
     * console's real provisioning capability lives on its OWN control-panel row,
     * which Phase 1 registers as an "external" server pointing at its own host.
     *
     * So: prefer an active panel driver that actually holds a credential, and
     * fall back to the platform row only if there is none.
     *
     * Servers replicated from the other side are excluded automatically — the
     * mirror records those as `disabled` with no credential precisely so they can
     * never be provisioned against from here.
     */
    private function replicaHostServer(): ?array
    {
        $pdo = $this->centralPdo();
        if ($pdo === null) return null;

        $rows = $pdo->query('SELECT * FROM `pp_db_servers`')->fetchAll(\PDO::FETCH_ASSOC) ?: [];
        $platform = $this->platformServerRow();

        return self::chooseReplicaHost(
            $rows,
            $platform,
            fn(): bool => $this->canConfigAccountCreate()
        );
    }

    /**
     * The selection RULE, as a pure function.
     *
     * Kept free of database and network access on purpose: this decides which
     * machine gets a real `CREATE DATABASE`, so it must be exhaustively testable
     * without a test ever being able to touch a live control panel. (It could,
     * and did — a first version of the test created two databases on a
     * production cPanel account before this was extracted.)
     *
     * @param array<int,array<string,mixed>> $rows      every server row
     * @param array<string,mixed>|null       $platform  the platform row, if any
     * @param callable():bool                $probe     may the config account create databases?
     * @return array<string,mixed>|null
     */
    public static function chooseReplicaHost(array $rows, ?array $platform, callable $probe): ?array
    {
        $usable = [];
        foreach ($rows as $r) {
            if ((string) ($r['status'] ?? '') !== 'active') continue;
            if (!in_array((string) ($r['driver'] ?? ''), ['cpanel', 'directadmin'], true)) continue;
            // A replicated row from the other side carries no credential — it
            // could not authenticate, and provisioning onto the peer's panel
            // would be actively wrong.
            if (trim((string) ($r['admin_pass'] ?? '')) === '') continue;
            $usable[] = $r;
        }
        if ($usable !== []) {
            // is_platform DESC (b first), then id ASC (a first) — deterministic, so a
            // replica lands on the same server every run.
            usort($usable, static fn($a, $b) =>
                [(int) ($b['is_platform'] ?? 0), (int) ($a['id'] ?? 0)]
                <=> [(int) ($a['is_platform'] ?? 0), (int) ($b['id'] ?? 0)]);
            return $usable[0];
        }

        // No panel: the platform row is usable only if a privileged provisioning
        // login was stored on it (plain runtime creds cannot CREATE DATABASE).
        if ($platform !== null && trim((string) ($platform['admin_user'] ?? '')) !== '') return $platform;

        // Last resort — a dev box where the config account itself is privileged.
        if ($platform !== null && $probe()) return $platform;

        return null;
    }

    /** Does the app's own config account have CREATE DATABASE? (dev boxes usually do.) */
    private function canConfigAccountCreate(): bool
    {
        try {
            $probe = 'pp_probe_' . bin2hex(random_bytes(4));
            $pdo = $this->centralPdo();
            if ($pdo === null) return false;
            $pdo->exec('CREATE DATABASE `' . $probe . '`');
            $pdo->exec('DROP DATABASE `' . $probe . '`');
            return true;
        } catch (\Throwable $e) {
            return false;
        }
    }



    /**
     * The replica's CURRENT per-table digests, read from the replica itself.
     *
     * Deliberately not cached and not stored: this is the comparison state for
     * incremental cloning, and any stored copy could disagree with reality (an
     * owner dropping the replica in phpMyAdmin is exactly what produced bug-048).
     * Reading the truth costs a few milliseconds and cannot go stale.
     *
     * @return array<string,string> empty when there is no replica yet
     */
    public function replicaDigests(string $sourceDbName): array
    {
        if (!class_exists(\PpPhp\Core\Databases\TableDigest::class)) return [];
        $this->ensureSchema();
        $this->ensureReplicaSchema();
        $pdo = $this->centralPdo();
        if ($pdo === null) return [];

        $st = $pdo->prepare('SELECT * FROM `pp_db_replicas` WHERE `source_db` = ?');
        $st->execute([$sourceDbName]);
        $known = $st->fetch(\PDO::FETCH_ASSOC) ?: null;
        if (!$known) return [];

        $addr = $this->platformServerRow();
        $ac   = $addr !== null ? $this->serverCreds($addr) : $this->config();
        return TableDigest::of([
            'host' => (string) (($addr['db_host'] ?? '') ?: ($ac['host'] ?? '127.0.0.1')),
            'port' => (int) (($addr['port'] ?? 0) ?: ($ac['port'] ?? 3306)),
            'dbname' => (string) $known['replica_db'],
            'user' => (string) $known['replica_user'],
            'pass' => (string) $known['replica_pass'],
        ]);
    }

    /** Can we actually connect to this database right now? */
    private function schemaExists(array $conn): bool
    {
        try {
            new \PDO(sprintf('mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4',
                (string) $conn['host'], (int) $conn['port'], (string) $conn['dbname']),
                (string) $conn['user'], (string) $conn['pass'],
                [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, \PDO::ATTR_TIMEOUT => 10]);
            return true;
        } catch (\Throwable $e) {
            return false;
        }
    }

    /**
     * Is the replica for this source database actually present, with content?
     *
     * Used to decide whether an "unchanged" database may be SKIPPED. Comparing
     * fingerprints alone is not enough: the fingerprint records what we last
     * restored, so if someone drops the replica by hand the backup would skip it
     * forever and report itself current while holding nothing.
     *
     * @return array{present:bool,tables:int,replica:string}
     */
    public function replicaStatus(string $sourceDbName): array
    {
        $this->ensureSchema();
        $this->ensureReplicaSchema();
        $pdo = $this->centralPdo();
        if ($pdo === null) return ['present' => false, 'tables' => 0, 'replica' => ''];

        $st = $pdo->prepare('SELECT * FROM `pp_db_replicas` WHERE `source_db` = ?');
        $st->execute([$sourceDbName]);
        $known = $st->fetch(\PDO::FETCH_ASSOC) ?: null;
        if (!$known) return ['present' => false, 'tables' => 0, 'replica' => ''];

        $addr = $this->platformServerRow();
        $ac   = $addr !== null ? $this->serverCreds($addr) : $this->config();
        try {
            $rp = new \PDO(sprintf('mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4',
                (string) (($addr['db_host'] ?? '') ?: ($ac['host'] ?? '127.0.0.1')),
                (int) (($addr['port'] ?? 0) ?: ($ac['port'] ?? 3306)),
                (string) $known['replica_db']),
                (string) $known['replica_user'], (string) $known['replica_pass'],
                [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, \PDO::ATTR_TIMEOUT => 10]);
            $n = (int) $rp->query('SELECT COUNT(*) FROM information_schema.TABLES WHERE table_schema = DATABASE()')->fetchColumn();
            return ['present' => true, 'tables' => $n, 'replica' => (string) $known['replica_db']];
        } catch (\Throwable $e) {
            return ['present' => false, 'tables' => 0, 'replica' => (string) $known['replica_db']];
        }
    }

    /** The row describing the server this console itself runs on. */
    private function platformServerRow(): ?array
    {
        $pdo = $this->centralPdo();
        if ($pdo === null) return null;
        $r = $pdo->query('SELECT * FROM `pp_db_servers` WHERE `is_platform` = 1 LIMIT 1')->fetch(\PDO::FETCH_ASSOC);
        return $r ?: null;
    }

    private function ensureReplicaSchema(): void
    {
        $pdo = $this->centralPdo();
        if ($pdo === null) return;
        $pdo->exec("CREATE TABLE IF NOT EXISTS `pp_db_replicas` (
            `id` INT AUTO_INCREMENT PRIMARY KEY,
            `source_db` VARCHAR(190) NOT NULL,
            `replica_db` VARCHAR(190) NOT NULL,
            `replica_user` VARCHAR(64) NOT NULL,
            `replica_pass` VARCHAR(190) NOT NULL,
            `server_id` INT NOT NULL DEFAULT 0,
            `created_at` DATETIME NULL,
            UNIQUE KEY `uq_source` (`source_db`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
        // Self-heal for installs created before server_id was recorded.
        try {
            $has = $pdo->query("SHOW COLUMNS FROM `pp_db_replicas` LIKE 'server_id'")->fetchAll();
            if (!$has) $pdo->exec('ALTER TABLE `pp_db_replicas` ADD COLUMN `server_id` INT NOT NULL DEFAULT 0');
        } catch (\Throwable $e) {
            // a read-only catalogue is reported elsewhere; never fatal here
        }
    }

    /**
     * Record that a database has been cloned at its current fingerprint.
     *
     * Written only AFTER a successful clone — so a failed run retries next time
     * instead of marking the database as done and silently skipping it forever.
     */
    public function markCloned(int $id, string $fingerprint, int $bytes): bool
    {
        try {
            DB::Query(
                'UPDATE `pp_managed_databases` SET `fingerprint` = ?, `cloned_at` = NOW(), `clone_bytes` = ? WHERE `id` = ?',
                [$fingerprint, $bytes, $id]
            );
            return true;
        } catch (\Throwable $e) {
            return false;
        }
    }

    /** Remove an external server (never the platform one, and only if it holds no databases). */
    public function deleteServer(int $id): array
    {
        $pdo = $this->centralPdo();
        if ($pdo === null) return ['success' => false, 'message' => 'No central database.'];
        $srv = $this->serverRow($id);
        if (!$srv) return ['success' => false, 'message' => 'Server not found.'];
        if ((int) $srv['is_platform'] === 1) return ['success' => false, 'message' => 'The platform server cannot be removed.'];
        $count = (int) $this->one("SELECT COUNT(*) FROM `pp_managed_databases` WHERE server_id = ?", [$id]);
        if ($count > 0) return ['success' => false, 'message' => "Remove its {$count} database(s) first."];
        $pdo->prepare("DELETE FROM `pp_db_servers` WHERE id = ?")->execute([$id]);
        return ['success' => true, 'message' => 'Server removed.'];
    }

    // ── databases ───────────────────────────────────────────────────────

    /**
     * Provision a brand-new database on a server with a dedicated MySQL login
     * (GRANTed to only that schema). Optionally assign it to a user immediately.
     *
     * @return array{success:bool,message:string,id?:int,db_name?:string}
     */
    public function provision(int $serverId, string $label, ?int $assignTo, ?int $ownerId, string $seedFrom = ''): array
    {
        $this->ensureSchema();
        if ($blocked = $this->assertWritable()) return $blocked;
        $srv = $this->serverRow($serverId);
        if (!$srv) return ['success' => false, 'message' => 'Unknown server.'];
        $creds = $this->serverCreds($srv);
        if ($creds['user'] === '') return ['success' => false, 'message' => 'This server has no admin credentials configured.'];

        $tag     = $this->slugTag($label !== '' ? $label : ('u' . (int) $assignTo));
        $dbName  = 'pp_db_' . $tag . '_' . $this->rand(4);
        $dbUser  = 'ppu_' . $this->rand(12);            // ≤ 32 chars (MySQL limit)
        $dbPass  = $this->randPassword(24);             // satisfies validate_password
        $ghost   = $creds['grant_host'] !== '' ? $creds['grant_host'] : '%';
        $dedicated = true;

        // ── Control-panel servers (cPanel / DirectAdmin) ──────────────────
        // These cannot be provisioned over SQL at all — the panel does it for us
        // over HTTPS. Handled first and returned early so the direct-SQL path
        // below is left exactly as it was.
        $driverId = \PpPhp\Core\Databases\Drivers\DriverFactory::normalise((string) ($srv['driver'] ?? 'sql'));
        if ($driverId !== \PpPhp\Core\Databases\Drivers\DriverFactory::SQL) {
            $driver = \PpPhp\Core\Databases\Drivers\DriverFactory::make(
                $srv + ['admin_user' => $creds['user'], 'admin_pass' => $creds['pass']]
            );
            // Panels bolt on their own `<account>_` prefix and cap the total at
            // 64 chars, so ask for something short rather than our full name.
            $res = $driver->createDatabase($tag . $this->rand(4), 'u' . $this->rand(8), $dbPass);
            if (empty($res['success'])) {
                return ['success' => false, 'message' => (string) ($res['message'] ?? 'Provisioning failed.')];
            }
            // Store the names the PANEL actually created, never the ones we asked
            // for — they differ by the prefix, and the catalogue must match reality.
            return $this->recordDatabase(
                $serverId, $label,
                (string) $res['dbName'], (string) $res['dbUser'], $dbPass,
                !empty($res['dedicated']), $assignTo, $ownerId
            );
        }

        try {
            $admin = new \PDO(
                "mysql:host={$creds['host']};port={$creds['port']};charset=utf8mb4",
                $creds['user'], $creds['pass'],
                [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, \PDO::ATTR_TIMEOUT => 8]
            );
        } catch (\Throwable $e) {
            return ['success' => false, 'message' => $this->friendlyConnError($e, (string) $creds['host'], (int) $creds['port'])];
        }

        $safeDb = preg_replace('/[^A-Za-z0-9_]/', '', $dbName);
        try {
            $admin->exec("CREATE DATABASE IF NOT EXISTS `{$safeDb}` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
        } catch (\Throwable $e) {
            // Most common on shared/cPanel hosting: the MySQL account cannot CREATE
            // DATABASE via SQL. Give an actionable message instead of a raw SQL error.
            if ($this->isPrivilegeError($e)) {
                return ['success' => false, 'message' =>
                    "The MySQL account “{$creds['user']}” isn’t allowed to create databases on this server. "
                    . "On shared/cPanel hosting this is expected — create the database in your hosting panel, "
                    . "then register & assign it here, or point the platform at a MySQL user that has the "
                    . "CREATE and CREATE USER privileges."];
            }
            return ['success' => false, 'message' => 'Could not create the database: ' . $e->getMessage()];
        }

        // Dedicated login scoped to just this schema. Fall back to the admin
        // credentials if the server can't create users (limited grant).
        try {
            // CREATE USER / GRANT can't be prepared statements in MySQL, so the
            // identifiers are quoted literals. db_user/host/pass are generated
            // from a safe alphabet, so quoting is belt-and-braces.
            $u = $admin->quote($dbUser) . '@' . $admin->quote($ghost);
            $admin->exec("CREATE USER {$u} IDENTIFIED BY " . $admin->quote($dbPass));
            $admin->exec("GRANT ALL PRIVILEGES ON `{$safeDb}`.* TO {$u}");
            $admin->exec("FLUSH PRIVILEGES");
        } catch (\Throwable $e) {
            // Couldn't mint a dedicated login — reuse the admin connection so the
            // database is still usable (shared login, flagged as not isolated).
            $dedicated = false;
            $dbUser = $creds['user'];
            $dbPass = $creds['pass'];
        }

        return $this->recordDatabase($serverId, $label, $dbName, $dbUser, $dbPass, $dedicated, $assignTo, $ownerId);
    }

    /**
     * Write a freshly-provisioned database into the catalogue.
     *
     * Shared by every driver, and the reason the panel drivers can return names
     * that differ from the ones requested: whatever is passed here is what the
     * rest of the platform will believe exists.
     *
     * @return array{success:bool,message:string,id?:int,db_name?:string}
     */
    private function recordDatabase(
        int $serverId, string $label, string $dbName, string $dbUser, string $dbPass,
        bool $dedicated, ?int $assignTo, ?int $ownerId
    ): array {
        $pdo = $this->centralPdo();
        if ($pdo === null) return ['success' => false, 'message' => 'No central database.'];
        $st = $pdo->prepare(
            "INSERT INTO `pp_managed_databases`
             (`server_id`,`label`,`db_name`,`db_user`,`db_pass`,`dedicated`,`assigned_to`,`status`,`created_by`)
             VALUES (?,?,?,?,?,?,?,'active',?)"
        );
        $st->execute([
            $serverId,
            mb_substr($label, 0, 160) ?: $dbName,
            $dbName, $dbUser, $dbPass, $dedicated ? 1 : 0,
            $assignTo ?: null,
            $ownerId ?: null,
        ]);
        return ['success' => true, 'message' => 'Database created.', 'id' => (int) $pdo->lastInsertId(), 'db_name' => $dbName];
    }

    /** @return array<int,array<string,mixed>> all managed databases + server label + assignee (no passwords) */
    public function databases(): array
    {
        $this->ensureSchema();
        $pdo = $this->centralPdo();
        if ($pdo === null) return [];
        $rows = $pdo->query(
            "SELECT d.*, s.label AS server_label, s.is_platform, u.username AS assignee
             FROM `pp_managed_databases` d
             JOIN `pp_db_servers` s ON s.id = d.server_id
             LEFT JOIN `users` u ON u.id = d.assigned_to
             ORDER BY d.created_at DESC"
        )->fetchAll(\PDO::FETCH_ASSOC);
        return array_map(fn(array $r): array => $this->publicRow($r), $rows ?: []);
    }

    /** @return array<int,array<string,mixed>> ACTIVE databases assigned to a user (public fields). */
    public function forUser(int $userId): array
    {
        $this->ensureSchema();
        $pdo = $this->centralPdo();
        if ($pdo === null) return [];
        $st = $pdo->prepare(
            "SELECT d.*, s.label AS server_label, s.is_platform
             FROM `pp_managed_databases` d JOIN `pp_db_servers` s ON s.id = d.server_id
             WHERE d.assigned_to = ? AND d.status = 'active' ORDER BY d.created_at DESC"
        );
        $st->execute([$userId]);
        $rows = array_map(fn(array $r): array => $this->publicRow($r), $st->fetchAll(\PDO::FETCH_ASSOC) ?: []);
        if ($rows === []) return $rows;

        // Usage, so the user can see how much of their allowance a project is
        // using. This is on the /projects page-load path, so it MUST only touch
        // the servers this user's own databases live on — never the global
        // sizes() (which walks every registered server, including remote panels
        // that from here are slow or unreachable). Short timeout, and a failure
        // degrades to "size unknown" rather than hanging the page.
        $byServer = [];
        foreach ($rows as $r) $byServer[(int) $r['server_id']][] = (string) $r['db_name'];

        $sizes = [];
        foreach ($byServer as $sid => $names) {
            $srv = $this->serverRow((int) $sid);
            if ($srv !== null) $sizes += $this->sizesOnServer($srv, $names, 2);
        }
        foreach ($rows as $i => $r) {
            $rows[$i]['usage'] = DatabaseQuota::status((int) ($sizes[$r['db_name']] ?? 0));
        }
        return $rows;
    }

    /**
     * The full connection (WITH password + resolved host) for a managed database —
     * server-side only, used by project creation. Returns null if not found/active
     * or not assigned to $forUserId (when given).
     *
     * @return array<string,string>|null
     */
    public function connectionFor(int $id, ?int $forUserId = null): ?array
    {
        $pdo = $this->centralPdo();
        if ($pdo === null) return null;
        $st = $pdo->prepare("SELECT * FROM `pp_managed_databases` WHERE id = ? AND status = 'active'");
        $st->execute([$id]);
        $d = $st->fetch(\PDO::FETCH_ASSOC);
        if (!$d) return null;
        if ($forUserId !== null && (int) ($d['assigned_to'] ?? 0) !== $forUserId) return null;
        $srv = $this->serverRow((int) $d['server_id']);
        if (!$srv) return null;
        $creds = $this->serverCreds($srv);

        // WHERE A CLIENT CONNECTS is not the same as where we provisioned.
        //
        // For a panel server the API lives at e.g. ppradhan.com:2083, but the
        // database it created is reached on the MySQL port — and which hostname
        // works depends on where the consuming app runs:
        //   • app on the SAME hosting account  → 'localhost'
        //   • app anywhere else                → the host's MySQL hostname, with
        //     that client IP whitelisted in the panel's Remote Database Access
        //
        // Only the owner knows which, so `db_host` is what they set; the panel
        // host is a last resort rather than a guess dressed up as a default.
        $dbHost = trim((string) ($srv['db_host'] ?? ''));
        if ($dbHost === '') $dbHost = (string) $creds['host'];

        return [
            'type'    => 'mysql',
            'host'    => $dbHost,
            'port'    => (string) ((int) ($srv['port'] ?? 0) ?: (int) $creds['port'] ?: 3306),
            'dbname'  => (string) $d['db_name'],
            'user'    => (string) $d['db_user'],
            'pass'    => (string) ($d['db_pass'] ?? ''),
            'charset' => 'utf8mb4',
        ];
    }

    /**
     * Prove a managed database is actually usable: connect with the exact
     * credentials a user's project would be given, and confirm it can read and
     * write. Provisioning succeeding is NOT the same as the database being
     * reachable — on shared hosting those are two different networks.
     *
     * @return array{success:bool,message:string,host?:string,canWrite?:bool}
     */
    public function verifyDatabase(int $id): array
    {
        $conn = $this->connectionFor($id);
        if ($conn === null) return ['success' => false, 'message' => 'Database not found.'];

        try {
            $pdo = new \PDO(
                "mysql:host={$conn['host']};port={$conn['port']};dbname={$conn['dbname']};charset=utf8mb4",
                (string) $conn['user'], (string) $conn['pass'],
                [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, \PDO::ATTR_TIMEOUT => 8]
            );
        } catch (\Throwable $e) {
            $code = ($e instanceof \PDOException && isset($e->errorInfo[1])) ? (int) $e->errorInfo[1] : 0;
            $hint = ($code === 2002 || stripos($e->getMessage(), 'timed out') !== false || stripos($e->getMessage(), 'refused') !== false)
                ? ' The database was created, but this server cannot REACH it. On shared hosting either run PPPHP on the same account (set “Database host” to localhost), or whitelist this server’s IP under Remote Database Access in the panel and set “Database host” to the host’s MySQL hostname.'
                : ($code === 1045 ? ' The stored login was rejected — the panel may have changed it.' : '');
            return ['success' => false, 'host' => (string) $conn['host'],
                    'message' => 'Could not connect to ' . $conn['dbname'] . ' at ' . $conn['host'] . ':' . $conn['port'] . '.' . $hint];
        }

        // A user's project needs to CREATE and DROP tables, not just read.
        $canWrite = false;
        $probe = 'pp_check_' . bin2hex(random_bytes(3));
        try {
            $pdo->exec("CREATE TABLE `{$probe}` (`id` INT)");
            $pdo->exec("DROP TABLE `{$probe}`");
            $canWrite = true;
        } catch (\Throwable $e) { /* read-only grant */ }

        return [
            'success' => true, 'host' => (string) $conn['host'], 'canWrite' => $canWrite,
            'message' => $canWrite
                ? 'Connected to ' . $conn['dbname'] . ' at ' . $conn['host'] . ' — tables can be created and dropped.'
                : 'Connected to ' . $conn['dbname'] . ', but the login cannot create tables. Projects will fail to build their schema.',
        ];
    }

    public function assign(int $id, ?int $userId): bool
    {
        if ($this->assertWritable() !== null) return false;
        $pdo = $this->centralPdo();
        if ($pdo === null) return false;
        return $pdo->prepare("UPDATE `pp_managed_databases` SET assigned_to = ? WHERE id = ?")
            ->execute([$userId ?: null, $id]);
    }

    public function setStatus(int $id, string $status): bool
    {
        if ($this->assertWritable() !== null) return false;
        if (!in_array($status, ['active', 'suspended'], true)) return false;
        $pdo = $this->centralPdo();
        if ($pdo === null) return false;
        return $pdo->prepare("UPDATE `pp_managed_databases` SET status = ? WHERE id = ?")->execute([$status, $id]);
    }

    /** Drop a managed database + its dedicated user, then remove the catalogue row. */
    public function delete(int $id): array
    {
        if ($blocked = $this->assertWritable()) return $blocked;
        $pdo = $this->centralPdo();
        if ($pdo === null) return ['success' => false, 'message' => 'No central database.'];
        $st = $pdo->prepare("SELECT * FROM `pp_managed_databases` WHERE id = ?");
        $st->execute([$id]);
        $d = $st->fetch(\PDO::FETCH_ASSOC);
        if (!$d) return ['success' => false, 'message' => 'Database not found.'];
        $srv = $this->serverRow((int) $d['server_id']);

        // Panel-provisioned databases must be dropped through the same panel —
        // we have no SQL route to them. Handled first, so the direct-SQL branch
        // below is unchanged.
        if ($srv && \PpPhp\Core\Databases\Drivers\DriverFactory::normalise((string) ($srv['driver'] ?? 'sql'))
                    !== \PpPhp\Core\Databases\Drivers\DriverFactory::SQL) {
            $creds = $this->serverCreds($srv);
            $res = \PpPhp\Core\Databases\Drivers\DriverFactory::make(
                $srv + ['admin_user' => $creds['user'], 'admin_pass' => $creds['pass']]
            )->dropDatabase((string) $d['db_name'], (string) $d['db_user']);

            $pdo->prepare("DELETE FROM `pp_managed_databases` WHERE id = ?")->execute([$id]);
            return empty($res['success'])
                ? ['success' => true, 'message' => 'Removed from the catalogue, but the panel reported: ' . (string) $res['message']]
                : ['success' => true, 'message' => 'Database dropped.'];
        }

        if ($srv) {
            $creds = $this->serverCreds($srv);
            try {
                $admin = new \PDO(
                    "mysql:host={$creds['host']};port={$creds['port']};charset=utf8mb4",
                    $creds['user'], $creds['pass'],
                    [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, \PDO::ATTR_TIMEOUT => 8]
                );
                $safeDb = preg_replace('/[^A-Za-z0-9_]/', '', (string) $d['db_name']);
                $admin->exec("DROP DATABASE IF EXISTS `{$safeDb}`");
                if ((int) ($d['dedicated'] ?? 1) === 1 && (string) $d['db_user'] !== $creds['user']) {
                    try { $admin->exec("DROP USER IF EXISTS " . $admin->quote((string) $d['db_user']) . "@" . $admin->quote((string) $creds['grant_host'])); } catch (\Throwable $e) {}
                }
            } catch (\Throwable $e) {
                // Even if the drop fails (server offline), still remove the record so
                // the catalogue stays usable; report the warning.
                $pdo->prepare("DELETE FROM `pp_managed_databases` WHERE id = ?")->execute([$id]);
                return ['success' => true, 'message' => 'Removed from catalogue, but the server could not be reached to drop it: ' . $e->getMessage()];
            }
        }
        $pdo->prepare("DELETE FROM `pp_managed_databases` WHERE id = ?")->execute([$id]);
        return ['success' => true, 'message' => 'Database dropped.'];
    }

    /** Per-server data size (bytes) keyed by db_name, for the overview. Best-effort. */
    public function sizes(): array
    {
        $out = [];

        // Real database names per server, from the catalogue.
        $byServer = [];
        $pdo = $this->centralPdo();
        if ($pdo !== null) {
            foreach ($pdo->query('SELECT `server_id`,`db_name` FROM `pp_managed_databases`')
                         ->fetchAll(\PDO::FETCH_ASSOC) ?: [] as $r) {
                $byServer[(int) $r['server_id']][] = (string) $r['db_name'];
            }
        }

        foreach ($this->servers() as $s) {
            $names = $byServer[(int) $s['id']] ?? [];
            // CRITICAL: skip BEFORE opening any connection. A server that hosts no
            // managed database has nothing to measure, and connecting anyway meant
            // every /projects load paid a multi-second timeout per remote panel
            // (bug: the empty-check used to sit after `new PDO`).
            if ($names === []) continue;
            $srv = $this->serverRow((int) $s['id']);
            if ($srv === null) continue;
            $out += $this->sizesOnServer($srv, $names, 6);
        }
        return $out;
    }

    /**
     * On-disk size (bytes) for the named schemas on ONE server, keyed by db_name.
     *
     * Best-effort and self-contained: an unreachable server yields an empty array
     * rather than throwing, so a slow remote panel degrades the usage bar to
     * "unknown" instead of hanging whatever page called it. The timeout is a
     * parameter because the caller decides how much latency it can tolerate — the
     * owner overview can wait a few seconds, the per-user projects page cannot.
     *
     * @param array<string,mixed> $srv    a `pp_db_servers` row
     * @param array<int,string>   $names  managed database names on that server
     * @return array<string,int>
     */
    private function sizesOnServer(array $srv, array $names, int $timeout): array
    {
        $out = [];
        if ($names === []) return $out;
        $creds = $this->serverCreds($srv);
        if (($creds['user'] ?? '') === '') return $out;
        try {
            $admin = new \PDO(
                "mysql:host={$creds['host']};port={$creds['port']};charset=utf8mb4",
                $creds['user'], $creds['pass'],
                [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION, \PDO::ATTR_TIMEOUT => $timeout]
            );
            // Ask about the schemas we ACTUALLY have, not a name pattern. The old
            // `LIKE 'pp\_db\_%'` only matched self-provisioned names, so every
            // panel-created database (cPanel/DirectAdmin force `<account>_…`) read 0 B.
            $in = implode(',', array_fill(0, count($names), '?'));
            $st = $admin->prepare(
                "SELECT table_schema, SUM(data_length+index_length) AS bytes
                   FROM information_schema.TABLES
                  WHERE table_schema IN ($in) GROUP BY table_schema"
            );
            $st->execute($names);
            foreach ($st->fetchAll(\PDO::FETCH_ASSOC) as $r) {
                $out[(string) $r['table_schema']] = (int) $r['bytes'];
            }
            // A database with no tables reports no row at all — record it as 0 so
            // callers can tell "empty" from "unknown".
            foreach ($names as $n) if (!isset($out[$n])) $out[$n] = 0;
        } catch (\Throwable $e) { /* unreachable — leave those names absent (unknown) */ }
        return $out;
    }

    /**
     * Copy every base table (structure + data) from one connection to another —
     * used to move a project off its throwaway sandbox onto a real database.
     * Works same-server or cross-server (row-by-row via PDO).
     *
     * @param array<string,string> $from  {host,port,dbname,user,pass}
     * @param array<string,string> $to
     * @return array{success:bool,message:string,tables?:int,rows?:int}
     */
    public function migrate(array $from, array $to): array
    {
        try {
            $src = new \PDO("mysql:host={$from['host']};port={$from['port']};dbname={$from['dbname']};charset=utf8mb4", (string) $from['user'], (string) $from['pass'], [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
            $dst = new \PDO("mysql:host={$to['host']};port={$to['port']};dbname={$to['dbname']};charset=utf8mb4", (string) $to['user'], (string) $to['pass'], [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
        } catch (\Throwable $e) {
            return ['success' => false, 'message' => 'Connection failed: ' . $e->getMessage()];
        }
        // Size allowance, checked before a byte is written. This is the largest
        // single writer into a managed database (a whole sandbox moving across),
        // so it is exactly where an unnoticed overrun would happen — and refusing
        // up front beats aborting half way with tables partially copied.
        try {
            $incoming = (int) $src->query(
                'SELECT COALESCE(SUM(data_length + index_length), 0)
                   FROM information_schema.TABLES WHERE table_schema = DATABASE()'
            )->fetchColumn();
            $already = (int) $dst->query(
                'SELECT COALESCE(SUM(data_length + index_length), 0)
                   FROM information_schema.TABLES WHERE table_schema = DATABASE()'
            )->fetchColumn();
            $g = DatabaseQuota::mayGrow($already, $incoming);
            if (!$g['allowed']) {
                return ['success' => false, 'message' =>
                    'That project needs ' . DatabaseQuota::human($incoming) . ', which does not fit. ' . $g['message']];
            }
        } catch (\Throwable $e) {
            // Cannot measure => do not block the move; the usage bar still reports it.
        }

        try {
            $dst->exec('SET FOREIGN_KEY_CHECKS=0');
            $tables = $src->query("SHOW FULL TABLES WHERE Table_type = 'BASE TABLE'")->fetchAll(\PDO::FETCH_COLUMN);
            $nTables = 0; $nRows = 0;
            foreach ((array) $tables as $t) {
                $safe = preg_replace('/[^A-Za-z0-9_]/', '', (string) $t);
                if ($safe === '') continue;
                $create = $src->query("SHOW CREATE TABLE `{$safe}`")->fetch(\PDO::FETCH_NUM)[1] ?? '';
                if ($create === '') continue;
                $dst->exec("DROP TABLE IF EXISTS `{$safe}`");
                $dst->exec($create);
                $cols = array_map(static fn($c) => (string) $c['Field'], $src->query("SHOW COLUMNS FROM `{$safe}`")->fetchAll(\PDO::FETCH_ASSOC));
                if ($cols === []) { $nTables++; continue; }
                $colList = '`' . implode('`,`', $cols) . '`';
                $ins = $dst->prepare("INSERT INTO `{$safe}` ({$colList}) VALUES (" . implode(',', array_fill(0, count($cols), '?')) . ")");
                $sel = $src->query("SELECT {$colList} FROM `{$safe}`");
                $dst->beginTransaction();
                while (($row = $sel->fetch(\PDO::FETCH_NUM)) !== false) { $ins->execute($row); $nRows++; }
                $dst->commit();
                $nTables++;
            }
            $dst->exec('SET FOREIGN_KEY_CHECKS=1');
            return ['success' => true, 'message' => "Copied {$nTables} table(s) and {$nRows} row(s).", 'tables' => $nTables, 'rows' => $nRows];
        } catch (\Throwable $e) {
            if ($dst->inTransaction()) { try { $dst->rollBack(); } catch (\Throwable $x) {} }
            return ['success' => false, 'message' => 'Migration failed: ' . $e->getMessage()];
        }
    }

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

    /** @return array<string,mixed> public projection of a managed_databases row (no password) */
    private function publicRow(array $r): array
    {
        return [
            'id'          => (int) $r['id'],
            'server_id'   => (int) $r['server_id'],
            'server'      => (string) ($r['server_label'] ?? ''),
            'is_platform' => (int) ($r['is_platform'] ?? 0) === 1,
            'label'       => (string) ($r['label'] ?? ''),
            'db_name'     => (string) $r['db_name'],
            'db_user'     => (string) $r['db_user'],
            'dedicated'   => (int) ($r['dedicated'] ?? 1) === 1,
            'assigned_to' => $r['assigned_to'] !== null ? (int) $r['assigned_to'] : null,
            'assignee'    => (string) ($r['assignee'] ?? ''),
            'status'      => (string) $r['status'],
            'created_at'  => (string) $r['created_at'],
            // Phase 4 clone state — per-side, so on a backup these describe what
            // THIS server has physically restored (see LicenseSync::stripCloneState).
            'cloned_at'   => (string) ($r['cloned_at'] ?? ''),
            'clone_bytes' => (int) ($r['clone_bytes'] ?? 0),
        ];
    }

    private function config(): array
    {
        if ($this->cfg !== null) return $this->cfg;
        $file = dirname(__DIR__, 2) . '/config/database.php';
        $cfg = is_file($file) ? require $file : [];
        return $this->cfg = is_array($cfg) ? $cfg : [];
    }

    private function centralPdo(): ?\PDO
    {
        if ($this->central !== null) return $this->central;
        $cfg = $this->config();
        if (empty($cfg['host'])) return null;
        try {
            return $this->central = new \PDO(
                "mysql:host={$cfg['host']};port={$cfg['port']};dbname={$cfg['dbname']};charset=utf8mb4",
                (string) ($cfg['user'] ?? ''), (string) ($cfg['pass'] ?? ''),
                [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]
            );
        } catch (\Throwable $e) {
            return null;
        }
    }

    private function one(string $sql, array $args = []): mixed
    {
        $pdo = $this->centralPdo();
        if ($pdo === null) return null;
        $st = $pdo->prepare($sql);
        $st->execute($args);
        return $st->fetchColumn();
    }

    /** True when an exception is a MySQL access/privilege denial (1044/1045/1142/1227). */
    private function isPrivilegeError(\Throwable $e): bool
    {
        $code = 0;
        if ($e instanceof \PDOException && isset($e->errorInfo[1])) $code = (int) $e->errorInfo[1];
        if (in_array($code, [1044, 1045, 1142, 1227], true)) return true;
        return stripos($e->getMessage(), 'access denied') !== false
            || stripos($e->getMessage(), 'command denied') !== false;
    }

    /** Short, DB-identifier-safe tag from a label (letters/digits, ≤ 20 chars). */
    private function slugTag(string $s): string
    {
        $s = strtolower((string) preg_replace('/[^a-z0-9]+/i', '_', $s));
        $s = trim($s, '_');
        // `replica_` is the marker DbCloner uses to decide a name is a safe
        // restore target. A live database must never contain it, or a clone
        // could be written over real user data — so the word is neutralised
        // here rather than trusted not to appear.
        $s = str_replace('replica', 'repl1ca', $s);
        return $s !== '' ? substr($s, 0, 20) : 'db';
    }

    private function rand(int $len): string
    {
        $a = 'abcdefghijklmnopqrstuvwxyz0123456789';
        $o = '';
        for ($i = 0; $i < $len; $i++) $o .= $a[random_int(0, strlen($a) - 1)];
        return $o;
    }

    /** Random password guaranteed to satisfy MySQL validate_password (mixed case,
     *  digit, special). Avoids quote/backslash/backtick so quoting is trivial. */
    private function randPassword(int $len = 24): string
    {
        $sets = ['abcdefghijkmnpqrstuvwxyz', 'ABCDEFGHJKLMNPQRSTUVWXYZ', '23456789', '!@#%^*_-+='];
        $pw = '';
        foreach ($sets as $s) $pw .= $s[random_int(0, strlen($s) - 1)];   // one from each class
        $all = implode('', $sets);
        for ($i = strlen($pw); $i < $len; $i++) $pw .= $all[random_int(0, strlen($all) - 1)];
        return str_shuffle($pw);
    }
}
