<?php

declare(strict_types=1);

namespace Lab;

use PDO;

final class Database
{
    private static ?PDO $pdo = null;

    public static function connection(string $path): PDO
    {
        if (self::$pdo instanceof PDO) {
            return self::$pdo;
        }
        $directory = dirname($path);
        if (! is_dir($directory)) {
            mkdir($directory, 0777, true);
        }
        @chmod($directory, 0777);
        if (is_file($path)) {
            @chmod($path, 0666);
        }
        self::$pdo = new PDO('sqlite:' . $path, null, null, [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]);
        @chmod($path, 0666);
        self::$pdo->exec('PRAGMA foreign_keys = ON');
        self::migrate(self::$pdo);
        self::migrateColumns(self::$pdo);

        return self::$pdo;
    }

    public static function seedProducts(PDO $pdo, array $config): void
    {
        $insert = $pdo->prepare(
            'INSERT OR IGNORE INTO products (sku, name, width_cm, height_cm, price_cents, sort, active)
             VALUES (:sku, :name, :width_cm, :height_cm, :price_cents, :sort, 1)'
        );
        foreach ($config['products'] ?? [] as $product) {
            $insert->execute([
                'sku' => (string) $product['sku'],
                'name' => (string) $product['name'],
                'width_cm' => (int) $product['width_cm'],
                'height_cm' => (int) $product['height_cm'],
                'price_cents' => (int) $product['price_cents'],
                'sort' => (int) $product['sort'],
            ]);
        }
        $pdo->exec("UPDATE products SET active = 0 WHERE sku = 'a4'");
    }

    private static function migrate(PDO $pdo): void
    {
        $pdo->exec(<<<'SQL'
            CREATE TABLE IF NOT EXISTS products (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                sku TEXT NOT NULL UNIQUE,
                name TEXT NOT NULL,
                width_cm INTEGER NOT NULL,
                height_cm INTEGER NOT NULL,
                price_cents INTEGER NOT NULL,
                sort INTEGER NOT NULL DEFAULT 0,
                active INTEGER NOT NULL DEFAULT 1
            );

            CREATE TABLE IF NOT EXISTS orders (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                code TEXT NOT NULL UNIQUE,
                channel TEXT NOT NULL,
                status TEXT NOT NULL DEFAULT 'new',
                customer_name TEXT NOT NULL,
                customer_phone TEXT NOT NULL,
                customer_email TEXT,
                store_id TEXT,
                delivery TEXT NOT NULL DEFAULT 'pickup',
                address_line TEXT,
                notes TEXT,
                total_cents INTEGER NOT NULL DEFAULT 0,
                items_json TEXT NOT NULL,
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                exported_at TEXT
            );

            CREATE TABLE IF NOT EXISTS order_photos (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                order_id INTEGER NOT NULL,
                photo_key TEXT NOT NULL,
                original_name TEXT NOT NULL,
                stored_name TEXT NOT NULL,
                width INTEGER NOT NULL DEFAULT 0,
                height INTEGER NOT NULL DEFAULT 0,
                bytes INTEGER NOT NULL DEFAULT 0,
                FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
            );

            CREATE TABLE IF NOT EXISTS licenses (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                code TEXT NOT NULL UNIQUE,
                note TEXT,
                revoked INTEGER NOT NULL DEFAULT 0,
                device_id TEXT,
                device_name TEXT,
                token TEXT,
                activated_at TEXT,
                last_seen TEXT,
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
            );
        SQL);
    }

    private static function migrateColumns(PDO $pdo): void
    {
        $cols = $pdo->query('PRAGMA table_info(orders)')->fetchAll();
        $names = array_column($cols, 'name');
        $add = [
            'paper' => "ALTER TABLE orders ADD COLUMN paper TEXT NOT NULL DEFAULT 'glossy'",
            'border' => "ALTER TABLE orders ADD COLUMN border TEXT NOT NULL DEFAULT 'none'",
            'rush' => 'ALTER TABLE orders ADD COLUMN rush INTEGER NOT NULL DEFAULT 0',
            'payment' => "ALTER TABLE orders ADD COLUMN payment TEXT NOT NULL DEFAULT 'counter'",
        ];
        foreach ($add as $col => $sql) {
            if (! in_array($col, $names, true)) {
                $pdo->exec($sql);
            }
        }
    }
}
