<?php
/**
 * ============================================================
 * DATABASE CONNECTION (PDO)
 * ============================================================
 * Singleton pattern for database connection using PDO
 * All queries use prepared statements for security
 */

class Database {
    private static ?PDO $instance = null;
    
    /**
     * Get PDO database instance (singleton)
     * @return PDO
     */
    public static function getInstance(): PDO {
        if (self::$instance === null) {
            try {
                $dsn = sprintf(
                    'mysql:host=%s;dbname=%s;charset=%s',
                    DB_HOST,
                    DB_NAME,
                    DB_CHARSET
                );
                
                $options = [
                    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES   => false,
                    PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"
                ];
                
                self::$instance = new PDO($dsn, DB_USER, DB_PASS, $options);
                
            } catch (PDOException $e) {
                if (APP_DEBUG) {
                    die('Database Connection Error: ' . $e->getMessage());
                } else {
                    die('Database connection failed. Please try again later.');
                }
            }
        }
        
        return self::$instance;
    }
    
    /**
     * Get database connection (alias for getInstance)
     * @return PDO
     */
    public static function getConnection(): PDO {
        return self::getInstance();
    }
    
    /**
     * Execute a SELECT query and return all results
     * @param string $sql
     * @param array $params
     * @return array
     */
    public static function fetchAll(string $sql, array $params = []): array {
        $stmt = self::getInstance()->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }
    
    /**
     * Execute a SELECT query and return single row
     * @param string $sql
     * @param array $params
     * @return array|null
     */
    public static function fetchOne(string $sql, array $params = []): ?array {
        $stmt = self::getInstance()->prepare($sql);
        $stmt->execute($params);
        $result = $stmt->fetch();
        return $result ?: null;
    }
    
    /**
     * Execute a SELECT query and return single value
     * @param string $sql
     * @param array $params
     * @return mixed
     */
    public static function fetchColumn(string $sql, array $params = []) {
        $stmt = self::getInstance()->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchColumn();
    }
    
    /**
     * Execute INSERT/UPDATE/DELETE query
     * @param string $sql
     * @param array $params
     * @return int Number of affected rows
     */
    public static function execute(string $sql, array $params = []): int {
        $stmt = self::getInstance()->prepare($sql);
        $stmt->execute($params);
        return $stmt->rowCount();
    }
    
    /**
     * Execute INSERT and return last inserted ID
     * @param string $sql
     * @param array $params
     * @return int
     */
    public static function insert(string $sql, array $params = []): int {
        $stmt = self::getInstance()->prepare($sql);
        $stmt->execute($params);
        return (int) self::getInstance()->lastInsertId();
    }
    
    /**
     * Begin a transaction
     */
    public static function beginTransaction(): void {
        self::getInstance()->beginTransaction();
    }
    
    /**
     * Commit a transaction
     */
    public static function commit(): void {
        self::getInstance()->commit();
    }
    
    /**
     * Rollback a transaction
     */
    public static function rollback(): void {
        self::getInstance()->rollBack();
    }
    
    /**
     * Check if currently in a transaction
     * @return bool
     */
    public static function inTransaction(): bool {
        return self::getInstance()->inTransaction();
    }
    
    // Prevent cloning and unserialization
    private function __construct() {}
    private function __clone() {}
    public function __wakeup() {
        throw new Exception("Cannot unserialize singleton");
    }
}

/**
 * Shorthand function to get PDO instance
 * @return PDO
 */
function db(): PDO {
    return Database::getInstance();
}
