<?php

use Illuminate\Foundation\Inspiring;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Artisan;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Str;
use App\Support\CardCsvImporter;

Artisan::command('inspire', function () {
    $this->comment(Inspiring::quote());
})->purpose('Display an inspiring quote');

// Import cards from CSV via CLI without a custom Console Kernel (Laravel 12 console routes)
Artisan::command('cards:import {file : Absolute path to the CSV file} {--delimiter=, : CSV delimiter} {--dry-run : Parse without writing}', function (string $file) {
    $delimiter = (string) $this->option('delimiter');
    $dryRun = (bool) $this->option('dry-run');

    $importer = new CardCsvImporter();
    try {
        [$inserted, $updated, $skipped] = $importer->import($file, $delimiter, $dryRun);
        if ($dryRun) {
            $this->info("Dry run complete.");
        }
        $this->info("Import finished. Inserted: {$inserted}, Updated: {$updated}, Skipped: {$skipped}");
    } catch (\Throwable $e) {
        $this->error($e->getMessage());
    }
})->purpose('Import cards from a CSV file (upsert by name).');

Artisan::command('cards:convert-id-to-uuid {--prepare-only : Only backfill the temporary uuid column} {--force : Skip the confirmation prompt}', function () {
    if (! Schema::hasTable('cards')) {
        $this->error('The cards table does not exist.');

        return self::FAILURE;
    }

    $idColumn = collect(DB::select('SHOW COLUMNS FROM cards LIKE "id"'))->first();

    if (! $idColumn) {
        $this->error('The cards.id column does not exist.');

        return self::FAILURE;
    }

    if (str_starts_with(strtolower((string) $idColumn->Type), 'char(36)')) {
        $this->info('cards.id is already using UUIDs.');

        return self::SUCCESS;
    }

    if (! Schema::hasColumn('cards', 'uuid')) {
        $this->error('Missing cards.uuid column. Run php artisan migrate first.');

        return self::FAILURE;
    }

    if (! Schema::hasColumn('cards', 'asset_id')) {
        $this->error('Missing cards.asset_id column. Run php artisan migrate first.');

        return self::FAILURE;
    }

    $cards = DB::table('cards')->select(['id', 'uuid', 'asset_id'])->orderBy('id')->get();

    foreach ($cards as $card) {
        $updates = [];

        if (! $card->uuid) {
            $updates['uuid'] = (string) Str::uuid();
        }

        if (! $card->asset_id) {
            $updates['asset_id'] = (int) $card->id;
        }

        if ($updates === []) {
            continue;
        }

        DB::table('cards')
            ->where('id', $card->id)
            ->update($updates);
    }

    $missingUuidCount = DB::table('cards')->whereNull('uuid')->count();

    if ($missingUuidCount > 0) {
        $this->error("Backfill incomplete. {$missingUuidCount} cards are still missing UUIDs.");

        return self::FAILURE;
    }

    $duplicateUuidRows = collect(DB::select('SELECT uuid, COUNT(*) AS total FROM cards GROUP BY uuid HAVING COUNT(*) > 1'));

    if ($duplicateUuidRows->isNotEmpty()) {
        $this->error('Duplicate UUIDs detected in cards.uuid. Aborting swap.');

        return self::FAILURE;
    }

    $this->info('Temporary UUIDs are ready for all existing cards.');

    if ($this->option('prepare-only')) {
        $this->comment('Preparation only mode finished. Run the command again without --prepare-only to swap the primary key.');

        return self::SUCCESS;
    }

    if (! $this->option('force') && ! $this->confirm('This will permanently replace cards.id with UUID values. Continue?')) {
        $this->comment('Operation cancelled.');

        return self::SUCCESS;
    }

    // MySQL requires AUTO_INCREMENT columns to remain indexed, so remove the
    // attribute before dropping the integer primary key.
    DB::statement('ALTER TABLE cards MODIFY id BIGINT UNSIGNED NOT NULL');
    DB::statement('ALTER TABLE cards DROP PRIMARY KEY');
    DB::statement('ALTER TABLE cards DROP COLUMN id');
    DB::statement('ALTER TABLE cards CHANGE uuid id CHAR(36) NOT NULL');
    DB::statement('ALTER TABLE cards ADD PRIMARY KEY (id)');

    $this->info('cards.id has been converted to UUID successfully.');

    return self::SUCCESS;
})->purpose('Backfill UUIDs for cards and swap cards.id from bigint to UUID.');