<?php

declare(strict_types=1);

namespace Instinctor\Core;

/**
 * Schema Migration Runner
 *
 * Applies versioned migrations from includes/migrations/*.php in filename order.
 * Tracks applied migrations in instinctor_migrations table.
 * Each migration file returns an array:
 *
 *   [
 *       'description' => 'Human-readable summary',
 *       'up'          => ['SQL...', 'SQL...'],   // array of statements to apply
 *       'down'        => ['SQL...', 'SQL...'],   // array of statements to reverse
 *       'php'         => function() { ... },     // OPTIONAL — runs after 'up' SQL
 *   ]
 *
 * SQL statements may use {prefix} and {charset} placeholders.
 * The optional 'php' callback runs once after all 'up' SQL succeeds —
 * use it for data migrations that need real PHP (JSON column rewrites,
 * cross-row transformations, etc.). Throws on failure to halt the run.
 *
 * On WordPress:  called from Plugin::handleUpgrade() on version change.
 * On Standalone: called from bootstrap/deploy entry point.
 */

if (!defined('ABSPATH')) exit;
class Migration
{
    private const TABLE    = 'instinctor_migrations';
    private const MAX_DOWN = 50; // safety cap for rollback

    // -------------------------------------------------------------------------
    // Public API
    // -------------------------------------------------------------------------

    /**
     * Apply all pending migrations in order.
     * Safe to call on every boot — skips already-applied migrations.
     */
    public static function run(): void
    {
        self::ensureTable();

        $applied = self::getApplied();
        $all     = self::getAllFiles();
        $pending = array_diff($all, $applied);

        if (empty($pending)) {
            return;
        }

        $batch = self::getNextBatch();

        foreach ($pending as $file) {
            $migration = self::load($file);
            if ($migration === null) {
                continue;
            }

            $up   = $migration['up'] ?? [];
            $sqls = is_array($up) ? $up : [$up];

            try {
                foreach ($sqls as $sql) {
                    $sql = trim($sql);
                    if ($sql === '') {
                        continue;
                    }
                    DB::query(self::interpolate($sql));

                    if (DB::last_error() !== '') {
                        throw new \RuntimeException(DB::last_error());
                    }
                }

                // Optional PHP data-migration callback. Runs once after all 'up'
                // SQL has succeeded. Throws on failure → caught below to halt
                // further migrations (same blast radius as a SQL error).
                if (isset($migration['php']) && is_callable($migration['php'])) {
                    call_user_func($migration['php']);
                }

                DB::insert(DB::prefix() . self::TABLE, [
                    'migration'  => $file,
                    'batch'      => $batch,
                    'applied_at' => gmdate('Y-m-d H:i:s'),
                ]);

            } catch (\Throwable $e) {
                // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
                error_log(sprintf(
                    '[Instinctor] Migration failed: %s, %s',
                    $file,
                    $e->getMessage()
                ));
                // Stop processing further migrations in this run
                return;
            }
        }
    }

    /**
     * Roll back the last $n batches (dev/CLI use only).
     */
    public static function rollback(int $n = 1): void
    {
        self::ensureTable();

        $table    = DB::prefix() . self::TABLE;
        $maxBatch = (int) DB::get_var("SELECT MAX(batch) FROM {$table}");

        if ($maxBatch === 0) {
            return;
        }

        $targetBatch = max(0, $maxBatch - $n + 1);

        $rows = DB::get_results(DB::prepare(
            "SELECT migration FROM {$table} WHERE batch >= %d ORDER BY batch DESC, id DESC LIMIT %d",
            $targetBatch,
            self::MAX_DOWN
        ));

        if (empty($rows)) {
            return;
        }

        foreach ($rows as $row) {
            $migration = self::load($row->migration);
            if ($migration === null) {
                continue;
            }

            $sqls = is_array($migration['down']) ? $migration['down'] : [$migration['down']];

            foreach ($sqls as $sql) {
                $sql = trim($sql);
                if ($sql === '') {
                    continue;
                }
                DB::query(self::interpolate($sql));
            }

            DB::delete($table, ['migration' => $row->migration]);
        }
    }

    /**
     * Return list of applied migration filenames.
     *
     * @return string[]
     */
    public static function getApplied(): array
    {
        self::ensureTable();
        $table = DB::prefix() . self::TABLE;
        $rows  = DB::get_results("SELECT migration FROM {$table} ORDER BY id ASC");
        return $rows ? array_column($rows, 'migration') : [];
    }

    /**
     * Return list of pending (not yet applied) migration filenames.
     *
     * @return string[]
     */
    public static function getPending(): array
    {
        return array_diff(self::getAllFiles(), self::getApplied());
    }

    // -------------------------------------------------------------------------
    // Private helpers
    // -------------------------------------------------------------------------

    /**
     * Create the migrations tracking table if it does not exist.
     * Uses direct CREATE TABLE — no dependency on dbDelta.
     */
    private static function ensureTable(): void
    {
        $table   = DB::prefix() . self::TABLE;
        $charset = DB::get_charset_collate();

        DB::query("CREATE TABLE IF NOT EXISTS {$table} (
            id          int unsigned NOT NULL AUTO_INCREMENT,
            migration   varchar(255) NOT NULL,
            batch       int unsigned NOT NULL,
            applied_at  datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY migration (migration)
        ) {$charset}");
    }

    /**
     * Sorted list of all migration filenames (basename only, e.g. "0001_initial_schema.php").
     *
     * @return string[]
     */
    private static function getAllFiles(): array
    {
        $dir   = self::migrationsPath();
        $files = glob($dir . '*.php');

        if (!$files) {
            return [];
        }

        $names = array_map('basename', $files);
        sort($names); // lexicographic = numeric order for 0001_, 0002_, ...
        return $names;
    }

    /**
     * Load and validate a migration file. Returns null on failure.
     *
     * @return array{description:string,up:string|string[],down:string|string[]}|null
     */
    private static function load(string $filename): ?array
    {
        $path = self::migrationsPath() . $filename;

        if (!file_exists($path)) {
            // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
            error_log("[Instinctor] Migration file not found: {$path}");
            return null;
        }

        $migration = require $path;

        // Accept migrations that have either schema SQL ('up') or a data
        // callback ('php'). Pure-data migrations declare `'up' => []` and
        // do all their work in the callback.
        $hasUp  = !empty($migration['up']);
        $hasPhp = isset($migration['php']) && is_callable($migration['php']);
        if (!is_array($migration) || (!$hasUp && !$hasPhp)) {
            // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
            error_log("[Instinctor] Invalid migration format: {$filename}");
            return null;
        }

        return $migration;
    }

    /**
     * Replace {prefix} and {charset} placeholders in a SQL statement.
     */
    private static function interpolate(string $sql): string
    {
        return str_replace(
            ['{prefix}', '{charset}'],
            [DB::prefix(), DB::get_charset_collate()],
            $sql
        );
    }

    /**
     * Next batch number (max applied + 1, or 1 if none applied yet).
     */
    private static function getNextBatch(): int
    {
        $table = DB::prefix() . self::TABLE;
        return (int) DB::get_var("SELECT MAX(batch) FROM {$table}") + 1;
    }

    /**
     * Absolute path to the migrations directory (with trailing slash).
     */
    private static function migrationsPath(): string
    {
        return __DIR__ . '/../migrations/';
    }
}
