using System.Text.Json;
using Omika.Core.Contracts.Mobile.Sync;
using Omika.Data.Configuration;
using Omika.Data.Connection;
using Omika.Data.Repositories.Technical;


namespace Omika.Tests.Repositories.Technical;

/// <summary>
/// Tests unitaires du suivi de statut, de la purge de MobLog et du routage des
/// connexions du SyncTransferRepository.
///
/// Aucune base HFSQL réelle : les deux connexions physiques (Service pour TOSRV,
/// WebService pour MobReception/MobLog) sont simulées par des fakes qui routent
/// les requêtes vers des lignes en mémoire par table.
/// </summary>
public class SyncTransferRepositoryTests
{
    private const string DeviceId = "device-1";

    private static readonly JsonSerializerOptions JsonOptions = new()
    {
        PropertyNamingPolicy = JsonNamingPolicy.CamelCase
    };

    // =========================================================================
    // GetStatusAsync
    // =========================================================================

    [Fact]
    public async Task GetStatus_OperationInTosrv_ReturnsPending()
    {
        var operationId = Guid.NewGuid();
        var service = new FakeServiceConnection();
        service.TosrvRows =
        [
            TosrvRow(operationId, DeviceId, retryCount: 2)
        ];
        var web = new FakeWebServiceConnection();
        var repo = new SyncTransferRepository(service, web);

        var status = await repo.GetStatusAsync(operationId, DeviceId);

        Assert.NotNull(status);
        Assert.Equal(SyncStatus.Pending, status.Status);
        Assert.Equal(2, status.RetryCount);
        Assert.Equal(operationId, status.OperationId);
        Assert.Equal(DeviceId, status.DeviceId);
    }

    [Fact]
    public async Task GetStatus_OperationInMobReception_ReturnsPending()
    {
        var operationId = Guid.NewGuid();
        var service = new FakeServiceConnection();
        var web = new FakeWebServiceConnection();
        web.ReceptionRows =
        [
            ReceptionRow(operationId, DeviceId, retryCount: 1)
        ];
        var repo = new SyncTransferRepository(service, web);

        var status = await repo.GetStatusAsync(operationId, DeviceId);

        Assert.NotNull(status);
        Assert.Equal(SyncStatus.Pending, status.Status);
        Assert.Equal(1, status.RetryCount);
    }

    [Fact]
    public async Task GetStatus_SuccessInMobLog_ReturnsSuccessWithPublicGuids()
    {
        var operationId = Guid.NewGuid();
        var requested = Guid.NewGuid();
        var canonical = Guid.NewGuid();
        var service = new FakeServiceConnection();
        var web = new FakeWebServiceConnection();
        web.LogRows =
        [
            LogRow(operationId, DeviceId, SyncStatus.Success,
                requestedEntityGuid: requested, canonicalEntityGuid: canonical)
        ];
        var repo = new SyncTransferRepository(service, web);

        var status = await repo.GetStatusAsync(operationId, DeviceId);

        Assert.NotNull(status);
        Assert.Equal(SyncStatus.Success, status.Status);
        Assert.Equal(requested, status.RequestedEntityGuid);
        Assert.Equal(canonical, status.CanonicalEntityGuid);
        Assert.NotNull(status.CompletedAt);
    }

    [Fact]
    public async Task GetStatus_RejectedInMobLog_ReturnsRejectedWithError()
    {
        var operationId = Guid.NewGuid();
        var service = new FakeServiceConnection();
        var web = new FakeWebServiceConnection();
        web.LogRows =
        [
            LogRow(operationId, DeviceId, SyncStatus.Rejected,
                errorCode: "MAX_RETRY", errorMessage: "Rejet définitif")
        ];
        var repo = new SyncTransferRepository(service, web);

        var status = await repo.GetStatusAsync(operationId, DeviceId);

        Assert.NotNull(status);
        Assert.Equal(SyncStatus.Rejected, status.Status);
        Assert.Equal("MAX_RETRY", status.ErrorCode);
        Assert.Equal("Rejet définitif", status.ErrorMessage);
    }

    [Fact]
    public async Task GetStatus_Unknown_ReturnsNull()
    {
        var service = new FakeServiceConnection();
        var web = new FakeWebServiceConnection();
        var repo = new SyncTransferRepository(service, web);

        var status = await repo.GetStatusAsync(Guid.NewGuid(), DeviceId);

        Assert.Null(status);
    }

    [Fact]
    public async Task GetStatus_OtherDevice_ReturnsNull_NoLeak()
    {
        var operationId = Guid.NewGuid();
        var service = new FakeServiceConnection();
        var web = new FakeWebServiceConnection();
        // L'opération existe mais appartient à un autre appareil.
        web.LogRows =
        [
            LogRow(operationId, "autre-device", SyncStatus.Success)
        ];
        var repo = new SyncTransferRepository(service, web);

        var status = await repo.GetStatusAsync(operationId, DeviceId);

        // Aucune information métier sensible ne doit fuiter vers un autre device.
        Assert.Null(status);
    }

    // =========================================================================
    // PurgeLogsAsync
    // =========================================================================

    [Fact]
    public async Task Purge_OldTerminalSyncLog_EmitsDeleteWithCutoff()
    {
        var cutoff = DateTime.UtcNow.AddDays(-31);
        var service = new FakeServiceConnection();
        var web = new FakeWebServiceConnection();
        var repo = new SyncTransferRepository(service, web);

        await repo.PurgeLogsAsync(cutoff);

        var delete = web.Commands.Single(c => c.Sql.StartsWith("DELETE FROM MobLog"));
        Assert.NotNull(delete);
        // Le cutoff est passé en paramètre.
        Assert.Equal(cutoff, delete.Parameters[0]);
        // Ciblage strict : uniquement les logs structurés Sync terminaux.
        // Les marqueurs sont passés en paramètres SQL (LIKE ?), pas littéralement
        // dans le SQL. On vérifie donc les valeurs des paramètres.
        Assert.Equal("%\"schemaVersion\"%", delete.Parameters[1]);
        Assert.Equal("%\"operationId\"%", delete.Parameters[2]);
        Assert.Equal($"%\"status\":\"{SyncStatus.Success}\"%", delete.Parameters[3]);
        Assert.Equal($"%\"status\":\"{SyncStatus.Rejected}\"%", delete.Parameters[4]);
    }

    [Fact]
    public async Task Purge_RecentTerminalSyncLog_Kept()
    {
        // Un log récent ne doit pas être éligible : le cutoff est dans le futur
        // par rapport à la date du log. On vérifie que le SQL DELETE est bien
        // émis avec un cutoff qui exclut les logs récents (le filtrage par date
        // est appliqué côté base).
        var cutoff = DateTime.UtcNow.AddDays(-1);
        var service = new FakeServiceConnection();
        var web = new FakeWebServiceConnection();
        var repo = new SyncTransferRepository(service, web);

        await repo.PurgeLogsAsync(cutoff);

        var delete = web.Commands.Single(c => c.Sql.StartsWith("DELETE FROM MobLog"));
        Assert.Equal(cutoff, delete.Parameters[0]);
    }

    [Fact]
    public async Task Purge_NonSyncLog_NeverDeleted()
    {
        // Le SQL de purge ne doit jamais cibler les logs non-Sync : il exige
        // la présence de "schemaVersion" et "operationId" (format structuré
        // propre au mécanisme de synchronisation mobile).
        var cutoff = DateTime.UtcNow.AddDays(-31);
        var service = new FakeServiceConnection();
        var web = new FakeWebServiceConnection();
        var repo = new SyncTransferRepository(service, web);

        await repo.PurgeLogsAsync(cutoff);

        var delete = web.Commands.Single(c => c.Sql.StartsWith("DELETE FROM MobLog"));
        // Les marqueurs "schemaVersion" et "operationId" sont passés en
        // paramètres SQL (LIKE ?), pas littéralement dans le SQL.
        Assert.Equal("%\"schemaVersion\"%", delete.Parameters[1]);
        Assert.Equal("%\"operationId\"%", delete.Parameters[2]);
    }

    [Fact]
    public async Task Purge_PendingSyncLog_NeverDeleted()
    {
        // Le SQL de purge ne doit jamais cibler les opérations PENDING : il ne
        // filtre que sur les statuts terminaux SUCCESS / REJECTED.
        var cutoff = DateTime.UtcNow.AddDays(-31);
        var service = new FakeServiceConnection();
        var web = new FakeWebServiceConnection();
        var repo = new SyncTransferRepository(service, web);

        await repo.PurgeLogsAsync(cutoff);

        var delete = web.Commands.Single(c => c.Sql.StartsWith("DELETE FROM MobLog"));
        // Les statuts sont passés en paramètres SQL (LIKE ?). On vérifie que
        // seuls SUCCESS et REJECTED sont ciblés, jamais PENDING.
        Assert.DoesNotContain(delete.Parameters, p => p is string s && s.Contains($"\"status\":\"{SyncStatus.Pending}\""));
        Assert.Equal($"%\"status\":\"{SyncStatus.Success}\"%", delete.Parameters[3]);
        Assert.Equal($"%\"status\":\"{SyncStatus.Rejected}\"%", delete.Parameters[4]);
    }

    // =========================================================================
    // Routage des connexions
    // =========================================================================

    [Fact]
    public async Task InsertToSrv_UsesServiceConnection_NotWebService()
    {
        var service = new FakeServiceConnection();
        var web = new FakeWebServiceConnection();
        var repo = new SyncTransferRepository(service, web);

        await repo.InsertToSrvAsync(new SyncEnvelope
        {
            OperationId = Guid.NewGuid(),
            DeviceId = DeviceId,
            EntityType = "Client",
            OperationType = SyncOperationType.Create,
            EntityGuid = Guid.NewGuid(),
            RetryCount = 0,
            Payload = "{}"
        });

        // L'INSERT TOSRV doit être émis sur la connexion Service.
        Assert.Contains(service.Commands, c => c.Sql.StartsWith("INSERT INTO TOSRV"));
        // Aucune commande ne doit être émise sur la connexion WebService.
        Assert.Empty(web.Commands);
    }

    [Fact]
    public async Task GetTerminalResult_UsesWebServiceConnection_NotService()
    {
        var operationId = Guid.NewGuid();
        var service = new FakeServiceConnection();
        var web = new FakeWebServiceConnection();
        web.LogRows =
        [
            LogRow(operationId, DeviceId, SyncStatus.Success)
        ];
        var repo = new SyncTransferRepository(service, web);

        var result = await repo.GetTerminalResultAsync(operationId);

        Assert.NotNull(result);
        // La lecture MobLog doit être émise sur la connexion WebService.
        Assert.Contains(web.Commands, c => c.Sql.Contains("FROM MobLog"));
        // Aucune commande ne doit être émise sur la connexion Service.
        Assert.Empty(service.Commands);
    }

    [Fact]
    public async Task WriteLog_UsesWebServiceConnection_NotService()
    {
        var service = new FakeServiceConnection();
        var web = new FakeWebServiceConnection();
        var repo = new SyncTransferRepository(service, web);

        await repo.WriteLogAsync(new SyncLogEntry
        {
            OperationId = Guid.NewGuid(),
            Status = SyncStatus.Success,
            DeviceId = DeviceId,
            EntityType = "Client",
            OperationType = SyncOperationType.Create,
            EntityGuid = Guid.NewGuid(),
            RetryCount = 0,
            DateUtc = DateTimeOffset.UtcNow
        });

        // L'INSERT MobLog doit être émis sur la connexion WebService.
        Assert.Contains(web.Commands, c => c.Sql.StartsWith("INSERT INTO MobLog"));
        // Aucune commande ne doit être émise sur la connexion Service.
        Assert.Empty(service.Commands);
    }

    [Fact]
    public async Task GetPendingOperations_UsesWebServiceConnection_NotService()
    {
        var service = new FakeServiceConnection();
        var web = new FakeWebServiceConnection();
        var repo = new SyncTransferRepository(service, web);

        await repo.GetPendingOperationsAsync();

        // La lecture MobReception doit être émise sur la connexion WebService.
        Assert.Contains(web.Commands, c => c.Sql.Contains("FROM MobReception"));
        // Aucune commande ne doit être émise sur la connexion Service.
        Assert.Empty(service.Commands);
    }

    [Fact]
    public async Task MarkSuccess_UsesWebServiceConnection_NotService()
    {
        var service = new FakeServiceConnection();
        var web = new FakeWebServiceConnection();
        var repo = new SyncTransferRepository(service, web);

        await repo.MarkSuccessAsync(new SyncLogEntry
        {
            OperationId = Guid.NewGuid(),
            Status = SyncStatus.Success,
            DeviceId = DeviceId,
            EntityType = "Client",
            OperationType = SyncOperationType.Create,
            EntityGuid = Guid.NewGuid(),
            RetryCount = 0,
            DateUtc = DateTimeOffset.UtcNow
        });

        // Le DELETE MobReception et l'INSERT MobLog doivent être émis sur la
        // connexion WebService.
        Assert.Contains(web.Commands, c => c.Sql.StartsWith("DELETE FROM MobReception"));
        Assert.Contains(web.Commands, c => c.Sql.StartsWith("INSERT INTO MobLog"));
        // Aucune commande ne doit être émise sur la connexion Service.
        Assert.Empty(service.Commands);
    }

    [Fact]
    public async Task MarkRejected_UsesWebServiceConnection_NotService()
    {
        var service = new FakeServiceConnection();
        var web = new FakeWebServiceConnection();
        var repo = new SyncTransferRepository(service, web);

        await repo.MarkRejectedAsync(new SyncLogEntry
        {
            OperationId = Guid.NewGuid(),
            Status = SyncStatus.Rejected,
            DeviceId = DeviceId,
            EntityType = "Client",
            OperationType = SyncOperationType.Create,
            EntityGuid = Guid.NewGuid(),
            RetryCount = 0,
            DateUtc = DateTimeOffset.UtcNow
        });

        // L'UPDATE MobReception et l'INSERT MobLog doivent être émis sur la
        // connexion WebService.
        Assert.Contains(web.Commands, c => c.Sql.StartsWith("UPDATE MobReception"));
        Assert.Contains(web.Commands, c => c.Sql.StartsWith("INSERT INTO MobLog"));
        // Aucune commande ne doit être émise sur la connexion Service.
        Assert.Empty(service.Commands);
    }

    // =========================================================================
    // TransferToReceptionAsync (inter-connexion)
    // =========================================================================

    [Fact]
    public async Task TransferToReception_TosrvToMobReception_ThenDeleteTosrv()
    {
        var operationId = Guid.NewGuid();
        var service = new FakeServiceConnection();
        service.TosrvRows =
        [
            TosrvRow(operationId, DeviceId, retryCount: 0)
        ];
        var web = new FakeWebServiceConnection();
        var repo = new SyncTransferRepository(service, web);

        int transferred = await repo.TransferToReceptionAsync();

        Assert.Equal(1, transferred);
        // Lecture TOSRV sur la connexion Service.
        Assert.Contains(service.Commands, c => c.Sql.Contains("FROM TOSRV"));
        // Insertion MobReception sur la connexion WebService.
        Assert.Contains(web.Commands, c => c.Sql.StartsWith("INSERT INTO MobReception"));
        // Suppression TOSRV sur la connexion Service (après succès de l'insertion).
        Assert.Contains(service.Commands, c => c.Sql.StartsWith("DELETE FROM TOSRV"));
    }

    [Fact]
    public async Task TransferToReception_InsertionFails_TosrvNotLost()
    {
        var operationId = Guid.NewGuid();
        var service = new FakeServiceConnection();
        service.TosrvRows =
        [
            TosrvRow(operationId, DeviceId, retryCount: 0)
        ];
        var web = new FakeWebServiceConnection();
        // L'insertion MobReception échoue.
        web.FailInsertMobReception = true;
        var repo = new SyncTransferRepository(service, web);

        await Assert.ThrowsAsync<InvalidOperationException>(() => repo.TransferToReceptionAsync());

        // TOSRV ne doit PAS être supprimé : l'opération n'est pas perdue.
        Assert.DoesNotContain(service.Commands, c => c.Sql.StartsWith("DELETE FROM TOSRV"));
    }

    [Fact]
    public async Task TransferToReception_AlreadyInMobReception_DoesNotDuplicate()
    {
        var operationId = Guid.NewGuid();
        var service = new FakeServiceConnection();
        service.TosrvRows =
        [
            TosrvRow(operationId, DeviceId, retryCount: 0)
        ];
        var web = new FakeWebServiceConnection();
        // L'opération existe déjà dans MobReception (crash après insertion avant
        // suppression TOSRV). Un nouveau cycle ne doit pas recréer MobReception.
        web.ReceptionRows =
        [
            ReceptionRow(operationId, DeviceId, retryCount: 0)
        ];
        var repo = new SyncTransferRepository(service, web);

        int transferred = await repo.TransferToReceptionAsync();

        // Aucune nouvelle insertion MobReception (idempotence par OperationId).
        Assert.DoesNotContain(web.Commands, c => c.Sql.StartsWith("INSERT INTO MobReception"));
        // TOSRV est nettoyé car l'opération est déjà connue dans MobReception.
        Assert.Contains(service.Commands, c => c.Sql.StartsWith("DELETE FROM TOSRV"));
    }

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

    private static Dictionary<string, object?> TosrvRow(
        Guid operationId, string deviceId, int retryCount)
    {
        var envelope = new SyncEnvelope
        {
            OperationId = operationId,
            DeviceId = deviceId,
            EntityType = "Client",
            OperationType = SyncOperationType.Create,
            EntityGuid = Guid.NewGuid(),
            RetryCount = retryCount,
            Payload = "{}"
        };
        return new Dictionary<string, object?>
        {
            ["IDTOSRV"] = 1,
            ["TS_Date"] = DateTime.Now,
            ["TS_Mobile"] = deviceId,
            ["TS_CMD"] = "MOB.OP",
            ["TS_Buffer"] = null,
            ["TS_Lu"] = false,
            ["TS_JSON"] = JsonSerializer.Serialize(envelope, JsonOptions)
        };
    }

    private static Dictionary<string, object?> ReceptionRow(
        Guid operationId, string deviceId, int retryCount)
    {
        var envelope = new SyncEnvelope
        {
            OperationId = operationId,
            DeviceId = deviceId,
            EntityType = "Client",
            OperationType = SyncOperationType.Create,
            EntityGuid = Guid.NewGuid(),
            RetryCount = retryCount,
            Payload = "{}"
        };
        return new Dictionary<string, object?>
        {
            ["MR_PKID"] = 1L,
            ["MR_IDToSRV"] = 1L,
            ["MR_JSON"] = JsonSerializer.Serialize(envelope, JsonOptions),
            ["MR_CMD"] = "MOB.OP",
            ["MR_Buffer"] = null,
            ["MR_NumSerie"] = null,
            ["MR_Fait"] = false,
            ["MR_DateHeureRecep"] = DateTime.Now
        };
    }

    private static Dictionary<string, object?> LogRow(
        Guid operationId,
        string deviceId,
        SyncStatus status,
        Guid? requestedEntityGuid = null,
        Guid? canonicalEntityGuid = null,
        string? errorCode = null,
        string? errorMessage = null)
    {
        var entry = new SyncLogEntry
        {
            OperationId = operationId,
            Status = status,
            DeviceId = deviceId,
            EntityType = "Client",
            OperationType = SyncOperationType.Create,
            EntityGuid = Guid.NewGuid(),
            RequestedEntityGuid = requestedEntityGuid,
            CanonicalEntityGuid = canonicalEntityGuid,
            RetryCount = 0,
            ErrorCode = errorCode,
            ErrorMessage = errorMessage,
            DateUtc = DateTimeOffset.UtcNow
        };
        var message = JsonSerializer.Serialize(new Dictionary<string, object?>
        {
            ["schemaVersion"] = 1,
            ["operationId"] = operationId,
            ["status"] = status.ToString(),
            ["deviceId"] = deviceId,
            ["entityType"] = "Client",
            ["operationType"] = "Create",
            ["entityGuid"] = entry.EntityGuid,
            ["requestedEntityGuid"] = requestedEntityGuid,
            ["canonicalEntityGuid"] = canonicalEntityGuid,
            ["retryCount"] = 0,
            ["errorCode"] = errorCode,
            ["errorMessage"] = errorMessage,
            ["completedAtUtc"] = DateTime.UtcNow
        }, JsonOptions);

        return new Dictionary<string, object?>
        {
            ["ML_PKID"] = 1L,
            ["ML_DateCreat"] = DateTime.Now,
            ["ML_Message"] = message,
            ["ML_Niveau"] = (sbyte)0
        };
    }

    // =========================================================================
    // Fakes de connexion (Service et WebService)
    // =========================================================================

    /// <summary>
    /// Fake de la connexion Service : route les requêtes TOSRV vers des lignes
    /// en mémoire et enregistre les commandes exécutées.
    /// </summary>
    private sealed class FakeServiceConnection : IServiceHfSqlConnection
    {
        public IReadOnlyList<IReadOnlyDictionary<string, object?>> TosrvRows { get; set; } = [];
        public List<ExecutedCommand> Commands { get; } = [];

        public HfsqlOptions Options => new();
        public HfsqlConnectionState State { get; private set; } = HfsqlConnectionState.Closed;

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

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

        public Task<object?> ExecuteScalarAsync(
            string sql,
            IReadOnlyList<object?>? parameters = null,
            CancellationToken ct = default)
        {
            Commands.Add(new ExecutedCommand(sql, parameters ?? []));
            return Task.FromResult<object?>(null);
        }

        public Task<int> ExecuteNonQueryAsync(
            string sql,
            IReadOnlyList<object?>? parameters = null,
            CancellationToken ct = default)
        {
            Commands.Add(new ExecutedCommand(sql, parameters ?? []));
            return Task.FromResult(1);
        }

        public Task<IReadOnlyList<IReadOnlyDictionary<string, object?>>> QueryAsync(
            string sql,
            IReadOnlyList<object?>? parameters = null,
            CancellationToken ct = default)
        {
            Commands.Add(new ExecutedCommand(sql, parameters ?? []));
            if (ContainsFrom(sql, "TOSRV")) return Task.FromResult(TosrvRows);
            return Task.FromResult<IReadOnlyList<IReadOnlyDictionary<string, object?>>>([]);
        }

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

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

    /// <summary>
    /// Fake de la connexion WebService/Business : route les requêtes MobReception
    /// et MobLog vers des lignes en mémoire et enregistre les commandes exécutées.
    /// </summary>
    private sealed class FakeWebServiceConnection : IWebServiceHfSqlConnection
    {
        public IReadOnlyList<IReadOnlyDictionary<string, object?>> LogRows { get; set; } = [];
        public IReadOnlyList<IReadOnlyDictionary<string, object?>> ReceptionRows { get; set; } = [];
        public bool FailInsertMobReception { get; set; }
        public List<ExecutedCommand> Commands { get; } = [];

        public HfsqlOptions Options => new();
        public HfsqlConnectionState State { get; private set; } = HfsqlConnectionState.Closed;

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

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

        public Task<object?> ExecuteScalarAsync(
            string sql,
            IReadOnlyList<object?>? parameters = null,
            CancellationToken ct = default)
        {
            Commands.Add(new ExecutedCommand(sql, parameters ?? []));
            return Task.FromResult<object?>(null);
        }

        public Task<int> ExecuteNonQueryAsync(
            string sql,
            IReadOnlyList<object?>? parameters = null,
            CancellationToken ct = default)
        {
            Commands.Add(new ExecutedCommand(sql, parameters ?? []));
            if (FailInsertMobReception && sql.StartsWith("INSERT INTO MobReception"))
            {
                throw new InvalidOperationException("Échec simulé de l'insertion MobReception.");
            }
            return Task.FromResult(1);
        }

        public Task<IReadOnlyList<IReadOnlyDictionary<string, object?>>> QueryAsync(
            string sql,
            IReadOnlyList<object?>? parameters = null,
            CancellationToken ct = default)
        {
            Commands.Add(new ExecutedCommand(sql, parameters ?? []));
            if (ContainsFrom(sql, "MobLog")) return Task.FromResult(LogRows);
            if (ContainsFrom(sql, "MobReception")) return Task.FromResult(ReceptionRows);
            return Task.FromResult<IReadOnlyList<IReadOnlyDictionary<string, object?>>>([]);
        }

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

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

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

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