// // 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.Linq;
// using System.Threading;
// using System.Threading.Tasks;
// using FloraMultiPlatformFrontend.Application.Services;
// using FloraMultiPlatformFrontend.Application.Services.Interfaces;
// using FloraMultiPlatformFrontend.Data.Repositories.Interfaces;
// using FloraMultiPlatformFrontend.Presentation.Models;
// using FloraMultiPlatformFrontend.Presentation.Models.Analysis;
// using Microsoft.Extensions.Logging;
// using Microsoft.Extensions.Logging.Abstractions;
//
// namespace FloraMultiPlatformFrontend.Data.Repositories;
//
// /// <summary>
// /// Provides active operation intervals for given tag IDs within a time window by scanning FeedOPC state tables.
// /// </summary>
// public class TimelineIntervalProvider : ITimelineIntervalProvider
// {
//     private readonly IDatabaseService _databaseService;
//     private readonly ILogger<TimelineIntervalProvider> _logger;
//     private readonly ILogger _calculationsLogger;
//     private readonly IConfigurationService _configurationService;
//
//     public TimelineIntervalProvider(
//         IDatabaseService databaseService,
//         ILogger<TimelineIntervalProvider> logger,
//         IConfigurationService configurationService,
//         ILoggerFactory loggerFactory
//     )
//     {
//         _databaseService = databaseService ?? throw new ArgumentNullException(nameof(databaseService));
//         _logger = logger ?? throw new ArgumentNullException(nameof(logger));
//
//         _configurationService = configurationService ?? throw new ArgumentNullException(nameof(configurationService));
//         _calculationsLogger = loggerFactory?.CreateLogger("Calculations") ?? NullLogger.Instance;
//     }
//
//     /// <summary>
//     /// Returns for each tag ID the list of (start,end) intervals where the state was active (1) between start and end.
//     /// </summary>
//     public async Task<Dictionary<int, List<(DateTime start, DateTime end)>>> GetIntervalsAsync(
//         IEnumerable<int> tagIds,
//         DateTime start,
//         DateTime end,
//         string machineCode,
//         bool activeState = true,
//         CancellationToken cancellationToken = default
//     )
//     {
//         _logger.LogDebug("GetIntervalsAsync: tagIds={0}, start={1:o}, end={2:o}, machine={3}, activeState={4}", tagIds.Count(), start, end, machineCode, activeState);
//         _calculationsLogger.LogInformation("GetIntervalsAsync started for {count} tags, from {start} to {end} for machine {machine}, activeState={activeState}", tagIds.Count(), start, end, machineCode, activeState);
//
//         var query = new TbmDataQuery(
//             MachineCode: machineCode,
//             FromPeriod: 0,
//             ToPeriod: 0,
//             StartDateTime: start,
//             EndDateTime: end,
//             ProductionIds: new List<int>(),
//             GeneratorIds: new List<int>(),
//             MaintenanceIds: new List<int>(),
//             NoAdvanceCountReleaseIds: new List<int>(),
//             ReleaseStartId: 0,
//             ReleaseEndId: 0
//         );
//
//         var result = await QueryOperationPeriodsAsync(query, tagIds, activeState);
//         _calculationsLogger.LogInformation("GetIntervalsAsync finished, returning {count} tags with a total of {intervals} intervals.", result.Keys.Count, result.Values.Sum(l => l.Count));
//         foreach (var kvp in result)
//         {
//             _calculationsLogger.LogTrace("  - Tag {TagId}: {Count} intervals", kvp.Key, kvp.Value.Count);
//             foreach (var interval in kvp.Value)
//             {
//                 _calculationsLogger.LogTrace("    - {Start} -> {End}", interval.start, interval.end);
//             }
//         }
//         return result;
//     }
//
//     // NOTE: Implementation aligned with user's provided pattern
//     private async Task<Dictionary<int, List<(DateTime start, DateTime end)>>> QueryOperationPeriodsAsync(
//         TbmDataQuery query,
//         IEnumerable<int> ids,
//         bool activeState
//     )
//     {
//         var operationPeriods = new Dictionary<int, List<(DateTime start, DateTime end)>>();
//         var idList = ids?.Distinct().ToList() ?? new List<int>();
//         if (!idList.Any())
//         {
//             return operationPeriods;
//         }
//
//         _logger.LogDebug("QueryOperationPeriodsAsync: querying {0} IDs", idList.Count);
//
//         // Initialize dictionary keys
//         foreach (var pid in idList)
//         {
//             operationPeriods[pid] = new List<(DateTime, DateTime)>();
//         }
//
//         var validStateDataTables = await GetValidStateDataTablesAsync(query);
//         if (!validStateDataTables.Any())
//         {
//             return operationPeriods;
//         }
//
//         var start = query.StartDateTime.ToString("yyyy-MM-dd HH:mm:ss");
//         var end = query.EndDateTime.ToString("yyyy-MM-dd HH:mm:ss");
//         var idsCsv = string.Join(",", idList);
//
//         var unionQueries = string.Join(" UNION ALL ", validStateDataTables.Select(table =>
//             $@"SELECT ID, tagValue, tagTime FROM {table} WHERE ID IN ({idsCsv}) AND tagTime BETWEEN '{start}' AND '{end}'"));
//
//         var startValue = activeState ? 1 : 0;
//         var endValue = activeState ? 0 : 1;
//
//         var stateDataQuery = $@"
//                 WITH numbered_states AS (
//                     SELECT
//                         ID,
//                         tagValue,
//                         tagTime,
//                         ROW_NUMBER() OVER (PARTITION BY ID ORDER BY tagTime) as rn
//                     FROM ({unionQueries}) AS combined_data
//                 ),
//                 starts AS (
//                     SELECT ID, tagTime as StartTime, rn
//                     FROM numbered_states
//                     WHERE tagValue = {startValue}
//                 ),
//                 ends AS (
//                     SELECT ID, tagTime as EndTime, rn
//                     FROM numbered_states
//                     WHERE tagValue = {endValue}
//                 )
//                 SELECT
//                     s.ID,
//                     s.StartTime,
//                     MIN(e.EndTime) as EndTime
//                 FROM starts s
//                 LEFT JOIN ends e ON s.ID = e.ID AND e.rn > s.rn
//                 GROUP BY s.ID, s.StartTime, s.rn
//                 HAVING MIN(e.EndTime) IS NOT NULL
//                 ORDER BY s.ID, s.StartTime";
//
//         _calculationsLogger.LogInformation("Fetching operation periods for IDs {Ids}, start {Start}, end {End}, tables {Tables}", idsCsv, start, end, string.Join(", ", validStateDataTables));
//
//         _logger.LogDebug("Executing operation periods query for {0} IDs with {1} tables", idList.Count, validStateDataTables.Count);
//
//         var results = await _databaseService.QueryAsync<OperationPeriodDto>(stateDataQuery);
//
//         _calculationsLogger.LogInformation("Fetched {Count} operation period records", results.Count());
//
//         foreach (var result in results)
//         {
//             if (operationPeriods.ContainsKey(result.ID))
//             {
//                 operationPeriods[result.ID].Add((result.StartTime, result.EndTime));
//             }
//         }
//         return operationPeriods;
//     }
//
//     /// <summary>
//     /// Discovers state data tables for the machine, e.g. FeedOPC_{MachineCode}_StateData_TBM_{yyMMdd}.
//     /// </summary>
//     private async Task<List<string>> GetValidStateDataTablesAsync(TbmDataQuery query)
//     {
//         var validTables = new List<string>();
//         var startDate = query.StartDateTime.Date;
//         var endDate = query.EndDateTime.Date;
//
//         _logger.LogDebug("Scanning for tables from {Start:yyyy-MM-dd} to {End:yyyy-MM-dd} for machine {Machine}", startDate, endDate, query.MachineCode);
//
//         for (var day = startDate; day <= endDate; day = day.AddDays(1))
//         {
//
//             var serverName = await _configurationService.GetMachineServerNameAsync();
//             var tableName = $"dbo.{serverName}_{query.MachineCode}_StateData_TBM_{day:yyMMdd}";
//             var checkTableQuery = $"IF OBJECT_ID('{tableName}', 'U') IS NOT NULL SELECT 1 ELSE SELECT 0";
//
//             try
//             {
//                 var tableExists = await _databaseService.ExecuteScalarAsync<int>(checkTableQuery);
//                 if (tableExists == 1)
//                 {
//                     validTables.Add(tableName);
//                 }
//             }
//             catch (Exception ex)
//             {
//                 _logger.LogError(ex, "Failed to check for existence of table {TableName}", tableName);
//             }
//         }
//
//         _calculationsLogger.LogInformation("Found {Count} valid state data tables for machine {MachineCode} between {Start} and {End}: {Tables}",
//             validTables.Count, query.MachineCode, startDate, endDate, string.Join(", ", validTables));
//
//         if (!validTables.Any())
//         {
//             _logger.LogWarning("No state data tables found for machine {MachineCode} in the specified date range.", query.MachineCode);
//         }
//
//         return validTables;
//     }
//
//     private class OperationPeriodDto
//     {
//         public int ID { get; set; }
//         public DateTime StartTime { get; set; }
//         public DateTime EndTime { get; set; }
//     }
//
//     private class TableName
//     {
//         public string table_name { get; set; } = "";
//     }
// }
