<?php

namespace App\Services\Customer;

use App\Models\BusinessSetting;
use App\Models\CustomerQualityExclusion;
use App\Models\CustomerQualitySyncLog;
use Illuminate\Contracts\Pagination\LengthAwarePaginator as LengthAwarePaginatorContract;
use Illuminate\Database\Query\Builder;
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;

class CustomerQualityService
{
    public const HIGH_AUDIENCE_KEY = 'high_receive_low_return';

    public const RISK_AUDIENCE_KEY = 'high_return_blacklisted';

    public const AUDIENCE_NAMES = [
        self::HIGH_AUDIENCE_KEY => 'Ammar - High Receive Low Return Customers',
        self::RISK_AUDIENCE_KEY => 'Ammar - High Cancel, High Return and Blacklisted Customers',
    ];

    public const SETTING_KEY = 'customer_quality_meta_sync';

    public const META_STATUS_FRESH_HOURS = 24;

    public function report(Request $request): LengthAwarePaginatorContract
    {
        if (! Schema::hasTable('customer_quality_metrics')) {
            return $this->emptyPaginator($request);
        }

        $query = $this->metricsQuery($request);
        $this->applyMetricStatusFilter($query, (string) $request->query('quality_status', ''));

        $sort = (string) $request->query('sort', 'total_orders_desc');
        match ($sort) {
            'receive_rate_desc' => $query->orderByDesc('receive_rate'),
            'return_rate_desc' => $query->orderByDesc('return_rate'),
            'delivered_amount_desc' => $query->orderByDesc('delivered_amount'),
            'average_order_value_desc' => $query->orderByDesc('average_order_value'),
            'latest_customer' => $query->orderByDesc('calculated_at'),
            default => $query->orderByDesc('total_orders')->orderBy('customer_name'),
        };

        $paginator = $query
            ->paginate((int) config('default_pagination', 15))
            ->appends($request->query());

        $paginator->getCollection()->transform(fn ($row) => $this->decorateMetricRow($row));

        return $paginator;
    }

    public function totals(Request $request): array
    {
        if (! Schema::hasTable('customer_quality_metrics')) {
            return [
                'customers' => 0,
                'high_quality' => 0,
                'high_return' => 0,
                'high_cancel' => 0,
                'blacklisted_fake' => 0,
                'manual_blacklist' => 0,
                'excluded_internal' => 0,
                'invalid_phone' => 0,
                'meta_excluded' => 0,
                'high_cancel_only' => 0,
                'high_return_only' => 0,
                'high_cancel_return_overlap' => 0,
                'risk_unique' => 0,
                'delivered_amount' => 0.0,
            ];
        }

        $query = $this->metricsQuery($request);
        $this->applyMetricStatusFilter($query, (string) $request->query('quality_status', ''));

        return [
            'customers' => (clone $query)->count(),
            'high_quality' => (clone $query)->where('is_high_quality', true)->count(),
            'high_return' => (clone $query)->where('is_high_return', true)->count(),
            'high_cancel' => (clone $query)->where('is_high_cancel', true)->count(),
            'blacklisted_fake' => (clone $query)->where('quality_status', 'blacklisted_fake')->count(),
            'manual_blacklist' => (clone $query)->where('is_blacklisted', true)->count(),
            'excluded_internal' => (clone $query)->where('is_internal', true)->count(),
            'invalid_phone' => (clone $query)->whereNull('phone_hash')->count(),
            'meta_excluded' => Schema::hasTable('customer_quality_exclusions')
                ? (clone $query)->where('cqe.is_excluded', true)->count()
                : 0,
            'delivered_amount' => (float) (clone $query)->sum('delivered_amount'),
            'high_cancel_only' => (clone $query)->where('is_high_cancel', true)->where('is_high_return', false)->count(),
            'high_return_only' => (clone $query)->where('is_high_return', true)->where('is_high_cancel', false)->count(),
            'high_cancel_return_overlap' => (clone $query)->where('is_high_cancel', true)->where('is_high_return', true)->count(),
            'risk_unique' => (clone $query)->where('is_internal', false)->whereNotNull('phone_hash')->where(function ($risk) {
                $risk->where('is_high_cancel', true)->orWhere('is_high_return', true)->orWhere('is_blacklisted', true);
            })->count(),
        ];
    }

    public function audiencePlan(string $audienceKey, ?array $metricKeys = null, bool $syncAllEligible = true): array
    {
        $candidateResult = $this->audienceCandidates($audienceKey, $metricKeys);
        $candidateRows = collect($candidateResult['rows']);
        $candidateHashes = $candidateRows
            ->pluck('phone_hash')
            ->filter()
            ->unique()
            ->values();

        $existingRows = Schema::hasTable('customer_quality_audience_members')
            ? DB::table('customer_quality_audience_members')
                ->where('audience_key', $audienceKey)
                ->get()
            : collect();

        $existingHashes = $existingRows
            ->pluck('phone_hash')
            ->filter()
            ->unique()
            ->values();

        $addHashes = $candidateHashes->diff($existingHashes)->values();
        $proposedRemoveHashes = $syncAllEligible ? $existingHashes->diff($candidateHashes)->values() : collect();
        $removalEnabled = (bool) data_get($this->settings(), 'automatic_removal_enabled', false);
        $removeHashes = $removalEnabled ? $proposedRemoveHashes : collect();

        $addRows = $candidateRows
            ->whereIn('phone_hash', $addHashes->all())
            ->values()
            ->all();
        $removeRows = $existingRows
            ->whereIn('phone_hash', $removeHashes->all())
            ->values()
            ->all();

        if ($removeRows !== [] && Schema::hasTable('customer_quality_metrics')) {
            $metricDetails = DB::table('customer_quality_metrics')
                ->whereIn('phone_hash', $removeHashes->all())
                ->get(['phone_hash', 'customer_name', 'phone', 'normalized_phone'])
                ->keyBy('phone_hash');

            foreach ($removeRows as $row) {
                $metric = $metricDetails->get($row->phone_hash);
                $row->customer_name = (string) data_get($metric, 'customer_name', '');
                $row->phone = (string) data_get($metric, 'phone', '');
                $row->normalized_phone = $this->normalizeBangladeshPhone(
                    (string) data_get($metric, 'normalized_phone', data_get($metric, 'phone', ''))
                );
                $row->formatted_phone = $row->normalized_phone ?: $row->phone;
            }
        }

        $scope = [
            'audience_key' => $audienceKey,
            'sync_all_eligible' => $syncAllEligible,
            'metric_keys' => $metricKeys === null ? ['*'] : collect($metricKeys)->sort()->values()->all(),
            'settings' => $this->checksumSettings($this->settings()),
            'metrics_checksum' => $this->metricsCacheChecksum(),
        ];

        $scopeHash = $this->checksum($scope);
        $checksum = $this->checksum([
            'scope_hash' => $scopeHash,
            'candidate_hashes' => $candidateHashes->sort()->values()->all(),
            'add_hashes' => $addHashes->sort()->values()->all(),
            'remove_hashes' => $removeHashes->sort()->values()->all(),
            'suppressed_remove_hashes' => $proposedRemoveHashes->diff($removeHashes)->sort()->values()->all(),
            'skipped_reasons' => $candidateResult['skipped_reasons'],
            'skipped_metric_keys' => collect($candidateResult['skipped_rows'])->pluck('metric_key')->sort()->values()->all(),
            'values' => $candidateRows
                ->map(fn ($row) => [
                    'phone_hash' => $row->phone_hash,
                    'email_hash' => $row->email_hash,
                    'external_id_hash' => $row->external_id_hash,
                    'country_code' => $row->country_code,
                    'lookalike_value' => round((float) $row->lookalike_value, 2),
                    'quality_score' => round((float) $row->quality_score, 2),
                ])
                ->sortBy('phone_hash')
                ->values()
                ->all(),
        ]);

        return [
            'audience_key' => $audienceKey,
            'audience_name' => $this->audienceName($audienceKey),
            'candidate_count' => $candidateResult['candidate_count'],
            'eligible_count' => $candidateHashes->count(),
            'skipped_count' => $candidateResult['skipped_count'],
            'skipped_reasons' => $candidateResult['skipped_reasons'],
            'skipped_rows' => $candidateResult['skipped_rows'],
            'add_count' => $addHashes->count(),
            'remove_count' => $removeHashes->count(),
            'proposed_remove_count' => $proposedRemoveHashes->count(),
            'suppressed_remove_count' => $proposedRemoveHashes->diff($removeHashes)->count(),
            'removal_enabled' => $removalEnabled,
            'removal_percentage' => $existingHashes->count() > 0 ? round(($proposedRemoveHashes->count() / $existingHashes->count()) * 100, 2) : 0.0,
            'add_hashes' => $addHashes->all(),
            'remove_hashes' => $removeHashes->all(),
            'add_rows' => $addRows,
            'remove_rows' => $removeRows,
            'rows' => $candidateRows->values()->all(),
            'sync_all_eligible' => $syncAllEligible,
            'scope_hash' => $scopeHash,
            'checksum' => $checksum,
            'expires_at' => now()->addMinutes((int) data_get($this->settings(), 'dry_run_expires_minutes', 30)),
        ];
    }

    public function audienceCandidates(string $audienceKey, ?array $metricKeys = null): array
    {
        if (! Schema::hasTable('customer_quality_metrics')) {
            return [
                'rows' => [],
                'candidate_count' => 0,
                'skipped_count' => 0,
                'skipped_reasons' => [],
                'skipped_rows' => [],
            ];
        }

        $query = DB::table('customer_quality_metrics as cqm')->select('cqm.*');

        if (Schema::hasTable('customer_quality_exclusions')) {
            $query->leftJoin('customer_quality_exclusions as cqe', function ($join) {
                $join->on('cqe.metric_key', '=', 'cqm.metric_key')
                    ->where('cqe.is_excluded', '=', true);
            })->addSelect([
                'cqe.is_excluded as is_meta_excluded',
                'cqe.category as meta_exclusion_category',
                'cqe.reason as meta_exclusion_reason',
            ]);
        } else {
            $query->selectRaw('0 as is_meta_excluded, NULL as meta_exclusion_category, NULL as meta_exclusion_reason');
        }

        if ($metricKeys !== null) {
            $keys = collect($metricKeys)
                ->filter()
                ->map(fn ($key) => (string) $key)
                ->unique()
                ->values()
                ->all();

            $query->whereIn('cqm.metric_key', $keys ?: ['__none__']);
        }

        if ($audienceKey === self::HIGH_AUDIENCE_KEY) {
            $query->where('cqm.is_high_quality', true);
        } elseif ($audienceKey === self::RISK_AUDIENCE_KEY) {
            $query->where(function ($risk) {
                $risk->where('cqm.is_high_return', true)
                    ->orWhere('cqm.is_high_cancel', true)
                    ->orWhere('cqm.is_blacklisted', true);
            });
        } else {
            $query->whereRaw('1 = 0');
        }

        $rows = $query
            ->orderBy('cqm.metric_key')
            ->get()
            ->map(fn ($row) => $this->decorateMetricRow($row));

        $validRows = [];
        $skippedRows = [];
        $skippedReasons = [];
        $seenPhoneHashes = [];

        foreach ($rows as $row) {
            $reason = null;
            $detail = null;

            if ($row->is_internal) {
                $reason = 'internal_test_exclusion';
                $detail = $row->internal_exclusion_reason ?: 'Internal/test customer.';
            } elseif (blank($row->phone_hash)) {
                $reason = $row->phone_validation_reason ?: 'invalid_phone';
                $detail = 'Mobile number is not eligible for Meta upload.';
            } elseif ($row->is_meta_excluded) {
                $reason = 'manual_exclusion';
                $detail = trim(implode(': ', array_filter([
                    $row->meta_exclusion_category,
                    $row->meta_exclusion_reason,
                ]))) ?: 'Manually excluded from Meta Audience.';
            } elseif (isset($seenPhoneHashes[$row->phone_hash])) {
                $reason = 'duplicate_phone';
                $detail = 'Another customer metric uses the same normalized mobile number.';
            }

            if ($reason !== null) {
                $skippedReasons[$reason] = ($skippedReasons[$reason] ?? 0) + 1;
                $skippedRows[] = [
                    'metric_key' => $row->metric_key,
                    'customer_name' => $row->customer_name,
                    'phone' => $row->formatted_phone,
                    'reason' => $reason,
                    'detail' => $detail,
                ];

                continue;
            }

            $seenPhoneHashes[$row->phone_hash] = true;
            $validRows[] = $row;
        }

        return [
            'rows' => $validRows,
            'candidate_count' => $rows->count(),
            'skipped_count' => count($skippedRows),
            'skipped_reasons' => $skippedReasons,
            'skipped_rows' => $skippedRows,
        ];
    }

    public function audienceCounts(): array
    {
        return [
            self::HIGH_AUDIENCE_KEY => $this->audiencePlan(self::HIGH_AUDIENCE_KEY)['eligible_count'],
            self::RISK_AUDIENCE_KEY => $this->audiencePlan(self::RISK_AUDIENCE_KEY)['eligible_count'],
        ];
    }

    public function audienceSummaries(): array
    {
        $summaries = [];
        foreach (array_keys(self::AUDIENCE_NAMES) as $key) {
            $plan = $this->audiencePlan($key);
            $summary = collect($plan)->only([
                'candidate_count', 'eligible_count', 'add_count', 'remove_count',
                'proposed_remove_count', 'suppressed_remove_count', 'skipped_count',
                'skipped_reasons', 'removal_percentage', 'removal_enabled',
            ])->all();
            $dryRun = $this->latestValidDryRun($key, $plan['scope_hash'], $plan['checksum']);
            $summary['checksum'] = $plan['checksum'];
            $summary['dry_run_ready'] = $dryRun !== null;
            $summary['dry_run_expires_at'] = $dryRun?->expires_at?->toDateTimeString();
            $summary['test_upload_ready'] = $dryRun !== null && $this->hasSuccessfulTestUpload($key, $plan['scope_hash'], $plan['checksum']);
            $summary['live_ready'] = $summary['dry_run_ready'] && $summary['test_upload_ready'];
            $summaries[$key] = $summary;
        }

        return $summaries;
    }

    public function latestLogs(int $limit = 10)
    {
        if (! Schema::hasTable('customer_quality_sync_logs')) {
            return collect();
        }

        return CustomerQualitySyncLog::query()
            ->latest('id')
            ->limit($limit)
            ->get();
    }

    public function latestValidDryRun(string $audienceKey, string $scopeHash, string $checksum): ?CustomerQualitySyncLog
    {
        if (! Schema::hasTable('customer_quality_sync_logs')) {
            return null;
        }

        return CustomerQualitySyncLog::query()
            ->where('audience_key', $audienceKey)
            ->where('mode', 'dry_run')
            ->where('status', 'dry_run')
            ->where('scope_hash', $scopeHash)
            ->where('checksum', $checksum)
            ->whereNotNull('expires_at')
            ->where('expires_at', '>=', now())
            ->latest('id')
            ->first();
    }

    public function hasSuccessfulDryRun(string $audienceKey, ?string $scopeHash = null, ?string $checksum = null): bool
    {
        if (! Schema::hasTable('customer_quality_sync_logs')) {
            return false;
        }

        $query = CustomerQualitySyncLog::query()
            ->where('audience_key', $audienceKey)
            ->where('mode', 'dry_run')
            ->where('status', 'dry_run')
            ->whereNotNull('scope_hash')
            ->whereNotNull('checksum')
            ->whereNotNull('expires_at')
            ->where('expires_at', '>=', now());

        if ($scopeHash !== null) {
            $query->where('scope_hash', $scopeHash);
        }

        if ($checksum !== null) {
            $query->where('checksum', $checksum);
        }

        return $query->exists();
    }

    public function hasSuccessfulDryRunForAllAudiences(): bool
    {
        foreach (array_keys(self::AUDIENCE_NAMES) as $audienceKey) {
            if (! $this->hasSuccessfulDryRun($audienceKey)) {
                return false;
            }
        }

        return true;
    }

    public function settings(): array
    {
        if (! Schema::hasTable('business_settings') || ! Schema::hasColumn('business_settings', 'value')) {
            return $this->defaultSettings();
        }

        $query = BusinessSetting::query()->withoutGlobalScopes();

        if (Schema::hasColumn('business_settings', 'key')) {
            $query->where('key', self::SETTING_KEY);
        } elseif (Schema::hasColumn('business_settings', 'type')) {
            $query->where('type', self::SETTING_KEY);
        } else {
            return $this->defaultSettings();
        }

        $stored = $query->value('value');
        $decoded = is_string($stored) ? json_decode($stored, true) : [];
        $decoded = is_array($decoded) ? $decoded : [];

        return $this->sanitizeSettings($this->mergeSettings($this->defaultSettings(), $decoded));
    }

    public function updateSettings(array $settings): array
    {
        $normalizer = $this->settingsNormalizer();
        $normalizedStatusMappings = $normalizer->normalizeStatusMappings(
            data_get($settings, 'status_mappings', [])
        );
        $normalizedKeywords = $normalizer->normalizeKeywords(data_get($settings, 'auto_exclusions.keywords', []));
        $normalizedEmails = $normalizer->normalizeEmails(data_get($settings, 'auto_exclusions.emails', []));
        $normalizedPhones = $normalizer->splitList(data_get($settings, 'auto_exclusions.phones', []));
        $candidate = array_replace_recursive($this->settings(), [
            'auto_sync_enabled' => (bool) data_get($settings, 'auto_sync_enabled', false),
            'automatic_removal_enabled' => (bool) data_get($settings, 'automatic_removal_enabled', false),
            'lookalike_sync_enabled' => (bool) data_get($settings, 'lookalike_sync_enabled', false),
            'max_removal_percentage' => (float) data_get($settings, 'max_removal_percentage', 20),
            'min_delivered_orders' => (int) data_get($settings, 'min_delivered_orders', 2),
            'min_receive_rate' => (float) data_get($settings, 'min_receive_rate', 90),
            'max_return_rate' => (float) data_get($settings, 'max_return_rate', 10),
            'max_cancel_rate' => (float) data_get($settings, 'max_cancel_rate', 30),
            'min_delivered_amount' => (float) data_get($settings, 'min_delivered_amount', 3000),
            'min_average_order_value' => (float) data_get($settings, 'min_average_order_value', 800),
            'dry_run_expires_minutes' => (int) data_get($settings, 'dry_run_expires_minutes', 30),
            'country_code' => strtoupper((string) data_get($settings, 'country_code', 'BD')),
            'auto_exclusions' => [
                'enabled' => (bool) data_get($settings, 'auto_exclusions.enabled', true),
                'match_admin_staff_tables' => (bool) data_get($settings, 'auto_exclusions.match_admin_staff_tables', true),
            ],
            'lookalike' => [
                'country' => strtoupper((string) data_get($settings, 'lookalike.country', 'BD')),
                'ratio' => (float) data_get($settings, 'lookalike.ratio', 0.01),
            ],
            'audiences' => [
                self::HIGH_AUDIENCE_KEY => [
                    'meta_audience_id' => trim((string) data_get($settings, 'audiences.'.self::HIGH_AUDIENCE_KEY.'.meta_audience_id')),
                    'lookalike_audience_id' => trim((string) data_get($settings, 'audiences.'.self::HIGH_AUDIENCE_KEY.'.lookalike_audience_id')),
                ],
                self::RISK_AUDIENCE_KEY => [
                    'meta_audience_id' => trim((string) data_get($settings, 'audiences.'.self::RISK_AUDIENCE_KEY.'.meta_audience_id')),
                ],
            ],
        ]);

        data_set($candidate, 'status_mappings', $normalizedStatusMappings);
        data_set($candidate, 'auto_exclusions.keywords', $normalizedKeywords);
        data_set($candidate, 'auto_exclusions.emails', $normalizedEmails);
        data_set($candidate, 'auto_exclusions.phones', $normalizedPhones);
        $merged = $this->sanitizeSettings($candidate);

        $this->saveSettings($merged);

        return $merged;
    }

    public function statusMappingErrors(mixed $mappings): array
    {
        return $this->settingsNormalizer()->statusMappingErrors($mappings);
    }

    public function metaWriteEligibility(
        ?array $settings = null,
        ?array $successfulTestUploads = null,
        ?\DateTimeInterface $referenceTime = null
    ): array {
        $settings ??= $this->settings();
        $successfulTestUploads ??= collect(array_keys(self::AUDIENCE_NAMES))
            ->mapWithKeys(fn ($audienceKey) => [$audienceKey => $this->hasAnySuccessfulTestUpload($audienceKey)])
            ->all();
        $reference = $referenceTime
            ? Carbon::instance($referenceTime)->copy()
            : Carbon::now();
        $cutoff = $reference->copy()->subHours(self::META_STATUS_FRESH_HOURS);
        $audiences = [];
        $reasons = [];

        foreach (array_keys(self::AUDIENCE_NAMES) as $audienceKey) {
            $status = (array) data_get($settings, 'audiences.'.$audienceKey.'.status', []);
            $state = (string) data_get($status, 'state', 'not_checked');
            $ready = $state === 'ready' && (bool) data_get($status, 'is_ready', true);
            $checkedAtValue = data_get($status, 'checked_at', data_get($settings, 'audiences.'.$audienceKey.'.status_checked_at'));
            $checkedAt = null;

            if (filled($checkedAtValue)) {
                try {
                    $checkedAt = Carbon::parse((string) $checkedAtValue);
                } catch (\Throwable) {
                    $checkedAt = null;
                }
            }

            $fresh = $checkedAt !== null
                && $checkedAt->betweenIncluded($cutoff, $reference->copy()->addMinutes(5));
            $testUploadPassed = (bool) ($successfulTestUploads[$audienceKey] ?? false);
            $audienceReasons = [];

            if (! $testUploadPassed) {
                $audienceReasons[] = 'successful accepted-and-cleaned test upload is missing';
            }
            if (! $ready) {
                $audienceReasons[] = 'Meta audience status is not Ready';
            } elseif (! $fresh) {
                $audienceReasons[] = 'Meta audience status is older than 24 hours';
            }

            $audiences[$audienceKey] = [
                'allowed' => $audienceReasons === [],
                'state' => $state,
                'checked_at' => $checkedAt?->toDateTimeString(),
                'test_upload_passed' => $testUploadPassed,
                'reasons' => $audienceReasons,
            ];

            foreach ($audienceReasons as $reason) {
                $reasons[] = self::AUDIENCE_NAMES[$audienceKey].': '.$reason.'.';
            }
        }

        return [
            'allowed' => $reasons === [],
            'fresh_hours' => self::META_STATUS_FRESH_HOURS,
            'reasons' => $reasons,
            'audiences' => $audiences,
        ];
    }

    public function rememberMetaAudienceId(string $audienceKey, string $audienceId): void
    {
        $settings = $this->settings();
        data_set($settings, 'audiences.'.$audienceKey.'.meta_audience_id', $audienceId);

        $this->saveSettings($settings);
    }

    public function forgetMetaAudienceId(string $audienceKey): void
    {
        $this->rememberMetaAudienceId($audienceKey, '');
    }

    public function rememberLookalikeAudienceId(string $audienceKey, string $audienceId): void
    {
        $settings = $this->settings();
        data_set($settings, 'audiences.'.$audienceKey.'.lookalike_audience_id', $audienceId);

        $this->saveSettings($settings);
    }

    public function rememberAudienceStatus(string $audienceKey, array $status): void
    {
        $settings = $this->settings();
        data_set($settings, 'audiences.'.$audienceKey.'.status', $status);
        data_set($settings, 'audiences.'.$audienceKey.'.status_checked_at', now()->toDateTimeString());

        $this->saveSettings($settings);
    }

    public function rememberAudienceSyncResult(string $audienceKey, array $result): void
    {
        $settings = $this->settings();
        $current = (array) data_get($settings, 'audiences.'.$audienceKey.'.sync_result', []);
        data_set($settings, 'audiences.'.$audienceKey.'.sync_result', array_replace($current, $result));
        $this->saveSettings($settings);
    }

    public function audienceStatus(string $audienceKey): array
    {
        return (array) data_get($this->settings(), 'audiences.'.$audienceKey.'.status', []);
    }

    public function hasSuccessfulTestUpload(string $audienceKey, string $scopeHash, string $checksum): bool
    {
        if (! Schema::hasTable('customer_quality_sync_logs')) {
            return false;
        }

        return CustomerQualitySyncLog::query()
            ->where('audience_key', $audienceKey)
            ->where('mode', 'test_upload')
            ->where('status', 'test_passed')
            ->where('scope_hash', $scopeHash)
            ->where('checksum', $checksum)
            ->exists();
    }

    public function hasSuccessfulTestUploadForAllAudiences(): bool
    {
        if (! Schema::hasTable('customer_quality_sync_logs')) {
            return false;
        }

        foreach (array_keys(self::AUDIENCE_NAMES) as $audienceKey) {
            if (! $this->hasAnySuccessfulTestUpload($audienceKey)) {
                return false;
            }
        }

        return true;
    }

    public function hasAnySuccessfulTestUpload(string $audienceKey): bool
    {
        return Schema::hasTable('customer_quality_sync_logs')
            && CustomerQualitySyncLog::query()
                ->where('audience_key', $audienceKey)
                ->where('mode', 'test_upload')
                ->where('status', 'test_passed')
                ->exists();
    }

    public function hasCompletedLiveSync(string $audienceKey): bool
    {
        return Schema::hasTable('customer_quality_sync_logs')
            && CustomerQualitySyncLog::query()
                ->where('audience_key', $audienceKey)
                ->where('mode', 'live')
                ->whereIn('status', ['ready', 'completed'])
                ->exists();
    }

    public function updateMetaExclusion(
        string $metricKey,
        ?int $userId,
        bool $isExcluded,
        string $category,
        ?string $reason,
        ?int $adminId
    ): CustomerQualityExclusion {
        if (! Schema::hasTable('customer_quality_exclusions')) {
            throw new \RuntimeException('Customer quality exclusion table is missing. Please run migrations first.');
        }

        $exclusion = CustomerQualityExclusion::query()->firstOrNew(['metric_key' => $metricKey]);
        if (! $exclusion->exists) {
            $exclusion->created_by_admin_id = $adminId;
        }

        $exclusion->fill([
            'user_id' => $userId,
            'is_excluded' => $isExcluded,
            'category' => $category,
            'reason' => $reason,
            'updated_by_admin_id' => $adminId,
        ])->save();

        return $exclusion;
    }

    public function defaultSettings(): array
    {
        $serviceConfig = function_exists('app') && app()->bound('config')
            ? (array) config('services.meta_marketing', [])
            : [];

        return [
            'auto_sync_enabled' => false,
            'automatic_removal_enabled' => false,
            'lookalike_sync_enabled' => false,
            'max_removal_percentage' => 20.0,
            'min_delivered_orders' => 2,
            'min_receive_rate' => 90.0,
            'max_return_rate' => 10.0,
            'max_cancel_rate' => 30.0,
            'min_delivered_amount' => 3000.0,
            'min_average_order_value' => 800.0,
            'dry_run_expires_minutes' => 30,
            'country_code' => 'BD',
            'auto_exclusions' => [
                'enabled' => true,
                'match_admin_staff_tables' => true,
                'keywords' => CustomerQualitySettingsNormalizer::APPROVED_INTERNAL_KEYWORDS,
                'emails' => [],
                'phones' => [],
            ],
            'status_mappings' => CustomerQualitySettingsNormalizer::APPROVED_STATUS_MAPPINGS,
            'audiences' => [
                self::HIGH_AUDIENCE_KEY => [
                    'name' => self::AUDIENCE_NAMES[self::HIGH_AUDIENCE_KEY],
                    'meta_audience_id' => trim((string) data_get($serviceConfig, 'audiences.'.self::HIGH_AUDIENCE_KEY.'.id')) ?: '120252718996570404',
                    'lookalike_audience_id' => (string) data_get($serviceConfig, 'audiences.'.self::HIGH_AUDIENCE_KEY.'.lookalike_id', ''),
                ],
                self::RISK_AUDIENCE_KEY => [
                    'name' => self::AUDIENCE_NAMES[self::RISK_AUDIENCE_KEY],
                    'meta_audience_id' => trim((string) data_get($serviceConfig, 'audiences.'.self::RISK_AUDIENCE_KEY.'.id')) ?: '120252720839690404',
                ],
            ],
            'lookalike' => [
                'country' => strtoupper((string) data_get($serviceConfig, 'lookalike_country', 'BD')),
                'ratio' => (float) data_get($serviceConfig, 'lookalike_ratio', 0.01),
            ],
        ];
    }

    public function audienceName(string $audienceKey): string
    {
        return self::AUDIENCE_NAMES[$audienceKey] ?? $audienceKey;
    }

    public function lookalikeName(string $audienceKey): string
    {
        return $this->audienceName($audienceKey).' - Bangladesh 1% Lookalike';
    }

    public function statusMappings(?array $settings = null): array
    {
        $settings = $settings ?: $this->settings();

        return $this->settingsNormalizer()->normalizeStatusMappings(
            data_get($settings, 'status_mappings', [])
        );
    }

    public function statusesFor(string $group, ?array $settings = null): array
    {
        return data_get($this->statusMappings($settings), $group, []);
    }

    public function refreshMetrics(): array
    {
        if (! Schema::hasTable('customer_quality_metrics')) {
            throw new \RuntimeException('Customer quality metric table is missing. Please run migrations first.');
        }

        if (! Schema::hasTable('users') || ! Schema::hasTable('orders')) {
            throw new \RuntimeException('Users or orders table is missing.');
        }

        $settings = $this->settings();
        $statusMappings = $this->statusMappings($settings);
        $flags = $this->loadQualityFlags();
        $internalIdentities = $this->loadInternalIdentities($settings);
        $metrics = [];
        $userMetricKeys = [];
        $calculatedAt = now()->startOfSecond();
        $orderScopeChecksum = $this->ordersScopeChecksum($settings);

        $userColumns = $this->availableUserColumns();
        DB::table('users')
            ->select($userColumns)
            ->orderBy('id')
            ->chunkById(1000, function (Collection $users) use (&$metrics, &$userMetricKeys, $flags, $settings, $internalIdentities) {
                foreach ($users as $user) {
                    $metric = $this->userSeedMetric($user, $flags[(int) $user->id] ?? [], $settings, $internalIdentities);
                    $metrics[$metric['metric_key']] = array_replace($metrics[$metric['metric_key']] ?? [], $metric);
                    $userMetricKeys[(int) $user->id] = $metric['metric_key'];
                }
            });

        $orderColumns = $this->availableOrderColumns();
        $orderQuery = DB::table('orders as cq_orders')
            ->select(collect($orderColumns)->map(fn ($column) => 'cq_orders.'.$column)->all());

        if (Schema::hasTable('order_courier_shipments')) {
            $orderQuery->leftJoin('order_courier_shipments as cq_shipments', 'cq_shipments.order_id', '=', 'cq_orders.id');
            foreach (['cod_collected_amount', 'collectable_amount', 'cod_collection_status'] as $column) {
                if (Schema::hasColumn('order_courier_shipments', $column)) {
                    $orderQuery->addSelect("cq_shipments.{$column} as cq_shipment_{$column}");
                }
            }
        }

        $orderQuery
            ->orderBy('cq_orders.id')
            ->chunkById(1000, function (Collection $orders) use (&$metrics, $userMetricKeys, $flags, $settings, $statusMappings, $internalIdentities) {
                foreach ($orders as $order) {
                    $contact = $this->orderContact($order);
                    $metricKey = $this->orderMetricKey($order, $contact, $userMetricKeys, $settings);

                    if (! isset($metrics[$metricKey])) {
                        $metrics[$metricKey] = $this->guestSeedMetric($metricKey, $contact, $settings, $internalIdentities);
                    }

                    $userId = $this->orderUserId($order);
                    if ($userId && isset($flags[$userId])) {
                        $metrics[$metricKey]['is_blacklisted'] = (bool) ($metrics[$metricKey]['is_blacklisted'] || data_get($flags[$userId], 'is_blacklisted'));
                        $metrics[$metricKey]['is_fake'] = (bool) ($metrics[$metricKey]['is_fake'] || data_get($flags[$userId], 'is_fake'));
                    }

                    if (blank($metrics[$metricKey]['phone']) && filled($contact['phone'])) {
                        $metrics[$metricKey]['phone'] = $contact['phone'];
                    }
                    if (blank($metrics[$metricKey]['customer_name']) && filled($contact['name'])) {
                        $metrics[$metricKey]['customer_name'] = $contact['name'];
                    }
                    if (blank($metrics[$metricKey]['email_hash']) && filled($contact['email'])) {
                        $metrics[$metricKey]['email_hash'] = $this->hashEmail($contact['email']);
                        $metrics[$metricKey]['customer_email'] = strtolower(trim((string) $contact['email']));
                    }

                    $autoExclusion = $this->autoExclusionForContact($contact, $settings, $internalIdentities, $userId);
                    if ($autoExclusion['excluded']) {
                        $metrics[$metricKey]['is_internal'] = true;
                        $metrics[$metricKey]['internal_exclusion_reason'] = $autoExclusion['reason'];
                        $metrics[$metricKey]['internal_exclusion_evidence'] = $autoExclusion['evidence'];
                    }

                    $metrics[$metricKey]['total_orders']++;
                    $metrics[$metricKey]['source_order_count']++;

                    $normalizedStatus = $this->normalizeStatus((string) data_get($order, 'order_status'));
                    $statusGroup = $this->statusGroup($normalizedStatus, $statusMappings);
                    if ($statusGroup === 'delivered') {
                        $metrics[$metricKey]['delivered_orders']++;
                        $metrics[$metricKey]['delivered_amount'] += $this->orderAmount($order, $normalizedStatus);
                    } elseif ($statusGroup === 'returned') {
                        $metrics[$metricKey]['returned_orders']++;
                    } elseif ($statusGroup === 'cancelled') {
                        $metrics[$metricKey]['cancelled_orders']++;
                    }
                }
            }, 'cq_orders.id', 'id');

        $upserts = [];
        foreach ($metrics as $metric) {
            $upserts[] = $this->finalizeMetric($metric, $settings, $orderScopeChecksum, $calculatedAt);
        }

        foreach (array_chunk($upserts, 1000) as $chunk) {
            DB::table('customer_quality_metrics')->upsert(
                $chunk,
                ['metric_key'],
                [
                    'user_id',
                    'customer_name',
                    'phone',
                    'normalized_phone',
                    'phone_hash',
                    'customer_email',
                    'phone_validation_reason',
                    'email_hash',
                    'external_id_hash',
                    'country_code',
                    'total_orders',
                    'delivered_orders',
                    'returned_orders',
                    'cancelled_orders',
                    'delivered_amount',
                    'average_order_value',
                    'receive_rate',
                    'return_rate',
                    'cancel_rate',
                    'quality_score',
                    'lookalike_value',
                    'quality_status',
                    'is_high_quality',
                    'is_high_return',
                    'is_high_cancel',
                    'classification_reasons',
                    'is_blacklisted',
                    'is_fake',
                    'is_blocked',
                    'is_internal',
                    'internal_exclusion_reason',
                    'internal_exclusion_evidence',
                    'source_order_count',
                    'order_scope_checksum',
                    'calculated_at',
                    'updated_at',
                ]
            );
        }

        DB::table('customer_quality_metrics')
            ->where(function ($query) use ($calculatedAt) {
                $query->whereNull('calculated_at')->orWhere('calculated_at', '<', $calculatedAt);
            })
            ->delete();

        return [
            'calculated_at' => $calculatedAt,
            'metric_count' => count($upserts),
            'high_quality_count' => collect($upserts)->where('is_high_quality', true)->count(),
            'risk_count' => collect($upserts)->filter(fn ($row) => ! $row['is_internal'] && ($row['is_high_return'] || $row['is_high_cancel'] || $row['is_blacklisted']))->count(),
            'checksum' => $this->metricsCacheChecksum(),
        ];
    }

    public function normalizeBangladeshPhone(?string $phone): ?string
    {
        return $this->phoneValidation($phone)['normalized'];
    }

    public function phoneValidation(?string $phone): array
    {
        $raw = trim((string) $phone);

        if ($raw === '') {
            return ['normalized' => null, 'reason' => 'blank_phone'];
        }

        if (preg_match('/[A-Za-z\p{L}]/u', $raw) === 1) {
            return ['normalized' => null, 'reason' => 'non_numeric_character'];
        }

        if (preg_match('/[,;|\/]|\b(?:\+?88)?01[3-9]\d{8}\b.*\b(?:\+?88)?01[3-9]\d{8}\b/', $raw) === 1) {
            return ['normalized' => null, 'reason' => 'multiple_phone'];
        }

        $digits = preg_replace('/\D+/', '', $raw) ?? '';

        if ($digits === '') {
            return ['normalized' => null, 'reason' => 'blank_phone'];
        }

        if (str_starts_with($digits, '00880')) {
            $digits = substr($digits, 2);
        }

        $length = strlen($digits);
        if ($length < 10) {
            return ['normalized' => null, 'reason' => 'missing_digit'];
        }
        if ($length > 13) {
            return ['normalized' => null, 'reason' => 'extra_digit'];
        }

        if (str_starts_with($digits, '880') && $length === 13) {
            $normalized = $digits;
        } elseif (str_starts_with($digits, '01') && $length === 11) {
            $normalized = '880'.substr($digits, 1);
        } elseif (str_starts_with($digits, '1') && $length === 10) {
            $normalized = '880'.$digits;
        } else {
            $reason = $length === 10 && str_starts_with($digits, '0')
                ? 'missing_digit'
                : (in_array($length, [10, 11, 13], true) ? 'invalid_prefix' : ($length < 11 ? 'missing_digit' : 'extra_digit'));

            return ['normalized' => null, 'reason' => $reason];
        }

        if (preg_match('/^8801[3-9][0-9]{8}$/', $normalized) !== 1) {
            return ['normalized' => null, 'reason' => 'invalid_prefix'];
        }

        return ['normalized' => '+'.$normalized, 'reason' => null];
    }

    public function hashPhone(?string $normalizedPhone): ?string
    {
        $normalizedPhone = preg_replace('/\D+/', '', trim((string) $normalizedPhone)) ?? '';

        return $normalizedPhone !== '' ? hash('sha256', $normalizedPhone) : null;
    }

    public function hashEmail(?string $email): ?string
    {
        $email = strtolower(trim((string) $email));

        return $email !== '' && str_contains($email, '@') ? hash('sha256', $email) : null;
    }

    public function hashCountry(string $countryCode): string
    {
        return hash('sha256', strtolower(trim($countryCode) ?: 'bd'));
    }

    public function classifyFromMetrics(
        int $deliveredOrders,
        int $returnedOrders,
        bool $excludedFromHighQuality,
        ?array $settings = null,
        float $deliveredAmount = 0.0,
        float $averageOrderValue = 0.0,
        int $cancelledOrders = 0
    ): string {
        $result = $this->classificationBreakdown(
            $deliveredOrders,
            $returnedOrders,
            $cancelledOrders,
            $deliveredAmount,
            $averageOrderValue,
            $excludedFromHighQuality,
            $settings
        );

        if ($excludedFromHighQuality) {
            return 'blacklisted_fake';
        }

        if ($result['is_high_quality']) {
            return 'high_quality';
        }

        if ($result['is_high_return']) {
            return 'high_return';
        }

        if ($result['is_high_cancel']) {
            return 'high_cancel';
        }

        return 'standard';
    }

    public function classificationBreakdown(
        int $deliveredOrders,
        int $returnedOrders,
        int $cancelledOrders,
        float $deliveredAmount,
        float $averageOrderValue,
        bool $excluded,
        ?array $settings = null
    ): array {
        $settings = $settings ?: $this->defaultSettings();
        $rates = $this->rates($deliveredOrders, $returnedOrders);
        $cancelRate = $this->cancelRate($cancelledOrders, $deliveredOrders + $returnedOrders + $cancelledOrders);
        $checks = [
            'delivered_order_requirement' => $deliveredOrders >= (int) $settings['min_delivered_orders'],
            'receive_rate_requirement' => $rates['receive_rate'] >= (float) $settings['min_receive_rate'],
            'return_rate_requirement' => $rates['return_rate'] <= (float) $settings['max_return_rate'],
            'cancel_rate_requirement' => $cancelRate <= (float) $settings['max_cancel_rate'],
            'delivered_amount_requirement' => $deliveredAmount >= (float) $settings['min_delivered_amount'],
            'average_order_value_requirement' => $averageOrderValue >= (float) $settings['min_average_order_value'],
            'not_excluded' => ! $excluded,
        ];

        return [
            'is_high_quality' => ! in_array(false, $checks, true),
            'is_high_return' => $returnedOrders > 0 && $rates['return_rate'] > (float) $settings['max_return_rate'],
            'is_high_cancel' => $cancelledOrders > 0 && $cancelRate > (float) $settings['max_cancel_rate'],
            'receive_rate' => $rates['receive_rate'],
            'return_rate' => $rates['return_rate'],
            'cancel_rate' => $cancelRate,
            'quality_score' => $this->qualityScore($rates['return_rate'], $cancelRate),
            'checks' => $checks,
            'reasons' => collect($checks)->filter(fn ($passed) => ! $passed)->keys()->values()->all(),
        ];
    }

    public function qualityScore(float $returnRate, float $cancelRate): float
    {
        return max(0, min(100, round(100 - $returnRate - $cancelRate, 2)));
    }

    public function rates(int $deliveredOrders, int $returnedOrders): array
    {
        $denominator = $deliveredOrders + $returnedOrders;

        if ($denominator <= 0) {
            return [
                'receive_rate' => 0.0,
                'return_rate' => 0.0,
            ];
        }

        return [
            'receive_rate' => round(($deliveredOrders / $denominator) * 100, 2),
            'return_rate' => round(($returnedOrders / $denominator) * 100, 2),
        ];
    }

    public function cancelRate(int $cancelledOrders, int $totalOrders): float
    {
        return $totalOrders > 0
            ? round(($cancelledOrders / $totalOrders) * 100, 2)
            : 0.0;
    }

    private function metricsQuery(?Request $request = null): Builder
    {
        $query = DB::table('customer_quality_metrics as cqm')
            ->select([
                'cqm.id',
                'cqm.metric_key',
                'cqm.user_id',
                'cqm.customer_name',
                'cqm.phone',
                'cqm.normalized_phone',
                'cqm.phone_hash',
                'cqm.customer_email',
                'cqm.phone_validation_reason',
                'cqm.email_hash',
                'cqm.external_id_hash',
                'cqm.country_code',
                'cqm.total_orders',
                'cqm.delivered_orders',
                'cqm.returned_orders',
                'cqm.cancelled_orders',
                'cqm.delivered_amount',
                'cqm.average_order_value',
                'cqm.receive_rate',
                'cqm.return_rate',
                'cqm.cancel_rate',
                'cqm.quality_score',
                'cqm.lookalike_value',
                'cqm.quality_status',
                'cqm.is_high_quality',
                'cqm.is_high_return',
                'cqm.is_high_cancel',
                'cqm.classification_reasons',
                'cqm.is_blacklisted',
                'cqm.is_fake',
                'cqm.is_blocked',
                'cqm.is_internal',
                'cqm.internal_exclusion_reason',
                'cqm.internal_exclusion_evidence',
                'cqm.calculated_at',
            ]);

        if (Schema::hasTable('customer_quality_exclusions')) {
            $query->leftJoin('customer_quality_exclusions as cqe', 'cqe.metric_key', '=', 'cqm.metric_key')
                ->addSelect([
                    'cqe.is_excluded as is_meta_excluded',
                    'cqe.category as meta_exclusion_category',
                    'cqe.reason as meta_exclusion_reason',
                ]);
        } else {
            $query->selectRaw('0 as is_meta_excluded, NULL as meta_exclusion_category, NULL as meta_exclusion_reason');
        }

        if ($request?->filled('search')) {
            $search = trim((string) $request->query('search'));
            $query->where(function ($searchQuery) use ($search) {
                $searchQuery
                    ->where('cqm.customer_name', 'like', "%{$search}%")
                    ->orWhere('cqm.phone', 'like', "%{$search}%")
                    ->orWhere('cqm.normalized_phone', 'like', "%{$search}%")
                    ->orWhere('cqm.customer_email', 'like', "%{$search}%")
                    ->orWhere('cqm.user_id', $search)
                    ->orWhere('cqm.metric_key', 'like', "%{$search}%");
            });
        }

        return $query;
    }

    private function applyMetricStatusFilter(Builder $query, string $qualityStatus): void
    {
        if ($qualityStatus === 'high_quality') {
            $query->where('is_high_quality', true);

            return;
        }
        if ($qualityStatus === 'high_return') {
            $query->where('is_high_return', true);

            return;
        }
        if ($qualityStatus === 'high_cancel') {
            $query->where('is_high_cancel', true);

            return;
        }
        if ($qualityStatus === 'manual_blacklist') {
            $query->where('is_blacklisted', true);

            return;
        }
        if ($qualityStatus === 'excluded_internal') {
            $query->where('is_internal', true);

            return;
        }
        if (in_array($qualityStatus, ['blacklisted_fake', 'standard'], true)) {
            $query->where('quality_status', $qualityStatus);

            return;
        }

        if ($qualityStatus === 'invalid_phone') {
            $query->whereNull('phone_hash');

            return;
        }
        if ($qualityStatus === 'meta_eligible') {
            $query->where('is_internal', false)->whereNotNull('phone_hash')->where(function ($eligible) {
                $eligible->where('is_high_quality', true)->orWhere('is_high_return', true)->orWhere('is_high_cancel', true)->orWhere('is_blacklisted', true);
            });

            return;
        }
        if ($qualityStatus === 'meta_not_eligible') {
            $query->where(function ($notEligible) {
                $notEligible->where('is_internal', true)->orWhereNull('phone_hash')->orWhere(function ($flags) {
                    $flags->where('is_high_quality', false)->where('is_high_return', false)->where('is_high_cancel', false)->where('is_blacklisted', false);
                });
            });
        }
    }

    private function decorateMetricRow(object $row): object
    {
        $row->id = (int) ($row->id ?? 0);
        $row->user_id = $row->user_id === null ? null : (int) $row->user_id;
        $row->metric_key = (string) ($row->metric_key ?? '');
        $row->customer_name = trim((string) ($row->customer_name ?: 'Guest Customer'));
        $row->phone = (string) ($row->phone ?? '');
        $row->normalized_phone = $row->normalized_phone ?: $this->normalizeBangladeshPhone($row->phone);
        $row->formatted_phone = $this->normalizeBangladeshPhone($row->normalized_phone ?: $row->phone) ?: $row->phone;
        $row->phone_hash = $row->phone_hash ?: $this->hashPhone($row->normalized_phone);
        $row->customer_email = (string) ($row->customer_email ?? '');
        $row->phone_validation_reason = (string) ($row->phone_validation_reason ?? '');
        $row->email_hash = (string) ($row->email_hash ?? '');
        $row->external_id_hash = (string) ($row->external_id_hash ?? '');
        $row->country_code = strtoupper((string) ($row->country_code ?: 'BD'));
        $row->total_orders = (int) $row->total_orders;
        $row->delivered_orders = (int) $row->delivered_orders;
        $row->returned_orders = (int) $row->returned_orders;
        $row->cancelled_orders = (int) $row->cancelled_orders;
        $row->delivered_amount = (float) $row->delivered_amount;
        $row->average_order_value = (float) $row->average_order_value;
        $row->receive_rate = (float) $row->receive_rate;
        $row->return_rate = (float) $row->return_rate;
        $row->cancel_rate = (float) ($row->cancel_rate ?? $this->cancelRate(
            $row->cancelled_orders,
            $row->delivered_orders + $row->returned_orders + $row->cancelled_orders
        ));
        $row->quality_score = (float) $row->quality_score;
        $row->lookalike_value = (float) $row->lookalike_value;
        $row->is_blacklisted = (bool) $row->is_blacklisted;
        $row->is_fake = (bool) $row->is_fake;
        $row->is_blocked = (bool) $row->is_blocked;
        $row->is_high_quality = (bool) ($row->is_high_quality ?? false);
        $row->is_high_return = (bool) ($row->is_high_return ?? false);
        $row->is_high_cancel = (bool) ($row->is_high_cancel ?? false);
        $row->classification_reasons = is_array($row->classification_reasons ?? null) ? $row->classification_reasons : (json_decode((string) ($row->classification_reasons ?? '[]'), true) ?: []);
        $row->is_internal = (bool) ($row->is_internal ?? false);
        $row->internal_exclusion_reason = (string) ($row->internal_exclusion_reason ?? '');
        $row->internal_exclusion_evidence = is_array($row->internal_exclusion_evidence ?? null) ? $row->internal_exclusion_evidence : (json_decode((string) ($row->internal_exclusion_evidence ?? '{}'), true) ?: []);
        $row->is_meta_excluded = (bool) ($row->is_meta_excluded ?? false);
        $row->meta_exclusion_category = (string) ($row->meta_exclusion_category ?? '');
        $row->meta_exclusion_reason = (string) ($row->meta_exclusion_reason ?? '');
        $row->is_excluded_from_high_quality = $row->is_blacklisted || $row->is_fake || $row->is_blocked || $row->is_internal;
        $row->customer_quality_status = (string) $row->quality_status;
        $row->customer_quality_label = $this->statusLabel($row->customer_quality_status);

        return $row;
    }

    private function statusLabel(string $status): string
    {
        return match ($status) {
            'high_quality' => 'High Quality Customer',
            'high_return' => 'High Return Customer',
            'high_cancel' => 'High Cancel Customer',
            'blacklisted_fake' => 'Blacklisted/Fake Customer',
            'excluded_internal' => 'Excluded Internal/Test Customer',
            default => 'Standard Customer',
        };
    }

    private function settingsNormalizer(): CustomerQualitySettingsNormalizer
    {
        return new CustomerQualitySettingsNormalizer;
    }

    private function sanitizeSettings(array $settings): array
    {
        $settings['min_delivered_orders'] = max(0, (int) data_get($settings, 'min_delivered_orders', 2));
        $settings['min_receive_rate'] = max(0, min(100, (float) data_get($settings, 'min_receive_rate', 90)));
        $settings['max_return_rate'] = max(0, min(100, (float) data_get($settings, 'max_return_rate', 10)));
        $settings['max_cancel_rate'] = max(0, min(100, (float) data_get($settings, 'max_cancel_rate', 30)));
        $deliveredAmount = (float) data_get($settings, 'min_delivered_amount', 3000);
        $averageOrderValue = (float) data_get($settings, 'min_average_order_value', 800);
        $settings['min_delivered_amount'] = $deliveredAmount > 0 ? $deliveredAmount : 3000.0;
        $settings['min_average_order_value'] = $averageOrderValue > 0 ? $averageOrderValue : 800.0;
        $settings['dry_run_expires_minutes'] = max(5, min(1440, (int) data_get($settings, 'dry_run_expires_minutes', 30)));
        $settings['country_code'] = strtoupper(substr((string) data_get($settings, 'country_code', 'BD'), 0, 2)) ?: 'BD';
        $settings['auto_sync_enabled'] = (bool) data_get($settings, 'auto_sync_enabled', false);
        $settings['automatic_removal_enabled'] = (bool) data_get($settings, 'automatic_removal_enabled', false);
        $settings['lookalike_sync_enabled'] = (bool) data_get($settings, 'lookalike_sync_enabled', false);
        $settings['max_removal_percentage'] = max(0, min(20, (float) data_get($settings, 'max_removal_percentage', 20)));
        $settings['lookalike']['country'] = strtoupper(substr((string) data_get($settings, 'lookalike.country', 'BD'), 0, 2)) ?: 'BD';
        $settings['lookalike']['ratio'] = max(0.01, min(0.2, (float) data_get($settings, 'lookalike.ratio', 0.01)));

        $settings['auto_exclusions']['enabled'] = (bool) data_get($settings, 'auto_exclusions.enabled', true);
        $settings['auto_exclusions']['match_admin_staff_tables'] = (bool) data_get($settings, 'auto_exclusions.match_admin_staff_tables', true);
        $settings = $this->settingsNormalizer()->normalizeSettingsLists($settings);
        $settings['auto_exclusions']['phones'] = collect(data_get($settings, 'auto_exclusions.phones', []))
            ->map(fn ($phone) => $this->normalizeBangladeshPhone($phone))
            ->filter()->unique()->values()->all();

        foreach (array_keys(self::AUDIENCE_NAMES) as $audienceKey) {
            $settings['audiences'][$audienceKey]['name'] = self::AUDIENCE_NAMES[$audienceKey];
        }

        return $settings;
    }

    private function mergeSettings(array $defaults, array $stored): array
    {
        $merged = array_replace_recursive($defaults, $stored);

        foreach ([
            'auto_exclusions.keywords',
            'auto_exclusions.emails',
            'auto_exclusions.phones',
            'status_mappings.delivered',
            'status_mappings.returned',
            'status_mappings.cancelled',
        ] as $path) {
            if (data_get($stored, $path) !== null) {
                data_set($merged, $path, data_get($stored, $path, []));
            }
        }

        return $merged;
    }

    private function saveSettings(array $settings): void
    {
        if (! Schema::hasTable('business_settings') || ! Schema::hasColumn('business_settings', 'value')) {
            return;
        }

        $identity = [];
        if (Schema::hasColumn('business_settings', 'key')) {
            $identity['key'] = self::SETTING_KEY;
        } elseif (Schema::hasColumn('business_settings', 'type')) {
            $identity['type'] = self::SETTING_KEY;
        }

        if ($identity === []) {
            return;
        }

        $payload = [
            'value' => json_encode($settings, JSON_UNESCAPED_SLASHES),
        ];

        if (Schema::hasColumn('business_settings', 'key')) {
            $payload['key'] = self::SETTING_KEY;
        }

        if (Schema::hasColumn('business_settings', 'type')) {
            $payload['type'] = self::SETTING_KEY;
        }

        BusinessSetting::query()
            ->withoutGlobalScopes()
            ->updateOrCreate($identity, $payload);
    }

    private function normalizeStatus(string $status): string
    {
        return trim(strtolower(str_replace([' ', '-'], '_', $status)));
    }

    private function statusGroup(string $status, array $statusMappings): ?string
    {
        $status = $this->normalizeStatus($status);

        foreach ($statusMappings as $group => $statuses) {
            if (in_array($status, $statuses, true)) {
                return $group;
            }
        }

        return null;
    }

    private function loadQualityFlags(): array
    {
        if (! Schema::hasTable('customer_quality_flags')) {
            return [];
        }

        return DB::table('customer_quality_flags')
            ->select(['user_id', 'is_blacklisted', 'is_fake'])
            ->get()
            ->keyBy('user_id')
            ->map(fn ($row) => [
                'is_blacklisted' => (bool) $row->is_blacklisted,
                'is_fake' => (bool) $row->is_fake,
            ])
            ->all();
    }

    private function loadInternalIdentities(array $settings): array
    {
        $identities = ['phones' => [], 'emails' => []];

        if (! (bool) data_get($settings, 'auto_exclusions.enabled', true)) {
            return $identities;
        }

        foreach ((array) data_get($settings, 'auto_exclusions.phones', []) as $phone) {
            if ($normalized = $this->normalizeBangladeshPhone($phone)) {
                $identities['phones'][$normalized] = $this->exclusionEvidence('configured', 'business_settings', 'phone', $phone, 'configured_internal_phone');
            }
        }
        foreach ((array) data_get($settings, 'auto_exclusions.emails', []) as $email) {
            if ($normalized = $this->normalizePlainEmail($email)) {
                $identities['emails'][$normalized] = $this->exclusionEvidence('configured', 'business_settings', 'email', $email, 'configured_internal_email');
            }
        }

        if (! (bool) data_get($settings, 'auto_exclusions.match_admin_staff_tables', true)) {
            return $identities;
        }

        foreach (['admins', 'admin_users', 'staff', 'employees'] as $table) {
            if (! Schema::hasTable($table)) {
                continue;
            }
            $columns = collect(['email', 'phone', 'mobile', 'contact_number', 'contact_person_number'])
                ->filter(fn ($column) => Schema::hasColumn($table, $column))->values()->all();
            if ($columns === []) {
                continue;
            }
            DB::table($table)->select($columns)->orderBy(Schema::hasColumn($table, 'id') ? 'id' : $columns[0])
                ->chunk(500, function (Collection $rows) use (&$identities, $columns, $table) {
                    foreach ($rows as $row) {
                        foreach ($columns as $column) {
                            $value = data_get($row, $column);
                            if ($column === 'email') {
                                if ($normalized = $this->normalizePlainEmail($value)) {
                                    $identities['emails'][$normalized] = $this->exclusionEvidence('table_match', $table, $column, $value, $table.'_email');
                                }
                            } elseif ($normalized = $this->normalizeBangladeshPhone($value)) {
                                $identities['phones'][$normalized] = $this->exclusionEvidence('table_match', $table, $column, $value, $table.'_phone');
                            }
                        }
                    }
                });
        }

        return $identities;
    }

    private function autoExclusionForUser(object $user, array $settings, array $internalIdentities): array
    {
        if (! (bool) data_get($settings, 'auto_exclusions.enabled', true)) {
            return $this->notExcluded();
        }

        foreach (['is_admin', 'is_staff', 'is_internal', 'is_test'] as $column) {
            if (data_get($user, $column) !== null && filter_var(data_get($user, $column), FILTER_VALIDATE_BOOLEAN)) {
                return $this->excluded($this->exclusionEvidence('user_flag', 'users', $column, data_get($user, $column), $column));
            }
        }

        foreach (['user_type', 'role', 'type', 'account_type'] as $column) {
            $value = strtolower(trim((string) data_get($user, $column, '')));
            if (in_array($value, ['admin', 'staff', 'employee', 'internal', 'test', 'demo', 'own_store'], true)) {
                return $this->excluded($this->exclusionEvidence('user_role', 'users', $column, $value, $column.':'.$value));
            }
        }

        return $this->autoExclusionForContact([
            'name' => trim((string) data_get($user, 'f_name').' '.(string) data_get($user, 'l_name')) ?: (string) data_get($user, 'name', ''),
            'phone' => data_get($user, 'phone'),
            'email' => data_get($user, 'email'),
        ], $settings, $internalIdentities, (int) data_get($user, 'id', 0));
    }

    private function autoExclusionForContact(array $contact, array $settings, array $internalIdentities, ?int $userId = null): array
    {
        if (! (bool) data_get($settings, 'auto_exclusions.enabled', true)) {
            return $this->notExcluded();
        }

        $phone = $this->normalizeBangladeshPhone($contact['phone'] ?? '');
        if ($phone && isset($internalIdentities['phones'][$phone])) {
            return $this->excluded($internalIdentities['phones'][$phone]);
        }
        $email = $this->normalizePlainEmail($contact['email'] ?? '');
        if ($email && isset($internalIdentities['emails'][$email])) {
            return $this->excluded($internalIdentities['emails'][$email]);
        }

        foreach (['name', 'email'] as $field) {
            $value = (string) ($contact[$field] ?? '');
            foreach ((array) data_get($settings, 'auto_exclusions.keywords', []) as $keyword) {
                if ($this->containsSafeToken($value, (string) $keyword)) {
                    return $this->excluded($this->exclusionEvidence('keyword', $userId ? 'users' : 'order_contact', $field, $value, 'keyword:'.mb_strtolower(trim((string) $keyword))));
                }
            }
        }

        return $this->notExcluded();
    }

    public function containsSafeToken(string $value, string $keyword): bool
    {
        $keyword = mb_strtolower(trim($keyword));
        if ($keyword === '') {
            return false;
        }

        return preg_match('/(?<![\pL\pN])'.preg_quote($keyword, '/').'(?![\pL\pN])/iu', $value) === 1;
    }

    private function normalizePlainEmail(mixed $email): ?string
    {
        $email = strtolower(trim((string) $email));

        return filter_var($email, FILTER_VALIDATE_EMAIL) !== false ? $email : null;
    }

    private function exclusionEvidence(string $source, string $table, string $field, mixed $value, string $reason): array
    {
        return ['source' => $source, 'table' => $table, 'field' => $field, 'value' => (string) $value, 'reason' => $reason];
    }

    private function excluded(array $evidence): array
    {
        return ['excluded' => true, 'reason' => (string) $evidence['reason'], 'evidence' => $evidence];
    }

    private function notExcluded(): array
    {
        return ['excluded' => false, 'reason' => '', 'evidence' => []];
    }

    private function userSeedMetric(object $user, array $flag, array $settings, array $internalIdentities): array
    {
        $phone = (string) data_get($user, 'phone', '');
        $normalizedPhone = $this->normalizeBangladeshPhone($phone);
        $phoneHash = $this->hashPhone($normalizedPhone);
        $metricKey = $phoneHash ? 'phone:'.$phoneHash : 'user:'.(int) $user->id;
        $externalIdHash = hash('sha256', 'customer:'.(int) $user->id);
        $statusColumn = $this->userStatusColumn();
        $userStatus = $statusColumn ? data_get($user, $statusColumn, 1) : 1;
        $autoExclusion = $this->autoExclusionForUser($user, $settings, $internalIdentities);
        $phoneValidation = $this->phoneValidation($phone);

        return $this->blankMetric([
            'metric_key' => $metricKey,
            'user_id' => (int) $user->id,
            'customer_name' => trim((string) data_get($user, 'f_name').' '.(string) data_get($user, 'l_name')) ?: (string) data_get($user, 'name', ''),
            'phone' => $phone,
            'normalized_phone' => $normalizedPhone,
            'phone_hash' => $phoneHash,
            'phone_validation_reason' => $phoneValidation['reason'],
            'customer_email' => strtolower(trim((string) data_get($user, 'email'))),
            'email_hash' => $this->hashEmail(data_get($user, 'email')),
            'external_id_hash' => $externalIdHash,
            'country_code' => strtoupper((string) data_get($settings, 'country_code', 'BD')),
            'is_blacklisted' => (bool) data_get($flag, 'is_blacklisted', false),
            'is_fake' => (bool) data_get($flag, 'is_fake', false),
            'is_blocked' => (int) $userStatus === 0,
            'is_internal' => $autoExclusion['excluded'],
            'internal_exclusion_reason' => $autoExclusion['reason'],
            'internal_exclusion_evidence' => $autoExclusion['evidence'],
        ]);
    }

    private function guestSeedMetric(string $metricKey, array $contact, array $settings, array $internalIdentities): array
    {
        $normalizedPhone = $this->normalizeBangladeshPhone($contact['phone'] ?? '');
        $phoneHash = $this->hashPhone($normalizedPhone);
        $autoExclusion = $this->autoExclusionForContact($contact, $settings, $internalIdentities);
        $phoneValidation = $this->phoneValidation($contact['phone'] ?? '');

        return $this->blankMetric([
            'metric_key' => $metricKey,
            'user_id' => null,
            'customer_name' => (string) ($contact['name'] ?: 'Guest Customer'),
            'phone' => (string) ($contact['phone'] ?? ''),
            'normalized_phone' => $normalizedPhone,
            'phone_hash' => $phoneHash,
            'phone_validation_reason' => $phoneValidation['reason'],
            'customer_email' => strtolower(trim((string) ($contact['email'] ?? ''))),
            'email_hash' => $this->hashEmail($contact['email'] ?? ''),
            'external_id_hash' => $phoneHash ? hash('sha256', 'guest-phone:'.$normalizedPhone) : hash('sha256', $metricKey),
            'country_code' => strtoupper((string) data_get($settings, 'country_code', 'BD')),
            'is_internal' => $autoExclusion['excluded'],
            'internal_exclusion_reason' => $autoExclusion['reason'],
            'internal_exclusion_evidence' => $autoExclusion['evidence'],
        ]);
    }

    private function blankMetric(array $data): array
    {
        return array_replace([
            'metric_key' => '',
            'user_id' => null,
            'customer_name' => '',
            'phone' => '',
            'normalized_phone' => null,
            'phone_hash' => null,
            'customer_email' => '',
            'phone_validation_reason' => null,
            'email_hash' => null,
            'external_id_hash' => null,
            'country_code' => 'BD',
            'total_orders' => 0,
            'delivered_orders' => 0,
            'returned_orders' => 0,
            'cancelled_orders' => 0,
            'delivered_amount' => 0.0,
            'average_order_value' => 0.0,
            'receive_rate' => 0.0,
            'return_rate' => 0.0,
            'cancel_rate' => 0.0,
            'quality_score' => 0.0,
            'lookalike_value' => 0.0,
            'quality_status' => 'standard',
            'is_high_quality' => false,
            'is_high_return' => false,
            'is_high_cancel' => false,
            'classification_reasons' => [],
            'is_blacklisted' => false,
            'is_fake' => false,
            'is_blocked' => false,
            'is_internal' => false,
            'internal_exclusion_reason' => '',
            'internal_exclusion_evidence' => [],
            'source_order_count' => 0,
        ], $data);
    }

    private function finalizeMetric(array $metric, array $settings, string $orderScopeChecksum, $calculatedAt): array
    {
        $rates = $this->rates((int) $metric['delivered_orders'], (int) $metric['returned_orders']);
        $averageOrderValue = (int) $metric['delivered_orders'] > 0
            ? round((float) $metric['delivered_amount'] / (int) $metric['delivered_orders'], 2)
            : 0.0;
        $finalOrderCount = (int) $metric['delivered_orders']
            + (int) $metric['returned_orders']
            + (int) $metric['cancelled_orders'];
        $cancelRate = $this->cancelRate((int) $metric['cancelled_orders'], $finalOrderCount);
        $excludedInternal = (bool) ($metric['is_internal'] ?? false);
        $excluded = (bool) ($metric['is_blacklisted'] || $metric['is_fake'] || $metric['is_blocked'] || $excludedInternal);
        $classification = $this->classificationBreakdown(
            (int) $metric['delivered_orders'],
            (int) $metric['returned_orders'],
            (int) $metric['cancelled_orders'],
            (float) $metric['delivered_amount'],
            $averageOrderValue,
            $excluded,
            $settings
        );
        $qualityScore = $classification['quality_score'];
        $qualityStatus = $excludedInternal ? 'excluded_internal' : $this->classifyFromMetrics(
            (int) $metric['delivered_orders'],
            (int) $metric['returned_orders'],
            $excluded,
            $settings,
            (float) $metric['delivered_amount'],
            $averageOrderValue,
            (int) $metric['cancelled_orders']
        );
        $lookalikeValue = round(max((float) $metric['delivered_amount'], $averageOrderValue, 1) * max(0.01, $qualityScore / 100), 2);
        $now = now();

        return array_replace($metric, [
            'customer_name' => trim((string) $metric['customer_name']) ?: 'Guest Customer',
            'phone' => (string) $metric['phone'],
            'country_code' => strtoupper((string) ($metric['country_code'] ?: 'BD')),
            'average_order_value' => $averageOrderValue,
            'receive_rate' => $rates['receive_rate'],
            'return_rate' => $rates['return_rate'],
            'cancel_rate' => $cancelRate,
            'quality_score' => $qualityScore,
            'lookalike_value' => $lookalikeValue,
            'quality_status' => $qualityStatus,
            'is_high_quality' => $classification['is_high_quality'],
            'is_high_return' => $classification['is_high_return'],
            'is_high_cancel' => $classification['is_high_cancel'],
            'classification_reasons' => json_encode($classification['reasons'], JSON_UNESCAPED_SLASHES),
            'internal_exclusion_evidence' => json_encode((array) ($metric['internal_exclusion_evidence'] ?? []), JSON_UNESCAPED_SLASHES),
            'order_scope_checksum' => $orderScopeChecksum,
            'calculated_at' => $calculatedAt,
            'created_at' => $now,
            'updated_at' => $now,
        ]);
    }

    private function orderMetricKey(object $order, array $contact, array $userMetricKeys, array $settings): string
    {
        $normalizedPhone = $this->normalizeBangladeshPhone($contact['phone'] ?? '');
        $phoneHash = $this->hashPhone($normalizedPhone);

        if ($phoneHash) {
            return 'phone:'.$phoneHash;
        }

        $userId = $this->orderUserId($order);
        if ($userId && isset($userMetricKeys[$userId])) {
            return $userMetricKeys[$userId];
        }

        return 'order:'.(int) data_get($order, 'id');
    }

    private function orderUserId(object $order): ?int
    {
        foreach (['user_id', 'customer_id'] as $column) {
            $value = data_get($order, $column);
            if (is_numeric($value) && (int) $value > 0) {
                return (int) $value;
            }
        }

        return null;
    }

    private function orderContact(object $order): array
    {
        $contact = [
            'name' => '',
            'phone' => '',
            'email' => '',
        ];

        foreach (['delivery_address', 'receiver_details', 'shipping_address_data'] as $field) {
            $decoded = $this->decodePossibleJson(data_get($order, $field));
            if (! is_array($decoded)) {
                continue;
            }

            $contact['name'] = $contact['name'] ?: (string) data_get($decoded, 'contact_person_name', data_get($decoded, 'name', ''));
            $contact['phone'] = $contact['phone'] ?: (string) data_get($decoded, 'contact_person_number', data_get($decoded, 'phone', ''));
            $contact['email'] = $contact['email'] ?: (string) data_get($decoded, 'contact_person_email', data_get($decoded, 'email', ''));
        }

        foreach ([
            'contact_person_name' => 'name',
            'customer_name' => 'name',
            'name' => 'name',
            'contact_person_number' => 'phone',
            'phone' => 'phone',
            'contact_person_email' => 'email',
            'email' => 'email',
        ] as $column => $target) {
            if (blank($contact[$target]) && filled(data_get($order, $column))) {
                $contact[$target] = (string) data_get($order, $column);
            }
        }

        return $contact;
    }

    private function decodePossibleJson(mixed $value): mixed
    {
        if (is_array($value)) {
            return $value;
        }

        if ($value instanceof \stdClass) {
            return (array) $value;
        }

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

        $decoded = json_decode($value, true);

        return json_last_error() === JSON_ERROR_NONE ? $decoded : null;
    }

    private function orderAmount(object $order, string $normalizedStatus): float
    {
        if ($this->isPartialDeliveredStatus($normalizedStatus)) {
            $collectedAmount = data_get($order, 'cq_shipment_cod_collected_amount');
            if ($collectedAmount !== null && is_numeric($collectedAmount)) {
                return max(0, (float) $collectedAmount);
            }

            $collectionStatus = $this->normalizeStatus((string) data_get($order, 'cq_shipment_cod_collection_status'));
            $collectableAmount = data_get($order, 'cq_shipment_collectable_amount');
            if (in_array($collectionStatus, ['collected', 'cod_collected', 'cash_collected', 'paid', 'settled'], true)
                && $collectableAmount !== null
                && is_numeric($collectableAmount)
            ) {
                return max(0, (float) $collectableAmount);
            }

            return 0.0;
        }

        return (float) data_get($order, 'order_amount', 0);
    }

    private function isPartialDeliveredStatus(string $status): bool
    {
        return in_array($status, [
            'partial_delivered',
            'partial_delivered_approval_pending',
            'partially_delivered',
            'partially_delivered_approval_pending',
        ], true) || (str_contains($status, 'partial') && str_contains($status, 'deliver'));
    }

    private function availableUserColumns(): array
    {
        return collect(['id', 'f_name', 'l_name', 'name', 'phone', 'email', 'status', 'is_active', 'user_type', 'role', 'type', 'account_type', 'is_admin', 'is_staff', 'is_internal', 'is_test'])
            ->filter(fn ($column) => Schema::hasColumn('users', $column))
            ->values()
            ->all();
    }

    private function availableOrderColumns(): array
    {
        return collect([
            'id',
            'user_id',
            'customer_id',
            'order_status',
            'order_amount',
            'is_guest',
            'delivery_address',
            'receiver_details',
            'shipping_address_data',
            'contact_person_name',
            'contact_person_number',
            'contact_person_email',
            'customer_name',
            'name',
            'phone',
            'email',
            'created_at',
            'updated_at',
        ])->filter(fn ($column) => Schema::hasColumn('orders', $column))
            ->values()
            ->all();
    }

    private function userStatusColumn(): ?string
    {
        if (Schema::hasColumn('users', 'status')) {
            return 'status';
        }

        if (Schema::hasColumn('users', 'is_active')) {
            return 'is_active';
        }

        return null;
    }

    private function ordersScopeChecksum(array $settings): string
    {
        $aggregate = DB::table('orders')
            ->selectRaw('COUNT(*) as order_count')
            ->selectRaw(Schema::hasColumn('orders', 'id') ? 'MAX(id) as max_order_id' : '0 as max_order_id')
            ->selectRaw(Schema::hasColumn('orders', 'updated_at') ? 'MAX(updated_at) as max_updated_at' : 'NULL as max_updated_at')
            ->first();
        $shipmentAggregate = null;

        if (Schema::hasTable('order_courier_shipments')) {
            $shipmentQuery = DB::table('order_courier_shipments')
                ->selectRaw('COUNT(*) as shipment_count')
                ->selectRaw(Schema::hasColumn('order_courier_shipments', 'updated_at')
                    ? 'MAX(updated_at) as max_updated_at'
                    : 'NULL as max_updated_at');

            if (Schema::hasColumn('order_courier_shipments', 'cod_collected_amount')) {
                $shipmentQuery->selectRaw('SUM(cod_collected_amount) as collected_amount');
            }

            $shipmentAggregate = $shipmentQuery->first();
        }

        return $this->checksum([
            'order_count' => data_get($aggregate, 'order_count'),
            'max_order_id' => data_get($aggregate, 'max_order_id'),
            'max_updated_at' => (string) data_get($aggregate, 'max_updated_at'),
            'shipment_count' => data_get($shipmentAggregate, 'shipment_count'),
            'shipment_max_updated_at' => (string) data_get($shipmentAggregate, 'max_updated_at'),
            'shipment_collected_amount' => data_get($shipmentAggregate, 'collected_amount'),
            'settings' => $this->checksumSettings($settings),
        ]);
    }

    private function metricsCacheChecksum(): string
    {
        if (! Schema::hasTable('customer_quality_metrics')) {
            return $this->checksum(['missing_metrics_table' => true]);
        }

        $aggregate = DB::table('customer_quality_metrics')
            ->selectRaw('COUNT(*) as metric_count')
            ->selectRaw('SUM(total_orders) as total_orders')
            ->selectRaw('SUM(delivered_orders) as delivered_orders')
            ->selectRaw('SUM(returned_orders) as returned_orders')
            ->selectRaw('SUM(cancelled_orders) as cancelled_orders')
            ->selectRaw('SUM(delivered_amount) as delivered_amount')
            ->selectRaw('MAX(calculated_at) as calculated_at')
            ->first();

        return $this->checksum([
            'metric_count' => data_get($aggregate, 'metric_count'),
            'total_orders' => data_get($aggregate, 'total_orders'),
            'delivered_orders' => data_get($aggregate, 'delivered_orders'),
            'returned_orders' => data_get($aggregate, 'returned_orders'),
            'cancelled_orders' => data_get($aggregate, 'cancelled_orders'),
            'delivered_amount' => data_get($aggregate, 'delivered_amount'),
            'calculated_at' => (string) data_get($aggregate, 'calculated_at'),
        ]);
    }

    private function checksumSettings(array $settings): array
    {
        return [
            'min_delivered_orders' => (int) data_get($settings, 'min_delivered_orders', 2),
            'min_receive_rate' => (float) data_get($settings, 'min_receive_rate', 90),
            'max_return_rate' => (float) data_get($settings, 'max_return_rate', 10),
            'max_cancel_rate' => (float) data_get($settings, 'max_cancel_rate', 30),
            'min_delivered_amount' => (float) data_get($settings, 'min_delivered_amount', 3000),
            'min_average_order_value' => (float) data_get($settings, 'min_average_order_value', 800),
            'auto_exclusions' => [
                'enabled' => (bool) data_get($settings, 'auto_exclusions.enabled', true),
                'match_admin_staff_tables' => (bool) data_get($settings, 'auto_exclusions.match_admin_staff_tables', true),
                'keywords' => (array) data_get($settings, 'auto_exclusions.keywords', []),
                'emails' => (array) data_get($settings, 'auto_exclusions.emails', []),
                'phones' => (array) data_get($settings, 'auto_exclusions.phones', []),
            ],
            'status_mappings' => $this->statusMappings($settings),
            'country_code' => data_get($settings, 'country_code', 'BD'),
        ];
    }

    private function checksum(array $value): string
    {
        $this->ksortRecursive($value);

        return hash('sha256', json_encode($value, JSON_UNESCAPED_SLASHES));
    }

    private function ksortRecursive(array &$array): void
    {
        ksort($array);

        foreach ($array as &$value) {
            if (is_array($value)) {
                $this->ksortRecursive($value);
            }
        }
    }

    private function emptyPaginator(Request $request): LengthAwarePaginator
    {
        $perPage = (int) config('default_pagination', 15);

        return new LengthAwarePaginator(
            collect(),
            0,
            $perPage,
            LengthAwarePaginator::resolveCurrentPage(),
            [
                'path' => $request->url(),
                'query' => $request->query(),
            ]
        );
    }
}
