<?php
// CoreFlux/core/CLI/Commands/DbFluxCommand.php

namespace Core\CLI\Commands;

use Core\CLI\Command;
use Core\Database\Connection;
use Core\Database\Schema;
use Core\Database\MigrationOperation;
use Exception;

class DbFluxCommand implements Command
{
    public function getName(): string
    {
        return 'db:flux';
    }

    public function getDescription(): string
    {
        return 'Applies or rolls back database migrations.';
    }

    public function execute(array $args): int
    {
        echo PHP_EOL . PHP_EOL . "Applying CoreFlux database changes...\n";

        // --- 1. Define Core Paths (CRITICAL for CLI) ---
        // Since CLI scripts run from the project root, paths must be handled carefully.
        if (!defined('ROOT_PATH')) {
            define('ROOT_PATH', dirname(dirname(dirname(dirname(__DIR__)))));
        }
        if (!defined('FS_CONFIG_PATH')) {
            define('FS_CONFIG_PATH', ROOT_PATH . DIRECTORY_SEPARATOR . 'app' . DIRECTORY_SEPARATOR . 'config');
        }
        if (!defined('FS_STORAGE_PATH')) {
            define('FS_STORAGE_PATH', ROOT_PATH . DIRECTORY_SEPARATOR . 'storage');
        }

        try {
            // --- 2. Initialize the Database Connection ---
            $databaseConfig = require FS_CONFIG_PATH . DIRECTORY_SEPARATOR . 'database.php';
            Connection::init($databaseConfig);

            $this->ensureMigrationsTable();

            $migrationsDir = FS_STORAGE_PATH . DIRECTORY_SEPARATOR . 'migrations';
            if (!is_dir($migrationsDir)) {
                echo PHP_EOL . PHP_EOL . "Warning: Migrations directory '{$migrationsDir}' not found. No migrations to run.\n";
                return 0;
            }

            // --- 3. Check for rollback command ---
            $command = $args[0] ?? 'migrate'; // Default is 'migrate'
            if ($command === 'rollback') {
                $step = $args[1] ?? 1; // Default to rolling back 1 migration
                $this->rollbackMigrations($migrationsDir, $step);
                return 0;
            }

            // --- 4. Apply pending migrations ---
            $appliedMigrations = $this->getAppliedMigrations();
            $pendingMigrations = $this->getPendingMigrations($migrationsDir, $appliedMigrations);

            if (empty($pendingMigrations)) {
                echo PHP_EOL . PHP_EOL . "Info: No pending migrations to apply.\n";
                return 0;
            }

            foreach ($pendingMigrations as $migrationFile) {
                $migrationPath = $migrationsDir . DIRECTORY_SEPARATOR . $migrationFile;
                echo PHP_EOL . PHP_EOL . "Running migration: {$migrationFile} (up)\n";

                $migrationInstance = require $migrationPath;

                if (!($migrationInstance instanceof MigrationOperation)) {
                    echo PHP_EOL . PHP_EOL . "Error: Migration file '{$migrationFile}' did not return a valid MigrationOperation.\n";
                    return 1;
                }

                $migrationInstance->up();
                $this->markMigrationAsApplied($migrationFile);

                echo "Migration {$migrationFile} (up) completed.\n";
            }

            echo "All pending migrations applied successfully.\n";
            return 0;

        } catch (Exception $e) {
            echo PHP_EOL . PHP_EOL . "Error fluxing: " . $e->getMessage() . "\n";
            return 1;
        }
    }

    /**
     * Rolls back the last N migrations.
     * @param string $migrationsDir The directory containing migration files.
     * @param int $steps The number of migrations to roll back.
     */
    private function rollbackMigrations(string $migrationsDir, int $steps): void
    {
        echo PHP_EOL . PHP_EOL . "Rolling back the last {$steps} migrations...\n";

        $lastAppliedMigrations = $this->getLastAppliedMigrations($steps);
        if (empty($lastAppliedMigrations)) {
            echo "Info: No migrations have been applied yet. Nothing to rollback.\n";
            return;
        }
        
        foreach ($lastAppliedMigrations as $migrationRow) {
            $migrationFile = $migrationRow['migration'];
            $migrationPath = $migrationsDir . DIRECTORY_SEPARATOR . $migrationFile;

            if (!file_exists($migrationPath)) {
                echo "Error: Migration file '{$migrationFile}' not found for rollback. Skipping.\n";
                continue;
            }
            
            echo "Rolling back: {$migrationFile} (down)\n";

            $migrationInstance = require $migrationPath;

            if (!($migrationInstance instanceof MigrationOperation)) {
                echo "Error: Migration file '{$migrationFile}' did not return a valid MigrationOperation.\n";
                continue;
            }
            
            try {
                $migrationInstance->down();
                $this->markMigrationAsRolledBack($migrationFile);
                echo "Rollback of {$migrationFile} completed successfully.\n";
            } catch (Exception $e) {
                echo "Error during rollback of '{$migrationFile}': " . $e->getMessage() . "\n";
                echo "Rollback process halted.\n";
                return; // Stop the rollback process if one fails
            }
        }
    }

    /**
     * Gets the last N applied migrations from the database.
     * @param int $steps The number of migrations to retrieve.
     * @return array The list of migrations.
     */
    private function getLastAppliedMigrations(int $steps): array
    {
        if (!Schema::tableExists('migrations')) {
            return [];
        }
        $sql = "SELECT migration FROM migrations ORDER BY id DESC LIMIT {$steps}";
        return Connection::fetchAll($sql);
    }
    
    /**
     * Marks a migration as rolled back in the database by deleting its record.
     * @param string $migrationFile The filename of the migration.
     */
    private function markMigrationAsRolledBack(string $migrationFile): void
    {
        Connection::execute("DELETE FROM migrations WHERE migration = ?", [$migrationFile]);
    }
    
    // --- Helper methods (ensureMigrationsTable, getAppliedMigrations, etc.) ---
    // These methods remain largely the same as before.

    /**
     * Ensures the 'migrations' table exists to track applied migrations.
     */
    private function ensureMigrationsTable(): void
    {
        $tableName = 'migrations';
        if (!Schema::tableExists($tableName)) {
            Schema::createTable($tableName, [
                'id' => 'INT PRIMARY KEY AUTO_INCREMENT',
                'migration' => 'VARCHAR(255) NOT NULL UNIQUE',
                'applied_at' => 'DATETIME DEFAULT CURRENT_TIMESTAMP'
            ]);
            echo "Info: '{$tableName}' table created.\n";
        } else {
            echo "Info: '{$tableName}' table ensured.\n";
        }
    }

    /**
     * Retrieves a list of migrations already applied to the database.
     * @return array An array of applied migration filenames.
     */
    private function getAppliedMigrations(): array
    {
        if (!Schema::tableExists('migrations')) {
            return [];
        }

        $rows = Connection::fetchAll("SELECT migration FROM migrations ORDER BY id ASC");
        return array_column($rows, 'migration');
    }

    /**
     * Retrieves a list of migration files that are pending (not yet applied).
     * @param string $migrationsDir The directory containing migration files.
     * @param array $appliedMigrations An array of already applied migration filenames.
     * @return array An array of pending migration filenames.
     */
    private function getPendingMigrations(string $migrationsDir, array $appliedMigrations): array
    {
        $allMigrationFiles = glob($migrationsDir . DIRECTORY_SEPARATOR . '*.php');
        sort($allMigrationFiles);

        $pending = [];
        foreach ($allMigrationFiles as $filePath) {
            $fileName = basename($filePath);
            if (!in_array($fileName, $appliedMigrations)) {
                $pending[] = $fileName;
            }
        }
        return $pending;
    }

    /**
     * Marks a migration as applied in the database.
     * @param string $migrationFile The filename of the migration to mark.
     */
    private function markMigrationAsApplied(string $migrationFile): void
    {
        Connection::execute(
            "INSERT INTO migrations (migration, applied_at) VALUES (?, ?)",
            [$migrationFile, date('Y-m-d H:i:s')]
        );
    }
}