<?php
namespace App\Database;

use PDO;
use PDOException;
use App\Utils\Logger;

/**
 * Database Connection Class
 * Manages MySQL PDO connection
 */
class Connection
{
    private static $instance = null;
    private $pdo;
    private static $columnCache = [];
    // PERF: full column-name set per table, fetched once with a single SHOW COLUMNS
    // instead of one INFORMATION_SCHEMA query per (table, column) probe.
    private static $tableColumns = [];

    private function __construct()
    {
        $config = require __DIR__ . '/../config/database.php';
        
        $dsn = sprintf(
            'mysql:host=%s;dbname=%s;charset=%s',
            $config['host'],
            $config['database'],
            $config['charset']
        );

        try {
            $this->pdo = new PDO($dsn, $config['username'], $config['password'], $config['options']);
            
            // Enable foreign key checks for MySQL
            $this->pdo->exec('SET FOREIGN_KEY_CHECKS = 1');
            
            // Logger::logInfo("Database connection established", [
            //     'host' => $config['host'],
            //     'database' => $config['database']
            // ]);
        } catch (PDOException $e) {
            Logger::logError("Database connection failed", $e);
            throw new \Exception("خطا در اتصال به دیتابیس. لطفاً با مدیر سیستم تماس بگیرید.");
        }
    }

    /**
     * Get singleton instance
     */
    public static function getInstance()
    {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    /**
     * Get PDO instance
     */
    public function getPdo()
    {
        return $this->pdo;
    }

    /**
     * Check if a column exists in a table (MySQL version).
     *
     * PERF: instead of one INFORMATION_SCHEMA query per column (hot paths like
     * DealRepository::save() probe 20+ columns per request), the full column set for a
     * table is loaded once via SHOW COLUMNS and cached for the process. When APCu is
     * available the set is also cached across requests (5 min TTL) to remove the lookup
     * entirely after warm-up. The schema is code-defined and changes only via deliberate
     * migrations, so this cache is safe.
     */
    public function hasColumn($table, $column)
    {
        $cacheKey = $table . '.' . $column;
        if (isset(self::$columnCache[$cacheKey])) {
            return self::$columnCache[$cacheKey];
        }

        $columns = $this->getTableColumns($table);
        $exists = isset($columns[$column]);
        self::$columnCache[$cacheKey] = $exists;
        return $exists;
    }

    /**
     * Return the set of column names for a table as an associative map (name => true),
     * loaded once per process (and per request via APCu when available).
     */
    public function getTableColumns($table)
    {
        if (isset(self::$tableColumns[$table])) {
            return self::$tableColumns[$table];
        }

        $apcuKey = null;
        if (function_exists('apcu_enabled') && apcu_enabled()) {
            $apcuKey = 'saleos_cols_' . $table;
            $cached = apcu_fetch($apcuKey, $ok);
            if ($ok && is_array($cached)) {
                self::$tableColumns[$table] = $cached;
                return $cached;
            }
        }

        $columns = [];
        try {
            // Table name can't be parameterized; quote/escape the backtick-safe identifier.
            $safeTable = '`' . str_replace('`', '``', (string)$table) . '`';
            $stmt = $this->pdo->query("SHOW COLUMNS FROM {$safeTable}");
            foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
                if (isset($row['Field'])) {
                    $columns[$row['Field']] = true;
                }
            }
        } catch (PDOException $e) {
            Logger::logError("Failed to load table columns", $e, ['table' => $table]);
            // Cache the empty set for the process to avoid hammering a missing/locked table.
        }

        self::$tableColumns[$table] = $columns;
        if ($apcuKey !== null) {
            apcu_store($apcuKey, $columns, 300);
        }
        return $columns;
    }

    /**
     * Invalidate the cached column metadata. Call after migrations alter the schema so the
     * rest of the request sees newly-added columns instead of the pre-migration snapshot.
     */
    public static function flushColumnCache()
    {
        // Delete any cross-request APCu copies for tables we've touched, otherwise a stale
        // pre-migration column set would be re-read after the in-process cache is cleared.
        if (function_exists('apcu_enabled') && apcu_enabled() && function_exists('apcu_delete')) {
            foreach (array_keys(self::$tableColumns) as $table) {
                apcu_delete('saleos_cols_' . $table);
            }
        }
        self::$columnCache = [];
        self::$tableColumns = [];
    }

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

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

    /**
     * Rollback transaction
     */
    public function rollback()
    {
        return $this->pdo->rollBack();
    }
}

