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

namespace FloraMultiPlatformFrontend.Data.Repositories;

/// <summary>
/// Repository for accessing historical IO tag data from date-partitioned StateData tables.
/// Queries tables with pattern: {serverName}_{machineCode}_IOStateData_TBM_{yyMMdd}
/// </summary>
public class IoHistoryTagDataRepository : IIoHistoryTagDataRepository
{
    private readonly IDatabaseService _databaseService;
    private readonly IConfigurationService _configurationService;
    private readonly ILogger<IoHistoryTagDataRepository> _logger;

    public IoHistoryTagDataRepository(
        IDatabaseService databaseService,
        IConfigurationService configurationService,
        ILogger<IoHistoryTagDataRepository> logger)
    {
        _databaseService = databaseService ?? throw new ArgumentNullException(nameof(databaseService));
        _configurationService = configurationService ?? throw new ArgumentNullException(nameof(configurationService));
        _logger = logger ?? throw new ArgumentNullException(nameof(logger));
    }

    public async Task<IEnumerable<ChartDataPoint>> GetIoTagHistoryAsync(
        int tagId,
        DateTime startDate,
        DateTime endDate,
        string machineCode,
        CancellationToken cancellationToken = default)
    {
        try
        {
            _logger.LogDebug(
                "Getting IO tag history for TagId {TagId} from {StartDate} to {EndDate} for machine {MachineCode}",
                tagId, startDate, endDate, machineCode);

            // Use the multi-tag method and extract single tag data
            var multipleTagsData = await GetMultipleIoTagsHistoryAsync(
                new[] { tagId },
                startDate,
                endDate,
                machineCode,
                cancellationToken
            );

            if (multipleTagsData.TryGetValue(tagId, out var tagData))
            {
                return tagData;
            }

            _logger.LogWarning("No history data found for TagId {TagId}", tagId);
            return Enumerable.Empty<ChartDataPoint>();
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error getting IO tag history for TagId {TagId}", tagId);
            return Enumerable.Empty<ChartDataPoint>();
        }
    }

    public async Task<IDictionary<int, IEnumerable<ChartDataPoint>>> GetMultipleIoTagsHistoryAsync(
        IEnumerable<int> tagIds,
        DateTime startDate,
        DateTime endDate,
        string machineCode,
        CancellationToken cancellationToken = default)
    {
        try
        {
            var tagIdList = tagIds.ToList();

            if (!tagIdList.Any())
            {
                _logger.LogWarning("No tag IDs provided for IO history query");
                return new Dictionary<int, IEnumerable<ChartDataPoint>>();
            }

            _logger.LogInformation(
                "Getting IO history for {TagCount} tags from {StartDate} to {EndDate} for machine {MachineCode}",
                tagIdList.Count, startDate, endDate, machineCode);

            // Build union query for IO history tables
            var unionQuery = await BuildUnionQueryForIoHistoryTablesAsync(
                tagIdList,
                machineCode,
                startDate,
                endDate
            );

            if (string.IsNullOrEmpty(unionQuery))
            {
                _logger.LogWarning(
                    "Empty union query for tags {TagIds} - no historical tables found",
                    string.Join(",", tagIdList));
                return tagIdList.ToDictionary(
                    tagId => tagId,
                    _ => Enumerable.Empty<ChartDataPoint>());
            }

            // Execute query
            var fullQuery = $@"
                SELECT ID, tagTime, tagValue
                FROM ({unionQuery}) AS CombinedData
                ORDER BY tagTime";

            _logger.LogDebug("Executing IO history query: {Query}", fullQuery);

            var allData = await _databaseService.QueryAsync<RawIoHistoryDataDto>(fullQuery);

            if (allData == null || !allData.Any())
            {
                _logger.LogInformation("No IO history data returned for tags {TagIds}", string.Join(",", tagIdList));
                return tagIdList.ToDictionary(
                    tagId => tagId,
                    _ => Enumerable.Empty<ChartDataPoint>());
            }

            // Group by TagId and convert to ChartDataPoint
            var result = allData
                .GroupBy(x => x.ID)
                .ToDictionary(
                    g => g.Key,
                    g => g.Select(x => new ChartDataPoint
                    {
                        Timestamp = x.tagTime,
                        Value = x.tagValue ? 1.0 : 0.0  // Convert bool to double (1.0 = ON, 0.0 = OFF)
                    }).AsEnumerable()
                );

            // Ensure all requested tags are in dictionary (even if no data)
            foreach (var tagId in tagIdList)
            {
                if (!result.ContainsKey(tagId))
                {
                    result[tagId] = Enumerable.Empty<ChartDataPoint>();
                    _logger.LogDebug("No data found for TagId {TagId}", tagId);
                }
                else
                {
                    _logger.LogDebug("Found {Count} data points for TagId {TagId}", result[tagId].Count(), tagId);
                }
            }

            return result;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error getting multiple IO tags history for machine {MachineCode}", machineCode);
            return new Dictionary<int, IEnumerable<ChartDataPoint>>();
        }
    }

    /// <summary>
    /// Builds a UNION ALL query combining IO history data from multiple date-partitioned tables.
    /// Checks for table existence before including in the union.
    /// </summary>
    private async Task<string> BuildUnionQueryForIoHistoryTablesAsync(
        IEnumerable<int> tagIds,
        string machineCode,
        DateTime startDate,
        DateTime endDate)
    {
        try
        {
            var serverName = await _configurationService.GetMachineServerNameAsync();
            var tagConfig = await _configurationService.GetTagConfigurationAsync();
            var ioTablePrefix = "TBM";

            var tagIdList = tagIds.ToList();
            var tagIdFilter = string.Join(",", tagIdList);
            var unionParts = new List<string>();

            // Generate date list for the date range
            var dateList = new List<string>();
            for (var dt = startDate.Date; dt <= endDate.Date; dt = dt.AddDays(1))
            {
                dateList.Add(dt.ToString("yyMMdd"));  // Format: yyMMdd (e.g., "251124")
            }

            _logger.LogDebug(
                "Building IO history query for {DateCount} days from {StartDate} to {EndDate}",
                dateList.Count, startDate, endDate);

            // Get existing tables from information_schema
            var tablePattern = $"{serverName}_{machineCode}_%";
            var existingTablesQuery = "SELECT table_name FROM information_schema.tables WHERE table_name LIKE {0}";
            var existingTables = await _databaseService.QueryAsync<TableName>(existingTablesQuery, tablePattern);
            var existingTableNames = new HashSet<string>(
                existingTables.Select(t => t.table_name),
                StringComparer.OrdinalIgnoreCase);

            _logger.LogDebug(
                "Found {Count} existing tables matching pattern {Pattern}",
                existingTableNames.Count, tablePattern);

            // Build union for each date
            // IO history is stored in StateData tables (for boolean IO values)
            foreach (var dateSuffix in dateList)
            {
                // Check StateData table pattern: {serverName}_{machineCode}_IOStateData_TBM_{dateSuffix}
                var ioHistoryTable = $"{serverName}_{machineCode}_IOStateData_{ioTablePrefix}_{dateSuffix}";

                if (existingTableNames.Contains(ioHistoryTable))
                {
                    unionParts.Add($@"
                    SELECT ID, tagTime, tagValue
                    FROM [{ioHistoryTable}]
                    WHERE ID IN ({tagIdFilter})
                    AND tagTime >= '{startDate:yyyy-MM-dd HH:mm:ss}'
                    AND tagTime <= '{endDate:yyyy-MM-dd HH:mm:ss}'");

                    _logger.LogDebug("Added table {TableName} to union query", ioHistoryTable);
                }
                else
                {
                    _logger.LogDebug("Table {TableName} does not exist, skipping", ioHistoryTable);
                }
            }

            if (!unionParts.Any())
            {
                _logger.LogWarning(
                    "No valid IO history tables found for date range {StartDate} to {EndDate} with pattern {Pattern}",
                    startDate, endDate, $"{serverName}_{machineCode}_IOStateData_{ioTablePrefix}_*");
                return string.Empty;
            }

            _logger.LogInformation("Built union query with {Count} table parts", unionParts.Count);
            return string.Join(" UNION ALL ", unionParts);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error building union query for IO history tables");
            return string.Empty;
        }
    }

    /// <summary>
    /// DTO for raw IO history data from database.
    /// </summary>
    private class RawIoHistoryDataDto
    {
        public int ID { get; set; }
        public DateTime tagTime { get; set; }
        public bool tagValue { get; set; }
    }

    /// <summary>
    /// DTO for table name queries.
    /// </summary>
    private class TableName
    {
        public string table_name { get; set; } = "";
    }
}
