<?php
$B = htmlspecialchars(rtrim((string) ($baseUrl ?? ''), '/'));
if (!function_exists('pp_where')) {
    function pp_where(string $path, string $lang = 'php'): string {
        $l = $lang === 'js' ? 'JS' : 'PHP';
        return '<div class="help-where"><span class="help-where-tag"><i class="bi bi-geo-alt-fill"></i> Where</span>'
             . '<span class="help-where-path">' . $path . '</span>'
             . '<span class="help-where-lang ' . htmlspecialchars($lang) . '">' . $l . '</span></div>';
    }
}
?>
<h1><i class="bi bi-hdd-network-fill"></i> pp() — the Server API</h1>
<p class="lead"><code>pp()</code> is the PHP companion to <a href="<?= $B ?>/help/PPjs">pp.js / Runner</a>. Where
<code>Runner</code> reaches the page from the browser, <code>pp()</code> reaches it from the server — and it can
<strong>drive the browser live</strong>: set a field, toast a message, reload the grid, all from PHP, with no
client-after code. Available in every server event, Custom&nbsp;Button PHP, and <code>action_*</code> function.</p>

<div class="help-callout help-callout-info">
    <i class="bi bi-arrow-left-right"></i>
    <div><strong>How “live” works.</strong> Your PHP queues <em>commands</em> (<code>pp()->toast(...)</code> …). The
        request returns <code>{ result, commands }</code>, and pp.js's <code>Runner.apply()</code> runs each command in
        the browser. So a round-trip through <code>pp.run()</code> or a Custom&nbsp;Button server stage updates the page
        by itself.</div>
</div>

<h2>Three things it gives you</h2>
<table class="help-table">
    <thead><tr><th>Area</th><th>Calls</th><th>For</th></tr></thead>
    <tbody>
        <tr><td><b>Context</b></td><td><code>user() · userId() · username() · role() · can(a,t) · page() · param(k) · id()</code></td><td>Who's acting, what they may do, and what the browser sent.</td></tr>
        <tr><td><b>Data</b></td><td><code>find(t,id) · rows(sql) · row(sql) · value(sql) · insert(t,d) · update(t,id,d) · delete(t,id)</code></td><td>Read/write records — permission-gated, row-level-security applied.</td></tr>
        <tr><td><b>Commands</b></td><td><code>toast · setControl · setData · message · hide · show · focus · addClass · addStyle · reloadGrid · reload · redirect · openForm</code></td><td>Drive the browser live. All chainable.</td></tr>
    </tbody>
</table>

<h2>Recipes</h2>

<h3>1 · Approve a record and reflect it instantly</h3>
<?= pp_where('Designer → Custom Button (on the View/Edit page) → <b>Server</b>', 'php') ?>
<pre><code>if (!pp()->can('edit', 'orders')) { pp()->toast('error', 'Not allowed.'); return; }
pp()->update('orders', pp()->id(), ['status' => 'approved', 'approved_by' => pp()->username()]);
pp()->setControl('status', 'approved')
    ->toast('success', 'Order approved')
    ->reloadGrid();</code></pre>
<p>No Client After code — the field updates and the toast shows because the server queued them.</p>

<h3>2 · Server-side uniqueness check, message the field</h3>
<?= pp_where('Custom Functions → PHP — an <code>action_*</code> called from a field On Change', 'php') ?>
<pre><code>function action_check_email(array $params): array {
    $taken = pp()->value('SELECT COUNT(*) FROM customers WHERE email = ?', [$params['email'] ?? '']) > 0;
    if ($taken) pp()->message('email', 'Already registered', 'error');
    else        pp()->clearMessage('email');
    return ['ok' => !$taken];
}</code></pre>
<?= pp_where("Events → customers → Fields → email → <b>On Change</b> (Client)", 'js') ?>
<pre><code>pp.run('check_email', { email: Runner.getControlValue('email') });
// the server's message() command lands on the field automatically</code></pre>

<h3>3 · Fill several fields from one lookup</h3>
<?= pp_where('Custom Button → <b>Server</b>, or an <code>action_*</code>', 'php') ?>
<pre><code>$c = pp()->find('customers', pp()->param('customer_id'));
if ($c) {
    pp()->setData([
        'email'   => $c['email'],
        'phone'   => $c['phone'],
        'country' => $c['country'],
    ])->toast('info', 'Customer details filled in');
}</code></pre>

<h3>4 · Redirect after a server action</h3>
<?= pp_where('Custom Button → <b>Server</b>', 'php') ?>
<pre><code>$id = pp()->insert('invoices', ['order_id' => pp()->id(), 'created' => now()]);
if ($id) pp()->redirect(pp()->url('generated/invoices/view.php?id=' . $id));</code></pre>

<h3>5 · Ask, then act — pair with pp.dialog.form()</h3>
<p>Collect input on the client with <a href="<?= $B ?>/help/component_custom_button">pp.dialog.form()</a>, do the work
in PHP, and let the server confirm:</p>
<?= pp_where('Custom Button → <b>Server</b>', 'php') ?>
<pre><code>$m = (array) pp()->param('mail');
$to = filter_var($m['email'] ?? '', FILTER_VALIDATE_EMAIL);
if (!$to) { pp()->toast('error', 'A valid email is required'); return; }
pp_send_email($to, $m['subject'] ?? 'Report', $m['note'] ?? '');
pp()->toast('success', 'Sent to ' . $to);</code></pre>

<h2>The query builder — <code>pp()->table()</code></h2>
<p>A small, safe, chainable builder. Identifiers are validated and quoted, values are always bound, and
<a href="<?= $B ?>/help/Security">row-level security</a> is merged into every read — the same guards the built-in
list pages use.</p>
<?= pp_where('any server event, Custom Button PHP, or <code>action_*</code>', 'php') ?>
<pre><code>// read
$open = pp()->table('orders')
    ->where('status', 'open')
    ->where('total', '>', 100)          // where(col, op, val)
    ->whereIn('region', ['EU', 'UK'])
    ->orderBy('created', 'desc')
    ->limit(20)
    ->get();                            // array of rows

$one   = pp()->table('orders')->where('ref', $ref)->first();     // ?array
$total = pp()->table('orders')->where('status', 'open')->count(); // int
$has   = pp()->table('orders')->where('id', $id)->exists();       // bool
$names = pp()->table('customers')->orderBy('name')->pluck('name');// array of one column
$page  = pp()->table('orders')->orderBy('id')->paginate(20, 2);   // { data, total, page, perPage, pages }</code></pre>
<table class="help-table">
    <thead><tr><th>Build</th><th>Terminal</th></tr></thead>
    <tbody>
        <tr><td><code>where(col[, op], val)</code> · <code>whereIn</code> · <code>whereNull</code> / <code>whereNotNull</code> · <code>whereRaw(sql, params)</code></td><td><code>get()</code> · <code>first()</code> · <code>value(col)</code></td></tr>
        <tr><td><code>orderBy(col, 'asc'|'desc')</code> · <code>select(...cols)</code> · <code>limit(n)</code> · <code>offset(n)</code></td><td><code>count()</code> · <code>exists()</code> · <code>pluck(col)</code></td></tr>
        <tr><td colspan="2"><code>paginate(perPage, page)</code> · <code>toSql()</code> → <code>[sql, bindings]</code> for debugging</td></tr>
    </tbody>
</table>
<p><code>where()</code> operators are whitelisted (<code>= != &lt; &lt;= &gt; &gt;= like</code>…); an unknown one falls back
to <code>=</code>, and a non-identifier column name is rejected — so the builder can never be an injection vector. Use
<code>whereRaw()</code> for anything fancier (still bound).</p>

<h3>Joins</h3>
<p>Join related tables with <code>join()</code> / <code>leftJoin()</code> and use qualified <code>table.column</code>
names. Row-level security still applies to the base table.</p>
<?= pp_where('any server event, Custom Button PHP, or <code>action_*</code>', 'php') ?>
<pre><code>$rows = pp()->table('orders')
    ->select('orders.id', 'orders.total', 'customers.name')
    ->leftJoin('customers', 'orders.customer_id', '=', 'customers.id')
    ->where('orders.status', 'open')
    ->orderBy('orders.created', 'desc')
    ->get();</code></pre>

<h3>Aggregates &amp; grouped reporting</h3>
<pre><code>$revenue = pp()->table('orders')->where('status', 'paid')->sum('total');   // float
$biggest = pp()->table('orders')->max('total');
$avgCart = pp()->table('orders')->where('status', 'paid')->avg('total');

// top customers by spend — selectRaw + groupBy + having
$top = pp()->table('orders')
    ->selectRaw('customer_id, SUM(total) AS spent, COUNT(*) AS orders')
    ->where('status', 'paid')
    ->groupBy('customer_id')
    ->having('SUM(total) > ?', [1000])
    ->orderBy('spent', 'desc')
    ->limit(10)
    ->get();</code></pre>
<table class="help-table">
    <thead><tr><th>More builder methods</th><th>Does</th></tr></thead>
    <tbody>
        <tr><td><code>join(t, a, op, b)</code> / <code>leftJoin(t, a, op, b)</code></td><td>Join a table on qualified columns.</td></tr>
        <tr><td><code>sum · avg · min · max (col)</code></td><td>Single-value aggregates over the current filter + joins.</td></tr>
        <tr><td><code>groupBy(...cols)</code> · <code>having(sql, params)</code></td><td>Grouped rows + a bound HAVING filter.</td></tr>
        <tr><td><code>selectRaw(expr)</code> · <code>distinct()</code></td><td>Raw select expression · de-duplicate rows.</td></tr>
        <tr><td><code>whereBetween(col, a, b)</code> · <code>whereNull / whereNotNull(col)</code></td><td>Range and null filters.</td></tr>
    </tbody>
</table>

<h3>Bulk writes — <code>update()</code> / <code>delete()</code></h3>
<p>The builder also writes. <code>update()</code> and <code>delete()</code> apply to exactly the rows your filter
matches and return how many were affected. They're <strong>permission-gated</strong> (<code>edit</code> / <code>delete</code>
rights) and <strong>refuse an unfiltered write</strong> — you must call <code>->where(...)</code> first (or have a
<a href="<?= $B ?>/help/Security">row-level rule</a>), so a stray call can never touch the whole table.</p>
<?= pp_where('Custom Button → <b>Server</b>, or an <code>action_*</code>', 'php') ?>
<pre><code>// expire stale pending orders in one statement
$n = pp()->table('orders')
    ->where('status', 'pending')
    ->where('created', '<', $cutoff)
    ->update(['status' => 'expired']);
pp()->toast('info', $n . ' order(s) expired')->reloadGrid();

// delete the rows the user ticked on the List page
$gone = pp()->table('logs')->whereIn('id', pp()->selectedKeys())->delete();
pp()->toast('success', $gone . ' log(s) removed')->reloadGrid();</code></pre>
<div class="help-callout help-callout-warn">
    <i class="bi bi-exclamation-triangle"></i>
    <div><code>pp()->table('t')->update([...])</code> with <strong>no</strong> <code>->where()</code> throws
        <em>“Refusing to update every row of t”</em>. To change one known record, prefer
        <code>pp()->update('t', $id, [...])</code>.</div>
</div>

<h3>Atomic counters — <code>increment()</code> / <code>decrement()</code></h3>
<p>Bump a numeric column in the database itself (<code>SET col = col ± ?</code>) — race-safe, no read-then-write.
Permission-gated and filter-required, like the other writes.</p>
<?= pp_where('Custom Button → <b>Server</b>, a field event, or an <code>action_*</code>', 'php') ?>
<pre><code>pp()->table('posts')->where('id', $id)->increment('views');           // +1
pp()->table('wallets')->where('user_id', $uid)->increment('balance', 50);
pp()->table('stock')->where('sku', $sku)->decrement('qty', $sold, ['updated' => now()]);  // also set a column</code></pre>

<h3>Insert-or-update — <code>upsert()</code></h3>
<p>Write a row, or update it if one already exists on the conflict column(s). It's <strong>driver-aware</strong> —
MySQL <code>ON DUPLICATE KEY</code>, Postgres/SQLite <code>ON CONFLICT</code>, and a portable fallback elsewhere — so
the same call works on every supported database. Needs <code>add</code> + <code>edit</code> rights.</p>
<?= pp_where('Custom Button → <b>Server</b>, or an <code>action_*</code>', 'php') ?>
<pre><code>// one settings row per user — insert the first time, update every time after
pp()->upsert('settings',
    ['user_id' => pp()->userId(), 'theme' => 'dark', 'lang' => 'en'],
    ['user_id']);                       // ← the unique/conflict column(s)

// only touch specific columns on conflict (4th arg)
pp()->upsert('counters', ['key' => 'hits', 'n' => 1, 'updated' => now()], ['key'], ['n', 'updated']);</code></pre>
<p>By default every non-conflict column is updated; pass a 4th argument to limit which ones. The conflict column(s)
must have a UNIQUE (or primary) key in the database.</p>

<h2>Transactions</h2>
<p>Wrap several writes so they all land or none do. <code>transaction()</code> commits on success and rolls back
(then re-throws) on any exception — and nesting is safe (a nested call joins the outer transaction).</p>
<?= pp_where('Custom Button → <b>Server</b>, or an <code>action_*</code>', 'php') ?>
<pre><code>pp()->transaction(function ($pp) {
    $orderId = $pp->insert('orders', ['customer_id' => $pp->param('cid'), 'created' => now()]);
    foreach ((array) $pp->param('items') as $it) {
        $pp->insert('order_items', ['order_id' => $orderId, 'sku' => $it['sku'], 'qty' => $it['qty']]);
    }
    return $orderId;
});
// any failure inside → everything above is rolled back automatically
pp()->toast('success', 'Order placed')->reloadGrid();</code></pre>
<p>Prefer <code>transaction()</code>; the manual <code>pp()->begin() / commit() / rollback()</code> are there for
finer control.</p>

<h3>Savepoints — partial rollback</h3>
<p>Inside a transaction, a <strong>savepoint</strong> lets you undo just part of the work and keep going.
<code>trySavepoint()</code> is the common case: run a step in a savepoint, and if it fails, roll back only that step
and continue — the <em>“skip the bad row, keep the good ones”</em> pattern for imports and batch jobs.</p>
<?= pp_where('Custom Button → <b>Server</b>, or an <code>action_*</code>', 'php') ?>
<pre><code>$ok = 0; $bad = 0;
pp()->transaction(function ($pp) use (&$ok, &$bad) {
    foreach ((array) $pp->param('rows') as $row) {
        // each insert is isolated — one failure doesn't lose the batch
        if ($pp->trySavepoint('row', fn($pp) => $pp->insert('items', $row))) $ok++;
        else $bad++;
    }
});
pp()->toast('info', "$ok imported, $bad skipped")->reloadGrid();</code></pre>
<p>For manual control the trio is <code>pp()->savepoint('name')</code>, <code>rollbackTo('name')</code>,
<code>releaseSavepoint('name')</code>. Savepoints must run inside <code>pp()->transaction()</code>.</p>

<h2>The current record &amp; selected rows</h2>
<p>Straight from the button object idea — reach the record a page/button acts on, or the rows ticked on a List page,
without wiring params yourself (a Custom&nbsp;Button and <code>pp.run()</code> send the context automatically).</p>
<?= pp_where('Custom Button → <b>Server</b> (on a View/Edit page)', 'php') ?>
<pre><code>$r = pp()->record();                       // the current record (from the page's table + id)
if ($r) pp()->toast('info', 'Editing ' . $r['name']);</code></pre>
<?= pp_where('Custom Button → <b>Server</b> (on a List page, with rows ticked)', 'php') ?>
<pre><code>// act on every selected row — the clean form of PHPRunner's getNextSelectedRecord loop
pp()->eachSelected(function ($row) {
    pp()->update('orders', $row['id'], ['status' => 'shipped']);
});
pp()->toast('success', count(pp()->selectedRecords()) . ' order(s) shipped')->reloadGrid();
// also: pp()->selectedKeys()  ·  pp()->selectedRecords()</code></pre>

<h2>The command reference</h2>
<table class="help-table">
    <thead><tr><th>PHP command</th><th>Applied in the browser as</th></tr></thead>
    <tbody>
        <tr><td><code>pp()->toast(type, msg)</code></td><td><code>pp.dialog.toast()</code></td></tr>
        <tr><td><code>pp()->alert(title, text, icon)</code></td><td><code>pp.dialog.alert()</code></td></tr>
        <tr><td><code>pp()->setControl(field, value)</code></td><td><code>Runner.setControlValue()</code></td></tr>
        <tr><td><code>pp()->setData([field => value])</code></td><td><code>Runner.setData()</code></td></tr>
        <tr><td><code>pp()->message(field, msg, tone)</code></td><td><code>control.setMessage()</code></td></tr>
        <tr><td><code>pp()->hide / show / focus (field)</code></td><td><code>control.hide/show/focus()</code></td></tr>
        <tr><td><code>pp()->addClass / addStyle (field, …)</code></td><td><code>control.addClass/addStyle()</code></td></tr>
        <tr><td><code>pp()->reloadGrid() / reload()</code></td><td><code>Runner.reloadGrid()</code> / page reload</td></tr>
        <tr><td><code>pp()->redirect(url)</code></td><td><code>location.href = url</code></td></tr>
        <tr><td><code>pp()->openForm(config)</code></td><td><code>pp.dialog.form(config)</code></td></tr>
        <tr><td><code>pp()->openUrl(url, newTab)</code> · <code>download(url)</code></td><td>Navigate / new tab · trigger a download</td></tr>
        <tr><td><code>pp()->scrollTo(field)</code> · <code>highlight(field)</code></td><td>Scroll into view · flash the field</td></tr>
        <tr><td><code>pp()->closeDialog()</code> · <code>log(value)</code></td><td>Close any dialog · <code>console.log()</code></td></tr>
        <tr><td><code>pp()->emit(name, detail)</code></td><td><code>document.dispatchEvent(new CustomEvent('pp:name'…))</code></td></tr>
        <tr><td><code>pp()->call(fn, args)</code></td><td>Call a global JS function by name</td></tr>
    </tbody>
</table>

<h3>Extensible commands — <code>emit()</code> &amp; <code>call()</code></h3>
<p>When a built-in command isn't enough, the server can hand off to your own client code. <code>emit()</code> fires a DOM
event you listen for; <code>call()</code> invokes a global function you defined.</p>
<?= pp_where('Global Events → Application → <b>JS OnLoad</b> (define the listener once)', 'js') ?>
<pre><code>document.addEventListener('pp:order-approved', function (e) {
    // e.detail is whatever PHP passed
    confetti();  Runner.getControl('status').addStyle('color:#16a34a;font-weight:700');
});</code></pre>
<?= pp_where('Custom Button → <b>Server</b>', 'php') ?>
<pre><code>pp()->update('orders', pp()->id(), ['status' => 'approved']);
pp()->emit('order-approved', ['id' => pp()->id()]);   // your listener does the rest</code></pre>

<div class="help-callout help-callout-warn">
    <i class="bi bi-shield-lock"></i>
    <div><code>insert/update/delete</code> are permission-gated (<code>add/edit/delete</code> rights) and
        <code>find</code> honours <a href="<?= $B ?>/help/Security">row-level security</a>, exactly like the built-in
        pages. Commands are applied only when pp.js is on the page (every generated page) — on a lean custom page they
        are simply ignored, and <code>result</code> still returns.</div>
</div>
