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