<?php

namespace App\Http\Controllers;

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


class FaultStockController extends Controller
{
    public function index(Request $request)
    {
        $query = FaultStock::with('purchaseStock');

        // filters
        if ($request->filled('item_name')) {
            $query->where(function ($q) use ($request) {
                $q->where('item_name', 'like', '%'.$request->item_name.'%')
                  ->orWhereHas('purchaseStock', function($sub) use ($request) {
                      $sub->where('item_name', 'like', '%'.$request->item_name.'%');
                  });
            });
        }
        if ($request->filled('given_to')) {
            $query->where('given_to', 'like', '%'.$request->given_to.'%');
        }
        if ($request->filled('fault_date')) {
            $query->whereDate('fault_date', $request->fault_date);
        }

        $faults = $query->orderByDesc('id')->paginate(10);

        return view('fault_view', compact('faults'));
    }


       public function categories()
    {
        // SQL equivalent: qty - (fault + outward)
        $rows = PurchaseStock::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',
                'purchase_stock.qty as purchased_qty',
                DB::raw('COALESCE(fault_sum.fault_qty,0) as fault_qty'),
                DB::raw('COALESCE(outward_sum.outward_qty,0) as outward_qty'),
                DB::raw('(purchase_stock.qty - COALESCE(fault_sum.fault_qty,0) - COALESCE(outward_sum.outward_qty,0)) as available_qty')
            )
            ->leftJoin(DB::raw('(SELECT purchase_stock_id, SUM(qty) as fault_qty 
                                 FROM fault_stock GROUP BY purchase_stock_id) as fault_sum'),
                        'purchase_stock.id', '=', 'fault_sum.purchase_stock_id')
            ->leftJoin(DB::raw('(SELECT purchase_stock_id, SUM(qty) as outward_qty 
                                 FROM outward_stock GROUP BY purchase_stock_id) as outward_sum'),
                        'purchase_stock.id', '=', 'outward_sum.purchase_stock_id')
            ->get();

        // Build tree (item -> particular -> spec -> model)
        $tree = [];
        foreach ($rows as $row) {
            $tree[$row->item_name][$row->particular_name][$row->specification][] = [
                'model' => $row->model_number,
                'available' => $row->available_qty,
            ];
        }

        return view('categories', compact('tree'));
    }
}

