<?php
namespace App\Core;

use PDO; 
use PDOException;

class Database
{
    private static ?PDO $pdo = null;

    public static function pdo(): PDO
    {
        if (self::$pdo === null) {
            $config = self::loadConfig();
            $dsn = sprintf('%s:host=%s;port=%d;dbname=%s;charset=%s',
                $config['driver'], $config['host'], $config['port'], $config['database'], $config['charset']
            );
            try {
                self::$pdo = new PDO($dsn, $config['username'], $config['password'], [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                ]);
                
                // Configuration du fuseau horaire MySQL pour correspondre à PHP
                $appConfig = @include __DIR__ . '/../../config/app.php';
                $timezone = $appConfig['timezone'] ?? 'Africa/Abidjan';
                
                // Convertir le fuseau PHP en décalage MySQL (ex: Africa/Abidjan -> +00:00)
                try {
                    $tz = new \DateTimeZone($timezone);
                    $dt = new \DateTime('now', $tz);
                    $offset = $tz->getOffset($dt);
                    $hours = floor($offset / 3600);
                    $minutes = abs(($offset % 3600) / 60);
                    $mysqlTimezone = sprintf('%+03d:%02d', $hours, $minutes);
                    self::$pdo->exec("SET time_zone = '$mysqlTimezone'");
                } catch (\Exception $e) {
                    // Fallback: ne rien faire si la conversion échoue
                }
            } catch (PDOException $e) {
                http_response_code(500);
                echo 'DB connection error: ' . htmlspecialchars($e->getMessage());
                exit;
            }
        }
        return self::$pdo;
    }

    private static function loadConfig(): array
    {
        $path = __DIR__ . '/../../config/db.php';
        if (!file_exists($path)) {
            $pathExample = __DIR__ . '/../../config/db.php.example';
            return include $pathExample;
        }
        return include $path;
    }
}