<?php

declare(strict_types=1);

/**
 * bin/migrate.php
 *
 * Usage: php bin/migrate.php [--fresh]
 *
 *   php bin/migrate.php          Applies any migration not yet recorded
 *                                 in schema_migrations, in filename order.
 *   php bin/migrate.php --fresh  Drops every table and re-applies all
 *                                 migrations from zero. NEVER run this
 *                                 against production.
 *
 * Deliberately a plain PDO script, not a framework artisan-style
 * command — this is the one tool a developer reaches for before the
 * app even boots, so it has zero dependency on the Router/Application
 * request-lifecycle code.
 */

require __DIR__ . '/../app/Core/Autoloader.php';

use App\Core\Autoloader;
use App\Core\Database;
use App\Core\Env;

Autoloader::register(dirname(__DIR__));
Env::load(dirname(__DIR__) . '/.env');

$fresh = in_array('--fresh', $argv, true);
$pdo = Database::connection();

if ($fresh) {
    echo "⚠  --fresh: dropping all tables...\n";
    $pdo->exec('SET FOREIGN_KEY_CHECKS = 0');
    $tables = $pdo->query('SHOW TABLES')->fetchAll(PDO::FETCH_COLUMN);
    foreach ($tables as $table) {
        $pdo->exec("DROP TABLE IF EXISTS `{$table}`");
    }
    $pdo->exec('SET FOREIGN_KEY_CHECKS = 1');
}

// schema_migrations itself is created by 0001_create_migration_tracker.sql,
// so the very first run has nothing to query yet — handle that gracefully.
$alreadyApplied = [];
try {
    $alreadyApplied = $pdo->query('SELECT migration FROM schema_migrations')->fetchAll(PDO::FETCH_COLUMN);
} catch (\PDOException) {
    // Table doesn't exist yet — fine, treat as "nothing applied."
}

$migrationsDir = dirname(__DIR__) . '/database/migrations';
$files = glob($migrationsDir . '/*.sql');
sort($files);

$batch = 1;
try {
    $maxBatch = $pdo->query('SELECT COALESCE(MAX(batch), 0) FROM schema_migrations')->fetchColumn();
    $batch = ((int) $maxBatch) + 1;
} catch (\PDOException) {
    // schema_migrations doesn't exist yet on a fresh database — this is batch 1.
}

$applied = 0;
foreach ($files as $file) {
    $name = basename($file);

    if (in_array($name, $alreadyApplied, true)) {
        continue;
    }

    echo "Applying {$name} ... ";

    $sql = file_get_contents($file);
    $pdo->exec($sql);

    $stmt = $pdo->prepare('INSERT INTO schema_migrations (migration, batch) VALUES (:m, :b)');
    $stmt->execute(['m' => $name, 'b' => $batch]);

    echo "done\n";
    $applied++;
}

echo $applied === 0
    ? "Nothing to migrate — database is up to date.\n"
    : "Applied {$applied} migration(s).\n";
