1019 lines
60 KiB
C#
1019 lines
60 KiB
C#
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
|
||
{
|
||
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,
|
||
`TwoFactorEnabled` 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 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;
|
||
}
|
||
|
||
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;
|
||
}
|
||
|
||
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;
|
||
}
|
||
|
||
static void Exec(DbConnection c, string sql)
|
||
{
|
||
using var cmd = c.CreateCommand();
|
||
cmd.CommandText = sql;
|
||
cmd.ExecuteNonQuery();
|
||
}
|
||
|
||
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);
|
||
}
|
||
|
||
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 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);
|
||
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
|
||
// the ShortSummary column. Ensure it exists unconditionally if missing.
|
||
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;");
|
||
|
||
// Ensure ownership columns exist even on non-legacy DBs.
|
||
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, "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;");
|
||
|
||
// 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 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;
|
||
}
|
||
|
||
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 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;
|
||
}
|
||
|
||
static void EnsureMySqlColumn(DbConnection c, string table, string column, string ddl)
|
||
{
|
||
using var existsCmd = c.CreateCommand();
|
||
existsCmd.CommandText = "SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @schema AND TABLE_NAME = @table LIMIT 1;";
|
||
var ep1 = existsCmd.CreateParameter(); ep1.ParameterName = "@schema"; ep1.Value = c.Database; existsCmd.Parameters.Add(ep1);
|
||
var ep2 = existsCmd.CreateParameter(); ep2.ParameterName = "@table"; ep2.Value = table; existsCmd.Parameters.Add(ep2);
|
||
if (existsCmd.ExecuteScalar() is null) return;
|
||
|
||
if (MySqlColumnExists(c, table, column)) return;
|
||
using var ddlCmd = c.CreateCommand();
|
||
ddlCmd.CommandText = ddl;
|
||
ddlCmd.ExecuteNonQuery();
|
||
}
|
||
|
||
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, "CvUploadArtifacts", "Id");
|
||
EnsureMySqlAutoIncrementPrimaryKey(conn, "CvExtractionRuns", "Id");
|
||
EnsureMySqlAutoIncrementPrimaryKey(conn, "TailoredCvDrafts", "Id");
|
||
|
||
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", "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, "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;");
|
||
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, "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();
|
||
}
|
||
|
||
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, "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;
|
||
}
|
||
}
|
||
|
||
var readiness = app.Services.GetRequiredService<IStartupReadiness>();
|
||
readiness.MarkReady();
|
||
|
||
return Task.CompletedTask;
|
||
}
|
||
}
|