<?php

namespace App\Http\Controllers\Api;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

use App\Models\Product;
use App\Models\Category;
use App\Models\Brand;
use App\Models\Style;
use App\Models\Size;
use App\Models\Color;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xls;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;

class StockValueController extends Controller
{
    //

    // Add these methods to your existing StockController class

/**
 * Get stock value data based on filters
 */
public function getStockValueData(Request $request)
{
    $categoryId = $request->input('category_id');
    $brandId = $request->input('brand_id');
    $modelName = $request->input('model_name'); // This is still product_code for filtering
    $styleId = $request->input('style_id', 'all');

    $query = DB::table('products')
        ->join('categories', 'products.category_id', '=', 'categories.category_id')
        ->join('brands', 'products.brand_id', '=', 'brands.brand_id')
        ->join('styles', 'products.style_id', '=', 'styles.style_id')
        ->join('sizes', 'products.size_id', '=', 'sizes.size_id')
        ->join('colors', 'products.color_id', '=', 'colors.color_id')
        ->where('products.active_status', 1)
        ->whereNull('products.deleted_at');

    // Apply filters
    if ($categoryId && $categoryId !== 'all') {
        $query->where('products.category_id', $categoryId);
    }

    if ($brandId && $brandId !== 'all') {
        $query->where('products.brand_id', $brandId);
    }

    if ($modelName) {
        $query->where('products.product_code', 'like', "%{$modelName}%");
    }

    if ($styleId && $styleId !== 'all') {
        $query->where('products.style_id', $styleId);
    }

    $stockData = $query->select(
        'categories.category_id',
        'categories.category_name',
        'brands.brand_id',
        'brands.brand_name',
        'products.product_code',
        'styles.style_id',
        'styles.style_name as type',
        'sizes.size_name',
        'colors.color_name',
        'products.product_quantity',
        'products.product_manual_quantity',
        'products.threshold',
        'products.product_price' // Add product_price field
    )->get();

    if ($styleId === 'all') {
        return $this->formatAllTypesValueData($stockData);
    } else {
        return $this->formatSingleTypeValueData($stockData, $styleId);
    }
}

/**
 * Format value data when 'all' types are selected
 */
private function formatAllTypesValueData($stockData)
{
    $groupedData = [];
    
    foreach ($stockData as $item) {
        // Use category_id, brand_id, and style_id for robust grouping key
        $catBrandStyleKey = $item->category_id . '-' . $item->brand_id . '-' . $item->style_id;
        $styleId = $item->style_id;
        
        if (!isset($groupedData[$styleId])) {
            $groupedData[$styleId] = [];
        }
        
        if (!isset($groupedData[$styleId][$catBrandStyleKey])) {
            $groupedData[$styleId][$catBrandStyleKey] = [
                'category_id' => $item->category_id,
                'category' => $item->category_name,
                'brand_id' => $item->brand_id,
                'brand' => $item->brand_name,
                'model' => $item->type,
                'product_code' => $item->product_code,
                'style_id' => $item->style_id,
                'type' => $item->type,
                'threshold' => $item->threshold,
                'values_by_color_size' => [] // New structure for aggregated values
            ];
        }
        
        $colorName = $item->color_name;
        $sizeName = $item->size_name;
        $quantity = $item->product_quantity + $item->product_manual_quantity;
        $value = $quantity * ($item->product_price ?? 0); // Calculate stock value

        // Aggregate values for the same color-size combination
        if (!isset($groupedData[$styleId][$catBrandStyleKey]['values_by_color_size'][$colorName])) {
            $groupedData[$styleId][$catBrandStyleKey]['values_by_color_size'][$colorName] = [];
        }
        $groupedData[$styleId][$catBrandStyleKey]['values_by_color_size'][$colorName][$sizeName] = 
            ($groupedData[$styleId][$catBrandStyleKey]['values_by_color_size'][$colorName][$sizeName] ?? 0) + $value;
    }

    // Convert to matrix format for each type
    $result = [];
    foreach ($groupedData as $styleId => $items) {
        $typeName = count($items) > 0 ? array_values($items)[0]['type'] : '';
        $result[$styleId] = [
            'style_id' => $styleId,
            'type_name' => $typeName,
            'items' => $this->convertToValueMatrix($items)
        ];
    }

    return response()->json([
        'success' => true,
        'data' => $result,
        'type' => 'all'
    ]);
}

/**
 * Format value data for single type selection
 */
private function formatSingleTypeValueData($stockData, $selectedStyleId)
{
    $groupedData = [];
    $selectedType = '';
    
    foreach ($stockData as $item) {
        // Use category_id, brand_id, and style_id for robust grouping key
        $catBrandStyleKey = $item->category_id . '-' . $item->brand_id . '-' . $item->style_id;
        $selectedType = $item->type;
        
        if (!isset($groupedData[$catBrandStyleKey])) {
            $groupedData[$catBrandStyleKey] = [
                'category_id' => $item->category_id,
                'category' => $item->category_name,
                'brand_id' => $item->brand_id,
                'brand' => $item->brand_name,
                'model' => $item->type,
                'product_code' => $item->product_code,
                'style_id' => $item->style_id,
                'type' => $item->type,
                'threshold' => $item->threshold,
                'values_by_color_size' => [] // New structure for aggregated values
            ];
        }
        
        $colorName = $item->color_name;
        $sizeName = $item->size_name;
        $quantity = $item->product_quantity + $item->product_manual_quantity;
        $value = $quantity * ($item->product_price ?? 0); // Calculate stock value

        // Aggregate values for the same color-size combination
        if (!isset($groupedData[$catBrandStyleKey]['values_by_color_size'][$colorName])) {
            $groupedData[$catBrandStyleKey]['values_by_color_size'][$colorName] = [];
        }
        $groupedData[$catBrandStyleKey]['values_by_color_size'][$colorName][$sizeName] = 
            ($groupedData[$catBrandStyleKey]['values_by_color_size'][$colorName][$sizeName] ?? 0) + $value;
    }

    $matrixData = $this->convertToValueMatrix($groupedData);

    return response()->json([
        'success' => true,
        'data' => $matrixData,
        'type' => $selectedType,
        'style_id' => $selectedStyleId,
        'groupedData' => $groupedData
    ]);
}

/**
 * Convert grouped value data to matrix format
 */
private function convertToValueMatrix($groupedData)
{
    $result = [];
    
    foreach ($groupedData as $key => $item) {
        // Get unique sizes and colors from the aggregated data
        $allSizes = collect();
        $allColors = collect(array_keys($item['values_by_color_size']));

        foreach ($item['values_by_color_size'] as $colorValues) {
            $allSizes = $allSizes->merge(array_keys($colorValues));
        }
        $uniqueSizes = $allSizes->unique()->sort()->values();
        $uniqueColors = $allColors->unique()->sort()->values();
        
        // Create matrix
        $matrix = [];
        $matrix['header'] = [$item['category'].'-'.$item['brand'].'-'.$item['type'], ...$uniqueSizes->toArray()];
        
        foreach ($uniqueColors as $color) {
            $row = [$color];
            foreach ($uniqueSizes as $size) {
                // Retrieve the pre-aggregated value
                $value = $item['values_by_color_size'][$color][$size] ?? 0;
                $row[] = number_format($value, 2); // Format as currency
            }
            $matrix['rows'][] = $row;
        }
        
        $result[] = [
            'key' => $key,
            'category_id' => $item['category_id'],
            'category' => $item['category'],
            'brand_id' => $item['brand_id'],
            'brand' => $item['brand'],
            'product_code' => $item['product_code'],
            'style_id' => $item['style_id'],
            'type' => $item['type'],
            'threshold' => $item['threshold'],
            'matrix' => $matrix
        ];
    }
    
    return $result;
}

/**
 * Export stock value report to Excel
 */
public function exportStockValueReport(Request $request)
{
    $categoryId = $request->input('category_id');
    $brandId = $request->input('brand_id');
    $styleId = $request->input('style_id', 'all');

    $query = DB::table('products')
        ->join('categories', 'products.category_id', '=', 'categories.category_id')
        ->join('brands', 'products.brand_id', '=', 'brands.brand_id')
        ->join('styles', 'products.style_id', '=', 'styles.style_id')
        ->join('sizes', 'products.size_id', '=', 'sizes.size_id')
        ->join('colors', 'products.color_id', '=', 'colors.color_id')
        ->where('products.active_status', 1)
        ->whereNull('products.deleted_at');

    if ($categoryId && $categoryId !== 'all') {
        $query->where('products.category_id', $categoryId);
    }
    if ($brandId && $brandId !== 'all') {
        $query->where('products.brand_id', $brandId);
    }
    if ($styleId && $styleId !== 'all') {
        $query->where('products.style_id', $styleId);
    }

    $rawStockData = $query->select(
        'categories.category_id',
        'categories.category_name',
        'brands.brand_id',
        'brands.brand_name',
        'products.product_code',
        'styles.style_id',
        'styles.style_name as type',
        'sizes.size_name',
        'colors.color_name',
        'products.product_quantity',
        'products.product_manual_quantity',
        'products.threshold',
        'products.product_price' // Add product_price field
    )->get();

    $formattedResponse = ($styleId === 'all') 
        ? $this->formatAllTypesValueData($rawStockData) 
        : $this->formatSingleTypeValueData($rawStockData, $styleId);

    $formattedData = $formattedResponse->getData(true)['data'];
    $exportType = $formattedResponse->getData(true)['type'];

    // Create spreadsheet
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    $row = 1;

    // Report Header
    $sheet->setCellValue("A{$row}", 'Stock Value Report'); $row++;
    $sheet->setCellValue("A{$row}", 'Generated On:'); 
    $sheet->setCellValue("B{$row}", date('Y-m-d H:i:s')); 
    $row += 2;

    // Loop through data
    if ($exportType === 'all') {
        foreach ($formattedData as $styleGroup) {
            foreach ($styleGroup['items'] as $item) {
                // Stock Value Matrix Header
                $header = $item['matrix']['header'] ?? ['Color/Size', 'Size 1', 'Size 2'];
                $col = 'A';
                foreach ($header as $head) {
                    $sheet->setCellValue("{$col}{$row}", $head);
                    $sheet->getStyle("{$col}{$row}")->getFont()->setBold(true);
                    $sheet->getStyle("{$col}{$row}")->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFDDDDDD');
                    $col++;
                }
                $row++;

                // Stock Value Matrix Rows
                $totalValue = 0;
                foreach ($item['matrix']['rows'] ?? [] as $matrixRow) {
                    $col = 'A';
                    foreach ($matrixRow as $index => $val) {
                        $sheet->setCellValue("{$col}{$row}", $val);

                        // Highlight high-value cells
                        if ($index > 0 && is_numeric(str_replace([',', '.'], ['', '.'], str_replace(',', '', $val)))) {
                            $numericVal = (float) str_replace(',', '', $val);
                            $totalValue += $numericVal;
                            
                            if ($numericVal > 10000) { // Highlight values over 10,000
                                $sheet->getStyle("{$col}{$row}")->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFB6D7A8');
                            } else if ($numericVal > 5000) { // Highlight values over 5,000
                                $sheet->getStyle("{$col}{$row}")->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFFCE5CD');
                            }
                        }

                        $col++;
                    }
                    $row++;
                }

                // // Add total row
                // $sheet->setCellValue("A{$row}", "TOTAL VALUE:");
                // $sheet->setCellValue("C{$row}", number_format($totalValue, 2));
                // $sheet->getStyle("A{$row}:B{$row}")->getFont()->setBold(true);
                // $sheet->getStyle("A{$row}:B{$row}")->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFCFE2F3');
                 $row += 3;
            }
        }
    } else {
        foreach ($formattedData as $item) {
            $header = $item['matrix']['header'] ?? ['Color/Size', 'Size 1', 'Size 2'];
            $col = 'A';
            foreach ($header as $head) {
                $sheet->setCellValue("{$col}{$row}", $head);
                $sheet->getStyle("{$col}{$row}")->getFont()->setBold(true);
                $sheet->getStyle("{$col}{$row}")->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFDDDDDD');
                $col++;
            }
            $row++;

            $totalValue = 0;
            foreach ($item['matrix']['rows'] ?? [] as $matrixRow) {
                $col = 'A';
                foreach ($matrixRow as $index => $val) {
                    $sheet->setCellValue("{$col}{$row}", $val);
                   
                    if ($index > 0 && is_numeric(str_replace([',', '.'], ['', '.'], str_replace(',', '', $val)))) {
                        $numericVal = (float) str_replace(',', '', $val);
                        $totalValue += $numericVal;
                        
                        if ($numericVal > 10000) {
                            $sheet->getStyle("{$col}{$row}")->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFB6D7A8');
                        } else if ($numericVal > 5000) {
                            $sheet->getStyle("{$col}{$row}")->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFFCE5CD');
                        }
                    }

                    $col++;
                }
                $row++;
            }

            // // Add total row
            // $sheet->setCellValue("A{$row}", "TOTAL VALUE:");
            // $sheet->setCellValue("C{$row}", number_format($totalValue, 2));
            // $sheet->getStyle("A{$row}:B{$row}")->getFont()->setBold(true);
            // $sheet->getStyle("A{$row}:B{$row}")->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFCFE2F3');
             $row += 3;
        }
    }

    // Return Excel file
    $fileName = 'stock-value-report-' . date('Y-m-d-H-i-s') . '.xls';
    $writer = new Xls($spreadsheet);

    return response()->streamDownload(function() use ($writer) {
        $writer->save('php://output');
    }, $fileName, [
        'Content-Type' => 'application/vnd.ms-excel',
    ]);
}
}
