<?php

declare(strict_types=1);

/**
 * 本文件属于 DBErp 进销存系统。
 *
 * 版权所有 © 2026 北京珑大钜商科技有限公司。保留所有权利。
 *
 * 未经官方授权，不得删除、篡改或隐藏本文件中的版权声明、产品标识与来源信息。
 * 商业授权、官方更新与技术支持服务，请以珑大官方支持中心记录为准。
 *
 * @link https://support.loongdom.cn
 * @see  https://www.dberp.com.cn
 */

namespace App\Business\Report;

use App\Business\Inventory\Enums\InventoryChangeDirection;
use App\Business\Inventory\Enums\InventoryExceptionStatus;
use App\Business\Inventory\Enums\StocktakeDifferenceStatus;
use App\Models\DberpInventoryBalance;
use App\Models\DberpInventoryException;
use App\Models\DberpInventoryTransaction;
use App\Models\DberpProduct;
use App\Models\DberpStocktakeDifference;
use App\Models\DberpWarehouse;
use App\Support\DberpNumberFormatter;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;

/**
 * 库存分析页聚合：结余为时点快照；流水与趋势按库存流水业务时间；预警为规则命中快照；异常/盘点差异为区间内占位计数。
 */
final class InventoryAnalysisReportService
{
    public function __construct(
        private readonly InventoryReportService $inventoryReportService,
    ) {}

    /**
     * @param  array<string, mixed>  $formState
     * @return array<string, mixed>
     */
    public function normalizeFilters(array $formState): array
    {
        $filters = [
            'warehouse_id' => (int) ($formState['warehouse_id'] ?? 0),
            'category_id' => (int) ($formState['category_id'] ?? 0),
            'product_id' => (int) ($formState['product_id'] ?? 0),
            'business_time_from' => $formState['business_time_from'] ?? null,
            'business_time_until' => $formState['business_time_until'] ?? null,
        ];
        if ($filters['warehouse_id'] <= 0) {
            unset($filters['warehouse_id']);
        }
        if ($filters['category_id'] <= 0) {
            unset($filters['category_id']);
        }
        if ($filters['product_id'] <= 0) {
            unset($filters['product_id']);
        }
        if (empty($filters['business_time_from'])) {
            unset($filters['business_time_from']);
        }
        if (empty($filters['business_time_until'])) {
            unset($filters['business_time_until']);
        }

        return $filters;
    }

    /**
     * @param  array<string, mixed>  $filters
     * @return array<string, int|string|float>
     */
    public function summary(array $filters): array
    {
        $balanceBase = $this->balanceBaseQuery($filters);
        $txnBase = $this->transactionsInPeriodQuery($filters);

        $warningCount = (int) $this->inventoryReportService->warningQuery($filters)->count();

        $exceptionBase = DberpInventoryException::query()
            ->where('status', InventoryExceptionStatus::Open->value);
        $this->applyOccurredAtRange($exceptionBase, $filters);
        $this->applyExceptionProductWarehouse($exceptionBase, $filters);

        $diffBase = DberpStocktakeDifference::query()
            ->where('status', StocktakeDifferenceStatus::Pending->value);
        $this->applyCreatedAtRange($diffBase, $filters);
        $this->applyDifferenceProductWarehouse($diffBase, $filters);

        return [
            'sku_location_count' => (int) (clone $balanceBase)->count(),
            'realtime_quantity_total' => DberpNumberFormatter::quantity((clone $balanceBase)->sum('realtime_quantity')),
            'available_quantity_total' => DberpNumberFormatter::quantity((clone $balanceBase)->sum('available_quantity')),
            'warning_sku_count' => $warningCount,
            'period_inbound_quantity' => DberpNumberFormatter::quantity((clone $txnBase)->where('direction', InventoryChangeDirection::In->value)->sum('change_quantity')),
            'period_outbound_quantity' => DberpNumberFormatter::quantity((clone $txnBase)->where('direction', InventoryChangeDirection::Out->value)->sum('change_quantity')),
            'open_exception_count' => (int) $exceptionBase->count(),
            'pending_stocktake_difference_count' => (int) $diffBase->count(),
        ];
    }

    /**
     * @param  array<string, mixed>  $filters
     * @return list<array{date: string, inbound: string, outbound: string}>
     */
    public function dailyFlowTrend(array $filters): array
    {
        $base = $this->transactionsInPeriodQuery($filters);

        $rows = (clone $base)
            ->selectRaw('DATE(business_time) as stat_date')
            ->selectRaw('SUM(CASE WHEN direction = ? THEN change_quantity ELSE 0 END) as in_qty', [InventoryChangeDirection::In->value])
            ->selectRaw('SUM(CASE WHEN direction = ? THEN change_quantity ELSE 0 END) as out_qty', [InventoryChangeDirection::Out->value])
            ->groupBy(DB::raw('DATE(business_time)'))
            ->orderBy('stat_date')
            ->get();

        $out = [];
        foreach ($rows as $row) {
            $out[] = [
                'date' => (string) $row->stat_date,
                'inbound' => DberpNumberFormatter::quantity($row->in_qty ?? 0),
                'outbound' => DberpNumberFormatter::quantity($row->out_qty ?? 0),
            ];
        }

        return $out;
    }

    /**
     * @param  array<string, mixed>  $filters
     * @return list<array{id: int, name: string, inbound: string, outbound: string}>
     */
    public function topProductsByFlow(array $filters, int $limit = 8): array
    {
        $base = $this->transactionsInPeriodQuery($filters);

        $ids = (clone $base)
            ->select('product_id')
            ->selectRaw('SUM(CASE WHEN direction = ? THEN change_quantity ELSE 0 END) as in_qty', [InventoryChangeDirection::In->value])
            ->selectRaw('SUM(CASE WHEN direction = ? THEN change_quantity ELSE 0 END) as out_qty', [InventoryChangeDirection::Out->value])
            ->groupBy('product_id')
            // MariaDB 不允许 ORDER BY 使用聚合别名做运算（1247），须重复 SUM 表达式。
            ->orderByRaw(
                '(SUM(CASE WHEN direction = ? THEN change_quantity ELSE 0 END) + SUM(CASE WHEN direction = ? THEN change_quantity ELSE 0 END)) DESC',
                [InventoryChangeDirection::In->value, InventoryChangeDirection::Out->value]
            )
            ->limit($limit)
            ->get();

        $names = DberpProduct::query()->whereIn('id', $ids->pluck('product_id'))->pluck('name', 'id');

        $out = [];
        foreach ($ids as $row) {
            $pid = (int) $row->product_id;
            $out[] = [
                'id' => $pid,
                'name' => (string) ($names[$pid] ?? (string) $pid),
                'inbound' => DberpNumberFormatter::quantity($row->in_qty ?? 0),
                'outbound' => DberpNumberFormatter::quantity($row->out_qty ?? 0),
            ];
        }

        return $out;
    }

    /**
     * @param  array<string, mixed>  $filters
     * @return list<array{id: int, name: string, inbound: string, outbound: string}>
     */
    public function topWarehousesByFlow(array $filters, int $limit = 8): array
    {
        $base = $this->transactionsInPeriodQuery($filters);

        $ids = (clone $base)
            ->select('warehouse_id')
            ->selectRaw('SUM(CASE WHEN direction = ? THEN change_quantity ELSE 0 END) as in_qty', [InventoryChangeDirection::In->value])
            ->selectRaw('SUM(CASE WHEN direction = ? THEN change_quantity ELSE 0 END) as out_qty', [InventoryChangeDirection::Out->value])
            ->groupBy('warehouse_id')
            // MariaDB 不允许 ORDER BY 使用聚合别名做运算（1247），须重复 SUM 表达式。
            ->orderByRaw(
                '(SUM(CASE WHEN direction = ? THEN change_quantity ELSE 0 END) + SUM(CASE WHEN direction = ? THEN change_quantity ELSE 0 END)) DESC',
                [InventoryChangeDirection::In->value, InventoryChangeDirection::Out->value]
            )
            ->limit($limit)
            ->get();

        $names = DberpWarehouse::query()->whereIn('id', $ids->pluck('warehouse_id'))->pluck('name', 'id');

        $out = [];
        foreach ($ids as $row) {
            $wid = (int) $row->warehouse_id;
            $out[] = [
                'id' => $wid,
                'name' => (string) ($names[$wid] ?? (string) $wid),
                'inbound' => DberpNumberFormatter::quantity($row->in_qty ?? 0),
                'outbound' => DberpNumberFormatter::quantity($row->out_qty ?? 0),
            ];
        }

        return $out;
    }

    /**
     * @param  array<string, mixed>  $filters
     */
    private function balanceBaseQuery(array $filters): Builder
    {
        return DberpInventoryBalance::query()
            ->when($filters['warehouse_id'] ?? null, fn (Builder $q, int $id): Builder => $q->where('warehouse_id', $id))
            ->when($filters['product_id'] ?? null, fn (Builder $q, int $id): Builder => $q->where('product_id', $id))
            ->when($filters['category_id'] ?? null, fn (Builder $q, int $id): Builder => $q->whereHas('product', fn (Builder $p) => $p->where('category_id', $id)));
    }

    /**
     * @param  array<string, mixed>  $filters
     */
    private function transactionsInPeriodQuery(array $filters): Builder
    {
        return DberpInventoryTransaction::query()
            ->when($filters['business_time_from'] ?? null, fn (Builder $q, string $d): Builder => $q->whereDate('business_time', '>=', $d))
            ->when($filters['business_time_until'] ?? null, fn (Builder $q, string $d): Builder => $q->whereDate('business_time', '<=', $d))
            ->when($filters['warehouse_id'] ?? null, fn (Builder $q, int $id): Builder => $q->where('warehouse_id', $id))
            ->when($filters['product_id'] ?? null, fn (Builder $q, int $id): Builder => $q->where('product_id', $id))
            ->when($filters['category_id'] ?? null, fn (Builder $q, int $id): Builder => $q->whereHas('product', fn (Builder $p) => $p->where('category_id', $id)));
    }

    /**
     * @param  array<string, mixed>  $filters
     */
    private function applyOccurredAtRange(Builder $query, array $filters): void
    {
        if (! empty($filters['business_time_from'])) {
            $query->whereDate('occurred_at', '>=', $filters['business_time_from']);
        }
        if (! empty($filters['business_time_until'])) {
            $query->whereDate('occurred_at', '<=', $filters['business_time_until']);
        }
    }

    /**
     * @param  array<string, mixed>  $filters
     */
    private function applyCreatedAtRange(Builder $query, array $filters): void
    {
        if (! empty($filters['business_time_from'])) {
            $query->whereDate('created_at', '>=', $filters['business_time_from']);
        }
        if (! empty($filters['business_time_until'])) {
            $query->whereDate('created_at', '<=', $filters['business_time_until']);
        }
    }

    /**
     * @param  array<string, mixed>  $filters
     */
    private function applyExceptionProductWarehouse(Builder $query, array $filters): void
    {
        if (! empty($filters['warehouse_id'])) {
            $query->where('warehouse_id', (int) $filters['warehouse_id']);
        }
        if (! empty($filters['product_id'])) {
            $query->where('product_id', (int) $filters['product_id']);
        }
        if (! empty($filters['category_id'])) {
            $query->whereHas('product', fn (Builder $p) => $p->where('category_id', (int) $filters['category_id']));
        }
    }

    /**
     * @param  array<string, mixed>  $filters
     */
    private function applyDifferenceProductWarehouse(Builder $query, array $filters): void
    {
        if (! empty($filters['warehouse_id'])) {
            $query->where('warehouse_id', (int) $filters['warehouse_id']);
        }
        if (! empty($filters['product_id'])) {
            $query->where('product_id', (int) $filters['product_id']);
        }
        if (! empty($filters['category_id'])) {
            $query->whereHas('product', fn (Builder $p) => $p->where('category_id', (int) $filters['category_id']));
        }
    }
}
