using System.Reflection;
using Omika.Core.Contracts.Common;
using Omika.Core.Contracts.Interventions;
using Omika.Core.Domain.Commands;
using Omika.Core.Domain.Entities.Interventions;
using Omika.Core.Domain.Exceptions;
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="BatikInterventionRepository"/> : 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.
///
/// Le mapping Row → domaine (dont <c>SourceReference</c>) est couvert par les
/// tests dédiés du mapper ; ces tests se concentrent sur le SQL généré, la
/// paramétrisation, la délégation et la propagation d'erreurs.
/// </summary>
public class BatikInterventionRepositoryTests
{
    // =========================================================================
    // SEARCH
    // =========================================================================

    [Fact]
    public async Task SearchAsync_ReturnsCanonicalInterventions()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows =
        [
            InterventionRow(1, "Réparation", "INT-001", typeId: 10, etatId: 20)
        ];
        connection.ParametreMetierRows =
        [
            ParametreMetierRow(10, "TYP", "Maintenance"),
            ParametreMetierRow(20, "STA", "En cours")
        ];
        var repository = new BatikInterventionRepository(connection);

        PagedResult<Intervention> result = await repository.SearchAsync(new InterventionSearchCriteria());

        Intervention intervention = Assert.Single(result.Items);
        Assert.Equal("Réparation", intervention.Titre);
        Assert.Equal("INT-001", intervention.Reference);
        Assert.Equal("Maintenance", intervention.Type?.Label);
        Assert.Equal("En cours", intervention.Etat?.Label);
    }

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

        PagedResult<Intervention> result = await repository.SearchAsync(new InterventionSearchCriteria());

        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.InterventionRows =
        [
            InterventionRow(1, "Alpha", "INT-001"),
            InterventionRow(2, "Beta", "INT-002"),
            InterventionRow(3, "Gamma", "INT-003"),
            InterventionRow(4, "Delta", "INT-004"),
            InterventionRow(5, "Epsilon", "INT-005")
        ];
        var repository = new BatikInterventionRepository(connection);

        PagedResult<Intervention> result = await repository.SearchAsync(
            new InterventionSearchCriteria { Page = 2, PageSize = 2, SortBy = "Titre" });

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

    [Fact]
    public async Task SearchAsync_TotalCountIsTotalBeforePagination()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows =
        [
            InterventionRow(1, "Alpha", "INT-001"),
            InterventionRow(2, "Beta", "INT-002"),
            InterventionRow(3, "Gamma", "INT-003")
        ];
        var repository = new BatikInterventionRepository(connection);

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

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

    [Fact]
    public async Task SearchAsync_TitreFilter_IsParameterized()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(1, "Réparation", "INT-001")];
        var repository = new BatikInterventionRepository(connection);

        await repository.SearchAsync(new InterventionSearchCriteria { Titre = "Réparation" });

        Assert.Contains("IN_Titre LIKE ?", connection.InterventionSql);
        Assert.Equal("%Réparation%", connection.InterventionParameters![0]);
    }

    [Fact]
    public async Task SearchAsync_ReferenceFilter_IsParameterized()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(1, "Réparation", "INT-001")];
        var repository = new BatikInterventionRepository(connection);

        await repository.SearchAsync(new InterventionSearchCriteria { Reference = "INT-001" });

        Assert.Contains("IN_RefInterne LIKE ?", connection.InterventionSql);
        Assert.Equal("%INT-001%", connection.InterventionParameters![0]);
    }

    [Fact]
    public async Task SearchAsync_ArchiveFilter_IsParameterized()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(1, "Réparation", "INT-001")];
        var repository = new BatikInterventionRepository(connection);

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

        Assert.Contains("Archive = ?", connection.InterventionSql);
        Assert.Equal(true, connection.InterventionParameters![0]);
    }

    [Fact]
    public async Task SearchAsync_GeneralSearch_IsParameterized()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(1, "Réparation", "INT-001")];
        var repository = new BatikInterventionRepository(connection);

        await repository.SearchAsync(new InterventionSearchCriteria { Search = "Réparation" });

        Assert.Contains("(IN_RefInterne LIKE ? OR IN_Titre LIKE ?)", connection.InterventionSql);
        Assert.Equal("%Réparation%", connection.InterventionParameters![0]);
        Assert.Equal("%Réparation%", connection.InterventionParameters![1]);
    }

    [Fact]
    public async Task SearchAsync_UserValueNeverConcatenatedInSql()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(1, "Réparation", "INT-001")];
        var repository = new BatikInterventionRepository(connection);

        await repository.SearchAsync(new InterventionSearchCriteria { Titre = "Réparation' OR '1'='1" });

        Assert.DoesNotContain("Réparation", connection.InterventionSql);
        Assert.Contains("IN_Titre LIKE ?", connection.InterventionSql);
        Assert.Equal("%Réparation' OR '1'='1%", connection.InterventionParameters![0]);
    }

    [Fact]
    public async Task SearchAsync_NumeroSansRelations_NAjouteAucuneClauseRelation()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(1, "Reparation", "INT-001")];
        var repository = new BatikInterventionRepository(connection);

        await repository.SearchAsync(new InterventionSearchCriteria
        {
            NumIntervention = "000105",
            EstArchive = false
        });

        Assert.Contains("IN_NumIntervention = ?", connection.InterventionSql);
        Assert.Contains("Archive = ?", connection.InterventionSql);
        Assert.DoesNotContain("IN_IDClient =", connection.InterventionSql);
        Assert.DoesNotContain("IN_IDLieu =", connection.InterventionSql);
        Assert.DoesNotContain("IC_IDCollaborateur", connection.InterventionSql);
        Assert.Equal("000105", connection.InterventionParameters![0]);
        Assert.Equal(false, connection.InterventionParameters![1]);
    }

    [Fact]
    public async Task SearchAsync_RelationsReelles_AjouteClausesCorrespondantes()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(1, "Reparation", "INT-001")];
        var repository = new BatikInterventionRepository(connection);

        await repository.SearchAsync(new InterventionSearchCriteria
        {
            ClientId = "42",
            SiteId = "43",
            CollaborateurId = "44"
        });

        Assert.Contains("IN_IDClient = ?", connection.InterventionSql);
        Assert.Contains("IN_IDLieu = ?", connection.InterventionSql);
        Assert.Contains("IC_IDCollaborateur = ?", connection.InterventionSql);
        Assert.Contains(42L, connection.InterventionParameters!);
        Assert.Contains(43L, connection.InterventionParameters!);
        Assert.Contains(44L, connection.InterventionParameters!);
    }

    [Fact]
    public async Task SearchAsync_SortByTitre_UsesWhitelistedColumn()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(1, "Réparation", "INT-001")];
        var repository = new BatikInterventionRepository(connection);

        await repository.SearchAsync(new InterventionSearchCriteria { SortBy = "Titre" });

        Assert.Contains("ORDER BY IN_Titre ASC", connection.InterventionSql);
    }

    [Fact]
    public async Task SearchAsync_SortByReference_UsesWhitelistedColumn()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(1, "Réparation", "INT-001")];
        var repository = new BatikInterventionRepository(connection);

        await repository.SearchAsync(new InterventionSearchCriteria { SortBy = "Reference" });

        Assert.Contains("ORDER BY IN_RefInterne ASC", connection.InterventionSql);
    }

    [Fact]
    public async Task SearchAsync_UnknownSortBy_NeverBecomesRawSql()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(1, "Réparation", "INT-001")];
        var repository = new BatikInterventionRepository(connection);

        await repository.SearchAsync(new InterventionSearchCriteria { SortBy = "Titre; DROP TABLE Intervention" });

        // Valeur inconnue : tri par défaut déterministe, jamais de SQL brut.
        Assert.Contains("ORDER BY ModifDate DESC", connection.InterventionSql);
        Assert.DoesNotContain("DROP", connection.InterventionSql);
        Assert.DoesNotContain(";", connection.InterventionSql);
    }

    [Fact]
    public async Task SearchAsync_SortDescending_ProducesControlledDesc()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(1, "Réparation", "INT-001")];
        var repository = new BatikInterventionRepository(connection);

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

        Assert.Contains("ORDER BY IN_Titre DESC", connection.InterventionSql);
    }

    [Fact]
    public async Task SearchAsync_TypeFilter_UsesParameterizedSubquery()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(1, "Alpha", "INT-001", typeId: 10)];
        var repository = new BatikInterventionRepository(connection);

        await repository.SearchAsync(new InterventionSearchCriteria { Type = "Maintenance" });

        Assert.Contains(
            "IN_IDTypeIntervention IN (SELECT PA_PKID FROM ParametreMetier WHERE PA_Code = ? OR PA_Libelle = ?)",
            connection.InterventionSql);
        Assert.Contains("Maintenance", connection.InterventionParameters!);
    }

    [Fact]
    public async Task SearchAsync_EtatFilter_UsesParameterizedSubquery()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(1, "Alpha", "INT-001", etatId: 20)];
        var repository = new BatikInterventionRepository(connection);

        await repository.SearchAsync(new InterventionSearchCriteria { Etat = "En cours" });

        Assert.Contains(
            "IN_IDEtatIntervention IN (SELECT PA_PKID FROM ParametreMetier WHERE PA_Code = ? OR PA_Libelle = ?)",
            connection.InterventionSql);
        Assert.Contains("En cours", connection.InterventionParameters!);
    }

    [Fact]
    public async Task SearchAsync_CancellationTokenTransmitted()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(1, "Réparation", "INT-001")];
        var repository = new BatikInterventionRepository(connection);
        using var cts = new CancellationTokenSource();

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

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

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

    [Fact]
    public async Task GetDetailsAsync_ValidNumericId_QueriesInPkidWithParameter()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(42, "Réparation", "INT-001")];
        var repository = new BatikInterventionRepository(connection);

        Intervention? intervention = await repository.GetDetailsAsync("42");

        Assert.NotNull(intervention);
        ExecutedCommand detail = connection.Commands.First(c => c.Sql.Contains("WHERE IN_PKID = ?"));
        Assert.Equal(42L, detail.Parameters[0]);
    }

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

        Intervention? intervention = await repository.GetDetailsAsync("abc");

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

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

        Intervention? intervention = await repository.GetDetailsAsync(Guid.NewGuid().ToString());

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

    [Fact]
    public async Task GetDetailsAsync_InterventionAbsent_ReturnsNull()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [];
        var repository = new BatikInterventionRepository(connection);

        Intervention? intervention = await repository.GetDetailsAsync("42");

        Assert.Null(intervention);
    }

    [Fact]
    public async Task GetDetailsAsync_BaseInterventionMappedCorrectly()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(42, "Réparation", "INT-001", typeId: 10, etatId: 20)];
        connection.ParametreMetierRows =
        [
            ParametreMetierRow(10, "TYP", "Maintenance"),
            ParametreMetierRow(20, "STA", "En cours")
        ];
        var repository = new BatikInterventionRepository(connection);

        Intervention? intervention = await repository.GetDetailsAsync("42");

        Assert.NotNull(intervention);
        Assert.Equal("Réparation", intervention.Titre);
        Assert.Equal("INT-001", intervention.Reference);
        Assert.Equal("Maintenance", intervention.Type?.Label);
        Assert.Equal("En cours", intervention.Etat?.Label);
    }

    [Fact]
    public async Task GetDetailsAsync_EnrichesSocieteAndSuiviPar()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(42, "Réparation", "INT-001")];
        connection.ParametreMetierRows = [];
        connection.EnrichissementRows = [EnrichissementRow(42, "ACME", "Martin", "Jean")];
        var repository = new BatikInterventionRepository(connection);

        Intervention? intervention = await repository.GetDetailsAsync("42");

        Assert.NotNull(intervention);
        Assert.Equal("ACME", intervention.SocieteNom);
        Assert.Equal("Jean Martin", intervention.SuiviParNom);
    }

    [Fact]
    public async Task GetDetailsAsync_ActionsFromEtatIntervention()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(42, "Réparation", "INT-001")];
        connection.EtatInterventionRows =
        [
            EtatInterventionRow(1, etatId: 20, interventionId: 42, description: "Ouvert", dateDebut: new DateTime(2026, 1, 1), dateFin: new DateTime(2026, 1, 5))
        ];
        var repository = new BatikInterventionRepository(connection);

        Intervention? intervention = await repository.GetDetailsAsync("42");

        Assert.NotNull(intervention);
        ActionIntervention action = Assert.Single(intervention.Actions);
        Assert.Equal("Ouvert", action.Description);
        Assert.Equal(new DateTime(2026, 1, 1), action.DatePrevue);
        Assert.Equal(new DateTime(2026, 1, 5), action.DateRealisation);
    }

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

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

    [Fact]
    public async Task GetDetailsAsync_SqlAlwaysParameterized()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(42, "Réparation", "INT-001")];
        var repository = new BatikInterventionRepository(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);
    }

    // =========================================================================
    // CREATE
    //
    // Le fake ne peut pas retourner d'identifiant auto-généré (le repli de
    // ExecuteInTransactionAsync retourne des identifiants nuls pour les
    // implémentations non-OleDb). CreateAsync lève donc une
    // InvalidOperationException après avoir généré l'INSERT. On vérifie ici que
    // le SQL d'INSERT est bien généré et paramétré, et que l'erreur est propre.
    // =========================================================================

    [Fact]
    public async Task CreateAsync_GeneratesInsertSql()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(1, "Nouveau", "INT-001")];
        connection.ParametreMetierRows = [];
        var repository = new BatikInterventionRepository(connection);

        await Assert.ThrowsAsync<InvalidOperationException>(() =>
            repository.CreateAsync(new InterventionCreateCommand
            {
                Titre = "Nouveau",
                Reference = "INT-001",
                Description = "Description"
            }));

        string insertSql = connection.AllSql.First(s => s.StartsWith("INSERT INTO Intervention"));
        Assert.Contains("INSERT INTO Intervention", insertSql);
        Assert.Contains("IN_Titre", insertSql);
        Assert.Contains("IN_RefInterne", insertSql);
    }

    [Fact]
    public async Task CreateAsync_TypeKeyStoredInIdTypeIntervention()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(1, "Nouveau", "INT-001")];
        connection.ParametreMetierRows =
        [
            ParametreMetierRow(10, "TYP", "Maintenance")
        ];
        var repository = new BatikInterventionRepository(connection);

        await Assert.ThrowsAsync<InvalidOperationException>(() =>
            repository.CreateAsync(new InterventionCreateCommand
            {
                Titre = "Nouveau",
                TypeKey = "TYP"
            }));

        var insertCmd = connection.Commands.Single(c => c.Sql.StartsWith("INSERT INTO Intervention"));
        object[] p = insertCmd.Parameters.ToArray()!;
        Assert.Contains(10L, p);
    }

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

        await Assert.ThrowsAsync<InvalidOperationException>(
            () => repository.CreateAsync(new InterventionCreateCommand { Titre = "X" }));
    }

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

    [Fact]
    public async Task UpdateAsync_ValidNumericId_UpdatesIntervention()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(42, "Orig", "INT-001")];
        connection.ParametreMetierRows = [];
        var repository = new BatikInterventionRepository(connection);

        await repository.UpdateAsync("42", new InterventionUpdateCommand { Titre = "Updated", Reference = "INT-002" });

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

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

        Intervention? result = await repository.UpdateAsync("abc", new InterventionUpdateCommand());

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

    [Fact]
    public async Task UpdateAsync_AbsentIntervention_ReturnsNull()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [];
        var repository = new BatikInterventionRepository(connection);

        Intervention? result = await repository.UpdateAsync("42", new InterventionUpdateCommand());

        Assert.Null(result);
    }

    [Fact]
    public async Task UpdateAsync_TypeEtatKeysStored()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(42, "Dupont", "INT-001")];
        connection.ParametreMetierRows =
        [
            ParametreMetierRow(10, "TYP", "Maintenance"),
            ParametreMetierRow(20, "STA", "En cours")
        ];
        var repository = new BatikInterventionRepository(connection);

        await repository.UpdateAsync("42", new InterventionUpdateCommand
        {
            TypeKey = "TYP",
            EtatKey = "STA"
        });

        var updateCmd = connection.Commands.Single(c => c.Sql.StartsWith("UPDATE Intervention SET"));
        object[] p = updateCmd.Parameters.ToArray()!;
        // Les clés Type/Etat sont résolues en PA_PKID via ParametreMetier.
        Assert.Contains(10L, p);
        Assert.Contains(20L, p);
    }

    [Fact]
    public async Task UpdateAsync_PersistsCollaborateurAndPersonneRelations()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(42, "Dupont", "INT-001")];
        connection.ParametreMetierRows = [];
        var repository = new BatikInterventionRepository(connection);

        await repository.UpdateAsync("42", new InterventionUpdateCommand
        {
            CollaborateurIds = ["7", "8"],
            ContactIds = ["9"]
        });

        // Remplacement complet : DELETE des deux tables de relations.
        Assert.Contains(connection.AllSql, s => s.StartsWith("DELETE FROM Intervention_Collaborateur"));
        Assert.Contains(connection.AllSql, s => s.StartsWith("DELETE FROM Intervention_Personne"));

        // INSERT des collaborateurs (IC_IDCollaborateur = 7, 8).
        var collabInserts = connection.Commands
            .Where(c => c.Sql.StartsWith("INSERT INTO Intervention_Collaborateur"))
            .ToList();
        Assert.Equal(2, collabInserts.Count);
        Assert.Contains(collabInserts, c => c.Parameters.Any(p => Equals(p, 7L)));
        Assert.Contains(collabInserts, c => c.Parameters.Any(p => Equals(p, 8L)));

        // INSERT des personnes (IP_IDPersonne = 9).
        var personneInserts = connection.Commands
            .Where(c => c.Sql.StartsWith("INSERT INTO Intervention_Personne"))
            .ToList();
        Assert.Single(personneInserts);
        Assert.Contains(personneInserts[0].Parameters, p => Equals(p, 9L));
    }

    [Fact]
    public async Task UpdateAsync_InvalidCollaborateurId_ThrowsControlledError()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(42, "Dupont", "INT-001")];
        connection.ParametreMetierRows = [];
        var repository = new BatikInterventionRepository(connection);

        await Assert.ThrowsAsync<DomainValidationException>(() =>
            repository.UpdateAsync("42", new InterventionUpdateCommand
            {
                CollaborateurIds = ["abc", "7"]
            }));

        // Aucune écriture relationnelle partielle : ni DELETE ni INSERT.
        Assert.DoesNotContain(connection.AllSql, s => s.StartsWith("DELETE FROM Intervention_Collaborateur"));
        Assert.DoesNotContain(connection.AllSql, s => s.StartsWith("INSERT INTO Intervention_Collaborateur"));
        Assert.DoesNotContain(connection.AllSql, s => s.StartsWith("DELETE FROM Intervention_Personne"));
        Assert.DoesNotContain(connection.AllSql, s => s.StartsWith("INSERT INTO Intervention_Personne"));
    }

    [Fact]
    public async Task UpdateAsync_InvalidContactId_ThrowsControlledError()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(42, "Dupont", "INT-001")];
        connection.ParametreMetierRows = [];
        var repository = new BatikInterventionRepository(connection);

        await Assert.ThrowsAsync<DomainValidationException>(() =>
            repository.UpdateAsync("42", new InterventionUpdateCommand
            {
                ContactIds = ["xyz"]
            }));

        // Aucune écriture relationnelle partielle.
        Assert.DoesNotContain(connection.AllSql, s => s.StartsWith("DELETE FROM Intervention_Personne"));
        Assert.DoesNotContain(connection.AllSql, s => s.StartsWith("INSERT INTO Intervention_Personne"));
    }

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

    [Fact]
    public async Task ArchiveAsync_Existing_ReturnsTrue()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(42, "Dupont", "INT-001")];
        var repository = new BatikInterventionRepository(connection);

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

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

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

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

        Assert.False(result);
    }

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

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

        Assert.False(result);
    }

    [Fact]
    public async Task ArchiveAsync_NoPhysicalDelete()
    {
        var connection = new FakeHfsqlConnection();
        connection.InterventionRows = [InterventionRow(42, "Dupont", "INT-001")];
        var repository = new BatikInterventionRepository(connection);

        await repository.ArchiveAsync("42");

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

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

    [Fact]
    public void Repository_ImplementsIInterventionRepository()
    {
        Assert.True(typeof(IInterventionRepository).IsAssignableFrom(typeof(BatikInterventionRepository)));
    }

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

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

        Assert.DoesNotContain(referenced, t =>
            t != type && t.Name.Contains("Omika", StringComparison.OrdinalIgnoreCase));
    }

    [Fact]
    public void Repository_DoesNotExposeOdbcTypes()
    {
        var type = typeof(BatikInterventionRepository);
        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_DoesNotReturnInterventionDtos()
    {
        var type = typeof(BatikInterventionRepository);

        foreach (MethodInfo method in type.GetMethods(BindingFlags.Public | BindingFlags.Instance))
        {
            Assert.DoesNotContain("InterventionSummaryDto", method.ReturnType.Name);
            Assert.DoesNotContain("InterventionDetailsDto", 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?> InterventionRow(
        long pkid,
        string? titre,
        string? reference,
        long typeId = 0,
        long etatId = 0)
        => new()
        {
            ["IN_PKID"] = pkid,
            ["IN_Titre"] = titre,
            ["IN_RefInterne"] = reference,
            ["IN_IDTypeIntervention"] = typeId,
            ["IN_IDEtatIntervention"] = etatId,
            ["IN_IDClient"] = 0L,
            ["IN_IDLieu"] = 0L,
            ["IN_IDDossier"] = 0L,
            ["IN_IDSuiviPar"] = 0L,
            ["IN_IDDemandeur"] = 0L,
            ["IN_IDSociete"] = 0L,
            ["IN_Description"] = null,
            ["IN_AttestationTVAPresente"] = false,
            ["IN_NoteInterne"] = null,
            ["IN_RefExterne"] = null,
            ["IN_IDImport"] = null,
            ["IN_DureePrevue"] = 0f,
            ["IN_NumDevis"] = null,
            ["IN_NumFacture"] = null,
            ["IN_NumIntervention"] = null,
            ["Archive"] = false,
            ["CreationDate"] = DateTime.MinValue,
            ["ModifDate"] = DateTime.MinValue,
            ["DateArchivage"] = DateTime.MinValue,
            ["IN_DateIntervention"] = default(DateOnly),
            ["IN_DateDevis"] = default(DateOnly),
            ["IN_DateFacture"] = default(DateOnly)
        };

    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?> EtatInterventionRow(
        long pkid,
        long etatId,
        long interventionId,
        string? description,
        DateTime dateDebut,
        DateTime dateFin)
        => new()
        {
            ["EI_PKID"] = pkid,
            ["EI_IDEtat"] = etatId,
            ["EI_IDIntervention"] = interventionId,
            ["EI_Description"] = description,
            ["EI_DateDebut"] = dateDebut,
            ["EI_DateFin"] = dateFin
        };

    private static Dictionary<string, object?> EnrichissementRow(long pkid, string societeNom, string coNom, string coPrenom)
        => new()
        {
            ["IN_PKID"] = pkid,
            ["SO_Nom"] = societeNom,
            ["CO_Nom"] = coNom,
            ["CO_Prenom"] = coPrenom
        };

    // =========================================================================
    // 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 = [];

        public string? InterventionSql { get; private set; }
        public IReadOnlyList<object?>? InterventionParameters { 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, "Intervention"))
            {
                InterventionSql = sql;
                InterventionParameters = parameters;
            }
        }

        public IReadOnlyList<IReadOnlyDictionary<string, object?>> InterventionRows { get; set; } = [];
        public IReadOnlyList<IReadOnlyDictionary<string, object?>> ParametreMetierRows { get; set; } = [];
        public IReadOnlyList<IReadOnlyDictionary<string, object?>> EtatInterventionRows { get; set; } = [];
        public IReadOnlyList<IReadOnlyDictionary<string, object?>> EnrichissementRows { 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);
            if (sql.Contains("COUNT(*)", StringComparison.OrdinalIgnoreCase))
            {
                return Task.FromResult<object?>((long)InterventionRows.Count);
            }

            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)
        {
            if (ContainsFrom(sql, "ParametreMetier")) return ParametreMetierRows;
            if (ContainsFrom(sql, "EtatIntervention")) return EtatInterventionRows;
            if (sql.Contains("SO_Nom", StringComparison.OrdinalIgnoreCase)) return EnrichissementRows;
            if (ContainsFrom(sql, "Intervention")) return ApplyOrderByAndLimit(InterventionRows, sql);
            return [];
        }

        private static IReadOnlyList<IReadOnlyDictionary<string, object?>> ApplyOrderByAndLimit(
            IReadOnlyList<IReadOnlyDictionary<string, object?>> rows,
            string sql)
        {
            List<IReadOnlyDictionary<string, object?>> result = [.. rows];

            int orderIndex = sql.IndexOf("ORDER BY", StringComparison.OrdinalIgnoreCase);
            if (orderIndex >= 0)
            {
                string tail = sql[(orderIndex + "ORDER BY".Length)..];
                int limitIndex = tail.IndexOf("LIMIT", StringComparison.OrdinalIgnoreCase);
                string orderClause = (limitIndex >= 0 ? tail[..limitIndex] : tail).Trim();
                string[] parts = orderClause.Split(' ', StringSplitOptions.RemoveEmptyEntries);
                if (parts.Length > 0)
                {
                    string column = parts[0];
                    bool descending = parts.Length > 1
                        && parts[1].Equals("DESC", StringComparison.OrdinalIgnoreCase);

                    result.Sort((a, b) =>
                    {
                        a.TryGetValue(column, out object? va);
                        b.TryGetValue(column, out object? vb);
                        int comparison = string.Compare(
                            va?.ToString(),
                            vb?.ToString(),
                            StringComparison.Ordinal);
                        return descending ? -comparison : comparison;
                    });
                }
            }

            int limitIdx = sql.IndexOf("LIMIT", StringComparison.OrdinalIgnoreCase);
            if (limitIdx >= 0)
            {
                string limitClause = sql[(limitIdx + "LIMIT".Length)..].Trim();
                string[] parts = limitClause.Split(',', StringSplitOptions.RemoveEmptyEntries);
                int offset = int.Parse(parts[0].Trim());
                int count = parts.Length > 1 ? int.Parse(parts[1].Trim()) : result.Count;
                result = result.Skip(offset).Take(count).ToList();
            }

            return result;
        }

        private static bool ContainsFrom(string sql, string table)
        {
            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;
        }
    }
}