<?php

declare(strict_types=1);

namespace App\Business\Report;

use App\Business\Inventory\Enums\InventoryBusinessType;
use App\Business\Inventory\InventoryWarningService;
use App\Models\DberpInventoryBalance;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;

final class InventoryReportService
{
    public function __construct(
        private readonly InventoryWarningService $warningService,
    ) {}

    /**
     * 将 Filament 表格筛选状态转为进销存汇总服务入参（与导出 normalize 口径对齐）。
     *
     * @param  array<string, mixed>|null  $tableFilters
     * @return array<string, mixed>
     */
    public static function filtersFromTableState(?array $tableFilters): array
    {
        if ($tableFilters === null || $tableFilters === []) {
            return [];
        }

        $pick = static function (string $key) use ($tableFilters): mixed {
            $nested = $tableFilters[$key] ?? null;

            return is_array($nested) ? ($nested['value'] ?? null) : ($tableFilters[$key] ?? null);
        };

        $warehouseId = (int) ($pick('warehouse_id') ?? 0);
        $productId = (int) ($pick('product_id') ?? 0);
        $categoryId = (int) ($pick('category_id') ?? 0);
        $productSkuId = (int) ($pick('product_sku_id') ?? 0);

        $businessTime = $tableFilters['business_time'] ?? [];
        $businessTimeFrom = is_array($businessTime) ? ($businessTime['from'] ?? null) : null;
        $businessTimeUntil = is_array($businessTime) ? ($businessTime['until'] ?? null) : null;

        return array_filter([
            'warehouse_id' => $warehouseId > 0 ? $warehouseId : null,
            'product_id' => $productId > 0 ? $productId : null,
            'category_id' => $categoryId > 0 ? $categoryId : null,
            'product_sku_id' => $productSkuId > 0 ? $productSkuId : null,
            'business_time_from' => $businessTimeFrom,
            'business_time_until' => $businessTimeUntil,
        ], static fn ($v): bool => $v !== null && $v !== '' && $v !== 0);
    }

    /**
     * 按「物料 + 仓库」汇总结余与区间流水（跨规格组合聚合）。
     *
     * @param  array<string, mixed>  $filters
     */
    public function productSummaryQuery(array $filters = []): Builder
    {
        $skuFilterId = (int) ($filters['product_sku_id'] ?? 0);

        $aggregatedSub = DB::table('dberp_inventory_balances as b')
            ->select([
                DB::raw('MIN(b.id) as id'),
                'b.product_id',
                'b.warehouse_id',
                $skuFilterId > 0 ? DB::raw('MIN(b.product_sku_id) as product_sku_id') : DB::raw('null as product_sku_id'),
                DB::raw('SUM(b.realtime_quantity) as realtime_quantity'),
                DB::raw('SUM(b.available_quantity) as available_quantity'),
                DB::raw('SUM(b.occupied_quantity) as occupied_quantity'),
                DB::raw('SUM(b.safety_stock) as safety_stock'),
                DB::raw('MAX(b.last_moved_at) as last_moved_at'),
                DB::raw('MAX(b.created_at) as created_at'),
                DB::raw('MAX(b.updated_at) as updated_at'),
            ])
            ->when($skuFilterId > 0, fn (\Illuminate\Database\Query\Builder $builder): \Illuminate\Database\Query\Builder => $builder->where('b.product_sku_id', $skuFilterId))
            ->groupBy('b.product_id', 'b.warehouse_id');

        $inboundQuery = DB::table('dberp_inventory_transactions')
            ->selectRaw('COALESCE(SUM(change_quantity), 0)')
            ->whereColumn('product_id', 'dberp_inventory_balances.product_id')
            ->whereColumn('warehouse_id', 'dberp_inventory_balances.warehouse_id')
            ->where('direction', 'in')
            ->when($skuFilterId > 0, fn (\Illuminate\Database\Query\Builder $builder): \Illuminate\Database\Query\Builder => $builder->where('dberp_inventory_transactions.product_sku_id', $skuFilterId));

        $outboundQuery = DB::table('dberp_inventory_transactions')
            ->selectRaw('COALESCE(SUM(change_quantity), 0)')
            ->whereColumn('product_id', 'dberp_inventory_balances.product_id')
            ->whereColumn('warehouse_id', 'dberp_inventory_balances.warehouse_id')
            ->where('direction', 'out')
            ->when($skuFilterId > 0, fn (\Illuminate\Database\Query\Builder $builder): \Illuminate\Database\Query\Builder => $builder->where('dberp_inventory_transactions.product_sku_id', $skuFilterId));

        $purchaseInQuery = DB::table('dberp_inventory_transactions')
            ->selectRaw('COALESCE(SUM(change_quantity), 0)')
            ->whereColumn('product_id', 'dberp_inventory_balances.product_id')
            ->whereColumn('warehouse_id', 'dberp_inventory_balances.warehouse_id')
            ->where('business_type', InventoryBusinessType::PurchaseReceipt->value)
            ->when($skuFilterId > 0, fn (\Illuminate\Database\Query\Builder $builder): \Illuminate\Database\Query\Builder => $builder->where('dberp_inventory_transactions.product_sku_id', $skuFilterId));

        $salesOutQuery = DB::table('dberp_inventory_transactions')
            ->selectRaw('COALESCE(SUM(change_quantity), 0)')
            ->whereColumn('product_id', 'dberp_inventory_balances.product_id')
            ->whereColumn('warehouse_id', 'dberp_inventory_balances.warehouse_id')
            ->where('business_type', InventoryBusinessType::SalesOutbound->value)
            ->when($skuFilterId > 0, fn (\Illuminate\Database\Query\Builder $builder): \Illuminate\Database\Query\Builder => $builder->where('dberp_inventory_transactions.product_sku_id', $skuFilterId));

        $transferInQuery = DB::table('dberp_inventory_transactions')
            ->selectRaw('COALESCE(SUM(change_quantity), 0)')
            ->whereColumn('product_id', 'dberp_inventory_balances.product_id')
            ->whereColumn('warehouse_id', 'dberp_inventory_balances.warehouse_id')
            ->where('business_type', InventoryBusinessType::TransferIn->value)
            ->when($skuFilterId > 0, fn (\Illuminate\Database\Query\Builder $builder): \Illuminate\Database\Query\Builder => $builder->where('dberp_inventory_transactions.product_sku_id', $skuFilterId));

        $transferOutQuery = DB::table('dberp_inventory_transactions')
            ->selectRaw('COALESCE(SUM(change_quantity), 0)')
            ->whereColumn('product_id', 'dberp_inventory_balances.product_id')
            ->whereColumn('warehouse_id', 'dberp_inventory_balances.warehouse_id')
            ->where('business_type', InventoryBusinessType::TransferOut->value)
            ->when($skuFilterId > 0, fn (\Illuminate\Database\Query\Builder $builder): \Illuminate\Database\Query\Builder => $builder->where('dberp_inventory_transactions.product_sku_id', $skuFilterId));

        foreach ([$inboundQuery, $outboundQuery, $purchaseInQuery, $salesOutQuery, $transferInQuery, $transferOutQuery] as $query) {
            $query
                ->when($filters['business_time_from'] ?? null, fn ($builder, $date) => $builder->whereDate('business_time', '>=', $date))
                ->when($filters['business_time_until'] ?? null, fn ($builder, $date) => $builder->whereDate('business_time', '<=', $date));
        }

        return DberpInventoryBalance::query()
            ->fromSub($aggregatedSub, 'dberp_inventory_balances')
            ->select('dberp_inventory_balances.*')
            ->with(['product.category', 'warehouse', 'productSku'])
            ->addSelect([
                'total_in_quantity' => $inboundQuery,
                'total_out_quantity' => $outboundQuery,
                'purchase_in_quantity' => $purchaseInQuery,
                'sales_out_quantity' => $salesOutQuery,
                'transfer_in_quantity' => $transferInQuery,
                'transfer_out_quantity' => $transferOutQuery,
            ])
            ->when(($filters['warehouse_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('warehouse_id', (int) $filters['warehouse_id']))
            ->when(($filters['product_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('product_id', (int) $filters['product_id']))
            ->when(($filters['category_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->whereHas('product', fn (Builder $productQuery) => $productQuery->where('category_id', (int) $filters['category_id'])));
    }

    public function summaryQuery(array $filters = []): Builder
    {
        $inboundQuery = DB::table('dberp_inventory_transactions')
            ->selectRaw('COALESCE(SUM(change_quantity), 0)')
            ->whereColumn('product_id', 'dberp_inventory_balances.product_id')
            ->whereColumn('product_sku_id', 'dberp_inventory_balances.product_sku_id')
            ->whereColumn('warehouse_id', 'dberp_inventory_balances.warehouse_id')
            ->where('direction', 'in');

        $outboundQuery = DB::table('dberp_inventory_transactions')
            ->selectRaw('COALESCE(SUM(change_quantity), 0)')
            ->whereColumn('product_id', 'dberp_inventory_balances.product_id')
            ->whereColumn('product_sku_id', 'dberp_inventory_balances.product_sku_id')
            ->whereColumn('warehouse_id', 'dberp_inventory_balances.warehouse_id')
            ->where('direction', 'out');

        $purchaseInQuery = DB::table('dberp_inventory_transactions')
            ->selectRaw('COALESCE(SUM(change_quantity), 0)')
            ->whereColumn('product_id', 'dberp_inventory_balances.product_id')
            ->whereColumn('product_sku_id', 'dberp_inventory_balances.product_sku_id')
            ->whereColumn('warehouse_id', 'dberp_inventory_balances.warehouse_id')
            ->where('business_type', InventoryBusinessType::PurchaseReceipt->value);

        $salesOutQuery = DB::table('dberp_inventory_transactions')
            ->selectRaw('COALESCE(SUM(change_quantity), 0)')
            ->whereColumn('product_id', 'dberp_inventory_balances.product_id')
            ->whereColumn('product_sku_id', 'dberp_inventory_balances.product_sku_id')
            ->whereColumn('warehouse_id', 'dberp_inventory_balances.warehouse_id')
            ->where('business_type', InventoryBusinessType::SalesOutbound->value);

        $transferInQuery = DB::table('dberp_inventory_transactions')
            ->selectRaw('COALESCE(SUM(change_quantity), 0)')
            ->whereColumn('product_id', 'dberp_inventory_balances.product_id')
            ->whereColumn('product_sku_id', 'dberp_inventory_balances.product_sku_id')
            ->whereColumn('warehouse_id', 'dberp_inventory_balances.warehouse_id')
            ->where('business_type', InventoryBusinessType::TransferIn->value);

        $transferOutQuery = DB::table('dberp_inventory_transactions')
            ->selectRaw('COALESCE(SUM(change_quantity), 0)')
            ->whereColumn('product_id', 'dberp_inventory_balances.product_id')
            ->whereColumn('product_sku_id', 'dberp_inventory_balances.product_sku_id')
            ->whereColumn('warehouse_id', 'dberp_inventory_balances.warehouse_id')
            ->where('business_type', InventoryBusinessType::TransferOut->value);

        foreach ([$inboundQuery, $outboundQuery, $purchaseInQuery, $salesOutQuery, $transferInQuery, $transferOutQuery] as $query) {
            $query
                ->when($filters['business_time_from'] ?? null, fn ($builder, $date) => $builder->whereDate('business_time', '>=', $date))
                ->when($filters['business_time_until'] ?? null, fn ($builder, $date) => $builder->whereDate('business_time', '<=', $date));
        }

        return DberpInventoryBalance::query()
            ->with(['product.category', 'warehouse', 'productSku'])
            ->addSelect([
                'total_in_quantity' => $inboundQuery,
                'total_out_quantity' => $outboundQuery,
                'purchase_in_quantity' => $purchaseInQuery,
                'sales_out_quantity' => $salesOutQuery,
                'transfer_in_quantity' => $transferInQuery,
                'transfer_out_quantity' => $transferOutQuery,
            ])
            ->when(($filters['warehouse_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('warehouse_id', (int) $filters['warehouse_id']))
            ->when(($filters['product_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('product_id', (int) $filters['product_id']))
            ->when(($filters['product_sku_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('dberp_inventory_balances.product_sku_id', (int) $filters['product_sku_id']))
            ->when(($filters['category_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->whereHas('product', fn (Builder $productQuery) => $productQuery->where('category_id', (int) $filters['category_id'])));
    }

    public function warningQuery(array $filters = []): Builder
    {
        return $this->warningService->query()
            ->when(($filters['warehouse_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('warehouse_id', (int) $filters['warehouse_id']))
            ->when(($filters['product_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('product_id', (int) $filters['product_id']))
            ->when(($filters['category_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->whereHas('product', fn (Builder $productQuery) => $productQuery->where('category_id', (int) $filters['category_id'])));
    }
}
