<?php

declare(strict_types=1);

namespace App\Business\Report;

use App\Business\Inventory\Enums\InventoryDocumentStatus;
use App\Models\DberpProduct;
use App\Models\DberpProductSku;
use App\Models\DberpPurchaseOrder;
use App\Models\DberpPurchasePriceReferenceSnapshot;
use App\Models\DberpSupplier;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Query\Builder as QueryBuilder;
use Illuminate\Support\Facades\DB;

final class PurchaseReportService
{
    /**
     * 将 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);
        };

        $supplierId = (int) ($pick('supplier_id') ?? 0);
        $handlerEmployeeId = (int) ($pick('handler_employee_id') ?? 0);
        $categoryId = (int) ($pick('category_id') ?? 0);
        $productId = (int) ($pick('product_id') ?? 0);
        $productSkuId = (int) ($pick('product_sku_id') ?? 0);
        $status = $pick('status');

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

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

    /**
     * 采购分析 / 聚合用：不含 with，避免在大量聚合场景重复预加载。
     */
    public function purchaseOrdersFilteredQuery(array $filters = []): Builder
    {
        $query = DberpPurchaseOrder::query()->whereNot('status', 'draft');

        return $this->applyPurchaseOrderFilters($query, $filters);
    }

    public function statisticsQuery(array $filters = []): Builder
    {
        $query = DberpPurchaseOrder::query()
            ->with(['supplier', 'handlerEmployee'])
            ->whereNot('status', 'draft');

        return $this->applyPurchaseOrderFilters($query, $filters);
    }

    public function supplierRankingQuery(array $filters = [])
    {
        $purchaseOrders = DberpPurchaseOrder::query()
            ->selectRaw('COALESCE(SUM(total_amount), 0)')
            ->whereColumn('supplier_id', 'dberp_suppliers.id')
            ->whereNot('status', 'draft');

        $paidOrders = DberpPurchaseOrder::query()
            ->selectRaw('COALESCE(SUM(paid_amount), 0)')
            ->whereColumn('supplier_id', 'dberp_suppliers.id')
            ->whereNot('status', 'draft');

        $orderCount = DberpPurchaseOrder::query()
            ->selectRaw('COUNT(*)')
            ->whereColumn('supplier_id', 'dberp_suppliers.id')
            ->whereNot('status', 'draft');

        $this->applyPurchaseOrderFilters($purchaseOrders, $filters);
        $this->applyPurchaseOrderFilters($paidOrders, $filters);
        $this->applyPurchaseOrderFilters($orderCount, $filters);

        $existsQuery = DberpPurchaseOrder::query()
            ->selectRaw('1')
            ->whereColumn('supplier_id', 'dberp_suppliers.id')
            ->whereNot('status', 'draft');

        $this->applyPurchaseOrderFilters($existsQuery, $filters);

        return DberpSupplier::query()
            ->addSelect([
                'purchase_amount' => $purchaseOrders,
                'paid_amount_total' => $paidOrders,
                'order_count' => $orderCount,
            ])
            ->whereExists($existsQuery)
            ->orderByDesc('purchase_amount')
            ->orderBy('id');
    }

    public function productRankingQuery(array $filters = [])
    {
        $purchaseItemQuery = DB::table('dberp_purchase_order_items')
            ->join('dberp_purchase_orders', 'dberp_purchase_orders.id', '=', 'dberp_purchase_order_items.order_id')
            ->whereColumn('dberp_purchase_order_items.product_id', 'dberp_products.id')
            ->where('dberp_purchase_orders.status', '!=', 'draft');

        $purchaseAmount = (clone $purchaseItemQuery)->selectRaw('COALESCE(SUM(dberp_purchase_order_items.line_amount), 0)');
        $purchaseQuantity = (clone $purchaseItemQuery)->selectRaw('COALESCE(SUM(dberp_purchase_order_items.base_quantity), 0)');
        $orderCount = (clone $purchaseItemQuery)->selectRaw('COUNT(DISTINCT dberp_purchase_order_items.order_id)');

        $this->applyPurchaseItemFilters($purchaseAmount, $filters);
        $this->applyPurchaseItemFilters($purchaseQuantity, $filters);
        $this->applyPurchaseItemFilters($orderCount, $filters);

        $existsQuery = DB::table('dberp_purchase_order_items')
            ->selectRaw('1')
            ->join('dberp_purchase_orders', 'dberp_purchase_orders.id', '=', 'dberp_purchase_order_items.order_id')
            ->whereColumn('dberp_purchase_order_items.product_id', 'dberp_products.id')
            ->where('dberp_purchase_orders.status', '!=', 'draft');

        $this->applyPurchaseItemFilters($existsQuery, $filters);

        return DberpProduct::query()
            ->with(['category', 'unit'])
            ->addSelect([
                'purchase_amount' => $purchaseAmount,
                'purchase_quantity' => $purchaseQuantity,
                'purchase_order_count' => $orderCount,
            ])
            ->whereExists($existsQuery)
            ->orderByDesc('purchase_amount')
            ->orderBy('id');
    }

    /**
     * 按「商品规格」维度的采购排行（仅统计行上已绑定 product_sku_id 的明细）。
     */
    public function productSkuRankingQuery(array $filters = [])
    {
        $purchaseItemQuery = DB::table('dberp_purchase_order_items')
            ->join('dberp_purchase_orders', 'dberp_purchase_orders.id', '=', 'dberp_purchase_order_items.order_id')
            ->whereColumn('dberp_purchase_order_items.product_sku_id', 'dberp_product_skus.id')
            ->where('dberp_purchase_orders.status', '!=', 'draft');

        $purchaseAmount = (clone $purchaseItemQuery)->selectRaw('COALESCE(SUM(dberp_purchase_order_items.line_amount), 0)');
        $purchaseQuantity = (clone $purchaseItemQuery)->selectRaw('COALESCE(SUM(dberp_purchase_order_items.base_quantity), 0)');
        $orderCount = (clone $purchaseItemQuery)->selectRaw('COUNT(DISTINCT dberp_purchase_order_items.order_id)');

        $this->applyPurchaseItemFilters($purchaseAmount, $filters);
        $this->applyPurchaseItemFilters($purchaseQuantity, $filters);
        $this->applyPurchaseItemFilters($orderCount, $filters);

        $existsQuery = DB::table('dberp_purchase_order_items')
            ->selectRaw('1')
            ->join('dberp_purchase_orders', 'dberp_purchase_orders.id', '=', 'dberp_purchase_order_items.order_id')
            ->whereColumn('dberp_purchase_order_items.product_sku_id', 'dberp_product_skus.id')
            ->where('dberp_purchase_orders.status', '!=', 'draft');

        $this->applyPurchaseItemFilters($existsQuery, $filters);

        return DberpProductSku::query()
            ->with(['product.category', 'product.unit'])
            ->addSelect([
                'purchase_amount' => $purchaseAmount,
                'purchase_quantity' => $purchaseQuantity,
                'purchase_order_count' => $orderCount,
            ])
            ->whereExists($existsQuery)
            ->orderByDesc('purchase_amount')
            ->orderBy('id');
    }

    public function supplierPerformanceQuery(array $filters = []): Builder
    {
        $purchaseOrders = DberpPurchaseOrder::query()
            ->selectRaw('COALESCE(SUM(total_amount), 0)')
            ->whereColumn('supplier_id', 'dberp_suppliers.id')
            ->whereNot('status', 'draft');

        $orderCount = DberpPurchaseOrder::query()
            ->selectRaw('COUNT(*)')
            ->whereColumn('supplier_id', 'dberp_suppliers.id')
            ->whereNot('status', 'draft');

        $lastPurchaseDate = DberpPurchaseOrder::query()
            ->selectRaw('MAX(business_time)')
            ->whereColumn('supplier_id', 'dberp_suppliers.id')
            ->whereNot('status', 'draft');

        $this->applyPurchaseOrderFilters($purchaseOrders, $filters);
        $this->applyPurchaseOrderFilters($orderCount, $filters);
        $this->applyPurchaseOrderFilters($lastPurchaseDate, $filters);

        $effectiveReceiptCount = DB::table('dberp_purchase_receipt_orders')
            ->join('dberp_purchase_orders as purchase_orders', 'purchase_orders.id', '=', 'dberp_purchase_receipt_orders.purchase_order_id')
            ->selectRaw('COUNT(*)')
            ->whereColumn('dberp_purchase_receipt_orders.supplier_id', 'dberp_suppliers.id')
            ->where('dberp_purchase_receipt_orders.status', InventoryDocumentStatus::Effective->value)
            ->where('purchase_orders.status', '!=', 'draft');

        $this->applyPurchaseOrderFiltersToJoinedPurchaseOrdersAlias($effectiveReceiptCount, 'purchase_orders', $filters);

        $exceptionCount30d = DB::table('dberp_purchase_exceptions')
            ->selectRaw('COUNT(*)')
            ->whereColumn('supplier_id', 'dberp_suppliers.id')
            ->whereDate('created_at', '>=', now()->subDays(30)->toDateString());

        $returnOrderCount30d = DB::table('dberp_purchase_return_orders')
            ->selectRaw('COUNT(*)')
            ->whereColumn('supplier_id', 'dberp_suppliers.id')
            ->where('status', InventoryDocumentStatus::Effective->value)
            ->whereDate('business_time', '>=', now()->subDays(30)->toDateString());

        $priceReferenceBaseQuery = DberpPurchasePriceReferenceSnapshot::query()
            ->whereColumn('supplier_id', 'dberp_suppliers.id')
            ->where('scope_type', 'supplier_product');

        $activePriceReferenceCount = (clone $priceReferenceBaseQuery)
            ->selectRaw('COUNT(*)')
            ->whereNotNull('latest_transaction_date');

        $averageLeadTimeDays = (clone $priceReferenceBaseQuery)
            ->selectRaw('ROUND(AVG(latest_lead_time_days), 0)')
            ->whereNotNull('latest_lead_time_days');

        $latestReferenceDate = (clone $priceReferenceBaseQuery)
            ->selectRaw('MAX(latest_transaction_date)');

        $existsQuery = DberpPurchaseOrder::query()
            ->selectRaw('1')
            ->whereColumn('supplier_id', 'dberp_suppliers.id')
            ->whereNot('status', 'draft');

        $this->applyPurchaseOrderFilters($existsQuery, $filters);

        return DberpSupplier::query()
            ->addSelect([
                'purchase_amount' => $purchaseOrders,
                'order_count' => $orderCount,
                'last_purchase_date' => $lastPurchaseDate,
                'effective_receipt_count' => $effectiveReceiptCount,
                'exception_count_30d' => $exceptionCount30d,
                'return_order_count_30d' => $returnOrderCount30d,
                'active_price_reference_count' => $activePriceReferenceCount,
                'avg_lead_time_days' => $averageLeadTimeDays,
                'latest_reference_date' => $latestReferenceDate,
            ])
            ->whereExists($existsQuery)
            ->orderByDesc('exception_count_30d')
            ->orderByDesc('purchase_amount')
            ->orderBy('id');
    }

    public function priceReferenceQuery(array $filters = []): Builder
    {
        $query = DberpPurchasePriceReferenceSnapshot::query()
            ->with(['product', 'supplier', 'latestPurchaseOrder'])
            ->when($filters['scope_type'] ?? null, fn (Builder $builder, $scopeType): Builder => $builder->where('scope_type', $scopeType))
            ->when(($filters['supplier_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('supplier_id', (int) $filters['supplier_id']))
            ->when(($filters['product_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('product_id', (int) $filters['product_id']))
            ->when(self::priceReferenceStaleFilterActive($filters), fn (Builder $builder): Builder => self::applyIsStalePriceReferenceConstraint($builder))
            ->orderByDesc('latest_transaction_date')
            ->orderByDesc('refreshed_at')
            ->orderByDesc('id');

        /** @var Builder $query */
        return $query;
    }

    /**
     * @param  array<string, mixed>  $filters
     */
    public static function priceReferenceStaleFilterActive(array $filters): bool
    {
        $flag = $filters['is_stale_isActive'] ?? $filters['is_stale'] ?? false;

        if (is_bool($flag)) {
            return $flag;
        }

        if (is_string($flag)) {
            return in_array(strtolower($flag), ['1', 'true', 'yes', 'on'], true);
        }

        return (int) $flag === 1;
    }

    public static function applyIsStalePriceReferenceConstraint(Builder $query): Builder
    {
        return $query
            ->where('scope_type', 'supplier_product')
            ->where(function (Builder $builder): Builder {
                return $builder
                    ->whereNull('latest_transaction_date')
                    ->orWhereDate('latest_transaction_date', '<', now()->subDays(30)->toDateString());
            });
    }

    private function applyPurchaseOrderFilters(Builder $query, array $filters): Builder
    {
        return $query
            ->when($filters['status'] ?? null, fn (Builder $builder, $status): Builder => $builder->where('status', $status))
            ->when(($filters['supplier_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->where('supplier_id', (int) $filters['supplier_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))
            ->when(($filters['product_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->whereHas('items', fn (Builder $itemQuery) => $itemQuery->where('product_id', (int) $filters['product_id'])))
            ->when(($filters['product_sku_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->whereHas('items', fn (Builder $itemQuery) => $itemQuery->where('product_sku_id', (int) $filters['product_sku_id'])))
            ->when(($filters['category_id'] ?? 0) > 0, fn (Builder $builder): Builder => $builder->whereHas('items.product', fn (Builder $productQuery) => $productQuery->where('category_id', (int) $filters['category_id'])));
    }

    private function applyPurchaseItemFilters(QueryBuilder $query, array $filters): QueryBuilder
    {
        return $query
            ->when($filters['status'] ?? null, fn ($builder, $status) => $builder->where('dberp_purchase_orders.status', $status))
            ->when(($filters['supplier_id'] ?? 0) > 0, fn ($builder) => $builder->where('dberp_purchase_orders.supplier_id', (int) $filters['supplier_id']))
            ->when(($filters['handler_employee_id'] ?? 0) > 0, fn ($builder) => $builder->where('dberp_purchase_orders.handler_employee_id', (int) $filters['handler_employee_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))
            ->when(($filters['category_id'] ?? 0) > 0, fn ($builder) => $builder->join('dberp_products', 'dberp_products.id', '=', 'dberp_purchase_order_items.product_id')->where('dberp_products.category_id', (int) $filters['category_id']))
            ->when(($filters['product_id'] ?? 0) > 0, fn ($builder) => $builder->where('dberp_purchase_order_items.product_id', (int) $filters['product_id']))
            ->when(($filters['product_sku_id'] ?? 0) > 0, fn ($builder) => $builder->where('dberp_purchase_order_items.product_sku_id', (int) $filters['product_sku_id']));
    }

    private function applyPurchaseOrderFiltersToJoinedPurchaseOrdersAlias(QueryBuilder $query, string $purchaseOrdersAlias, array $filters): QueryBuilder
    {
        $t = $purchaseOrdersAlias;

        return $query
            ->when($filters['status'] ?? null, fn (QueryBuilder $builder, mixed $status): QueryBuilder => $builder->where($t.'.status', (string) $status))
            ->when(($filters['supplier_id'] ?? 0) > 0, fn (QueryBuilder $builder): QueryBuilder => $builder->where($t.'.supplier_id', (int) $filters['supplier_id']))
            ->when(($filters['handler_employee_id'] ?? 0) > 0, fn (QueryBuilder $builder): QueryBuilder => $builder->where($t.'.handler_employee_id', (int) $filters['handler_employee_id']))
            ->when($filters['business_time_from'] ?? null, fn (QueryBuilder $builder, mixed $date): QueryBuilder => $builder->whereDate($t.'.business_time', '>=', $date))
            ->when($filters['business_time_until'] ?? null, fn (QueryBuilder $builder, mixed $date): QueryBuilder => $builder->whereDate($t.'.business_time', '<=', $date))
            ->when(($filters['product_id'] ?? 0) > 0, function (QueryBuilder $builder) use ($t, $filters): QueryBuilder {
                $productId = (int) $filters['product_id'];

                return $builder->whereExists(function (QueryBuilder $sub) use ($t, $productId): void {
                    $sub->selectRaw('1')
                        ->from('dberp_purchase_order_items')
                        ->whereColumn('dberp_purchase_order_items.order_id', $t.'.id')
                        ->where('dberp_purchase_order_items.product_id', $productId);
                });
            })
            ->when(($filters['product_sku_id'] ?? 0) > 0, function (QueryBuilder $builder) use ($t, $filters): QueryBuilder {
                $productSkuId = (int) $filters['product_sku_id'];

                return $builder->whereExists(function (QueryBuilder $sub) use ($t, $productSkuId): void {
                    $sub->selectRaw('1')
                        ->from('dberp_purchase_order_items')
                        ->whereColumn('dberp_purchase_order_items.order_id', $t.'.id')
                        ->where('dberp_purchase_order_items.product_sku_id', $productSkuId);
                });
            })
            ->when(($filters['category_id'] ?? 0) > 0, function (QueryBuilder $builder) use ($t, $filters): QueryBuilder {
                $categoryId = (int) $filters['category_id'];

                return $builder->whereExists(function (QueryBuilder $sub) use ($t, $categoryId): void {
                    $sub->selectRaw('1')
                        ->from('dberp_purchase_order_items')
                        ->join('dberp_products', 'dberp_products.id', '=', 'dberp_purchase_order_items.product_id')
                        ->whereColumn('dberp_purchase_order_items.order_id', $t.'.id')
                        ->where('dberp_products.category_id', $categoryId);
                });
            });
    }
}
