<?php

declare(strict_types=1);

namespace PpPhp\Modules\DatabaseManager\Controllers;

use PpPhp\Core\Config;
use PpPhp\Core\DatabaseConnection;
use PpPhp\Core\Database\Dialect\DatabaseDialectRegistry;
use PpPhp\Core\Database\SqlScriptRunner;
use PpPhp\Core\Databases\VirtualDb;
use PpPhp\Core\Databases\VirtualDbService;
use PpPhp\Core\Databases\VirtualDbSizePolicy;
use PpPhp\Core\Projects\SqlScriptService;
use PpPhp\Core\Security;

/**
 * Virtual Database Engine — console page + JSON API.
 *
 * The owner manages the whole pool (create / assign / quotas / drop / backup /
 * restore) and every database's contents through the built-in admin (tables,
 * rows, structure, SQL console, exports). Regular users see and manage ONLY the
 * databases assigned to them, and can create their own within the quota the
 * owner sets. All data access runs on the SQLite dialect the platform already
 * ships — no external server is ever involved.
 */
class DatabaseController
{
    public function __construct(private Config $config)
    {
    }

    /* ───────────────────────── page ───────────────────────── */

    public function index(): string
    {
        Security::RequireAuth();
        $baseUrl   = $this->config->get('base_url');
        $assetsUrl = $this->config->get('assets_url');
        $user      = Security::CurrentUserData();
        $isOwner   = Security::IsOwner();
        $svc       = new VirtualDbService();
        $svc->ensureSchema();

        $databases = $this->scoped($svc->all(), $isOwner, (int) ($user['id'] ?? 0));
        $users     = $isOwner ? $svc->assignableUsers() : [];
        $quota     = [
            'default'  => $svc->defaultQuota(),
            'mine'     => $isOwner ? -1 : $svc->effectiveQuota((int) ($user['id'] ?? 0)),
            'used'     => $isOwner ? 0 : $svc->usage((int) ($user['id'] ?? 0)),
            'report'   => $isOwner ? $svc->quotaReport() : [],
        ];
        $targets = SqlScriptService::targetOptions();

        ob_start();
        include __DIR__ . '/../Views/index.php';
        return (string) ob_get_clean();
    }

    /**
     * Data Studio — the full-screen, phpMyAdmin-class workspace. Any signed-in user
     * gets it; the databases they see are scoped by visibleTo() (owned / assigned /
     * shared). The heavy lifting reuses the same JSON API as the console.
     */
    public function studio(): string
    {
        Security::RequireAuth();
        $baseUrl   = $this->config->get('base_url');
        $assetsUrl = $this->config->get('assets_url');
        $user      = Security::CurrentUserData();
        $isOwner   = Security::IsOwner();
        $csrf      = Security::CSRF()->Token();
        $svc       = new VirtualDbService();
        $svc->ensureSchema();

        $uid       = (int) ($user['id'] ?? 0);
        $databases = $svc->visibleTo($uid, $isOwner);
        $shareUsers = $isOwner || in_array((string) Security::CurrentUserGroup(), ['superadmin', 'developer'], true);
        $myPrefix   = $svc->accountPrefix($uid);                 // this user's cPanel-style DB name prefix
        $assignUsers = $isOwner ? $svc->assignableUsers() : [];  // owner may create/assign for anyone
        $planSlug  = VirtualDbSizePolicy::currentPlanSlug();
        $planName  = \PpPhp\Core\Licensing\PlanRegistry::nameOf($planSlug);
        $planCapMb = VirtualDbSizePolicy::planCapMb($planSlug);
        $quota     = [
            'default' => $svc->defaultQuota(),
            'mine'    => $isOwner ? -1 : $svc->effectiveQuota($uid),
            'used'    => $isOwner ? count($databases) : $svc->usage($uid),
        ];
        $targets   = SqlScriptService::targetOptions();

        ob_start();
        include __DIR__ . '/../Views/studio.php';
        return (string) ob_get_clean();
    }

    /* ───────────────────────── pool lifecycle ───────────────────────── */

    public function apiDatabases(): void
    {
        [$uid, $isOwner] = $this->jsonGate();
        $svc = new VirtualDbService();
        echo json_encode(['success' => true, 'databases' => $this->scoped($svc->all(), $isOwner, $uid)]);
        exit;
    }

    public function create(): void
    {
        [$uid, $isOwner] = $this->jsonGate();
        $in  = $this->jsonInput();
        $svc = new VirtualDbService();
        // Owner may create for anyone (or leave unassigned); a user always
        // creates for themself — that is what the quota meters.
        $assign = $isOwner
            ? ((int) ($in['assigned_to'] ?? 0) > 0 ? (int) $in['assigned_to'] : null)
            : $uid;
        if (!$isOwner && $svc->quotaExceeded($uid)) {
            $this->fail('You have reached your virtual-database quota (' . $svc->effectiveQuota($uid) . '). Ask the owner to raise it.');
        }
        $res = $svc->create(trim((string) ($in['label'] ?? '')), $uid, $assign);
        if (!$res['success']) http_response_code(400);
        echo json_encode($res);
        exit;
    }

    public function rename(): void
    {
        $this->jsonGate();
        $in  = $this->jsonInput();
        $row = $this->rowFor((int) ($in['id'] ?? $in['db'] ?? 0));
        $this->requireWrite($row);
        echo json_encode((new VirtualDbService())->rename((int) $row['id'], (string) ($in['label'] ?? '')));
        exit;
    }

    public function assign(): void
    {
        [, $isOwner] = $this->jsonGate();
        if (!$isOwner) $this->fail('Only the owner can assign databases.', 403);
        $in  = $this->jsonInput();
        $userId = (int) ($in['user_id'] ?? 0) > 0 ? (int) $in['user_id'] : null;
        $res = (new VirtualDbService())->assignUser((int) ($in['id'] ?? 0), $userId);
        if (!$res['success']) http_response_code(400);
        echo json_encode($res);
        exit;
    }

    public function drop(): void
    {
        $this->jsonGate();
        $in  = $this->jsonInput();
        $row = $this->rowFor((int) ($in['id'] ?? $in['db'] ?? 0));
        $this->requireWrite($row);
        echo json_encode((new VirtualDbService())->drop((int) $row['id']));
        exit;
    }

    public function vacuum(): void
    {
        $this->jsonGate();
        $in  = $this->jsonInput();
        $row = $this->rowFor((int) ($in['id'] ?? $in['db'] ?? 0));
        $this->requireWrite($row);
        echo json_encode((new VirtualDbService())->vacuum((int) $row['id']));
        exit;
    }

    public function integrity(): void
    {
        $this->jsonGate();
        $in  = $this->jsonInput();
        $row = $this->rowFor((int) ($in['id'] ?? $in['db'] ?? 0));
        echo json_encode((new VirtualDbService())->integrity((int) $row['id']));
        exit;
    }

    /** POST → one health/maintenance pass (quick_check + planner refresh + WAL checkpoint). */
    public function maintain(): void
    {
        $this->jsonGate();
        $in  = $this->jsonInput();
        $row = $this->rowFor((int) ($in['id'] ?? $in['db'] ?? 0));
        $this->requireWrite($row);
        echo json_encode((new VirtualDbService())->maintain((int) $row['id']));
        exit;
    }

    /** POST → clone a database (structure + data) into a brand-new pool database. */
    public function duplicate(): void
    {
        [$uid, $isOwner] = $this->jsonGate();
        $in  = $this->jsonInput();
        $row = $this->rowFor((int) ($in['id'] ?? $in['db'] ?? 0));
        $this->requireWrite($row);
        $svc = new VirtualDbService();
        // The clone belongs to the person making it (unless the owner is cloning),
        // so it is prefixed with their handle and counts against their quota.
        $assign = $isOwner ? null : $uid;
        if (!$isOwner && $svc->quotaExceeded($uid)) {
            $this->fail('You have reached your virtual-database quota (' . $svc->effectiveQuota($uid) . '). Ask the owner to raise it.');
        }
        $res = $svc->duplicate((int) $row['id'], (string) ($in['label'] ?? ''), $uid, $assign);
        if (!($res['success'] ?? false)) http_response_code(400);
        echo json_encode($res);
        exit;
    }

    /** POST → take a rotating snapshot (optionally AES-256-GCM encrypted at rest). */
    public function snapshot(): void
    {
        $this->jsonGate();
        $in  = $this->jsonInput();
        $row = $this->rowFor((int) ($in['id'] ?? $in['db'] ?? 0));
        $this->requireWrite($row);
        $keep = max(1, min(50, (int) ($in['keep'] ?? 7)));
        echo json_encode((new VirtualDbService())->snapshot((int) $row['id'], $keep, !empty($in['encrypt'])));
        exit;
    }

    /** GET → snapshot history for a database (newest first). */
    public function snapshots(): void
    {
        Security::RequireAuth();
        header('Content-Type: application/json');
        $row = $this->rowFor((int) ($_GET['db'] ?? 0));
        echo json_encode(['success' => true, 'snapshots' => (new VirtualDbService())->snapshots((int) $row['id'])]);
        exit;
    }

    /** POST → restore a named snapshot back into the live database (integrity-gated). */
    public function snapshotRestore(): void
    {
        $this->jsonGate();
        $in  = $this->jsonInput();
        $row = $this->rowFor((int) ($in['id'] ?? $in['db'] ?? 0));
        $this->requireWrite($row);
        $res = (new VirtualDbService())->restoreSnapshot((int) $row['id'], (string) ($in['snapshot'] ?? ''));
        if (!($res['success'] ?? false)) http_response_code(400);
        echo json_encode($res);
        exit;
    }

    /** GET → download a consistent .sqlite snapshot. */
    public function backup(): void
    {
        Security::RequireAuth();
        $row = $this->rowFor((int) ($_GET['db'] ?? 0), asJson: false);
        $bk  = (new VirtualDbService())->backupSnapshot((int) $row['id']);
        if ($bk === null) { http_response_code(500); echo 'Backup failed.'; exit; }
        header('Content-Type: application/vnd.sqlite3');
        header('Content-Disposition: attachment; filename="' . $bk['filename'] . '"');
        header('Content-Length: ' . (string) filesize($bk['path']));
        header('Cache-Control: no-store');
        readfile($bk['path']);
        @unlink($bk['path']);
        exit;
    }

    /** POST multipart → replace a database's contents from an uploaded .sqlite file. */
    public function restore(): void
    {
        $this->jsonGate();
        if (!Security::CSRF()->Check((string) ($_POST['_token'] ?? ''))) {
            $this->fail('Your session expired. Refresh and try again.', 419);
        }
        $row = $this->rowFor((int) ($_POST['id'] ?? 0));
        $this->requireWrite($row);
        $f = $_FILES['file'] ?? null;
        if (!is_array($f) || ($f['error'] ?? \UPLOAD_ERR_NO_FILE) !== \UPLOAD_ERR_OK) {
            $this->fail('No file uploaded.');
        }
        if (($f['size'] ?? 0) > 512 * 1024 * 1024) {
            $this->fail('Backups over 512 MB cannot be restored through the browser.');
        }
        $res = (new VirtualDbService())->restore((int) $row['id'], (string) $f['tmp_name']);
        if (!$res['success']) http_response_code(400);
        echo json_encode($res);
        exit;
    }

    /* ───────────────────────── quotas (owner) ───────────────────────── */

    public function apiQuotas(): void
    {
        [, $isOwner] = $this->jsonGate();
        if (!$isOwner) $this->fail('Owner only.', 403);
        $svc = new VirtualDbService();
        echo json_encode(['success' => true, 'default' => $svc->defaultQuota(), 'report' => $svc->quotaReport()]);
        exit;
    }

    public function setQuota(): void
    {
        [, $isOwner] = $this->jsonGate();
        if (!$isOwner) $this->fail('Owner only.', 403);
        $in  = $this->jsonInput();
        $svc = new VirtualDbService();
        if (array_key_exists('default', $in)) {
            $ok = $svc->setDefaultQuota((int) $in['default']);
            echo json_encode(['success' => $ok, 'message' => $ok ? 'Default quota updated.' : 'Could not save the default quota.']);
            exit;
        }
        $userId = (int) ($in['user_id'] ?? 0);
        if ($userId < 1) $this->fail('A user is required.');
        $max = ($in['max'] === null || $in['max'] === '') ? null : (int) $in['max'];
        echo json_encode($svc->setUserQuota($userId, $max));
        exit;
    }

    /* ───────────────────────── sharing (owner + database managers) ───────────────────────── */

    /** True when the current user MANAGES this DB (owner, or its creator/assignee). */
    private function isManager(array $row): bool
    {
        if (Security::IsOwner()) { return true; }
        $uid = (int) (Security::CurrentUserData()['id'] ?? 0);
        return (int) ($row['created_by'] ?? 0) === $uid || (int) ($row['assigned_to'] ?? 0) === $uid;
    }

    /** GET → who a database is shared with + teammates it can be shared with. */
    public function apiShares(): void
    {
        $this->jsonGate();
        $row = $this->rowFor((int) ($_GET['db'] ?? 0));
        if (!$this->isManager($row)) $this->fail('Only the database owner can manage sharing.', 403);
        $svc = new VirtualDbService();
        $u = Security::CurrentUserData();
        echo json_encode([
            'success'    => true,
            'shares'     => $svc->sharesOf((int) $row['id']),
            'candidates' => $svc->shareCandidates((int) ($u['id'] ?? 0), (string) Security::CurrentUserGroup()),
        ]);
        exit;
    }

    /** POST → share a database with a teammate (read or read/write). */
    public function share(): void
    {
        $this->jsonGate();
        $in  = $this->jsonInput();
        $row = $this->rowFor((int) ($in['db'] ?? $in['id'] ?? 0));
        if (!$this->isManager($row)) $this->fail('Only the database owner can share it.', 403);
        echo json_encode((new VirtualDbService())->share((int) $row['id'], (int) ($in['user_id'] ?? 0), !empty($in['can_write'])));
        exit;
    }

    /** POST → stop sharing a database with a teammate. */
    public function unshare(): void
    {
        $this->jsonGate();
        $in  = $this->jsonInput();
        $row = $this->rowFor((int) ($in['db'] ?? $in['id'] ?? 0));
        if (!$this->isManager($row)) $this->fail('Only the database owner can manage sharing.', 403);
        echo json_encode((new VirtualDbService())->unshare((int) $row['id'], (int) ($in['user_id'] ?? 0)));
        exit;
    }

    /* ───────────────────────── size caps (owner) ───────────────────────── */

    /** GET → per-plan DB size caps + the install's active plan (owner only). */
    public function apiSizePolicy(): void
    {
        [, $isOwner] = $this->jsonGate();
        if (!$isOwner) $this->fail('Owner only.', 403);
        $plans = [];
        foreach (\PpPhp\Core\Licensing\PlanRegistry::all() as $p) {
            $plans[] = ['slug' => $p['slug'], 'name' => $p['name'], 'icon' => $p['icon'], 'color' => $p['color'],
                        'mb' => $p['max_db_mb']];   // null = unlimited
        }
        echo json_encode(['success' => true, 'plans' => $plans, 'current_plan' => VirtualDbSizePolicy::currentPlanSlug()]);
        exit;
    }

    /** POST → save per-plan caps and/or pin the install's active plan (owner only). */
    public function saveSizePolicy(): void
    {
        [, $isOwner] = $this->jsonGate();
        if (!$isOwner) $this->fail('Owner only.', 403);
        $in = $this->jsonInput();
        $caps = is_array($in['caps'] ?? null) ? $in['caps'] : [];
        foreach ($caps as $slug => $mb) {
            $p = \PpPhp\Core\Licensing\PlanRegistry::get((string) $slug);
            if ($p === null) { continue; }
            $p['max_db_mb'] = ($mb === '' || $mb === null) ? 0 : max(0, (int) $mb);   // 0 = unlimited
            \PpPhp\Core\Licensing\PlanRegistry::save($p);
        }
        if (!empty($in['current_plan'])) {
            \PpPhp\Core\PlatformSettings::set('vdb_current_plan', ['slug' => (string) $in['current_plan']]);
        }
        echo json_encode(['success' => true, 'message' => 'Size policy saved.']);
        exit;
    }

    /** POST → set one database's size override (owner). mb: ''/0 = inherit plan, -1 = unlimited, N = N MB. */
    public function setSize(): void
    {
        [, $isOwner] = $this->jsonGate();
        if (!$isOwner) $this->fail('Owner only.', 403);
        $in  = $this->jsonInput();
        $row = $this->rowFor((int) ($in['id'] ?? $in['db'] ?? 0));
        $mb  = $in['mb'] ?? '';
        if ($mb === '' || $mb === null) { $bytes = 0; }
        elseif ((int) $mb < 0)          { $bytes = -1; }
        else                            { $bytes = max(0, (int) $mb) * 1048576; }
        echo json_encode((new VirtualDbService())->setSizeOverride((int) $row['id'], $bytes));
        exit;
    }

    /* ───────────────────────── database admin (browse / edit / SQL) ───────────────────────── */

    public function apiTables(): void
    {
        $this->jsonGate();
        $row = $this->rowFor((int) ($_GET['db'] ?? 0));
        [$pdo, $dialect, $cfg] = $this->open($row);
        $objects = [];
        foreach ($dialect->listObjects($pdo, $cfg) as $o) {
            $name = (string) $o['name'];
            $type = strtoupper((string) ($o['type'] ?? 'TABLE'));
            $rows = 0;
            if ($type !== 'VIEW') {
                try { $rows = (int) $dialect->countRows($pdo, $name, $cfg); } catch (\Throwable $e) { $rows = 0; }
            }
            $objects[] = ['name' => $name, 'type' => $type, 'rows' => $rows];
        }
        usort($objects, static fn ($a, $b) => strcasecmp($a['name'], $b['name']));
        $stats = VirtualDb::stats(VirtualDb::poolPath((string) $row['name']));
        echo json_encode(['success' => true, 'objects' => $objects,
            'stats' => ['size' => VirtualDb::humanSize((int) $stats['bytes']), 'tables' => $stats['tables'], 'ok' => $stats['ok']]]);
        exit;
    }

    public function apiStructure(): void
    {
        $this->jsonGate();
        $row = $this->rowFor((int) ($_GET['db'] ?? 0));
        [$pdo, $dialect, $cfg] = $this->open($row);
        $table = $this->tableIn($pdo, $dialect, $cfg, (string) ($_GET['table'] ?? ''));
        echo json_encode(['success' => true, 'details' => $dialect->describeTable($pdo, $table, $cfg)]);
        exit;
    }

    public function apiRows(): void
    {
        $this->jsonGate();
        $row = $this->rowFor((int) ($_GET['db'] ?? 0));
        [$pdo, $dialect, $cfg] = $this->open($row);
        $table = $this->tableIn($pdo, $dialect, $cfg, (string) ($_GET['table'] ?? ''));
        $q     = trim((string) ($_GET['q'] ?? ''));
        $page  = max(1, (int) ($_GET['page'] ?? 1));
        $per   = max(1, min(100, (int) ($_GET['per'] ?? 25)));
        $sort  = trim((string) ($_GET['sort'] ?? ''));
        $dir   = strtolower((string) ($_GET['dir'] ?? 'asc')) === 'desc' ? 'DESC' : 'ASC';

        $qt   = $dialect->quoteIdentifier($table);
        $cols = $this->columnsOf($pdo, $table);
        $names = array_column($cols, 'name');

        $clauses = [];
        $params = [];
        // global "search all columns"
        if ($q !== '') {
            $like = [];
            foreach ($names as $n) { $like[] = 'CAST(' . $dialect->quoteIdentifier($n) . ' AS TEXT) LIKE ?'; $params[] = '%' . $q . '%'; }
            if ($like) $clauses[] = '(' . implode(' OR ', $like) . ')';
        }
        // per-column Search-tab filters: {"col":"value", …} → AND col LIKE %value%
        $filters = $_GET['filters'] ?? '';
        if (is_string($filters) && $filters !== '') { $filters = json_decode($filters, true); }
        if (is_array($filters)) {
            foreach ($filters as $col => $val) {
                if (!in_array((string) $col, $names, true) || $val === '' || $val === null) continue;
                $clauses[] = 'CAST(' . $dialect->quoteIdentifier((string) $col) . ' AS TEXT) LIKE ?';
                $params[] = '%' . $val . '%';
            }
        }
        $where = $clauses ? (' WHERE ' . implode(' AND ', $clauses)) : '';
        $order = ($sort !== '' && in_array($sort, $names, true))
            ? ' ORDER BY ' . $dialect->quoteIdentifier($sort) . ' ' . $dir
            : '';
        $limit = ' LIMIT ' . $per . ' OFFSET ' . (($page - 1) * $per);

        $st = $pdo->prepare("SELECT COUNT(*) FROM {$qt}{$where}");
        $st->execute($params);
        $total = (int) $st->fetchColumn();

        // rowid gives us a stable editing handle; WITHOUT ROWID tables browse read-only.
        $editable = true;
        try {
            $st = $pdo->prepare("SELECT rowid AS __rid, * FROM {$qt}{$where}{$order}{$limit}");
            $st->execute($params);
            $data = $st->fetchAll(\PDO::FETCH_ASSOC);
        } catch (\Throwable $e) {
            $editable = false;
            $st = $pdo->prepare("SELECT * FROM {$qt}{$where}{$order}{$limit}");
            $st->execute($params);
            $data = $st->fetchAll(\PDO::FETCH_ASSOC);
        }
        // Binary values can't travel as JSON — flag + hex-preview them.
        foreach ($data as &$r) {
            foreach ($r as $k => $v) {
                if (is_string($v) && (str_contains($v, "\0") || !mb_check_encoding($v, 'UTF-8'))) {
                    $r[$k] = ['__blob' => true, 'hex' => strtoupper(substr(bin2hex($v), 0, 32)), 'bytes' => strlen($v)];
                }
            }
        }
        unset($r);
        echo json_encode(['success' => true, 'columns' => $cols, 'rows' => $data,
            'total' => $total, 'page' => $page, 'per' => $per, 'editable' => $editable]);
        exit;
    }

    public function apiRowSave(): void
    {
        $this->jsonGate();
        $in  = $this->jsonInput();
        $row = $this->rowFor((int) ($in['db'] ?? 0));
        [$pdo, $dialect, $cfg] = $this->open($row);
        $table = $this->tableIn($pdo, $dialect, $cfg, (string) ($in['table'] ?? ''));
        $qt    = $dialect->quoteIdentifier($table);
        $cols  = $this->columnsOf($pdo, $table);
        $types = array_column($cols, 'type', 'name');
        $data  = is_array($in['data'] ?? null) ? $in['data'] : [];

        $sets = [];
        $vals = [];
        foreach ($data as $col => $val) {
            if (!array_key_exists($col, $types)) continue;      // unknown column → ignored
            // '' into a non-text column means "no value" — store NULL, not ''.
            if ($val === '' && !preg_match('/CHAR|TEXT|CLOB/i', (string) $types[$col])) $val = null;
            $sets[$col] = $val;
        }
        if ($sets === []) $this->fail('Nothing to save.');

        $rid = $in['rid'] ?? null;
        try {
            if ($rid === null || $rid === '') {
                $names = array_map(fn ($c) => $dialect->quoteIdentifier((string) $c), array_keys($sets));
                $st = $pdo->prepare("INSERT INTO {$qt} (" . implode(', ', $names) . ') VALUES (' . rtrim(str_repeat('?,', count($sets)), ',') . ')');
                $st->execute(array_values($sets));
                echo json_encode(['success' => true, 'message' => 'Row added.', 'rid' => $pdo->lastInsertId()]);
            } else {
                $frag = [];
                foreach (array_keys($sets) as $c) $frag[] = $dialect->quoteIdentifier((string) $c) . ' = ?';
                $vals = array_values($sets);
                $vals[] = (int) $rid;
                $st = $pdo->prepare("UPDATE {$qt} SET " . implode(', ', $frag) . ' WHERE rowid = ?');
                $st->execute($vals);
                echo json_encode(['success' => true, 'message' => 'Row updated.']);
            }
        } catch (\Throwable $e) {
            $this->fail($e->getMessage());
        }
        exit;
    }

    public function apiRowDelete(): void
    {
        $this->jsonGate();
        $in  = $this->jsonInput();
        $row = $this->rowFor((int) ($in['db'] ?? 0));
        [$pdo, $dialect, $cfg] = $this->open($row);
        $table = $this->tableIn($pdo, $dialect, $cfg, (string) ($in['table'] ?? ''));
        $st = $pdo->prepare('DELETE FROM ' . $dialect->quoteIdentifier($table) . ' WHERE rowid = ?');
        $st->execute([(int) ($in['rid'] ?? -1)]);
        echo json_encode(['success' => true, 'message' => 'Row deleted.', 'affected' => $st->rowCount()]);
        exit;
    }

    public function apiTableDrop(): void
    {
        $this->jsonGate();
        $in  = $this->jsonInput();
        $row = $this->rowFor((int) ($in['db'] ?? 0));
        [$pdo, $dialect, $cfg] = $this->open($row);
        $name = (string) ($in['table'] ?? '');
        $type = strtoupper((string) ($in['type'] ?? 'TABLE')) === 'VIEW' ? 'VIEW' : 'TABLE';
        $this->tableIn($pdo, $dialect, $cfg, $name);
        try {
            $dialect->dropObject($pdo, $name, $type, $cfg);
            echo json_encode(['success' => true, 'message' => ($type === 'VIEW' ? 'View' : 'Table') . ' dropped.']);
        } catch (\Throwable $e) {
            $this->fail($e->getMessage());
        }
        exit;
    }

    /* ─────────────────── visual table / view designer ─────────────────── */

    /** Build a schema designer bound to one pool database. @return array{0:\PpPhp\Core\Database\Schema\TableDesignerService,1:\PDO,2:object,3:array} */
    private function designerFor(array $row): array
    {
        [$pdo, $dialect, $cfg] = $this->open($row);
        return [new \PpPhp\Core\Database\Schema\TableDesignerService($pdo, $dialect, $cfg), $pdo, $dialect, $cfg];
    }

    /** GET → UI metadata for the visual builder (field types, sized/enum/temporal, capabilities). */
    public function apiDialectMeta(): void
    {
        $this->jsonGate();
        $row = $this->rowFor((int) ($_GET['db'] ?? 0));
        [$svc] = $this->designerFor($row);
        echo json_encode(['success' => true, 'ui' => $svc->dialectConfig()]);
        exit;
    }

    /** POST → create a new table from the visual builder. */
    public function apiTableCreate(): void
    {
        $this->jsonGate();
        $in  = $this->jsonInput();
        $row = $this->rowFor((int) ($in['db'] ?? 0));
        [$svc] = $this->designerFor($row);
        try {
            $svc->create((string) ($in['table'] ?? ''), is_array($in['fields'] ?? null) ? $in['fields'] : []);
            echo json_encode(['success' => true, 'message' => 'Table “' . (string) ($in['table'] ?? '') . '” created.']);
        } catch (\Throwable $e) { $this->fail($e->getMessage()); }
        exit;
    }

    /** POST → alter an existing table (rename + column add/drop/modify via safe rebuild). */
    public function apiTableUpdate(): void
    {
        $this->jsonGate();
        $in  = $this->jsonInput();
        $row = $this->rowFor((int) ($in['db'] ?? 0));
        [$svc, $pdo, $dialect, $cfg] = $this->designerFor($row);
        $old = $this->tableIn($pdo, $dialect, $cfg, (string) ($in['old_table'] ?? ''));
        $new = trim((string) ($in['new_table'] ?? '')) !== '' ? (string) $in['new_table'] : $old;
        try {
            $svc->update($old, $new, is_array($in['fields'] ?? null) ? $in['fields'] : []);
            echo json_encode(['success' => true, 'message' => 'Table saved.']);
        } catch (\Throwable $e) { $this->fail($e->getMessage()); }
        exit;
    }

    /** POST → create or replace a view. */
    public function apiViewSave(): void
    {
        $this->jsonGate();
        $in  = $this->jsonInput();
        $row = $this->rowFor((int) ($in['db'] ?? 0));
        [$svc] = $this->designerFor($row);
        try {
            $svc->createView((string) ($in['name'] ?? ''), (string) ($in['select'] ?? ''), !empty($in['replace']));
            echo json_encode(['success' => true, 'message' => 'View “' . (string) ($in['name'] ?? '') . '” saved.']);
        } catch (\Throwable $e) { $this->fail($e->getMessage()); }
        exit;
    }

    /** POST → preview a view's SELECT (first rows) for the builder. */
    public function apiViewPreview(): void
    {
        $this->jsonGate();
        $in  = $this->jsonInput();
        $row = $this->rowFor((int) ($in['db'] ?? 0));
        [$svc] = $this->designerFor($row);
        try {
            echo json_encode(['success' => true, 'preview' => $svc->preview((string) ($in['select'] ?? ''), 10)]);
        } catch (\Throwable $e) { $this->fail($e->getMessage()); }
        exit;
    }

    /* ──────────────── scheduled maintenance / auto-snapshots ──────────────── */

    /** OWNER: current schedule settings + the token-guarded cron URL + last run. */
    public function apiMaintenance(): void
    {
        [, $isOwner] = $this->jsonGate();
        if (!$isOwner) $this->fail('Owner only.', 403);
        $svc = new VirtualDbService();
        $base = rtrim((string) $this->config->get('base_url'), '/');
        echo json_encode([
            'success'  => true,
            'settings' => $svc->maintenanceSettings(),
            'cron_url' => $base . '/database-manager/cron/maintenance?token=' . rawurlencode($svc->cronSecret()),
        ]);
        exit;
    }

    /** OWNER: save schedule settings (enabled / keep / encrypt / interval hours). */
    public function saveMaintenance(): void
    {
        [, $isOwner] = $this->jsonGate();
        if (!$isOwner) $this->fail('Owner only.', 403);
        $in  = $this->jsonInput();
        echo json_encode((new VirtualDbService())->setMaintenanceSettings($in));
        exit;
    }

    /** OWNER: run maintenance + snapshots for every pool database right now. */
    public function runMaintenance(): void
    {
        [, $isOwner] = $this->jsonGate();
        if (!$isOwner) $this->fail('Owner only.', 403);
        echo json_encode((new VirtualDbService())->runScheduledMaintenance('manual'));
        exit;
    }

    /** Token-guarded cron: maintain + snapshot every pool database (no session). */
    public function maintenanceCron(): void
    {
        header('Content-Type: application/json');
        $svc = new VirtualDbService();
        if (!hash_equals($svc->cronSecret(), (string) ($_GET['token'] ?? ''))) {
            http_response_code(403);
            echo json_encode(['success' => false, 'message' => 'Invalid cron token.']);
            exit;
        }
        echo json_encode($svc->runScheduledMaintenance('cron'));
        exit;
    }

    /** SQL console: run any script against the database, per-statement report. */
    public function apiSql(): void
    {
        [, $isOwner] = $this->jsonGate();
        $in  = $this->jsonInput();
        $row = $this->rowFor((int) ($in['db'] ?? 0));
        [$pdo, , ] = $this->open($row);
        $sql = (string) ($in['sql'] ?? '');
        if (trim($sql) === '') $this->fail('Type some SQL first.');
        $this->guardSqlIsolation($sql, $isOwner);
        $res = (new SqlScriptRunner($pdo))->execute($sql, [
            'stopOnError' => true,
            'transaction' => (bool) ($in['transaction'] ?? false),
            'dryRun'      => (bool) ($in['dryRun'] ?? false),
            'maxRows'     => 200,
        ]);
        echo json_encode(['success' => true, 'result' => $res]);
        exit;
    }

    /** POST → empty (truncate) a table — deletes all rows, keeps the structure. */
    public function apiTableEmpty(): void
    {
        $this->jsonGate();
        $in  = $this->jsonInput();
        $row = $this->rowFor((int) ($in['db'] ?? 0));
        $this->requireWrite($row);
        [$pdo, $dialect, $cfg] = $this->open($row);
        $table = $this->tableIn($pdo, $dialect, $cfg, (string) ($in['table'] ?? ''));
        try {
            $n = $pdo->exec('DELETE FROM ' . $dialect->quoteIdentifier($table));
            echo json_encode(['success' => true, 'message' => 'Emptied “' . $table . '” (' . (int) $n . ' rows removed).']);
        } catch (\Throwable $e) { $this->fail($e->getMessage()); }
        exit;
    }

    /**
     * POST multipart → import a .sql script (run it) or a .csv file (append rows to a
     * chosen table). Runs on the SIZE-CAPPED connection, so an import that would blow
     * the database past its limit is refused at the boundary.
     */
    public function apiImport(): void
    {
        [, $isOwner] = $this->jsonGate();
        if (!Security::CSRF()->Check((string) ($_POST['_token'] ?? ''))) $this->fail('Your session expired. Refresh and try again.', 419);
        $row = $this->rowFor((int) ($_POST['db'] ?? 0));
        $this->requireWrite($row);
        $f = $_FILES['file'] ?? null;
        if (!is_array($f) || ($f['error'] ?? \UPLOAD_ERR_NO_FILE) !== \UPLOAD_ERR_OK) $this->fail('No file uploaded.');
        if (($f['size'] ?? 0) > 64 * 1024 * 1024) $this->fail('Imports over 64 MB are not supported here.');
        $fname = strtolower((string) ($f['name'] ?? ''));
        [$pdo, $dialect, $cfg] = $this->open($row);

        // CSV → append rows to a table
        if (str_ends_with($fname, '.csv') || trim((string) ($_POST['table'] ?? '')) !== '') {
            $table = $this->tableIn($pdo, $dialect, $cfg, (string) ($_POST['table'] ?? ''));
            $cols  = array_column($this->columnsOf($pdo, $table), 'name');
            $fh = @fopen((string) $f['tmp_name'], 'r');
            if (!$fh) $this->fail('Could not read the CSV file.');
            $header = fgetcsv($fh);
            if (!is_array($header)) { fclose($fh); $this->fail('The CSV file is empty.'); }
            $header = array_map(static fn ($h) => trim((string) $h), $header);
            $use = array_values(array_filter($header, static fn ($h) => in_array($h, $cols, true)));
            if ($use === []) { fclose($fh); $this->fail('No CSV column matches this table. Header row must use the column names: ' . implode(', ', $cols)); }
            $idx = [];
            foreach ($header as $i => $h) { if (in_array($h, $use, true)) $idx[$h] = $i; }
            $ph = rtrim(str_repeat('?,', count($use)), ',');
            $sql = 'INSERT INTO ' . $dialect->quoteIdentifier($table) . ' ('
                 . implode(',', array_map(fn ($c) => $dialect->quoteIdentifier((string) $c), $use)) . ') VALUES (' . $ph . ')';
            $stmt = $pdo->prepare($sql);
            $inserted = 0; $failed = 0; $firstErr = '';
            $pdo->beginTransaction();
            try {
                while (($r = fgetcsv($fh)) !== false) {
                    if ($r === [null] || $r === false) continue;
                    $vals = [];
                    foreach ($use as $c) { $v = $r[$idx[$c]] ?? null; $vals[] = ($v === '' ? null : $v); }
                    try { $stmt->execute($vals); $inserted++; }
                    catch (\Throwable $e) { $failed++; if ($firstErr === '') $firstErr = $e->getMessage(); if (str_contains($e->getMessage(), 'disk is full')) { throw $e; } }
                }
                $pdo->commit();
            } catch (\Throwable $e) {
                if ($pdo->inTransaction()) $pdo->rollBack();
                fclose($fh);
                $this->fail('Import stopped — the database hit its size limit after ' . $inserted . ' rows. ' . $e->getMessage());
            }
            fclose($fh);
            echo json_encode(['success' => true, 'message' => "Imported {$inserted} row(s) into “{$table}”" . ($failed ? ", {$failed} skipped ({$firstErr})." : '.')]);
            exit;
        }

        // SQL → run the whole script in one transaction
        $sql = (string) file_get_contents((string) $f['tmp_name']);
        if (trim($sql) === '') $this->fail('The SQL file is empty.');
        $this->guardSqlIsolation($sql, $isOwner);
        try {
            $res = (new SqlScriptRunner($pdo))->execute($sql, ['stopOnError' => true, 'transaction' => true, 'maxRows' => 0]);
            echo json_encode(['success' => true, 'result' => $res, 'message' => 'SQL script imported.']);
        } catch (\Throwable $e) { $this->fail($e->getMessage()); }
        exit;
    }

    /** GET → download a SQL script of the database, optionally CONVERTED to another engine. */
    public function export(): void
    {
        Security::RequireAuth();
        $row = $this->rowFor((int) ($_GET['db'] ?? 0), asJson: false);
        $abs = VirtualDb::poolPath((string) $row['name']);
        $target = strtolower(trim((string) ($_GET['targetType'] ?? '')));
        $mode = in_array(($_GET['mode'] ?? 'both'), ['structure', 'data', 'both'], true) ? (string) $_GET['mode'] : 'both';
        try {
            $svc = new SqlScriptService(VirtualDb::poolDir(), ['type' => 'sqlite', 'path' => $abs]);
            $tmp = tempnam(sys_get_temp_dir(), 'ppvdbsql');
            $fh = fopen($tmp, 'w+b');
            try {
                $svc->generate(['scope' => 'all', 'mode' => $mode, 'targetType' => $target, 'extendedInsert' => true], $fh);
            } finally {
                fclose($fh);
            }
            $base = VirtualDb::safeName($row['label'] !== '' ? (string) $row['label'] : (string) $row['name']);
            $name = $base . ($target !== '' ? '-' . $target : '') . '.sql';
            header('Content-Type: application/sql; charset=utf-8');
            header('Content-Disposition: attachment; filename="' . $name . '"');
            header('Content-Length: ' . (string) filesize($tmp));
            header('Cache-Control: no-store');
            readfile($tmp);
            @unlink($tmp);
        } catch (\Throwable $e) {
            http_response_code(500);
            header('Content-Type: text/plain');
            echo 'Export failed: ' . $e->getMessage();
        }
        exit;
    }

    /* ───────────────────────── helpers ───────────────────────── */

    /** @return array{0:int,1:bool} [current user id, is owner] */
    private function jsonGate(): array
    {
        Security::RequireAuth();
        header('Content-Type: application/json');
        $u = Security::CurrentUserData();
        return [(int) ($u['id'] ?? 0), Security::IsOwner()];
    }

    private function jsonInput(): array
    {
        $in = json_decode((string) file_get_contents('php://input'), true) ?: $_POST;
        if (!Security::CSRF()->Check((string) ($in['_token'] ?? ''))) {
            $this->fail('Your session expired. Refresh and try again.', 419);
        }
        return is_array($in) ? $in : [];
    }

    private function fail(string $message, int $code = 400): never
    {
        http_response_code($code);
        echo json_encode(['success' => false, 'message' => $message]);
        exit;
    }

    /**
     * The databases the current user may see — owner: all; else created / assigned /
     * shared, each tagged with an `access` level. Share-aware (supersedes the old
     * assigned-only filter). $all is ignored; visibleTo() is authoritative.
     */
    private function scoped(array $all, bool $isOwner, int $uid): array
    {
        return (new VirtualDbService())->visibleTo($uid, $isOwner);
    }

    /** Load a pool row the CURRENT user may READ, or fail the request. */
    private function rowFor(int $id, bool $asJson = true): array
    {
        $u = Security::CurrentUserData();
        $svc = new VirtualDbService();
        $row = $svc->get($id);
        $ok = $row !== null && $svc->canRead($id, (int) ($u['id'] ?? 0), Security::IsOwner());
        if (!$ok) {
            http_response_code(404);
            if ($asJson) { header('Content-Type: application/json'); echo json_encode(['success' => false, 'message' => 'Database not found.']); }
            else { echo 'Database not found.'; }
            exit;
        }
        return $row;
    }

    /** Fail the request unless the current user may WRITE to (mutate) this database. */
    private function requireWrite(array $row): void
    {
        $u = Security::CurrentUserData();
        if (!(new VirtualDbService())->canWrite((int) $row['id'], (int) ($u['id'] ?? 0), Security::IsOwner())) {
            $this->fail('You have read-only access to this database.', 403);
        }
    }

    /**
     * Keep a non-owner's SQL INSIDE their own database. SQLite's ATTACH could open
     * ANOTHER user's .sqlite file by path, which would breach isolation — so it is
     * refused for non-owners. (The owner already sees every database, so it's moot
     * for them.) Extension loading is off by default in PDO, closing the other vector.
     */
    private function guardSqlIsolation(string $sql, bool $isOwner): void
    {
        if ($isOwner) { return; }
        if (preg_match('/(?:^|;)\s*ATTACH\b/i', $sql)) {
            $this->fail('ATTACH is not permitted here — you can only work inside this database.', 403);
        }
    }

    /** @return array{0:\PDO,1:object,2:array} connection + SQLite dialect + config */
    private function open(array $row): array
    {
        $abs = VirtualDb::poolPath((string) $row['name']);
        if (!is_file($abs)) $this->fail('The database file is missing on disk.', 410);
        $cfg = DatabaseConnection::normalize(['type' => 'sqlite', 'path' => $abs]);
        $pdo = DatabaseConnection::connect($cfg, VirtualDb::basePath());
        // Enforce the database's size cap for THIS connection — SQLite refuses any
        // write past the ceiling (reads still work), so the SQL console, imports and
        // row inserts are all bounded by the plan/override.
        VirtualDbSizePolicy::applyTo($pdo, VirtualDbSizePolicy::capBytesFor($row));
        // A read-only accessor (a database shared read-only) gets a query_only
        // connection: SQLite blocks EVERY write — DML and DDL — so all admin
        // endpoints are safe without per-endpoint gating.
        $u = Security::CurrentUserData();
        if (!(new VirtualDbService())->canWrite((int) $row['id'], (int) ($u['id'] ?? 0), Security::IsOwner())) {
            try { $pdo->exec('PRAGMA query_only = ON'); } catch (\Throwable $e) { /* best effort */ }
        }
        return [$pdo, DatabaseDialectRegistry::forConfig($cfg), $cfg];
    }

    /** Validate a table/view name against what actually exists (never trust input). */
    private function tableIn(\PDO $pdo, object $dialect, array $cfg, string $name): string
    {
        foreach ($dialect->listObjects($pdo, $cfg) as $o) {
            if ((string) $o['name'] === $name) return $name;
        }
        $this->fail('No such table in this database.', 404);
    }

    /** @return array<int,array{name:string,type:string,notnull:bool,pk:bool,dflt:mixed}> */
    private function columnsOf(\PDO $pdo, string $table): array
    {
        $st = $pdo->prepare('PRAGMA table_info("' . str_replace('"', '""', $table) . '")');
        $st->execute();
        $out = [];
        foreach ($st->fetchAll(\PDO::FETCH_ASSOC) as $c) {
            $out[] = [
                'name'    => (string) $c['name'],
                'type'    => (string) ($c['type'] ?? ''),
                'notnull' => (bool) ($c['notnull'] ?? false),
                'pk'      => (bool) ($c['pk'] ?? false),
                'dflt'    => $c['dflt_value'] ?? null,
            ];
        }
        return $out;
    }
}
