<?php

namespace App\Http\Controllers;

use App\Models\Container;
use App\Models\ContainerRow;
use App\Models\MarkList;
use App\Models\Invoice;
use App\Models\InvoiceItem;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;
use Carbon\Carbon;
use Barryvdh\DomPDF\Facade\Pdf;
use Illuminate\Support\Facades\Storage;

class ContainerController extends Controller
{
    public function index()
    {
        $containers = Container::with('rows')->latest()->get();

        $containers->each(function ($container) {
            $rows = $container->rows;

            $totalCtn = 0;
            $totalQty = 0;
            $totalCbm = 0;
            $totalKg  = 0;

            $ctnKeys = ['CTN', 'CTNS'];
            $qtyKeys = ['ITLQTY', 'TOTALQTY', 'TTLQTY', 'QTY', 'PCS', 'PIECES'];
            $cbmKeys = ['TOTALCBM', 'TTLCBM', 'ITLCBM', 'CBM'];
            $kgKeys  = ['TOTALKG', 'TTKG', 'KG', 'WEIGHT'];

            $getFirstNumeric = function (array $data, array $possibleKeys) {
                $normalizedMap = [];

                foreach ($data as $key => $value) {
                    if ($key === null || $key === '') continue;

                    $normKey = strtoupper((string)$key);
                    $normKey = str_replace([' ', '/', '-', '.'], '', $normKey);
                    $normalizedMap[$normKey] = $value;
                }

                foreach ($possibleKeys as $search) {
                    $normSearch = strtoupper($search);
                    $normSearch = str_replace([' ', '/', '-', '.'], '', $normSearch);

                    foreach ($normalizedMap as $nKey => $value) {
                        if (
                            strpos($nKey, $normSearch) !== false &&
                            (is_numeric($value) || (is_string($value) && is_numeric(trim($value))))
                        ) {
                            return (float) trim($value);
                        }
                    }
                }

                return 0;
            };

            foreach ($rows as $row) {
                $data = $row->data ?? [];

                $totalCtn += $getFirstNumeric($data, $ctnKeys);
                $totalQty += $getFirstNumeric($data, $qtyKeys);
                $totalCbm += $getFirstNumeric($data, $cbmKeys);
                $totalKg  += $getFirstNumeric($data, $kgKeys);
            }

            $container->summary = [
                'total_ctn' => round($totalCtn, 2),
                'total_qty' => round($totalQty, 2),
                'total_cbm' => round($totalCbm, 3),
                'total_kg'  => round($totalKg, 2),
            ];
        });

        return view('admin.container', compact('containers'));
    }

    public function create()
    {
        return view('admin.container_create');
    }

    private function isValidExcelFormat($rows, $header)
    {
        if (empty($header) || count($rows) < 2) return false;

        $validKeywords = [
            'MARK', 'DESCRIPTION', 'DESC', 'CTN', 'CTNS', 'QTY', 'TOTALQTY', 'ITLQTY', 'ITL QTY',
            'UNIT', 'CBM', 'TOTAL CBM', 'KG', 'TOTAL KG',
            'METAL BUCKLE', 'WATCH MOVEMENT', 'STEEL BOTTLE',
            'MEHULPAID', 'ITEM NO', 'ITEM NO.', 'SAHILPAID', 'PINAKIN', 'GST',
            'MOON LAMP', 'TRANSPARENT BOTTLE', 'PLASTIC FONDANT',
        ];

        $headerText      = implode(' ', array_map('strtoupper', $header));
        $requiredHeaders = ['CTN', 'QTY', 'DESCRIPTION', 'DESC'];

        $hasValidHeaders = false;
        foreach ($requiredHeaders as $key) {
            if (stripos($headerText, $key) !== false) {
                $hasValidHeaders = true;
                break;
            }
        }

        if (!$hasValidHeaders) return false;

        $dataPreview = '';
        for ($i = 0; $i < min(5, count($rows)); $i++) {
            $rowText = implode(' ', array_slice($rows[$i], 0, 10));
            $dataPreview .= ' ' . strtoupper((string)$rowText);
        }

        $validMatches = 0;
        foreach ($validKeywords as $keyword) {
            if (stripos($headerText . $dataPreview, strtoupper($keyword)) !== false) {
                $validMatches++;
            }
        }

        return $validMatches >= 3;
    }

    private function normalizeKey($value): string
    {
        $norm = strtoupper((string)$value);
        return str_replace([' ', '/', '-', '.'], '', $norm);
    }

    public function store(Request $request)
    {
        $request->validate([
            'container_name'   => 'required|string',
            'container_number' => 'required|string|unique:containers,container_number',
            'container_date'   => 'required|date',
            'excel_file'       => 'required|file|mimes:xls,xlsx',
        ]);

        $file   = $request->file('excel_file');
        $sheets = Excel::toArray([], $file);
        $rows   = $sheets[0] ?? [];

        if (count($rows) < 2) {
            return back()
                ->withErrors(['excel_file' => 'Excel file is empty.'])
                ->withInput();
        }

        // HEADER DETECTION
        $headerRowIndex = null;
        $header         = [];

        foreach ($rows as $i => $row) {
            $trimmed  = array_map(fn($v) => trim((string)$v), $row);
            $nonEmpty = array_filter($trimmed, fn($v) => $v !== '');
            if (empty($nonEmpty)) continue;

            if (count($nonEmpty) >= 4) {
                $headerRowIndex = $i;
                $header         = $trimmed;
                break;
            }
        }

        if ($headerRowIndex === null) {
            return back()
                ->withErrors(['excel_file' => 'Header row not found in Excel.'])
                ->withInput();
        }

        if (!$this->isValidExcelFormat($rows, $header)) {
            return back()
                ->withErrors(['excel_file' => 'Only MEHUL / SAHIL / PINAKIN / GST loading list formats allowed.'])
                ->withInput();
        }

        // COLUMN INDEXES
        $essentialColumns = [
            'desc_col'      => null,
            'ctn_col'       => null,
            'qty_col'       => null,
            'totalqty_col'  => null,
            'unit_col'      => null,
            'price_col'     => null,
            'amount_col'    => null,
            'cbm_col'       => null,
            'totalcbm_col'  => null,
            'kg_col'        => null,
            'totalkg_col'   => null,
            'itemno_col'    => null,
        ];

        foreach ($header as $colIndex => $headingText) {
            if (empty($headingText)) continue;

            $normalized = $this->normalizeKey($headingText);

            if (strpos($normalized, 'DESCRIPTION') !== false || strpos($normalized, 'DESC') !== false) {
                $essentialColumns['desc_col'] = $colIndex;
            } elseif (strpos($normalized, 'CTN') !== false || strpos($normalized, 'CTNS') !== false) {
                $essentialColumns['ctn_col'] = $colIndex;
            } elseif (
                strpos($normalized, 'ITLQTY') !== false ||
                strpos($normalized, 'TOTALQTY') !== false ||
                strpos($normalized, 'TTLQTY') !== false
            ) {
                $essentialColumns['totalqty_col'] = $colIndex;
            } elseif (strpos($normalized, 'QTY') !== false) {
                $essentialColumns['qty_col'] = $colIndex;
            } elseif (strpos($normalized, 'UNIT') !== false) {
                $essentialColumns['unit_col'] = $colIndex;
            } elseif (strpos($normalized, 'PRICE') !== false) {
                $essentialColumns['price_col'] = $colIndex;
            } elseif (strpos($normalized, 'AMOUNT') !== false) {
                $essentialColumns['amount_col'] = $colIndex;
            } elseif (strpos($normalized, 'TOTALCBM') !== false || strpos($normalized, 'ITLCBM') !== false) {
                $essentialColumns['totalcbm_col'] = $colIndex;
            } elseif (strpos($normalized, 'CBM') !== false) {
                $essentialColumns['cbm_col'] = $colIndex;
            } elseif (strpos($normalized, 'TOTALKG') !== false || strpos($normalized, 'TTKG') !== false) {
                $essentialColumns['totalkg_col'] = $colIndex;
            } elseif (strpos($normalized, 'KG') !== false) {
                $essentialColumns['kg_col'] = $colIndex;
            } elseif (
                strpos($normalized, 'MARKNO') !== false ||
                strpos($normalized, 'MARK') !== false ||
                strpos($normalized, 'ITEMNO') !== false ||
                strpos($normalized, 'ITEM') !== false
            ) {
                $essentialColumns['itemno_col'] = $colIndex;
            }
        }

        if (is_null($essentialColumns['itemno_col'])) {
            return back()
                ->withErrors(['excel_file' => 'Mark / Item column not found in Excel (expected headers like MARK NO / Mark_No / Item_No).'])
                ->withInput();
        }

        // ROWS CLEANING
        $dataRows          = array_slice($rows, $headerRowIndex + 1);
        $cleanedRows       = [];
        $unmatchedRowsData = [];

        foreach ($dataRows as $offset => $row) {
            $trimmedRow    = array_map(fn($v) => trim((string)$v), $row);
            $nonEmptyCells = array_filter($trimmedRow, fn($v) => $v !== '');
            if (count($nonEmptyCells) < 2) continue;

            $rowText = strtoupper(implode(' ', $trimmedRow));
            if (
                stripos($rowText, 'TOTAL') !== false ||
                stripos($rowText, 'TTL')   !== false ||
                stripos($rowText, 'GRAND') !== false
            ) {
                continue;
            }

            $descValue = '';
            if ($essentialColumns['desc_col'] !== null) {
                $descValue = trim($row[$essentialColumns['desc_col']] ?? '');
            }

            if ($essentialColumns['desc_col'] !== null && $descValue === '' && count($nonEmptyCells) >= 1) {
                continue;
            }

            $cleanedRows[] = [
                'row'    => $row,
                'offset' => $offset,
            ];
        }

        if (empty($cleanedRows)) {
            return back()
                ->withErrors(['excel_file' => 'No valid item rows found in Excel.'])
                ->withInput();
        }

        // FORMULA CHECK
        $cleanNumber = function ($value) {
            if (is_string($value)) {
                $value = str_replace(',', '', trim($value));
            }
            return is_numeric($value) ? (float)$value : 0;
        };

        $formulaErrors = [];

        foreach ($cleanedRows as $item) {
            $row    = $item['row'];
            $offset = $item['offset'];

            $ctn  = $essentialColumns['ctn_col']      !== null ? $cleanNumber($row[$essentialColumns['ctn_col']]      ?? 0) : 0;
            $qty  = $essentialColumns['qty_col']      !== null ? $cleanNumber($row[$essentialColumns['qty_col']]      ?? 0) : 0;
            $ttlQ = $essentialColumns['totalqty_col'] !== null ? $cleanNumber($row[$essentialColumns['totalqty_col']] ?? 0) : 0;
            $cbm  = $essentialColumns['cbm_col']      !== null ? $cleanNumber($row[$essentialColumns['cbm_col']]      ?? 0) : 0;
            $ttlC = $essentialColumns['totalcbm_col'] !== null ? $cleanNumber($row[$essentialColumns['totalcbm_col']] ?? 0) : 0;
            $kg   = $essentialColumns['kg_col']       !== null ? $cleanNumber($row[$essentialColumns['kg_col']]       ?? 0) : 0;
            $ttlK = $essentialColumns['totalkg_col']  !== null ? $cleanNumber($row[$essentialColumns['totalkg_col']]  ?? 0) : 0;

            $price     = $essentialColumns['price_col']  !== null ? $cleanNumber($row[$essentialColumns['price_col']]  ?? 0) : 0;
            $ttlAmount = $essentialColumns['amount_col'] !== null ? $cleanNumber($row[$essentialColumns['amount_col']] ?? 0) : 0;

            $desc = $essentialColumns['desc_col']   !== null ? (string)($row[$essentialColumns['desc_col']]   ?? '') : '';
            $mark = $essentialColumns['itemno_col'] !== null ? (string)($row[$essentialColumns['itemno_col']] ?? '') : '';

            $expTtlQty    = $qty * $ctn;
            $expTtlCbm    = $cbm * $ctn;
            $expTtlKg     = $kg  * $ctn;
            $expTtlAmount = ($qty * $ctn) * $price;

            $rowErrors = [];

            if (abs($ttlQ - $expTtlQty) > 0.01) {
                $rowErrors['TOTAL QTY'] = [
                    'actual'   => $ttlQ,
                    'expected' => $expTtlQty,
                ];
            }

            if (abs($ttlC - $expTtlCbm) > 0.0005) {
                $rowErrors['TOTAL CBM'] = [
                    'actual'   => $ttlC,
                    'expected' => $expTtlCbm,
                ];
            }

            if (abs($ttlK - $expTtlKg) > 0.01) {
                $rowErrors['TOTAL KG'] = [
                    'actual'   => $ttlK,
                    'expected' => $expTtlKg,
                ];
            }

            if ($essentialColumns['amount_col'] !== null && $essentialColumns['price_col'] !== null) {
                if (abs($ttlAmount - $expTtlAmount) > 0.01) {
                    $rowErrors['TOTAL AMOUNT'] = [
                        'actual'   => $ttlAmount,
                        'expected' => $expTtlAmount,
                    ];
                }
            }

            if (!empty($rowErrors)) {
                $rowData = [];
                foreach ($header as $colIndex => $headingText) {
                    $value = $row[$colIndex] ?? null;
                    if (is_string($value)) $value = trim($value);
                    $rowData[$headingText] = $value;
                }

                $formulaErrors[] = [
                    'excel_row'   => $headerRowIndex + 1 + $offset,
                    'mark_no'     => $mark,
                    'description' => $desc,
                    'errors'      => $rowErrors,
                    'data'        => $rowData,
                ];
            }
        }

        // MARK CHECK
        $marksFromExcel = [];
        foreach ($cleanedRows as $item) {
            $row     = $item['row'];
            $rawMark = $row[$essentialColumns['itemno_col']] ?? null;
            $mark    = trim((string)($rawMark ?? ''));
            if ($mark !== '') {
                $marksFromExcel[] = $mark;
            }
        }

        $marksFromExcel = array_values(array_unique($marksFromExcel));

        if (empty($marksFromExcel)) {
            return back()
                ->withErrors(['excel_file' => 'No mark numbers found in Excel file.'])
                ->withInput();
        }

        $validMarks = MarkList::whereIn('mark_no', $marksFromExcel)
            ->where('status', 'active')
            ->pluck('mark_no')
            ->toArray();

        $unmatchedMarks = array_values(array_diff($marksFromExcel, $validMarks));

        $markErrors = [];

        if (!empty($unmatchedMarks)) {
            foreach ($cleanedRows as $item) {
                $row     = $item['row'];
                $offset  = $item['offset'];
                $rowMark = trim((string)($row[$essentialColumns['itemno_col']] ?? ''));

                if ($rowMark === '' || !in_array($rowMark, $unmatchedMarks)) {
                    continue;
                }

                $rowData = [];
                foreach ($header as $colIndex => $headingText) {
                    $value = $row[$colIndex] ?? null;
                    if (is_string($value)) $value = trim($value);
                    $rowData[$headingText] = $value;
                }

                $markErrors[] = [
                    'excel_row' => $headerRowIndex + 1 + $offset,
                    'mark_no'   => $rowMark,
                    'data'      => $rowData,
                ];
            }
        }

        if (!empty($formulaErrors) || !empty($markErrors)) {
            return back()
                ->withInput()
                ->with([
                    'formula_errors' => $formulaErrors,
                    'mark_errors'    => $markErrors,
                ]);
        }

        // STEP 1: Marks → customers mapping + grouping
        $markRecords = MarkList::whereIn('mark_no', $marksFromExcel)
            ->where('status', 'active')
            ->get();

        $markToCustomerId = [];
        $markToSnapshot   = [];

        foreach ($markRecords as $mr) {
            $markToCustomerId[$mr->mark_no] = $mr->customer_id;

            $markToSnapshot[$mr->mark_no] = [
                'customer_name' => $mr->customer_name,
                'company_name'  => $mr->company_name,
                'mobile_no'     => $mr->mobile_no,
            ];
        }

        $groupedByCustomer = [];

        foreach ($cleanedRows as $item) {
            $row    = $item['row'];
            $offset = $item['offset'];

            $rawMark = $row[$essentialColumns['itemno_col']] ?? null;
            $mark    = trim((string)($rawMark ?? ''));

            if ($mark === '') {
                continue;
            }

            $customerId = $markToCustomerId[$mark] ?? null;
            if (!$customerId) {
                continue;
            }

            if (!isset($groupedByCustomer[$customerId])) {
                $groupedByCustomer[$customerId] = [];
            }

            $groupedByCustomer[$customerId][] = [
                'row'    => $row,
                'offset' => $offset,
                'mark'   => $mark,
            ];
        }

        // STEP 2: Container + ContainerRows save
        $container = Container::create([
            'container_name'   => $request->container_name,
            'container_number' => $request->container_number,
            'container_date'   => $request->container_date,
            'status'           => 'pending',
        ]);

        $path = $file->store('containers');
        $container->update(['excel_file' => $path]);

        $savedCount = 0;

        foreach ($cleanedRows as $item) {
            $row    = $item['row'];
            $offset = $item['offset'];

            $data = [];
            foreach ($header as $colIndex => $headingText) {
                $value = $row[$colIndex] ?? null;
                if (is_string($value)) $value = trim($value);
                $data[$headingText] = $value;
            }

            ContainerRow::create([
                'container_id' => $container->id,
                'row_index'    => $headerRowIndex + 1 + $offset,
                'data'         => $data,
            ]);

            $savedCount++;
        }

        // STEP 3: per-customer invoices + invoice items
        $invoiceCount = 0;

        foreach ($groupedByCustomer as $customerId => $rowsForCustomer) {
            if (empty($rowsForCustomer)) {
                continue;
            }

            $firstMark = $rowsForCustomer[0]['mark'];
            $snap      = $markToSnapshot[$firstMark] ?? null;

            // ✅ Customer User model वरून fetch करा (customer_id string आहे जसे CID-2025-000001)
            $customerUser = \App\Models\User::where('customer_id', $customerId)->first();

            $invoice = new Invoice();
            $invoice->container_id = $container->id;
            $invoice->customer_id  = $customerUser->id ?? null; // ✅ integer id store करतोय
            $invoice->mark_no      = $firstMark;

            $invoice->invoice_number = $this->generateInvoiceNumber();

            // invoice_date = container_date
            $invoice->invoice_date = $container->container_date;

            // due_date = container_date + 10 days
            $invoice->due_date = Carbon::parse($invoice->invoice_date)
                ->addDays(10)
                ->format('Y-m-d');

            // ✅ Snapshot data from MarkList (backward compatibility)
            if ($snap) {
                $invoice->customer_name   = $snap['customer_name'] ?? null;
                $invoice->company_name    = $snap['company_name'] ?? null;
                $invoice->customer_mobile = $snap['mobile_no'] ?? null;
            }

            // ✅ User model वरून email, address, pincode घ्या
            if ($customerUser) {
                $invoice->customer_email   = $customerUser->email   ?? null;
                $invoice->customer_address = $customerUser->address ?? null;
                $invoice->pincode          = $customerUser->pincode ?? null;
            }

            $invoice->final_amount          = 0;
            $invoice->gst_percent           = 0;
            $invoice->gst_amount            = 0;
            $invoice->final_amount_with_gst = 0;

            $uniqueMarks    = array_unique(array_column($rowsForCustomer, 'mark'));
            $invoice->notes = 'Auto-created from Container ' . $container->container_number
                . ' for Mark(s): ' . implode(', ', $uniqueMarks);
            $invoice->pdf_path = null;
            $invoice->status   = 'pending';

            $invoice->save();
            $invoiceCount++;

            $totalAmount = 0;

            foreach ($rowsForCustomer as $item) {
                $row    = $item['row'];
                $offset = $item['offset'];

                $description = $essentialColumns['desc_col']      !== null ? ($row[$essentialColumns['desc_col']]      ?? null) : null;
                $ctn         = $essentialColumns['ctn_col']       !== null ? (int)   ($row[$essentialColumns['ctn_col']]       ?? 0) : 0;
                $qty         = $essentialColumns['qty_col']       !== null ? (int)   ($row[$essentialColumns['qty_col']]       ?? 0) : 0;
                $ttlQty      = $essentialColumns['totalqty_col']  !== null ? (int)   ($row[$essentialColumns['totalqty_col']]  ?? 0) : $qty;
                $unit        = $essentialColumns['unit_col']      !== null ? ($row[$essentialColumns['unit_col']]      ?? null) : null;
                $price       = $essentialColumns['price_col']     !== null ? (float) ($row[$essentialColumns['price_col']]     ?? 0) : 0;
                $ttlAmount   = $essentialColumns['amount_col']    !== null ? (float) ($row[$essentialColumns['amount_col']]    ?? 0) : 0;
                $cbm         = $essentialColumns['cbm_col']       !== null ? (float) ($row[$essentialColumns['cbm_col']]       ?? 0) : 0;
                $ttlCbm      = $essentialColumns['totalcbm_col']  !== null ? (float) ($row[$essentialColumns['totalcbm_col']]  ?? $cbm) : $cbm;
                $kg          = $essentialColumns['kg_col']        !== null ? (float) ($row[$essentialColumns['kg_col']]        ?? 0) : 0;
                $ttlKg       = $essentialColumns['totalkg_col']   !== null ? (float) ($row[$essentialColumns['totalkg_col']]   ?? $kg) : $kg;

                $rowIndex = $headerRowIndex + 1 + $offset;

                InvoiceItem::create([
                    'invoice_id'          => $invoice->id,
                    'container_id'        => $container->id,
                    'container_row_index' => $rowIndex,
                    'description'         => $description,
                    'ctn'                 => $ctn,
                    'qty'                 => $qty,
                    'ttl_qty'             => $ttlQty,
                    'unit'                => $unit,
                    'price'               => $price,
                    'ttl_amount'          => $ttlAmount,
                    'cbm'                 => $cbm,
                    'ttl_cbm'             => $ttlCbm,
                    'kg'                  => $kg,
                    'ttl_kg'              => $ttlKg,
                    'shop_no'             => null,
                ]);

                $totalAmount += $ttlAmount;
            }

            $invoice->final_amount          = $totalAmount;
            $invoice->gst_percent           = 0;
            $invoice->gst_amount            = 0;
            $invoice->final_amount_with_gst = $totalAmount;

            $invoice->save();
        }

        $msg = "Container '{$container->container_number}' created with {$savedCount} rows and {$invoiceCount} customer invoice(s).";
        return redirect()->route('containers.index')->with('success', $msg);
    }

    public function show(Container $container)
    {
        $container->load('rows');
    
        // paid invoices च्या row indexes collect करा
        $lockedRowIndexes = \App\Models\Invoice::where('invoices.container_id', $container->id)
            ->where('invoices.status', 'paid')
            ->join('invoice_items', 'invoices.id', '=', 'invoice_items.invoice_id')
            ->pluck('invoice_items.container_row_index')
            ->filter()
            ->unique()
            ->values()
            ->toArray();
    
        return view('admin.container_show', compact('container', 'lockedRowIndexes'));
    }

    public function updateRows(Request $request, Container $container)
    {
        $rowsInput = $request->input('rows', []);

        foreach ($rowsInput as $rowId => $cols) {
            $row = ContainerRow::where('container_id', $container->id)
                ->where('id', $rowId)
                ->first();

            if (!$row) {
                continue;
            }

            // 1) update container_rows.data
            $data = $row->data ?? [];
            foreach ($cols as $colHeader => $value) {
                $data[$colHeader] = $value;
            }
            $row->update(['data' => $data]);

            // 2) normalize keys
            $normalizedMap = [];
            foreach ($data as $key => $value) {
                if ($key === null || $key === '') {
                    continue;
                }

                $normKey = strtoupper((string)$key);
                $normKey = str_replace([' ', '/', '-', '.'], '', $normKey);
                $normalizedMap[$normKey] = $value;
            }

            // helper: get first numeric value from given keys
            $getFirstNumeric = function (array $map, array $possibleKeys) {
                foreach ($possibleKeys as $search) {
                    $normSearch = strtoupper($search);
                    $normSearch = str_replace([' ', '/', '-', '.'], '', $normSearch);

                    foreach ($map as $nKey => $value) {
                        if (strpos($nKey, $normSearch) !== false) {
                            if (is_numeric($value)) {
                                return (float)$value;
                            }
                            if (is_string($value) && is_numeric(trim($value))) {
                                return (float)trim($value);
                            }
                        }
                    }
                }
                return 0;
            };

            // 3) read values – QTY vs TTLQTY separately
            $ctnKeys    = ['CTN', 'CTNS'];
            $qtyKeys    = ['QTY', 'PCS', 'PIECES'];              // per-carton qty
            $ttlQtyKeys = ['ITLQTY', 'TOTALQTY', 'TTLQTY'];      // total qty
            $cbmKeys    = ['TOTALCBM', 'TTLCBM', 'ITLCBM', 'CBM'];
            $kgKeys     = ['TOTALKG', 'TTKG', 'KG', 'WEIGHT'];
            $amountKeys = ['AMOUNT', 'TTLAMOUNT', 'TOTALAMOUNT'];

            $ctn = $getFirstNumeric($normalizedMap, $ctnKeys);

            // per carton qty
            $qty = $getFirstNumeric($normalizedMap, $qtyKeys);

            // total qty direct from TOTALQTY/TTLQTY/ITLQTY
            $ttlQ = $getFirstNumeric($normalizedMap, $ttlQtyKeys);

            // if total column is 0 then compute ctn * qty
            if ($ttlQ == 0 && $ctn && $qty) {
                $ttlQ = $ctn * $qty;
            }

            $cbm  = $getFirstNumeric($normalizedMap, ['CBM']);
            $ttlC = $getFirstNumeric($normalizedMap, ['TOTALCBM', 'TTLCBM', 'ITLCBM']);
            if ($ttlC == 0 && $cbm && $ctn) {
                $ttlC = $cbm * $ctn;
            }

            $kg   = $getFirstNumeric($normalizedMap, ['KG', 'WEIGHT']);
            $ttlK = $getFirstNumeric($normalizedMap, ['TOTALKG', 'TTKG']);
            if ($ttlK == 0 && $kg && $ctn) {
                $ttlK = $kg * $ctn;
            }

            $price  = $getFirstNumeric($normalizedMap, ['PRICE', 'RATE']);
            $amount = $getFirstNumeric($normalizedMap, $amountKeys);
            if ($amount == 0 && $price && $ttlQ) {
                $amount = $price * $ttlQ;
            }

            // 4) get description
            $desc = null;
            foreach (['DESCRIPTION', 'DESC'] as $dKey) {
                $normD = str_replace([' ', '/', '-', '.'], '', strtoupper($dKey));
                foreach ($normalizedMap as $nKey => $v) {
                    if (strpos($nKey, $normD) !== false) {
                        $desc = is_string($v) ? trim($v) : $v;
                        break 2;
                    }
                }
            }

            $rowIndex = $row->row_index;

            // 5) find linked invoice_items
            $items = InvoiceItem::where('container_id', $container->id)
                ->where('container_row_index', $rowIndex)
                ->get();

            if ($items->isEmpty() && $desc) {
                $items = InvoiceItem::where('container_id', $container->id)
                    ->whereNull('container_row_index')
                    ->where('description', $desc)
                    ->get();
            }

            // 6) update invoice_items + recalc invoice totals
            foreach ($items as $item) {
                $item->description = $desc;
                $item->ctn         = $ctn;
                $item->qty         = $qty;   // per carton
                $item->ttl_qty     = $ttlQ;  // total
                $item->price       = $price;
                $item->ttl_amount  = $amount;
                $item->cbm         = $cbm;
                $item->ttl_cbm     = $ttlC;
                $item->kg          = $kg;
                $item->ttl_kg      = $ttlK;
                $item->save();

                $invoice = $item->invoice;
                if ($invoice) {
                    $newBaseAmount = InvoiceItem::where('invoice_id', $invoice->id)
                        ->sum('ttl_amount');

                    $taxType     = $invoice->tax_type;
                    $cgstPercent = (float) ($invoice->cgst_percent ?? 0);
                    $sgstPercent = (float) ($invoice->sgst_percent ?? 0);
                    $igstPercent = (float) ($invoice->igst_percent ?? 0);

                    $gstPercent = 0;
                    if ($taxType === 'gst') {
                        $gstPercent = $cgstPercent + $sgstPercent;
                    } elseif ($taxType === 'igst') {
                        $gstPercent = $igstPercent;
                    }

                    $gstAmount    = $newBaseAmount * $gstPercent / 100;
                    $finalWithGst = $newBaseAmount + $gstAmount;

                    $invoice->final_amount          = $newBaseAmount;
                    $invoice->gst_amount            = $gstAmount;
                    $invoice->final_amount_with_gst = $finalWithGst;
                    $invoice->gst_percent           = $gstPercent;
                    $invoice->save();
                }
            }
        }

        return redirect()
            ->route('containers.show', $container->id)
            ->with('success', 'Excel rows updated successfully.');
    }

    public function updateStatus(Request $request, Container $container)
    {
        $request->validate([
            'status' => 'required|in:container-ready,export-custom,international-transit,arrived-at-india,import-custom,warehouse,domestic-distribution,out-for-delivery,delivered',
        ]);

        $container->status = $request->status;
        $container->save();

        if ($request->wantsJson() || $request->ajax()) {
            return response()->json([
                'success' => true,
                'status'  => $container->status,
            ]);
        }

        return back()->with('success', 'Container status updated.');
    }

    public function destroy(Container $container)
    {
        $container->delete();

        if (request()->wantsJson() || request()->ajax()) {
            return response()->json([
                'success' => true,
                'message' => 'Container deleted',
            ]);
        }

        return redirect()
            ->route('containers.index')
            ->with('success', 'Container deleted.');
    }

    private function generateInvoiceNumber(): string
    {
        $year = now()->format('Y');

        $last = Invoice::whereYear('created_at', $year)
            ->orderBy('id', 'desc')
            ->first();

        if ($last) {
            $parts = explode('-', $last->invoice_number);
            $seq   = 0;

            if (count($parts) === 3) {
                $seq = (int) $parts[2];
            }

            $nextSeq = $seq + 1;
        } else {
            $nextSeq = 1;
        }

        return 'INV-' . $year . '-' . str_pad($nextSeq, 6, '0', STR_PAD_LEFT);
    }

    public function downloadPdf(Container $container)
    {
        $container->load('rows');

        $pdf = Pdf::loadView('admin.container_pdf', [
            'container' => $container,
        ])->setPaper('a4', 'landscape');

        $fileName = 'container-'.$container->container_number.'.pdf';

        return $pdf->download($fileName);
    }

    public function downloadExcel(Container $container)
    {
        if (!$container->excel_file) {
            abort(404, 'Excel file not found on record.');
        }

        // Stored path like "containers/abc.xlsx"
        $path = $container->excel_file;

        if (!Storage::exists($path)) {
            abort(404, 'Excel file missing on server.');
        }

        $fileName = 'container-'.$container->container_number.'.xlsx';

        return Storage::download($path, $fileName);
    }

    public function popupPopup(Container $container)
    {
        // existing show सारखाच data वापरू
        $container->load('rows');

        // summary आधीपासून index() मध्ये जसा काढतोस तसाच logic reuse
        $rows = $container->rows ?? collect();

        $totalCtn = 0;
        $totalQty = 0;
        $totalCbm = 0;
        $totalKg  = 0;

        $ctnKeys = ['CTN', 'CTNS'];
        $qtyKeys = ['ITLQTY', 'TOTALQTY', 'TTLQTY', 'QTY', 'PCS', 'PIECES'];
        $cbmKeys = ['TOTALCBM', 'TTLCBM', 'ITLCBM', 'CBM'];
        $kgKeys  = ['TOTALKG', 'TTKG', 'KG', 'WEIGHT'];

        $getFirstNumeric = function (array $data, array $possibleKeys) {
            $normalizedMap = [];
            foreach ($data as $key => $value) {
                if ($key === null) continue;
                $normKey = strtoupper((string)$key);
                $normKey = str_replace([' ', ',', '-', '.', "\n", "\r", "\t"], '', $normKey);
                $normalizedMap[$normKey] = $value;
            }

            foreach ($possibleKeys as $search) {
                $normSearch = strtoupper($search);
                $normSearch = str_replace([' ', ',', '-', '.', "\n", "\r", "\t"], '', $normSearch);

                foreach ($normalizedMap as $nKey => $value) {
                    if (strpos($nKey, $normSearch) !== false) {
                        if (is_numeric($value)) {
                            return (float)$value;
                        }
                        if (is_string($value) && is_numeric(trim($value))) {
                            return (float)trim($value);
                        }
                    }
                }
            }
            return 0;
        };

        foreach ($rows as $row) {
            $data = $row->data ?? [];
            if (!is_array($data)) continue;

            $totalCtn += $getFirstNumeric($data, $ctnKeys);
            $totalQty += $getFirstNumeric($data, $qtyKeys);
            $totalCbm += $getFirstNumeric($data, $cbmKeys);
            $totalKg  += $getFirstNumeric($data, $kgKeys);
        }

        $summary = [
            'total_ctn' => round($totalCtn, 2),
            'total_qty' => round($totalQty, 2),
            'total_cbm' => round($totalCbm, 3),
            'total_kg'  => round($totalKg, 2),
        ];

        return view('admin.partials.container_popup_readonly', [
            'container' => $container,
            'summary'   => $summary,
        ]);
    }
}