<?php

namespace App\Http\Controllers\Admin;

use App\Http\Controllers\Controller;
use App\Models\ActivationHistory;
use App\Models\ActivationRequest;
use App\Models\BalanceLog;
use App\Models\FixedCommissionSetting;
use App\Models\IncomeHistory;
use App\Models\User;
use App\Services\ActivationHistoryService;
use App\Services\FixedCommissionDistributor;
use Illuminate\Database\QueryException;
use Illuminate\Http\Request;
use Illuminate\Pagination\LengthAwarePaginator;
use Illuminate\Support\Carbon;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Validation\ValidationException;

class ActivationRequestAdminController extends Controller
{
    private const VERIFIED_HISTORY_EVENTS = [
        'voucher_verified',
        'direct_payment_verified',
        'admin_request_approved',
        'admin_manual_verified',
    ];

    private const REJECTED_HISTORY_EVENTS = [
        'admin_request_rejected',
        'admin_manual_unverified',
    ];

    private const AUTO_VERIFIED_CHANNELS = [
        'voucher',
        'direct_payment',
    ];

    private const AUTO_VERIFIED_REQUEST_STATES = [
        'no_request_auto_verified',
        'linked_pending_request_auto_approved',
        'auto_verified_after_payment_success',
        'auto_verified_after_payment_webhook',
    ];

    private ActivationHistoryService $activationHistoryService;
    private FixedCommissionDistributor $fixedCommissionDistributor;

    public function __construct(
        ActivationHistoryService $activationHistoryService,
        ?FixedCommissionDistributor $fixedCommissionDistributor = null
    )
    {
        $this->activationHistoryService = $activationHistoryService;
        $this->fixedCommissionDistributor = $fixedCommissionDistributor ?? app(FixedCommissionDistributor::class);
    }

    public function index(Request $request)
    {
        $this->syncMissingActivationHistories();

        $lookupReferral = trim((string) $request->query('lookup_referral', ''));
        $lookupUser = null;
        $lookupError = null;
        $historySummary = $this->buildGlobalHistorySummary();

        if ($lookupReferral !== '') {
            $lookupColumns = ['id', 'name', 'email', 'mobile', 'referral_code', 'is_active', 'activated_at'];
            if (Schema::hasColumn('users', 'typing_job_referral_enabled')) {
                $lookupColumns[] = 'typing_job_referral_enabled';
            }
            if (Schema::hasColumn('users', 'typing_job_referral_enabled_at')) {
                $lookupColumns[] = 'typing_job_referral_enabled_at';
            }
            if (Schema::hasColumn('users', 'welcome_offer_referral_enabled')) {
                $lookupColumns[] = 'welcome_offer_referral_enabled';
            }
            if (Schema::hasColumn('users', 'welcome_offer_referral_enabled_at')) {
                $lookupColumns[] = 'welcome_offer_referral_enabled_at';
            }

            $lookupUser = $this->applyHiddenUserScope(User::query())
                ->whereRaw('LOWER(referral_code) = ?', [strtolower($lookupReferral)])
                ->first($lookupColumns);

            if (!$lookupUser) {
                $lookupError = 'এই referral code-এর কোনো account পাওয়া যায়নি।';
            }
        }

        return view('admin.activation_requests.index', compact(
            'historySummary',
            'lookupReferral',
            'lookupUser',
            'lookupError'
        ));
    }

    public function history(Request $request)
    {
        $this->syncMissingActivationHistories();

        $status = in_array($request->query('status'), ['all', 'approved', 'rejected'], true)
            ? $request->query('status')
            : 'all';
        $historySearch = trim((string) $request->query('history_search', ''));

        if (Schema::hasTable('activation_histories')) {
            $historyQuery = $this->historyQueryByStatus($status, $historySearch);
            $summaryHistories = (clone $historyQuery)->get([
                'id',
                'user_id',
                'event',
                'verification_channel',
                'metadata',
                'activation_request_id',
                'happened_at',
                'created_at',
            ]);

            $histories = $historyQuery
                ->paginate(20, ['*'], 'history_page')
                ->appends($request->query());
        } else {
            $histories = new LengthAwarePaginator(
                collect(),
                0,
                20,
                1,
                [
                    'path' => $request->url(),
                    'pageName' => 'history_page',
                ]
            );
            $summaryHistories = collect();
        }

        [$histories] = $this->decorateHistories($histories);
        $historySummary = $status === 'all'
            ? $this->buildAllHistorySummaryFromHistories($summaryHistories)
            : $this->buildHistorySummary($summaryHistories);

        return view('admin.activation_requests.history', compact(
            'status',
            'histories',
            'historySummary',
            'historySearch'
        ));
    }

    public function approve(ActivationRequest $activationRequest)
    {
        if ($activationRequest->status !== 'pending') {
            return back()->with('error', 'এই রিকোয়েস্ট ইতোমধ্যে রিভিউ করা হয়েছে।');
        }

        DB::transaction(function () use ($activationRequest) {
            $activationRequest->update([
                'status' => 'approved',
                'reviewed_at' => Carbon::now(),
                'reviewed_by' => auth('admin')->id(),
            ]);

            $userUpdates = ['is_active' => 1];
            if (Schema::hasColumn('users', 'activated_at')) {
                $userUpdates['activated_at'] = now();
            }

            User::where('id', $activationRequest->user_id)->update($userUpdates);

            $this->activationHistoryService->record([
                'user_id' => (int) $activationRequest->user_id,
                'activation_request_id' => (int) $activationRequest->id,
                'admin_id' => auth('admin')->id(),
                'event' => 'admin_request_approved',
                'status' => 'approved',
                'verification_channel' => 'admin_manual',
                'source' => 'admin_activation_panel',
                'payment_method' => null,
                'amount' => 0,
                'note' => 'Activation request approved manually from admin panel.',
                'metadata' => [
                    'request_status' => 'approved',
                    'commission_action' => 'skipped',
                    'hidden_reward_eligible' => false,
                ],
                'happened_at' => now(),
            ]);
        });

        return back()->with('success', 'Approved ✅ User activated.');
    }

    public function reject(Request $request, ActivationRequest $activationRequest)
    {
        if ($activationRequest->status !== 'pending') {
            return back()->with('error', 'এই রিকোয়েস্ট ইতোমধ্যে রিভিউ করা হয়েছে।');
        }

        $request->validate([
            'admin_note' => ['nullable','string','max:500'],
        ]);

        $activationRequest->update([
            'status' => 'rejected',
            'admin_note' => $request->admin_note,
            'reviewed_at' => now(),
            'reviewed_by' => auth('admin')->id(),
        ]);

        $this->activationHistoryService->record([
            'user_id' => (int) $activationRequest->user_id,
            'activation_request_id' => (int) $activationRequest->id,
            'admin_id' => auth('admin')->id(),
            'event' => 'admin_request_rejected',
            'status' => 'rejected',
            'verification_channel' => 'admin_manual',
            'source' => 'admin_activation_panel',
            'payment_method' => null,
            'amount' => 0,
            'note' => $request->admin_note ?: 'Activation request rejected manually from admin panel.',
            'metadata' => [
                'request_status' => 'rejected',
            ],
            'happened_at' => now(),
        ]);

        return back()->with('success', 'Rejected ✅ User remains inactive.');
    }

    public function manualStatusUpdate(Request $request)
    {
        $data = $request->validate([
            'user_id' => ['required', 'integer', 'exists:users,id'],
            'action' => ['required', 'in:verify,unverify'],
            'commission_mode' => ['required', 'in:give,skip,remove,keep'],
            'transaction_reference' => ['nullable', 'string', 'max:191'],
            'transaction_reference_fallback' => ['nullable', 'string', 'max:191'],
            'typing_job_mode' => ['nullable', 'string', 'in:enable,disable', 'required_if:action,verify'],
            'welcome_offer_mode' => ['nullable', 'string', 'in:enable,disable', 'required_if:action,verify'],
            'leadership_count_mode' => ['nullable', 'string', 'in:enable,disable', 'required_if:action,verify'],
            'typing_job_unverify_mode' => ['nullable', 'string', 'in:disable,keep', 'required_if:action,unverify'],
            'welcome_offer_unverify_mode' => ['nullable', 'string', 'in:disable,keep', 'required_if:action,unverify'],
            'leadership_unverify_mode' => ['nullable', 'string', 'in:remove,keep', 'required_if:action,unverify'],
        ]);

        $user = User::findOrFail((int) $data['user_id']);

        if ($data['action'] === 'verify') {
            if ((int) $user->is_active === 1) {
                return back()->with('error', 'এই account ইতোমধ্যে verified আছে।');
            }

            if (!in_array($data['commission_mode'], ['give', 'skip'], true)) {
                return back()->withErrors([
                    'commission_mode' => 'ম্যানুয়াল verify-এর জন্য সঠিক verify option বেছে নিন।',
                ])->withInput();
            }

            $transactionReference = trim((string) ($data['transaction_reference'] ?? ''));
            if ($transactionReference === '') {
                $transactionReference = trim((string) ($data['transaction_reference_fallback'] ?? ''));
            }

            try {
                $message = $this->verifyUserManually(
                    $user,
                    $data['commission_mode'],
                    $transactionReference,
                    (string) ($data['typing_job_mode'] ?? 'disable'),
                    (string) ($data['welcome_offer_mode'] ?? 'enable'),
                    (string) ($data['leadership_count_mode'] ?? 'enable')
                );
            } catch (ValidationException $exception) {
                return back()->withErrors($exception->errors())->withInput();
            }

            return back()->with('success', $message);
        }

        if (!in_array($data['commission_mode'], ['remove', 'keep'], true)) {
            return back()->withErrors([
                'commission_mode' => 'ম্যানুয়াল non-verify-এর জন্য সঠিক option বেছে নিন।',
            ])->withInput();
        }

        $typingJobUnverifyMode = (string) ($data['typing_job_unverify_mode'] ?? 'disable');
        $welcomeOfferUnverifyMode = (string) ($data['welcome_offer_unverify_mode'] ?? 'disable');
        $leadershipUnverifyMode = (string) ($data['leadership_unverify_mode'] ?? 'remove');

        if ((int) $user->is_active !== 1) {
            if ($data['commission_mode'] === 'remove') {
                $result = $this->removeOutstandingCommissionForInactiveUser(
                    $user,
                    $typingJobUnverifyMode,
                    $welcomeOfferUnverifyMode,
                    $leadershipUnverifyMode
                );

                return back()->with($result['status'], $result['message']);
            }

            return back()->with('error', 'এই account এখন verified না, তাই non-verify করা যাবে না।');
        }

        $message = $this->unverifyUserManually(
            $user,
            $data['commission_mode'],
            $typingJobUnverifyMode,
            $welcomeOfferUnverifyMode,
            $leadershipUnverifyMode
        );

        return back()->with('success', $message);
    }

    private function decorateHistories(LengthAwarePaginator $histories): array
    {
        $items = collect($histories->items());
        $paymentMap = $this->activationPaymentMap($items);

        $decorated = $items->map(function (ActivationHistory $history) use ($paymentMap) {
            $history->derived = $this->deriveHistoryDetails($history, $paymentMap[$history->transaction_id] ?? null);

            return $history;
        });

        $histories->setCollection($decorated);

        return [$histories, $this->buildHistorySummary($decorated)];
    }

    private function activationPaymentMap(Collection $histories): array
    {
        if (!Schema::hasTable('payments')) {
            return [];
        }

        $transactionIds = $histories
            ->pluck('transaction_id')
            ->filter(fn ($id) => filled($id))
            ->unique()
            ->values();

        if ($transactionIds->isEmpty()) {
            return [];
        }

        return DB::table('payments')
            ->whereIn('transaction_id', $transactionIds->all())
            ->select(['transaction_id', 'payment_method', 'trx_id', 'amount', 'status', 'raw_response', 'created_at', 'updated_at'])
            ->get()
            ->keyBy('transaction_id')
            ->map(fn ($row) => (array) $row)
            ->all();
    }

    private function deriveHistoryDetails(ActivationHistory $history, ?array $paymentRow): array
    {
        $metadata = is_array($history->metadata) ? $history->metadata : [];
        $paymentRaw = $this->jsonDecodeToArray($paymentRow['raw_response'] ?? null);
        $gatewayPayload = $this->extractGatewayPayload($metadata, $paymentRaw);
        $paymentMobile = $metadata['payment_mobile']
            ?? $metadata['account_mobile']
            ?? $this->extractMobileFromMixed($gatewayPayload)
            ?? $this->extractMobileFromMixed($paymentRaw)
            ?? $history->user?->mobile
            ?? $history->activationRequest?->mobile;

        $verifiedAt = $history->happened_at ?: $history->created_at;
        $weekLabel = $verifiedAt
            ? $verifiedAt->copy()->startOfWeek()->format('d M') . ' - ' . $verifiedAt->copy()->endOfWeek()->format('d M Y')
            : '—';
        $requestState = $metadata['request_state']
            ?? ($history->activation_request_id ? 'linked_request' : 'no_request_auto_verified');
        $autoVerified = $this->isAutoVerifiedHistory($history, $metadata);
        $repairOnly = (bool) ($metadata['repair_only'] ?? false);
        $legacyBackfill = (bool) ($metadata['legacy_backfill'] ?? false);
        $autoApprovedRequest = (string) $history->event === 'admin_request_approved' && $autoVerified;

        return [
            'event_label' => $this->historyEventLabel((string) $history->event, $repairOnly, $autoApprovedRequest, $legacyBackfill),
            'trail_label' => $this->historyTrailLabel($history, $metadata),
            'selected_option_label' => $this->historyOptionLabel($metadata['selected_option'] ?? null),
            'previous_event_label' => $this->historyEventLabel((string) ($metadata['previous_verification_event'] ?? '')),
            'account_name' => $metadata['account_name'] ?? $history->user?->name ?? 'N/A',
            'account_mobile' => $metadata['account_mobile'] ?? $history->user?->mobile ?? $history->activationRequest?->mobile,
            'account_email' => $metadata['account_email'] ?? $history->user?->email,
            'account_referral_code' => $metadata['account_referral_code'] ?? $history->user?->referral_code ?? $history->activationRequest?->referral_code,
            'account_status' => (int) ($history->user?->is_active ?? 0) === 1 ? 'Verified' : 'Inactive',
            'payment_mobile' => $paymentMobile,
            'payment_status' => $metadata['payment_status'] ?? $paymentRow['status'] ?? $history->status,
            'payment_amount' => round((float) ($paymentRow['amount'] ?? $history->amount ?? 0), 2),
            'payment_gateway' => $history->payment_method ?? ($paymentRow['payment_method'] ?? null),
            'payment_transaction_id' => $history->transaction_id ?: ($paymentRow['transaction_id'] ?? null),
            'payment_trx_id' => $history->trx_id ?: ($paymentRow['trx_id'] ?? null),
            'request_state' => $requestState,
            'request_status' => $history->activationRequest?->status,
            'request_mobile' => $history->activationRequest?->mobile,
            'request_referral_code' => $history->activationRequest?->referral_code,
            'auto_verified' => $autoVerified,
            'repair_only' => $repairOnly,
            'commission_action' => $metadata['commission_action'] ?? null,
            'week_label' => $weekLabel,
            'verified_at_label' => $verifiedAt ? $verifiedAt->format('d M Y, h:i A') : '—',
            'voucher_balance_before' => $metadata['voucher_balance_before'] ?? null,
            'voucher_balance_after' => $metadata['voucher_balance_after'] ?? null,
        ];
    }

    private function historyEventLabel(
        string $event,
        bool $repairOnly = false,
        bool $autoApprovedRequest = false,
        bool $legacyBackfill = false
    ): string
    {
        return match ($event) {
            'voucher_verified' => 'ভাউচার দিয়ে ভেরিফাই',
            'direct_payment_verified' => 'ডিরেক্ট পেমেন্ট ভেরিফাই',
            'admin_manual_verified' => $legacyBackfill ? 'পুরনো ভেরিফাই sync' : 'অ্যাডমিন ম্যানুয়াল ভেরিফাই',
            'admin_manual_unverified' => $repairOnly ? 'কমিশন রিপেয়ার' : 'অ্যাডমিন ম্যানুয়াল নন-ভেরিফাই',
            'admin_request_approved' => $autoApprovedRequest ? 'অটো রিকোয়েস্ট approved' : 'অ্যাডমিন রিকোয়েস্ট approved',
            'admin_request_rejected' => 'অ্যাডমিন রিকোয়েস্ট rejected',
            '' => '—',
            default => ucwords(str_replace('_', ' ', $event)),
        };
    }

    private function historyTrailLabel(ActivationHistory $history, array $metadata): string
    {
        return match ($history->event) {
            'voucher_verified' => 'ভাউচার ব্যালেন্স থেকে অটো ভেরিফাই হয়েছে।',
            'direct_payment_verified' => ($history->source === 'payment_webhook'
                ? 'পেমেন্ট webhook confirm হওয়ার পরে অটো ভেরিফাই হয়েছে।'
                : 'পেমেন্ট success confirm হওয়ার পরে অটো ভেরিফাই হয়েছে।'),
            'admin_request_approved' => !empty($metadata['auto_verified'])
                ? 'Pending activation request অটো approved হয়ে account verify হয়েছে।'
                : 'Pending activation request অ্যাডমিন approve করে account verify করেছেন।',
            'admin_request_rejected' => 'Pending activation request অ্যাডমিন reject করেছেন।',
            'admin_manual_verified' => !empty($metadata['legacy_backfill'])
                ? 'পুরনো verified account history sync করা হয়েছে। original verification source পাওয়া যায়নি।'
                : (($metadata['commission_action'] ?? null) === 'given'
                    ? 'অ্যাডমিন ম্যানুয়ালি verify করে referral commission দিয়েছেন। ' . $this->typingJobReferralHistoryLabel($metadata) . ' ' . $this->welcomeOfferReferralHistoryLabel($metadata)
                    : 'অ্যাডমিন ম্যানুয়ালি verify করেছেন, তবে referral commission skipped করেছেন। ' . $this->typingJobReferralHistoryLabel($metadata) . ' ' . $this->welcomeOfferReferralHistoryLabel($metadata)),
            'admin_manual_unverified' => !empty($metadata['repair_only'])
                ? $this->repairOnlyHistoryLabel($metadata)
                : (($metadata['commission_action'] ?? null) === 'removed'
                    ? 'অ্যাডমিন ম্যানুয়ালি non-verify করে referral commission remove করেছেন। ' . $this->typingJobReferralHistoryLabel($metadata) . ' ' . $this->welcomeOfferReferralHistoryLabel($metadata)
                    : (($metadata['commission_action'] ?? null) === 'kept'
                        ? 'অ্যাডমিন ম্যানুয়ালি non-verify করেছেন, তবে commission রাখা হয়েছে। ' . $this->typingJobReferralHistoryLabel($metadata) . ' ' . $this->welcomeOfferReferralHistoryLabel($metadata)
                        : 'অ্যাডমিন ম্যানুয়ালি non-verify trail record হয়েছে।')),
            default => $history->note ?: '—',
        };
    }

    private function historyOptionLabel(?string $option): string
    {
        return match ($option) {
            'give' => 'ভেরিফাই + কমিশন দিন',
            'skip' => 'ভেরিফাই + কমিশন skip',
            'remove' => 'নন-ভেরিফাই + কমিশন remove',
            'keep' => 'নন-ভেরিফাই + কমিশন রাখুন',
            null, '' => '—',
            default => ucwords(str_replace('_', ' ', $option)),
        };
    }

    private function typingJobReferralHistoryLabel(array $metadata): string
    {
        return match ($metadata['typing_job_referral_action'] ?? null) {
            'enabled' => 'Typing task referrer/upline-এর জন্য চালু করা হয়েছে।',
            'disabled' => 'Typing task referrer/upline-এর জন্য চালু করা হয়নি।',
            'kept' => 'Typing task referrer/upline setting আগের মতোই রাখা হয়েছে।',
            default => '',
        };
    }

    private function welcomeOfferReferralHistoryLabel(array $metadata): string
    {
        return match ($metadata['welcome_offer_referral_action'] ?? null) {
            'enabled' => 'Welcome offer count referrer/upline-এর জন্য চালু করা হয়েছে।',
            'disabled' => 'Welcome offer count referrer/upline-এর জন্য চালু করা হয়নি।',
            'kept' => 'Welcome offer count setting আগের মতোই রাখা হয়েছে।',
            default => '',
        };
    }

    private function repairOnlyHistoryLabel(array $metadata): string
    {
        $commissionLabel = match ($metadata['commission_action'] ?? null) {
            'removed' => 'আগে non-verified account-এ outstanding commission detect করে repair/reversal করা হয়েছে।',
            'remove_requested_but_not_found' => 'আগে non-verified account-এ outstanding commission detect করা যায়নি, তবে repair check চালানো হয়েছে।',
            default => 'আগে non-verified account-এ repair trail record করা হয়েছে।',
        };

        return trim(
            $commissionLabel
            . ' '
            . $this->typingJobReferralHistoryLabel($metadata)
            . ' '
            . $this->welcomeOfferReferralHistoryLabel($metadata)
        );
    }

    private function buildHistorySummary(Collection $histories): array
    {
        $completed = $this->currentVerifiedHistories($histories);
        $now = now();

        return [
            'summary_mode' => 'verified',
            'total_verified' => $completed->count(),
            'voucher_verified' => $completed->where('verification_channel', 'voucher')->count(),
            'direct_verified' => $completed->where('verification_channel', 'direct_payment')->count(),
            'this_week_verified' => $completed->filter(function ($h) use ($now) {
                $when = $h->happened_at ?: $h->created_at;
                return $when && $when->between($now->copy()->startOfWeek(), $now->copy()->endOfWeek());
            })->count(),
            'auto_verified' => $this->countAutoVerifiedHistories($completed),
            'manual_skip_verified' => $this->countManualSkipVerifiedHistories($completed),
        ];
    }

    private function buildGlobalHistorySummary(): array
    {
        if (!Schema::hasTable('activation_histories')) {
            return [
                'summary_mode' => 'verified',
                'total_verified' => 0,
                'voucher_verified' => 0,
                'direct_verified' => 0,
                'this_week_verified' => 0,
                'auto_verified' => 0,
                'manual_skip_verified' => 0,
            ];
        }

        $now = now();
        $weekStart = $now->copy()->startOfWeek();
        $weekEnd = $now->copy()->endOfWeek();

        $histories = ActivationHistory::query()
            ->whereIn('event', array_merge(self::VERIFIED_HISTORY_EVENTS, self::REJECTED_HISTORY_EVENTS))
            ->get([
                'id',
                'user_id',
                'event',
                'verification_channel',
                'metadata',
                'activation_request_id',
                'happened_at',
                'created_at',
            ]);

        $currentVerified = $this->currentVerifiedHistories($histories);

        return [
            'summary_mode' => 'verified',
            'total_verified' => $currentVerified->count(),
            'voucher_verified' => $currentVerified->where('verification_channel', 'voucher')->count(),
            'direct_verified' => $currentVerified->where('verification_channel', 'direct_payment')->count(),
            'this_week_verified' => $currentVerified->filter(function ($history) use ($weekStart, $weekEnd) {
                $when = $history->happened_at ?: $history->created_at;
                return $when && $when->between($weekStart, $weekEnd);
            })->count(),
            'auto_verified' => $this->countAutoVerifiedHistories($currentVerified),
            'manual_skip_verified' => $this->countManualSkipVerifiedHistories($currentVerified),
        ];
    }

    private function buildAllHistorySummary(): array
    {
        if (!Schema::hasTable('activation_histories')) {
            return [
                'summary_mode' => 'all',
                'total_trail' => 0,
                'total_verified' => 0,
                'auto_verified' => 0,
                'admin_verified' => 0,
                'manual_skip_verified' => 0,
                'non_verified' => 0,
                'repair_actions' => 0,
            ];
        }

        return $this->buildAllHistorySummaryFromHistories(
            ActivationHistory::query()->get([
                'id',
                'user_id',
                'event',
                'verification_channel',
                'metadata',
                'activation_request_id',
                'happened_at',
                'created_at',
            ])
        );
    }

    private function buildAllHistorySummaryFromHistories(Collection $histories): array
    {
        $verified = $this->currentVerifiedHistories($histories);
        $autoVerified = $this->countAutoVerifiedHistories($verified);
        $adminVerified = $this->countAdminVerifiedHistories($verified);
        $manualSkipVerified = $this->countManualSkipVerifiedHistories($verified);
        $nonVerified = $this->currentNonVerifiedHistories($histories);
        $repairActions = $histories
            ->filter(fn ($history) => in_array((string) $history->event, self::REJECTED_HISTORY_EVENTS, true))
            ->filter(fn ($history) => (bool) data_get($history->metadata, 'repair_only'))
            ->count();

        return [
            'summary_mode' => 'all',
            'total_trail' => $histories->count(),
            'total_verified' => $verified->count(),
            'auto_verified' => $autoVerified,
            'admin_verified' => $adminVerified,
            'manual_skip_verified' => $manualSkipVerified,
            'non_verified' => $nonVerified->count(),
            'repair_actions' => $repairActions,
        ];
    }

    private function countAdminVerifiedHistories(Collection $histories): int
    {
        return $histories
            ->reject(fn ($history) => $this->isAutoVerifiedHistory($history))
            ->count();
    }

    private function countManualSkipVerifiedHistories(Collection $histories): int
    {
        return $histories
            ->filter(function ($history) {
                if ((string) $history->event !== 'admin_manual_verified') {
                    return false;
                }

                $commissionAction = (string) data_get($history->metadata, 'commission_action', '');
                $selectedOption = (string) data_get($history->metadata, 'selected_option', '');

                return $commissionAction === 'skipped' || $selectedOption === 'skip';
            })
            ->count();
    }

    private function currentVerifiedHistories(Collection $histories): Collection
    {
        $latestVerified = $this->latestHistoryPerUser(
            $histories->filter(fn ($history) => in_array((string) $history->event, self::VERIFIED_HISTORY_EVENTS, true))
        );

        if ($latestVerified->isEmpty()) {
            return collect();
        }

        $activeMap = $this->userActiveStateMap($histories);
        $latestRelevant = $this->latestHistoryPerUser(
            $histories->filter(fn ($history) => $this->isRelevantVerificationHistory($history))
        )->keyBy(fn ($history) => (int) ($history->user_id ?? 0));

        return $latestVerified
            ->filter(function ($history) use ($activeMap, $latestRelevant) {
                $userId = (int) ($history->user_id ?? 0);

                if ($userId > 0 && array_key_exists($userId, $activeMap)) {
                    return $activeMap[$userId] === true;
                }

                $latest = $latestRelevant->get($userId);

                return $latest && in_array((string) $latest->event, self::VERIFIED_HISTORY_EVENTS, true);
            })
            ->values();
    }

    private function currentNonVerifiedHistories(Collection $histories): Collection
    {
        $latestRelevant = $this->latestHistoryPerUser(
            $histories->filter(fn ($history) => $this->isRelevantVerificationHistory($history))
        );

        if ($latestRelevant->isEmpty()) {
            return collect();
        }

        $activeMap = $this->userActiveStateMap($histories);

        return $latestRelevant
            ->filter(function ($history) use ($activeMap) {
                $userId = (int) ($history->user_id ?? 0);

                if ($userId > 0 && array_key_exists($userId, $activeMap)) {
                    return $activeMap[$userId] === false;
                }

                return in_array((string) $history->event, self::REJECTED_HISTORY_EVENTS, true);
            })
            ->values();
    }

    private function latestHistoryPerUser(Collection $histories): Collection
    {
        return $histories
            ->filter(fn ($history) => (int) ($history->user_id ?? 0) > 0)
            ->sortByDesc(fn ($history) => $this->historyRecencyKey($history))
            ->unique(fn ($history) => (int) ($history->user_id ?? 0))
            ->values();
    }

    private function userActiveStateMap(Collection $histories): array
    {
        $userIds = $histories
            ->pluck('user_id')
            ->map(fn ($userId) => (int) $userId)
            ->filter(fn ($userId) => $userId > 0)
            ->unique()
            ->values();

        if ($userIds->isEmpty()) {
            return [];
        }

        return User::query()
            ->whereIn('id', $userIds->all())
            ->pluck('is_active', 'id')
            ->map(fn ($isActive) => (int) $isActive === 1)
            ->all();
    }

    private function isRelevantVerificationHistory($history): bool
    {
        return in_array((string) $history->event, array_merge(self::VERIFIED_HISTORY_EVENTS, self::REJECTED_HISTORY_EVENTS), true);
    }

    private function historyRecencyKey($history): string
    {
        $when = $history->happened_at ?? $history->created_at;
        $timestamp = $when instanceof \DateTimeInterface ? $when->getTimestamp() : strtotime((string) $when);

        return sprintf('%020d-%010d', (int) ($timestamp ?: 0), (int) ($history->id ?? 0));
    }

    private function syncMissingActivationHistories(): void
    {
        $this->syncMissingReviewedRequestHistories();
        $this->syncMissingAutoVerificationHistories();
        $this->syncMissingVerifiedAccountsWithoutHistory();
    }

    private function syncMissingReviewedRequestHistories(): void
    {
        if (!Schema::hasTable('activation_histories') || !Schema::hasTable('activation_requests')) {
            return;
        }

        $requests = ActivationRequest::query()
            ->whereIn('status', ['approved', 'rejected'])
            ->whereNotNull('reviewed_by')
            ->orderBy('reviewed_at')
            ->orderBy('id')
            ->get([
                'id',
                'user_id',
                'mobile',
                'referral_code',
                'status',
                'admin_note',
                'reviewed_at',
                'reviewed_by',
                'created_at',
                'updated_at',
            ]);

        if ($requests->isEmpty()) {
            return;
        }

        $existingEvents = ActivationHistory::query()
            ->whereIn('activation_request_id', $requests->pluck('id')->all())
            ->whereIn('event', ['admin_request_approved', 'admin_request_rejected'])
            ->get(['activation_request_id', 'event'])
            ->groupBy('activation_request_id')
            ->map(fn (Collection $items) => $items->pluck('event')->all())
            ->all();

        $users = User::query()
            ->whereIn('id', $requests->pluck('user_id')->filter()->unique()->all())
            ->get(['id', 'name', 'email', 'mobile', 'referral_code'])
            ->keyBy('id');

        foreach ($requests as $activationRequest) {
            $event = $activationRequest->status === 'approved'
                ? 'admin_request_approved'
                : 'admin_request_rejected';

            if (in_array($event, $existingEvents[(int) $activationRequest->id] ?? [], true)) {
                continue;
            }

            /** @var User|null $user */
            $user = $users->get((int) $activationRequest->user_id);

            $this->activationHistoryService->record([
                'user_id' => (int) $activationRequest->user_id,
                'activation_request_id' => (int) $activationRequest->id,
                'admin_id' => $activationRequest->reviewed_by ? (int) $activationRequest->reviewed_by : null,
                'event' => $event,
                'status' => (string) $activationRequest->status,
                'verification_channel' => 'admin_manual',
                'source' => 'admin_activation_panel',
                'payment_method' => null,
                'amount' => 0,
                'note' => $activationRequest->admin_note ?: (
                    $event === 'admin_request_approved'
                        ? 'Legacy activation request approval synced into history.'
                        : 'Legacy activation request rejection synced into history.'
                ),
                'metadata' => [
                    'auto_verified' => false,
                    'request_status' => (string) $activationRequest->status,
                    'request_state' => 'linked_request',
                    'request_present' => true,
                    'account_name' => $user?->name,
                    'account_email' => $user?->email,
                    'account_mobile' => $user?->mobile ?? $activationRequest->mobile,
                    'account_referral_code' => $user?->referral_code ?? $activationRequest->referral_code,
                ],
                'happened_at' => $activationRequest->reviewed_at
                    ?? $activationRequest->updated_at
                    ?? $activationRequest->created_at
                    ?? now(),
            ]);
        }
    }

    private function syncMissingAutoVerificationHistories(): void
    {
        if (!Schema::hasTable('activation_histories') || !Schema::hasTable('payments')) {
            return;
        }

        $paymentRows = DB::table('payments')
            ->where('status', 'completed')
            ->whereNotNull('user_id')
            ->whereNotNull('transaction_id')
            ->whereNotExists(function ($query) {
                $query->select(DB::raw(1))
                    ->from('activation_histories')
                    ->whereColumn('activation_histories.transaction_id', 'payments.transaction_id');
            })
            ->orderBy('updated_at')
            ->orderBy('created_at')
            ->get([
                'user_id',
                'amount',
                'status',
                'payment_method',
                'transaction_id',
                'trx_id',
                'raw_response',
                'created_at',
                'updated_at',
            ]);

        if ($paymentRows->isEmpty()) {
            return;
        }

        $userIds = $paymentRows
            ->pluck('user_id')
            ->filter()
            ->map(fn ($id) => (int) $id)
            ->unique()
            ->values();

        if ($userIds->isEmpty()) {
            return;
        }

        $users = User::query()
            ->whereIn('id', $userIds->all())
            ->get(['id', 'name', 'email', 'mobile', 'referral_code'])
            ->keyBy('id');

        $activationRequestIdMap = $this->latestActivationRequestIdMap($userIds->all());
        $walletRechargeTransactionIds = $this->walletRechargeTransactionIdLookup(
            $paymentRows->pluck('transaction_id')->filter()->map(fn ($id) => (string) $id)->all()
        );
        $activationAmount = $this->activationAmountFromSettings();

        foreach ($paymentRows as $paymentRow) {
            $payment = (array) $paymentRow;

            if (!$this->isLegacyAutoVerificationPayment($payment, $walletRechargeTransactionIds, $activationAmount)) {
                continue;
            }

            /** @var User|null $user */
            $user = $users->get((int) $paymentRow->user_id);
            if (!$user) {
                continue;
            }

            $payload = $this->buildLegacyAutoVerificationHistoryPayload(
                $user,
                $payment,
                $activationRequestIdMap[(int) $paymentRow->user_id] ?? null
            );

            if ($payload === null) {
                continue;
            }

            $this->activationHistoryService->record($payload);
        }
    }

    private function syncMissingVerifiedAccountsWithoutHistory(): void
    {
        if (!Schema::hasTable('activation_histories') || !Schema::hasTable('users')) {
            return;
        }

        $verifiedHistoryUserIds = ActivationHistory::query()
            ->whereIn('event', self::VERIFIED_HISTORY_EVENTS)
            ->pluck('user_id')
            ->filter()
            ->unique()
            ->all();

        $users = User::query()
            ->where('is_active', 1)
            ->when($verifiedHistoryUserIds !== [], fn ($query) => $query->whereNotIn('id', $verifiedHistoryUserIds))
            ->orderBy('activated_at')
            ->orderBy('id')
            ->get([
                'id',
                'name',
                'email',
                'mobile',
                'referral_code',
                'activated_at',
                'created_at',
            ]);

        if ($users->isEmpty()) {
            return;
        }

        $requestMap = [];
        if (Schema::hasTable('activation_requests')) {
            $requestMap = ActivationRequest::query()
                ->whereIn('user_id', $users->pluck('id')->all())
                ->orderByDesc('id')
                ->get([
                    'id',
                    'user_id',
                    'mobile',
                    'referral_code',
                    'status',
                    'admin_note',
                    'reviewed_at',
                    'reviewed_by',
                    'created_at',
                    'updated_at',
                ])
                ->unique('user_id')
                ->keyBy('user_id')
                ->all();
        }

        foreach ($users as $user) {
            /** @var ActivationRequest|null $activationRequest */
            $activationRequest = $requestMap[(int) $user->id] ?? null;

            if ($activationRequest && $activationRequest->status === 'approved' && !$activationRequest->reviewed_by) {
                $this->activationHistoryService->record([
                    'user_id' => (int) $user->id,
                    'activation_request_id' => (int) $activationRequest->id,
                    'admin_id' => null,
                    'event' => 'admin_request_approved',
                    'status' => 'approved',
                    'verification_channel' => 'admin_manual',
                    'source' => 'legacy_activation_sync',
                    'payment_method' => null,
                    'amount' => 0,
                    'note' => 'Legacy auto-approved activation request synced into history.',
                    'metadata' => [
                        'auto_verified' => true,
                        'legacy_backfill' => true,
                        'request_status' => 'approved',
                        'request_state' => 'linked_pending_request_auto_approved',
                        'request_present' => true,
                        'account_name' => $user->name,
                        'account_email' => $user->email,
                        'account_mobile' => $user->mobile ?? $activationRequest->mobile,
                        'account_referral_code' => $user->referral_code ?? $activationRequest->referral_code,
                    ],
                    'happened_at' => $activationRequest->reviewed_at
                        ?? $user->activated_at
                        ?? $activationRequest->updated_at
                        ?? $activationRequest->created_at
                        ?? $user->created_at
                        ?? now(),
                ]);

                continue;
            }

            $this->activationHistoryService->record([
                'user_id' => (int) $user->id,
                'activation_request_id' => $activationRequest?->id,
                'admin_id' => $activationRequest?->reviewed_by ? (int) $activationRequest->reviewed_by : null,
                'event' => 'admin_manual_verified',
                'status' => 'completed',
                'verification_channel' => 'admin_manual',
                'source' => 'legacy_activation_sync',
                'payment_method' => null,
                'amount' => 0,
                'note' => 'Legacy verified account synced into history without original activation source.',
                'metadata' => [
                    'auto_verified' => false,
                    'legacy_backfill' => true,
                    'commission_action' => 'legacy_unknown',
                    'request_status' => $activationRequest?->status,
                    'request_state' => $activationRequest ? 'linked_request' : 'no_request_manual_verify',
                    'request_present' => $activationRequest !== null,
                    'account_name' => $user->name,
                    'account_email' => $user->email,
                    'account_mobile' => $user->mobile ?? $activationRequest?->mobile,
                    'account_referral_code' => $user->referral_code ?? $activationRequest?->referral_code,
                ],
                'happened_at' => $user->activated_at
                    ?? $activationRequest?->reviewed_at
                    ?? $activationRequest?->updated_at
                    ?? $activationRequest?->created_at
                    ?? $user->created_at
                    ?? now(),
            ]);
        }
    }

    private function latestActivationRequestIdMap(array $userIds): array
    {
        if ($userIds === [] || !Schema::hasTable('activation_requests')) {
            return [];
        }

        return ActivationRequest::query()
            ->whereIn('user_id', $userIds)
            ->orderByDesc('id')
            ->get(['id', 'user_id'])
            ->unique('user_id')
            ->mapWithKeys(fn (ActivationRequest $request) => [(int) $request->user_id => (int) $request->id])
            ->all();
    }

    private function walletRechargeTransactionIdLookup(array $transactionIds): array
    {
        if ($transactionIds === [] || !Schema::hasTable('voucher_transactions')) {
            return [];
        }

        return DB::table('voucher_transactions')
            ->where('source', 'wallet_addmoney_credit')
            ->where('reference_type', 'payment')
            ->whereIn('reference_id', $transactionIds)
            ->pluck('reference_id')
            ->mapWithKeys(fn ($transactionId) => [(string) $transactionId => true])
            ->all();
    }

    private function isLegacyAutoVerificationPayment(
        array $paymentRow,
        array $walletRechargeTransactionIds = [],
        ?float $activationAmount = null
    ): bool
    {
        if ((string) ($paymentRow['status'] ?? '') !== 'completed') {
            return false;
        }

        if (empty($paymentRow['user_id']) || empty($paymentRow['transaction_id'])) {
            return false;
        }

        $raw = $this->jsonDecodeToArray($paymentRow['raw_response'] ?? null);
        $purpose = $this->legacyPaymentPurpose($raw);
        $paymentMethod = strtolower((string) ($paymentRow['payment_method'] ?? ''));
        $transactionIdRaw = (string) ($paymentRow['transaction_id'] ?? '');
        $transactionId = strtoupper($transactionIdRaw);

        if ($purpose === 'wallet_recharge' || isset($walletRechargeTransactionIds[$transactionIdRaw])) {
            return false;
        }

        if (
            $paymentMethod === 'voucher'
            || ($raw['channel'] ?? null) === 'voucher'
            || str_starts_with($transactionId, 'ACT-VCH-')
        ) {
            return true;
        }

        if ($purpose === 'activation') {
            return true;
        }

        $activationAmount = round((float) ($activationAmount ?? 0), 2);
        $paymentAmount = round((float) ($paymentRow['amount'] ?? 0), 2);

        return $activationAmount > 0 && $paymentAmount === $activationAmount;
    }

    private function legacyPaymentPurpose(array $raw): ?string
    {
        foreach ([
            data_get($raw, 'purpose'),
            data_get($raw, 'metadata.purpose'),
            data_get($raw, 'verify.metadata.purpose'),
            data_get($raw, 'success_query.metadata.purpose'),
        ] as $purpose) {
            if (is_string($purpose) && trim($purpose) !== '') {
                return strtolower(trim($purpose));
            }
        }

        return null;
    }

    private function buildLegacyAutoVerificationHistoryPayload(User $user, array $paymentRow, ?int $activationRequestId): ?array
    {
        $raw = $this->jsonDecodeToArray($paymentRow['raw_response'] ?? null);
        $paymentMethod = strtolower((string) ($paymentRow['payment_method'] ?? ''));
        $transactionId = strtoupper((string) ($paymentRow['transaction_id'] ?? ''));
        $isVoucher = $paymentMethod === 'voucher'
            || ($raw['channel'] ?? null) === 'voucher'
            || str_starts_with($transactionId, 'ACT-VCH-');

        $requestState = $isVoucher
            ? ($activationRequestId ? 'linked_pending_request_auto_approved' : 'no_request_auto_verified')
            : (isset($raw['success_query'])
                ? 'auto_verified_after_payment_success'
                : (isset($raw['metadata']) || isset($raw['transaction_id']) || isset($raw['status'])
                    ? 'auto_verified_after_payment_webhook'
                    : ($activationRequestId ? 'linked_pending_request_auto_approved' : 'no_request_auto_verified')));

        $metadata = [
            'auto_verified' => true,
            'verification_channel' => $isVoucher ? 'voucher' : 'direct_payment',
            'commission_action' => 'given',
            'account_name' => $user->name,
            'account_email' => $user->email,
            'account_mobile' => $user->mobile,
            'account_referral_code' => $user->referral_code,
            'account_user_id' => (int) $user->id,
            'request_present' => $activationRequestId !== null,
            'request_state' => $requestState,
            'payment_mobile' => $this->extractMobileFromMixed($raw) ?? $user->mobile,
            'payment_status' => $paymentRow['status'] ?? 'completed',
            'legacy_backfill' => true,
        ];

        if ($isVoucher) {
            $metadata['voucher_balance_before'] = data_get($raw, 'voucher_balance_before');
            $metadata['voucher_balance_after'] = data_get($raw, 'voucher_balance_after');
        } else {
            $metadata['gateway_payload'] = $raw;
        }

        return [
            'user_id' => (int) $user->id,
            'activation_request_id' => $activationRequestId,
            'admin_id' => null,
            'event' => $isVoucher ? 'voucher_verified' : 'direct_payment_verified',
            'status' => 'completed',
            'verification_channel' => $isVoucher ? 'voucher' : 'direct_payment',
            'source' => $isVoucher
                ? 'user_activation_page'
                : (isset($raw['success_query']) ? 'payment_success_callback' : 'payment_webhook'),
            'payment_method' => $paymentRow['payment_method'] ?? ($isVoucher ? 'voucher' : null),
            'transaction_id' => $paymentRow['transaction_id'] ?? null,
            'trx_id' => $paymentRow['trx_id'] ?? null,
            'amount' => round((float) ($paymentRow['amount'] ?? 0), 2),
            'note' => $isVoucher
                ? 'Legacy voucher auto verification history synced from payment record.'
                : 'Legacy payment auto verification history synced from payment record.',
            'metadata' => $metadata,
            'happened_at' => $paymentRow['updated_at'] ?? $paymentRow['created_at'] ?? now(),
        ];
    }

    private function activationAmountFromSettings(): ?float
    {
        if (!Schema::hasTable('general_settings')) {
            return null;
        }

        $amount = DB::table('general_settings')->where('id', 1)->value('verify_fee');

        return $amount === null ? null : (float) $amount;
    }

    private function countAutoVerifiedHistories(Collection $histories): int
    {
        return $histories
            ->filter(fn (ActivationHistory $history) => $this->isAutoVerifiedHistory($history))
            ->count();
    }

    private function isAutoVerifiedHistory(ActivationHistory $history, ?array $metadata = null): bool
    {
        $metadata = $metadata ?? (is_array($history->metadata) ? $history->metadata : []);

        if (array_key_exists('auto_verified', $metadata)) {
            return (bool) $metadata['auto_verified'];
        }

        $requestState = $metadata['request_state'] ?? null;
        if (in_array($requestState, self::AUTO_VERIFIED_REQUEST_STATES, true)) {
            return true;
        }

        return in_array((string) $history->verification_channel, self::AUTO_VERIFIED_CHANNELS, true);
    }

    private function jsonDecodeToArray($value): array
    {
        if (is_array($value)) {
            return $value;
        }

        if (!is_string($value) || trim($value) === '') {
            return [];
        }

        $decoded = json_decode($value, true);

        return is_array($decoded) ? $decoded : [];
    }

    private function extractGatewayPayload(array $metadata, array $paymentRaw): array
    {
        $payload = $metadata['gateway_payload'] ?? [];
        if (is_array($payload) && !empty($payload)) {
            return $payload;
        }

        if (isset($paymentRaw['verify']) || isset($paymentRaw['success_query'])) {
            return $paymentRaw;
        }

        return [];
    }

    private function historyQueryByStatus(string $status, string $historySearch = '')
    {
        $query = ActivationHistory::query()
            ->with([
                'user:id,name,email,mobile,referral_code,is_active,activated_at',
                'admin:id,name,email',
                'activationRequest:id,status,mobile,referral_code,reviewed_at',
            ])
            ->orderByDesc('happened_at')
            ->orderByDesc('id');

        if ($status === 'rejected') {
            return $query->whereIn('event', self::REJECTED_HISTORY_EVENTS);
        }

        if ($status === 'approved') {
            $query->whereIn('event', self::VERIFIED_HISTORY_EVENTS);
        } elseif ($status === 'rejected') {
            $query->whereIn('event', self::REJECTED_HISTORY_EVENTS);
        }

        $this->applyHistorySearch($query, $historySearch);

        return $query;
    }

    private function applyHistorySearch($query, string $historySearch): void
    {
        $historySearch = trim($historySearch);
        if ($historySearch === '') {
            return;
        }

        $like = '%' . str_replace(['\\', '%', '_'], ['\\\\', '\%', '\_'], $historySearch) . '%';

        $query->where(function ($inner) use ($like) {
            $inner->whereHas('user', function ($userQuery) use ($like) {
                $userQuery
                    ->where('referral_code', 'like', $like)
                    ->orWhere('mobile', 'like', $like);
            });

            if (Schema::hasTable('activation_requests')) {
                $inner->orWhereHas('activationRequest', function ($requestQuery) use ($like) {
                    $requestQuery
                        ->where('referral_code', 'like', $like)
                        ->orWhere('mobile', 'like', $like);
                });
            }

            $inner
                ->orWhere('metadata->account_referral_code', 'like', $like)
                ->orWhere('metadata->account_mobile', 'like', $like)
                ->orWhere('metadata->request_referral_code', 'like', $like)
                ->orWhere('metadata->request_mobile', 'like', $like)
                ->orWhere('metadata->payment_mobile', 'like', $like);
        });
    }

    private function extractMobileFromMixed($mixed): ?string
    {
        if (is_string($mixed)) {
            if (preg_match('/\b01[3-9]\d{8}\b/', $mixed, $m)) {
                return $m[0];
            }

            return null;
        }

        if (!is_array($mixed)) {
            return null;
        }

        foreach ($mixed as $key => $value) {
            $normalized = strtolower((string) $key);

            if (
                in_array($normalized, ['mobile', 'mobile_number', 'number', 'phone', 'phone_number', 'customer_mobile', 'cus_phone', 'cus_mobile'], true)
                && is_string($value)
                && preg_match('/\b01[3-9]\d{8}\b/', $value, $m)
            ) {
                return $m[0];
            }
        }

        foreach ($mixed as $value) {
            $mobile = $this->extractMobileFromMixed($value);
            if ($mobile) {
                return $mobile;
            }
        }

        return null;
    }

    private function verifyUserManually(User $user, string $commissionMode, string $transactionReference, string $typingJobMode, string $welcomeOfferMode, string $leadershipCountMode): string
    {
        $adminId = auth('admin')->id();
        $latestPendingRequestId = $this->latestActivationRequestId((int) $user->id, 'pending');
        $giveCommission = $commissionMode === 'give';
        $enableTypingJobReferral = $typingJobMode === 'enable';
        $enableWelcomeOfferReferral = $welcomeOfferMode === 'enable';
        $enableLeadershipReferral = $leadershipCountMode === 'enable';
        $normalizedTransactionReference = $this->normalizeTransactionReference($transactionReference);
        $note = $giveCommission
            ? 'Admin verified the account manually and distributed referral commission. Transaction: ' . $normalizedTransactionReference
            : 'Admin verified the account manually without referral commission. Transaction: ' . $normalizedTransactionReference;

        $this->ensureTransactionReferenceAvailable($normalizedTransactionReference);

        DB::transaction(function () use ($user, $adminId, $latestPendingRequestId, $giveCommission, $note, $commissionMode, $normalizedTransactionReference, $enableTypingJobReferral, $enableWelcomeOfferReferral, $enableLeadershipReferral) {
            $lockedUser = User::query()->whereKey($user->id)->lockForUpdate()->firstOrFail();

            if ((int) $lockedUser->is_active === 1) {
                return;
            }

            $lockedUser->is_active = 1;
            if (Schema::hasColumn('users', 'activated_at')) {
                $lockedUser->activated_at = now();
            }
            $this->applyTypingJobReferralSetting($lockedUser, $enableTypingJobReferral);
            $this->applyWelcomeOfferReferralSetting($lockedUser, $enableWelcomeOfferReferral);
            $this->applyLeadershipReferralSetting($lockedUser, $enableLeadershipReferral);
            $lockedUser->save();

            if ($latestPendingRequestId) {
                $this->approvePendingRequests((int) $lockedUser->id, 'Approved from activation panel manual verify search.');
            }

            if ($giveCommission) {
                $this->adjustReferralCommissions($lockedUser, +1);
            }

            try {
                $this->activationHistoryService->record([
                    'user_id' => (int) $lockedUser->id,
                    'activation_request_id' => $latestPendingRequestId,
                    'admin_id' => $adminId,
                    'event' => 'admin_manual_verified',
                    'status' => 'completed',
                    'verification_channel' => 'admin_manual',
                    'source' => 'admin_activation_panel',
                    'payment_method' => 'manual_transaction',
                    'transaction_id' => $normalizedTransactionReference,
                    'trx_id' => $normalizedTransactionReference,
                    'amount' => 0,
                    'note' => $note,
                    'metadata' => [
                        'auto_verified' => false,
                        'commission_action' => $giveCommission ? 'given' : 'skipped',
                        'hidden_reward_eligible' => $giveCommission,
                        'request_state' => $latestPendingRequestId ? 'linked_pending_request_manual_approved' : 'no_request_manual_verify',
                        'request_present' => $latestPendingRequestId !== null,
                        'account_name' => $lockedUser->name,
                        'account_email' => $lockedUser->email,
                        'account_mobile' => $lockedUser->mobile,
                        'account_referral_code' => $lockedUser->referral_code,
                        'action' => 'verify',
                        'selected_option' => $commissionMode,
                        'manual_transaction_reference' => $normalizedTransactionReference,
                        'typing_job_referral_action' => $enableTypingJobReferral ? 'enabled' : 'disabled',
                        'welcome_offer_referral_action' => $enableWelcomeOfferReferral ? 'enabled' : 'disabled',
                        'leadership_referral_action' => $enableLeadershipReferral ? 'enabled' : 'disabled',
                    ],
                    'happened_at' => now(),
                ]);
            } catch (QueryException $exception) {
                if ($this->isDuplicateTransactionConstraint($exception)) {
                    throw ValidationException::withMessages([
                        'transaction_reference' => 'এই transaction reference আগেই ব্যবহার করা হয়েছে। অন্য transaction দিন।',
                    ]);
                }

                throw $exception;
            }
        });

        $typingLabel = $enableTypingJobReferral
            ? 'typing task upline-এর জন্য চালু হয়েছে'
            : 'typing task upline-এর জন্য চালু করা হয়নি';
        $welcomeOfferLabel = $enableWelcomeOfferReferral
            ? 'welcome offer count upline-এর জন্য চালু হয়েছে'
            : 'welcome offer count upline-এর জন্য চালু করা হয়নি';
        $leadershipLabel = $enableLeadershipReferral
            ? 'leadership count upline-এর জন্য চালু হয়েছে'
            : 'leadership count upline-এর জন্য চালু করা হয়নি';

        if ($giveCommission) {
            return 'Account verified হয়েছে, commission দেওয়া হয়েছে, ' . $typingLabel . ', ' . $welcomeOfferLabel . ', ' . $leadershipLabel . ', এবং transaction lock করা হয়েছে।';
        }

        return 'Account verified হয়েছে, commission দেওয়া হয়নি, ' . $typingLabel . ', ' . $welcomeOfferLabel . ', ' . $leadershipLabel . ', এবং transaction lock করা হয়েছে।';
    }

    private function unverifyUserManually(
        User $user,
        string $commissionMode,
        string $typingJobMode = 'disable',
        string $welcomeOfferMode = 'disable',
        string $leadershipMode = 'remove'
    ): string
    {
        $adminId = auth('admin')->id();
        $latestRequestId = $this->latestActivationRequestId((int) $user->id);
        $removeCommission = $commissionMode === 'remove';
        $disableTypingJob = $typingJobMode === 'disable';
        $disableWelcomeOffer = $welcomeOfferMode === 'disable';
        $disableLeadership = $leadershipMode === 'remove';
        $latestVerificationHistory = $this->latestVerificationHistory((int) $user->id);
        $commissionRemoved = false;
        $hiddenRewardAction = $removeCommission ? 'remove_requested_but_not_found' : 'kept';
        $countActions = [
            'typing_job_referral_action' => $disableTypingJob ? 'disabled' : 'kept',
            'welcome_offer_referral_action' => $disableWelcomeOffer ? 'disabled' : 'kept',
            'leadership_referral_action' => $disableLeadership ? 'disabled' : 'kept',
        ];
        $note = $removeCommission
            ? 'Admin marked the account as non-verified and requested commission removal.'
            : 'Admin marked the account as non-verified without removing commissions.';

        DB::transaction(function () use ($user, $adminId, $latestRequestId, $removeCommission, $disableTypingJob, $disableWelcomeOffer, $disableLeadership, &$commissionRemoved, &$hiddenRewardAction, &$countActions, $note, $commissionMode, $latestVerificationHistory) {
            $lockedUser = User::query()->whereKey($user->id)->lockForUpdate()->firstOrFail();

            if ((int) $lockedUser->is_active !== 1) {
                return;
            }

            $lockedUser->is_active = 0;
            if (Schema::hasColumn('users', 'activated_at')) {
                $lockedUser->activated_at = null;
            }
            $countActions = $this->applyUnverifyReferralProgramModes(
                $lockedUser,
                $disableTypingJob,
                $disableWelcomeOffer,
                $disableLeadership
            );
            $lockedUser->save();

            if ($removeCommission) {
                $commissionRemoval = $this->removeVerificationCommission($lockedUser, $latestVerificationHistory);
                $commissionRemoved = (bool) ($commissionRemoval['removed'] ?? false);
                $hiddenRewardAction = (string) ($commissionRemoval['hidden_reward_action'] ?? $hiddenRewardAction);
            }

            $this->activationHistoryService->record([
                'user_id' => (int) $lockedUser->id,
                'activation_request_id' => $latestRequestId,
                'admin_id' => $adminId,
                'event' => 'admin_manual_unverified',
                'status' => 'completed',
                'verification_channel' => 'admin_manual',
                'source' => 'admin_activation_panel',
                'amount' => 0,
                'note' => $note,
                'metadata' => [
                    'auto_verified' => false,
                    'commission_action' => $commissionRemoved ? 'removed' : ($removeCommission ? 'remove_requested_but_not_found' : 'kept'),
                    'hidden_reward_action' => $hiddenRewardAction,
                    'request_state' => $latestRequestId ? 'linked_request' : 'no_request_manual_unverify',
                    'request_present' => $latestRequestId !== null,
                    'account_name' => $lockedUser->name,
                    'account_email' => $lockedUser->email,
                    'account_mobile' => $lockedUser->mobile,
                    'account_referral_code' => $lockedUser->referral_code,
                    'action' => 'unverify',
                    'selected_option' => $commissionMode,
                    'previous_verification_event' => $latestVerificationHistory?->event,
                    'typing_job_referral_action' => $countActions['typing_job_referral_action'],
                    'welcome_offer_referral_action' => $countActions['welcome_offer_referral_action'],
                    'leadership_referral_action' => $countActions['leadership_referral_action'],
                ],
                'happened_at' => now(),
            ]);
        });

        $countMessage = $this->referralProgramCountMessage(
            $countActions['typing_job_referral_action'],
            $countActions['welcome_offer_referral_action'],
            $countActions['leadership_referral_action']
        );

        if ($removeCommission) {
            return $commissionRemoved
                ? 'Account non-verified হয়েছে, commission remove করা হয়েছে, এবং ' . $countMessage
                : 'Account non-verified হয়েছে, verified commission trail পাওয়া যায়নি, এবং ' . $countMessage;
        }

        return 'Account non-verified হয়েছে, commission remove করা হয়নি, এবং ' . $countMessage;
    }

    private function removeOutstandingCommissionForInactiveUser(
        User $user,
        string $typingJobMode = 'disable',
        string $welcomeOfferMode = 'disable',
        string $leadershipMode = 'remove'
    ): array
    {
        $adminId = auth('admin')->id();
        $latestRequestId = $this->latestActivationRequestId((int) $user->id);
        $latestVerificationHistory = $this->latestVerificationHistory((int) $user->id);
        $disableTypingJob = $typingJobMode === 'disable';
        $disableWelcomeOffer = $welcomeOfferMode === 'disable';
        $disableLeadership = $leadershipMode === 'remove';
        $commissionRemoved = false;
        $hiddenRewardAction = 'remove_requested_but_not_found';
        $countActions = [
            'typing_job_referral_action' => $disableTypingJob ? 'disabled' : 'kept',
            'welcome_offer_referral_action' => $disableWelcomeOffer ? 'disabled' : 'kept',
            'leadership_referral_action' => $disableLeadership ? 'disabled' : 'kept',
            'changed' => false,
        ];

        DB::transaction(function () use ($user, $adminId, $latestRequestId, $latestVerificationHistory, $disableTypingJob, $disableWelcomeOffer, $disableLeadership, &$commissionRemoved, &$hiddenRewardAction, &$countActions) {
            $lockedUser = User::query()->whereKey($user->id)->lockForUpdate()->firstOrFail();

            if ((int) $lockedUser->is_active === 1) {
                return;
            }

            $commissionRemoval = $this->removeVerificationCommission($lockedUser, $latestVerificationHistory);
            $commissionRemoved = (bool) ($commissionRemoval['removed'] ?? false);
            $hiddenRewardAction = (string) ($commissionRemoval['hidden_reward_action'] ?? $hiddenRewardAction);
            $countActions = $this->applyUnverifyReferralProgramModes(
                $lockedUser,
                $disableTypingJob,
                $disableWelcomeOffer,
                $disableLeadership
            );

            if (!$commissionRemoved && !($countActions['changed'] ?? false)) {
                return;
            }

            $lockedUser->save();

            $this->activationHistoryService->record([
                'user_id' => (int) $lockedUser->id,
                'activation_request_id' => $latestRequestId,
                'admin_id' => $adminId,
                'event' => 'admin_manual_unverified',
                'status' => 'completed',
                'verification_channel' => 'admin_manual',
                'source' => 'admin_activation_panel',
                'amount' => 0,
                'note' => 'Admin repaired an already non-verified account by removing leftover commission and/or updating referral program counts.',
                'metadata' => [
                    'auto_verified' => false,
                    'commission_action' => $commissionRemoved ? 'removed' : 'remove_requested_but_not_found',
                    'hidden_reward_action' => $hiddenRewardAction,
                    'request_state' => $latestRequestId ? 'linked_request' : 'no_request_manual_unverify',
                    'request_present' => $latestRequestId !== null,
                    'account_name' => $lockedUser->name,
                    'account_email' => $lockedUser->email,
                    'account_mobile' => $lockedUser->mobile,
                    'account_referral_code' => $lockedUser->referral_code,
                    'action' => $commissionRemoved ? 'remove_commission_only' : 'remove_counts_only',
                    'selected_option' => 'remove',
                    'previous_verification_event' => $latestVerificationHistory?->event,
                    'repair_only' => true,
                    'typing_job_referral_action' => $countActions['typing_job_referral_action'],
                    'welcome_offer_referral_action' => $countActions['welcome_offer_referral_action'],
                    'leadership_referral_action' => $countActions['leadership_referral_action'],
                ],
                'happened_at' => now(),
            ]);
        });

        $countMessage = $this->referralProgramCountMessage(
            $countActions['typing_job_referral_action'],
            $countActions['welcome_offer_referral_action'],
            $countActions['leadership_referral_action']
        );

        if ($commissionRemoved) {
            return ['status' => 'success', 'message' => 'Inactive account-এর outstanding commission remove করা হয়েছে এবং ' . $countMessage];
        }

        if (($countActions['changed'] ?? false) === true) {
            return ['status' => 'success', 'message' => 'Inactive account-এর জন্য ' . $countMessage];
        }

        return ['status' => 'error', 'message' => 'এই account-এর জন্য remove করার মতো outstanding commission বা selected count change পাওয়া যায়নি।'];
    }

    private function adjustReferralCommissions(User $user, int $direction): void
    {
        if ($direction >= 0) {
            $this->fixedCommissionDistributor->creditVerificationCommissions($user);

            return;
        }

        $this->fixedCommissionDistributor->reverseVerificationCommissions($user);
    }

    private function removeVerificationCommission(User $user, ?ActivationHistory $latestVerificationHistory): array
    {
        $hiddenRewardEligible = $this->activationHistoryService->verificationGrantsHiddenReward($latestVerificationHistory);
        $referralRemoved = $this->reverseOutstandingReferralCommissions($user);
        $hiddenRewardRemoved = $this->activationHistoryService->reverseHiddenAccountReward($user, $latestVerificationHistory);

        if ($referralRemoved || $hiddenRewardRemoved) {
            return [
                'removed' => true,
                'hidden_reward_action' => $hiddenRewardRemoved
                    ? 'removed'
                    : ($hiddenRewardEligible ? 'remove_requested_but_not_found' : 'not_eligible'),
            ];
        }

        if (!$this->verificationGrantedCommission($latestVerificationHistory)) {
            return [
                'removed' => false,
                'hidden_reward_action' => $hiddenRewardEligible ? 'remove_requested_but_not_found' : 'not_eligible',
            ];
        }

        $this->adjustReferralCommissions($user, -1);

        return [
            'removed' => true,
            'hidden_reward_action' => $hiddenRewardEligible ? 'remove_requested_but_not_found' : 'not_eligible',
        ];
    }

    private function reverseOutstandingReferralCommissions(User $user): bool
    {
        $outstanding = $this->outstandingReferralCommissionMapForUser($user);
        if ($outstanding === []) {
            return false;
        }

        $removed = false;

        foreach ($outstanding as $uplineUserId => $amount) {
            $reverseAmount = round((float) $amount, 2);
            if ($reverseAmount <= 0) {
                continue;
            }

            $uplineUser = User::query()->whereKey((int) $uplineUserId)->lockForUpdate()->first();
            if (!$uplineUser) {
                continue;
            }

            $beforeBalance = (float) ($uplineUser->balance ?? 0);
            $applyAmount = round(min($reverseAmount, $beforeBalance), 2);
            if ($applyAmount <= 0) {
                continue;
            }

            $uplineUser->balance = round($beforeBalance - $applyAmount, 2);
            $uplineUser->save();

            BalanceLog::create([
                'user_id' => $uplineUser->id,
                'amount' => -1 * $applyAmount,
            ]);

            IncomeHistory::create([
                'user_id' => $uplineUser->id,
                'source' => 'referral_reversal',
                'amount' => -1 * $applyAmount,
                'description' => 'রিভার্সাল: ' . $user->name . ' (' . $user->referral_code . ') এর একাউন্ট non-verify হওয়ায় ' . $applyAmount . '৳ কমিশন সমন্বয় করা হয়েছে।',
            ]);

            $removed = true;
        }

        return $removed;
    }

    private function outstandingReferralCommissionMapForUser(User $user): array
    {
        if (!Schema::hasTable('income_histories')) {
            return [];
        }

        $referralCode = trim((string) $user->referral_code);
        if ($referralCode === '') {
            return [];
        }

        $rows = IncomeHistory::query()
            ->whereIn('source', ['referral', 'referral_reversal'])
            ->where('description', 'like', '%(' . $this->escapeLikeForLike($referralCode) . ')%')
            ->where(function ($query) {
                $query->where('description', 'like', '%একাউন্ট অ্যাক্টিভেশন%')
                    ->orWhere('description', 'like', '%একাউন্ট ভেরিফিকেশন%')
                    ->orWhere('description', 'like', '%একাউন্ট non-verify%');
            })
            ->get(['user_id', 'amount']);

        return $this->summarizeOutstandingReferralCommissions($rows);
    }

    private function summarizeOutstandingReferralCommissions(Collection $rows): array
    {
        return $rows
            ->groupBy('user_id')
            ->map(fn (Collection $items) => round((float) $items->sum(fn ($item) => (float) ($item->amount ?? 0)), 2))
            ->filter(fn (float $amount) => $amount > 0)
            ->all();
    }

    private function escapeLikeForLike(string $value): string
    {
        return str_replace(['\\', '%', '_'], ['\\\\', '\%', '\_'], $value);
    }

    private function approvePendingRequests(int $userId, string $note): void
    {
        if (!Schema::hasTable('activation_requests')) {
            return;
        }

        $updates = [
            'status' => 'approved',
            'reviewed_at' => now(),
            'reviewed_by' => auth('admin')->id(),
        ];

        if (Schema::hasColumn('activation_requests', 'admin_note')) {
            $updates['admin_note'] = $note;
        }

        ActivationRequest::query()
            ->where('user_id', $userId)
            ->where('status', 'pending')
            ->update($updates);
    }

    private function latestActivationRequestId(int $userId, ?string $status = null): ?int
    {
        if (!Schema::hasTable('activation_requests')) {
            return null;
        }

        return ActivationRequest::query()
            ->where('user_id', $userId)
            ->when($status !== null, fn ($q) => $q->where('status', $status))
            ->latest('id')
            ->value('id');
    }

    private function latestVerificationHistory(int $userId): ?ActivationHistory
    {
        if (!Schema::hasTable('activation_histories')) {
            return null;
        }

        return ActivationHistory::query()
            ->where('user_id', $userId)
            ->whereIn('event', [
                'voucher_verified',
                'direct_payment_verified',
                'admin_manual_verified',
                'admin_request_approved',
            ])
            ->orderByDesc('happened_at')
            ->orderByDesc('id')
            ->first();
    }

    private function verificationGrantedCommission(?ActivationHistory $history): bool
    {
        if (!$history) {
            return false;
        }

        if (in_array($history->event, ['voucher_verified', 'direct_payment_verified'], true)) {
            return true;
        }

        return data_get($history->metadata, 'commission_action') === 'given';
    }

    private function normalizeTransactionReference(string $transactionReference): string
    {
        return strtoupper((string) preg_replace('/\s+/', '', trim($transactionReference)));
    }

    private function applyTypingJobReferralSetting(User $user, bool $enabled): void
    {
        if (Schema::hasColumn('users', 'typing_job_referral_enabled')) {
            $user->typing_job_referral_enabled = $enabled ? 1 : 0;
        }

        if (Schema::hasColumn('users', 'typing_job_referral_enabled_at')) {
            $user->typing_job_referral_enabled_at = $enabled ? now() : null;
        }
    }

    private function applyWelcomeOfferReferralSetting(User $user, bool $enabled): void
    {
        if (Schema::hasColumn('users', 'welcome_offer_referral_enabled')) {
            $user->welcome_offer_referral_enabled = $enabled ? 1 : 0;
        }

        if (Schema::hasColumn('users', 'welcome_offer_referral_enabled_at')) {
            $user->welcome_offer_referral_enabled_at = $enabled ? now() : null;
        }
    }

    private function applyLeadershipReferralSetting(User $user, bool $enabled): void
    {
        if (Schema::hasColumn('users', 'leadership_referral_enabled')) {
            $user->leadership_referral_enabled = $enabled ? 1 : 0;
        }

        if (Schema::hasColumn('users', 'leadership_referral_enabled_at')) {
            $user->leadership_referral_enabled_at = $enabled ? now() : null;
        }
    }

    private function applyUnverifyReferralProgramModes(User $user, bool $disableTypingJob, bool $disableWelcomeOffer, bool $disableLeadership): array
    {
        $changed = false;

        if ($disableTypingJob && Schema::hasColumn('users', 'typing_job_referral_enabled') && (int) ($user->typing_job_referral_enabled ?? 0) !== 0) {
            $changed = true;
        }

        if ($disableTypingJob && Schema::hasColumn('users', 'typing_job_referral_enabled_at') && $user->typing_job_referral_enabled_at !== null) {
            $changed = true;
        }

        if ($disableWelcomeOffer && Schema::hasColumn('users', 'welcome_offer_referral_enabled') && ($user->welcome_offer_referral_enabled ?? null) !== 0) {
            $changed = true;
        }

        if ($disableWelcomeOffer && Schema::hasColumn('users', 'welcome_offer_referral_enabled_at') && $user->welcome_offer_referral_enabled_at !== null) {
            $changed = true;
        }

        if ($disableLeadership && Schema::hasColumn('users', 'leadership_referral_enabled') && (int) ($user->leadership_referral_enabled ?? 0) !== 0) {
            $changed = true;
        }

        if ($disableLeadership && Schema::hasColumn('users', 'leadership_referral_enabled_at') && $user->leadership_referral_enabled_at !== null) {
            $changed = true;
        }

        if ($disableTypingJob) {
            $this->applyTypingJobReferralSetting($user, false);
        }

        if ($disableWelcomeOffer) {
            $this->applyWelcomeOfferReferralSetting($user, false);
        }

        if ($disableLeadership) {
            $this->applyLeadershipReferralSetting($user, false);
        }

        return [
            'changed' => $changed,
            'typing_job_referral_action' => $disableTypingJob ? 'disabled' : 'kept',
            'welcome_offer_referral_action' => $disableWelcomeOffer ? 'disabled' : 'kept',
            'leadership_referral_action' => $disableLeadership ? 'disabled' : 'kept',
        ];
    }

    private function referralProgramCountMessage(string $typingAction, string $welcomeAction, string $leadershipAction): string
    {
        $parts = [];

        $parts[] = $typingAction === 'disabled'
            ? 'typing task count off করা হয়েছে'
            : 'typing task count রাখা হয়েছে';

        $parts[] = $welcomeAction === 'disabled'
            ? 'welcome offer count off করা হয়েছে'
            : 'welcome offer count রাখা হয়েছে';

        $parts[] = $leadershipAction === 'disabled'
            ? 'leadership count off করা হয়েছে'
            : 'leadership count রাখা হয়েছে';

        return implode(' এবং ', $parts) . '।';
    }

    private function ensureTransactionReferenceAvailable(string $transactionReference): void
    {
        if ($transactionReference === '') {
            throw ValidationException::withMessages([
                'transaction_reference' => 'ম্যানুয়াল verify করতে transaction reference অবশ্যই দিতে হবে।',
            ]);
        }

        if (!Schema::hasTable('activation_histories')) {
            throw ValidationException::withMessages([
                'transaction_reference' => 'Activation history table ready না থাকায় transaction verify করা যাচ্ছে না।',
            ]);
        }

        $exists = ActivationHistory::query()
            ->where(function ($query) use ($transactionReference) {
                $query->where('transaction_id', $transactionReference)
                    ->orWhere('trx_id', $transactionReference);
            })
            ->exists();

        if ($exists) {
            throw ValidationException::withMessages([
                'transaction_reference' => 'এই transaction reference আগেই ব্যবহার করা হয়েছে। অন্য transaction দিন।',
            ]);
        }
    }

    private function isDuplicateTransactionConstraint(QueryException $exception): bool
    {
        $message = strtolower($exception->getMessage());

        return str_contains($message, 'activation_histories_transaction_id_unique')
            || str_contains($message, 'duplicate entry')
            || str_contains($message, 'unique');
    }

    private function canSeeHiddenUsers(): bool
    {
        $admin = auth('admin')->user();
        if (!$admin || !method_exists($admin, 'isSuperAdmin')) {
            return false;
        }

        return (bool) $admin->isSuperAdmin();
    }

    private function applyHiddenUserScope($query)
    {
        if (!$this->canSeeHiddenUsers() && Schema::hasColumn('users', 'is_hidden')) {
            $query->where(function ($inner) {
                $inner->whereNull('is_hidden')->orWhere('is_hidden', 0);
            });
        }

        return $query;
    }
}
