<?php
/*
 * Owner/admin-only guide: Database Manager.
 * Served by modules/Help/HelpController (topic = "database_manager").
 * $c() prints an HTML-safe code block from a nowdoc so samples containing
 * <, > and & display literally.
 */
$c = static fn (string $s): string => '<pre><code>' . htmlspecialchars($s) . '</code></pre>';
?>

<h1><i class="bi bi-hdd-stack-fill"></i> Database Manager</h1>
<p class="lead">Create real databases, hand them to users with their own isolated logins,
provision through cPanel or DirectAdmin when plain SQL cannot, cap how much space each one
may use, and keep a warm copy on a backup server.</p>

<div class="alert alert-info">
    <strong>Who is this for?</strong> Owners. The page lives at <code>/platform/databases</code>
    and is hidden from everyone else. There is also a quick in&#8209;page <strong>Guide</strong>
    button; this document is the deeper reference.
</div>

<h2>Contents</h2>
<ul>
    <li><a href="#model">The model &mdash; servers, databases, logins</a></li>
    <li><a href="#panels">Provisioning through a control panel</a></li>
    <li><a href="#hosts">The two addresses, and why they differ</a></li>
    <li><a href="#quota">Storage limits</a></li>
    <li><a href="#replication">Replication to a backup server</a></li>
    <li><a href="#verify">Verify &mdash; proving a database really works</a></li>
    <li><a href="#trouble">Troubleshooting</a></li>
</ul>

<h2 id="model">The model</h2>
<p>Three things stack up:</p>
<ul>
    <li><strong>Server</strong> &mdash; somewhere databases can be created. Either the
        <em>platform</em> server (your own MySQL, from <code>config/database.php</code>) or an
        <em>external</em> one you register.</li>
    <li><strong>Database</strong> &mdash; a real schema, created on one of those servers.</li>
    <li><strong>Login</strong> &mdash; a dedicated MySQL account scoped by <code>GRANT</code> to
        that one schema. This is what makes users isolated from one another.</li>
</ul>
<p>A user assigned a database picks <strong>&ldquo;Use my database&rdquo;</strong> when creating a
project. They never see the password &mdash; the platform injects the connection server&#8209;side.</p>

<h2 id="panels">Provisioning through a control panel</h2>
<p>On shared hosting the MySQL account your application runs as almost never has
<code>CREATE DATABASE</code>. You get:</p>
<?= $c("SQLSTATE[42000]: Access denied for user '...'@'%' to database '...' (1044)") ?>
<p>That is not a bug to work around with a bigger password &mdash; the account genuinely lacks the
privilege, and on most shared hosts it always will. Two ways forward:</p>
<ol>
    <li><strong>A privileged MySQL login</strong> &mdash; store one on the platform server row via
        <strong>Set account</strong>. It is verified with a live create/drop probe before it is saved.</li>
    <li><strong>A control&#8209;panel driver</strong> &mdash; let cPanel or DirectAdmin do it over
        their API, which is the same route the panel&rsquo;s own UI takes. Usually the better answer,
        because it needs no elevated MySQL account at all.</li>
</ol>

<h3>Registering a panel</h3>
<p><strong>Add external server</strong> &rarr; choose the driver. The form reshapes itself.</p>
<ul>
    <li><strong>cPanel</strong> &mdash; API port <code>2083</code>, an API token from
        <em>Security &rarr; Manage API Tokens</em>.</li>
    <li><strong>DirectAdmin</strong> &mdash; API port <code>2222</code>, a login key.</li>
</ul>
<p>Press <strong>Test</strong> before <strong>Add</strong>. Test reports <em>which endpoint
answered</em>, so a blocked port is never mistaken for a bad token.</p>

<h3>Name prefixes</h3>
<p>Both panels force the account name onto everything they create. Ask for
<code>orders</code> and you get <code>youracct_orders</code>. The two panels disagree about how
to be asked &mdash; cPanel wants the full prefixed name, DirectAdmin wants it bare and adds the
prefix itself &mdash; so <?= htmlspecialchars(\PpPhp\Core\Brand::name()) ?> always stores the name the panel <em>actually</em> created rather
than the one it requested. What you see in the list is what exists on disk.</p>

<h2 id="hosts">The two addresses, and why they differ</h2>
<div class="alert alert-warning">
    <strong>The most common setup mistake.</strong> The address that <em>provisions</em> a database
    is not the address that <em>uses</em> it.
</div>
<p>A panel answers on something like <code>cpanel.example.com:2083</code>. The MySQL those
databases live on is usually <code>localhost</code>, reachable only from that machine. So:</p>
<ul>
    <li><strong>Host / API port</strong> &mdash; where <?= htmlspecialchars(\PpPhp\Core\Brand::name()) ?> talks to the panel.</li>
    <li><strong>Database host</strong> &mdash; where generated projects connect. Set this whenever
        it differs, which on shared hosting is nearly always.</li>
</ul>
<p>If projects fail to connect while the panel tests fine, this is almost certainly why.
<strong>Verify</strong> will tell you outright.</p>

<h2 id="quota">Storage limits</h2>
<p><?= htmlspecialchars(\PpPhp\Core\Brand::name()) ?> is a development platform. A project here holds a schema and a little demo data; the
real database lives on whatever hosting the user deploys their generated app to. So each managed
database has an allowance &mdash; <strong>Limit / db</strong> in the Databases header, default
<strong>5&nbsp;MB</strong>. It applies to every database, not only new ones.</p>
<p>Users see a usage bar on their Projects page, colour&#8209;changing at 75%, 90% and 100%.</p>

<h3>What is actually enforced</h3>
<p>Worth being precise, because the word &ldquo;limit&rdquo; promises more than any system here can
deliver. MySQL has no per&#8209;schema disk quota and shared hosting gives no filesystem quota, so
<strong>nothing can physically stop a row inserted by a user&rsquo;s own generated app</strong>.
What the limit does do:</p>
<ul>
    <li>measures usage accurately, and shows it long before it matters;</li>
    <li>refuses platform&#8209;side operations that would breach it &mdash; notably moving a
        sandbox project onto a managed database;</li>
    <li>gives you a single number to raise when someone genuinely needs more.</li>
</ul>
<p>The platform&rsquo;s own database is exempt: it holds every project, licence and visitor row
and is legitimately far larger than any per&#8209;project allowance.</p>

<h2 id="replication">Replication to a backup server</h2>
<p>An install is either the <strong>primary</strong> or a <strong>backup</strong>. Two separate
things travel between them, and confusing them causes the most support questions:</p>
<ul>
    <li><strong>The catalogue</strong> &mdash; licences, accounts, the <em>list</em> of databases.
        Syncs every 15 minutes.</li>
    <li><strong>The data</strong> &mdash; actual tables and rows. Cloned separately, far less often.</li>
</ul>
<div class="alert alert-warning">
    A database appearing in the backup&rsquo;s list does <strong>not</strong> mean its data is
    there. The catalogue is replicated; the data is cloned. Use the <strong>Replication</strong>
    panel, which only appears on a backup.
</div>

<h3>How a clone decides what to copy</h3>
<p>The backup asks the primary for a fingerprint <em>per table</em>, compares it against its own
replica, and pulls only what differs. An estate where nothing changed transfers nothing at all.
Fingerprints are checksums, not size estimates &mdash; an <code>UPDATE</code> that changes a value
without changing row count or size is still detected.</p>
<p>Everything is restored into a <code>replica_*</code> database. A clone can never be written
over a live one; that rule is enforced in code, not by convention. Tables deleted on the primary
are removed from the replica too.</p>

<h3>Scheduling</h3>
<div class="alert alert-danger">
    <strong>Turning &ldquo;Scheduled&rdquo; on does not clone anything by itself.</strong> It records
    the intent; a cron entry has to call it. The panel warns you if the switch is on and nothing has
    run it.
</div>
<?= $c('# shell cron, every 6 hours
0 */6 * * * php /path/to/PpPhp/bin/pp-clone-databases.php >> storage/logs/clone.log 2>&1') ?>
<p>On hosts with no shell cron, point a cron service at the web&#8209;cron URL shown under
<em>How to schedule it</em>. It carries a per&#8209;install secret &mdash; treat it as a password.</p>
<p>Creating and assigning are disabled on a backup. Do both on the primary and let them replicate.</p>

<h2 id="verify">Verify</h2>
<p><strong>Verify</strong> connects with the <em>exact</em> credentials a project would be handed,
then creates and drops a table. It proves the database works end to end rather than merely
existing in the list.</p>
<p>Reach for it whenever a user says their project cannot connect: it separates a wrong
<strong>Database host</strong> from a permissions problem and says which.</p>

<h2 id="trouble">Troubleshooting</h2>
<table class="table">
    <thead><tr><th>Symptom</th><th>Usually means</th></tr></thead>
    <tbody>
    <tr><td><code>1044 Access denied</code> on create</td>
        <td>The account cannot <code>CREATE DATABASE</code>. Use a panel driver, or store a
            privileged login with <strong>Set account</strong>.</td></tr>
    <tr><td>Panel <strong>Test</strong> passes, projects cannot connect</td>
        <td><strong>Database host</strong> is unset or wrong &mdash; you are handing projects the
            panel&rsquo;s address instead of MySQL&rsquo;s. Run <strong>Verify</strong>.</td></tr>
    <tr><td>cPanel returns <code>403 Access denied</code></td>
        <td>Token, username, or the token lacking database permissions. A 403 from a fallback
            endpoint can also mean the direct port is firewalled &mdash; Test names which endpoint
            answered.</td></tr>
    <tr><td>Backup lists a database, phpMyAdmin does not show it</td>
        <td>Expected. The catalogue replicated; the data has not been cloned yet. Press
            <strong>Clone now</strong>.</td></tr>
    <tr><td>Clone says <em>&ldquo;No tables yet&rdquo;</em></td>
        <td>The source database is genuinely empty. Normal for a new project.</td></tr>
    <tr><td>&ldquo;Scheduled&rdquo; is on but nothing clones</td>
        <td>No cron entry is calling it. Add one from <em>How to schedule it</em>.</td></tr>
    <tr><td>Replica deleted by hand</td>
        <td>Self&#8209;heals. The next run notices the copy is gone and re&#8209;clones it.</td></tr>
    <tr><td>Size shows <code>0 B</code></td>
        <td>Either the database really is empty, or it is unreachable &mdash; <strong>Verify</strong>
            distinguishes the two.</td></tr>
    </tbody>
</table>
