<?php
/**
 * Database Configuration and Connection Class
 * For Bus Check-in System API
 */

class Database {
    private $host;
    private $db_name;
    private $username;
    private $password;
    private $conn;

    public function __construct() {
        // Load database configuration from environment variables or config file
        $this->loadDatabaseConfig();
    }

    /**
     * Load database configuration securely from .env
     */
    private function loadDatabaseConfig() {
        // Load from .env file (most secure)
        $this->host = getenv('DB_HOST') ?: '127.0.0.1';
        $this->db_name = getenv('DB_NAME') ?: 'transubd_PointageBus';
        $this->username = getenv('DB_USER') ?: 'transubd_transub_user';

        // Handle encrypted password from .env
        $encryptedPassword = getenv('DB_PASS_ENCRYPTED');
        if ($encryptedPassword) {
            // Password is encrypted in .env
            require_once __DIR__ . '/../middleware/DataEncryption.php';
            $this->password = DataEncryption::decrypt($encryptedPassword);
        } else {
            // Fallback to plain text for compatibility
            $this->password = getenv('DB_PASS') ?: 'Tr@nsub2@24';
        }
    }

    /**
     * Get configuration value from secure config file
     */
    private function getConfigValue($key, $default = null) {
        $configFile = __DIR__ . '/../config/database_secure.php';

        if (file_exists($configFile)) {
            $config = include $configFile;
            return $config[$key] ?? $default;
        }

        return $default;
    }

    // Get database connection
    public function getConnection() {
        $this->conn = null;

        try {
            $this->conn = new PDO(
                "mysql:host=" . $this->host . ";dbname=" . $this->db_name,
                $this->username,
                $this->password
            );
            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
            $this->conn->exec("SET NAMES utf8");
            // Set MySQL timezone to match PHP timezone (Europe/Paris UTC+1)
            $this->conn->exec("SET time_zone = '+01:00'");
        } catch(PDOException $exception) {
            error_log("Database connection error: " . $exception->getMessage());
            throw new Exception("Database connection failed");
        }

        return $this->conn;
    }

    // Close database connection
    public function closeConnection() {
        $this->conn = null;
    }

    // Test database connection
    public function testConnection() {
        try {
            $this->getConnection();
            return ["success" => true, "message" => "Database connected successfully"];
        } catch(Exception $e) {
            return ["success" => false, "message" => $e->getMessage()];
        }
    }

    // Get current MySQL server time
    public function getCurrentTime($conn = null) {
        try {
            // Use existing connection if provided, otherwise create new one
            $useExistingConnection = $conn !== null;
            if (!$useExistingConnection) {
                $conn = $this->getConnection();
            }

            $stmt = $conn->prepare("SELECT NOW() as current_time");
            $stmt->execute();
            $result = $stmt->fetch(PDO::FETCH_ASSOC);

            // Close connection if we created it
            if (!$useExistingConnection) {
                $this->closeConnection();
            }

            return $result['current_time'];
        } catch(Exception $e) {
            // Fallback to PHP time if database is unavailable
            return date('Y-m-d H:i:s');
        }
    }
}
?>
