<?php
declare(strict_types=1);

namespace App\Database;

use PDO;
use PDOStatement;
use RuntimeException;
use Throwable;

final class Migrator
{
    public function __construct(
        private readonly PDO $pdo,
        private readonly string $migrationsDirectory,
    ) {
    }

    public function migrate(): array
    {
        $this->ensureSchemaTable();

        $applied = array_fill_keys($this->applied(), true);
        $appliedThisRun = [];

        foreach ($this->migrationFiles() as $file) {
            $filename = basename($file);

            if (isset($applied[$filename])) {
                continue;
            }

            foreach ($this->statementsFor($file) as $index => $statement) {
                try {
                    $this->executeStatement($statement);
                } catch (Throwable $throwable) {
                    if ($this->isHarmlessMissingPreparedStatementDeallocate($statement, $throwable)) {
                        continue;
                    }
                    throw new RuntimeException(
                        sprintf(
                            'Migration %s failed at statement %d: %s',
                            $filename,
                            $index + 1,
                            $throwable->getMessage()
                        ),
                        0,
                        $throwable
                    );
                }
            }

            $record = $this->pdo->prepare(
                'INSERT INTO schema_migrations (filename, applied_at) VALUES (:filename, NOW())'
            );
            $record->execute(['filename' => $filename]);

            $appliedThisRun[] = $filename;
            $applied[$filename] = true;
        }

        return $appliedThisRun;
    }


    private function isHarmlessMissingPreparedStatementDeallocate(string $statement, Throwable $throwable): bool
    {
        if (!str_starts_with(strtoupper(ltrim($statement)), 'DEALLOCATE PREPARE')) {
            return false;
        }

        // MySQL/MariaDB error 1243 means the SQL-level prepared statement no
        // longer exists. On a forward-migration rerun this can happen after the
        // preceding PREPARE/EXECUTE work already completed. DEALLOCATE is cleanup
        // only, so treating this one specific server error as successful makes a
        // partially applied migration recoverable without hiding DDL failures.
        if ($throwable instanceof \PDOException) {
            $errorInfo = $throwable->errorInfo ?? null;
            if (is_array($errorInfo) && (int) ($errorInfo[1] ?? 0) === 1243) {
                return true;
            }
        }

        return str_contains($throwable->getMessage(), '1243')
            && stripos($throwable->getMessage(), 'prepared statement') !== false;
    }

    private function executeStatement(string $statement): void
    {
        $trimmed = ltrim($statement);
        $verb = strtoupper((string) strtok($trimmed, " \t\r\n"));

        // PDO::exec() cannot consume result sets. That matters for SQL-level EXECUTE
        // because an idempotent migration may PREPARE a SELECT/SHOW no-op branch.
        // If that result is left pending, MariaDB/MySQL rejects the following
        // DEALLOCATE with error 2014. Run result-producing statements through a
        // cursor and drain every rowset before continuing.
        if (in_array($verb, ['SELECT', 'SHOW', 'DESCRIBE', 'EXPLAIN', 'EXECUTE'], true)) {
            $restoreEmulation = null;
            if ($verb === 'EXECUTE') {
                try {
                    $restoreEmulation = (bool) $this->pdo->getAttribute(PDO::ATTR_EMULATE_PREPARES);
                    // MySQL documents that SQL PREPARE/EXECUTE/DEALLOCATE syntax cannot
                    // itself be nested inside the binary prepared-statement API. Emulated
                    // mode makes PDO send this SQL-level EXECUTE as a direct statement.
                    $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
                } catch (Throwable) {
                    $restoreEmulation = null;
                }
            }

            try {
                $cursor = $this->pdo->query($statement);
                if ($cursor instanceof PDOStatement) {
                    do {
                        $cursor->fetchAll(PDO::FETCH_NUM);
                    } while ($cursor->nextRowset());
                    $cursor->closeCursor();
                }
            } finally {
                if ($restoreEmulation !== null) {
                    try {
                        $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, $restoreEmulation);
                    } catch (Throwable) {
                    }
                }
            }
            return;
        }

        $this->pdo->exec($statement);
    }


    /**
     * Re-apply explicitly approved idempotent migrations without rewriting
     * schema_migrations. Used only by the database reconciliation service.
     *
     * @param list<string> $filenames
     * @return list<string>
     */
    public function reapply(array $filenames): array
    {
        $requested = array_fill_keys(array_values(array_map('basename', $filenames)), true);
        $reapplied = [];

        foreach ($this->migrationFiles() as $file) {
            $filename = basename($file);
            if (!isset($requested[$filename])) continue;

            foreach ($this->statementsFor($file) as $index => $statement) {
                try {
                    $this->executeStatement($statement);
                } catch (Throwable $throwable) {
                    if ($this->isHarmlessMissingPreparedStatementDeallocate($statement, $throwable)) continue;
                    throw new RuntimeException(
                        sprintf('Repair migration %s failed at statement %d: %s', $filename, $index + 1, $throwable->getMessage()),
                        0,
                        $throwable
                    );
                }
            }
            $reapplied[] = $filename;
        }

        return $reapplied;
    }

    public function applied(): array
    {
        $this->ensureSchemaTable();

        return $this->pdo->query(
            'SELECT filename FROM schema_migrations ORDER BY id ASC'
        )->fetchAll(PDO::FETCH_COLUMN);
    }

    private function ensureSchemaTable(): void
    {
        $this->pdo->exec(
            'CREATE TABLE IF NOT EXISTS schema_migrations (
                id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                filename VARCHAR(255) NOT NULL,
                applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                UNIQUE KEY uniq_schema_migrations_filename (filename)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
        );
    }

    private function migrationFiles(): array
    {
        if (!is_dir($this->migrationsDirectory)) {
            throw new RuntimeException(sprintf('Migration directory not found: %s', $this->migrationsDirectory));
        }

        $files = glob($this->migrationsDirectory . DIRECTORY_SEPARATOR . '*.sql');

        if ($files === false) {
            throw new RuntimeException('Unable to read migration files.');
        }

        sort($files, SORT_STRING);

        return $files;
    }

    private function statementsFor(string $file): array
    {
        $sql = file_get_contents($file);

        if ($sql === false) {
            throw new RuntimeException(sprintf('Unable to read migration file: %s', $file));
        }

        $sql = preg_replace('/^\xEF\xBB\xBF/', '', $sql);
        $parts = preg_split('/;\s*(?:\r?\n|$)/', trim($sql));

        if ($parts === false) {
            throw new RuntimeException(sprintf('Unable to parse migration file: %s', $file));
        }

        $statements = [];

        foreach ($parts as $part) {
            $statement = trim($part);

            if ($statement === '') {
                continue;
            }

            $statements[] = $statement;
        }

        if ($statements === []) {
            throw new RuntimeException(sprintf('Migration file is empty: %s', $file));
        }

        return $statements;
    }
}
