// 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; } = "";
    }
}
