cesnimda
257 lines
15 KiB
C#
257 lines
15 KiB
C#
using System.Globalization;
|
|
using JobTrackerApi.Data;
|
|
using Microsoft.EntityFrameworkCore;
|
|
|
|
namespace JobTrackerApi.Services;
|
|
|
|
public sealed record DocumentsPageDto(IReadOnlyList<DocumentIndexItemDto> Items, int Page, int PageSize, int Total, int TotalPages);
|
|
|
|
public sealed record DocumentIndexItemDto(
|
|
string Id,
|
|
string ResourceId,
|
|
string Type,
|
|
string Name,
|
|
string State,
|
|
string? Visibility,
|
|
int? JobApplicationId,
|
|
string? JobTitle,
|
|
string? CompanyName,
|
|
bool? JobIsDeleted,
|
|
string? RecordedAt,
|
|
string DateKind,
|
|
long? Version,
|
|
int? HistoryCount,
|
|
int? AttachmentCount,
|
|
int? CvVariantId,
|
|
string? CvVariantName,
|
|
int? CvVariantVersion,
|
|
string? FileType,
|
|
long? FileSize,
|
|
string? Purpose,
|
|
bool? UseForAi,
|
|
string? Provider,
|
|
string SourceHref,
|
|
string? DownloadHref,
|
|
string? PublicHref,
|
|
bool? HasCurrentContent);
|
|
|
|
public sealed class DocumentsIndexService(JobTrackerContext db)
|
|
{
|
|
public async Task<DocumentsPageDto> GetAsync(string owner, int page, int pageSize, string? q,
|
|
string type, string state, string visibility, int? jobApplicationId, string sort, CancellationToken ct)
|
|
{
|
|
var pattern = (q ?? "").Trim().ToLowerInvariant().Replace("!", "!!").Replace("%", "!%").Replace("_", "!_");
|
|
object[] parameters = [owner, $"%{pattern}%", type, state, visibility, jobApplicationId ?? 0, pageSize, (page - 1) * pageSize];
|
|
var sqlite = db.Database.IsSqlite();
|
|
var count = await db.Database.SqlQueryRaw<int>(BuildSql(sqlite, true, sort), parameters).ToListAsync(ct);
|
|
var rows = await db.Database.SqlQueryRaw<DocumentRow>(BuildSql(sqlite, false, sort), parameters).ToListAsync(ct);
|
|
var total = count.Single();
|
|
return new DocumentsPageDto(rows.Select(ToDto).ToArray(), page, pageSize, total, (int)Math.Ceiling(total / (double)pageSize));
|
|
}
|
|
|
|
private static string BuildSql(bool sqlite, bool count, string sort)
|
|
{
|
|
const string history = "FROM `CoverLetterVersions` h WHERE h.OwnerUserId = {0} AND h.JobApplicationId = j.Id";
|
|
var letterDate = $"(SELECT CAST(h.CreatedAtUtc AS CHAR) {history} ORDER BY h.Version DESC, h.Id DESC LIMIT 1)";
|
|
var letterCount = $"(SELECT COUNT(*) {history})";
|
|
var letterVersion = $"(SELECT MAX(h.Version) {history})";
|
|
var letterContent = HasContent("j.CoverLetterText");
|
|
var answers = HasAnswers(sqlite);
|
|
var recruiter = HasContent("j.RecruiterMessageDraft");
|
|
var tailored = HasContent("j.TailoredCvText");
|
|
var sources = $$"""
|
|
SELECT 'cv' AS Type, CAST(v.Id AS CHAR) AS ResourceId, v.Name AS Name, 'current' AS State,
|
|
CASE WHEN v.IsPublic = 1 THEN 'public' ELSE 'private' END AS Visibility,
|
|
j.Id AS JobApplicationId, j.JobTitle AS JobTitle, c.Name AS CompanyName, j.IsDeleted AS JobIsDeleted,
|
|
CAST(v.UpdatedAtUtc AS CHAR) AS RawRecordedAt, 'updated-utc' AS DateKind,
|
|
v.Version AS Version, NULL AS HistoryCount, NULL AS AttachmentCount,
|
|
NULL AS CvVariantId, NULL AS CvVariantName, NULL AS CvVariantVersion,
|
|
NULL AS FileType, NULL AS FileSize, NULL AS Purpose, NULL AS UseForAi, NULL AS Provider,
|
|
v.PublicSlug AS PublicSlug, NULL AS HasCurrentContent
|
|
FROM `CvVariants` v
|
|
LEFT JOIN `JobApplications` j ON j.Id = v.JobApplicationId AND j.OwnerUserId = {0}
|
|
LEFT JOIN `Companies` c ON c.Id = j.CompanyId AND c.OwnerUserId = {0}
|
|
WHERE v.OwnerUserId = {0}
|
|
UNION ALL
|
|
SELECT 'cover-letter', CAST(j.Id AS CHAR), j.JobTitle, 'draft', NULL,
|
|
j.Id, j.JobTitle, c.Name, j.IsDeleted, {{letterDate}},
|
|
CASE WHEN {{letterDate}} IS NULL THEN 'unknown' ELSE 'history-recorded-utc' END,
|
|
{{letterVersion}}, {{letterCount}}, NULL, NULL, NULL, NULL,
|
|
NULL, NULL, NULL, NULL, NULL, NULL, CASE WHEN {{letterContent}} THEN 1 ELSE 0 END
|
|
FROM `JobApplications` j
|
|
LEFT JOIN `Companies` c ON c.Id = j.CompanyId AND c.OwnerUserId = {0}
|
|
WHERE j.OwnerUserId = {0} AND ({{letterContent}} OR {{letterCount}} > 0)
|
|
UNION ALL
|
|
SELECT 'application-material', CAST(j.Id AS CHAR), j.JobTitle, 'draft', NULL,
|
|
j.Id, j.JobTitle, c.Name, j.IsDeleted, NULL, 'unknown',
|
|
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
|
|
FROM `JobApplications` j
|
|
LEFT JOIN `Companies` c ON c.Id = j.CompanyId AND c.OwnerUserId = {0}
|
|
WHERE j.OwnerUserId = {0} AND ({{answers}} OR {{recruiter}})
|
|
UNION ALL
|
|
SELECT 'tailored-cv', CAST(j.Id AS CHAR), j.JobTitle, 'draft', NULL,
|
|
j.Id, j.JobTitle, c.Name, j.IsDeleted,
|
|
CAST(COALESCE(d.LastEditedAtUtc, d.LastGeneratedAtUtc, j.TailoredCvUpdatedAt) AS CHAR),
|
|
CASE WHEN COALESCE(d.LastEditedAtUtc, d.LastGeneratedAtUtc, j.TailoredCvUpdatedAt) IS NULL THEN 'unknown' ELSE 'updated-utc' END,
|
|
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
|
|
FROM `JobApplications` j
|
|
LEFT JOIN `Companies` c ON c.Id = j.CompanyId AND c.OwnerUserId = {0}
|
|
LEFT JOIN `TailoredCvDrafts` d ON d.JobApplicationId = j.Id AND d.OwnerUserId = {0}
|
|
WHERE j.OwnerUserId = {0} AND (d.Id IS NOT NULL OR {{tailored}})
|
|
UNION ALL
|
|
SELECT 'attachment', CAST(a.Id AS CHAR), a.FileName, 'current', NULL,
|
|
j.Id, j.JobTitle, c.Name, j.IsDeleted, CAST(a.UploadDate AS CHAR), 'uploaded-local',
|
|
NULL, NULL, NULL, NULL, NULL, NULL, a.FileType, a.FileSize, a.Purpose, a.UseForAi, NULL, NULL, NULL
|
|
FROM `Attachments` a
|
|
JOIN `JobApplications` j ON j.Id = a.JobApplicationId AND j.OwnerUserId = {0}
|
|
LEFT JOIN `Companies` c ON c.Id = j.CompanyId AND c.OwnerUserId = {0}
|
|
UNION ALL
|
|
SELECT 'submitted-package', CAST(p.Id AS CHAR), COALESCE(p.CvVariantName, j.JobTitle), 'frozen', NULL,
|
|
j.Id, j.JobTitle, c.Name, j.IsDeleted, CAST(p.CreatedAtUtc AS CHAR), 'captured-utc',
|
|
p.Version, NULL, (SELECT COUNT(*) FROM `SubmittedPackageAttachments` a WHERE a.SubmittedApplicationPackageId = p.Id),
|
|
p.CvVariantId, p.CvVariantName, p.CvVariantVersion, NULL, NULL, NULL, NULL, NULL, NULL, NULL
|
|
FROM `SubmittedApplicationPackages` p
|
|
JOIN `JobApplications` j ON j.Id = p.JobApplicationId AND j.OwnerUserId = {0}
|
|
LEFT JOIN `Companies` c ON c.Id = j.CompanyId AND c.OwnerUserId = {0}
|
|
WHERE p.OwnerUserId = {0}
|
|
UNION ALL
|
|
SELECT 'email-draft', CAST(d.Id AS CHAR), d.Subject, 'draft', NULL,
|
|
j.Id, j.JobTitle, c.Name, j.IsDeleted, CAST(d.UpdatedAtUtc AS CHAR), 'updated-utc',
|
|
d.Revision, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, d.Provider, NULL, NULL
|
|
FROM `EmailDrafts` d
|
|
JOIN `JobApplications` j ON j.Id = d.JobApplicationId AND j.OwnerUserId = {0}
|
|
LEFT JOIN `Companies` c ON c.Id = j.CompanyId AND c.OwnerUserId = {0}
|
|
WHERE d.OwnerUserId = {0}
|
|
""";
|
|
var filtered = $$"""
|
|
FROM ({{sources}}) documents
|
|
WHERE ({2} = 'all' OR Type = {2})
|
|
AND ({3} = 'all' OR State = {3})
|
|
AND ({4} = 'all' OR (Type = 'cv' AND Visibility = {4}))
|
|
AND ({5} = 0 OR JobApplicationId = {5})
|
|
AND ({{Fold("Name")}} LIKE {1} ESCAPE '!'
|
|
OR {{Fold("JobTitle")}} LIKE {1} ESCAPE '!'
|
|
OR {{Fold("CompanyName")}} LIKE {1} ESCAPE '!'
|
|
OR {{Fold("CvVariantName")}} LIKE {1} ESCAPE '!')
|
|
""";
|
|
if (count) return $"SELECT COUNT(*) AS Value {filtered}";
|
|
var order = sort switch
|
|
{
|
|
"date-desc" => "(DateSortTicks IS NULL), DateSortTicks DESC",
|
|
"date-asc" => "(DateSortTicks IS NULL), DateSortTicks ASC",
|
|
"name-asc" => $"{Fold("Name")} ASC",
|
|
"name-desc" => $"{Fold("Name")} DESC",
|
|
_ => throw new ArgumentOutOfRangeException(nameof(sort))
|
|
};
|
|
return $$"""
|
|
SELECT documents.*, {{DateTicks(sqlite)}} AS DateSortTicks {{filtered}}
|
|
ORDER BY {{order}}, Type ASC, ResourceId ASC
|
|
LIMIT {6} OFFSET {7}
|
|
""";
|
|
}
|
|
|
|
private static string Fold(string value) => $"REPLACE(REPLACE(REPLACE(LOWER(COALESCE({value}, '')), 'Æ', 'æ'), 'Ø', 'ø'), 'Å', 'å')";
|
|
|
|
private static string HasContent(string value)
|
|
{
|
|
// SQL TRIM does not implement .NET's Unicode whitespace semantics.
|
|
var expression = $"COALESCE({value}, '')";
|
|
foreach (var character in "\t\n\v\f\r \u0085\u00a0\u1680\u2000\u2001\u2002\u2003\u2004\u2005\u2006\u2007\u2008\u2009\u200a\u2028\u2029\u202f\u205f\u3000")
|
|
{
|
|
// EF formats SQL line endings; CHAR preserves the exact control whitespace being removed.
|
|
var needle = character is >= '\t' and <= '\r' ? $"CHAR({(int)character})" : $"'{character}'";
|
|
expression = $"REPLACE({expression}, {needle}, '')";
|
|
}
|
|
return $"LENGTH({expression}) > 0";
|
|
}
|
|
|
|
private static string HasAnswers(bool sqlite)
|
|
{
|
|
string Position(string needle, string value) => sqlite ? $"INSTR({value}, '{needle}')"
|
|
: $"LOCATE(CONVERT('{needle}' USING utf8mb4) COLLATE utf8mb4_bin, {value})";
|
|
const string startMarker = "<<<APPLICATION_ANSWER_DRAFT>>>";
|
|
const string endMarker = "<<<END_APPLICATION_ANSWER_DRAFT>>>";
|
|
const string legacyPrefix = "application answer draft:";
|
|
// Keep character offsets aligned while matching modern markers with ordinal case sensitivity.
|
|
var notes = sqlite ? "COALESCE(j.Notes, '')" : "CONVERT(COALESCE(j.Notes, '') USING utf8mb4) COLLATE utf8mb4_bin";
|
|
var start = Position(startMarker, notes);
|
|
var end = Position(endMarker, notes);
|
|
var legacy = Position(legacyPrefix, $"LOWER({notes})" + (sqlite ? "" : " COLLATE utf8mb4_bin"));
|
|
return $"CASE WHEN {start} > 0 AND {end} > {start} THEN {HasContent($"SUBSTR({notes}, {start} + {startMarker.Length}, {end} - {start} - {startMarker.Length})")} "
|
|
+ $"WHEN {legacy} > 0 THEN {HasContent($"SUBSTR({notes}, {legacy} + {legacyPrefix.Length})")} ELSE 0 END";
|
|
}
|
|
|
|
private static string DateTicks(bool sqlite)
|
|
{
|
|
string ticks;
|
|
if (sqlite)
|
|
{
|
|
// Split whole seconds from seven fractional digits; SQLite's fractional date arithmetic rounds.
|
|
const string offsetLength = "CASE WHEN SUBSTR(RawRecordedAt, -6, 1) IN ('+', '-') THEN 6 WHEN SUBSTR(RawRecordedAt, -1) = 'Z' THEN 1 ELSE 0 END";
|
|
const string offset = "CASE WHEN SUBSTR(RawRecordedAt, -6, 1) IN ('+', '-') THEN SUBSTR(RawRecordedAt, -6) ELSE '+00:00' END";
|
|
var fraction = $"CASE WHEN SUBSTR(RawRecordedAt, 20, 1) = '.' THEN SUBSTR(RawRecordedAt, 21, LENGTH(RawRecordedAt) - 20 - ({offsetLength})) ELSE '0' END";
|
|
ticks = $"CAST(STRFTIME('%s', SUBSTR(RawRecordedAt, 1, 19) || ({offset})) AS INTEGER) * 10000000 "
|
|
+ $"+ CAST(SUBSTR(({fraction}) || '0000000', 1, 7) AS INTEGER)";
|
|
}
|
|
else ticks = "TIMESTAMPDIFF(MICROSECOND, '1970-01-01 00:00:00', RawRecordedAt) * 10";
|
|
return $"CASE WHEN DateKind IN ('unknown', 'uploaded-local') OR RawRecordedAt IS NULL THEN NULL ELSE {ticks} END";
|
|
}
|
|
|
|
private static DocumentIndexItemDto ToDto(DocumentRow row)
|
|
{
|
|
var resourceId = row.Type == "email-draft" ? Guid.Parse(row.ResourceId).ToString("D") : row.ResourceId;
|
|
var source = row.Type switch
|
|
{
|
|
"cv" => $"/career/builder/{resourceId}",
|
|
"attachment" => $"/jobs/{row.JobApplicationId}?section=documents",
|
|
"cover-letter" => $"/jobs/{row.JobApplicationId}?section=application&sub=letter",
|
|
"application-material" => $"/jobs/{row.JobApplicationId}?section=application&sub=answers",
|
|
"tailored-cv" => $"/jobs/{row.JobApplicationId}?section=application&sub=cv",
|
|
"submitted-package" => $"/jobs/{row.JobApplicationId}?section=application&sub=submission",
|
|
"email-draft" => "/correspondence",
|
|
_ => throw new InvalidOperationException("Unsupported document source.")
|
|
};
|
|
var recordedAt = row.DateSortTicks.HasValue
|
|
? DateTimeOffset.UnixEpoch.AddTicks(row.DateSortTicks.Value).UtcDateTime.ToString("O", CultureInfo.InvariantCulture)
|
|
: row.DateKind == "uploaded-local" && row.RawRecordedAt is not null
|
|
? DateTime.Parse(row.RawRecordedAt, CultureInfo.InvariantCulture).ToString("yyyy-MM-dd'T'HH:mm:ss.fffffff", CultureInfo.InvariantCulture)
|
|
: null;
|
|
return new DocumentIndexItemDto($"{row.Type}:{resourceId}", resourceId, row.Type, row.Name, row.State, row.Visibility,
|
|
row.JobApplicationId, row.JobTitle, row.CompanyName, row.JobIsDeleted, recordedAt, row.DateKind, row.Version,
|
|
row.HistoryCount, row.AttachmentCount, row.CvVariantId, row.CvVariantName, row.CvVariantVersion,
|
|
row.FileType, row.FileSize, row.Purpose, row.UseForAi, row.Provider, source,
|
|
row.Type == "attachment" ? $"/api/attachments/download/{resourceId}" : null,
|
|
row.Type == "cv" && row.Visibility == "public" && !string.IsNullOrWhiteSpace(row.PublicSlug) ? $"/cv/{Uri.EscapeDataString(row.PublicSlug)}" : null,
|
|
row.HasCurrentContent);
|
|
}
|
|
|
|
private sealed class DocumentRow
|
|
{
|
|
public string Type { get; set; } = "";
|
|
public string ResourceId { get; set; } = "";
|
|
public string Name { get; set; } = "";
|
|
public string State { get; set; } = "";
|
|
public string? Visibility { get; set; }
|
|
public int? JobApplicationId { get; set; }
|
|
public string? JobTitle { get; set; }
|
|
public string? CompanyName { get; set; }
|
|
public bool? JobIsDeleted { get; set; }
|
|
public string? RawRecordedAt { get; set; }
|
|
public string DateKind { get; set; } = "";
|
|
public long? Version { get; set; }
|
|
public int? HistoryCount { get; set; }
|
|
public int? AttachmentCount { get; set; }
|
|
public int? CvVariantId { get; set; }
|
|
public string? CvVariantName { get; set; }
|
|
public int? CvVariantVersion { get; set; }
|
|
public string? FileType { get; set; }
|
|
public long? FileSize { get; set; }
|
|
public string? Purpose { get; set; }
|
|
public bool? UseForAi { get; set; }
|
|
public string? Provider { get; set; }
|
|
public string? PublicSlug { get; set; }
|
|
public bool? HasCurrentContent { get; set; }
|
|
public long? DateSortTicks { get; set; }
|
|
}
|
|
}
|