cesnimda
547 lines
38 KiB
C#
547 lines
38 KiB
C#
using System.Data.Common;
|
|
using System.Net;
|
|
using System.Reflection;
|
|
using System.Security.Claims;
|
|
using System.Security.Cryptography;
|
|
using System.Text;
|
|
using System.Text.Encodings.Web;
|
|
using System.Text.Json;
|
|
using System.Text.RegularExpressions;
|
|
using JobTrackerApi.Controllers;
|
|
using JobTrackerApi.Data;
|
|
using JobTrackerApi.Models;
|
|
using JobTrackerApi.Services;
|
|
using JobTrackerApi.Tests.TestSupport;
|
|
using Microsoft.AspNetCore.Authentication;
|
|
using Microsoft.AspNetCore.Hosting.Server;
|
|
using Microsoft.AspNetCore.Hosting.Server.Features;
|
|
using Microsoft.EntityFrameworkCore;
|
|
using Microsoft.EntityFrameworkCore.Diagnostics;
|
|
using Microsoft.Extensions.Options;
|
|
using Moq;
|
|
using Xunit;
|
|
|
|
namespace JobTrackerApi.Tests;
|
|
|
|
public sealed class DocumentsControllerTests
|
|
{
|
|
private const string OwnerA = "documents-owner-a";
|
|
private const string OwnerB = "documents-owner-b";
|
|
private const string PrivateContent = "NEVER_EXPOSE_DOCUMENT_CONTENT";
|
|
private static readonly string[] ItemFields =
|
|
[
|
|
"id", "resourceId", "type", "name", "state", "visibility", "jobApplicationId", "jobTitle",
|
|
"companyName", "jobIsDeleted", "recordedAt", "dateKind", "version", "historyCount",
|
|
"attachmentCount", "cvVariantId", "cvVariantName", "cvVariantVersion", "fileType", "fileSize",
|
|
"purpose", "useForAi", "provider", "sourceHref", "downloadHref", "publicHref", "hasCurrentContent",
|
|
];
|
|
|
|
[Fact]
|
|
public async Task Empty_index_has_bounded_defaults_and_requires_a_known_authenticated_owner()
|
|
{
|
|
await using var fixture = await Fixture.CreateAsync();
|
|
var page = await fixture.GetAsync();
|
|
Assert.Equal(new[] { "items", "page", "pageSize", "total", "totalPages" }.Order(),
|
|
page.EnumerateObject().Select(property => property.Name).Order());
|
|
Assert.Empty(Items(page));
|
|
Assert.Equal(1, page.GetProperty("page").GetInt32());
|
|
Assert.Equal(25, page.GetProperty("pageSize").GetInt32());
|
|
Assert.Equal(0, page.GetProperty("total").GetInt32());
|
|
Assert.Equal(0, page.GetProperty("totalPages").GetInt32());
|
|
Assert.Equal(HttpStatusCode.Unauthorized, await fixture.StatusAsync("", null));
|
|
Assert.Equal(HttpStatusCode.Unauthorized, await fixture.StatusAsync("", "unrecognised-owner"));
|
|
}
|
|
|
|
[Fact]
|
|
public async Task Seven_existing_resource_kinds_expose_only_metadata_and_existing_actions()
|
|
{
|
|
await using var fixture = await Fixture.CreateAsync(catalog: true);
|
|
var page = await fixture.GetAsync();
|
|
var items = Items(page);
|
|
Assert.Equal(8, page.GetProperty("total").GetInt32());
|
|
Assert.Equal(new[] { "application-material", "attachment", "cover-letter", "cv", "email-draft", "submitted-package", "tailored-cv" },
|
|
items.Select(item => Text(item, "type")).Distinct().Order());
|
|
foreach (var item in items)
|
|
{
|
|
Assert.Equal(ItemFields.Order(), item.EnumerateObject().Select(property => property.Name).Order());
|
|
Assert.Equal($"{Text(item, "type")}:{Text(item, "resourceId")}", Text(item, "id"));
|
|
Assert.StartsWith("/", Text(item, "sourceHref"));
|
|
}
|
|
var cv = items.Single(item => Text(item, "id") == "cv:10");
|
|
Assert.Equal("Public ÆØÅ CV", Text(cv, "name"));
|
|
Assert.Equal("current", Text(cv, "state"));
|
|
Assert.Equal("public", Text(cv, "visibility"));
|
|
Assert.Equal("/career/builder/10", Text(cv, "sourceHref"));
|
|
Assert.Equal("/cv/documents-public-a", Text(cv, "publicHref"));
|
|
Assert.Equal(2, cv.GetProperty("version").GetInt64());
|
|
Assert.Null(Text(cv, "downloadHref"));
|
|
var upload = items.Single(item => Text(item, "type") == "attachment");
|
|
Assert.Equal("/api/attachments/download/1", Text(upload, "downloadHref"));
|
|
Assert.Equal("portfolio", Text(upload, "purpose"));
|
|
Assert.Equal("application/pdf", Text(upload, "fileType"));
|
|
Assert.Equal(41, upload.GetProperty("fileSize").GetInt64());
|
|
Assert.False(upload.GetProperty("useForAi").GetBoolean());
|
|
Assert.Equal("uploaded-local", Text(upload, "dateKind"));
|
|
Assert.Equal("2026-01-02T03:04:05.1234567", Text(upload, "recordedAt"));
|
|
var email = items.Single(item => Text(item, "type") == "email-draft");
|
|
Assert.Equal("Saved subject ÆØÅ", Text(email, "name"));
|
|
Assert.Equal("gmail", Text(email, "provider"));
|
|
Assert.Equal("draft", Text(email, "state"));
|
|
Assert.Contains("/correspondence", Text(email, "sourceHref"));
|
|
var letter = items.Single(item => Text(item, "type") == "cover-letter");
|
|
Assert.Equal("draft", Text(letter, "state"));
|
|
Assert.True(letter.GetProperty("hasCurrentContent").GetBoolean());
|
|
Assert.Equal(2, letter.GetProperty("historyCount").GetInt32());
|
|
Assert.Equal(2, letter.GetProperty("version").GetInt64());
|
|
Assert.Equal("history-recorded-utc", Text(letter, "dateKind"));
|
|
Assert.DoesNotContain(PrivateContent, page.GetRawText());
|
|
Assert.DoesNotContain("private-recipient@example.test", page.GetRawText());
|
|
Assert.DoesNotContain("private-thread-a", page.GetRawText());
|
|
Assert.DoesNotContain("private-client-request-a", page.GetRawText());
|
|
Assert.DoesNotContain("private-storage", page.GetRawText());
|
|
}
|
|
|
|
[Fact]
|
|
public async Task Owner_scope_includes_owned_history_and_trash_without_leaking_cross_owner_references()
|
|
{
|
|
await using var fixture = await Fixture.CreateAsync(catalog: true);
|
|
await fixture.SeedAsync(db =>
|
|
{
|
|
db.JobApplications.Add(new JobApplication { Id = 3, OwnerUserId = OwnerA, CompanyId = 1, JobTitle = "Owned trash", IsDeleted = true, CoverLetterText = PrivateContent });
|
|
db.CvVariants.Add(Variant(12, OwnerA, "Malformed association", 2));
|
|
db.CoverLetterVersions.Add(new CoverLetterVersion { OwnerUserId = OwnerB, JobApplicationId = 1, Version = 99, Text = PrivateContent });
|
|
db.EmailDrafts.Add(Draft(Guid.Parse("33333333-3333-3333-3333-333333333333"), OwnerA, 2, "Malformed cross-owner draft"));
|
|
db.SubmittedApplicationPackages.Add(new SubmittedApplicationPackage { OwnerUserId = OwnerA, JobApplicationId = 2, Version = 99, CvVariantName = "Malformed cross-owner capture" });
|
|
db.JobApplications.AddRange(
|
|
new JobApplication { Id = 4, OwnerUserId = OwnerA, CompanyId = 2, JobTitle = "Owned role with damaged company reference", CoverLetterText = PrivateContent },
|
|
new JobApplication { Id = 5, OwnerUserId = OwnerA, CompanyId = 1, JobTitle = "Owned role with foreign structured draft" });
|
|
db.TailoredCvDrafts.Add(new TailoredCvDraft { OwnerUserId = OwnerB, JobApplicationId = 5, SummaryJson = $"[\"{PrivateContent}\"]", LastEditedAtUtc = DateTimeOffset.Parse("2026-01-01T00:00:00Z") });
|
|
});
|
|
var own = Items(await fixture.GetAsync("?pageSize=100"));
|
|
Assert.Contains(own, item => Text(item, "name") == "Owned trash" && item.GetProperty("jobIsDeleted").GetBoolean());
|
|
Assert.DoesNotContain(own, item => Text(item, "name") == "Foreign CV" || Text(item, "companyName") == "Foreign company");
|
|
Assert.DoesNotContain(own, item => Text(item, "name") is "Malformed cross-owner draft" or "Malformed cross-owner capture");
|
|
var malformedCv = own.Single(item => Text(item, "id") == "cv:12");
|
|
Assert.Equal(JsonValueKind.Null, malformedCv.GetProperty("jobApplicationId").ValueKind);
|
|
Assert.Null(Text(malformedCv, "jobTitle"));
|
|
Assert.Null(Text(malformedCv, "companyName"));
|
|
var damagedCompany = own.Single(item => Text(item, "id") == "cover-letter:4");
|
|
Assert.Equal(4, damagedCompany.GetProperty("jobApplicationId").GetInt32());
|
|
Assert.Null(Text(damagedCompany, "companyName"));
|
|
Assert.DoesNotContain(own, item => Text(item, "id") == "tailored-cv:5");
|
|
Assert.Equal(2, own.Single(item => Text(item, "id") == "cover-letter:1").GetProperty("historyCount").GetInt32());
|
|
Assert.Equal(own.Select(item => Text(item, "id")), Items(await fixture.GetAsync($"?pageSize=100&ownerUserId={OwnerB}")).Select(item => Text(item, "id")));
|
|
Assert.Empty(Items(await fixture.GetAsync("?jobApplicationId=2")));
|
|
Assert.Empty(Items(await fixture.GetAsync("?jobApplicationId=99999")));
|
|
var other = Items(await fixture.GetAsync("?pageSize=100", OwnerB));
|
|
Assert.Equal(7, other.Length);
|
|
Assert.All(other, item => Assert.Equal(2, item.GetProperty("jobApplicationId").GetInt32()));
|
|
Assert.DoesNotContain(PrivateContent, JsonSerializer.Serialize(other));
|
|
}
|
|
|
|
[Fact]
|
|
public async Task Pagination_counts_the_filtered_result_and_uses_stable_ordinal_resource_ties()
|
|
{
|
|
await using var fixture = await Fixture.CreateAsync();
|
|
await fixture.SeedAsync(db => db.CvVariants.AddRange(Enumerable.Range(1, 70).Select(id => Variant(id, OwnerA, $"CV {id:D3}"))));
|
|
var first = await fixture.GetAsync("?type=cv&sort=date-desc");
|
|
var second = await fixture.GetAsync("?type=cv&sort=date-desc&page=2");
|
|
Assert.Equal(70, first.GetProperty("total").GetInt32());
|
|
Assert.Equal(3, first.GetProperty("totalPages").GetInt32());
|
|
Assert.Equal(25, Items(first).Length);
|
|
Assert.Equal(25, Items(second).Length);
|
|
var orderedIds = Enumerable.Range(1, 70).Select(id => id.ToString()).Order(StringComparer.Ordinal).ToArray();
|
|
Assert.Equal(orderedIds.Take(25), Items(first).Select(item => Text(item, "resourceId")));
|
|
Assert.Equal(orderedIds.Skip(25).Take(25), Items(second).Select(item => Text(item, "resourceId")));
|
|
var last = await fixture.GetAsync("?type=cv&sort=name-desc&page=3");
|
|
Assert.Equal(Enumerable.Range(1, 20).Reverse().Select(id => $"CV {id:D3}"), Items(last).Select(item => Text(item, "name")));
|
|
var all = await fixture.GetAsync("?type=cv&pageSize=100");
|
|
Assert.Equal(70, Items(all).Length);
|
|
Assert.Empty(Items(await fixture.GetAsync("?type=cv&page=10000")));
|
|
}
|
|
|
|
[Fact]
|
|
public async Task Type_state_visibility_and_owned_job_filters_intersect_without_changing_totals()
|
|
{
|
|
await using var fixture = await Fixture.CreateAsync(catalog: true);
|
|
Assert.Equal(4, (await fixture.GetAsync("?state=draft")).GetProperty("total").GetInt32());
|
|
Assert.Equal(3, (await fixture.GetAsync("?state=current")).GetProperty("total").GetInt32());
|
|
Assert.Equal(1, (await fixture.GetAsync("?state=frozen")).GetProperty("total").GetInt32());
|
|
var publicOnly = Items(await fixture.GetAsync("?visibility=public"));
|
|
Assert.Equal("cv:10", Text(Assert.Single(publicOnly), "id"));
|
|
Assert.Equal("cv:11", Text(Assert.Single(Items(await fixture.GetAsync("?visibility=private"))), "id"));
|
|
Assert.Empty(Items(await fixture.GetAsync("?type=attachment&visibility=public")));
|
|
Assert.Equal(7, (await fixture.GetAsync("?jobApplicationId=1")).GetProperty("total").GetInt32());
|
|
var scopedCv = await fixture.GetAsync("?type=cv&state=current&jobApplicationId=1&pageSize=1");
|
|
Assert.Equal(1, scopedCv.GetProperty("total").GetInt32());
|
|
Assert.Equal("cv:10", Text(Assert.Single(Items(scopedCv)), "id"));
|
|
}
|
|
|
|
[Fact]
|
|
public async Task Search_treats_wildcards_literally_folds_Nordic_metadata_and_never_searches_content()
|
|
{
|
|
await using var fixture = await Fixture.CreateAsync(catalog: true);
|
|
await fixture.SeedAsync(db => db.CvVariants.AddRange(
|
|
Variant(31, OwnerA, "Literal 100%"), Variant(32, OwnerA, "Literal under_score"),
|
|
Variant(33, OwnerA, "Literal bang!"), Variant(34, OwnerA, "Literal decoy")));
|
|
foreach (var (query, expected) in new[] { ("%", "cv:31"), ("_", "cv:32"), ("!", "cv:33") })
|
|
Assert.Equal(expected, Text(Assert.Single(Items(await fixture.GetAsync($"?q={Uri.EscapeDataString(query)}"))), "id"));
|
|
Assert.Equal(new[] { "cv:10", "email-draft:11111111-1111-1111-1111-111111111111", "submitted-package:1" },
|
|
Items(await fixture.GetAsync($"?q={Uri.EscapeDataString("æøå")}&sort=name-asc")).Select(item => Text(item, "id")).Order());
|
|
Assert.Equal(7, (await fixture.GetAsync("?q=nordlys")).GetProperty("total").GetInt32());
|
|
foreach (var query in new[] { PrivateContent, "private-recipient", "private-client-request", "private-thread", "private-storage" })
|
|
Assert.Empty(Items(await fixture.GetAsync($"?q={Uri.EscapeDataString(query)}")));
|
|
}
|
|
|
|
[Fact]
|
|
public async Task Date_order_uses_exact_instants_and_fractional_ticks_with_unzoned_and_unknown_dates_last()
|
|
{
|
|
await using var fixture = await Fixture.CreateAsync();
|
|
var instant = DateTimeOffset.Parse("2026-02-03T04:05:06.1234567+00:00");
|
|
await fixture.SeedAsync(db =>
|
|
{
|
|
var first = Variant(1, OwnerA, "Exact instant");
|
|
first.UpdatedAtUtc = instant;
|
|
var tied = Variant(10, OwnerA, "Offset tie");
|
|
tied.UpdatedAtUtc = instant.ToOffset(TimeSpan.FromHours(3));
|
|
var later = Variant(2, OwnerA, "One tick later");
|
|
later.UpdatedAtUtc = instant.AddTicks(1).ToOffset(TimeSpan.FromHours(-4));
|
|
db.CvVariants.AddRange(first, tied, later);
|
|
db.Attachments.Add(new Attachment { Id = 1, JobApplicationId = 1, FileName = "Future local upload", UploadDate = new DateTime(2099, 1, 1), FilePath = "private-storage", FileType = "text/plain" });
|
|
db.JobApplications.Single(job => job.Id == 1).RecruiterMessageDraft = PrivateContent;
|
|
});
|
|
var asc = Items(await fixture.GetAsync("?sort=date-asc"));
|
|
var desc = Items(await fixture.GetAsync("?sort=date-desc"));
|
|
Assert.Equal(new[] { "cv:1", "cv:10", "cv:2", "application-material:1", "attachment:1" }, asc.Select(item => Text(item, "id")));
|
|
Assert.Equal(new[] { "cv:2", "cv:1", "cv:10", "application-material:1", "attachment:1" }, desc.Select(item => Text(item, "id")));
|
|
Assert.Equal(instant.ToString("yyyy-MM-dd'T'HH:mm:ss.fffffff'Z'"), Text(asc[0], "recordedAt"));
|
|
Assert.Equal(instant.ToString("yyyy-MM-dd'T'HH:mm:ss.fffffff'Z'"), Text(asc[1], "recordedAt"));
|
|
Assert.Equal(JsonValueKind.Null, asc[3].GetProperty("recordedAt").ValueKind);
|
|
Assert.Equal("unknown", Text(asc[3], "dateKind"));
|
|
Assert.Equal("uploaded-local", Text(asc[4], "dateKind"));
|
|
}
|
|
|
|
[Fact]
|
|
public async Task Invalid_parameters_are_rejected_before_any_catalog_query()
|
|
{
|
|
await using var fixture = await Fixture.CreateAsync(catalog: true);
|
|
foreach (var query in new[]
|
|
{
|
|
"?page=0", "?page=-1", "?page=10001", "?page=oops", "?pageSize=0", "?pageSize=101", "?pageSize=oops",
|
|
"?type=unknown", "?state=sent", "?visibility=shared", "?sort=unknown", "?jobApplicationId=0",
|
|
"?jobApplicationId=-1", "?jobApplicationId=oops", $"?q={new string('a', 201)}",
|
|
"?sort=date-desc%3BDROP%20TABLE%20CvVariants",
|
|
})
|
|
{
|
|
fixture.Commands.Reset();
|
|
Assert.Equal(HttpStatusCode.BadRequest, await fixture.StatusAsync(query));
|
|
Assert.Empty(fixture.Commands.Snapshot());
|
|
}
|
|
}
|
|
|
|
[Fact]
|
|
public async Task Cleared_letter_history_answer_markers_and_structured_tailoring_keep_their_real_sources()
|
|
{
|
|
var whitespace = new string(Enumerable.Range(char.MinValue, char.MaxValue + 1)
|
|
.Select(value => (char)value).Where(char.IsWhiteSpace).ToArray());
|
|
var emptyAnswer = $"<<<APPLICATION_ANSWER_DRAFT>>>{whitespace}<<<END_APPLICATION_ANSWER_DRAFT>>>";
|
|
Assert.True(string.IsNullOrWhiteSpace(whitespace));
|
|
Assert.Null(JobApplicationHelpers.ExtractSavedApplicationAnswerDraft(emptyAnswer));
|
|
await using var fixture = await Fixture.CreateAsync();
|
|
await fixture.SeedAsync(db =>
|
|
{
|
|
var job = db.JobApplications.Single(job => job.Id == 1);
|
|
job.Notes = $"Ordinary notes {PrivateContent}";
|
|
db.CoverLetterVersions.Add(new CoverLetterVersion { OwnerUserId = OwnerA, JobApplicationId = 1, Version = 4, Text = PrivateContent, CreatedAtUtc = DateTimeOffset.Parse("2026-02-03T04:00:00+02:00") });
|
|
db.TailoredCvDrafts.Add(new TailoredCvDraft { OwnerUserId = OwnerA, JobApplicationId = 1, SummaryJson = $"[\"{PrivateContent}\"]", LastEditedAtUtc = DateTimeOffset.Parse("2026-02-04T01:00:00Z") });
|
|
db.JobApplications.AddRange(
|
|
new JobApplication { Id = 3, OwnerUserId = OwnerA, CompanyId = 1, JobTitle = "Legacy answers", Notes = $"Human notes\naPpLiCaTiOn AnSwEr DrAfT: {PrivateContent}" },
|
|
new JobApplication { Id = 4, OwnerUserId = OwnerA, CompanyId = 1, JobTitle = "Modern answers", Notes = $"<<<APPLICATION_ANSWER_DRAFT>>>\n{PrivateContent}\n<<<END_APPLICATION_ANSWER_DRAFT>>>" },
|
|
new JobApplication { Id = 5, OwnerUserId = OwnerA, CompanyId = 1, JobTitle = "Empty marker", Notes = "<<<APPLICATION_ANSWER_DRAFT>>>\n \n<<<END_APPLICATION_ANSWER_DRAFT>>>" },
|
|
new JobApplication { Id = 6, OwnerUserId = OwnerA, CompanyId = 1, JobTitle = "Unclosed marker", Notes = "<<<APPLICATION_ANSWER_DRAFT>>>not an answer" },
|
|
new JobApplication { Id = 7, OwnerUserId = OwnerA, CompanyId = 1, JobTitle = "Whitespace drafts", CoverLetterText = whitespace, RecruiterMessageDraft = whitespace, Notes = emptyAnswer },
|
|
new JobApplication { Id = 8, OwnerUserId = OwnerA, CompanyId = 1, JobTitle = "Empty modern marker takes precedence", Notes = $"<<<APPLICATION_ANSWER_DRAFT>>> \t <<<END_APPLICATION_ANSWER_DRAFT>>> Application answer draft: {PrivateContent}" },
|
|
new JobApplication { Id = 9, OwnerUserId = OwnerA, CompanyId = 1, JobTitle = "Strict modern marker casing", Notes = $"<<<application_answer_draft>>>{PrivateContent}<<<end_application_answer_draft>>>" });
|
|
});
|
|
var page = await fixture.GetAsync();
|
|
var items = Items(page);
|
|
Assert.Equal(new[] { "application-material:3", "application-material:4", "cover-letter:1", "tailored-cv:1" },
|
|
items.Select(item => Text(item, "id")).Order());
|
|
Assert.Equal(4, items.Length);
|
|
var letter = items.Single(item => Text(item, "type") == "cover-letter");
|
|
Assert.False(letter.GetProperty("hasCurrentContent").GetBoolean());
|
|
Assert.Equal(1, letter.GetProperty("historyCount").GetInt32());
|
|
Assert.Equal(4, letter.GetProperty("version").GetInt64());
|
|
Assert.Equal("2026-02-03T02:00:00.0000000Z", Text(letter, "recordedAt"));
|
|
Assert.Equal(new[] { "application-material:3", "application-material:4" },
|
|
items.Where(item => Text(item, "type") == "application-material").Select(item => Text(item, "id")).Order());
|
|
Assert.Equal("tailored-cv:1", Text(items.Single(item => Text(item, "type") == "tailored-cv"), "id"));
|
|
Assert.DoesNotContain(PrivateContent, page.GetRawText());
|
|
}
|
|
|
|
[Fact]
|
|
public async Task Frozen_metadata_and_archived_bytes_survive_live_cv_upload_and_letter_changes()
|
|
{
|
|
await using var fixture = await Fixture.CreateAsync(catalog: true);
|
|
var before = Assert.Single(Items(await fixture.GetAsync("?type=submitted-package")));
|
|
Assert.Equal("Frozen CV ÆØÅ", Text(before, "cvVariantName"));
|
|
Assert.Equal(10, before.GetProperty("cvVariantId").GetInt32());
|
|
Assert.Equal(1, before.GetProperty("cvVariantVersion").GetInt32());
|
|
Assert.Equal(1, before.GetProperty("attachmentCount").GetInt32());
|
|
Assert.Equal("frozen", Text(before, "state"));
|
|
var archivedBytes = await File.ReadAllBytesAsync(fixture.ArchivedFile);
|
|
await fixture.SeedAsync(db =>
|
|
{
|
|
db.JobApplications.Single(job => job.Id == 1).CoverLetterText = "Changed live letter";
|
|
db.CvVariants.Remove(db.CvVariants.Single(cv => cv.Id == 10));
|
|
db.Attachments.Remove(db.Attachments.Single(attachment => attachment.Id == 1));
|
|
});
|
|
File.Delete(fixture.LiveFile);
|
|
var after = Assert.Single(Items(await fixture.GetAsync("?type=submitted-package")));
|
|
Assert.Equal(before.GetRawText(), after.GetRawText());
|
|
Assert.Equal(archivedBytes, await File.ReadAllBytesAsync(fixture.ArchivedFile));
|
|
Assert.Equal(Convert.ToHexString(SHA256.HashData(archivedBytes)).ToLowerInvariant(), fixture.ArchivedSha256);
|
|
Assert.DoesNotContain(PrivateContent, after.GetRawText());
|
|
}
|
|
|
|
[Fact]
|
|
public async Task Index_uses_native_count_and_bounded_page_queries_without_writes_or_content_projection()
|
|
{
|
|
await using var fixture = await Fixture.CreateAsync(catalog: true);
|
|
await fixture.SeedAsync(db => db.CvVariants.AddRange(Enumerable.Range(100, 120).Select(id => Variant(id, OwnerA, $"Large {id:D3}"))));
|
|
var before = SHA256.HashData(await File.ReadAllBytesAsync(fixture.DatabaseFile));
|
|
fixture.Commands.Reset();
|
|
var page = await fixture.GetAsync("?page=2&pageSize=7");
|
|
Assert.Equal(7, Items(page).Length);
|
|
Assert.Equal(128, page.GetProperty("total").GetInt32());
|
|
var commands = fixture.Commands.Snapshot();
|
|
Assert.Equal(2, commands.Length);
|
|
Assert.All(commands, command => Assert.StartsWith("SELECT", command.TrimStart(), StringComparison.OrdinalIgnoreCase));
|
|
Assert.Contains(commands, command => command.Contains("COUNT(", StringComparison.OrdinalIgnoreCase));
|
|
Assert.Contains(commands, command => command.Contains("LIMIT", StringComparison.OrdinalIgnoreCase) && command.Contains("OFFSET", StringComparison.OrdinalIgnoreCase));
|
|
foreach (var forbidden in new[] { "\"BodyText\"", "\"SettingsJson\"", "\"RenderedCvHtml\"", "\"CareerProfileJson\"", "\"ApplicationMaterialJson\"", "\"FilePath\"", "\"To\"", "\"ThreadId\"", "\"ClientRequestId\"" })
|
|
Assert.All(commands, command => Assert.DoesNotContain(forbidden, command, StringComparison.OrdinalIgnoreCase));
|
|
Assert.All(commands, command => Assert.False(Regex.IsMatch(command,
|
|
@"\b(?:BodyText|SettingsJson|RenderedCvHtml|CareerProfileJson|ApplicationMaterialJson|FilePath|To|ThreadId|ClientRequestId)\b", RegexOptions.IgnoreCase)));
|
|
Assert.Equal(before, SHA256.HashData(await File.ReadAllBytesAsync(fixture.DatabaseFile)));
|
|
}
|
|
|
|
[Fact]
|
|
public void MariaDb_composition_keeps_unicode_markers_ordinal_and_paging_parameterized_without_connecting()
|
|
{
|
|
var service = typeof(AttachmentsController).Assembly.GetType("JobTrackerApi.Services.DocumentsIndexService")!;
|
|
var builder = service.GetMethod("BuildSql", BindingFlags.Static | BindingFlags.NonPublic)!;
|
|
var countSql = (string)builder.Invoke(null, [false, true, "date-desc"])!;
|
|
var pageSql = (string)builder.Invoke(null, [false, false, "date-desc"])!;
|
|
// Raw SQL composition cannot prove MariaDB execution; this check protects the declared Unicode/ordinal policy.
|
|
Assert.Contains("COLLATE utf8mb4_bin", countSql, StringComparison.OrdinalIgnoreCase);
|
|
Assert.Contains("CONVERT(COALESCE(j.Notes, '') USING utf8mb4)", countSql, StringComparison.OrdinalIgnoreCase);
|
|
Assert.Contains("LOCATE(", countSql, StringComparison.OrdinalIgnoreCase);
|
|
Assert.Contains("TIMESTAMPDIFF(MICROSECOND", pageSql, StringComparison.OrdinalIgnoreCase);
|
|
Assert.DoesNotContain("STRFTIME", pageSql, StringComparison.OrdinalIgnoreCase);
|
|
|
|
var commands = new CommandRecorder();
|
|
var currentUser = Mock.Of<ICurrentUserService>(current => current.UserId == OwnerA);
|
|
var options = new DbContextOptionsBuilder<JobTrackerContext>()
|
|
.UseMySql("Server=127.0.0.1;Database=composition_only;User=fixture;Password=fixture", new MariaDbServerVersion(new Version(11, 0, 0)))
|
|
.AddInterceptors(commands).Options;
|
|
using var db = new JobTrackerContext(options, currentUser);
|
|
object[] parameters = [OwnerA, "%æøå!%!_!!%", "all", "all", "all", 0, 7, 14];
|
|
var countComposition = db.Database.SqlQueryRaw<int>(countSql, parameters).ToQueryString();
|
|
var rowType = service.GetNestedType("DocumentRow", BindingFlags.NonPublic)!;
|
|
var queryMethod = typeof(RelationalDatabaseFacadeExtensions).GetMethods()
|
|
.Single(method => method.Name == "SqlQueryRaw" && method.IsGenericMethodDefinition && method.GetParameters().Length == 3);
|
|
var pageQuery = (IQueryable)queryMethod.MakeGenericMethod(rowType).Invoke(null, [db.Database, pageSql, parameters])!;
|
|
var pageComposition = pageQuery.ToQueryString();
|
|
Assert.Contains("COUNT(*)", countComposition, StringComparison.OrdinalIgnoreCase);
|
|
Assert.Contains("@p0", countComposition, StringComparison.Ordinal);
|
|
// Pomelo's display query substitutes pagination values; inspect the actual unexecuted command for binding.
|
|
Assert.Matches(@"(?i)\bLIMIT\s+7\s+OFFSET\s+14\s*;?\s*$", pageComposition);
|
|
using var pageCommand = pageQuery.CreateDbCommand();
|
|
var paging = Regex.Match(pageCommand.CommandText, @"\bLIMIT\s+(?<limit>@\w+)\s+OFFSET\s+(?<offset>@\w+)\s*;?\s*$", RegexOptions.IgnoreCase);
|
|
Assert.True(paging.Success, pageCommand.CommandText);
|
|
Assert.NotEqual(paging.Groups["limit"].Value, paging.Groups["offset"].Value);
|
|
foreach (var (name, value) in new[] { ("limit", 7), ("offset", 14) })
|
|
{
|
|
var parameter = Assert.Single(pageCommand.Parameters.Cast<DbParameter>(),
|
|
parameter => parameter.ParameterName.TrimStart('@') == paging.Groups[name].Value.TrimStart('@'));
|
|
Assert.Equal(value, Convert.ToInt32(parameter.Value));
|
|
}
|
|
Assert.Contains(pageCommand.Parameters.Cast<DbParameter>(), parameter => Equals(parameter.Value, OwnerA));
|
|
Assert.DoesNotMatch(@"(?i)\bLIMIT\s+\d+\s+OFFSET\s+\d+\s*;?\s*$", pageCommand.CommandText);
|
|
Assert.Contains("COLLATE utf8mb4_bin", pageComposition, StringComparison.OrdinalIgnoreCase);
|
|
Assert.Equal("Pomelo.EntityFrameworkCore.MySql", db.Database.ProviderName);
|
|
Assert.Empty(commands.Snapshot());
|
|
}
|
|
|
|
private static JsonElement[] Items(JsonElement page) => page.GetProperty("items").EnumerateArray().ToArray();
|
|
private static string? Text(JsonElement item, string name) => item.GetProperty(name).ValueKind == JsonValueKind.Null ? null : item.GetProperty(name).GetString();
|
|
|
|
private static CvVariant Variant(int id, string owner, string name, int? jobId = null) => new()
|
|
{
|
|
Id = id, OwnerUserId = owner, Name = name, JobApplicationId = jobId, PublicSlug = $"documents-{owner}-{id}",
|
|
SettingsJson = $"{{\"private\":\"{PrivateContent}\"}}", Version = 2,
|
|
CreatedAtUtc = DateTimeOffset.Parse("2026-01-01T00:00:00Z"), UpdatedAtUtc = DateTimeOffset.Parse("2026-01-01T00:00:00Z"),
|
|
};
|
|
|
|
private static EmailDraft Draft(Guid id, string owner, int jobId, string subject) => new()
|
|
{
|
|
Id = id, OwnerUserId = owner, JobApplicationId = jobId, Provider = "gmail", Subject = subject,
|
|
To = "private-recipient@example.test", BodyText = PrivateContent, ThreadId = "private-thread-a", ClientRequestId = $"private-client-request-{owner}",
|
|
CreatedAtUtc = new DateTime(2026, 1, 3, 0, 0, 0, DateTimeKind.Utc), UpdatedAtUtc = new DateTime(2026, 1, 4, 0, 0, 0, DateTimeKind.Utc),
|
|
};
|
|
|
|
private sealed class Fixture(WebApplication app, string root, CommandRecorder commands) : IAsyncDisposable
|
|
{
|
|
public CommandRecorder Commands { get; } = commands;
|
|
public string DatabaseFile { get; } = Path.Combine(root, "documents.sqlite");
|
|
public string LiveFile { get; } = Path.Combine(root, "private-storage-live.pdf");
|
|
public string ArchivedFile { get; } = Path.Combine(root, "private-storage-archived.pdf");
|
|
public string ArchivedSha256 { get; private set; } = "";
|
|
private readonly HttpClient _client = new() { BaseAddress = new Uri(app.Services.GetRequiredService<IServer>().Features.Get<IServerAddressesFeature>()!.Addresses.Single()) };
|
|
|
|
public static async Task<Fixture> CreateAsync(bool catalog = false)
|
|
{
|
|
var root = Path.Combine(Path.GetTempPath(), $"jobjakt-documents-contract-{Guid.NewGuid():N}");
|
|
Directory.CreateDirectory(root);
|
|
var commands = new CommandRecorder();
|
|
var builder = WebApplication.CreateBuilder(new WebApplicationOptions { EnvironmentName = "Development", ApplicationName = typeof(AttachmentsController).Assembly.GetName().Name });
|
|
builder.Logging.ClearProviders();
|
|
builder.WebHost.UseKestrel().UseUrls("http://127.0.0.1:0");
|
|
builder.Services.AddControllers().AddApplicationPart(typeof(AttachmentsController).Assembly);
|
|
builder.Services.AddAuthentication("local").AddScheme<AuthenticationSchemeOptions, FixtureAuthentication>("local", _ => { });
|
|
builder.Services.AddAuthorization();
|
|
builder.Services.AddHttpContextAccessor();
|
|
builder.Services.AddScoped<ICurrentUserService, CurrentUserService>();
|
|
builder.Services.AddDbContext<JobTrackerContext>(options => options.UseSqlite($"Data Source={Path.Combine(root, "documents.sqlite")};Pooling=False").AddInterceptors(commands));
|
|
if (typeof(AttachmentsController).Assembly.GetType("JobTrackerApi.Services.DocumentsIndexService") is { } indexService)
|
|
builder.Services.AddScoped(indexService);
|
|
var users = TestHostFactory.CreateUserManager();
|
|
users.Setup(manager => manager.GetUserAsync(It.IsAny<ClaimsPrincipal>())).ReturnsAsync((ClaimsPrincipal principal) =>
|
|
LocalAuthIdentity.GetRequiredUserId(principal) is { } owner ? new ApplicationUser { Id = owner } : null);
|
|
builder.Services.AddSingleton(users.Object);
|
|
var app = builder.Build();
|
|
app.UseAuthentication();
|
|
app.UseAuthorization();
|
|
app.MapControllers();
|
|
await app.StartAsync();
|
|
var fixture = new Fixture(app, root, commands);
|
|
await using (var scope = app.Services.CreateAsyncScope())
|
|
{
|
|
var db = scope.ServiceProvider.GetRequiredService<JobTrackerContext>();
|
|
await db.Database.EnsureCreatedAsync();
|
|
db.Users.AddRange(new ApplicationUser { Id = OwnerA }, new ApplicationUser { Id = OwnerB });
|
|
db.Companies.AddRange(new Company { Id = 1, OwnerUserId = OwnerA, Name = "Nordlys" }, new Company { Id = 2, OwnerUserId = OwnerB, Name = "Foreign company" });
|
|
db.JobApplications.AddRange(new JobApplication { Id = 1, OwnerUserId = OwnerA, CompanyId = 1, JobTitle = "Owned role", Status = "Saved" }, new JobApplication { Id = 2, OwnerUserId = OwnerB, CompanyId = 2, JobTitle = "Foreign role", Status = "Saved" });
|
|
await db.SaveChangesAsync();
|
|
}
|
|
if (catalog) await fixture.SeedCatalogAsync();
|
|
fixture.Commands.Reset();
|
|
return fixture;
|
|
}
|
|
|
|
public async Task SeedAsync(Action<JobTrackerContext> seed)
|
|
{
|
|
await using var scope = app.Services.CreateAsyncScope();
|
|
var db = scope.ServiceProvider.GetRequiredService<JobTrackerContext>();
|
|
using var owner = ((CurrentUserService)scope.ServiceProvider.GetRequiredService<ICurrentUserService>()).UseBackgroundUser(OwnerA);
|
|
seed(db);
|
|
await db.SaveChangesAsync();
|
|
}
|
|
|
|
private async Task SeedCatalogAsync()
|
|
{
|
|
await File.WriteAllBytesAsync(LiveFile, Encoding.UTF8.GetBytes("Live upload ÆØÅ"));
|
|
var bytes = Encoding.UTF8.GetBytes("Frozen upload ÆØÅ exact bytes\n");
|
|
await File.WriteAllBytesAsync(ArchivedFile, bytes);
|
|
ArchivedSha256 = Convert.ToHexString(SHA256.HashData(bytes)).ToLowerInvariant();
|
|
await using var scope = app.Services.CreateAsyncScope();
|
|
var db = scope.ServiceProvider.GetRequiredService<JobTrackerContext>();
|
|
var jobs = await db.JobApplications.IgnoreQueryFilters().OrderBy(job => job.Id).ToListAsync();
|
|
foreach (var job in jobs)
|
|
{
|
|
var own = job.Id == 1;
|
|
var owner = own ? OwnerA : OwnerB;
|
|
var cvId = own ? 10 : 20;
|
|
var cv = Variant(cvId, owner, own ? "Public ÆØÅ CV" : "Foreign CV", job.Id);
|
|
cv.IsPublic = own;
|
|
if (own) cv.PublicSlug = "documents-public-a";
|
|
db.CvVariants.Add(cv);
|
|
job.CoverLetterText = PrivateContent;
|
|
job.Notes = $"Human notes\n<<<APPLICATION_ANSWER_DRAFT>>>\n{PrivateContent}\n<<<END_APPLICATION_ANSWER_DRAFT>>>";
|
|
job.TailoredCvText = PrivateContent;
|
|
job.TailoredCvUpdatedAt = new DateTime(2026, 1, 5, 0, 0, 0, DateTimeKind.Utc);
|
|
db.CoverLetterVersions.AddRange(Enumerable.Range(1, own ? 2 : 1).Select(version => new CoverLetterVersion { OwnerUserId = owner, JobApplicationId = job.Id, Version = version, Text = PrivateContent, CreatedAtUtc = DateTimeOffset.Parse($"2026-01-0{version}T00:00:00Z") }));
|
|
db.Attachments.Add(new Attachment { Id = job.Id, JobApplicationId = job.Id, FileName = own ? "Portfolio.pdf" : "Foreign upload.pdf", FilePath = LiveFile, FileSize = 41, FileType = "application/pdf", Purpose = "portfolio", UseForAi = false, UploadDate = new DateTime(2026, 1, 2, 3, 4, 5).AddTicks(1234567) });
|
|
db.SubmittedApplicationPackages.Add(new SubmittedApplicationPackage
|
|
{
|
|
Id = job.Id, OwnerUserId = owner, JobApplicationId = job.Id, Version = 1, CvVariantId = cvId,
|
|
CvVariantName = own ? "Frozen CV ÆØÅ" : "Foreign frozen CV", CvVariantVersion = 1,
|
|
CvSettingsJson = PrivateContent, CareerProfileJson = PrivateContent, RenderedCvHtml = PrivateContent,
|
|
CoverLetterText = PrivateContent, ApplicationMaterialJson = PrivateContent, CreatedAtUtc = DateTimeOffset.Parse("2026-01-06T00:00:00Z"),
|
|
Attachments = [new SubmittedPackageAttachment { FileName = "Archived.pdf", FilePath = ArchivedFile, FileSize = bytes.Length, FileType = "application/pdf", Sha256 = ArchivedSha256 }],
|
|
});
|
|
db.EmailDrafts.Add(Draft(Guid.Parse(own ? "11111111-1111-1111-1111-111111111111" : "22222222-2222-2222-2222-222222222222"), owner, job.Id, own ? "Saved subject ÆØÅ" : "Foreign subject"));
|
|
}
|
|
db.CvVariants.Add(Variant(11, OwnerA, "Independent private CV"));
|
|
await db.SaveChangesAsync();
|
|
}
|
|
|
|
public async Task<JsonElement> GetAsync(string query = "", string owner = OwnerA)
|
|
{
|
|
using var response = await SendAsync(query, owner);
|
|
Assert.Equal(HttpStatusCode.OK, response.StatusCode);
|
|
using var document = JsonDocument.Parse(await response.Content.ReadAsStringAsync());
|
|
return document.RootElement.Clone();
|
|
}
|
|
|
|
public async Task<HttpStatusCode> StatusAsync(string query, string? owner = OwnerA)
|
|
{
|
|
using var response = await SendAsync(query, owner);
|
|
return response.StatusCode;
|
|
}
|
|
|
|
private Task<HttpResponseMessage> SendAsync(string query, string? owner)
|
|
{
|
|
var request = new HttpRequestMessage(HttpMethod.Get, $"/api/documents{query}");
|
|
if (owner is not null) request.Headers.Add("X-Documents-Test-Owner", owner);
|
|
return _client.SendAsync(request);
|
|
}
|
|
|
|
public async ValueTask DisposeAsync()
|
|
{
|
|
_client.Dispose();
|
|
await app.StopAsync();
|
|
await app.DisposeAsync();
|
|
Directory.Delete(root, recursive: true);
|
|
}
|
|
}
|
|
|
|
// The fixture authenticates only its two fixed identities; production JWT/session acceptance remains in its existing suite.
|
|
private sealed class FixtureAuthentication(IOptionsMonitor<AuthenticationSchemeOptions> options, ILoggerFactory logger, UrlEncoder encoder)
|
|
: AuthenticationHandler<AuthenticationSchemeOptions>(options, logger, encoder)
|
|
{
|
|
protected override Task<AuthenticateResult> HandleAuthenticateAsync()
|
|
{
|
|
var owner = Request.Headers["X-Documents-Test-Owner"].ToString();
|
|
if (owner is not (OwnerA or OwnerB)) return Task.FromResult(AuthenticateResult.NoResult());
|
|
var principal = new ClaimsPrincipal(new ClaimsIdentity([new Claim(ClaimTypes.NameIdentifier, owner)], "local"));
|
|
return Task.FromResult(AuthenticateResult.Success(new AuthenticationTicket(principal, "local")));
|
|
}
|
|
}
|
|
|
|
private sealed class CommandRecorder : DbCommandInterceptor
|
|
{
|
|
private readonly List<string> _commands = [];
|
|
public void Reset() { lock (_commands) _commands.Clear(); }
|
|
public string[] Snapshot() { lock (_commands) return _commands.ToArray(); }
|
|
private void Record(DbCommand command) { lock (_commands) _commands.Add(command.CommandText); }
|
|
public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result) { Record(command); return result; }
|
|
public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default) { Record(command); return ValueTask.FromResult(result); }
|
|
public override InterceptionResult<int> NonQueryExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<int> result) { Record(command); return result; }
|
|
public override ValueTask<InterceptionResult<int>> NonQueryExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<int> result, CancellationToken cancellationToken = default) { Record(command); return ValueTask.FromResult(result); }
|
|
public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> result) { Record(command); return result; }
|
|
public override ValueTask<InterceptionResult<object>> ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<object> result, CancellationToken cancellationToken = default) { Record(command); return ValueTask.FromResult(result); }
|
|
}
|
|
}
|