<?php

declare(strict_types=1);

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

namespace App\Business\MasterData;

use App\Business\Purchase\Enums\PurchaseOrderReceiptStatus;
use App\Business\Purchase\Enums\PurchaseOrderStatus;
use App\Business\Settlement\Enums\SettlementOverdueStatus;
use App\Business\Settlement\SettlementDueConstraint;
use App\Models\DberpPayableRecord;
use App\Models\DberpPurchaseOrder;
use App\Models\DberpSupplier;
use App\Support\DberpNumberFormatter;
use App\Tools\Entity\EntityReference;
use App\Tools\Entity\EntityType;
use App\Tools\Support\ToolDateRange;
use App\Tools\Support\ToolJoinMetricFilter;
use App\Tools\Support\ToolPagedQuery;
use Illuminate\Database\Query\Builder as QueryBuilder;

/**
 * 供应商跨域暴露检索。采购口径与 purchase.summary 订单合计相同，未结/逾期与 payable.search 相同，未完成订单与 receipt_status=open 相同。
 */
final class SupplierExposureSearchQuery
{
    public const SORTABLE = [
        'id',
        'code',
        'name',
        'purchase_amount',
        'purchase_order_count',
        'outstanding_amount',
        'overdue_amount',
        'open_order_count',
        'open_order_amount',
    ];

    public const DEFAULT_SORT = 'outstanding_amount';

    /**
     * @param  array<string, mixed>  $input
     * @param  array{code: string, meaning: string}  $currency
     * @return array<string, mixed>
     */
    public function search(array $input, string $asOfDate, array $currency): array
    {
        $dates = ToolDateRange::parseOptional(
            $input['date_from'] ?? null,
            $input['date_until'] ?? null,
            'date_from',
            'date_until',
        );

        $table = (new DberpSupplier)->getTable();
        $query = DberpSupplier::query()
            ->leftJoinSub($this->purchaseSubquery($dates), 'se_purchase', 'se_purchase.supplier_id', '=', $table.'.id')
            ->leftJoinSub($this->payableOpenSubquery(), 'se_open_ap', 'se_open_ap.supplier_id', '=', $table.'.id')
            ->leftJoinSub($this->payableOverdueSubquery($asOfDate), 'se_overdue_ap', 'se_overdue_ap.supplier_id', '=', $table.'.id')
            ->leftJoinSub($this->openOrdersSubquery(), 'se_open_po', 'se_open_po.supplier_id', '=', $table.'.id')
            ->select($table.'.*')
            ->selectRaw('COALESCE(se_purchase.order_count, 0) as purchase_order_count')
            ->selectRaw('COALESCE(se_purchase.total_amount, 0) as purchase_amount')
            ->selectRaw('COALESCE(se_purchase.total_quantity, 0) as purchase_quantity')
            ->selectRaw('COALESCE(se_open_ap.record_count, 0) as outstanding_count')
            ->selectRaw('COALESCE(se_open_ap.amount, 0) as outstanding_amount')
            ->selectRaw('COALESCE(se_overdue_ap.record_count, 0) as overdue_count')
            ->selectRaw('COALESCE(se_overdue_ap.amount, 0) as overdue_amount')
            ->selectRaw('COALESCE(se_open_po.order_count, 0) as open_order_count')
            ->selectRaw('COALESCE(se_open_po.amount, 0) as open_order_amount');

        PartnerExposureSearchSupport::applyKeyword($query, $input, $table);
        PartnerExposureSearchSupport::applyEnabled($query, $input, $table);

        if (($input['supplier_id'] ?? 0) > 0) {
            $query->whereKey((int) $input['supplier_id']);
        }

        ToolJoinMetricFilter::stabilizePrimaryKey($query);

        PartnerExposureSearchSupport::applyNumericRange($query, 'COALESCE(se_purchase.total_amount, 0)', $input, 'purchase_amount_min', 'purchase_amount_max');
        PartnerExposureSearchSupport::applyNumericRange($query, 'COALESCE(se_purchase.order_count, 0)', $input, 'purchase_order_count_min', 'purchase_order_count_max');
        PartnerExposureSearchSupport::applyNumericRange($query, 'COALESCE(se_open_ap.amount, 0)', $input, 'outstanding_amount_min', 'outstanding_amount_max');
        PartnerExposureSearchSupport::applyNumericRange($query, 'COALESCE(se_overdue_ap.amount, 0)', $input, 'overdue_amount_min', 'overdue_amount_max');
        PartnerExposureSearchSupport::applyNumericRange($query, 'COALESCE(se_open_po.order_count, 0)', $input, 'open_order_count_min', 'open_order_count_max');
        PartnerExposureSearchSupport::applyNumericRange($query, 'COALESCE(se_open_po.amount, 0)', $input, 'open_order_amount_min', 'open_order_amount_max');

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

        return [
            'as_of_date' => $asOfDate,
            'period' => [
                'from' => $dates['from'],
                'until' => $dates['until'],
            ],
            'items' => $page['rows']
                ->map(fn (mixed $row): array => $this->mapSupplier($row))
                ->values()
                ->all(),
            ...ToolPagedQuery::meta($page),
            'currency' => $currency,
        ];
    }

    /**
     * @param  array{from: ?string, until: ?string}  $dates
     */
    private function purchaseSubquery(array $dates): QueryBuilder
    {
        return DberpPurchaseOrder::query()
            ->whereIn('status', PurchaseOrderStatus::statisticsScopeValues())
            ->when($dates['from'] ?? null, fn ($builder, $date) => $builder->whereDate('business_time', '>=', $date))
            ->when($dates['until'] ?? null, fn ($builder, $date) => $builder->whereDate('business_time', '<=', $date))
            ->select('supplier_id')
            ->selectRaw('COUNT(*) as order_count')
            ->selectRaw('COALESCE(SUM(total_amount), 0) as total_amount')
            ->selectRaw('COALESCE(SUM(total_quantity), 0) as total_quantity')
            ->groupBy('supplier_id')
            ->toBase();
    }

    private function payableOpenSubquery(): QueryBuilder
    {
        $query = DberpPayableRecord::query();
        SettlementDueConstraint::applyOpen($query);

        return $query
            ->select('supplier_id')
            ->selectRaw('COUNT(*) as record_count')
            ->selectRaw('COALESCE(SUM(remaining_amount), 0) as amount')
            ->groupBy('supplier_id')
            ->toBase();
    }

    private function payableOverdueSubquery(string $asOfDate): QueryBuilder
    {
        $query = DberpPayableRecord::query();
        SettlementDueConstraint::apply($query, SettlementOverdueStatus::Overdue, $asOfDate);

        return $query
            ->select('supplier_id')
            ->selectRaw('COUNT(*) as record_count')
            ->selectRaw('COALESCE(SUM(remaining_amount), 0) as amount')
            ->groupBy('supplier_id')
            ->toBase();
    }

    private function openOrdersSubquery(): QueryBuilder
    {
        return DberpPurchaseOrder::query()
            ->whereIn('status', PurchaseOrderReceiptStatus::Open->statusValues())
            ->select('supplier_id')
            ->selectRaw('COUNT(*) as order_count')
            ->selectRaw('COALESCE(SUM(total_amount), 0) as amount')
            ->groupBy('supplier_id')
            ->toBase();
    }

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

        return EntityReference::attach([
            'id' => (int) $row->getKey(),
            'code' => (string) $row->code,
            'name' => (string) $row->name,
            'is_enabled' => (bool) $row->is_enabled,
            'purchases' => [
                'order_count' => (int) $row->getAttribute('purchase_order_count'),
                'total_amount' => DberpNumberFormatter::money($row->getAttribute('purchase_amount')),
                'total_quantity' => DberpNumberFormatter::quantity($row->getAttribute('purchase_quantity')),
            ],
            'outstanding_payables' => [
                'count' => (int) $row->getAttribute('outstanding_count'),
                'amount' => DberpNumberFormatter::money($row->getAttribute('outstanding_amount')),
            ],
            'overdue_payables' => [
                'count' => (int) $row->getAttribute('overdue_count'),
                'amount' => DberpNumberFormatter::money($row->getAttribute('overdue_amount')),
            ],
            'open_orders' => [
                'count' => (int) $row->getAttribute('open_order_count'),
                'amount' => DberpNumberFormatter::money($row->getAttribute('open_order_amount')),
            ],
        ], EntityType::Supplier, (int) $row->getKey(), (string) $row->code, (string) $row->name);
    }
}
