<?php

namespace App\Services;

use App\Models\Yarn;
use App\Models\StockLedger;
use Illuminate\Support\Facades\DB;

class StockService
{
    public function getStockList($request = null)
    {
        $query = Yarn::active()->with('stockLedgers');

        if ($request?->yarn_id) {
            $query->where('id', $request->yarn_id);
        }
        if ($request?->category) {
            $query->where('category', $request->category);
        }

        return $query->get()->map(function ($yarn) {
            $in  = (float) $yarn->stockLedgers()->sum('in_qty');
            $out = (float) $yarn->stockLedgers()->sum('out_qty');
            $net = $in - $out;

            $avgCost = DB::table('purchase_items')
                ->join('purchases', 'purchases.id', '=', 'purchase_items.purchase_id')
                ->where('purchase_items.yarn_id', $yarn->id)
                ->selectRaw('SUM(purchase_items.quantity * purchase_items.rate) / NULLIF(SUM(purchase_items.quantity), 0) as avg_cost')
                ->value('avg_cost') ?? 0;

            return (object) [
                'id'           => $yarn->id,
                'name'         => $yarn->name,
                'code'         => $yarn->code,
                'category'     => $yarn->category,
                'unit'         => $yarn->unit,
                'bags'         => (int) round($net / 30),
                'quantity'     => round($net, 2),
                'avg_cost'     => round($avgCost, 2),
                'stock_value'  => round($net * $avgCost, 2),
                'reorder_level'=> $yarn->reorder_level,
                'status'       => $this->getStockStatus($net, $yarn->reorder_level),
            ];
        });
    }

    public function getStockLedger($request = null)
    {
        $query = StockLedger::with('yarn');

        if ($request?->yarn_id) {
            $query->where('yarn_id', $request->yarn_id);
        }
        if ($request?->from_date) {
            $query->whereDate('transaction_date', '>=', $request->from_date);
        }
        if ($request?->to_date) {
            $query->whereDate('transaction_date', '<=', $request->to_date);
        }
        if ($request?->type) {
            $query->where('type', $request->type);
        }

        return $query->orderBy('transaction_date')->orderBy('id')->paginate(30)->withQueryString();
    }

    public function getStockSummary(): array
    {
        $yarns = $this->getStockList();

        return [
            'total_types'  => $yarns->count(),
            'total_bags'   => $yarns->sum('bags'),
            'total_qty'    => round($yarns->sum('quantity'), 2),
            'total_value'  => round($yarns->sum('stock_value'), 2),
            'low_stock'    => $yarns->filter(fn ($y) => $y->status === 'low_stock')->count(),
            'out_of_stock' => $yarns->filter(fn ($y) => $y->status === 'out_of_stock')->count(),
        ];
    }

    public function getLedgerSummary($yarnId, $fromDate, $toDate): array
    {
        $yarn = Yarn::findOrFail($yarnId);

        $opening = StockLedger::where('yarn_id', $yarnId)
            ->whereDate('transaction_date', '<', $fromDate)
            ->selectRaw('COALESCE(SUM(in_qty), 0) as total_in, COALESCE(SUM(out_qty), 0) as total_out')
            ->first();

        $movements = StockLedger::where('yarn_id', $yarnId)
            ->whereBetween('transaction_date', [$fromDate, $toDate])
            ->selectRaw('
                SUM(in_qty) as total_in,
                SUM(out_qty) as total_out,
                SUM(value) as total_value
            ')->first();

        return [
            'yarn'    => $yarn,
            'opening' => [
                'qty'   => round(($opening->total_in ?? 0) - ($opening->total_out ?? 0), 2),
                'value' => round(($opening->total_in ?? 0) * $this->getAverageCost($yarnId), 2),
            ],
            'inflow'  => round($movements->total_in ?? 0, 2),
            'outflow' => round($movements->total_out ?? 0, 2),
            'closing' => round(
                (($opening->total_in ?? 0) - ($opening->total_out ?? 0))
                + ($movements->total_in ?? 0) - ($movements->total_out ?? 0), 2),
        ];
    }

    private function getStockStatus(float $qty, float $reorderLevel): string
    {
        if ($qty <= 0) return 'out_of_stock';
        if ($qty <= $reorderLevel) return 'low_stock';
        return 'in_stock';
    }

    private function getAverageCost(int $yarnId): float
    {
        return DB::table('purchase_items')
            ->join('purchases', 'purchases.id', '=', 'purchase_items.purchase_id')
            ->where('purchase_items.yarn_id', $yarnId)
            ->selectRaw('SUM(purchase_items.quantity * purchase_items.rate) / NULLIF(SUM(purchase_items.quantity), 0)')
            ->value('avg_cost') ?? 0;
    }
}
