<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use App\Models\MenuItemRecipe;
use App\Models\PosMenuItem;
use App\Models\Item;
use App\Models\Inventory;
use Maatwebsite\Excel\Facades\Excel;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Str;

class ExportRecipes extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'recipes:export 
                            {--restaurant_id= : Restaurant ID to filter recipes} 
                            {--branch_id= : Branch ID to filter recipes}
                            {--sample_file= : Path to sample recipe Excel file}
                            {--content_file= : Path to content file with recipe data}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Export all menu item recipes to Excel files (single file and separate files per menu item). Can read from Excel files or export from database.';

    /**
     * Execute the console command.
     */
    public function handle()
    {
        $sampleFile = $this->option('sample_file');
        $contentFile = $this->option('content_file');

        // If files are provided, read from them
        if ($sampleFile || $contentFile) {
            return $this->exportFromFiles($sampleFile, $contentFile);
        }

        // Otherwise, export from database
        return $this->exportFromDatabase();
    }

    /**
     * Export recipes from database
     */
    private function exportFromDatabase()
    {
        $restaurantId = $this->option('restaurant_id');
        $branchId = $this->option('branch_id');

        // Build query for recipes
        $query = MenuItemRecipe::with(['menuItem', 'inventoryItem']);

        if ($restaurantId) {
            $query->where('restaurant_id', $restaurantId);
        }

        if ($branchId) {
            $query->whereHas('menuItem', function($q) use ($branchId) {
                $q->where('branch_id', $branchId);
            });
        }

        $recipes = $query->orderBy('menu_item_id')
            ->orderBy('sort_order')
            ->get();

        if ($recipes->isEmpty()) {
            $this->error('No recipes found to export.');
            return 1;
        }

        $this->info("Found {$recipes->count()} recipes to export.");

        // Get units from inventory for each item
        $itemUnits = $this->getItemUnits($recipes);

        // Convert recipes to array format for processing
        $recipesArray = [];
        foreach ($recipes as $recipe) {
            $menuItemName = $recipe->menuItem ? $recipe->menuItem->name : 'Unknown';
            $inventoryItemName = $recipe->inventoryItem ? $recipe->inventoryItem->name : 'Unknown';
            $unit = $itemUnits[$recipe->inventory_item_id] ?? $recipe->unit ?? 'pcs';
            $unit = $this->normalizeUnit($unit);

            $recipesArray[] = [
                $menuItemName,
                $inventoryItemName,
                $recipe->quantity,
                $unit,
                $recipe->details ?? '',
                $recipe->sort_order ?? 0,
            ];
        }

        return $this->processAndExportRecipes($recipesArray, []);
    }

    /**
     * Get units from inventory table for all items
     */
    private function getItemUnits($recipes)
    {
        $itemIds = $recipes->pluck('inventory_item_id')->unique()->filter();
        
        if ($itemIds->isEmpty()) {
            return [];
        }

        // Get the latest inventory for each item
        $itemUnits = [];
        foreach ($itemIds as $itemId) {
            $latestInventory = Inventory::where('item_id', $itemId)
                ->whereNotNull('item_unit')
                ->orderBy('created_at', 'desc')
                ->first();
            
            if ($latestInventory) {
                $itemUnits[$itemId] = $latestInventory->item_unit;
            }
        }

        return $itemUnits;
    }

    /**
     * Export recipes from Excel files
     */
    private function exportFromFiles($sampleFile, $contentFile)
    {
        $allRecipes = [];

        // Read from sample file if provided
        if ($sampleFile && file_exists($sampleFile)) {
            $this->info("Reading sample file: {$sampleFile}");
            $sampleData = Excel::toArray([], $sampleFile);
            if (!empty($sampleData[0])) {
                $rows = $sampleData[0];
                $headers = array_shift($rows); // Remove header row
                foreach ($rows as $row) {
                    if (!empty($row[0])) {
                        $allRecipes[] = $row;
                    }
                }
                $this->info("  Found " . count($rows) . " recipes in sample file");
            }
        }

        // Read from content file if provided
        if ($contentFile && file_exists($contentFile)) {
            $this->info("Reading content file: {$contentFile}");
            $contentData = Excel::toArray([], $contentFile);
            if (!empty($contentData[0])) {
                $rows = $contentData[0];
                $headers = array_shift($rows); // Remove header row
                foreach ($rows as $row) {
                    if (!empty($row[0])) {
                        $allRecipes[] = $row;
                    }
                }
                $this->info("  Found " . count($rows) . " recipes in content file");
            }
        }

        if (empty($allRecipes)) {
            $this->error('No recipes found in the provided files.');
            return 1;
        }

        $this->info("Total recipes to process: " . count($allRecipes));

        // Get units from inventory for all inventory items
        $inventoryItemNames = array_unique(array_column($allRecipes, 1));
        $itemUnits = $this->getItemUnitsFromNames($inventoryItemNames);

        // Process recipes
        return $this->processAndExportRecipes($allRecipes, $itemUnits);
    }

    /**
     * Process recipes and export to files
     */
    private function processAndExportRecipes($recipes, $itemUnits = [])
    {
        // Prepare data for single file
        $allRecipesData = [];
        $allRecipesData[] = ['Menu Item Name', 'Inventory Item Name', 'Quantity', 'Unit', 'Details', 'Sort Order'];

        // Group recipes by menu item for separate files
        $recipesByMenuItem = [];

        foreach ($recipes as $recipe) {
            $menuItemName = trim($recipe[0] ?? '');
            $inventoryItemName = trim($recipe[1] ?? '');
            $quantity = isset($recipe[2]) ? (float) $recipe[2] : 0;
            $unit = isset($recipe[3]) ? trim($recipe[3] ?? '') : '';
            $details = isset($recipe[4]) ? trim($recipe[4] ?? '') : '';
            $sortOrder = isset($recipe[5]) ? (int) $recipe[5] : 0;

            if (empty($menuItemName) || empty($inventoryItemName)) {
                continue; // Skip invalid rows
            }

            // Get unit from inventory if available, otherwise use provided unit
            $finalUnit = $itemUnits[strtolower($inventoryItemName)] ?? $unit;
            
            // Normalize unit (g -> gram, kg stays as kg)
            $finalUnit = $this->normalizeUnit($finalUnit);

            $row = [
                $menuItemName,
                $inventoryItemName,
                number_format($quantity, 2, '.', ''),
                $finalUnit,
                $details,
                $sortOrder,
            ];

            $allRecipesData[] = $row;

            // Group by menu item
            if (!isset($recipesByMenuItem[$menuItemName])) {
                $recipesByMenuItem[$menuItemName] = [];
                $recipesByMenuItem[$menuItemName][] = ['Menu Item Name', 'Inventory Item Name', 'Quantity', 'Unit', 'Details', 'Sort Order'];
            }
            $recipesByMenuItem[$menuItemName][] = $row;
        }

        // Create output directory
        $outputDir = storage_path('app/exports/recipes');
        if (!file_exists($outputDir)) {
            mkdir($outputDir, 0755, true);
        }

        // Export single file with all recipes
        $singleFileName = 'all_recipes_' . date('Y-m-d_His') . '.xlsx';
        $singleFilePath = $outputDir . '/' . $singleFileName;
        
        Excel::store(
            new class($allRecipesData) implements \Maatwebsite\Excel\Concerns\FromArray {
                protected $data;
                public function __construct($data) { $this->data = $data; }
                public function array(): array { return $this->data; }
            },
            'exports/recipes/' . $singleFileName
        );

        $this->info("✓ Created single file: {$singleFileName}");
        $this->line("  Path: {$singleFilePath}");

        // Export separate files for each menu item
        $separateFilesDir = $outputDir . '/by_menu_item';
        if (!file_exists($separateFilesDir)) {
            mkdir($separateFilesDir, 0755, true);
        }

        $fileCount = 0;
        foreach ($recipesByMenuItem as $menuItemName => $menuItemData) {
            $safeFileName = Str::slug($menuItemName, '_');
            $fileName = $safeFileName . '_recipe_' . date('Y-m-d_His') . '.xlsx';
            $filePath = $separateFilesDir . '/' . $fileName;

            Excel::store(
                new class($menuItemData) implements \Maatwebsite\Excel\Concerns\FromArray {
                    protected $data;
                    public function __construct($data) { $this->data = $data; }
                    public function array(): array { return $this->data; }
                },
                'exports/recipes/by_menu_item/' . $fileName
            );

            $fileCount++;
            $this->info("✓ Created file for '{$menuItemName}': {$fileName}");
        }

        $this->newLine();
        $this->info("Export completed successfully!");
        $this->line("  - Single file: {$singleFileName} (" . (count($allRecipesData) - 1) . " recipes)");
        $this->line("  - Separate files: {$fileCount} files in 'by_menu_item' directory");
        $this->line("  - Output directory: {$outputDir}");

        return 0;
    }

    /**
     * Get units from inventory table by item names
     */
    private function getItemUnitsFromNames($itemNames)
    {
        $itemUnits = [];
        
        foreach ($itemNames as $itemName) {
            // Find item by name
            $item = Item::whereRaw('LOWER(name) = ?', [strtolower($itemName)])->first();
            
            if ($item) {
                // Get latest inventory for this item
                $latestInventory = Inventory::where('item_id', $item->id)
                    ->whereNotNull('item_unit')
                    ->orderBy('created_at', 'desc')
                    ->first();
                
                if ($latestInventory) {
                    $itemUnits[strtolower($itemName)] = $latestInventory->item_unit;
                }
            }
        }

        return $itemUnits;
    }

    /**
     * Normalize unit format
     */
    private function normalizeUnit($unit)
    {
        $unit = strtolower(trim($unit));
        
        // Convert common variations
        if ($unit === 'g') {
            return 'gram';
        } elseif ($unit === 'kilogram' || $unit === 'kgs') {
            return 'kg';
        } elseif ($unit === 'liter' || $unit === 'l') {
            return 'liter';
        } elseif ($unit === 'piece' || $unit === 'pieces') {
            return 'pcs';
        }
        
        return $unit;
    }
}

