common.php

<?php
// Shared layout for every FoodScan page. Reuses PantryPrep color tokens.
require_once __DIR__ . '/db.php';
require_once __DIR__ . '/auth.php'; // defines AUTH_COOKIE; no auth check fires until a page calls requireLogin/requireAllowedIP

// Force a consistent timezone for every page so the timestamp `now()` writes
// to the scans table matches the date used by report filters. Without this,
// a scan at 8 pm ET on a UTC-default server lands as the next calendar day.
date_default_timezone_set('America/New_York');

getDB(); // ensure schema exists on first hit

function fsPrefix(): string {
    // Pages in subfolders (e.g. /foodscan/scan/) set $GLOBALS['FS_PREFIX']='../'
    // before requiring common.php so the shared header/nav resolves links and
    // assets correctly regardless of how deep the page sits.
    return $GLOBALS['FS_PREFIX'] ?? '';
}

function renderHead(string $title): void {
    $p = fsPrefix();
    ?><!DOCTYPE html>
<html lang="en">
<head>
<link rel="icon" type="image/x-icon" href="<?= $p ?>menucounter/favicon.ico">
<link rel="apple-touch-icon" href="<?= $p ?>apple-touch-icon.png">
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title><?= htmlspecialchars($title) ?> – OpenPantry</title>
<style>
  :root {
    --brown:  #6B4C11;
    --green:  #8BAF3A;
    --light:  #F5F0E8;
    --border: #D4C9A8;
    --text:   #333;
    --cat-bg: #EEE8D5;
    --blue:   #0056b3;
    --red:    #b1452a;
  }
  * { box-sizing: border-box; margin: 0; padding: 0; }
  html { font-size: 18px; }
  body { font-family: Arial, sans-serif; background: var(--light); color: var(--text); min-height: 100vh; }

  .site-header {
    background: #fff; border-bottom: 3px solid var(--green);
    padding: 14px 24px; display: flex; align-items: center; gap: 16px;
    box-shadow: 0 2px 6px rgba(0,0,0,.08);
  }
  .site-header img { height: 56px; }
  .header-text h1 { font-size: 1.1rem; color: var(--brown); font-weight: 700; text-transform: uppercase; }
  .header-text p  { font-size: .8rem; color: #777; }

  nav.subnav {
    background: #fff; border-bottom: 1px solid var(--border);
    padding: 8px 24px; display: flex; gap: 6px; flex-wrap: wrap;
  }
  nav.subnav a {
    text-decoration: none; color: var(--brown); padding: 6px 12px;
    border-radius: 6px; font-size: .85rem; font-weight: 700;
  }
  nav.subnav a.active, nav.subnav a:hover { background: var(--cat-bg); }
  nav.subnav .nav-logout { margin-left: auto; color: #8B1A1A; }

  /* Dropdown groups: the trigger looks like the flat nav links so the bar
     reads as one consistent row, and the panel anchors under it. The
     panel opens on click (handled in renderNav's inline JS) and on hover
     for desktop convenience. */
  nav.subnav .nav-group { position: relative; display: inline-flex; }
  nav.subnav .nav-trigger {
    background: transparent; border: none; cursor: pointer;
    color: var(--brown); padding: 6px 12px; border-radius: 6px;
    font-size: .85rem; font-weight: 700; font-family: inherit;
    display: inline-flex; align-items: center; gap: 4px;
  }
  nav.subnav .nav-trigger.active, nav.subnav .nav-trigger:hover,
  nav.subnav .nav-group:hover         > .nav-trigger,
  nav.subnav .nav-group:focus-within  > .nav-trigger { background: var(--cat-bg); }
  nav.subnav .nav-caret { font-size: .7rem; line-height: 1; }
  nav.subnav .nav-dropdown {
    display: none; position: absolute; top: 100%; left: 0; z-index: 100;
    flex-direction: column; min-width: 180px; padding: 6px;
    background: #fff; border: 1px solid var(--border); border-radius: 6px;
    box-shadow: 0 4px 12px rgba(0,0,0,.08); margin-top: 6px;
  }
  /* Invisible "bridge" filling the gap between the trigger and the panel so the
     cursor can travel from one to the other without crossing a non-hover zone
     (which would close the menu). Its negative top overlaps the trigger a touch
     so the hover area is continuous. */
  nav.subnav .nav-dropdown::before {
    content: ''; position: absolute; left: 0; right: 0; top: -8px; height: 8px;
  }
  nav.subnav .nav-group:hover        > .nav-dropdown,
  nav.subnav .nav-group:focus-within > .nav-dropdown { display: flex; }
  nav.subnav .nav-dropdown a {
    padding: 8px 12px; border-radius: 4px; white-space: nowrap;
  }
  nav.subnav .nav-dropdown a:hover,
  nav.subnav .nav-dropdown a.active { background: var(--cat-bg); }

  .container { max-width: 980px; margin: 24px auto 40px; padding: 0 16px; }
  .card { background: #fff; border: 1px solid var(--border); border-radius: 10px;
          box-shadow: 0 2px 10px rgba(0,0,0,.07); padding: 20px; margin-bottom: 20px; }
  .card h2 { font-size: 1rem; color: var(--brown); text-transform: uppercase;
             border-bottom: 2px solid var(--cat-bg); padding-bottom: 8px; margin-bottom: 14px; }

  .btn {
    border: none; border-radius: 7px; padding: 12px 18px;
    font-size: 1rem; font-weight: 700; cursor: pointer; font-family: inherit;
  }
  .btn-primary { background: var(--green); color: #fff; }
  .btn-secondary { background: #fff; color: var(--brown); border: 2px solid var(--border); }
  .btn-danger { background: var(--red); color: #fff; }
  .btn-block { display: block; width: 100%; }
  .btn:disabled { opacity: .5; cursor: not-allowed; }

  input[type=text], input[type=number], input[type=password], input[type=email],
  input[type=search], input[type=date], select, textarea {
    border: 2px solid var(--border); border-radius: 6px; padding: 10px;
    font-size: 1rem; font-family: inherit; background: #fff; width: 100%;
  }
  input:focus, select:focus, textarea:focus { outline: none; border-color: var(--blue); }
  label { font-size: .8rem; font-weight: 700; text-transform: uppercase;
          color: var(--brown); display: block; margin-bottom: 4px; }

  table.data { width: 100%; border-collapse: collapse; }
  table.data th, table.data td { padding: 8px 10px; text-align: left; border-bottom: 1px solid var(--border); font-size: .9rem; }
  table.data th { background: var(--cat-bg); color: var(--brown); text-transform: uppercase; font-size: .75rem; }
  table.data tr:hover td { background: #fafaf5; }
  table.data td.num, table.data th.num { text-align: right; font-variant-numeric: tabular-nums; }

  .banner { padding: 14px 18px; border-radius: 8px; margin-bottom: 16px;
            display: flex; gap: 12px; align-items: center; }
  .banner.success  { background: #D4EDDA; border: 1px solid #A8D8B9; color: #276437; }
  .banner.error    { background: #F8D7DA; border: 1px solid #F1AEB5; color: #8B1A1A; }
  .banner.warn     { background: #FFF3CD; border: 1px solid #FFE69C; color: #806000; }
  .banner.info     { background: #E7F3FF; border: 1px solid #BADCFF; color: var(--blue); }

  .stat-grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(160px, 1fr)); gap: 12px; }
  .stat { background: var(--cat-bg); border-radius: 8px; padding: 14px; text-align: center; }
  .stat .v { font-size: 1.6rem; font-weight: 800; color: var(--brown); }
  .stat .k { font-size: .75rem; text-transform: uppercase; color: #777; margin-top: 2px; }

  .row { display: flex; gap: 10px; align-items: end; flex-wrap: wrap; }
  .row > * { flex: 1 1 160px; }
</style>
</head>
<body>
<header class="site-header">
  <?php
    // Logo lives at foodscan/logo.jpg (admin-replaceable via Settings →
    // Food Pantry Information). Cache-bust with the file mtime so a freshly
    // uploaded logo shows immediately instead of a stale cached copy.
    $logoFile = __DIR__ . '/logo.jpg';
    $logoVer  = is_file($logoFile) ? ('?v=' . filemtime($logoFile)) : '';
    $pantryName = setting('food_pantry_name', '') ?? '';
  ?>
  <img src="<?= $p ?>logo.jpg<?= $logoVer ?>" alt="<?= htmlspecialchars($pantryName !== '' ? $pantryName : 'Logo') ?>">
  <div class="header-text">
    <h1><span style="color:#8baf3a;">Open</span>Pantry</h1>
    <p>Inventory tracking management</p>
  </div>
</header>
<?php
}

function renderNav(string $active = ''): void {
    $p = fsPrefix();
    // Dashboard lives at /foodscan/. From the dashboard itself, $p is '' so
    // the link is current-page; from subfolders, $p is '../' so it goes up
    // to /foodscan/ which serves index.php.
    $dash = $p === '' ? '' : $p;

    // Nav structure: each node is either a flat link or a "group" that
    // renders as a click-to-open dropdown trigger. Active highlighting
    // propagates from a child up to its parent trigger (so visiting any
    // Reports page lights up the Reports trigger).
    //
    // Scan and Menu Counter (PantryPrep admin) open in a new tab —
    // the rest open in the current tab.
    $newTabKeys = ['scan', 'menu_counter'];
    $nav = [
        ['type' => 'link', 'key' => 'index', 'label' => 'Dashboard', 'href' => $dash],
        ['type' => 'group', 'label' => 'Checkout', 'children' => [
            // One scan page for both input methods: the laser scanner types into
            // the barcode field, and Start Camera turns a phone into a scanner.
            ['key' => 'scan',         'label' => 'Scan',          'href' => $p . 'scan/'],
            ['key' => 'menu_counter', 'label' => 'Menu Counter',  'href' => $p . 'menucounter/admin/'],
            ['key' => 'delivery',     'label' => 'Deliveries',    'href' => $p . 'delivery/client/'],
            ['key' => 'order_ahead',  'label' => 'OrderAhead',    'href' => $p . 'orderahead/'],
            ['key' => 'event',        'label' => 'Events',        'href' => $p . 'event/'],
        ]],
        ['type' => 'link', 'key' => 'restock',   'label' => 'Restock',       'href' => $p . 'restock/'],
        ['type' => 'link', 'key' => 'inventory', 'label' => 'Inventory',     'href' => $p . 'inventory/'],
        ['type' => 'link', 'key' => 'lookup',    'label' => 'Lookup Tables', 'href' => $p . 'lookup_admin/'],
        ['type' => 'group', 'label' => 'Reports', 'children' => [
            ['key' => 'report',  'label' => 'Order Now',      'href' => $p . 'reports/order_report/'],
            ['key' => 'orders',  'label' => 'Orders Listing', 'href' => $p . 'reports/orders_listing_report/'],
            ['key' => 'usage',   'label' => 'Item Usage',     'href' => $p . 'reports/usage_report/'],
            ['key' => 'volume',  'label' => 'Daily Volume',   'href' => $p . 'reports/volume_report/'],
            ['key' => 'basket',  'label' => 'Basket Size',    'href' => $p . 'reports/basket_report/'],
            ['key' => 'traffic', 'label' => 'Client Traffic', 'href' => $p . 'reports/traffic_report/'],
            ['key' => 'impact',  'label' => 'Impact',         'href' => $p . 'reports/impact_report/'],
        ]],
        ['type' => 'link', 'key' => 'settings', 'label' => 'Settings', 'href' => $p . 'settings/'],
    ];

    echo '<nav class="subnav">';
    foreach ($nav as $node) {
        if ($node['type'] === 'link') {
            $cls = ($node['key'] === $active) ? ' class="active"' : '';
            $tgt = in_array($node['key'], $newTabKeys, true) ? ' target="_blank" rel="noopener"' : '';
            echo '<a href="' . htmlspecialchars($node['href']) . '"' . $cls . $tgt . '>'
               . htmlspecialchars($node['label']) . '</a>';
            continue;
        }
        // group: a button-style trigger plus a dropdown panel of child links.
        // The trigger gets the active style when any of its children is active.
        $groupActive = false;
        foreach ($node['children'] as $child) {
            if ($child['key'] === $active) { $groupActive = true; break; }
        }
        $groupCls = $groupActive ? 'nav-group active' : 'nav-group';
        $triggerCls = $groupActive ? 'nav-trigger active' : 'nav-trigger';
        echo '<div class="' . $groupCls . '">';
        echo   '<button type="button" class="' . $triggerCls . '" aria-haspopup="true" tabindex="0">'
             . htmlspecialchars($node['label']) . ' <span class="nav-caret">▾</span>'
             . '</button>';
        echo   '<div class="nav-dropdown" role="menu">';
        foreach ($node['children'] as $child) {
            $childCls = ($child['key'] === $active) ? ' class="active"' : '';
            $childTgt = in_array($child['key'], $newTabKeys, true) ? ' target="_blank" rel="noopener"' : '';
            echo '<a href="' . htmlspecialchars($child['href']) . '"' . $childCls . $childTgt
               . ' role="menuitem">' . htmlspecialchars($child['label']) . '</a>';
        }
        echo   '</div>';
        echo '</div>';
    }
    // Show logout only if the admin cookie is currently valid.
    if (isset($_COOKIE[AUTH_COOKIE]) && $_COOKIE[AUTH_COOKIE] !== '') {
        echo '<a href="' . $p . 'logout/" class="nav-logout">🔒 Log out</a>';
    }
    echo '</nav>';
    // Dropdown behavior: hover only. The CSS opens the panel on
    // `.nav-group:hover` and `:focus-within` (the latter keeps keyboard
    // tabbing usable without JS), so no script is needed.
}

function renderFoot(): void {
    $p = fsPrefix();
    ?>
<footer class="site-footer" style="text-align:center; padding:24px 16px; font-size:.78rem;
        color:#999; border-top:1px solid var(--border); margin-top:40px;">
  &copy; 2026 <strong>Chromagic Development</strong> &mdash;
  <strong>Bruce Alexander</strong>.
  Released under the
  <a href="<?= $p ?>LICENSE" style="color:var(--brown); text-decoration:none;">MIT License</a>.
</footer>
</body></html><?php
}

// Opaque per-device station id, used so two scanning stations can each keep
// their own open order at the same time. Stored in the `fs_station` cookie
// (random 128-bit token); generated and set on first use. MUST be called
// before any output is sent — every current caller (the scan pages before
// renderHead, and the JSON APIs before jsonOut) satisfies that.
function currentStationId(): string {
    static $sid = null;
    if ($sid !== null) return $sid;

    $cookie = $_COOKIE['fs_station'] ?? '';
    if (is_string($cookie) && preg_match('/^[a-f0-9]{32}$/', $cookie)) {
        return $sid = $cookie;
    }

    $sid = bin2hex(random_bytes(16));
    // 1-year cookie, path '/' so the scan page (/scan/) and the API
    // endpoints at the app root share the same token. Legacy setcookie
    // signature for PHP 7.2 compatibility (matches the sodium note in db.php).
    @setcookie('fs_station', $sid, time() + 365 * 24 * 3600, '/');
    $_COOKIE['fs_station'] = $sid; // visible to the rest of this same request
    return $sid;
}

// The open order for a given station (defaults to this device's station). Each
// station has at most one open order, so this is the row the scan flow appends
// to and the End/Cancel actions operate on. Pass '' to match legacy orders.
function currentOpenOrder(?string $station = null): ?array {
    if ($station === null) $station = currentStationId();
    $stmt = getDB()->prepare(
        "SELECT * FROM orders WHERE status='open' AND station=? ORDER BY id DESC LIMIT 1"
    );
    $stmt->execute([$station]);
    $row = $stmt->fetch();
    return $row ?: null;
}

// Every currently-open order, regardless of station — for admin overviews
// (e.g. the dashboard) that want to surface all in-progress orders rather than
// just the viewing device's own.
function allOpenOrders(): array {
    return getDB()
        ->query("SELECT * FROM orders WHERE status='open' ORDER BY id DESC")
        ->fetchAll();
}

// ── Team scanning ───────────────────────────────────────────────────────────
// A second station can "assist" an order started elsewhere so two operators
// check the same household out together. The assist is a row in order_assists;
// ownership stays with orders.station, so only the starting station may End or
// Cancel. A helper leaves by deleting its row.

// The open order this station has joined as a helper, or null.
function assistedOrder(?string $station = null): ?array {
    if ($station === null) $station = currentStationId();
    $stmt = getDB()->prepare(
        "SELECT o.* FROM orders o
           JOIN order_assists a ON a.order_id = o.id
          WHERE o.status='open' AND a.station=?
          ORDER BY o.id DESC LIMIT 1"
    );
    $stmt->execute([$station]);
    $row = $stmt->fetch();
    return $row ?: null;
}

// The order this station should be scanning into: its own open order if it has
// one, otherwise one it is assisting. Adds `is_assist` so callers can tell the
// two apart. Owning always wins, so a station is never in both roles at once —
// which is also why joinOrderAssist() refuses while an own order is open.
//
// This is the resolver for every scan-flow write (api_scan record/delete,
// api_kitchen). End / Cancel deliberately keep using currentOpenOrder().
function activeScanOrder(): ?array {
    $own = currentOpenOrder();
    if ($own) { $own['is_assist'] = false; return $own; }
    $assisted = assistedOrder();
    if ($assisted) { $assisted['is_assist'] = true; return $assisted; }
    return null;
}

// Open orders this station could offer to help with: someone else's, and not a
// legacy station-less row (those can't be attributed to a live scanner). Each
// carries the counts the picker shows.
function assistableOrders(): array {
    $stmt = getDB()->prepare(
        "SELECT o.*,
                (SELECT COUNT(*) FROM scans s WHERE s.order_id = o.id) AS scan_count,
                (SELECT COUNT(*) FROM order_assists a WHERE a.order_id = o.id) AS assist_count
           FROM orders o
          WHERE o.status='open' AND o.station <> '' AND o.station <> ?
          ORDER BY o.id DESC"
    );
    $stmt->execute([currentStationId()]);
    return $stmt->fetchAll();
}

// Short per-order display labels for the stations working an order: the owner
// is '1', helpers are '2', '3'… in join order. Derived rather than stored, so
// nothing new persists and a label stays stable for the life of the order.
// Returns station token => label.
function orderStations(int $orderId): array {
    $db = getDB();
    $own = $db->prepare("SELECT station FROM orders WHERE id=?");
    $own->execute([$orderId]);
    $ownerStation = (string)($own->fetchColumn() ?: '');

    $labels = [];
    if ($ownerStation !== '') $labels[$ownerStation] = '1';

    $helpers = $db->prepare(
        "SELECT station FROM order_assists WHERE order_id=? ORDER BY joined_at, station"
    );
    $helpers->execute([$orderId]);
    foreach ($helpers->fetchAll() as $h) {
        $s = (string)$h['station'];
        if ($s !== '' && !isset($labels[$s])) $labels[$s] = (string)(count($labels) + 1);
    }
    return $labels;
}

// How many stations are currently helping an order (excludes the owner).
function orderAssistCount(int $orderId): int {
    $stmt = getDB()->prepare("SELECT COUNT(*) FROM order_assists WHERE order_id=?");
    $stmt->execute([$orderId]);
    return (int)$stmt->fetchColumn();
}

// Join an open order as a helper. Returns an error string on refusal, null on
// success. Idempotent — re-joining an order this station already helps is fine.
function joinOrderAssist(int $orderId): ?string {
    $db = getDB();
    $stmt = $db->prepare("SELECT * FROM orders WHERE id=?");
    $stmt->execute([$orderId]);
    $order = $stmt->fetch();

    if (!$order)                        return 'That order no longer exists.';
    if ($order['status'] !== 'open')    return 'Order #' . $orderId . ' is already closed.';
    if ($order['station'] === currentStationId()) {
        return 'That is this station\'s own order.';
    }
    if (currentOpenOrder()) {
        return 'End this station\'s own order before assisting another.';
    }

    $db->prepare(
        "INSERT INTO order_assists (order_id, station, joined_at) VALUES (?, ?, ?)
         ON CONFLICT(order_id, station) DO NOTHING"
    )->execute([$orderId, currentStationId(), now()]);
    return null;
}

// Detach this station from the order it is assisting (or from a specific one).
function leaveOrderAssist(?int $orderId = null): void {
    if ($orderId === null) {
        getDB()->prepare("DELETE FROM order_assists WHERE station=?")
               ->execute([currentStationId()]);
        return;
    }
    getDB()->prepare("DELETE FROM order_assists WHERE order_id=? AND station=?")
           ->execute([$orderId, currentStationId()]);
}

// Detach every helper from an order — called when it is ended or cancelled, so
// the helping stations fall back to idle on their next sync.
function clearOrderAssists(int $orderId): void {
    getDB()->prepare("DELETE FROM order_assists WHERE order_id=?")->execute([$orderId]);
}

// ── Ending and cancelling an order ──────────────────────────────────────────
// The mechanics only, split out of api_order.php so the owner station's End /
// Cancel buttons and an administrator closing a stranded order that belongs to
// some other station run exactly the same code. Neither function decides *who*
// may do this, and both assume the order is open — the endpoint checks both.

// Was this failure the database being busy, or something that will fail again?
//
// It matters because the two need opposite advice. Contention clears on its own
// and "tap End again" is honest; a constraint violation, a missing column or a
// bug in this file reproduces identically on every retry, and telling a
// volunteer to keep tapping turns a one-off error into a household whose order
// can never be closed and nobody who knows to call for help.
//
// SQLITE_BUSY is 5 and SQLITE_LOCKED is 6, reported in errorInfo[1] by the
// SQLite driver. errorInfo is empty for a few PDOException paths (notably one
// thrown from beginTransaction()), so the message text is checked as well.
function isDbBusyError(\Throwable $e): bool {
    if (!($e instanceof \PDOException)) return false;
    $code = (int)($e->errorInfo[1] ?? 0);
    if ($code === 5 || $code === 6) return true;
    return stripos($e->getMessage(), 'is locked') !== false;
}

// Close an order and deduct what went out the door from inventory. Returns the
// ended_at it wrote, so the caller reports the timestamp actually stored.
//
// One transaction, for two separate reasons. Atomicity is the obvious one: a
// median order touches fifteen inventory rows, and as fifteen autocommit writes
// a PHP timeout partway through left the order closed with only some of its
// items deducted — a silent shortfall nothing retries and only a physical count
// finds. The subtler one is locking. Each `SELECT 1 FROM inventory` below left
// a read cursor open across the UPDATE that follows it, so the write had to
// upgrade a read snapshot; SQLite refuses that outright the moment another
// station has committed since the snapshot was taken, and returns SQLITE_BUSY
// *without consulting the busy handler* — so the 5s busy_timeout set in db.php
// never applied and the close failed instantly. Taking the write lock up front
// (the UPDATE below is the transaction's first statement, so BEGIN DEFERRED
// acquires it immediately) removes the upgrade entirely, and every station that
// arrives mid-close now waits its turn instead of erroring out.
function endOrderById(int $orderId): string {
    $db = getDB();
    $endedAt = now();
    $db->beginTransaction();
    try {
        $db->prepare("UPDATE orders SET status='closed', ended_at=? WHERE id=?")
           ->execute([$endedAt, $orderId]);
        // Release the helpers: their next sync sees no order and drops to idle.
        clearOrderAssists($orderId);

        // Decrement inventory by what was scanned in this order so the inventory
        // count reflects what just left the building.
        $items = $db->prepare(
            "SELECT generic_name, SUM(quantity) qty, SUM(COALESCE(weight_lbs,0)) wt, kind
             FROM scans WHERE order_id=? GROUP BY generic_name"
        );
        $items->execute([$orderId]);
        $rows = $items->fetchAll();
        $items->closeCursor();
        $exists = $db->prepare('SELECT 1 FROM inventory WHERE generic_name=?');
        $update = $db->prepare(
            "UPDATE inventory SET count = MAX(0, count - ?), updated_at=? WHERE generic_name=?"
        );
        foreach ($rows as $row) {
            $delta = ($row['kind'] === 'produce') ? (float)$row['wt'] : (float)$row['qty'];
            // Only decrement existing inventory rows. Closing an order shouldn't
            // imply "the prior count was zero" for an item never counted.
            $exists->execute([$row['generic_name']]);
            $have = $exists->fetchColumn();
            // fetchColumn() stops at the first row and leaves the statement's
            // cursor open. Harmless inside the transaction, but a reused handle
            // holding a cursor across a write is exactly the shape that made
            // this function fail, so close it rather than leave the trap set.
            $exists->closeCursor();
            if ($have) {
                $update->execute([$delta, $endedAt, $row['generic_name']]);
            }
        }
        $db->commit();
    } catch (\Throwable $e) {
        // Nothing deducted and the order still open beats a half-deducted close:
        // the station can simply press End again.
        if ($db->inTransaction()) $db->rollBack();
        throw $e;
    }
    return $endedAt;
}

// Cancellation = "this order didn't happen". Wipe its scans so they don't
// pollute the demand history, drop the order row, and reset the AUTOINCREMENT
// high-water mark to the surviving MAX(id). When the cancelled order was the
// newest, this reuses its number instead of skipping it; with another
// station's order still open, MAX(id) is that order so seq is left effectively
// unchanged. Either way the next insert is MAX(id)+1, so no live id is ever
// collided with. Returns when it happened.
function cancelOrderById(int $orderId): string {
    $db = getDB();
    $db->beginTransaction();
    try {
        $db->prepare("DELETE FROM scans WHERE order_id=?")->execute([$orderId]);
        // Before the order row, so the order_assists foreign key still resolves.
        $db->prepare("DELETE FROM order_assists WHERE order_id=?")->execute([$orderId]);
        $db->prepare("DELETE FROM orders WHERE id=?")->execute([$orderId]);
        $db->exec(
            "UPDATE sqlite_sequence SET seq = (SELECT IFNULL(MAX(id), 0) FROM orders) WHERE name='orders'"
        );
        $db->commit();
    } catch (\Throwable $e) {
        // Same contract as endOrderById(): leave the order exactly as it was so
        // the station can try again, rather than half-discarded.
        if ($db->inTransaction()) $db->rollBack();
        throw $e;
    }
    return now();
}

// ── Sticky assist mode ──────────────────────────────────────────────
// Joining an order also switches this station into assist mode, and it stays
// there until Leave Assist. clearOrderAssists() still drops the helper the
// moment the order closes, but the mode outlives it: on the next poll the
// station picks up whatever order is open now, so a dedicated helper works
// consecutive households without re-joining between each one.

// Is this station in assist mode? True both while it is helping an order and
// while it waits between orders for the next one.
function assistModeOn(?string $station = null): bool {
    if ($station === null) $station = currentStationId();
    $stmt = getDB()->prepare("SELECT 1 FROM assist_mode WHERE station=?");
    $stmt->execute([$station]);
    return (bool)$stmt->fetchColumn();
}

// Switch this station's assist mode on or off. On is idempotent — re-joining
// keeps the original `since`, which is when the operator chose to start
// helping rather than when the current order happened to come along.
function setAssistMode(bool $on): void {
    $db = getDB();
    if (!$on) {
        $db->prepare("DELETE FROM assist_mode WHERE station=?")
           ->execute([currentStationId()]);
        return;
    }
    $db->prepare(
        "INSERT INTO assist_mode (station, since) VALUES (?, ?)
         ON CONFLICT(station) DO NOTHING"
    )->execute([currentStationId(), now()]);
}

// The automatic half of sticky assist: if this station is in assist mode and
// attached to nothing, join the order it could help. Returns the order id it
// joined, or null when it joined nothing — the mode is off, the station is
// already on an order, no other station has one open, or *several* do. That
// last case is left to the operator via the Assist card, for the same reason
// 'assist_auto' refuses to guess: choosing wrong puts one household's
// groceries on another household's order.
//
// Safe (and cheap) to call on every poll and page load: a no-op unless the
// station is genuinely between orders with the mode on.
function autoJoinNextAssist(): ?int {
    if (!assistModeOn())    return null;
    if (currentOpenOrder()) return null;   // owns an order — not free to help
    if (assistedOrder())    return null;   // already helping one
    $open = assistableOrders();
    if (count($open) !== 1) return null;
    $orderId = (int)$open[0]['id'];
    return joinOrderAssist($orderId) === null ? $orderId : null;
}

// ── Per-station preferences ──────────────────────────────────────────
// Switches that belong to a device rather than to the pantry, kept in
// station_prefs so they survive the order, the assist, and the browser session.
// No row means every default, so a station that never touches a switch never
// writes one.

// Should this station sound the scan tone as it records items while assisting?
// Read only by the station itself — the beep is local, on the assisting
// station's own speaker. Defaults to on.
function stationScanBeep(?string $station = null): bool {
    if ($station === null) $station = currentStationId();
    $stmt = getDB()->prepare("SELECT scan_beep FROM station_prefs WHERE station=?");
    $stmt->execute([$station]);
    $v = $stmt->fetchColumn();
    return $v === false ? true : ((int)$v === 1);   // no row — default on
}

function setStationScanBeep(bool $on): void {
    getDB()->prepare(
        "INSERT INTO station_prefs (station, scan_beep, updated_at) VALUES (?, ?, ?)
         ON CONFLICT(station) DO UPDATE SET scan_beep = excluded.scan_beep,
                                            updated_at = excluded.updated_at"
    )->execute([currentStationId(), $on ? 1 : 0, now()]);
}


// An order's scans in ascending id order, shaped for the scan page's table:
// the raw columns plus a server-formatted time (scanned_at is 'Y-m-d H:i:s',
// which JS Date parsing handles inconsistently across engines) and the short
// station label from orderStations(). Used both to hydrate the page on load and
// to redraw it from the sync poll, so the two paths can't drift apart.
function orderScanRows(int $orderId): array {
    $labels = orderStations($orderId);
    $st = getDB()->prepare(
        "SELECT id, barcode, generic_name, kind, quantity, weight_lbs, scanned_at, station
           FROM scans WHERE order_id = ? ORDER BY id ASC"
    );
    $st->execute([$orderId]);
    $rows = [];
    foreach ($st->fetchAll() as $r) {
        $r['time_label']    = date('g:i:s A', strtotime($r['scanned_at']));
        $r['station_label'] = $labels[(string)$r['station']] ?? '';
        $rows[] = $r;
    }
    return $rows;
}

function jsonOut($data, int $code = 200): void {
    http_response_code($code);
    header('Content-Type: application/json');
    echo json_encode($data);
    exit;
}

function jsonIn(): array {
    $raw = file_get_contents('php://input');
    if (!$raw) return $_POST;
    $data = json_decode($raw, true);
    return is_array($data) ? $data : [];
}