// // 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>>();
//         }
//     }
// }
