<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;

class UpdateOrganisationUrls extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'users:update-organisation-urls';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Update organisation_url column with slugified organisation values for organisers';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        $this->info('Starting organisation URL updates for organisers...');

        // Get total count for progress bar
        $total = DB::table('users')
            ->where('role_id', 3) // Only organisers
            ->whereNotNull('organisation')
            ->where(function ($query) {
                $query->whereNull('organisation_url')
                    ->orWhere('organisation_url', '');
            })
            ->count();

        if ($total === 0) {
            $this->info('No organisers found needing URL updates.');
            return Command::SUCCESS;
        }

        $this->output->progressStart($total);
        $count = 0;

        // Process users in chunks of 100
        DB::table('users')
            ->where('role_id', 3) // Only organisers
            ->whereNotNull('organisation')
            ->where(function ($query) {
                $query->whereNull('organisation_url')
                    ->orWhere('organisation_url', '');
            })
            ->orderBy('id')
            ->chunk(100, function ($users) use (&$count) {
                foreach ($users as $user) {
                    // Generate URL-friendly slug from organisation name
                    $organisationUrl = Str::slug($user->organisation);

                    // Check if this URL already exists
                    $existingCount = DB::table('users')
                        ->where('organisation_url', $organisationUrl)
                        ->count();

                    // If URL exists, append a unique number
                    if ($existingCount > 0) {
                        $organisationUrl = $organisationUrl . '-' . Str::random(6);
                    }

                    // Update the user's organisation_url
                    DB::table('users')
                        ->where('id', $user->id)
                        ->update(['organisation_url' => $organisationUrl]);

                    $count++;
                    $this->output->progressAdvance();
                }
            });

        $this->output->progressFinish();
        $this->info("Successfully updated {$count} organiser URLs.");

        // Show warning for organisers with null organisation values
        $nullOrgCount = DB::table('users')
            ->where('role_id', 3) // Only organisers
            ->where(function ($query) {
                $query->whereNull('organisation')
                    ->orWhere('organisation', '');
            })
            ->count();

        if ($nullOrgCount > 0) {
            $this->warn("Warning: {$nullOrgCount} organisers have no organisation value set. Their organisation pages will show 404.");
        }

        return Command::SUCCESS;
    }
}
