<?php

namespace Modules\EcommerceFinance\Repositories;

use InvalidArgumentException;
use Modules\EcommerceFinance\Services\UpstreamReadOnlyQuery;
use Modules\EcommerceFinance\Services\UpstreamReadOnlyQueryFactory;

/** 固定字段、固定表的推广账户及唯品会营销日报只读查询。 */
class PlatformPromotionAccountRepository
{
    private const SOURCE = 'js_data_middle_platform';

    public function __construct(private readonly UpstreamReadOnlyQueryFactory $queries)
    {
    }

    public function dashboard(array $filters, int $limit = 200, ?string $platform = null): array
    {
        if ($platform !== null && ! in_array($platform, ['jd', 'douyin', 'dewu', 'vip'], true)) {
            throw new InvalidArgumentException('未知的推广账户平台。');
        }

        $month = $filters['month'];
        $shop = $filters['shop_name'] ?? null;
        $account = $filters['account'] ?? null;
        $advertiser = $filters['advertiser_id'] ?? null;
        $limit = min(max($limit, 1), 5000);
        $query = $this->queries->forSource(self::SOURCE);

        $jd = [];
        $jdSummary = [];
        $douyin = [];
        $douyinSummary = [];
        $douyinStatus = [];
        $dewu = [];
        $dewuSummary = [];
        $vipMarketing = [];
        $vipMarketingDaily = [];
        $vipSummary = [];

        if ($platform === null || $platform === 'jd') {
            $jd = $this->query($query, "SELECT TOP {$limit} data_month, shop_name, account, login_pin, in_out_type_name, trade_type_name, amount, balance_amount, settle_datetime, show_date, trade_no, remark FROM JSDataMiddlePlatform.dbo.jd_jzt_finance_records WHERE data_month = ?", [$month], $shop, $account, 'shop_name');
            $jdSummary = $this->query($query, "SELECT COALESCE(SUM(CASE WHEN in_out_type='IN' THEN amount ELSE 0 END),0) income, COALESCE(SUM(CASE WHEN in_out_type='OUT' THEN amount ELSE 0 END),0) expense, COUNT(*) record_count FROM JSDataMiddlePlatform.dbo.jd_jzt_finance_records WHERE data_month = ?", [$month], $shop, $account, 'shop_name', true)[0] ?? [];
        }

        if ($platform === null || $platform === 'douyin') {
            $douyin = $this->query($query, "SELECT TOP {$limit} shopName, account, qianchuanAavid, qianchuanAccountName, statDate, cost, income, transferOut, transferIn, balance, status, remark FROM JSDataMiddlePlatform.dbo.dy_qianchuan_finance_transactions WHERE dataMonth = ? AND rowType = N'daily' AND status = N'success'", [$month . '-01'], $shop, $account, 'shopName');
            $douyinSummary = $this->query($query, "SELECT COALESCE(SUM(cost),0) cost, COALESCE(SUM(income),0) income, COALESCE(SUM(transferOut),0) transfer_out, COALESCE(SUM(transferIn),0) transfer_in, COUNT(DISTINCT qianchuanAavid) account_count FROM JSDataMiddlePlatform.dbo.dy_qianchuan_finance_transactions WHERE dataMonth = ? AND rowType = N'daily' AND status = N'success'", [$month . '-01'], $shop, $account, 'shopName', true)[0] ?? [];
            $douyinStatus = $this->query($query, "SELECT COUNT(*) status_count FROM JSDataMiddlePlatform.dbo.dy_qianchuan_finance_transactions WHERE dataMonth = ? AND rowType = N'status' AND status = N'no_permission'", [$month . '-01'], $shop, $account, 'shopName', true)[0] ?? [];
        }

        if ($platform === null || $platform === 'dewu') {
            $dewu = $this->query($query, "SELECT TOP {$limit} data_month, shop_name, account, amount_type, flow_type, trade_amount_yuan, balance_yuan, trade_date, date_time, remark FROM JSDataMiddlePlatform.dbo.dw_ad_finance_records WHERE data_month = ?", [$month], $shop, $account, 'shop_name');
            $dewuSummary = $this->query($query, 'SELECT COALESCE(SUM(total_income_amount),0) income, COALESCE(SUM(total_expense_amount),0) expense, COALESCE(SUM(net_amount),0) net, COALESCE(SUM(record_count),0) record_count FROM JSDataMiddlePlatform.dbo.dw_ad_finance_month_summaries WHERE data_month = ?', [$month], $shop, $account, 'shop_name', true)[0] ?? [];
        }

        if ($platform === null || $platform === 'vip') {
            $vipMarketing = $this->query($query, "SELECT TOP {$limit} account, accountName, advertiserId, dataMonth, startDate, endDate, totalCostYuan, totalImpressionCount, recordCount, updatedAt FROM JSDataMiddlePlatform.dbo.vip_marketingMonthlySummary WHERE dataMonth = ?", [$month], null, $account, null, true, $advertiser);
            $vipMarketingDaily = $this->query($query, "SELECT TOP {$limit} account, accountName, advertiserId, advertiserTitle, dataMonth, reportDate, reportTimestamp, costYuan, impressionCount, updatedAt FROM JSDataMiddlePlatform.dbo.vip_marketingDailyReport WHERE dataMonth = ?", [$month], null, $account, null, false, $advertiser, 'reportDate, advertiserId, account');
            $vipSummary = [
                'cost' => $this->sum($vipMarketing, 'totalCostYuan'),
                'impressions' => $this->sum($vipMarketing, 'totalImpressionCount'),
                'record_count' => $this->sum($vipMarketing, 'recordCount'),
                'account_count' => $this->distinctCount($vipMarketing, 'account'),
                'daily_record_count' => count($vipMarketingDaily),
            ];
        }

        return [
            'jd' => $jd,
            'jdSummary' => $jdSummary,
            'dy' => $douyin,
            'dySummary' => $douyinSummary,
            'dyStatus' => $douyinStatus,
            'dw' => $dewu,
            'dwSummary' => $dewuSummary,
            // 保留旧键为空数组，避免调用方因契约变更报错；唯品会页签只使用营销日报。
            'vipSales' => [],
            'vipTax' => [],
            'vipMarketing' => $vipMarketing,
            'vipMarketingDaily' => $vipMarketingDaily,
            'vipSummary' => $vipSummary,
        ];
    }

    public function latestMonth(): ?string
    {
        $row = $this->queries->forSource(self::SOURCE)->first(
            'SELECT MAX(data_month) AS month FROM JSDataMiddlePlatform.dbo.jd_jzt_finance_records'
        );
        $month = trim((string) ($row['month'] ?? ''));

        return preg_match('/^\d{4}-\d{2}$/', $month) === 1 ? $month : null;
    }

    private function query(
        UpstreamReadOnlyQuery $query,
        string $sql,
        array $bindings,
        ?string $shop,
        ?string $account,
        ?string $shopColumn,
        bool $summary = false,
        ?string $advertiser = null,
        ?string $orderBy = null,
    ): array {
        if ($shop !== null && $shop !== '' && $shopColumn !== null) {
            $sql .= " AND {$shopColumn} LIKE ?";
            $bindings[] = '%' . $shop . '%';
        }
        if ($account !== null && $account !== '') {
            $sql .= ' AND account LIKE ?';
            $bindings[] = '%' . $account . '%';
        }
        if ($advertiser !== null && $advertiser !== '') {
            $sql .= ' AND advertiserId LIKE ?';
            $bindings[] = '%' . $advertiser . '%';
        }
        if (! $summary) {
            $sql .= ' ORDER BY ' . ($orderBy ?: '1');
        }

        return $query->select($sql, $bindings);
    }

    private function sum(array $rows, string $key): float
    {
        return round(array_sum(array_map(static fn (array $row): float => (float) ($row[$key] ?? 0), $rows)), 2);
    }

    private function distinctCount(array $rows, string $key): int
    {
        $values = [];
        foreach ($rows as $row) {
            $value = $row[$key] ?? null;
            if ($value !== null && $value !== '') {
                $values[(string) $value] = true;
            }
        }

        return count($values);
    }
}