<?php

function config(): array
{
    static $cfg = null;
    return $cfg ??= require __DIR__ . '/config.php';
}

// Snapshot the database into data/backups/ before any write (add, edit,
// delete, bulk edit, usage deduction), so a botched change can be undone by
// copying a backup over filaments.sqlite. The newest 10 snapshots are kept.
function backup_db(): void
{
    $src = config()['db_path'];
    if (!is_file($src)) {
        return;
    }
    $dir = dirname($src) . '/backups';
    if (!is_dir($dir)) {
        mkdir($dir, 0775, true);
    }
    // Microseconds keep two writes within the same second from colliding.
    copy($src, $dir . '/filaments-' . (new DateTime())->format('Ymd-His-u') . '.sqlite');
    $old = glob($dir . '/filaments-*.sqlite');
    sort($old); // timestamped names sort chronologically
    foreach (array_slice($old, 0, max(0, count($old) - 10)) as $f) {
        unlink($f);
    }
}

function db(): PDO
{
    static $pdo = null;
    if ($pdo === null) {
        $path = config()['db_path'];
        $dir = dirname($path);
        if (!is_dir($dir)) {
            mkdir($dir, 0775, true);
        }
        $pdo = new PDO('sqlite:' . $path, null, null, [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]);
        $pdo->exec('PRAGMA foreign_keys = ON');
        $pdo->exec(<<<SQL
CREATE TABLE IF NOT EXISTS filaments (
    id             INTEGER PRIMARY KEY AUTOINCREMENT,
    type           TEXT NOT NULL,
    color_hex      TEXT,
    color_hex2     TEXT,
    color_name     TEXT,
    brand          TEXT,
    total_weight_g REAL,
    spool_weight_g REAL,
    original_g     REAL DEFAULT 1000,
    remaining_g    REAL,
    abrasive       INTEGER NOT NULL DEFAULT 0,
    highflow       INTEGER NOT NULL DEFAULT 0,
    silk           INTEGER NOT NULL DEFAULT 0,
    matte          INTEGER NOT NULL DEFAULT 0,
    rainbow        INTEGER NOT NULL DEFAULT 0,
    transparent    INTEGER NOT NULL DEFAULT 0,
    twotone        INTEGER NOT NULL DEFAULT 0,
    sparkle        INTEGER NOT NULL DEFAULT 0,
    small_spool    INTEGER NOT NULL DEFAULT 0,
    cost           REAL,
    last_used_at   TEXT,
    notes          TEXT,
    created_at     TEXT DEFAULT (datetime('now')),
    updated_at     TEXT
);
CREATE TABLE IF NOT EXISTS usage_log (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    filament_id INTEGER NOT NULL REFERENCES filaments(id) ON DELETE CASCADE,
    grams       REAL NOT NULL,
    source_file TEXT,
    created_at  TEXT DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS settings (
    key   TEXT PRIMARY KEY,
    value TEXT
);
CREATE TABLE IF NOT EXISTS auth_tokens (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    selector     TEXT NOT NULL UNIQUE,
    validator    TEXT NOT NULL,
    created_at   TEXT DEFAULT (datetime('now')),
    last_used_at TEXT
);
CREATE TABLE IF NOT EXISTS passkeys (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    credential_id TEXT NOT NULL UNIQUE,
    public_key    TEXT NOT NULL,
    sign_count    INTEGER NOT NULL DEFAULT 0,
    rp_id         TEXT NOT NULL,
    label         TEXT,
    created_at    TEXT DEFAULT (datetime('now')),
    last_used_at  TEXT
);
SQL);
        // Databases created before these columns existed need them added.
        $cols = $pdo->query('PRAGMA table_info(filaments)')->fetchAll(PDO::FETCH_COLUMN, 1);
        if (!in_array('color_name', $cols, true)) {
            $pdo->exec('ALTER TABLE filaments ADD COLUMN color_name TEXT');
        }
        if (!in_array('color_hex2', $cols, true)) {
            $pdo->exec('ALTER TABLE filaments ADD COLUMN color_hex2 TEXT');
        }
        if (!in_array('original_g', $cols, true)) {
            $pdo->exec('ALTER TABLE filaments ADD COLUMN original_g REAL DEFAULT 1000');
        }
        foreach (['silk', 'matte', 'rainbow', 'transparent', 'twotone', 'sparkle', 'small_spool'] as $col) {
            if (!in_array($col, $cols, true)) {
                $pdo->exec("ALTER TABLE filaments ADD COLUMN $col INTEGER NOT NULL DEFAULT 0");
            }
        }
    }
    return $pdo;
}