<?php

namespace App\Console\Commands;

use App\Support\LegacySqlDumpReader;
use Database\Seeders\SiteSettingsSeeder;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use PDO;
use RuntimeException;
use Throwable;

class ImportLegacyDatabase extends Command
{
    protected $signature = 'legacy:import
        {path=database/trevvmne_tebkngl.sql : Path to the legacy phpMyAdmin SQL dump}
        {--admin= : Username or email of the one local administrator to preserve}
        {--force : Back up and replace local application data}';

    protected $description = 'Safely translate a legacy Basketloot MariaDB dump into the current application schema';

    private const SOURCE_TABLES = [
        'users', 'users_geo_data', 'users_referral_data', 'users_referrals',
        'providers', 'offers', 'leads', 'cashout_methods', 'cashout_requests',
        'bonuses', 'bonus_usages', 'notifications', 'tickets', 'ticket_messages',
        'settings',
    ];

    private array $userMap = [];
    private array $providerMap = [];
    private array $sourceAdminIds = [];
    private array $imported = [];

    public function handle(): int
    {
        $path = $this->absolutePath((string) $this->argument('path'));
        if (!is_file($path) || !is_readable($path)) {
            $this->error("Readable SQL dump not found: {$path}");
            return self::FAILURE;
        }
        if (config('database.default') !== 'sqlite') {
            $this->error('This guarded importer only replaces the local SQLite database.');
            return self::FAILURE;
        }

        $stagePath = tempnam(sys_get_temp_dir(), 'basketloot-legacy-');
        if ($stagePath === false) {
            $this->error('Unable to create a temporary staging database.');
            return self::FAILURE;
        }

        try {
            [$stage, $counts, $allCounts] = $this->stageDump($path, $stagePath);
            $this->renderAnalysis($path, $counts, $allCounts);

            if (!$this->option('force')) {
                $this->newLine();
                $this->warn('Analysis only: no application data was changed. Re-run with --force to import.');
                return self::SUCCESS;
            }

            $admin = $this->findAdmin();
            if ($admin === null) {
                $this->error('No local administrator was found to preserve. Import cancelled.');
                return self::FAILURE;
            }

            $backup = $this->backupDatabase();
            $this->info("Safety backup created: {$backup}");
            $this->warn("Preserving only local administrator: {$admin->username}");

            $this->replaceData($stage, (array) $admin);
            $this->validateImport($counts, (int) $admin->id);

            $this->newLine();
            $this->info('Legacy database translation completed successfully.');
            $this->table(['Entity', 'Imported'], collect($this->imported)->map(
                fn ($count, $entity) => [$entity, number_format($count)]
            )->values()->all());
            $this->line('Legacy administrator accounts were imported as normal users.');
            $this->line('Production API secrets and unsupported legacy settings were intentionally excluded.');

            return self::SUCCESS;
        } catch (Throwable $exception) {
            report($exception);
            $this->error('Import failed: '.$exception->getMessage());
            return self::FAILURE;
        } finally {
            @unlink($stagePath);
        }
    }

    /** @return array{PDO, array<string, int>, array<string, int>} */
    private function stageDump(string $path, string $stagePath): array
    {
        $this->info('Analyzing and staging the legacy dump...');
        $stage = new PDO('sqlite:'.$stagePath, null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
        $stage->exec('CREATE TABLE legacy_rows (table_name TEXT NOT NULL, source_id INTEGER, payload TEXT NOT NULL)');
        $stage->exec('CREATE INDEX legacy_rows_table_id ON legacy_rows (table_name, source_id)');
        $insert = $stage->prepare('INSERT INTO legacy_rows (table_name, source_id, payload) VALUES (?, ?, ?)');
        $wanted = array_flip(self::SOURCE_TABLES);
        $counts = [];
        $allCounts = [];

        $stage->beginTransaction();
        LegacySqlDumpReader::read($path, function (string $table, array $row) use ($insert, $wanted, &$counts, &$allCounts): void {
            $allCounts[$table] = ($allCounts[$table] ?? 0) + 1;
            if (!isset($wanted[$table])) {
                return;
            }
            $json = json_encode($row, JSON_UNESCAPED_SLASHES | JSON_UNESCAPED_UNICODE | JSON_INVALID_UTF8_SUBSTITUTE);
            if ($json === false) {
                throw new RuntimeException("Unable to encode a {$table} row for staging.");
            }
            $insert->execute([$table, $row['id'] ?? null, $json]);
            $counts[$table] = ($counts[$table] ?? 0) + 1;
        });
        $stage->commit();
        ksort($counts);
        ksort($allCounts);

        return [$stage, $counts, $allCounts];
    }

    private function renderAnalysis(string $path, array $counts, array $allCounts): void
    {
        $this->line('Dump: '.$path);
        $this->line('Size: '.number_format(filesize($path) / 1024 / 1024, 2).' MiB');
        $this->table(['Mapped legacy table', 'Rows'], collect($counts)->map(
            fn ($count, $table) => [$table, number_format($count)]
        )->values()->all());

        $unsupported = array_diff_key($allCounts, array_flip(self::SOURCE_TABLES));
        $unsupportedRows = array_sum($unsupported);
        $this->line(count($unsupported).' legacy-only tables ('.number_format($unsupportedRows).' rows) have no safe current-schema target.');
        if ($unsupported !== []) {
            $this->table(['Legacy-only table', 'Rows'], collect($unsupported)->map(
                fn ($count, $table) => [$table, number_format($count)]
            )->values()->all());
        }
    }

    private function replaceData(PDO $stage, array $admin): void
    {
        $driver = DB::connection()->getDriverName();
        $this->setForeignKeys($driver, false);

        try {
            DB::transaction(function () use ($stage, $admin): void {
                $this->clearApplicationData((int) $admin['id']);
                (new SiteSettingsSeeder())->run();

                $this->importUsers($stage, $admin);
                $this->importUserMetadata($stage);
                $this->importProviders($stage);
                $this->importOffers($stage);
                $this->importLeads($stage);
                $this->importPayments($stage);
                $this->importWithdrawals($stage);
                $this->importBonuses($stage);
                $this->importNotifications($stage);
                $this->importTickets($stage);
                $this->importReferrals($stage);
                $this->importSafeSettings($stage);
                $this->refreshUserTotals((int) $admin['id']);
            }, 1);
        } finally {
            $this->setForeignKeys($driver, true);
        }
    }

    private function clearApplicationData(int $adminId): void
    {
        $tables = [
            'bonus_histories', 'bonuses', 'callback_logs', 'campaigns', 'face_verifications',
            'failed_jobs', 'lead_rules', 'leaderboard_awards', 'notifications', 'offerwall_clicks',
            'provider_offers', 'offerwalls', 'password_resets', 'payments', 'personal_access_tokens',
            'referral_events', 'site_settings', 'ticket_messages', 'support_tickets', 'withdrawals', 'leads',
        ];
        foreach ($tables as $table) {
            if (Schema::hasTable($table)) {
                DB::table($table)->delete();
            }
        }
        DB::table('users')->where('id', '<>', $adminId)->delete();
    }

    private function importUsers(PDO $stage, array $admin): void
    {
        $usedUsernames = [mb_strtolower((string) $admin['username']) => true];
        $usedEmails = [mb_strtolower((string) $admin['email']) => true];
        $nextId = max((int) $admin['id'], (int) $stage->query("SELECT COALESCE(MAX(source_id), 0) FROM legacy_rows WHERE table_name='users'")->fetchColumn()) + 1;
        $rows = [];

        foreach ($this->rows($stage, 'users') as $source) {
            $sourceId = (int) $source['id'];
            if (($source['role'] ?? 'user') === 'admin') {
                $this->sourceAdminIds[$sourceId] = true;
            }

            if (mb_strtolower((string) ($source['email'] ?? '')) === mb_strtolower((string) $admin['email'])
                || mb_strtolower((string) ($source['username'] ?? '')) === mb_strtolower((string) $admin['username'])) {
                $this->userMap[$sourceId] = (int) $admin['id'];
                continue;
            }

            $id = $sourceId === (int) $admin['id'] ? $nextId++ : $sourceId;
            $username = $this->uniqueUsername((string) ($source['username'] ?: 'user_'.$sourceId), $sourceId, $usedUsernames);
            $email = $this->uniqueEmail((string) $source['email'], $sourceId, $usedEmails);
            $this->userMap[$sourceId] = $id;
            $rows[] = [
                'id' => $id,
                'username' => $username,
                'email' => $email,
                'email_verified_at' => $source['email_verified_at'],
                'password' => (string) $source['password'],
                'profile_photo_path' => null,
                'is_admin' => 0,
                'is_banned' => (int) ($source['is_banned'] ?? 0),
                'current_points' => (float) ($source['points'] ?? 0),
                'today_points' => 0,
                'total_points' => 0,
                'user_agent' => null,
                'registered_ip' => null,
                'last_login_ip' => null,
                'last_seen_at' => $source['last_activity_at'],
                'remember_token' => $source['remember_token'],
                'created_at' => $source['created_at'],
                'updated_at' => $source['updated_at'],
                'is_profile_private' => (int) ($source['privacy'] ?? 0),
                'balance_display' => 'usd',
                'referral_code' => null,
                'referred_by' => null,
                'referral_earnings' => 0,
                'two_factor_enabled' => 0,
                'two_factor_method' => null,
                'two_factor_secret' => null,
                'two_factor_confirmed_at' => null,
                'role' => 'user',
                'admin_permissions' => null,
            ];
            $this->flush($rows, 'users');
        }
        $this->flush($rows, 'users', true);
        $this->imported['users'] = count($this->userMap) - (in_array((int) $admin['id'], $this->userMap, true) ? 1 : 0);
    }

    private function importUserMetadata(PDO $stage): void
    {
        foreach ($this->rows($stage, 'users_geo_data') as $row) {
            if (!isset($this->userMap[(int) $row['user_id']])) continue;
            DB::table('users')->where('id', $this->userMap[(int) $row['user_id']])->update([
                'registered_ip' => $row['ip'],
                'last_login_ip' => $row['latest_login_ip'] ?: $row['ip'],
                'user_agent' => mb_substr((string) ($row['latest_user_agent'] ?: $row['user_agent']), 0, 255),
                'last_seen_at' => $row['latest_login_at'],
            ]);
        }

        $usedCodes = [];
        foreach ($this->rows($stage, 'users_referral_data') as $row) {
            if (!isset($this->userMap[(int) $row['user_id']])) continue;
            $base = mb_substr((string) $row['referral_code'], 0, 24);
            $code = $base;
            if ($code === '' || isset($usedCodes[mb_strtolower($code)])) {
                $code = mb_substr('ref'.(int) $row['user_id'], 0, 24);
            }
            $usedCodes[mb_strtolower($code)] = true;
            DB::table('users')->where('id', $this->userMap[(int) $row['user_id']])->update([
                'referral_code' => $code,
                'referral_earnings' => (float) ($row['referral_points'] ?? 0),
            ]);
        }
    }

    private function importProviders(PDO $stage): void
    {
        $rows = [];
        foreach ($this->rows($stage, 'providers') as $source) {
            $id = (int) $source['id'];
            $this->providerMap[mb_strtolower(trim((string) $source['name']))] = $id;
            $rows[] = [
                'id' => $id,
                'name' => (string) $source['name'],
                'iframe_url' => (string) ($source['url'] ?: '#'),
                'photo_path' => (string) ($source['image'] ?: 'branding/basketloot-logo.svg'),
                'is_active' => (int) ($source['is_active'] ?? 1),
                'created_at' => $source['created_at'], 'updated_at' => $source['updated_at'],
                'type' => (string) ($source['type'] ?: 'offer'),
                'launch_mode' => 'iframe',
                'background_color' => (string) ($source['bg_color'] ?: '#202637'),
                'badge' => $source['badge'],
                'badge_color' => (string) ($source['badge_bg_color'] ?: '#02d46f'),
                'show_rate' => (int) ($source['show_rate'] ?? 1),
                'rate' => $source['rate'] === null ? null : (string) $source['rate'],
                'unlock_level' => (int) ($source['unlock_level'] ?? 1),
                'is_mobile' => (int) ($source['is_mobile'] ?? 0),
                'sdk_data' => $source['sdk_data'],
                'description' => 'Imported legacy '.($source['type'] ?: 'offer').' provider.',
            ];
            $this->flush($rows, 'offerwalls');
        }
        $this->flush($rows, 'offerwalls', true);
        $this->imported['offerwalls'] = count($this->providerMap);
    }

    private function importOffers(PDO $stage): void
    {
        $rows = [];
        $count = 0;
        foreach ($this->rows($stage, 'offers') as $source) {
            $provider = mb_strtolower(trim((string) $source['provider']));
            $offerwallId = $this->providerMap[$provider] ?? $this->createPlaceholderProvider((string) $source['provider']);
            $categories = $this->jsonArray($source['categories'] ?? null);
            $countries = $this->jsonArray($source['countries'] ?? null);
            $rows[] = [
                'id' => (int) $source['id'],
                'offerwall_id' => $offerwallId,
                'provider_offer_id' => (string) $source['offer_id'],
                'name' => (string) $source['title'],
                'description' => $source['description'],
                'requirements' => $source['requirements'],
                'steps' => $source['instructions'],
                'rewards' => $source['events'],
                'reward_points' => (float) ($source['points'] ?? 0),
                'payout' => (float) ($source['payout'] ?? 0),
                'icon_url' => $source['image'], 'icon_path' => null,
                'direct_url' => $source['link'], 'launch_mode' => 'direct',
                'category' => (string) ($categories[0] ?? 'other'),
                'devices' => $source['devices'],
                'countries' => implode(',', $countries),
                'is_active' => 1, 'is_featured' => 0,
                'is_hot' => (int) ($source['hot_offer'] ?? 0), 'sort_order' => 0,
                'last_synced_at' => $source['updated_at'],
                'raw_payload' => json_encode($source, JSON_UNESCAPED_SLASHES | JSON_INVALID_UTF8_SUBSTITUTE),
                'created_at' => $source['created_at'], 'updated_at' => $source['updated_at'],
                'curation_locked' => 0, 'deleted_at' => null,
            ];
            $count++;
            $this->flush($rows, 'provider_offers');
        }
        $this->flush($rows, 'provider_offers', true);
        $this->imported['provider offers'] = $count;
    }

    private function importLeads(PDO $stage): void
    {
        $rows = [];
        $seen = [];
        $count = 0;
        foreach ($this->rows($stage, 'leads') as $source) {
            $sourceUser = (int) $source['user_id'];
            if (!isset($this->userMap[$sourceUser])) continue;
            $company = (string) ($source['provider'] ?: $source['name'] ?: 'Legacy');
            $trx = trim((string) ($source['offer_trx_id'] ?? '')) ?: 'legacy-'.$source['id'];
            $key = mb_strtolower($company).'|'.$trx;
            if (isset($seen[$key])) $trx .= '-'.$source['id'];
            $seen[mb_strtolower($company).'|'.$trx] = true;
            $status = in_array($source['status'], ['approved', 'pending', 'rejected'], true) ? $source['status'] : 'pending';
            $rows[] = [
                'id' => (int) $source['id'], 'user_id' => $this->userMap[$sourceUser],
                'company' => $company, 'offer_id' => $source['offer_id'],
                'offer_name' => $source['offer_name'] ?: $source['name'],
                'points' => (string) ($source['points'] ?? 0),
                'original_points' => (string) ($source['points'] ?? 0),
                'credited_points' => $status === 'approved' ? (string) ($source['points'] ?? 0) : '0',
                'payout' => (string) ($source['payout'] ?? 0),
                'ip' => $source['ip'], 'country' => $source['country_code'], 'trx' => $trx,
                'created_at' => $source['created_at'], 'updated_at' => $source['updated_at'],
                'type' => (string) ($source['type'] ?: 'offer'), 'status' => $status,
                'admin_note' => $source['reason'],
                'reviewed_at' => $status === 'pending' ? null : $source['updated_at'],
                'reviewed_by' => null, 'points_reversed_at' => $status === 'rejected' ? $source['updated_at'] : null,
            ];
            $count++;
            $this->flush($rows, 'leads');
        }
        $this->flush($rows, 'leads', true);
        DB::table('leads')
            ->where('credited_points', '>', 0)
            ->orderBy('id')
            ->chunkById(500, function ($leads): void {
                DB::table('lead_adjustments')->insertOrIgnore($leads->map(fn ($lead): array => [
                    'lead_id' => $lead->id,
                    'admin_id' => null,
                    'token' => 'legacy-import:'.$lead->id,
                    'action' => 'legacy_credit',
                    'points' => $lead->credited_points,
                    'credited_before' => 0,
                    'credited_after' => $lead->credited_points,
                    'requested_unit' => 'coins',
                    'requested_amount' => $lead->credited_points,
                    'note' => 'Opening credited balance imported from the legacy database.',
                    'created_at' => $lead->created_at,
                    'updated_at' => $lead->updated_at,
                ])->all());
            });
        $this->imported['transactions'] = $count;
    }

    private function importPayments(PDO $stage): void
    {
        $rows = [];
        $count = 0;
        foreach ($this->rows($stage, 'cashout_methods') as $source) {
            $rows[] = [
                'id' => (int) $source['id'], 'name' => (string) $source['name'],
                'currency' => 'USD', 'photo_path' => $source['image'],
                'is_active' => (int) ($source['is_active'] ?? 1),
                'created_at' => $source['created_at'], 'updated_at' => $source['updated_at'],
            ];
            $count++;
            $this->flush($rows, 'payments');
        }
        $this->flush($rows, 'payments', true);
        $this->imported['payment providers'] = $count;
    }

    private function importWithdrawals(PDO $stage): void
    {
        $rows = [];
        $count = 0;
        foreach ($this->rows($stage, 'cashout_requests') as $source) {
            $sourceUser = (int) $source['user_id'];
            if (!isset($this->userMap[$sourceUser])) continue;
            $status = in_array($source['status'], ['pending', 'approved', 'rejected'], true) ? $source['status'] : 'pending';
            $rows[] = [
                'id' => (int) $source['id'], 'user_id' => $this->userMap[$sourceUser],
                'method' => (string) $source['method_name'], 'amount' => (float) $source['amount'],
                'account' => (string) $source['address'], 'status' => $status, 'ip' => null,
                'created_at' => $source['created_at'], 'updated_at' => $source['updated_at'],
                'verification_status' => 'approved',
            ];
            $count++;
            $this->flush($rows, 'withdrawals');
        }
        $this->flush($rows, 'withdrawals', true);
        $this->imported['cashout requests'] = $count;
    }

    private function importBonuses(PDO $stage): void
    {
        $rows = [];
        $bonusIds = [];
        foreach ($this->rows($stage, 'bonuses') as $source) {
            $bonusIds[(int) $source['id']] = true;
            $rows[] = [
                'id' => (int) $source['id'], 'code' => (string) $source['code'],
                'points' => (int) $source['reward_points'], 'max_uses' => (int) $source['max_uses'],
                'is_active' => (int) ($source['is_active'] ?? 1),
                'created_at' => $source['created_at'], 'updated_at' => $source['updated_at'],
            ];
            $this->flush($rows, 'bonuses');
        }
        $this->flush($rows, 'bonuses', true);
        $history = [];
        $seen = [];
        $historyCount = 0;
        foreach ($this->rows($stage, 'bonus_usages') as $source) {
            $user = $this->userMap[(int) $source['user_id']] ?? null;
            $bonus = (int) $source['bonus_id'];
            $key = $user.'|'.$bonus;
            if (!$user || !isset($bonusIds[$bonus]) || isset($seen[$key])) continue;
            $seen[$key] = true;
            $history[] = [
                'id' => (int) $source['id'], 'user_id' => $user, 'bonus_id' => $bonus,
                'created_at' => $source['created_at'], 'updated_at' => $source['updated_at'],
            ];
            $historyCount++;
            $this->flush($history, 'bonus_histories');
        }
        $this->flush($history, 'bonus_histories', true);
        $this->imported['bonuses'] = count($bonusIds);
        $this->imported['bonus redemptions'] = $historyCount;
    }

    private function importNotifications(PDO $stage): void
    {
        $rows = [];
        $count = 0;
        foreach ($this->rows($stage, 'notifications') as $source) {
            $user = $this->userMap[(int) $source['user_id']] ?? null;
            if (!$user) continue;
            $rows[] = [
                'id' => (int) $source['id'], 'user_id' => $user,
                'title' => (string) $source['title'], 'message' => (string) $source['message'],
                'is_read' => (int) ($source['is_read'] ?? 0),
                'created_at' => $source['created_at'], 'updated_at' => $source['updated_at'],
            ];
            $count++;
            $this->flush($rows, 'notifications');
        }
        $this->flush($rows, 'notifications', true);
        $this->imported['notifications'] = $count;
    }

    private function importTickets(PDO $stage): void
    {
        $rows = [];
        $initialMessages = [];
        $ticketIds = [];
        $count = 0;
        foreach ($this->rows($stage, 'tickets') as $source) {
            $user = $this->userMap[(int) $source['user_id']] ?? null;
            if (!$user) continue;
            $id = (int) $source['id'];
            $ticketIds[$id] = true;
            $status = (int) ($source['is_resolved'] ?? 0) ? 'closed' : (string) ($source['status'] ?: 'open');
            $rows[] = [
                'id' => $id, 'user_id' => $user, 'subject' => (string) $source['title'],
                'category' => 'general', 'priority' => (string) ($source['priority'] ?: 'normal'),
                'status' => $status, 'last_reply_at' => $source['updated_at'],
                'created_at' => $source['created_at'], 'updated_at' => $source['updated_at'], 'deleted_at' => null,
            ];
            if (!empty($source['message'])) {
                $initialMessages[] = [
                    'support_ticket_id' => $id, 'user_id' => $user, 'is_admin' => 0,
                    'message' => (string) $source['message'],
                    'created_at' => $source['created_at'], 'updated_at' => $source['created_at'],
                ];
            }
            $count++;
            $this->flush($rows, 'support_tickets');
        }
        $this->flush($rows, 'support_tickets', true);
        $this->flush($initialMessages, 'ticket_messages', true);

        $messages = [];
        $messageCount = count($initialMessages);
        foreach ($this->rows($stage, 'ticket_messages') as $source) {
            $userSource = (int) $source['user_id'];
            $user = $this->userMap[$userSource] ?? null;
            $ticket = (int) $source['ticket_id'];
            if (!$user || !isset($ticketIds[$ticket])) continue;
            $messages[] = [
                'support_ticket_id' => $ticket, 'user_id' => $user,
                'is_admin' => isset($this->sourceAdminIds[$userSource]) ? 1 : 0,
                'message' => (string) $source['message'],
                'created_at' => $source['created_at'], 'updated_at' => $source['updated_at'],
            ];
            $messageCount++;
            $this->flush($messages, 'ticket_messages');
        }
        $this->flush($messages, 'ticket_messages', true);
        $this->imported['support tickets'] = $count;
        $this->imported['ticket messages'] = $messageCount;
    }

    private function importReferrals(PDO $stage): void
    {
        $events = [];
        $count = 0;
        foreach ($this->rows($stage, 'users_referrals') as $source) {
            $referred = $this->userMap[(int) $source['user_id']] ?? null;
            $referrer = $this->userMap[(int) $source['referred_by_id']] ?? null;
            if (!$referred || !$referrer || $referred === $referrer) continue;
            DB::table('users')->where('id', $referred)->update(['referred_by' => $referrer]);
            $events[] = [
                'referrer_id' => $referrer, 'referred_user_id' => $referred, 'lead_id' => null,
                'type' => 'signup', 'source_points' => 0, 'reward_points' => 0,
                'status' => 'credited', 'meta' => json_encode(['source' => 'legacy_import']),
                'created_at' => $source['created_at'], 'updated_at' => $source['updated_at'],
            ];
            $count++;
            $this->flush($events, 'referral_events');
        }
        $this->flush($events, 'referral_events', true);
        $this->imported['referral relationships'] = $count;
    }

    private function importSafeSettings(PDO $stage): void
    {
        $map = [
            'social.facebook' => 'social_facebook', 'social.twitter' => 'social_x',
            'social.telegram' => 'social_telegram', 'social.discord' => 'social_discord',
            'referral.enable_rewards' => 'referral_enabled', 'referral.points' => 'referral_signup_bonus',
            'levels.enable_rewards' => 'levels_enabled', 'leaderboard.enable_rewards' => 'leaderboard_enabled',
            'streaks.enable_rewards' => 'streaks_enabled',
            'protection.enable_max_accounts_per_ip' => 'protection_max_accounts_per_ip',
            'protection.max_accounts_per_ip' => 'protection_max_accounts_count',
            'protection.ip_change_enable' => 'protection_ip_change',
            'protection.ip_change_block_acc' => 'protection_auto_block_ip_change',
            'protection.email_custom_domain' => 'protection_custom_email_domains',
            'protection.email_verify_to_withdraw' => 'protection_verify_email_withdraw',
            'protection.country_block' => 'protection_block_countries',
        ];
        $count = 0;
        foreach ($this->rows($stage, 'settings') as $source) {
            $target = $map[(string) $source['key']] ?? null;
            if ($target === null) continue;
            $value = $this->settingScalar($source['value']);
            DB::table('site_settings')->updateOrInsert(['key' => $target], [
                'value' => $value, 'updated_at' => now(), 'created_at' => now(),
            ]);
            $count++;
        }
        $this->imported['safe settings'] = $count;
    }

    private function refreshUserTotals(int $adminId): void
    {
        DB::statement("UPDATE users SET total_points = COALESCE((SELECT SUM(CAST(credited_points AS REAL)) FROM leads WHERE leads.user_id = users.id), 0)");
        DB::statement("UPDATE users SET today_points = COALESCE((SELECT SUM(CAST(credited_points AS REAL)) FROM leads WHERE leads.user_id = users.id AND date(leads.created_at) = date('now')), 0)");
        // The preserved administrator's authentication and chosen balance remain untouched.
        DB::table('users')->where('id', $adminId)->update([
            'is_admin' => 1, 'role' => 'admin',
        ]);
    }

    private function validateImport(array $sourceCounts, int $adminId): void
    {
        $adminCount = DB::table('users')->where('is_admin', 1)->count();
        if ($adminCount !== 1 || !DB::table('users')->where('id', $adminId)->where('is_admin', 1)->exists()) {
            throw new RuntimeException('Administrator preservation check failed.');
        }
        if (($sourceCounts['users'] ?? 0) > 0 && DB::table('users')->count() < 2) {
            throw new RuntimeException('User import validation failed.');
        }
        if (($sourceCounts['offers'] ?? 0) > 0 && DB::table('provider_offers')->count() === 0) {
            throw new RuntimeException('Offer import validation failed.');
        }

        if (DB::connection()->getDriverName() === 'sqlite') {
            $violations = DB::select('PRAGMA foreign_key_check');
            if ($violations !== []) {
                throw new RuntimeException('Foreign-key validation found '.count($violations).' violation(s).');
            }
            DB::statement('PRAGMA optimize');
        }
    }

    /** @return \Generator<int, array<string, mixed>> */
    private function rows(PDO $stage, string $table): \Generator
    {
        $query = $stage->prepare('SELECT payload FROM legacy_rows WHERE table_name = ? ORDER BY source_id');
        $query->execute([$table]);
        while (($json = $query->fetchColumn()) !== false) {
            yield json_decode($json, true, 512, JSON_THROW_ON_ERROR);
        }
    }

    private function flush(array &$rows, string $table, bool $force = false): void
    {
        if ($rows !== [] && ($force || count($rows) >= 400)) {
            DB::table($table)->insert($rows);
            $rows = [];
        }
    }

    private function createPlaceholderProvider(string $name): int
    {
        $key = mb_strtolower(trim($name));
        if (isset($this->providerMap[$key])) return $this->providerMap[$key];
        $id = (int) DB::table('offerwalls')->max('id') + 1;
        DB::table('offerwalls')->insert([
            'id' => $id, 'name' => $name ?: 'Legacy Provider', 'iframe_url' => '#',
            'photo_path' => 'branding/basketloot-logo.svg', 'is_active' => 0,
            'type' => 'offer', 'launch_mode' => 'direct', 'background_color' => '#202637',
            'badge' => 'Imported', 'badge_color' => '#35bdf4', 'show_rate' => 1,
            'rate' => null, 'unlock_level' => 1, 'is_mobile' => 0, 'sdk_data' => null,
            'description' => 'Placeholder for imported API offers.', 'created_at' => now(), 'updated_at' => now(),
        ]);
        return $this->providerMap[$key] = $id;
    }

    private function findAdmin(): ?object
    {
        $query = DB::table('users')->where(function ($builder): void {
            $builder->where('is_admin', 1)->orWhere('role', 'admin');
        });
        if ($identifier = $this->option('admin')) {
            $query->where(function ($builder) use ($identifier): void {
                $builder->where('username', $identifier)->orWhere('email', $identifier);
            });
        } else {
            $query->orderByRaw("CASE WHEN lower(username) = 'snake' THEN 0 ELSE 1 END")->orderBy('id');
        }
        return $query->first();
    }

    private function backupDatabase(): string
    {
        $source = config('database.connections.sqlite.database');
        if (!is_file($source)) throw new RuntimeException('Local SQLite database is missing.');
        $directory = storage_path('app/backups');
        if (!is_dir($directory) && !mkdir($directory, 0750, true) && !is_dir($directory)) {
            throw new RuntimeException('Unable to create database backup directory.');
        }
        $target = $directory.'/pre_legacy_import_'.now()->format('Y-m-d_His').'.sqlite';
        if (!copy($source, $target)) throw new RuntimeException('Unable to create safety backup.');
        return $target;
    }

    private function setForeignKeys(string $driver, bool $enabled): void
    {
        if ($driver === 'sqlite') DB::statement('PRAGMA foreign_keys = '.($enabled ? 'ON' : 'OFF'));
        if ($driver === 'mysql') DB::statement('SET FOREIGN_KEY_CHECKS='.($enabled ? '1' : '0'));
    }

    private function uniqueUsername(string $candidate, int $sourceId, array &$used): string
    {
        $candidate = trim($candidate) ?: 'user_'.$sourceId;
        $candidate = mb_substr($candidate, 0, 50);
        if (isset($used[mb_strtolower($candidate)])) {
            $suffix = '_'.$sourceId;
            $candidate = mb_substr($candidate, 0, 50 - mb_strlen($suffix)).$suffix;
        }
        $used[mb_strtolower($candidate)] = true;
        return $candidate;
    }

    private function uniqueEmail(string $email, int $sourceId, array &$used): string
    {
        $email = trim($email);
        if ($email === '' || !str_contains($email, '@')) $email = "legacy{$sourceId}@local.invalid";
        if (isset($used[mb_strtolower($email)])) {
            [$local, $domain] = array_pad(explode('@', $email, 2), 2, 'local.invalid');
            $email = $local.'+legacy'.$sourceId.'@'.$domain;
        }
        $used[mb_strtolower($email)] = true;
        return $email;
    }

    private function jsonArray(mixed $value): array
    {
        if (!is_string($value) || $value === '') return [];
        $decoded = json_decode($value, true);
        return is_array($decoded) ? array_values($decoded) : [];
    }

    private function settingScalar(mixed $value): string
    {
        if (!is_string($value)) return (string) $value;
        $decoded = json_decode($value, true);
        if (is_bool($decoded)) return $decoded ? '1' : '0';
        if (is_scalar($decoded)) return (string) $decoded;
        return $value;
    }

    private function absolutePath(string $path): string
    {
        return str_starts_with($path, DIRECTORY_SEPARATOR) ? $path : base_path($path);
    }
}
