// 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.Interfaces;
using FloraMultiPlatformFrontend.Data.Entities;
using FloraMultiPlatformFrontend.Data.Repositories.Interfaces;
using FloraMultiPlatformFrontend.Presentation.Models;
using FloraMultiPlatformFrontend.Presentation.Models.Charts;
using FloraMultiPlatformFrontend.Presentation.Models.Machines;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace FloraMultiPlatformFrontend.Data.Repositories;

public class ErrorsRepository : IErrorsRepository
{
    private readonly IDbContextFactory<FloraDbContext> _contextFactory;
    private readonly IDbContextFactory<LocalCacheDbContext> _cacheContextFactory;
    private readonly IErrorCachingService _errorCachingService;
    private readonly IRingHistoryService _ringHistoryService;
    private readonly ILogger<ErrorsRepository> _logger;

    public ErrorsRepository(
        IDbContextFactory<FloraDbContext> contextFactory,
        IDbContextFactory<LocalCacheDbContext> cacheContextFactory,
        IErrorCachingService errorCachingService,
        IConfigurationService configurationService,
        IRingHistoryService ringHistoryService,
        ILogger<ErrorsRepository> logger
    )
    {
        _contextFactory = contextFactory ?? throw new ArgumentNullException(nameof(contextFactory));
        _cacheContextFactory = cacheContextFactory ?? throw new ArgumentNullException(nameof(cacheContextFactory));
        _errorCachingService = errorCachingService ?? throw new ArgumentNullException(nameof(errorCachingService));
        _ringHistoryService = ringHistoryService;
        _logger = logger ?? throw new ArgumentNullException(nameof(logger));
    }

    public async Task<IEnumerable<ErrorDatabaseResult>> GetErrorSummariesAsync(
        DateTime startDateTime,
        DateTime endDateTime,
        string serverName,
        string machineCode,
        bool useCache,
        IEnumerable<int>? selectedTagIds,
        CancellationToken cancellationToken = default
    )
    {
        if (!AreDatesValid(startDateTime, endDateTime))
        {
            _logger.LogWarning("Invalid date range provided: {StartDate} to {EndDate}", startDateTime, endDateTime);
            return Enumerable.Empty<ErrorDatabaseResult>();
        }

        try
        {
            if (useCache)
            {
                // Ensure data is cached
                await _errorCachingService.CacheStateErrorsAsync(machineCode, startDateTime, endDateTime, cancellationToken);

                // Query from cache
                using var cacheContext = await _cacheContextFactory.CreateDbContextAsync(cancellationToken);
                var query = cacheContext.CachedStateErrors
                    .Where(e => e.MachineCode == machineCode &&
                               e.TagTime >= startDateTime &&
                               e.TagTime <= endDateTime &&
                               e.TagValue == true);

                if (selectedTagIds != null && selectedTagIds.Any())
                {
                    query = query.Where(e => selectedTagIds.Contains(e.TagId));
                }

                var results = await query
                    .GroupBy(e => e.TagId)
                    .Select(g => new ErrorDatabaseResult
                    {
                        ID = g.Key,
                        NrOfTimesError = g.Count()
                    })
                    .OrderByDescending(r => r.NrOfTimesError)
                    .ToListAsync(cancellationToken);

                return results;
            }
            else
            {
                // Query directly from main database
                var query = BuildErrorSummaryQuery(startDateTime, endDateTime, serverName, machineCode, selectedTagIds);
                using var context = await _contextFactory.CreateDbContextAsync(cancellationToken);
                var results = await context.Database
                    .SqlQueryRaw<ErrorDatabaseResult>(query)
                    .ToListAsync(cancellationToken);

                return results;
            }
        }
        catch (Exception ex)
        {
            _logger.LogError(
                ex,
                "Error fetching error summaries for machine {MachineCode} (UseCache: {UseCache})",
                machineCode,
                useCache
            );
            return Enumerable.Empty<ErrorDatabaseResult>();
        }
    }

    public async Task<IEnumerable<WarningAlarmDetail>> GetErrorDetailsAsync(
        int tagId,
        DateTime startDateTime,
        DateTime endDateTime,
        string serverName,
        string machineCode,
        bool useCache,
        CancellationToken cancellationToken = default
    )
    {
        if (!AreDatesValid(startDateTime, endDateTime))
        {
            _logger.LogWarning("Invalid date range provided: {StartDate} to {EndDate}", startDateTime, endDateTime);
            return Enumerable.Empty<WarningAlarmDetail>();
        }

        try
        {
            var ringIntervals = await _ringHistoryService.GetIntervalsAsync(
                startDateTime,
                endDateTime,
                cancellationToken);

            if (useCache)
            {
                // Ensure data is cached
                await _errorCachingService.CacheStateErrorsAsync(machineCode, startDateTime, endDateTime, cancellationToken);

                // Query from cache and compute details
                using var cacheContext = await _cacheContextFactory.CreateDbContextAsync(cancellationToken);
                var data = await cacheContext.CachedStateErrors
                    .Where(e => e.MachineCode == machineCode &&
                               e.TagId == tagId &&
                               e.TagTime >= startDateTime &&
                               e.TagTime <= endDateTime)
                    .OrderBy(e => e.TagTime)
                    .ToListAsync(cancellationToken);

                var results = new List<WarningAlarmDetail>();
                CachedStateError? previous = null;

                foreach (var current in data)
                {
                    if (previous != null && previous.TagValue == true && !current.TagValue)
                    {
                        results.Add(new WarningAlarmDetail
                        {
                            TagID = tagId,
                            AlarmStart = previous.TagTime,
                            AlarmEnd = current.TagTime,
                            Duration = (int)(current.TagTime - previous.TagTime).TotalSeconds,
                            RingNumber = ringIntervals
                                .LastOrDefault(r =>
                                    r.StartTime <= previous.TagTime &&
                                    (r.EndTime == null || r.EndTime > previous.TagTime))
                                ?.RingNumber
                        });
                    }
                    previous = current;
                }

                return results.OrderBy(r => r.AlarmStart);
            }
            else
            {
                // Query directly from main database
                var query = BuildErrorDetailsQuery(tagId, startDateTime, endDateTime, serverName, machineCode);
                using var context = await _contextFactory.CreateDbContextAsync(cancellationToken);
                var results = await context.Database
                    .SqlQueryRaw<WarningAlarmDetail>(query)
                    .ToListAsync(cancellationToken);

                foreach (var r in results)
                {
                    r.RingNumber = ringIntervals
                        .LastOrDefault(ri =>
                            ri.StartTime <= r.AlarmStart &&
                            (ri.EndTime == null || ri.EndTime > r.AlarmStart))
                        ?.RingNumber;
                }

                return results;
            }
        }
        catch (Exception ex)
        {
            _logger.LogError(
                ex,
                "Error fetching error details for tag {TagId} on machine {MachineCode} (UseCache: {UseCache})",
                tagId,
                machineCode,
                useCache
            );
            return Enumerable.Empty<WarningAlarmDetail>();
        }
    }

    public async Task<IEnumerable<WarningAlarmDetail>> GetErrorDetailsAsync(
        int tagId,
        FilterData filterData,
        string serverName,
        string machineCode,
        bool useCache,
        CancellationToken cancellationToken = default
    )
    {
        var startDateTime = filterData.DateRange.From.Date.Add(filterData.TimeRange.From);
        var endDateTime = filterData.DateRange.To.Date.Add(filterData.TimeRange.To);

        return await GetErrorDetailsAsync(tagId, startDateTime, endDateTime, serverName, machineCode, useCache, cancellationToken);
    }

    public async Task<IEnumerable<CurrentErrorDatabaseResult>> GetCurrentErrorsAsync(
        string serverName,
        string machineCode,
        IEnumerable<int>? selectedTagIds = null,
        CancellationToken cancellationToken = default
    )
    {
        try
        {
            var query = BuildCurrentErrorQuery(serverName, machineCode, selectedTagIds);

            _logger.LogDebug(
                "Executing current error query for machine {MachineCode}",
                machineCode
            );

            using var context = await _contextFactory.CreateDbContextAsync(cancellationToken);
            var result = await context.Database
                .SqlQueryRaw<CurrentErrorDatabaseResult>(query)
                .ToListAsync(cancellationToken);

            return result;
        }
        catch (Exception ex)
        {
            _logger.LogError(
                ex,
                "Error fetching current errors for machine {MachineCode}",
                machineCode
            );
            return Enumerable.Empty<CurrentErrorDatabaseResult>();
        }
    }

    private string BuildErrorSummaryQuery(DateTime startDateTime, DateTime endDateTime,
        string serverName, string machineCode, IEnumerable<int>? selectedTagIds)
    {
        var start = startDateTime.ToString("yyyy-MM-dd HH:mm:ss");
        var end = endDateTime.ToString("yyyy-MM-dd HH:mm:ss");

        string idFilterClause = "";
        if (selectedTagIds != null && selectedTagIds.Any())
        {
            var idList = string.Join(",", selectedTagIds.Select(id => id.ToString()));
            idFilterClause = $"AND ID IN ({idList})";
        }

        return $@"
            SELECT
                ID,
                COUNT(ID) AS NrOfTimesError
            FROM
                dbo.{serverName}_{machineCode}_StateData_Errors
            WHERE
                tagValue = 1
                AND tagTime BETWEEN '{start}' AND '{end}'
                {idFilterClause}
            GROUP BY
                ID
            ORDER BY NrOfTimesError DESC";
    }

    private string BuildErrorDetailsQuery(int tagId, DateTime startDateTime, DateTime endDateTime,
        string serverName, string machineCode)
    {
        var start = startDateTime.ToString("yyyy-MM-dd HH:mm:ss");
        var end = endDateTime.ToString("yyyy-MM-dd HH:mm:ss");

        return $@"
            WITH AllEvents AS (
                SELECT
                    ID,
                    tagTime,
                    tagValue,
                    LEAD(tagTime) OVER (PARTITION BY ID ORDER BY tagTime) as NextTagTime,
                    LEAD(tagValue) OVER (PARTITION BY ID ORDER BY tagTime) as NextTagValue
                FROM
                    dbo.{serverName}_{machineCode}_StateData_Errors
                WHERE
                    ID = {tagId}
                    AND tagTime BETWEEN '{start}' AND '{end}'
            )
            SELECT
                ID as TagID,
                tagTime as AlarmStart,
                NextTagTime as AlarmEnd,
                DATEDIFF(SECOND, tagTime, IsNull(NextTagTime,GETDATE())) as Duration
            FROM
                AllEvents
            WHERE
                tagValue = 1
                AND (NextTagValue = 0 OR NextTagValue is null)
            ORDER BY
                tagTime";
    }

    private string BuildCurrentErrorQuery(string serverName, string machineCode, IEnumerable<int>? selectedTagIds)
    {
        string idFilterClause = "";
        if (selectedTagIds != null && selectedTagIds.Any())
        {
            var idList = string.Join(",", selectedTagIds.Select(id => id.ToString()));
            idFilterClause = $"AND ID IN ({idList})";
        }

        return $@"
            SELECT
                ID,
                tagTime,
                tagValue
            FROM
                dbo.{serverName}_{machineCode}_Errors
            WHERE
                tagValue = 1
                {idFilterClause}
        ";
    }


    private static readonly DateTime SqlMinDate = new DateTime(1900, 1, 1);
    private static readonly DateTime SqlMaxDate = DateTime.Now.AddHours(1);

    public async Task<IEnumerable<ChartDataPoint>> GetErrorTagHistoryAsync(
        int tagId,
        DateTime startDateTime,
        DateTime endDateTime,
        string serverName,
        string machineCode,
        CancellationToken cancellationToken = default
    )
    {
        if (!AreDatesValid(startDateTime, endDateTime))
        {
            _logger.LogWarning("Invalid date range provided: {StartDate} to {EndDate}", startDateTime, endDateTime);
            return Enumerable.Empty<ChartDataPoint>();
        }

        try
        {
            var query = BuildErrorTagHistoryQuery(tagId, startDateTime, endDateTime, serverName, machineCode);

            _logger.LogDebug(
                "Executing error tag history query for tag {TagId} on machine {MachineCode}",
                tagId,
                machineCode
            );

            using var context = await _contextFactory.CreateDbContextAsync(cancellationToken);
            var result = await context.Database
                .SqlQueryRaw<ErrorTagHistoryResult>(query)
                .ToListAsync(cancellationToken);

            return result.Select(r => new ChartDataPoint
            {
                Timestamp = r.TagTime,
                Value = r.TagValue ? 1.0 : 0.0
            });
        }
        catch (Exception ex)
        {
            _logger.LogError(
                ex,
                "Error fetching error tag history for tag {TagId} on machine {MachineCode}",
                tagId,
                machineCode
            );
            return Enumerable.Empty<ChartDataPoint>();
        }
    }

    private string BuildErrorTagHistoryQuery(int tagId, DateTime startDateTime, DateTime endDateTime,
        string serverName, string machineCode)
    {
        var start = startDateTime.ToString("yyyy-MM-dd HH:mm:ss");
        var end = endDateTime.ToString("yyyy-MM-dd HH:mm:ss");

        return $@"
            SELECT
                tagTime,
                tagValue
            FROM
                dbo.{serverName}_{machineCode}_StateData_Errors
            WHERE
                ID = {tagId}
                AND tagTime BETWEEN '{start}' AND '{end}'
            ORDER BY
                tagTime";
    }

    private bool AreDatesValid(DateTime start, DateTime end)
    {
        return start >= SqlMinDate && end >= SqlMinDate
            && start <= SqlMaxDate && end <= SqlMaxDate
            && start <= end;
    }

}

// Helper class for SQL query result mapping
public class ErrorTagHistoryResult
{
    public DateTime TagTime { get; set; }
    public bool TagValue { get; set; }
}
