using System.Reflection;
using Omika.Core.Contracts.Clients;
using Omika.Core.Contracts.Common;
using Omika.Core.Domain.Commands;
using Omika.Core.Domain.Entities.Clients;
using Omika.Core.Interfaces;
using Omika.Data.Configuration;
using Omika.Data.Connection;
using Omika.Data.HfSql.Metadata;
using Omika.Data.Repositories;
using Omika.Data.Repositories.Batik;


namespace Omika.Tests.Repositories.Batik;

/// <summary>
/// Tests unitaires de <see cref="BatikClientRepository"/> : aucun serveur HFSQL,
/// aucun driver PC SOFT, aucun ODBC réel, aucun réseau. Un fake
/// <see cref="IHfsqlConnection"/> enregistre le SQL et les paramètres et
/// retourne des dictionnaires par requête.
/// </summary>
public class BatikClientRepositoryTests
{
    // =========================================================================
    // SEARCH
    // =========================================================================

    [Fact]
    public async Task SearchAsync_ReturnsCanonicalClients()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows =
        [
            ClientRow(1, "Dupont", "C001", typeId: 10, statutId: 20, adressePrincipaleId: 100)
        ];
        connection.ParametreMetierRows =
        [
            ParametreMetierRow(10, "TYP", "Entreprise"),
            ParametreMetierRow(20, "STA", "Actif")
        ];
        connection.AdresseRows =
        [
            AdresseRow(100, "Paris")
        ];
        var repository = new BatikClientRepository(connection);

        PagedResult<Client> result = await repository.SearchAsync(new ClientSearchCriteria());

        Client client = Assert.Single(result.Items);
        Assert.Equal("Dupont", client.Nom);
        Assert.Equal("C001", client.Code);
        Assert.Equal("Entreprise", client.Type?.Label);
        Assert.Equal("Actif", client.Statut?.Label);
        Assert.Equal("Paris", client.AdressePrincipale?.AdressePostale?.City);
        Assert.Equal(1, client.SourceReference?.NumericId);
    }

    [Fact]
    public async Task SearchAsync_EmptyResult_ReturnsEmptyPage()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [];
        var repository = new BatikClientRepository(connection);

        PagedResult<Client> result = await repository.SearchAsync(new ClientSearchCriteria());

        Assert.Empty(result.Items);
        Assert.Equal(0, result.TotalCount);
        Assert.Equal(0, result.TotalPages);
    }

    [Fact]
    public async Task SearchAsync_PaginationRespectsPageAndPageSize()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows =
        [
            ClientRow(1, "Alpha", "C001"),
            ClientRow(2, "Beta", "C002"),
            ClientRow(3, "Gamma", "C003"),
            ClientRow(4, "Delta", "C004"),
            ClientRow(5, "Epsilon", "C005")
        ];
        var repository = new BatikClientRepository(connection);

        PagedResult<Client> result = await repository.SearchAsync(
            new ClientSearchCriteria { Page = 2, PageSize = 2, SortBy = "Nom" });

        // Tri explicite : Nom ASC (Alpha, Beta, Delta, Epsilon, Gamma).
        Assert.Equal(2, result.Items.Count);
        Assert.Equal("Delta", result.Items[0].Nom);
        Assert.Equal("Epsilon", result.Items[1].Nom);
        Assert.Equal(5, result.TotalCount);
        Assert.Equal(3, result.TotalPages);

    }

    [Fact]
    public async Task SearchAsync_TotalCountIsTotalBeforePagination()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows =
        [
            ClientRow(1, "Alpha", "C001"),
            ClientRow(2, "Beta", "C002"),
            ClientRow(3, "Gamma", "C003")
        ];
        var repository = new BatikClientRepository(connection);

        PagedResult<Client> result = await repository.SearchAsync(
            new ClientSearchCriteria { Page = 1, PageSize = 2 });

        Assert.Equal(2, result.Items.Count);
        Assert.Equal(3, result.TotalCount);
    }

    [Fact]
    public async Task SearchAsync_NomFilter_IsParameterized()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(1, "Dupont", "C001")];
        var repository = new BatikClientRepository(connection);

        await repository.SearchAsync(new ClientSearchCriteria { Nom = "Dupont" });

        Assert.Contains("CL_Nom LIKE ?", connection.ClientSql);
        Assert.Equal("%Dupont%", connection.ClientParameters![0]);

    }

    [Fact]
    public async Task SearchAsync_CodeFilter_IsParameterized()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(1, "Dupont", "C001")];
        var repository = new BatikClientRepository(connection);

        await repository.SearchAsync(new ClientSearchCriteria { Code = "C001" });

        Assert.Contains("CL_CodeClient LIKE ?", connection.ClientSql);
        Assert.Equal("%C001%", connection.ClientParameters![0]);

    }

    [Fact]
    public async Task SearchAsync_ArchiveFilter_IsParameterized()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(1, "Dupont", "C001")];
        var repository = new BatikClientRepository(connection);

        await repository.SearchAsync(new ClientSearchCriteria { EstArchive = true });

        Assert.Contains("Archive = ?", connection.ClientSql);
        Assert.Equal(true, connection.ClientParameters![0]);

    }

    [Fact]
    public async Task SearchAsync_BloqueFilter_IsParameterized()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(1, "Dupont", "C001")];
        var repository = new BatikClientRepository(connection);

        await repository.SearchAsync(new ClientSearchCriteria { EstBloque = true });

        Assert.Contains("CL_Blocage = ?", connection.ClientSql);
        Assert.Equal(true, connection.ClientParameters![0]);

    }

    [Fact]
    public async Task SearchAsync_GeneralSearch_IsParameterized()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(1, "Dupont", "C001")];
        var repository = new BatikClientRepository(connection);

        await repository.SearchAsync(new ClientSearchCriteria { Search = "Dupont" });

        Assert.Contains("(CL_Nom LIKE ? OR CL_CodeClient LIKE ?)", connection.ClientSql);
        Assert.Equal("%Dupont%", connection.ClientParameters![0]);
        Assert.Equal("%Dupont%", connection.ClientParameters![1]);

    }

    [Fact]
    public async Task SearchAsync_UserValueNeverConcatenatedInSql()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(1, "Dupont", "C001")];
        var repository = new BatikClientRepository(connection);

        await repository.SearchAsync(new ClientSearchCriteria { Nom = "Dupont' OR '1'='1" });

        Assert.DoesNotContain("Dupont", connection.ClientSql);
        Assert.Contains("CL_Nom LIKE ?", connection.ClientSql);
        Assert.Equal("%Dupont' OR '1'='1%", connection.ClientParameters![0]);

    }

    [Fact]
    public async Task SearchAsync_SortByNom_UsesWhitelistedColumn()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(1, "Dupont", "C001")];
        var repository = new BatikClientRepository(connection);

        await repository.SearchAsync(new ClientSearchCriteria { SortBy = "Nom" });

        Assert.Contains("ORDER BY CL_Nom ASC", connection.ClientSql);

    }

    [Fact]
    public async Task SearchAsync_SortByCode_UsesWhitelistedColumn()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(1, "Dupont", "C001")];
        var repository = new BatikClientRepository(connection);

        await repository.SearchAsync(new ClientSearchCriteria { SortBy = "Code" });

        Assert.Contains("ORDER BY CL_CodeClient ASC", connection.ClientSql);

    }

    [Fact]
    public async Task SearchAsync_UnknownSortBy_NeverBecomesRawSql()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(1, "Dupont", "C001")];
        var repository = new BatikClientRepository(connection);

        await repository.SearchAsync(new ClientSearchCriteria { SortBy = "Nom; DROP TABLE Client" });

        Assert.Contains("ORDER BY ModifDate DESC", connection.ClientSql);
        Assert.DoesNotContain("DROP", connection.ClientSql);
        Assert.DoesNotContain(";", connection.ClientSql);

    }

    [Fact]
    public async Task SearchAsync_SortDescending_ProducesControlledDesc()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(1, "Dupont", "C001")];
        var repository = new BatikClientRepository(connection);

        await repository.SearchAsync(new ClientSearchCriteria { SortBy = "Nom", SortDescending = true });

        Assert.Contains("ORDER BY CL_Nom DESC", connection.ClientSql);

    }

    [Fact]
    public async Task SearchAsync_DoesNotLoadContactsOrSites()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(1, "Dupont", "C001")];
        var repository = new BatikClientRepository(connection);

        await repository.SearchAsync(new ClientSearchCriteria());

        string allSql = string.Join(" ", connection.AllSql);
        Assert.DoesNotContain("FROM Personne", allSql, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("FROM Lieu", allSql, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("FROM Client_Adresse", allSql, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("FROM Client_Lieu", allSql, StringComparison.OrdinalIgnoreCase);
        Assert.DoesNotContain("FROM Lieu_Personne", allSql, StringComparison.OrdinalIgnoreCase);

    }

    [Fact]
    public async Task SearchAsync_CancellationTokenTransmitted()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(1, "Dupont", "C001")];
        var repository = new BatikClientRepository(connection);
        using var cts = new CancellationTokenSource();

        await repository.SearchAsync(new ClientSearchCriteria(), cts.Token);

        Assert.Equal(cts.Token, connection.LastCancellationToken);
    }

    // =========================================================================
    // DETAIL
    // =========================================================================

    [Fact]
    public async Task GetDetailsAsync_ValidNumericId_QueriesClPkidWithParameter()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(42, "Dupont", "C001")];
        var repository = new BatikClientRepository(connection);

        Client? client = await repository.GetDetailsAsync("42");

        Assert.NotNull(client);
        Assert.Contains("WHERE CL_PKID = ?", connection.ClientSql);
        Assert.Equal(42L, connection.ClientParameters![0]);

    }

    [Fact]
    public async Task GetDetailsAsync_NonNumericId_ReturnsNullWithoutDetailQuery()
    {
        var connection = new FakeHfsqlConnection();
        var repository = new BatikClientRepository(connection);

        Client? client = await repository.GetDetailsAsync("abc");

        Assert.Null(client);
        Assert.Equal(0, connection.QueryCount);
    }

    [Fact]
    public async Task GetDetailsAsync_GuidOnBatik_ReturnsNull()
    {
        var connection = new FakeHfsqlConnection();
        var repository = new BatikClientRepository(connection);

        Client? client = await repository.GetDetailsAsync(Guid.NewGuid().ToString());

        Assert.Null(client);
        Assert.Equal(0, connection.QueryCount);
    }

    [Fact]
    public async Task GetDetailsAsync_ClientAbsent_ReturnsNull()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [];
        var repository = new BatikClientRepository(connection);

        Client? client = await repository.GetDetailsAsync("42");

        Assert.Null(client);
    }

    [Fact]
    public async Task GetDetailsAsync_BaseClientMappedCorrectly()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(42, "Dupont", "C001", typeId: 10, statutId: 20)];
        connection.ParametreMetierRows =
        [
            ParametreMetierRow(10, "TYP", "Entreprise"),
            ParametreMetierRow(20, "STA", "Actif")
        ];
        var repository = new BatikClientRepository(connection);

        Client? client = await repository.GetDetailsAsync("42");

        Assert.NotNull(client);
        Assert.Equal("Dupont", client.Nom);
        Assert.Equal("C001", client.Code);
        Assert.Equal("Entreprise", client.Type?.Label);
        Assert.Equal("Actif", client.Statut?.Label);
        Assert.Equal(42, client.SourceReference?.NumericId);
    }

    [Fact]
    public async Task GetDetailsAsync_AdressesAssembled()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(42, "Dupont", "C001")];
        connection.ClientAdresseRows =
        [
            ClientAdresseRow(1, adresseId: 100, clientId: 42, typeAdresseId: 0, principale: false),
            ClientAdresseRow(2, adresseId: 101, clientId: 42, typeAdresseId: 0, principale: false)
        ];
        connection.AdresseRows =
        [
            AdresseRow(100, "Paris"),
            AdresseRow(101, "Lyon")
        ];
        var repository = new BatikClientRepository(connection);

        Client? client = await repository.GetDetailsAsync("42");

        Assert.NotNull(client);
        Assert.Equal(2, client.Adresses.Count);
        Assert.Contains(client.Adresses, a => a.AdressePostale?.City == "Paris");
        Assert.Contains(client.Adresses, a => a.AdressePostale?.City == "Lyon");
    }

    [Fact]
    public async Task GetDetailsAsync_AdressePrincipaleResolved()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(42, "Dupont", "C001", adressePrincipaleId: 100)];
        connection.AdresseRows = [AdresseRow(100, "Paris")];
        var repository = new BatikClientRepository(connection);

        Client? client = await repository.GetDetailsAsync("42");

        Assert.NotNull(client);
        Assert.NotNull(client.AdressePrincipale);
        Assert.Equal("Paris", client.AdressePrincipale.AdressePostale?.City);
    }

    [Fact]
    public async Task GetDetailsAsync_SitesAssembled()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(42, "Dupont", "C001")];
        connection.ClientLieuRows = [ClientLieuRow(1, clientId: 42, lieuId: 500)];
        connection.LieuRows = [LieuRow(500, "Site A", adresseId: 100, typeId: 0)];
        connection.AdresseRows = [AdresseRow(100, "Paris")];
        var repository = new BatikClientRepository(connection);

        Client? client = await repository.GetDetailsAsync("42");

        Assert.NotNull(client);
        Site site = Assert.Single(client.Sites);
        Assert.Equal("Site A", site.Code);
        Assert.Equal("Paris", site.Adresse?.City);
        Assert.Equal(500, site.SourceReference?.NumericId);

    }

    [Fact]
    public async Task GetDetailsAsync_DirectContactsAssembled()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(42, "Dupont", "C001")];
        connection.PersonneRows =
        [
            PersonneRow(1, "Martin", "Jean", clientId: 42),
            PersonneRow(2, "Durand", "Paul", clientId: 42)
        ];
        var repository = new BatikClientRepository(connection);

        Client? client = await repository.GetDetailsAsync("42");

        Assert.NotNull(client);
        Assert.Equal(2, client.Contacts.Count);
        Assert.Contains(client.Contacts, c => c.Nom == "Martin");
        Assert.Contains(client.Contacts, c => c.Nom == "Durand");
    }

    [Fact]
    public async Task GetDetailsAsync_SiteContactsNonInclus()
    {
        // Les contacts de site (Lieu_Personne) appartiennent à la fiche site, pas
        // à la fiche client : un contact lié uniquement à un site du client ne
        // doit PAS apparaître dans la liste des contacts du client.
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(42, "Dupont", "C001")];
        connection.ClientLieuRows = [ClientLieuRow(1, clientId: 42, lieuId: 500)];
        connection.LieuRows = [LieuRow(500, "Site A", adresseId: 0, typeId: 0)];
        connection.LieuPersonneRows = [LieuPersonneRow(1, personneId: 7, lieuId: 500)];
        connection.PersonneRows = [PersonneRow(7, "Bernard", "Luc", clientId: 0)];
        var repository = new BatikClientRepository(connection);

        Client? client = await repository.GetDetailsAsync("42");

        Assert.NotNull(client);
        // La table Lieu_Personne ne doit plus être interrogée : les contacts de
        // site appartiennent à la fiche site, pas à la fiche client.
        string allSql = string.Join(" ", connection.AllSql);
        Assert.DoesNotContain("FROM Lieu_Personne", allSql, StringComparison.OrdinalIgnoreCase);
    }

    [Fact]
    public async Task GetDetailsAsync_DuplicatesEliminatedByAssembler()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(42, "Dupont", "C001")];
        connection.ClientLieuRows = [ClientLieuRow(1, clientId: 42, lieuId: 500)];
        connection.LieuRows = [LieuRow(500, "Site A", adresseId: 0, typeId: 0)];
        // Même personne (PE_PKID = 7) en contact direct ET en contact de site.
        connection.LieuPersonneRows = [LieuPersonneRow(1, personneId: 7, lieuId: 500)];
        connection.PersonneRows = [PersonneRow(7, "Bernard", "Luc", clientId: 42)];
        var repository = new BatikClientRepository(connection);

        Client? client = await repository.GetDetailsAsync("42");

        Assert.NotNull(client);
        Assert.Single(client.Contacts);
        Assert.Equal("Bernard", client.Contacts[0].Nom);
    }

    [Fact]
    public async Task GetDetailsAsync_ReferencesResolved()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(42, "Dupont", "C001", typeId: 10, statutId: 20)];
        connection.ParametreMetierRows =
        [
            ParametreMetierRow(10, "TYP", "Entreprise"),
            ParametreMetierRow(20, "STA", "Actif")
        ];
        var repository = new BatikClientRepository(connection);

        Client? client = await repository.GetDetailsAsync("42");

        Assert.NotNull(client);
        Assert.Equal("Entreprise", client.Type?.Label);
        Assert.Equal("Actif", client.Statut?.Label);
        Assert.Equal(10, client.Type?.LegacyId);
        Assert.Equal(20, client.Statut?.LegacyId);
    }

    [Fact]
    public async Task GetDetailsAsync_NoGuidFabricatedForBatik()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(42, "Dupont", "C001")];
        var repository = new BatikClientRepository(connection);

        Client? client = await repository.GetDetailsAsync("42");

        Assert.NotNull(client);
        Assert.Equal(42, client.SourceReference?.NumericId);
        Assert.Null(client.SourceReference?.EntityGuid);
        Assert.Null(client.SourceReference?.PhysicalGuid);
    }

    [Fact]
    public async Task GetDetailsAsync_InfrastructureError_Propagates()
    {
        var connection = new FakeHfsqlConnection();
        connection.ThrowOnQuery = true;
        var repository = new BatikClientRepository(connection);

        await Assert.ThrowsAsync<InvalidOperationException>(
            () => repository.GetDetailsAsync("42"));
    }

    [Fact]
    public async Task GetDetailsAsync_SqlAlwaysParameterized()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(42, "Dupont", "C001")];
        connection.ClientAdresseRows = [ClientAdresseRow(1, adresseId: 100, clientId: 42, typeAdresseId: 0, principale: false)];
        connection.AdresseRows = [AdresseRow(100, "Paris")];
        var repository = new BatikClientRepository(connection);

        await repository.GetDetailsAsync("42");

        // Aucune valeur utilisateur (42) ne doit apparaître en clair dans le SQL.
        Assert.DoesNotContain("= 42", connection.AllSql);
        Assert.DoesNotContain("IN (42)", connection.AllSql);
    }

    // =========================================================================
    // ARCHITECTURE
    // =========================================================================

    [Fact]
    public void Repository_ImplementsIClientRepository()
    {
        Assert.True(typeof(IClientRepository).IsAssignableFrom(typeof(BatikClientRepository)));
    }

    [Fact]
    public void Repository_InheritsRepositoryBase()
    {
        Assert.True(typeof(RepositoryBase).IsAssignableFrom(typeof(BatikClientRepository)));
    }

    [Fact]
    public void Repository_DoesNotReferenceOmikaModels()
    {
        var type = typeof(BatikClientRepository);
        var referenced = GetPublicSurfaceTypes(type);

        // Aucun type dont le nom contient « Omika » (modèles/mappers Omika) ne
        // doit apparaître dans la surface publique. Le type lui-même est exclu.
        Assert.DoesNotContain(referenced, t =>
            t != type && t.Name.Contains("Omika", StringComparison.OrdinalIgnoreCase));
    }

    [Fact]
    public void Repository_DoesNotExposeOdbcTypes()
    {
        var type = typeof(BatikClientRepository);
        var referenced = GetPublicSurfaceTypes(type);

        Assert.DoesNotContain(referenced, t =>
            t.Namespace is not null && t.Namespace.StartsWith("System.Data.Odbc", StringComparison.Ordinal));
    }

    [Fact]
    public void Repository_DoesNotReturnClientDtos()
    {
        var type = typeof(BatikClientRepository);

        foreach (MethodInfo method in type.GetMethods(BindingFlags.Public | BindingFlags.Instance))
        {
            Assert.DoesNotContain("ClientSummaryDto", method.ReturnType.Name);
            Assert.DoesNotContain("ClientDetailsDto", method.ReturnType.Name);
        }
    }

    private static IEnumerable<Type> GetPublicSurfaceTypes(Type type)
    {
        var members = type.GetMembers(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Static)
            .Select(m => m.DeclaringType)
            .Where(t => t is not null)
            .Cast<Type>();

        var parameters = type.GetMethods(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance)
            .SelectMany(m => m.GetParameters())
            .Select(p => p.ParameterType);

        var returnTypes = type.GetMethods(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance)
            .Select(m => m.ReturnType);

        var properties = type.GetProperties(BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance)
            .Select(p => p.PropertyType);

        return members.Concat(parameters).Concat(returnTypes).Concat(properties);
    }

    // =========================================================================
    // Helpers de construction de lignes
    // =========================================================================

    private static Dictionary<string, object?> ClientRow(
        long pkid,
        string? nom,
        string? code,
        long typeId = 0,
        long statutId = 0,
        long adressePrincipaleId = 0)
        => new()
        {
            ["CL_PKID"] = pkid,
            ["CL_Nom"] = nom,
            ["CL_CodeClient"] = code,
            ["CL_IDType"] = typeId,
            ["CL_IDStatut"] = statutId,
            ["CL_IDAdressePrincipale"] = adressePrincipaleId,
            ["CL_Blocage"] = false,
            ["Archive"] = false
        };

    private static Dictionary<string, object?> ParametreMetierRow(long pkid, string code, string libelle)
        => new()
        {
            ["PA_PKID"] = pkid,
            ["PA_Code"] = code,
            ["PA_Libelle"] = libelle,
            ["PA_Valeur"] = null
        };

    private static Dictionary<string, object?> AdresseRow(long pkid, string ville)
        => new()
        {
            ["AD_PKID"] = pkid,
            ["AD_Ville"] = ville,
            ["AD_Ligne_1"] = null,
            ["AD_CodePostal"] = null,
            ["AD_Etiquette"] = null
        };

    private static Dictionary<string, object?> LieuRow(long pkid, string codeLieu, long adresseId, long typeId)
        => new()
        {
            ["LI_PKID"] = pkid,
            ["LI_CodeLieu"] = codeLieu,
            ["LI_IDAdresse"] = adresseId,
            ["LI_IDType"] = typeId,
            ["Archive"] = false
        };

    private static Dictionary<string, object?> PersonneRow(long pkid, string nom, string prenom, long clientId)
        => new()
        {
            ["PE_PKID"] = pkid,
            ["PE_Nom"] = nom,
            ["PE_Prenom"] = prenom,
            ["PE_IDClient"] = clientId,
            ["PE_IDCivilite"] = 0,
            ["PE_IDRole"] = 0,
            ["PE_Actif"] = true,
            ["Archive"] = false
        };

    private static Dictionary<string, object?> ClientAdresseRow(
        long pkid,
        long adresseId,
        long clientId,
        long typeAdresseId,
        bool principale)
        => new()
        {
            ["CA_PKID"] = pkid,
            ["CA_IDAdresse"] = adresseId,
            ["CA_IDClient"] = clientId,
            ["CA_IDTypeAdresse"] = typeAdresseId,
            ["CA_Principale"] = principale
        };

    private static Dictionary<string, object?> ClientLieuRow(long pkid, long clientId, long lieuId)
        => new()
        {
            ["CLI_PKID"] = pkid,
            ["CLI_IDClient"] = clientId,
            ["CLI_IDLieu"] = lieuId
        };

    private static Dictionary<string, object?> LieuPersonneRow(long pkid, long personneId, long lieuId)
        => new()
        {
            ["LIP_PKID"] = pkid,
            ["LIP_IDPersonne"] = personneId,
            ["LIP_IDLieu"] = lieuId
        };

    // =========================================================================
    // Fake de connexion
    // =========================================================================

    private sealed record ExecutedCommand(string Sql, IReadOnlyList<object?> Parameters);

    private sealed class FakeHfsqlConnection : IHfsqlConnection
    {
        public HfsqlOptions Options => new();
        public HfsqlConnectionState State { get; private set; } = HfsqlConnectionState.Closed;

        public int OpenCount { get; private set; }
        public int QueryCount { get; private set; }
        public bool ThrowOnQuery { get; set; }

        public string? LastSql { get; private set; }
        public IReadOnlyList<object?>? LastParameters { get; private set; }
        public CancellationToken LastCancellationToken { get; private set; }
        public IReadOnlyList<string> AllSql { get; } = new List<string>();
        public IReadOnlyList<ExecutedCommand> Commands => _commands;
        private readonly List<ExecutedCommand> _commands = [];

        // SQL et paramètres de la requête principale « FROM Client » (la plus
        // pertinente pour vérifier les filtres et le tri de la recherche).
        public string? ClientSql { get; private set; }
        public IReadOnlyList<object?>? ClientParameters { get; private set; }
        private void Record(string sql, IReadOnlyList<object?>? parameters, CancellationToken cancellationToken)
        {
            LastSql = sql;
            LastParameters = parameters;
            LastCancellationToken = cancellationToken;
            QueryCount++;
            ((List<string>)AllSql).Add(sql);
            _commands.Add(new ExecutedCommand(sql, parameters?.ToArray() ?? []));

            if (ContainsFrom(sql, "Client"))
            {
                ClientSql = sql;
                ClientParameters = parameters;
            }
        }


        public IReadOnlyList<IReadOnlyDictionary<string, object?>> ClientRows { get; set; } = [];
        public IReadOnlyList<IReadOnlyDictionary<string, object?>> ParametreMetierRows { get; set; } = [];
        public IReadOnlyList<IReadOnlyDictionary<string, object?>> AdresseRows { get; set; } = [];
        public IReadOnlyList<IReadOnlyDictionary<string, object?>> LieuRows { get; set; } = [];
        public IReadOnlyList<IReadOnlyDictionary<string, object?>> PersonneRows { get; set; } = [];
        public IReadOnlyList<IReadOnlyDictionary<string, object?>> ClientAdresseRows { get; set; } = [];
        public IReadOnlyList<IReadOnlyDictionary<string, object?>> ClientLieuRows { get; set; } = [];
        public IReadOnlyList<IReadOnlyDictionary<string, object?>> LieuPersonneRows { get; set; } = [];

        public Task OpenAsync(CancellationToken cancellationToken = default)
        {
            State = HfsqlConnectionState.Open;
            OpenCount++;
            return Task.CompletedTask;
        }

        public Task CloseAsync()
        {
            State = HfsqlConnectionState.Closed;
            return Task.CompletedTask;
        }

        public Task<object?> ExecuteScalarAsync(
            string sql,
            IReadOnlyList<object?>? parameters = null,
            CancellationToken cancellationToken = default)
        {
            Record(sql, parameters, cancellationToken);
            return Task.FromResult<object?>(null);
        }

        public Task<int> ExecuteNonQueryAsync(
            string sql,
            IReadOnlyList<object?>? parameters = null,
            CancellationToken cancellationToken = default)
        {
            Record(sql, parameters, cancellationToken);
            return Task.FromResult(0);
        }

        public Task<IReadOnlyList<IReadOnlyDictionary<string, object?>>> QueryAsync(
            string sql,
            IReadOnlyList<object?>? parameters = null,
            CancellationToken cancellationToken = default)
        {
            Record(sql, parameters, cancellationToken);

            if (ThrowOnQuery)
            {
                throw new InvalidOperationException("Panne d'infrastructure simulée.");
            }

            return Task.FromResult(ResolveRows(sql));
        }

        public Task<IReadOnlyList<HfsqlColumnMetadata>> GetColumnsAsync(
            string tableName,
            CancellationToken cancellationToken = default)
        {
            return Task.FromResult<IReadOnlyList<HfsqlColumnMetadata>>([]);
        }

        public ValueTask DisposeAsync()
        {
            State = HfsqlConnectionState.Closed;
            return ValueTask.CompletedTask;
        }

        private IReadOnlyList<IReadOnlyDictionary<string, object?>> ResolveRows(string sql)

        {
            // Ordre de spécificité : les tables composées avant les tables simples.
            if (ContainsFrom(sql, "Client_Adresse")) return ClientAdresseRows;
            if (ContainsFrom(sql, "Client_Lieu")) return ClientLieuRows;
            if (ContainsFrom(sql, "Lieu_Personne")) return LieuPersonneRows;
            if (ContainsFrom(sql, "ParametreMetier")) return ParametreMetierRows;
            if (ContainsFrom(sql, "Adresse")) return AdresseRows;
            if (ContainsFrom(sql, "Lieu")) return LieuRows;
            if (ContainsFrom(sql, "Personne")) return PersonneRows;
            if (ContainsFrom(sql, "Client")) return ClientRows;
            return [];
        }

        private static bool ContainsFrom(string sql, string table)
        {
            // Le nom de table doit être suivi d'un espace, d'une fin de chaîne ou
            // d'un caractère non-identifiant, afin de ne pas matcher une table
            // composée (ex. « FROM Client » ne doit pas matcher « FROM Client_Lieu »).
            string marker = $"FROM {table}";
            int index = sql.IndexOf(marker, StringComparison.OrdinalIgnoreCase);
            while (index >= 0)
            {
                int next = index + marker.Length;
                if (next >= sql.Length || (!char.IsLetterOrDigit(sql[next]) && sql[next] != '_'))
                {
                    return true;
                }

                index = sql.IndexOf(marker, index + 1, StringComparison.OrdinalIgnoreCase);
            }

            return false;
        }
    }

    // =========================================================================
    // UPDATE
    // =========================================================================

    [Fact]
    public async Task UpdateAsync_ValidNumericId_UpdatesClient()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(42, "Orig", "C001")];
        connection.ParametreMetierRows = [];
        var repository = new BatikClientRepository(connection);

        var command = new ClientUpdateCommand { Nom = "Updated", Code = "C002" };
        await repository.UpdateAsync("42", command);

        string updateSql = connection.AllSql.First(s => s.Contains("UPDATE Client SET"));
        Assert.Contains("UPDATE Client SET", updateSql);
        Assert.Contains("WHERE CL_PKID = ?", updateSql);
    }

    [Fact]
    public async Task UpdateAsync_InvalidId_ReturnsNullNoQuery()
    {
        var connection = new FakeHfsqlConnection();
        var repository = new BatikClientRepository(connection);

        Client? result = await repository.UpdateAsync("abc", new ClientUpdateCommand());

        Assert.Null(result);
        Assert.Equal(0, connection.QueryCount);
    }

    [Fact]
    public async Task UpdateAsync_AbsentClient_ReturnsNull()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [];
        var repository = new BatikClientRepository(connection);

        Client? result = await repository.UpdateAsync("42", new ClientUpdateCommand());

        Assert.Null(result);
    }

    [Fact]
    public async Task UpdateAsync_NullPut_SendsDefaults()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(42, "Dupont", "C001")];
        var repository = new BatikClientRepository(connection);

        await repository.UpdateAsync("42", new ClientUpdateCommand());

        // L'UPDATE a bien été exécuté (pas d'exception).
        string updateSql = connection.AllSql.First(s => s.StartsWith("UPDATE Client SET"));
        Assert.NotNull(updateSql);
    }

    [Fact]
    public async Task UpdateAsync_TypeStatutResolved()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(42, "Dupont", "C001")];
        connection.ParametreMetierRows =
        [
            ParametreMetierRow(10, "ENT", "Entreprise"),
            ParametreMetierRow(20, "ACT", "Actif")
        ];
        var repository = new BatikClientRepository(connection);

        await repository.UpdateAsync("42", new ClientUpdateCommand { TypeKey = "Entreprise", StatutKey = "Actif" });

        var updateCmd = connection.Commands.Single(c => c.Sql.StartsWith("UPDATE Client SET"));
        object[] p = updateCmd.Parameters.ToArray()!;
        Assert.Equal(10L, p[2]); // CL_IDType
        Assert.Equal(20L, p[3]); // CL_IDStatut
    }

    [Fact]
    public async Task UpdateAsync_NoGuidFabricated()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(42, "Dupont", "C001")];
        var repository = new BatikClientRepository(connection);

        Client? result = await repository.UpdateAsync("42", new ClientUpdateCommand { Nom = "X" });

        Assert.NotNull(result);
        Assert.Null(result.SourceReference?.EntityGuid);
    }

    // =========================================================================
    // ARCHIVE
    // =========================================================================

    [Fact]
    public async Task ArchiveAsync_Existing_ReturnsTrue()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(42, "Dupont", "C001")];
        var repository = new BatikClientRepository(connection);

        bool result = await repository.ArchiveAsync("42");

        Assert.True(result);
        Assert.Contains("UPDATE Client SET Archive", connection.LastSql);
    }

    [Fact]
    public async Task ArchiveAsync_InvalidId_ReturnsFalse()
    {
        var connection = new FakeHfsqlConnection();
        var repository = new BatikClientRepository(connection);

        bool result = await repository.ArchiveAsync("abc");

        Assert.False(result);
    }

    [Fact]
    public async Task ArchiveAsync_Absent_ReturnsFalse()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [];
        var repository = new BatikClientRepository(connection);

        bool result = await repository.ArchiveAsync("42");

        Assert.False(result);
    }

    [Fact]
    public async Task ArchiveAsync_NoPhysicalDelete()
    {
        var connection = new FakeHfsqlConnection();
        connection.ClientRows = [ClientRow(42, "Dupont", "C001")];
        var repository = new BatikClientRepository(connection);

        await repository.ArchiveAsync("42");

        Assert.DoesNotContain("DELETE", connection.LastSql, StringComparison.OrdinalIgnoreCase);
    }
}