/*
 * Copyright (c) 2026 Erik Darling, Darling Data LLC
 *
 * This file is part of the SQL Server Performance Monitor Lite.
 *
 * Licensed under the MIT License. See LICENSE file in the project root for full license information.
 */

using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using System.Windows.Media;
using DuckDB.NET.Data;

namespace PerformanceMonitorLite.Services;

public partial class LocalDataService
{
    /// <summary>
    /// Gets a summary of server health for the overview dashboard.
    /// </summary>
    public async Task<ServerSummaryItem?> GetServerSummaryAsync(int serverId, string displayName)
    {
        using var connection = await OpenConnectionAsync();

        double? cpuPercent = null;
        double? otherProcessCpuPercent = null;
        double? memoryMb = null;
        int blockingCount = 0;
        int deadlockCount = 0;
        DateTime? lastCollection = null;

        /* Latest CPU — read both SQL Server CPU and other-process CPU so the UI can surface
           total non-idle CPU alongside the SQL-only number. */
        using (var cmd = connection.CreateCommand())
        {
            cmd.CommandText = @"
SELECT sqlserver_cpu_utilization, other_process_cpu_utilization, sample_time
FROM v_cpu_utilization_stats
WHERE server_id = $1
ORDER BY sample_time DESC
LIMIT 1";
            cmd.Parameters.Add(new DuckDBParameter { Value = serverId });
            using var reader = await cmd.ExecuteReaderAsync();
            if (await reader.ReadAsync())
            {
                cpuPercent = reader.IsDBNull(0) ? null : ToDouble(reader.GetValue(0));
                otherProcessCpuPercent = reader.IsDBNull(1) ? null : ToDouble(reader.GetValue(1));
                lastCollection = reader.IsDBNull(2) ? null : reader.GetDateTime(2);
            }
        }

        /* Latest SQL Memory */
        using (var cmd = connection.CreateCommand())
        {
            cmd.CommandText = @"
SELECT total_server_memory_mb
FROM v_memory_stats
WHERE server_id = $1
ORDER BY collection_time DESC
LIMIT 1";
            cmd.Parameters.Add(new DuckDBParameter { Value = serverId });
            using var reader = await cmd.ExecuteReaderAsync();
            if (await reader.ReadAsync())
            {
                memoryMb = reader.IsDBNull(0) ? null : ToDouble(reader.GetValue(0));
            }
        }

        /* Blocking count in last hour - uses XE blocked process reports */
        using (var cmd = connection.CreateCommand())
        {
            /* Prefer the blocked-process-report; fall back to the always-on DMV snapshot (AWS RDS). */
            cmd.CommandText = @"
SELECT COALESCE(NULLIF(
    (SELECT COUNT(*) FROM v_blocked_process_reports WHERE server_id = $1 AND event_time >= $2), 0),
    (SELECT COUNT(*) FROM v_dmv_blocking_snapshots WHERE server_id = $1 AND event_time >= $2))";
            cmd.Parameters.Add(new DuckDBParameter { Value = serverId });
            cmd.Parameters.Add(new DuckDBParameter { Value = DateTime.UtcNow.AddHours(-1) });
            var result = await cmd.ExecuteScalarAsync();
            blockingCount = result != null ? Convert.ToInt32(result) : 0;
        }

        /* Deadlock count in last hour */
        using (var cmd = connection.CreateCommand())
        {
            cmd.CommandText = @"
SELECT COUNT(*)
FROM v_deadlocks
WHERE server_id = $1
AND   deadlock_time >= $2";
            cmd.Parameters.Add(new DuckDBParameter { Value = serverId });
            cmd.Parameters.Add(new DuckDBParameter { Value = DateTime.UtcNow.AddHours(-1) });
            var result = await cmd.ExecuteScalarAsync();
            deadlockCount = result != null ? Convert.ToInt32(result) : 0;
        }

        /* Last collection time from collection_log */
        using (var cmd = connection.CreateCommand())
        {
            cmd.CommandText = @"
SELECT MAX(collection_time)
FROM v_collection_log
WHERE server_id = $1";
            cmd.Parameters.Add(new DuckDBParameter { Value = serverId });
            var result = await cmd.ExecuteScalarAsync();
            if (result != null && result != DBNull.Value)
            {
                lastCollection = Convert.ToDateTime(result);
            }
        }

        return new ServerSummaryItem
        {
            DisplayName = displayName,
            ServerId = serverId,
            CpuPercent = cpuPercent,
            OtherProcessCpuPercent = otherProcessCpuPercent,
            MemoryMb = memoryMb,
            BlockingCount = blockingCount,
            DeadlockCount = deadlockCount,
            LastCollectionTime = lastCollection
        };
    }
}

public class ServerSummaryItem
{
    public string DisplayName { get; set; } = "";
    public string ServerName { get; set; } = "";
    public int ServerId { get; set; }
    public bool? IsOnline { get; set; }
    /// <summary>True when the server is reachable but one or more collectors have consecutive errors.</summary>
    public bool HasCollectorErrors { get; set; }
    /// <summary>SQL Server scheduler ProcessUtilization from sys.dm_os_ring_buffers. NULL on Azure SQL DB.</summary>
    public double? CpuPercent { get; set; }
    /// <summary>Non-SQL-Server CPU on the host (computed as 100 - SystemIdle - ProcessUtilization). NULL on Azure SQL DB.</summary>
    public double? OtherProcessCpuPercent { get; set; }
    /// <summary>Total non-idle CPU on the host = sql_server + other_process. Tracks closer to OS user+system counters.</summary>
    public double? TotalCpuPercent =>
        CpuPercent.HasValue ? CpuPercent.Value + (OtherProcessCpuPercent ?? 0) : null;
    /// <summary>The CPU value the alert evaluator and headline display use. Driven by App.AlertCpuMode.</summary>
    public double? CpuPercentForAlert =>
        App.AlertCpuMode == CpuAlertMode.Total ? (TotalCpuPercent ?? CpuPercent) : CpuPercent;
    public double? MemoryMb { get; set; }
    public int BlockingCount { get; set; }
    public int DeadlockCount { get; set; }
    public DateTime? LastCollectionTime { get; set; }

    /// <summary>
    /// Headline CPU display. Shows total non-idle CPU prominently with the SQL-only number alongside,
    /// e.g. "64% (SQL 60%)". Falls back to a single number when only one value is available.
    /// </summary>
    public string CpuDisplay
    {
        get
        {
            if (!CpuPercent.HasValue) return "--";
            if (!OtherProcessCpuPercent.HasValue) return $"{CpuPercent:F0}%";
            return $"{TotalCpuPercent:F0}% (SQL {CpuPercent:F0}%)";
        }
    }
    public string MemoryDisplay => MemoryMb.HasValue ? $"{MemoryMb / 1024.0:F1} GB" : "--";
    public string BlockingDisplay => BlockingCount > 0 ? BlockingCount.ToString() : "0";
    public string DeadlockDisplay => DeadlockCount > 0 ? DeadlockCount.ToString() : "0";
    public string LastCollectionDisplay => LastCollectionTime.HasValue ? ServerTimeHelper.FormatServerTime(LastCollectionTime, "HH:mm:ss") : "Never";

    /* Connection status */
    public string StatusDisplay => IsOnline switch
    {
        true when HasCollectorErrors => "Warning",
        true => "Online",
        false => "Offline",
        _ => "Unknown"
    };
    public SolidColorBrush StatusBrush => MakeBrush(IsOnline switch
    {
        true when HasCollectorErrors => "#FFD54F",  // amber — connected but collectors failing
        true => "#81C784",
        false => "#E57373",
        _ => "#888888"
    });
    public bool IsOffline => IsOnline == false;

    /* Color coding */
    public SolidColorBrush CpuBrush
    {
        get
        {
            var v = CpuPercentForAlert;
            return MakeBrush(v >= 80 ? "#E57373" : v >= 50 ? "#FFB74D" : "#81C784");
        }
    }
    public SolidColorBrush BlockingBrush => MakeBrush(BlockingCount > 0 ? "#FFB74D" : "#81C784");
    public SolidColorBrush DeadlockBrush => MakeBrush(DeadlockCount > 0 ? "#E57373" : "#81C784");
    public SolidColorBrush CardBorderBrush => MakeBrush(
        IsOnline == false ? "#E57373" :
        DeadlockCount > 0 ? "#E57373" :
        BlockingCount > 0 ? "#FFB74D" :
        CpuPercentForAlert >= 80 ? "#FFB74D" :
        HasCollectorErrors ? "#FFD54F" :   // amber border when collectors are failing
        "#2a2d35");

    private static SolidColorBrush MakeBrush(string hex)
    {
        var color = (Color)ColorConverter.ConvertFromString(hex);
        var brush = new SolidColorBrush(color);
        brush.Freeze();
        return brush;
    }
    public bool HasAlerts => BlockingCount > 0 || DeadlockCount > 0;
}
