<?php

namespace KeyLair;

use PDOException;

/**
 * Lets an admin apply pending files from sql/migrations/ from the backend
 * instead of needing SSH + mysql access. Tracks what's already been applied
 * in schema_migrations, which this class creates itself on first use so
 * upgrading to this feature doesn't need its own migration file.
 *
 * schema_migrations.source distinguishes two very different reasons a
 * filename can be in this table: 'install' means the setup wizard marked it
 * as already-covered because schema.sql already included everything it adds
 * (see setup.php's kv_mark_migrations_applied()) — no one actually ran it
 * here. 'manual' means an admin genuinely ran it (or explicitly marked it
 * applied) via this class. Both count identically for pending() — either way
 * the database already has the change, so it must never be offered as
 * "Pending" again. But only 'manual' rows are real history worth showing in
 * the Applied list; a fresh install's Applied list should read empty, not a
 * copy of every migration filename that ever existed.
 */
class Migrator
{
    private static function ensureTrackingTable(): void
    {
        Database::connection()->exec(
            'CREATE TABLE IF NOT EXISTS schema_migrations (
                filename   VARCHAR(190) NOT NULL PRIMARY KEY,
                applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                source     VARCHAR(16) NOT NULL DEFAULT \'manual\'
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'
        );
    }

    /** @return string[] filenames under sql/migrations/, sorted. */
    private static function availableFiles(): array
    {
        $dir = dirname(__DIR__) . '/sql/migrations';
        if (!is_dir($dir)) {
            return [];
        }
        $files = array_values(array_filter(scandir($dir), fn ($f) => str_ends_with($f, '.sql')));
        sort($files);
        return $files;
    }

    /** @return string[] every filename recorded in schema_migrations, regardless of source — the full "already covered" set used for the pending diff. */
    private static function allRecordedFilenames(): array
    {
        self::ensureTrackingTable();
        $rows = Database::connection()->query('SELECT filename FROM schema_migrations')->fetchAll();
        return array_column($rows, 'filename');
    }

    /** @return array<string, string> filename => applied_at, newest first, for migrations actually run through this class (source = 'manual') — excludes filenames the setup wizard only marked as pre-covered by schema.sql. */
    public static function applied(): array
    {
        self::ensureTrackingTable();
        try {
            $rows = Database::connection()->query(
                "SELECT filename, applied_at FROM schema_migrations WHERE source = 'manual' ORDER BY applied_at DESC, filename DESC"
            )->fetchAll();
        } catch (PDOException $e) {
            // An install from before the 'source' column existed (migration
            // 014 hasn't run yet here). Every row is unclassified at this
            // point, so show them all rather than crash the very page an
            // admin needs in order to run that migration.
            $rows = Database::connection()->query(
                'SELECT filename, applied_at FROM schema_migrations ORDER BY applied_at DESC, filename DESC'
            )->fetchAll();
        }
        return array_column($rows, 'applied_at', 'filename');
    }

    /** @return string[] filenames not yet recorded as applied (by any source), in order. */
    public static function pending(): array
    {
        $recorded = self::allRecordedFilenames();
        return array_values(array_diff(self::availableFiles(), $recorded));
    }

    /**
     * Records a migration as applied without running its SQL — for a file
     * that was already run by hand (e.g. over SSH, before this page
     * existed, or to recover from a partial failure fixed manually).
     *
     * @return array{ok: bool, error?: string}
     */
    public static function markApplied(string $filename): array
    {
        self::ensureTrackingTable();

        if (!in_array($filename, self::availableFiles(), true)) {
            return ['ok' => false, 'error' => 'Unknown migration file.'];
        }
        if (in_array($filename, self::allRecordedFilenames(), true)) {
            return ['ok' => false, 'error' => 'Already applied.'];
        }

        self::recordApplied($filename);

        return ['ok' => true];
    }

    /**
     * Inserts the applied-migration row, tolerating a table that predates
     * the 'source' column (migration 014 not run here yet) the same way
     * applied() does — falls back to the old two-column insert instead of
     * failing every other migration until 014 specifically is run first.
     */
    private static function recordApplied(string $filename): void
    {
        try {
            $stmt = Database::connection()->prepare("INSERT INTO schema_migrations (filename, applied_at, source) VALUES (:filename, NOW(), 'manual')");
            $stmt->execute(['filename' => $filename]);
        } catch (PDOException $e) {
            $stmt = Database::connection()->prepare('INSERT INTO schema_migrations (filename, applied_at) VALUES (:filename, NOW())');
            $stmt->execute(['filename' => $filename]);
        }
    }

    /**
     * Runs one migration file and records it as applied. Not transactional
     * across statements — DDL like CREATE/ALTER TABLE causes an implicit
     * commit in MySQL anyway, so a mid-file failure can leave some
     * statements applied. The error message says exactly which statement
     * failed so that's diagnosable.
     *
     * @return array{ok: bool, error?: string}
     */
    public static function run(string $filename): array
    {
        self::ensureTrackingTable();

        if (!in_array($filename, self::availableFiles(), true)) {
            return ['ok' => false, 'error' => 'Unknown migration file.'];
        }
        if (in_array($filename, self::allRecordedFilenames(), true)) {
            return ['ok' => false, 'error' => 'Already applied.'];
        }

        $path = dirname(__DIR__) . '/sql/migrations/' . $filename;
        $sql = file_get_contents($path);

        // Same comment-stripping approach as the setup wizard's schema
        // import: strip inline "-- ..." comments line by line BEFORE
        // splitting on ';', since a semicolon inside a comment would
        // otherwise be mistaken for a statement boundary.
        $cleanLines = [];
        foreach (explode("\n", $sql) as $line) {
            $line = preg_replace('/--\s.*$/', '', $line);
            if (trim($line) !== '') {
                $cleanLines[] = $line;
            }
        }
        $statements = array_filter(array_map('trim', explode(';', implode("\n", $cleanLines))));

        $pdo = Database::connection();
        foreach ($statements as $statement) {
            if ($statement === '') {
                continue;
            }
            try {
                $pdo->exec($statement);
            } catch (PDOException $e) {
                return ['ok' => false, 'error' => $e->getMessage() . ' (in statement: ' . substr($statement, 0, 120) . '...)'];
            }
        }

        self::recordApplied($filename);

        return ['ok' => true];
    }
}
