<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\PriceListingSetup;
use App\Models\InventoryJournal;
use App\Models\InventoryCategory;
use App\Models\CompanySettings;
use App\Models\Warehouse;
use App\Models\SupplierDocumentHeader;
use App\Models\SupplierDocumentLine;
use App\Models\DocumentHeader;
use App\Models\DocumentLine;
use App\Models\WarehouseTransfer;
use App\Models\WarehouseTransferLine;
use PDF;
use Carbon\Carbon;
use Maatwebsite\Excel\Facades\Excel;
use App\Exports\InventoryMovementReportExport;
use DB;

class InventoryMovementReportController extends Controller
{
    // Display initial form
    public function index()
    {
        $items = PriceListingSetup::orderBy('code')->get();
        $warehouses = Warehouse::all();
        $currentDate = Carbon::now()->format('Y-m-d');
        
        return view('reports.inventory-movement-report', compact('items', 'warehouses', 'currentDate'));
    }

    // Generate inventory movement report
    public function generate(Request $request)
    {
        $request->validate([
            'item_from' => 'required',
            'item_to' => 'required',
            'date_from' => 'required|date',
            'date_to' => 'required|date|after_or_equal:date_from',
        ]);

        $itemFrom = $request->item_from;
        $itemTo = $request->item_to;
        $dateFrom = $request->date_from;
        $dateTo = $request->date_to;
        $warehouseFilter = $request->warehouse_id;
        $company = CompanySettings::first();

        $items = PriceListingSetup::orderBy('code')->get();
        $warehouses = Warehouse::all();

        // Get all items in the selected range
        $selectedItems = PriceListingSetup::whereBetween('code', [$itemFrom, $itemTo])
            ->orderBy('code')
            ->get();

        $movementData = [];
        $totalReceived = 0;
        $totalIssued = 0;
        
        foreach ($selectedItems as $item) {
            // Get inventory journals for this item within the date range
            $journals = InventoryJournal::with(['warehouse'])
                ->where('item_id', $item->id)
                ->when($warehouseFilter, function($query) use ($warehouseFilter) {
                    return $query->where('warehouse_id', $warehouseFilter);
                })
                ->whereBetween('date', [$dateFrom, $dateTo])
                ->orderBy('date')
                ->get();

            // Get goods received data
            $goodsReceived = $this->getGoodsReceivedData($item, $dateFrom, $dateTo, $warehouseFilter);
            
            // Get invoice data
            $invoices = $this->getInvoiceData($item, $dateFrom, $dateTo, $warehouseFilter);
            
            // Get warehouse transfer data
            $transfers = $this->getTransferData($item, $dateFrom, $dateTo, $warehouseFilter);
            
            // Calculate opening balance (before the date range)
            $openingBalance = $this->calculateOpeningBalance($item, $dateFrom, $warehouseFilter);

            // Process each journal entry
            $runningBalance = $openingBalance;
            
            // Add opening balance row
            $category = InventoryCategory::find($item->category_id);
            $categoryName = $category ? $category->name : 'N/A';
            
            $movementData[] = [
                'type' => 'opening_balance',
                'date' => $dateFrom,
                'item_code' => $item->code,
                'item_description' => $item->description1,
                'warehouse' => $warehouseFilter ? Warehouse::find($warehouseFilter)->description : 'All Warehouses',
                'group' => $categoryName,
                'reference' => 'Opening Balance',
                'qty_in' => 0,
                'qty_out' => 0,
                'qty_available' => $openingBalance,
            ];

            // Combine all transactions
            $allTransactions = collect([])
                ->merge($journals->map(function($journal) use ($item) {
                    return [
                        'date' => $journal->date,
                        'type' => 'journal',
                        'reference' => $journal->reference,
                        'qty_in' => $journal->quantity_in,
                        'qty_out' => $journal->quantity_out,
                        'warehouse' => $journal->warehouse ? $journal->warehouse->description : 'N/A'
                    ];
                }))
                ->merge($goodsReceived->map(function($grn) {
                    return [
                        'date' => $grn->document_date,
                        'type' => 'goods_received',
                        'reference' => $grn->document_number,
                        'qty_in' => $grn->confirmed_quantity,
                        'qty_out' => 0,
                        'warehouse' => $grn->warehouse_name
                    ];
                }))
                ->merge($invoices->map(function($invoice) {
                    return [
                        'date' => $invoice->document_date,
                        'type' => 'invoice',
                        'reference' => $invoice->document_number,
                        'qty_in' => 0,
                        'qty_out' => $invoice->confirmed_quantity,
                        'warehouse' => $invoice->warehouse_name
                    ];
                }))
                ->merge($transfers->map(function($transfer) use ($item) {
                    $isIn = $transfer->to_warehouse_id == $transfer->warehouse_id;
                    return [
                        'date' => $transfer->transfer_date,
                        'type' => 'transfer',
                        'reference' => $transfer->document_number,
                        'qty_in' => $isIn ? $transfer->quantity : 0,
                        'qty_out' => !$isIn ? $transfer->quantity : 0,
                        'warehouse' => $transfer->warehouse_name
                    ];
                }))
                ->sortBy('date');

            // Process each transaction entry
            foreach ($allTransactions as $transaction) {
                // Update running balance
                $runningBalance += $transaction['qty_in'] - $transaction['qty_out'];

                // Add to movement data
                $movementData[] = [
                    'type' => 'transaction',
                    'date' => $transaction['date'],
                    'item_code' => $item->code,
                    'item_description' => $item->description1,
                    'warehouse' => $transaction['warehouse'],
                    'group' => $categoryName,
                    'reference' => $transaction['reference'],
                    'qty_in' => $transaction['qty_in'],
                    'qty_out' => $transaction['qty_out'],
                    'qty_available' => $runningBalance,
                ];

                // Add to totals
                $totalReceived += $transaction['qty_in'];
                $totalIssued += $transaction['qty_out'];
            }
            
            // Add closing balance row
            $movementData[] = [
                'type' => 'closing_balance',
                'date' => $dateTo,
                'item_code' => $item->code,
                'item_description' => $item->description1,
                'warehouse' => $warehouseFilter ? Warehouse::find($warehouseFilter)->description : 'All Warehouses',
                'group' => $categoryName,
                'reference' => 'Closing Balance',
                'qty_in' => 0,
                'qty_out' => 0,
                'qty_available' => $runningBalance,
            ];
        }

        $totalOnHand = $totalReceived - $totalIssued;

        return view('reports.inventory-movement-report', compact(
            'movementData',
            'itemFrom',
            'itemTo',
            'warehouseFilter',
            'dateFrom',
            'dateTo',
            'items',
            'warehouses',
            'company',
            'totalReceived',
            'totalIssued',
            'totalOnHand'
        ));
    }

    // Helper function to get goods received data
    private function getGoodsReceivedData($item, $dateFrom, $dateTo, $warehouseFilter)
    {
        $query = SupplierDocumentLine::join('supplier_document_headers', 'supplier_document_lines.supplier_document_header_id', '=', 'supplier_document_headers.id')
            ->join('warehouses', 'supplier_document_lines.warehouse_id', '=', 'warehouses.id')
            ->where('supplier_document_headers.type', 'goods_received')
            ->where('supplier_document_lines.item_id', $item->id)
            ->whereBetween('supplier_document_headers.document_date', [$dateFrom, $dateTo]);
            
        if ($warehouseFilter) {
            $query->where('supplier_document_lines.warehouse_id', $warehouseFilter);
        }
            
        return $query->select(
                'supplier_document_headers.document_date',
                'supplier_document_headers.document_number',
                'supplier_document_lines.confirmed_quantity',
                'warehouses.description as warehouse_name',
                'supplier_document_lines.warehouse_id'
            )
            ->get();
    }

    // Helper function to get invoice data
    private function getInvoiceData($item, $dateFrom, $dateTo, $warehouseFilter)
    {
        $query = DocumentLine::join('document_headers', 'document_lines.document_header_id', '=', 'document_headers.id')
            ->join('warehouses', 'document_lines.warehouse_id', '=', 'warehouses.id')
            ->where('document_headers.type', 'invoice')
            ->where('document_lines.item_id', $item->id)
            ->whereBetween('document_headers.document_date', [$dateFrom, $dateTo]);
            
        if ($warehouseFilter) {
            $query->where('document_lines.warehouse_id', $warehouseFilter);
        }
            
        return $query->select(
                'document_headers.document_date',
                'document_headers.document_number',
                'document_lines.confirmed_quantity',
                'warehouses.description as warehouse_name',
                'document_lines.warehouse_id'
            )
            ->get();
    }

    // Helper function to get transfer data
    private function getTransferData($item, $dateFrom, $dateTo, $warehouseFilter)
    {
        $query = WarehouseTransferLine::join('warehouse_transfers', 'warehouse_transfer_lines.transfer_id', '=', 'warehouse_transfers.id')
            ->join('warehouses', function($join) {
                $join->on('warehouse_transfers.from_warehouse_id', '=', 'warehouses.id')
                     ->orOn('warehouse_transfers.to_warehouse_id', '=', 'warehouses.id');
            })
            ->where('warehouse_transfer_lines.item_id', $item->id)
            ->whereBetween('warehouse_transfers.transfer_date', [$dateFrom, $dateTo]);
            
        if ($warehouseFilter) {
            $query->where(function($q) use ($warehouseFilter) {
                $q->where('warehouse_transfers.from_warehouse_id', $warehouseFilter)
                  ->orWhere('warehouse_transfers.to_warehouse_id', $warehouseFilter);
            });
        }
            
        return $query->select(
                'warehouse_transfers.transfer_date',
                'warehouse_transfers.document_number',
                'warehouse_transfer_lines.quantity',
                'warehouses.description as warehouse_name',
                'warehouse_transfers.from_warehouse_id',
                'warehouse_transfers.to_warehouse_id',
                'warehouses.id as warehouse_id'
            )
            ->get();
    }

    // Helper function to calculate opening balance
    private function calculateOpeningBalance($item, $dateFrom, $warehouseFilter)
    {
        $openingBalance = $item->original_quantity;
        
        // Subtract inventory journals before date range
        $journalBalance = InventoryJournal::where('item_id', $item->id)
            ->when($warehouseFilter, function($query) use ($warehouseFilter) {
                return $query->where('warehouse_id', $warehouseFilter);
            })
            ->where('date', '<', $dateFrom)
            ->get()
            ->reduce(function ($carry, $journal) {
                return $carry + $journal->quantity_in - $journal->quantity_out;
            }, 0);
            
        $openingBalance += $journalBalance;
        
        // Add goods received before date range
        $goodsReceived = SupplierDocumentLine::join('supplier_document_headers', 'supplier_document_lines.supplier_document_header_id', '=', 'supplier_document_headers.id')
            ->where('supplier_document_headers.type', 'goods_received')
            ->where('supplier_document_lines.item_id', $item->id)
            ->when($warehouseFilter, function($query) use ($warehouseFilter) {
                return $query->where('supplier_document_lines.warehouse_id', $warehouseFilter);
            })
            ->where('supplier_document_headers.document_date', '<', $dateFrom)
            ->sum('supplier_document_lines.confirmed_quantity');
            
        $openingBalance += $goodsReceived;
        
        // Subtract invoices before date range
        $invoices = DocumentLine::join('document_headers', 'document_lines.document_header_id', '=', 'document_headers.id')
            ->where('document_headers.type', 'invoice')
            ->where('document_lines.item_id', $item->id)
            ->when($warehouseFilter, function($query) use ($warehouseFilter) {
                return $query->where('document_lines.warehouse_id', $warehouseFilter);
            })
            ->where('document_headers.document_date', '<', $dateFrom)
            ->sum('document_lines.confirmed_quantity');
            
        $openingBalance -= $invoices;
        
        // Add transfers in before date range
        $transfersIn = WarehouseTransferLine::join('warehouse_transfers', 'warehouse_transfer_lines.transfer_id', '=', 'warehouse_transfers.id')
            ->where('warehouse_transfer_lines.item_id', $item->id)
            ->when($warehouseFilter, function($query) use ($warehouseFilter) {
                return $query->where('warehouse_transfers.to_warehouse_id', $warehouseFilter);
            })
            ->where('warehouse_transfers.transfer_date', '<', $dateFrom)
            ->sum('warehouse_transfer_lines.quantity');
            
        $openingBalance += $transfersIn;
        
        // Subtract transfers out before date range
        $transfersOut = WarehouseTransferLine::join('warehouse_transfers', 'warehouse_transfer_lines.transfer_id', '=', 'warehouse_transfers.id')
            ->where('warehouse_transfer_lines.item_id', $item->id)
            ->when($warehouseFilter, function($query) use ($warehouseFilter) {
                return $query->where('warehouse_transfers.from_warehouse_id', $warehouseFilter);
            })
            ->where('warehouse_transfers.transfer_date', '<', $dateFrom)
            ->sum('warehouse_transfer_lines.quantity');
            
        $openingBalance -= $transfersOut;
        
        return $openingBalance;
    }

    // Download PDF
    public function downloadPdf(Request $request)
    {
        $request->validate([
            'item_from' => 'required',
            'item_to' => 'required',
            'date_from' => 'required|date',
            'date_to' => 'required|date|after_or_equal:date_from',
        ]);

        $itemFrom = $request->item_from;
        $itemTo = $request->item_to;
        $dateFrom = $request->date_from;
        $dateTo = $request->date_to;
        $warehouseFilter = $request->warehouse_id;
        $company = CompanySettings::first();

        // Get all items in the selected range
        $selectedItems = PriceListingSetup::whereBetween('code', [$itemFrom, $itemTo])
            ->orderBy('code')
            ->get();

        $movementData = [];
        $totalReceived = 0;
        $totalIssued = 0;
        
        foreach ($selectedItems as $item) {
            // Get inventory journals for this item within the date range
            $journals = InventoryJournal::with(['warehouse'])
                ->where('item_id', $item->id)
                ->when($warehouseFilter, function($query) use ($warehouseFilter) {
                    return $query->where('warehouse_id', $warehouseFilter);
                })
                ->whereBetween('date', [$dateFrom, $dateTo])
                ->orderBy('date')
                ->get();

            // Get goods received data
            $goodsReceived = $this->getGoodsReceivedData($item, $dateFrom, $dateTo, $warehouseFilter);
            
            // Get invoice data
            $invoices = $this->getInvoiceData($item, $dateFrom, $dateTo, $warehouseFilter);
            
            // Get warehouse transfer data
            $transfers = $this->getTransferData($item, $dateFrom, $dateTo, $warehouseFilter);
            
            // Calculate opening balance (before the date range)
            $openingBalance = $this->calculateOpeningBalance($item, $dateFrom, $warehouseFilter);

            // Process each journal entry
            $runningBalance = $openingBalance;
            
            // Add opening balance row
            $category = InventoryCategory::find($item->category_id);
            $categoryName = $category ? $category->name : 'N/A';
            
            $movementData[] = [
                'type' => 'opening_balance',
                'date' => $dateFrom,
                'item_code' => $item->code,
                'item_description' => $item->description1,
                'warehouse' => $warehouseFilter ? Warehouse::find($warehouseFilter)->description : 'All Warehouses',
                'group' => $categoryName,
                'reference' => 'Opening Balance',
                'qty_in' => 0,
                'qty_out' => 0,
                'qty_available' => $openingBalance,
            ];

            // Combine all transactions
            $allTransactions = collect([])
                ->merge($journals->map(function($journal) use ($item) {
                    return [
                        'date' => $journal->date,
                        'type' => 'journal',
                        'reference' => $journal->reference,
                        'qty_in' => $journal->quantity_in,
                        'qty_out' => $journal->quantity_out,
                        'warehouse' => $journal->warehouse ? $journal->warehouse->description : 'N/A'
                    ];
                }))
                ->merge($goodsReceived->map(function($grn) {
                    return [
                        'date' => $grn->document_date,
                        'type' => 'goods_received',
                        'reference' => $grn->document_number,
                        'qty_in' => $grn->confirmed_quantity,
                        'qty_out' => 0,
                        'warehouse' => $grn->warehouse_name
                    ];
                }))
                ->merge($invoices->map(function($invoice) {
                    return [
                        'date' => $invoice->document_date,
                        'type' => 'invoice',
                        'reference' => $invoice->document_number,
                        'qty_in' => 0,
                        'qty_out' => $invoice->confirmed_quantity,
                        'warehouse' => $invoice->warehouse_name
                    ];
                }))
                ->merge($transfers->map(function($transfer) use ($item) {
                    $isIn = $transfer->to_warehouse_id == $transfer->warehouse_id;
                    return [
                        'date' => $transfer->transfer_date,
                        'type' => 'transfer',
                        'reference' => $transfer->document_number,
                        'qty_in' => $isIn ? $transfer->quantity : 0,
                        'qty_out' => !$isIn ? $transfer->quantity : 0,
                        'warehouse' => $transfer->warehouse_name
                    ];
                }))
                ->sortBy('date');

            // Process each transaction entry
            foreach ($allTransactions as $transaction) {
                // Update running balance
                $runningBalance += $transaction['qty_in'] - $transaction['qty_out'];

                // Add to movement data
                $movementData[] = [
                    'type' => 'transaction',
                    'date' => $transaction['date'],
                    'item_code' => $item->code,
                    'item_description' => $item->description1,
                    'warehouse' => $transaction['warehouse'],
                    'group' => $categoryName,
                    'reference' => $transaction['reference'],
                    'qty_in' => $transaction['qty_in'],
                    'qty_out' => $transaction['qty_out'],
                    'qty_available' => $runningBalance,
                ];

                // Add to totals
                $totalReceived += $transaction['qty_in'];
                $totalIssued += $transaction['qty_out'];
            }
            
            // Add closing balance row
            $movementData[] = [
                'type' => 'closing_balance',
                'date' => $dateTo,
                'item_code' => $item->code,
                'item_description' => $item->description1,
                'warehouse' => $warehouseFilter ? Warehouse::find($warehouseFilter)->description : 'All Warehouses',
                'group' => $categoryName,
                'reference' => 'Closing Balance',
                'qty_in' => 0,
                'qty_out' => 0,
                'qty_available' => $runningBalance,
            ];
        }

        $totalOnHand = $totalReceived - $totalIssued;

        $pdf = PDF::loadView('reports.inventory-movement-report-pdf', compact(
            'movementData',
            'itemFrom',
            'itemTo',
            'warehouseFilter',
            'dateFrom',
            'dateTo',
            'company',
            'totalReceived',
            'totalIssued',
            'totalOnHand'
        ));

        return $pdf->download('inventory-movement-report.pdf');
    }

    // Download Excel
    public function downloadExcel(Request $request)
    {
        $request->validate([
            'item_from' => 'required',
            'item_to' => 'required',
            'date_from' => 'required|date',
            'date_to' => 'required|date|after_or_equal:date_from',
        ]);

        $itemFrom = $request->item_from;
        $itemTo = $request->item_to;
        $dateFrom = $request->date_from;
        $dateTo = $request->date_to;
        $warehouseFilter = $request->warehouse_id;

        return Excel::download(new InventoryMovementReportExport($itemFrom, $itemTo, $dateFrom, $dateTo, $warehouseFilter), 'inventory-movement-report.xlsx');
    }
}