<?php
// db/config.php
function db(): ?PDO {
    static $pdo = null;
    if ($pdo !== null) {
        return $pdo;
    }

    $host = getenv('DB_HOST') ?: '127.0.0.1';
    $port = getenv('DB_PORT') ?: '3306';
    $db   = getenv('DB_NAME') ?: 'app_35256';
    $user = getenv('DB_USER') ?: 'app_35256';
    $pass = getenv('DB_PASS') ?: '0297656a-e7ff-4e19-adc5-34bd6e744b8f';
    $charset = 'utf8mb4';

    $dsn = "mysql:host=$host;port=$port;dbname=$db;charset=$charset";
    $options = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ];

    try {
        $pdo = new PDO($dsn, $user, $pass, $options);
        run_migrations($pdo);
        return $pdo;
    } catch (\PDOException $e) {
        error_log($e->getMessage());
        return null;
    }
}

function run_migrations(PDO $pdo): void {
    $migrations_dir = __DIR__ . '/migrations';
    if (!is_dir($migrations_dir)) {
        return;
    }

    try {
        $pdot = $pdo->query("SHOW TABLES LIKE 'migrations'");
        $table_exists = $pdot->rowCount() > 0;

        if (!$table_exists) {
            $pdo->exec("CREATE TABLE migrations (
                id INT AUTO_INCREMENT PRIMARY KEY,
                migration VARCHAR(255) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
        }

        $stmt = $pdo->prepare("SELECT migration FROM migrations");
        $stmt->execute();
        $run_migrations = $stmt->fetchAll(PDO::FETCH_COLUMN);

        $migration_files = glob($migrations_dir . '/*.sql');
        foreach ($migration_files as $file) {
            $migration_name = basename($file);
            if (!in_array($migration_name, $run_migrations)) {
                $sql = file_get_contents($file);
                if ($sql === false) continue;
                $pdo->exec($sql);

                $stmt = $pdo->prepare("INSERT INTO migrations (migration) VALUES (?)");
                $stmt->execute([$migration_name]);
            }
        }
    } catch (\PDOException $e) {
        error_log("Migration failed: " . $e->getMessage());
    }
}