// Licensed to the .NET Foundation under one or more agreements.
// The .NET Foundation licenses this file to you under the MIT license.

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using FloraMultiPlatformFrontend.Application.Services.Interfaces;
using FloraMultiPlatformFrontend.Data.Entities;
using FloraMultiPlatformFrontend.Data.Repositories.Interfaces;
using FloraMultiPlatformFrontend.Presentation.Models.Charts;
using FloraMultiPlatformFrontend.Presentation.Models.Filter;
using FloraMultiPlatformFrontend.Presentation.Models.ServiceResult;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using LocalCacheContext = FloraMultiPlatformFrontend.Data.LocalCacheDbContext;

namespace FloraMultiPlatformFrontend.Data.Repositories;

public class DashboardRepository : IDashboardRepository
{
    private readonly IDbContextFactory<FloraDbContext> _contextFactory;
    private readonly IDbContextFactory<LocalCacheContext> _cacheContextFactory;
    private readonly ILogger<DashboardRepository> _logger;
    private readonly IDatabaseService _databaseService;
    private readonly IConfigurationService _configurationService;
    // Safe SQL Server-compatible date bounds for remote queries
    private static readonly DateTime SqlMinDate = new DateTime(1900, 1, 1);
    private static DateTime SqlMaxDate => DateTime.UtcNow.AddHours(1);


    public DashboardRepository(IDbContextFactory<FloraDbContext> contextFactory, IDbContextFactory<LocalCacheContext> cacheContextFactory, ILogger<DashboardRepository> logger, IDatabaseService databaseService, IConfigurationService configurationService)
    {
        _contextFactory = contextFactory ?? throw new ArgumentNullException(nameof(contextFactory));
        _cacheContextFactory = cacheContextFactory ?? throw new ArgumentNullException(nameof(cacheContextFactory));
        _logger = logger ?? throw new ArgumentNullException(nameof(logger));
        _databaseService = databaseService ?? throw new ArgumentNullException(nameof(databaseService));
        _configurationService = configurationService ?? throw new ArgumentNullException(nameof(configurationService));
    }

    public async Task<double?> GetAverageRingBuildingTimeAsync(
        DateTime startDate,
        DateTime endDate,
        string machineCode,
        CancellationToken cancellationToken = default)
    {
        try
        {
            _logger.LogDebug("Getting average ring building time from {StartDate} to {EndDate} for machine {MachineCode}", startDate, endDate, machineCode);
            using var context = await _contextFactory.CreateDbContextAsync();
            context.Database.SetCommandTimeout(180);
            var results = await context.Database
                            .SqlQueryRaw<double>("EXEC dbo.CalculateAverageMinutes @StartDate = {0}, @EndDate = {1}, @MachineCode = {2}",
                                startDate, endDate, machineCode)
                            .ToListAsync(cancellationToken);

            // Get the first result from the list (or default, which is 0.0 for double)
            var singleResult = results.FirstOrDefault();

            return singleResult;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error getting average ring building time for machine {MachineCode}", machineCode);
            return null;
        }
    }

    public async Task<double?> GetAverageRingsPerDayAsync(
        DateTime startDate,
        DateTime endDate,
        string machineCode,
        CancellationToken cancellationToken = default)
    {
        try
        {
            Trace.WriteLine($"Getting average rings per day from {startDate} to {endDate} for machine {machineCode}");

            _logger.LogDebug("Getting average rings per day from {StartDate} to {EndDate} for machine {MachineCode}", startDate, endDate, machineCode);
            using var context = await _contextFactory.CreateDbContextAsync();
            context.Database.SetCommandTimeout(180);
            var results = await context.Database
                .SqlQueryRaw<decimal>("EXEC dbo.CalculateAverageEntriesPerDay @StartDate = {0}, @EndDate = {1}, @MachineCode = {2}",
                    startDate, endDate, machineCode)
                .ToListAsync(cancellationToken);

            var singleResult = results.FirstOrDefault();
            Trace.WriteLine($"Getting average Single result {singleResult}");

            return (double)singleResult;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error getting average rings per day for machine {MachineCode}", machineCode);
            return null;
        }
    }

    public async Task<IEnumerable<TagAverageResult>> GetTagAveragesAsync(
        IEnumerable<int> tagIds,
        DateTime startDate,
        DateTime endDate,
        string machineCode,
        CancellationToken cancellationToken = default)
    {
        try
        {
            var tagIdList = tagIds.ToList();

            if (!tagIdList.Any())
            {
                return Enumerable.Empty<TagAverageResult>();
            }

            _logger.LogDebug("Getting averages for {TagCount} tags from {StartDate} to {EndDate} for machine {MachineCode}",
                tagIdList.Count, startDate, endDate, machineCode);

            using var context = await _contextFactory.CreateDbContextAsync();
            context.Database.SetCommandTimeout(180);
            Trace.WriteLine($"Getting averages for {tagIdList.Count} tags from {startDate} to {endDate} for machine {machineCode}");
            var relevantTagValues = context.TagValues
                .Where(tv => tagIdList.Contains(tv.TagId) &&
                             tv.TagTime >= startDate &&
                             tv.TagTime <= endDate);

            var query =
                from tv in relevantTagValues
                where context.AdvanceTimes.Any(at =>
                    at.AdvanceStart <= endDate &&
                    at.AdvanceEnd >= startDate &&
                    tv.TagTime >= at.AdvanceStart &&
                    tv.TagTime <= at.AdvanceEnd)
                group tv by tv.TagId into g
                select new TagAverageResult
                {
                    TagId = g.Key,
                    Average = g.Average(t => t.TagValue)
                };

            _logger.LogDebug("Executing GetTagAveragesAsync query: {QueryString}", query.ToQueryString());

            var results = await query.ToListAsync(cancellationToken);
            return results;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error getting tag averages for {TagIds} and machine {MachineCode}", string.Join(",", tagIds), machineCode);
            return Enumerable.Empty<TagAverageResult>();
        }
    }

    public async Task<IDictionary<int, double>> GetTagAveragesDictionaryAsync(
        IEnumerable<int> tagIds,
        DateTime startDate,
        DateTime endDate,
        string machineCode,
        CancellationToken cancellationToken = default)
    {
        var results = await GetTagAveragesAsync(tagIds, startDate, endDate, machineCode, cancellationToken);
        return results.ToDictionary(r => r.TagId, r => r.Average);
    }

    public async Task<IEnumerable<ChartDataPoint>> GetTagHistoryAsync(
        int tagId,
        DateTime startDate,
        DateTime endDate,
        string machineCode,
        CancellationToken cancellationToken = default)
    {
        try
        {
            _logger.LogDebug("Getting tag history for TagId {TagId} from {StartDate} to {EndDate} for machine {MachineCode}",
                tagId, startDate, endDate, machineCode);

            // Use GetMultipleTagsRawDataAsync to fetch data from surveyor tables (TagValues table doesn't exist in SMAT project)
            var multipleTagsData = await GetMultipleTagsRawDataAsync(
                new[] { tagId },
                startDate,
                endDate,
                machineCode,
                cancellationToken
            );

            // Extract the data for the single tag
            if (multipleTagsData.TryGetValue(tagId, out var tagData))
            {
                var results = tagData.ToList();
                _logger.LogDebug("Retrieved {Count} data points for TagId {TagId}", results.Count, tagId);
                return results;
            }

            _logger.LogDebug("No data found for TagId {TagId}", tagId);
            return Enumerable.Empty<ChartDataPoint>();
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error getting tag history for TagId {TagId} and machine {MachineCode}", tagId, machineCode);
            return Enumerable.Empty<ChartDataPoint>();
        }
    }

    public async Task<IDictionary<int, IEnumerable<ChartDataPoint>>> GetMultipleTagsRawDataAsync(
        IEnumerable<int> tagIds,
        DateTime startDate,
        DateTime endDate,
        string machineCode,
        CancellationToken cancellationToken = default)
    {
        var totalStopwatch = System.Diagnostics.Stopwatch.StartNew();
        var stepStopwatch = System.Diagnostics.Stopwatch.StartNew();

        try
        {
            var tagIdList = tagIds.ToList();

            if (!tagIdList.Any())
            {
                return new Dictionary<int, IEnumerable<ChartDataPoint>>();
            }

            _logger.LogInformation("⏱️ REPO PERF - Getting raw data for {TagCount} tags from {StartDate} to {EndDate} (span: {DayCount} days)",
                tagIdList.Count, startDate, endDate, (endDate - startDate).TotalDays);

            // Build union query for all three table types (ShortData, ValueData, StateData)
            stepStopwatch.Restart();
            var unionQuery = await BuildUnionQueryForAllSurveyorTablesAsync(
                tagIdList,
                machineCode,
                startDate,
                endDate
            );
            stepStopwatch.Stop();
            _logger.LogInformation("⏱️ REPO PERF - Union Query Build Time: {ElapsedMs}ms", stepStopwatch.ElapsedMilliseconds);

            if (string.IsNullOrEmpty(unionQuery))
            {
                _logger.LogWarning("Empty union query generated for tags {TagIds}", string.Join(",", tagIdList));
                return tagIdList.ToDictionary(tagId => tagId, _ => Enumerable.Empty<ChartDataPoint>());
            }

            // Wrap the union query to order by time
            var fullQuery = $@"
                SELECT ID, tagTime, tagValue
                FROM ({unionQuery}) AS CombinedData
                ORDER BY tagTime";

            // Log query size for diagnostics
            var queryLineCount = unionQuery.Split(new[] { "UNION ALL" }, StringSplitOptions.None).Length;
            _logger.LogInformation("⏱️ REPO PERF - Generated query with {UnionCount} UNION ALL parts", queryLineCount - 1);

            // Execute the raw SQL query
            stepStopwatch.Restart();
            var allData = await _databaseService.QueryAsync<RawTagDataDto>(fullQuery);
            stepStopwatch.Stop();
            _logger.LogInformation("⏱️ REPO PERF - Database Query Execution Time: {ElapsedMs}ms, Rows Retrieved: {RowCount}",
                stepStopwatch.ElapsedMilliseconds, allData?.Count() ?? 0);

            if (allData == null || !allData.Any())
            {
                _logger.LogDebug("No data found for tags {TagIds}", string.Join(",", tagIdList));
                return tagIdList.ToDictionary(tagId => tagId, _ => Enumerable.Empty<ChartDataPoint>());
            }

            // Group by TagId (ID column from database)
            stepStopwatch.Restart();
            var result = allData
                .GroupBy(x => x.ID)
                .ToDictionary(
                    g => g.Key,
                    g => g.Select(x => new ChartDataPoint
                    {
                        Timestamp = x.tagTime,
                        Value = x.tagValue
                    }).AsEnumerable()
                );

            // Ensure all requested tags are in the dictionary (even if empty)
            foreach (var tagId in tagIdList)
            {
                if (!result.ContainsKey(tagId))
                {
                    result[tagId] = Enumerable.Empty<ChartDataPoint>();
                }
            }
            stepStopwatch.Stop();
            _logger.LogInformation("⏱️ REPO PERF - Data Grouping Time: {ElapsedMs}ms", stepStopwatch.ElapsedMilliseconds);

            totalStopwatch.Stop();
            _logger.LogInformation("⏱️ REPO PERF SUMMARY - Total GetMultipleTagsRawDataAsync Time: {ElapsedMs}ms ({ElapsedSeconds:F2}s)",
                totalStopwatch.ElapsedMilliseconds, totalStopwatch.ElapsedMilliseconds / 1000.0);

            return result;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error getting raw data for tags {TagIds} and machine {MachineCode}",
                string.Join(",", tagIds), machineCode);
            return new Dictionary<int, IEnumerable<ChartDataPoint>>();
        }
    }

    /// <summary>
    /// Gets time-weighted aggregated tag history data at a specific level of detail.
    /// This method performs database-level aggregation using SQL Server window functions
    /// to correctly handle sparse change-only data.
    /// </summary>
    /// <param name="tagId">The tag ID to query</param>
    /// <param name="startDate">Start date of the range</param>
    /// <param name="endDate">End date of the range</param>
    /// <param name="bucketSizeMs">Bucket size in milliseconds (e.g., 300000 for 5 minutes)</param>
    /// <param name="machineCode">Machine code identifier</param>
    /// <param name="cancellationToken">Cancellation token</param>
    /// <returns>Time-weighted aggregated chart data points</returns>
    public async Task<IEnumerable<ChartDataPoint>> GetTimeWeightedAggregatedHistoryAsync(
        int tagId,
        DateTime startDate,
        DateTime endDate,
        long bucketSizeMs,
        string machineCode,
        CancellationToken cancellationToken = default)
    {
        try
        {
            _logger.LogDebug("Getting time-weighted aggregated history for TagId {TagId} with bucket size {BucketMs}ms from {StartDate} to {EndDate}",
                tagId, bucketSizeMs, startDate, endDate);

            // Build the union query for all surveyor tables
            var unionQuery = await BuildUnionQueryForAllSurveyorTablesAsync(
                new[] { tagId },
                machineCode,
                startDate,
                endDate
            );

            if (string.IsNullOrEmpty(unionQuery))
            {
                _logger.LogWarning("Empty union query generated for tag {TagId}", tagId);
                return Enumerable.Empty<ChartDataPoint>();
            }

            // Build the time-weighted aggregation query
            var aggregationQuery = $@"
DECLARE @startDate DATETIME = '{startDate:yyyy-MM-dd HH:mm:ss.fff}';
DECLARE @endDate DATETIME = '{endDate:yyyy-MM-dd HH:mm:ss.fff}';
DECLARE @bucketSizeMs BIGINT = {bucketSizeMs};

WITH RawData AS (
    -- Get all raw data from union of tables
    SELECT ID, tagTime, tagValue
    FROM ({unionQuery}) AS UnionedData
),
TimeWeightedData AS (
    -- Step 1: Calculate duration for each value using LEAD window function
    SELECT
        tagTime,
        tagValue,
        LEAD(tagTime, 1, @endDate) OVER (ORDER BY tagTime) AS NextChangeTime,
        DATEDIFF_BIG(MILLISECOND, tagTime, LEAD(tagTime, 1, @endDate) OVER (ORDER BY tagTime)) AS DurationMs
    FROM RawData
    WHERE tagTime >= @startDate AND tagTime < @endDate
),
TimeWeightedDataWithBuckets AS (
    -- Step 2: Calculate which bucket each point belongs to
    SELECT
        tagTime,
        tagValue,
        NextChangeTime,
        DurationMs,
        DATEADD(MILLISECOND,
            (@bucketSizeMs * (DATEDIFF_BIG(MILLISECOND, @startDate, tagTime) / @bucketSizeMs)),
            @startDate) AS BucketStart,
        DATEADD(MILLISECOND,
            @bucketSizeMs,
            DATEADD(MILLISECOND,
                (@bucketSizeMs * (DATEDIFF_BIG(MILLISECOND, @startDate, tagTime) / @bucketSizeMs)),
                @startDate)) AS BucketEnd
    FROM TimeWeightedData
),
OverlapCalculation AS (
    -- Step 3: Calculate overlap duration between value duration and bucket
    SELECT
        BucketStart,
        BucketEnd,
        tagTime,
        tagValue,
        NextChangeTime,
        DurationMs,
        CASE
            WHEN NextChangeTime <= BucketStart THEN 0
            WHEN tagTime >= BucketEnd THEN 0
            ELSE DATEDIFF_BIG(MILLISECOND,
                CASE WHEN tagTime > BucketStart THEN tagTime ELSE BucketStart END,
                CASE WHEN NextChangeTime < BucketEnd THEN NextChangeTime ELSE BucketEnd END
            )
        END AS OverlapMs
    FROM TimeWeightedDataWithBuckets
)
-- Step 4: Aggregate by bucket with time-weighted average
SELECT
    BucketStart AS Timestamp,
    CASE
        WHEN SUM(OverlapMs) > 0
        THEN SUM(tagValue * OverlapMs) / CAST(SUM(OverlapMs) AS FLOAT)
        ELSE 0
    END AS TimeWeightedAvg,
    MIN(tagValue) AS MinValue,
    MAX(tagValue) AS MaxValue,
    COUNT(*) AS PointCount,
    SUM(OverlapMs) AS TotalDurationMs
FROM OverlapCalculation
WHERE OverlapMs > 0
GROUP BY BucketStart
ORDER BY BucketStart;";

            // Execute the query
            var results = await _databaseService.QueryAsync<AggregatedDataPoint>(aggregationQuery);

            if (results == null || !results.Any())
            {
                _logger.LogDebug("No aggregated data found for tag {TagId}", tagId);
                return Enumerable.Empty<ChartDataPoint>();
            }

            // Convert to ChartDataPoint
            var chartData = results.Select(r => new ChartDataPoint
            {
                Timestamp = r.Timestamp,
                Value = r.TimeWeightedAvg
            }).ToList();

            _logger.LogDebug("Retrieved {Count} aggregated data points for tag {TagId} (reduced from potentially much larger dataset)",
                chartData.Count, tagId);

            return chartData;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error getting time-weighted aggregated history for TagId {TagId}", tagId);
            return Enumerable.Empty<ChartDataPoint>();
        }
    }

    /// <summary>
    /// DTO for aggregated data point results from SQL query
    /// </summary>
    public class AggregatedDataPoint
    {
        public DateTime Timestamp { get; set; }
        public double TimeWeightedAvg { get; set; }
        public double MinValue { get; set; }
        public double MaxValue { get; set; }
        public int PointCount { get; set; }
        public long TotalDurationMs { get; set; }
    }

    /// <summary>
    /// Checks if aggregated data is cached for the given parameters.
    /// </summary>
    public async Task<bool> HasCachedAggregatedDataAsync(
        int tagId,
        DateTime startDate,
        DateTime endDate,
        int levelOfDetail,
        string machineCode)
    {
        try
        {
            await LocalCacheContext.EnsureCacheSchemaAsync(_cacheContextFactory, _logger);
            using var cacheContext = await _cacheContextFactory.CreateDbContextAsync();

            return await cacheContext.CachedAggregatedSurveyorData
                .AnyAsync(d => d.MachineCode == machineCode
                            && d.TagId == tagId
                            && d.LevelOfDetail == levelOfDetail
                            && d.BucketStartTime >= startDate
                            && d.BucketStartTime <= endDate);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error checking cached aggregated data for TagId {TagId}, LOD {LOD}", tagId, levelOfDetail);
            return false;
        }
    }

    /// <summary>
    /// Gets cached aggregated data from the local cache.
    /// </summary>
    public async Task<IEnumerable<ChartDataPoint>> GetCachedAggregatedDataAsync(
        int tagId,
        DateTime startDate,
        DateTime endDate,
        int levelOfDetail,
        string machineCode)
    {
        try
        {
            await LocalCacheContext.EnsureCacheSchemaAsync(_cacheContextFactory, _logger);
            using var cacheContext = await _cacheContextFactory.CreateDbContextAsync();

            var cachedData = await cacheContext.CachedAggregatedSurveyorData
                .Where(d => d.MachineCode == machineCode
                         && d.TagId == tagId
                         && d.LevelOfDetail == levelOfDetail
                         && d.BucketStartTime >= startDate
                         && d.BucketStartTime <= endDate)
                .OrderBy(d => d.BucketStartTime)
                .ToListAsync();

            return cachedData.Select(d => new ChartDataPoint
            {
                Timestamp = d.BucketStartTime,
                Value = d.TimeWeightedAvg
            }).ToList();
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error retrieving cached aggregated data for TagId {TagId}, LOD {LOD}", tagId, levelOfDetail);
            return Enumerable.Empty<ChartDataPoint>();
        }
    }

    /// <summary>
    /// Caches aggregated data to the local cache.
    /// </summary>
    public async Task CacheAggregatedDataAsync(
        int tagId,
        DateTime startDate,
        DateTime endDate,
        int levelOfDetail,
        string machineCode,
        IEnumerable<AggregatedDataPoint> aggregatedData)
    {
        try
        {
            var dataList = aggregatedData.ToList();
            if (!dataList.Any())
            {
                _logger.LogDebug("No aggregated data to cache for TagId {TagId}, LOD {LOD}", tagId, levelOfDetail);
                return;
            }

            await LocalCacheContext.EnsureCacheSchemaAsync(_cacheContextFactory, _logger);
            using var cacheContext = await _cacheContextFactory.CreateDbContextAsync();

            // Get existing timestamps to avoid duplicates
            var existingTimes = await cacheContext.CachedAggregatedSurveyorData
                .Where(d => d.MachineCode == machineCode
                         && d.TagId == tagId
                         && d.LevelOfDetail == levelOfDetail
                         && d.BucketStartTime >= startDate
                         && d.BucketStartTime <= endDate)
                .Select(d => d.BucketStartTime)
                .ToListAsync();

            var existingTimesSet = new HashSet<DateTime>(existingTimes);

            // Prepare entities to insert
            var toInsert = dataList
                .Where(d => !existingTimesSet.Contains(d.Timestamp))
                .Select(d => new CachedAggregatedSurveyorData
                {
                    MachineCode = machineCode,
                    TagId = tagId,
                    LevelOfDetail = levelOfDetail,
                    BucketStartTime = d.Timestamp,
                    TimeWeightedAvg = d.TimeWeightedAvg,
                    MinValue = d.MinValue,
                    MaxValue = d.MaxValue,
                    OriginalPointCount = d.PointCount,
                    TotalDurationMs = d.TotalDurationMs,
                    CachedAt = DateTime.UtcNow
                })
                .ToList();

            if (toInsert.Any())
            {
                await cacheContext.CachedAggregatedSurveyorData.AddRangeAsync(toInsert);
                await cacheContext.SaveChangesAsync();

                _logger.LogDebug("Cached {Count} aggregated data points for TagId {TagId}, LOD {LOD}",
                    toInsert.Count, tagId, levelOfDetail);
            }
            else
            {
                _logger.LogDebug("All aggregated data already cached for TagId {TagId}, LOD {LOD}", tagId, levelOfDetail);
            }
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error caching aggregated data for TagId {TagId}, LOD {LOD}", tagId, levelOfDetail);
        }
    }

    /// <summary>
    /// Gets aggregated data with caching - checks cache first, then queries and caches if needed.
    /// </summary>
    public async Task<IEnumerable<ChartDataPoint>> GetAggregatedDataWithCacheAsync(
        int tagId,
        DateTime startDate,
        DateTime endDate,
        long bucketSizeMs,
        int levelOfDetail,
        string machineCode,
        CancellationToken cancellationToken = default)
    {
        try
        {
            // Check cache first
            var hasCached = await HasCachedAggregatedDataAsync(tagId, startDate, endDate, levelOfDetail, machineCode);

            if (hasCached)
            {
                _logger.LogDebug("Using cached aggregated data for TagId {TagId}, LOD {LOD}", tagId, levelOfDetail);
                return await GetCachedAggregatedDataAsync(tagId, startDate, endDate, levelOfDetail, machineCode);
            }

            // Cache miss - query database
            _logger.LogDebug("Cache miss for TagId {TagId}, LOD {LOD} - querying database", tagId, levelOfDetail);
            var aggregatedResults = await GetTimeWeightedAggregatedHistoryAsync(
                tagId, startDate, endDate, bucketSizeMs, machineCode, cancellationToken);

            // Convert to AggregatedDataPoint for caching (need full data)
            // Re-query with full data structure
            var fullAggregatedData = await GetTimeWeightedAggregatedHistoryWithMetadataAsync(
                tagId, startDate, endDate, bucketSizeMs, machineCode, cancellationToken);

            // Cache the results for future use
            await CacheAggregatedDataAsync(tagId, startDate, endDate, levelOfDetail, machineCode, fullAggregatedData);

            // Return the chart data
            return aggregatedResults;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error getting aggregated data with cache for TagId {TagId}, LOD {LOD}", tagId, levelOfDetail);
            return Enumerable.Empty<ChartDataPoint>();
        }
    }

    /// <summary>
    /// Gets time-weighted aggregated data with full metadata (for caching purposes).
    /// Similar to GetTimeWeightedAggregatedHistoryAsync but returns AggregatedDataPoint with all fields.
    /// </summary>
    private async Task<IEnumerable<AggregatedDataPoint>> GetTimeWeightedAggregatedHistoryWithMetadataAsync(
        int tagId,
        DateTime startDate,
        DateTime endDate,
        long bucketSizeMs,
        string machineCode,
        CancellationToken cancellationToken = default)
    {
        try
        {
            // Build the union query for all surveyor tables
            var unionQuery = await BuildUnionQueryForAllSurveyorTablesAsync(
                new[] { tagId },
                machineCode,
                startDate,
                endDate
            );

            if (string.IsNullOrEmpty(unionQuery))
            {
                return Enumerable.Empty<AggregatedDataPoint>();
            }

            // Build the time-weighted aggregation query (same as before)
            var aggregationQuery = $@"
DECLARE @startDate DATETIME = '{startDate:yyyy-MM-dd HH:mm:ss.fff}';
DECLARE @endDate DATETIME = '{endDate:yyyy-MM-dd HH:mm:ss.fff}';
DECLARE @bucketSizeMs BIGINT = {bucketSizeMs};

WITH RawData AS (
    SELECT ID, tagTime, tagValue
    FROM ({unionQuery}) AS UnionedData
),
TimeWeightedData AS (
    SELECT
        tagTime,
        tagValue,
        LEAD(tagTime, 1, @endDate) OVER (ORDER BY tagTime) AS NextChangeTime,
        DATEDIFF_BIG(MILLISECOND, tagTime, LEAD(tagTime, 1, @endDate) OVER (ORDER BY tagTime)) AS DurationMs
    FROM RawData
    WHERE tagTime >= @startDate AND tagTime < @endDate
),
TimeWeightedDataWithBuckets AS (
    SELECT
        tagTime,
        tagValue,
        NextChangeTime,
        DurationMs,
        DATEADD(MILLISECOND,
            (@bucketSizeMs * (DATEDIFF_BIG(MILLISECOND, @startDate, tagTime) / @bucketSizeMs)),
            @startDate) AS BucketStart,
        DATEADD(MILLISECOND,
            @bucketSizeMs,
            DATEADD(MILLISECOND,
                (@bucketSizeMs * (DATEDIFF_BIG(MILLISECOND, @startDate, tagTime) / @bucketSizeMs)),
                @startDate)) AS BucketEnd
    FROM TimeWeightedData
),
OverlapCalculation AS (
    SELECT
        BucketStart,
        BucketEnd,
        tagTime,
        tagValue,
        NextChangeTime,
        DurationMs,
        CASE
            WHEN NextChangeTime <= BucketStart THEN 0
            WHEN tagTime >= BucketEnd THEN 0
            ELSE DATEDIFF_BIG(MILLISECOND,
                CASE WHEN tagTime > BucketStart THEN tagTime ELSE BucketStart END,
                CASE WHEN NextChangeTime < BucketEnd THEN NextChangeTime ELSE BucketEnd END
            )
        END AS OverlapMs
    FROM TimeWeightedDataWithBuckets
)
SELECT
    BucketStart AS Timestamp,
    CASE
        WHEN SUM(OverlapMs) > 0
        THEN SUM(tagValue * OverlapMs) / CAST(SUM(OverlapMs) AS FLOAT)
        ELSE 0
    END AS TimeWeightedAvg,
    MIN(tagValue) AS MinValue,
    MAX(tagValue) AS MaxValue,
    COUNT(*) AS PointCount,
    SUM(OverlapMs) AS TotalDurationMs
FROM OverlapCalculation
WHERE OverlapMs > 0
GROUP BY BucketStart
ORDER BY BucketStart;";

            var results = await _databaseService.QueryAsync<AggregatedDataPoint>(aggregationQuery);
            return results ?? Enumerable.Empty<AggregatedDataPoint>();
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error getting time-weighted aggregated history with metadata for TagId {TagId}", tagId);
            return Enumerable.Empty<AggregatedDataPoint>();
        }
    }

    private async Task<string> BuildUnionQueryForSurveyorTablesAsync(
        string tablePrefix,
        int tagId,
        string machineCode,
        DateTime startDate,
        DateTime endDate
    )
    {
        var serverName = await _configurationService.GetMachineServerNameAsync();
        var baseTableName = $"{serverName}_{machineCode}_{tablePrefix}";

        var tableNames = new List<string>();
        for (var dt = startDate.Date; dt <= endDate.Date; dt = dt.AddDays(1))
        {
            // Format the date as yyMMdd
            var dateSuffix = dt.ToString("yyMMdd");
            tableNames.Add($"{baseTableName}_{dateSuffix}");
        }

        if (!tableNames.Any())
        {
            _logger.LogWarning(
                "No table names generated for date range {StartDate} to {EndDate} with pattern {Pattern}",
                startDate,
                endDate,
                $"{baseTableName}_[date]"
            );
            return string.Empty;
        }

        // Get all existing tables that match our pattern to validate table existence
        var tablePattern = $"{baseTableName}%";
        var existingTablesQuery = "SELECT table_name FROM information_schema.tables WHERE table_name LIKE {0}";
        var existingTables = await _databaseService.QueryAsync<TableName>(existingTablesQuery, tablePattern);
        var existingTableNames = new HashSet<string>(existingTables.Select(t => t.table_name), StringComparer.OrdinalIgnoreCase);

        _logger.LogDebug("Found {Count} existing tables matching pattern {Pattern}", existingTableNames.Count, tablePattern);

        // Only build union parts for tables that actually exist
        var unionParts = new List<string>();
        foreach (var tableName in tableNames)
        {
            if (existingTableNames.Contains(tableName))
            {
                unionParts.Add($"SELECT tagTime, tagValue FROM {tableName} WHERE ID = {tagId} AND tagValue <> 0 AND tagTime >= '{startDate:yyyy-MM-dd HH:mm:ss}' AND tagTime <= '{endDate:yyyy-MM-dd HH:mm:ss}'");
            }
            else
            {
                _logger.LogDebug("Table {TableName} does not exist, skipping", tableName);
            }
        }

        if (!unionParts.Any())
        {
            _logger.LogWarning("No valid tables found for date range {StartDate} to {EndDate} with pattern {Pattern}", startDate, endDate, tablePattern);
            return string.Empty;
        }

        return string.Join(" UNION ALL ", unionParts);
    }

    /// <summary>
    /// Builds a union query that fetches data from all three surveyor table types (ShortData, ValueData, StateData)
    /// for multiple tag IDs across the specified date range.
    /// Only includes tables that actually exist in the database.
    /// </summary>
    private async Task<string> BuildUnionQueryForAllSurveyorTablesAsync(
        IEnumerable<int> tagIds,
        string machineCode,
        DateTime startDate,
        DateTime endDate
    )
    {
        var serverName = await _configurationService.GetMachineServerNameAsync();
        var tagIdList = tagIds.ToList();

        if (!tagIdList.Any())
        {
            _logger.LogWarning("No tag IDs provided for union query");
            return string.Empty;
        }

        var tagIdFilter = string.Join(",", tagIdList);
        var unionParts = new List<string>();

        // Build date-based table names for the date range
        var dateList = new List<string>();
        for (var dt = startDate.Date; dt <= endDate.Date; dt = dt.AddDays(1))
        {
            dateList.Add(dt.ToString("yyMMdd"));
        }

        if (!dateList.Any())
        {
            _logger.LogWarning(
                "No dates generated for date range {StartDate} to {EndDate}",
                startDate,
                endDate
            );
            return string.Empty;
        }

        // Table prefixes to query (both Surveyor and TBM)
        var tablePrefixes = new[] { "Surveyor", "TBM" };

        // Get all existing tables that match our pattern
        var tableQueryStopwatch = System.Diagnostics.Stopwatch.StartNew();
        var tablePattern = $"{serverName}_{machineCode}_%";
        var existingTablesQuery = "SELECT table_name FROM information_schema.tables WHERE table_name LIKE {0}";
        var existingTables = await _databaseService.QueryAsync<TableName>(existingTablesQuery, tablePattern);
        var existingTableNames = new HashSet<string>(existingTables.Select(t => t.table_name), StringComparer.OrdinalIgnoreCase);
        tableQueryStopwatch.Stop();

        _logger.LogInformation("⏱️ REPO PERF - Query existing tables ({Count} found): {ElapsedMs}ms",
            existingTableNames.Count, tableQueryStopwatch.ElapsedMilliseconds);

        // For each date and table prefix, create union parts for all three table types (only if they exist)
        foreach (var dateSuffix in dateList)
        {
            foreach (var tablePrefix in tablePrefixes)
            {
                // ShortData tables (INT values)
                var shortDataTable = $"{serverName}_{machineCode}_ShortData_{tablePrefix}_{dateSuffix}";
                if (existingTableNames.Contains(shortDataTable))
                {
                    unionParts.Add($@"
                    SELECT ID, tagTime, CAST(tagValue AS FLOAT) AS tagValue
                    FROM dbo.{shortDataTable}
                    WHERE ID IN ({tagIdFilter})
                    AND tagValue <> 0
                    AND tagTime >= '{startDate:yyyy-MM-dd HH:mm:ss}'
                    AND tagTime <= '{endDate:yyyy-MM-dd HH:mm:ss}'");
                }
                else
                {
                    _logger.LogDebug("Table {TableName} does not exist, skipping", shortDataTable);
                }

                // ValueData tables (FLOAT values)
                var valueDataTable = $"{serverName}_{machineCode}_ValueData_{tablePrefix}_{dateSuffix}";
                if (existingTableNames.Contains(valueDataTable))
                {
                    unionParts.Add($@"
                    SELECT ID, tagTime, tagValue
                    FROM dbo.{valueDataTable}
                    WHERE ID IN ({tagIdFilter})
                    AND tagValue <> 0
                    AND tagTime >= '{startDate:yyyy-MM-dd HH:mm:ss}'
                    AND tagTime <= '{endDate:yyyy-MM-dd HH:mm:ss}'");
                }
                else
                {
                    _logger.LogDebug("Table {TableName} does not exist, skipping", valueDataTable);
                }

                // StateData tables (BIT values)
                var stateDataTable = $"{serverName}_{machineCode}_StateData_{tablePrefix}_{dateSuffix}";
                if (existingTableNames.Contains(stateDataTable))
                {
                    unionParts.Add($@"
                    SELECT ID, tagTime, CAST(tagValue AS FLOAT) AS tagValue
                    FROM dbo.{stateDataTable}
                    WHERE ID IN ({tagIdFilter})
                    AND tagTime >= '{startDate:yyyy-MM-dd HH:mm:ss}'
                    AND tagTime <= '{endDate:yyyy-MM-dd HH:mm:ss}'");
                }
                else
                {
                    _logger.LogDebug("Table {TableName} does not exist, skipping", stateDataTable);
                }
            }
        }

        if (!unionParts.Any())
        {
            _logger.LogWarning("No valid tables found for date range {StartDate} to {EndDate}", startDate, endDate);
            return string.Empty;
        }

        _logger.LogDebug("Built union query with {Count} table parts", unionParts.Count);
        return string.Join(" UNION ALL ", unionParts);
    }


    public async Task<Range<int>?> GetRingRangeForDateRangeAsync(
        DateTime startDate,
        DateTime endDate,
        string machineCode
    )
    {
        var tagConfig = await _configurationService.GetTagConfigurationAsync();
        return await GetRangeForDateRangeAsync<int>(
            tagConfig.RingTablePrefix,
            tagConfig.RingTagId,
            startDate,
            endDate,
            machineCode,
            value => (int)value,
            "INT"
        );
    }

    public async Task<Range<int>?> GetStrokeRangeForDateRangeAsync(
        DateTime startDate,
        DateTime endDate,
        string machineCode
    )
    {
        var tagConfig = await _configurationService.GetTagConfigurationAsync();
        return await GetRangeForDateRangeAsync<int>(
            tagConfig.StrokeTablePrefix,
            tagConfig.StrokeTagId,
            startDate,
            endDate,
            machineCode,
            value => (int)value,
            "INT"
        );
    }

    public async Task<Range<double>?> GetChainageRangeForDateRangeAsync(
        DateTime startDate,
        DateTime endDate,
        string machineCode
    )
    {
        var tagConfig = await _configurationService.GetTagConfigurationAsync();
        return await GetRangeForDateRangeAsync<double>(
            tagConfig.ChainageTablePrefix,
            tagConfig.ChainageTagId,
            startDate,
            endDate,
            machineCode,
            value => value,
            "FLOAT"
        );
    }



    public async Task<Range<DateTime>?> GetDateRangeForRingRangeAsync(
        int startRing,
        int endRing,
        string machineCode
    )
    {
        var useCache = await _configurationService.GetUseCacheAsync();

        if (!useCache)
        {
            _logger.LogDebug("Cache disabled for ring range query on machine {Machine} (UseCache: {UseCache})", machineCode, useCache);
            // Skip cache and go directly to remote query
            var tagConfig = await _configurationService.GetTagConfigurationAsync();
            var unionQuery = await BuildUnionQueryForSurveyorTablesAsync(
                tagConfig.RingTablePrefix,
                tagConfig.RingTagId,
                machineCode,
                SqlMinDate,
                SqlMaxDate
            );
            if (string.IsNullOrEmpty(unionQuery))
                return null;

            var fullQuery = $@"
                SELECT MIN(TRY_CONVERT(datetime2, tagTime)) AS Start, MAX(TRY_CONVERT(datetime2, tagTime)) AS [End]
                FROM ({unionQuery}) AS CombinedData
                WHERE CAST(tagValue AS INT) >= {{0}} AND CAST(tagValue AS INT) <= {{1}}";

            var result =
                await _databaseService.QuerySingleOrDefaultAsync<Range<DateTime>>(
                    fullQuery,
                    startRing,
                    endRing
                );
            return result;
        }

        try
        {
            // Ensure cache schema
            await LocalCacheContext.EnsureCacheSchemaAsync(_cacheContextFactory, _logger);
            using var cacheContext = await _cacheContextFactory.CreateDbContextAsync();

            // Check if data is cached (need all data for this tag)
            var tagConfig = await _configurationService.GetTagConfigurationAsync();
            var hasCachedData = await cacheContext.CachedSurveyorData
                .AnyAsync(d => d.MachineCode == machineCode &&
                              d.TableName.Contains(tagConfig.RingTablePrefix) &&
                              d.TagId == tagConfig.RingTagId);

            if (!hasCachedData)
            {
                // Cache all data for this tag (bounded)
                await CacheSurveyorDataAsync(tagConfig.RingTablePrefix, tagConfig.RingTagId, machineCode, SqlMinDate, SqlMaxDate);
            }

            // Query from cache
            var data = await cacheContext.CachedSurveyorData
                .Where(d => d.MachineCode == machineCode &&
                           d.TableName.Contains(tagConfig.RingTablePrefix) &&
                           d.TagId == tagConfig.RingTagId &&
                           (int)d.TagValue >= startRing &&
                           (int)d.TagValue <= endRing)
                .Select(d => d.TagTime)
                .ToListAsync();

            if (!data.Any())
                return null;

            return new Range<DateTime>(data.Min(), data.Max());
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error in GetDateRangeForRingRangeAsync, falling back to remote query (UseCache: {UseCache})", useCache);
            // Fallback to original remote query
            var tagConfig = await _configurationService.GetTagConfigurationAsync();
            var unionQuery = await BuildUnionQueryForSurveyorTablesAsync(
                tagConfig.RingTablePrefix,
                tagConfig.RingTagId,
                machineCode,
                SqlMinDate,
                SqlMaxDate
            );
            if (string.IsNullOrEmpty(unionQuery))
                return null;

            var fullQuery = $@"
                SELECT MIN(TRY_CONVERT(datetime2, tagTime)) AS Start, MAX(TRY_CONVERT(datetime2, tagTime)) AS [End]
                FROM ({unionQuery}) AS CombinedData
                WHERE CAST(tagValue AS INT) >= {{0}} AND CAST(tagValue AS INT) <= {{1}}";

            var result =
                await _databaseService.QuerySingleOrDefaultAsync<Range<DateTime>>(
                    fullQuery,
                    startRing,
                    endRing
                );
            return result;
        }
    }



    public async Task<Range<DateTime>?> GetDateRangeForChainageRangeAsync(
        double startChainage,
        double endChainage,
        string machineCode
    )
    {
        var useCache = await _configurationService.GetUseCacheAsync();

        if (!useCache)
        {
            _logger.LogDebug("Cache disabled for chainage range query on machine {Machine} (UseCache: {UseCache})", machineCode, useCache);
            // Skip cache and go directly to remote query
            var tagConfig = await _configurationService.GetTagConfigurationAsync();
            var unionQuery = await BuildUnionQueryForSurveyorTablesAsync(
                tagConfig.ChainageTablePrefix,
                tagConfig.ChainageTagId,
                machineCode,
                SqlMinDate,
                SqlMaxDate
            );
            if (string.IsNullOrEmpty(unionQuery))
                return null;

            var fullQuery = $@"
                SELECT MIN(TRY_CONVERT(datetime2, tagTime)) AS Start, MAX(TRY_CONVERT(datetime2, tagTime)) AS [End]
                FROM ({unionQuery}) AS CombinedData
                WHERE CAST(tagValue AS FLOAT) >= {{0}} AND CAST(tagValue AS FLOAT) <= {{1}}";

            var result =
                await _databaseService.QuerySingleOrDefaultAsync<Range<DateTime>>(
                    fullQuery,
                    startChainage,
                    endChainage
                );
            return result;
        }

        try
        {
            // Ensure cache schema
            await LocalCacheContext.EnsureCacheSchemaAsync(_cacheContextFactory, _logger);
            using var cacheContext = await _cacheContextFactory.CreateDbContextAsync();

            // Check if data is cached (need all data for this tag)
            var tagConfig = await _configurationService.GetTagConfigurationAsync();
            var hasCachedData = await cacheContext.CachedSurveyorData
                .AnyAsync(d => d.MachineCode == machineCode &&
                              d.TableName.Contains(tagConfig.ChainageTablePrefix) &&
                              d.TagId == tagConfig.ChainageTagId);

            if (!hasCachedData)
            {
                // Cache all data for this tag (bounded)
                await CacheSurveyorDataAsync(tagConfig.ChainageTablePrefix, tagConfig.ChainageTagId, machineCode, SqlMinDate, SqlMaxDate);
            }

            // Query from cache
            var data = await cacheContext.CachedSurveyorData
                .Where(d => d.MachineCode == machineCode &&
                           d.TableName.Contains(tagConfig.ChainageTablePrefix) &&
                           d.TagId == tagConfig.ChainageTagId &&
                           d.TagValue >= startChainage &&
                           d.TagValue <= endChainage)
                .Select(d => d.TagTime)
                .ToListAsync();

            if (!data.Any())
                return null;

            return new Range<DateTime>(data.Min(), data.Max());
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error in GetDateRangeForChainageRangeAsync, falling back to remote query (UseCache: {UseCache})", useCache);
            // Fallback to original remote query
            var tagConfig = await _configurationService.GetTagConfigurationAsync();
            var unionQuery = await BuildUnionQueryForSurveyorTablesAsync(
                tagConfig.ChainageTablePrefix,
                tagConfig.ChainageTagId,
                machineCode,
                SqlMinDate,
                SqlMaxDate
            );
            if (string.IsNullOrEmpty(unionQuery))
                return null;

            var fullQuery = $@"
                SELECT MIN(TRY_CONVERT(datetime2, tagTime)) AS Start, MAX(TRY_CONVERT(datetime2, tagTime)) AS [End]
                FROM ({unionQuery}) AS CombinedData
                WHERE CAST(tagValue AS FLOAT) >= {{0}} AND CAST(tagValue AS FLOAT) <= {{1}}";

            var result =
                await _databaseService.QuerySingleOrDefaultAsync<Range<DateTime>>(
                    fullQuery,
                    startChainage,
                    endChainage
                );
            return result;
        }
    }



    private async Task CacheSurveyorDataAsync(string tablePrefix, int tagId, string machineCode, DateTime startDate, DateTime endDate)
    {
        try
        {
            // Bound dates to safe SQL range
            var boundedStart = startDate < SqlMinDate ? SqlMinDate : startDate;
            var boundedEnd = endDate > SqlMaxDate ? SqlMaxDate : endDate;

            var serverName = await _configurationService.GetMachineServerNameAsync();
            var baseTableName = $"{serverName}_{machineCode}_{tablePrefix}";

            var getTablesQuery = "SELECT table_name FROM information_schema.tables WHERE table_name LIKE {0}";
            var tables = await _databaseService.QueryAsync<TableName>(getTablesQuery, $"{baseTableName}%");

            if (!tables.Any())
                return;

            // Ensure schema before write
            await LocalCacheContext.EnsureCacheSchemaAsync(_cacheContextFactory, _logger);
            using var cacheContext = await _cacheContextFactory.CreateDbContextAsync();

            int totalInserted = 0;

            foreach (var table in tables)
            {
                // Chunk by day to avoid huge pulls
                var dayStart = boundedStart.Date;
                while (dayStart <= boundedEnd)
                {
                    var dayEnd = dayStart.Date.AddDays(1).AddTicks(-1);
                    if (dayEnd > boundedEnd) dayEnd = boundedEnd;

                    // Parameterized raw SQL; table name injected from information_schema whitelist
                    var query = $"SELECT {{0}} AS MachineCode, {{1}} AS TableName, ID, tagTime, tagValue FROM {table.table_name} WHERE ID = {{2}} AND tagValue <> 0 AND tagTime BETWEEN {{3}} AND {{4}}";

                    if (tablePrefix == "ShortData_Surveyor")
                    {
                        var rawData = (await _databaseService.QueryAsync<SurveyorShortDataDto>(
                            query,
                            machineCode,
                            table.table_name,
                            tagId,
                            dayStart,
                            dayEnd
                        )).ToList();

                        if (rawData.Count > 0)
                        {
                            // Avoid duplicates against existing rows (unique index on MachineCode, TableName, TagId, TagTime)
                            var existingTimes = await cacheContext.CachedSurveyorData
                                .Where(d => d.MachineCode == machineCode
                                         && d.TableName == table.table_name
                                         && d.TagId == tagId
                                         && d.TagTime >= dayStart
                                         && d.TagTime <= dayEnd)
                                .Select(d => d.TagTime)
                                .ToListAsync();

                            var toInsert = rawData
                                .Where(r => !existingTimes.Contains(r.tagTime))
                                .Select(r => new CachedSurveyorData
                                {
                                    MachineCode = r.MachineCode,
                                    TableName = r.TableName,
                                    TagId = r.ID,
                                    TagTime = r.tagTime,
                                    TagValue = r.tagValue.HasValue ? r.tagValue.Value : 0.0
                                })
                                .ToList();

                            if (toInsert.Count > 0)
                            {
                                await cacheContext.CachedSurveyorData.AddRangeAsync(toInsert);
                                totalInserted += toInsert.Count;
                            }
                        }
                    }
                    else // ValueData_Surveyor
                    {
                        var rawData = (await _databaseService.QueryAsync<SurveyorValueDataDto>(
                            query,
                            machineCode,
                            table.table_name,
                            tagId,
                            dayStart,
                            dayEnd
                        )).ToList();

                        if (rawData.Count > 0)
                        {
                            // Avoid duplicates against existing rows (unique index on MachineCode, TableName, TagId, TagTime)
                            var existingTimes = await cacheContext.CachedSurveyorData
                                .Where(d => d.MachineCode == machineCode
                                         && d.TableName == table.table_name
                                         && d.TagId == tagId
                                         && d.TagTime >= dayStart
                                         && d.TagTime <= dayEnd)
                                .Select(d => d.TagTime)
                                .ToListAsync();

                            var toInsert = rawData
                                .Where(r => !existingTimes.Contains(r.tagTime))
                                .Select(r => new CachedSurveyorData
                                {
                                    MachineCode = r.MachineCode,
                                    TableName = r.TableName,
                                    TagId = r.ID,
                                    TagTime = r.tagTime,
                                    TagValue = r.tagValue ?? 0.0
                                })
                                .ToList();

                            if (toInsert.Count > 0)
                            {
                                await cacheContext.CachedSurveyorData.AddRangeAsync(toInsert);
                                totalInserted += toInsert.Count;
                            }
                        }
                    }

                    dayStart = dayStart.AddDays(1);
                }
            }

            if (totalInserted > 0)
            {
                await cacheContext.SaveChangesAsync();
            }

            _logger.LogInformation("Cached {Count} surveyor records for {TablePrefix} on machine {MachineCode}", totalInserted, tablePrefix, machineCode);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error caching surveyor data for {TablePrefix} on machine {MachineCode}", tablePrefix, machineCode);
        }
    }

    private class TableName
    {
        public string table_name { get; set; } = "";
    }

    private class SurveyorShortDataDto
    {
        public string MachineCode { get; set; } = "";
        public string TableName { get; set; } = "";
        public int ID { get; set; } // Matches SQL column name
        public DateTime tagTime { get; set; } // Matches SQL column name
        public int? tagValue { get; set; } // For ShortData_Surveyor tables
    }

    private class SurveyorValueDataDto
    {
        public string MachineCode { get; set; } = "";
        public string TableName { get; set; } = "";
        public int ID { get; set; } // Matches SQL column name
        public DateTime tagTime { get; set; } // Matches SQL column name
        public double? tagValue { get; set; } // For ValueData_Surveyor tables
    }

    private class RawTagDataDto
    {
        public int ID { get; set; } // Tag ID
        public DateTime tagTime { get; set; } // Timestamp
        public double tagValue { get; set; } // Value (already cast to FLOAT in query)
    }

    /// <summary>
    /// DTO for pre-aggregated weighted values from AValueData table
    /// </summary>
    public class AValueDataPoint
    {
        public int TagID { get; set; }
        public DateTime TagTime { get; set; }
        public decimal TagValue { get; set; }
        public decimal Weight_Seconds { get; set; }
    }

    /// <summary>
    /// DTO for ring-based weighted averages from AValueDataAverage table
    /// </summary>
    public class AValueDataAveragePoint
    {
        public int TagID { get; set; }
        public int RingNumber { get; set; }
        public DateTime TimestampStart { get; set; }
        public DateTime TimestampEnd { get; set; }
        public decimal AverageValue { get; set; }
        public decimal? MedianValue { get; set; }
        public decimal AverageWeight_Seconds { get; set; }
        public int DataPointCount { get; set; }
    }

    private async Task<Range<T>?> GetRangeForDateRangeAsync<T>(
        string tablePrefix,
        int tagId,
        DateTime startDate,
        DateTime endDate,
        string machineCode,
        Func<double, T> converter,
        string sqlCastType
    ) where T : struct
    {
        var useCache = await _configurationService.GetUseCacheAsync();

        if (!useCache)
        {
            _logger.LogDebug("Cache disabled for {Table} tag {TagId} machine {Machine} range {Start} - {End} (UseCache: {UseCache})", tablePrefix, tagId, machineCode, startDate, endDate, useCache);
            // Skip cache and go directly to remote query
            var unionQuery = await BuildUnionQueryForSurveyorTablesAsync(
                tablePrefix,
                tagId,
                machineCode,
                startDate,
                endDate
            );
            if (string.IsNullOrEmpty(unionQuery))
                return null;

            var fullQuery = $@"
            SELECT
                ISNULL(MIN(CAST(tagValue AS {sqlCastType})), 0) AS Start,
                ISNULL(MAX(CAST(tagValue AS {sqlCastType})), 0) AS [End]
            FROM ({unionQuery}) AS CombinedData
            WHERE TRY_CONVERT(datetime2, tagTime) >= {{0}} AND TRY_CONVERT(datetime2, tagTime) <= {{1}}";

            var result =
                await _databaseService.QuerySingleOrDefaultAsync<Range<T>>(
                    fullQuery,
                    startDate,
                    endDate
                );
            return result;
        }

        try
        {
            // Ensure cache schema
            await LocalCacheContext.EnsureCacheSchemaAsync(_cacheContextFactory, _logger);
            using var cacheContext = await _cacheContextFactory.CreateDbContextAsync();

            // Check if data is cached
            var hasCachedData = await cacheContext.CachedSurveyorData
                .AnyAsync(d => d.MachineCode == machineCode &&
                              d.TableName.Contains(tablePrefix) &&
                              d.TagId == tagId &&
                              d.TagTime >= startDate &&
                              d.TagTime <= endDate);

            if (!hasCachedData)
            {
                _logger.LogDebug("Cache miss for {Table} tag {TagId} machine {Machine} range {Start} - {End} (UseCache: {UseCache})", tablePrefix, tagId, machineCode, startDate, endDate, useCache);
                await CacheSurveyorDataAsync(tablePrefix, tagId, machineCode, startDate, endDate);
            }
            else
            {
                _logger.LogDebug("Cache hit for {Table} tag {TagId} machine {Machine} range {Start} - {End} (UseCache: {UseCache})", tablePrefix, tagId, machineCode, startDate, endDate, useCache);
            }

            // Query from cache
            var values = await cacheContext.CachedSurveyorData
                .Where(d => d.MachineCode == machineCode &&
                           d.TableName.Contains(tablePrefix) &&
                           d.TagId == tagId &&
                           d.TagTime >= startDate &&
                           d.TagTime <= endDate &&
                           d.TagValue != 0)
                .Select(d => d.TagValue)
                .ToListAsync();

            var data = values.Select(v => converter(v)).ToList();

            if (!data.Any())
                return null;

            return new Range<T>(data.Min(), data.Max());
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error in GetRangeForDateRangeAsync, falling back to remote query (UseCache: {UseCache})", useCache);
            // Fallback to original remote query
            var unionQuery = await BuildUnionQueryForSurveyorTablesAsync(
                tablePrefix,
                tagId,
                machineCode,
                startDate,
                endDate
            );
            if (string.IsNullOrEmpty(unionQuery))
                return null;

            var fullQuery = $@"
            SELECT
                ISNULL(MIN(CAST(tagValue AS {sqlCastType})), 0) AS Start,
                ISNULL(MAX(CAST(tagValue AS {sqlCastType})), 0) AS [End]
            FROM ({unionQuery}) AS CombinedData
            WHERE TRY_CONVERT(datetime2, tagTime) >= {{0}} AND TRY_CONVERT(datetime2, tagTime) <= {{1}}";

            var result =
                await _databaseService.QuerySingleOrDefaultAsync<Range<T>>(
                    fullQuery,
                    startDate,
                    endDate
                );
            return result;
        }
    }

    public Task<IEnumerable<ChartDataPoint>> GetAggregatedDataWithCacheAsync(int tagId, DateTime startDate, DateTime endDate, long bucketSizeMs, string machineCode, CancellationToken cancellationToken) => throw new NotImplementedException();

    /// <summary>
    /// Gets pre-aggregated weighted values from AValueData table for conditional average calculation.
    /// This method fetches data that has been pre-processed by the ProcessAValueDataForDay stored procedure.
    /// </summary>
    public async Task<IDictionary<int, IEnumerable<AValueDataPoint>>> GetAValueDataAsync(
        IEnumerable<int> tagIds,
        DateTime startDate,
        DateTime endDate,
        string machineCode,
        decimal conditionValue = 2.0m,
        CancellationToken cancellationToken = default)
    {
        var stopwatch = System.Diagnostics.Stopwatch.StartNew();

        try
        {
            var tagIdList = tagIds.ToList();

            if (!tagIdList.Any())
            {
                return new Dictionary<int, IEnumerable<AValueDataPoint>>();
            }

            var tagIdsParam = string.Join(",", tagIdList);

            _logger.LogInformation("⏱️ REPO PERF - Fetching AValueData for {TagCount} tags (ConditionValue: {ConditionValue})",
                tagIdList.Count, conditionValue);

            var query = @"
                SELECT
                    TagID,
                    TagTime,
                    TagValue,
                    Weight_Seconds
                FROM [dbo].[AValueData]
                WHERE MachineCode = {0}
                  AND ConditionValue = {1}
                  AND TagTime >= {2}
                  AND TagTime <= {3}
                  AND TagID IN (SELECT CAST(value AS INT) FROM STRING_SPLIT({4}, ','))
                ORDER BY TagID, TagTime";

            var data = await _databaseService.QueryAsync<AValueDataPoint>(
                query,
                machineCode,
                conditionValue,
                startDate,
                endDate,
                tagIdsParam
            );

            var result = data
                .GroupBy(x => x.TagID)
                .ToDictionary(
                    g => g.Key,
                    g => g.AsEnumerable()
                );

            // Ensure all requested tags are in the dictionary
            foreach (var tagId in tagIdList)
            {
                if (!result.ContainsKey(tagId))
                {
                    result[tagId] = Enumerable.Empty<AValueDataPoint>();
                }
            }

            stopwatch.Stop();
            _logger.LogInformation("⏱️ REPO PERF - AValueData fetch complete: {ElapsedMs}ms, {RowCount} rows",
                stopwatch.ElapsedMilliseconds, data.Count());

            return result;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error fetching AValueData");
            return new Dictionary<int, IEnumerable<AValueDataPoint>>();
        }
    }

    /// <summary>
    /// Get ring-based weighted averages from AValueDataAverage table
    /// </summary>
    public async Task<IDictionary<int, IEnumerable<AValueDataAveragePoint>>> GetAValueDataAverageAsync(
        IEnumerable<int> tagIds,
        DateTime startDate,
        DateTime endDate,
        string machineCode,
        int? ringNumberStart = null,
        int? ringNumberEnd = null,
        CancellationToken cancellationToken = default)
    {
        var stopwatch = System.Diagnostics.Stopwatch.StartNew();

        try
        {
            var tagIdList = tagIds.ToList();

            if (!tagIdList.Any())
            {
                return new Dictionary<int, IEnumerable<AValueDataAveragePoint>>();
            }

            var tagIdsParam = string.Join(",", tagIdList);

            _logger.LogInformation("⏱️ REPO PERF - Fetching AValueDataAverage for {TagCount} tags",
                tagIdList.Count);

            var query = @"
                SELECT
                    TagID,
                    RingNumber,
                    TimestampStart,
                    TimestampEnd,
                    AverageValue,
                    MedianValue,
                    AverageWeight_Seconds,
                    DataPointCount
                FROM [dbo].[AValueDataAverage]
                WHERE MachineCode = {0}
                  AND TimestampStart >= {1}
                  AND TimestampEnd <= {2}
                  AND TagID IN (SELECT CAST(value AS INT) FROM STRING_SPLIT({3}, ','))";

            // Add optional ring number filters
            if (ringNumberStart.HasValue)
            {
                query += " AND RingNumber >= {4}";
            }
            if (ringNumberEnd.HasValue)
            {
                query += " AND RingNumber <= {5}";
            }

            query += " ORDER BY TagID, RingNumber";

            var parameters = new List<object> { machineCode, startDate, endDate, tagIdsParam };
            if (ringNumberStart.HasValue)
            {
                parameters.Add(ringNumberStart.Value);
            }
            if (ringNumberEnd.HasValue)
            {
                parameters.Add(ringNumberEnd.Value);
            }

            var data = await _databaseService.QueryAsync<AValueDataAveragePoint>(
                query,
                parameters.ToArray()
            );

            var result = data
                .GroupBy(x => x.TagID)
                .ToDictionary(
                    g => g.Key,
                    g => g.AsEnumerable()
                );

            // Ensure all requested tags are in the dictionary
            foreach (var tagId in tagIdList)
            {
                if (!result.ContainsKey(tagId))
                {
                    result[tagId] = Enumerable.Empty<AValueDataAveragePoint>();
                }
            }

            stopwatch.Stop();
            _logger.LogInformation("⏱️ REPO PERF - AValueDataAverage fetch complete: {ElapsedMs}ms, {RowCount} rows",
                stopwatch.ElapsedMilliseconds, data.Count());

            return result;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error fetching AValueDataAverage");
            return new Dictionary<int, IEnumerable<AValueDataAveragePoint>>();
        }
    }
}
