using System.Reflection;
using System.Security.Claims;
using Api.SeaHavenIndustries.Controllers;
using Data.SeaHavenIndustries;
using Data.SeaHavenIndustries.Enums;
using Data.SeaHavenIndustries.Migrations;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Migrations.Operations;
using SeaHaven.DataServices.Implementation;
using SeaHaven.Services.DTOs;
using SeaHaven.Services.Exceptions;
using SeaHaven.Services.Helpers;
using SeaHaven.Services.Implementation;
using Xunit;

namespace SeaHavenIndustries.Tests;

// Completion document templates registry: authoring with ordered procedures,
// search, permission-gated writes, tenant-scoped linked work orders, and a
// delete that unlinks Services without dropping their document requirement.
public class CompletionDocTemplateServiceTests
{
    private const string AdminId = "admin-1";
    private const string SchedulerId = "scheduler-1";
    private const string DispatcherId = "dispatcher-1";

    private static ApplicationDbContext NewContext()
    {
        var options = new DbContextOptionsBuilder<ApplicationDbContext>()
            .UseInMemoryDatabase(Guid.NewGuid().ToString())
            .Options;
        var context = new ApplicationDbContext(options);
        SeedUser(context, AdminId, "Admin", "Jane", "Doe");
        SeedUser(context, SchedulerId, "Scheduler", "Sam", "Scheduler");
        SeedUser(context, DispatcherId, "Dispatcher", "Dee", "Dispatcher");
        context.SaveChanges();
        return context;
    }

    private static void SeedUser(ApplicationDbContext context, string userId, string role, string first, string last)
    {
        context.Users.Add(new ApplicationUser
        {
            Id = userId,
            UserName = userId,
            NormalizedUserName = userId.ToUpperInvariant(),
            FirstName = first,
            LastName = last
        });
        var roleId = "role-" + role;
        if (context.Roles.Local.All(r => r.Id != roleId))
            context.Roles.Add(new IdentityRole { Id = roleId, Name = role, NormalizedName = role.ToUpperInvariant() });
        context.UserRoles.Add(new IdentityUserRole<string> { UserId = userId, RoleId = roleId });
    }

    private static CompletionDocTemplateService NewService(ApplicationDbContext context) =>
        new(
            new CompletionDocTemplateDataService(context),
            new TeamPermissionOverrideDataService(context),
            new TeamPermissionPolicy(),
            WorkOrderAccountTestHelpers.Resolver(context));

    private static ClaimsPrincipal Caller(string userId, string role, int? accountId = null)
    {
        var claims = new List<Claim>
        {
            new(ClaimTypes.NameIdentifier, userId),
            new(ClaimTypes.Role, role)
        };
        claims.Add(accountId.HasValue
            ? new Claim(SeaHavenClaimTypes.AccountId, accountId.Value.ToString())
            : new Claim(SeaHavenClaimTypes.OrgScope, SeaHavenClaimTypes.OrgScopeAll));
        return new ClaimsPrincipal(new ClaimsIdentity(claims, authenticationType: "test"));
    }

    private static ClaimsPrincipal Admin(int? accountId = null) => Caller(AdminId, "Admin", accountId);
    private static ClaimsPrincipal Scheduler() => Caller(SchedulerId, "Scheduler");
    private static ClaimsPrincipal Dispatcher() => Caller(DispatcherId, "Dispatcher");

    private static CompletionDocTemplateCreateDto Body(
        string? name = "Backflow Test Report",
        string? extraSafetyNote = null,
        params (string? Name, string? Description)[] procedures) => new()
        {
            Name = name,
            ExtraSafetyNote = extraSafetyNote,
            Procedures = procedures
                .Select(p => new CompletionDocTemplateProcedureInputDto { Name = p.Name, Description = p.Description })
                .ToList()
        };

    [Fact]
    public async Task Create_PersistsTitleNoteAndProceduresInSubmittedOrder()
    {
        using var context = NewContext();
        var service = NewService(context);

        var created = await service.CreateAsync(
            Admin(),
            Body("  Backflow Test Report  ", " Wear gloves ",
                ("Backflow Procedure", "line 1\nline 2"),
                ("Shutoff", "Close valve"),
                ("Cleanup", "")),
            CancellationToken.None);

        Assert.Equal("Backflow Test Report", created.Name);
        Assert.Equal("Wear gloves", created.ExtraSafetyNote);
        Assert.Equal(new[] { "Backflow Procedure", "Shutoff", "Cleanup" }, created.Procedures.Select(p => p.Name));
        Assert.Equal(new[] { 0, 1, 2 }, created.Procedures.Select(p => p.SortOrder));
        Assert.Equal("line 1\nline 2", created.Procedures[0].Description);
        Assert.All(created.Procedures, p => Assert.True(p.Id > 0));
        Assert.Equal("Jane Doe", created.CreatedByName);
        Assert.NotNull(created.CreatedAt);
        Assert.Equal(DateTimeKind.Utc, created.CreatedAt!.Value.Kind);
        Assert.Null(created.UpdatedAt);
        Assert.True(created.IsActive);
        Assert.Equal("", created.ServiceKey);
        Assert.Equal("", created.TemplateUrl);

        var fetched = await service.GetAsync(created.Id, CancellationToken.None);
        Assert.Equal(created.Procedures.Select(p => p.Id), fetched!.Procedures.Select(p => p.Id));
    }

    [Fact]
    public async Task Create_WithZeroProceduresAndNoNote_Succeeds()
    {
        using var context = NewContext();

        var created = await NewService(context).CreateAsync(
            Admin(), new CompletionDocTemplateCreateDto { Name = "Empty" }, CancellationToken.None);

        Assert.Empty(created.Procedures);
        Assert.Null(created.ExtraSafetyNote);
    }

    [Fact]
    public async Task Create_AllowsBlankProcedureNameAndDescription()
    {
        using var context = NewContext();

        var created = await NewService(context).CreateAsync(
            Admin(), Body("Placeholder", null, (null, null), ("  ", "")), CancellationToken.None);

        Assert.Equal(2, created.Procedures.Count);
        Assert.All(created.Procedures, p => Assert.Equal("", p.Name));
    }

    [Theory]
    [InlineData(null)]
    [InlineData("")]
    [InlineData("   ")]
    public async Task CreateAndUpdate_WithoutTitle_ThrowTitleRequired(string? name)
    {
        using var context = NewContext();
        var service = NewService(context);
        var existing = await service.CreateAsync(Admin(), Body(), CancellationToken.None);

        var onCreate = await Assert.ThrowsAsync<WorkOrderBoardValidationException>(() =>
            service.CreateAsync(Admin(), Body(name), CancellationToken.None));
        var onUpdate = await Assert.ThrowsAsync<WorkOrderBoardValidationException>(() =>
            service.UpdateAsync(Admin(), existing.Id, Body(name), CancellationToken.None));

        Assert.Equal("TitleRequired", onCreate.Code);
        Assert.Equal("Document Title is required.", onCreate.Message);
        Assert.Equal("TitleRequired", onUpdate.Code);
        Assert.Single(context.CompletionDocTemplates);
    }

    [Fact]
    public async Task Create_RejectsOverlongExtraSafetyNoteAndProcedureName()
    {
        using var context = NewContext();
        var service = NewService(context);

        var note = await Assert.ThrowsAsync<WorkOrderBoardValidationException>(() =>
            service.CreateAsync(Admin(), Body("T", new string('n', 2001)), CancellationToken.None));
        var procedure = await Assert.ThrowsAsync<WorkOrderBoardValidationException>(() =>
            service.CreateAsync(Admin(), Body("T", null, (new string('p', 201), "d")), CancellationToken.None));

        Assert.Equal("InvalidValue", note.Code);
        Assert.Equal("InvalidValue", procedure.Code);
        Assert.Empty(context.CompletionDocTemplates);
    }

    [Fact]
    public async Task Update_ReplacesWholeProcedureListAndStampsUpdatedAt()
    {
        using var context = NewContext();
        var service = NewService(context);
        var created = await service.CreateAsync(
            Admin(), Body("T", "Note", ("A", "a"), ("B", "b"), ("C", "c")), CancellationToken.None);

        var updated = await service.UpdateAsync(
            Admin(), created.Id, Body("T2", "Note", ("C", "c2"), ("A", "a2")), CancellationToken.None);

        Assert.Equal("T2", updated.Name);
        Assert.Equal(new[] { "C", "A" }, updated.Procedures.Select(p => p.Name));
        Assert.Equal(new[] { 0, 1 }, updated.Procedures.Select(p => p.SortOrder));
        Assert.Equal(2, context.CompletionDocTemplateProcedures.Count());
        Assert.NotNull(updated.UpdatedAt);
        Assert.Equal("Jane Doe", updated.CreatedByName);
    }

    [Fact]
    public async Task Update_NullNoteClearsIt_EmptyProceduresClearThem_NullProceduresKeepThem()
    {
        using var context = NewContext();
        var service = NewService(context);
        var created = await service.CreateAsync(Admin(), Body("T", "Note", ("A", "a")), CancellationToken.None);

        var kept = await service.UpdateAsync(
            Admin(), created.Id, new CompletionDocTemplateCreateDto { Name = "T", Procedures = null }, CancellationToken.None);
        Assert.Null(kept.ExtraSafetyNote);
        Assert.Equal(new[] { "A" }, kept.Procedures.Select(p => p.Name));

        var cleared = await service.UpdateAsync(
            Admin(), created.Id, new CompletionDocTemplateCreateDto { Name = "T", Procedures = new() }, CancellationToken.None);
        Assert.Empty(cleared.Procedures);
    }

    [Fact]
    public async Task Update_OmittedLegacyFieldsKeepStoredValues()
    {
        using var context = NewContext();
        context.CompletionDocTemplates.Add(new CompletionDocTemplate
        {
            Id = 7,
            Name = "HVAC PM Completion",
            ServiceKey = "HVAC PM",
            WorkOrderType = WorkOrderType.PM,
            TemplateUrl = "https://example.com/hvac.pdf",
            IsActive = true
        });
        await context.SaveChangesAsync();

        var updated = await NewService(context).UpdateAsync(
            Admin(), 7, Body("HVAC PM Completion v2", "Mask"), CancellationToken.None);

        Assert.Equal("HVAC PM", updated.ServiceKey);
        Assert.Equal(WorkOrderType.PM, updated.WorkOrderType);
        Assert.Equal("https://example.com/hvac.pdf", updated.TemplateUrl);
        Assert.True(updated.IsActive);
        Assert.Null(updated.CreatedByName);
    }

    // Bodies are bound by System.Text.Json with the web defaults, as MVC does.
    private static CompletionDocTemplateCreateDto JsonBody(string json) =>
        System.Text.Json.JsonSerializer.Deserialize<CompletionDocTemplateCreateDto>(
            json, new System.Text.Json.JsonSerializerOptions(System.Text.Json.JsonSerializerDefaults.Web))!;

    [Fact]
    public async Task Update_WorkOrderTypeExplicitNullClears_OmittedKeeps_ValueSets()
    {
        using var context = NewContext();
        context.CompletionDocTemplates.Add(new CompletionDocTemplate
        {
            Id = 8,
            Name = "HVAC PM Completion",
            ServiceKey = "HVAC",
            WorkOrderType = WorkOrderType.PM,
            TemplateUrl = "",
            IsActive = true
        });
        await context.SaveChangesAsync();
        var service = NewService(context);

        var omitted = JsonBody("""{"name":"HVAC Completion"}""");
        Assert.False(omitted.WorkOrderTypeSpecified);
        var kept = await service.UpdateAsync(Admin(), 8, omitted, CancellationToken.None);
        Assert.Equal(WorkOrderType.PM, kept.WorkOrderType);

        var cleared = await service.UpdateAsync(
            Admin(), 8, JsonBody("""{"name":"HVAC Completion","workOrderType":null}"""), CancellationToken.None);
        Assert.Null(cleared.WorkOrderType);
        Assert.Null((await context.CompletionDocTemplates.AsNoTracking().SingleAsync(t => t.Id == 8)).WorkOrderType);

        var set = await service.UpdateAsync(
            Admin(), 8, JsonBody("""{"name":"HVAC Completion","workOrderType":6}"""), CancellationToken.None);
        Assert.Equal(WorkOrderType.Reactive, set.WorkOrderType);
    }

    [Fact]
    public async Task Update_MissingOrDeletedTemplate_ThrowsNotFound()
    {
        using var context = NewContext();
        context.CompletionDocTemplates.Add(new CompletionDocTemplate { Id = 3, Name = "Gone", IsDeleted = true });
        await context.SaveChangesAsync();
        var service = NewService(context);

        var missing = await Assert.ThrowsAsync<WorkOrderBoardValidationException>(() =>
            service.UpdateAsync(Admin(), 999, Body(), CancellationToken.None));
        var deleted = await Assert.ThrowsAsync<WorkOrderBoardValidationException>(() =>
            service.UpdateAsync(Admin(), 3, Body(), CancellationToken.None));

        Assert.Equal("NotFound", missing.Code);
        Assert.Equal("NotFound", deleted.Code);
    }

    [Fact]
    public async Task List_SearchIsPartialCaseInsensitiveAndOrderedByName()
    {
        using var context = NewContext();
        var service = NewService(context);
        foreach (var name in new[] { "Roof Inspection", "Backflow Test Report", "Annual BACKFLOW check", "HVAC PM" })
            await service.CreateAsync(Admin(), Body(name), CancellationToken.None);
        context.CompletionDocTemplates.Add(new CompletionDocTemplate { Name = "Backflow deleted", IsDeleted = true });
        await context.SaveChangesAsync();

        var matches = await service.ListAsync("backFLOW", null, null, CancellationToken.None);
        var all = await service.ListAsync(null, null, null, CancellationToken.None);
        var none = await service.ListAsync("zzz", null, null, CancellationToken.None);

        Assert.Equal(new[] { "Annual BACKFLOW check", "Backflow Test Report" }, matches.Select(t => t.Name));
        Assert.Equal(
            new[] { "Annual BACKFLOW check", "Backflow Test Report", "HVAC PM", "Roof Inspection" },
            all.Select(t => t.Name));
        Assert.Empty(none);
    }

    [Fact]
    public async Task List_KeepsLegacyServiceKeyAndWorkOrderTypeFilters()
    {
        using var context = NewContext();
        context.CompletionDocTemplates.AddRange(
            new CompletionDocTemplate { Name = "HVAC PM", ServiceKey = "HVAC", WorkOrderType = WorkOrderType.PM },
            new CompletionDocTemplate { Name = "HVAC Any", ServiceKey = "HVAC" },
            new CompletionDocTemplate { Name = "HVAC Reactive", ServiceKey = "HVAC", WorkOrderType = WorkOrderType.Reactive },
            new CompletionDocTemplate { Name = "Plumbing", ServiceKey = "Plumbing" });
        await context.SaveChangesAsync();

        var rows = await NewService(context).ListAsync(null, "HVAC", WorkOrderType.PM, CancellationToken.None);

        Assert.Equal(new[] { "HVAC Any", "HVAC PM" }, rows.Select(t => t.Name));
    }

    [Fact]
    public async Task Writes_FollowTeamPermissions()
    {
        using var context = NewContext();
        var service = NewService(context);

        var byScheduler = await service.CreateAsync(Scheduler(), Body("Scheduler template"), CancellationToken.None);
        await service.UpdateAsync(Scheduler(), byScheduler.Id, Body("Scheduler edit"), CancellationToken.None);
        Assert.Equal("Sam Scheduler", byScheduler.CreatedByName);

        var schedulerDelete = await Assert.ThrowsAsync<WorkOrderBoardValidationException>(() =>
            service.DeleteAsync(Scheduler(), byScheduler.Id, CancellationToken.None));
        var dispatcherCreate = await Assert.ThrowsAsync<WorkOrderBoardValidationException>(() =>
            service.CreateAsync(Dispatcher(), Body("Nope"), CancellationToken.None));
        var dispatcherEdit = await Assert.ThrowsAsync<WorkOrderBoardValidationException>(() =>
            service.UpdateAsync(Dispatcher(), byScheduler.Id, Body("Nope"), CancellationToken.None));
        var anonymous = await Assert.ThrowsAsync<WorkOrderBoardValidationException>(() =>
            service.CreateAsync(new ClaimsPrincipal(new ClaimsIdentity()), Body("Nope"), CancellationToken.None));

        Assert.All(new[] { schedulerDelete, dispatcherCreate, dispatcherEdit, anonymous },
            ex => Assert.Equal("Forbidden", ex.Code));
        Assert.Equal("Scheduler edit", context.CompletionDocTemplates.Single().Name);
        Assert.NotEqual(true, context.CompletionDocTemplates.Single().IsDeleted);
    }

    [Fact]
    public async Task Writes_HonorPerPersonOverrides()
    {
        using var context = NewContext();
        context.UserPermissionOverrides.Add(new UserPermissionOverride
        {
            UserId = SchedulerId,
            PermissionKey = "deleteCompletionDocTemplates",
            State = UserPermissionState.Allow
        });
        context.UserPermissionOverrides.Add(new UserPermissionOverride
        {
            UserId = SchedulerId,
            PermissionKey = "createCompletionDocTemplates",
            State = UserPermissionState.Deny
        });
        await context.SaveChangesAsync();
        var service = NewService(context);
        var template = await service.CreateAsync(Admin(), Body(), CancellationToken.None);

        var denied = await Assert.ThrowsAsync<WorkOrderBoardValidationException>(() =>
            service.CreateAsync(Scheduler(), Body("Denied"), CancellationToken.None));
        await service.DeleteAsync(Scheduler(), template.Id, CancellationToken.None);

        Assert.Equal("Forbidden", denied.Code);
        Assert.True(context.CompletionDocTemplates.Single().IsDeleted);
    }

    [Fact]
    public async Task LinkedWorkOrders_CountsOnlyOpenWorkOrdersOfLinkedServicesInCallerTenant()
    {
        using var context = NewContext();
        var template = new CompletionDocTemplate { Id = 10, Name = "Backflow" };
        var other = new CompletionDocTemplate { Id = 11, Name = "Other" };
        context.CompletionDocTemplates.AddRange(template, other);
        context.Services.AddRange(
            new Service { Id = 1, Name = "Backflow", NormalizedName = "BACKFLOW", RequiresCompletionDocument = true, CompletionDocTemplateId = 10 },
            new Service { Id = 2, Name = "Backflow 2", NormalizedName = "BACKFLOW 2", RequiresCompletionDocument = true, CompletionDocTemplateId = 10 },
            new Service { Id = 3, Name = "Roof", NormalizedName = "ROOF", RequiresCompletionDocument = true, CompletionDocTemplateId = 11 });
        context.workOrders.AddRange(
            new WorkOrder { Id = 101, ServiceId = 1, AccountId = 1, LifecycleStatus = LifecycleStatus.Scheduled },
            new WorkOrder { Id = 102, ServiceId = 2, AccountId = 1, LifecycleStatus = null },
            new WorkOrder { Id = 103, ServiceId = 1, AccountId = 1, LifecycleStatus = LifecycleStatus.Completed },
            new WorkOrder { Id = 104, ServiceId = 1, AccountId = 1, LifecycleStatus = LifecycleStatus.Canceled },
            new WorkOrder { Id = 105, ServiceId = 3, AccountId = 1, LifecycleStatus = LifecycleStatus.Scheduled },
            new WorkOrder { Id = 106, ServiceId = null, AccountId = 1, LifecycleStatus = LifecycleStatus.Scheduled },
            new WorkOrder { Id = 107, ServiceId = 1, AccountId = 1, LifecycleStatus = LifecycleStatus.Scheduled, IsDeleted = true },
            new WorkOrder { Id = 108, ServiceId = 1, AccountId = 1, LifecycleStatus = LifecycleStatus.Scheduled, istemplate = true },
            new WorkOrder { Id = 205, ServiceId = 1, AccountId = 2, LifecycleStatus = LifecycleStatus.InProgress });
        context.workOrders.AddRange(LegacyStatusWorkOrders(serviceId: 1, accountId: 1));
        await context.SaveChangesAsync();
        var service = NewService(context);

        var tenantOne = await service.GetLinkedWorkOrdersAsync(Admin(accountId: 1), 10, CancellationToken.None);
        var tenantTwo = await service.GetLinkedWorkOrdersAsync(Admin(accountId: 2), 10, CancellationToken.None);
        var orgWide = await service.GetLinkedWorkOrdersAsync(Admin(), 10, CancellationToken.None);

        Assert.Equal(3, tenantOne.Count);
        Assert.Equal(new[] { 101, 102, 111 }, tenantOne.WorkOrderIds);
        Assert.Equal(new[] { 205 }, tenantTwo.WorkOrderIds);
        Assert.Equal(new[] { 101, 102, 111, 205 }, orgWide.WorkOrderIds);
        Assert.Equal(4, orgWide.Count);
    }

    // Legacy rows carry no LifecycleStatus; their legacy status text decides whether they are open.
    private static WorkOrder[] LegacyStatusWorkOrders(int serviceId, int accountId) => new[]
    {
        new WorkOrder { Id = 109, ServiceId = serviceId, AccountId = accountId, LegacyStatus = "Completed" },
        new WorkOrder { Id = 110, ServiceId = serviceId, AccountId = accountId, Status = " Cancelled " },
        new WorkOrder { Id = 111, ServiceId = serviceId, AccountId = accountId, LegacyStatus = "On Hold" }
    };

    [Fact]
    public async Task LinkedWorkOrders_UnknownTemplateOrMissingScope_Throws()
    {
        using var context = NewContext();
        context.CompletionDocTemplates.Add(new CompletionDocTemplate { Id = 10, Name = "Backflow" });
        await context.SaveChangesAsync();
        var service = NewService(context);
        var noScope = new ClaimsPrincipal(new ClaimsIdentity(
            new[] { new Claim(ClaimTypes.NameIdentifier, DispatcherId), new Claim(ClaimTypes.Role, "Dispatcher") },
            authenticationType: "test"));

        var missing = await Assert.ThrowsAsync<WorkOrderBoardValidationException>(() =>
            service.GetLinkedWorkOrdersAsync(Admin(), 999, CancellationToken.None));
        var forbidden = await Assert.ThrowsAsync<WorkOrderBoardValidationException>(() =>
            service.GetLinkedWorkOrdersAsync(noScope, 10, CancellationToken.None));

        Assert.Equal("NotFound", missing.Code);
        Assert.Equal("Forbidden", forbidden.Code);
    }

    [Fact]
    public async Task Delete_ClearsServiceLinksKeepsDocumentRequirementAndHidesTemplate()
    {
        using var context = NewContext();
        context.CompletionDocTemplates.Add(new CompletionDocTemplate { Id = 10, Name = "Backflow" });
        context.Services.AddRange(
            new Service { Id = 1, Name = "Backflow", NormalizedName = "BACKFLOW", RequiresCompletionDocument = true, CompletionDocTemplateId = 10 },
            new Service { Id = 2, Name = "Unrelated", NormalizedName = "UNRELATED", RequiresCompletionDocument = true, CompletionDocTemplateId = null });
        await context.SaveChangesAsync();
        var service = NewService(context);

        await service.DeleteAsync(Admin(), 10, CancellationToken.None);

        var linked = context.Services.Single(s => s.Id == 1);
        Assert.Null(linked.CompletionDocTemplateId);
        Assert.True(linked.RequiresCompletionDocument);
        Assert.True(context.Services.Single(s => s.Id == 2).RequiresCompletionDocument);
        var template = context.CompletionDocTemplates.Single();
        Assert.True(template.IsDeleted);
        Assert.Equal(AdminId, template.DeleterUserId);
        Assert.Null(await service.GetAsync(10, CancellationToken.None));
        Assert.Empty(await service.ListAsync(null, null, null, CancellationToken.None));

        var again = await Assert.ThrowsAsync<WorkOrderBoardValidationException>(() =>
            service.DeleteAsync(Admin(), 10, CancellationToken.None));
        Assert.Equal("NotFound", again.Code);
    }

    [Fact]
    public async Task Reads_ForwardCancellation()
    {
        using var context = NewContext();
        var service = NewService(context);
        await service.CreateAsync(Admin(), Body(), CancellationToken.None);
        using var cts = new CancellationTokenSource();
        cts.Cancel();

        await Assert.ThrowsAnyAsync<OperationCanceledException>(() =>
            service.ListAsync(null, null, null, cts.Token));
        await Assert.ThrowsAnyAsync<OperationCanceledException>(() =>
            service.CreateAsync(Admin(), Body("Cancelled"), cts.Token));
        Assert.Single(context.CompletionDocTemplates);
    }

    // Relational evidence (SQLite enforces foreign keys; the in-memory provider does not).

    [Fact]
    public async Task Relational_DeleteOfLinkedTemplateSucceedsAndUnlinksService()
    {
        await using var connection = new SqliteConnection("DataSource=:memory:");
        await connection.OpenAsync();
        await using var context = await NewSqliteContextAsync(connection);
        SeedUser(context, AdminId, "Admin", "Jane", "Doe");
        var template = new CompletionDocTemplate { Name = "Backflow" };
        context.CompletionDocTemplates.Add(template);
        await context.SaveChangesAsync();
        context.Services.Add(new Service
        {
            Name = "Backflow",
            NormalizedName = "BACKFLOW",
            RequiresCompletionDocument = true,
            CompletionDocTemplateId = template.Id
        });
        await context.SaveChangesAsync();
        var service = NewService(context);

        await service.DeleteAsync(Admin(), template.Id, CancellationToken.None);

        context.ChangeTracker.Clear();
        var stored = await context.Services.SingleAsync();
        Assert.Null(stored.CompletionDocTemplateId);
        Assert.True(stored.RequiresCompletionDocument);
        Assert.True((await context.CompletionDocTemplates.SingleAsync()).IsDeleted);
    }

    [Fact]
    public async Task Relational_LinkedWorkOrdersExcludeTerminalAndLegacyTerminalRows()
    {
        await using var connection = new SqliteConnection("DataSource=:memory:");
        await connection.OpenAsync();
        await using var context = await NewSqliteContextAsync(connection);
        await WorkOrderAccountTestHelpers.EnsureAccountAsync(context, 1);
        await WorkOrderAccountTestHelpers.EnsureAccountAsync(context, 2, "Other Corp");
        context.CompletionDocTemplates.Add(new CompletionDocTemplate { Id = 10, Name = "Backflow" });
        context.Services.Add(new Service
        {
            Id = 1,
            Name = "Backflow",
            NormalizedName = "BACKFLOW",
            RequiresCompletionDocument = true,
            CompletionDocTemplateId = 10
        });
        context.workOrders.AddRange(
            new WorkOrder { Id = 101, ServiceId = 1, AccountId = 1, LifecycleStatus = LifecycleStatus.Scheduled },
            new WorkOrder { Id = 102, ServiceId = 1, AccountId = 1 },
            new WorkOrder { Id = 103, ServiceId = 1, AccountId = 1, LifecycleStatus = LifecycleStatus.Completed },
            new WorkOrder { Id = 104, ServiceId = 1, AccountId = 1, LifecycleStatus = LifecycleStatus.Canceled },
            new WorkOrder { Id = 205, ServiceId = 1, AccountId = 2, LifecycleStatus = LifecycleStatus.OnSite });
        context.workOrders.AddRange(LegacyStatusWorkOrders(serviceId: 1, accountId: 1));
        await context.SaveChangesAsync();
        var data = new CompletionDocTemplateDataService(context);

        var tenantOne = await data.GetOpenLinkedWorkOrderIdsAsync(10, 1, CancellationToken.None);
        var orgWide = await data.GetOpenLinkedWorkOrderIdsAsync(10, null, CancellationToken.None);

        Assert.Equal(new[] { 101, 102, 111 }, tenantOne);
        Assert.Equal(new[] { 101, 102, 111, 205 }, orgWide);
    }

    [Fact]
    public async Task Relational_UpdateReplacesProceduresInOrder()
    {
        await using var connection = new SqliteConnection("DataSource=:memory:");
        await connection.OpenAsync();
        await using var context = await NewSqliteContextAsync(connection);
        SeedUser(context, AdminId, "Admin", "Jane", "Doe");
        await context.SaveChangesAsync();
        var service = NewService(context);
        var created = await service.CreateAsync(
            Admin(), Body("T", null, ("A", "a"), ("B", "b")), CancellationToken.None);

        context.ChangeTracker.Clear();
        var updated = await service.UpdateAsync(
            Admin(), created.Id, Body("T", null, ("B", "b"), ("C", "c"), ("A", "a")), CancellationToken.None);

        Assert.Equal(new[] { "B", "C", "A" }, updated.Procedures.Select(p => p.Name));
        Assert.Equal("Jane Doe", updated.CreatedByName);
        context.ChangeTracker.Clear();
        Assert.Equal(3, await context.CompletionDocTemplateProcedures.CountAsync());
    }

    private static async Task<ApplicationDbContext> NewSqliteContextAsync(SqliteConnection connection)
    {
        var options = new DbContextOptionsBuilder<ApplicationDbContext>()
            .UseSqlite(connection)
            .Options;
        var context = new SqliteTemplateTestDbContext(options);
        await context.Database.EnsureCreatedAsync();
        return context;
    }

    private sealed class SqliteTemplateTestDbContext : ApplicationDbContext
    {
        public SqliteTemplateTestDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);

            // SQL Server filtered index syntax is invalid on SQLite.
            foreach (var index in builder.Model.GetEntityTypes().SelectMany(e => e.GetIndexes()))
            {
                if (index.GetFilter() != null)
                    index.SetFilter(null);
            }

            // SQLite has no rowversion type; treat as plain nullable blobs.
            foreach (var entityType in new[] { typeof(WorkOrder), typeof(Dispatch) })
            {
                var property = builder.Entity(entityType).Property("RowVersion").Metadata;
                property.ValueGenerated = Microsoft.EntityFrameworkCore.Metadata.ValueGenerated.Never;
                property.IsConcurrencyToken = false;
            }
        }
    }

    // HTTP contract of the template endpoints.

    private static WorkOrderCompletionController NewController(ApplicationDbContext context, ClaimsPrincipal user) =>
        new(null!, NewService(context), null!)
        {
            ControllerContext = new ControllerContext { HttpContext = new DefaultHttpContext { User = user } }
        };

    [Fact]
    public async Task Controller_MapsTitleRequiredTo422AndForbiddenTo403()
    {
        using var context = NewContext();

        var blank = await NewController(context, Admin())
            .CreateCompletionTemplate(Body("  "), CancellationToken.None);
        var denied = await NewController(context, Dispatcher())
            .CreateCompletionTemplate(Body("Allowed title"), CancellationToken.None);
        var missing = await NewController(context, Admin())
            .DeleteCompletionTemplate(999, CancellationToken.None);

        var unprocessable = Assert.IsType<UnprocessableEntityObjectResult>(blank);
        var error = Assert.IsType<WorkOrderBoardValidationErrorDto>(unprocessable.Value);
        Assert.Equal("TitleRequired", error.Code);
        Assert.Equal("Document Title is required.", error.Message);
        var forbidden = Assert.IsType<ObjectResult>(denied);
        Assert.Equal(StatusCodes.Status403Forbidden, forbidden.StatusCode);
        Assert.IsType<NotFoundObjectResult>(missing);
    }

    [Fact]
    public async Task Controller_LinkedWorkOrdersAndDelete_ReturnContractShapes()
    {
        using var context = NewContext();
        context.CompletionDocTemplates.Add(new CompletionDocTemplate { Id = 10, Name = "Backflow" });
        context.Services.Add(new Service { Id = 1, Name = "B", NormalizedName = "B", RequiresCompletionDocument = true, CompletionDocTemplateId = 10 });
        context.workOrders.Add(new WorkOrder { Id = 101, ServiceId = 1, AccountId = 1, LifecycleStatus = LifecycleStatus.Scheduled });
        await context.SaveChangesAsync();
        var controller = NewController(context, Admin());

        var linked = await controller.GetCompletionTemplateLinkedWorkOrders(10, CancellationToken.None);
        var deleted = await controller.DeleteCompletionTemplate(10, CancellationToken.None);

        var body = Assert.IsType<CompletionDocTemplateLinkedWorkOrdersDto>(Assert.IsType<OkObjectResult>(linked).Value);
        Assert.Equal(1, body.Count);
        Assert.Equal(new[] { 101 }, body.WorkOrderIds);
        Assert.IsType<NoContentResult>(deleted);
    }

    // Migration shape (G6 evidence): additive only.

    [Fact]
    public void Migration_Up_AddsBoundedNoteAndCascadingProcedureTableOnly()
    {
        var operations = BuildOperations("Up");

        var column = Assert.Single(operations.OfType<AddColumnOperation>());
        Assert.Equal("CompletionDocTemplates", column.Table);
        Assert.Equal("ExtraSafetyNote", column.Name);
        Assert.True(column.IsNullable);
        Assert.Equal(2000, column.MaxLength);

        var table = Assert.Single(operations.OfType<CreateTableOperation>());
        Assert.Equal("CompletionDocTemplateProcedures", table.Name);
        Assert.Contains(table.Columns, c => c.Name == "Name" && c.MaxLength == 200 && !c.IsNullable);
        var fk = Assert.Single(table.ForeignKeys);
        Assert.Equal("CompletionDocTemplates", fk.PrincipalTable);
        Assert.Equal(ReferentialAction.Cascade, fk.OnDelete);

        var index = Assert.Single(operations.OfType<CreateIndexOperation>());
        Assert.Equal(new[] { "CompletionDocTemplateId", "SortOrder" }, index.Columns);

        Assert.Equal(3, operations.Count);
    }

    [Fact]
    public void Migration_Down_DropsOnlyWhatUpAdded()
    {
        var operations = BuildOperations("Down");

        Assert.Equal("CompletionDocTemplateProcedures", Assert.Single(operations.OfType<DropTableOperation>()).Name);
        var dropped = Assert.Single(operations.OfType<DropColumnOperation>());
        Assert.Equal(("CompletionDocTemplates", "ExtraSafetyNote"), (dropped.Table, dropped.Name));
        Assert.Equal(2, operations.Count);
    }

    private static List<MigrationOperation> BuildOperations(string direction)
    {
        var builder = new MigrationBuilder("Microsoft.EntityFrameworkCore.SqlServer");
        var operation = typeof(Migration).GetMethod(direction, BindingFlags.Instance | BindingFlags.NonPublic)
                 ?? throw new InvalidOperationException($"Migration.{direction} not found.");
        operation.Invoke(new CompletionDocTemplateProcedures(), [builder]);
        return builder.Operations;
    }
}