jobtrackingapp/JobTrackerApi/Services/StartupInitializationExtensions.cs

using System.Data.Common;
using JobTrackerApi.Data;
using JobTrackerApi.Models;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Identity;
using Microsoft.EntityFrameworkCore;

namespace JobTrackerApi.Services;

public static class StartupInitializationExtensions
{
    // SQLite-dialect schema helpers. Promoted from local functions to class-level statics so a
    // second reconciliation pass can run after Migrate() creates the base tables on a brand-new
    // database (see the CoreSchemaReady-adjacent block near the end of InitializeJobTrackerAsync):
    // the ad-hoc EnsureColumn calls below no-op on a table that doesn't exist yet, so a genuinely
    // fresh boot needs them re-run once Migrate() has created JobApplications/Correspondences.
    private static bool HasTable(DbConnection c, string table)
    {
        using var cmd = c.CreateCommand();
        cmd.CommandText = "SELECT 1 FROM sqlite_master WHERE type='table' AND name=$name LIMIT 1;";
        var p = cmd.CreateParameter();
        p.ParameterName = "$name";
        p.Value = table;
        cmd.Parameters.Add(p);
        return cmd.ExecuteScalar() is not null;
    }

    private static bool HasColumn(DbConnection c, string table, string column)
    {
        using var cmd = c.CreateCommand();
        cmd.CommandText = $"SELECT 1 FROM pragma_table_info('{table}') WHERE name = '{column}' LIMIT 1;";
        return cmd.ExecuteScalar() is not null;
    }

    private static bool HasMigration(DbConnection c, string migrationId)
    {
        if (!HasTable(c, "__EFMigrationsHistory")) return false;
        using var cmd = c.CreateCommand();
        cmd.CommandText = "SELECT 1 FROM __EFMigrationsHistory WHERE MigrationId=$id LIMIT 1;";
        var p = cmd.CreateParameter();
        p.ParameterName = "$id";
        p.Value = migrationId;
        cmd.Parameters.Add(p);
        return cmd.ExecuteScalar() is not null;
    }

    private static void Exec(DbConnection c, string sql)
    {
        using var cmd = c.CreateCommand();
        cmd.CommandText = sql;
        cmd.ExecuteNonQuery();
    }

    private static void EnsureColumn(DbConnection c, string table, string column, string ddl)
    {
        // Fresh databases won't have the table until EF migrations run.
        if (!HasTable(c, table)) return;
        if (!HasColumn(c, table, column)) Exec(c, ddl);
    }

    // Ad-hoc columns/backfills added over time without a matching EF migration (the reason the
    // ModelSnapshot drifted -- see the SyncModelSnapshot migration's doc comment). Safe to call
    // any number of times against any connection state: every check no-ops if the table or
    // column doesn't exist yet or already matches.
    private static void ReconcileCoreAppColumns(DbConnection conn)
    {
        EnsureColumn(conn, "JobApplications", "ShortSummary", "ALTER TABLE JobApplications ADD COLUMN ShortSummary TEXT NULL;");
        EnsureColumn(conn, "JobApplications", "TailoredCvText", "ALTER TABLE JobApplications ADD COLUMN TailoredCvText TEXT NULL;");
        EnsureColumn(conn, "JobApplications", "TailoredCvUpdatedAt", "ALTER TABLE JobApplications ADD COLUMN TailoredCvUpdatedAt TEXT NULL;");
        EnsureColumn(conn, "JobApplications", "LastReminderEmailSentAt", "ALTER TABLE JobApplications ADD COLUMN LastReminderEmailSentAt TEXT NULL;");
        EnsureColumn(conn, "JobApplications", "RecruiterMessageDraft", "ALTER TABLE JobApplications ADD COLUMN RecruiterMessageDraft TEXT NULL;");
        EnsureColumn(conn, "JobApplications", "SalaryMin", "ALTER TABLE JobApplications ADD COLUMN SalaryMin TEXT NULL;");
        EnsureColumn(conn, "JobApplications", "SalaryMax", "ALTER TABLE JobApplications ADD COLUMN SalaryMax TEXT NULL;");
        EnsureColumn(conn, "JobApplications", "SalaryCurrency", "ALTER TABLE JobApplications ADD COLUMN SalaryCurrency TEXT NULL;");
        EnsureColumn(conn, "JobApplications", "SalaryPeriod", "ALTER TABLE JobApplications ADD COLUMN SalaryPeriod TEXT NULL;");
        EnsureColumn(conn, "Companies", "OwnerUserId", "ALTER TABLE Companies ADD COLUMN OwnerUserId TEXT NULL;");
        EnsureColumn(conn, "JobApplications", "OwnerUserId", "ALTER TABLE JobApplications ADD COLUMN OwnerUserId TEXT NULL;");
        EnsureColumn(conn, "Correspondences", "Subject", "ALTER TABLE Correspondences ADD COLUMN Subject TEXT NULL;");
        EnsureColumn(conn, "Correspondences", "Channel", "ALTER TABLE Correspondences ADD COLUMN Channel TEXT NULL;");
        EnsureColumn(conn, "Correspondences", "ExternalMessageId", "ALTER TABLE Correspondences ADD COLUMN ExternalMessageId TEXT NULL;");
        EnsureColumn(conn, "Correspondences", "ExternalThreadId", "ALTER TABLE Correspondences ADD COLUMN ExternalThreadId TEXT NULL;");
        EnsureColumn(conn, "Correspondences", "ExternalFrom", "ALTER TABLE Correspondences ADD COLUMN ExternalFrom TEXT NULL;");
        EnsureColumn(conn, "Correspondences", "ExternalTo", "ALTER TABLE Correspondences ADD COLUMN ExternalTo TEXT NULL;");
        EnsureColumn(conn, "Correspondences", "Direction", "ALTER TABLE Correspondences ADD COLUMN Direction TEXT NULL;");
        EnsureColumn(conn, "Correspondences", "ExternalLabelsJson", "ALTER TABLE Correspondences ADD COLUMN ExternalLabelsJson TEXT NULL;");
        EnsureColumn(conn, "Correspondences", "AttachmentMetadataJson", "ALTER TABLE Correspondences ADD COLUMN AttachmentMetadataJson TEXT NULL;");
        EnsureColumn(conn, "Correspondences", "Provider", "ALTER TABLE Correspondences ADD COLUMN Provider TEXT NULL;");
        if (HasTable(conn, "Correspondences"))
        {
            Exec(conn, "UPDATE Correspondences SET Provider = 'gmail' WHERE Provider IS NULL AND ExternalThreadId IS NOT NULL;");
            Exec(conn, "UPDATE Correspondences SET Provider = 'manual' WHERE Provider IS NULL;");
        }
        EnsureColumn(conn, "Attachments", "Purpose", "ALTER TABLE Attachments ADD COLUMN Purpose TEXT NULL;");
        EnsureColumn(conn, "Attachments", "UseForAi", "ALTER TABLE Attachments ADD COLUMN UseForAi INTEGER NOT NULL DEFAULT 1;");
    }

    // MySQL/MariaDB-dialect equivalents of the helpers above.
    private static bool HasMySqlTable(DbConnection c, string table)
    {
        using var cmd = c.CreateCommand();
        cmd.CommandText = "SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @table LIMIT 1;";
        var p1 = cmd.CreateParameter(); p1.ParameterName = "@schema"; p1.Value = c.Database; cmd.Parameters.Add(p1);
        var p2 = cmd.CreateParameter(); p2.ParameterName = "@table"; p2.Value = table; cmd.Parameters.Add(p2);
        return cmd.ExecuteScalar() is not null;
    }

    private static bool MySqlColumnExists(DbConnection c, string table, string column)
    {
        using var cmd = c.CreateCommand();
        cmd.CommandText = "SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @table AND COLUMN_NAME = @column LIMIT 1;";
        var p1 = cmd.CreateParameter(); p1.ParameterName = "@schema"; p1.Value = c.Database; cmd.Parameters.Add(p1);
        var p2 = cmd.CreateParameter(); p2.ParameterName = "@table"; p2.Value = table; cmd.Parameters.Add(p2);
        var p3 = cmd.CreateParameter(); p3.ParameterName = "@column"; p3.Value = column; cmd.Parameters.Add(p3);
        return cmd.ExecuteScalar() is not null;
    }

    private static void EnsureMySqlColumn(DbConnection c, string table, string column, string ddl)
    {
        if (!HasMySqlTable(c, table)) return;
        if (MySqlColumnExists(c, table, column)) return;
        using var ddlCmd = c.CreateCommand();
        ddlCmd.CommandText = ddl;
        ddlCmd.ExecuteNonQuery();
    }

    // MySQL mirror of ReconcileCoreAppColumns -- same rationale (re-run after Migrate() on a
    // brand-new database, where these tables didn't exist yet during the pre-Migrate pass).
    private static void ReconcileCoreAppColumnsMySql(DbConnection conn)
    {
        EnsureMySqlColumn(conn, "Companies", "OwnerUserId", "ALTER TABLE `Companies` ADD COLUMN `OwnerUserId` varchar(255) NULL;");
        EnsureMySqlColumn(conn, "Companies", "Source", "ALTER TABLE `Companies` ADD COLUMN `Source` longtext NULL;");
        EnsureMySqlColumn(conn, "Companies", "RecruiterName", "ALTER TABLE `Companies` ADD COLUMN `RecruiterName` longtext NULL;");
        EnsureMySqlColumn(conn, "Companies", "RecruiterEmail", "ALTER TABLE `Companies` ADD COLUMN `RecruiterEmail` longtext NULL;");
        EnsureMySqlColumn(conn, "Companies", "RecruiterLinkedIn", "ALTER TABLE `Companies` ADD COLUMN `RecruiterLinkedIn` longtext NULL;");
        EnsureMySqlColumn(conn, "Companies", "LastContactedAt", "ALTER TABLE `Companies` ADD COLUMN `LastContactedAt` datetime NULL;");
        EnsureMySqlColumn(conn, "Companies", "NextContactAt", "ALTER TABLE `Companies` ADD COLUMN `NextContactAt` datetime NULL;");
        EnsureMySqlColumn(conn, "Companies", "PipelineStage", "ALTER TABLE `Companies` ADD COLUMN `PipelineStage` longtext NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "OwnerUserId", "ALTER TABLE `JobApplications` ADD COLUMN `OwnerUserId` varchar(255) NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "IsDeleted", "ALTER TABLE `JobApplications` ADD COLUMN `IsDeleted` tinyint(1) NOT NULL DEFAULT 0;");
        EnsureMySqlColumn(conn, "JobApplications", "DeletedAt", "ALTER TABLE `JobApplications` ADD COLUMN `DeletedAt` datetime NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "Location", "ALTER TABLE `JobApplications` ADD COLUMN `Location` longtext NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "Salary", "ALTER TABLE `JobApplications` ADD COLUMN `Salary` longtext NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "SalaryMin", "ALTER TABLE `JobApplications` ADD COLUMN `SalaryMin` decimal(18,2) NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "SalaryMax", "ALTER TABLE `JobApplications` ADD COLUMN `SalaryMax` decimal(18,2) NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "SalaryCurrency", "ALTER TABLE `JobApplications` ADD COLUMN `SalaryCurrency` varchar(8) NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "SalaryPeriod", "ALTER TABLE `JobApplications` ADD COLUMN `SalaryPeriod` varchar(16) NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "NextAction", "ALTER TABLE `JobApplications` ADD COLUMN `NextAction` longtext NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "FollowUpAt", "ALTER TABLE `JobApplications` ADD COLUMN `FollowUpAt` datetime NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "FeedbackRequestedAt", "ALTER TABLE `JobApplications` ADD COLUMN `FeedbackRequestedAt` datetime NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "RecruiterMessageDraft", "ALTER TABLE `JobApplications` ADD COLUMN `RecruiterMessageDraft` longtext NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "ResponseReceived", "ALTER TABLE `JobApplications` ADD COLUMN `ResponseReceived` tinyint(1) NOT NULL DEFAULT 0;");
        EnsureMySqlColumn(conn, "JobApplications", "ResponseDate", "ALTER TABLE `JobApplications` ADD COLUMN `ResponseDate` datetime NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "Notes", "ALTER TABLE `JobApplications` ADD COLUMN `Notes` longtext NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "CoverLetterText", "ALTER TABLE `JobApplications` ADD COLUMN `CoverLetterText` longtext NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "JobUrl", "ALTER TABLE `JobApplications` ADD COLUMN `JobUrl` longtext NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "Description", "ALTER TABLE `JobApplications` ADD COLUMN `Description` longtext NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "TranslatedDescription", "ALTER TABLE `JobApplications` ADD COLUMN `TranslatedDescription` longtext NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "DescriptionLanguage", "ALTER TABLE `JobApplications` ADD COLUMN `DescriptionLanguage` longtext NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "Tags", "ALTER TABLE `JobApplications` ADD COLUMN `Tags` longtext NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "Deadline", "ALTER TABLE `JobApplications` ADD COLUMN `Deadline` datetime NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "ShortSummary", "ALTER TABLE `JobApplications` ADD COLUMN `ShortSummary` longtext NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "TailoredCvText", "ALTER TABLE `JobApplications` ADD COLUMN `TailoredCvText` longtext NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "TailoredCvUpdatedAt", "ALTER TABLE `JobApplications` ADD COLUMN `TailoredCvUpdatedAt` datetime NULL;");
        EnsureMySqlColumn(conn, "JobApplications", "LastReminderEmailSentAt", "ALTER TABLE `JobApplications` ADD COLUMN `LastReminderEmailSentAt` datetime NULL;");
        EnsureMySqlColumn(conn, "Correspondences", "Subject", "ALTER TABLE `Correspondences` ADD COLUMN `Subject` longtext NULL;");
        EnsureMySqlColumn(conn, "Correspondences", "Channel", "ALTER TABLE `Correspondences` ADD COLUMN `Channel` longtext NULL;");
        EnsureMySqlColumn(conn, "Correspondences", "ExternalMessageId", "ALTER TABLE `Correspondences` ADD COLUMN `ExternalMessageId` longtext NULL;");
        EnsureMySqlColumn(conn, "Correspondences", "ExternalThreadId", "ALTER TABLE `Correspondences` ADD COLUMN `ExternalThreadId` longtext NULL;");
        EnsureMySqlColumn(conn, "Correspondences", "ExternalFrom", "ALTER TABLE `Correspondences` ADD COLUMN `ExternalFrom` longtext NULL;");
        EnsureMySqlColumn(conn, "Correspondences", "ExternalTo", "ALTER TABLE `Correspondences` ADD COLUMN `ExternalTo` longtext NULL;");
        EnsureMySqlColumn(conn, "Correspondences", "Direction", "ALTER TABLE `Correspondences` ADD COLUMN `Direction` varchar(100) NULL;");
        EnsureMySqlColumn(conn, "Correspondences", "ExternalLabelsJson", "ALTER TABLE `Correspondences` ADD COLUMN `ExternalLabelsJson` longtext NULL;");
        EnsureMySqlColumn(conn, "Correspondences", "AttachmentMetadataJson", "ALTER TABLE `Correspondences` ADD COLUMN `AttachmentMetadataJson` longtext NULL;");
        EnsureMySqlColumn(conn, "Correspondences", "Provider", "ALTER TABLE `Correspondences` ADD COLUMN `Provider` varchar(50) NULL;");
        if (HasMySqlTable(conn, "Correspondences"))
        {
            using (var backfillGmail = conn.CreateCommand())
            {
                backfillGmail.CommandText = "UPDATE `Correspondences` SET `Provider` = 'gmail' WHERE `Provider` IS NULL AND `ExternalThreadId` IS NOT NULL;";
                backfillGmail.ExecuteNonQuery();
            }
            using (var backfillManual = conn.CreateCommand())
            {
                backfillManual.CommandText = "UPDATE `Correspondences` SET `Provider` = 'manual' WHERE `Provider` IS NULL;";
                backfillManual.ExecuteNonQuery();
            }
        }
        EnsureMySqlColumn(conn, "Attachments", "Purpose", "ALTER TABLE `Attachments` ADD COLUMN `Purpose` varchar(100) NULL;");
        EnsureMySqlColumn(conn, "Attachments", "UseForAi", "ALTER TABLE `Attachments` ADD COLUMN `UseForAi` tinyint(1) NOT NULL DEFAULT 1;");
    }

    public static Task InitializeJobTrackerAsync(this WebApplication app)
    {
        // Apply EF migrations on startup (SQLite dev DB lives in the repo).
        using (var scope = app.Services.CreateScope())
        {
            var db = scope.ServiceProvider.GetRequiredService<JobTrackerContext>();
            var paths = scope.ServiceProvider.GetRequiredService<AppPaths>();
            var users = scope.ServiceProvider.GetRequiredService<UserManager<ApplicationUser>>();
            var roles = scope.ServiceProvider.GetRequiredService<RoleManager<IdentityRole>>();
            var provider = (app.Configuration["Database:Provider"] ?? "sqlite").Trim().ToLowerInvariant();
            var useSqliteBootstrap = provider is not "mysql" and not "mariadb";

            static void EnsureIdentityTablesMySql(DbConnection c)
            {
                using var cmd = c.CreateCommand();
                cmd.CommandText = @"
        CREATE TABLE IF NOT EXISTS `AspNetRoles` (
            `Id` varchar(255) NOT NULL,
            `Name` varchar(256) NULL,
            `NormalizedName` varchar(256) NULL,
            `ConcurrencyStamp` longtext NULL,
            PRIMARY KEY (`Id`)
        ) CHARACTER SET=utf8mb4;

        CREATE TABLE IF NOT EXISTS `AspNetUsers` (
            `Id` varchar(255) NOT NULL,
            `UserName` varchar(256) NULL,
            `NormalizedUserName` varchar(256) NULL,
            `Email` varchar(256) NULL,
            `NormalizedEmail` varchar(256) NULL,
            `EmailConfirmed` tinyint(1) NOT NULL,
            `PasswordHash` longtext NULL,
            `SecurityStamp` longtext NULL,
            `ConcurrencyStamp` longtext NULL,
            `PhoneNumber` longtext NULL,
            `PhoneNumberConfirmed` tinyint(1) NOT NULL,

            `LockoutEnd` datetime(6) NULL,
            `LockoutEnabled` tinyint(1) NOT NULL,
            `AccessFailedCount` int NOT NULL,
            `FirstName` longtext NULL,
            `LastName` longtext NULL,
            `DisplayName` longtext NULL,
            `ProfileCvText` longtext NULL,
            `ProfileCvStructureJson` longtext NULL,
            `CurrentCvUploadArtifactId` int NULL,
            `CurrentCvExtractionRunId` int NULL,
            `CurrentCvProfileVersion` int NULL,
            `AvatarImageDataUrl` longtext NULL,
            `GoogleSubject` longtext NULL,
            `GoogleEmail` longtext NULL,
            `GoogleLinkedAt` datetime(6) NULL,
            PRIMARY KEY (`Id`)
        ) CHARACTER SET=utf8mb4;

        CREATE TABLE IF NOT EXISTS `AspNetRoleClaims` (
            `Id` int NOT NULL AUTO_INCREMENT,
            `RoleId` varchar(255) NOT NULL,
            `ClaimType` longtext NULL,
            `ClaimValue` longtext NULL,
            PRIMARY KEY (`Id`),
            CONSTRAINT `FK_AspNetRoleClaims_AspNetRoles_RoleId` FOREIGN KEY (`RoleId`) REFERENCES `AspNetRoles` (`Id`) ON DELETE CASCADE
        ) CHARACTER SET=utf8mb4;

        CREATE TABLE IF NOT EXISTS `AspNetUserClaims` (
            `Id` int NOT NULL AUTO_INCREMENT,
            `UserId` varchar(255) NOT NULL,
            `ClaimType` longtext NULL,
            `ClaimValue` longtext NULL,
            PRIMARY KEY (`Id`),
            CONSTRAINT `FK_AspNetUserClaims_AspNetUsers_UserId` FOREIGN KEY (`UserId`) REFERENCES `AspNetUsers` (`Id`) ON DELETE CASCADE
        ) CHARACTER SET=utf8mb4;

        CREATE TABLE IF NOT EXISTS `AspNetUserLogins` (
            `LoginProvider` varchar(255) NOT NULL,
            `ProviderKey` varchar(255) NOT NULL,
            `ProviderDisplayName` longtext NULL,
            `UserId` varchar(255) NOT NULL,
            PRIMARY KEY (`LoginProvider`, `ProviderKey`),
            CONSTRAINT `FK_AspNetUserLogins_AspNetUsers_UserId` FOREIGN KEY (`UserId`) REFERENCES `AspNetUsers` (`Id`) ON DELETE CASCADE
        ) CHARACTER SET=utf8mb4;

        CREATE TABLE IF NOT EXISTS `AspNetUserRoles` (
            `UserId` varchar(255) NOT NULL,
            `RoleId` varchar(255) NOT NULL,
            PRIMARY KEY (`UserId`, `RoleId`),
            CONSTRAINT `FK_AspNetUserRoles_AspNetRoles_RoleId` FOREIGN KEY (`RoleId`) REFERENCES `AspNetRoles` (`Id`) ON DELETE CASCADE,
            CONSTRAINT `FK_AspNetUserRoles_AspNetUsers_UserId` FOREIGN KEY (`UserId`) REFERENCES `AspNetUsers` (`Id`) ON DELETE CASCADE
        ) CHARACTER SET=utf8mb4;

        CREATE TABLE IF NOT EXISTS `AspNetUserTokens` (
            `UserId` varchar(255) NOT NULL,
            `LoginProvider` varchar(255) NOT NULL,
            `Name` varchar(255) NOT NULL,
            `Value` longtext NULL,
            PRIMARY KEY (`UserId`, `LoginProvider`, `Name`),
            CONSTRAINT `FK_AspNetUserTokens_AspNetUsers_UserId` FOREIGN KEY (`UserId`) REFERENCES `AspNetUsers` (`Id`) ON DELETE CASCADE
        ) CHARACTER SET=utf8mb4;

        CREATE UNIQUE INDEX IF NOT EXISTS `RoleNameIndex` ON `AspNetRoles` (`NormalizedName`);
        CREATE INDEX IF NOT EXISTS `IX_AspNetRoleClaims_RoleId` ON `AspNetRoleClaims` (`RoleId`);
        CREATE INDEX IF NOT EXISTS `EmailIndex` ON `AspNetUsers` (`NormalizedEmail`);
        CREATE UNIQUE INDEX IF NOT EXISTS `UserNameIndex` ON `AspNetUsers` (`NormalizedUserName`);
        CREATE INDEX IF NOT EXISTS `IX_AspNetUserClaims_UserId` ON `AspNetUserClaims` (`UserId`);
        CREATE INDEX IF NOT EXISTS `IX_AspNetUserLogins_UserId` ON `AspNetUserLogins` (`UserId`);
        CREATE INDEX IF NOT EXISTS `IX_AspNetUserRoles_RoleId` ON `AspNetUserRoles` (`RoleId`);
        ";
                cmd.ExecuteNonQuery();
            }

            if (useSqliteBootstrap)
            {
                // Bridge older dev DBs that were modified via ad-hoc ALTER TABLE (before migrations were applied).
                // If the schema already contains the columns added by migration 20260310195000, record that migration
                // so EF doesn't try to apply it again and fail on duplicate columns.
                const string legacyMigrationId = "20260310195000_AddJobFieldsAndSoftDelete";
                const string legacyProductVersion = "7.0.17";

                using DbConnection conn = db.Database.GetDbConnection();
                conn.Open();

                static void EnsureIdentityTables(DbConnection c)
                {
                    // EF migrations are used for the app schema. In some environments `dotnet ef` isn’t available,
                    // so create the ASP.NET Core Identity tables directly if they don’t exist yet.
                    if (HasTable(c, "AspNetUsers")) return;

                    Exec(c, """
        CREATE TABLE IF NOT EXISTS "AspNetRoles" (
            "Id" TEXT NOT NULL CONSTRAINT "PK_AspNetRoles" PRIMARY KEY,
            "Name" TEXT NULL,
            "NormalizedName" TEXT NULL,
            "ConcurrencyStamp" TEXT NULL
        );
        """);

                    Exec(c, """
        CREATE TABLE IF NOT EXISTS "AspNetUsers" (
            "Id" TEXT NOT NULL CONSTRAINT "PK_AspNetUsers" PRIMARY KEY,
            "UserName" TEXT NULL,
            "NormalizedUserName" TEXT NULL,
            "Email" TEXT NULL,
            "NormalizedEmail" TEXT NULL,
            "EmailConfirmed" INTEGER NOT NULL,
            "PasswordHash" TEXT NULL,
            "SecurityStamp" TEXT NULL,
            "ConcurrencyStamp" TEXT NULL,
            "PhoneNumber" TEXT NULL,
            "PhoneNumberConfirmed" INTEGER NOT NULL,
            "TwoFactorEnabled" INTEGER NOT NULL,
            "LockoutEnd" TEXT NULL,
            "LockoutEnabled" INTEGER NOT NULL,
            "AccessFailedCount" INTEGER NOT NULL,
            "FirstName" TEXT NULL,
            "LastName" TEXT NULL,
            "DisplayName" TEXT NULL,
            "ProfileCvText" TEXT NULL,
            "ProfileCvStructureJson" TEXT NULL,
            "CurrentCvUploadArtifactId" INTEGER NULL,
            "CurrentCvExtractionRunId" INTEGER NULL,
            "CurrentCvProfileVersion" INTEGER NULL,
            "AvatarImageDataUrl" TEXT NULL,
            "GoogleSubject" TEXT NULL,
            "GoogleEmail" TEXT NULL,
            "GoogleLinkedAt" TEXT NULL
        );
        """);

                    Exec(c, """
        CREATE TABLE IF NOT EXISTS "AspNetRoleClaims" (
            "Id" INTEGER NOT NULL CONSTRAINT "PK_AspNetRoleClaims" PRIMARY KEY AUTOINCREMENT,
            "RoleId" TEXT NOT NULL,
            "ClaimType" TEXT NULL,
            "ClaimValue" TEXT NULL,
            CONSTRAINT "FK_AspNetRoleClaims_AspNetRoles_RoleId" FOREIGN KEY ("RoleId") REFERENCES "AspNetRoles" ("Id") ON DELETE CASCADE
        );
        """);

                    Exec(c, """
        CREATE TABLE IF NOT EXISTS "AspNetUserClaims" (
            "Id" INTEGER NOT NULL CONSTRAINT "PK_AspNetUserClaims" PRIMARY KEY AUTOINCREMENT,
            "UserId" TEXT NOT NULL,
            "ClaimType" TEXT NULL,
            "ClaimValue" TEXT NULL,
            CONSTRAINT "FK_AspNetUserClaims_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
        );
        """);

                    Exec(c, """
        CREATE TABLE IF NOT EXISTS "AspNetUserLogins" (
            "LoginProvider" TEXT NOT NULL,
            "ProviderKey" TEXT NOT NULL,
            "ProviderDisplayName" TEXT NULL,
            "UserId" TEXT NOT NULL,
            CONSTRAINT "PK_AspNetUserLogins" PRIMARY KEY ("LoginProvider", "ProviderKey"),
            CONSTRAINT "FK_AspNetUserLogins_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
        );
        """);

                    Exec(c, """
        CREATE TABLE IF NOT EXISTS "AspNetUserRoles" (
            "UserId" TEXT NOT NULL,
            "RoleId" TEXT NOT NULL,
            CONSTRAINT "PK_AspNetUserRoles" PRIMARY KEY ("UserId", "RoleId"),
            CONSTRAINT "FK_AspNetUserRoles_AspNetRoles_RoleId" FOREIGN KEY ("RoleId") REFERENCES "AspNetRoles" ("Id") ON DELETE CASCADE,
            CONSTRAINT "FK_AspNetUserRoles_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
        );
        """);

                    Exec(c, """
        CREATE TABLE IF NOT EXISTS "AspNetUserTokens" (
            "UserId" TEXT NOT NULL,
            "LoginProvider" TEXT NOT NULL,
            "Name" TEXT NOT NULL,
            "Value" TEXT NULL,
            CONSTRAINT "PK_AspNetUserTokens" PRIMARY KEY ("UserId", "LoginProvider", "Name"),
            CONSTRAINT "FK_AspNetUserTokens_AspNetUsers_UserId" FOREIGN KEY ("UserId") REFERENCES "AspNetUsers" ("Id") ON DELETE CASCADE
        );
        """);

                    Exec(c, """CREATE UNIQUE INDEX IF NOT EXISTS "RoleNameIndex" ON "AspNetRoles" ("NormalizedName");""");
                    Exec(c, """CREATE INDEX IF NOT EXISTS "IX_AspNetRoleClaims_RoleId" ON "AspNetRoleClaims" ("RoleId");""");
                    Exec(c, """CREATE INDEX IF NOT EXISTS "EmailIndex" ON "AspNetUsers" ("NormalizedEmail");""");
                    Exec(c, """CREATE UNIQUE INDEX IF NOT EXISTS "UserNameIndex" ON "AspNetUsers" ("NormalizedUserName");""");
                    Exec(c, """CREATE INDEX IF NOT EXISTS "IX_AspNetUserClaims_UserId" ON "AspNetUserClaims" ("UserId");""");
                    Exec(c, """CREATE INDEX IF NOT EXISTS "IX_AspNetUserLogins_UserId" ON "AspNetUserLogins" ("UserId");""");
                    Exec(c, """CREATE INDEX IF NOT EXISTS "IX_AspNetUserRoles_RoleId" ON "AspNetUserRoles" ("RoleId");""");
                }

                EnsureIdentityTables(conn);
                EnsureColumn(conn, "AspNetUsers", "FirstName", "ALTER TABLE AspNetUsers ADD COLUMN FirstName TEXT NULL;");
                EnsureColumn(conn, "AspNetUsers", "LastName", "ALTER TABLE AspNetUsers ADD COLUMN LastName TEXT NULL;");
                EnsureColumn(conn, "AspNetUsers", "DisplayName", "ALTER TABLE AspNetUsers ADD COLUMN DisplayName TEXT NULL;");
                EnsureColumn(conn, "AspNetUsers", "ProfileCvText", "ALTER TABLE AspNetUsers ADD COLUMN ProfileCvText TEXT NULL;");
                EnsureColumn(conn, "AspNetUsers", "ProfileCvStructureJson", "ALTER TABLE AspNetUsers ADD COLUMN ProfileCvStructureJson TEXT NULL;");
                EnsureColumn(conn, "AspNetUsers", "CurrentCvUploadArtifactId", "ALTER TABLE AspNetUsers ADD COLUMN CurrentCvUploadArtifactId INTEGER NULL;");
                EnsureColumn(conn, "AspNetUsers", "CurrentCvExtractionRunId", "ALTER TABLE AspNetUsers ADD COLUMN CurrentCvExtractionRunId INTEGER NULL;");
                EnsureColumn(conn, "AspNetUsers", "CurrentCvProfileVersion", "ALTER TABLE AspNetUsers ADD COLUMN CurrentCvProfileVersion INTEGER NULL;");
                EnsureColumn(conn, "AspNetUsers", "AvatarImageDataUrl", "ALTER TABLE AspNetUsers ADD COLUMN AvatarImageDataUrl TEXT NULL;");
                EnsureColumn(conn, "AspNetUsers", "GoogleSubject", "ALTER TABLE AspNetUsers ADD COLUMN GoogleSubject TEXT NULL;");
                EnsureColumn(conn, "AspNetUsers", "GoogleEmail", "ALTER TABLE AspNetUsers ADD COLUMN GoogleEmail TEXT NULL;");
                EnsureColumn(conn, "AspNetUsers", "GoogleLinkedAt", "ALTER TABLE AspNetUsers ADD COLUMN GoogleLinkedAt TEXT NULL;");

                static void EnsureUserRuleSettingsTable(DbConnection c)
                {
                    if (HasTable(c, "UserRuleSettings")) return;

                    Exec(c, """
        CREATE TABLE IF NOT EXISTS "UserRuleSettings" (
            "OwnerUserId" TEXT NOT NULL CONSTRAINT "PK_UserRuleSettings" PRIMARY KEY,
            "AppliedFollowUpDays" INTEGER NOT NULL,
            "AppliedGhostDays" INTEGER NOT NULL,
            "OfferFollowUpDays" INTEGER NOT NULL,
            "OfferGhostDays" INTEGER NOT NULL,
            "FeedbackFollowUpDays" INTEGER NOT NULL,
            "FeedbackGhostDays" INTEGER NOT NULL
        );
        """);
                }

                EnsureUserRuleSettingsTable(conn);

                static void EnsureGmailConnectionsTable(DbConnection c)
                {
                    Exec(c, """
        CREATE TABLE IF NOT EXISTS "GmailConnections" (
            "Id" INTEGER NOT NULL CONSTRAINT "PK_GmailConnections" PRIMARY KEY AUTOINCREMENT,
            "OwnerUserId" TEXT NOT NULL,
            "GmailAddress" TEXT NOT NULL,
            "EncryptedRefreshToken" TEXT NOT NULL,
            "EncryptedAccessToken" TEXT NULL,
            "AccessTokenExpiresAt" TEXT NULL,
            "Scope" TEXT NOT NULL,
            "ConnectedAt" TEXT NOT NULL,
            "LastSyncedAt" TEXT NULL,
            "LastSyncAttemptedAt" TEXT NULL,
            "LastSyncSucceededAt" TEXT NULL,
            "LastSyncMode" TEXT NULL,
            "LastSyncSource" TEXT NULL,
            "LastSyncStatus" TEXT NULL,
            "LastSyncError" TEXT NULL
        );
        """);

                    Exec(c, """
        CREATE TABLE IF NOT EXISTS "GmailReviewDecisions" (
            "Id" INTEGER NOT NULL CONSTRAINT "PK_GmailReviewDecisions" PRIMARY KEY AUTOINCREMENT,
            "OwnerUserId" TEXT NOT NULL,
            "ThreadId" TEXT NOT NULL,
            "JobApplicationId" INTEGER NULL,
            "Decision" TEXT NOT NULL,
            "Note" TEXT NULL,
            "UpdatedAt" TEXT NOT NULL
        );
        """);

                    EnsureColumn(c, "GmailConnections", "LastSyncAttemptedAt", "ALTER TABLE GmailConnections ADD COLUMN LastSyncAttemptedAt TEXT NULL;");
                    EnsureColumn(c, "GmailConnections", "LastSyncSucceededAt", "ALTER TABLE GmailConnections ADD COLUMN LastSyncSucceededAt TEXT NULL;");
                    EnsureColumn(c, "GmailConnections", "LastSyncMode", "ALTER TABLE GmailConnections ADD COLUMN LastSyncMode TEXT NULL;");
                    EnsureColumn(c, "GmailConnections", "LastSyncSource", "ALTER TABLE GmailConnections ADD COLUMN LastSyncSource TEXT NULL;");
                    EnsureColumn(c, "GmailConnections", "LastSyncStatus", "ALTER TABLE GmailConnections ADD COLUMN LastSyncStatus TEXT NULL;");
                    EnsureColumn(c, "GmailConnections", "LastSyncError", "ALTER TABLE GmailConnections ADD COLUMN LastSyncError TEXT NULL;");

                    Exec(c, """CREATE INDEX IF NOT EXISTS "IX_GmailConnections_OwnerUserId" ON "GmailConnections" ("OwnerUserId");""");
                    Exec(c, """CREATE UNIQUE INDEX IF NOT EXISTS "IX_GmailConnections_OwnerUserId_GmailAddress" ON "GmailConnections" ("OwnerUserId", "GmailAddress");""");
                }

                static void EnsureMicrosoftGraphConnectionsTable(DbConnection c)
                {
                    Exec(c, """
        CREATE TABLE IF NOT EXISTS "MicrosoftGraphConnections" (
            "Id" INTEGER NOT NULL CONSTRAINT "PK_MicrosoftGraphConnections" PRIMARY KEY AUTOINCREMENT,
            "OwnerUserId" TEXT NOT NULL,
            "MailAddress" TEXT NOT NULL,
            "EncryptedRefreshToken" TEXT NOT NULL,
            "EncryptedAccessToken" TEXT NULL,
            "AccessTokenExpiresAt" TEXT NULL,
            "Scope" TEXT NOT NULL,
            "ConnectedAt" TEXT NOT NULL,
            "LastSyncedAt" TEXT NULL,
            "LastSyncAttemptedAt" TEXT NULL,
            "LastSyncSucceededAt" TEXT NULL,
            "LastSyncMode" TEXT NULL,
            "LastSyncSource" TEXT NULL,
            "LastSyncStatus" TEXT NULL,
            "LastSyncError" TEXT NULL
        );
        """);

                    Exec(c, """CREATE INDEX IF NOT EXISTS "IX_MicrosoftGraphConnections_OwnerUserId" ON "MicrosoftGraphConnections" ("OwnerUserId");""");
                    Exec(c, """CREATE UNIQUE INDEX IF NOT EXISTS "IX_MicrosoftGraphConnections_OwnerUserId_MailAddress" ON "MicrosoftGraphConnections" ("OwnerUserId", "MailAddress");""");
                }

                static void EnsureImapConnectionsTable(DbConnection c)
                {
                    Exec(c, """
        CREATE TABLE IF NOT EXISTS "ImapConnections" (
            "Id" INTEGER NOT NULL CONSTRAINT "PK_ImapConnections" PRIMARY KEY AUTOINCREMENT,
            "OwnerUserId" TEXT NOT NULL,
            "Host" TEXT NOT NULL,
            "Port" INTEGER NOT NULL,
            "UseSsl" INTEGER NOT NULL,
            "Username" TEXT NOT NULL,
            "EncryptedPassword" TEXT NOT NULL,
            "ConnectedAt" TEXT NOT NULL,
            "LastSyncedAt" TEXT NULL,
            "LastSyncAttemptedAt" TEXT NULL,
            "LastSyncSucceededAt" TEXT NULL,
            "LastSyncMode" TEXT NULL,
            "LastSyncSource" TEXT NULL,
            "LastSyncStatus" TEXT NULL,
            "LastSyncError" TEXT NULL
        );
        """);

                    Exec(c, """CREATE UNIQUE INDEX IF NOT EXISTS "IX_ImapConnections_OwnerUserId" ON "ImapConnections" ("OwnerUserId");""");
                }

                static void EnsureCvTables(DbConnection c)
                {
                    Exec(c, """
        CREATE TABLE IF NOT EXISTS "CvUploadArtifacts" (
            "Id" INTEGER NOT NULL CONSTRAINT "PK_CvUploadArtifacts" PRIMARY KEY AUTOINCREMENT,
            "OwnerUserId" TEXT NOT NULL,
            "OriginalFileName" TEXT NOT NULL,
            "StoredFileName" TEXT NOT NULL,
            "MimeType" TEXT NOT NULL,
            "ByteSize" INTEGER NOT NULL,
            "Sha256" TEXT NOT NULL,
            "StoragePath" TEXT NOT NULL,
            "UploadedAtUtc" TEXT NOT NULL
        );
        """);

                    Exec(c, """
        CREATE TABLE IF NOT EXISTS "CvExtractionRuns" (
            "Id" INTEGER NOT NULL CONSTRAINT "PK_CvExtractionRuns" PRIMARY KEY AUTOINCREMENT,
            "OwnerUserId" TEXT NOT NULL,
            "ArtifactId" INTEGER NULL,
            "Trigger" TEXT NOT NULL,
            "ParserVersion" TEXT NOT NULL,
            "NormalizerVersion" TEXT NOT NULL,
            "LlmPromptVersion" TEXT NOT NULL,
            "Status" TEXT NOT NULL,
            "RawExtractedText" TEXT NULL,
            "NormalizedText" TEXT NULL,
            "StructuredProfileJson" TEXT NULL,
            "ErrorMessage" TEXT NULL,
            "StartedAtUtc" TEXT NOT NULL,
            "CompletedAtUtc" TEXT NULL,
            "AppliedAtUtc" TEXT NULL,
            CONSTRAINT "FK_CvExtractionRuns_CvUploadArtifacts_ArtifactId" FOREIGN KEY ("ArtifactId") REFERENCES "CvUploadArtifacts" ("Id") ON DELETE SET NULL
        );
        """);

                    Exec(c, """CREATE INDEX IF NOT EXISTS "IX_CvUploadArtifacts_OwnerUserId_UploadedAtUtc" ON "CvUploadArtifacts" ("OwnerUserId", "UploadedAtUtc");""");
                    Exec(c, """CREATE INDEX IF NOT EXISTS "IX_CvExtractionRuns_OwnerUserId_StartedAtUtc" ON "CvExtractionRuns" ("OwnerUserId", "StartedAtUtc");""");
                    Exec(c, """CREATE INDEX IF NOT EXISTS "IX_CvExtractionRuns_ArtifactId" ON "CvExtractionRuns" ("ArtifactId");""");

                    Exec(c, """
        CREATE TABLE IF NOT EXISTS "TailoredCvDrafts" (
            "Id" INTEGER NOT NULL CONSTRAINT "PK_TailoredCvDrafts" PRIMARY KEY AUTOINCREMENT,
            "OwnerUserId" TEXT NOT NULL,
            "JobApplicationId" INTEGER NOT NULL,
            "CanonicalProfileVersion" INTEGER NULL,
            "TemplateId" TEXT NOT NULL,
            "Headline" TEXT NULL,
            "SummaryJson" TEXT NULL,
            "SelectedSkillsJson" TEXT NULL,
            "ExperienceJson" TEXT NULL,
            "EducationJson" TEXT NULL,
            "CustomSectionsJson" TEXT NULL,
            "RenderOptionsJson" TEXT NULL,
            "GenerationContextHash" TEXT NULL,
            "LastGeneratedAtUtc" TEXT NULL,
            "LastEditedAtUtc" TEXT NULL,
            "Status" TEXT NOT NULL,
            CONSTRAINT "FK_TailoredCvDrafts_JobApplications_JobApplicationId" FOREIGN KEY ("JobApplicationId") REFERENCES "JobApplications" ("Id") ON DELETE CASCADE
        );
        """);

                    Exec(c, """CREATE UNIQUE INDEX IF NOT EXISTS "IX_TailoredCvDrafts_OwnerUserId_JobApplicationId" ON "TailoredCvDrafts" ("OwnerUserId", "JobApplicationId");""");
                    Exec(c, """CREATE INDEX IF NOT EXISTS "IX_TailoredCvDrafts_JobApplicationId" ON "TailoredCvDrafts" ("JobApplicationId");""");
                }

                EnsureGmailConnectionsTable(conn);
                EnsureMicrosoftGraphConnectionsTable(conn);
                EnsureImapConnectionsTable(conn);
                EnsureCvTables(conn);

                // Legacy DB signature: migration history exists (AddCorrespondence applied), but 20260310195000 not recorded,
                // and at least one of the new columns already exists.
                var isLegacy =
                    HasMigration(conn, "20260310174114_AddCorrespondence") &&
                    !HasMigration(conn, legacyMigrationId) &&
                    (HasColumn(conn, "Companies", "Source") || HasColumn(conn, "JobApplications", "IsDeleted"));

                if (isLegacy)
                {
                    EnsureColumn(conn, "Companies", "Source", "ALTER TABLE Companies ADD COLUMN Source TEXT NULL;");
                    EnsureColumn(conn, "JobApplications", "IsDeleted", "ALTER TABLE JobApplications ADD COLUMN IsDeleted INTEGER NOT NULL DEFAULT 0;");
                    EnsureColumn(conn, "JobApplications", "DeletedAt", "ALTER TABLE JobApplications ADD COLUMN DeletedAt TEXT NULL;");
                    EnsureColumn(conn, "JobApplications", "Location", "ALTER TABLE JobApplications ADD COLUMN Location TEXT NULL;");
                    EnsureColumn(conn, "JobApplications", "Salary", "ALTER TABLE JobApplications ADD COLUMN Salary TEXT NULL;");
                    EnsureColumn(conn, "JobApplications", "NextAction", "ALTER TABLE JobApplications ADD COLUMN NextAction TEXT NULL;");
                    EnsureColumn(conn, "JobApplications", "FollowUpAt", "ALTER TABLE JobApplications ADD COLUMN FollowUpAt TEXT NULL;");

                    // Ensure the persisted short summary column exists for older dev DBs.
                    EnsureColumn(conn, "JobApplications", "ShortSummary", "ALTER TABLE JobApplications ADD COLUMN ShortSummary TEXT NULL;");

                    // Multi-user support: scope data to the authenticated user.
                    EnsureColumn(conn, "Companies", "OwnerUserId", "ALTER TABLE Companies ADD COLUMN OwnerUserId TEXT NULL;");
                    EnsureColumn(conn, "JobApplications", "OwnerUserId", "ALTER TABLE JobApplications ADD COLUMN OwnerUserId TEXT NULL;");

                    // Legacy DBs may be missing later correspondence columns (Subject/Channel).
                    if (HasTable(conn, "Correspondences"))
                    {
                        EnsureColumn(conn, "Correspondences", "Subject", "ALTER TABLE Correspondences ADD COLUMN Subject TEXT NULL;");
                        EnsureColumn(conn, "Correspondences", "Channel", "ALTER TABLE Correspondences ADD COLUMN Channel TEXT NULL;");
                        EnsureColumn(conn, "Correspondences", "ExternalMessageId", "ALTER TABLE Correspondences ADD COLUMN ExternalMessageId TEXT NULL;");
                        EnsureColumn(conn, "Correspondences", "ExternalThreadId", "ALTER TABLE Correspondences ADD COLUMN ExternalThreadId TEXT NULL;");
                        EnsureColumn(conn, "Correspondences", "ExternalFrom", "ALTER TABLE Correspondences ADD COLUMN ExternalFrom TEXT NULL;");
                        EnsureColumn(conn, "Correspondences", "ExternalTo", "ALTER TABLE Correspondences ADD COLUMN ExternalTo TEXT NULL;");
                        EnsureColumn(conn, "Correspondences", "Direction", "ALTER TABLE Correspondences ADD COLUMN Direction TEXT NULL;");
                        EnsureColumn(conn, "Correspondences", "ExternalLabelsJson", "ALTER TABLE Correspondences ADD COLUMN ExternalLabelsJson TEXT NULL;");
                        EnsureColumn(conn, "Correspondences", "AttachmentMetadataJson", "ALTER TABLE Correspondences ADD COLUMN AttachmentMetadataJson TEXT NULL;");
                    }

                    // Record the migration as applied.
                    Exec(
                        conn,
                        "INSERT INTO __EFMigrationsHistory (MigrationId, ProductVersion) " +
                            $"VALUES ('{legacyMigrationId}', '{legacyProductVersion}');"
                    );
                }

                // Some dev DBs may not match the "legacy" fingerprint above but still lack
                // later ad-hoc columns. Ensure them unconditionally if missing (also re-run once
                // more after Migrate() below, in case this is a brand-new DB where these tables
                // didn't exist yet at this point).
                ReconcileCoreAppColumns(conn);

                // Hot-path composite indexes for tenant-scoped list/board/stats/analytics
                // (OwnerUserId + IsDeleted) and reminders (OwnerUserId + FollowUpAt). Guarded
                // on table existence: on a brand-new DB the table is created by Migrate()
                // below, so the index is picked up on the next start.
                if (HasTable(conn, "JobApplications"))
                {
                    Exec(conn, """CREATE INDEX IF NOT EXISTS "IX_JobApplications_OwnerUserId_IsDeleted" ON "JobApplications" ("OwnerUserId", "IsDeleted");""");
                    Exec(conn, """CREATE INDEX IF NOT EXISTS "IX_JobApplications_OwnerUserId_FollowUpAt" ON "JobApplications" ("OwnerUserId", "FollowUpAt");""");
                }

                // Ensure data folder exists before creating/opening SQLite files.
                Directory.CreateDirectory(paths.DataRoot);
            }
            else
            {
                var cs = app.Configuration.GetConnectionString("JobTracker");
                if (!string.IsNullOrWhiteSpace(cs))
                {
                    using DbConnection conn = db.Database.GetDbConnection();
                    conn.Open();
                    EnsureIdentityTablesMySql(conn);

                    static bool MySqlIndexExists(DbConnection c, string table, string indexName)
                    {
                        using var cmd = c.CreateCommand();
                        cmd.CommandText = "SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @table AND INDEX_NAME = @index LIMIT 1;";

                        var p1 = cmd.CreateParameter(); p1.ParameterName = "@schema"; p1.Value = c.Database; cmd.Parameters.Add(p1);
                        var p2 = cmd.CreateParameter(); p2.ParameterName = "@table"; p2.Value = table; cmd.Parameters.Add(p2);
                        var p4 = cmd.CreateParameter(); p4.ParameterName = "@index"; p4.Value = indexName; cmd.Parameters.Add(p4);

                        return cmd.ExecuteScalar() is not null;
                    }


                    static bool MySqlIntPrimaryKeyIsAutoIncrement(DbConnection c, string table, string column)
                    {
                        using var cmd = c.CreateCommand();
                        cmd.CommandText = @"SELECT EXTRA
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @table AND COLUMN_NAME = @column
        LIMIT 1;";
                        var p1 = cmd.CreateParameter(); p1.ParameterName = "@schema"; p1.Value = c.Database; cmd.Parameters.Add(p1);
                        var p2 = cmd.CreateParameter(); p2.ParameterName = "@table"; p2.Value = table; cmd.Parameters.Add(p2);
                        var p3 = cmd.CreateParameter(); p3.ParameterName = "@column"; p3.Value = column; cmd.Parameters.Add(p3);
                        var extra = cmd.ExecuteScalar()?.ToString() ?? string.Empty;
                        return extra.Contains("auto_increment", StringComparison.OrdinalIgnoreCase);
                    }

                    static void EnsureMySqlAutoIncrementPrimaryKey(DbConnection c, string table, string column)
                    {
                        if (!HasMySqlTable(c, table) || !MySqlColumnExists(c, table, column) || MySqlIntPrimaryKeyIsAutoIncrement(c, table, column))
                        {
                            return;
                        }

                        using var cmd = c.CreateCommand();
                        cmd.CommandText = $"ALTER TABLE `{table}` MODIFY COLUMN `{column}` int NOT NULL AUTO_INCREMENT;";
                        cmd.ExecuteNonQuery();
                    }

                    EnsureMySqlAutoIncrementPrimaryKey(conn, "Companies", "Id");
                    EnsureMySqlAutoIncrementPrimaryKey(conn, "JobApplications", "Id");
                    EnsureMySqlAutoIncrementPrimaryKey(conn, "Correspondences", "Id");
                    EnsureMySqlAutoIncrementPrimaryKey(conn, "Attachments", "Id");
                    EnsureMySqlAutoIncrementPrimaryKey(conn, "JobEvents", "Id");
                    EnsureMySqlAutoIncrementPrimaryKey(conn, "GmailConnections", "Id");
                    EnsureMySqlAutoIncrementPrimaryKey(conn, "MicrosoftGraphConnections", "Id");
                    EnsureMySqlAutoIncrementPrimaryKey(conn, "ImapConnections", "Id");
                    EnsureMySqlAutoIncrementPrimaryKey(conn, "CvUploadArtifacts", "Id");
                    EnsureMySqlAutoIncrementPrimaryKey(conn, "CvExtractionRuns", "Id");
                    EnsureMySqlAutoIncrementPrimaryKey(conn, "TailoredCvDrafts", "Id");

                    // Ad-hoc columns for the tables Migrate() creates (Companies/JobApplications/
                    // Correspondences/Attachments) -- re-run once more after Migrate() below via
                    // ReconcileCoreAppColumnsMySql, in case this is a brand-new database.
                    ReconcileCoreAppColumnsMySql(conn);
                    EnsureMySqlColumn(conn, "AspNetUsers", "ProfileCvText", "ALTER TABLE `AspNetUsers` ADD COLUMN `ProfileCvText` longtext NULL;");
                    EnsureMySqlColumn(conn, "AspNetUsers", "ProfileCvStructureJson", "ALTER TABLE `AspNetUsers` ADD COLUMN `ProfileCvStructureJson` longtext NULL;");
                    EnsureMySqlColumn(conn, "AspNetUsers", "CurrentCvUploadArtifactId", "ALTER TABLE `AspNetUsers` ADD COLUMN `CurrentCvUploadArtifactId` int NULL;");
                    EnsureMySqlColumn(conn, "AspNetUsers", "CurrentCvExtractionRunId", "ALTER TABLE `AspNetUsers` ADD COLUMN `CurrentCvExtractionRunId` int NULL;");
                    EnsureMySqlColumn(conn, "AspNetUsers", "CurrentCvProfileVersion", "ALTER TABLE `AspNetUsers` ADD COLUMN `CurrentCvProfileVersion` int NULL;");
                    EnsureMySqlColumn(conn, "AspNetUsers", "AvatarImageDataUrl", "ALTER TABLE `AspNetUsers` ADD COLUMN `AvatarImageDataUrl` longtext NULL;");
                    EnsureMySqlColumn(conn, "AspNetUsers", "GoogleSubject", "ALTER TABLE `AspNetUsers` ADD COLUMN `GoogleSubject` longtext NULL;");
                    EnsureMySqlColumn(conn, "AspNetUsers", "GoogleEmail", "ALTER TABLE `AspNetUsers` ADD COLUMN `GoogleEmail` longtext NULL;");
                    EnsureMySqlColumn(conn, "AspNetUsers", "GoogleLinkedAt", "ALTER TABLE `AspNetUsers` ADD COLUMN `GoogleLinkedAt` datetime NULL;");

                    if (!HasMySqlTable(conn, "RuleSettings"))
                    {
                        using var cmd = conn.CreateCommand();
                        cmd.CommandText = @"CREATE TABLE IF NOT EXISTS `RuleSettings` (
        `Id` int NOT NULL,
        `AppliedFollowUpDays` int NOT NULL,
        `AppliedGhostDays` int NOT NULL,
        `OfferFollowUpDays` int NOT NULL,
        `OfferGhostDays` int NOT NULL,
        `FeedbackFollowUpDays` int NOT NULL,
        `FeedbackGhostDays` int NOT NULL,
        PRIMARY KEY (`Id`)
        );";
                        cmd.ExecuteNonQuery();
                    }

                    using (var seedRuleSettings = conn.CreateCommand())
                    {
                        seedRuleSettings.CommandText = @"INSERT INTO `RuleSettings` (`Id`, `AppliedFollowUpDays`, `AppliedGhostDays`, `OfferFollowUpDays`, `OfferGhostDays`, `FeedbackFollowUpDays`, `FeedbackGhostDays`)
        SELECT 1, 14, 30, 7, 14, 7, 14
        WHERE NOT EXISTS (SELECT 1 FROM `RuleSettings` WHERE `Id` = 1);";
                        seedRuleSettings.ExecuteNonQuery();
                    }

                    if (!HasMySqlTable(conn, "UserRuleSettings"))
                    {
                        using var cmd = conn.CreateCommand();
                        cmd.CommandText = @"CREATE TABLE IF NOT EXISTS `UserRuleSettings` (
        `OwnerUserId` varchar(255) NOT NULL,
        `AppliedFollowUpDays` int NOT NULL,
        `AppliedGhostDays` int NOT NULL,
        `OfferFollowUpDays` int NOT NULL,
        `OfferGhostDays` int NOT NULL,
        `FeedbackFollowUpDays` int NOT NULL,
        `FeedbackGhostDays` int NOT NULL,
        PRIMARY KEY (`OwnerUserId`)
        );";
                        cmd.ExecuteNonQuery();
                    }

                    if (!HasMySqlTable(conn, "SystemEmailSettings"))
                    {
                        using var cmd = conn.CreateCommand();
                        cmd.CommandText = @"CREATE TABLE IF NOT EXISTS `SystemEmailSettings` (
        `Id` int NOT NULL,
        `Enabled` tinyint(1) NULL,
        `SmtpHost` longtext NULL,
        `SmtpPort` int NULL,
        `SmtpUser` longtext NULL,
        `SmtpPassword` longtext NULL,
        `From` longtext NULL,
        `FromName` longtext NULL,
        `SmtpEnableSsl` tinyint(1) NULL,
        `SmtpTimeoutMs` int NULL,
        PRIMARY KEY (`Id`)
        );";
                        cmd.ExecuteNonQuery();
                    }

                    if (!HasMySqlTable(conn, "CvUploadArtifacts"))
                    {
                        using var cmd = conn.CreateCommand();
                        cmd.CommandText = @"CREATE TABLE IF NOT EXISTS `CvUploadArtifacts` (
        `Id` int NOT NULL AUTO_INCREMENT,
        `OwnerUserId` varchar(255) NOT NULL,
        `OriginalFileName` longtext NOT NULL,
        `StoredFileName` longtext NOT NULL,
        `MimeType` longtext NOT NULL,
        `ByteSize` bigint NOT NULL,
        `Sha256` longtext NOT NULL,
        `StoragePath` longtext NOT NULL,
        `UploadedAtUtc` datetime(6) NOT NULL,
        PRIMARY KEY (`Id`)
        );";
                        cmd.ExecuteNonQuery();
                    }

                    if (!HasMySqlTable(conn, "CvExtractionRuns"))
                    {
                        using var cmd = conn.CreateCommand();
                        cmd.CommandText = @"CREATE TABLE IF NOT EXISTS `CvExtractionRuns` (
        `Id` int NOT NULL AUTO_INCREMENT,
        `OwnerUserId` varchar(255) NOT NULL,
        `ArtifactId` int NULL,
        `Trigger` longtext NOT NULL,
        `ParserVersion` longtext NOT NULL,
        `NormalizerVersion` longtext NOT NULL,
        `LlmPromptVersion` longtext NOT NULL,
        `Status` longtext NOT NULL,
        `RawExtractedText` longtext NULL,
        `NormalizedText` longtext NULL,
        `StructuredProfileJson` longtext NULL,
        `ErrorMessage` longtext NULL,
        `StartedAtUtc` datetime(6) NOT NULL,
        `CompletedAtUtc` datetime(6) NULL,
        `AppliedAtUtc` datetime(6) NULL,
        PRIMARY KEY (`Id`),
        CONSTRAINT `FK_CvExtractionRuns_CvUploadArtifacts_ArtifactId` FOREIGN KEY (`ArtifactId`) REFERENCES `CvUploadArtifacts` (`Id`) ON DELETE SET NULL
        );";
                        cmd.ExecuteNonQuery();
                    }

                    if (!HasMySqlTable(conn, "GmailConnections"))
                    {
                        using var cmd = conn.CreateCommand();
                        cmd.CommandText = @"CREATE TABLE IF NOT EXISTS `GmailConnections` (
        `Id` int NOT NULL AUTO_INCREMENT,
        `OwnerUserId` varchar(255) NOT NULL,
        `GmailAddress` varchar(512) NOT NULL,
        `EncryptedRefreshToken` longtext NOT NULL,
        `EncryptedAccessToken` longtext NULL,
        `AccessTokenExpiresAt` datetime(6) NULL,
        `Scope` longtext NOT NULL,
        `ConnectedAt` datetime(6) NOT NULL,
        `LastSyncedAt` datetime(6) NULL,
        `LastSyncAttemptedAt` datetime(6) NULL,
        `LastSyncSucceededAt` datetime(6) NULL,
        `LastSyncMode` varchar(255) NULL,
        `LastSyncSource` varchar(255) NULL,
        `LastSyncStatus` varchar(255) NULL,
        `LastSyncError` longtext NULL,
        PRIMARY KEY (`Id`)
        );";
                        cmd.ExecuteNonQuery();
                    }

                    EnsureMySqlColumn(conn, "GmailConnections", "LastSyncAttemptedAt", "ALTER TABLE `GmailConnections` ADD COLUMN `LastSyncAttemptedAt` datetime(6) NULL;");
                    EnsureMySqlColumn(conn, "GmailConnections", "LastSyncSucceededAt", "ALTER TABLE `GmailConnections` ADD COLUMN `LastSyncSucceededAt` datetime(6) NULL;");
                    EnsureMySqlColumn(conn, "GmailConnections", "LastSyncMode", "ALTER TABLE `GmailConnections` ADD COLUMN `LastSyncMode` varchar(255) NULL;");
                    EnsureMySqlColumn(conn, "GmailConnections", "LastSyncSource", "ALTER TABLE `GmailConnections` ADD COLUMN `LastSyncSource` varchar(255) NULL;");
                    EnsureMySqlColumn(conn, "GmailConnections", "LastSyncStatus", "ALTER TABLE `GmailConnections` ADD COLUMN `LastSyncStatus` varchar(255) NULL;");
                    EnsureMySqlColumn(conn, "GmailConnections", "LastSyncError", "ALTER TABLE `GmailConnections` ADD COLUMN `LastSyncError` longtext NULL;");

                    if (!HasMySqlTable(conn, "MicrosoftGraphConnections"))
                    {
                        using var cmd = conn.CreateCommand();
                        cmd.CommandText = @"CREATE TABLE IF NOT EXISTS `MicrosoftGraphConnections` (
        `Id` int NOT NULL AUTO_INCREMENT,
        `OwnerUserId` varchar(255) NOT NULL,
        `MailAddress` varchar(512) NOT NULL,
        `EncryptedRefreshToken` longtext NOT NULL,
        `EncryptedAccessToken` longtext NULL,
        `AccessTokenExpiresAt` datetime(6) NULL,
        `Scope` longtext NOT NULL,
        `ConnectedAt` datetime(6) NOT NULL,
        `LastSyncedAt` datetime(6) NULL,
        `LastSyncAttemptedAt` datetime(6) NULL,
        `LastSyncSucceededAt` datetime(6) NULL,
        `LastSyncMode` varchar(255) NULL,
        `LastSyncSource` varchar(255) NULL,
        `LastSyncStatus` varchar(255) NULL,
        `LastSyncError` longtext NULL,
        PRIMARY KEY (`Id`)
        );";
                        cmd.ExecuteNonQuery();
                    }

                    if (!HasMySqlTable(conn, "ImapConnections"))
                    {
                        using var cmd = conn.CreateCommand();
                        cmd.CommandText = @"CREATE TABLE IF NOT EXISTS `ImapConnections` (
        `Id` int NOT NULL AUTO_INCREMENT,
        `OwnerUserId` varchar(255) NOT NULL,
        `Host` varchar(255) NOT NULL,
        `Port` int NOT NULL,
        `UseSsl` tinyint(1) NOT NULL,
        `Username` varchar(255) NOT NULL,
        `EncryptedPassword` longtext NOT NULL,
        `ConnectedAt` datetime(6) NOT NULL,
        `LastSyncedAt` datetime(6) NULL,
        `LastSyncAttemptedAt` datetime(6) NULL,
        `LastSyncSucceededAt` datetime(6) NULL,
        `LastSyncMode` varchar(255) NULL,
        `LastSyncSource` varchar(255) NULL,
        `LastSyncStatus` varchar(255) NULL,
        `LastSyncError` longtext NULL,
        PRIMARY KEY (`Id`)
        );";
                        cmd.ExecuteNonQuery();
                    }

                    if (!HasMySqlTable(conn, "TailoredCvDrafts"))
                    {
                        using var cmd = conn.CreateCommand();
                        cmd.CommandText = @"CREATE TABLE IF NOT EXISTS `TailoredCvDrafts` (
        `Id` int NOT NULL AUTO_INCREMENT,
        `OwnerUserId` varchar(255) NOT NULL,
        `JobApplicationId` int NOT NULL,
        `CanonicalProfileVersion` int NULL,
        `TemplateId` varchar(100) NOT NULL,
        `Headline` longtext NULL,
        `SummaryJson` longtext NULL,
        `SelectedSkillsJson` longtext NULL,
        `ExperienceJson` longtext NULL,
        `EducationJson` longtext NULL,
        `CustomSectionsJson` longtext NULL,
        `RenderOptionsJson` longtext NULL,
        `GenerationContextHash` longtext NULL,
        `LastGeneratedAtUtc` datetime(6) NULL,
        `LastEditedAtUtc` datetime(6) NULL,
        `Status` varchar(100) NOT NULL,
        PRIMARY KEY (`Id`),
        CONSTRAINT `FK_TailoredCvDrafts_JobApplications_JobApplicationId` FOREIGN KEY (`JobApplicationId`) REFERENCES `JobApplications` (`Id`) ON DELETE CASCADE
        );";
                        cmd.ExecuteNonQuery();
                    }

                    if (!MySqlIndexExists(conn, "Companies", "IX_Companies_OwnerUserId"))
                    {
                        using var cmd = conn.CreateCommand();
                        cmd.CommandText = "CREATE INDEX `IX_Companies_OwnerUserId` ON `Companies` (`OwnerUserId`);";
                        cmd.ExecuteNonQuery();
                    }

                    if (!MySqlIndexExists(conn, "JobApplications", "IX_JobApplications_OwnerUserId"))
                    {
                        using var cmd = conn.CreateCommand();
                        cmd.CommandText = "CREATE INDEX `IX_JobApplications_OwnerUserId` ON `JobApplications` (`OwnerUserId`);";
                        cmd.ExecuteNonQuery();
                    }

                    // Hot-path composite indexes for tenant-scoped list/board/stats/analytics
                    // (OwnerUserId + IsDeleted) and reminders (OwnerUserId + FollowUpAt).
                    if (!MySqlIndexExists(conn, "JobApplications", "IX_JobApplications_OwnerUserId_IsDeleted"))
                    {
                        using var cmd = conn.CreateCommand();
                        cmd.CommandText = "CREATE INDEX `IX_JobApplications_OwnerUserId_IsDeleted` ON `JobApplications` (`OwnerUserId`, `IsDeleted`);";
                        cmd.ExecuteNonQuery();
                    }

                    if (!MySqlIndexExists(conn, "JobApplications", "IX_JobApplications_OwnerUserId_FollowUpAt"))
                    {
                        using var cmd = conn.CreateCommand();
                        cmd.CommandText = "CREATE INDEX `IX_JobApplications_OwnerUserId_FollowUpAt` ON `JobApplications` (`OwnerUserId`, `FollowUpAt`);";
                        cmd.ExecuteNonQuery();
                    }

                    if (!MySqlIndexExists(conn, "CvUploadArtifacts", "IX_CvUploadArtifacts_OwnerUserId_UploadedAtUtc"))
                    {
                        using var cmd = conn.CreateCommand();
                        cmd.CommandText = "CREATE INDEX `IX_CvUploadArtifacts_OwnerUserId_UploadedAtUtc` ON `CvUploadArtifacts` (`OwnerUserId`, `UploadedAtUtc`);";
                        cmd.ExecuteNonQuery();
                    }

                    if (!MySqlIndexExists(conn, "CvExtractionRuns", "IX_CvExtractionRuns_OwnerUserId_StartedAtUtc"))
                    {
                        using var cmd = conn.CreateCommand();
                        cmd.CommandText = "CREATE INDEX `IX_CvExtractionRuns_OwnerUserId_StartedAtUtc` ON `CvExtractionRuns` (`OwnerUserId`, `StartedAtUtc`);";
                        cmd.ExecuteNonQuery();
                    }

                    if (!MySqlIndexExists(conn, "CvExtractionRuns", "IX_CvExtractionRuns_ArtifactId"))
                    {
                        using var cmd = conn.CreateCommand();
                        cmd.CommandText = "CREATE INDEX `IX_CvExtractionRuns_ArtifactId` ON `CvExtractionRuns` (`ArtifactId`);";
                        cmd.ExecuteNonQuery();
                    }

                    if (!MySqlIndexExists(conn, "GmailConnections", "IX_GmailConnections_OwnerUserId"))
                    {
                        using var cmd = conn.CreateCommand();
                        cmd.CommandText = "CREATE INDEX `IX_GmailConnections_OwnerUserId` ON `GmailConnections` (`OwnerUserId`);";
                        cmd.ExecuteNonQuery();
                    }

                    if (!MySqlIndexExists(conn, "GmailConnections", "IX_GmailConnections_OwnerUserId_GmailAddress"))
                    {
                        using var cmd = conn.CreateCommand();
                        cmd.CommandText = "CREATE UNIQUE INDEX `IX_GmailConnections_OwnerUserId_GmailAddress` ON `GmailConnections` (`OwnerUserId`, `GmailAddress`);";
                        cmd.ExecuteNonQuery();
                    }

                    if (!MySqlIndexExists(conn, "MicrosoftGraphConnections", "IX_MicrosoftGraphConnections_OwnerUserId"))
                    {
                        using var cmd = conn.CreateCommand();
                        cmd.CommandText = "CREATE INDEX `IX_MicrosoftGraphConnections_OwnerUserId` ON `MicrosoftGraphConnections` (`OwnerUserId`);";
                        cmd.ExecuteNonQuery();
                    }

                    if (!MySqlIndexExists(conn, "MicrosoftGraphConnections", "IX_MicrosoftGraphConnections_OwnerUserId_MailAddress"))
                    {
                        using var cmd = conn.CreateCommand();
                        cmd.CommandText = "CREATE UNIQUE INDEX `IX_MicrosoftGraphConnections_OwnerUserId_MailAddress` ON `MicrosoftGraphConnections` (`OwnerUserId`, `MailAddress`);";
                        cmd.ExecuteNonQuery();
                    }

                    if (!MySqlIndexExists(conn, "ImapConnections", "IX_ImapConnections_OwnerUserId"))
                    {
                        using var cmd = conn.CreateCommand();
                        cmd.CommandText = "CREATE UNIQUE INDEX `IX_ImapConnections_OwnerUserId` ON `ImapConnections` (`OwnerUserId`);";
                        cmd.ExecuteNonQuery();
                    }

                    if (!MySqlIndexExists(conn, "TailoredCvDrafts", "IX_TailoredCvDrafts_OwnerUserId_JobApplicationId"))
                    {
                        using var cmd = conn.CreateCommand();
                        cmd.CommandText = "CREATE UNIQUE INDEX `IX_TailoredCvDrafts_OwnerUserId_JobApplicationId` ON `TailoredCvDrafts` (`OwnerUserId`, `JobApplicationId`);";
                        cmd.ExecuteNonQuery();
                    }

                    if (!MySqlIndexExists(conn, "TailoredCvDrafts", "IX_TailoredCvDrafts_JobApplicationId"))
                    {
                        using var cmd = conn.CreateCommand();
                        cmd.CommandText = "CREATE INDEX `IX_TailoredCvDrafts_JobApplicationId` ON `TailoredCvDrafts` (`JobApplicationId`);";
                        cmd.ExecuteNonQuery();
                    }
                }
            }

            try
            {
                using var migrationScope = app.Services.CreateScope();
                var migrationDb = migrationScope.ServiceProvider.GetRequiredService<JobTrackerContext>();
                migrationDb.Database.Migrate();
            }
            catch (Exception ex)
            {
                app.Logger.LogError(ex, "Database migration failed during startup initialization.");
                throw;
            }

            // Optional: seed an initial admin user for local username/password login.
            // Set Auth:AdminEmail and Auth:AdminPassword to enable.
            var adminEmail = (app.Configuration["Auth:AdminEmail"] ?? "").Trim();
            var adminPassword = (app.Configuration["Auth:AdminPassword"] ?? "").Trim();
            if (!string.IsNullOrWhiteSpace(adminEmail) && !string.IsNullOrWhiteSpace(adminPassword))
            {
                using var adminScope = app.Services.CreateScope();
                var adminDb = adminScope.ServiceProvider.GetRequiredService<JobTrackerContext>();
                var adminUsers = adminScope.ServiceProvider.GetRequiredService<UserManager<ApplicationUser>>();
                var adminRoles = adminScope.ServiceProvider.GetRequiredService<RoleManager<IdentityRole>>();
                const string adminRole = "Admin";

                if (!adminRoles.RoleExistsAsync(adminRole).GetAwaiter().GetResult())
                {
                    adminRoles.CreateAsync(new IdentityRole(adminRole)).GetAwaiter().GetResult();
                }

                var existing = adminUsers.FindByEmailAsync(adminEmail).GetAwaiter().GetResult();
                if (existing is null)
                {
                    var u = new ApplicationUser { UserName = adminEmail, Email = adminEmail, EmailConfirmed = true };
                    var created = adminUsers.CreateAsync(u, adminPassword).GetAwaiter().GetResult();
                    if (created.Succeeded)
                    {
                        adminUsers.AddToRoleAsync(u, adminRole).GetAwaiter().GetResult();
                        app.Logger.LogInformation("Seeded admin user: {Email}", adminEmail);
                    }
                    else
                    {
                        app.Logger.LogWarning("Failed to seed admin user: {Errors}", string.Join("; ", created.Errors.Select(e => e.Description)));
                    }
                }
                else
                {
                    var inRole = adminUsers.IsInRoleAsync(existing, adminRole).GetAwaiter().GetResult();
                    if (!inRole) adminUsers.AddToRoleAsync(existing, adminRole).GetAwaiter().GetResult();
                }

                // One-time claim of legacy data for the admin user so enabling auth doesn't "hide" existing records.
                var admin = adminUsers.FindByEmailAsync(adminEmail).GetAwaiter().GetResult();
                if (admin is not null)
                {
                    try
                    {
                        using var conn = adminDb.Database.GetDbConnection();
                        conn.Open();

                        static bool ColumnExists(DbConnection c, string providerName, string table, string column)
                        {
                            using var cmd = c.CreateCommand();
                            if (providerName is "mysql" or "mariadb")
                            {
                                var databaseName = c.Database;
                                cmd.CommandText = "SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @table AND COLUMN_NAME = @column LIMIT 1;";

                                var schemaParam = cmd.CreateParameter();
                                schemaParam.ParameterName = "@schema";
                                schemaParam.Value = databaseName;
                                cmd.Parameters.Add(schemaParam);

                                var tableParam = cmd.CreateParameter();
                                tableParam.ParameterName = "@table";
                                tableParam.Value = table;
                                cmd.Parameters.Add(tableParam);

                                var columnParam = cmd.CreateParameter();
                                columnParam.ParameterName = "@column";
                                columnParam.Value = column;
                                cmd.Parameters.Add(columnParam);
                            }
                            else
                            {
                                cmd.CommandText = $"SELECT 1 FROM pragma_table_info('{table}') WHERE name = '{column}' LIMIT 1;";
                            }

                            return cmd.ExecuteScalar() is not null;
                        }

                        var companyOwnershipExists = ColumnExists(conn, provider, "Companies", "OwnerUserId");
                        var jobOwnershipExists = ColumnExists(conn, provider, "JobApplications", "OwnerUserId");

                        if (companyOwnershipExists || jobOwnershipExists)
                        {
                            if (companyOwnershipExists)
                            {
                                adminDb.Database.ExecuteSqlRaw("UPDATE Companies SET OwnerUserId = {0} WHERE OwnerUserId IS NULL;", admin.Id);
                            }

                            if (jobOwnershipExists)
                            {
                                adminDb.Database.ExecuteSqlRaw("UPDATE JobApplications SET OwnerUserId = {0} WHERE OwnerUserId IS NULL;", admin.Id);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        app.Logger.LogWarning(ex, "Skipping legacy ownership claim because the current schema does not support it yet.");
                    }
                }
            }
        }

        bool CoreSchemaReady(DbConnection connection, string providerName)
        {
            using var cmd = connection.CreateCommand();
            if (providerName is "mysql" or "mariadb")
            {
                cmd.CommandText = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN ('JobApplications', 'RuleSettings');";
                return Convert.ToInt32(cmd.ExecuteScalar() ?? 0) == 2;
            }

            cmd.CommandText = "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name IN ('JobApplications', 'RuleSettings');";
            return Convert.ToInt32(cmd.ExecuteScalar() ?? 0) == 2;
        }

        using (var scope = app.Services.CreateScope())
        {
            var db = scope.ServiceProvider.GetRequiredService<JobTrackerContext>();
            var runtimeProvider = (app.Configuration["Database:Provider"] ?? "sqlite").Trim().ToLowerInvariant();
            using var conn = db.Database.GetDbConnection();
            conn.Open();
            if (!CoreSchemaReady(conn, runtimeProvider))
            {
                app.Logger.LogWarning("Core schema is incomplete after startup initialization. Background services will remain paused until required tables exist.");
                return Task.CompletedTask;
            }

            // On a brand-new database, the ad-hoc-column reconciliation above ran before
            // Migrate() created JobApplications/Correspondences, so every EnsureColumn call
            // no-opped. Now that CoreSchemaReady confirms the tables exist (created either just
            // now by Migrate(), or already, on a prior boot), re-run it -- idempotent, so this is
            // free on every boot except the very first one, where it's required.
            if (runtimeProvider is "mysql" or "mariadb")
            {
                ReconcileCoreAppColumnsMySql(conn);
            }
            else
            {
                ReconcileCoreAppColumns(conn);
            }
        }

        var readiness = app.Services.GetRequiredService<IStartupReadiness>();
        readiness.MarkReady();

        return Task.CompletedTask;
    }
}