<?php

namespace App\Console\Commands;

use App\Models\Category;
use App\Models\Location;
use Illuminate\Console\Command;

class FixLocationCategories extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'locations:fix-categories';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Fix inconsistencies between category and category_id in locations table';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        $this->info('Starting location category fix...');

        // Step 1: Update locations where category_id is missing but category (string) exists
        $this->info('Step 1: Fixing locations with missing category_id...');
        $locations = Location::whereNull('category_id')
            ->orWhere('category_id', 0)
            ->whereNotNull('category')
            ->get();

        $this->info("Found {$locations->count()} locations with missing category_id");

        $updatedCount1 = 0;
        foreach ($locations as $location) {
            // Find the corresponding category by key
            if (! empty($location->category)) {
                $category = Category::where('category_key', $location->category)->first();

                if ($category) {
                    // Update the location with the correct category_id
                    $location->category_id = $category->id;
                    $location->category_fa = $category->category_fa;
                    $location->save();

                    $updatedCount1++;
                    $this->line("Updated location {$location->id} with category_id {$category->id} from category key {$location->category}");
                }
            }
        }
        $this->info("Updated {$updatedCount1} locations with correct category_id");

        // Step 2: Update locations where category (string) is missing but category_id exists
        $this->info('Step 2: Fixing locations with missing category key...');
        $locations = Location::whereNotNull('category_id')
            ->where('category_id', '>', 0)
            ->where(function ($query) {
                $query->whereNull('category')
                    ->orWhere('category', '');
            })
            ->get();

        $this->info("Found {$locations->count()} locations with missing category key");

        $updatedCount2 = 0;
        foreach ($locations as $location) {
            // Find the corresponding category by id
            $category = Category::find($location->category_id);

            if ($category) {
                // Update the location with the correct category key
                $location->category = $category->category_key;
                $location->category_fa = $category->category_fa;
                $location->save();

                $updatedCount2++;
                $this->line("Updated location {$location->id} with category key {$category->category_key} from category_id {$location->category_id}");
            }
        }
        $this->info("Updated {$updatedCount2} locations with correct category key");

        // Additional check: locations with category_id that doesn't exist in categories table
        $this->info('Step 3: Checking for invalid category_id values...');
        $invalidLocations = Location::whereNotNull('category_id')
            ->whereNotIn('category_id', function ($query) {
                $query->select('id')->from('categories');
            })
            ->get();

        if ($invalidLocations->count() > 0) {
            $this->warn("Found {$invalidLocations->count()} locations with invalid category_id. Please fix these manually:");
            foreach ($invalidLocations as $location) {
                $this->line("Location ID: {$location->id}, Title: {$location->title}, Invalid category_id: {$location->category_id}");
            }
        } else {
            $this->info('No locations with invalid category_id found.');
        }

        $this->info('Location category fix completed!');

        return 0;
    }
}
