hyprcrd/support-relay/inc/bootstrap.php

<?php
// DB, master-key, schema, shared helpers. All include files require
// SUPPORT_RELAY_BOOTSTRAPPED to be defined by index.php.

if (!defined('SUPPORT_RELAY_BOOTSTRAPPED')) { http_response_code(404); exit; }

error_reporting(0);
ini_set('display_errors', '0');

$SUPPORT_ROOT = dirname(__DIR__);
$dbPath = $SUPPORT_ROOT . '/data/support.db';
$dbDir = dirname($dbPath);
if (!is_dir($dbDir)) mkdir($dbDir, 0755, true);

// Master key for encrypting SMTP creds + admin password hash context
$masterKeyPath = $SUPPORT_ROOT . '/data/master.key';
if (!file_exists($masterKeyPath)) {
    file_put_contents($masterKeyPath, bin2hex(random_bytes(32)));
    @chmod($masterKeyPath, 0600);
}
$MASTER_KEY = trim(file_get_contents($masterKeyPath));

$db = new PDO('sqlite:' . $dbPath, null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
$db->exec('PRAGMA journal_mode = WAL');
$db->exec('PRAGMA synchronous = NORMAL');
$db->exec('PRAGMA foreign_keys = ON');

// ── Schema ─────────────────────────────────────────────────────────

$db->exec('CREATE TABLE IF NOT EXISTS settings (
    key TEXT PRIMARY KEY,
    value TEXT
)');

// Tickets are the source of truth here. hyprcrd mirrors them as cards.
// status: open (user submitted) | answered (we sent a reply) | closed
// message_id: Message-ID of the first outbound reply, used for email
// threading so user replies land back under the same thread.
$db->exec('CREATE TABLE IF NOT EXISTS tickets (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    email TEXT NOT NULL,
    subject TEXT NOT NULL DEFAULT "",
    body TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT "open",
    message_id TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_reply_at TEXT
)');
$db->exec('CREATE INDEX IF NOT EXISTS idx_tickets_updated ON tickets(updated_at DESC)');
$db->exec('CREATE INDEX IF NOT EXISTS idx_tickets_status ON tickets(status, updated_at DESC)');
$db->exec('CREATE INDEX IF NOT EXISTS idx_tickets_email ON tickets(email)');

// Per-ticket reply log. direction: in (from user via form) | out (from
// hyprcrd → SMTP). sent_at is populated on successful SMTP delivery.
$db->exec('CREATE TABLE IF NOT EXISTS replies (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    ticket_id INTEGER NOT NULL,
    direction TEXT NOT NULL,
    body TEXT NOT NULL,
    sent_at TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
)');
$db->exec('CREATE INDEX IF NOT EXISTS idx_replies_ticket ON replies(ticket_id, created_at)');

// Rate limits for the public form (IP-keyed, tumbling windows)
$db->exec('CREATE TABLE IF NOT EXISTS rate_limits (
    ip TEXT NOT NULL,
    endpoint TEXT NOT NULL,
    attempted_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
)');

// Lightweight cleanup on every request (runs only old rows)
$db->exec("DELETE FROM rate_limits WHERE attempted_at < datetime('now', '-1 hour')");

// ── Encryption helpers (SMTP password is stored encrypted) ─────────

function supportRelayEncrypt(string $plain, string $masterKeyHex): string {
    $key = hex2bin($masterKeyHex);
    $nonce = random_bytes(12);
    $tag = '';
    $cipher = openssl_encrypt($plain, 'aes-256-gcm', $key, OPENSSL_RAW_DATA, $nonce, $tag, '', 16);
    return base64_encode($nonce . $cipher . $tag);
}
function supportRelayDecrypt(string $encoded, string $masterKeyHex): string {
    $raw = base64_decode($encoded, true);
    if (!$raw || strlen($raw) < 12 + 16 + 1) return '';
    $key = hex2bin($masterKeyHex);
    $nonce = substr($raw, 0, 12);
    $tag = substr($raw, -16);
    $cipher = substr($raw, 12, -16);
    return openssl_decrypt($cipher, 'aes-256-gcm', $key, OPENSSL_RAW_DATA, $nonce, $tag) ?: '';
}

// ── Settings helpers ───────────────────────────────────────────────

function supportRelayGet(PDO $db, string $key, ?string $default = null): ?string {
    $stmt = $db->prepare('SELECT value FROM settings WHERE key = :k');
    $stmt->execute(['k' => $key]);
    $v = $stmt->fetchColumn();
    return $v === false ? $default : $v;
}
function supportRelaySet(PDO $db, string $key, ?string $value): void {
    if ($value === null) {
        $db->prepare('DELETE FROM settings WHERE key = :k')->execute(['k' => $key]);
    } else {
        $db->prepare('INSERT OR REPLACE INTO settings (key, value) VALUES (:k, :v)')
           ->execute(['k' => $key, 'v' => $value]);
    }
}

function supportRelaySetupDone(PDO $db): bool {
    return supportRelayGet($db, 'setup_done') === '1';
}

function supportRelayIssueId(PDO $db, int $ticketId): string {
    $prefix = supportRelayGet($db, 'issue_prefix', 'OUMTA') ?: 'OUMTA';
    return $prefix . '-' . $ticketId;
}
function supportRelayParseIssueId(PDO $db, string $s): ?int {
    $prefix = supportRelayGet($db, 'issue_prefix', 'OUMTA') ?: 'OUMTA';
    $s = trim($s);
    if (preg_match('/^' . preg_quote($prefix, '/') . '-(\d+)$/i', $s, $m)) return (int)$m[1];
    if (preg_match('/^\d+$/', $s)) return (int)$s;
    return null;
}

function supportRelaySmtpConfig(PDO $db, string $masterKeyHex): array {
    $encPw = supportRelayGet($db, 'smtp_password', '') ?: '';
    return [
        'host'       => supportRelayGet($db, 'smtp_host', '') ?: '',
        'port'       => (int)(supportRelayGet($db, 'smtp_port', '587') ?: 587),
        'user'       => supportRelayGet($db, 'smtp_user', '') ?: '',
        'password'   => $encPw ? supportRelayDecrypt($encPw, $masterKeyHex) : '',
        'from'       => supportRelayGet($db, 'smtp_from', '') ?: '',
        'from_name'  => supportRelayGet($db, 'smtp_from_name', 'Support') ?: 'Support',
        'encryption' => supportRelayGet($db, 'smtp_encryption', 'starttls') ?: 'starttls',
    ];
}

// ── Rate limiting ──────────────────────────────────────────────────

function supportRelayRateLimited(PDO $db, string $endpoint, int $max = 5, int $windowMinutes = 10): bool {
    $ip = $_SERVER['REMOTE_ADDR'] ?? '';
    if ($ip === '') return false;
    $count = $db->prepare("SELECT COUNT(*) FROM rate_limits WHERE ip = :ip AND endpoint = :ep AND attempted_at > datetime('now', '-{$windowMinutes} minutes')");
    $count->execute(['ip' => $ip, 'ep' => $endpoint]);
    if ((int)$count->fetchColumn() >= $max) return true;
    $db->prepare('INSERT INTO rate_limits (ip, endpoint) VALUES (:ip, :ep)')->execute(['ip' => $ip, 'ep' => $endpoint]);
    return false;
}

// ── Response helper ────────────────────────────────────────────────

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

// ── Mail footer (configurable template) ───────────────────────────

/** Default footer if the admin hasn't set one in the config. */
const SUPPORT_RELAY_FOOTER_DEFAULT = "---\n{brand} · Ticket {issue_id}\nReply via {public_url} (issue id: {issue_id})";

/** Substitute the supported placeholders in the footer template.
 *  Whole lines containing a placeholder that resolves to empty are
 *  dropped, so "Reply via {public_url}" vanishes cleanly when no
 *  public URL is configured. */
function supportRelayRenderFooter(PDO $db, string $issueId): string {
    $tpl = supportRelayGet($db, 'mail_footer', '') ?: SUPPORT_RELAY_FOOTER_DEFAULT;
    $vars = [
        'brand'      => supportRelayGet($db, 'brand', 'Support') ?: 'Support',
        'issue_id'   => $issueId,
        'public_url' => rtrim((string)(supportRelayGet($db, 'public_url', '') ?: ''), '/'),
    ];
    $lines = [];
    foreach (explode("\n", $tpl) as $line) {
        // Identify the placeholders referenced on this line; drop the line
        // if any of them is empty. Keeps the footer tidy when public_url
        // or similar optional fields haven't been filled in.
        if (preg_match_all('/\{([a-z_]+)\}/', $line, $m)) {
            $skip = false;
            foreach ($m[1] as $k) { if (!isset($vars[$k]) || $vars[$k] === '') { $skip = true; break; } }
            if ($skip) continue;
        }
        $lines[] = strtr($line, array_combine(
            array_map(fn($k) => '{' . $k . '}', array_keys($vars)),
            array_values($vars)
        ));
    }
    return implode("\n", $lines);
}

// ── Mail rendering (plain + HTML + optional header logo) ───────────

/** Locate a header logo on disk. PNG wins over SVG (broadest mail-client
 *  support); SVG falls back for setups where only an SVG is shipped.
 *  Returns ['path', 'mime'] or null. */
function supportRelayLogo(): ?array {
    $root = dirname(__DIR__);
    $candidates = [
        ['path' => $root . '/logo2026col.png', 'mime' => 'image/png'],
        ['path' => $root . '/logo2026col.svg', 'mime' => 'image/svg+xml'],
    ];
    foreach ($candidates as $c) {
        if (is_file($c['path'])) return $c;
    }
    return null;
}

/** Wrap a plain-text body in a tiny HTML shell with the brand logo
 *  floated at the top. Escapes text, preserves line breaks. */
function supportRelayHtmlWrap(string $plain, string $brand, bool $hasLogo): string {
    $safe = htmlspecialchars($plain, ENT_QUOTES | ENT_HTML5, 'UTF-8');
    $safe = nl2br($safe);
    $logo = $hasLogo
        ? '<img src="cid:logo" alt="' . htmlspecialchars($brand, ENT_QUOTES) . '" style="max-height:48px;display:block;margin-bottom:18px;">'
        : '';
    return '<!DOCTYPE html><html><head><meta charset="utf-8"></head>'
         . '<body style="font-family:system-ui,-apple-system,Segoe UI,sans-serif;color:#1a2438;line-height:1.55;padding:20px;max-width:620px;margin:0 auto;">'
         . $logo
         . '<div style="font-size:14px;">' . $safe . '</div>'
         . '</body></html>';
}

/** Build the $options array that SmtpClient::send expects for a
 *  logo-branded mail. Returns an empty array when there's no logo on
 *  disk — the message then goes out as plain-text only (same as
 *  before this feature existed). */
function supportRelayMailOptions(string $plain, string $brand): array {
    $logo = supportRelayLogo();
    $html = supportRelayHtmlWrap($plain, $brand, $logo !== null);
    $opts = ['html' => $html];
    if ($logo) $opts['inlineImages'] = [['cid' => 'logo', 'path' => $logo['path'], 'mime' => $logo['mime']]];
    return $opts;
}