<?php

declare(strict_types=1);

namespace BotGuard\Core;

/**
 * Runs SQL migration files against a database connection.
 *
 * Shared by the CLI (bin/migrate.php) and the web installer so both apply the
 * exact same schema. Applied files are tracked in a `migrations` table, making
 * runs incremental and idempotent.
 */
final class Migrator
{
    public function __construct(private Database $db, private string $migrationsDir)
    {
    }

    /**
     * Apply all pending migrations. Returns the list of files applied this run.
     *
     * @return string[]
     */
    public function run(): array
    {
        $this->ensureTable();

        $applied = array_column($this->db->select('SELECT migration FROM `migrations`'), 'migration');
        $files = glob($this->migrationsDir . '/*.sql') ?: [];
        sort($files);

        $ran = [];
        foreach ($files as $file) {
            $name = basename($file);
            if (in_array($name, $applied, true)) {
                continue;
            }

            foreach ($this->splitStatements((string) file_get_contents($file)) as $statement) {
                $this->db->pdo()->exec($statement);
            }

            $this->db->insert('migrations', ['migration' => $name]);
            $ran[] = $name;
        }

        return $ran;
    }

    /**
     * Drop every table in the schema (used by --fresh / re-install).
     */
    public function dropAll(string $database): void
    {
        $tables = array_column(
            $this->db->select(
                'SELECT table_name AS t FROM information_schema.tables WHERE table_schema = ?',
                [$database]
            ),
            't'
        );

        if ($tables === []) {
            return;
        }

        $this->db->pdo()->exec('SET FOREIGN_KEY_CHECKS = 0');
        foreach ($tables as $table) {
            $this->db->pdo()->exec("DROP TABLE IF EXISTS `{$table}`");
        }
        $this->db->pdo()->exec('SET FOREIGN_KEY_CHECKS = 1');
    }

    private function ensureTable(): void
    {
        $this->db->run(
            'CREATE TABLE IF NOT EXISTS `migrations` (
                `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                `migration` VARCHAR(191) NOT NULL,
                `applied_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (`id`), UNIQUE KEY `uq_migration` (`migration`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'
        );
    }

    /**
     * Split a .sql file into individual statements on line-terminating
     * semicolons (the DDL here contains no semicolons inside literals).
     *
     * @return string[]
     */
    private function splitStatements(string $sql): array
    {
        $lines = preg_split('/\r?\n/', $sql) ?: [];
        $clean = array_filter($lines, static fn (string $l): bool => ! str_starts_with(trim($l), '--'));
        $sql = implode("\n", $clean);

        $parts = preg_split('/;\s*(?:\n|$)/', $sql) ?: [];

        return array_values(array_filter(array_map('trim', $parts), static fn (string $s): bool => $s !== ''));
    }
}
