<?php

namespace App\Console\Commands;

use App\Models\Product;
use App\Models\StockMovement;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class FixCriticalIssues extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'fix:critical-issues {--dry-run : Show what would be done without making changes}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Fix critical performance and data integrity issues';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        $dryRun = $this->option('dry-run');
        
        $this->info('🔧 Starting critical issues fix...');
        
        if ($dryRun) {
            $this->warn('DRY RUN MODE - No changes will be made');
        }

        // 1. Fix stock calculations
        $this->fixStockCalculations($dryRun);
        
        // 2. Update database indexes
        $this->updateDatabaseIndexes($dryRun);
        
        // 3. Clean up orphaned records
        $this->cleanupOrphanedRecords($dryRun);
        
        // 4. Validate data integrity
        $this->validateDataIntegrity();
        
        $this->info('✅ Critical issues fix completed!');
        
        return 0;
    }

    private function fixStockCalculations(bool $dryRun): void
    {
        $this->info('📊 Fixing stock calculations...');
        
        $products = Product::all();
        $bar = $this->output->createProgressBar($products->count());
        
        foreach ($products as $product) {
            $calculatedStock = StockMovement::where('product_id', $product->id)->sum('quantity');
            
            if ($product->current_stock !== $calculatedStock) {
                $this->line("\nProduct ID {$product->id}: Current={$product->current_stock}, Calculated={$calculatedStock}");
                
                if (!$dryRun) {
                    $product->update(['current_stock' => $calculatedStock]);
                }
            }
            
            $bar->advance();
        }
        
        $bar->finish();
        $this->newLine();
    }

    private function updateDatabaseIndexes(bool $dryRun): void
    {
        $this->info('🗂️  Checking database indexes...');
        
        if (!$dryRun) {
            try {
                // Run the migration to add missing foreign keys
                $this->call('migrate', ['--path' => 'database/migrations/2025_11_24_000001_add_missing_foreign_keys.php']);
                $this->info('✅ Foreign keys added successfully');
            } catch (\Exception $e) {
                $this->error('❌ Failed to add foreign keys: ' . $e->getMessage());
            }
        } else {
            $this->info('Would run migration to add missing foreign keys');
        }
    }

    private function cleanupOrphanedRecords(bool $dryRun): void
    {
        $this->info('🧹 Cleaning up orphaned records...');
        
        // Find cart items with non-existent products
        $orphanedCartItems = DB::table('cart_items')
            ->leftJoin('products', 'cart_items.product_id', '=', 'products.id')
            ->whereNull('products.id')
            ->count();
            
        if ($orphanedCartItems > 0) {
            $this->warn("Found {$orphanedCartItems} orphaned cart items");
            
            if (!$dryRun) {
                DB::table('cart_items')
                    ->leftJoin('products', 'cart_items.product_id', '=', 'products.id')
                    ->whereNull('products.id')
                    ->delete();
                $this->info('✅ Orphaned cart items cleaned up');
            }
        }

        // Find order items with non-existent products
        $orphanedOrderItems = DB::table('order_items')
            ->leftJoin('products', 'order_items.product_id', '=', 'products.id')
            ->whereNull('products.id')
            ->count();
            
        if ($orphanedOrderItems > 0) {
            $this->error("Found {$orphanedOrderItems} orphaned order items - MANUAL REVIEW REQUIRED");
        }
    }

    private function validateDataIntegrity(): void
    {
        $this->info('🔍 Validating data integrity...');
        
        // Check for products with negative stock
        $negativeStock = Product::where('current_stock', '<', 0)->count();
        if ($negativeStock > 0) {
            $this->warn("⚠️  Found {$negativeStock} products with negative stock");
        }
        
        // Check for products without categories
        $noCategory = Product::whereNull('category_id')->count();
        if ($noCategory > 0) {
            $this->warn("⚠️  Found {$noCategory} products without categories");
        }
        
        // Check for orders without users
        $noUser = DB::table('orders')->whereNull('user_id')->count();
        if ($noUser > 0) {
            $this->warn("⚠️  Found {$noUser} orders without users");
        }
        
        $this->info('✅ Data integrity check completed');
    }
}