<?php

declare(strict_types=1);

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Schema;
use Symfony\Component\Finder\Finder;

class AuditMigrationSchemaCommand extends Command
{
    protected $signature = 'schema:audit-migrations
                            {--all-files : Audit all discovered migration files, not only migrations marked as ran}
                            {--json : Output the report as JSON}
                            {--base-path=* : Additional base paths to scan for migrations, relative to project root}';

    protected $description = 'Compare the live database schema against executed migration files and report missing tables or columns';

    private const NO_ARG_COLUMN_METHODS = [
        'id' => ['id'],
        'timestamps' => ['created_at', 'updated_at'],
        'timestampsTz' => ['created_at', 'updated_at'],
        'softDeletes' => ['deleted_at'],
        'softDeletesTz' => ['deleted_at'],
        'rememberToken' => ['remember_token'],
    ];

    private const NON_COLUMN_METHODS = [
        'index',
        'unique',
        'primary',
        'foreign',
        'foreignIdFor',
        'dropColumn',
        'dropColumns',
        'dropIndex',
        'dropUnique',
        'dropPrimary',
        'dropForeign',
        'dropConstrainedForeignId',
        'renameColumn',
        'renameIndex',
        'constrained',
        'cascadeOnDelete',
        'restrictOnDelete',
        'nullOnDelete',
        'cascadeOnUpdate',
        'restrictOnUpdate',
        'nullOnUpdate',
        'after',
        'nullable',
        'default',
        'useCurrent',
        'useCurrentOnUpdate',
        'comment',
        'charset',
        'collation',
        'storedAs',
        'virtualAs',
        'generatedAs',
        'always',
        'invisible',
        'first',
    ];

    public function handle(): int
    {
        $migrationFiles = $this->discoverMigrationFiles();
        $ranMigrations = collect(DB::table('migrations')->pluck('migration')->all())
            ->mapWithKeys(fn (string $migration): array => [$migration => true])
            ->all();

        $expected = [];
        $parsedFiles = 0;

        foreach ($migrationFiles as $migrationName => $path) {
            if (! $this->option('all-files') && ! isset($ranMigrations[$migrationName])) {
                continue;
            }

            $parsedFiles++;
            $operations = $this->parseMigrationFile($path);

            foreach ($operations as $operation) {
                $table = $operation['table'];

                if ($operation['type'] === 'create-table' && isset($expected[$table])) {
                    $expected[$table]['sources'][$migrationName] = true;

                    if (Schema::hasTable($table)) {
                        $actualColumns = array_fill_keys(
                            array_map('strtolower', Schema::getColumnListing($table)),
                            true
                        );

                        $existingColumns = array_fill_keys(array_keys($expected[$table]['columns']), true);
                        $replacementColumns = [];

                        foreach ($operation['add'] as $column) {
                            if (! isset($existingColumns[$column]) && isset($actualColumns[$column])) {
                                $replacementColumns[$column] = true;
                            }
                        }

                        if ($replacementColumns !== []) {
                            foreach ($replacementColumns as $column => $_) {
                                $expected[$table]['columns'][$column] = true;
                            }

                            foreach (array_keys($expected[$table]['columns']) as $column) {
                                if (! isset($actualColumns[$column]) && ! isset($replacementColumns[$column])) {
                                    unset($expected[$table]['columns'][$column]);
                                }
                            }
                        }
                    }

                    continue;
                }

                if ($operation['type'] === 'alter-table' && ! isset($expected[$table]) && ! Schema::hasTable($table)) {
                    continue;
                }

                $expected[$table] ??= [
                    'columns' => [],
                    'sources' => [],
                ];

                $expected[$table]['sources'][$migrationName] = true;

                if ($operation['type'] === 'drop-table') {
                    unset($expected[$table]);
                    continue;
                }

                foreach ($operation['add'] as $column) {
                    $expected[$table]['columns'][$column] = true;
                }

                foreach ($operation['drop'] as $column) {
                    unset($expected[$table]['columns'][$column]);
                }
            }
        }

        ksort($expected);

        $report = [
            'parsed_files' => $parsedFiles,
            'expected_tables' => count($expected),
            'missing_tables' => [],
            'missing_columns' => [],
        ];

        foreach ($expected as $table => $spec) {
            $sources = array_keys($spec['sources']);
            sort($sources);

            if (! Schema::hasTable($table)) {
                $report['missing_tables'][] = [
                    'table' => $table,
                    'sources' => $sources,
                ];
                continue;
            }

            $actualColumns = array_map('strtolower', Schema::getColumnListing($table));
            $expectedColumns = array_keys($spec['columns']);
            sort($expectedColumns);

            $missing = array_values(array_diff($expectedColumns, $actualColumns));
            if ($missing !== []) {
                $report['missing_columns'][] = [
                    'table' => $table,
                    'columns' => $missing,
                    'sources' => $sources,
                ];
            }
        }

        if ((bool) $this->option('json')) {
            $this->line(json_encode($report, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES));
        } else {
            $this->renderReport($report);
        }

        return ($report['missing_tables'] === [] && $report['missing_columns'] === [])
            ? self::SUCCESS
            : self::FAILURE;
    }

    /**
     * @return array<string, string>
     */
    private function discoverMigrationFiles(): array
    {
        $paths = [
            database_path('migrations'),
            base_path('packages/klevze'),
        ];

        foreach ((array) $this->option('base-path') as $relativePath) {
            $resolved = base_path((string) $relativePath);
            if (is_dir($resolved)) {
                $paths[] = $resolved;
            }
        }

        $finder = new Finder();
        $finder->files()->name('*.php');

        foreach ($paths as $path) {
            if (is_dir($path)) {
                $finder->in($path);
            }
        }

        $files = [];
        foreach ($finder as $file) {
            $realPath = $file->getRealPath();
            if (! $realPath) {
                continue;
            }

            $normalized = str_replace('\\', '/', $realPath);
            if (! str_contains($normalized, '/database/migrations/') && ! str_contains($normalized, '/Migrations/')) {
                continue;
            }

            $files[pathinfo($realPath, PATHINFO_FILENAME)] = $realPath;
        }

        ksort($files);

        return $files;
    }

    /**
     * @return array<int, array{type:string, table:string, add:array<int,string>, drop:array<int,string>}>
     */
    private function parseMigrationFile(string $path): array
    {
        $content = File::get($path);
        $upBody = $this->extractMethodBody($content, 'up');
        if ($upBody === null) {
            return [];
        }

        $operations = [];

        foreach ($this->extractSchemaClosures($upBody) as $closure) {
            $operations[] = [
                'type' => $closure['operation'],
                'table' => $closure['table'],
                'add' => $this->extractAddedColumns($closure['body']),
                'drop' => $this->extractDroppedColumns($closure['body']),
            ];
        }

        if (preg_match_all("/Schema::drop(?:IfExists)?\(\s*['\"]([^'\"]+)['\"]\s*\)/", $upBody, $matches)) {
            foreach ($matches[1] as $table) {
                $operations[] = [
                    'type' => 'drop-table',
                    'table' => strtolower((string) $table),
                    'add' => [],
                    'drop' => [],
                ];
            }
        }

        foreach ($this->extractRawAlterTableChanges($upBody) as $change) {
            $operations[] = [
                'type' => 'alter-table',
                'table' => $change['table'],
                'add' => [$change['new_column']],
                'drop' => [$change['old_column']],
            ];
        }

        return $operations;
    }

    /**
     * @return array<int, array{table:string, old_column:string, new_column:string}>
     */
    private function extractRawAlterTableChanges(string $upBody): array
    {
        $changes = [];

        if (preg_match_all(
            '/ALTER\s+TABLE\s+[`"]?([^`"\s]+)[`"]?\s+CHANGE(?:\s+COLUMN)?\s+[`"]?([^`"\s]+)[`"]?\s+[`"]?([^`"\s]+)[`"]?/i',
            $upBody,
            $matches,
            PREG_SET_ORDER
        )) {
            foreach ($matches as $match) {
                $oldColumn = strtolower((string) $match[2]);
                $newColumn = strtolower((string) $match[3]);

                if ($oldColumn === $newColumn) {
                    continue;
                }

                $changes[] = [
                    'table' => strtolower((string) $match[1]),
                    'old_column' => $oldColumn,
                    'new_column' => $newColumn,
                ];
            }
        }

        return $changes;
    }

    private function extractMethodBody(string $content, string $method): ?string
    {
        if (! preg_match('/function\s+' . preg_quote($method, '/') . '\s*\([^)]*\)\s*(?::\s*[^{]+)?\s*\{/m', $content, $match, PREG_OFFSET_CAPTURE)) {
            return null;
        }

        $start = $match[0][1] + strlen($match[0][0]) - 1;
        $end = $this->findMatchingBrace($content, $start);

        if ($end === null) {
            return null;
        }

        return substr($content, $start + 1, $end - $start - 1);
    }

    private function findMatchingBrace(string $content, int $openingBracePos): ?int
    {
        $length = strlen($content);
        $depth = 0;
        $inSingle = false;
        $inDouble = false;

        for ($index = $openingBracePos; $index < $length; $index++) {
            $char = $content[$index];
            $prev = $index > 0 ? $content[$index - 1] : '';

            if ($char === "'" && ! $inDouble && $prev !== '\\') {
                $inSingle = ! $inSingle;
                continue;
            }

            if ($char === '"' && ! $inSingle && $prev !== '\\') {
                $inDouble = ! $inDouble;
                continue;
            }

            if ($inSingle || $inDouble) {
                continue;
            }

            if ($char === '{') {
                $depth++;
                continue;
            }

            if ($char === '}') {
                $depth--;
                if ($depth === 0) {
                    return $index;
                }
            }
        }

        return null;
    }

    /**
     * @return array<int, array{operation:string, table:string, body:string}>
     */
    private function extractSchemaClosures(string $upBody): array
    {
        preg_match_all('/Schema::(create|table)\(\s*[\'\"]([^\'\"]+)[\'\"]\s*,\s*function/s', $upBody, $matches, PREG_OFFSET_CAPTURE);

        $closures = [];
        foreach ($matches[0] as $index => $fullMatch) {
            $offset = (int) $fullMatch[1];
            $operation = strtolower((string) $matches[1][$index][0]) === 'create' ? 'create-table' : 'alter-table';
            $table = strtolower((string) $matches[2][$index][0]);

            $bracePos = strpos($upBody, '{', $offset);
            if ($bracePos === false) {
                continue;
            }

            $closing = $this->findMatchingBrace($upBody, $bracePos);
            if ($closing === null) {
                continue;
            }

            $closures[] = [
                'operation' => $operation,
                'table' => $table,
                'body' => substr($upBody, $bracePos + 1, $closing - $bracePos - 1),
            ];
        }

        return $closures;
    }

    /**
     * @return array<int, string>
     */
    private function extractAddedColumns(string $body): array
    {
        $columns = [];

        if (preg_match_all('/\$table->([A-Za-z_][A-Za-z0-9_]*)\(\s*[\'\"]([^\'\"]+)[\'\"][^;]*\);/s', $body, $matches, PREG_SET_ORDER)) {
            foreach ($matches as $match) {
                $method = (string) $match[1];
                $column = strtolower((string) $match[2]);

                if (in_array($method, self::NON_COLUMN_METHODS, true)) {
                    continue;
                }

                $columns[$column] = true;
            }
        }

        if (preg_match_all('/\$table->([A-Za-z_][A-Za-z0-9_]*)\(\s*\)(?:[^;]*)?;/s', $body, $matches, PREG_SET_ORDER)) {
            foreach ($matches as $match) {
                $method = (string) $match[1];
                foreach (self::NO_ARG_COLUMN_METHODS[$method] ?? [] as $column) {
                    $columns[$column] = true;
                }
            }
        }

        if (preg_match_all('/\$table->(nullableMorphs|morphs|uuidMorphs|nullableUuidMorphs|ulidMorphs|nullableUlidMorphs)\(\s*[\'\"]([^\'\"]+)[\'\"][^;]*\);/s', $body, $matches, PREG_SET_ORDER)) {
            foreach ($matches as $match) {
                $prefix = strtolower((string) $match[2]);
                $columns[$prefix . '_type'] = true;
                $columns[$prefix . '_id'] = true;
            }
        }

        ksort($columns);

        return array_keys($columns);
    }

    /**
     * @return array<int, string>
     */
    private function extractDroppedColumns(string $body): array
    {
        $columns = [];

        if (preg_match_all('/\$table->dropColumn\(\s*[\'\"]([^\'\"]+)[\'\"][^;]*\);/s', $body, $matches)) {
            foreach ($matches[1] as $column) {
                $columns[strtolower((string) $column)] = true;
            }
        }

        if (preg_match_all('/\$table->dropColumn\(\s*\[(.*?)\]\s*\);/s', $body, $matches)) {
            foreach ($matches[1] as $arrayBody) {
                if (preg_match_all('/[\'\"]([^\'\"]+)[\'\"]/', $arrayBody, $columnMatches)) {
                    foreach ($columnMatches[1] as $column) {
                        $columns[strtolower((string) $column)] = true;
                    }
                }
            }
        }

        if (preg_match_all('/\$table->renameColumn\(\s*[\'\"]([^\'\"]+)[\'\"]\s*,\s*[\'\"]([^\'\"]+)[\'\"][^;]*\);/s', $body, $matches, PREG_SET_ORDER)) {
            foreach ($matches as $match) {
                $columns[strtolower((string) $match[1])] = true;
            }
        }

        ksort($columns);

        return array_keys($columns);
    }

    /**
     * @param array{parsed_files:int, expected_tables:int, missing_tables:array<int,array{table:string,sources:array<int,string>}>, missing_columns:array<int,array{table:string,columns:array<int,string>,sources:array<int,string>}>} $report
     */
    private function renderReport(array $report): void
    {
        $this->info(sprintf(
            'Parsed %d migration file(s). Expected schema covers %d table(s).',
            $report['parsed_files'],
            $report['expected_tables']
        ));

        if ($report['missing_tables'] === [] && $report['missing_columns'] === []) {
            $this->info('Schema audit passed. No missing tables or columns detected.');
            return;
        }

        if ($report['missing_tables'] !== []) {
            $this->newLine();
            $this->error('Missing tables:');
            foreach ($report['missing_tables'] as $item) {
                $this->line(sprintf('  - %s', $item['table']));
                $this->line(sprintf('    sources: %s', implode(', ', $item['sources'])));
            }
        }

        if ($report['missing_columns'] !== []) {
            $this->newLine();
            $this->error('Missing columns:');
            foreach ($report['missing_columns'] as $item) {
                $this->line(sprintf('  - %s: %s', $item['table'], implode(', ', $item['columns'])));
                $this->line(sprintf('    sources: %s', implode(', ', $item['sources'])));
            }
        }
    }
}