Program.cs

using System.Collections.Generic;
using System.Text.Json.Serialization;
using Microsoft.EntityFrameworkCore;
using PoeCraftTracker.Data;
using PoeCraftTracker.Endpoints;
using PoeCraftTracker.Services;

var builder = WebApplication.CreateBuilder(args);

ConfigureDataPath(builder);

builder.Services.AddRazorPages();
builder.Services.ConfigureHttpJsonOptions(options =>
{
    options.SerializerOptions.Converters.Add(new JsonStringEnumConverter());
});
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlite(builder.Configuration.GetConnectionString("DefaultConnection")));

builder.Services.Configure<PoeNinjaOptions>(
    builder.Configuration.GetSection(PoeNinjaOptions.SectionName));
builder.Services.AddMemoryCache();
builder.Services.AddHttpClient<PoeNinjaClient>(client =>
{
    client.DefaultRequestHeaders.UserAgent.ParseAdd("PoeCraftTracker/1.0 (personal craft cost tracker)");
    client.Timeout = TimeSpan.FromSeconds(30);
});
builder.Services.AddScoped<PriceService>();

var app = builder.Build();

using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    db.Database.EnsureCreated();
    await db.Database.ExecuteSqlRawAsync("""
        CREATE TABLE IF NOT EXISTS "PriceCaches" (
            "Id" INTEGER NOT NULL CONSTRAINT "PK_PriceCaches" PRIMARY KEY AUTOINCREMENT,
            "League" TEXT NOT NULL,
            "FetchedAt" TEXT NOT NULL,
            "PayloadJson" TEXT NOT NULL
        );
        CREATE UNIQUE INDEX IF NOT EXISTS "IX_PriceCaches_League" ON "PriceCaches" ("League");
        """);
    await MigrateCraftSchemaAsync(db);
}

static async Task MigrateCraftSchemaAsync(AppDbContext db)
{
    if (!await TableExistsAsync(db, "Crafts"))
        return;

    if (!await TableExistsAsync(db, "CraftAttempts"))
    {
        await db.Database.ExecuteSqlRawAsync("""
            CREATE TABLE IF NOT EXISTS "CraftAttempts" (
                "Id" INTEGER NOT NULL CONSTRAINT "PK_CraftAttempts" PRIMARY KEY AUTOINCREMENT,
                "CraftId" INTEGER NOT NULL,
                "AttemptNumber" INTEGER NOT NULL,
                "Status" TEXT NOT NULL,
                "CreatedAt" TEXT NOT NULL,
                "UpdatedAt" TEXT NOT NULL,
                CONSTRAINT "FK_CraftAttempts_Crafts_CraftId" FOREIGN KEY ("CraftId") REFERENCES "Crafts" ("Id") ON DELETE CASCADE
            );
            CREATE UNIQUE INDEX IF NOT EXISTS "IX_CraftAttempts_CraftId_AttemptNumber" ON "CraftAttempts" ("CraftId", "AttemptNumber");
            """);
    }

    var expenseColumns = await GetColumnNamesAsync(db, "CraftExpenses");
    if (expenseColumns.Count == 0)
        return;

    if (!expenseColumns.Contains("PriceSource"))
    {
        await db.Database.ExecuteSqlRawAsync(
            """ALTER TABLE "CraftExpenses" ADD COLUMN "PriceSource" TEXT NOT NULL DEFAULT 'Manual';""");
    }

    if (expenseColumns.Contains("CraftId") && !expenseColumns.Contains("CraftAttemptId"))
    {
        await db.Database.ExecuteSqlRawAsync(
            """ALTER TABLE "CraftExpenses" ADD COLUMN "CraftAttemptId" INTEGER;""");

        var craftsColumns = await GetColumnNamesAsync(db, "Crafts");
        if (craftsColumns.Contains("Status"))
        {
            await db.Database.ExecuteSqlRawAsync("""
                INSERT INTO "CraftAttempts" ("CraftId", "AttemptNumber", "Status", "CreatedAt", "UpdatedAt")
                SELECT "Id", 1, "Status", "CreatedAt", "UpdatedAt"
                FROM "Crafts" c
                WHERE NOT EXISTS (
                    SELECT 1 FROM "CraftAttempts" ca WHERE ca."CraftId" = c."Id"
                );
                """);
        }
        else
        {
            await db.Database.ExecuteSqlRawAsync("""
                INSERT INTO "CraftAttempts" ("CraftId", "AttemptNumber", "Status", "CreatedAt", "UpdatedAt")
                SELECT "Id", 1, 'InProgress', "CreatedAt", "UpdatedAt"
                FROM "Crafts" c
                WHERE NOT EXISTS (
                    SELECT 1 FROM "CraftAttempts" ca WHERE ca."CraftId" = c."Id"
                );
                """);
        }

        await db.Database.ExecuteSqlRawAsync("""
            UPDATE "CraftExpenses"
            SET "CraftAttemptId" = (
                SELECT ca."Id" FROM "CraftAttempts" ca
                WHERE ca."CraftId" = "CraftExpenses"."CraftId"
                LIMIT 1
            )
            WHERE "CraftAttemptId" IS NULL;
            """);

        await db.Database.ExecuteSqlRawAsync("""
            CREATE TABLE "CraftExpenses_new" (
                "Id" INTEGER NOT NULL CONSTRAINT "PK_CraftExpenses" PRIMARY KEY AUTOINCREMENT,
                "CraftAttemptId" INTEGER NOT NULL,
                "Currency" TEXT NOT NULL,
                "CustomCurrency" TEXT NULL,
                "Quantity" TEXT NOT NULL,
                "UnitPrice" TEXT NOT NULL,
                "PriceSource" TEXT NOT NULL DEFAULT 'Manual',
                "SortOrder" INTEGER NOT NULL,
                CONSTRAINT "FK_CraftExpenses_CraftAttempts_CraftAttemptId" FOREIGN KEY ("CraftAttemptId") REFERENCES "CraftAttempts" ("Id") ON DELETE CASCADE
            );
            INSERT INTO "CraftExpenses_new" ("Id", "CraftAttemptId", "Currency", "CustomCurrency", "Quantity", "UnitPrice", "PriceSource", "SortOrder")
            SELECT "Id", "CraftAttemptId", "Currency", "CustomCurrency", "Quantity", "UnitPrice", COALESCE("PriceSource", 'Manual'), "SortOrder"
            FROM "CraftExpenses";
            DROP TABLE "CraftExpenses";
            ALTER TABLE "CraftExpenses_new" RENAME TO "CraftExpenses";
            """);
    }

    await db.Database.ExecuteSqlRawAsync("""
        INSERT INTO "CraftAttempts" ("CraftId", "AttemptNumber", "Status", "CreatedAt", "UpdatedAt")
        SELECT "Id", 1, 'InProgress', "CreatedAt", "UpdatedAt"
        FROM "Crafts" c
        WHERE NOT EXISTS (
            SELECT 1 FROM "CraftAttempts" ca WHERE ca."CraftId" = c."Id"
        );
        """);

    var craftTableColumns = await GetColumnNamesAsync(db, "Crafts");
    if (craftTableColumns.Contains("Status"))
    {
        await db.Database.ExecuteSqlRawAsync("""
            CREATE TABLE "Crafts_new" (
                "Id" INTEGER NOT NULL CONSTRAINT "PK_Crafts" PRIMARY KEY AUTOINCREMENT,
                "ItemName" TEXT NOT NULL,
                "Notes" TEXT NOT NULL,
                "DisplayCurrency" TEXT NOT NULL,
                "CreatedAt" TEXT NOT NULL,
                "UpdatedAt" TEXT NOT NULL
            );
            INSERT INTO "Crafts_new" ("Id", "ItemName", "Notes", "DisplayCurrency", "CreatedAt", "UpdatedAt")
            SELECT "Id", "ItemName", "Notes", "DisplayCurrency", "CreatedAt", "UpdatedAt" FROM "Crafts";
            DROP TABLE "Crafts";
            ALTER TABLE "Crafts_new" RENAME TO "Crafts";
            """);
    }
}

static async Task<bool> TableExistsAsync(AppDbContext db, string tableName)
{
    var connection = db.Database.GetDbConnection();
    await connection.OpenAsync();
    try
    {
        await using var command = connection.CreateCommand();
        command.CommandText = """
            SELECT COUNT(*) FROM sqlite_master
            WHERE type = 'table' AND name = $name;
            """;
        var param = command.CreateParameter();
        param.ParameterName = "$name";
        param.Value = tableName;
        command.Parameters.Add(param);
        var result = await command.ExecuteScalarAsync();
        return Convert.ToInt64(result) > 0;
    }
    finally
    {
        await connection.CloseAsync();
    }
}

static async Task<HashSet<string>> GetColumnNamesAsync(AppDbContext db, string tableName)
{
    var columns = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
    var connection = db.Database.GetDbConnection();
    await connection.OpenAsync();
    try
    {
        await using var command = connection.CreateCommand();
        command.CommandText = $"PRAGMA table_info(\"{tableName}\");";
        await using var reader = await command.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            columns.Add(reader.GetString(1));
        }
    }
    finally
    {
        await connection.CloseAsync();
    }

    return columns;
}

static void ConfigureDataPath(WebApplicationBuilder builder)
{
    var dataDir = Environment.GetEnvironmentVariable("POE_CRAFT_TRACKER_DATA");
    if (string.IsNullOrWhiteSpace(dataDir))
        return;

    Directory.CreateDirectory(dataDir);
    var dbPath = Path.Combine(dataDir, "crafts.db");

    var cwdDb = Path.Combine(builder.Environment.ContentRootPath, "crafts.db");
    if (!File.Exists(dbPath) && File.Exists(cwdDb))
        File.Copy(cwdDb, dbPath);

    builder.Configuration.AddInMemoryCollection(new Dictionary<string, string?>
    {
        ["ConnectionStrings:DefaultConnection"] = $"Data Source={dbPath}"
    });
}

if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Error");
}

app.UseRouting();
app.UseAuthorization();

app.MapStaticAssets();
app.MapRazorPages().WithStaticAssets();
app.MapCraftEndpoints();
app.MapPriceEndpoints();

app.Run();