<?php

declare(strict_types=1);

namespace App\Business\Report;

use App\Business\Audit\Enums\DberpAuditLogCategory;
use App\Models\DberpAuditLog;
use App\Models\DberpCustomerPriceRule;
use App\Models\DberpDocumentQuery;
use App\Models\DberpIntegrationSyncLog;
use App\Models\DberpInventoryTransaction;
use App\Models\DberpProduct;
use App\Models\DberpProductSku;
use App\Models\DberpReceiptPaymentQuery;
use App\Models\DberpSalesQuote;
use App\Models\DberpSettlementEntry;
use App\Support\AuditLogDisplay\DberpAuditLogCategoryQuery;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;

final class QueryCenterService
{
    public function documentQuery(array $filters = []): Builder
    {
        return DberpDocumentQuery::query()
            ->with(['customer', 'supplier', 'warehouse', 'handlerEmployee'])
            ->when($filters['document_type'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('document_type', $value))
            ->when($filters['status'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('status', $value))
            ->when(($filters['customer_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('customer_id', (int) $filters['customer_id']))
            ->when(($filters['supplier_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('supplier_id', (int) $filters['supplier_id']))
            ->when(($filters['warehouse_id'] ?? 0) > 0, function (Builder $builder) use ($filters): Builder {
                $warehouseId = (int) $filters['warehouse_id'];

                return $builder->where(function (Builder $scoped) use ($warehouseId): void {
                    $scoped
                        ->where('warehouse_id', $warehouseId)
                        ->orWhere('to_warehouse_id', $warehouseId);
                });
            })
            ->when(($filters['handler_employee_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('handler_employee_id', (int) $filters['handler_employee_id']))
            ->when($filters['business_time_from'] ?? null, fn (Builder $builder, $date): Builder => $builder->whereDate('business_time', '>=', $date))
            ->when($filters['business_time_until'] ?? null, fn (Builder $builder, $date): Builder => $builder->whereDate('business_time', '<=', $date));
    }

    public function productBusinessQuery(array $filters = []): Builder
    {
        $table = (new DberpProduct)->getTable();
        $salesAgg = $this->buildProductSalesMetricsSubquery($filters);
        $purchaseAgg = $this->buildProductPurchaseMetricsSubquery($filters);
        $stockAgg = $this->buildProductStockMetricsSubquery($filters);

        return DberpProduct::query()
            ->with(['category', 'unit'])
            ->leftJoinSub($salesAgg, 'po_product_sales', 'po_product_sales.product_id', '=', $table.'.id')
            ->leftJoinSub($purchaseAgg, 'po_product_purchase', 'po_product_purchase.product_id', '=', $table.'.id')
            ->leftJoinSub($stockAgg, 'po_product_stock', 'po_product_stock.product_id', '=', $table.'.id')
            ->select($table.'.*')
            ->selectRaw('COALESCE(po_product_sales.sales_amount, 0) as sales_amount')
            ->selectRaw('COALESCE(po_product_sales.sales_quantity, 0) as sales_quantity')
            ->selectRaw('COALESCE(po_product_purchase.purchase_amount, 0) as purchase_amount')
            ->selectRaw('COALESCE(po_product_purchase.purchase_quantity, 0) as purchase_quantity')
            ->selectRaw('COALESCE(po_product_stock.current_stock_quantity, 0) as current_stock_quantity')
            ->when(($filters['category_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where($table.'.category_id', (int) $filters['category_id']))
            ->when(
                ($filters['product_sku_id'] ?? 0) > 0,
                function (Builder $builder) use ($filters, $table): Builder {
                    $productId = (int) DberpProductSku::query()->whereKey((int) $filters['product_sku_id'])->value('product_id');
                    if ($productId <= 0) {
                        return $builder->whereRaw('1 = 0');
                    }
                    if (($filters['product_id'] ?? 0) > 0 && (int) $filters['product_id'] !== $productId) {
                        return $builder->whereRaw('1 = 0');
                    }

                    return $builder->where($table.'.id', $productId);
                },
            )
            ->when(
                ($filters['product_sku_id'] ?? 0) <= 0 && ($filters['product_id'] ?? 0) > 0,
                fn (Builder $builder): Builder => $builder->whereKey((int) $filters['product_id']),
            );
    }

    /**
     * 按「商品规格（SKU）」维度展示经营指标（销售/采购/当前库存均按 product_sku_id 对齐）。
     * 默认列出全部规格组合；筛选口径与 {@see self::productBusinessQuery()} 的子查询一致。
     */
    public function productSkuBusinessQuery(array $filters = []): Builder
    {
        $table = (new DberpProductSku)->getTable();
        $salesAgg = $this->buildProductSkuSalesMetricsSubquery($filters);
        $purchaseAgg = $this->buildProductSkuPurchaseMetricsSubquery($filters);
        $stockAgg = $this->buildProductSkuStockMetricsSubquery($filters);

        return DberpProductSku::query()
            ->with(['product.category', 'product.unit'])
            ->leftJoinSub($salesAgg, 'po_sku_sales', 'po_sku_sales.product_sku_id', '=', $table.'.id')
            ->leftJoinSub($purchaseAgg, 'po_sku_purchase', 'po_sku_purchase.product_sku_id', '=', $table.'.id')
            ->leftJoinSub($stockAgg, 'po_sku_stock', 'po_sku_stock.product_sku_id', '=', $table.'.id')
            ->select($table.'.*')
            ->selectRaw('COALESCE(po_sku_sales.sales_amount, 0) as sales_amount')
            ->selectRaw('COALESCE(po_sku_sales.sales_quantity, 0) as sales_quantity')
            ->selectRaw('COALESCE(po_sku_purchase.purchase_amount, 0) as purchase_amount')
            ->selectRaw('COALESCE(po_sku_purchase.purchase_quantity, 0) as purchase_quantity')
            ->selectRaw('COALESCE(po_sku_stock.current_stock_quantity, 0) as current_stock_quantity')
            ->when(($filters['category_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->whereHas('product', fn (Builder $productQuery) => $productQuery->where('category_id', (int) $filters['category_id'])))
            ->when(($filters['product_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where($table.'.product_id', (int) $filters['product_id']))
            ->when(($filters['product_sku_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->whereKey((int) $filters['product_sku_id']));
    }

    public function customerSettlementQuery(array $filters = []): Builder
    {
        return DberpSettlementEntry::query()
            ->with('customer')
            ->where('counterparty_type', 'customer')
            ->when(($filters['customer_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('counterparty_id', (int) $filters['customer_id']))
            ->when($filters['source_type'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('source_type', $value))
            ->when($filters['change_type'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('change_type', $value))
            ->when($filters['business_time_from'] ?? null, fn (Builder $builder, $date): Builder => $builder->whereDate('business_time', '>=', $date))
            ->when($filters['business_time_until'] ?? null, fn (Builder $builder, $date): Builder => $builder->whereDate('business_time', '<=', $date));
    }

    public function supplierSettlementQuery(array $filters = []): Builder
    {
        return DberpSettlementEntry::query()
            ->with('supplier')
            ->where('counterparty_type', 'supplier')
            ->when(($filters['supplier_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('counterparty_id', (int) $filters['supplier_id']))
            ->when($filters['source_type'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('source_type', $value))
            ->when($filters['change_type'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('change_type', $value))
            ->when($filters['business_time_from'] ?? null, fn (Builder $builder, $date): Builder => $builder->whereDate('business_time', '>=', $date))
            ->when($filters['business_time_until'] ?? null, fn (Builder $builder, $date): Builder => $builder->whereDate('business_time', '<=', $date));
    }

    public function receiptPaymentQuery(array $filters = []): Builder
    {
        return DberpReceiptPaymentQuery::query()
            ->with(['customer', 'supplier', 'paymentMethodItem', 'handlerEmployee', 'salesOrder', 'purchaseOrder'])
            ->when($filters['direction'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('direction', $value))
            ->when($filters['status'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('status', $value))
            ->when(($filters['customer_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('customer_id', (int) $filters['customer_id']))
            ->when(($filters['supplier_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('supplier_id', (int) $filters['supplier_id']))
            ->when(($filters['payment_method_item_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('payment_method_item_id', (int) $filters['payment_method_item_id']))
            ->when(($filters['handler_employee_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('handler_employee_id', (int) $filters['handler_employee_id']))
            ->when($filters['business_time_from'] ?? null, fn (Builder $builder, $date): Builder => $builder->whereDate('business_time', '>=', $date))
            ->when($filters['business_time_until'] ?? null, fn (Builder $builder, $date): Builder => $builder->whereDate('business_time', '<=', $date));
    }

    public function inventoryTransactionQuery(array $filters = []): Builder
    {
        return DberpInventoryTransaction::query()
            ->with(['product.category', 'warehouse', 'operator'])
            ->when($filters['direction'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('direction', $value))
            ->when($filters['business_type'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('business_type', $value))
            ->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('product_sku_id', (int) $filters['product_sku_id']))
            ->when($filters['business_time_from'] ?? null, fn (Builder $builder, $date): Builder => $builder->whereDate('business_time', '>=', $date))
            ->when($filters['business_time_until'] ?? null, fn (Builder $builder, $date): Builder => $builder->whereDate('business_time', '<=', $date));
    }

    public function auditLogQuery(array $filters = []): Builder
    {
        return DberpAuditLog::query()
            ->with('user')
            ->when($filters['action'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('action', 'like', $value.'%'))
            ->when($filters['auditable_type'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('auditable_type', $value))
            ->when(($filters['auditable_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('auditable_id', (int) $filters['auditable_id']))
            ->when(($filters['user_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('user_id', (int) $filters['user_id']))
            ->when($filters['category'] ?? null, function (Builder $builder, string $category): Builder {
                $resolved = DberpAuditLogCategory::tryFrom($category);

                return $resolved === null
                    ? $builder
                    : app(DberpAuditLogCategoryQuery::class)->apply($builder, $resolved);
            })
            ->when($filters['correlation_id'] ?? null, fn (Builder $builder, string $value): Builder => $builder->where('correlation_id', $value))
            ->when($filters['created_from'] ?? null, fn (Builder $builder, $date): Builder => $builder->whereDate('created_at', '>=', $date))
            ->when($filters['created_until'] ?? null, fn (Builder $builder, $date): Builder => $builder->whereDate('created_at', '<=', $date));
    }

    public function salesQuoteQuery(array $filters = []): Builder
    {
        return DberpSalesQuote::query()
            ->with(['customer', 'handlerEmployee'])
            ->when($filters['status'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('status', $value))
            ->when(($filters['customer_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('customer_id', (int) $filters['customer_id']))
            ->when(($filters['handler_employee_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('handler_employee_id', (int) $filters['handler_employee_id']))
            ->when($filters['attention_status'] ?? null, function (Builder $builder, $value): Builder {
                $today = now()->toDateString();
                $dueSoonDate = now()->addDays((int) config('dberp.sales.reminders.quote_expiry_soon_days', 7))->toDateString();
                $longUnconvertedDate = now()->subDays((int) config('dberp.sales.reminders.long_unconverted_days', 15))->toDateString();

                $builder->whereNotIn('status', ['draft', 'converted', 'voided']);

                return match ($value) {
                    'pending_effective' => $builder->whereDate('valid_from', '>', $today),
                    'expiring_soon' => $builder->whereNull('converted_sales_order_id')->whereDate('valid_from', '<=', $today)->whereBetween('valid_until', [$today, $dueSoonDate]),
                    'expired' => $builder->whereDate('valid_until', '<', $today),
                    'long_unconverted' => $builder->whereNull('converted_sales_order_id')->whereDate('valid_from', '<=', $today)->whereDate('valid_until', '>=', $today)->whereDate('quote_date', '<=', $longUnconvertedDate),
                    default => $builder,
                };
            });
    }

    public function customerPriceRuleQuery(array $filters = []): Builder
    {
        return DberpCustomerPriceRule::query()
            ->with(['customer', 'product'])
            ->when($filters['rule_type'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('rule_type', $value))
            ->when($filters['status'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('status', $value))
            ->when(($filters['customer_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('customer_id', (int) $filters['customer_id']))
            ->when($filters['attention_status'] ?? null, function (Builder $builder, $value): Builder {
                $today = now()->toDateString();
                $dueSoonDate = now()->addDays((int) config('dberp.sales.reminders.price_rule_expiry_soon_days', 7))->toDateString();

                $builder->where('status', 'active');

                return match ($value) {
                    'expiring_soon' => $builder->whereBetween('valid_until', [$today, $dueSoonDate]),
                    'expired' => $builder->whereDate('valid_until', '<', $today),
                    default => $builder,
                };
            });
    }

    public function integrationSyncLogQuery(array $filters = []): Builder
    {
        return DberpIntegrationSyncLog::query()
            ->with('relatedModel')
            ->when($filters['channel'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('channel', $value))
            ->when($filters['source_system'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('source_system', $value))
            ->when($filters['target_system'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('target_system', $value))
            ->when($filters['direction'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('direction', $value))
            ->when($filters['sync_object_type'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('sync_object_type', $value))
            ->when($filters['status'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('status', $value))
            ->when($filters['error_type'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('error_type', $value))
            ->when($filters['source_document_no'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('source_document_no', 'like', '%'.$value.'%'))
            ->when($filters['external_reference'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('external_reference', 'like', '%'.$value.'%'))
            ->when($filters['reference'] ?? null, fn (Builder $builder, $value): Builder => $builder->where('reference', 'like', '%'.$value.'%'))
            ->when($filters['created_from'] ?? null, fn (Builder $builder, $date): Builder => $builder->whereDate('created_at', '>=', $date))
            ->when($filters['created_until'] ?? null, fn (Builder $builder, $date): Builder => $builder->whereDate('created_at', '<=', $date));
    }

    /**
     * 商品经营查询列表：业务时间筛选（按「有购销流水」缩窄主键集合）。
     */
    public static function applyProductOperationBusinessTimeListingFilter(Builder $query, array $data): Builder
    {
        if (! ($data['from'] ?? null) && ! ($data['until'] ?? null)) {
            return $query;
        }

        $model = $query->getModel();
        if ($model instanceof DberpProductSku) {
            return $query->where(function (Builder $builder) use ($data): void {
                $builder
                    ->whereIn('dberp_product_skus.id', DB::table('dberp_sales_order_items')
                        ->join('dberp_sales_orders', 'dberp_sales_orders.id', '=', 'dberp_sales_order_items.order_id')
                        ->select('dberp_sales_order_items.product_sku_id')
                        ->whereNotNull('dberp_sales_order_items.product_sku_id')
                        ->when($data['from'] ?? null, fn ($subQuery, $date) => $subQuery->whereDate('dberp_sales_orders.business_time', '>=', $date))
                        ->when($data['until'] ?? null, fn ($subQuery, $date) => $subQuery->whereDate('dberp_sales_orders.business_time', '<=', $date))
                        ->distinct())
                    ->orWhereIn('dberp_product_skus.id', DB::table('dberp_purchase_order_items')
                        ->join('dberp_purchase_orders', 'dberp_purchase_orders.id', '=', 'dberp_purchase_order_items.order_id')
                        ->select('dberp_purchase_order_items.product_sku_id')
                        ->whereNotNull('dberp_purchase_order_items.product_sku_id')
                        ->when($data['from'] ?? null, fn ($subQuery, $date) => $subQuery->whereDate('dberp_purchase_orders.business_time', '>=', $date))
                        ->when($data['until'] ?? null, fn ($subQuery, $date) => $subQuery->whereDate('dberp_purchase_orders.business_time', '<=', $date))
                        ->distinct());
            });
        }

        return $query->where(function (Builder $builder) use ($data): void {
            $builder
                ->whereIn('id', DB::table('dberp_sales_order_items')
                    ->join('dberp_sales_orders', 'dberp_sales_orders.id', '=', 'dberp_sales_order_items.order_id')
                    ->select('dberp_sales_order_items.product_id')
                    ->when($data['from'] ?? null, fn ($subQuery, $date) => $subQuery->whereDate('dberp_sales_orders.business_time', '>=', $date))
                    ->when($data['until'] ?? null, fn ($subQuery, $date) => $subQuery->whereDate('dberp_sales_orders.business_time', '<=', $date))
                    ->distinct())
                ->orWhereIn('id', DB::table('dberp_purchase_order_items')
                    ->join('dberp_purchase_orders', 'dberp_purchase_orders.id', '=', 'dberp_purchase_order_items.order_id')
                    ->select('dberp_purchase_order_items.product_id')
                    ->when($data['from'] ?? null, fn ($subQuery, $date) => $subQuery->whereDate('dberp_purchase_orders.business_time', '>=', $date))
                    ->when($data['until'] ?? null, fn ($subQuery, $date) => $subQuery->whereDate('dberp_purchase_orders.business_time', '<=', $date))
                    ->distinct());
        });
    }

    public static function applyProductOperationCustomerListingFilter(Builder $query, int $customerId): Builder
    {
        $model = $query->getModel();
        if ($model instanceof DberpProductSku) {
            return $query->whereIn('dberp_product_skus.id', DB::table('dberp_sales_order_items')
                ->join('dberp_sales_orders', 'dberp_sales_orders.id', '=', 'dberp_sales_order_items.order_id')
                ->select('dberp_sales_order_items.product_sku_id')
                ->where('dberp_sales_orders.customer_id', $customerId)
                ->whereNotNull('dberp_sales_order_items.product_sku_id')
                ->distinct());
        }

        return $query->whereIn('id', DB::table('dberp_sales_order_items')
            ->join('dberp_sales_orders', 'dberp_sales_orders.id', '=', 'dberp_sales_order_items.order_id')
            ->select('dberp_sales_order_items.product_id')
            ->where('dberp_sales_orders.customer_id', $customerId)
            ->distinct());
    }

    public static function applyProductOperationSupplierListingFilter(Builder $query, int $supplierId): Builder
    {
        $model = $query->getModel();
        if ($model instanceof DberpProductSku) {
            return $query->whereIn('dberp_product_skus.id', DB::table('dberp_purchase_order_items')
                ->join('dberp_purchase_orders', 'dberp_purchase_orders.id', '=', 'dberp_purchase_order_items.order_id')
                ->select('dberp_purchase_order_items.product_sku_id')
                ->where('dberp_purchase_orders.supplier_id', $supplierId)
                ->whereNotNull('dberp_purchase_order_items.product_sku_id')
                ->distinct());
        }

        return $query->whereIn('id', DB::table('dberp_purchase_order_items')
            ->join('dberp_purchase_orders', 'dberp_purchase_orders.id', '=', 'dberp_purchase_order_items.order_id')
            ->select('dberp_purchase_order_items.product_id')
            ->where('dberp_purchase_orders.supplier_id', $supplierId)
            ->distinct());
    }

    public static function applyProductOperationProductIdListingFilter(Builder $query, int $productId): Builder
    {
        $model = $query->getModel();
        if ($model instanceof DberpProductSku) {
            return $query->where('dberp_product_skus.product_id', $productId);
        }

        return $query->whereKey($productId);
    }

    public static function applyProductOperationProductSkuIdListingFilter(Builder $query, int $productSkuId): Builder
    {
        $model = $query->getModel();
        if ($model instanceof DberpProductSku) {
            return $query->whereKey($productSkuId);
        }

        $productId = (int) DberpProductSku::query()->whereKey($productSkuId)->value('product_id');

        return $productId > 0 ? $query->whereKey($productId) : $query->whereRaw('1 = 0');
    }

    /**
     * @param  array<string, mixed>  $filters
     */
    private function buildProductSalesMetricsSubquery(array $filters)
    {
        return DB::table('dberp_sales_order_items')
            ->join('dberp_sales_orders', 'dberp_sales_orders.id', '=', 'dberp_sales_order_items.order_id')
            ->where('dberp_sales_orders.status', '!=', 'draft')
            ->when(($filters['customer_id'] ?? 0) > 0, fn ($builder) => $builder->where('dberp_sales_orders.customer_id', (int) $filters['customer_id']))
            ->when($filters['business_time_from'] ?? null, fn ($builder, $date) => $builder->whereDate('dberp_sales_orders.business_time', '>=', $date))
            ->when($filters['business_time_until'] ?? null, fn ($builder, $date) => $builder->whereDate('dberp_sales_orders.business_time', '<=', $date))
            ->groupBy('dberp_sales_order_items.product_id')
            ->selectRaw('dberp_sales_order_items.product_id as product_id')
            ->selectRaw('COALESCE(SUM(dberp_sales_order_items.final_amount), 0) as sales_amount')
            ->selectRaw('COALESCE(SUM(dberp_sales_order_items.base_quantity), 0) as sales_quantity');
    }

    /**
     * @param  array<string, mixed>  $filters
     */
    private function buildProductPurchaseMetricsSubquery(array $filters)
    {
        return DB::table('dberp_purchase_order_items')
            ->join('dberp_purchase_orders', 'dberp_purchase_orders.id', '=', 'dberp_purchase_order_items.order_id')
            ->where('dberp_purchase_orders.status', '!=', 'draft')
            ->when(($filters['supplier_id'] ?? 0) > 0, fn ($builder) => $builder->where('dberp_purchase_orders.supplier_id', (int) $filters['supplier_id']))
            ->when($filters['business_time_from'] ?? null, fn ($builder, $date) => $builder->whereDate('dberp_purchase_orders.business_time', '>=', $date))
            ->when($filters['business_time_until'] ?? null, fn ($builder, $date) => $builder->whereDate('dberp_purchase_orders.business_time', '<=', $date))
            ->groupBy('dberp_purchase_order_items.product_id')
            ->selectRaw('dberp_purchase_order_items.product_id as product_id')
            ->selectRaw('COALESCE(SUM(dberp_purchase_order_items.line_amount), 0) as purchase_amount')
            ->selectRaw('COALESCE(SUM(dberp_purchase_order_items.base_quantity), 0) as purchase_quantity');
    }

    /**
     * @param  array<string, mixed>  $filters
     */
    private function buildProductStockMetricsSubquery(array $filters)
    {
        return DB::table('dberp_inventory_balances')
            ->when(($filters['warehouse_id'] ?? 0) > 0, fn ($builder) => $builder->where('warehouse_id', (int) $filters['warehouse_id']))
            ->groupBy('product_id')
            ->selectRaw('product_id')
            ->selectRaw('COALESCE(SUM(realtime_quantity), 0) as current_stock_quantity');
    }

    /**
     * @param  array<string, mixed>  $filters
     */
    private function buildProductSkuSalesMetricsSubquery(array $filters)
    {
        return DB::table('dberp_sales_order_items')
            ->join('dberp_sales_orders', 'dberp_sales_orders.id', '=', 'dberp_sales_order_items.order_id')
            ->where('dberp_sales_orders.status', '!=', 'draft')
            ->whereNotNull('dberp_sales_order_items.product_sku_id')
            ->when(($filters['customer_id'] ?? 0) > 0, fn ($builder) => $builder->where('dberp_sales_orders.customer_id', (int) $filters['customer_id']))
            ->when($filters['business_time_from'] ?? null, fn ($builder, $date) => $builder->whereDate('dberp_sales_orders.business_time', '>=', $date))
            ->when($filters['business_time_until'] ?? null, fn ($builder, $date) => $builder->whereDate('dberp_sales_orders.business_time', '<=', $date))
            ->groupBy('dberp_sales_order_items.product_sku_id')
            ->selectRaw('dberp_sales_order_items.product_sku_id as product_sku_id')
            ->selectRaw('COALESCE(SUM(dberp_sales_order_items.final_amount), 0) as sales_amount')
            ->selectRaw('COALESCE(SUM(dberp_sales_order_items.base_quantity), 0) as sales_quantity');
    }

    /**
     * @param  array<string, mixed>  $filters
     */
    private function buildProductSkuPurchaseMetricsSubquery(array $filters)
    {
        return DB::table('dberp_purchase_order_items')
            ->join('dberp_purchase_orders', 'dberp_purchase_orders.id', '=', 'dberp_purchase_order_items.order_id')
            ->where('dberp_purchase_orders.status', '!=', 'draft')
            ->whereNotNull('dberp_purchase_order_items.product_sku_id')
            ->when(($filters['supplier_id'] ?? 0) > 0, fn ($builder) => $builder->where('dberp_purchase_orders.supplier_id', (int) $filters['supplier_id']))
            ->when($filters['business_time_from'] ?? null, fn ($builder, $date) => $builder->whereDate('dberp_purchase_orders.business_time', '>=', $date))
            ->when($filters['business_time_until'] ?? null, fn ($builder, $date) => $builder->whereDate('dberp_purchase_orders.business_time', '<=', $date))
            ->groupBy('dberp_purchase_order_items.product_sku_id')
            ->selectRaw('dberp_purchase_order_items.product_sku_id as product_sku_id')
            ->selectRaw('COALESCE(SUM(dberp_purchase_order_items.line_amount), 0) as purchase_amount')
            ->selectRaw('COALESCE(SUM(dberp_purchase_order_items.base_quantity), 0) as purchase_quantity');
    }

    /**
     * @param  array<string, mixed>  $filters
     */
    private function buildProductSkuStockMetricsSubquery(array $filters)
    {
        return DB::table('dberp_inventory_balances')
            ->when(($filters['warehouse_id'] ?? 0) > 0, fn ($builder) => $builder->where('warehouse_id', (int) $filters['warehouse_id']))
            ->whereNotNull('product_sku_id')
            ->groupBy('product_sku_id')
            ->selectRaw('product_sku_id')
            ->selectRaw('COALESCE(SUM(realtime_quantity), 0) as current_stock_quantity');
    }

    /**
     * @param  array<string, mixed>|null  $tableFilters
     * @return array<string, mixed>
     */
    public static function productOperationFiltersFromTableState(?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);
        };

        $customerId = (int) ($pick('customer_id') ?? 0);
        $supplierId = (int) ($pick('supplier_id') ?? 0);
        $categoryId = (int) ($pick('category_id') ?? 0);
        $productId = (int) ($pick('product_id') ?? 0);
        $productSkuId = (int) ($pick('product_sku_id') ?? 0);
        $warehouseId = (int) ($pick('warehouse_id') ?? 0);
        $aggregateLevel = (string) ($pick('business_aggregate_level') ?? 'product');

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

        return array_filter([
            'customer_id' => $customerId > 0 ? $customerId : null,
            'supplier_id' => $supplierId > 0 ? $supplierId : null,
            'category_id' => $categoryId > 0 ? $categoryId : null,
            'product_id' => $productId > 0 ? $productId : null,
            'product_sku_id' => $productSkuId > 0 ? $productSkuId : null,
            'warehouse_id' => $warehouseId > 0 ? $warehouseId : null,
            'business_aggregate_level' => in_array($aggregateLevel, ['product', 'product_spec'], true) ? $aggregateLevel : null,
            'business_time_from' => $businessTimeFrom,
            'business_time_until' => $businessTimeUntil,
        ], static fn ($v): bool => $v !== null && $v !== '' && $v !== 0);
    }
}
