<?php
/**
 * SQLite Database Wrapper using PDO
 */
class Database {
    private static ?PDO $instance = null;

    public static function getInstance(): PDO {
        if (self::$instance === null) {
            $dbPath = defined('DB_PATH') ? DB_PATH : __DIR__ . '/../data/database.sqlite';
            $dir = dirname($dbPath);
            if (!is_dir($dir)) {
                mkdir($dir, 0755, true);
            }
            self::$instance = new PDO("sqlite:$dbPath");
            self::$instance->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            self::$instance->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
            self::$instance->exec("PRAGMA journal_mode=WAL");
            self::$instance->exec("PRAGMA foreign_keys=ON");
        }
        return self::$instance;
    }

    public static function initSchema(): void {
        $db = self::getInstance();
        $db->exec("
            CREATE TABLE IF NOT EXISTS PhotoProject (
                id TEXT PRIMARY KEY,
                title TEXT NOT NULL,
                status TEXT DEFAULT 'pending',
                createdAt TEXT DEFAULT (datetime('now')),
                updatedAt TEXT DEFAULT (datetime('now'))
            );

            CREATE TABLE IF NOT EXISTS PhotoEdit (
                id TEXT PRIMARY KEY,
                `order` INTEGER NOT NULL,
                toolType TEXT NOT NULL,
                toolLabel TEXT NOT NULL,
                prompt TEXT NOT NULL,
                originalPath TEXT NOT NULL,
                editedPath TEXT,
                analysis TEXT,
                photoProjectId TEXT NOT NULL,
                createdAt TEXT DEFAULT (datetime('now')),
                FOREIGN KEY (photoProjectId) REFERENCES PhotoProject(id) ON DELETE CASCADE
            );

            CREATE TABLE IF NOT EXISTS Admin (
                id TEXT PRIMARY KEY,
                username TEXT UNIQUE NOT NULL,
                password TEXT NOT NULL,
                createdAt TEXT DEFAULT (datetime('now'))
            );

            CREATE TABLE IF NOT EXISTS Banner (
                id TEXT PRIMARY KEY,
                adType TEXT DEFAULT 'image',
                imageUrl TEXT DEFAULT '',
                adCode TEXT DEFAULT '',
                linkUrl TEXT DEFAULT '',
                text TEXT DEFAULT '',
                active INTEGER DEFAULT 0,
                position TEXT DEFAULT 'top',
                createdAt TEXT DEFAULT (datetime('now')),
                updatedAt TEXT DEFAULT (datetime('now'))
            );
        ");
    }
}