<?php
declare(strict_types=1);

/**
 * Migration runner sederhana untuk PHP native/cPanel.
 *
 * Cara pakai dari root project:
 *   php scripts/migrate.php status
 *   php scripts/migrate.php up
 *   php scripts/migrate.php up --legacy
 *   php scripts/migrate.php mark-all
 *   php scripts/migrate.php mark-all --legacy
 *
 * Catatan:
 * - Jalankan dulu di STAGING, bukan langsung production.
 * - Runner ini memakai database dari file .env.
 * - Folder legacy_sql tidak dijalankan kecuali memakai flag --legacy.
 */

if (PHP_SAPI !== 'cli') {
    http_response_code(403);
    echo "403 Forbidden\n";
    exit(1);
}

$root = dirname(__DIR__);
require_once $root . '/config/database.php';

$command = $argv[1] ?? 'status';
$includeLegacy = in_array('--legacy', $argv, true);
$allowed = ['status', 'up', 'mark-all'];

if (!in_array($command, $allowed, true)) {
    fwrite(STDERR, "Command tidak dikenal: {$command}\n");
    fwrite(STDERR, "Gunakan: status | up | mark-all [--legacy]\n");
    exit(1);
}

$pdo = getAppConnectionPDO();
$mysqli = getAppConnection();

ensureMigrationTable($pdo);
$applied = getAppliedMigrations($pdo);
$migrations = collectMigrations($root, $includeLegacy);

if ($command === 'status') {
    printStatus($migrations, $applied, $includeLegacy);
    exit(0);
}

if ($command === 'mark-all') {
    markAllMigrations($pdo, $migrations, $applied);
    echo "OK: semua migration yang ditemukan sudah ditandai applied tanpa dieksekusi.\n";
    exit(0);
}

runPendingMigrations($pdo, $mysqli, $migrations, $applied);
echo "OK: migration selesai.\n";

function ensureMigrationTable(PDO $pdo): void
{
    $sql = "CREATE TABLE IF NOT EXISTS `schema_migrations` (
        `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
        `migration` VARCHAR(255) NOT NULL,
        `checksum` CHAR(64) NOT NULL,
        `batch` INT UNSIGNED NOT NULL DEFAULT 1,
        `executed_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (`id`),
        UNIQUE KEY `uniq_schema_migrations_migration` (`migration`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    $pdo->exec($sql);
}

function getAppliedMigrations(PDO $pdo): array
{
    $rows = $pdo->query('SELECT migration, checksum FROM schema_migrations ORDER BY migration')->fetchAll();
    $applied = [];
    foreach ($rows as $row) {
        $applied[(string)$row['migration']] = (string)$row['checksum'];
    }
    return $applied;
}

function collectMigrations(string $root, bool $includeLegacy): array
{
    $base = $root . '/database/migrations';
    $paths = glob($base . '/*.sql') ?: [];

    if ($includeLegacy) {
        $legacyPaths = glob($base . '/legacy_sql/*.sql') ?: [];
        $paths = array_merge($paths, $legacyPaths);
    }

    sort($paths, SORT_NATURAL | SORT_FLAG_CASE);

    $migrations = [];
    foreach ($paths as $path) {
        $relative = str_replace($base . '/', '', $path);
        $sql = file_get_contents($path);
        if ($sql === false) {
            throw new RuntimeException("Tidak bisa membaca migration: {$relative}");
        }
        $migrations[$relative] = [
            'path' => $path,
            'checksum' => hash('sha256', $sql),
            'sql' => $sql,
        ];
    }

    return $migrations;
}

function printStatus(array $migrations, array $applied, bool $includeLegacy): void
{
    echo "Migration status" . ($includeLegacy ? " + legacy" : "") . "\n";
    echo str_repeat('-', 80) . "\n";

    if (!$migrations) {
        echo "Tidak ada file migration ditemukan.\n";
        return;
    }

    foreach ($migrations as $name => $item) {
        $status = array_key_exists($name, $applied) ? 'APPLIED' : 'PENDING';
        $changed = '';
        if ($status === 'APPLIED' && $applied[$name] !== $item['checksum']) {
            $changed = ' / CHECKSUM_CHANGED';
        }
        echo str_pad($status . $changed, 26) . $name . "\n";
    }
}

function markAllMigrations(PDO $pdo, array $migrations, array $applied): void
{
    $batch = getNextBatch($pdo);
    $stmt = $pdo->prepare('INSERT INTO schema_migrations (migration, checksum, batch) VALUES (?, ?, ?)');

    foreach ($migrations as $name => $item) {
        if (array_key_exists($name, $applied)) {
            continue;
        }
        $stmt->execute([$name, $item['checksum'], $batch]);
        echo "MARKED: {$name}\n";
    }
}

function runPendingMigrations(PDO $pdo, mysqli $mysqli, array $migrations, array $applied): void
{
    $batch = getNextBatch($pdo);
    $stmt = $pdo->prepare('INSERT INTO schema_migrations (migration, checksum, batch) VALUES (?, ?, ?)');

    foreach ($migrations as $name => $item) {
        if (array_key_exists($name, $applied)) {
            if ($applied[$name] !== $item['checksum']) {
                fwrite(STDERR, "PERINGATAN: checksum berubah untuk migration yang sudah applied: {$name}\n");
            }
            continue;
        }

        echo "RUN: {$name}\n";
        executeSqlFile($mysqli, $item['sql'], $name);
        $stmt->execute([$name, $item['checksum'], $batch]);
        echo "DONE: {$name}\n";
    }
}

function executeSqlFile(mysqli $mysqli, string $sql, string $name): void
{
    $sql = trim($sql);
    if ($sql === '') {
        return;
    }

    if (!$mysqli->multi_query($sql)) {
        throw new RuntimeException("Gagal menjalankan {$name}: " . $mysqli->error);
    }

    do {
        if ($result = $mysqli->store_result()) {
            $result->free();
        }
        if ($mysqli->more_results()) {
            $mysqli->next_result();
        } else {
            break;
        }
    } while (true);

    if ($mysqli->errno) {
        throw new RuntimeException("Gagal menjalankan {$name}: " . $mysqli->error);
    }
}

function getNextBatch(PDO $pdo): int
{
    $max = $pdo->query('SELECT COALESCE(MAX(batch), 0) AS max_batch FROM schema_migrations')->fetch();
    return ((int)($max['max_batch'] ?? 0)) + 1;
}
