<?php
/**
 * Database — SQLite init + helpers
 */
require_once __DIR__ . '/config.php';

function getDB(): SQLite3 {
    static $db = null;
    if ($db === null) {
        $dir = dirname(DB_PATH);
        if (!is_dir($dir)) {
            mkdir($dir, 0755, true);
        }
        $db = new SQLite3(DB_PATH);
        $db->exec('PRAGMA journal_mode=WAL');
        $db->exec('PRAGMA foreign_keys=ON');
        _initTables($db);
    }
    return $db;
}

function _initTables(SQLite3 $db): void {
    $db->exec("
        CREATE TABLE IF NOT EXISTS orders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            token TEXT UNIQUE NOT NULL,
            order_number TEXT,
            status TEXT DEFAULT 'pending',
            vehicle_name TEXT DEFAULT '',
            vehicle_model TEXT DEFAULT '',
            vehicle_plate TEXT DEFAULT '',
            vehicle_city TEXT DEFAULT '',
            diagnosis_title TEXT DEFAULT '',
            diagnosis_desc TEXT DEFAULT '',
            diagnosis_verified INTEGER DEFAULT 1,
            whatsapp TEXT DEFAULT '',
            whatsapp_message TEXT DEFAULT '',
            validity_days INTEGER DEFAULT 7,
            warranty_days INTEGER DEFAULT 90,
            payment_info TEXT DEFAULT '',
            parts_total REAL DEFAULT 0,
            labor_total REAL DEFAULT 0,
            total REAL DEFAULT 0,
            created_at DATETIME DEFAULT (datetime('now','localtime')),
            updated_at DATETIME DEFAULT (datetime('now','localtime'))
        )
    ");

    $db->exec("
        CREATE TABLE IF NOT EXISTS order_items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            order_id INTEGER NOT NULL,
            type TEXT NOT NULL CHECK(type IN ('part','labor')),
            name TEXT DEFAULT '',
            code TEXT DEFAULT '',
            origin TEXT DEFAULT '',
            details TEXT DEFAULT '',
            quantity REAL DEFAULT 1,
            price REAL DEFAULT 0,
            FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
        )
    ");

    $db->exec("
        CREATE TABLE IF NOT EXISTS order_photos (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            order_id INTEGER NOT NULL,
            filename TEXT NOT NULL,
            label TEXT DEFAULT '',
            sort_order INTEGER DEFAULT 0,
            FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
        )
    ");

    $db->exec("
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password_hash TEXT NOT NULL
        )
    ");

    // Seed default admin
    $count = $db->querySingle("SELECT COUNT(*) FROM users");
    if ($count == 0) {
        $hash = password_hash(ADMIN_PASS, PASSWORD_DEFAULT);
        $u = SQLite3::escapeString(ADMIN_USER);
        $db->exec("INSERT INTO users (username, password_hash) VALUES ('$u', '$hash')");
    }
}

/* ── Helpers ─────────────────────────────────────────── */

function generateToken(): string {
    return bin2hex(random_bytes(16));
}

function getOrderByToken(string $token): ?array {
    $db = getDB();
    $t = SQLite3::escapeString($token);
    $row = $db->querySingle("SELECT * FROM orders WHERE token = '$t'", true);
    return $row ?: null;
}

function getOrderItems(int $orderId): array {
    $db = getDB();
    $res = $db->query("SELECT * FROM order_items WHERE order_id = $orderId ORDER BY type, id");
    $items = [];
    while ($row = $res->fetchArray(SQLITE3_ASSOC)) {
        $items[] = $row;
    }
    return $items;
}

function getOrderPhotos(int $orderId): array {
    $db = getDB();
    $res = $db->query("SELECT * FROM order_photos WHERE order_id = $orderId ORDER BY sort_order, id");
    $photos = [];
    while ($row = $res->fetchArray(SQLITE3_ASSOC)) {
        $photos[] = $row;
    }
    return $photos;
}

function getAllOrders(): array {
    $db = getDB();
    $res = $db->query("SELECT * FROM orders ORDER BY created_at DESC");
    $orders = [];
    while ($row = $res->fetchArray(SQLITE3_ASSOC)) {
        $orders[] = $row;
    }
    return $orders;
}

function deleteOrder(int $id): void {
    $db = getDB();
    // Delete photos from disk
    $photos = getOrderPhotos($id);
    foreach ($photos as $p) {
        $path = UPLOAD_DIR . '/' . $p['filename'];
        if (file_exists($path)) unlink($path);
    }
    $db->exec("DELETE FROM orders WHERE id = $id");
}