strprfcrm/app/Http/Controllers/StockController.php

<?php

declare(strict_types=1);

namespace App\Http\Controllers;

use App\Http\Requests\ReserveStockRequest;
use App\Models\CommonCatalogItem;
use App\Models\StockOrder;
use App\Models\StockReservation;
use App\Models\User;
use App\Services\StockInventoryService;
use DomainException;
use Illuminate\Contracts\View\View;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Query\Builder as QueryBuilder;
use Illuminate\Http\RedirectResponse;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class StockController extends Controller
{
    private const SORT_COLUMNS = [
        'article',
        'calculator_name',
        'kind',
        'unit',
        'physical_quantity',
        'reserved_quantity',
        'available_stock_quantity',
        'ordered_stock_quantity',
        'latest_order_note',
    ];

    public function __construct(private readonly StockInventoryService $inventoryService) {}

    public function index(Request $request): View
    {
        $search = trim((string) $request->query('search', ''));
        $kind = trim((string) $request->query('kind', ''));
        $status = trim((string) $request->query('status', ''));
        $perPage = $this->perPage($request);
        $sortBy = in_array($request->string('sortBy')->toString(), self::SORT_COLUMNS, true)
            ? $request->string('sortBy')->toString()
            : 'article';
        $orderBy = $request->query('order') === 'desc' ? 'desc' : 'asc';
        session(['gp_stock_availability' => $request->query()]);

        $physicalQuantity = $this->stockOrderQuantitySubquery(
            StockOrder::STATUS_IN_STOCK,
            'available_quantity',
        );
        $reservedQuantity = $this->reservedQuantitySubquery();
        $orderedQuantity = $this->stockOrderQuantitySubquery(
            StockOrder::STATUS_ORDERED,
            'ordered_quantity',
        );
        $availableQuantity = $this->availableQuantitySubquery();

        $query = CommonCatalogItem::query()
            ->select('common_catalog_items.*')
            ->whereHas('stockOrders')
            ->with('imageFile')
            ->addSelect([
                'physical_quantity' => $physicalQuantity,
                'reserved_quantity' => $reservedQuantity,
                'available_stock_quantity' => $availableQuantity,
                'ordered_stock_quantity' => $orderedQuantity,
                'latest_order_note' => $this->latestOrderNoteSubquery(),
            ])
            ->when($search !== '', function (Builder $query) use ($search): void {
                $query->where(function (Builder $query) use ($search): void {
                    $query->where('article', 'like', "%{$search}%")
                        ->orWhere('calculator_name', 'like', "%{$search}%")
                        ->orWhere('kind', 'like', "%{$search}%");
                });
            })
            ->when($kind !== '', fn (Builder $query) => $query->where('kind', $kind))
            ->when(array_key_exists($status, StockOrder::STATUS_NAMES), fn (Builder $query) => $query
                ->whereHas('stockOrders', fn (Builder $query) => $query->where('status', $status)));

        $this->applyColumnFilters($query, $request);
        $query->orderBy($sortBy, $orderBy)->orderBy('id');

        $items = $query
            ->paginate($perPage)
            ->withQueryString();

        return view('stock.index', [
            'active' => 'stock',
            'items' => $items,
            'search' => $search,
            'kind' => $kind,
            'status' => $status,
            'kinds' => CommonCatalogItem::query()
                ->whereHas('stockOrders')
                ->whereNotNull('kind')
                ->where('kind', '<>', '')
                ->distinct()
                ->orderBy('kind')
                ->pluck('kind'),
            'statuses' => StockOrder::STATUS_NAMES,
            'per_page' => $perPage,
            'sortBy' => $sortBy,
            'orderBy' => $orderBy,
            'tableId' => 'stock_availability',
            'columnFilters' => [
                'article' => [],
                'calculator_name' => [],
                'kind' => [],
                'unit' => [],
                'physical_quantity' => ['type' => 'ranges'],
                'reserved_quantity' => ['type' => 'ranges'],
                'available_stock_quantity' => ['type' => 'ranges'],
                'ordered_stock_quantity' => ['type' => 'ranges'],
                'latest_order_note' => [],
            ],
        ]);
    }

    public function show(CommonCatalogItem $item): View
    {
        $item->load([
            'imageFile',
            'stockOrders' => fn ($query) => $query
                ->withSum(['reservations as active_reservations_sum_quantity' => fn ($query) => $query->active()], 'quantity')
                ->orderByDesc('created_at'),
            'stockReservations' => fn ($query) => $query
                ->with(['manager', 'order'])
                ->orderByDesc('created_at'),
            'stockMovements' => fn ($query) => $query
                ->with(['user', 'order'])
                ->orderByDesc('created_at'),
        ]);

        return view('stock.show', [
            'active' => 'stock',
            'item' => $item,
            'summary' => $this->inventoryService->summary($item),
            'managers' => User::query()->orderBy('name')->get(),
        ]);
    }

    public function reserve(ReserveStockRequest $request, CommonCatalogItem $item): RedirectResponse
    {
        /** @var User $actor */
        $actor = $request->user();
        $manager = User::query()->findOrFail((int) $request->validated('manager_id'));

        try {
            $this->inventoryService->reserve(
                $item,
                $manager,
                (int) $request->validated('quantity'),
                $request->validated('note'),
                $actor,
            );

            return back()->with('success', 'Позиция забронирована.');
        } catch (DomainException $exception) {
            return back()->withErrors(['quantity' => $exception->getMessage()])->withInput();
        }
    }

    public function issue(Request $request, StockReservation $reservation): RedirectResponse
    {
        /** @var User $actor */
        $actor = $request->user();

        try {
            $this->inventoryService->issue($reservation, $actor, $request->string('note')->toString());

            return back()->with('success', 'Бронь списана.');
        } catch (DomainException $exception) {
            return back()->with('error', $exception->getMessage());
        }
    }

    public function cancel(Request $request, StockReservation $reservation): RedirectResponse
    {
        /** @var User $actor */
        $actor = $request->user();

        try {
            $this->inventoryService->cancel($reservation, $actor, $request->string('note')->toString());

            return back()->with('success', 'Бронь отменена.');
        } catch (DomainException $exception) {
            return back()->with('error', $exception->getMessage());
        }
    }

    private function perPage(Request $request): int
    {
        $allowed = [50, 100, 200, 500, 2000];
        $perPage = (int) $request->query('per_page', session('per_page', 50));
        $perPage = in_array($perPage, $allowed, true) ? $perPage : 50;
        session(['per_page' => $perPage]);

        return $perPage;
    }

    private function applyColumnFilters(Builder $query, Request $request): void
    {
        foreach (['article', 'calculator_name', 'kind', 'unit'] as $column) {
            $values = $this->filterValues($request, $column);
            if ($values !== []) {
                $query->where(function (Builder $query) use ($column, $values): void {
                    $this->applyValuesCondition($query, $column, $values);
                });
            }
        }

        $latestNotes = $this->filterValues($request, 'latest_order_note');
        if ($latestNotes !== []) {
            $nonEmpty = array_values(array_diff($latestNotes, ['-пусто-']));
            $includeEmpty = in_array('-пусто-', $latestNotes, true);
            $query->where(function (Builder $query) use ($nonEmpty, $includeEmpty): void {
                if ($nonEmpty !== []) {
                    $query->whereHas('latestStockOrder', fn (Builder $query) => $query->whereIn('note', $nonEmpty));
                }
                if ($includeEmpty) {
                    $method = $nonEmpty === [] ? 'where' : 'orWhere';
                    $query->{$method}(function (Builder $query): void {
                        $query->whereDoesntHave('latestStockOrder')
                            ->orWhereHas('latestStockOrder', function (Builder $query): void {
                                $query->whereNull('note')->orWhereRaw("TRIM(note) = ''");
                            });
                    });
                }
            });
        }

        $this->applySubqueryRange($query, $request, 'physical_quantity', $this->stockOrderQuantitySubquery(
            StockOrder::STATUS_IN_STOCK,
            'available_quantity',
        ));
        $this->applySubqueryRange($query, $request, 'reserved_quantity', $this->reservedQuantitySubquery());
        $this->applySubqueryRange($query, $request, 'available_stock_quantity', $this->availableQuantitySubquery());
        $this->applySubqueryRange($query, $request, 'ordered_stock_quantity', $this->stockOrderQuantitySubquery(
            StockOrder::STATUS_ORDERED,
            'ordered_quantity',
        ));
    }

    /** @return array<int, string> */
    private function filterValues(Request $request, string $column): array
    {
        $value = $request->input("filters.{$column}");

        return is_string($value) && $value !== '' ? explode('||', $value) : [];
    }

    /** @param array<int, string> $values */
    private function applyValuesCondition(Builder $query, string $column, array $values): void
    {
        $nonEmpty = array_values(array_diff($values, ['-пусто-']));
        if ($nonEmpty !== []) {
            $query->orWhereIn($column, $nonEmpty);
        }
        if (in_array('-пусто-', $values, true)) {
            $this->applyEmptyFilterCondition($query, $column);
        }
    }

    private function applySubqueryRange(
        Builder $query,
        Request $request,
        string $column,
        QueryBuilder $subquery,
    ): void {
        $from = $request->input("filters.{$column}_from");
        $to = $request->input("filters.{$column}_to");

        if (is_numeric($from)) {
            $query->where(clone $subquery, '>=', (float) $from);
        }
        if (is_numeric($to)) {
            $query->where(clone $subquery, '<=', (float) $to);
        }
    }

    private function stockOrderQuantitySubquery(string $status, string $column): QueryBuilder
    {
        return DB::table('stock_orders')
            ->selectRaw("COALESCE(SUM({$column}), 0)")
            ->whereColumn('stock_orders.common_catalog_item_id', 'common_catalog_items.id')
            ->where('stock_orders.status', $status)
            ->whereNull('stock_orders.deleted_at');
    }

    private function reservedQuantitySubquery(): QueryBuilder
    {
        return DB::table('stock_reservations')
            ->selectRaw('COALESCE(SUM(quantity), 0)')
            ->whereColumn('stock_reservations.common_catalog_item_id', 'common_catalog_items.id')
            ->where('stock_reservations.status', StockReservation::STATUS_ACTIVE);
    }

    private function availableQuantitySubquery(): QueryBuilder
    {
        $physical = $this->stockOrderQuantitySubquery(StockOrder::STATUS_IN_STOCK, 'available_quantity');
        $reserved = $this->reservedQuantitySubquery();

        return DB::query()->selectRaw(
            'GREATEST(0, ('.$physical->toSql().') - ('.$reserved->toSql().'))',
            array_merge($physical->getBindings(), $reserved->getBindings()),
        );
    }

    private function latestOrderNoteSubquery(): QueryBuilder
    {
        return DB::table('stock_orders')
            ->select('note')
            ->whereColumn('stock_orders.common_catalog_item_id', 'common_catalog_items.id')
            ->whereNull('stock_orders.deleted_at')
            ->orderByDesc('stock_orders.id')
            ->limit(1);
    }
}