PDQ/lib/db.php

<?php
// PDO SQLite bootstrap + idempotent schema migrations.
// Call db() from any endpoint to get the connection.

function db(): PDO {
    static $pdo = null;
    if ($pdo !== null) return $pdo;

    $dir = __DIR__ . '/../data';
    if (!is_dir($dir)) {
        mkdir($dir, 0775, true);
    }
    $path = $dir . '/pdq.sqlite';

    $pdo = new PDO('sqlite:' . $path);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $pdo->exec('PRAGMA foreign_keys = ON');
    $pdo->exec('PRAGMA journal_mode = WAL');

    db_migrate($pdo);
    return $pdo;
}

function db_migrate(PDO $pdo): void {
    $version = (int) $pdo->query('PRAGMA user_version')->fetchColumn();

    if ($version < 1) {
        $pdo->exec("
            CREATE TABLE vendors (
                id     INTEGER PRIMARY KEY,
                slug   TEXT NOT NULL UNIQUE,
                name   TEXT NOT NULL,
                active INTEGER NOT NULL DEFAULT 1
            );

            CREATE TABLE jobs (
                id          INTEGER PRIMARY KEY,
                vendor_id   INTEGER NOT NULL REFERENCES vendors(id),
                job         TEXT NOT NULL DEFAULT 'New',
                material    TEXT NOT NULL DEFAULT '',
                description TEXT NOT NULL DEFAULT '',
                qty         INTEGER NOT NULL DEFAULT 0,
                due_date    TEXT,
                ack         TEXT NOT NULL DEFAULT 'new',
                status      TEXT NOT NULL DEFAULT '',
                created_at  TEXT NOT NULL DEFAULT (datetime('now')),
                updated_at  TEXT NOT NULL DEFAULT (datetime('now'))
            );
            CREATE INDEX jobs_vendor_status ON jobs(vendor_id, status);

            CREATE TABLE job_history (
                id         INTEGER PRIMARY KEY,
                job_id     INTEGER NOT NULL REFERENCES jobs(id),
                field      TEXT NOT NULL,
                old_value  TEXT,
                new_value  TEXT,
                actor      TEXT NOT NULL,
                changed_at TEXT NOT NULL DEFAULT (datetime('now'))
            );
            CREATE INDEX job_history_job ON job_history(job_id, changed_at);

            CREATE TABLE messages (
                id        INTEGER PRIMARY KEY,
                vendor_id INTEGER NOT NULL REFERENCES vendors(id),
                author    TEXT NOT NULL,
                body      TEXT NOT NULL,
                posted_at TEXT NOT NULL DEFAULT (datetime('now'))
            );
            CREATE INDEX messages_vendor_time ON messages(vendor_id, posted_at);
        ");

        $pdo->prepare('INSERT INTO vendors(slug, name) VALUES(?, ?)')
            ->execute(['bill', 'Bill']);

        $pdo->exec('PRAGMA user_version = 1');
    }
}

function find_vendor_by_slug(string $slug): ?array {
    $stmt = db()->prepare('SELECT * FROM vendors WHERE slug = ? AND active = 1');
    $stmt->execute([$slug]);
    $row = $stmt->fetch();
    return $row ?: null;
}

function all_vendors(): array {
    return db()->query('SELECT * FROM vendors WHERE active = 1 ORDER BY name')->fetchAll();
}