<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use PDO;
use RuntimeException;
use Throwable;

class ImportCurrentSqliteToMysql extends Command
{
    protected $signature = 'database:import-sqlite
        {path=storage/app/import/basketloot.sqlite : Private path to the current-schema SQLite database}
        {--force : Confirm that the empty MySQL destination may be populated}';

    protected $description = 'Copy a current-schema Basketloot SQLite database into an empty migrated MySQL database';

    private const TABLES = [
        'users',
        'offerwalls',
        'payments',
        'bonuses',
        'site_settings',
        'campaigns',
        'provider_offers',
        'leads',
        'withdrawals',
        'notifications',
        'support_tickets',
        'ticket_messages',
        'offerwall_clicks',
        'callback_logs',
        'referral_events',
        'bonus_histories',
        'face_verifications',
        'lead_rules',
        'leaderboard_awards',
        'lead_adjustments',
        'password_resets',
        'personal_access_tokens',
        'failed_jobs',
    ];

    public function handle(): int
    {
        if (DB::connection()->getDriverName() !== 'mysql') {
            $this->error('Destination must be the MySQL connection configured in .env.');
            return self::FAILURE;
        }

        if (!$this->option('force')) {
            $this->error('Import not started. Re-run with --force after verifying the destination is the empty production database.');
            return self::FAILURE;
        }

        $path = $this->absolutePath((string) $this->argument('path'));
        if (!is_file($path) || !is_readable($path)) {
            $this->error("Readable SQLite source not found: {$path}");
            return self::FAILURE;
        }

        try {
            $source = new PDO('sqlite:'.$path, null, null, [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            ]);

            $this->assertDestinationReady();
            $tables = $this->validatedTables($source);
            $sourceCounts = $this->counts($source, $tables);
            $this->displaySourceSummary($source, $sourceCounts);

            DB::beginTransaction();
            DB::statement('SET FOREIGN_KEY_CHECKS=0');

            try {
                foreach ($tables as $table) {
                    $this->copyTable($source, $table, $sourceCounts[$table]);
                }

                DB::statement('SET FOREIGN_KEY_CHECKS=1');
                $this->assertImportedCounts($sourceCounts);
                DB::commit();
            } catch (Throwable $exception) {
                DB::rollBack();
                DB::statement('SET FOREIGN_KEY_CHECKS=1');
                throw $exception;
            }

            $this->newLine();
            $this->info('SQLite-to-MySQL import completed and row counts match.');
            $this->warn('Delete the private SQLite import file from the server after final verification.');

            return self::SUCCESS;
        } catch (Throwable $exception) {
            report($exception);
            $this->error('Import failed and was rolled back: '.$exception->getMessage());

            return self::FAILURE;
        }
    }

    private function assertDestinationReady(): void
    {
        foreach (['users', 'leads', 'withdrawals', 'site_settings'] as $table) {
            if (!Schema::hasTable($table)) {
                throw new RuntimeException("Destination table {$table} is missing. Run php artisan migrate --force first.");
            }

            if (DB::table($table)->exists()) {
                throw new RuntimeException("Destination table {$table} is not empty. Import refused to protect existing data.");
            }
        }
    }

    /** @return array<int,string> */
    private function validatedTables(PDO $source): array
    {
        $sourceTables = $source->query("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")
            ->fetchAll(PDO::FETCH_COLUMN);

        foreach (self::TABLES as $table) {
            if (!in_array($table, $sourceTables, true)) {
                throw new RuntimeException("Source table {$table} is missing.");
            }
            if (!Schema::hasTable($table)) {
                throw new RuntimeException("Destination table {$table} is missing.");
            }

            $sourceColumns = array_column($source->query('PRAGMA table_info("'.$table.'")')->fetchAll(), 'name');
            $destinationColumns = Schema::getColumnListing($table);
            sort($sourceColumns);
            sort($destinationColumns);

            if ($sourceColumns !== $destinationColumns) {
                throw new RuntimeException("Column mismatch for {$table}; ensure the source and destination use this exact release.");
            }
        }

        return self::TABLES;
    }

    /** @return array<string,int> */
    private function counts(PDO $source, array $tables): array
    {
        $counts = [];
        foreach ($tables as $table) {
            $counts[$table] = (int) $source->query('SELECT COUNT(*) FROM "'.$table.'"')->fetchColumn();
        }

        return $counts;
    }

    private function copyTable(PDO $source, string $table, int $total): void
    {
        $this->line('Copying '.$table.' ('.number_format($total).')...');
        if ($total === 0) {
            return;
        }

        $statement = $source->query('SELECT * FROM "'.$table.'" ORDER BY rowid');
        $chunk = [];

        while ($row = $statement->fetch()) {
            $chunk[] = $row;
            if (count($chunk) >= 200) {
                DB::table($table)->insert($chunk);
                $chunk = [];
            }
        }

        if ($chunk !== []) {
            DB::table($table)->insert($chunk);
        }
    }

    private function assertImportedCounts(array $sourceCounts): void
    {
        foreach ($sourceCounts as $table => $expected) {
            $actual = DB::table($table)->count();
            if ($actual !== $expected) {
                throw new RuntimeException("Row-count mismatch for {$table}: expected {$expected}, imported {$actual}.");
            }
        }
    }

    private function displaySourceSummary(PDO $source, array $counts): void
    {
        $userPoints = (float) $source->query('SELECT COALESCE(SUM(current_points), 0) FROM users')->fetchColumn();
        $leadPoints = (float) $source->query('SELECT COALESCE(SUM(credited_points), 0) FROM leads')->fetchColumn();

        $this->table(['Check', 'Source value'], [
            ['Users', number_format($counts['users'])],
            ['Leads', number_format($counts['leads'])],
            ['Withdrawals', number_format($counts['withdrawals'])],
            ['User current points', number_format($userPoints, 4)],
            ['Lead credited points', number_format($leadPoints, 4)],
        ]);
    }

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

        return base_path($path);
    }
}
