<?php

declare(strict_types=1);

namespace PpPhp\Core\Databases;

/**
 * VirtualDbService — the owner-managed CENTRAL POOL of virtual (SQLite) databases.
 *
 * Per-project virtual databases live inside each project folder and need no
 * registry. This service is only for the shared pool the Owner curates: a small
 * catalogue table in the platform DB (`pp_virtual_databases`) plus the file
 * lifecycle (create / assign / backup / restore / vacuum / integrity / drop),
 * all delegating the actual SQLite work to {@see VirtualDb}.
 *
 * It uses its own short-lived PDO to the platform DB so it never disturbs the
 * DB facade's active (possibly project-scoped) connection.
 */
final class VirtualDbService
{
    private ?\PDO $central = null;

    /* ── platform DB access (mirrors ManagedDatabaseService::centralPdo) ── */
    private function config(): array
    {
        $file = VirtualDb::basePath() . '/config/database.php';
        return is_file($file) ? (array) (require $file) : [];
    }

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

    public function ensureSchema(): void
    {
        $pdo = $this->pdo();
        if ($pdo === null) { return; }
        $pdo->exec(
            "CREATE TABLE IF NOT EXISTS `pp_virtual_databases` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `name` VARCHAR(80) NOT NULL UNIQUE,
                `label` VARCHAR(160) NOT NULL DEFAULT '',
                `note` VARCHAR(255) NOT NULL DEFAULT '',
                `created_by` INT NULL,
                `assigned_to` INT NULL,
                `project_slug` VARCHAR(160) NULL,
                `max_bytes` BIGINT NOT NULL DEFAULT 0,
                `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
        );
        // Self-heal max_bytes onto a table created before size caps existed
        // (0 = inherit the plan cap; <0 = force unlimited; >0 = explicit bytes).
        try { $pdo->exec("ALTER TABLE `pp_virtual_databases` ADD COLUMN `max_bytes` BIGINT NOT NULL DEFAULT 0"); }
        catch (\Throwable $e) { /* column already present */ }
        $pdo->exec(
            "CREATE TABLE IF NOT EXISTS `pp_virtual_db_quotas` (
                `user_id` INT PRIMARY KEY,
                `max_dbs` INT NOT NULL DEFAULT 0
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
        );
        // Group sharing: a database's creator may share it with teammates (read or
        // read/write) beyond the single owner-set assignment.
        $pdo->exec(
            "CREATE TABLE IF NOT EXISTS `pp_virtual_db_shares` (
                `db_id` INT NOT NULL,
                `user_id` INT NOT NULL,
                `can_write` TINYINT NOT NULL DEFAULT 0,
                `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (`db_id`, `user_id`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"
        );
    }

    /* ── quotas / seats ── */
    private const QUOTA_KEY = 'virtual_db_quota';
    private const DEFAULT_QUOTA = 3;

    /** Global default: how many virtual databases a user may own. -1 = unlimited. */
    public function defaultQuota(): int
    {
        $v = \PpPhp\Core\PlatformSettings::get(self::QUOTA_KEY, ['default' => self::DEFAULT_QUOTA]);
        return isset($v['default']) ? (int) $v['default'] : self::DEFAULT_QUOTA;
    }

    public function setDefaultQuota(int $max): bool
    {
        return \PpPhp\Core\PlatformSettings::set(self::QUOTA_KEY, ['default' => max(-1, $max)]);
    }

    /** Per-user override (null when none). */
    public function userQuotaOverride(int $userId): ?int
    {
        $pdo = $this->pdo(); if ($pdo === null) { return null; }
        $this->ensureSchema();
        $st = $pdo->prepare("SELECT max_dbs FROM `pp_virtual_db_quotas` WHERE user_id = ?");
        $st->execute([$userId]);
        $v = $st->fetchColumn();
        return $v === false ? null : (int) $v;
    }

    /** Effective quota for a user: override if set, else the global default. */
    public function effectiveQuota(int $userId): int
    {
        // The platform owner is the unlimited top tier — never quota-capped.
        if ($this->isOwnerUid($userId)) { return -1; }
        $o = $this->userQuotaOverride($userId);
        return $o !== null ? $o : $this->defaultQuota();
    }

    /** True when this user id is the hidden platform owner (unlimited tier). */
    private function isOwnerUid(int $userId): bool
    {
        if ($userId < 1) { return false; }
        $pdo = $this->pdo(); if ($pdo === null) { return false; }
        try {
            $st = $pdo->prepare("SELECT role FROM `users` WHERE id = ?");
            $st->execute([$userId]);
            return (string) $st->fetchColumn() === 'owner';
        } catch (\Throwable $e) { return false; }
    }

    public function setUserQuota(int $userId, ?int $max): array
    {
        $pdo = $this->pdo(); if ($pdo === null) { return ['success' => false, 'message' => 'No connection.']; }
        $this->ensureSchema();
        if ($max === null) {
            $pdo->prepare("DELETE FROM `pp_virtual_db_quotas` WHERE user_id = ?")->execute([$userId]);
            return ['success' => true, 'message' => 'Reset to the default quota.'];
        }
        $pdo->prepare("INSERT INTO `pp_virtual_db_quotas` (user_id, max_dbs) VALUES (?,?)
                       ON DUPLICATE KEY UPDATE max_dbs = VALUES(max_dbs)")->execute([$userId, max(-1, $max)]);
        return ['success' => true, 'message' => 'Quota updated.'];
    }

    /** How many pool databases currently belong to a user. */
    public function usage(int $userId): int
    {
        $pdo = $this->pdo(); if ($pdo === null) { return 0; }
        $this->ensureSchema();
        $st = $pdo->prepare("SELECT COUNT(*) FROM `pp_virtual_databases` WHERE assigned_to = ?");
        $st->execute([$userId]);
        return (int) $st->fetchColumn();
    }

    /** Would assigning one more DB to this user exceed their quota? */
    public function quotaExceeded(int $userId): bool
    {
        $q = $this->effectiveQuota($userId);
        if ($q < 0) { return false; }                 // unlimited
        return $this->usage($userId) >= $q;
    }

    /** Per-user quota report for the settings UI (non-owner users). */
    public function quotaReport(): array
    {
        $pdo = $this->pdo(); if ($pdo === null) { return []; }
        $this->ensureSchema();
        $rows = $pdo->query(
            "SELECT u.id, u.username, u.role,
                    (SELECT COUNT(*) FROM `pp_virtual_databases` v WHERE v.assigned_to = u.id) AS used,
                    q.max_dbs AS override
             FROM `users` u LEFT JOIN `pp_virtual_db_quotas` q ON q.user_id = u.id
             WHERE u.role <> 'owner'
             ORDER BY u.username ASC"
        )->fetchAll(\PDO::FETCH_ASSOC);
        $def = $this->defaultQuota();
        return array_map(static function (array $r) use ($def): array {
            $override = $r['override'] !== null ? (int) $r['override'] : null;
            return [
                'id' => (int) $r['id'], 'username' => (string) $r['username'], 'role' => (string) $r['role'],
                'used' => (int) $r['used'], 'override' => $override,
                'effective' => $override !== null ? $override : $def,
            ];
        }, $rows ?: []);
    }

    /* ── path ── */
    public function pathFor(array $row): string
    {
        return VirtualDb::poolPath((string) $row['name']);
    }

    public function connectionFor(int $id): ?array
    {
        $row = $this->get($id);
        if ($row === null) { return null; }
        return \PpPhp\Core\DatabaseConnection::normalize(['type' => 'sqlite', 'path' => $this->pathFor($row)]);
    }

    /* ── size caps ── */

    /** Effective byte cap for a pool DB row (0 = unlimited). */
    public function capBytesFor(array $row): int
    {
        return VirtualDbSizePolicy::capBytesFor($row);
    }

    /** Live usage snapshot (bytes + cap + level/message) for the UI meter. */
    public function usageFor(array $row): array
    {
        $bytes = VirtualDb::fileSize($this->pathFor($row));
        return VirtualDbSizePolicy::usage($bytes, $this->capBytesFor($row));
    }

    /**
     * Open a hardened, SIZE-CAPPED PDO to a pool DB — the write path for the
     * console/Studio. SQLite refuses writes past the cap (reads still work).
     */
    public function openCapped(array $row): \PDO
    {
        $abs = $this->pathFor($row);
        $pdo = new \PDO('sqlite:' . $abs, null, null, [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
        \PpPhp\Core\DatabaseConnection::applySqlitePragmas($pdo);
        VirtualDbSizePolicy::applyTo($pdo, $this->capBytesFor($row));
        return $pdo;
    }

    /**
     * Enforce the pool size cap on ANY already-open SQLite connection whose file
     * lives in the central pool dir — the normal write path (DB::connection() →
     * Tables designer / running app) as well as the console/Studio. A no-op when
     * the file is not a pool DB, or no row/cap can be resolved. Fails OPEN: a
     * lookup failure never breaks the connection (worst case = no cap applied).
     */
    public function applyCapForPath(\PDO $pdo, string $absPath): void
    {
        try {
            $poolDir = str_replace('\\', '/', rtrim(VirtualDb::poolDir(), '/\\'));
            $dir     = str_replace('\\', '/', rtrim(dirname($absPath), '/\\'));
            if ($dir !== $poolDir) { return; }               // not a central-pool DB
            $name = basename($absPath, '.sqlite');
            $row  = $this->findByName($name);
            if ($row === null) { return; }                   // unknown file → no cap
            VirtualDbSizePolicy::applyTo($pdo, $this->capBytesFor($row));
        } catch (\Throwable $e) { /* best effort — never break opening a connection */ }
    }

    /** Owner: set a per-DB size override (0 = inherit plan; -1 = unlimited; N MB → bytes). */
    public function setSizeOverride(int $id, int $bytes): array
    {
        $pdo = $this->pdo(); if ($pdo === null) { return ['success' => false, 'message' => 'No connection.']; }
        $pdo->prepare("UPDATE `pp_virtual_databases` SET max_bytes = ? WHERE id = ?")->execute([$bytes, $id]);
        return ['success' => true, 'message' => 'Size limit updated.'];
    }

    /* ── group sharing / visibility ── */

    /** Map of db_id => can_write for databases shared WITH this user. */
    private function sharedDbIdsFor(int $userId): array
    {
        $pdo = $this->pdo(); if ($pdo === null) { return []; }
        $this->ensureSchema();
        $st = $pdo->prepare("SELECT db_id, can_write FROM `pp_virtual_db_shares` WHERE user_id = ?");
        $st->execute([$userId]);
        $out = [];
        foreach ($st->fetchAll(\PDO::FETCH_ASSOC) as $r) { $out[(int) $r['db_id']] = (bool) $r['can_write']; }
        return $out;
    }

    /**
     * Databases a user may SEE, each tagged with their access level:
     *   'owner'   — the platform owner (all databases)
     *   'manage'  — they created it or it's assigned to them (full control)
     *   'write'   — shared with read/write
     *   'read'    — shared read-only
     * @return array<int,array<string,mixed>>
     */
    public function visibleTo(int $userId, bool $isOwner): array
    {
        $all = $this->all();
        if ($isOwner) {
            return array_map(static function (array $d): array { $d['access'] = 'owner'; return $d; }, $all);
        }
        $shared = $this->sharedDbIdsFor($userId);
        $out = [];
        foreach ($all as $d) {
            $id = (int) $d['id'];
            if ((int) ($d['created_by'] ?? 0) === $userId || (int) ($d['assigned_to'] ?? 0) === $userId) {
                $d['access'] = 'manage';
            } elseif (array_key_exists($id, $shared)) {
                $d['access'] = $shared[$id] ? 'write' : 'read';
            } else {
                continue;
            }
            $out[] = $d;
        }
        return $out;
    }

    /** May this user WRITE to (mutate) a database? */
    public function canWrite(int $dbId, int $userId, bool $isOwner): bool
    {
        if ($isOwner) { return true; }
        $row = $this->get($dbId);
        if ($row === null) { return false; }
        if ((int) ($row['created_by'] ?? 0) === $userId || (int) ($row['assigned_to'] ?? 0) === $userId) { return true; }
        return $this->sharedDbIdsFor($userId)[$dbId] ?? false;
    }

    /** May this user SEE a database at all (read)? */
    public function canRead(int $dbId, int $userId, bool $isOwner): bool
    {
        if ($isOwner) { return true; }
        $row = $this->get($dbId);
        if ($row === null) { return false; }
        if ((int) ($row['created_by'] ?? 0) === $userId || (int) ($row['assigned_to'] ?? 0) === $userId) { return true; }
        return array_key_exists($dbId, $this->sharedDbIdsFor($userId));
    }

    /** Who a database is currently shared with. @return array<int,array{user_id:int,username:string,can_write:bool}> */
    public function sharesOf(int $dbId): array
    {
        $pdo = $this->pdo(); if ($pdo === null) { return []; }
        $this->ensureSchema();
        $st = $pdo->prepare(
            "SELECT s.user_id, s.can_write, u.username, u.role
             FROM `pp_virtual_db_shares` s LEFT JOIN `users` u ON u.id = s.user_id
             WHERE s.db_id = ? ORDER BY u.username ASC"
        );
        $st->execute([$dbId]);
        return array_map(static fn (array $r): array => [
            'user_id'   => (int) $r['user_id'],
            'username'  => (string) ($r['username'] ?? ('#' . $r['user_id'])),
            'role'      => (string) ($r['role'] ?? ''),
            'can_write' => (bool) $r['can_write'],
        ], $st->fetchAll(\PDO::FETCH_ASSOC) ?: []);
    }

    /** Add or update a share. */
    public function share(int $dbId, int $userId, bool $canWrite): array
    {
        $pdo = $this->pdo(); if ($pdo === null) { return ['success' => false, 'message' => 'No connection.']; }
        if ($userId < 1) { return ['success' => false, 'message' => 'Pick a teammate to share with.']; }
        $this->ensureSchema();
        $pdo->prepare(
            "INSERT INTO `pp_virtual_db_shares` (db_id, user_id, can_write) VALUES (?,?,?)
             ON DUPLICATE KEY UPDATE can_write = VALUES(can_write)"
        )->execute([$dbId, $userId, $canWrite ? 1 : 0]);
        return ['success' => true, 'message' => 'Shared' . ($canWrite ? ' (read/write).' : ' (read-only).')];
    }

    /** Remove a share. */
    public function unshare(int $dbId, int $userId): array
    {
        $pdo = $this->pdo(); if ($pdo === null) { return ['success' => false, 'message' => 'No connection.']; }
        $pdo->prepare("DELETE FROM `pp_virtual_db_shares` WHERE db_id = ? AND user_id = ?")->execute([$dbId, $userId]);
        return ['success' => true, 'message' => 'Share removed.'];
    }

    /**
     * People a user may share a database with: teammates in the same group(s), plus
     * — for admins (owner/superadmin/developer) — every other non-owner user. Never
     * includes the sharer or hidden owner accounts.
     * @return array<int,array{id:int,username:string,role:string}>
     */
    public function shareCandidates(int $userId, string $role): array
    {
        $pdo = $this->pdo(); if ($pdo === null) { return []; }
        $ids = [];
        // group-mates (best-effort — the groups tables may not exist on every install)
        try {
            $st = $pdo->prepare(
                "SELECT DISTINCT u.id, u.username, u.role
                 FROM `users` u JOIN `user_groups` ug ON ug.user_id = u.id
                 WHERE ug.group_id IN (SELECT group_id FROM `user_groups` WHERE user_id = ?)
                   AND u.id <> ? AND u.role <> 'owner'"
            );
            $st->execute([$userId, $userId]);
            foreach ($st->fetchAll(\PDO::FETCH_ASSOC) as $r) { $ids[(int) $r['id']] = $r; }
        } catch (\Throwable $e) { /* no groups table — rely on the admin fallback below */ }
        // admins can share with anyone (they manage the install)
        if (in_array($role, ['owner', 'superadmin', 'developer'], true)) {
            try {
                $st = $pdo->query("SELECT id, username, role FROM `users` WHERE role <> 'owner'");
                foreach ($st->fetchAll(\PDO::FETCH_ASSOC) as $r) {
                    if ((int) $r['id'] !== $userId) { $ids[(int) $r['id']] = $r; }
                }
            } catch (\Throwable $e) { /* best effort */ }
        }
        $out = array_map(static fn (array $r): array => [
            'id' => (int) $r['id'], 'username' => (string) $r['username'], 'role' => (string) ($r['role'] ?? ''),
        ], array_values($ids));
        usort($out, static fn ($a, $b) => strcasecmp($a['username'], $b['username']));
        return $out;
    }

    /* ── reads ── */
    public function get(int $id): ?array
    {
        $pdo = $this->pdo(); if ($pdo === null) { return null; }
        $this->ensureSchema();
        $st = $pdo->prepare("SELECT * FROM `pp_virtual_databases` WHERE id = ?");
        $st->execute([$id]);
        $row = $st->fetch(\PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    public function findByName(string $name): ?array
    {
        $pdo = $this->pdo(); if ($pdo === null) { return null; }
        $this->ensureSchema();
        $st = $pdo->prepare("SELECT * FROM `pp_virtual_databases` WHERE name = ?");
        $st->execute([$name]);
        $row = $st->fetch(\PDO::FETCH_ASSOC);
        return $row ?: null;
    }

    /** All pool databases, each enriched with live file stats + owner/user labels. */
    public function all(): array
    {
        $pdo = $this->pdo(); if ($pdo === null) { return []; }
        $this->ensureSchema();
        $rows = $pdo->query(
            "SELECT v.*, u.username AS assigned_username, c.username AS created_username
             FROM `pp_virtual_databases` v
             LEFT JOIN `users` u ON u.id = v.assigned_to
             LEFT JOIN `users` c ON c.id = v.created_by
             ORDER BY v.created_at DESC"
        )->fetchAll(\PDO::FETCH_ASSOC);
        return array_map(function (array $r): array {
            $path = VirtualDb::poolPath((string) $r['name']);
            $s = VirtualDb::stats($path);
            $cap = VirtualDbSizePolicy::capBytesFor($r);
            $usage = VirtualDbSizePolicy::usage((int) $s['bytes'], $cap);
            return [
                'id'           => (int) $r['id'],
                'name'         => (string) $r['name'],
                'label'        => (string) $r['label'],
                'note'         => (string) $r['note'],
                'assigned_to'  => $r['assigned_to'] !== null ? (int) $r['assigned_to'] : null,
                'assigned_username' => $r['assigned_username'] ?? null,
                'created_by'   => $r['created_by'] !== null ? (int) $r['created_by'] : null,
                'created_username'  => $r['created_username'] ?? null,
                'project_slug' => $r['project_slug'] ?? null,
                'created_at'   => (string) $r['created_at'],
                'bytes'        => (int) $s['bytes'],
                'size'         => VirtualDb::humanSize((int) $s['bytes']),
                'tables'       => (int) $s['tables'],
                'ok'           => (bool) $s['ok'],
                'exists'       => (bool) $s['exists'],
                'max_bytes'    => (int) ($r['max_bytes'] ?? 0),
                'cap'          => $cap,
                'usage'        => $usage,
            ];
        }, $rows ?: []);
    }

    /* ── mutations ── */
    /** Create a new pool database (file + catalogue row). @return array{success:bool,message:string,id?:int} */
    public function create(string $label, ?int $createdBy = null, ?int $assignTo = null): array
    {
        $pdo = $this->pdo();
        if ($pdo === null) { return ['success' => false, 'message' => 'No connection to the platform database.']; }
        $this->ensureSchema();
        if ($assignTo !== null && $this->quotaExceeded($assignTo)) {
            return ['success' => false, 'message' => 'That user has reached their virtual-database quota (' . $this->effectiveQuota($assignTo) . ').'];
        }
        $typed = trim($label);
        // The database belongs to the assignee (or the creator when self-created);
        // its name is PREFIXED with that account's handle — the cPanel/DirectAdmin/
        // Plesk convention (ppradhan_cms), so ownership is visible and names never
        // collide across users.
        $ownerUid = $assignTo ?? $createdBy;
        $prefix   = $this->accountPrefix($ownerUid);
        $name     = $this->uniqueName(VirtualDb::composeName($prefix, $typed !== '' ? $typed : 'db'));
        // Friendly label = what the user typed (the part after the prefix).
        $friendly = $typed !== '' ? $typed : substr($name, strlen($prefix) + 1);
        try {
            VirtualDb::createFile(VirtualDb::poolPath($name));
        } catch (\Throwable $e) {
            return ['success' => false, 'message' => 'Could not create the database file: ' . $e->getMessage()];
        }
        try {
            $st = $pdo->prepare("INSERT INTO `pp_virtual_databases` (name, label, created_by, assigned_to) VALUES (?,?,?,?)");
            $st->execute([$name, mb_substr($friendly, 0, 160), $createdBy, $assignTo]);
            return ['success' => true, 'message' => 'Database “' . $name . '” created.', 'id' => (int) $pdo->lastInsertId(), 'name' => $name];
        } catch (\Throwable $e) {
            VirtualDb::deleteFile(VirtualDb::poolPath($name));   // don't leave an orphan file
            return ['success' => false, 'message' => 'Could not record the database: ' . $e->getMessage()];
        }
    }

    /** The database-name prefix for a user — their sanitized account handle. null → 'owner'. */
    public function accountPrefix(?int $userId): string
    {
        if ($userId === null || $userId < 1) { return 'owner'; }
        $name = '';
        try {
            $pdo = $this->pdo();
            if ($pdo !== null) {
                $st = $pdo->prepare("SELECT username FROM `users` WHERE id = ?");
                $st->execute([$userId]);
                $name = (string) ($st->fetchColumn() ?: '');
            }
        } catch (\Throwable $e) { /* best effort */ }
        return VirtualDb::userPrefix($name !== '' ? $name : ('user' . $userId));
    }

    /** Ensure a pool-DB name is unique in the pool, appending 2,3,… on collision. */
    public function uniqueName(string $base): string
    {
        if ($this->findByName($base) === null && !is_file(VirtualDb::poolPath($base))) { return $base; }
        for ($i = 2; $i < 1000; $i++) {
            $cand = $base . $i;
            if ($this->findByName($cand) === null && !is_file(VirtualDb::poolPath($cand))) { return $cand; }
        }
        return $base . '_' . substr(bin2hex(random_bytes(3)), 0, 6);
    }

    public function rename(int $id, string $label): array
    {
        $pdo = $this->pdo(); if ($pdo === null) { return ['success' => false, 'message' => 'No connection.']; }
        $pdo->prepare("UPDATE `pp_virtual_databases` SET label = ? WHERE id = ?")->execute([mb_substr(trim($label), 0, 160), $id]);
        return ['success' => true, 'message' => 'Renamed.'];
    }

    public function setNote(int $id, string $note): array
    {
        $pdo = $this->pdo(); if ($pdo === null) { return ['success' => false, 'message' => 'No connection.']; }
        $pdo->prepare("UPDATE `pp_virtual_databases` SET note = ? WHERE id = ?")->execute([mb_substr(trim($note), 0, 255), $id]);
        return ['success' => true, 'message' => 'Saved.'];
    }

    public function assignUser(int $id, ?int $userId): array
    {
        $pdo = $this->pdo(); if ($pdo === null) { return ['success' => false, 'message' => 'No connection.']; }
        if ($userId !== null) {
            $row = $this->get($id);
            // Re-assigning to the same user is a no-op, not a quota event.
            if (($row === null || (int) ($row['assigned_to'] ?? 0) !== $userId) && $this->quotaExceeded($userId)) {
                return ['success' => false, 'message' => 'That user has reached their virtual-database quota (' . $this->effectiveQuota($userId) . '). Raise it in Quotas first.'];
            }
        }
        $pdo->prepare("UPDATE `pp_virtual_databases` SET assigned_to = ? WHERE id = ?")->execute([$userId, $id]);
        return ['success' => true, 'message' => $userId ? 'Assigned.' : 'Unassigned.'];
    }

    public function drop(int $id): array
    {
        $row = $this->get($id);
        if ($row === null) { return ['success' => false, 'message' => 'Not found.']; }
        VirtualDb::deleteFile($this->pathFor($row));
        // remove its rotating snapshots too, so a dropped database leaves nothing behind
        $snapDir = $this->snapDir($row);
        if (is_dir($snapDir)) {
            foreach (glob($snapDir . '/*') ?: [] as $f) { @unlink($f); }
            foreach (['.htaccess', 'web.config', 'index.html'] as $g) { @unlink($snapDir . '/' . $g); }
            @rmdir($snapDir);
        }
        $pdo = $this->pdo();
        if ($pdo !== null) { $pdo->prepare("DELETE FROM `pp_virtual_databases` WHERE id = ?")->execute([$id]); }
        return ['success' => true, 'message' => 'Virtual database deleted.'];
    }

    public function vacuum(int $id): array
    {
        $row = $this->get($id); if ($row === null) { return ['success' => false, 'message' => 'Not found.']; }
        try { VirtualDb::vacuum($this->pathFor($row)); return ['success' => true, 'message' => 'Optimised (VACUUM).']; }
        catch (\Throwable $e) { return ['success' => false, 'message' => $e->getMessage()]; }
    }

    public function integrity(int $id): array
    {
        $row = $this->get($id); if ($row === null) { return ['success' => false, 'message' => 'Not found.']; }
        $res = VirtualDb::integrityCheck($this->pathFor($row));
        return ['success' => $res === 'ok', 'message' => $res === 'ok' ? 'Integrity check passed.' : $res];
    }

    /** Produce a consistent snapshot in the OS temp dir; caller streams + unlinks it. */
    public function backupSnapshot(int $id): ?array
    {
        $row = $this->get($id); if ($row === null) { return null; }
        $tmp = rtrim(sys_get_temp_dir(), '/\\') . '/ppvdb_' . bin2hex(random_bytes(4)) . '.sqlite';
        try {
            VirtualDb::backupTo($this->pathFor($row), $tmp);
            return ['path' => $tmp, 'filename' => VirtualDb::safeName($row['label'] !== '' ? $row['label'] : $row['name']) . '.sqlite'];
        } catch (\Throwable $e) {
            return null;
        }
    }

    /** Replace a pool database's contents from an uploaded SQLite file. */
    public function restore(int $id, string $uploadedTmpPath): array
    {
        $row = $this->get($id); if ($row === null) { return ['success' => false, 'message' => 'Not found.']; }
        if (!VirtualDb::isSqliteFile($uploadedTmpPath)) {
            return ['success' => false, 'message' => 'That file is not a valid SQLite database.'];
        }
        $dest = $this->pathFor($row);
        VirtualDb::deleteFile($dest);                 // clear old main + WAL/SHM
        if (!@copy($uploadedTmpPath, $dest)) {
            return ['success' => false, 'message' => 'Could not write the restored file.'];
        }
        // integrity gate
        $chk = VirtualDb::integrityCheck($dest);
        return ['success' => $chk === 'ok', 'message' => $chk === 'ok' ? 'Database restored.' : 'Restored, but integrity check reported: ' . $chk];
    }

    /* ── maintenance · snapshots · clone (robustness) ── */

    /** Rotating-snapshot directory for one pool database. */
    private function snapDir(array $row): string
    {
        return VirtualDb::poolDir() . '/_snapshots/' . VirtualDb::safeName((string) $row['name']);
    }

    /**
     * Per-install secret used to encrypt snapshots at rest. Generated once and
     * kept in platform settings (never leaves the server).
     */
    private function snapshotKey(): string
    {
        $bag = \PpPhp\Core\PlatformSettings::get('vdb_snapshot_key', ['k' => '']);
        $k = (string) ($bag['k'] ?? '');
        if (strlen($k) < 32) {
            $k = bin2hex(random_bytes(32));
            \PpPhp\Core\PlatformSettings::set('vdb_snapshot_key', ['k' => $k]);
        }
        return $k;
    }

    /**
     * Take a consistent, rotating snapshot of a pool database (keeping the newest
     * $keep). Optionally AES-256-GCM encrypted at rest.
     * @return array{success:bool,message:string,path?:string}
     */
    public function snapshot(int $id, int $keep = 7, bool $encrypt = false): array
    {
        $row = $this->get($id); if ($row === null) { return ['success' => false, 'message' => 'Not found.']; }
        $src = $this->pathFor($row);
        if (!VirtualDb::isSqliteFile($src)) { return ['success' => false, 'message' => 'The database file is missing or invalid.']; }
        try {
            $path = VirtualDb::snapshot($src, $this->snapDir($row), max(1, $keep), $encrypt ? $this->snapshotKey() : null);
            return ['success' => true, 'message' => 'Snapshot saved' . ($encrypt ? ' (encrypted).' : '.'), 'path' => $path];
        } catch (\Throwable $e) {
            return ['success' => false, 'message' => 'Snapshot failed: ' . $e->getMessage()];
        }
    }

    /** Newest-first snapshot history for a pool database (for the console). */
    public function snapshots(int $id): array
    {
        $row = $this->get($id); if ($row === null) { return []; }
        return array_map(static function (array $s): array {
            return [
                'name'      => $s['name'],
                'size'      => VirtualDb::humanSize((int) $s['bytes']),
                'bytes'     => (int) $s['bytes'],
                'when'      => date('Y-m-d H:i', (int) $s['mtime']),
                'encrypted' => (bool) $s['encrypted'],
            ];
        }, VirtualDb::listSnapshots($this->snapDir($row)));
    }

    /** Restore a named snapshot back into the live database (integrity-gated). */
    public function restoreSnapshot(int $id, string $snapName): array
    {
        $row = $this->get($id); if ($row === null) { return ['success' => false, 'message' => 'Not found.']; }
        $snapName = basename($snapName);                       // no path traversal
        $snap = $this->snapDir($row) . '/' . $snapName;
        if (!is_file($snap)) { return ['success' => false, 'message' => 'That snapshot no longer exists.']; }

        // Decrypt to a temp plain file first when the snapshot is encrypted.
        $plain = $snap;
        $tmp = null;
        if (str_ends_with($snap, '.enc')) {
            $tmp = rtrim(sys_get_temp_dir(), '/\\') . '/ppvdb_rs_' . bin2hex(random_bytes(4)) . '.sqlite';
            try { VirtualDb::decryptFile($snap, $tmp, $this->snapshotKey()); }
            catch (\Throwable $e) { return ['success' => false, 'message' => $e->getMessage()]; }
            $plain = $tmp;
        }
        $res = $this->restore($id, $plain);
        if ($tmp !== null) { VirtualDb::deleteFile($tmp); }
        return $res;
    }

    /** One health/maintenance pass (quick_check + optimize + WAL checkpoint). */
    public function maintain(int $id): array
    {
        $row = $this->get($id); if ($row === null) { return ['success' => false, 'message' => 'Not found.']; }
        $rep = VirtualDb::maintain($this->pathFor($row));
        return [
            'success' => $rep['ok'],
            'message' => $rep['ok']
                ? 'Maintenance done — planner refreshed, WAL checkpointed' . ($rep['bytes_before'] !== $rep['bytes_after'] ? ', ' . VirtualDb::humanSize($rep['bytes_before']) . ' → ' . VirtualDb::humanSize($rep['bytes_after']) . '.' : '.')
                : ('Integrity problem: ' . ($rep['error'] !== '' ? $rep['error'] : 'quick_check failed')),
        ];
    }

    /**
     * Maintain + snapshot every pool database. Built for a cron/scheduled tick.
     * @return array{count:int,ok:int,failed:int}
     */
    public function maintainAll(int $keep = 7, bool $encrypt = false): array
    {
        $count = 0; $ok = 0; $failed = 0;
        foreach ($this->all() as $db) {
            if (empty($db['exists'])) { continue; }
            $count++;
            $m = $this->maintain((int) $db['id']);
            $s = $this->snapshot((int) $db['id'], $keep, $encrypt);
            if (($m['success'] ?? false) && ($s['success'] ?? false)) { $ok++; } else { $failed++; }
        }
        return ['count' => $count, 'ok' => $ok, 'failed' => $failed];
    }

    /* ── scheduled maintenance settings + cron ── */

    private function maint(): array
    {
        return \PpPhp\Core\PlatformSettings::get('vdb_maintenance', [
            'secret' => '', 'enabled' => false, 'keep' => 7, 'encrypt' => false,
            'interval_hours' => 24, 'last_run' => 0, 'last_result' => '',
        ]);
    }

    /** Token used to authenticate the maintenance cron URL (generated once). */
    public function cronSecret(): string
    {
        $m = $this->maint();
        if (!is_string($m['secret'] ?? null) || strlen((string) $m['secret']) < 24) {
            $m['secret'] = bin2hex(random_bytes(16));
            \PpPhp\Core\PlatformSettings::set('vdb_maintenance', $m);
        }
        return (string) $m['secret'];
    }

    /** Owner-facing schedule settings + last-run info (never exposes the raw secret). */
    public function maintenanceSettings(): array
    {
        $m = $this->maint();
        $this->cronSecret();   // ensure a secret exists
        return [
            'enabled'        => (bool) ($m['enabled'] ?? false),
            'keep'           => max(1, min(50, (int) ($m['keep'] ?? 7))),
            'encrypt'        => (bool) ($m['encrypt'] ?? false),
            'interval_hours' => max(1, (int) ($m['interval_hours'] ?? 24)),
            'last_run'       => (int) ($m['last_run'] ?? 0),
            'last_run_h'     => ($m['last_run'] ?? 0) ? date('Y-m-d H:i', (int) $m['last_run']) : 'never',
            'last_result'    => (string) ($m['last_result'] ?? ''),
        ];
    }

    public function setMaintenanceSettings(array $in): array
    {
        $m = $this->maint();
        $m['secret']         = $this->cronSecret();
        $m['enabled']        = !empty($in['enabled']);
        $m['keep']           = max(1, min(50, (int) ($in['keep'] ?? ($m['keep'] ?? 7))));
        $m['encrypt']        = !empty($in['encrypt']);
        $m['interval_hours'] = max(1, (int) ($in['interval_hours'] ?? ($m['interval_hours'] ?? 24)));
        \PpPhp\Core\PlatformSettings::set('vdb_maintenance', $m);
        return ['success' => true, 'message' => 'Schedule saved.', 'settings' => $this->maintenanceSettings()];
    }

    /**
     * Maintain + snapshot every pool database, honouring the keep/encrypt settings,
     * then record the run. $trigger 'cron' additionally respects the enabled flag and
     * the interval (a too-frequent cron hit is a safe no-op); 'manual' always runs.
     */
    public function runScheduledMaintenance(string $trigger = 'cron'): array
    {
        $m = $this->maint();
        if ($trigger === 'cron') {
            if (empty($m['enabled'])) {
                return ['success' => true, 'skipped' => true, 'message' => 'Scheduled maintenance is disabled.'];
            }
            $due = (int) ($m['last_run'] ?? 0) + max(1, (int) ($m['interval_hours'] ?? 24)) * 3600;
            if (time() < $due) {
                return ['success' => true, 'skipped' => true, 'message' => 'Not due yet.', 'next_due' => $due];
            }
        }
        $res = $this->maintainAll(max(1, min(50, (int) ($m['keep'] ?? 7))), !empty($m['encrypt']));
        $m['last_run']    = time();
        $m['last_result'] = "count={$res['count']} ok={$res['ok']} failed={$res['failed']}";
        \PpPhp\Core\PlatformSettings::set('vdb_maintenance', $m);
        return ['success' => true, 'report' => $res,
            'message' => "Maintained {$res['ok']}/{$res['count']} databases" . ($res['failed'] ? ", {$res['failed']} with problems." : '.')];
    }

    /**
     * Fork an existing pool database into a brand-new one (structure + data).
     * @return array{success:bool,message:string,id?:int}
     */
    public function duplicate(int $id, string $label = '', ?int $createdBy = null, ?int $assignTo = null): array
    {
        $pdo = $this->pdo();
        if ($pdo === null) { return ['success' => false, 'message' => 'No connection to the platform database.']; }
        $row = $this->get($id); if ($row === null) { return ['success' => false, 'message' => 'Not found.']; }
        $src = $this->pathFor($row);
        if (!VirtualDb::isSqliteFile($src)) { return ['success' => false, 'message' => 'The source database is missing or invalid.']; }

        $typed = trim($label) !== '' ? trim($label) : ((string) $row['label'] !== '' ? $row['label'] . ' copy' : 'copy');
        // The clone belongs to whoever it's assigned to (or the cloner) — prefix + own it.
        $ownerUid = $assignTo ?? $createdBy;
        $prefix   = $this->accountPrefix($ownerUid);
        $name     = $this->uniqueName(VirtualDb::composeName($prefix, $typed));
        try {
            VirtualDb::cloneTo($src, VirtualDb::poolPath($name));
        } catch (\Throwable $e) {
            return ['success' => false, 'message' => 'Could not clone the database file: ' . $e->getMessage()];
        }
        try {
            $st = $pdo->prepare("INSERT INTO `pp_virtual_databases` (name, label, created_by, assigned_to) VALUES (?,?,?,?)");
            $st->execute([$name, mb_substr($typed, 0, 160), $createdBy, $assignTo]);
            return ['success' => true, 'message' => 'Database cloned to “' . $name . '”.', 'id' => (int) $pdo->lastInsertId(), 'name' => $name];
        } catch (\Throwable $e) {
            VirtualDb::deleteFile(VirtualDb::poolPath($name));
            return ['success' => false, 'message' => 'Clone written, but could not record it: ' . $e->getMessage()];
        }
    }

    /** Users list for the assignment dropdown (best-effort). */
    public function assignableUsers(): array
    {
        $pdo = $this->pdo(); if ($pdo === null) { return []; }
        try {
            return $pdo->query("SELECT id, username FROM `users` ORDER BY username ASC")->fetchAll(\PDO::FETCH_ASSOC) ?: [];
        } catch (\Throwable $e) {
            return [];
        }
    }
}
