using Omika.Core.Observability;
using Omika.Diagnostics.Models;
using Omika.Diagnostics.Queries;
using Omika.Diagnostics.Repositories;

namespace Omika.Diagnostics.Tests.Fakes;

/// <summary>
/// Repository in-memory (sans PostgreSQL, §51) : reproduit les mêmes contrats que
/// <c>NpgsqlDiagnosticEventRepository</c> (déduplication par event_id, tri, pagination).
/// </summary>
public sealed class FakeDiagnosticEventRepository : IDiagnosticEventRepository
{
    private readonly Dictionary<Guid, DiagnosticEventRecord> _byEventId = new();
    private readonly List<DiagnosticEventRecord> _all = new();
    private readonly object _sync = new();

    /// <summary>
    /// Identifiants URL-safe des groupes d'erreurs « traités » (tâche en statut terminal
    /// Résolu/Rejeté). Reproduit la jointure SQL de
    /// <c>NpgsqlDiagnosticEventRepository.DeleteOlderThanAsync</c> : seuls les événements
    /// de ces groupes sont éligibles à la purge.
    /// </summary>
    private readonly HashSet<string> _treatedGroups = new(StringComparer.Ordinal);

    public int InsertedCount { get; private set; }

    /// <summary>Marque un groupe d'erreurs comme « traité » (tâche terminale), donc purgable.</summary>
    public void MarkGroupTreated(string groupUrlIdentifier)
    {
        lock (_sync)
        {
            _treatedGroups.Add(groupUrlIdentifier);
        }
    }

    public Task<int> InsertBatchAsync(
        IReadOnlyList<DiagnosticEventRecord> events,
        CancellationToken cancellationToken = default)
    {
        lock (_sync)
        {
            int inserted = 0;
            foreach (DiagnosticEventRecord e in events)
            {
                if (_byEventId.TryAdd(e.EventId, e))
                {
                    _all.Add(e);
                    inserted++;
                }
            }

            InsertedCount += inserted;
            return Task.FromResult(inserted);
        }
    }

    public Task<DiagnosticQueryResult<DiagnosticEventRecord>> QueryAsync(
        DiagnosticEventQuery query,
        CancellationToken cancellationToken = default)
    {
        List<DiagnosticEventRecord> all;
        lock (_sync)
        {
            all = new List<DiagnosticEventRecord>(_all);
        }

        IEnumerable<DiagnosticEventRecord> filtered = all.Where(e =>
            (query.FromUtc is null || e.TimestampUtc >= query.FromUtc) &&
            (query.ToUtc is null || e.TimestampUtc <= query.ToUtc) &&
            (query.ErrorsOnly ? DiagnosticErrorLevels.IsError(e.Level) : (string.IsNullOrWhiteSpace(query.Level) || e.Level == query.Level)) &&
            (string.IsNullOrWhiteSpace(query.Priority) || e.Priority == query.Priority) &&
            (string.IsNullOrWhiteSpace(query.Component) || e.Component == query.Component) &&
            (query.SyncOnly ? DiagnosticSyncEventCodes.IsSyncEventCode(e.EventCode) : (string.IsNullOrWhiteSpace(query.EventCode) || e.EventCode == query.EventCode)) &&
            (string.IsNullOrWhiteSpace(query.ErrorCode) || e.ErrorCode == query.ErrorCode) &&
            (string.IsNullOrWhiteSpace(query.AppVersion) || e.AppVersion == query.AppVersion) &&
            (string.IsNullOrWhiteSpace(query.Platform) || e.Platform == query.Platform) &&
            (string.IsNullOrWhiteSpace(query.ActorKey) || e.ActorKey == query.ActorKey) &&
            (string.IsNullOrWhiteSpace(query.ClientCode) || e.ClientCode == query.ClientCode) &&
            (string.IsNullOrWhiteSpace(query.ServerInstance) || e.ServerInstance == query.ServerInstance) &&
            (string.IsNullOrWhiteSpace(query.Environment) || e.Environment == query.Environment) &&
            (string.IsNullOrWhiteSpace(query.LicenseNumber) || e.LicenseNumber == query.LicenseNumber) &&
            (string.IsNullOrWhiteSpace(query.DeviceKey) || e.DeviceKey == query.DeviceKey) &&
            (string.IsNullOrWhiteSpace(query.Manufacturer) || e.DeviceManufacturer == query.Manufacturer) &&
            (string.IsNullOrWhiteSpace(query.DeviceModel) || e.DeviceModel == query.DeviceModel) &&
            (string.IsNullOrWhiteSpace(query.OperatingSystem) || e.OperatingSystem == query.OperatingSystem) &&
            (string.IsNullOrWhiteSpace(query.OperatingSystemVersion) || e.OperatingSystemVersion == query.OperatingSystemVersion) &&
            (string.IsNullOrWhiteSpace(query.ScreenCode) || e.ScreenCode == query.ScreenCode) &&
            (string.IsNullOrWhiteSpace(query.FeatureCode) || e.FeatureCode == query.FeatureCode));

        List<DiagnosticEventRecord> ordered = filtered
            .OrderByDescending(e => e.TimestampUtc)
            .ThenByDescending(e => e.Id)
            .ToList();

        List<DiagnosticEventRecord> page = ordered
            .Skip(query.Offset)
            .Take(query.PageSize)
            .ToList();

        return Task.FromResult(new DiagnosticQueryResult<DiagnosticEventRecord>
        {
            Items = page,
            TotalCount = ordered.Count,
            Page = query.Page,
            PageSize = query.PageSize
        });
    }

    public Task<IReadOnlyList<DiagnosticEventRecord>> GetByTraceIdAsync(
        Guid traceId,
        CancellationToken cancellationToken = default) =>
        Task.FromResult<IReadOnlyList<DiagnosticEventRecord>>(
            _all.Where(e => e.TraceId == traceId)
                .OrderBy(e => e.TimestampUtc)
                .ThenBy(e => e.Id)
                .ToList());

    public Task<IReadOnlyList<DiagnosticEventRecord>> GetByOperationIdAsync(
        Guid operationId,
        CancellationToken cancellationToken = default) =>
        Task.FromResult<IReadOnlyList<DiagnosticEventRecord>>(
            _all.Where(e => e.OperationId == operationId)
                .OrderBy(e => e.TimestampUtc)
                .ThenBy(e => e.Id)
                .ToList());

    public Task<DiagnosticEventRecord?> GetByIdAsync(
        long id,
        CancellationToken cancellationToken = default)
    {
        DiagnosticEventRecord? match = _all.FirstOrDefault(e => e.Id == id);
        return Task.FromResult(match);
    }

    public Task<IReadOnlyList<DiagnosticEventRecord>> GetCorrelatedAsync(
        DiagnosticCorrelationQuery query,
        CancellationToken cancellationToken = default)
    {
        query.Validate();

        if (!query.HasAnyIdentifier)
        {
            return Task.FromResult<IReadOnlyList<DiagnosticEventRecord>>([]);
        }

        IReadOnlyList<DiagnosticEventRecord> matches = _all
            .Where(e =>
                e.TimestampUtc >= query.FromUtc &&
                e.TimestampUtc < query.ToUtc &&
                ((query.TraceId is not null && e.TraceId == query.TraceId) ||
                 (query.OperationId is not null && e.OperationId == query.OperationId) ||
                 (query.SessionId is not null && e.SessionId == query.SessionId) ||
                 (!string.IsNullOrWhiteSpace(query.ActorKey) && e.ActorKey == query.ActorKey)))
            .ToList();

        // Miroir de NpgsqlDiagnosticEventRepository.GetCorrelatedAsync : sans ancre, les
        // plus anciens de la fenêtre ; avec ancre, les plus proches de l'événement
        // consulté. Restitution chronologique dans les deux cas.
        IEnumerable<DiagnosticEventRecord> selection = query.AnchorUtc is { } anchor
            ? matches
                .OrderBy(e => Math.Abs((e.TimestampUtc - anchor).Ticks))
                .ThenBy(e => e.TimestampUtc)
                .ThenBy(e => e.Id)
            : matches
                .OrderBy(e => e.TimestampUtc)
                .ThenBy(e => e.Id);

        IReadOnlyList<DiagnosticEventRecord> result = selection
            .Take(query.Limit)
            .OrderBy(e => e.TimestampUtc)
            .ThenBy(e => e.Id)
            .ToList();

        return Task.FromResult(result);
    }

    public Task<bool> IsAvailableAsync(CancellationToken cancellationToken = default) =>
        Task.FromResult(true);

    public Task<IReadOnlyList<string>> GetFilterOptionsAsync(
        DiagnosticFilterDimension dimension,
        DateTimeOffset fromUtc,
        int limit,
        CancellationToken cancellationToken = default)
    {
        int safeLimit = Math.Clamp(limit, 1, DiagnosticAggregateDefaults.MaxLimit);

        Func<DiagnosticEventRecord, string?> selector = dimension switch
        {
            DiagnosticFilterDimension.EventCode => e => e.EventCode,
            DiagnosticFilterDimension.ErrorCode => e => e.ErrorCode,
            DiagnosticFilterDimension.Component => e => e.Component,
            DiagnosticFilterDimension.Platform => e => e.Platform,
            DiagnosticFilterDimension.AppVersion => e => e.AppVersion,
            DiagnosticFilterDimension.ClientCode => e => e.ClientCode,
            DiagnosticFilterDimension.ServerInstance => e => e.ServerInstance,
            DiagnosticFilterDimension.Environment => e => e.Environment,
            DiagnosticFilterDimension.Manufacturer => e => e.DeviceManufacturer,
            DiagnosticFilterDimension.DeviceModel => e => e.DeviceModel,
            DiagnosticFilterDimension.OperatingSystem => e => e.OperatingSystem,
            DiagnosticFilterDimension.OperatingSystemVersion => e => e.OperatingSystemVersion,
            DiagnosticFilterDimension.ScreenCode => e => e.ScreenCode,
            DiagnosticFilterDimension.FeatureCode => e => e.FeatureCode,
            _ => throw new ArgumentOutOfRangeException(nameof(dimension), dimension, "Dimension de filtre inconnue.")
        };

        // Miroir de NpgsqlDiagnosticEventRepository.GetFilterOptionsAsync (T20 §3-5) :
        // Component/ErrorCode sont normalisés (mêmes règles que les agrégations) avant
        // déduplication, exactement comme la projection SQL canonique.
        Func<string?, string?> normalize = dimension switch
        {
            DiagnosticFilterDimension.Component => DiagnosticDimensionNormalization.Component,
            DiagnosticFilterDimension.ErrorCode => DiagnosticDimensionNormalization.ErrorCode,
            _ => static v => v
        };

        IReadOnlyList<string> result = _all
            .Where(e => e.TimestampUtc >= fromUtc)
            .Select(selector)
            .Where(v => !string.IsNullOrWhiteSpace(v))
            .Select(normalize)
            .Where(v => v is not null)
            .Select(v => v!)
            .Distinct(StringComparer.Ordinal)
            .OrderBy(v => v, StringComparer.Ordinal)
            .Take(safeLimit)
            .ToList();

        return Task.FromResult(result);
    }

    public Task<IReadOnlyList<DiagnosticRecentCorrelation>> GetRecentCorrelationsAsync(
        DiagnosticCorrelationDimension dimension,
        DateTimeOffset fromUtc,
        int limit,
        CancellationToken cancellationToken = default)
    {
        int safeLimit = Math.Clamp(limit, 1, DiagnosticAggregateDefaults.MaxLimit);

        Func<DiagnosticEventRecord, Guid?> idSelector = dimension switch
        {
            DiagnosticCorrelationDimension.Trace => e => e.TraceId,
            DiagnosticCorrelationDimension.Operation => e => e.OperationId,
            _ => throw new ArgumentOutOfRangeException(nameof(dimension), dimension, "Dimension de corrélation inconnue.")
        };

        IReadOnlyList<DiagnosticRecentCorrelation> result = _all
            .Where(e => e.TimestampUtc >= fromUtc && idSelector(e) is not null)
            .GroupBy(e => idSelector(e)!.Value)
            .Select(g => new DiagnosticRecentCorrelation(
                g.Key,
                g.LongCount(),
                g.Min(e => e.TimestampUtc),
                g.Max(e => e.TimestampUtc),
                MinRaw(g.Select(e => e.Component)),
                MinRaw(g.Select(e => e.AppVersion))))
            .OrderByDescending(x => x.LastSeenUtc)
            .ThenBy(x => x.CorrelationId)
            .Take(safeLimit)
            .ToList();

        return Task.FromResult<IReadOnlyList<DiagnosticRecentCorrelation>>(result);
    }

    public Task<int> DeleteOlderThanAsync(
        DateTimeOffset cutoff,
        int batchSize,
        CancellationToken cancellationToken = default)
    {
        if (batchSize <= 0)
        {
            return Task.FromResult(0);
        }

        lock (_sync)
        {
            List<DiagnosticEventRecord> toDelete = _all
                .Where(e => e.TimestampUtc < cutoff)
                // Règle de conservation (miroir de NpgsqlDiagnosticEventRepository) :
                // les NON-erreurs sont toujours purgées ; les ERREURS ne le sont que si
                // leur groupe est « traité » (tâche terminale).
                .Where(e => !DiagnosticErrorLevels.IsError(e.Level) || _treatedGroups.Contains(GroupIdentifier(e)))
                .OrderBy(e => e.TimestampUtc)
                .ThenBy(e => e.Id)
                .Take(batchSize)
                .ToList();

            foreach (DiagnosticEventRecord e in toDelete)
            {
                _all.Remove(e);
                _byEventId.Remove(e.EventId);
            }

            return Task.FromResult(toDelete.Count);
        }
    }

    public Task<long> DeleteErrorGroupAsync(
        DiagnosticErrorGroupIdentity identity,
        CancellationToken cancellationToken = default)
    {
        lock (_sync)
        {
            // Miroir de NpgsqlDiagnosticEventRepository.DeleteErrorGroupAsync : aucune
            // borne de période, identité normalisée (event_code + 3 clés) et restriction
            // aux niveaux d'erreur réels. Un événement non-erreur de même signature n'est
            // jamais supprimé.
            List<DiagnosticEventRecord> toDelete = _all
                .Where(e => DiagnosticErrorLevels.IsError(e.Level))
                .Where(e => e.EventCode == identity.EventCode)
                .Where(e => DiagnosticErrorGrouping.NormalizeKey(e.ErrorCode) == identity.NormalizedErrorCode)
                .Where(e => DiagnosticErrorGrouping.NormalizeKey(e.ExceptionType) == identity.NormalizedExceptionType)
                .Where(e => DiagnosticErrorGrouping.NormalizeKey(e.Component) == identity.NormalizedComponent)
                .ToList();

            foreach (DiagnosticEventRecord e in toDelete)
            {
                _all.Remove(e);
                _byEventId.Remove(e.EventId);
            }

            return Task.FromResult((long)toDelete.Count);
        }
    }

    /// <summary>Identifiant URL-safe du groupe d'un événement (équivalent SQL de la purge).</summary>
    private static string GroupIdentifier(DiagnosticEventRecord e) =>
        DiagnosticErrorGroupLink.Encode(new DiagnosticErrorGroupIdentity(
            e.EventCode,
            DiagnosticErrorGrouping.NormalizeKey(e.ErrorCode),
            DiagnosticErrorGrouping.NormalizeKey(e.ExceptionType),
            DiagnosticErrorGrouping.NormalizeKey(e.Component)));

    public Task<IReadOnlyList<DiagnosticGroupCount>> CountGroupedAsync(
        DiagnosticGroupDimension dimension,
        DiagnosticAggregateQuery query,
        CancellationToken cancellationToken = default)
    {
        query.Validate();

        Func<DiagnosticEventRecord, string?> keySelector = dimension switch
        {
            DiagnosticGroupDimension.EventCode => e => e.EventCode,
            DiagnosticGroupDimension.ErrorCode => e => DiagnosticDimensionNormalization.ErrorCode(e.ErrorCode),
            DiagnosticGroupDimension.AppVersion => e => e.AppVersion,
            DiagnosticGroupDimension.Component => e => DiagnosticDimensionNormalization.Component(e.Component),
            DiagnosticGroupDimension.Level => e => DiagnosticLevels.Normalize(e.Level),
            DiagnosticGroupDimension.Priority => e => e.Priority,
            DiagnosticGroupDimension.Platform => e => e.Platform,
            DiagnosticGroupDimension.Source => e => e.Source,
            _ => throw new ArgumentOutOfRangeException(nameof(dimension), dimension, "Dimension de groupement inconnue.")
        };

        IReadOnlyList<DiagnosticGroupCount> result = Filter(query)
            .GroupBy(keySelector)
            .Select(g => new DiagnosticGroupCount(g.Key, g.LongCount()))
            .OrderByDescending(x => x.Count)
            .ThenBy(x => x.Key, StringComparer.Ordinal)
            .Take(query.Limit)
            .ToList();

        return Task.FromResult(result);
    }

    public Task<IReadOnlyList<DiagnosticBucketCount>> CountByTimeBucketAsync(
        DiagnosticTimeBucket bucket,
        DiagnosticAggregateQuery query,
        CancellationToken cancellationToken = default)
    {
        query.Validate();

        Func<DateTimeOffset, DateTimeOffset> truncate = bucket switch
        {
            DiagnosticTimeBucket.Hour => t => new DateTimeOffset(t.Year, t.Month, t.Day, t.Hour, 0, 0, t.Offset),
            DiagnosticTimeBucket.Day => t => new DateTimeOffset(t.Year, t.Month, t.Day, 0, 0, 0, t.Offset),
            _ => throw new ArgumentOutOfRangeException(nameof(bucket), bucket, "Bucket temporel inconnu.")
        };

        IReadOnlyList<DiagnosticBucketCount> result = Filter(query)
            .GroupBy(e => truncate(e.TimestampUtc))
            .Select(g => new DiagnosticBucketCount(g.Key, g.LongCount()))
            .OrderBy(x => x.BucketUtc)
            .ToList();

        return Task.FromResult(result);
    }

    public Task<IReadOnlyList<DiagnosticBucketClientCount>> CountByTimeBucketAndClientAsync(
        DiagnosticTimeBucket bucket,
        DiagnosticAggregateQuery query,
        CancellationToken cancellationToken = default)
    {
        query.Validate();

        Func<DateTimeOffset, DateTimeOffset> truncate = bucket switch
        {
            DiagnosticTimeBucket.Hour => t => new DateTimeOffset(t.Year, t.Month, t.Day, t.Hour, 0, 0, t.Offset),
            DiagnosticTimeBucket.Day => t => new DateTimeOffset(t.Year, t.Month, t.Day, 0, 0, 0, t.Offset),
            _ => throw new ArgumentOutOfRangeException(nameof(bucket), bucket, "Bucket temporel inconnu.")
        };

        // Miroir du NULLIF(TRIM(client_code), '') SQL : NULL/vides/espaces → groupe null.
        IReadOnlyList<DiagnosticBucketClientCount> result = Filter(query)
            .GroupBy(e => (Bucket: truncate(e.TimestampUtc), Client: NormalizeClient(e.ClientCode)))
            .Select(g => new DiagnosticBucketClientCount(g.Key.Bucket, g.Key.Client, g.LongCount()))
            .OrderBy(x => x.BucketUtc)
            .ThenBy(x => x.ClientCode is null)
            .ThenBy(x => x.ClientCode, StringComparer.OrdinalIgnoreCase)
            .ToList();

        return Task.FromResult(result);
    }

    public Task<IReadOnlyList<DiagnosticLicenseErrorCount>> CountLicensesByErrorsAsync(
        DiagnosticAggregateQuery query,
        CancellationToken cancellationToken = default)
    {
        query.Validate();

        // Miroir du GROUP BY NULLIF(TRIM(license_number), '') + prédicat d'erreur réel.
        IReadOnlyList<DiagnosticLicenseErrorCount> result = Filter(query)
            .Where(e => DiagnosticErrorLevels.IsError(e.Level))
            .Where(e => !string.IsNullOrWhiteSpace(e.LicenseNumber))
            .GroupBy(e => e.LicenseNumber!.Trim())
            .Select(g => new DiagnosticLicenseErrorCount(g.Key, g.LongCount()))
            .OrderByDescending(x => x.ErrorCount)
            .ThenBy(x => x.LicenseNumber, StringComparer.Ordinal)
            .Take(query.Limit)
            .ToList();

        return Task.FromResult(result);
    }

    public Task<IReadOnlyList<DiagnosticGroupCount>> CountErrorsByClientAsync(
        DiagnosticAggregateQuery query,
        CancellationToken cancellationToken = default)
    {
        query.Validate();

        // Miroir du GROUP BY NULLIF(TRIM(client_code), '') + prédicat d'erreur réel.
        // NULL/vides/espaces → groupe null (rendu « Non renseigné »).
        IReadOnlyList<DiagnosticGroupCount> result = Filter(query)
            .Where(e => DiagnosticErrorLevels.IsError(e.Level))
            .GroupBy(e => NormalizeClient(e.ClientCode))
            .Select(g => new DiagnosticGroupCount(g.Key, g.LongCount()))
            .OrderByDescending(x => x.Count)
            .ThenBy(x => x.Key is null)
            .ThenBy(x => x.Key, StringComparer.OrdinalIgnoreCase)
            .Take(query.Limit)
            .ToList();

        return Task.FromResult(result);
    }

    public Task<DiagnosticQueryResult<DiagnosticEventRecord>> SearchAsync(
        DiagnosticSearchQuery query,
        CancellationToken cancellationToken = default)
    {
        query.Validate();

        // Même règle de normalisation que l'implémentation PostgreSQL (§T2).
        string? term = DiagnosticSearchDefaults.Normalize(query.SearchText);

        List<DiagnosticEventRecord> all;
        lock (_sync)
        {
            all = new List<DiagnosticEventRecord>(_all);
        }

        IEnumerable<DiagnosticEventRecord> filtered = all.Where(e =>
            e.TimestampUtc >= query.FromUtc!.Value &&
            e.TimestampUtc < query.ToUtc!.Value &&
            (term is null || MatchesText(e, term)) &&
            (string.IsNullOrWhiteSpace(query.Level) || e.Level == query.Level) &&
            (string.IsNullOrWhiteSpace(query.Priority) || e.Priority == query.Priority) &&
            (string.IsNullOrWhiteSpace(query.Component) || e.Component == query.Component) &&
            (string.IsNullOrWhiteSpace(query.EventCode) || e.EventCode == query.EventCode) &&
            (string.IsNullOrWhiteSpace(query.ErrorCode) || e.ErrorCode == query.ErrorCode) &&
            (string.IsNullOrWhiteSpace(query.AppVersion) || e.AppVersion == query.AppVersion) &&
            (string.IsNullOrWhiteSpace(query.Platform) || e.Platform == query.Platform) &&
            (string.IsNullOrWhiteSpace(query.ActorKey) || e.ActorKey == query.ActorKey) &&
            (string.IsNullOrWhiteSpace(query.ClientCode) || e.ClientCode == query.ClientCode) &&
            (string.IsNullOrWhiteSpace(query.ServerInstance) || e.ServerInstance == query.ServerInstance) &&
            (string.IsNullOrWhiteSpace(query.Environment) || e.Environment == query.Environment) &&
            (string.IsNullOrWhiteSpace(query.LicenseNumber) || e.LicenseNumber == query.LicenseNumber) &&
            (string.IsNullOrWhiteSpace(query.DeviceKey) || e.DeviceKey == query.DeviceKey) &&
            (string.IsNullOrWhiteSpace(query.Manufacturer) || e.DeviceManufacturer == query.Manufacturer) &&
            (string.IsNullOrWhiteSpace(query.DeviceModel) || e.DeviceModel == query.DeviceModel) &&
            (string.IsNullOrWhiteSpace(query.OperatingSystem) || e.OperatingSystem == query.OperatingSystem) &&
            (string.IsNullOrWhiteSpace(query.OperatingSystemVersion) || e.OperatingSystemVersion == query.OperatingSystemVersion) &&
            (string.IsNullOrWhiteSpace(query.ScreenCode) || e.ScreenCode == query.ScreenCode) &&
            (string.IsNullOrWhiteSpace(query.FeatureCode) || e.FeatureCode == query.FeatureCode));

        List<DiagnosticEventRecord> ordered = filtered
            .OrderByDescending(e => e.TimestampUtc)
            .ThenByDescending(e => e.Id)
            .ToList();

        List<DiagnosticEventRecord> page = ordered
            .Skip(query.Offset)
            .Take(query.PageSize)
            .ToList();

        return Task.FromResult(new DiagnosticQueryResult<DiagnosticEventRecord>
        {
            Items = page,
            TotalCount = ordered.Count,
            Page = query.Page,
            PageSize = query.PageSize
        });
    }

    public Task<IReadOnlyList<DiagnosticErrorGroup>> GroupErrorsAsync(
        DiagnosticErrorGroupQuery query,
        CancellationToken cancellationToken = default)
    {
        query.Validate();

        IReadOnlyList<DiagnosticErrorGroup> result = FilterGroup(query)
            .GroupBy(e => (
                EventCode: e.EventCode,
                ErrorCode: DiagnosticErrorGrouping.NormalizeKey(e.ErrorCode),
                ExceptionType: DiagnosticErrorGrouping.NormalizeKey(e.ExceptionType),
                Component: DiagnosticErrorGrouping.NormalizeKey(e.Component)))
            .Select(g => new DiagnosticErrorGroup
            {
                GroupKey = DiagnosticErrorGrouping.BuildGroupKey(
                    g.Key.EventCode, g.Key.ErrorCode, g.Key.ExceptionType, g.Key.Component),
                UrlIdentifier = DiagnosticErrorGroupLink.Encode(
                    new DiagnosticErrorGroupIdentity(
                        g.Key.EventCode, g.Key.ErrorCode, g.Key.ExceptionType, g.Key.Component)),
                EventCode = g.Key.EventCode,
                ErrorCode = MinRaw(g.Select(e => e.ErrorCode)),
                ExceptionType = MinRaw(g.Select(e => e.ExceptionType)),
                Component = MinRaw(g.Select(e => e.Component)),
                OccurrenceCount = g.LongCount(),
                FirstSeenUtc = g.Min(e => e.TimestampUtc),
                LastSeenUtc = g.Max(e => e.TimestampUtc),
                AppVersionCount = g.Select(e => e.AppVersion).Where(v => v is not null).Distinct().LongCount(),
                ActorCount = g.Select(e => e.ActorKey).Where(v => v is not null).Distinct().LongCount()
            })
            .OrderByDescending(x => x.OccurrenceCount)
            .ThenBy(x => x.EventCode, StringComparer.Ordinal)
            .ThenBy(x => x.ExceptionType is null)
            .ThenBy(x => x.ExceptionType, StringComparer.Ordinal)
            .ThenBy(x => x.ErrorCode is null)
            .ThenBy(x => x.ErrorCode, StringComparer.Ordinal)
            .ThenBy(x => x.Component is null)
            .ThenBy(x => x.Component, StringComparer.Ordinal)
            .Take(query.Limit)
            .ToList();

        return Task.FromResult(result);
    }

    /// <summary>
    /// Équivalent du <c>message ILIKE … OR exception_type ILIKE …</c> PostgreSQL :
    /// sous-chaîne insensible à la casse, terme littéral (aucun joker).
    /// </summary>
    private static bool MatchesText(DiagnosticEventRecord e, string term) =>
        (e.Message is not null && e.Message.Contains(term, StringComparison.OrdinalIgnoreCase)) ||
        (e.ExceptionType is not null && e.ExceptionType.Contains(term, StringComparison.OrdinalIgnoreCase));

    /// <summary>
    /// Équivalent du <c>MIN(col)</c> PostgreSQL (ignorant les NULL) sur une colonne
    /// texte : plus petite valeur non NULL en comparaison ordinale, <c>null</c> si tout
    /// est NULL. Utilisé comme valeur REPRÉSENTATIVE brute d'un groupe (§T2).
    /// </summary>
    private static string? MinRaw(IEnumerable<string?> values)
    {
        string? min = null;
        foreach (string? v in values)
        {
            if (v is null)
            {
                continue;
            }

            if (min is null || string.CompareOrdinal(v, min) < 0)
            {
                min = v;
            }
        }

        return min;
    }

    /// <summary>Équivalent du <c>NULLIF(TRIM(client_code), '')</c> PostgreSQL.</summary>
    private static string? NormalizeClient(string? clientCode) =>
        string.IsNullOrWhiteSpace(clientCode) ? null : clientCode.Trim();

    private IEnumerable<DiagnosticEventRecord> Filter(DiagnosticAggregateQuery query)
    {
        DateTimeOffset from = query.FromUtc!.Value; // Validate() garanti la présence.
        DateTimeOffset to = query.ToUtc!.Value;

        return _all.Where(e =>
            e.TimestampUtc >= from &&
            e.TimestampUtc < to &&
            (query.ErrorsOnly ? DiagnosticErrorLevels.IsError(e.Level) : (string.IsNullOrWhiteSpace(query.Level) || e.Level == query.Level)) &&
            (string.IsNullOrWhiteSpace(query.Priority) || e.Priority == query.Priority) &&
            (string.IsNullOrWhiteSpace(query.Component) || e.Component == query.Component) &&
            (query.SyncOnly ? DiagnosticSyncEventCodes.IsSyncEventCode(e.EventCode) : (string.IsNullOrWhiteSpace(query.EventCode) || e.EventCode == query.EventCode)) &&
            (query.HasErrorCode ? !string.IsNullOrWhiteSpace(e.ErrorCode) : true) &&
            (string.IsNullOrWhiteSpace(query.ErrorCode) || e.ErrorCode == query.ErrorCode) &&
            (string.IsNullOrWhiteSpace(query.AppVersion) || e.AppVersion == query.AppVersion) &&
            (string.IsNullOrWhiteSpace(query.Platform) || e.Platform == query.Platform) &&
            (string.IsNullOrWhiteSpace(query.ActorKey) || e.ActorKey == query.ActorKey) &&
            (string.IsNullOrWhiteSpace(query.ClientCode) || e.ClientCode == query.ClientCode) &&
            (string.IsNullOrWhiteSpace(query.ServerInstance) || e.ServerInstance == query.ServerInstance) &&
            (string.IsNullOrWhiteSpace(query.Environment) || e.Environment == query.Environment));
    }

    /// <summary>
    /// Filtre partagé des requêtes de regroupement d'erreurs (§T2/T4) : période obligatoire
    /// + filtres d'égalité + prédicat <c>ErrorsOnly</c> (niveaux d'erreur réels).
    /// </summary>
    private IEnumerable<DiagnosticEventRecord> FilterGroup(DiagnosticErrorGroupQuery query)
    {
        DateTimeOffset from = query.FromUtc!.Value; // Validate() garanti la présence.
        DateTimeOffset to = query.ToUtc!.Value;

        return _all.Where(e =>
            e.TimestampUtc >= from &&
            e.TimestampUtc < to &&
            (query.ErrorsOnly ? DiagnosticErrorLevels.IsError(e.Level) : (string.IsNullOrWhiteSpace(query.Level) || e.Level == query.Level)) &&
            (string.IsNullOrWhiteSpace(query.Priority) || e.Priority == query.Priority) &&
            (string.IsNullOrWhiteSpace(query.Component) || e.Component == query.Component) &&
            (string.IsNullOrWhiteSpace(query.EventCode) || e.EventCode == query.EventCode) &&
            (string.IsNullOrWhiteSpace(query.ErrorCode) || e.ErrorCode == query.ErrorCode) &&
            (string.IsNullOrWhiteSpace(query.AppVersion) || e.AppVersion == query.AppVersion) &&
            (string.IsNullOrWhiteSpace(query.Platform) || e.Platform == query.Platform) &&
            (string.IsNullOrWhiteSpace(query.ActorKey) || e.ActorKey == query.ActorKey) &&
            (string.IsNullOrWhiteSpace(query.ClientCode) || e.ClientCode == query.ClientCode) &&
            (string.IsNullOrWhiteSpace(query.ServerInstance) || e.ServerInstance == query.ServerInstance) &&
            (string.IsNullOrWhiteSpace(query.Environment) || e.Environment == query.Environment));
    }

    public Task<long> CountAsync(
        DiagnosticAggregateQuery query,
        CancellationToken cancellationToken = default)
    {
        query.Validate();
        return Task.FromResult(Filter(query).LongCount());
    }

    public Task<long> CountErrorGroupsAsync(
        DiagnosticErrorGroupQuery query,
        CancellationToken cancellationToken = default)
    {
        query.Validate();

        long count = FilterGroup(query)
            .Select(e => (
                EventCode: e.EventCode,
                ErrorCode: DiagnosticErrorGrouping.NormalizeKey(e.ErrorCode),
                ExceptionType: DiagnosticErrorGrouping.NormalizeKey(e.ExceptionType),
                Component: DiagnosticErrorGrouping.NormalizeKey(e.Component)))
            .Distinct()
            .LongCount();

        return Task.FromResult(count);
    }

    public Task<DiagnosticDurationStats?> GetDurationStatsAsync(
        DiagnosticAggregateQuery query,
        CancellationToken cancellationToken = default)
    {
        query.Validate();

        List<long> durations = Filter(query)
            .Where(e => e.DurationMs is not null)
            .Select(e => e.DurationMs!.Value)
            .ToList();

        if (durations.Count == 0)
        {
            return Task.FromResult<DiagnosticDurationStats?>(null); // absence ≠ zéro.
        }

        return Task.FromResult<DiagnosticDurationStats?>(new DiagnosticDurationStats(
            durations.Count,
            durations.Average(),
            durations.Min(),
            durations.Max()));
    }

    public Task<IReadOnlyList<DiagnosticDurationByEventCode>> GetTopDurationByEventCodeAsync(
        DiagnosticAggregateQuery query,
        CancellationToken cancellationToken = default)
    {
        query.Validate();

        IReadOnlyList<DiagnosticDurationByEventCode> result = Filter(query)
            .Where(e => e.DurationMs is not null)
            .GroupBy(e => e.EventCode)
            .Select(g => new DiagnosticDurationByEventCode(
                g.Key,
                g.LongCount(),
                g.Average(e => e.DurationMs!.Value),
                g.Max(e => e.DurationMs!.Value)))
            .OrderByDescending(x => x.AverageMs)
            .ThenBy(x => x.EventCode, StringComparer.Ordinal)
            .Take(query.Limit)
            .ToList();

        return Task.FromResult(result);
    }

    public Task<IReadOnlyList<DiagnosticVersionSummary>> GetVersionSummariesAsync(
        DiagnosticAggregateQuery query,
        CancellationToken cancellationToken = default)
    {
        query.Validate();

        IReadOnlyList<DiagnosticVersionSummary> result = Filter(query)
            .Where(e => !string.IsNullOrEmpty(e.AppVersion)) // NULL/vide exclus (miroir NULLIF)
            .GroupBy(e => e.AppVersion!)
            .Select(g => new DiagnosticVersionSummary(
                g.Key,
                g.LongCount(),
                g.LongCount(e => DiagnosticErrorLevels.IsError(e.Level)),
                g.Min(e => e.TimestampUtc),
                g.Max(e => e.TimestampUtc),
                g.Select(e => e.Platform).Where(p => p is not null).Distinct().LongCount()))
            .OrderByDescending(x => x.EventCount)
            .ThenBy(x => x.AppVersion, StringComparer.Ordinal)
            .Take(query.Limit)
            .ToList();

        return Task.FromResult(result);
    }

    public Task<DiagnosticVersionTotals> GetVersionTotalsAsync(
        DiagnosticAggregateQuery query,
        CancellationToken cancellationToken = default)
    {
        query.Validate();

        List<DiagnosticEventRecord> filtered = Filter(query).ToList();
        List<DiagnosticEventRecord> versioned = filtered.Where(e => !string.IsNullOrEmpty(e.AppVersion)).ToList();

        return Task.FromResult(new DiagnosticVersionTotals(
            versioned.Select(e => e.AppVersion).Distinct().LongCount(),
            versioned.LongCount(),
            versioned.LongCount(e => DiagnosticErrorLevels.IsError(e.Level)),
            filtered.LongCount(e => string.IsNullOrEmpty(e.AppVersion))));
    }

    public Task<DiagnosticSyncSummary> GetSyncSummaryAsync(
        DiagnosticAggregateQuery query,
        CancellationToken cancellationToken = default)
    {
        query.Validate();

        List<DiagnosticEventRecord> sync = _all
            .Where(e =>
                e.TimestampUtc >= query.FromUtc!.Value &&
                e.TimestampUtc < query.ToUtc!.Value &&
                DiagnosticSyncEventCodes.IsSyncEventCode(e.EventCode))
            .ToList();

        long eventCount = sync.Count;
        long operationCount = sync
            .Where(e => e.OperationId is not null)
            .Select(e => e.OperationId!.Value)
            .Distinct()
            .LongCount();
        long errorCount = sync.LongCount(e => DiagnosticErrorLevels.IsError(e.Level));

        List<long> durations = sync
            .Where(e => e.DurationMs is not null)
            .Select(e => e.DurationMs!.Value)
            .ToList();
        long durationCount = durations.Count;

        return Task.FromResult(new DiagnosticSyncSummary(
            eventCount,
            operationCount,
            errorCount,
            durationCount,
            durationCount == 0 ? null : durations.Average(),
            durationCount == 0 ? null : durations.Min(),
            durationCount == 0 ? null : durations.Max()));
    }

    public Task<DiagnosticErrorGroup?> GetErrorGroupAsync(
        DiagnosticErrorGroupOccurrenceQuery query,
        CancellationToken cancellationToken = default)
    {
        query.Validate();

        DiagnosticErrorGroup? group = Occurrences(query)
            .GroupBy(e => (
                EventCode: e.EventCode,
                ErrorCode: DiagnosticErrorGrouping.NormalizeKey(e.ErrorCode),
                ExceptionType: DiagnosticErrorGrouping.NormalizeKey(e.ExceptionType),
                Component: DiagnosticErrorGrouping.NormalizeKey(e.Component)))
            .Select(g => new DiagnosticErrorGroup
            {
                GroupKey = DiagnosticErrorGrouping.BuildGroupKey(
                    g.Key.EventCode, g.Key.ErrorCode, g.Key.ExceptionType, g.Key.Component),
                UrlIdentifier = DiagnosticErrorGroupLink.Encode(
                    new DiagnosticErrorGroupIdentity(
                        g.Key.EventCode, g.Key.ErrorCode, g.Key.ExceptionType, g.Key.Component)),
                EventCode = g.Key.EventCode,
                ErrorCode = MinRaw(g.Select(e => e.ErrorCode)),
                ExceptionType = MinRaw(g.Select(e => e.ExceptionType)),
                Component = MinRaw(g.Select(e => e.Component)),
                OccurrenceCount = g.LongCount(),
                FirstSeenUtc = g.Min(e => e.TimestampUtc),
                LastSeenUtc = g.Max(e => e.TimestampUtc),
                AppVersionCount = g.Select(e => e.AppVersion).Where(v => v is not null).Distinct().LongCount(),
                ActorCount = g.Select(e => e.ActorKey).Where(v => v is not null).Distinct().LongCount()
            })
            .FirstOrDefault();

        return Task.FromResult(group);
    }

    public Task<IReadOnlyList<DiagnosticEventRecord>> GetErrorGroupOccurrencesAsync(
        DiagnosticErrorGroupOccurrenceQuery query,
        CancellationToken cancellationToken = default)
    {
        query.Validate();

        IReadOnlyList<DiagnosticEventRecord> result = Occurrences(query)
            .OrderByDescending(e => e.TimestampUtc)
            .ThenByDescending(e => e.Id)
            .Skip(query.Offset)
            .Take(query.PageSize)
            .ToList();

        return Task.FromResult(result);
    }

    public Task<IReadOnlyList<DiagnosticGroupCount>> CountErrorGroupByClientAsync(
        DiagnosticErrorGroupOccurrenceQuery query,
        int limit,
        CancellationToken cancellationToken = default)
    {
        query.Validate();

        int safeLimit = Math.Clamp(limit, 1, DiagnosticAggregateDefaults.MaxLimit);

        // Miroir du NULLIF(TRIM(client_code), '') SQL : NULL/vides/espaces → groupe null.
        IReadOnlyList<DiagnosticGroupCount> result = Occurrences(query)
            .GroupBy(e => NormalizeClient(e.ClientCode))
            .Select(g => new DiagnosticGroupCount(g.Key, g.LongCount()))
            .OrderByDescending(x => x.Count)
            .ThenBy(x => x.Key is null)
            .ThenBy(x => x.Key, StringComparer.OrdinalIgnoreCase)
            .Take(safeLimit)
            .ToList();

        return Task.FromResult(result);
    }

    public Task<IReadOnlyList<DiagnosticLicenseErrorCount>> CountErrorGroupByLicenseAsync(
        DiagnosticErrorGroupOccurrenceQuery query,
        int limit,
        CancellationToken cancellationToken = default)
    {
        query.Validate();

        int safeLimit = Math.Clamp(limit, 1, DiagnosticAggregateDefaults.MaxLimit);

        // Miroir du GROUP BY NULLIF(TRIM(license_number), '') : NULL/vides exclus.
        IReadOnlyList<DiagnosticLicenseErrorCount> result = Occurrences(query)
            .Where(e => !string.IsNullOrWhiteSpace(e.LicenseNumber))
            .GroupBy(e => e.LicenseNumber!.Trim())
            .Select(g => new DiagnosticLicenseErrorCount(g.Key, g.LongCount()))
            .OrderByDescending(x => x.ErrorCount)
            .ThenBy(x => x.LicenseNumber, StringComparer.Ordinal)
            .Take(safeLimit)
            .ToList();

        return Task.FromResult(result);
    }

    /// <summary>
    /// Filtre les événements d'un groupe précis (§T5) : période obligatoire + identité
    /// normalisée. Reproduit EXACTEMENT la normalisation du regroupement (NormalizeKey,
    /// équivalent de LOWER(NULLIF(TRIM(col),''))), y compris le NULL (null == null).
    /// </summary>
    private IEnumerable<DiagnosticEventRecord> Occurrences(DiagnosticErrorGroupOccurrenceQuery query)
    {
        DateTimeOffset from = query.FromUtc; // Validate() garanti l'ordre.
        DateTimeOffset to = query.ToUtc;

        return _all.Where(e =>
            e.TimestampUtc >= from &&
            e.TimestampUtc < to &&
            e.EventCode == query.EventCode &&
            DiagnosticErrorGrouping.NormalizeKey(e.ErrorCode) == query.NormalizedErrorCode &&
            DiagnosticErrorGrouping.NormalizeKey(e.ExceptionType) == query.NormalizedExceptionType &&
            DiagnosticErrorGrouping.NormalizeKey(e.Component) == query.NormalizedComponent);
    }

    // ============================================================
    // T17-E — Statistiques de parc (identité appareil = DeviceKey).
    // ============================================================

    public Task<long> CountActiveDevicesAsync(
        DeviceFleetQuery query,
        CancellationToken cancellationToken = default)
    {
        query.Validate();
        return Task.FromResult(LatestDeviceEvents(query).LongCount());
    }

    public Task<IReadOnlyList<DeviceDistribution>> CountDevicesGroupedAsync(
        DeviceFleetDimension dimension,
        DeviceFleetQuery query,
        CancellationToken cancellationToken = default)
    {
        query.Validate();

        Func<DiagnosticEventRecord, string?> keySelector = dimension switch
        {
            DeviceFleetDimension.Platform => e => DeviceFleetNormalization.Platform(e.Platform),
            DeviceFleetDimension.OperatingSystem => e => DeviceFleetNormalization.OperatingSystem(e.OperatingSystem),
            DeviceFleetDimension.OperatingSystemVersion => e => DeviceFleetNormalization.OperatingSystemVersionKey(e.OperatingSystem, e.OperatingSystemVersion),
            DeviceFleetDimension.Manufacturer => e => DeviceFleetNormalization.Manufacturer(e.DeviceManufacturer),
            DeviceFleetDimension.DeviceModel => e => DeviceFleetNormalization.DeviceModelKey(e.DeviceManufacturer, e.DeviceModel),
            DeviceFleetDimension.AppVersion => e => DeviceFleetNormalization.AppVersion(e.AppVersion),
            DeviceFleetDimension.Resolution => e => DeviceFleetNormalization.ResolutionKey(e.ScreenWidth, e.ScreenHeight),
            _ => throw new ArgumentOutOfRangeException(nameof(dimension), dimension, "Dimension de parc inconnue.")
        };

        IReadOnlyList<DeviceDistribution> result = LatestDeviceEvents(query)
            .Select(keySelector)
            .Where(k => k is not null)
            .GroupBy(k => k!)
            .Select(g => new DeviceDistribution(g.Key, g.LongCount()))
            .OrderByDescending(x => x.DeviceCount)
            .ThenBy(x => x.Key, StringComparer.Ordinal)
            .Take(query.Limit)
            .ToList();

        return Task.FromResult(result);
    }

    public Task<DeviceFleetQueryResult> GetDevicesAsync(
        DeviceFleetQuery query,
        CancellationToken cancellationToken = default)
    {
        query.Validate();

        List<DeviceFleetRecord> all = LatestDeviceEvents(query)
            .OrderByDescending(e => e.TimestampUtc)
            .ThenBy(e => e.DeviceKey, StringComparer.Ordinal)
            .Select(ToFleetRecord)
            .ToList();

        List<DeviceFleetRecord> page = all
            .Skip(query.Offset)
            .Take(query.PageSize)
            .ToList();

        return Task.FromResult(new DeviceFleetQueryResult
        {
            Items = page,
            TotalCount = all.Count,
            Page = query.Page,
            PageSize = query.PageSize
        });
    }

    public Task<IReadOnlyList<DeviceDistribution>> CountDevicesByLicenseAsync(
        DeviceFleetQuery query,
        CancellationToken cancellationToken = default)
    {
        query.Validate();
        DateTimeOffset from = query.FromUtc!.Value;
        DateTimeOffset to = query.ToUtc!.Value;

        IReadOnlyList<DeviceDistribution> result = _all
            .Where(e => e.TimestampUtc >= from && e.TimestampUtc < to &&
                        !string.IsNullOrWhiteSpace(e.DeviceKey) &&
                        !string.IsNullOrWhiteSpace(e.LicenseNumber))
            .Where(e => string.IsNullOrWhiteSpace(query.ClientCode) || e.ClientCode == query.ClientCode)
            .Where(e => string.IsNullOrWhiteSpace(query.Environment) || e.Environment == query.Environment)
            .Where(e => string.IsNullOrWhiteSpace(query.LicenseNumber) || e.LicenseNumber == query.LicenseNumber)
            .GroupBy(e => e.LicenseNumber!.Trim())
            .Select(g => new DeviceDistribution(
                g.Key,
                g.Select(e => e.DeviceKey).Where(k => k is not null).Distinct(StringComparer.Ordinal).LongCount()))
            .OrderByDescending(x => x.DeviceCount)
            .ThenBy(x => x.Key, StringComparer.Ordinal)
            .Take(query.Limit)
            .ToList();

        return Task.FromResult(result);
    }

    public Task<IReadOnlyList<ScreenUsageRecord>> GetScreenUsageAsync(
        UsageTelemetryQuery query,
        CancellationToken cancellationToken = default)
    {
        query.Validate();
        DateTimeOffset from = query.FromUtc!.Value;
        DateTimeOffset to = query.ToUtc!.Value;
        string screenView = TelemetryEventCodes.MobileScreenView.Value!;

        IReadOnlyList<ScreenUsageRecord> result = _all
            .Where(e => e.TimestampUtc >= from && e.TimestampUtc < to)
            .Where(e => e.EventCode == screenView)
            .Where(e => !string.IsNullOrWhiteSpace(e.ScreenCode))
            .Where(e => MatchesUsageFilters(e, query))
            .GroupBy(e => e.ScreenCode!.Trim().ToUpperInvariant())
            .Select(g => new ScreenUsageRecord
            {
                ScreenCode = g.Key,
                Views = g.LongCount(),
                DistinctDevices = g.Select(e => e.DeviceKey).Where(k => k is not null).Distinct(StringComparer.Ordinal).LongCount(),
                DistinctSessions = g.Select(e => e.SessionId).Where(s => s is not null).Distinct().LongCount()
            })
            .OrderByDescending(x => x.Views)
            .ThenBy(x => x.ScreenCode, StringComparer.Ordinal)
            .Take(query.Limit)
            .ToList();

        return Task.FromResult(result);
    }

    public Task<IReadOnlyList<FeatureUsageRecord>> GetFeatureUsageAsync(
        UsageTelemetryQuery query,
        CancellationToken cancellationToken = default)
    {
        query.Validate();
        DateTimeOffset from = query.FromUtc!.Value;
        DateTimeOffset to = query.ToUtc!.Value;
        string featureUse = TelemetryEventCodes.MobileFeatureUse.Value!;

        IReadOnlyList<FeatureUsageRecord> result = _all
            .Where(e => e.TimestampUtc >= from && e.TimestampUtc < to)
            .Where(e => e.EventCode == featureUse)
            .Where(e => !string.IsNullOrWhiteSpace(e.FeatureCode))
            .Where(e => MatchesUsageFilters(e, query))
            .GroupBy(e => e.FeatureCode!.Trim().ToUpperInvariant())
            .Select(g => new FeatureUsageRecord
            {
                FeatureCode = g.Key,
                Uses = g.LongCount(),
                DistinctDevices = g.Select(e => e.DeviceKey).Where(k => k is not null).Distinct(StringComparer.Ordinal).LongCount()
            })
            .OrderByDescending(x => x.Uses)
            .ThenBy(x => x.FeatureCode, StringComparer.Ordinal)
            .Take(query.Limit)
            .ToList();

        return Task.FromResult(result);
    }

    /// <summary>Filtres d'usage (T18, §57) : tous facultatifs, combinables (AND).</summary>
    private static bool MatchesUsageFilters(DiagnosticEventRecord e, UsageTelemetryQuery q) =>
        (string.IsNullOrWhiteSpace(q.ClientCode) || e.ClientCode == q.ClientCode) &&
        (string.IsNullOrWhiteSpace(q.LicenseNumber) || e.LicenseNumber == q.LicenseNumber) &&
        (string.IsNullOrWhiteSpace(q.Platform) || e.Platform == q.Platform) &&
        (string.IsNullOrWhiteSpace(q.OperatingSystem) || e.OperatingSystem == q.OperatingSystem) &&
        (string.IsNullOrWhiteSpace(q.AppVersion) || e.AppVersion == q.AppVersion) &&
        (string.IsNullOrWhiteSpace(q.Environment) || e.Environment == q.Environment) &&
        (string.IsNullOrWhiteSpace(q.ScreenCode) || e.ScreenCode == q.ScreenCode) &&
        (string.IsNullOrWhiteSpace(q.FeatureCode) || e.FeatureCode == q.FeatureCode);

    /// <summary>
    /// « Dernière observation de chaque DeviceKey » (miroir du CTE PostgreSQL, §13) :
    /// filtres de scope ÉVÉNEMENT (période, client, environnement, licence) appliqués
    /// AVANT le choix de l'événement le plus récent ; filtres de scope DERNIÈRE
    /// OBSERVATION appliqués APRÈS (§47). Jamais de fusion par LicenseNumber (§14).
    /// </summary>
    private IEnumerable<DiagnosticEventRecord> LatestDeviceEvents(DeviceFleetQuery query)
    {
        DateTimeOffset from = query.FromUtc!.Value;
        DateTimeOffset to = query.ToUtc!.Value;

        return _all
            .Where(e => e.TimestampUtc >= from && e.TimestampUtc < to && !string.IsNullOrWhiteSpace(e.DeviceKey))
            .Where(e => string.IsNullOrWhiteSpace(query.ClientCode) || e.ClientCode == query.ClientCode)
            .Where(e => string.IsNullOrWhiteSpace(query.Environment) || e.Environment == query.Environment)
            .Where(e => string.IsNullOrWhiteSpace(query.LicenseNumber) || e.LicenseNumber == query.LicenseNumber)
            .GroupBy(e => e.DeviceKey!)
            .Select(g => g.OrderByDescending(e => e.TimestampUtc).ThenByDescending(e => e.Id).First())
            .Where(e => MatchesLastObservation(e, query));
    }

    private static bool MatchesLastObservation(DiagnosticEventRecord e, DeviceFleetQuery q) =>
        (string.IsNullOrWhiteSpace(q.Platform) || e.Platform == q.Platform) &&
        (string.IsNullOrWhiteSpace(q.OperatingSystem) || e.OperatingSystem == q.OperatingSystem) &&
        (string.IsNullOrWhiteSpace(q.OperatingSystemVersion) || e.OperatingSystemVersion == q.OperatingSystemVersion) &&
        (string.IsNullOrWhiteSpace(q.Manufacturer) || e.DeviceManufacturer == q.Manufacturer) &&
        (string.IsNullOrWhiteSpace(q.DeviceModel) || e.DeviceModel == q.DeviceModel) &&
        (string.IsNullOrWhiteSpace(q.AppVersion) || e.AppVersion == q.AppVersion);

    private static DeviceFleetRecord ToFleetRecord(DiagnosticEventRecord e) => new()
    {
        DeviceKey = e.DeviceKey!,
        LastSeenUtc = e.TimestampUtc,
        ClientCode = e.ClientCode,
        LicenseNumber = e.LicenseNumber,
        Environment = e.Environment,
        Platform = e.Platform,
        OperatingSystem = e.OperatingSystem,
        OperatingSystemVersion = e.OperatingSystemVersion,
        DeviceManufacturer = e.DeviceManufacturer,
        DeviceModel = e.DeviceModel,
        AppVersion = e.AppVersion,
        AppBuild = e.AppBuild,
        ScreenWidth = e.ScreenWidth,
        ScreenHeight = e.ScreenHeight,
        LogicalWidth = e.LogicalWidth,
        LogicalHeight = e.LogicalHeight,
        DisplayScale = e.DisplayScale
    };
}