<?php

namespace App\Console\Commands;

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

class RefreshGuildHighscoreCache extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'highscore:refresh-guilds';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Refresh the guild highscore cache from the player database';

    /**
     * Execute the console command.
     */
    public function handle(): int
    {
        $this->info('Refreshing guild highscore cache...');

        try {
            // Get guilds ordered by ladder_point
            // Join with player table to get master name
            // Join with player_index to get empire (empire is stored per account, not per player)
            $guilds = DB::connection('player')
                ->table('guild')
                ->leftJoin('player', 'guild.master', '=', 'player.id')
                ->leftJoin('player_index', 'player.account_id', '=', 'player_index.id')
                ->select([
                    'guild.name',
                    'player.name as master',
                    'player_index.empire',
                    'guild.level',
                    'guild.ladder_point'
                ])
                ->orderByDesc('guild.ladder_point')
                ->limit(5000) // Limit to top 5000 guilds
                ->get();

            if ($guilds->isEmpty()) {
                $this->warn('No guilds found in the database.');
                return Command::SUCCESS;
            }

            // Clear existing cache
            DB::connection('website')->table('guild_highscore_cache')->truncate();

            // Insert new data with rank
            $rank = 0;
            $chunks = $guilds->chunk(500); // Insert in chunks for better performance

            foreach ($chunks as $chunk) {
                $insertData = [];
                foreach ($chunk as $guild) {
                    $rank++;
                    $insertData[] = [
                        'id' => $rank,
                        'name' => $guild->name,
                        'master' => $guild->master ?? 'Unknown', // Handle deleted characters
                        'empire' => $guild->empire,
                        'level' => $guild->level,
                        'ladder_point' => $guild->ladder_point ?? 0,
                        'date' => now(),
                    ];
                }
                DB::connection('website')->table('guild_highscore_cache')->insert($insertData);
            }

            $this->info("Successfully cached {$rank} guilds.");
            Log::info("Guild highscore cache refreshed with {$rank} guilds.");

            return Command::SUCCESS;
        } catch (\Exception $e) {
            $this->error('Failed to refresh guild highscore cache: ' . $e->getMessage());
            Log::error('Guild highscore cache refresh failed: ' . $e->getMessage());
            return Command::FAILURE;
        }
    }
}
