<?php

namespace Database\Seeders;

use App\Models\Article;
use App\Models\Category;
use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;

class CleanSlugsSeeder extends Seeder
{
    private const CHUNK_SIZE = 100; // Process 100 records at a time

    /**
     * Run the database seeds.
     */
    public function run(): void
    {
        $this->command->info('Starting slug cleanup...');

        // Clean article slugs
        $this->cleanArticleSlugs();

        // Clean category slugs
        $this->cleanCategorySlugs();

        $this->command->info('Slug cleanup completed!');
    }

    /**
     * Clean article slugs with chunking for better performance
     */
    private function cleanArticleSlugs(): void
    {
        $this->command->info('Cleaning article slugs...');

        $totalCount = Article::count();
        $this->command->info("Processing {$totalCount} articles...");

        $updatedCount = 0;
        $processedSlugs = []; // Track processed slugs by type
        $existingSlugs = $this->getExistingSlugs('articles'); // Pre-load existing slugs

        $bar = $this->command->getOutput()->createProgressBar($totalCount);
        $bar->start();

        // Process articles in chunks
        Article::orderBy('id')->chunk(self::CHUNK_SIZE, function ($articles) use (&$updatedCount, &$processedSlugs, &$existingSlugs, $bar) {
            $updatesToProcess = [];

            foreach ($articles as $article) {
                $originalSlug = $article->slug;
                $cleanedSlug = $this->sanitizeSlug($originalSlug);

                $typeKey = "type_{$article->type}";
                if (! isset($processedSlugs[$typeKey])) {
                    $processedSlugs[$typeKey] = [];
                }

                $finalSlug = $cleanedSlug;

                // Check conflicts with processed slugs
                if (in_array($cleanedSlug, $processedSlugs[$typeKey])) {
                    $finalSlug = $this->makeUniqueSlugWithProcessed($cleanedSlug, $processedSlugs[$typeKey]);
                }

                // Check conflicts with existing database slugs
                $finalSlug = $this->makeUniqueSlugWithExisting($finalSlug, $existingSlugs, $article->type, $article->id);

                if ($originalSlug !== $finalSlug) {
                    $updatesToProcess[] = [
                        'id'       => $article->id,
                        'slug'     => $finalSlug,
                        'original' => $originalSlug,
                    ];
                    $updatedCount++;
                }

                // Track this slug as processed and update existing slugs cache
                $processedSlugs[$typeKey][] = $finalSlug;
                $existingSlugs[$typeKey][$finalSlug] = $article->id;

                $bar->advance();
            }

            // Batch update using raw SQL for better performance
            if (! empty($updatesToProcess)) {
                $this->batchUpdateSlugs('articles', $updatesToProcess);
            }
        });

        $bar->finish();
        $this->command->newLine();
        $this->command->info("Updated {$updatedCount} article slugs");
    }

    /**
     * Clean category slugs with chunking for better performance
     */
    private function cleanCategorySlugs(): void
    {
        $this->command->info('Cleaning category slugs...');

        $totalCount = Category::count();
        $this->command->info("Processing {$totalCount} categories...");

        $updatedCount = 0;
        $processedSlugs = []; // Track processed slugs by type
        $existingSlugs = $this->getExistingSlugs('categories'); // Pre-load existing slugs

        $bar = $this->command->getOutput()->createProgressBar($totalCount);
        $bar->start();

        // Process categories in chunks
        Category::orderBy('id')->chunk(self::CHUNK_SIZE, function ($categories) use (&$updatedCount, &$processedSlugs, &$existingSlugs, $bar) {
            $updatesToProcess = [];

            foreach ($categories as $category) {
                $originalSlug = $category->slug;
                $cleanedSlug = $this->sanitizeSlug($originalSlug);

                $typeKey = "type_{$category->type}";
                if (! isset($processedSlugs[$typeKey])) {
                    $processedSlugs[$typeKey] = [];
                }

                $finalSlug = $cleanedSlug;

                // Check conflicts with processed slugs
                if (in_array($cleanedSlug, $processedSlugs[$typeKey])) {
                    $finalSlug = $this->makeUniqueSlugWithProcessed($cleanedSlug, $processedSlugs[$typeKey]);
                }

                // Check conflicts with existing database slugs
                $finalSlug = $this->makeUniqueSlugWithExisting($finalSlug, $existingSlugs, $category->type, $category->id);

                if ($originalSlug !== $finalSlug) {
                    $updatesToProcess[] = [
                        'id'       => $category->id,
                        'slug'     => $finalSlug,
                        'original' => $originalSlug,
                    ];
                    $updatedCount++;
                }

                // Track this slug as processed and update existing slugs cache
                $processedSlugs[$typeKey][] = $finalSlug;
                $existingSlugs[$typeKey][$finalSlug] = $category->id;

                $bar->advance();
            }

            // Batch update using raw SQL for better performance
            if (! empty($updatesToProcess)) {
                $this->batchUpdateSlugs('categories', $updatesToProcess);
            }
        });

        $bar->finish();
        $this->command->newLine();
        $this->command->info("Updated {$updatedCount} category slugs");
    }

    /**
     * Sanitize slug to only allow a-z, 0-9, and hyphens
     */
    private function sanitizeSlug(string $slug): string
    {
        if (empty($slug)) {
            return $slug;
        }

        return preg_replace('/^-+|-+$/', '', // Remove leading/trailing hyphens
            preg_replace('/-+/', '-', // Replace multiple consecutive hyphens with single hyphen
                preg_replace('/[^a-z0-9\s-]/', '', // Remove special characters except spaces and hyphens
                    str_replace(' ', '-', strtolower(trim($slug))) // Replace spaces with hyphens
                )
            )
        );
    }

    /**
     * Pre-load existing slugs to reduce database queries
     */
    private function getExistingSlugs(string $table): array
    {
        $slugs = [];
        $results = DB::table($table)->select('id', 'slug', 'type')->get();

        foreach ($results as $row) {
            $typeKey = "type_{$row->type}";
            if (! isset($slugs[$typeKey])) {
                $slugs[$typeKey] = [];
            }
            $slugs[$typeKey][$row->slug] = $row->id;
        }

        return $slugs;
    }

    /**
     * Batch update slugs for better performance
     */
    private function batchUpdateSlugs(string $table, array $updates): void
    {
        if (empty($updates)) {
            return;
        }

        DB::transaction(function () use ($table, $updates) {
            foreach ($updates as $update) {
                DB::table($table)
                    ->where('id', $update['id'])
                    ->update(['slug' => $update['slug']]);

                $this->command->line("ID {$update['id']}: '{$update['original']}' → '{$update['slug']}'");
            }
        });
    }

    /**
     * Make slug unique against processed slugs array
     */
    private function makeUniqueSlugWithProcessed(string $slug, array $processedSlugs): string
    {
        $originalSlug = $slug;
        $counter = 1;

        while (in_array($slug, $processedSlugs)) {
            $slug = $originalSlug . '-' . $counter;
            $counter++;
        }

        return $slug;
    }

    /**
     * Make slug unique against existing slugs cache
     */
    private function makeUniqueSlugWithExisting(string $slug, array $existingSlugs, int $type, ?int $excludeId = null): string
    {
        $typeKey = "type_{$type}";

        if (! isset($existingSlugs[$typeKey])) {
            return $slug;
        }

        $originalSlug = $slug;
        $counter = 1;

        while (isset($existingSlugs[$typeKey][$slug]) && $existingSlugs[$typeKey][$slug] !== $excludeId) {
            $slug = $originalSlug . '-' . $counter;
            $counter++;
        }

        return $slug;
    }

    /**
     * Make slug unique by adding suffix if necessary (check database) - fallback method
     */
    private function makeUniqueSlug(string $slug, string $table, int $type, ?int $excludeId = null): string
    {
        $originalSlug = $slug;
        $counter = 1;

        while (true) {
            $query = DB::table($table)
                ->where('slug', $slug)
                ->where('type', $type);

            if ($excludeId) {
                $query->where('id', '!=', $excludeId);
            }

            if (! $query->exists()) {
                break;
            }

            $slug = $originalSlug . '-' . $counter;
            $counter++;
        }

        return $slug;
    }
}