<?php

declare(strict_types=1);

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

namespace App\Business\Settlement;

use App\Business\Settlement\Enums\SettlementRecordStatus;
use App\Models\DberpCustomer;
use App\Models\DberpPayableRecord;
use App\Models\DberpReceivableRecord;
use App\Models\DberpSupplier;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;

/**
 * 账龄分桶。聚合在数据库完成，禁止把全部未结记录加载进 PHP。
 */
final class SettlementAgingAnalysisService
{
    public const MAX_DETAIL_ROWS = 100;

    /**
     * @param  array<string, mixed>  $filters
     * @param  array{page?: int, per_page?: int}|null  $pagination  null 时返回全部往来（Filament）；Tool 必须传入分页
     * @return array{rows: list<array<string, mixed>>, totals: array<string, float>, total_counterparties: int, page: int, per_page: int}
     */
    public function analyze(array $filters, ?array $pagination = null): array
    {
        $normalized = $this->normalizeFilters($filters);
        $settlementSide = $normalized['settlement_side'];
        $asOfDate = $normalized['as_of_date'];
        $counterpartyId = $normalized['counterparty_id'];

        $totals = $this->aggregateTotals($settlementSide, $asOfDate, $counterpartyId);
        $totalCounterparties = $this->countCounterparties($settlementSide, $counterpartyId);

        $page = 1;
        $perPage = $totalCounterparties > 0 ? $totalCounterparties : 1;

        if ($pagination !== null) {
            $page = max(1, (int) ($pagination['page'] ?? 1));
            $perPage = min(self::MAX_DETAIL_ROWS, max(1, (int) ($pagination['per_page'] ?? 20)));
        }

        $rows = $this->aggregateCounterparties(
            $settlementSide,
            $asOfDate,
            $counterpartyId,
            $page,
            $perPage,
        );

        return [
            'rows' => $rows,
            'totals' => $totals,
            'total_counterparties' => $totalCounterparties,
            'page' => $page,
            'per_page' => $pagination === null ? max($totalCounterparties, 1) : $perPage,
        ];
    }

    /**
     * @param  array<string, mixed>  $formState
     * @return array{settlement_side: string, as_of_date: string, counterparty_id: int}
     */
    public function normalizeFilters(array $formState): array
    {
        $settlementSide = (string) ($formState['settlement_side'] ?? 'receivable');
        if (! in_array($settlementSide, ['receivable', 'payable'], true)) {
            $settlementSide = 'receivable';
        }

        $asOfDate = (string) ($formState['as_of_date'] ?? now()->toDateString());
        if ($asOfDate === '') {
            $asOfDate = now()->toDateString();
        }

        return [
            'settlement_side' => $settlementSide,
            'as_of_date' => $asOfDate,
            'counterparty_id' => max(0, (int) ($formState['counterparty_id'] ?? 0)),
        ];
    }

    /**
     * @return array<string, float>
     */
    private function aggregateTotals(string $settlementSide, string $asOfDate, int $counterpartyId): array
    {
        $query = $this->openRecordsQuery($settlementSide, $counterpartyId);
        $table = $query->getModel()->getTable();
        $select = $this->bucketSelect($table, $asOfDate);
        $base = $query->toBase();
        $base->columns = null;

        $row = $base->selectRaw($select['sql'], $select['bindings'])->first();

        return $this->emptyTotals($row);
    }

    /**
     * @return list<array<string, mixed>>
     */
    private function aggregateCounterparties(
        string $settlementSide,
        string $asOfDate,
        int $counterpartyId,
        int $page,
        int $perPage,
    ): array {
        $query = $this->openRecordsQuery($settlementSide, $counterpartyId);
        $table = $query->getModel()->getTable();
        $counterpartyColumn = $settlementSide === 'payable' ? 'supplier_id' : 'customer_id';
        $partyTable = $settlementSide === 'payable'
            ? (new DberpSupplier)->getTable()
            : (new DberpCustomer)->getTable();
        $select = $this->bucketSelect($table, $asOfDate);

        $base = $query->toBase();
        $base->columns = null;
        $base->leftJoin($partyTable, $partyTable.'.id', '=', $table.'.'.$counterpartyColumn);
        $base->selectRaw($table.'.'.$counterpartyColumn.' as counterparty_id');
        $base->selectRaw('MAX('.$partyTable.'.name) as counterparty_name');
        $base->selectRaw($select['sql'], $select['bindings']);
        $base->groupBy($table.'.'.$counterpartyColumn);
        $base->orderByDesc('total_amount');
        $base->orderBy($table.'.'.$counterpartyColumn);
        $base->forPage($page, $perPage);

        $rows = [];

        foreach ($base->get() as $row) {
            $mapped = $this->emptyTotals($row);
            $rows[] = [
                'counterparty_id' => (int) ($row->counterparty_id ?? 0),
                'counterparty_name' => (string) ($row->counterparty_name ?? ''),
                'record_count' => (int) ($row->record_count ?? 0),
                ...$mapped,
            ];
        }

        return $rows;
    }

    private function countCounterparties(string $settlementSide, int $counterpartyId): int
    {
        $query = $this->openRecordsQuery($settlementSide, $counterpartyId);
        $table = $query->getModel()->getTable();
        $counterpartyColumn = $settlementSide === 'payable' ? 'supplier_id' : 'customer_id';

        $base = $query->toBase();
        $base->columns = null;

        return (int) $base
            ->selectRaw('COUNT(DISTINCT '.$table.'.'.$counterpartyColumn.') as aggregate')
            ->value('aggregate');
    }

    /**
     * @return Builder<DberpPayableRecord>|Builder<DberpReceivableRecord>
     */
    private function openRecordsQuery(string $settlementSide, int $counterpartyId): Builder
    {
        if ($settlementSide === 'payable') {
            return DberpPayableRecord::query()
                ->where('remaining_amount', '>', 0)
                ->whereNot('record_status', SettlementRecordStatus::Voided->value)
                ->when($counterpartyId > 0, fn (Builder $query) => $query->where('supplier_id', $counterpartyId));
        }

        return DberpReceivableRecord::query()
            ->where('remaining_amount', '>', 0)
            ->whereNot('record_status', SettlementRecordStatus::Voided->value)
            ->when($counterpartyId > 0, fn (Builder $query) => $query->where('customer_id', $counterpartyId));
    }

    /**
     * @return array{sql: string, bindings: list<string>}
     */
    private function bucketSelect(string $table, string $asOfDate): array
    {
        $due = 'COALESCE('.$table.'.due_date, DATE('.$table.'.business_date))';
        $days = $this->overdueDaysSql($due);
        $amount = $table.'.remaining_amount';

        $sql = implode(', ', [
            'SUM(CASE WHEN '.$due.' >= ? THEN '.$amount.' ELSE 0 END) as current_amount',
            'SUM(CASE WHEN '.$days.' BETWEEN 1 AND 30 THEN '.$amount.' ELSE 0 END) as bucket_1_30_amount',
            'SUM(CASE WHEN '.$days.' BETWEEN 31 AND 60 THEN '.$amount.' ELSE 0 END) as bucket_31_60_amount',
            'SUM(CASE WHEN '.$days.' BETWEEN 61 AND 90 THEN '.$amount.' ELSE 0 END) as bucket_61_90_amount',
            'SUM(CASE WHEN '.$days.' >= 91 THEN '.$amount.' ELSE 0 END) as bucket_91_plus_amount',
            'SUM('.$amount.') as total_amount',
            'COUNT('.$table.'.id) as record_count',
        ]);

        return [
            'sql' => $sql,
            'bindings' => [$asOfDate, $asOfDate, $asOfDate, $asOfDate, $asOfDate],
        ];
    }

    private function overdueDaysSql(string $dueExpression): string
    {
        $driver = DB::connection()->getDriverName();

        if ($driver === 'sqlite') {
            return 'CAST(julianday(?) - julianday('.$dueExpression.') AS INTEGER)';
        }

        return 'DATEDIFF(?, '.$dueExpression.')';
    }

    /**
     * @return array<string, float>
     */
    private function emptyTotals(mixed $row): array
    {
        $source = is_object($row) ? $row : (object) [];

        return [
            'current_amount' => $this->toFloat($source->current_amount ?? 0),
            'bucket_1_30_amount' => $this->toFloat($source->bucket_1_30_amount ?? 0),
            'bucket_31_60_amount' => $this->toFloat($source->bucket_31_60_amount ?? 0),
            'bucket_61_90_amount' => $this->toFloat($source->bucket_61_90_amount ?? 0),
            'bucket_91_plus_amount' => $this->toFloat($source->bucket_91_plus_amount ?? 0),
            'total_amount' => $this->toFloat($source->total_amount ?? 0),
        ];
    }

    private function toFloat(mixed $value): float
    {
        if (is_numeric($value)) {
            return (float) $value;
        }

        return 0.0;
    }
}
