using Data.SeaHavenIndustries;
using Data.SeaHavenIndustries.Enums;
using FluentAssertions;
using Microsoft.EntityFrameworkCore;
using SeaHaven.DataServices.Implementation;
using SeaHaven.DataServices.Models;
using System.Text.RegularExpressions;
using Xunit;

namespace Api.SeaHavenIndustries.Tests;

public class VendorDataServiceTests
{
    private static ApplicationDbContext NewContext()
    {
        var options = new DbContextOptionsBuilder<ApplicationDbContext>()
            .UseInMemoryDatabase(Guid.NewGuid().ToString())
            .Options;
        return new ApplicationDbContext(options);
    }

    private static ApplicationDbContext NewSqlServerContext()
    {
        var options = new DbContextOptionsBuilder<ApplicationDbContext>()
            .UseSqlServer("Server=(local);Database=TranslationOnly;Trusted_Connection=True;TrustServerCertificate=True")
            .Options;
        return new ApplicationDbContext(options);
    }

    private static Vendor VendorWithJobs(
        string company,
        string city,
        string state,
        string trades,
        int jobCount,
        bool isActive = true)
    {
        return new Vendor
        {
            CompanyName = company,
            ContactName = $"{company} Technician",
            City = city,
            State = state,
            TradeSpecialties = trades,
            IsActive = isActive,
            IsDeleted = false,
            Dispatches = Enumerable.Range(1, jobCount)
                .Select(workOrderId => new Dispatch { WorkOrderId = workOrderId })
                .ToList()
        };
    }

    private static async Task SeedBoundaryVendors(ApplicationDbContext context)
    {
        context.Vendors.AddRange(
            VendorWithJobs("Under Co", "Chicago", "IL", "Plumbing, HVAC", 49),
            VendorWithJobs("Fifty Co", "Chicago", "IL", "Electrical", 50),
            VendorWithJobs("Hundred Co", "Dallas", "TX", "Plumbing", 100),
            VendorWithJobs("One Fifty Co", "Dallas", "TX", "Roofing", 150));
        await context.SaveChangesAsync();
    }

    [Theory]
    [InlineData("under-50", "Under Co")]
    [InlineData("50-99", "Fifty Co")]
    [InlineData("100-149", "Hundred Co")]
    [InlineData("150-plus", "One Fifty Co")]
    public async Task GetPagedAsync_AppliesExactJobBucketBoundaries(
        string bucket,
        string expectedCompany)
    {
        await using var context = NewContext();
        await SeedBoundaryVendors(context);
        var service = new VendorDataService(context);

        var (items, totalCount) = await service.GetPagedAsync(
            1,
            12,
            isActive: true,
            jobBuckets: new[] { bucket });

        totalCount.Should().Be(1);
        items.Single().CompanyName.Should().Be(expectedCompany);
    }

    [Fact]
    public async Task GetPagedAsync_UsesOrWithinFacetAndAndAcrossFacets()
    {
        await using var context = NewContext();
        await SeedBoundaryVendors(context);
        var service = new VendorDataService(context);

        var (items, totalCount) = await service.GetPagedAsync(
            1,
            12,
            isActive: true,
            companies: new[] { "Under Co", "Hundred Co" },
            trades: new[] { "HVAC", "Plumbing" },
            locations: new[] { "Chicago, IL" },
            jobBuckets: new[] { "under-50", "100-149" });

        totalCount.Should().Be(1);
        items.Single().CompanyName.Should().Be("Under Co");
    }

    [Fact]
    public async Task GetPagedAsync_StatusAndCountAreIndependentOfInactiveHistory()
    {
        await using var context = NewContext();
        context.Vendors.AddRange(
            VendorWithJobs("Active Co", "Chicago", "IL", "Plumbing", 1),
            VendorWithJobs("Inactive Co", "Chicago", "IL", "Plumbing", 1, false));
        await context.SaveChangesAsync();
        var service = new VendorDataService(context);

        var active = await service.GetPagedAsync(1, 1, isActive: true);
        var inactive = await service.GetPagedAsync(1, 1, isActive: false);

        active.TotalCount.Should().Be(1);
        inactive.TotalCount.Should().Be(1);
        active.Items.Single().CompanyName.Should().Be("Active Co");
        inactive.Items.Single().CompanyName.Should().Be("Inactive Co");
    }

    [Fact]
    public async Task GetPagedAsync_LocationFacetMatchesCityStateAndFullLabels()
    {
        await using var context = NewContext();
        context.Vendors.AddRange(
            new Vendor { CompanyName = "CityOnly", City = "Austin", State = null, TradeSpecialties = "Plumbing", IsActive = true, IsDeleted = false },
            new Vendor { CompanyName = "StateOnly", City = null, State = "TX", TradeSpecialties = "Plumbing", IsActive = true, IsDeleted = false },
            new Vendor { CompanyName = "FullLoc", City = "Austin", State = "TX", TradeSpecialties = "Plumbing", IsActive = true, IsDeleted = false });
        await context.SaveChangesAsync();
        var service = new VendorDataService(context);

        var cityOnly = await service.GetPagedAsync(1, 12, isActive: true, locations: new[] { "Austin" });
        cityOnly.TotalCount.Should().Be(1);
        cityOnly.Items.Single().CompanyName.Should().Be("CityOnly");

        var stateOnly = await service.GetPagedAsync(1, 12, isActive: true, locations: new[] { "TX" });
        stateOnly.TotalCount.Should().Be(1);
        stateOnly.Items.Single().CompanyName.Should().Be("StateOnly");

        var full = await service.GetPagedAsync(1, 12, isActive: true, locations: new[] { "Austin, TX" });
        full.TotalCount.Should().Be(1);
        full.Items.Single().CompanyName.Should().Be("FullLoc");
    }

    [Fact]
    public async Task GetPagedAsync_TradeFacetMatchesExactTokensNotSubstrings()
    {
        await using var context = NewContext();
        context.Vendors.AddRange(
            new Vendor { CompanyName = "HvacOnly", City = "Chicago", State = "IL", TradeSpecialties = "HVAC", IsActive = true, IsDeleted = false },
            new Vendor { CompanyName = "HvacControls", City = "Chicago", State = "IL", TradeSpecialties = "HVAC Controls", IsActive = true, IsDeleted = false },
            new Vendor { CompanyName = "PlumbingCo", City = "Chicago", State = "IL", TradeSpecialties = "Plumbing", IsActive = true, IsDeleted = false });
        await context.SaveChangesAsync();
        var service = new VendorDataService(context);

        var hvac = await service.GetPagedAsync(1, 12, isActive: true, trades: new[] { "HVAC" });
        hvac.TotalCount.Should().Be(1);
        hvac.Items.Single().CompanyName.Should().Be("HvacOnly");

        var hvacControls = await service.GetPagedAsync(1, 12, isActive: true, trades: new[] { "HVAC Controls" });
        hvacControls.TotalCount.Should().Be(1);
        hvacControls.Items.Single().CompanyName.Should().Be("HvacControls");

        var plumb = await service.GetPagedAsync(1, 12, isActive: true, trades: new[] { "Plumb" });
        plumb.TotalCount.Should().Be(0);
        plumb.Items.Should().BeEmpty();

        var plumbing = await service.GetPagedAsync(1, 12, isActive: true, trades: new[] { "Plumbing" });
        plumbing.TotalCount.Should().Be(1);
        plumbing.Items.Single().CompanyName.Should().Be("PlumbingCo");
    }

    [Fact]
    public async Task GetPagedAsync_TradeFacetMatchesTokenInCommaDelimitedList()
    {
        await using var context = NewContext();
        context.Vendors.AddRange(
            new Vendor { CompanyName = "Multi", City = "Chicago", State = "IL", TradeSpecialties = "Plumbing, HVAC", IsActive = true, IsDeleted = false },
            new Vendor { CompanyName = "Middle", City = "Chicago", State = "IL", TradeSpecialties = "Electrical, Plumbing, Roofing", IsActive = true, IsDeleted = false });
        await context.SaveChangesAsync();
        var service = new VendorDataService(context);

        var plumbing = await service.GetPagedAsync(1, 12, isActive: true, trades: new[] { "Plumbing" });
        plumbing.TotalCount.Should().Be(2);
        plumbing.Items.Select(v => v.CompanyName).Should().BeEquivalentTo("Multi", "Middle");

        var roofing = await service.GetPagedAsync(1, 12, isActive: true, trades: new[] { "Roofing" });
        roofing.TotalCount.Should().Be(1);
        roofing.Items.Single().CompanyName.Should().Be("Middle");
    }

    [Fact]
    public async Task GetPagedAsync_CountsDistinctDirectAndLinkedWorkOrders()
    {
        await using var context = NewContext();
        var vendor = new Vendor
        {
            CompanyName = "History Co",
            IsActive = true,
            IsDeleted = false,
            Dispatches = new List<Dispatch>
            {
                new()
                {
                    WorkOrderId = 100,
                    DispatchWorkOrders = new List<DispatchWorkOrder>
                    {
                        new() { WorkOrderId = 100 },
                        new() { WorkOrderId = 101 }
                    }
                },
                new() { WorkOrderId = 101 }
            }
        };
        context.Vendors.Add(vendor);
        await context.SaveChangesAsync();

        var service = new VendorDataService(context);
        var result = await service.GetPagedAsync(1, 12, isActive: true);

        result.Items.Single().TotalJobs.Should().Be(2);
    }

    [Fact]
    public async Task GetByIdAsync_ReturnsTrackedVendorWithoutLoadingDispatchGraph()
    {
        await using var context = NewContext();
        var vendor = VendorWithJobs("Lean Co", "Chicago", "IL", "Plumbing", 2);
        context.Vendors.Add(vendor);
        await context.SaveChangesAsync();
        context.ChangeTracker.Clear();

        var service = new VendorDataService(context);
        var result = await service.GetByIdAsync(vendor.Id);

        result.Should().NotBeNull();
        context.Entry(result!).State.Should().Be(EntityState.Unchanged);
        context.Entry(result!).Collection(nameof(Vendor.Dispatches)).IsLoaded.Should().BeFalse();
        result!.Dispatches.Should().BeNull();
    }

    [Theory]
    [InlineData("trade")]
    [InlineData("location")]
    [InlineData("address-location")]
    [InlineData("job-bucket")]
    public void BuildDirectoryQuery_TranslatesRepresentativeFiltersForSqlServer(string filter)
    {
        using var context = NewSqlServerContext();
        var service = new VendorDataService(context);

        var query = filter switch
        {
            "trade" => service.BuildDirectoryQuery(trades: new[] { "Plumbing" }),
            "location" => service.BuildDirectoryQuery(locations: new[] { "Chicago, IL" }),
            "address-location" => service.BuildDirectoryQuery(locations: new[] { "123 Main St" }),
            _ => service.BuildDirectoryQuery(jobBuckets: new[] { "50-99" })
        };

        var sql = query.ToQueryString();

        sql.Should().Contain("SELECT");
        if (filter == "trade")
            sql.Should().Contain("TradeSpecialties");
        if (filter == "location")
            sql.Should().Contain("City");
        if (filter == "address-location")
            sql.Should().Contain("Address");
        if (filter == "job-bucket")
            sql.Should().Contain("UNION");
    }

    [Fact]
    public void BuildLinkedWorkOrdersQuery_TranslatesForSqlServerWithoutClientEvaluation()
    {
        using var context = NewSqlServerContext();
        var service = new VendorDataService(context);

        var sql = service.BuildLinkedWorkOrdersQuery(60).ToQueryString();

        sql.Should().Contain("SELECT");
        sql.Should().Contain("Dispatches");
        sql.Should().Contain("DispatchWorkOrders");
        sql.Should().Contain("workOrders");
    }

    [Fact]
    public async Task GetLinkedWorkOrdersAsync_DedupsToOneRecordPerWorkOrderAcrossDirectAndMultiLinks()
    {
        await using var context = NewContext();
        var vendor = new Vendor { Id = 60, CompanyName = "Linked Co", IsActive = true };
        context.Vendors.Add(vendor);
        context.workOrders.AddRange(
            new WorkOrder
            {
                Id = 701,
                WorkerOrderNumber = "WO-701",
                WorkerOrderTitle = "Direct And Multi",
                Status = "Scheduled",
                LifecycleStatus = LifecycleStatus.Scheduled
            },
            new WorkOrder
            {
                Id = 702,
                WorkerOrderNumber = "WO-702",
                WorkerOrderTitle = "Multi Only",
                Status = "Scheduled",
                LifecycleStatus = LifecycleStatus.Scheduled
            });

        var directDispatch = new Dispatch { Id = 7001, VendorId = 60, WorkOrderId = 701, Vendor = vendor };
        var multiDispatch = new Dispatch { Id = 7002, VendorId = 60, WorkOrderId = null, Vendor = vendor };
        context.Dispatches.AddRange(directDispatch, multiDispatch);
        context.DispatchWorkOrders.AddRange(
            new DispatchWorkOrder { DispatchId = 7002, Dispatch = multiDispatch, WorkOrderId = 702 },
            new DispatchWorkOrder { DispatchId = 7002, Dispatch = multiDispatch, WorkOrderId = 701 });
        await context.SaveChangesAsync();

        var service = new VendorDataService(context);
        var result = await service.GetLinkedWorkOrdersAsync(60);

        result.Should().HaveCount(2);
        result.Select(x => x.WorkOrderId).Should().BeEquivalentTo(new[] { 701, 702 });
        result.Select(x => x.WorkOrderId).Should().OnlyHaveUniqueItems();
    }

    [Fact]
    public async Task GetCompanyDirectoryPagedAsync_GroupsCompaniesAndKeepsLegacyRowsDistinct()
    {
        await using var context = NewContext();
        var linkedCompany = new VendorCompany
        {
            Name = "Linked Co",
            NormalizedName = "linked co",
            Email = "dispatch@linked.example",
            CompanyPhone = "555-0100",
            Address = "100 Company Way",
            City = "Chicago",
            State = "IL"
        };
        var companyOnly = new VendorCompany { Name = "Company Only", NormalizedName = "company only" };
        context.VendorCompanies.AddRange(linkedCompany, companyOnly);
        context.Vendors.AddRange(
            new Vendor
            {
                Company = linkedCompany,
                CompanyName = "Stale Linked Name",
                ContactName = "Older Tech",
                Email = "older@linked.example",
                Phone = "555-0101",
                TradeSpecialties = "Plumbing",
                IsActive = true,
                IsDeleted = false,
                Dispatches = new List<Dispatch> { new() { WorkOrderId = 101 } }
            },
            new Vendor
            {
                Company = linkedCompany,
                CompanyName = "Another Stale Name",
                ContactName = "Newer Tech",
                Email = "newer@linked.example",
                Phone = "555-0102",
                TradeSpecialties = "HVAC, plumbing",
                IsActive = true,
                IsDeleted = false,
                Dispatches = new List<Dispatch> { new() { WorkOrderId = 102 } }
            },
            new Vendor { CompanyName = " Legacy Co ", ContactName = "Legacy One", IsActive = true, IsDeleted = false },
            new Vendor { CompanyName = "legacy co", ContactName = "Legacy Two", IsActive = true, IsDeleted = false },
            new Vendor { CompanyName = " ", ContactName = "Unnamed One", IsActive = true, IsDeleted = false },
            new Vendor { CompanyName = null, ContactName = "Unnamed Two", IsActive = true, IsDeleted = false });
        await context.SaveChangesAsync();

        var service = new VendorDataService(context);
        var (items, totalCount) = await service.GetCompanyDirectoryPagedAsync(1, 50, isActive: true);

        totalCount.Should().Be(5);
        var linked = items.Single(item => item.CompanyId == linkedCompany.Id);
        linked.CompanyName.Should().Be("Linked Co");
        linked.Email.Should().Be("dispatch@linked.example");
        linked.ContactName.Should().Be("Newer Tech");
        linked.Technicians.Should().HaveCount(2);
        linked.TotalJobs.Should().Be(2);
        linked.TradeSpecialties.Should().Be("HVAC, plumbing");

        var canonicalSearch = await service.GetCompanyDirectoryPagedAsync(
            1, 50, search: "Linked Co", isActive: true);
        canonicalSearch.Items.Should().ContainSingle(item => item.CompanyId == linkedCompany.Id);

        var canonicalCompanyFilter = await service.GetCompanyDirectoryPagedAsync(
            1, 50, isActive: true, companies: new[] { "Linked Co" });
        canonicalCompanyFilter.Items.Should().ContainSingle(item => item.CompanyId == linkedCompany.Id);

        var legacy = items.Single(item =>
            item.Technicians.Any(tech => tech.ContactName == "Legacy One"));
        legacy.Technicians.Select(tech => tech.ContactName)
            .Should().BeEquivalentTo("Legacy One", "Legacy Two");
        items.Count(item => item.Technicians.Count == 1 &&
            item.Technicians.Single().ContactName?.StartsWith("Unnamed") == true).Should().Be(2);
        items.Single(item => item.CompanyId == companyOnly.Id).Technicians.Should().BeEmpty();
    }

    [Fact]
    public async Task GetCompanyDirectoryPagedAsync_FilterSelectsGroupAndSearchPromotesMatchingTechnician()
    {
        await using var context = NewContext();
        var company = new VendorCompany { Name = "Search Co", NormalizedName = "search co" };
        context.VendorCompanies.Add(company);
        var older = new Vendor
        {
            Company = company,
            CompanyName = company.Name,
            ContactName = "Alice Plumber",
            TradeSpecialties = "Plumbing",
            IsActive = true,
            IsDeleted = false,
            Dispatches = new List<Dispatch> { new() { WorkOrderId = 201 } }
        };
        var newer = new Vendor
        {
            Company = company,
            CompanyName = company.Name,
            ContactName = "Zed HVAC",
            TradeSpecialties = "HVAC",
            IsActive = true,
            IsDeleted = false,
            Dispatches = new List<Dispatch> { new() { WorkOrderId = 202 } }
        };
        var inactive = new Vendor
        {
            Company = company,
            CompanyName = company.Name,
            ContactName = "Inactive Tech",
            TradeSpecialties = "Roofing",
            IsActive = false,
            IsDeleted = false,
            Dispatches = new List<Dispatch> { new() { WorkOrderId = 203 } }
        };
        context.Vendors.AddRange(older, newer, inactive);
        await context.SaveChangesAsync();

        var service = new VendorDataService(context);
        var search = await service.GetCompanyDirectoryPagedAsync(1, 10, search: "Alice", isActive: true);
        var trade = await service.GetCompanyDirectoryPagedAsync(
            1, 10, isActive: true, trades: new[] { "Plumbing" });

        search.TotalCount.Should().Be(1);
        search.Items.Single().Id.Should().Be(older.Id);
        search.Items.Single().ContactName.Should().Be("Alice Plumber");
        search.Items.Single().Technicians.Should().HaveCount(2);
        search.Items.Single().TotalJobs.Should().Be(2);

        trade.TotalCount.Should().Be(1);
        trade.Items.Single().Id.Should().Be(newer.Id);
        trade.Items.Single().Technicians.Should().HaveCount(2);
        trade.Items.Single().Technicians.Should().NotContain(tech => tech.Id == inactive.Id);
    }

    [Fact]
    public void BuildCompanyDirectoryKeysQuery_TranslatesGroupingAndFacetFiltersToSqlServer()
    {
        using var context = NewSqlServerContext();
        var service = new VendorDataService(context);

        var sql = service.BuildCompanyDirectoryKeysQuery(
            search: "alice",
            trades: new[] { "Plumbing" },
            locations: new[] { "Chicago, IL" },
            jobBuckets: new[] { "under-50" },
            areaFilter: new VendorAreaFilter { AreaIds = new[] { 1, 2 }, IncludeUnassigned = true }).ToQueryString();

        sql.Should().Contain("GROUP BY");
        sql.Should().Contain("LEFT JOIN");
        sql.Should().Contain("TradeSpecialties");
        sql.Should().Contain("TotalJobs");
        sql.Should().Contain("AreaId");
        Regex.IsMatch(sql, @"SUM\s*\(\s*\(\s*SELECT", RegexOptions.IgnoreCase)
            .Should().BeFalse("company totals must sum pre-aggregated scalar job counts");
    }

    [Fact]
    public async Task GetDirectoryPagedAsync_IncludesCompanyOnlyRowsAndPreservesTechnicianRows()
    {
        await using var context = NewContext();
        var soloCompany = new VendorCompany { Name = "Solo Co", NormalizedName = "solo co", Notes = "company notes" };
        var techCompany = new VendorCompany { Name = "Tech Co", NormalizedName = "tech co" };
        context.VendorCompanies.AddRange(soloCompany, techCompany);
        context.Vendors.AddRange(
            new Vendor
            {
                CompanyName = "Tech Co",
                ContactName = "Pat",
                City = "Austin",
                State = "TX",
                IsActive = true,
                IsDeleted = false,
                Company = techCompany
            },
            new Vendor
            {
                CompanyName = "Other",
                ContactName = "No Company Tech",
                IsActive = true,
                IsDeleted = false,
                CompanyId = null
            });
        await context.SaveChangesAsync();

        var service = new VendorDataService(context);
        var (items, totalCount) = await service.GetDirectoryPagedAsync(1, 50, isActive: true);

        totalCount.Should().Be(3);
        var companyOnly = items.Single(i => i.CompanyName == "Solo Co");
        companyOnly.VendorId.Should().BeNull();
        companyOnly.CompanyId.Should().Be(soloCompany.Id);
        companyOnly.Notes.Should().Be("company notes");

        items.Should().Contain(i => i.ContactName == "Pat" && i.VendorId != null && i.CompanyId == techCompany.Id);
        items.Should().Contain(i => i.ContactName == "No Company Tech" && i.VendorId != null);
    }

    [Fact]
    public async Task GetDirectoryPagedAsync_UsesCanonicalCompanyLocationAndEmailFallback()
    {
        await using var context = NewContext();
        var company = new VendorCompany
        {
            Name = "Canonical Co",
            NormalizedName = "canonical co",
            Email = "dispatch@canonical.example",
            City = "Chicago",
            State = "IL"
        };
        context.VendorCompanies.Add(company);
        context.Vendors.AddRange(
            new Vendor
            {
                CompanyName = "Canonical Co",
                ContactName = "Company Fallback",
                Email = "   ",
                City = "Stale City",
                State = "TX",
                IsActive = true,
                IsDeleted = false,
                Company = company
            },
            new Vendor
            {
                CompanyName = "Canonical Co",
                ContactName = "Technician Email",
                Email = "technician@canonical.example",
                IsActive = true,
                IsDeleted = false,
                Company = company
            },
            new Vendor
            {
                CompanyName = "Legacy Co",
                ContactName = "Unlinked Technician",
                Email = "legacy@example.com",
                City = "Austin",
                State = "TX",
                IsActive = true,
                IsDeleted = false,
                CompanyId = null
            });
        await context.SaveChangesAsync();

        var service = new VendorDataService(context);
        var (items, totalCount) = await service.GetDirectoryPagedAsync(1, 50, isActive: true);

        totalCount.Should().Be(3);
        var companyFallback = items.Single(i => i.ContactName == "Company Fallback");
        companyFallback.Email.Should().Be("dispatch@canonical.example");
        companyFallback.City.Should().Be("Chicago");
        companyFallback.State.Should().Be("IL");

        var technicianEmail = items.Single(i => i.ContactName == "Technician Email");
        technicianEmail.Email.Should().Be("technician@canonical.example");
        technicianEmail.City.Should().Be("Chicago");
        technicianEmail.State.Should().Be("IL");

        var legacy = items.Single(i => i.ContactName == "Unlinked Technician");
        legacy.Email.Should().Be("legacy@example.com");
        legacy.City.Should().Be("Austin");
        legacy.State.Should().Be("TX");
    }

    [Fact]
    public async Task GetDirectoryPagedAsync_SearchAndLocationFilterUseCanonicalCompanyLocation()
    {
        await using var context = NewContext();
        var company = new VendorCompany
        {
            Name = "Searchable Co",
            NormalizedName = "searchable co",
            City = "Chicago",
            State = null
        };
        context.VendorCompanies.Add(company);
        context.Vendors.Add(new Vendor
        {
            CompanyName = "Searchable Co",
            ContactName = "Pat",
            City = "Stale City",
            State = "TX",
            IsActive = true,
            IsDeleted = false,
            Company = company
        });
        await context.SaveChangesAsync();

        var service = new VendorDataService(context);
        var search = await service.GetDirectoryPagedAsync(1, 50, search: "chicago", isActive: true);
        var location = await service.GetDirectoryPagedAsync(
            1,
            50,
            isActive: true,
            locations: new[] { "Chicago" });

        var searchRow = search.Items.Should().ContainSingle(i => i.ContactName == "Pat").Subject;
        searchRow.City.Should().Be("Chicago");
        searchRow.State.Should().BeNull();

        var locationRow = location.Items.Should().ContainSingle(i => i.ContactName == "Pat").Subject;
        locationRow.City.Should().Be("Chicago");
        locationRow.State.Should().BeNull();
    }

    [Fact]
    public async Task GetDirectoryPagedAsync_UsesCanonicalCompanyAddressAndCompanyPhoneWithVendorFallback()
    {
        await using var context = NewContext();
        var fullCompany = new VendorCompany
        {
            Name = "Full Co",
            NormalizedName = "full co",
            Address = "100 Company Way",
            CompanyPhone = "(555) 111-2222"
        };
        var blankCompany = new VendorCompany
        {
            Name = "Blank Co",
            NormalizedName = "blank co",
            Address = "   ",
            CompanyPhone = ""
        };
        context.VendorCompanies.AddRange(fullCompany, blankCompany);
        context.Vendors.AddRange(
            new Vendor
            {
                CompanyName = "Full Co",
                ContactName = "Full Tech",
                Phone = "(555) 222-3333",
                CompanyPhone = "(555) 999-0000",
                Address = "9 Old Street",
                IsActive = true,
                IsDeleted = false,
                Company = fullCompany
            },
            new Vendor
            {
                CompanyName = "Blank Co",
                ContactName = "Blank Tech",
                CompanyPhone = "(555) 333-4444",
                Address = "200 Vendor Way",
                IsActive = true,
                IsDeleted = false,
                Company = blankCompany
            },
            new Vendor
            {
                CompanyName = "Legacy Co",
                ContactName = "Unlinked Tech",
                CompanyPhone = "(555) 777-8888",
                Address = "300 Legacy Way",
                IsActive = true,
                IsDeleted = false,
                CompanyId = null
            });
        await context.SaveChangesAsync();

        var service = new VendorDataService(context);
        var (items, totalCount) = await service.GetDirectoryPagedAsync(1, 50, isActive: true);

        totalCount.Should().Be(3);
        var fullTech = items.Single(i => i.ContactName == "Full Tech");
        fullTech.Address.Should().Be("100 Company Way");
        fullTech.CompanyPhone.Should().Be("(555) 111-2222");
        fullTech.Phone.Should().Be("(555) 222-3333");

        var blankTech = items.Single(i => i.ContactName == "Blank Tech");
        blankTech.Address.Should().Be("200 Vendor Way");
        blankTech.CompanyPhone.Should().Be("(555) 333-4444");

        var legacy = items.Single(i => i.ContactName == "Unlinked Tech");
        legacy.Address.Should().Be("300 Legacy Way");
        legacy.CompanyPhone.Should().Be("(555) 777-8888");
    }

    [Fact]
    public async Task GetDirectoryPagedAsync_SearchIncludesProjectedAddress()
    {
        await using var context = NewContext();
        var company = new VendorCompany
        {
            Name = "Addr Co",
            NormalizedName = "addr co",
            Address = "123 Main St"
        };
        context.VendorCompanies.Add(company);
        context.Vendors.Add(new Vendor
        {
            CompanyName = "Addr Co",
            ContactName = "Pat",
            Address = "9 Old Street",
            City = "Oldtown",
            State = "OT",
            IsActive = true,
            IsDeleted = false,
            Company = company
        });
        await context.SaveChangesAsync();

        var service = new VendorDataService(context);

        var search = await service.GetDirectoryPagedAsync(1, 50, search: "123 main", isActive: true);
        search.TotalCount.Should().Be(1);
        search.Items.Single().ContactName.Should().Be("Pat");

        var stale = await service.GetDirectoryPagedAsync(1, 50, search: "9 old street", isActive: true);
        stale.TotalCount.Should().Be(0);

        var staleCity = await service.GetDirectoryPagedAsync(1, 50, search: "oldtown", isActive: true);
        staleCity.TotalCount.Should().Be(0);
    }

    [Fact]
    public async Task GetDirectoryPagedAsync_LocationFilterFallsBackToExactAddressWhenCityStateMissing()
    {
        await using var context = NewContext();
        var addressOnlyCompany = new VendorCompany
        {
            Name = "Addr Only Co",
            NormalizedName = "addr only co",
            Address = " 123 Main St "
        };
        var soloCompany = new VendorCompany
        {
            Name = "Solo Co",
            NormalizedName = "solo co",
            Address = "500 Solo Way"
        };
        context.VendorCompanies.AddRange(addressOnlyCompany, soloCompany);
        context.Vendors.Add(new Vendor
        {
            CompanyName = "Addr Only Co",
            ContactName = "Pat",
            City = "Oldtown",
            State = "OT",
            IsActive = true,
            IsDeleted = false,
            Company = addressOnlyCompany
        });
        await context.SaveChangesAsync();

        var service = new VendorDataService(context);

        var technicianRow = await service.GetDirectoryPagedAsync(
            1, 50, isActive: true, locations: new[] { "123 Main St" });
        technicianRow.TotalCount.Should().Be(1);
        technicianRow.Items.Single().ContactName.Should().Be("Pat");

        var companyOnlyRow = await service.GetDirectoryPagedAsync(
            1, 50, isActive: true, locations: new[] { "500 Solo Way" });
        companyOnlyRow.TotalCount.Should().Be(1);
        companyOnlyRow.Items.Single().CompanyName.Should().Be("Solo Co");

        var partial = await service.GetDirectoryPagedAsync(
            1, 50, isActive: true, locations: new[] { "123 Main" });
        partial.TotalCount.Should().Be(0);

        var staleLocation = await service.GetDirectoryPagedAsync(
            1, 50, isActive: true, locations: new[] { "Oldtown, OT" });
        staleLocation.TotalCount.Should().Be(0);
    }

    [Fact]
    public async Task GetDirectoryPagedAsync_LocationLabelPrefersCityStateOverAddress()
    {
        await using var context = NewContext();
        context.Vendors.AddRange(
            new Vendor
            {
                CompanyName = "City Co",
                ContactName = "City Tech",
                City = "Austin",
                Address = "999 Address Way",
                IsActive = true,
                IsDeleted = false
            },
            new Vendor
            {
                CompanyName = "State Co",
                ContactName = "State Tech",
                State = "TX",
                Address = "888 Address Way",
                IsActive = true,
                IsDeleted = false
            });
        await context.SaveChangesAsync();

        var service = new VendorDataService(context);

        var byCity = await service.GetDirectoryPagedAsync(1, 50, isActive: true, locations: new[] { "Austin" });
        byCity.TotalCount.Should().Be(1);
        byCity.Items.Single().CompanyName.Should().Be("City Co");

        var byState = await service.GetDirectoryPagedAsync(1, 50, isActive: true, locations: new[] { "TX" });
        byState.TotalCount.Should().Be(1);
        byState.Items.Single().CompanyName.Should().Be("State Co");

        var bySuppressedAddress = await service.GetDirectoryPagedAsync(
            1, 50, isActive: true, locations: new[] { "999 Address Way" });
        bySuppressedAddress.TotalCount.Should().Be(0);
    }

    [Theory]
    [InlineData("trade")]
    [InlineData("location")]
    [InlineData("address-location")]
    [InlineData("job-bucket")]
    public void BuildDirectoryUnionQuery_TranslatesToSqlServerUnion(string filter)
    {
        using var context = NewSqlServerContext();
        var service = new VendorDataService(context);

        var query = filter switch
        {
            "trade" => service.BuildDirectoryUnionQuery(trades: new[] { "Plumbing" }),
            "location" => service.BuildDirectoryUnionQuery(locations: new[] { "Chicago, IL" }),
            "address-location" => service.BuildDirectoryUnionQuery(locations: new[] { "123 Main St" }),
            _ => service.BuildDirectoryUnionQuery(jobBuckets: new[] { "under-50" })
        };

        var sql = query.ToQueryString();

        sql.Should().Contain("SELECT");
        sql.Should().Contain("UNION");
        if (filter == "trade")
            sql.Should().Contain("TradeSpecialties");
        if (filter == "location")
            sql.Should().Contain("City");
        if (filter == "address-location")
            sql.Should().Contain("Address");
        if (filter == "job-bucket")
            sql.Should().Contain("TotalJobs");
    }

    [Fact]
    public void BuildDirectoryUnionQuery_TranslatesAddressSearchForSqlServer()
    {
        using var context = NewSqlServerContext();
        var service = new VendorDataService(context);

        var sql = service.BuildDirectoryUnionQuery(search: "123 main").ToQueryString();

        sql.Should().Contain("SELECT");
        sql.Should().Contain("UNION");
        sql.Should().Contain("Address");
    }

    [Fact]
    public async Task GetCompaniesForFacetsAsync_IncludesZeroTechnicianCompanyForActiveAndNullStatus()
    {
        await using var context = NewContext();
        var rosterService = new VendorCompanyRosterDataService(context);
        await rosterService.CreateRosterAsync(new VendorCompanyRosterWriteModel
        {
            Name = "Solo Co",
            Email = "solo@example.com",
            ActorUserId = "42",
            Technicians = new List<RosterTechnicianWriteModel>()
        }, CancellationToken.None);

        var techCompany = new VendorCompany { Name = "Tech Co", NormalizedName = "tech co" };
        context.VendorCompanies.Add(techCompany);
        context.Vendors.Add(new Vendor
        {
            CompanyName = "Tech Co",
            ContactName = "Pat",
            IsActive = true,
            IsDeleted = false,
            Company = techCompany
        });
        await context.SaveChangesAsync();

        var service = new VendorDataService(context);

        var active = await service.GetCompaniesForFacetsAsync(true, CancellationToken.None);
        active.Select(c => c.Name).Should().BeEquivalentTo(new[] { "Solo Co", "Tech Co" });

        var noStatus = await service.GetCompaniesForFacetsAsync(null, CancellationToken.None);
        noStatus.Select(c => c.Name).Should().BeEquivalentTo(new[] { "Solo Co", "Tech Co" });

        var inactive = await service.GetCompaniesForFacetsAsync(false, CancellationToken.None);
        inactive.Should().BeEmpty();
    }

    [Fact]
    public async Task GetCompaniesForFacetsAsync_InactiveStatusReturnsOnlyCompaniesWithInactiveTechnicians()
    {
        await using var context = NewContext();
        var activeOnlyCo = new VendorCompany { Name = "Active Only Co", NormalizedName = "active only co" };
        var inactiveOnlyCo = new VendorCompany { Name = "Inactive Only Co", NormalizedName = "inactive only co" };
        var mixedCo = new VendorCompany { Name = "Mixed Co", NormalizedName = "mixed co" };
        context.VendorCompanies.AddRange(activeOnlyCo, inactiveOnlyCo, mixedCo);
        context.Vendors.AddRange(
            new Vendor { CompanyName = "Active Only Co", IsActive = true, IsDeleted = false, Company = activeOnlyCo },
            new Vendor { CompanyName = "Inactive Only Co", IsActive = false, IsDeleted = false, Company = inactiveOnlyCo },
            new Vendor { CompanyName = "Mixed Co", IsActive = true, IsDeleted = false, Company = mixedCo },
            new Vendor { CompanyName = "Mixed Co", IsActive = false, IsDeleted = false, Company = mixedCo });
        await context.SaveChangesAsync();

        var service = new VendorDataService(context);

        var inactive = await service.GetCompaniesForFacetsAsync(false, CancellationToken.None);
        inactive.Select(c => c.Name).Should().BeEquivalentTo(new[] { "Inactive Only Co", "Mixed Co" });

        var active = await service.GetCompaniesForFacetsAsync(true, CancellationToken.None);
        active.Select(c => c.Name).Should().BeEquivalentTo(new[] { "Active Only Co", "Mixed Co" });

        var noStatus = await service.GetCompaniesForFacetsAsync(null, CancellationToken.None);
        noStatus.Select(c => c.Name).Should().BeEquivalentTo(new[] { "Active Only Co", "Inactive Only Co", "Mixed Co" });
    }

    [Fact]
    public async Task GetCompaniesForFacetsAsync_TreatsCompanyWithOnlyDeletedTechniciansAsZeroTechnician()
    {
        await using var context = NewContext();
        var company = new VendorCompany { Name = "Ghost Co", NormalizedName = "ghost co" };
        context.VendorCompanies.Add(company);
        context.Vendors.Add(new Vendor
        {
            CompanyName = "Ghost Co",
            IsActive = true,
            IsDeleted = true,
            Company = company
        });
        await context.SaveChangesAsync();

        var service = new VendorDataService(context);

        var active = await service.GetCompaniesForFacetsAsync(true, CancellationToken.None);
        active.Single().Name.Should().Be("Ghost Co");

        var inactive = await service.GetCompaniesForFacetsAsync(false, CancellationToken.None);
        inactive.Should().BeEmpty();
    }

    [Fact]
    public async Task GetActiveTradesAsync_ReturnsEmptyOnEmptyDatabase()
    {
        await using var context = NewContext();
        var service = new VendorDataService(context);

        var trades = await service.GetActiveTradesAsync(CancellationToken.None);

        trades.Should().BeEmpty();
    }

    [Fact]
    public async Task GetActiveTradesAsync_FiltersInactiveAndOrdersBySortOrderThenName()
    {
        await using var context = NewContext();
        context.Trades.AddRange(
            new Trade { Name = "Janitorial", NormalizedName = "janitorial", SortOrder = 7 },
            new Trade { Name = "Plumbing", NormalizedName = "plumbing", SortOrder = 1 },
            new Trade { Name = "Electrical", NormalizedName = "electrical", SortOrder = 2 },
            new Trade { Name = "Retired Trade", NormalizedName = "retired trade", SortOrder = 0, IsActive = false });
        await context.SaveChangesAsync();
        var service = new VendorDataService(context);

        var trades = await service.GetActiveTradesAsync(CancellationToken.None);

        trades.Select(t => t.Name).Should().Equal("Plumbing", "Electrical", "Janitorial");
    }

    [Fact]
    public async Task GetPagedAsync_TradeFilterStillMatchesLegacyFreeTextValues()
    {
        await using var context = NewContext();
        context.Vendors.AddRange(
            VendorWithJobs("Legacy Co", "Chicago", "IL", "Backflow Testing, Plumbing", 3),
            VendorWithJobs("Canonical Co", "Chicago", "IL", "Plumbing", 5));
        await context.SaveChangesAsync();
        var service = new VendorDataService(context);

        // SH-249: stored TradeSpecialties stay free text; filtering by a legacy
        // value that is not in the canonical catalog must keep matching.
        var (items, totalCount) = await service.GetPagedAsync(
            1,
            12,
            isActive: true,
            trades: new[] { "Backflow Testing" });

        totalCount.Should().Be(1);
        items.Single().CompanyName.Should().Be("Legacy Co");
    }

    [Fact]
    public async Task GetDirectoryPagedAsync_TradeFilterStillMatchesLegacyFreeTextValues()
    {
        await using var context = NewContext();
        context.Vendors.AddRange(
            VendorWithJobs("Legacy Co", "Chicago", "IL", "Backflow Testing, Plumbing", 3),
            VendorWithJobs("Canonical Co", "Chicago", "IL", "Plumbing", 5));
        await context.SaveChangesAsync();
        var service = new VendorDataService(context);

        var (items, totalCount) = await service.GetDirectoryPagedAsync(
            1,
            12,
            isActive: true,
            trades: new[] { "Backflow Testing" });

        totalCount.Should().Be(1);
        items.Single().CompanyName.Should().Be("Legacy Co");
    }

    [Theory]
    [InlineData(true)]
    [InlineData(false)]
    [InlineData(null)]
    public void BuildCompaniesForFacetsQuery_TranslatesToSqlServerWithoutClientEvaluation(bool? isActive)
    {
        using var context = NewSqlServerContext();
        var service = new VendorDataService(context);

        var sql = service.BuildCompaniesForFacetsQuery(isActive).ToQueryString();

        sql.Should().Contain("SELECT");
        sql.Should().Contain("VendorCompanies");
    }

    [Fact]
    public async Task GetCompanyDirectoryPagedAsync_AreaFilterOrsWithinFacetAndUnassignedIncludesLegacyRows()
    {
        await using var context = NewContext();
        var east = new Area { Id = 1, Name = "East", NormalizedName = "east" };
        var west = new Area { Id = 3, Name = "West", NormalizedName = "west" };
        context.Areas.AddRange(east, west);
        var eastCo = new VendorCompany { Name = "East Co", NormalizedName = "east co", Area = east };
        var westCo = new VendorCompany { Name = "West Co", NormalizedName = "west co", Area = west };
        var unassignedCo = new VendorCompany { Name = "Unassigned Co", NormalizedName = "unassigned co" };
        var eastOnly = new VendorCompany { Name = "East Only", NormalizedName = "east only", Area = east };
        context.VendorCompanies.AddRange(eastCo, westCo, unassignedCo, eastOnly);
        context.Vendors.AddRange(
            new Vendor { Company = eastCo, CompanyName = "East Co", ContactName = "E", City = "Boston", State = "MA", IsActive = true, IsDeleted = false },
            new Vendor { Company = westCo, CompanyName = "West Co", ContactName = "W", IsActive = true, IsDeleted = false },
            new Vendor { Company = unassignedCo, CompanyName = "Unassigned Co", ContactName = "U", IsActive = true, IsDeleted = false },
            new Vendor { CompanyName = "Legacy Co", ContactName = "L", IsActive = true, IsDeleted = false });
        await context.SaveChangesAsync();
        var service = new VendorDataService(context);

        var eastResult = await service.GetCompanyDirectoryPagedAsync(
            1, 50, isActive: true, areaFilter: new VendorAreaFilter { AreaIds = new[] { east.Id } });
        eastResult.Items.Select(item => item.CompanyName)
            .Should().BeEquivalentTo("East Co", "East Only");
        eastResult.TotalCount.Should().Be(2);
        var eastRow = eastResult.Items.Single(item => item.CompanyName == "East Co");
        eastRow.AreaId.Should().Be(east.Id);
        eastRow.AreaName.Should().Be("East");
        eastResult.Items.Single(item => item.CompanyName == "East Only").AreaName.Should().Be("East");

        var eastOrUnassigned = await service.GetCompanyDirectoryPagedAsync(
            1, 50, isActive: true,
            areaFilter: new VendorAreaFilter { AreaIds = new[] { east.Id }, IncludeUnassigned = true });
        eastOrUnassigned.Items.Select(item => item.CompanyName)
            .Should().BeEquivalentTo("East Co", "East Only", "Unassigned Co", "Legacy Co");
        eastOrUnassigned.Items.Single(item => item.CompanyName == "Legacy Co").AreaId.Should().BeNull();

        var eastAndBoston = await service.GetCompanyDirectoryPagedAsync(
            1, 50, isActive: true, locations: new[] { "Boston, MA" },
            areaFilter: new VendorAreaFilter { AreaIds = new[] { east.Id } });
        eastAndBoston.Items.Should().ContainSingle(item => item.CompanyName == "East Co");

        var matchesNobody = await service.GetCompanyDirectoryPagedAsync(
            1, 50, isActive: true, areaFilter: new VendorAreaFilter());
        matchesNobody.TotalCount.Should().Be(0);
        matchesNobody.Items.Should().BeEmpty();

        var unfiltered = await service.GetCompanyDirectoryPagedAsync(1, 50, isActive: true);
        unfiltered.TotalCount.Should().Be(5);
    }

    [Fact]
    public async Task GetActiveAreasAsync_ExcludesArchivedAreas()
    {
        await using var context = NewContext();
        context.Areas.AddRange(
            new Area { Id = 1, Name = "East", NormalizedName = "east" },
            new Area { Id = 4, Name = "California", NormalizedName = "california", IsActive = false });
        await context.SaveChangesAsync();

        var areas = await new VendorDataService(context).GetActiveAreasAsync(CancellationToken.None);

        areas.Select(area => area.Name).Should().Equal("East");
    }
}