<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Models\PurchaseStock;
use Illuminate\Support\Facades\DB;


class InventoryController extends Controller
{
      public function index(Request $request)
    {
        $query = PurchaseStock::query()
            ->select(
                'purchase_stock.id',
                'purchase_stock.item_name',
                'purchase_stock.particular_name',
                'purchase_stock.specification',
                'purchase_stock.model_number',
                'purchase_stock.invoice_number',
                'purchase_stock.incoming_date',
                'purchase_stock.courier_name',
                DB::raw('SUM(purchase_stock.qty) as purchased_qty'),
                DB::raw('COALESCE(SUM(purchase_stock.fault_count),0) as fault_qty'),
                DB::raw('(SELECT COALESCE(SUM(outward_stock.qty),0)
                          FROM outward_stock 
                          WHERE outward_stock.item_name = purchase_stock.item_name
                          AND outward_stock.particular_name = purchase_stock.particular_name
                          AND outward_stock.specification = purchase_stock.specification
                          AND outward_stock.model_number = purchase_stock.model_number
                ) as outward_qty')
            )
            ->leftJoin('purchase_invoice', 'purchase_stock.invoice_number', '=', 'purchase_invoice.invoice_number')
            ->groupBy([
                'purchase_stock.id', 'purchase_stock.item_name',
                'purchase_stock.particular_name', 'purchase_stock.specification',
                'purchase_stock.model_number', 'purchase_stock.invoice_number',
                'purchase_stock.incoming_date', 'purchase_stock.courier_name'
            ]);

        // Filters
        if ($request->filled('item_name')) {
            $query->where('purchase_stock.item_name', 'like', '%' . $request->item_name . '%');
        }
        if ($request->filled('invoice_number')) {
            $query->where('purchase_stock.invoice_number', 'like', '%' . $request->invoice_number . '%');
        }
        if ($request->filled('incoming_date')) {
            $query->whereDate('purchase_stock.incoming_date', $request->incoming_date);
        }

        $stocks = $query->paginate(10);

        return view('inventory', compact('stocks'));
    }
}

