<?php

namespace Modules\EcommerceFinance\Repositories;

use Illuminate\Database\ConnectionInterface;
use Illuminate\Database\Query\Builder;
use Illuminate\Support\Carbon;
use Modules\EcommerceFinance\Services\BusinessMirrorDatasetRegistry;
use Throwable;

/** 只读聚水潭协同工单镜像；HTTP 请求不得回退直连上游。 */
class WorkOrderMirrorRepository
{
    public const DEFAULT_WORK_TYPE_NAME = '仓内未发';

    public const MAIN_DATASET = 'operations_replica.rpt.work_order_main';
    public const SUB_DATASET = 'operations_replica.rpt.work_order_sub';
    public const CONFIG_DATASET = 'operations_replica.rpt.work_order_form_config';
    public const VALUE_DATASET = 'operations_replica.rpt.work_order_form_value';

    private const DATASETS = [
        'main' => self::MAIN_DATASET,
        'sub' => self::SUB_DATASET,
        'config' => self::CONFIG_DATASET,
        'values' => self::VALUE_DATASET,
    ];

    private readonly ConnectionInterface $connection;

    public function __construct(
        private readonly BusinessMirrorDatasetRegistry $datasets,
        private readonly GenericBusinessMirrorRepository $mirror,
    ) {
        $this->connection = $mirror->connection();
    }

    /**
     * @return array{
     *   ready:bool,
     *   code:string,
     *   last_succeeded_at:?string,
     *   datasets:array<string,array{dataset:string,ready:bool,status:string,last_succeeded_at:?string}>
     * }
     */
    public function syncStatus(): array
    {
        $ready = true;
        $lastSucceeded = [];
        $states = [];

        foreach (self::DATASETS as $name => $key) {
            try {
                $dataset = $this->datasets->get($key);
                $state = $this->mirror->state($dataset);
                $datasetReady = $this->mirror->ready($dataset);
            } catch (Throwable) {
                $state = null;
                $datasetReady = false;
            }

            $last = $this->dateValue($state['last_succeeded_at'] ?? null);
            if ($last !== null) {
                $lastSucceeded[] = $last;
            }

            $states[$name] = [
                'dataset' => $key,
                'ready' => $datasetReady,
                'status' => (string) ($state['status'] ?? 'pending'),
                'last_succeeded_at' => $last,
            ];
            $ready = $ready && $datasetReady;
        }

        return [
            'ready' => $ready,
            'code' => $ready ? 'ready' : 'initial_sync_pending',
            // The oldest successful component is the freshness of the joined result.
            'last_succeeded_at' => $lastSucceeded === [] ? null : min($lastSucceeded),
            'datasets' => $states,
        ];
    }

    /**
     * @param array<string,string> $filters
     * @return array{data:list<array<string,mixed>>,total:int,page:int,per_page:int,last_page:int,ready:bool,sync_status:array<string,mixed>}
     */
    public function paginate(int $page, int $perPage, array $filters = []): array
    {
        $page = max($page, 1);
        $perPage = min(max($perPage, 1), 100);
        $syncStatus = $this->syncStatus();

        if (! $syncStatus['ready']) {
            return [
                'data' => [],
                'total' => 0,
                'page' => $page,
                'per_page' => $perPage,
                'last_page' => 1,
                'ready' => false,
                'sync_status' => $syncStatus,
            ];
        }

        $main = $this->datasets->get(self::MAIN_DATASET)->targetTable();
        $config = $this->datasets->get(self::CONFIG_DATASET)->targetTable();
        $latestConfigs = $this->connection->table($config . ' AS config_versions')
            ->selectRaw('work_type, MAX(id) AS id')
            ->groupBy('work_type');

        $query = $this->connection->table($main . ' AS m')
            ->joinSub($latestConfigs, 'config_versions', static function ($join): void {
                $join->on('config_versions.work_type', '=', 'm.work_type');
            })
            ->join($config . ' AS c', 'c.id', '=', 'config_versions.id')
            ->where('c.work_type_name', self::DEFAULT_WORK_TYPE_NAME)
            ->where(static function (Builder $query): void {
                $query->whereNull('m.is_deleted')->orWhere('m.is_deleted', 0);
            });

        $this->applyFilters($query, $filters);
        $total = (clone $query)->count();
        $rows = $query
            ->select([
                'm.id', 'm.work_type', 'c.work_type_name', 'c.group_name',
                'm.order_no', 'm.title', 'm.status', 'm.create_user',
                'm.expect_minute', 'm.submit_time', 'm.expect_end_time',
                'm.end_time', 'm.score', 'm.update_user', 'm.description',
                'm.create_time', 'm.update_time',
            ])
            ->orderByDesc('m.create_time')
            ->orderByDesc('m.id')
            ->forPage($page, $perPage)
            ->get()
            ->map(static fn (object $row): array => (array) $row)
            ->all();

        $this->hydrateRelatedRows($rows);

        return [
            'data' => $rows,
            'total' => $total,
            'page' => $page,
            'per_page' => $perPage,
            'last_page' => max((int) ceil($total / $perPage), 1),
            'ready' => true,
            'sync_status' => $syncStatus,
        ];
    }

    /**
     * Return after-sales alert work orders for the after-sales expense page.
     * The work-order values describe an operational alert; they are not added
     * to the order/refund monetary columns.
     *
     * @param array<string,string> $filters
     * @return array{data:list<array<string,mixed>>,total:int,page:int,per_page:int,last_page:int,ready:bool,sync_status:array<string,mixed>}
     */
    public function paginateAfterSales(int $page, int $perPage, array $filters = []): array
    {
        $page = max($page, 1);
        $perPage = min(max($perPage, 1), 50);
        $syncStatus = $this->syncStatus();

        if (! $syncStatus['ready']) {
            return [
                'data' => [],
                'total' => 0,
                'page' => $page,
                'per_page' => $perPage,
                'last_page' => 1,
                'ready' => false,
                'sync_status' => $syncStatus,
            ];
        }

        $main = $this->datasets->get(self::MAIN_DATASET)->targetTable();
        $config = $this->datasets->get(self::CONFIG_DATASET)->targetTable();
        $latestConfigs = $this->connection->table($config . ' AS config_versions')
            ->selectRaw('work_type, MAX(id) AS id')
            ->groupBy('work_type');

        // Resolve the small type catalogue first. Constant type bounds let
        // MariaDB use (work_type, create_time, id) before looking up form values;
        // deriving types only through the joins scans every type in the period.
        $afterSalesTypes = $this->connection->table($config . ' AS current_config')
            ->joinSub(clone $latestConfigs, 'current_versions', static function ($join): void {
                $join->on('current_config.id', '=', 'current_versions.id');
            })
            ->where('current_config.work_type_name', 'like', '%售后%')
            ->pluck('current_config.work_type')
            ->all();

        $query = $this->connection->table($main . ' AS m')
            ->joinSub($latestConfigs, 'config_versions', static function ($join): void {
                $join->on('config_versions.work_type', '=', 'm.work_type');
            })
            ->join($config . ' AS c', 'c.id', '=', 'config_versions.id')
            ->where('c.work_type_name', 'like', '%售后%')
            ->whereIn('m.work_type', $afterSalesTypes)
            ->where(static function (Builder $active): void {
                $active->whereNull('m.is_deleted')->orWhere('m.is_deleted', 0);
            });

        $this->applyAfterSalesFilters($query, $filters);
        $rows = (clone $query)
            ->select([
                'm.id', 'm.work_type', 'c.work_type_name', 'c.group_name',
                'm.order_no', 'm.title', 'm.status', 'm.create_user',
                'm.submit_time', 'm.expect_end_time', 'm.end_time',
                'm.description', 'm.create_time', 'm.update_time',
            ])
            ->orderByDesc('m.create_time')
            ->orderByDesc('m.id')
            ->forPage($page, $perPage)
            ->get()
            ->map(static fn (object $row): array => (array) $row)
            ->all();

        // An incomplete first page already gives the exact total. In particular,
        // an empty alert panel must not scan the same form values a second time.
        $total = $page === 1 && count($rows) < $perPage ? count($rows) : (clone $query)->count();

        $this->hydrateRelatedRows($rows);

        return [
            'data' => $rows,
            'total' => $total,
            'page' => $page,
            'per_page' => $perPage,
            'last_page' => max((int) ceil($total / $perPage), 1),
            'ready' => true,
            'sync_status' => $syncStatus,
        ];
    }

    /** @param array<string,string> $filters */
    private function applyFilters(Builder $query, array $filters): void
    {
        if (($filters['work_type'] ?? '') !== '') {
            $query->where('m.work_type', $filters['work_type']);
        }
        if (($filters['order_no'] ?? '') !== '') {
            $query->whereRaw("m.order_no LIKE ? ESCAPE '!'", [$this->contains($filters['order_no'])]);
        }
        if (($filters['status'] ?? '') !== '') {
            $query->where('m.status', $filters['status']);
        }
        if (($filters['create_user'] ?? '') !== '') {
            $query->where('m.create_user', $filters['create_user']);
        }
        if (($filters['created_from'] ?? '') !== '') {
            $query->where('m.create_time', '>=', $filters['created_from'] . ' 00:00:00');
        }
        if (($filters['created_to'] ?? '') !== '') {
            $query->where('m.create_time', '<', Carbon::parse($filters['created_to'])->addDay()->toDateString());
        }
        if (($filters['assign_user'] ?? '') !== '') {
            $subTable = $this->datasets->get(self::SUB_DATASET)->targetTable();
            $assignUser = $filters['assign_user'];
            $query->whereExists(static function (Builder $subQuery) use ($subTable, $assignUser): void {
                $subQuery->selectRaw('1')
                    ->from($subTable . ' AS assigned_sub')
                    ->whereColumn('assigned_sub.main_order_no', 'm.order_no')
                    ->where('assigned_sub.assign_user', $assignUser)
                    ->where(static function (Builder $active): void {
                        $active->whereNull('assigned_sub.is_deleted')->orWhere('assigned_sub.is_deleted', 0);
                    });
            });
        }
    }

    /** @param array<string,string> $filters */
    private function applyAfterSalesFilters(Builder $query, array $filters): void
    {
        if (($filters['from'] ?? '') !== '') {
            $query->where('m.create_time', '>=', $filters['from'] . ' 00:00:00');
        }
        if (($filters['to'] ?? '') !== '') {
            $query->where('m.create_time', '<', Carbon::parse($filters['to'])->addDay()->toDateString());
        }
        if (($filters['order_no'] ?? '') !== '') {
            $query->whereRaw("m.order_no LIKE ? ESCAPE '!'", [$this->contains($filters['order_no'])]);
        }
        if (($filters['shop_name'] ?? '') !== '') {
            // Older Tmall/JD alert forms store the shop text in a generic
            // question/field_1 value instead of the explicit shop_name key.
            $this->whereFormValueLike($query, ['shop_name', 'question', 'field_1'], $filters['shop_name']);
        }
        if (($filters['sku_id'] ?? '') !== '') {
            $this->whereFormValueLike($query, ['goods_id', 'id', 'question', 'field_1'], $filters['sku_id']);
        }
    }

    /** @param list<string> $fieldCodes */
    private function whereFormValueLike(Builder $query, array $fieldCodes, string $value): void
    {
        $valueTable = $this->datasets->get(self::VALUE_DATASET)->targetTable();
        // A scalar LIMIT keeps this lookup correlated to the indexed parent.
        // MariaDB otherwise materializes and scans the entire values table for
        // the leading-wildcard text match, even for an empty alert panel.
        $query->where(function (Builder $valueQuery) use ($valueTable, $fieldCodes, $value): void {
            $valueQuery->selectRaw('1')
                ->from($valueTable . ' AS filter_values')
                ->whereColumn('filter_values.main_order_no', 'm.order_no')
                ->whereIn('filter_values.field_code', $fieldCodes)
                ->whereRaw("filter_values.field_value LIKE ? ESCAPE '!'", [$this->contains($value)])
                ->where(static function (Builder $active): void {
                    $active->whereNull('filter_values.is_deleted')->orWhere('filter_values.is_deleted', 0);
                })
                ->limit(1);
        }, '=', 1);
    }

    /** @param list<array<string,mixed>> $rows */
    private function hydrateRelatedRows(array &$rows): void
    {
        $orderNumbers = array_values(array_unique(array_filter(array_map(
            static fn (array $row): string => trim((string) ($row['order_no'] ?? '')),
            $rows,
        ))));

        if ($orderNumbers === []) {
            return;
        }

        $subTable = $this->datasets->get(self::SUB_DATASET)->targetTable();
        $subOrders = $this->connection->table($subTable)
            ->select([
                'id', 'sub_order_no', 'main_order_no', 'title', 'assign_user',
                'status', 'expect_minute', 'submit_time', 'expect_end_time',
                'finish_time', 'content', 'notify_submit', 'notify_soon',
                'notify_overdue', 'overdue_notify_count', 'create_time',
                'update_time', 'taskid',
            ])
            ->whereIn('main_order_no', $orderNumbers)
            ->where(static function (Builder $query): void {
                $query->whereNull('is_deleted')->orWhere('is_deleted', 0);
            })
            ->orderBy('id')
            ->get()
            ->groupBy(static fn (object $row): string => (string) $row->main_order_no);

        $valueTable = $this->datasets->get(self::VALUE_DATASET)->targetTable();
        $formValues = $this->connection->table($valueTable)
            ->select([
                'id', 'main_order_no', 'field_code', 'field_name', 'field_value',
                'field_type', 'sort', 'create_time',
            ])
            ->whereIn('main_order_no', $orderNumbers)
            ->where(static function (Builder $query): void {
                $query->whereNull('is_deleted')->orWhere('is_deleted', 0);
            })
            ->orderBy('sort')
            ->orderBy('id')
            ->get()
            ->groupBy(static fn (object $row): string => (string) $row->main_order_no);

        foreach ($rows as &$row) {
            $orderNumber = (string) ($row['order_no'] ?? '');
            $row['sub_orders'] = $subOrders->get($orderNumber, collect())
                ->map(static fn (object $subOrder): array => (array) $subOrder)
                ->values()
                ->all();
            $row['form_values'] = $formValues->get($orderNumber, collect())
                ->map(static fn (object $value): array => (array) $value)
                ->values()
                ->all();
        }
        unset($row);
    }

    private function contains(string $value): string
    {
        return '%' . str_replace(['!', '%', '_'], ['!!', '!%', '!_'], $value) . '%';
    }

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

        return $value instanceof \DateTimeInterface
            ? $value->format('Y-m-d H:i:s')
            : (string) $value;
    }
}