// // 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.Threading;
// using System.Threading.Tasks;
// using FloraMultiPlatformFrontend.Data.Entities;
// using Microsoft.EntityFrameworkCore;
// using Microsoft.Extensions.Logging;
//
// namespace FloraMultiPlatformFrontend.Data;
//
// public class LocalCacheDbContext : DbContext
// {
//     private static readonly SemaphoreSlim _schemaSemaphore = new SemaphoreSlim(1, 1);
//
//     public LocalCacheDbContext(DbContextOptions<LocalCacheDbContext> options)
//         : base(options)
//     {
//     }
//
//     public DbSet<CachedStateError> CachedStateErrors { get; set; } = null!;
//     public DbSet<CachedSurveyorData> CachedSurveyorData { get; set; } = null!;
//     public DbSet<CachedWarningAlarm> CachedWarningAlarms { get; set; } = null!;
//     public DbSet<CachedAggregatedSurveyorData> CachedAggregatedSurveyorData { get; set; } = null!;
//
//     protected override void OnModelCreating(ModelBuilder modelBuilder)
//     {
//         base.OnModelCreating(modelBuilder);
//
//         // Non-unique indexes for common query filters
//         modelBuilder.Entity<CachedStateError>()
//             .HasIndex(e => new { e.MachineCode, e.TagTime });
//
//         modelBuilder.Entity<CachedSurveyorData>()
//             .HasIndex(e => new { e.MachineCode, e.TableName, e.TagTime });
//
//         // Unique composite indexes to prevent duplicates and speed lookups
//         modelBuilder.Entity<CachedStateError>()
//             .HasIndex(e => new { e.MachineCode, e.TagId, e.TagTime })
//             .IsUnique();
//
//         modelBuilder.Entity<CachedSurveyorData>()
//             .HasIndex(e => new { e.MachineCode, e.TableName, e.TagId, e.TagTime })
//             .IsUnique();
//
//         modelBuilder.Entity<CachedWarningAlarm>()
//             .HasIndex(e => new { e.MachineCode, e.QueryStartDate, e.QueryEndDate });
//
//         modelBuilder.Entity<CachedWarningAlarm>()
//             .HasIndex(e => new { e.MachineCode, e.TagId, e.IsWarning, e.IsGreater, e.IsError, e.QueryStartDate, e.QueryEndDate })
//             .IsUnique();
//
//         // Aggregated surveyor data indexes
//         modelBuilder.Entity<CachedAggregatedSurveyorData>()
//             .HasIndex(e => new { e.MachineCode, e.TagId, e.LevelOfDetail, e.BucketStartTime })
//             .IsUnique();
//
//         modelBuilder.Entity<CachedAggregatedSurveyorData>()
//             .HasIndex(e => new { e.MachineCode, e.TagId, e.LevelOfDetail });
//     }
//
//     // Ensures the cache schema exists; if required tables are missing (e.g., DB created before entities were added),
//     // creates the missing tables and indexes to avoid "no such table" errors.
//     public static async Task EnsureCacheSchemaAsync(IDbContextFactory<LocalCacheDbContext> factory, ILogger? logger = null)
//     {
//         await _schemaSemaphore.WaitAsync();
//         try
//         {
//             using var ctx = await factory.CreateDbContextAsync();
//             var dbPath = ctx.Database.GetDbConnection().DataSource;
//
//             bool hasStateErrors = false;
//             bool hasSurveyor = false;
//             bool hasWarningAlarms = false;
//             bool hasAggregatedSurveyor = false;
//             try
//             {
//                 await ctx.Database.OpenConnectionAsync();
//                 using var cmd = ctx.Database.GetDbConnection().CreateCommand();
//                 cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='CachedStateErrors';";
//                 hasStateErrors = (await cmd.ExecuteScalarAsync()) != null;
//
//                 cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='CachedSurveyorData';";
//                 hasSurveyor = (await cmd.ExecuteScalarAsync()) != null;
//
//                 cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='CachedWarningAlarms';";
//                 hasWarningAlarms = (await cmd.ExecuteScalarAsync()) != null;
//
//                 cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='CachedAggregatedSurveyorData';";
//                 hasAggregatedSurveyor = (await cmd.ExecuteScalarAsync()) != null;
//             }
//             catch (Exception ex)
//             {
//                 logger?.LogWarning(ex, "Failed to check local cache schema, will attempt to create missing tables");
//             }
//
//             if (hasStateErrors && hasSurveyor && hasWarningAlarms && hasAggregatedSurveyor)
//             {
//                 logger?.LogDebug("Local cache schema OK at {Path}", dbPath);
//                 return;
//             }
//
//             try
//             {
//                 logger?.LogInformation("Ensuring local cache database schema at {Path} (missing tables: StateErrors={HasState}, Surveyor={HasSurveyor}, WarningAlarms={HasWarningAlarms}, AggregatedSurveyor={HasAggregatedSurveyor})",
//                     dbPath, !hasStateErrors, !hasSurveyor, !hasWarningAlarms, !hasAggregatedSurveyor);
//             }
//             catch
//             {
//                 // ignore logging failures
//             }
//
//             // Create missing tables
//             await ctx.Database.ExecuteSqlRawAsync(@"
//                 CREATE TABLE IF NOT EXISTS CachedStateErrors (
//                     RowId INTEGER PRIMARY KEY AUTOINCREMENT,
//                     MachineCode TEXT NOT NULL,
//                     ID INTEGER NOT NULL,
//                     TagTime TEXT NOT NULL,
//                     TagValue INTEGER NOT NULL
//                 );
//
//                 CREATE TABLE IF NOT EXISTS CachedSurveyorData (
//                     RowId INTEGER PRIMARY KEY AUTOINCREMENT,
//                     MachineCode TEXT NOT NULL,
//                     TableName TEXT NOT NULL,
//                     ID INTEGER NOT NULL,
//                     TagTime TEXT NOT NULL,
//                     TagValue REAL NOT NULL
//                 );
//
//                 CREATE TABLE IF NOT EXISTS CachedWarningAlarms (
//                     Id INTEGER PRIMARY KEY AUTOINCREMENT,
//                     MachineCode TEXT NOT NULL,
//                     TagId INTEGER NOT NULL,
//                     IsWarning INTEGER NOT NULL,
//                     IsGreater INTEGER NOT NULL,
//                     IsError INTEGER NOT NULL,
//                     NameOfAlarm TEXT NOT NULL,
//                     WarningDescription TEXT NOT NULL,
//                     DurationInSeconds INTEGER NOT NULL,
//                     NrOfTimesOccured INTEGER NOT NULL,
//                     QueryStartDate TEXT NOT NULL,
//                     QueryEndDate TEXT NOT NULL,
//                     CreatedAt TEXT NOT NULL
//                 );
//
//                 CREATE TABLE IF NOT EXISTS CachedAggregatedSurveyorData (
//                     Id INTEGER PRIMARY KEY AUTOINCREMENT,
//                     MachineCode TEXT NOT NULL,
//                     TagId INTEGER NOT NULL,
//                     LevelOfDetail INTEGER NOT NULL,
//                     BucketStartTime TEXT NOT NULL,
//                     TimeWeightedAvg REAL NOT NULL,
//                     MinValue REAL NOT NULL,
//                     MaxValue REAL NOT NULL,
//                     OriginalPointCount INTEGER NOT NULL,
//                     TotalDurationMs INTEGER NOT NULL,
//                     CachedAt TEXT NOT NULL
//                 );
//
//                 CREATE INDEX IF NOT EXISTS IX_CachedStateErrors_MachineCode_TagTime ON CachedStateErrors (MachineCode, TagTime);
//                 CREATE UNIQUE INDEX IF NOT EXISTS IX_CachedStateErrors_MachineCode_TagId_TagTime ON CachedStateErrors (MachineCode, ID, TagTime);
//                 CREATE INDEX IF NOT EXISTS IX_CachedSurveyorData_MachineCode_TableName_TagTime ON CachedSurveyorData (MachineCode, TableName, TagTime);
//                 CREATE UNIQUE INDEX IF NOT EXISTS IX_CachedSurveyorData_MachineCode_TableName_TagId_TagTime ON CachedSurveyorData (MachineCode, TableName, ID, TagTime);
//                 CREATE INDEX IF NOT EXISTS IX_CachedWarningAlarms_MachineCode_QueryStartDate_QueryEndDate ON CachedWarningAlarms (MachineCode, QueryStartDate, QueryEndDate);
//                 CREATE UNIQUE INDEX IF NOT EXISTS IX_CachedWarningAlarms_MachineCode_TagId_IsWarning_IsGreater_IsError_QueryStartDate_QueryEndDate ON CachedWarningAlarms (MachineCode, TagId, IsWarning, IsGreater, IsError, QueryStartDate, QueryEndDate);
//                 CREATE UNIQUE INDEX IF NOT EXISTS IX_CachedAggregatedSurveyorData_MachineCode_TagId_LOD_BucketStartTime ON CachedAggregatedSurveyorData (MachineCode, TagId, LevelOfDetail, BucketStartTime);
//                 CREATE INDEX IF NOT EXISTS IX_CachedAggregatedSurveyorData_MachineCode_TagId_LOD ON CachedAggregatedSurveyorData (MachineCode, TagId, LevelOfDetail);
//             ");
//
//             logger?.LogInformation("Local cache database schema ensured at {Path}", dbPath);
//         }
//         finally
//         {
//             _schemaSemaphore.Release();
//         }
//     }
// }
