<?php

namespace Modules\EcommerceFinance\Repositories;

use Illuminate\Database\ConnectionInterface;
use Illuminate\Database\DatabaseManager;
use Illuminate\Support\Facades\Schema;
use Modules\EcommerceFinance\Services\StoreReportPeriodBuilder;
use RuntimeException;

/**
 * 将店铺主体期间与共享日汇总组合为店铺维度报表。
 *
 * 页面不扫描千万级原始账单，也不依赖开发机或镜像中不存在的 SQLite。
 * 期间由 StoreReportPeriodBuilder 按期初数据和爬虫变更生成：变更店铺的老主体
 * 失效时间为变更日前一天、新主体从变更日当天生效，收入按实际日期归集，不重复统计。
 *
 * 两列金额口径不同，页面分别展示：
 *  销量（period_sales / sales_NN）：业绩明细（店铺收款匹配订单明细）的
 *    「收入金额 + 优惠金额 + 冲减金额 + 违约赔付（冲减收入） + 退款金额」合计，按「店铺名称 + 发生日期」
 *    预汇总到 store_report_paid_sales_rows，再按发生日期落到主体期间；
 *  收入（rolling_12m_income / income_NN）：平台账单日汇总 store_report_daily_rows 的收入，
 *    供主体维度经营报表按主体汇总使用。
 */
class StoreReportRepository
{
    // 保留同步命令使用的数据集键；页面只读生命周期与日汇总。
    public const BILLS_DATASET = 'store-report.platform-bills';
    public const LIFECYCLES_DATASET = 'store-report.store-lifecycles';
    /** 平台账单日汇总：主体维度经营报表的「收入」列仍按这里取数。 */
    public const DAILY_DATASET = 'store-report.daily-sales';
    /** 销量数据集：业绩明细四项金额合计按「店铺名称 + 发生日期」预汇总。 */
    public const PAID_SALES_DATASET = 'store-report.paid-sales';
    /** 销量汇总表；由业绩明细表汇总而来，页面按店铺名与发生日期关联主体期间。 */
    public const PAID_SALES_TABLE = 'store_report_paid_sales_rows';
    /** 业绩明细表（店铺收款匹配订单明细），销量汇总的唯一上游。 */
    public const PAID_SALES_SOURCE_TABLE = 'store_receipt_order_match_rows';

    /**
     * 销量来源：收入+优惠+冲减+退款四项合计（按店铺 + 发生日期预汇总）。
     * 列名沿用历史命名 paid_amount，避免共享镜像表结构变更影响线上旧版本。
     *
     * @var array{table:string,amount:string}
     */
    private const SALES_SOURCE = ['table' => self::PAID_SALES_TABLE, 'amount' => 'paid_amount'];

    /** @var array{table:string,amount:string} 收入来源：平台账单日汇总。 */
    private const INCOME_SOURCE = ['table' => 'store_report_daily_rows', 'amount' => 'income'];

    private const DEFAULT_EFFECTIVE_AT = '2026-07-01';
    private const DEFAULT_EXPIRES_AT = '2999-01-01';
    /** 主表 rpt.fys_shopinfo 中这些平台不支持变更主体（能否变更=否），其余平台均为是。 */
    public const UNCHANGEABLE_PLATFORMS = ['唯品会MP', '淘工厂'];
    private readonly ConnectionInterface $connection;
    private readonly StoreReportPeriodBuilder $periods;

    public function __construct(DatabaseManager $database)
    {
        $this->connection = $database->connection(
            (string) config('upstream_mirrors.connection', 'business_mirror'),
        );
        $this->periods = new StoreReportPeriodBuilder($database);
    }

    /**
     * 店铺维度报表的查询条件；日期统一左闭右开 [from, to)。
     *
     * @return array<string,string>
     */
    public static function requestFilters(): array
    {
        $rules = [];
        foreach (['opened_at', 'effective_at', 'invalid_at'] as $prefix) {
            $rules[$prefix . '_from'] = ['nullable', 'date_format:Y-m-d'];
            $rules[$prefix . '_to'] = ['nullable', 'date_format:Y-m-d'];
            if (request()->filled($prefix . '_from')) {
                $rules[$prefix . '_to'][] = 'after_or_equal:' . $prefix . '_from';
            }
        }
        foreach (['erp_store_name', 'store_name', 'store_id', 'profit_center', 'store_manager', 'department_manager', 'entity', 'company_type'] as $key) {
            $rules[$key] = ['nullable', 'string', 'max:191'];
        }
        foreach (['period_sales', ...array_map(static fn (int $month): string => sprintf('sales_%02d', $month), range(1, 12))] as $key) {
            $rules[$key] = ['nullable', 'string', 'max:64'];
        }
        $rules['status'] = ['nullable', 'string', 'in:0,3,6'];
        $rules['can_change'] = ['nullable', 'string', 'in:是,否'];
        $sortFields = [
            'erp_store_name', 'store_name', 'store_id', 'profit_center', 'store_manager', 'department_manager',
            'opened_at', 'status', 'can_change', 'entity', 'company_type', 'effective_at',
            'invalid_at', 'period_sales', ...array_map(static fn (int $month): string => sprintf('sales_%02d', $month), range(1, 12)),
        ];
        $rules['sort'] = ['nullable', 'string', 'in:' . implode(',', $sortFields)];
        $rules['direction'] = ['nullable', 'string', 'in:asc,desc'];

        $filters = validator(request()->only(array_keys($rules)), $rules)->validate();
        if (! empty($filters['sort'])) {
            $filters['_sort'] = $filters['sort'];
            $filters['_direction'] = ($filters['direction'] ?? 'asc') === 'desc' ? 'desc' : 'asc';
        }
        unset($filters['sort'], $filters['direction']);

        return $filters;
    }

    /**
     * @param array<int,array<string,mixed>> $stores
     * @param array<string,string> $filters 未覆盖字段已在上游 allStores 过滤；这里过滤主体、主体期间及归属覆盖字段。
     * @return array<int,array<string,mixed>>
     */
    public function rows(array $stores, ?int $reportYear = null, array $filters = []): array
    {
        if ($stores === []) {
            return [];
        }

        $salesGeneration = $this->activeGeneration(self::PAID_SALES_DATASET);
        $dailyGeneration = $this->activeGeneration(self::DAILY_DATASET);
        // 期间由期初数据、系统主体和爬虫变更生成，同一店铺不再展开流程历史重复行。
        $periods = $this->periods->build($stores);
        $multiPeriod = [];
        $singleNames = [];
        foreach ($periods as $name => $list) {
            if (count($list) > 1) {
                $multiPeriod[$name] = $list;
            } else {
                $singleNames[] = $name;
            }
        }
        $singleSales = $this->singlePeriodTotals($singleNames, $salesGeneration, self::SALES_SOURCE);
        $splitSales = $this->periodTotals($multiPeriod, $salesGeneration, self::SALES_SOURCE);
        $singleIncome = $this->singlePeriodTotals($singleNames, $dailyGeneration, self::INCOME_SOURCE);
        $splitIncome = $this->periodTotals($multiPeriod, $dailyGeneration, self::INCOME_SOURCE);
        $reportYear = $reportYear ?? (int) now()->format('Y');
        $currentMonth = now()->startOfMonth();
        $rollingMonths = collect(range(12, 1))->map(
            static fn (int $monthsAgo): string => $currentMonth->copy()->subMonthsNoOverflow($monthsAgo)->format('Y-m'),
        )->all();
        $rows = [];

        foreach ($stores as $store) {
            $storeName = trim((string) ($store['erp_store_name'] ?? ''));
            if ($storeName === '') {
                continue;
            }

            $list = $periods[$storeName] ?? [[
                'company_name' => $store['company_name'] ?? null,
                'effective_at' => self::DEFAULT_EFFECTIVE_AT,
                'expires_at' => null,
                'source' => 'fallback',
            ]];
            $split = count($list) > 1;

            foreach ($list as $index => $period) {
                $saleData = $split
                    ? ($splitSales[$storeName][$index] ?? ['period' => 0.0, 'months' => []])
                    : ($singleSales[$storeName] ?? ['period' => 0.0, 'months' => []]);
                $incomeData = $split
                    ? ($splitIncome[$storeName][$index] ?? ['period' => 0.0, 'months' => []])
                    : ($singleIncome[$storeName] ?? ['period' => 0.0, 'months' => []]);
                $monthly = [];
                $monthlyIncome = [];
                for ($month = 1; $month <= 12; $month++) {
                    $monthKey = sprintf('%d-%02d', $reportYear, $month);
                    $monthly[sprintf('sales_%02d', $month)] = (float) ($saleData['months'][$monthKey] ?? 0);
                    $monthlyIncome[sprintf('income_%02d', $month)] = $monthKey >= $currentMonth->format('Y-m')
                        || ! array_key_exists($monthKey, $incomeData['months'])
                            ? null
                            : (float) $incomeData['months'][$monthKey];
                }

                $rollingSales = array_sum(array_map(
                    static fn (string $month): float => (float) ($incomeData['months'][$month] ?? 0),
                    $rollingMonths,
                ));

                $effectiveAt = $this->date($period['effective_at'] ?? null, self::DEFAULT_EFFECTIVE_AT);
                $expiresAt = $this->date($period['expires_at'] ?? null, self::DEFAULT_EXPIRES_AT);
                $status = $this->status($store['store_status'] ?? null);
                // 关店和其他非正常状态不展示期间日期，避免把已关闭店铺计入在营期间。
                $normal = $status === '开店中';
                $row = [
                    ...$store,
                    'status' => $status,
                    'can_change' => $this->canChange($store['platform'] ?? null),
                    'entity' => $period['company_name'] ?: ($store['company_name'] ?? null),
                    'effective_at' => $normal ? $effectiveAt : '-',
                    'invalid_at' => $normal
                        ? ($expiresAt === self::DEFAULT_EXPIRES_AT ? '存续中' : $expiresAt)
                        : '-',
                    'period_sales' => (float) $saleData['period'],
                    'rolling_12m_income' => array_intersect($rollingMonths, array_keys($incomeData['months'])) === []
                        ? null
                        : $rollingSales,
                    ...$monthly,
                    ...$monthlyIncome,
                ];

                if (! $this->matchesFilters($row, $effectiveAt, $expiresAt, $filters)) {
                    continue;
                }

                $rows[] = $row;
            }
        }

        return $this->sortRows($rows, $filters);
    }

    /** @param array<int,array<string,mixed>> $rows @param array<string,string> $filters */
    private function sortRows(array $rows, array $filters): array
    {
        $key = (string) ($filters['_sort'] ?? '');
        if ($key === '') {
            return $rows;
        }

        $allowed = [
            'erp_store_name' => 'erp_store_name', 'store_name' => 'store_name', 'store_id' => 'store_id',
            'profit_center' => 'profit_center', 'store_manager' => 'store_manager',
            'department_manager' => 'department_manager', 'opened_at' => 'opened_at',
            'status' => 'status', 'can_change' => 'can_change', 'entity' => 'entity',
            'company_type' => 'company_type', 'effective_at' => 'effective_at',
            'invalid_at' => 'invalid_at', 'period_sales' => 'period_sales',
            ...array_combine(
                array_map(static fn (int $month): string => sprintf('sales_%02d', $month), range(1, 12)),
                array_map(static fn (int $month): string => sprintf('sales_%02d', $month), range(1, 12)),
            ),
        ];
        $column = $allowed[$key] ?? null;
        if ($column === null) {
            return $rows;
        }

        $direction = ($filters['_direction'] ?? 'asc') === 'desc' ? -1 : 1;
        usort($rows, static function (array $left, array $right) use ($column, $direction): int {
            $leftValue = $left[$column] ?? null;
            $rightValue = $right[$column] ?? null;
            if (is_numeric($leftValue) && is_numeric($rightValue)) {
                $comparison = ((float) $leftValue <=> (float) $rightValue) * $direction;
            } else {
                $comparison = strcmp((string) ($leftValue ?? ''), (string) ($rightValue ?? '')) * $direction;
            }

            return $comparison !== 0 ? $comparison : strcmp((string) ($left['store_id'] ?? ''), (string) ($right['store_id'] ?? ''));
        });

        return $rows;
    }

    /**
     * 多期间店铺按每个期间的主体生效日、失效日（含当天）拆分金额。
     *
     * @param array<string,list<array<string,mixed>>> $periodsByStore
     * @param array{table:string,amount:string} $source 金额来源表与金额列（表名、列名来自类常量，不接受外部输入）
     * @return array<string,array<int,array{period:float,months:array<string,float>}>>
     */
    private function periodTotals(array $periodsByStore, string $generation, array $source): array
    {
        $flat = [];
        foreach ($periodsByStore as $name => $list) {
            foreach ($list as $index => $period) {
                $flat[] = [
                    'key' => $name . "\x1f" . $index,
                    'store' => $name,
                    'effective_at' => $this->date($period['effective_at'] ?? null, self::DEFAULT_EFFECTIVE_AT),
                    'expires_at' => $this->date($period['expires_at'] ?? null, self::DEFAULT_EXPIRES_AT),
                ];
            }
        }

        $month = $this->connection->getDriverName() === 'sqlite'
            ? "substr(d.occurred_on, 1, 7)"
            : "DATE_FORMAT(d.occurred_on, '%Y-%m')";
        $table = $source['table'];
        $amount = $source['amount'];
        $collected = [];
        foreach (array_chunk($flat, 200) as $chunk) {
            $selects = [];
            $bindings = [];
            foreach ($chunk as $row) {
                $selects[] = 'SELECT ? AS period_key, ? AS store_name, ? AS effective_at, ? AS expires_at';
                array_push($bindings, $row['key'], $row['store'], $row['effective_at'], $row['expires_at']);
            }
            $rows = $this->select(
                "SELECT p.period_key, {$month} AS bill_month,
                        SUM(COALESCE(d.{$amount}, 0)) AS amount_total
                 FROM (" . implode(' UNION ALL ', $selects) . ") AS p
                 INNER JOIN {$table} AS d
                   ON d.store_name = p.store_name
                  AND d.generation = ?
                  AND d.occurred_on >= p.effective_at
                  AND d.occurred_on <= p.expires_at
                 GROUP BY p.period_key, {$month}",
                [...$bindings, $generation],
            );
            $this->collectTotals($collected, $rows, 'period_key');
        }

        $result = [];
        foreach ($collected as $key => $sales) {
            [$store, $index] = explode("\x1f", (string) $key, 2) + [1 => '0'];
            $result[$store][(int) $index] = $sales;
        }

        return $result;
    }

    /**
     * 单期间店铺的金额按月汇总；生效、失效时间用统一的默认窗口兜底。
     *
     * @param list<string> $names
     * @param array{table:string,amount:string} $source
     * @return array<string,array{period:float,months:array<string,float>}>
     */
    private function singlePeriodTotals(array $names, string $generation, array $source): array
    {
        if ($names === []) {
            return [];
        }

        $month = $this->connection->getDriverName() === 'sqlite'
            ? "substr(occurred_on, 1, 7)"
            : "DATE_FORMAT(occurred_on, '%Y-%m')";
        $table = $source['table'];
        $amount = $source['amount'];
        $result = [];
        foreach ($this->nameChunks($names) as $chunk) {
            $placeholders = implode(',', array_fill(0, count($chunk), '?'));
            $rows = $this->select(
                "SELECT store_name, {$month} AS bill_month,
                        SUM(COALESCE({$amount}, 0)) AS amount_total
                 FROM {$table}
                 WHERE generation = ? AND store_name IN ($placeholders)
                   AND occurred_on >= ? AND occurred_on < ?
                 GROUP BY store_name, {$month}",
                [$generation, ...$chunk, self::DEFAULT_EFFECTIVE_AT, self::DEFAULT_EXPIRES_AT],
            );
            $this->collectTotals($result, $rows, 'store_name');
        }

        return $result;
    }

    /** @param array<string,array{period:float,months:array<string,float>}> $result */
    private function collectTotals(array &$result, array $rows, string $keyColumn): void
    {
        foreach ($rows as $row) {
            $key = (string) $row[$keyColumn];
            $result[$key]['period'] = ($result[$key]['period'] ?? 0.0) + (float) $row['amount_total'];
            $result[$key]['months'][(string) $row['bill_month']] = ($result[$key]['months'][(string) $row['bill_month']] ?? 0.0) + (float) $row['amount_total'];
        }
    }

    /** @return array<int,array<string,mixed>> */
    private function select(string $query, array $bindings = []): array
    {
        return array_map(
            static fn (object $row): array => (array) $row,
            $this->connection->select($query, $bindings),
        );
    }

    /** @return list<list<string>> */
    private function nameChunks(array $names): array
    {
        // SQLite fixtures keep the conservative variable limit. Production
        // MariaDB uses larger chunks to avoid turning one report page into
        // dozens of otherwise identical round trips.
        return array_chunk($names, $this->connection->getDriverName() === 'sqlite' ? 100 : 500);
    }

    private function activeGeneration(string $dataset): string
    {
        $schema = Schema::connection($this->connection->getName());
        if (! $schema->hasTable('store_report_import_states')) {
            throw new RuntimeException('store_report_state_missing');
        }

        $state = $this->connection->table('store_report_import_states')
            ->where('dataset_key', $dataset)
            ->first();
        $generation = trim((string) ($state->active_generation ?? ''));
        if ($generation === '' || empty($state->initial_completed_at)) {
            throw new RuntimeException('store_report_dataset_not_ready:' . $dataset);
        }

        return $generation;
    }

    private function date(mixed $value, string $fallback): string
    {
        $value = trim((string) ($value ?? ''));
        return $value === '' ? $fallback : substr($value, 0, 10);
    }

    /**
     * 生命周期字段的行级过滤：主体、负责人和利润中心按关键词匹配，能否变更按原值匹配；生效、失效时间左闭右开。
     *
     * 店铺负责人/部门负责人/利润中心展示店铺账号镜像（PostgreSQL）覆盖后的值，
     * 与页面筛选保持一致，因此在这里按行值匹配而不是上游镜像 SQL。
     *
     * @param array<string,mixed> $row 展示行（关店店铺的期间字段已显示为“-”）
     * @param string $effectiveAt 期间真实生效日，不受状态展示影响
     * @param string $expiresAt 期间真实失效日，存续中按 2999-01-01 参与过滤
     * @param array<string,string> $filters
     */
    private function matchesFilters(array $row, string $effectiveAt, string $expiresAt, array $filters): bool
    {
        foreach (['entity', 'store_manager', 'department_manager', 'profit_center'] as $key) {
            $expected = trim((string) ($filters[$key] ?? ''));
            if ($expected !== '' && ! str_contains(mb_strtolower(trim((string) ($row[$key] ?? ''))), mb_strtolower($expected))) {
                return false;
            }
        }
        $canChange = trim((string) ($filters['can_change'] ?? ''));
        if ($canChange !== '' && trim((string) ($row['can_change'] ?? '')) !== $canChange) {
            return false;
        }

        foreach (['effective_at' => $effectiveAt, 'invalid_at' => $expiresAt] as $key => $value) {
            $from = trim((string) ($filters[$key . '_from'] ?? ''));
            $to = trim((string) ($filters[$key . '_to'] ?? ''));
            if ($from !== '' && strcmp($value, $from) < 0) {
                return false;
            }
            if ($to !== '' && strcmp($value, $to) >= 0) {
                return false;
            }
        }

        foreach (['period_sales', ...array_map(static fn (int $month): string => sprintf('sales_%02d', $month), range(1, 12))] as $key) {
            $needle = str_replace(',', '', trim((string) ($filters[$key] ?? '')));
            if ($needle !== '' && (! is_numeric($needle) || ! is_numeric($row[$key] ?? null)
                || abs((float) $row[$key] - (float) $needle) > 0.000001)) {
                return false;
            }
        }

        return true;
    }

    private function status(mixed $value): ?string
    {
        return match ((string) $value) {
            '0', '正常' => '开店中',
            '3', '关闭中' => '关店中',
            '6', '已关闭' => '关店',
            default => $value === null || $value === '' ? null : (string) $value,
        };
    }

    /** 能否变更：平台为唯品会MP或淘工厂时为“否”，其余（含平台为空）为“是”。 */
    private function canChange(mixed $platform): string
    {
        return in_array(trim((string) ($platform ?? '')), self::UNCHANGEABLE_PLATFORMS, true) ? '否' : '是';
    }
}