scry/tools/DbGenerator/GenerateCommand.cs

using System.ComponentModel;
using Scry.Core.Data;
using Scry.Core.Imaging;
using Scry.Core.Models;
using Scry.Core.Scryfall;
using SkiaSharp;
using Spectre.Console;
using Spectre.Console.Cli;

namespace DbGenerator;

public sealed class GenerateSettings : CommandSettings
{
    [CommandArgument(0, "[output]")]
    [Description("Output database file path")]
    [DefaultValue("card_hashes.db")]
    public string Output { get; set; } = "card_hashes.db";

    [CommandOption("-c|--count")]
    [Description("Maximum number of cards to include")]
    [DefaultValue(500)]
    public int Count { get; set; } = 500;

    [CommandOption("--include-test-cards")]
    [Description("Include priority test cards (default: true)")]
    [DefaultValue(true)]
    public bool IncludeTestCards { get; set; } = true;

    [CommandOption("--no-test-cards")]
    [Description("Exclude priority test cards")]
    public bool NoTestCards { get; set; }

    [CommandOption("-f|--force")]
    [Description("Force rebuild from scratch")]
    public bool Force { get; set; }
}

public sealed class GenerateCommand : AsyncCommand<GenerateSettings>
{
    // Cards that should be included for testing with preferred sets
    private static readonly Dictionary<string, string[]> PriorityCardsWithSets = new(StringComparer.OrdinalIgnoreCase)
    {
        // From reference_alpha/ - prefer LEA (Alpha) or LEB (Beta) for classic look
        ["Ancestral Recall"] = ["lea", "leb"],
        ["Badlands"] = ["lea", "leb"],
        ["Balance"] = ["lea", "leb"],
        ["Bayou"] = ["lea", "leb"],
        ["Birds of Paradise"] = ["lea", "leb"],
        ["Black Lotus"] = ["lea", "leb"],
        ["Channel"] = ["lea", "leb"],
        ["Chaos Orb"] = ["lea", "leb"],
        ["Clone"] = ["lea", "leb"],
        ["Control Magic"] = ["lea", "leb"],
        ["Counterspell"] = ["lea", "leb"],
        ["Dark Ritual"] = ["lea", "leb"],
        ["Demonic Tutor"] = ["lea", "leb"],
        ["Disenchant"] = ["lea", "leb"],
        ["Fireball"] = ["lea", "leb"],
        ["Force of Nature"] = ["lea", "leb"],
        ["Fork"] = ["lea", "leb"],
        ["Giant Growth"] = ["lea", "leb"],
        ["Hypnotic Specter"] = ["lea", "leb"],
        ["Lightning Bolt"] = ["lea", "leb"],
        ["Llanowar Elves"] = ["lea", "leb"],
        ["Mahamoti Djinn"] = ["lea", "leb"],
        ["Mind Twist"] = ["lea", "leb"],
        ["Mox Emerald"] = ["lea", "leb"],
        ["Mox Jet"] = ["lea", "leb"],
        ["Mox Pearl"] = ["lea", "leb"],
        ["Mox Ruby"] = ["lea", "leb"],
        ["Mox Sapphire"] = ["lea", "leb"],
        ["Nightmare"] = ["lea", "leb"],
        ["Plateau"] = ["lea", "leb"],
        ["Regrowth"] = ["lea", "leb"],
        ["Rock Hydra"] = ["lea", "leb"],
        ["Royal Assassin"] = ["lea", "leb"],
        ["Savannah"] = ["lea", "leb"],
        ["Scrubland"] = ["lea", "leb"],
        ["Serra Angel"] = ["lea", "leb"],
        ["Shivan Dragon"] = ["lea", "leb"],
        ["Sol Ring"] = ["lea", "leb"],
        ["Swords to Plowshares"] = ["lea", "leb"],
        ["Taiga"] = ["lea", "leb"],
        ["Time Walk"] = ["lea", "leb"],
        ["Timetwister"] = ["lea", "leb"],
        ["Tropical Island"] = ["lea", "leb"],
        ["Tundra"] = ["lea", "leb"],
        ["Underground Sea"] = ["lea", "leb"],
        ["Wheel of Fortune"] = ["lea", "leb"],
        ["Wrath of God"] = ["lea", "leb"],

        // From reference/ - any set is fine
        ["Brainstorm"] = [],
        ["Force of Will"] = [],
        ["Griselbrand"] = [],
        ["Lotus Petal"] = [],
        ["Ponder"] = [],
        ["Show and Tell"] = [],
        ["Volcanic Island"] = [],
        ["Wasteland"] = [],

        // From single_cards/ - any set is fine
        ["Adanto Vanguard"] = [],
        ["Angel of Sanctions"] = [],
        ["Attunement"] = [],
        ["Avaricious Dragon"] = [],
        ["Burgeoning"] = [],
        ["Jarad, Golgari Lich Lord"] = [],
        ["Meletis Charlatan"] = [],
        ["Mindstab Thrull"] = [],
        ["Pacifism"] = [],
        ["Platinum Angel"] = [],
        ["Queen Marchesa"] = [],
        ["Spellseeker"] = [],
        ["Tarmogoyf"] = [],
        ["Thought Reflection"] = [],
        ["Unsummon"] = [],

        // From varying_quality - prefer older sets
        ["Dragon Whelp"] = ["lea", "leb"],
        ["Evil Eye of Orms-by-Gore"] = [],
        ["Instill Energy"] = ["lea", "leb"],

        // Popular cards for general testing
        ["Lightning Helix"] = [],
        ["Path to Exile"] = [],
        ["Thoughtseize"] = [],
        ["Fatal Push"] = [],
        ["Snapcaster Mage"] = [],
        ["Jace, the Mind Sculptor"] = [],
        ["Liliana of the Veil"] = [],
        ["Noble Hierarch"] = [],
        ["Goblin Guide"] = [],
        ["Eidolon of the Great Revel"] = [],
    };

    public override async Task<int> ExecuteAsync(CommandContext context, GenerateSettings settings)
    {
        var outputDb = settings.Output;
        var maxCards = settings.Count;
        var includeTestCards = settings.IncludeTestCards && !settings.NoTestCards;
        var forceRebuild = settings.Force;

        // Header
        AnsiConsole.Write(new FigletText("Scry DB Gen").Color(Color.Blue));

        var configTable = new Table()
            .Border(TableBorder.Rounded)
            .AddColumn("Setting")
            .AddColumn("Value");

        configTable.AddRow("Output", outputDb);
        configTable.AddRow("Max Cards", maxCards.ToString());
        configTable.AddRow("Test Cards", includeTestCards ? "[green]Yes[/]" : "[grey]No[/]");
        configTable.AddRow("Force Rebuild", forceRebuild ? "[yellow]Yes[/]" : "[grey]No[/]");

        AnsiConsole.Write(configTable);
        AnsiConsole.WriteLine();

        var priorityCards = new HashSet<string>(PriorityCardsWithSets.Keys, StringComparer.OrdinalIgnoreCase);

        // Force rebuild if requested
        if (forceRebuild && File.Exists(outputDb))
        {
            AnsiConsole.MarkupLine("[yellow]Force rebuild requested, removing existing database...[/]");
            File.Delete(outputDb);
        }

        using var httpClient = new HttpClient();
        httpClient.DefaultRequestHeaders.UserAgent.ParseAdd("Scry/1.0 (MTG Card Scanner - Database Generator)");

        using var scryfallClient = new ScryfallClient(httpClient);
        using var db = new CardDatabase(outputDb);

        // Check existing database state
        var existingCardIds = await db.GetExistingCardIdsAsync();
        var existingCardNames = await db.GetExistingCardNamesAsync();
        var existingOracleIds = await db.GetExistingOracleIdsAsync();
        var existingSetIds = await db.GetExistingSetIdsAsync();
        var existingCount = await db.GetCardCountAsync();
        var storedScryfallDate = await db.GetMetadataAsync("scryfall_updated_at");

        var dbStateTable = new Table()
            .Border(TableBorder.Rounded)
            .Title("[blue]Current Database State[/]")
            .AddColumn("Metric")
            .AddColumn("Count", c => c.RightAligned());

        dbStateTable.AddRow("Cards", existingCount.ToString());
        dbStateTable.AddRow("Oracles", existingOracleIds.Count.ToString());
        dbStateTable.AddRow("Sets", existingSetIds.Count.ToString());

        AnsiConsole.Write(dbStateTable);
        AnsiConsole.WriteLine();

        // Fetch all sets
        List<ScryfallSet> scryfallSets = [];
        await AnsiConsole.Status()
            .Spinner(Spinner.Known.Dots)
            .StartAsync("Fetching sets from Scryfall...", async ctx =>
            {
                scryfallSets = await scryfallClient.GetAllSetsAsync();
            });

        AnsiConsole.MarkupLine($"[green]✓[/] Found [blue]{scryfallSets.Count}[/] sets");

        var setsById = scryfallSets.ToDictionary(s => s.Id ?? "", s => s);
        var setsByCode = scryfallSets.ToDictionary(s => s.Code ?? "", s => s, StringComparer.OrdinalIgnoreCase);

        // Insert any new sets
        var newSets = scryfallSets
            .Where(s => s.Id != null && !existingSetIds.Contains(s.Id))
            .Select(s => s.ToSet())
            .ToList();

        if (newSets.Count > 0)
        {
            AnsiConsole.MarkupLine($"[green]✓[/] Inserting [blue]{newSets.Count}[/] new sets");
            await db.InsertSetBatchAsync(newSets);
        }

        // Fetch bulk data info
        BulkDataInfo? bulkInfo = null;
        await AnsiConsole.Status()
            .Spinner(Spinner.Known.Dots)
            .StartAsync("Fetching bulk data info...", async ctx =>
            {
                bulkInfo = await scryfallClient.GetBulkDataInfoAsync("unique_artwork");
            });

        if (bulkInfo?.DownloadUri == null)
        {
            AnsiConsole.MarkupLine("[red]✗ Failed to get bulk data info from Scryfall[/]");
            return 1;
        }

        AnsiConsole.MarkupLine($"[green]✓[/] Scryfall data last updated: [blue]{bulkInfo.UpdatedAt:yyyy-MM-dd HH:mm}[/]");

        // Check if we need to update at all
        var scryfallDateStr = bulkInfo.UpdatedAt?.ToString("O") ?? "";
        var needsUpdate = existingCount == 0 ||
                          storedScryfallDate != scryfallDateStr ||
                          existingCount < maxCards;

        // Also check if all priority cards exist
        var missingPriorityCards = includeTestCards
            ? priorityCards.Where(c => !existingCardNames.Contains(c)).ToList()
            : [];

        if (missingPriorityCards is not [])
        {
            AnsiConsole.MarkupLine($"[yellow]![/] Missing [blue]{missingPriorityCards.Count}[/] priority cards");
            needsUpdate = true;
        }

        if (!needsUpdate)
        {
            AnsiConsole.MarkupLine("[green]✓ Database is up-to-date, no changes needed[/]");
            return 0;
        }

        AnsiConsole.WriteLine();

        var newCards = new List<Card>();
        var newOracles = new Dictionary<string, Oracle>();
        var processed = 0;
        var errors = 0;
        var skipped = 0;
        var priorityFound = 0;
        var priorityNeeded = includeTestCards ? priorityCards.Count : 0;

        // Track which priority cards we've already found with their set
        var foundPriorityWithSet = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);

        // Helper to check if a set is preferred for a priority card
        static bool IsPreferredSet(string cardName, string setCode)
        {
            if (!PriorityCardsWithSets.TryGetValue(cardName, out var preferredSets))
                return false;

            return preferredSets.Length == 0 || preferredSets.Contains(setCode, StringComparer.OrdinalIgnoreCase);
        }

        await AnsiConsole.Progress()
            .AutoClear(false)
            .HideCompleted(false)
            .Columns(
                new RemainingTimeColumn(),
                new SpinnerColumn(),
                new ProgressBarColumn(),
                new PercentageColumn(),
                new TaskDescriptionColumn()
                {
                    Alignment = Justify.Left,
                }
            )
            .StartAsync(async ctx =>
            {
                var downloadTask = ctx.AddTask("[blue]Downloading & processing cards[/]", maxValue: maxCards);
                var priorityTask = ctx.AddTask("[green]Priority cards[/]", maxValue: priorityNeeded);

                await foreach (var scryfallCard in scryfallClient.StreamBulkDataAsync(bulkInfo.DownloadUri))
                {
                    // Skip non-English cards
                    if (scryfallCard.Lang != "en")
                        continue;

                    var imageUri = scryfallCard.GetImageUri("normal");
                    if (string.IsNullOrEmpty(imageUri))
                        continue;

                    var cardId = scryfallCard.Id ?? Guid.NewGuid().ToString();
                    var cardName = scryfallCard.Name ?? "Unknown";
                    var setCode = scryfallCard.Set ?? "???";
                    var oracleId = scryfallCard.OracleId ?? cardId;
                    var setId = scryfallCard.SetId ?? "";

                    // Check if this card already exists in the database
                    if (existingCardIds.Contains(cardId))
                    {
                        skipped++;
                        continue;
                    }

                    // Check if this is a priority card we might need
                    var isPriorityCard = includeTestCards && priorityCards.Contains(cardName);
                    var isPreferred = isPriorityCard && IsPreferredSet(cardName, setCode);

                    // If this priority card already found with preferred set, skip
                    if (isPriorityCard && foundPriorityWithSet.TryGetValue(cardName, out var existingSet))
                    {
                        if (IsPreferredSet(cardName, existingSet))
                            continue;
                        if (!isPreferred)
                            continue;
                    }

                    // Calculate how many slots we have left
                    var totalCards = existingCount + newCards.Count;
                    var priorityRemaining = priorityNeeded - foundPriorityWithSet.Count;
                    var slotsForNonPriority = maxCards - priorityRemaining;

                    // Skip if we have enough non-priority cards and this isn't priority
                    if (!isPriorityCard && totalCards >= slotsForNonPriority)
                        continue;

                    // Download and process image
                    try
                    {
                        downloadTask.Description = $"[blue]{Markup.Escape(cardName.Length > 30 ? cardName[..27] + "..." : cardName)}[/]";

                        var imageBytes = await httpClient.GetByteArrayAsync(imageUri);
                        using var bitmap = SKBitmap.Decode(imageBytes);

                        if (bitmap == null)
                        {
                            errors++;
                            continue;
                        }

                        // Apply CLAHE preprocessing and compute hash
                        using var preprocessed = ImagePreprocessor.ApplyClahe(bitmap);
                        var hash = PerceptualHash.ComputeColorHash(preprocessed);

                        // Create Card (printing) with hash
                        var card = scryfallCard.ToCard() with { Hash = hash };
                        newCards.Add(card);

                        // Track Oracle if we haven't seen it
                        if (!existingOracleIds.Contains(oracleId) && !newOracles.ContainsKey(oracleId))
                        {
                            newOracles[oracleId] = scryfallCard.ToOracle();
                        }

                        if (isPriorityCard)
                        {
                            foundPriorityWithSet[cardName] = setCode;
                            priorityFound++;
                            priorityTask.Increment(1);
                        }

                        processed++;
                        downloadTask.Increment(1);

                        // Check if we have enough cards
                        var foundAllPriority = foundPriorityWithSet.Count >= priorityNeeded;
                        if (existingCount + newCards.Count >= maxCards && foundAllPriority)
                            break;

                        // Rate limit to be nice to Scryfall
                        await Task.Delay(50);
                    }
                    catch
                    {
                        errors++;
                    }
                }

                downloadTask.Value = downloadTask.MaxValue;
                priorityTask.Value = priorityTask.MaxValue;
            });

        AnsiConsole.WriteLine();

        // Summary table
        var summaryTable = new Table()
            .Border(TableBorder.Rounded)
            .Title("[blue]Processing Summary[/]")
            .AddColumn("Metric")
            .AddColumn("Count", c => c.RightAligned());

        summaryTable.AddRow("Skipped (already in DB)", skipped.ToString());
        summaryTable.AddRow("Newly processed", $"[green]{processed}[/]");
        summaryTable.AddRow("New oracles", newOracles.Count.ToString());
        summaryTable.AddRow("Priority cards found", $"{priorityFound}/{priorityNeeded}");
        summaryTable.AddRow("Errors", errors > 0 ? $"[red]{errors}[/]" : "0");

        AnsiConsole.Write(summaryTable);
        AnsiConsole.WriteLine();

        // Insert oracles first (cards reference them)
        if (newOracles.Count > 0)
        {
            await AnsiConsole.Status()
                .Spinner(Spinner.Known.Dots)
                .StartAsync($"Inserting {newOracles.Count} new oracles...", async ctx =>
                {
                    await db.InsertOracleBatchAsync(newOracles.Values);
                });
            AnsiConsole.MarkupLine($"[green]✓[/] Inserted [blue]{newOracles.Count}[/] oracles");
        }

        if (newCards.Count > 0)
        {
            await AnsiConsole.Status()
                .Spinner(Spinner.Known.Dots)
                .StartAsync($"Inserting {newCards.Count} new cards...", async ctx =>
                {
                    await db.InsertCardBatchAsync(newCards);
                });
            AnsiConsole.MarkupLine($"[green]✓[/] Inserted [blue]{newCards.Count}[/] cards");
        }

        await db.SetMetadataAsync("generated_at", DateTime.UtcNow.ToString("O"));
        await db.SetMetadataAsync("scryfall_updated_at", scryfallDateStr);

        var finalCardCount = await db.GetCardCountAsync();
        var finalOracleCount = await db.GetOracleCountAsync();
        var finalSetCount = await db.GetSetCountAsync();

        await db.SetMetadataAsync("card_count", finalCardCount.ToString());
        await db.SetMetadataAsync("oracle_count", finalOracleCount.ToString());
        await db.SetMetadataAsync("set_count", finalSetCount.ToString());

        AnsiConsole.WriteLine();

        var finalTable = new Table()
            .Border(TableBorder.Double)
            .Title("[green]Final Database State[/]")
            .AddColumn("Metric")
            .AddColumn("Count", c => c.RightAligned());

        finalTable.AddRow("Cards", $"[green]{finalCardCount}[/]");
        finalTable.AddRow("Oracles", $"[green]{finalOracleCount}[/]");
        finalTable.AddRow("Sets", $"[green]{finalSetCount}[/]");
        finalTable.AddRow("Output", $"[blue]{outputDb}[/]");

        AnsiConsole.Write(finalTable);

        // Report missing priority cards
        if (includeTestCards)
        {
            var missing = priorityCards.Where(c => !foundPriorityWithSet.ContainsKey(c)).ToList();

            if (missing.Count > 0)
            {
                AnsiConsole.WriteLine();
                AnsiConsole.MarkupLine($"[yellow]Missing priority cards ({missing.Count}):[/]");

                var tree = new Tree("[yellow]Missing Cards[/]");
                foreach (var name in missing.Take(20))
                {
                    tree.AddNode($"[grey]{Markup.Escape(name)}[/]");
                }
                if (missing.Count > 20)
                {
                    tree.AddNode($"[grey]... and {missing.Count - 20} more[/]");
                }
                AnsiConsole.Write(tree);
            }
        }

        return 0;
    }
}