<?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\ItemWarehouse;
use App\Models\SupplierDocumentLine;
use App\Models\SupplierDocumentHeader;
use App\Models\DocumentLine;
use App\Models\DocumentHeader;
use App\Models\WarehouseTransfer;
use App\Models\WarehouseTransferLine;
use PDF;
use Carbon\Carbon;
use DB;

class StockManagementReportController extends Controller
{
    public function index()
    {
        $items = PriceListingSetup::orderBy('code')->get();
        $warehouses = Warehouse::orderBy('description')->get();
        $currentDate = Carbon::now()->format('Y-m-d');

        return view('reports.stock-management-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;
        $warehouseFilter = $request->warehouse_id;
        $company = CompanySettings::first();

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

        $selectedItems = PriceListingSetup::whereBetween('code', [$itemFrom, $itemTo])
            ->orderBy('code')
            ->get();

        $reportData = [];
        $grandTotals = [
            'qty_sold' => 0,
            'qty_on_hand' => 0,
            'total_cost' => 0,
            'total_selling' => 0,
            'actual_profit_loss' => 0,
            'ex_total_cost' => 0,
            'ex_total_selling' => 0,
            'ex_profit_loss' => 0
        ];

        foreach ($selectedItems as $item) {
            // Calculate quantities
            $qtySold = $this->getQuantitySold($item, $dateFrom, $dateTo, $warehouseFilter);
            $qtyOnHand = $this->getQuantityOnHand($item, $dateFrom, $dateTo, $warehouseFilter);

            // Get prices
            $costPrice = $this->getCostPrice($item, $dateFrom, $dateTo, $warehouseFilter);
            $sellingPrice = $this->getSellingPrice($item, $dateFrom, $dateTo, $warehouseFilter);

            // Calculate derived values
            $totalCost = $qtySold * $costPrice;
            $totalSelling = $qtySold * $sellingPrice;
            $actualProfitLoss = $totalSelling - $totalCost;
            $exTotalCost = $costPrice * $qtyOnHand;
            $exTotalSelling = $sellingPrice * $qtyOnHand;
            $exProfitLoss = $exTotalSelling - $exTotalCost;

            // Only include items with activity
            if ($qtySold != 0 || $qtyOnHand != 0) {
                $reportData[] = [
                    'item' => $item,
                    'qty_sold' => $qtySold,
                    'qty_on_hand' => $qtyOnHand,
                    'cost_price' => $costPrice,
                    'selling_price' => $sellingPrice,
                    'total_cost' => $totalCost,
                    'total_selling' => $totalSelling,
                    'actual_profit_loss' => $actualProfitLoss,
                    'ex_total_cost' => $exTotalCost,
                    'ex_total_selling' => $exTotalSelling,
                    'ex_profit_loss' => $exProfitLoss,
                ];

                // Update grand totals
                $grandTotals['qty_sold'] += $qtySold;
                $grandTotals['qty_on_hand'] += $qtyOnHand;
                $grandTotals['total_cost'] += $totalCost;
                $grandTotals['total_selling'] += $totalSelling;
                $grandTotals['actual_profit_loss'] += $actualProfitLoss;
                $grandTotals['ex_total_cost'] += $exTotalCost;
                $grandTotals['ex_total_selling'] += $exTotalSelling;
                $grandTotals['ex_profit_loss'] += $exProfitLoss;
            }
        }

        return view('reports.stock-management-report', compact(
            'reportData',
            'grandTotals',
            'itemFrom',
            'itemTo',
            'dateFrom',
            'dateTo',
            'warehouseFilter',
            'items',
            'warehouses',
            'company'
        ));
    }

    private function getQuantitySold($item, $dateFrom, $dateTo, $warehouseFilter)
    {
        $query = DocumentLine::where('item_id', $item->id)
            ->whereHas('header', function($query) use ($dateFrom, $dateTo) {
                $query->where('type', 'invoice')
                      ->whereBetween('document_date', [$dateFrom, $dateTo]);
            });

        if ($warehouseFilter) {
            $query->where('warehouse_id', $warehouseFilter);
        }

        return $query->sum('confirmed_quantity');
    }

    private function getQuantityOnHand($item, $dateFrom, $dateTo, $warehouseFilter)
{
    // If a specific warehouse is selected
    if ($warehouseFilter) {
        return \App\Models\ItemWarehouse::where('item_id', $item->id)
            ->where('warehouse_id', $warehouseFilter)
            ->sum('quantity');
    }

    // If no specific warehouse, sum across all warehouses
    return \App\Models\ItemWarehouse::where('item_id', $item->id)
        ->sum('quantity');
}

    private function getCostPrice($item, $dateFrom, $dateTo, $warehouseFilter)
    {
        // Try to get cost from latest GRV within date range
        $query = SupplierDocumentLine::where('item_id', $item->id)
            ->whereHas('header', function($query) use ($dateFrom, $dateTo) {
                $query->where('type', 'goods_received')
                      ->whereBetween('document_date', [$dateFrom, $dateTo]);
            });

        if ($warehouseFilter) {
            $query->where('warehouse_id', $warehouseFilter);
        }

        $latestGRV = $query->orderBy('supplier_document_headers.document_date', 'desc')
            ->join('supplier_document_headers', 'supplier_document_lines.supplier_document_header_id', '=', 'supplier_document_headers.id')
            ->select('supplier_document_lines.price')
            ->first();

        if ($latestGRV && $latestGRV->price > 0) {
            return $latestGRV->price;
        }

        // Fallback to item's cost price
        return $item->cost ?? 0;
    }

    private function getSellingPrice($item, $dateFrom, $dateTo, $warehouseFilter)
    {
        // Get selling price from latest invoice within date range
        $query = DocumentLine::where('item_id', $item->id)
            ->whereHas('header', function($query) use ($dateFrom, $dateTo) {
                $query->where('type', 'invoice')
                      ->whereBetween('document_date', [$dateFrom, $dateTo]);
            });

        if ($warehouseFilter) {
            $query->where('warehouse_id', $warehouseFilter);
        }

        $latestInvoice = $query->orderBy('document_headers.document_date', 'desc')
            ->join('document_headers', 'document_lines.document_header_id', '=', 'document_headers.id')
            ->select('document_lines.price')
            ->first();

        if ($latestInvoice && $latestInvoice->price > 0) {
            return $latestInvoice->price;
        }

        return 0;
    }

    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();

        $selectedItems = PriceListingSetup::whereBetween('code', [$itemFrom, $itemTo])
            ->orderBy('code')
            ->get();

        $reportData = [];
        $grandTotals = [
            'qty_sold' => 0,
            'qty_on_hand' => 0,
            'total_cost' => 0,
            'total_selling' => 0,
            'actual_profit_loss' => 0,
            'ex_total_cost' => 0,
            'ex_total_selling' => 0,
            'ex_profit_loss' => 0
        ];

        foreach ($selectedItems as $item) {
            $qtySold = $this->getQuantitySold($item, $dateFrom, $dateTo, $warehouseFilter);
            $qtyOnHand = $this->getQuantityOnHand($item, $dateFrom, $dateTo, $warehouseFilter);
            $costPrice = $this->getCostPrice($item, $dateFrom, $dateTo, $warehouseFilter);
            $sellingPrice = $this->getSellingPrice($item, $dateFrom, $dateTo, $warehouseFilter);

            $totalCost = $qtySold * $costPrice;
            $totalSelling = $qtySold * $sellingPrice;
            $actualProfitLoss = $totalSelling - $totalCost;
            $exTotalCost = $costPrice * $qtyOnHand;
            $exTotalSelling = $sellingPrice * $qtyOnHand;
            $exProfitLoss = $exTotalSelling - $exTotalCost;

            if ($qtySold != 0 || $qtyOnHand != 0) {
                $reportData[] = [
                    'item' => $item,
                    'qty_sold' => $qtySold,
                    'qty_on_hand' => $qtyOnHand,
                    'cost_price' => $costPrice,
                    'selling_price' => $sellingPrice,
                    'total_cost' => $totalCost,
                    'total_selling' => $totalSelling,
                    'actual_profit_loss' => $actualProfitLoss,
                    'ex_total_cost' => $exTotalCost,
                    'ex_total_selling' => $exTotalSelling,
                    'ex_profit_loss' => $exProfitLoss,
                ];

                $grandTotals['qty_sold'] += $qtySold;
                $grandTotals['qty_on_hand'] += $qtyOnHand;
                $grandTotals['total_cost'] += $totalCost;
                $grandTotals['total_selling'] += $totalSelling;
                $grandTotals['actual_profit_loss'] += $actualProfitLoss;
                $grandTotals['ex_total_cost'] += $exTotalCost;
                $grandTotals['ex_total_selling'] += $exTotalSelling;
                $grandTotals['ex_profit_loss'] += $exProfitLoss;
            }
        }

        $pdf = PDF::loadView('reports.stock-management-report-pdf', compact(
            'reportData',
            'grandTotals',
            'dateFrom',
            'dateTo',
            'company'
        ));

        return $pdf->download('stock-management-report.pdf');
    }
}
