<?php
/**
 * OpsLog multi-operator LIVE STATUS renderer.
 *
 * Reads the shared `live_status` table that every OpsLog instance heartbeats
 * (operator call + station call + freq/band/mode, refreshed every ~15s) and
 * shows the operators active in the last 2 minutes.
 *
 * Put this file on YOUR web server (the one reachable from the internet), point
 * it at the SAME MySQL database OpsLog uses for the shared logbook, and embed it
 * on the QRZ.com bio of the station call:
 *
 *     <img src="https://your-server/tm74-status.php?img=1">     (image, cached ~min by QRZ)
 *  or <a href="https://your-server/tm74-status.php">Live operators</a>   (real-time page)
 *
 * QRZ strips <script>/<iframe>, so only an <img> auto-updates the page.
 */

$DB_HOST = '10.10.10.15';      // your MySQL host (same as OpsLog's logbook)
$DB_PORT = 3306;               // MySQL port — change if your server listens elsewhere
$DB_NAME = 'opslog';           // database name
$DB_USER = 'opslog';
$DB_PASS = 'CHANGE_ME';
$STALE_SECONDS = 120;          // an operator is "active" if seen within this window

// PHP 8.1+ makes mysqli THROW on errors by default; turn that off so a missing
// `live_status` table (not yet created by OpsLog) just yields an empty list
// instead of a fatal "table doesn't exist".
mysqli_report(MYSQLI_REPORT_OFF);

$mysqli = @new mysqli($DB_HOST, $DB_USER, $DB_PASS, $DB_NAME, $DB_PORT);
if ($mysqli->connect_errno) {
    http_response_code(500);
    exit('DB error');
}

// The table is created by OpsLog on first publish; tolerate it not existing yet.
$rows = [];
$sql = "SELECT operator, station, freq_hz, band, mode, updated_at
        FROM live_status
        WHERE updated_at >= UTC_TIMESTAMP() - INTERVAL ? SECOND
        ORDER BY band, freq_hz";
if ($stmt = @$mysqli->prepare($sql)) {
    $stmt->bind_param('i', $STALE_SECONDS);
    @$stmt->execute();
    if ($res = $stmt->get_result()) {
        while ($r = $res->fetch_assoc()) $rows[] = $r;
    }
}

$station = $rows ? $rows[0]['station'] : 'OpsLog';
$fmtFreq = function ($hz) { return $hz > 0 ? number_format($hz / 1e6, 3) . ' MHz' : '—'; };

// ── Image output (SVG) for the QRZ <img> embed: ?img=1 ──────────────────────
if (isset($_GET['img'])) {
    header('Content-Type: image/svg+xml');
    header('Cache-Control: no-cache, max-age=30');
    $rowH = 26; $h = 44 + max(1, count($rows)) * $rowH; $w = 440;
    echo "<svg xmlns='http://www.w3.org/2000/svg' width='$w' height='$h' font-family='Segoe UI,Arial'>";
    echo "<rect width='$w' height='$h' rx='8' fill='#0f172a'/>";
    echo "<text x='14' y='26' fill='#38bdf8' font-size='15' font-weight='bold'>" . htmlspecialchars($station) . " — live operators</text>";
    $y = 44 + 18;
    if (!$rows) {
        echo "<text x='14' y='$y' fill='#94a3b8' font-size='13'>No operator active right now.</text>";
    }
    foreach ($rows as $r) {
        $line = sprintf('%-10s  %-4s  %-9s  %s', $r['operator'], $r['band'], $r['mode'], $fmtFreq($r['freq_hz']));
        echo "<text x='14' y='$y' fill='#e2e8f0' font-size='13' font-family='monospace'>" . htmlspecialchars($line) . "</text>";
        $y += $rowH;
    }
    echo "</svg>";
    exit;
}

// ── HTML page (real-time when opened directly; auto-refreshes every 20s) ─────
header('Content-Type: text/html; charset=utf-8');
?><!doctype html>
<html lang="en"><head><meta charset="utf-8">
<meta http-equiv="refresh" content="20">
<title><?= htmlspecialchars($station) ?> — live operators</title>
<style>
  body { font-family: Segoe UI, Arial, sans-serif; background:#0f172a; color:#e2e8f0; margin:0; padding:16px; }
  h1 { color:#38bdf8; font-size:18px; margin:0 0 12px; }
  table { border-collapse:collapse; width:100%; max-width:560px; }
  th,td { text-align:left; padding:6px 12px; border-bottom:1px solid #1e293b; font-size:14px; }
  th { color:#94a3b8; text-transform:uppercase; font-size:11px; letter-spacing:.05em; }
  td.mono { font-family:monospace; }
  .none { color:#94a3b8; }
</style></head><body>
<h1><?= htmlspecialchars($station) ?> — operators active now</h1>
<?php if (!$rows): ?>
  <p class="none">No operator active right now.</p>
<?php else: ?>
  <table>
    <tr><th>Operator</th><th>Band</th><th>Mode</th><th>Frequency</th></tr>
    <?php foreach ($rows as $r): ?>
      <tr>
        <td><strong><?= htmlspecialchars($r['operator']) ?></strong></td>
        <td><?= htmlspecialchars($r['band']) ?></td>
        <td><?= htmlspecialchars($r['mode']) ?></td>
        <td class="mono"><?= $fmtFreq($r['freq_hz']) ?></td>
      </tr>
    <?php endforeach; ?>
  </table>
<?php endif; ?>
</body></html>
