<?php
/* ============================================================
   db.php - PDO SQLite connection + schema bootstrap
   ============================================================ */

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

    $dataDir = dirname(DB_PATH);
    if (!is_dir($dataDir)) mkdir($dataDir, 0755, true);

    $pdo = new PDO('sqlite:' . DB_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("CREATE TABLE IF NOT EXISTS licenses (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        license_key TEXT UNIQUE NOT NULL,
        customer_name TEXT,
        customer_email TEXT,
        features TEXT NOT NULL DEFAULT '',
        max_activations INTEGER NOT NULL DEFAULT 1,
        status TEXT NOT NULL DEFAULT 'active',
        expires_at TEXT,
        notes TEXT,
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
    )");

    $pdo->exec("CREATE TABLE IF NOT EXISTS activations (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        license_id INTEGER NOT NULL,
        machine_id TEXT NOT NULL,
        activated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        last_seen_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(license_id, machine_id),
        FOREIGN KEY(license_id) REFERENCES licenses(id) ON DELETE CASCADE
    )");

    // Admin-created broadcasts (promos, announcements, manual reminders).
    // target_license_id = NULL means "send to every customer".
    $pdo->exec("CREATE TABLE IF NOT EXISTS notifications (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        body TEXT NOT NULL,
        type TEXT NOT NULL DEFAULT 'promo',
        link TEXT,
        image_url TEXT,
        accent_color TEXT,
        target_license_id INTEGER,
        created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY(target_license_id) REFERENCES licenses(id) ON DELETE CASCADE
    )");

    // Migration: add image_url to a notifications table created before
    // this column existed. Harmless no-op on a fresh install.
    try {
        $cols = $pdo->query("PRAGMA table_info(notifications)")->fetchAll();
        $hasImageUrl = false;
        foreach ($cols as $c) { if ($c['name'] === 'image_url') { $hasImageUrl = true; break; } }
        if (!$hasImageUrl) {
            $pdo->exec("ALTER TABLE notifications ADD COLUMN image_url TEXT");
        }
    } catch (Exception $e) { /* best effort */ }

    // Tracks which device has already seen which notification, so the
    // app doesn't show the same popup twice. notification_id is TEXT so
    // it can hold both real notification ids and the synthetic ids used
    // for auto-generated expiry warnings (e.g. "expiry-12-5").
    $pdo->exec("CREATE TABLE IF NOT EXISTS notification_reads (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        notification_id TEXT NOT NULL,
        machine_id TEXT NOT NULL,
        read_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(notification_id, machine_id)
    )");

    return $pdo;
}

function generateLicenseKey() {
    $raw = strtoupper(bin2hex(random_bytes(15))); // 30 hex chars
    return implode('-', str_split($raw, 5));
}

function findLicenseByKey($key) {
    // Normalize: license keys are looked up ignoring dashes, spaces and case,
    // so it doesn't matter whether the caller sends "AB12-CD34" or "ab12cd34".
    $stmt = db()->prepare(
        "SELECT * FROM licenses WHERE REPLACE(REPLACE(UPPER(license_key), '-', ''), ' ', '') = ?"
    );
    $normalized = strtoupper(preg_replace('/[\s-]/', '', (string) $key));
    $stmt->execute([$normalized]);
    return $stmt->fetch();
}

function activationCount($licenseId) {
    $stmt = db()->prepare('SELECT COUNT(*) c FROM activations WHERE license_id = ?');
    $stmt->execute([$licenseId]);
    return (int) $stmt->fetch()['c'];
}

// How many distinct devices have acknowledged (seen) a given notification.
function notificationReadCount($notificationId) {
    $stmt = db()->prepare('SELECT COUNT(DISTINCT machine_id) c FROM notification_reads WHERE notification_id = ?');
    $stmt->execute([(string) $notificationId]);
    return (int) $stmt->fetch()['c'];
}
