<!DOCTYPE html>
<?php
/* Virtual Database Engine console.
   Vars from DatabaseController::index(): $baseUrl, $assetsUrl, $user, $isOwner,
   $databases, $users, $quota, $targets */
$databases = is_array($databases ?? null) ? $databases : [];
$users     = is_array($users ?? null) ? $users : [];
$quota     = is_array($quota ?? null) ? $quota : ['default' => 3, 'mine' => -1, 'used' => 0, 'report' => []];
$targets   = is_array($targets ?? null) ? $targets : [];
$csrf      = \PpPhp\Core\Security::CSRF()->Token();
$u         = static fn (string $p): string => rtrim((string) $baseUrl, '/') . $p;
$totalBytes = array_sum(array_map(static fn ($d) => (int) ($d['bytes'] ?? 0), $databases));
$fmtBytes = static function (int $b): string {
    if ($b <= 0) return '0 B';
    $units = ['B', 'KB', 'MB', 'GB', 'TB']; $i = max(0, min((int) floor(log($b, 1024)), 4));
    return round($b / (1024 ** $i), $i ? 1 : 0) . ' ' . $units[$i];
};
?>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title><?= htmlspecialchars(\PpPhp\Core\Brand::name()) ?> · Virtual Databases</title>
    <?php include constant('BASE_PATH') . '/core/views/favicon.php'; ?>
    <link href="<?= $assetsUrl ?>/bootstrap/css/bootstrap.min.css" rel="stylesheet">
    <link href="<?= $assetsUrl ?>/icons/bootstrap-icons.css" rel="stylesheet">
    <?php include constant('BASE_PATH') . '/core/views/theme-head.php'; ?>
    <link href="<?= $assetsUrl ?>/sweetalert2/sweetalert2.min.css" rel="stylesheet">
    <script src="<?= $assetsUrl ?>/sweetalert2/sweetalert2.min.js"></script>
    <script src="<?= $assetsUrl ?>/bootstrap/js/bootstrap.bundle.min.js" defer></script>
    <style>
        body { background: var(--pp-app-bg, #f5f6fa); color: var(--pp-text, #172033); padding-top: 56px; }
        .vdb-top { background: var(--pp-navbar-bg, #1f2328); color: #f4f6fb; display: flex; align-items: center; gap: 1rem; padding: .65rem 1.1rem; }
        .vdb-top a { color: #cbd5e1; text-decoration: none; font-size: .88rem; } .vdb-top a:hover { color: #fff; }
        .vdb-top .brand { font-weight: 800; letter-spacing: -.02em; }
        .vdb-wrap { max-width: 1220px; margin: 1.4rem auto 3rem; padding: 0 1rem; }
        h1 { font-size: 1.35rem; font-weight: 800; margin: 0; letter-spacing: -.02em; display: flex; align-items: center; gap: .55rem; }
        h1 .ic { width: 40px; height: 40px; border-radius: 11px; display: grid; place-items: center; color: #fff; font-size: 1.15rem;
            background: linear-gradient(135deg, #7c3aed, #6d28d9); }
        .vdb-sub { color: var(--pp-text-muted, #64748b); font-size: .88rem; margin: .25rem 0 0; }
        .vdb-stats { display: flex; gap: .6rem; flex-wrap: wrap; margin: 1rem 0; }
        .vdb-stat { flex: 1 1 130px; background: var(--pp-surface, #fff); border: 1px solid var(--pp-border, #e6e9f0); border-radius: 12px; padding: .7rem 1rem; }
        .vdb-stat b { display: block; font-size: 1.25rem; font-weight: 800; } .vdb-stat span { font-size: .72rem; color: var(--pp-text-muted, #64748b); }
        .vdb-card { background: var(--pp-surface, #fff); border: 1px solid var(--pp-border, #e6e9f0); border-radius: 14px; padding: 1rem 1.1rem; margin-bottom: 1rem; }
        .vdb-card h6 { font-weight: 800; font-size: .95rem; display: flex; align-items: center; gap: .45rem; }
        .vdb-list { width: 100%; border-collapse: collapse; font-size: .88rem; }
        .vdb-list th { text-align: left; font-size: .7rem; text-transform: uppercase; letter-spacing: .05em; color: var(--pp-text-muted, #64748b);
            padding: .45rem .6rem; border-bottom: 1px solid var(--pp-border, #e6e9f0); }
        .vdb-list td { padding: .55rem .6rem; border-bottom: 1px solid var(--pp-border, #eef1f6); vertical-align: middle; }
        .vdb-list tr:last-child td { border-bottom: 0; }
        .vdb-name b { display: block; } .vdb-name code { font-size: .72rem; color: var(--pp-text-muted, #64748b); background: none; padding: 0; }
        .vdb-ok { color: #16a34a; } .vdb-bad { color: #dc2626; }
        .vdb-btn { border: 1px solid var(--pp-border, #dfe3ec); background: var(--pp-surface, #fff); color: var(--pp-text, #334155);
            border-radius: 8px; padding: .28rem .55rem; font-size: .78rem; cursor: pointer; }
        .vdb-btn:hover { border-color: #7c3aed; color: #7c3aed; }
        .vdb-btn.primary { background: #7c3aed; border-color: #7c3aed; color: #fff; font-weight: 700; }
        .vdb-btn.primary:hover { background: #6d28d9; color: #fff; }
        .vdb-btn.danger:hover { border-color: #dc2626; color: #dc2626; }
        .vdb-actions { display: flex; gap: .3rem; flex-wrap: nowrap; white-space: nowrap; }
        .vdb-quota-pill { font-size: .74rem; background: var(--pp-surface-muted, var(--pp-surface, #eef1f6)); border-radius: 999px; padding: .25rem .7rem; }
        .vdb-empty { text-align: center; color: var(--pp-text-muted, #64748b); padding: 2.2rem 1rem; }
        .vdb-empty i { font-size: 2rem; display: block; margin-bottom: .4rem; opacity: .5; }

        /* ── Admin (open database) ── */
        #vdbAdmin { display: none; }
        /* ── visual table / view builder modal ── */
        .vdb-modal-back { position: fixed; inset: 0; background: rgba(2,6,23,.55); backdrop-filter: blur(2px); z-index: 1080; display: none; align-items: flex-start; justify-content: center; overflow: auto; padding: 3vh 1rem; }
        .vdb-modal-back.show { display: flex; }
        .vdb-modal { background: var(--pp-surface, #fff); color: var(--pp-text, #0f172a); border: 1px solid var(--pp-border, #dfe3ec); border-radius: 14px; width: min(920px, 100%); box-shadow: 0 24px 60px rgba(2,6,23,.4); }
        .vdb-modal-head { display: flex; align-items: center; justify-content: space-between; gap: .5rem; padding: .8rem 1rem; border-bottom: 1px solid var(--pp-border, #dfe3ec); font-weight: 800; }
        .vdb-modal-head .x { background: none; border: 0; font-size: 1.1rem; color: var(--pp-text-muted, #64748b); cursor: pointer; }
        .vdb-modal-body { padding: 1rem; max-height: 68vh; overflow: auto; }
        .vdb-modal-foot { display: flex; align-items: center; justify-content: flex-end; gap: .5rem; padding: .8rem 1rem; border-top: 1px solid var(--pp-border, #dfe3ec); }
        .vdb-cols { width: 100%; border-collapse: collapse; font-size: .8rem; }
        .vdb-cols th { text-align: left; font-size: .68rem; text-transform: uppercase; letter-spacing: .04em; color: var(--pp-text-muted, #64748b); padding: .3rem .35rem; border-bottom: 1px solid var(--pp-border, #dfe3ec); }
        .vdb-cols td { padding: .25rem .35rem; vertical-align: middle; border-bottom: 1px solid var(--pp-border, #eef1f6); }
        .vdb-cols input[type=text], .vdb-cols input[type=number], .vdb-cols select { width: 100%; border: 1px solid var(--pp-border, #dfe3ec); border-radius: 7px; padding: .28rem .4rem; font-size: .8rem; background: var(--pp-surface, #fff); color: inherit; }
        .vdb-cols input[type=number] { width: 74px; }
        .vdb-cols td.mid { text-align: center; }
        .vdb-cols .drag { cursor: grab; color: var(--pp-text-muted, #94a3b8); }
        .vdb-cols tr.dragging { opacity: .45; }
        .vdb-name-in { font-weight: 600; }
        .vdb-admin-grid { display: grid; grid-template-columns: 250px 1fr; gap: 1rem; align-items: start; }
        @media (max-width: 900px) { .vdb-admin-grid { grid-template-columns: 1fr; } }
        .vdb-objects { max-height: 60vh; overflow-y: auto; }
        .vdb-obj { display: flex; justify-content: space-between; align-items: center; gap: .5rem; width: 100%;
            border: 0; background: none; text-align: left; padding: .45rem .6rem; border-radius: 8px; font-size: .85rem; cursor: pointer; color: inherit; }
        .vdb-obj:hover { background: var(--pp-surface-muted, var(--pp-surface, #f1f3f9)); }
        .vdb-obj.active { background: color-mix(in srgb, var(--pp-accent, #7c3aed) 9%, transparent); color: var(--pp-accent, #7c3aed); font-weight: 700; }
        .vdb-obj .cnt { font-size: .7rem; color: var(--pp-text-muted, #94a3b8); }
        .vdb-tabs { display: flex; gap: .3rem; margin-bottom: .8rem; }
        .vdb-tab { border: 1px solid var(--pp-border, #dfe3ec); background: none; border-radius: 999px; padding: .3rem .85rem; font-size: .8rem; font-weight: 600; cursor: pointer; color: inherit; }
        .vdb-tab.active { background: var(--pp-accent, #7c3aed); border-color: var(--pp-accent, #7c3aed); color: var(--pp-accent-contrast, #fff); }
        .vdb-grid-wrap { overflow-x: auto; }
        .vdb-grid { width: 100%; border-collapse: collapse; font-size: .82rem; white-space: nowrap; }
        .vdb-grid th { position: sticky; top: 0; background: var(--pp-surface-muted, var(--pp-surface, #f6f7fb)); color: var(--pp-text-muted, #64748b);
            padding: .4rem .55rem; text-align: left;
            font-size: .7rem; text-transform: uppercase; letter-spacing: .04em; cursor: pointer; user-select: none; border-bottom: 1px solid var(--pp-border, #e6e9f0); }
        .vdb-grid td { padding: .38rem .55rem; border-bottom: 1px solid var(--pp-border, #eef1f6); max-width: 340px; overflow: hidden; text-overflow: ellipsis; }
        .vdb-grid tr:hover td { background: color-mix(in srgb, var(--pp-accent, #7c3aed) 5%, transparent); }
        .vdb-null { color: var(--pp-text-muted, #94a3b8); font-style: italic; }
        .vdb-blob { color: #b45309; font-size: .72rem; }
        .vdb-pager { display: flex; align-items: center; gap: .6rem; margin-top: .6rem; font-size: .8rem; }
        .vdb-sql { width: 100%; min-height: 130px; font-family: ui-monospace, Menlo, monospace; font-size: .84rem; border: 1px solid var(--pp-border, #dfe3ec);
            border-radius: 10px; padding: .6rem .7rem; background: var(--pp-surface, #fff); color: inherit; }
        .vdb-report { font-size: .8rem; margin-top: .7rem; }
        .vdb-report .ok { color: #16a34a; } .vdb-report .err { color: #dc2626; }
        .vdb-report pre { background: var(--pp-surface-muted, var(--pp-surface, #f6f7fb)); border-radius: 8px; padding: .5rem .6rem; margin: .25rem 0 .6rem; white-space: pre-wrap; }
        .vdb-search { border: 1px solid var(--pp-border, #dfe3ec); border-radius: 8px; padding: .32rem .6rem; font-size: .82rem; background: var(--pp-surface, #fff); color: inherit; }
        .vdb-flex { display: flex; align-items: center; gap: .5rem; flex-wrap: wrap; }
        .vdb-note { font-size: .75rem; color: var(--pp-text-muted, #64748b); }
        select.vdb-search { padding-right: 1.4rem; }
    </style>
</head>
<body>
<?php $navBrandLabel = 'Virtual Databases'; $navBrandIcon = 'hdd-stack';
      $navBack = ['url' => $u('/projects'), 'label' => 'Home', 'icon' => 'house'];
      include constant('BASE_PATH') . '/core/views/console-navbar.php'; ?>

<div class="vdb-wrap">
    <div class="d-flex flex-wrap align-items-start justify-content-between gap-2">
        <div>
            <h1><span class="ic"><i class="bi bi-lightning-charge-fill"></i></span> Virtual Database Engine</h1>
            <p class="vdb-sub">Private databases that live inside <?= htmlspecialchars(\PpPhp\Core\Brand::name()) ?> — created in one click, no external server, fully managed here.</p>
        </div>
        <div class="vdb-flex">
            <?php if (!$isOwner): ?>
                <span class="vdb-quota-pill" data-tip="Your quota — how many virtual databases you may have. The owner can raise it."><i class="bi bi-speedometer2 me-1"></i>
                    <?= (int) $quota['used'] ?> of <?= ((int) $quota['mine'] < 0 ? '∞' : (int) $quota['mine']) ?> databases used</span>
            <?php endif; ?>
            <button class="vdb-btn" data-guide-open="vdbGuide" data-tip="Open the guide — what the Virtual Database Engine is and how to use it"><i class="bi bi-question-circle me-1"></i>Guide</button>
            <a class="vdb-btn" href="<?= htmlspecialchars($u('/database-manager/studio')) ?>" data-tip="Open Data Studio — the full phpMyAdmin-style workspace to manage tables, run SQL, import/export and more"><i class="bi bi-window-sidebar me-1"></i>Open Data Studio</a>
            <button class="vdb-btn primary" id="vdbCreate" data-tip="Create a private database instantly — no server, no credentials, ready in a second"><i class="bi bi-plus-lg me-1"></i>New database</button>
        </div>
    </div>

    <div class="vdb-stats">
        <div class="vdb-stat"><b id="vdbStatCount"><?= count($databases) ?></b><span>Virtual databases</span></div>
        <div class="vdb-stat"><b id="vdbStatSize"><?= htmlspecialchars($fmtBytes($totalBytes)) ?></b><span>Total size on disk</span></div>
        <?php if ($isOwner): ?>
            <div class="vdb-stat"><b><?= count(array_filter($databases, static fn ($d) => !empty($d['assigned_to']))) ?></b><span>Assigned to users</span></div>
            <div class="vdb-stat"><b><?= (int) $quota['default'] < 0 ? '∞' : (int) $quota['default'] ?></b><span>Default per-user quota</span></div>
        <?php endif; ?>
    </div>

    <!-- ── Pool ── -->
    <div class="vdb-card">
        <h6><i class="bi bi-hdd-stack"></i> Databases</h6>
        <div class="table-responsive">
            <table class="vdb-list" id="vdbList">
                <thead><tr>
                    <th>Database</th><th>Size</th><th>Tables</th><th>Health</th>
                    <?php if ($isOwner): ?><th>Assigned to</th><?php endif; ?>
                    <th style="width:1%">Actions</th>
                </tr></thead>
                <tbody></tbody>
            </table>
        </div>
        <div class="vdb-empty" id="vdbEmpty" style="display:none">
            <i class="bi bi-database-x"></i>
            No virtual databases yet. Click <b>New database</b> — it exists a second later, no server needed.
        </div>
    </div>

    <?php if ($isOwner): ?>
    <!-- ── Quotas (owner) ── -->
    <div class="vdb-card">
        <h6><i class="bi bi-sliders"></i> User quotas <span class="vdb-note fw-normal">— how many virtual databases each user may have. −1 = unlimited.</span></h6>
        <div class="vdb-flex mb-2">
            <label class="vdb-note" for="vdbDefQuota">Default for every user:</label>
            <input type="number" id="vdbDefQuota" class="vdb-search" style="width:90px" min="-1" value="<?= (int) $quota['default'] ?>">
            <button class="vdb-btn" id="vdbDefQuotaSave">Save default</button>
        </div>
        <div class="table-responsive">
            <table class="vdb-list" id="vdbQuotaTable">
                <thead><tr><th>User</th><th>Role</th><th>Used</th><th>Allowed</th><th>Override</th></tr></thead>
                <tbody></tbody>
            </table>
        </div>
    </div>

    <!-- ── Automatic maintenance & snapshots (owner) ── -->
    <div class="vdb-card">
        <h6><i class="bi bi-shield-lock"></i> Automatic maintenance &amp; snapshots <span class="vdb-note fw-normal">— keep every database fast, healthy and backed up on a schedule.</span></h6>
        <div class="vdb-flex mb-2" style="gap:1rem;flex-wrap:wrap">
            <label class="vdb-note"><input type="checkbox" id="vmEnabled"> Enabled</label>
            <label class="vdb-note">Every <input type="number" id="vmInterval" class="vdb-search" style="width:70px" min="1" value="24"> hours</label>
            <label class="vdb-note">Keep <input type="number" id="vmKeep" class="vdb-search" style="width:64px" min="1" max="50" value="7"> snapshots</label>
            <label class="vdb-note"><input type="checkbox" id="vmEncrypt"> <i class="bi bi-lock-fill"></i> Encrypt at rest</label>
            <button class="vdb-btn" id="vmSave">Save schedule</button>
            <button class="vdb-btn primary" id="vmRun"><i class="bi bi-play-fill"></i> Run now</button>
            <span class="vdb-note" id="vmLast"></span>
        </div>
        <div class="vdb-flex" style="gap:.4rem">
            <input type="text" id="vmCronUrl" class="vdb-search" style="flex:1;font-family:ui-monospace,monospace;font-size:.72rem" readonly data-tip="Point your host's cron (or a web-cron service) at this URL to run maintenance automatically">
            <button class="vdb-btn" id="vmCopy" data-tip="Copy the cron URL"><i class="bi bi-clipboard"></i></button>
        </div>
        <div class="vdb-note mt-1">Add to server cron, e.g. <code>0 3 * * * curl -s "&lt;the URL above&gt;"</code> — or the platform's web-cron. Snapshots roll over automatically; encrypted ones need the server key to restore.</div>
    </div>
    <?php endif; ?>

    <!-- ── Database admin ── -->
    <div class="vdb-card" id="vdbAdmin">
        <div class="d-flex flex-wrap align-items-center justify-content-between gap-2 mb-2">
            <h6 class="mb-0"><i class="bi bi-terminal"></i> <span id="vdbAdminTitle">Database</span>
                <span class="vdb-note" id="vdbAdminMeta"></span></h6>
            <button class="vdb-btn" id="vdbAdminClose"><i class="bi bi-x-lg"></i> Close</button>
        </div>
        <div class="vdb-admin-grid">
            <div>
                <div class="vdb-flex mb-2">
                    <input type="text" id="vdbObjFilter" class="vdb-search" placeholder="Filter tables…" style="flex:1">
                </div>
                <div class="vdb-objects" id="vdbObjects"></div>
                <div class="vdb-flex mt-2" style="gap:.4rem">
                    <button class="vdb-btn primary" id="vdbNewTableBtn" style="flex:1" data-tip="Build a new table visually — columns, types, keys, defaults (no SQL needed)"><i class="bi bi-plus-lg me-1"></i>New table</button>
                    <button class="vdb-btn" id="vdbNewViewBtn" data-tip="Create a view from a SELECT query"><i class="bi bi-eye me-1"></i>View</button>
                </div>
            </div>
            <div>
                <div class="vdb-tabs">
                    <button class="vdb-tab active" data-tab="browse">Browse</button>
                    <button class="vdb-tab" data-tab="structure">Structure</button>
                    <button class="vdb-tab" data-tab="sql">SQL</button>
                </div>

                <div data-pane="browse">
                    <div class="vdb-flex mb-2">
                        <input type="text" id="vdbRowSearch" class="vdb-search" placeholder="Search all columns…" style="flex:1;max-width:320px">
                        <button class="vdb-btn" id="vdbAddRow"><i class="bi bi-plus-lg"></i> Add row</button>
                        <button class="vdb-btn danger" id="vdbDropTable"><i class="bi bi-trash3"></i> Drop table</button>
                        <span class="vdb-note" id="vdbBrowseMeta"></span>
                    </div>
                    <div class="vdb-grid-wrap"><table class="vdb-grid" id="vdbRows"><thead></thead><tbody></tbody></table></div>
                    <div class="vdb-pager">
                        <button class="vdb-btn" id="vdbPrev">‹ Prev</button>
                        <span id="vdbPageInfo"></span>
                        <button class="vdb-btn" id="vdbNext">Next ›</button>
                        <span class="vdb-note" id="vdbEditableNote"></span>
                    </div>
                </div>

                <div data-pane="structure" style="display:none">
                    <div class="vdb-flex mb-2">
                        <button class="vdb-btn primary" id="vdbEditStruct" data-tip="Edit this table's structure — add, rename, retype, drop or reorder columns (data is preserved)"><i class="bi bi-pencil-square"></i> Edit structure</button>
                        <span class="vdb-note" id="vdbStructMeta"></span>
                    </div>
                    <div class="vdb-grid-wrap"><table class="vdb-grid" id="vdbStructure"><thead></thead><tbody></tbody></table></div>
                    <div class="mt-2 vdb-note" id="vdbStructureExtra"></div>
                </div>

                <div data-pane="sql" style="display:none">
                    <textarea class="vdb-sql" id="vdbSqlText" placeholder="SELECT * FROM …  — any SQL works: CREATE TABLE, INSERT, UPDATE, PRAGMA…"></textarea>
                    <div class="vdb-flex mt-2">
                        <button class="vdb-btn primary" id="vdbSqlRun"><i class="bi bi-play-fill"></i> Run</button>
                        <label class="vdb-note"><input type="checkbox" id="vdbSqlTx"> wrap in transaction</label>
                        <label class="vdb-note"><input type="checkbox" id="vdbSqlDry"> dry run (roll back)</label>
                    </div>
                    <div class="vdb-report" id="vdbSqlReport"></div>
                </div>
            </div>
        </div>
    </div>
</div>

<!-- Visual table builder modal -->
<div class="vdb-modal-back" id="vdbTblModal">
    <div class="vdb-modal">
        <div class="vdb-modal-head"><span id="vdbTblModalTitle"><i class="bi bi-table me-1"></i> New table</span><button class="x" id="vdbTblModalX">&times;</button></div>
        <div class="vdb-modal-body">
            <label style="display:block;font-size:.75rem;font-weight:700;margin-bottom:.15rem">Table name</label>
            <input type="text" id="vdbTblName" class="vdb-search" style="width:100%;margin-bottom:.8rem" placeholder="e.g. customers" autocomplete="off">
            <table class="vdb-cols">
                <thead><tr>
                    <th style="width:1%"></th><th>Column</th><th style="width:130px">Type</th><th style="width:84px">Length</th>
                    <th style="width:1%" class="mid" title="Nullable">Null</th><th style="width:1%" class="mid" title="Primary key">PK</th>
                    <th style="width:1%" class="mid" title="Auto-increment identity">AI</th><th style="width:150px">Default</th><th style="width:1%"></th>
                </tr></thead>
                <tbody id="vdbColRows"></tbody>
            </table>
            <button class="vdb-btn mt-2" id="vdbAddColBtn"><i class="bi bi-plus-lg"></i> Add column</button>
            <div class="vdb-note mt-2" id="vdbTblHint">Tip: mark one INTEGER column as <b>AI</b> for an auto-incrementing primary key. Reorder with the <i class="bi bi-grip-vertical"></i> handle.</div>
        </div>
        <div class="vdb-modal-foot">
            <span class="vdb-note me-auto" id="vdbTblErr" style="color:#dc2626"></span>
            <button class="vdb-btn" id="vdbTblCancel">Cancel</button>
            <button class="vdb-btn primary" id="vdbTblSave"><i class="bi bi-check-lg"></i> Save table</button>
        </div>
    </div>
</div>

<script>
(() => {
    'use strict';
    const BASE = <?= json_encode($u('')) ?>;
    const CSRF = <?= json_encode($csrf) ?>;
    const IS_OWNER = <?= $isOwner ? 'true' : 'false' ?>;
    const USERS = <?= json_encode(array_map(static fn ($x) => ['id' => (int) $x['id'], 'username' => (string) $x['username']], $users)) ?>;
    const MY_PREFIX = <?= json_encode(\PpPhp\Core\Databases\VirtualDb::userPrefix((string) ($user['username'] ?? ''))) ?>;
    const prefixOf = (u) => (String(u || '').toLowerCase().replace(/[^a-z0-9]/g, '').slice(0, 24)) || 'user';
    const slugPart = (s) => String(s || '').toLowerCase().replace(/[^a-z0-9]+/g, '_').replace(/^_+|_+$/g, '');
    const TARGETS = <?= json_encode($targets) ?>;
    let DBS = <?= json_encode($databases) ?>;
    let QUOTA = <?= json_encode($quota) ?>;

    const $ = (id) => document.getElementById(id);
    const esc = (s) => String(s ?? '').replace(/[&<>"']/g, (c) => ({'&':'&amp;','<':'&lt;','>':'&gt;','"':'&quot;',"'":'&#39;'}[c]));
    const post = async (path, body) => {
        const r = await fetch(BASE + path, { method: 'POST', credentials: 'same-origin',
            headers: { 'Content-Type': 'application/json' }, body: JSON.stringify({ ...body, _token: CSRF }) });
        return r.json();
    };
    const getJson = async (path) => (await fetch(BASE + path, { credentials: 'same-origin' })).json();
    const toast = (ok, msg) => Swal.fire({ toast: true, position: 'top-end', timer: 2300, showConfirmButton: false,
        icon: ok ? 'success' : 'error', title: msg });

    /* ───────── pool list ───────── */
    function renderList() {
        const tb = document.querySelector('#vdbList tbody');
        tb.innerHTML = '';
        $('vdbEmpty').style.display = DBS.length ? 'none' : '';
        $('vdbStatCount').textContent = DBS.length;
        let bytes = 0;
        DBS.forEach((d) => {
            bytes += d.bytes || 0;
            const tr = document.createElement('tr');
            const health = d.exists
                ? (d.ok ? '<span class="vdb-ok"><i class="bi bi-check-circle-fill"></i> ok</span>' : '<span class="vdb-bad"><i class="bi bi-exclamation-triangle-fill"></i> check</span>')
                : '<span class="vdb-bad">missing</span>';
            let assignCell = '';
            if (IS_OWNER) {
                const opts = ['<option value="0">— unassigned —</option>']
                    .concat(USERS.map((u) => `<option value="${u.id}" ${d.assigned_to === u.id ? 'selected' : ''}>${esc(u.username)}</option>`));
                assignCell = `<td><select class="vdb-search" data-assign="${d.id}">${opts.join('')}</select></td>`;
            }
            tr.innerHTML = `
                <td class="vdb-name"><b>${esc(d.label || d.name)}</b><code>${esc(d.name)}</code></td>
                <td>${esc(d.size)}</td>
                <td>${d.tables}</td>
                <td>${health}</td>
                ${assignCell}
                <td><div class="vdb-actions">
                    <button class="vdb-btn primary" data-open="${d.id}" data-tip="Open the built-in database admin — browse tables, edit rows, run SQL"><i class="bi bi-terminal"></i> Open</button>
                    <button class="vdb-btn" data-rename="${d.id}" data-tip="Rename this database (the file name stays stable)"><i class="bi bi-pencil"></i></button>
                    <a class="vdb-btn" href="${BASE}/database-manager/backup?db=${d.id}" data-tip="Download a consistent .sqlite backup — safe even while the database is in use"><i class="bi bi-download"></i></a>
                    <button class="vdb-btn" data-restore="${d.id}" data-tip="Replace this database's contents from a .sqlite backup file"><i class="bi bi-upload"></i></button>
                    <button class="vdb-btn" data-export="${d.id}" data-tip="Export a SQL script — native SQLite, or CONVERTED for MySQL, PostgreSQL, SQL Server, Oracle…"><i class="bi bi-filetype-sql"></i></button>
                    <button class="vdb-btn" data-snaps="${d.id}" data-tip="Snapshots — take rotating point-in-time backups and roll back to any of them"><i class="bi bi-clock-history"></i></button>
                    <button class="vdb-btn" data-maintain="${d.id}" data-tip="Maintain — integrity probe, refresh the query planner, checkpoint the WAL (keeps it fast & healthy)"><i class="bi bi-heart-pulse"></i></button>
                    <button class="vdb-btn" data-clone="${d.id}" data-tip="Clone — fork this database (structure + data) into a brand-new virtual database"><i class="bi bi-files"></i></button>
                    <button class="vdb-btn" data-vacuum="${d.id}" data-tip="VACUUM — reclaim free space and defragment the file"><i class="bi bi-magic"></i></button>
                    <button class="vdb-btn" data-integrity="${d.id}" data-tip="Run SQLite's full integrity check on this database"><i class="bi bi-shield-check"></i></button>
                    <button class="vdb-btn danger" data-drop="${d.id}" data-tip="Delete this database and all its data permanently"><i class="bi bi-trash3"></i></button>
                </div></td>`;
            tb.appendChild(tr);
        });
        const units = ['B','KB','MB','GB','TB']; let i = 0, n = bytes;
        while (n >= 1024 && i < 4) { n /= 1024; i++; }
        $('vdbStatSize').textContent = (i ? n.toFixed(1) : n) + ' ' + units[i];
    }

    async function refresh() {
        const d = await getJson('/database-manager/api/databases');
        if (d.success) { DBS = d.databases; renderList(); }
    }

    async function openSnapshots(db) {
        const load = async () => (await getJson('/database-manager/api/snapshots?db=' + db.id)).snapshots || [];
        const rowHtml = (s) => `
            <div style="display:flex;align-items:center;justify-content:space-between;gap:.6rem;padding:.45rem .1rem;border-bottom:1px solid var(--pp-border,#e6e9f0)">
                <div style="text-align:left;min-width:0">
                    <div style="font-weight:650">${esc(s.when)}</div>
                    <div class="vdb-note">${esc(s.size)}${s.encrypted ? ' · <i class="bi bi-lock-fill"></i> encrypted at rest' : ''}</div>
                </div>
                <button class="vdb-btn" data-snap="${esc(s.name)}" style="flex:0 0 auto"><i class="bi bi-arrow-counterclockwise"></i> Restore</button>
            </div>`;
        const render = (snaps) => snaps.length
            ? `<div style="max-height:46vh;overflow:auto">${snaps.map(rowHtml).join('')}</div>`
            : '<div class="vdb-note" style="padding:.7rem 0">No snapshots yet — take one below. Snapshots are consistent point-in-time copies you can roll back to; the newest 7 are kept.</div>';
        await Swal.fire({
            title: 'Snapshots · ' + esc(db.label || db.name),
            html: render(await load()),
            width: 560, showConfirmButton: false, showCloseButton: true,
            footer: '<div style="display:flex;gap:.5rem;flex-wrap:wrap;justify-content:center">'
                + '<button id="vdbSnapNow" class="swal2-confirm swal2-styled" style="margin:0">Snapshot now</button>'
                + '<button id="vdbSnapEnc" class="swal2-deny swal2-styled" style="margin:0"><i class="bi bi-lock-fill"></i> Encrypted snapshot</button>'
                + '</div>',
            didOpen: () => {
                const box = Swal.getHtmlContainer();
                const bind = () => box.querySelectorAll('[data-snap]').forEach((b) => b.addEventListener('click', async () => {
                    const c = await Swal.fire({ title: 'Roll back to this snapshot?', text: 'The live database will be replaced with this snapshot (integrity-checked first).', icon: 'warning', showCancelButton: true, confirmButtonColor: '#dc2626', confirmButtonText: 'Restore' });
                    if (!c.isConfirmed) return;
                    const out = await post('/database-manager/snapshot-restore', { id: db.id, snapshot: b.dataset.snap });
                    toast(out.success, out.message); if (out.success) refresh();
                }));
                const rebind = async () => { box.innerHTML = render(await load()); bind(); };
                bind();
                document.getElementById('vdbSnapNow')?.addEventListener('click', async () => { const o = await post('/database-manager/snapshot', { id: db.id, keep: 7, encrypt: false }); toast(o.success, o.message); rebind(); });
                document.getElementById('vdbSnapEnc')?.addEventListener('click', async () => { const o = await post('/database-manager/snapshot', { id: db.id, keep: 7, encrypt: true }); toast(o.success, o.message); rebind(); });
            }
        });
    }

    $('vdbCreate').addEventListener('click', async () => {
        const ownerSel = (IS_OWNER && USERS.length)
            ? `<div style="text-align:left;margin-bottom:.5rem"><label style="font-size:.72rem;font-weight:700;color:#94a3b8">Belongs to</label>
                 <select id="swAssign" class="swal2-select" style="margin:.2rem 0;width:100%">
                   <option value="0">— me (${esc(MY_PREFIX)}) —</option>
                   ${USERS.map((u) => `<option value="${u.id}" data-u="${esc(u.username)}">${esc(u.username)}</option>`).join('')}
                 </select></div>` : '';
        const res = await Swal.fire({
            title: 'New virtual database', width: 480,
            html: `${ownerSel}
                <div style="text-align:left"><label style="font-size:.72rem;font-weight:700;color:#94a3b8">Database name</label>
                <div style="display:flex;align-items:stretch;margin-top:.2rem">
                  <span id="swPrefix" style="display:inline-flex;align-items:center;padding:0 .6rem;background:var(--pp-surface-muted,#f1f5f9);border:1px solid var(--pp-border,#dfe3ec);border-right:0;border-radius:8px 0 0 8px;font-family:ui-monospace,monospace;font-size:.85rem;color:#64748b">${esc(MY_PREFIX)}_</span>
                  <input id="swLabel" class="swal2-input" placeholder="cms" maxlength="160" style="margin:0;border-radius:0 8px 8px 0;flex:1" autocomplete="off">
                </div>
                <div id="swFull" style="font-family:ui-monospace,monospace;font-size:.78rem;color:var(--pp-accent,#4f46e5);margin-top:.4rem"></div>
                <div style="font-size:.72rem;color:#94a3b8;margin-top:.2rem">cPanel-style — each database is filed under its owner's name for easy tracking and stays private to them.</div></div>`,
            showCancelButton: true, confirmButtonText: 'Create',
            didOpen: () => {
                const nameEl = document.getElementById('swLabel'), ownerEl = document.getElementById('swAssign'),
                      prefEl = document.getElementById('swPrefix'), fullEl = document.getElementById('swFull');
                const curPrefix = () => {
                    if (!ownerEl || ownerEl.value === '0') return MY_PREFIX;
                    const opt = ownerEl.selectedOptions[0]; return prefixOf(opt ? opt.getAttribute('data-u') : '');
                };
                const sync = () => { const p = curPrefix(); prefEl.textContent = p + '_'; fullEl.textContent = 'Will be created as:  ' + p + '_' + (slugPart(nameEl.value) || 'db'); };
                nameEl.addEventListener('input', sync); if (ownerEl) ownerEl.addEventListener('change', sync);
                sync(); nameEl.focus();
            },
            preConfirm: () => ({ label: document.getElementById('swLabel').value.trim(),
                assigned_to: IS_OWNER ? parseInt(document.getElementById('swAssign')?.value || '0', 10) : 0 })
        });
        if (!res.isConfirmed || !res.value.label) return;
        const out = await post('/database-manager/create', res.value);
        toast(out.success, out.message || 'Done');
        if (out.success) refresh();
    });

    document.addEventListener('click', async (e) => {
        const btn = e.target.closest('[data-open],[data-rename],[data-vacuum],[data-integrity],[data-drop],[data-restore],[data-export],[data-clone],[data-maintain],[data-snaps]');
        if (!btn) return;
        const id = parseInt(btn.dataset.open || btn.dataset.rename || btn.dataset.vacuum || btn.dataset.integrity || btn.dataset.drop || btn.dataset.restore || btn.dataset.export || btn.dataset.clone || btn.dataset.maintain || btn.dataset.snaps, 10);
        const db = DBS.find((x) => x.id === id);
        if (!db) return;

        if (btn.dataset.open) { openAdmin(db); return; }
        if (btn.dataset.rename) {
            const r = await Swal.fire({ title: 'Rename database', input: 'text', inputValue: db.label || '', showCancelButton: true });
            if (!r.isConfirmed) return;
            const out = await post('/database-manager/rename', { id, label: r.value });
            toast(out.success, out.message); refresh(); return;
        }
        if (btn.dataset.vacuum) { const out = await post('/database-manager/vacuum', { id }); toast(out.success, out.message); refresh(); return; }
        if (btn.dataset.integrity) { const out = await post('/database-manager/integrity', { id }); Swal.fire(out.success ? 'Healthy' : 'Problems found', out.message, out.success ? 'success' : 'warning'); return; }
        if (btn.dataset.maintain) { const out = await post('/database-manager/maintain', { id }); toast(out.success, out.message); refresh(); return; }
        if (btn.dataset.clone) {
            const r = await Swal.fire({ title: 'Clone database', input: 'text', inputPlaceholder: 'New database name (optional)',
                inputValue: (db.label || db.name) + ' (copy)', showCancelButton: true, confirmButtonText: 'Clone',
                text: 'Creates a brand-new virtual database with a full copy of this one’s structure and data.' });
            if (!r.isConfirmed) return;
            const out = await post('/database-manager/duplicate', { id, label: (r.value || '').trim() });
            toast(out.success, out.message); if (out.success) refresh(); return;
        }
        if (btn.dataset.snaps) { await openSnapshots(db); return; }
        if (btn.dataset.drop) {
            const r = await Swal.fire({ title: 'Delete this database?', text: `"${db.label || db.name}" and ALL its data will be permanently removed. Download a backup first if you need it.`,
                icon: 'warning', showCancelButton: true, confirmButtonColor: '#dc2626', confirmButtonText: 'Delete forever' });
            if (!r.isConfirmed) return;
            const out = await post('/database-manager/drop', { id });
            toast(out.success, out.message); refresh(); return;
        }
        if (btn.dataset.restore) {
            const r = await Swal.fire({ title: 'Restore from backup', html: '<input type="file" id="swFile" class="form-control" accept=".sqlite,.db,.sqlite3">',
                text: 'The current contents will be replaced.', showCancelButton: true, confirmButtonText: 'Restore',
                preConfirm: () => document.getElementById('swFile').files[0] || Swal.showValidationMessage('Choose a .sqlite backup file') });
            if (!r.isConfirmed || !r.value) return;
            const fd = new FormData();
            fd.append('id', String(id)); fd.append('file', r.value); fd.append('_token', CSRF);
            const out = await (await fetch(BASE + '/database-manager/restore', { method: 'POST', credentials: 'same-origin', body: fd })).json();
            toast(out.success, out.message); refresh(); return;
        }
        if (btn.dataset.export) {
            const opts = ['<option value="">SQLite (native)</option>'].concat(
                TARGETS.filter((t) => t.id !== 'sqlite').map((t) => `<option value="${esc(t.id)}">${esc(t.label)} (converted)</option>`));
            const r = await Swal.fire({ title: 'Export SQL script',
                html: `<div style="text-align:left;font-size:.9rem">
                        <label class="d-block mb-1">Write the script for:</label>
                        <select id="swTarget" class="swal2-select" style="margin:0 0 10px">${opts.join('')}</select>
                        <label class="d-block mb-1">Contents:</label>
                        <select id="swMode" class="swal2-select" style="margin:0">
                            <option value="both">Structure and data</option><option value="structure">Structure only</option><option value="data">Data only</option>
                        </select></div>`,
                showCancelButton: true, confirmButtonText: 'Download',
                preConfirm: () => ({ target: document.getElementById('swTarget').value, mode: document.getElementById('swMode').value }) });
            if (!r.isConfirmed) return;
            location.href = BASE + '/database-manager/export?db=' + id + '&targetType=' + encodeURIComponent(r.value.target) + '&mode=' + encodeURIComponent(r.value.mode);
            return;
        }
    });

    document.addEventListener('change', async (e) => {
        const sel = e.target.closest('[data-assign]');
        if (!sel) return;
        const out = await post('/database-manager/assign', { id: parseInt(sel.dataset.assign, 10), user_id: parseInt(sel.value, 10) });
        toast(out.success, out.message);
        refresh();
        if (IS_OWNER) loadQuotas();
    });

    /* ───────── quotas (owner) ───────── */
    function renderQuotas() {
        const tb = document.querySelector('#vdbQuotaTable tbody');
        if (!tb) return;
        tb.innerHTML = '';
        (QUOTA.report || []).forEach((r) => {
            const tr = document.createElement('tr');
            tr.innerHTML = `
                <td>${esc(r.username)}</td><td>${esc(r.role)}</td><td>${r.used}</td>
                <td>${r.effective < 0 ? '∞' : r.effective}${r.override === null ? ' <span class="vdb-note">(default)</span>' : ''}</td>
                <td class="vdb-flex">
                    <input type="number" class="vdb-search" style="width:80px" min="-1" placeholder="${QUOTA.default}" value="${r.override === null ? '' : r.override}" data-quota-user="${r.id}">
                    <button class="vdb-btn" data-quota-save="${r.id}">Set</button>
                    <button class="vdb-btn" data-quota-reset="${r.id}" title="Use the default">Reset</button>
                </td>`;
            tb.appendChild(tr);
        });
    }
    async function loadQuotas() {
        const d = await getJson('/database-manager/api/quotas');
        if (d.success) { QUOTA.default = d.default; QUOTA.report = d.report; renderQuotas(); const el = $('vdbDefQuota'); if (el) el.value = d.default; }
    }
    $('vdbDefQuotaSave')?.addEventListener('click', async () => {
        const out = await post('/database-manager/quota', { default: parseInt($('vdbDefQuota').value, 10) });
        toast(out.success, out.message); loadQuotas();
    });
    document.addEventListener('click', async (e) => {
        const save = e.target.closest('[data-quota-save]');
        const reset = e.target.closest('[data-quota-reset]');
        if (save) {
            const uid = save.dataset.quotaSave;
            const val = document.querySelector(`[data-quota-user="${uid}"]`).value;
            const out = await post('/database-manager/quota', { user_id: parseInt(uid, 10), max: val === '' ? null : parseInt(val, 10) });
            toast(out.success, out.message); loadQuotas();
        } else if (reset) {
            const out = await post('/database-manager/quota', { user_id: parseInt(reset.dataset.quotaReset, 10), max: null });
            toast(out.success, out.message); loadQuotas();
        }
    });

    /* ───────── automatic maintenance (owner) ───────── */
    async function loadMaintenance() {
        const d = await getJson('/database-manager/api/maintenance');
        if (!d.success) return;
        const s = d.settings;
        $('vmEnabled').checked = !!s.enabled;
        $('vmInterval').value = s.interval_hours;
        $('vmKeep').value = s.keep;
        $('vmEncrypt').checked = !!s.encrypt;
        $('vmCronUrl').value = d.cron_url;
        $('vmLast').textContent = 'Last run: ' + s.last_run_h + (s.last_result ? ' · ' + s.last_result : '');
    }
    $('vmSave')?.addEventListener('click', async () => {
        const out = await post('/database-manager/maintenance/save', {
            enabled: $('vmEnabled').checked, interval_hours: parseInt($('vmInterval').value, 10) || 24,
            keep: parseInt($('vmKeep').value, 10) || 7, encrypt: $('vmEncrypt').checked,
        });
        toast(out.success, out.message); loadMaintenance();
    });
    $('vmRun')?.addEventListener('click', async () => {
        const btn = $('vmRun'); btn.disabled = true; btn.innerHTML = '<i class="bi bi-hourglass-split"></i> Running…';
        const out = await post('/database-manager/maintenance/run', {});
        btn.disabled = false; btn.innerHTML = '<i class="bi bi-play-fill"></i> Run now';
        toast(out.success, out.message); loadMaintenance(); refresh();
    });
    $('vmCopy')?.addEventListener('click', () => { navigator.clipboard?.writeText($('vmCronUrl').value); toast(true, 'Cron URL copied.'); });

    /* ───────── database admin ───────── */
    let A = { db: null, objects: [], table: '', page: 1, per: 25, q: '', sort: '', dir: 'asc', editable: true, columns: [] };

    async function openAdmin(db) {
        A = { ...A, db, table: '', page: 1, q: '', sort: '', dir: 'asc' };
        $('vdbAdmin').style.display = 'block';   // the stylesheet hides it by default
        $('vdbAdminTitle').textContent = db.label || db.name;
        $('vdbAdminMeta').textContent = '';
        await loadObjects();
        $('vdbAdmin').scrollIntoView({ behavior: 'smooth', block: 'start' });
    }
    $('vdbAdminClose').addEventListener('click', () => { $('vdbAdmin').style.display = 'none'; A.db = null; });

    async function loadObjects() {
        const d = await getJson('/database-manager/api/tables?db=' + A.db.id);
        if (!d.success) { toast(false, d.message || 'Could not read the database'); return; }
        A.objects = d.objects;
        $('vdbAdminMeta').textContent = ` — ${d.stats.tables} tables · ${d.stats.size}`;
        renderObjects();
        if (!A.table && A.objects.length) selectTable(A.objects[0].name);
        else if (A.table) selectTable(A.table);
        else { document.querySelector('#vdbRows thead').innerHTML = ''; document.querySelector('#vdbRows tbody').innerHTML = ''; }
    }
    function renderObjects() {
        const box = $('vdbObjects');
        const f = ($('vdbObjFilter').value || '').toLowerCase();
        box.innerHTML = '';
        A.objects.filter((o) => !f || o.name.toLowerCase().includes(f)).forEach((o) => {
            const b = document.createElement('button');
            b.className = 'vdb-obj' + (o.name === A.table ? ' active' : '');
            b.innerHTML = `<span><i class="bi bi-${o.type === 'VIEW' ? 'eye' : 'table'} me-1"></i>${esc(o.name)}</span><span class="cnt">${o.type === 'VIEW' ? 'view' : o.rows}</span>`;
            b.addEventListener('click', () => selectTable(o.name));
            box.appendChild(b);
        });
    }
    $('vdbObjFilter').addEventListener('input', renderObjects);

    function selectTable(name) {
        A.table = name; A.page = 1; A.q = ''; A.sort = ''; A.dir = 'asc';
        $('vdbRowSearch').value = '';
        renderObjects();
        loadRows();
        loadStructure();
    }

    async function loadRows() {
        if (!A.db || !A.table) return;
        const p = new URLSearchParams({ db: A.db.id, table: A.table, page: A.page, per: A.per, q: A.q, sort: A.sort, dir: A.dir });
        const d = await getJson('/database-manager/api/rows?' + p.toString());
        if (!d.success) { toast(false, d.message || 'Could not load rows'); return; }
        A.editable = d.editable; A.columns = d.columns;
        const thead = document.querySelector('#vdbRows thead');
        const tbody = document.querySelector('#vdbRows tbody');
        thead.innerHTML = '<tr>' + d.columns.map((c) =>
            `<th data-sort="${esc(c.name)}">${esc(c.name)}${c.pk ? ' <i class="bi bi-key-fill" style="color:#b45309"></i>' : ''}${A.sort === c.name ? (A.dir === 'asc' ? ' ▲' : ' ▼') : ''}</th>`).join('')
            + (d.editable ? '<th></th>' : '') + '</tr>';
        tbody.innerHTML = '';
        d.rows.forEach((r) => {
            const tr = document.createElement('tr');
            let cells = '';
            d.columns.forEach((c) => {
                const v = r[c.name];
                if (v === null || v === undefined) cells += '<td><span class="vdb-null">NULL</span></td>';
                else if (typeof v === 'object' && v.__blob) cells += `<td><span class="vdb-blob">BLOB ${v.bytes} B · ${esc(v.hex)}…</span></td>`;
                else cells += `<td title="${esc(v)}">${esc(String(v).length > 120 ? String(v).slice(0, 120) + '…' : v)}</td>`;
            });
            if (d.editable) {
                cells += `<td class="vdb-actions">
                    <button class="vdb-btn" data-edit-rid="${r.__rid}" title="Edit"><i class="bi bi-pencil"></i></button>
                    <button class="vdb-btn danger" data-del-rid="${r.__rid}" title="Delete"><i class="bi bi-trash3"></i></button></td>`;
            }
            tr.innerHTML = cells;
            tr.dataset.row = JSON.stringify(r);
            tbody.appendChild(tr);
        });
        const pages = Math.max(1, Math.ceil(d.total / A.per));
        $('vdbPageInfo').textContent = `Page ${A.page} / ${pages} · ${d.total} rows`;
        $('vdbPrev').disabled = A.page <= 1;
        $('vdbNext').disabled = A.page >= pages;
        $('vdbBrowseMeta').textContent = A.table;
        $('vdbEditableNote').textContent = d.editable ? '' : 'read-only (WITHOUT ROWID or view)';
    }

    document.querySelector('#vdbRows thead').addEventListener('click', (e) => {
        const th = e.target.closest('[data-sort]');
        if (!th) return;
        const col = th.dataset.sort;
        if (A.sort === col) A.dir = A.dir === 'asc' ? 'desc' : 'asc';
        else { A.sort = col; A.dir = 'asc'; }
        loadRows();
    });
    $('vdbPrev').addEventListener('click', () => { if (A.page > 1) { A.page--; loadRows(); } });
    $('vdbNext').addEventListener('click', () => { A.page++; loadRows(); });
    let searchTimer = null;
    $('vdbRowSearch').addEventListener('input', () => {
        clearTimeout(searchTimer);
        searchTimer = setTimeout(() => { A.q = $('vdbRowSearch').value.trim(); A.page = 1; loadRows(); }, 320);
    });

    /* row editor */
    async function rowEditor(existing) {
        const fields = A.columns.map((c) => {
            const raw = existing ? existing[c.name] : null;
            const isBlob = raw && typeof raw === 'object' && raw.__blob;
            const val = raw === null || raw === undefined || isBlob ? '' : String(raw);
            return `<div style="text-align:left;margin-bottom:.5rem">
                <label style="font-size:.75rem;font-weight:700">${esc(c.name)} <span style="color:#94a3b8">${esc(c.type)}${c.pk ? ' · PK' : ''}${c.notnull ? ' · NOT NULL' : ''}</span></label>
                ${isBlob ? `<div class="vdb-blob">BLOB kept as-is (edit via SQL)</div>`
                    : `<textarea data-field="${esc(c.name)}" class="form-control" rows="1" style="font-size:.85rem" ${existing === null && c.pk ? 'placeholder="auto"' : ''}>${esc(val)}</textarea>
                       <label style="font-size:.7rem;color:#94a3b8"><input type="checkbox" data-null="${esc(c.name)}" ${raw === null && existing ? 'checked' : ''}> NULL</label>`}
            </div>`;
        }).join('');
        const r = await Swal.fire({
            title: existing ? 'Edit row' : 'Add row', html: `<div style="max-height:55vh;overflow:auto">${fields}</div>`,
            width: 640, showCancelButton: true, confirmButtonText: existing ? 'Save' : 'Insert',
            preConfirm: () => {
                const data = {};
                A.columns.forEach((c) => {
                    const t = document.querySelector(`[data-field="${CSS.escape(c.name)}"]`);
                    const n = document.querySelector(`[data-null="${CSS.escape(c.name)}"]`);
                    if (!t) return;                        // blob column — untouched
                    if (n && n.checked) { data[c.name] = null; return; }
                    if (!existing && c.pk && t.value === '') return;   // let autoincrement fill it
                    data[c.name] = t.value;
                });
                return data;
            }
        });
        return r.isConfirmed ? r.value : null;
    }
    $('vdbAddRow').addEventListener('click', async () => {
        if (!A.editable) { toast(false, 'This table is read-only here.'); return; }
        const data = await rowEditor(null);
        if (!data) return;
        const out = await post('/database-manager/api/row-save', { db: A.db.id, table: A.table, rid: null, data });
        toast(out.success, out.message);
        if (out.success) { loadRows(); loadObjects(); }
    });
    document.querySelector('#vdbRows tbody').addEventListener('click', async (e) => {
        const edit = e.target.closest('[data-edit-rid]');
        const del = e.target.closest('[data-del-rid]');
        if (edit) {
            const row = JSON.parse(edit.closest('tr').dataset.row);
            const data = await rowEditor(row);
            if (!data) return;
            const out = await post('/database-manager/api/row-save', { db: A.db.id, table: A.table, rid: edit.dataset.editRid, data });
            toast(out.success, out.message);
            if (out.success) loadRows();
        } else if (del) {
            const r = await Swal.fire({ title: 'Delete this row?', icon: 'warning', showCancelButton: true, confirmButtonColor: '#dc2626' });
            if (!r.isConfirmed) return;
            const out = await post('/database-manager/api/row-delete', { db: A.db.id, table: A.table, rid: del.dataset.delRid });
            toast(out.success, out.message);
            loadRows(); loadObjects();
        }
    });
    $('vdbDropTable').addEventListener('click', async () => {
        if (!A.table) return;
        const obj = A.objects.find((o) => o.name === A.table);
        const r = await Swal.fire({ title: `Drop ${obj?.type === 'VIEW' ? 'view' : 'table'} "${A.table}"?`,
            text: 'This permanently removes it and all its data.', icon: 'warning', showCancelButton: true, confirmButtonColor: '#dc2626', confirmButtonText: 'Drop' });
        if (!r.isConfirmed) return;
        const out = await post('/database-manager/api/table-drop', { db: A.db.id, table: A.table, type: obj?.type || 'TABLE' });
        toast(out.success, out.message);
        A.table = '';
        loadObjects();
    });

    /* structure */
    async function loadStructure() {
        if (!A.db || !A.table) return;
        const d = await getJson('/database-manager/api/structure?db=' + A.db.id + '&table=' + encodeURIComponent(A.table));
        if (!d.success) return;
        const cols = (d.details && d.details.columns) || [];
        const thead = document.querySelector('#vdbStructure thead');
        const tbody = document.querySelector('#vdbStructure tbody');
        thead.innerHTML = '<tr><th>Column</th><th>Type</th><th>Null</th><th>Default</th><th>Key</th></tr>';
        tbody.innerHTML = cols.map((c) => `<tr>
            <td><b>${esc(c.name)}</b></td>
            <td>${esc(c.type)}${c.size ? '(' + c.size + (c.scale ? ',' + c.scale : '') + ')' : ''}</td>
            <td>${c.nullable ? 'YES' : 'NO'}</td>
            <td>${c.default === null || c.default === undefined ? '<span class="vdb-null">—</span>' : esc(c.default)}</td>
            <td>${c.primary_key ? '<i class="bi bi-key-fill" style="color:#b45309"></i> PK' : ''}${c.auto_increment ? ' AI' : ''}</td>
        </tr>`).join('');
        const idx = (d.details && d.details.indexes) || [];
        const fks = (d.details && (d.details.foreign_keys || d.details.foreignKeys)) || [];
        $('vdbStructureExtra').innerHTML =
            (idx.length ? '<b>Indexes:</b> ' + idx.map((i) => esc(i.name || JSON.stringify(i))).join(', ') + '<br>' : '')
            + (fks.length ? '<b>Foreign keys:</b> ' + fks.map((f) => esc(JSON.stringify(f))).join('<br>') : '');
    }

    /* SQL console */
    $('vdbSqlRun').addEventListener('click', async () => {
        const out = await post('/database-manager/api/sql', {
            db: A.db.id, sql: $('vdbSqlText').value,
            transaction: $('vdbSqlTx').checked, dryRun: $('vdbSqlDry').checked,
        });
        const box = $('vdbSqlReport');
        if (!out.success) { box.innerHTML = `<div class="err">${esc(out.message)}</div>`; return; }
        const rep = out.result.statements || out.result.report || out.result;
        let html = '';
        (Array.isArray(rep) ? rep : []).forEach((r) => {
            html += `<div class="${r.status === 'ok' ? 'ok' : 'err'}"><i class="bi bi-${r.status === 'ok' ? 'check-circle' : 'x-circle'}"></i>
                #${r.n ?? ''} ${esc(r.status)} ${r.kind ? '· ' + esc(r.kind) : ''} ${r.affected ? '· ' + r.affected + ' affected' : ''} ${r.ms ? '· ' + r.ms + ' ms' : ''} ${r.message ? '— ' + esc(r.message) : ''}</div>`;
            if (r.columns && r.rows) {
                html += '<pre>' + esc(r.columns.join(' | ')) + '\n' + r.rows.map((row) => row.map((v) => v === null ? 'NULL' : v).join(' | ')).join('\n')
                    + (r.truncated ? '\n… (truncated)' : '') + '</pre>';
            }
        });
        if (out.result.transaction && out.result.transaction !== 'none') html += `<div class="vdb-note">transaction: ${esc(out.result.transaction)}${out.result.dryRun ? ' (dry run — nothing saved)' : ''}</div>`;
        box.innerHTML = html || '<div class="vdb-note">No statements.</div>';
        loadObjects();
    });
    /* ─────────── visual table builder ─────────── */
    const FIELD_TYPES = ['INTEGER','BIGINT','REAL','NUMERIC','DECIMAL','TEXT','VARCHAR','CHAR','BOOLEAN','DATE','TIME','DATETIME','JSON','BLOB'];
    const SIZED = ['VARCHAR','CHAR','NUMERIC','DECIMAL'];
    let TB = { edit: null };   // edit = original table name when editing

    function colRow(col) {
        col = col || {};
        const tr = document.createElement('tr');
        tr.dataset.orig = col.name || '';
        const type = (col.type || 'INTEGER').toUpperCase();
        const opts = FIELD_TYPES.map((t) => `<option value="${t}"${t === type ? ' selected' : ''}>${t}</option>`).join('');
        const len = col.size ? (col.scale ? col.size + ',' + col.scale : String(col.size)) : '';
        const dflt = (col.default === null || col.default === undefined) ? '' : String(col.default);
        tr.innerHTML =
            `<td class="mid"><i class="bi bi-grip-vertical drag" draggable="true" title="Drag to reorder"></i></td>
             <td><input type="text" class="vdb-name-in c-name" value="${esc(col.name || '')}" placeholder="column_name" autocomplete="off"></td>
             <td><select class="c-type">${opts}</select></td>
             <td><input type="text" class="c-len" value="${esc(len)}" placeholder="—"></td>
             <td class="mid"><input type="checkbox" class="c-null"${col.nullable ? ' checked' : ''}></td>
             <td class="mid"><input type="checkbox" class="c-pk"${col.primary_key ? ' checked' : ''}></td>
             <td class="mid"><input type="checkbox" class="c-ai"${col.auto_increment ? ' checked' : ''}></td>
             <td><input type="text" class="c-def" value="${esc(dflt)}" placeholder="—"></td>
             <td class="mid"><button class="vdb-btn danger c-del" title="Remove column"><i class="bi bi-x-lg"></i></button></td>`;
        // keep type/length sensible as flags change
        const typeSel = tr.querySelector('.c-type'), lenIn = tr.querySelector('.c-len'),
              pk = tr.querySelector('.c-pk'), ai = tr.querySelector('.c-ai'), nul = tr.querySelector('.c-null');
        const syncLen = () => { lenIn.disabled = !SIZED.includes(typeSel.value.toUpperCase()) || ai.checked; if (lenIn.disabled) lenIn.value = ''; };
        typeSel.addEventListener('change', syncLen);
        ai.addEventListener('change', () => { if (ai.checked) { typeSel.value = 'INTEGER'; pk.checked = true; nul.checked = false; } syncLen(); });
        pk.addEventListener('change', () => { if (pk.checked) nul.checked = false; });
        tr.querySelector('.c-del').addEventListener('click', () => tr.remove());
        // drag reorder
        const grip = tr.querySelector('.drag');
        grip.addEventListener('dragstart', () => tr.classList.add('dragging'));
        grip.addEventListener('dragend', () => tr.classList.remove('dragging'));
        syncLen();
        return tr;
    }
    // container-level dragover to position the dragging row
    function wireDrag(tbody) {
        tbody.addEventListener('dragover', (e) => {
            e.preventDefault();
            const dragging = tbody.querySelector('tr.dragging'); if (!dragging) return;
            const after = [...tbody.querySelectorAll('tr:not(.dragging)')].find((r) => e.clientY <= r.getBoundingClientRect().top + r.offsetHeight / 2);
            if (after) tbody.insertBefore(dragging, after); else tbody.appendChild(dragging);
        });
    }

    function openTableBuilder(existing) {
        TB.edit = existing ? existing.name : null;
        $('vdbTblModalTitle').innerHTML = existing ? `<i class="bi bi-pencil-square me-1"></i> Edit table · ${esc(existing.name)}` : '<i class="bi bi-table me-1"></i> New table';
        $('vdbTblName').value = existing ? existing.name : '';
        $('vdbTblErr').textContent = '';
        const tbody = $('vdbColRows');
        tbody.innerHTML = '';
        const cols = (existing && existing.columns && existing.columns.length) ? existing.columns
            : [{ name: 'id', type: 'INTEGER', primary_key: true, auto_increment: true, nullable: false }];
        cols.forEach((c) => tbody.appendChild(colRow(c)));
        $('vdbTblModal').classList.add('show');
        setTimeout(() => $('vdbTblName').focus(), 50);
    }
    function collectFields() {
        const rows = [...$('vdbColRows').querySelectorAll('tr')];
        return rows.map((tr) => {
            const g = (s) => tr.querySelector(s);
            const name = g('.c-name').value.trim();
            if (!name) return null;
            const type = g('.c-type').value.toUpperCase();
            let size = 0, scale = 0;
            const lv = g('.c-len').value.trim();
            if (lv) { const p = lv.split(','); size = parseInt(p[0], 10) || 0; scale = p[1] ? (parseInt(p[1], 10) || 0) : 0; }
            const defRaw = g('.c-def').value;
            const f = {
                name, type,
                nullable: g('.c-null').checked,
                primary_key: g('.c-pk').checked,
                auto_increment: g('.c-ai').checked,
            };
            if (size) f.size = size;
            if (scale) f.scale = scale;
            if (defRaw !== '') f.default = defRaw;
            if (tr.dataset.orig) f.original_name = tr.dataset.orig;   // enables rename on edit
            return f;
        }).filter(Boolean);
    }
    $('vdbNewTableBtn').addEventListener('click', () => openTableBuilder(null));
    $('vdbAddColBtn').addEventListener('click', () => $('vdbColRows').appendChild(colRow({ type: 'VARCHAR', size: 255, nullable: true })));
    $('vdbTblModalX').addEventListener('click', () => $('vdbTblModal').classList.remove('show'));
    $('vdbTblCancel').addEventListener('click', () => $('vdbTblModal').classList.remove('show'));
    $('vdbTblModal').addEventListener('click', (e) => { if (e.target === $('vdbTblModal')) $('vdbTblModal').classList.remove('show'); });
    wireDrag($('vdbColRows'));
    $('vdbTblSave').addEventListener('click', async () => {
        const table = $('vdbTblName').value.trim();
        const fields = collectFields();
        const err = $('vdbTblErr');
        if (!/^[A-Za-z_][A-Za-z0-9_]*$/.test(table)) { err.textContent = 'Enter a valid table name (letters, numbers, underscore).'; return; }
        if (!fields.length) { err.textContent = 'Add at least one column.'; return; }
        err.textContent = '';
        const btn = $('vdbTblSave'); btn.disabled = true;
        let out;
        if (TB.edit) out = await post('/database-manager/api/table-update', { db: A.db.id, old_table: TB.edit, new_table: table, fields });
        else out = await post('/database-manager/api/table-create', { db: A.db.id, table, fields });
        btn.disabled = false;
        if (!out.success) { err.textContent = out.message || 'Could not save.'; return; }
        $('vdbTblModal').classList.remove('show');
        toast(true, out.message);
        A.table = table;
        loadObjects();
    });
    $('vdbEditStruct').addEventListener('click', async () => {
        if (!A.table) return;
        const obj = A.objects.find((o) => o.name === A.table);
        if (obj && obj.type === 'VIEW') { toast(false, 'Views are edited from the View button (they have no columns to alter).'); return; }
        const d = await getJson('/database-manager/api/structure?db=' + A.db.id + '&table=' + encodeURIComponent(A.table));
        if (!d.success) { toast(false, 'Could not read the table structure.'); return; }
        openTableBuilder({ name: A.table, columns: (d.details && d.details.columns) || [] });
    });

    /* ─────────── view builder ─────────── */
    $('vdbNewViewBtn').addEventListener('click', async () => {
        const r = await Swal.fire({
            title: 'New view', width: 640,
            html: `<input id="vwName" class="swal2-input" placeholder="view name" style="margin-bottom:.4rem">
                   <textarea id="vwSel" class="swal2-textarea" placeholder="SELECT … FROM …" style="height:150px;font-family:ui-monospace,monospace;font-size:.82rem"></textarea>
                   <div style="text-align:left"><button id="vwPrev" type="button" class="swal2-confirm swal2-styled" style="font-size:.8rem;padding:.3rem .7rem;background:#64748b">Preview</button></div>
                   <pre id="vwOut" style="text-align:left;max-height:22vh;overflow:auto;font-size:.75rem;margin-top:.4rem"></pre>`,
            showCancelButton: true, confirmButtonText: 'Create view',
            didOpen: () => {
                document.getElementById('vwPrev').addEventListener('click', async () => {
                    const sel = document.getElementById('vwSel').value.trim();
                    const out = await post('/database-manager/api/view-preview', { db: A.db.id, select: sel });
                    const box = document.getElementById('vwOut');
                    if (!out.success) { box.textContent = out.message || 'Preview failed.'; return; }
                    const p = out.preview || {};
                    box.textContent = (p.columns || []).join(' | ') + '\n' + (p.rows || []).map((row) => (Array.isArray(row) ? row : Object.values(row)).map((v) => v === null ? 'NULL' : v).join(' | ')).join('\n');
                });
            },
            preConfirm: () => ({ name: document.getElementById('vwName').value.trim(), select: document.getElementById('vwSel').value.trim() })
        });
        if (!r.isConfirmed) return;
        const out = await post('/database-manager/api/view-save', { db: A.db.id, name: r.value.name, select: r.value.select, replace: true });
        toast(out.success, out.message);
        if (out.success) { A.table = r.value.name; loadObjects(); }
    });

    /* tabs */
    function switchTab(name) {
        document.querySelectorAll('.vdb-tab').forEach((t) => t.classList.toggle('active', t.dataset.tab === name));
        document.querySelectorAll('[data-pane]').forEach((p) => { p.style.display = p.dataset.pane === name ? '' : 'none'; });
    }
    document.querySelectorAll('.vdb-tab').forEach((t) => t.addEventListener('click', () => switchTab(t.dataset.tab)));

    renderList();
    <?php if ($isOwner): ?>renderQuotas(); loadMaintenance();<?php endif; ?>
})();
</script>
<?php
$ownerGuide = [
    'id' => 'vdbGuide',
    'eyebrow' => 'Virtual Database Engine',
    'title' => 'Databases without servers',
    'subtitle' => 'Everything you need to know about the built-in database engine.',
    'icon' => 'lightning-charge-fill',
    'topics' => [
        ['id' => 'what', 'label' => 'What is it?', 'icon' => 'lightning-charge', 'keywords' => 'virtual sqlite engine built-in',
         'body' => '<p>The Virtual Database Engine gives every project and user a <b>real SQL database with zero setup</b> — no host, no port, no credentials, no external server. Each database is a single portable file (SQLite) stored safely inside ' . htmlspecialchars(\PpPhp\Core\Brand::name()) . ', hardened automatically (WAL journalling, enforced foreign keys) and blocked from web access.</p><p>Projects created with the <b>Virtual Database</b> option use their own private file; the databases on THIS page are the shared pool you manage centrally.</p>'],
        ['id' => 'create', 'label' => 'Creating & quotas', 'icon' => 'plus-circle', 'keywords' => 'create new quota seats limit assign',
         'body' => '<p><b>New database</b> creates one instantly. The owner can create for any user; users create for themselves within their <b>quota</b> (the owner sets a global default and per-user overrides in the Quotas panel — −1 means unlimited).</p><p>Assigning a database to a user makes it visible on their own Virtual Databases page.</p>'],
        ['id' => 'admin', 'label' => 'Browsing & editing data', 'icon' => 'terminal', 'keywords' => 'admin browse edit rows sql phpmyadmin tables view structure columns',
         'body' => '<p><b>Open</b> launches the built-in admin (a phpMyAdmin-style client): browse rows with search/sort/paging, edit or add rows inline, inspect table structure, and run <b>any SQL</b> in the console (with optional transaction or dry-run — a dry run rolls everything back).</p><p><b>New table</b> opens a <b>visual builder</b> — add columns with their type, length, primary key, auto-increment and default, no SQL needed. On the <b>Structure</b> tab, <b>Edit structure</b> reopens that builder to rename, retype, add, drop or reorder columns — <b>your data is preserved</b> across the change. <b>View</b> creates a saved query (with a live preview).</p>'],
        ['id' => 'backup', 'label' => 'Backup, restore & export', 'icon' => 'download', 'keywords' => 'backup restore export convert mysql postgres sql server oracle deploy',
         'body' => '<p><b>Backup</b> downloads a consistent .sqlite snapshot (safe during use). <b>Restore</b> replaces the contents from a backup file.</p><p><b>Export SQL</b> writes a script of structure + data — either native SQLite, or <b>converted to any engine the platform supports</b> (MySQL, MariaDB, TiDB, PostgreSQL, CockroachDB, SQL Server, Oracle). The converted script runs directly on that server: types are translated, identity/auto-increment is handled per engine, and seed data is quoted in the target\'s SQL rules — ideal for taking an app live on external hosting.</p>'],
        ['id' => 'snapshots', 'label' => 'Snapshots, maintenance & cloning', 'icon' => 'clock-history', 'keywords' => 'snapshot rollback restore point-in-time maintain optimize checkpoint clone copy fork encrypted backup rotating',
         'body' => '<p><b>Snapshots</b> (<i class="bi bi-clock-history"></i>) are rotating, point-in-time copies — take one before a risky change and <b>roll back</b> to it in one click (the newest 7 are kept automatically). Choose <b>Encrypted snapshot</b> to store it AES-256-GCM encrypted at rest, so even the backup file is unreadable without the server key.</p><p><b>Maintain</b> (<i class="bi bi-heart-pulse"></i>) runs a quick integrity probe, refreshes the query planner, and checkpoints the write-ahead log — keeping the database fast and healthy as it grows. <b>Clone</b> (<i class="bi bi-files"></i>) forks the whole database (structure + data) into a brand-new one — perfect for a sandbox or a template.</p>'],
        ['id' => 'security', 'label' => 'Where files live & safety', 'icon' => 'shield-check', 'keywords' => 'security htaccess files storage safety encryption speed',
         'body' => '<p>Pool databases live in <code>storage/vdb/</code>; project databases in each project\'s <code>database/</code> folder. Every folder holding a database gets a deny-all <code>.htaccess</code> <b>and</b> IIS <code>web.config</code> so files can never be downloaded over the web.</p><p>Every connection is tuned for <b>speed</b> (16&nbsp;MB page cache, memory-mapped I/O, in-memory temp store) and <b>safety</b> (WAL journalling, enforced foreign keys, corruption checks). <b>Integrity check</b> runs SQLite\'s full self-check; <b>VACUUM</b> compacts the file. Deleting a database securely shreds its file. Builds ship a consistent snapshot of a project\'s virtual database automatically.</p>'],
    ],
];
include constant('BASE_PATH') . '/core/views/owner-ux.php';
?>
</body>
</html>
