<?php
namespace App\Core;

use PDO;

class DB
{
    private static ?PDO $pdo = null;

    public static function get(): PDO
    {
        if (self::$pdo === null) {
            $dbDir = ROOT_PATH . '/data';
            if (!is_dir($dbDir)) {
                mkdir($dbDir, 0755, true);
            }
            $dbFile = $dbDir . '/sfiles.sqlite';
            self::$pdo = new PDO("sqlite:{$dbFile}", null, null, [
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            ]);
            self::$pdo->exec('PRAGMA journal_mode=WAL');
            self::$pdo->exec('PRAGMA foreign_keys=ON');
        }
        return self::$pdo;
    }

    public static function init(): void
    {
        $pdo = self::get();

        // Users table
        $pdo->exec("CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            password_hash TEXT NOT NULL,
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            updated_at TEXT NOT NULL DEFAULT (datetime('now'))
        )");

        // Folders table
        $pdo->exec("CREATE TABLE IF NOT EXISTS folders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            parent_id INTEGER DEFAULT NULL,
            shared_token TEXT UNIQUE,
            shared_enabled INTEGER NOT NULL DEFAULT 0,
            trashed_at TEXT DEFAULT NULL,
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            updated_at TEXT NOT NULL DEFAULT (datetime('now')),
            FOREIGN KEY (parent_id) REFERENCES folders(id) ON DELETE CASCADE
        )");

        // Files table with folder_id and trashed_at
        $pdo->exec("CREATE TABLE IF NOT EXISTS files (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            folder_id INTEGER DEFAULT NULL,
            object_key TEXT NOT NULL UNIQUE,
            original_name TEXT NOT NULL,
            display_name TEXT NOT NULL,
            size_bytes INTEGER NOT NULL DEFAULT 0,
            mime_type TEXT NOT NULL DEFAULT 'application/octet-stream',
            created_at TEXT NOT NULL DEFAULT (datetime('now')),
            updated_at TEXT NOT NULL DEFAULT (datetime('now')),
            shared_token TEXT UNIQUE,
            shared_enabled INTEGER NOT NULL DEFAULT 0,
            trashed_at TEXT DEFAULT NULL,
            FOREIGN KEY (folder_id) REFERENCES folders(id) ON DELETE SET NULL
        )");

        // Migration: add columns if missing (for existing databases)
        self::addColumnIfMissing($pdo, 'files', 'folder_id', 'INTEGER DEFAULT NULL');
        self::addColumnIfMissing($pdo, 'files', 'trashed_at', 'TEXT DEFAULT NULL');

        // Seed user default
        $stmt = $pdo->query("SELECT COUNT(*) as c FROM users");
        $count = $stmt->fetch()['c'];
        if ($count == 0) {
            $hash = password_hash(CONFIG['DEFAULT_PASSWORD'], PASSWORD_BCRYPT);
            $ins = $pdo->prepare("INSERT INTO users (username, password_hash) VALUES (?, ?)");
            $ins->execute([CONFIG['DEFAULT_USERNAME'], $hash]);
        }
    }

    private static function addColumnIfMissing(PDO $pdo, string $table, string $column, string $definition): void
    {
        $cols = $pdo->query("PRAGMA table_info({$table})")->fetchAll();
        $exists = false;
        foreach ($cols as $col) {
            if ($col['name'] === $column) { $exists = true; break; }
        }
        if (!$exists) {
            $pdo->exec("ALTER TABLE {$table} ADD COLUMN {$column} {$definition}");
        }
    }
}
