<?php
/**
 * Migration Runner
 *
 * @package ZenoChatbot\Framework\Database\Migration
 */

namespace ZenoChatbot\Framework\Database\Migration;

// Prevent direct access
if (!defined('ABSPATH')) {
    exit;
}

/**
 * Migration Runner Class
 */
class MigrationRunner
{
    /**
     * Migration table name
     *
     * @var string
     */
    protected $table = 'zenochatbot_migrations';

    /**
     * Migration paths
     *
     * @var array
     */
    protected $paths = [];

    /**
     * Migration namespace
     *
     * @var string
     */
    protected $namespace = 'ZenoChatbot\\Framework\\Database\\Migration\\Migrations\\';

    /**
     * Migration connection instance
     *
     * @var Connection
     */
    protected $connection;

    /**
     * Create a new migration runner instance
     */
    public function __construct()
    {
        $this->connection = new Connection();
        $this->ensureMigrationTableExists();
    }

    /**
     * Set migration paths
     *
     * @param array $paths
     * @return $this
     */
    public function setPaths(array $paths)
    {
        $this->paths = $paths;
        return $this;
    }

    /**
     * Add migration path
     *
     * @param string $path
     * @return $this
     */
    public function addPath($path)
    {
        $this->paths[] = $path;
        return $this;
    }

    /**
     * Set migration namespace
     *
     * @param string $namespace
     * @return $this
     */
    public function setNamespace($namespace)
    {
        $this->namespace = rtrim($namespace, '\\') . '\\';
        return $this;
    }

    /**
     * Run all pending migrations
     *
     * @return array
     */
    public function run()
    {
        $migrations = $this->getPendingMigrations();
        $executed = [];

        foreach ($migrations as $migration) {
            try {
                $this->runMigration($migration);
                $executed[] = $migration;
                $this->log("Migrated: {$migration}");
            } catch (\Exception $e) {
                $this->log("Migration failed: {$migration} - {$e->getMessage()}", 'error');
                throw $e;
            }
        }

        return $executed;
    }

    /**
     * Rollback the last batch of migrations
     *
     * @return array
     */
    public function rollback()
    {
        $batch = $this->getLastBatchNumber();
        return $this->rollbackBatch($batch);
    }

    /**
     * Rollback a specific batch of migrations
     *
     * @param int $batch
     * @return array
     */
    public function rollbackBatch($batch)
    {
        $migrations = $this->getMigrationsForBatch($batch);
        $rolledBack = [];

        // Rollback in reverse order
        foreach (array_reverse($migrations) as $migration) {
            try {
                $this->rollbackMigration($migration);
                $rolledBack[] = $migration;
                $this->log("Rolled back: {$migration}");
            } catch (\Exception $e) {
                $this->log("Rollback failed: {$migration} - {$e->getMessage()}", 'error');
                throw $e;
            }
        }

        return $rolledBack;
    }

    /**
     * Get all migration files
     *
     * @return array
     */
    public function getMigrationFiles()
    {
        $files = [];

        foreach ($this->paths as $path) {
            if (is_dir($path)) {
                $migrationFiles = glob($path . '/*.php');
                foreach ($migrationFiles as $file) {
                    $files[] = basename($file, '.php');
                }
            }
        }

        sort($files);
        return $files;
    }

    /**
     * Get pending migrations
     *
     * @return array
     */
    public function getPendingMigrations()
    {
        $allMigrations = $this->getMigrationFiles();
        $executedMigrations = $this->getExecutedMigrations();

        return array_diff($allMigrations, $executedMigrations);
    }

    /**
     * Get executed migrations
     *
     * @return array
     */
    public function getExecutedMigrations()
    {
        $table = $this->connection->getPrefix() . $this->table;
        $results = $this->connection->select(
            "SELECT migration FROM {$table} ORDER BY batch, migration"
        );

        return array_column($results, 'migration');
    }

    /**
     * Run a single migration using dbDelta
     *
     * @param string $migration
     * @return void
     */
    protected function runMigration($migration)
    {
        $instance = $this->loadMigration($migration);
        $sql = $instance->up();

        $this->executeSql($sql);
        $this->recordMigration($migration);
    }

    /**
     * Rollback a single migration using dbDelta
     *
     * @param string $migration
     * @return void
     */
    protected function rollbackMigration($migration)
    {
        $instance = $this->loadMigration($migration);
        $sql = $instance->down();

        $this->executeSql($sql);
        $this->removeMigrationRecord($migration);
    }

    /**
     * Execute SQL statement using dbDelta
     *
     * @param Sql $sql
     * @return void
     */
    protected function executeSql(Sql $sql)
    {
        if ($sql->isEmpty()) {
            return;
        }

        // Always use dbDelta for migrations
        $result = $this->connection->dbDelta($sql->getSql());

        if (empty($result)) {
            throw new \Exception("Migration execution failed - no result from dbDelta");
        }

        $this->log("Executed SQL: " . $sql->getFormattedSql());
    }

    /**
     * Load migration instance
     *
     * @param string $migration
     * @return Migration
     * @throws \Exception
     */
    protected function loadMigration($migration)
    {
        // Find migration file
        $file = $this->findMigrationFile($migration);
        if (!$file) {
            throw new \Exception("Migration file not found: {$migration}");
        }

        // Include the file
        require_once $file;

        // Extract class name from migration name
        $className = $this->getMigrationClassName($migration);

        if (!class_exists($className)) {
            throw new \Exception("Migration class not found: {$className}");
        }

        return new $className();
    }

    /**
     * Find migration file path
     *
     * @param string $migration
     * @return string|null
     */
    protected function findMigrationFile($migration)
    {
        foreach ($this->paths as $path) {
            $file = $path . '/' . $migration . '.php';
            if (file_exists($file)) {
                return $file;
            }
        }

        return null;
    }

    /**
     * Get migration class name from file name
     *
     * @param string $migration
     * @return string
     */
    protected function getMigrationClassName($migration)
    {
        // Convert file name to class name
        // e.g., "2024_01_15_000001_create_users_table" -> "CreateUsersTable"
        if (preg_match('/^\d{4}_\d{2}_\d{2}_\d{6}_(.+)$/', $migration, $matches)) {
            $name = $matches[1];
        } else {
            $name = $migration;
        }

        // Convert snake_case to PascalCase
        $className = str_replace(' ', '', ucwords(str_replace('_', ' ', $name)));

        return $this->namespace . $className;
    }

    /**
     * Record migration execution
     *
     * @param string $migration
     * @return void
     */
    protected function recordMigration($migration)
    {
        $batch = $this->getNextBatchNumber();

        $this->connection->insert($this->table, [
            'migration' => $migration,
            'batch' => $batch,
            'executed_at' => current_time('mysql')
        ]);
    }

    /**
     * Remove migration record
     *
     * @param string $migration
     * @return void
     */
    protected function removeMigrationRecord($migration)
    {
        $this->connection->delete($this->table, ['migration' => $migration]);
    }

    /**
     * Get next batch number
     *
     * @return int
     */
    protected function getNextBatchNumber()
    {
        $table = $this->connection->getPrefix() . $this->table;
        $result = $this->connection->selectOne("SELECT MAX(batch) as max_batch FROM {$table}");

        return $result && $result['max_batch'] ? $result['max_batch'] + 1 : 1;
    }

    /**
     * Get last batch number
     *
     * @return int
     */
    protected function getLastBatchNumber()
    {
        $table = $this->connection->getPrefix() . $this->table;
        $result = $this->connection->selectOne("SELECT MAX(batch) as max_batch FROM {$table}");

        return $result && $result['max_batch'] ? $result['max_batch'] : 0;
    }

    /**
     * Get migrations for a specific batch
     *
     * @param int $batch
     * @return array
     */
    protected function getMigrationsForBatch($batch)
    {
        $table = $this->connection->getPrefix() . $this->table;
        $results = $this->connection->select(
            "SELECT migration FROM {$table} WHERE batch = %d ORDER BY migration",
            [$batch]
        );

        return array_column($results, 'migration');
    }

    /**
     * Ensure migration table exists using dbDelta
     *
     * @return void
     */
    protected function ensureMigrationTableExists()
    {
        if (!$this->connection->tableExists($this->table)) {
            $table = $this->connection->getPrefix() . $this->table;
            $charset_collate = $this->connection->getCharsetCollate();

            $sql = "CREATE TABLE {$table} (
                id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                migration varchar(255) NOT NULL,
                batch int(11) NOT NULL,
                executed_at datetime DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                KEY migration (migration),
                KEY batch (batch)
            ) {$charset_collate};";

            $this->connection->dbDelta($sql);
        }
    }

    /**
     * Log migration message
     *
     * @param string $message
     * @param string $level
     * @return void
     */
    protected function log($message, $level = 'info')
    {
        if (defined('WP_DEBUG') && WP_DEBUG) {

        }
    }
}
