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); }
    }
}