<?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;

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,
            ];
        }

        // MARK CHECK: strict - collect ALL marks + unmatched rows
        $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));

        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;
                }

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

            return back()
                ->withErrors(['excel_file' => 'Some mark numbers are not found in Mark List. Container not created.'])
                ->withInput()
                ->with('unmatched_rows', $unmatchedRowsData);
        }

        // 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;

            $invoice = new Invoice();
            $invoice->container_id   = $container->id;
            // $invoice->customer_id    = $customerId;
            $invoice->invoice_number = $this->generateInvoiceNumber();
            $invoice->invoice_date   = now()->toDateString();
            $invoice->due_date       = null;

            if ($snap) {
                $invoice->customer_name   = $snap['customer_name'] ?? null;
                $invoice->company_name    = $snap['company_name'] ?? null;
                $invoice->customer_mobile = $snap['mobile_no'] ?? null;
            }

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

            $invoice->customer_email   = null;
            $invoice->customer_address = null;
            $invoice->pincode          = null;

            $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'];

                $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;

                InvoiceItem::create([
                    'invoice_id' => $invoice->id,
                    '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');
        return view('admin.container_show', compact('container'));
    }

    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;

            $data = $row->data ?? [];

            foreach ($cols as $colHeader => $value) {
                $data[$colHeader] = $value;
            }

            $row->update([
                'data' => $data,
            ]);
        }

        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:pending,in-progress,completed,cancelled']);

        $container->update(['status' => $request->status]);

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

    public function destroy(Container $container)
    {
        $container->delete();
        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);
    }
}