<?php

declare(strict_types=1);

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

namespace App\Business\Product;

use App\Business\Product\Enums\ProductSalesActivity;
use App\Business\Product\Enums\ProductSalesChange;
use App\Business\Report\QueryCenterService;
use App\Models\DberpProduct;
use App\Support\DberpNumberFormatter;
use App\Tools\Entity\EntityReference;
use App\Tools\Entity\EntityType;
use App\Tools\Exceptions\ToolInvalidInputException;
use App\Tools\Support\ToolDateRange;
use App\Tools\Support\ToolJoinMetricFilter;
use App\Tools\Support\ToolPagedQuery;
use App\Tools\Support\ToolPeriodComparison;
use Illuminate\Database\Eloquent\Builder;

/**
 * 商品多条件经营检索。指标复用 QueryCenter 商品经营查询，阈值与对比字段白名单受限。
 */
final class ProductPerformanceSearchQuery
{
    public const SORTABLE = [
        'id',
        'code',
        'name',
        'sales_amount',
        'sales_quantity',
        'purchase_amount',
        'purchase_quantity',
        'current_stock_quantity',
        'available_stock_quantity',
        'previous_sales_amount',
        'previous_sales_quantity',
    ];

    public const DEFAULT_SORT = 'sales_amount';

    private const SALES_QUANTITY = 'COALESCE(po_product_sales.sales_quantity, 0)';

    private const SALES_AMOUNT = 'COALESCE(po_product_sales.sales_amount, 0)';

    private const PURCHASE_QUANTITY = 'COALESCE(po_product_purchase.purchase_quantity, 0)';

    private const PURCHASE_AMOUNT = 'COALESCE(po_product_purchase.purchase_amount, 0)';

    private const CURRENT_STOCK = 'COALESCE(po_product_stock.current_stock_quantity, 0)';

    private const AVAILABLE_STOCK = 'COALESCE(po_product_stock.available_stock_quantity, 0)';

    private const PREVIOUS_SALES_QUANTITY = 'COALESCE(po_product_sales_prev.sales_quantity, 0)';

    private const PREVIOUS_SALES_AMOUNT = 'COALESCE(po_product_sales_prev.sales_amount, 0)';

    public function __construct(
        private readonly QueryCenterService $queryCenterService,
    ) {}

    /**
     * @param  array<string, mixed>  $input
     * @param  array{code: string, meaning: string}  $currency
     * @return array<string, mixed>
     */
    public function search(array $input, array $currency): array
    {
        $dates = ToolDateRange::parseOptional(
            $input['date_from'] ?? null,
            $input['date_until'] ?? null,
            'date_from',
            'date_until',
        );
        $comparison = $this->comparisonWindow($dates);
        $salesChange = ProductSalesChange::tryFrom(trim((string) ($input['sales_change'] ?? '')));

        if ($salesChange instanceof ProductSalesChange && $comparison === null) {
            throw ToolInvalidInputException::forField('sales_change', 'sales_change requires date_from and date_until.');
        }

        $sortBy = (string) ($input['sort_by'] ?? self::DEFAULT_SORT);
        if (in_array($sortBy, ['previous_sales_amount', 'previous_sales_quantity'], true) && $comparison === null) {
            throw ToolInvalidInputException::forField('sort_by', 'Previous-period sort requires date_from and date_until.');
        }

        $filters = array_filter([
            'customer_id' => (($input['customer_id'] ?? 0) > 0) ? (int) $input['customer_id'] : null,
            'supplier_id' => (($input['supplier_id'] ?? 0) > 0) ? (int) $input['supplier_id'] : null,
            'category_id' => (($input['category_id'] ?? 0) > 0) ? (int) $input['category_id'] : null,
            'product_id' => (($input['product_id'] ?? 0) > 0) ? (int) $input['product_id'] : null,
            'product_sku_id' => (($input['product_sku_id'] ?? 0) > 0) ? (int) $input['product_sku_id'] : null,
            'warehouse_id' => (($input['warehouse_id'] ?? 0) > 0) ? (int) $input['warehouse_id'] : null,
            'business_time_from' => $dates['from'],
            'business_time_until' => $dates['until'],
            'compare_business_time_from' => $comparison['previous_from'] ?? null,
            'compare_business_time_until' => $comparison['previous_until'] ?? null,
        ], static fn ($value): bool => $value !== null && $value !== '' && $value !== 0);

        $query = $this->queryCenterService->productBusinessQuery($filters);
        $table = (new DberpProduct)->getTable();

        $keyword = trim((string) ($input['q'] ?? ''));
        if ($keyword !== '') {
            $like = '%'.$keyword.'%';
            $query->where(function (Builder $builder) use ($table, $like): void {
                $builder
                    ->where($table.'.code', 'like', $like)
                    ->orWhere($table.'.name', 'like', $like);
            });
        }

        if (array_key_exists('is_enabled', $input)) {
            $query->where($table.'.is_enabled', (bool) $input['is_enabled']);
        }

        ToolJoinMetricFilter::stabilizePrimaryKey($query);

        $this->applyRange($query, self::SALES_QUANTITY, $input, 'sales_quantity_min', 'sales_quantity_max');
        $this->applyRange($query, self::SALES_AMOUNT, $input, 'sales_amount_min', 'sales_amount_max');
        $this->applyRange($query, self::PURCHASE_QUANTITY, $input, 'purchase_quantity_min', 'purchase_quantity_max');
        $this->applyRange($query, self::PURCHASE_AMOUNT, $input, 'purchase_amount_min', 'purchase_amount_max');
        $this->applyRange($query, self::CURRENT_STOCK, $input, 'stock_quantity_min', 'stock_quantity_max');
        $this->applyRange($query, self::AVAILABLE_STOCK, $input, 'available_quantity_min', 'available_quantity_max');

        $activity = ProductSalesActivity::tryFrom(trim((string) ($input['sales_activity'] ?? '')));
        if ($activity === ProductSalesActivity::HasSales) {
            $query->where(function (Builder $builder): void {
                ToolJoinMetricFilter::compare($builder, self::SALES_QUANTITY, '>', 0);
                $builder->orWhere(function (Builder $amount): void {
                    ToolJoinMetricFilter::compare($amount, self::SALES_AMOUNT, '>', 0);
                });
            });
        } elseif ($activity === ProductSalesActivity::NoSales) {
            ToolJoinMetricFilter::compare($query, self::SALES_QUANTITY, '=', 0);
            ToolJoinMetricFilter::compare($query, self::SALES_AMOUNT, '=', 0);
        }

        if ($salesChange instanceof ProductSalesChange && $comparison !== null) {
            $this->applySalesChange($query, $salesChange);
        }

        $page = ToolPagedQuery::paginate(
            $query,
            $input,
            self::SORTABLE,
            self::DEFAULT_SORT,
        );

        return [
            'period' => [
                'from' => $dates['from'],
                'until' => $dates['until'],
            ],
            'comparison_period' => $comparison === null ? null : [
                'from' => $comparison['previous_from'],
                'until' => $comparison['previous_until'],
            ],
            'items' => $page['rows']
                ->map(fn (mixed $row): array => $this->mapProduct($row, $comparison !== null))
                ->values()
                ->all(),
            ...ToolPagedQuery::meta($page),
            'currency' => $currency,
        ];
    }

    /**
     * @param  array{from: ?string, until: ?string}  $dates
     * @return array{from: string, until: string, previous_from: string, previous_until: string, span_days: int}|null
     */
    private function comparisonWindow(array $dates): ?array
    {
        if ($dates['from'] === null || $dates['until'] === null) {
            return null;
        }

        return ToolPeriodComparison::equalPrecedingWindow(
            $dates['from'],
            $dates['until'],
            'date_from',
            'date_until',
        );
    }

    /**
     * @param  array<string, mixed>  $input
     */
    private function applyRange(Builder $query, string $expression, array $input, string $minField, string $maxField): void
    {
        ToolJoinMetricFilter::applyNumericRange($query, $expression, $input, $minField, $maxField);
    }

    private function applySalesChange(Builder $query, ProductSalesChange $change): void
    {
        $currentAmount = $this->metricCast(self::SALES_AMOUNT);
        $currentQuantity = $this->metricCast(self::SALES_QUANTITY);
        $previousAmount = $this->metricCast(self::PREVIOUS_SALES_AMOUNT);
        $previousQuantity = $this->metricCast(self::PREVIOUS_SALES_QUANTITY);

        match ($change) {
            ProductSalesChange::New => $query
                ->whereRaw($previousAmount.' = 0')
                ->whereRaw($previousQuantity.' = 0')
                ->where(function (Builder $activity) use ($currentAmount, $currentQuantity): void {
                    $activity
                        ->whereRaw($currentAmount.' > 0')
                        ->orWhereRaw($currentQuantity.' > 0');
                }),
            ProductSalesChange::Flat => $query
                ->whereRaw($currentAmount.' = '.$previousAmount)
                ->whereRaw($currentQuantity.' = '.$previousQuantity),
            ProductSalesChange::Down => $query->where(function (Builder $builder) use ($currentAmount, $currentQuantity, $previousAmount, $previousQuantity): void {
                $builder
                    ->whereRaw($currentAmount.' < '.$previousAmount)
                    ->orWhere(function (Builder $tied) use ($currentAmount, $currentQuantity, $previousAmount, $previousQuantity): void {
                        $tied
                            ->whereRaw($currentAmount.' = '.$previousAmount)
                            ->whereRaw($currentQuantity.' < '.$previousQuantity);
                    });
            }),
            ProductSalesChange::Up => $query->where(function (Builder $builder) use ($currentAmount, $currentQuantity, $previousAmount, $previousQuantity): void {
                $builder
                    ->whereRaw($currentAmount.' > '.$previousAmount)
                    ->orWhere(function (Builder $tied) use ($currentAmount, $currentQuantity, $previousAmount, $previousQuantity): void {
                        $tied
                            ->whereRaw($currentAmount.' = '.$previousAmount)
                            ->whereRaw($currentQuantity.' > '.$previousQuantity);
                    });
            }),
        };
    }

    private function metricCast(string $expression): string
    {
        return 'CAST(('.$expression.') AS DECIMAL(18,4))';
    }

    /**
     * @return array<string, mixed>
     */
    private function mapProduct(mixed $row, bool $hasComparison): array
    {
        if (! $row instanceof DberpProduct) {
            return [];
        }

        $salesAmount = (float) $row->getAttribute('sales_amount');
        $salesQuantity = (float) $row->getAttribute('sales_quantity');
        $previousAmount = (float) $row->getAttribute('previous_sales_amount');
        $previousQuantity = (float) $row->getAttribute('previous_sales_quantity');

        return EntityReference::attach([
            'id' => (int) $row->getKey(),
            'code' => (string) $row->code,
            'name' => (string) $row->name,
            'is_enabled' => (bool) $row->is_enabled,
            'category_id' => $row->category_id === null ? null : (int) $row->category_id,
            'category_name' => $this->nullableString($row->category?->name),
            'sales_quantity' => DberpNumberFormatter::quantity($salesQuantity),
            'sales_amount' => DberpNumberFormatter::money($salesAmount),
            'purchase_quantity' => DberpNumberFormatter::quantity($row->getAttribute('purchase_quantity')),
            'purchase_amount' => DberpNumberFormatter::money($row->getAttribute('purchase_amount')),
            'current_stock_quantity' => DberpNumberFormatter::quantity($row->getAttribute('current_stock_quantity')),
            'available_stock_quantity' => DberpNumberFormatter::quantity($row->getAttribute('available_stock_quantity')),
            'sales_activity' => ($salesQuantity > 0 || $salesAmount > 0)
                ? ProductSalesActivity::HasSales->value
                : ProductSalesActivity::NoSales->value,
            'previous_sales_quantity' => $hasComparison ? DberpNumberFormatter::quantity($previousQuantity) : null,
            'previous_sales_amount' => $hasComparison ? DberpNumberFormatter::money($previousAmount) : null,
            'sales_change' => $hasComparison ? $this->salesChange($salesAmount, $salesQuantity, $previousAmount, $previousQuantity) : null,
        ], EntityType::Product, (int) $row->getKey(), (string) $row->code, (string) $row->name);
    }

    private function salesChange(float $currentAmount, float $currentQuantity, float $previousAmount, float $previousQuantity): string
    {
        if ($previousAmount == 0.0 && $previousQuantity == 0.0 && ($currentAmount > 0 || $currentQuantity > 0)) {
            return ProductSalesChange::New->value;
        }

        if ($currentAmount == $previousAmount && $currentQuantity == $previousQuantity) {
            return ProductSalesChange::Flat->value;
        }

        if ($currentAmount < $previousAmount || ($currentAmount == $previousAmount && $currentQuantity < $previousQuantity)) {
            return ProductSalesChange::Down->value;
        }

        return ProductSalesChange::Up->value;
    }

    private function nullableString(mixed $value): ?string
    {
        if ($value === null) {
            return null;
        }

        $normalized = trim((string) $value);

        return $normalized === '' ? null : $normalized;
    }
}
