<?php

namespace App\Console\Commands;

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

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

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

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

        try {
            // Get top players ordered by level, exp (matching old website logic)
            // Empire is stored in player_index table, not player table
            // Filter out GM accounts (names starting with [)
            $players = DB::connection('player')
                ->table('player')
                ->join('player_index', 'player.account_id', '=', 'player_index.id')
                ->select(['player.name', 'player.job', 'player_index.empire', 'player.level', 'player.exp'])
                ->where('player.name', 'NOT LIKE', '[%')
                ->orderByDesc('player.level')
                ->orderByDesc('player.exp')
                ->limit(10000) // Limit to top 10000 players
                ->get();

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

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

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

            foreach ($chunks as $chunk) {
                $insertData = [];
                foreach ($chunk as $player) {
                    $rank++;
                    $insertData[] = [
                        'id' => $rank,
                        'name' => $player->name,
                        'job' => $player->job,
                        'empire' => $player->empire,
                        'level' => $player->level,
                        'exp' => $player->exp,
                        'date' => now(),
                    ];
                }
                DB::connection('website')->table('highscore_cache')->insert($insertData);
            }

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

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