<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\WarehouseTransfer;
use App\Models\WarehouseTransferLine;
use App\Models\PriceListingSetup as Item;
use App\Models\InventoryCategory as Category;
use App\Models\SupplierDocumentHeader;
use App\Models\SupplierDocumentLine;
use App\Models\CompanySettings;
use App\Models\Warehouse;
use PDF;
use DB;

class WarehouseTransferReportController extends Controller
{
    public function index()
    {
        $items = Item::orderBy('code', 'asc')->get();
        $warehouses = Warehouse::orderBy('description', 'asc')->get();
        $currentDate = date('Y-m-d');
        
        return view('reports.warehouse-transfer-report', compact('items', 'warehouses', 'currentDate'));
    }

    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;
        $warehouseId = $request->warehouse_id;

        // Get items in the selected range
        $items = Item::whereBetween('code', [$itemFrom, $itemTo])
                    ->orderBy('code', 'asc')
                    ->get();
        
        $transferData = [];
        
        foreach ($items as $item) {
            // Get transfer lines for this item with date range
            $query = WarehouseTransferLine::with([
                    'transfer', 
                    'transfer.fromWarehouse', 
                    'transfer.toWarehouse',
                    'uom'
                ])
                ->where('item_id', $item->id)
                ->whereHas('transfer', function($query) use ($dateFrom, $dateTo, $warehouseId) {
                    $query->whereBetween('transfer_date', [$dateFrom, $dateTo])
                          ->where('status', 'completed');
                    
                    if ($warehouseId) {
                        $query->where(function($q) use ($warehouseId) {
                            $q->where('from_warehouse_id', $warehouseId)
                              ->orWhere('to_warehouse_id', $warehouseId);
                        });
                    }
                });

            $transferLines = $query->orderBy('created_at', 'asc')->get();

            if ($transferLines->count() > 0) {
                // Get category
                $category = Category::find($item->category_id);
                
                // Calculate stock quantities for each transfer line
                foreach ($transferLines as $line) {
                    // Calculate opening stock for the item at the from_warehouse before the transfer
                    $openingStock = $this->calculateOpeningStock(
                        $item->id, 
                        $line->transfer->from_warehouse_id, 
                        $line->transfer->transfer_date
                    );
                    
                    // Calculate available quantity after transfer
                    $availableQty = $openingStock - $line->quantity;
                    
                    // Add calculated values to the line
                    $line->opening_stock = $openingStock;
                    $line->issued_qty = $line->quantity;
                    $line->available_qty = $availableQty;
                }
                
                $transferData[] = [
                    'item' => $item,
                    'category' => $category,
                    'transferLines' => $transferLines
                ];
            }
        }

        $currentDate = date('Y-m-d');
        $warehouses = Warehouse::orderBy('description', 'asc')->get();
        
        return view('reports.warehouse-transfer-report', compact(
            'transferData', 
            'items', 
            'warehouses',
            'itemFrom', 
            'itemTo', 
            'dateFrom', 
            'dateTo',
            'warehouseId',
            'currentDate'
        ));
    }

    private function calculateOpeningStock($itemId, $warehouseId, $beforeDate)
    {
        // Calculate opening stock from goods received
        $goodsReceived = SupplierDocumentLine::where('item_id', $itemId)
            ->where('warehouse_id', $warehouseId)
            ->whereHas('header', function($query) use ($beforeDate) {
                $query->where('type', 'goods_received')
                      ->where('document_date', '<', $beforeDate);
            })
            ->sum('confirmed_quantity');

        // Calculate stock transferred out
        $transferredOut = WarehouseTransferLine::where('item_id', $itemId)
            ->whereHas('transfer', function($query) use ($warehouseId, $beforeDate) {
                $query->where('from_warehouse_id', $warehouseId)
                      ->where('transfer_date', '<', $beforeDate)
                      ->where('status', 'completed');
            })
            ->sum('quantity');

        // Calculate stock transferred in
        $transferredIn = WarehouseTransferLine::where('item_id', $itemId)
            ->whereHas('transfer', function($query) use ($warehouseId, $beforeDate) {
                $query->where('to_warehouse_id', $warehouseId)
                      ->where('transfer_date', '<', $beforeDate)
                      ->where('status', 'completed');
            })
            ->sum('quantity');

        return $goodsReceived + $transferredIn - $transferredOut;
    }

    public function downloadPdf(Request $request)
    {
        $itemFrom = $request->item_from;
        $itemTo = $request->item_to;
        $dateFrom = $request->date_from;
        $dateTo = $request->date_to;
        $warehouseId = $request->warehouse_id;

        // Get items in the selected range
        $items = Item::whereBetween('code', [$itemFrom, $itemTo])
                    ->orderBy('code', 'asc')
                    ->get();
        
        $transferData = [];
        $company = CompanySettings::first();
        
        foreach ($items as $item) {
            // Get transfer lines for this item
            $query = WarehouseTransferLine::with([
                    'transfer', 
                    'transfer.fromWarehouse', 
                    'transfer.toWarehouse',
                    'uom'
                ])
                ->where('item_id', $item->id)
                ->whereHas('transfer', function($query) use ($dateFrom, $dateTo, $warehouseId) {
                    $query->whereBetween('transfer_date', [$dateFrom, $dateTo])
                          ->where('status', 'completed');
                    
                    if ($warehouseId) {
                        $query->where(function($q) use ($warehouseId) {
                            $q->where('from_warehouse_id', $warehouseId)
                              ->orWhere('to_warehouse_id', $warehouseId);
                        });
                    }
                });

            $transferLines = $query->orderBy('created_at', 'asc')->get();

            if ($transferLines->count() > 0) {
                $category = Category::find($item->category_id);
                
                // Calculate quantities
                foreach ($transferLines as $line) {
                    $openingStock = $this->calculateOpeningStock(
                        $item->id, 
                        $line->transfer->from_warehouse_id, 
                        $line->transfer->transfer_date
                    );
                    
                    $line->opening_stock = $openingStock;
                    $line->issued_qty = $line->quantity;
                    $line->available_qty = $openingStock - $line->quantity;
                }
                
                $transferData[] = [
                    'item' => $item,
                    'category' => $category,
                    'transferLines' => $transferLines
                ];
            }
        }

        $pdf = PDF::loadView('reports.warehouse-transfer-report-pdf', compact(
            'transferData', 
            'company', 
            'dateFrom', 
            'dateTo'
        ));
        
        return $pdf->download('warehouse-transfer-report-'.date('Y-m-d').'.pdf');
    }
}