admin_api/app/Http/Controllers/Admin/AccountCookieController.php

<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Models\AccountsInfo;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class AccountCookieController extends Controller
{
    public function index(Request $request)
    {
        $filters = $this->buildFilters($request);

        $cookieSubQuery = DB::connection('read')->raw("
            (
                SELECT *,
                       ROW_NUMBER() OVER (
                           PARTITION BY
                               ISNULL(CAST(UserID AS VARCHAR(50)), '')
                           ORDER BY CreateTime DESC, ID DESC
                       ) AS rn
                FROM QPAccountsDB.dbo.AccountCookie
            ) ac
        ");

        $query = DB::connection('read')
            ->table($cookieSubQuery)
            ->leftJoin(AccountsInfo::TABLE . ' as ai', 'ai.UserID', '=', 'ac.UserID')
            ->select([
                'ac.ID',
                'ac.UserID',
                'ai.GameID',
                'ai.Channel as AccountChannel',
                'ai.RegisterDate',
                'ac.UrlSign',
                'ac.Platform',
                'ac.CreateTime',
                'ac.IP',
                'ac.Locale',
                'ac.Origin',
                'ac.FPID',
                'ac.FF',
                'ac.ClickUA',
                'ac.GameUA',
                'ac.Params',
                'ac.Cookie',
            ]);

        $this->applyFilters($query, $filters);
        $query->where('ac.rn', 1);

        $stats = $this->buildStats(clone $query);
        $fbclidGroups = $this->buildFbclidGroups(clone $query);
        $ffGroups = $this->buildDuplicateValueGroups(clone $query, 'FF', 'ff');

        $list = $query
            ->orderBy('ac.CreateTime', 'desc')
            ->paginate($filters['page_size'])
            ->appends($request->query());

        $userIds = $list->getCollection()
            ->pluck('UserID')
            ->filter(function ($userId) {
                return !empty($userId);
            })
            ->map(function ($userId) {
                return (int)$userId;
            })
            ->unique()
            ->values()
            ->all();

        $payMap = $this->loadPayStats($userIds);
        $adjustMap = $this->loadAdjustEventStats($userIds, $filters);

        $items = $list->getCollection()->map(function ($row) {
            $params = $this->decodeJson($row->Params);
            $cookieMap = $this->parseCookieString($row->Cookie);
            $uaInfo = $this->analyzeUserAgent($row->ClickUA ?: $row->GameUA);
            $paramAnalysis = $this->analyzeMarketingParams($params);

            $row->HasFbclid = stripos($row->Params ?? '', 'fbclid') !== false;
            $row->Fbp = $cookieMap['_fbp'] ?? '';
            $row->Fbc = $cookieMap['_fbc'] ?? ($params['fbclid'] ?? '');
            $row->Pixel = $paramAnalysis['primary']['pixel'];
            $row->UtmSource = $paramAnalysis['primary']['utm_source'];
            $row->UtmMedium = $paramAnalysis['primary']['utm_medium'];
            $row->UtmCampaign = $paramAnalysis['primary']['utm_campaign'];
            $row->ParamChannel = $paramAnalysis['primary']['channel'];
            $row->ParamCampaign = $paramAnalysis['primary']['campaign'];
            $row->ParamAdgroup = $paramAnalysis['primary']['adgroup'];
            $row->ParamCreative = $paramAnalysis['primary']['creative'];
            $row->UaApp = $uaInfo['app'];
            $row->UaOs = $uaInfo['os'];
            $row->UaDevice = $uaInfo['device'];
            $row->CookieKey = $this->makeCookieKey($row);
            $row->ParamsDecoded = $params;
            $row->ParamAnalysis = $paramAnalysis;
            $row->FbclidValue = $paramAnalysis['primary']['fbclid'];
            $row->FbclidGroup = null;
            $row->FFGroup = null;
            $row->FbclidCookieCheck = $this->validateFbclidCookieConsistency(
                $paramAnalysis['primary']['fbclid'],
                $cookieMap['_fbc'] ?? ''
            );

            return $row;
        });

        $items = $items->map(function ($row) use ($payMap, $adjustMap, $fbclidGroups, $ffGroups) {
            $pay = $payMap[(int)($row->UserID ?? 0)] ?? null;
            $adjust = $adjustMap[(int)($row->UserID ?? 0)] ?? null;
            $fbclidGroup = $fbclidGroups['rows'][$row->ID] ?? null;
            $ffGroup = $ffGroups['rows'][$row->ID] ?? null;

            $row->PayOrderCount = $pay->pay_order_count ?? 0;
            $row->PayAmountSum = $pay->pay_amount_sum ?? 0;
            $row->PayAmountDisplay = (string)round(((float)($row->PayAmountSum ?? 0)) / 100);
            $row->LastPayAt = $pay->last_pay_at ?? '';
            $row->AdjustStatus = $adjust['status'] ?? 'none';
            $row->AdjustLogs = $adjust['logs'] ?? [];
            $row->FbclidGroup = $fbclidGroup;
            $row->FFGroup = $ffGroup;

            return $row;
        });

        $list->setCollection($items);

        return view('admin.account_cookie.index', [
            'list' => $list,
            'filters' => $filters,
            'stats' => $stats,
            'fbclidGroups' => $fbclidGroups['groups'],
            'ffGroups' => $ffGroups['groups'],
        ]);
    }

    protected function buildFilters(Request $request)
    {
        $gameId = trim((string)$request->input('GameID', ''));
        $resolvedUserId = '';
        if ($gameId !== '') {
            $resolvedUserId = AccountsInfo::query()->where('GameID', $gameId)->value('UserID') ?: '';
        }

        $dateStartRaw = trim((string)$request->input('date_start', date('Y-m-d')));
        $dateEndRaw = trim((string)$request->input('date_end', date('Y-m-d')));
        $registerStartRaw = trim((string)$request->input('register_start', date('Y-m-d')));
        $registerEndRaw = trim((string)$request->input('register_end', date('Y-m-d')));

        return [
            'user_id' => trim((string)$request->input('UserID', '')),
            'game_id' => $gameId,
            'resolved_user_id' => $resolvedUserId,
            'url_signs' => $this->splitCsv($request->input('UrlSign', '')),
            'account_channels' => $this->splitCsv($request->input('AccountChannel', '')),
            'platform' => trim((string)$request->input('Platform', '')),
            'date_start_raw' => $dateStartRaw,
            'date_end_raw' => $dateEndRaw,
            'register_start_raw' => $registerStartRaw,
            'register_end_raw' => $registerEndRaw,
            'date_start' => $this->normalizeDateBoundary($dateStartRaw, false),
            'date_end' => $this->normalizeDateBoundary($dateEndRaw, true),
            'register_start' => $this->normalizeDateBoundary($registerStartRaw, false),
            'register_end' => $this->normalizeDateBoundary($registerEndRaw, true),
            'has_fbclid' => (int)$request->input('has_fbclid', 1),
            'origin' => trim((string)$request->input('Origin', '')),
            'ip' => trim((string)$request->input('IP', '')),
            'ua' => trim((string)$request->input('UA', '')),
            'param_category' => trim((string)$request->input('param_category', '')),
            'param_key' => trim((string)$request->input('param_key', '')),
            'param_value' => trim((string)$request->input('param_value', '')),
            'page_size' => max(20, min((int)$request->input('page_size', 100), 500)),
        ];
    }

    protected function applyFilters($query, array $filters)
    {
        if ($filters['user_id'] !== '') {
            $query->where('ac.UserID', $filters['user_id']);
        }

        if ($filters['game_id'] !== '') {
            if ($filters['resolved_user_id'] !== '') {
                $query->where('ac.UserID', $filters['resolved_user_id']);
            } else {
                $query->whereRaw('1 = 0');
            }
        }

        if (!empty($filters['url_signs'])) {
            $query->whereIn('ac.UrlSign', $filters['url_signs']);
        }

        if (!empty($filters['account_channels'])) {
            $query->whereIn('ai.Channel', $filters['account_channels']);
        }

        if ($filters['platform'] !== '') {
            $query->where('ac.Platform', $filters['platform']);
        }

        if ($filters['date_start'] !== '') {
            $query->where('ac.CreateTime', '>=', $filters['date_start']);
        }
        if ($filters['date_end'] !== '') {
            $query->where('ac.CreateTime', '<=', $filters['date_end']);
        }

        if ($filters['register_start'] !== '') {
            $query->where('ai.RegisterDate', '>=', $filters['register_start']);
        }
        if ($filters['register_end'] !== '') {
            $query->where('ai.RegisterDate', '<=', $filters['register_end']);
        }

        if ($filters['has_fbclid'] === 1) {
            $query->where('ac.Params', 'like', '%fbclid%');
        } elseif ($filters['has_fbclid'] === 2) {
            $query->where('ac.Params', 'not like', '%fbclid%');
        }

        if ($filters['origin'] !== '') {
            $query->where('ac.Origin', 'like', '%' . $filters['origin'] . '%');
        }

        if ($filters['ip'] !== '') {
            $query->where('ac.IP', 'like', '%' . $filters['ip'] . '%');
        }

        if ($filters['ua'] !== '') {
            $query->where(function ($subQuery) use ($filters) {
                $subQuery->where('ac.ClickUA', 'like', '%' . $filters['ua'] . '%')
                    ->orWhere('ac.GameUA', 'like', '%' . $filters['ua'] . '%');
            });
        }

        if ($filters['param_key'] !== '') {
            $query->where('ac.Params', 'like', '%"' . $filters['param_key'] . '"%');
        }

        if ($filters['param_value'] !== '') {
            $query->where('ac.Params', 'like', '%' . $filters['param_value'] . '%');
        }

        if ($filters['param_category'] !== '') {
            $keys = $this->getCategoryKeys($filters['param_category']);
            if (empty($keys)) {
                $query->whereRaw('1 = 0');
            } else {
                $query->where(function ($subQuery) use ($keys) {
                    foreach ($keys as $key) {
                        $subQuery->orWhere('ac.Params', 'like', '%"' . $key . '"%');
                    }
                });
            }
        }
    }

    protected function buildStats($query)
    {
        $rows = $query->get([
            'ac.ID',
            'ac.UserID',
            'ac.FPID',
            'ac.FF',
            'ac.UrlSign',
            'ac.Platform',
            'ac.IP',
            'ac.Origin',
            'ac.ClickUA',
            'ac.GameUA',
            'ac.Params',
        ]);

        $stats = [
            'total' => $rows->count(),
            'unique_users' => $rows->pluck('UserID')->filter()->unique()->count(),
            'unique_ips' => $rows->pluck('IP')->filter()->unique()->count(),
            'unique_cookies' => $rows->map(function ($row) {
                return $this->makeCookieKey($row);
            })->filter()->unique()->count(),
            'registered_users' => $rows->filter(function ($row) {
                return !empty($row->UserID);
            })->pluck('UserID')->unique()->count(),
            'paid_users' => 0,
            'fbclid_count' => 0,
            'fb_inapp_count' => 0,
            'ig_inapp_count' => 0,
            'platforms' => [],
            'url_signs' => [],
            'origins' => [],
            'utm_sources' => [],
            'param_categories' => [],
            'param_keys' => [],
            'param_category_stats' => [],
            'duplicate_fbclid_groups' => 0,
            'duplicate_fbclid_rows' => 0,
            'duplicate_ff_groups' => 0,
            'duplicate_ff_rows' => 0,
            'fbclid_cookie_issues' => 0,
        ];

        $fbclidBuckets = [];
        $ffBuckets = [];

        foreach ($rows as $row) {
            $params = $this->decodeJson($row->Params);
            $uaInfo = $this->analyzeUserAgent($row->ClickUA ?: $row->GameUA);
            $paramAnalysis = $this->analyzeMarketingParams($params);
            $cookieMap = $this->parseCookieString($row->Cookie ?? '');

            if (stripos($row->Params ?? '', 'fbclid') !== false) {
                $stats['fbclid_count']++;
            }
            if ($uaInfo['app'] === 'Facebook') {
                $stats['fb_inapp_count']++;
            }
            if ($uaInfo['app'] === 'Instagram') {
                $stats['ig_inapp_count']++;
            }

            $this->incrementBucket($stats['platforms'], $row->Platform ?: 'unknown');
            $this->incrementBucket($stats['url_signs'], (string)($row->UrlSign ?: 'unknown'));
            $this->incrementBucket($stats['origins'], $row->Origin ?: 'unknown');
            $this->incrementBucket($stats['utm_sources'], $params['utm_source'] ?? 'unknown');

            foreach ($paramAnalysis['categories'] as $category => $entries) {
                $label = $this->getParamCategoryLabel($category);
                $this->incrementBucket($stats['param_categories'], $label);

                if (!isset($stats['param_category_stats'][$category])) {
                    $stats['param_category_stats'][$category] = [
                        'label' => $label,
                        'keys' => [],
                        'values' => [],
                    ];
                }

                foreach ($entries as $entry) {
                    $this->incrementBucket($stats['param_category_stats'][$category]['keys'], $entry['key']);
                    $this->incrementBucket($stats['param_keys'], $entry['key']);

                    $valueLabel = $entry['key'] . '=' . $this->truncateParamValue($entry['value']);
                    $this->incrementBucket($stats['param_category_stats'][$category]['values'], $valueLabel);
                }
            }

            $fbclid = $paramAnalysis['primary']['fbclid'];
            if ($fbclid !== '') {
                $fbclidBuckets[$fbclid] = ($fbclidBuckets[$fbclid] ?? 0) + 1;
            }

            $ff = trim((string)($row->FF ?? ''));
            if ($ff !== '') {
                $ffBuckets[$ff] = ($ffBuckets[$ff] ?? 0) + 1;
            }

            $fbclidCookieCheck = $this->validateFbclidCookieConsistency(
                $paramAnalysis['primary']['fbclid'],
                $cookieMap['_fbc'] ?? ''
            );
            if (!$fbclidCookieCheck['ok']) {
                $stats['fbclid_cookie_issues']++;
            }
        }

        $stats['paid_users'] = $this->loadPaidUserCount(
            $rows->pluck('UserID')->filter()->map(function ($userId) {
                return (int)$userId;
            })->unique()->values()->all()
        );

        arsort($stats['platforms']);
        arsort($stats['url_signs']);
        arsort($stats['origins']);
        arsort($stats['utm_sources']);
        arsort($stats['param_categories']);
        arsort($stats['param_keys']);

        foreach ($stats['param_category_stats'] as &$categoryStats) {
            arsort($categoryStats['keys']);
            arsort($categoryStats['values']);
        }
        unset($categoryStats);

        foreach ($fbclidBuckets as $count) {
            if ($count > 1) {
                $stats['duplicate_fbclid_groups']++;
                $stats['duplicate_fbclid_rows'] += $count;
            }
        }

        foreach ($ffBuckets as $count) {
            if ($count > 1) {
                $stats['duplicate_ff_groups']++;
                $stats['duplicate_ff_rows'] += $count;
            }
        }

        return $stats;
    }

    protected function buildFbclidGroups($query)
    {
        $rows = $query->get([
            'ac.ID',
            'ac.Params',
        ]);

        $groupsByFbclid = [];

        foreach ($rows as $row) {
            $params = $this->decodeJson($row->Params);
            $fbclid = $this->analyzeMarketingParams($params)['primary']['fbclid'];
            if ($fbclid === '') {
                continue;
            }

            $groupsByFbclid[$fbclid][] = (int)$row->ID;
        }

        return $this->finalizeDuplicateGroups($groupsByFbclid, 'fbclid');
    }

    protected function buildDuplicateValueGroups($query, $field, $type)
    {
        $rows = $query->get([
            'ac.ID',
            'ac.' . $field,
        ]);

        $groups = [];

        foreach ($rows as $row) {
            $value = trim((string)($row->{$field} ?? ''));
            if ($value === '') {
                continue;
            }

            $groups[$value][] = (int)$row->ID;
        }

        return $this->finalizeDuplicateGroups($groups, $type);
    }

    protected function finalizeDuplicateGroups(array $groupedIds, $type)
    {
        $palettes = [
            'fbclid' => ['#fff3cd', '#d1ecf1', '#d4edda', '#f8d7da', '#e2d9f3', '#fde2b8', '#d6eaf8', '#f5c6cb'],
            'ff' => ['#e8f5e9', '#e3f2fd', '#fff8e1', '#fce4ec', '#ede7f6', '#e0f7fa', '#f1f8e9', '#fff3e0'],
        ];

        $palette = $palettes[$type] ?? ['#f5f5f5'];

        $groups = [];
        $rowMap = [];
        $groupIndex = 1;

        foreach ($groupedIds as $value => $ids) {
            $ids = array_values(array_unique($ids));
            if (count($ids) < 2) {
                continue;
            }

            $color = $palette[($groupIndex - 1) % count($palette)];
            $group = [
                'index' => $groupIndex,
                'value' => $value,
                'count' => count($ids),
                'color' => $color,
                'row_ids' => $ids,
                'type' => $type,
            ];

            $groups[] = $group;

            foreach ($ids as $id) {
                $rowMap[$id] = $group;
            }

            $groupIndex++;
        }

        return [
            'groups' => $groups,
            'rows' => $rowMap,
        ];
    }

    protected function validateFbclidCookieConsistency($paramFbclid, $cookieFbc)
    {
        $paramFbclid = trim((string)$paramFbclid);
        $cookieFbc = trim((string)$cookieFbc);
        $cookieFbclid = $this->extractFbclidFromFbc($cookieFbc);

        if ($paramFbclid === '' && $cookieFbclid === '') {
            return [
                'ok' => true,
                'status' => 'none',
                'cookie_fbclid' => '',
                'message' => '',
            ];
        }

        if ($paramFbclid !== '' && $cookieFbclid === '') {
            return [
                'ok' => false,
                'status' => 'missing_cookie_fbc',
                'cookie_fbclid' => '',
                'message' => 'Params 有 fbclid，但 Cookie 未解析出 _fbc/fbclid',
            ];
        }

        if ($paramFbclid === '' && $cookieFbclid !== '') {
            return [
                'ok' => false,
                'status' => 'missing_param_fbclid',
                'cookie_fbclid' => $cookieFbclid,
                'message' => 'Cookie 有 _fbc/fbclid，但 Params 缺少 fbclid',
            ];
        }

        if ($paramFbclid === $cookieFbclid) {
            return [
                'ok' => true,
                'status' => 'match',
                'cookie_fbclid' => $cookieFbclid,
                'message' => '匹配',
            ];
        }

        return [
            'ok' => false,
            'status' => 'mismatch',
            'cookie_fbclid' => $cookieFbclid,
            'message' => 'Params.fbclid 与 Cookie._fbc 中的 fbclid 不一致',
        ];
    }

    protected function extractFbclidFromFbc($cookieFbc)
    {
        $cookieFbc = trim((string)$cookieFbc);
        if ($cookieFbc === '') {
            return '';
        }

        $parts = explode('.', $cookieFbc, 4);
        if (count($parts) === 4 && $parts[0] === 'fb' && in_array($parts[1], ['1', '2'], true)) {
            return trim((string)$parts[3]);
        }

        return $cookieFbc;
    }

    protected function incrementBucket(array &$bucket, $key)
    {
        $bucket[$key] = ($bucket[$key] ?? 0) + 1;
    }

    protected function splitCsv($value)
    {
        return array_values(array_filter(array_map('trim', explode(',', (string)$value)), function ($item) {
            return $item !== '';
        }));
    }

    protected function normalizeDateBoundary($value, $endOfDay = false)
    {
        $value = trim((string)$value);
        if ($value === '') {
            return '';
        }

        $value = substr(str_replace('T', ' ', $value), 0, 10);
        return $value . ($endOfDay ? ' 23:59:59' : ' 00:00:00');
    }

    protected function decodeJson($json)
    {
        $data = json_decode((string)$json, true);
        return is_array($data) ? $data : [];
    }

    protected function analyzeMarketingParams(array $params)
    {
        $analysis = [
            'flat' => [],
            'categories' => [],
            'primary' => [
                'channel' => '',
                'utm_source' => '',
                'utm_medium' => '',
                'utm_campaign' => '',
                'campaign' => '',
                'adgroup' => '',
                'creative' => '',
                'pixel' => '',
                'fbclid' => '',
            ],
        ];

        foreach ($params as $key => $value) {
            if (is_array($value) || is_object($value)) {
                $value = json_encode($value, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
            }

            $key = trim((string)$key);
            $value = trim((string)$value);
            if ($key === '' || $value === '') {
                continue;
            }

            $category = $this->detectParamCategory($key);

            $analysis['flat'][$key] = $value;
            $analysis['categories'][$category][] = [
                'key' => $key,
                'value' => $value,
            ];
        }

        $analysis['primary']['channel'] = $analysis['flat']['c'] ?? ($analysis['flat']['channel'] ?? '');
        $analysis['primary']['utm_source'] = $analysis['flat']['utm_source'] ?? '';
        $analysis['primary']['utm_medium'] = $analysis['flat']['utm_medium'] ?? '';
        $analysis['primary']['utm_campaign'] = $analysis['flat']['utm_campaign'] ?? '';
        $analysis['primary']['campaign'] = $analysis['flat']['campaign'] ?? ($analysis['flat']['utm_campaign'] ?? '');
        $analysis['primary']['adgroup'] = $analysis['flat']['adgroup'] ?? ($analysis['flat']['adset'] ?? ($analysis['flat']['utm_term'] ?? ''));
        $analysis['primary']['creative'] = $analysis['flat']['creative'] ?? ($analysis['flat']['utm_content'] ?? '');
        $analysis['primary']['pixel'] = $analysis['flat']['pixel'] ?? ($analysis['flat']['pixelID'] ?? ($analysis['flat']['pixel_id'] ?? ''));
        $analysis['primary']['fbclid'] = $analysis['flat']['fbclid'] ?? '';

        return $analysis;
    }

    protected function detectParamCategory($key)
    {
        $normalizedKey = strtolower(trim((string)$key));

        if (strpos($normalizedKey, 'utm_') === 0) {
            return 'utm';
        }

        if (in_array($normalizedKey, ['campaign', 'adgroup', 'adset', 'ad_id', 'creative', 'creative_id', 'placement', 'site_source_name'], true)) {
            return 'campaign';
        }

        if (in_array($normalizedKey, ['fbclid', 'gclid', 'ttclid', 'msclkid', 'wbraid', 'gbraid', 'fbc', 'fbp', '_fbc', '_fbp', 'pixel', 'pixelid', 'pixel_id'], true)) {
            return 'attribution';
        }

        if (in_array($normalizedKey, ['c', 'channel', 'source', 'media_source', 'utm_source_platform', 'pid', 'af_channel', 'sub_channel'], true)) {
            return 'channel';
        }

        if (preg_match('/(_id|id)$/', $normalizedKey)) {
            return 'identifier';
        }

        return 'custom';
    }

    protected function getParamCategoryLabel($category)
    {
        $labels = [
            'utm' => 'UTM',
            'campaign' => 'Campaign',
            'attribution' => 'Attribution',
            'channel' => 'Channel',
            'identifier' => 'Identifier',
            'custom' => 'Custom',
        ];

        return $labels[$category] ?? ucfirst($category);
    }

    protected function getCategoryKeys($category)
    {
        $map = [
            'utm' => ['utm_source', 'utm_medium', 'utm_campaign', 'utm_content', 'utm_term', 'utm_id', 'utm_source_platform'],
            'campaign' => ['campaign', 'adgroup', 'adset', 'ad_id', 'creative', 'creative_id', 'placement', 'site_source_name'],
            'attribution' => ['fbclid', 'gclid', 'ttclid', 'msclkid', 'wbraid', 'gbraid', 'fbc', 'fbp', '_fbc', '_fbp', 'pixel', 'pixelID', 'pixel_id'],
            'channel' => ['c', 'channel', 'source', 'media_source', 'pid', 'af_channel', 'sub_channel'],
            'identifier' => ['utm_id', 'campaign_id', 'adgroup_id', 'adset_id', 'creative_id', 'pixel_id', 'pixelID'],
        ];

        return $map[$category] ?? [];
    }

    protected function truncateParamValue($value, $length = 36)
    {
        $value = (string)$value;
        if ($value === '') {
            return '-';
        }

        return mb_strlen($value) > $length ? mb_substr($value, 0, $length) . '...' : $value;
    }

    protected function makeCookieKey($row)
    {
        if (!empty($row->FPID)) {
            return 'fpid:' . $row->FPID;
        }
        if (!empty($row->FF)) {
            return 'ff:' . $row->FF;
        }
        if (!empty($row->UserID)) {
            return 'uid:' . $row->UserID;
        }
        return 'row:' . ($row->ID ?? '');
    }

    protected function parseCookieString($cookieString)
    {
        $cookies = [];
        foreach (explode(';', (string)$cookieString) as $part) {
            $part = trim($part);
            if ($part === '' || strpos($part, '=') === false) {
                continue;
            }
            [$name, $value] = explode('=', $part, 2);
            $cookies[trim($name)] = trim($value);
        }
        return $cookies;
    }

    protected function analyzeUserAgent($ua)
    {
        $ua = (string)$ua;
        $app = 'Browser';
        if (stripos($ua, 'Instagram') !== false) {
            $app = 'Instagram';
        } elseif (stripos($ua, 'FBAN') !== false || stripos($ua, 'FBAV') !== false || stripos($ua, 'Facebook') !== false) {
            $app = 'Facebook';
        }

        $os = 'Unknown';
        if (stripos($ua, 'iPhone') !== false || stripos($ua, 'iPad') !== false || stripos($ua, 'iOS') !== false) {
            $os = 'iOS';
        } elseif (stripos($ua, 'Android') !== false) {
            $os = 'Android';
        }

        $device = 'Unknown';
        if (stripos($ua, 'iPhone') !== false) {
            $device = 'iPhone';
        } elseif (stripos($ua, 'iPad') !== false) {
            $device = 'iPad';
        } elseif (stripos($ua, 'Android') !== false) {
            $device = 'Android';
        }

        return compact('app', 'os', 'device');
    }

    protected function loadPaidUserCount(array $userIds)
    {
        if (empty($userIds)) {
            return 0;
        }

        return DB::connection('read')
            ->table('agent.dbo.order')
            ->whereIn('user_id', $userIds)
            ->where('pay_status', 1)
            ->distinct('user_id')
            ->count('user_id');
    }

    protected function loadPayStats(array $userIds)
    {
        if (empty($userIds)) {
            return [];
        }

        return DB::connection('read')
            ->table('agent.dbo.order')
            ->whereIn('user_id', $userIds)
            ->where('pay_status', 1)
            ->groupBy('user_id')
            ->selectRaw('user_id, count(*) as pay_order_count, cast(sum(amount) as decimal(18,2)) as pay_amount_sum, max(pay_at) as last_pay_at')
            ->get()
            ->keyBy('user_id')
            ->all();
    }

    protected function loadAdjustEventStats(array $userIds, array $filters)
    {
        if (empty($userIds)) {
            return [];
        }

        $result = [];
        $userIdMap = array_fill_keys(array_map('strval', $userIds), true);

        foreach ($this->resolveAdjustLogFiles($filters) as $logFile) {
            $handle = @fopen($logFile, 'r');
            if ($handle === false) {
                continue;
            }

            $currentUserId = null;

            while (($line = fgets($handle)) !== false) {
                $userId = $this->extractAdjustUserId($line);
                $status = $this->classifyAdjustLine($line);

                if ($userId !== null && isset($userIdMap[(string)$userId])) {
                    $currentUserId = $userId;
                } elseif ($userId === null && $currentUserId !== null && in_array($status, ['request', 'success', 'skipped', 'failed'], true)) {
                    $userId = $currentUserId;
                } else {
                    if ($status === 'enter') {
                        $currentUserId = null;
                    }
                    continue;
                }

                if (!isset($result[$userId])) {
                    $result[$userId] = ['status' => 'enter', 'logs' => []];
                }

                if ($status !== null) {
                    $result[$userId]['status'] = $status;
                }

                if (count($result[$userId]['logs']) < 50) {
                    $result[$userId]['logs'][] = trim($line);
                }
            }

            fclose($handle);
        }

        return $result;
    }

    protected function resolveAdjustLogFiles(array $filters)
    {
        $start = substr($filters['date_start'] ?: $filters['register_start'], 0, 10);
        $end = substr($filters['date_end'] ?: $filters['register_end'], 0, 10);
        if ($start === '') {
            $start = date('Y-m-d');
        }
        if ($end === '') {
            $end = $start;
        }

        $startTs = strtotime($start);
        $endTs = strtotime($end);
        if ($startTs === false || $endTs === false) {
            return [];
        }
        if ($endTs < $startTs) {
            [$startTs, $endTs] = [$endTs, $startTs];
        }

        $files = [];
        $days = 0;
        for ($time = $startTs; $time <= $endTs && $days < 7; $time += 86400, $days++) {
            $file = storage_path('logs/adjustEvent-' . date('Y-m-d', $time) . '.log');
            if (file_exists($file)) {
                $files[] = $file;
            }
        }

        return $files;
    }

    protected function extractAdjustUserId($line)
    {
        if (preg_match('/"UserID":"?(\d+)"?/', $line, $matches)) {
            return (int)$matches[1];
        }
        if (preg_match('/"user_id":"?(\d+)"?/', $line, $matches)) {
            return (int)$matches[1];
        }
        return null;
    }

    protected function classifyAdjustLine($line)
    {
        if (strpos($line, 'facebook s2s response') !== false) {
            return 'success';
        }
        if (strpos($line, 'facebook s2s request') !== false) {
            return 'request';
        }
        if (strpos($line, 'facebook s2s skipped') !== false) {
            return 'skipped';
        }
        if (strpos($line, 'facebook s2s failed') !== false) {
            return 'failed';
        }
        if (strpos($line, 'enter:') !== false) {
            return 'enter';
        }
        return null;
    }

    protected function extractChannelFromCookie($userID, $user = null)
    {
        $cookieInfo = \App\Services\ApkService::loadCookie($userID, $user->FPID ?? '', $user->FF ?? '');
        if (!$cookieInfo || empty($cookieInfo['Params'])) {
            return null;
        }

        $params = json_decode($cookieInfo['Params'], true);
        if (!is_array($params)) {
            return null;
        }

        return $params['c'] ?? null;
    }
}