<?php

declare(strict_types=1);

namespace Notifier;

use PDO;
use PDOException;
use PDOStatement;

/**
 * Database wrapper that enforces SQLite best practices.
 *
 * CRITICAL: This class ensures foreign keys, WAL mode, and busy timeout
 * are always enabled. Do NOT create PDO connections outside this class.
 */
class Database
{
    private PDO $pdo;
    private static ?self $instance = null;

    public function __construct(string $path)
    {
        $isNew = !file_exists($path);

        $this->pdo = new PDO("sqlite:{$path}");
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

        // CRITICAL: These PRAGMAs must run on every connection
        $this->pdo->exec('PRAGMA foreign_keys = ON');
        $this->pdo->exec('PRAGMA busy_timeout = 5000');
        $this->pdo->exec('PRAGMA journal_mode = WAL');

        // Verify foreign keys are enabled (paranoia check)
        $fkStatus = $this->pdo->query('PRAGMA foreign_keys')->fetchColumn();
        if ($fkStatus != 1) {
            throw new PDOException('Failed to enable foreign keys');
        }
    }

    /**
     * Get singleton instance (uses config path).
     */
    public static function getInstance(): self
    {
        if (self::$instance === null) {
            $config = require __DIR__ . '/../config.php';
            self::$instance = new self($config['database']['path']);
        }
        return self::$instance;
    }

    /**
     * Get the raw PDO instance (for transactions, etc.).
     */
    public function getPdo(): PDO
    {
        return $this->pdo;
    }

    /**
     * Execute a query and return the statement.
     */
    public function query(string $sql, array $params = []): PDOStatement
    {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }

    /**
     * Execute a statement and return affected row count.
     */
    public function execute(string $sql, array $params = []): int
    {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->rowCount();
    }

    /**
     * Fetch all rows from a query.
     */
    public function fetchAll(string $sql, array $params = []): array
    {
        return $this->query($sql, $params)->fetchAll();
    }

    /**
     * Fetch a single row from a query.
     */
    public function fetchOne(string $sql, array $params = []): ?array
    {
        $result = $this->query($sql, $params)->fetch();
        return $result === false ? null : $result;
    }

    /**
     * Fetch a single column value from a query.
     */
    public function fetchColumn(string $sql, array $params = []): mixed
    {
        return $this->query($sql, $params)->fetchColumn();
    }

    /**
     * Get the last inserted row ID.
     */
    public function lastInsertId(): int
    {
        return (int) $this->pdo->lastInsertId();
    }

    /**
     * Begin a transaction.
     */
    public function beginTransaction(): bool
    {
        return $this->pdo->beginTransaction();
    }

    /**
     * Commit a transaction.
     */
    public function commit(): bool
    {
        return $this->pdo->commit();
    }

    /**
     * Roll back a transaction.
     */
    public function rollBack(): bool
    {
        return $this->pdo->rollBack();
    }

    /**
     * Check if in a transaction.
     */
    public function inTransaction(): bool
    {
        return $this->pdo->inTransaction();
    }

    /**
     * Run a callback within a transaction.
     */
    public function transaction(callable $callback): mixed
    {
        $this->beginTransaction();
        try {
            $result = $callback($this);
            $this->commit();
            return $result;
        } catch (\Throwable $e) {
            $this->rollBack();
            throw $e;
        }
    }

    /**
     * Get database info for debugging.
     */
    public function getInfo(): array
    {
        return [
            'foreign_keys' => $this->pdo->query('PRAGMA foreign_keys')->fetchColumn(),
            'journal_mode' => $this->pdo->query('PRAGMA journal_mode')->fetchColumn(),
            'busy_timeout' => $this->pdo->query('PRAGMA busy_timeout')->fetchColumn(),
        ];
    }
}
