<?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 = [];

    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)
     */
    public function hasColumn($table, $column)
    {
        $cacheKey = $table . '.' . $column;
        
        if (isset(self::$columnCache[$cacheKey])) {
            return self::$columnCache[$cacheKey];
        }
        
        try {
            $stmt = $this->pdo->prepare("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
                                      WHERE TABLE_SCHEMA = DATABASE() 
                                      AND TABLE_NAME = ? 
                                      AND COLUMN_NAME = ?");
            $stmt->execute([$table, $column]);
            $exists = $stmt->fetchColumn() > 0;
            
            self::$columnCache[$cacheKey] = $exists;
            return $exists;
        } catch (PDOException $e) {
            Logger::logError("Failed to check column existence", $e, [
                'table' => $table,
                'column' => $column
            ]);
            self::$columnCache[$cacheKey] = false;
            return false;
        }
    }

    /**
     * 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();
    }
}

