<?php

namespace App\Console\Commands;

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

class FixMiscategorizedProducts extends Command
{
    protected $signature = 'fix:categories';
    protected $description = 'Fix miscategorized products - Gaming Focus Only';

    public function handle()
    {
        $this->info('🔧 Fixing categories for GAMING-ONLY store...');
        $this->newLine();
        
        $fixed = 0;

        // ============================================
        // 1. DELETE NON-GAMING CATEGORIES
        // ============================================
        $this->info('🗑️  Removing non-gaming categories...');
        
        $nonGaming = [
            'alight', 'capcut', 'canva', 'chatgpt', 'gemini', 'grammarly', 
            'notion', 'picsart', 'kinemaster', 'warp', 'discord nitro',
            'perplexity', 'netflix', 'spotify', 'youtube premium', 'disney',
            'viu', 'iqiyi', 'wetv', 'vidio', 'amazon prime', 'hbo', 'bstation'
        ];
        
        foreach ($nonGaming as $item) {
            // Delete products first (foreign key)
            $productCount = DB::table('products')
                ->whereIn('category_id', function($query) use ($item) {
                    $query->select('id')
                        ->from('categories')
                        ->whereRaw('LOWER(name) LIKE ?', ["%{$item}%"]);
                })
                ->delete();
            
            // Then delete categories
            $catCount = DB::table('categories')
                ->whereRaw('LOWER(name) LIKE ?', ["%{$item}%"])
                ->delete();
            
            if ($catCount > 0) {
                $this->line("  ✅ Deleted: {$catCount} categories + {$productCount} products matching '{$item}'");
                $fixed += $catCount;
            }
        }

        // ============================================
        // 2. FIX GAMING VOUCHERS (Move from 'game' to 'voucher')
        // ============================================
        $this->info('🎫 Moving Gaming Vouchers to Voucher category...');
        
        $gamingVouchers = [
            'garena shell', 'voucher garena',
            'megaxus', 'voucher megaxus',
            'pb zepetto', 'voucher pb',
            'playstation', 'psn', 'voucher psn',
            'razer gold', 'voucher razer',
            'roblox', 'voucher roblox',
            'valorant', 'voucher valorant',
            'steam wallet', 'voucher steam',
            'xbox', 'voucher xbox',
            'nintendo', 'eshop',
            'google play', 'voucher google play'
        ];
        
        foreach ($gamingVouchers as $voucher) {
            $count = DB::table('categories')
                ->whereRaw('LOWER(name) LIKE ?', ["%{$voucher}%"])
                ->where('category_type', '!=', 'voucher')
                ->update([
                    'category_type' => 'voucher',
                    'input_type' => 'voucher',
                    'updated_at' => now(),
                ]);
            
            if ($count > 0) {
                $this->line("  ✅ Fixed {$count} categories matching '{$voucher}'");
                $fixed += $count;
            }
        }

        // ============================================
        // 3. ENSURE REAL GAMES ARE 'game' TYPE
        // ============================================
        $this->info('🎮 Ensuring game categories are type=game...');
        
        $realGames = [
            'mobile legends', 'free fire', 'pubg', 'genshin', 'honkai',
            'valorant', 'league of legends', 'arena of valor', 'call of duty',
            'clash of clans', 'clash royale', 'brawl stars'
        ];
        
        foreach ($realGames as $game) {
            $count = DB::table('categories')
                ->whereRaw('LOWER(name) LIKE ?', ["%{$game}%"])
                ->where('category_type', '!=', 'game')
                ->update([
                    'category_type' => 'game',
                    'updated_at' => now(),
                ]);
            
            if ($count > 0) {
                $this->line("  ✅ Fixed {$count} categories matching '{$game}'");
                $fixed += $count;
            }
        }

        // ============================================
        // 4. SUMMARY
        // ============================================
        $this->newLine();
        if ($fixed === 0) {
            $this->comment('  ℹ️  No categories needed fixing - all correct!');
        } else {
            $this->info("  🎉 Total fixed: {$fixed} categories");
        }

        // ============================================
        // 5. SHOW DISTRIBUTION
        // ============================================
        $this->newLine();
        $this->info('📊 Current Category Distribution:');
        
        $distribution = DB::table('categories')
            ->select('category_type', DB::raw('COUNT(*) as count'))
            ->groupBy('category_type')
            ->orderBy('count', 'desc')
            ->get();
        
        foreach ($distribution as $item) {
            $icon = match($item->category_type) {
                'game' => '🎮',
                'voucher' => '🎫',
                default => '❌'
            };
            
            $this->line("  {$icon} " . ucfirst($item->category_type) . ": {$item->count} categories");
        }

        // ============================================
        // 6. CHECK FOR NON-GAMING LEFTOVERS
        // ============================================
        $this->newLine();
        $this->info('🔍 Checking for non-gaming leftovers...');
        
        $suspects = DB::table('categories')
            ->whereRaw('LOWER(name) REGEXP ?', ['chatgpt|canva|spotify|netflix|youtube|discord|warp|capcut'])
            ->get();
            
        if ($suspects->count() > 0) {
            $this->warn('  ⚠️  Found non-gaming categories (will be removed on next sync):');
            foreach ($suspects as $cat) {
                $this->line("     - {$cat->name} ({$cat->category_type})");
            }
        } else {
            $this->comment('  ✅ No non-gaming categories found - Perfect!');
        }

        return 0;
    }
}