<?php

namespace App\Http\Controllers;

use App\Models\StockLedger;
use App\Models\WarehouseStock;
use App\Models\Product;
use App\Models\Warehouse;
use App\Models\Category;
use Illuminate\Http\Request;

class StockLedgerController extends Controller
{
    public function index(Request $request)
    {
        $query = StockLedger::with(['product', 'warehouse', 'unit', 'createdBy'])
            ->orderByDesc('transaction_date')
            ->orderByDesc('id');

        if ($request->product_id) {
            $query->where('product_id', $request->product_id);
        }

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

        if ($request->transaction_type) {
            $query->where('transaction_type', $request->transaction_type);
        }

        if ($request->date_from) {
            $query->whereDate('transaction_date', '>=', $request->date_from);
        }

        if ($request->date_to) {
            $query->whereDate('transaction_date', '<=', $request->date_to);
        }

        $ledgers     = $query->paginate(50)->withQueryString();
        $warehouses  = Warehouse::where('status', 'active')->get();
        $txTypes     = ['OPENING_STOCK', 'PURCHASE', 'PURCHASE_RETURN', 'SALE', 'SALES_RETURN',
                        'STOCK_TRANSFER_IN', 'STOCK_TRANSFER_OUT', 'STOCK_ADJUSTMENT_IN', 'STOCK_ADJUSTMENT_OUT', 'DAMAGE'];

        return view('inventory.stock_ledger.index', compact('ledgers', 'warehouses', 'txTypes'));
    }

    /**
     * Current Stock by Warehouse report.
     */
    public function currentStock(Request $request)
    {
        $query = WarehouseStock::with(['product.category', 'product.baseUnit', 'warehouse'])
            ->where('qty', '!=', 0);

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

        if ($request->category_id) {
            $query->whereHas('product', fn($q) => $q->where('category_id', $request->category_id));
        }

        if ($request->stock_status) {
            match ($request->stock_status) {
                'negative'     => $query->where('qty', '<', 0),
                'out_of_stock' => $query->where('qty', '=', 0),
                'low'          => $query->whereHas('product', function ($q) {
                    $q->whereColumn('warehouse_stocks.qty', '<=', 'products.reorder_level')
                      ->where('products.reorder_level', '>', 0);
                }),
                default => null,
            };
        }

        $stocks      = $query->orderBy('product_id')->paginate(50)->withQueryString();
        $warehouses  = Warehouse::where('status', 'active')->get();
        $categories  = Category::where('status', 'active')->orderBy('name_en')->get();

        // Total inventory value
        $totalValue  = WarehouseStock::selectRaw('SUM(qty * avg_cost) as total')->value('total') ?? 0;

        return view('inventory.current_stock.index', compact('stocks', 'warehouses', 'categories', 'totalValue'));
    }

    /**
     * Stock by warehouse for a specific product.
     */
    public function byWarehouse(Product $product)
    {
        $stocks = WarehouseStock::with('warehouse')
            ->where('product_id', $product->id)
            ->get();

        return view('inventory.stock_ledger.by_warehouse', compact('product', 'stocks'));
    }

    /**
     * Export stock ledger to CSV.
     */
    public function export(Request $request)
    {
        $ledgers = StockLedger::with(['product', 'warehouse', 'unit'])
            ->when($request->product_id, fn($q) => $q->where('product_id', $request->product_id))
            ->when($request->warehouse_id, fn($q) => $q->where('warehouse_id', $request->warehouse_id))
            ->orderByDesc('transaction_date')
            ->get();

        $filename = 'stock_ledger_' . now()->format('Ymd_His') . '.csv';
        $callback = function () use ($ledgers) {
            $h = fopen('php://output', 'w');
            fputcsv($h, ['Date', 'Type', 'Reference', 'Product', 'Warehouse', 'Unit', 'Qty In', 'Qty Out', 'Base Qty In', 'Base Qty Out', 'Unit Cost', 'Total Cost', 'Running Qty']);
            foreach ($ledgers as $l) {
                fputcsv($h, [
                    $l->transaction_date, $l->transaction_type, $l->reference_number,
                    $l->product?->name_en, $l->warehouse?->name_en, $l->unit?->code,
                    $l->qty_in, $l->qty_out, $l->base_qty_in, $l->base_qty_out,
                    $l->unit_cost, $l->total_cost, $l->running_quantity,
                ]);
            }
            fclose($h);
        };

        return response()->streamDownload($callback, $filename, ['Content-Type' => 'text/csv']);
    }
}
