<?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\Sales\Enums\SalesOrderFulfillmentStatus;
use App\Business\Sales\Enums\SalesOrderStatus;
use App\Business\Settlement\Enums\SettlementOverdueStatus;
use App\Business\Settlement\SettlementDueConstraint;
use App\Models\DberpCustomer;
use App\Models\DberpReceivableRecord;
use App\Models\DberpSalesOrder;
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;

/**
 * 客户跨域暴露检索。销售口径与 sales.summary 相同，未结/逾期与 receivable.search 相同，未完成订单与 fulfillment_status=open 相同。
 */
final class CustomerExposureSearchQuery
{
    public const SORTABLE = [
        'id',
        'code',
        'name',
        'sales_amount',
        'sales_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 DberpCustomer)->getTable();
        $query = DberpCustomer::query()
            ->leftJoinSub($this->salesSubquery($dates), 'ce_sales', 'ce_sales.customer_id', '=', $table.'.id')
            ->leftJoinSub($this->receivableOpenSubquery(), 'ce_open_ar', 'ce_open_ar.customer_id', '=', $table.'.id')
            ->leftJoinSub($this->receivableOverdueSubquery($asOfDate), 'ce_overdue_ar', 'ce_overdue_ar.customer_id', '=', $table.'.id')
            ->leftJoinSub($this->openOrdersSubquery(), 'ce_open_so', 'ce_open_so.customer_id', '=', $table.'.id')
            ->select($table.'.*')
            ->selectRaw('COALESCE(ce_sales.order_count, 0) as sales_order_count')
            ->selectRaw('COALESCE(ce_sales.final_amount, 0) as sales_amount')
            ->selectRaw('COALESCE(ce_sales.total_quantity, 0) as sales_quantity')
            ->selectRaw('COALESCE(ce_open_ar.record_count, 0) as outstanding_count')
            ->selectRaw('COALESCE(ce_open_ar.amount, 0) as outstanding_amount')
            ->selectRaw('COALESCE(ce_overdue_ar.record_count, 0) as overdue_count')
            ->selectRaw('COALESCE(ce_overdue_ar.amount, 0) as overdue_amount')
            ->selectRaw('COALESCE(ce_open_so.order_count, 0) as open_order_count')
            ->selectRaw('COALESCE(ce_open_so.amount, 0) as open_order_amount');

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

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

        ToolJoinMetricFilter::stabilizePrimaryKey($query);

        PartnerExposureSearchSupport::applyNumericRange($query, 'COALESCE(ce_sales.final_amount, 0)', $input, 'sales_amount_min', 'sales_amount_max');
        PartnerExposureSearchSupport::applyNumericRange($query, 'COALESCE(ce_sales.order_count, 0)', $input, 'sales_order_count_min', 'sales_order_count_max');
        PartnerExposureSearchSupport::applyNumericRange($query, 'COALESCE(ce_open_ar.amount, 0)', $input, 'outstanding_amount_min', 'outstanding_amount_max');
        PartnerExposureSearchSupport::applyNumericRange($query, 'COALESCE(ce_overdue_ar.amount, 0)', $input, 'overdue_amount_min', 'overdue_amount_max');
        PartnerExposureSearchSupport::applyNumericRange($query, 'COALESCE(ce_open_so.order_count, 0)', $input, 'open_order_count_min', 'open_order_count_max');
        PartnerExposureSearchSupport::applyNumericRange($query, 'COALESCE(ce_open_so.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->mapCustomer($row))
                ->values()
                ->all(),
            ...ToolPagedQuery::meta($page),
            'currency' => $currency,
        ];
    }

    /**
     * @param  array{from: ?string, until: ?string}  $dates
     */
    private function salesSubquery(array $dates): QueryBuilder
    {
        return DberpSalesOrder::query()
            ->whereIn('status', SalesOrderStatus::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('customer_id')
            ->selectRaw('COUNT(*) as order_count')
            ->selectRaw('COALESCE(SUM(final_amount), 0) as final_amount')
            ->selectRaw('COALESCE(SUM(total_quantity), 0) as total_quantity')
            ->groupBy('customer_id')
            ->toBase();
    }

    private function receivableOpenSubquery(): QueryBuilder
    {
        $query = DberpReceivableRecord::query();
        SettlementDueConstraint::applyOpen($query);

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

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

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

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

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

        return EntityReference::attach([
            'id' => (int) $row->getKey(),
            'code' => (string) $row->code,
            'name' => (string) $row->name,
            'is_enabled' => (bool) $row->is_enabled,
            'sales' => [
                'order_count' => (int) $row->getAttribute('sales_order_count'),
                'final_amount' => DberpNumberFormatter::money($row->getAttribute('sales_amount')),
                'total_quantity' => DberpNumberFormatter::quantity($row->getAttribute('sales_quantity')),
            ],
            'outstanding_receivables' => [
                'count' => (int) $row->getAttribute('outstanding_count'),
                'amount' => DberpNumberFormatter::money($row->getAttribute('outstanding_amount')),
            ],
            'overdue_receivables' => [
                '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::Customer, (int) $row->getKey(), (string) $row->code, (string) $row->name);
    }
}
