/*
 * 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.Linq;
using System.Xml.Linq;
using System.Net;
using System.Threading;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Input;
using System.Windows.Threading;
using PerformanceMonitorLite.Controls;
using PerformanceMonitorLite.Database;
using PerformanceMonitorLite.Mcp;
using PerformanceMonitorLite.Models;
using PerformanceMonitorLite.Services;
using PerformanceMonitorLite.Windows;

namespace PerformanceMonitorLite;

public partial class MainWindow : Window
{
    private readonly DuckDbInitializer _databaseInitializer;
    private readonly ServerManager _serverManager;
    private readonly ScheduleManager _scheduleManager;
    private RemoteCollectorService? _collectorService;
    private CollectionBackgroundService? _backgroundService;
    private CancellationTokenSource? _backgroundCts;
    private SystemTrayService? _trayService;
    private readonly Dictionary<string, TabItem> _openServerTabs = new();
    private readonly Dictionary<string, (Action<int, int, DateTime?> AlertCounts, Action<int> ApplyTimeRange, Func<Task> ManualRefresh)> _tabEventHandlers = new();
    private readonly Dictionary<string, bool> _previousConnectionStates = new();
    private readonly Dictionary<string, bool> _previousCollectorErrorStates = new();
    private readonly Dictionary<string, DateTime> _lastCpuAlert = new();
    private readonly Dictionary<string, DateTime> _lastBlockingAlert = new();
    private readonly Dictionary<string, DateTime> _lastDeadlockAlert = new();
    private readonly Dictionary<string, DateTime> _lastPoisonWaitAlert = new();
    private readonly Dictionary<string, DateTime> _lastLongRunningQueryAlert = new();
    private readonly Dictionary<string, DateTime> _lastTempDbSpaceAlert = new();
    private readonly Dictionary<string, DateTime> _lastLongRunningJobAlert = new();
    private readonly DispatcherTimer _statusTimer;
    private LocalDataService? _dataService;
    private McpHostService? _mcpService;
    private readonly AlertStateService _alertStateService = new();
    private readonly MuteRuleService _muteRuleService;
    private EmailAlertService _emailAlertService;

    /* Track active alert states for resolved notifications */
    private readonly Dictionary<string, bool> _activeCpuAlert = new();
    private readonly Dictionary<string, bool> _activeBlockingAlert = new();
    private readonly Dictionary<string, bool> _activeDeadlockAlert = new();
    private readonly Dictionary<string, bool> _activePoisonWaitAlert = new();
    private readonly Dictionary<string, bool> _activeLongRunningQueryAlert = new();
    private readonly Dictionary<string, bool> _activeTempDbSpaceAlert = new();
    private readonly Dictionary<string, bool> _activeLongRunningJobAlert = new();

    public MainWindow()
    {
        InitializeComponent();

        // Initialize services (with loggers wired to AppLogger)
        _databaseInitializer = new DuckDbInitializer(App.DatabasePath, new AppLoggerAdapter<DuckDbInitializer>());
        _emailAlertService = new EmailAlertService(_databaseInitializer);
        _muteRuleService = new MuteRuleService(_databaseInitializer);
        _serverManager = new ServerManager(App.SharedConfigDirectory, logger: new AppLoggerAdapter<ServerManager>());
        _scheduleManager = new ScheduleManager(App.ConfigDirectory);

        // Status bar update timer
        _statusTimer = new DispatcherTimer { Interval = TimeSpan.FromSeconds(30) };
        _statusTimer.Tick += async (s, e) =>
        {
            UpdateStatusBar();
            await RefreshOverviewAsync();
            CheckConnectionsAndNotify();

            /* Auto-refresh alert history if the tab is active */
            if (ServerTabControl.SelectedItem == AlertsTab)
                AlertsHistoryContent.RefreshAlerts();
        };

        // Initialize database and UI
        Loaded += MainWindow_Loaded;
        Closing += MainWindow_Closing;
        ServerTabControl.SelectionChanged += ServerTabControl_SelectionChanged;
    }

    private async void MainWindow_Loaded(object sender, RoutedEventArgs e)
    {
        try
        {
            StatusText.Text = "Initializing database...";

            // Initialize the DuckDB database
            await _databaseInitializer.InitializeAsync();

            // Initialize the collection engine (with loggers wired to AppLogger)
            _collectorService = new RemoteCollectorService(
                _databaseInitializer,
                _serverManager,
                _scheduleManager,
                new AppLoggerAdapter<RemoteCollectorService>());

            var archiveService = new ArchiveService(_databaseInitializer, App.ArchiveDirectory, new AppLoggerAdapter<ArchiveService>());
            var retentionService = new RetentionService(App.ArchiveDirectory, new AppLoggerAdapter<RetentionService>());

            _backgroundService = new CollectionBackgroundService(
                _collectorService, _databaseInitializer, archiveService, retentionService, _serverManager,
                new AppLoggerAdapter<CollectionBackgroundService>());

            // Start background collection
            _backgroundCts = new CancellationTokenSource();
            _ = _backgroundService.StartAsync(_backgroundCts.Token);

            // Initialize system tray
            _trayService = new SystemTrayService(this, _backgroundService);
            _trayService.Initialize();

            // Initialize data service for overview
            _dataService = new LocalDataService(_databaseInitializer);

            // Load mute rules from database
            await _muteRuleService.LoadAsync();

            // Initialize alerts history tab
            AlertsHistoryContent.Initialize(_dataService);
            AlertsHistoryContent.MuteRuleService = _muteRuleService;

            // Initialize FinOps tab
            FinOpsContent.Initialize(_dataService, _serverManager);

            // Start MCP server if enabled
            await StartMcpServerAsync();

            // Load servers
            RefreshServerList();

            // Update status
            UpdateStatusBar();
            _statusTimer.Start();

            await RefreshOverviewAsync();
            StatusText.Text = "Ready - Collection active";

            _ = CheckForUpdatesOnStartupAsync();
        }
        catch (Exception ex)
        {
            StatusText.Text = $"Error: {ex.Message}";
            MessageBox.Show(
                $"Failed to initialize the application:\n\n{ex.Message}",
                "Initialization Error",
                MessageBoxButton.OK,
                MessageBoxImage.Error);
        }
    }

    private async Task CheckForUpdatesOnStartupAsync()
    {
        try
        {
            await Task.Delay(5000); // Don't slow down startup

            if (!App.CheckForUpdatesOnStartup) return;

            // Try Velopack first (supports download + apply)
            try
            {
                var mgr = new Velopack.UpdateManager(
                    new Velopack.Sources.GithubSource(
                        "https://github.com/erikdarlingdata/PerformanceMonitor", null, false));

                var newVersion = await mgr.CheckForUpdatesAsync();
                if (newVersion != null)
                {
                    Dispatcher.Invoke(() =>
                    {
                        Title = $"Performance Monitor Lite — Update v{newVersion.TargetFullRelease.Version} available (Help > About)";
                    });
                    return;
                }
            }
            catch
            {
                // Velopack packages may not exist yet — fall through
            }

            // Fallback: GitHub Releases API check
            var result = await UpdateCheckService.CheckForUpdateAsync();
            if (result?.IsUpdateAvailable == true)
            {
                Dispatcher.Invoke(() =>
                {
                    Title = $"Performance Monitor Lite — Update {result.LatestVersion} available (Help > About)";
                });
            }
        }
        catch
        {
            // Never crash on update check failure
        }
    }

    private async void MainWindow_Closing(object? sender, System.ComponentModel.CancelEventArgs e)
    {
        // Dispose system tray
        _trayService?.Dispose();

        // Stop background collection with timeout
        _backgroundCts?.Cancel();

        await StopMcpServerAsync();

        if (_backgroundService != null)
        {
            try
            {
                using var shutdownCts = new CancellationTokenSource(TimeSpan.FromSeconds(5));
                await _backgroundService.StopAsync(shutdownCts.Token);
            }
            catch (OperationCanceledException)
            {
                /* Shutdown timed out, proceeding anyway */
            }
        }

        // Stop all server tab refresh timers
        foreach (var tab in _openServerTabs.Values)
        {
            if (tab.Content is ServerTab serverTab)
            {
                serverTab.StopRefresh();
            }
        }

        _statusTimer.Stop();
    }

    private void ServerTabControl_SelectionChanged(object sender, SelectionChangedEventArgs e)
    {
        // Only respond to tab selection changes, not child control selection events that bubble up
        if (e.OriginalSource != ServerTabControl) return;

        /* Restore the selected tab's UTC offset so charts use the correct server timezone */
        if (ServerTabControl.SelectedItem is TabItem { Content: ServerTab serverTab })
        {
            ServerTimeHelper.UtcOffsetMinutes = serverTab.UtcOffsetMinutes;
            StatusText.Text = $"Connected to {serverTab.Server.DisplayNameWithIntent}";
        }

        /* Refresh alerts tab when selected */
        if (ServerTabControl.SelectedItem == AlertsTab)
        {
            AlertsHistoryContent.RefreshAlerts();
        }

        UpdateCollectorHealth();
    }

    private async Task StartMcpServerAsync()
    {
        var mcpSettings = McpSettings.Load(App.ConfigDirectory);
        if (!mcpSettings.Enabled) return;

        try
        {
            bool portInUse = await PortUtilityService.IsTcpPortListeningAsync(mcpSettings.Port, IPAddress.Loopback);
            if (portInUse)
            {
                AppLogger.Error("MCP", $"Port {mcpSettings.Port} is already in use — MCP server not started");
                return;
            }

            _mcpService = new McpHostService(_dataService!, _serverManager, _muteRuleService, _databaseInitializer, mcpSettings.Port);
            _ = _mcpService.StartAsync(_backgroundCts!.Token);
        }
        catch (Exception ex)
        {
            AppLogger.Error("MCP", $"Failed to start MCP server: {ex.Message}");
        }
    }

    private async Task StopMcpServerAsync()
    {
        if (_mcpService != null)
        {
            try
            {
                using var shutdownCts = new CancellationTokenSource(TimeSpan.FromSeconds(5));
                await _mcpService.StopAsync(shutdownCts.Token);
            }
            catch (OperationCanceledException)
            {
                /* MCP shutdown timed out */
            }
            _mcpService = null;
        }
    }

    private void RefreshServerList()
    {
        var servers = _serverManager.GetAllServers();
        foreach (var server in servers)
        {
            server.IsOnline = _serverManager.GetConnectionStatus(server.Id).IsOnline;
            server.HasCollectorErrors = _collectorService != null
                && server.IsOnline == true
                && _collectorService.GetHealthSummary(server).ErroringCollectors > 0;
        }
        ServerListView.ItemsSource = servers;

        // Update UI based on server count
        if (servers.Count == 0 && _openServerTabs.Count == 0)
        {
            EmptyStatePanel.Visibility = Visibility.Visible;
            ServerTabControl.Visibility = Visibility.Collapsed;
        }
        else
        {
            EmptyStatePanel.Visibility = Visibility.Collapsed;
            ServerTabControl.Visibility = Visibility.Visible;
        }

        ServerCountText.Text = $"Servers: {servers.Count}";

        // Refresh FinOps server dropdown when server list changes
        FinOpsContent.RefreshServerList();

        // Refresh overview when server list changes
        _ = RefreshOverviewAsync();
    }

    private void UpdateStatusBar()
    {
        // Update database size
        var fileSizeMb = _databaseInitializer.GetDatabaseSizeMb();
        var usedSizeMb = _databaseInitializer.GetUsedDataSizeMb();
        if (fileSizeMb > 0)
        {
            DatabaseSizeText.Text = usedSizeMb.HasValue
                ? $"Database: {usedSizeMb.Value:F1} / {fileSizeMb:F1} MB"
                : $"Database: {fileSizeMb:F1} MB";
        }
        else
        {
            DatabaseSizeText.Text = "Database: New";
        }

        // Update collection status
        if (_backgroundService != null)
        {
            if (_backgroundService.IsCollecting)
            {
                CollectionStatusText.Text = "Collection: Running";
            }
            else if (_backgroundService.IsPaused)
            {
                CollectionStatusText.Text = "Collection: Paused";
            }
            else if (_backgroundService.LastCollectionTime.HasValue)
            {
                var ago = DateTime.UtcNow - _backgroundService.LastCollectionTime.Value;
                CollectionStatusText.Text = $"Collection: {ago.TotalSeconds:F0}s ago";
            }
            else
            {
                CollectionStatusText.Text = "Collection: Starting...";
            }
        }
        else
        {
            CollectionStatusText.Text = "Collection: Stopped";
        }

        // Update collector health
        UpdateCollectorHealth();
    }

    private void UpdateCollectorHealth()
    {
        if (_collectorService == null)
        {
            CollectorHealthText.Text = "";
            return;
        }

        int? selectedServerId = null;
        if (ServerTabControl.SelectedItem is TabItem { Content: ServerTab serverTab })
        {
            selectedServerId = serverTab.ServerId;
        }

        var health = _collectorService.GetHealthSummary(selectedServerId);

        if (health.TotalCollectors == 0)
        {
            CollectorHealthText.Text = "";
            return;
        }

        if (health.LoggingFailures > 0)
        {
            CollectorHealthText.Text = $"Logging: BROKEN ({health.LoggingFailures} failures)";
            CollectorHealthText.Foreground = System.Windows.Media.Brushes.Red;
            CollectorHealthText.ToolTip = $"collection_log INSERT is failing.\nThis means collector errors are invisible.\nCheck the log file for details.";
        }
        else if (health.ErroringCollectors > 0)
        {
            var names = string.Join(", ", health.Errors.Select(e => e.CollectorName));
            CollectorHealthText.Text = $"Collectors: {health.ErroringCollectors} erroring";
            CollectorHealthText.Foreground = System.Windows.Media.Brushes.OrangeRed;
            CollectorHealthText.ToolTip = $"Failing: {names}\n\n" +
                string.Join("\n", health.Errors.Select(e =>
                    $"{e.CollectorName}: {e.ConsecutiveErrors}x consecutive - {e.LastErrorMessage}"));
        }
        else
        {
            CollectorHealthText.Text = $"Collectors: {health.TotalCollectors} OK";
            CollectorHealthText.Foreground = (System.Windows.Media.Brush)FindResource("ForegroundBrush");
            CollectorHealthText.ToolTip = null;
        }
    }

    private async Task RefreshOverviewAsync()
    {
        if (_dataService == null) return;

        var servers = _serverManager.GetAllServers();
        if (servers.Count == 0) return;

        try
        {
            var summaries = new List<ServerSummaryItem>();
            foreach (var server in servers)
            {
                try
                {
                    var serverId = RemoteCollectorService.GetDeterministicHashCode(RemoteCollectorService.GetServerNameForStorage(server));
                    var summary = await _dataService.GetServerSummaryAsync(serverId, server.DisplayNameWithIntent);
                    if (summary != null)
                    {
                        summary.ServerName = server.ServerName;
                        var connStatus = _serverManager.GetConnectionStatus(server.Id);
                        summary.IsOnline = connStatus.IsOnline;
                        if (_collectorService != null && connStatus.IsOnline == true)
                            summary.HasCollectorErrors = _collectorService.GetHealthSummary(server).ErroringCollectors > 0;
                        summaries.Add(summary);
                    }
                }
                catch (Exception ex)
                {
                    AppLogger.Info("Overview", $"Failed to get summary for {server.DisplayName}: {ex.Message}");
                }
            }

            OverviewItemsControl.ItemsSource = summaries;

            foreach (var summary in summaries)
            {
                CheckPerformanceAlerts(summary);
            }
        }
        catch (Exception ex)
        {
            AppLogger.Info("Overview", $"RefreshOverviewAsync failed: {ex.Message}");
        }
    }

    private void ServerListView_MouseDoubleClick(object sender, MouseButtonEventArgs e)
    {
        if (ServerListView.SelectedItem is ServerConnection server)
        {
            ConnectToServer(server);
        }
    }

    private void OverviewCard_MouseLeftButtonDown(object sender, MouseButtonEventArgs e)
    {
        if (e.ClickCount == 2 && sender is FrameworkElement fe && fe.DataContext is ServerSummaryItem summary)
        {
            var server = _serverManager.GetAllServers()
                .FirstOrDefault(s => s.ServerName == summary.ServerName);
            if (server != null)
            {
                ConnectToServer(server);
            }
        }
    }

    private async void ConnectToServer(ServerConnection server)
    {
        // Check if tab already open
        if (_openServerTabs.TryGetValue(server.Id, out var existingTab))
        {
            ServerTabControl.SelectedItem = existingTab;
            return;
        }

        // Clear MFA cancellation flag when user explicitly connects
        // This gives them a fresh attempt at authentication
        var currentStatus = _serverManager.GetConnectionStatus(server.Id);
        if (server.AuthenticationType == AuthenticationTypes.EntraMFA && currentStatus.UserCancelledMfa)
        {
            currentStatus.UserCancelledMfa = false;
            StatusText.Text = "Retrying MFA authentication...";
        }

        // Ensure connection status is populated with UTC offset before opening tab
        // This is critical for timezone-correct chart display
        var status = _serverManager.GetConnectionStatus(server.Id);
        if (!status.UtcOffsetMinutes.HasValue)
        {
            StatusText.Text = "Checking server connection...";
            // Allow interactive auth (MFA) when user explicitly opens a server
            status = await _serverManager.CheckConnectionAsync(server.Id, allowInteractiveAuth: true);
        }

        var utcOffset = status.UtcOffsetMinutes ?? 0;
        var serverTab = new ServerTab(server, _databaseInitializer, _serverManager.CredentialService, utcOffset, status.HasMsdbAccess, status.SqlEngineEdition == 5);
        var tabHeader = CreateTabHeader(server);
        var tabItem = new TabItem
        {
            Header = tabHeader,
            Content = serverTab
        };

        /* Subscribe to events — store handlers so we can unsubscribe on tab close */
        var serverId = server.Id;
        Action<int, int, DateTime?> alertHandler = (blockingCount, deadlockCount, latestEventTime) =>
        {
            Dispatcher.Invoke(() => UpdateTabBadge(tabHeader, serverId, blockingCount, deadlockCount, latestEventTime));
        };
        Action<int> timeRangeHandler = (selectedIndex) =>
        {
            Dispatcher.Invoke(() =>
            {
                foreach (var tab in _openServerTabs.Values)
                {
                    if (tab.Content is ServerTab st && st != serverTab)
                    {
                        st.SetTimeRangeIndex(selectedIndex);
                    }
                }
            });
        };
        Func<Task> refreshHandler = async () =>
        {
            if (_collectorService != null)
            {
                var onLoadCollectors = _scheduleManager.GetOnLoadCollectorsForServer(server.Id);
                foreach (var collector in onLoadCollectors)
                {
                    try
                    {
                        await _collectorService.RunCollectorAsync(server, collector.Name);
                    }
                    catch (Exception ex)
                    {
                        AppLogger.Info("MainWindow", $"Re-collection of {collector.Name} failed: {ex.Message}");
                    }
                }
            }
        };

        serverTab.AlertCountsChanged += alertHandler;
        serverTab.ApplyTimeRangeRequested += timeRangeHandler;
        serverTab.ManualRefreshRequested += refreshHandler;
        _tabEventHandlers[server.Id] = (alertHandler, timeRangeHandler, refreshHandler);

        _openServerTabs[server.Id] = tabItem;
        ServerTabControl.Items.Add(tabItem);
        ServerTabControl.SelectedItem = tabItem;

        // Show the tab control, hide empty state
        EmptyStatePanel.Visibility = Visibility.Collapsed;
        ServerTabControl.Visibility = Visibility.Visible;

        _serverManager.UpdateLastConnected(server.Id);

        // Show existing historical data immediately
        serverTab.RefreshData();

        // Then collect fresh data and refresh again
        if (_collectorService != null)
        {
            StatusText.Text = $"Collecting data from {server.DisplayNameWithIntent}...";
            try
            {
                await _collectorService.RunAllCollectorsForServerAsync(server);
                StatusText.Text = $"Connected to {server.DisplayNameWithIntent} - Data loaded";
                serverTab.RefreshData();
                UpdateCollectorHealth();
                _ = RefreshOverviewAsync();
            }
            catch (Exception ex)
            {
                StatusText.Text = $"Connected to {server.DisplayNameWithIntent} - Collection error: {ex.Message}";
            }
        }
        else
        {
            StatusText.Text = $"Connected to {server.DisplayNameWithIntent}";
        }
    }

    private StackPanel CreateTabHeader(ServerConnection server)
    {
        var panel = new StackPanel { Orientation = Orientation.Horizontal };

        var tabLabel = server.ReadOnlyIntent ? $"{server.DisplayName} (RO)" : server.DisplayName;
        panel.Children.Add(new TextBlock
        {
            Text = tabLabel,
            VerticalAlignment = VerticalAlignment.Center,
            Margin = new Thickness(0, 0, 4, 0)
        });

        /* Alert badge - hidden by default, shown when blocking/deadlocks detected */
        var badge = new System.Windows.Controls.Border
        {
            Background = System.Windows.Media.Brushes.OrangeRed,
            CornerRadius = new CornerRadius(7),
            Padding = new Thickness(5, 1, 5, 1),
            Margin = new Thickness(0, 0, 4, 0),
            VerticalAlignment = VerticalAlignment.Center,
            Visibility = Visibility.Collapsed,
            Child = new TextBlock
            {
                FontSize = 10,
                FontWeight = FontWeights.Bold,
                Foreground = System.Windows.Media.Brushes.White,
                VerticalAlignment = VerticalAlignment.Center
            }
        };
        badge.Tag = "AlertBadge";

        /* Add context menu to badge for acknowledge/silence functionality */
        var serverId = server.Id;
        var contextMenu = new ContextMenu();

        var acknowledgeItem = new MenuItem
        {
            Header = "Acknowledge Alert",
            Tag = serverId,
            Icon = new TextBlock { Text = "✓", FontWeight = FontWeights.Bold }
        };
        acknowledgeItem.Click += AcknowledgeServerAlert_Click;

        var silenceItem = new MenuItem
        {
            Header = "Silence This Server",
            Tag = serverId,
            Icon = new TextBlock { Text = "🔇" }
        };
        silenceItem.Click += SilenceServer_Click;

        var unsilenceItem = new MenuItem
        {
            Header = "Unsilence",
            Tag = serverId,
            Icon = new TextBlock { Text = "🔔" }
        };
        unsilenceItem.Click += UnsilenceServer_Click;

        contextMenu.Items.Add(acknowledgeItem);
        contextMenu.Items.Add(silenceItem);
        contextMenu.Items.Add(new Separator());
        contextMenu.Items.Add(unsilenceItem);

        /* Update menu items based on state when opened */
        contextMenu.Opened += (s, args) =>
        {
            var isSilenced = _alertStateService.IsServerSilenced(serverId);
            var hasAlert = badge.Visibility == Visibility.Visible;

            acknowledgeItem.IsEnabled = hasAlert;
            silenceItem.IsEnabled = !isSilenced;
            unsilenceItem.IsEnabled = isSilenced;
        };

        badge.ContextMenu = contextMenu;
        panel.Children.Add(badge);

        var closeButton = new Button
        {
            Content = "x",
            FontSize = 10,
            Padding = new Thickness(4, 0, 4, 0),
            VerticalAlignment = VerticalAlignment.Center,
            Cursor = Cursors.Hand
        };
        closeButton.Click += (s, e) => CloseServerTab(server.Id);
        panel.Children.Add(closeButton);

        return panel;
    }

    private void UpdateTabBadge(StackPanel tabHeader, string serverId, int blockingCount, int deadlockCount, DateTime? latestEventTime)
    {
        var totalAlerts = blockingCount + deadlockCount;

        /* Delegate count tracking and acknowledgement clearing to AlertStateService.
           Uses latestEventTime to only clear ack when genuinely new events arrive,
           not when the user just switches time ranges. */
        bool shouldShow = _alertStateService.UpdateAlertCounts(serverId, blockingCount, deadlockCount, latestEventTime);

        foreach (var child in tabHeader.Children)
        {
            if (child is System.Windows.Controls.Border border && border.Tag as string == "AlertBadge")
            {
                if (shouldShow)
                {
                    border.Visibility = Visibility.Visible;
                    border.Background = deadlockCount > 0
                        ? System.Windows.Media.Brushes.Red
                        : System.Windows.Media.Brushes.OrangeRed;

                    if (border.Child is TextBlock text)
                    {
                        text.Text = totalAlerts > 99 ? "99+" : totalAlerts.ToString();
                        text.ToolTip = $"Blocking: {blockingCount}, Deadlocks: {deadlockCount}\nRight-click to dismiss";
                    }
                }
                else
                {
                    border.Visibility = Visibility.Collapsed;
                }
                break;
            }
        }
    }

    private void AcknowledgeServerAlert_Click(object sender, RoutedEventArgs e)
    {
        if (sender is MenuItem menuItem && menuItem.Tag is string serverId)
        {
            _alertStateService.AcknowledgeAlert(serverId);

            /* Find and hide the badge for this server */
            if (_openServerTabs.TryGetValue(serverId, out var tab) && tab.Header is StackPanel panel)
            {
                foreach (var child in panel.Children)
                {
                    if (child is System.Windows.Controls.Border border && border.Tag as string == "AlertBadge")
                    {
                        border.Visibility = Visibility.Collapsed;
                        break;
                    }
                }
            }
        }
    }

    private void SilenceServer_Click(object sender, RoutedEventArgs e)
    {
        if (sender is MenuItem menuItem && menuItem.Tag is string serverId)
        {
            _alertStateService.SilenceServer(serverId);

            /* Find and hide the badge for this server */
            if (_openServerTabs.TryGetValue(serverId, out var tab) && tab.Header is StackPanel panel)
            {
                foreach (var child in panel.Children)
                {
                    if (child is System.Windows.Controls.Border border && border.Tag as string == "AlertBadge")
                    {
                        border.Visibility = Visibility.Collapsed;
                        break;
                    }
                }
            }
        }
    }

    private void UnsilenceServer_Click(object sender, RoutedEventArgs e)
    {
        if (sender is MenuItem menuItem && menuItem.Tag is string serverId)
        {
            _alertStateService.UnsilenceServer(serverId);

            /* The next refresh cycle will show the badge if there are alerts */
        }
    }

    private void CloseServerTab(string serverId)
    {
        if (_openServerTabs.TryGetValue(serverId, out var tab))
        {
            if (tab.Content is ServerTab serverTab)
            {
                /* Unsubscribe event handlers to prevent memory leaks */
                if (_tabEventHandlers.TryGetValue(serverId, out var handlers))
                {
                    serverTab.AlertCountsChanged -= handlers.AlertCounts;
                    serverTab.ApplyTimeRangeRequested -= handlers.ApplyTimeRange;
                    serverTab.ManualRefreshRequested -= handlers.ManualRefresh;
                    _tabEventHandlers.Remove(serverId);
                }

                serverTab.StopRefresh();
                serverTab.DisposeChartHelpers();

                /* Clear delta cache for this server to free memory */
                _collectorService?.DeltaCalculator?.ClearServer(serverTab.ServerId);
            }

            ServerTabControl.Items.Remove(tab);
            _openServerTabs.Remove(serverId);

            /* Clean up alert state for this server */
            _alertStateService.RemoveServerState(serverId);

            // Show empty state if no tabs open
            if (_openServerTabs.Count == 0)
            {
                var servers = _serverManager.GetAllServers();
                if (servers.Count == 0)
                {
                    EmptyStatePanel.Visibility = Visibility.Visible;
                    ServerTabControl.Visibility = Visibility.Collapsed;
                }
            }
        }
    }

    private void AddServerButton_Click(object sender, RoutedEventArgs e)
    {
        var dialog = new AddServerDialog(_serverManager) { Owner = this };
        if (dialog.ShowDialog() == true && dialog.AddedServer != null)
        {
            RefreshServerList();
            StatusText.Text = $"Added server: {dialog.AddedServer.DisplayNameWithIntent}";
        }
    }

    private void ManageServersButton_Click(object sender, RoutedEventArgs e)
    {
        var window = new ManageServersWindow(_serverManager) { Owner = this };
        window.ShowDialog();

        if (window.ServersChanged)
        {
            // Purge collector health for servers that were removed
            if (_collectorService != null)
            {
                var currentServerIds = new HashSet<int>(
                    _serverManager.GetAllServers().Select(s =>
                        RemoteCollectorService.GetDeterministicHashCode(
                            RemoteCollectorService.GetServerNameForStorage(s))));
                _collectorService.ClearHealthExcept(currentServerIds);
            }

            RefreshServerList();
        }
    }

    private async void SettingsButton_Click(object sender, RoutedEventArgs e)
    {
        var window = new SettingsWindow(_scheduleManager, _serverManager, _backgroundService, _mcpService, _muteRuleService) { Owner = this };
        window.ShowDialog();
        UpdateStatusBar();

        if (window.McpSettingsChanged)
        {
            await StopMcpServerAsync();
            await StartMcpServerAsync();
        }
    }

    private void AboutButton_Click(object sender, RoutedEventArgs e)
    {
        var window = new Windows.AboutWindow { Owner = this };
        window.ShowDialog();
    }

    private void ViewLogButton_Click(object sender, RoutedEventArgs e)
    {
        var logFile = AppLogger.GetCurrentLogFile();
        try
        {
            if (System.IO.File.Exists(logFile))
            {
                System.Diagnostics.Process.Start(new System.Diagnostics.ProcessStartInfo
                {
                    FileName = logFile,
                    UseShellExecute = true
                });
            }
            else
            {
                System.Diagnostics.Process.Start(new System.Diagnostics.ProcessStartInfo
                {
                    FileName = AppLogger.GetLogDirectory(),
                    UseShellExecute = true
                });
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show($"Could not open log file: {ex.Message}", "Error",
                MessageBoxButton.OK, MessageBoxImage.Error);
        }
    }

    private void OpenLogFolderButton_Click(object sender, RoutedEventArgs e)
    {
        var logDir = AppLogger.GetLogDirectory();
        try
        {
            System.IO.Directory.CreateDirectory(logDir);
            System.Diagnostics.Process.Start(new System.Diagnostics.ProcessStartInfo
            {
                FileName = logDir,
                UseShellExecute = true
            });
        }
        catch (Exception ex)
        {
            MessageBox.Show($"Could not open log folder: {ex.Message}", "Error",
                MessageBoxButton.OK, MessageBoxImage.Error);
        }
    }

    private void ImportSettingsButton_Click(object sender, RoutedEventArgs e)
    {
        var dialog = new Microsoft.Win32.OpenFolderDialog
        {
            Title = "Select Previous Lite Install Folder"
        };

        if (dialog.ShowDialog() != true) return;

        var oldConfigDir = System.IO.Path.Combine(dialog.FolderName, "config");
        var serversJsonPath = System.IO.Path.Combine(oldConfigDir, "servers.json");
        if (!System.IO.File.Exists(serversJsonPath))
        {
            MessageBox.Show(
                "No config\\servers.json found in the selected folder.\n\nSelect the root folder of a previous Lite installation.",
                "Import Settings",
                MessageBoxButton.OK,
                MessageBoxImage.Warning);
            return;
        }

        try
        {
            // Import server connections (upsert by server name)
            var (imported, skipped) = _serverManager.ImportServersFromFile(serversJsonPath);

            // Copy config files that don't already exist in the current install
            var settingsFiles = new[] { "settings.json", "collection_schedule.json", "ignored_wait_types.json" };
            int settingsCopied = 0;

            foreach (var fileName in settingsFiles)
            {
                var source = System.IO.Path.Combine(oldConfigDir, fileName);
                var target = System.IO.Path.Combine(App.ConfigDirectory, fileName);

                if (System.IO.File.Exists(source) && !System.IO.File.Exists(target))
                {
                    System.IO.File.Copy(source, target);
                    settingsCopied++;
                }
            }

            // Copy alert_state.json from old root directory
            var oldAlertState = System.IO.Path.Combine(dialog.FolderName, "alert_state.json");
            var currentAlertState = System.IO.Path.Combine(App.DataDirectory, "alert_state.json");
            if (System.IO.File.Exists(oldAlertState) && !System.IO.File.Exists(currentAlertState))
            {
                System.IO.File.Copy(oldAlertState, currentAlertState);
                settingsCopied++;
            }

            var message = $"Imported {imported} server connection(s).";
            if (skipped > 0)
                message += $"\nSkipped {skipped} duplicate(s) (already configured).";
            if (settingsCopied > 0)
                message += $"\nCopied {settingsCopied} settings file(s).";
            if (imported > 0)
                message += "\n\nCredentials from the previous install are preserved.\nIf any connections fail to authenticate, re-enter the password in Manage Servers.";
            if (settingsCopied > 0)
                message += "\n\nRestart the application to apply imported settings.";

            MessageBox.Show(message, "Import Settings", MessageBoxButton.OK, MessageBoxImage.Information);

            if (imported > 0)
                RefreshServerList();
        }
        catch (Exception ex)
        {
            MessageBox.Show($"Failed to import settings: {ex.Message}", "Import Error", MessageBoxButton.OK, MessageBoxImage.Error);
        }
    }

    private async void ImportDataButton_Click(object sender, RoutedEventArgs e)
    {
        /* Open folder browser to select the old Lite install directory */
        var dialog = new Microsoft.Win32.OpenFolderDialog
        {
            Title = "Select Previous Lite Install Folder",
            Multiselect = false
        };

        if (dialog.ShowDialog(this) != true || string.IsNullOrWhiteSpace(dialog.FolderName))
        {
            return;
        }

        var sourceFolder = dialog.FolderName;

        /* Validate that monitor.duckdb exists in the selected folder */
        if (!DataImportService.ValidateSourceFolder(sourceFolder))
        {
            MessageBox.Show(
                "The selected folder does not contain a monitor.duckdb file.\n\n" +
                "Please select the folder where the previous Lite application was installed.",
                "Invalid Folder",
                MessageBoxButton.OK,
                MessageBoxImage.Warning);
            return;
        }

        /* Prevent double-clicks */
        ImportDataButton.IsEnabled = false;
        ImportDataButtonText.Text = "Importing...";
        StatusText.Text = "Importing data from previous install...";

        try
        {
            var importService = new DataImportService(_databaseInitializer, App.ArchiveDirectory);

            /* The tryLockOldDb callback runs on the UI thread to show the retry dialog */
            var result = await Task.Run(async () =>
                await importService.RunImportAsync(sourceFolder, async _ =>
                {
                    var answer = MessageBoxResult.Cancel;
                    await Dispatcher.InvokeAsync(() =>
                    {
                        answer = MessageBox.Show(
                            "Could not lock the database to flush current data.\n\n" +
                            "Close the previous Lite application and click OK to try again.",
                            "Database Locked",
                            MessageBoxButton.OKCancel,
                            MessageBoxImage.Warning);
                    });
                    return answer == MessageBoxResult.OK;
                }));

            if (result.Success)
            {
                StatusText.Text = "Import complete — refreshing views...";
                await _serverManager.CheckAllConnectionsAsync();
                RefreshServerList();
                UpdateStatusBar();
                StatusText.Text = "Import complete";

                MessageBox.Show(
                    $"Import completed successfully.\n\n" +
                    $"Tables flushed from old database: {result.TablesFlushed}\n" +
                    $"Parquet files imported: {result.FilesImported}\n\n" +
                    "Historical data is now available in all views.",
                    "Import Complete",
                    MessageBoxButton.OK,
                    MessageBoxImage.Information);
            }
            else
            {
                StatusText.Text = "Import cancelled or failed";
                if (!string.IsNullOrEmpty(result.ErrorMessage))
                {
                    MessageBox.Show(
                        result.ErrorMessage,
                        "Import Failed",
                        MessageBoxButton.OK,
                        MessageBoxImage.Error);
                }
            }
        }
        catch (Exception ex)
        {
            AppLogger.Error("DataImport", "Unhandled import error", ex);
            StatusText.Text = "Import failed";
            MessageBox.Show(
                $"An unexpected error occurred during import:\n\n{ex.Message}",
                "Import Error",
                MessageBoxButton.OK,
                MessageBoxImage.Error);
        }
        finally
        {
            ImportDataButton.IsEnabled = true;
            ImportDataButtonText.Text = "Import Data";
        }
    }

    /// <summary>
    /// Gets the ServerConnection from a context menu click on a server list item.
    /// </summary>
    private ServerConnection? GetServerFromContextMenu(object sender)
    {
        if (sender is not MenuItem menuItem) return null;
        var contextMenu = menuItem.Parent as ContextMenu;
        var border = contextMenu?.PlacementTarget as FrameworkElement;
        return border?.DataContext as ServerConnection;
    }

    private void ServerContextMenu_Connect_Click(object sender, RoutedEventArgs e)
    {
        var server = GetServerFromContextMenu(sender);
        if (server != null) ConnectToServer(server);
    }

    private void ServerContextMenu_Disconnect_Click(object sender, RoutedEventArgs e)
    {
        var server = GetServerFromContextMenu(sender);
        if (server != null) CloseServerTab(server.Id);
    }

    private void ServerContextMenu_ToggleFavorite_Click(object sender, RoutedEventArgs e)
    {
        var server = GetServerFromContextMenu(sender);
        if (server != null)
        {
            _serverManager.ToggleFavorite(server.Id);
            RefreshServerList();
        }
    }

    private void ServerContextMenu_Edit_Click(object sender, RoutedEventArgs e)
    {
        var server = GetServerFromContextMenu(sender);
        if (server == null) return;

        var dialog = new AddServerDialog(_serverManager, server) { Owner = this };
        if (dialog.ShowDialog() == true)
        {
            RefreshServerList();
        }
    }

    private void ServerContextMenu_Remove_Click(object sender, RoutedEventArgs e)
    {
        var server = GetServerFromContextMenu(sender);
        if (server == null) return;

        var result = MessageBox.Show(
            $"Remove server '{server.DisplayNameWithIntent}'?",
            "Remove Server",
            MessageBoxButton.YesNo,
            MessageBoxImage.Question);

        if (result == MessageBoxResult.Yes)
        {
            CloseServerTab(server.Id);
            _collectorService?.ClearHealthForServer(
                RemoteCollectorService.GetDeterministicHashCode(
                    RemoteCollectorService.GetServerNameForStorage(server)));
            _serverManager.DeleteServer(server.Id);
            RefreshServerList();
            StatusText.Text = $"Removed server: {server.DisplayNameWithIntent}";
        }
    }

    private bool _sidebarCollapsed;

    private void ToggleSidebar_Click(object sender, RoutedEventArgs e)
    {
        _sidebarCollapsed = !_sidebarCollapsed;

        if (_sidebarCollapsed)
        {
            SidebarColumn.Width = new GridLength(40);
            SidebarTitle.Visibility = Visibility.Collapsed;
            SidebarSubtitle.Visibility = Visibility.Collapsed;
            if (sender is System.Windows.Controls.Button btn) btn.Content = "»";
        }
        else
        {
            SidebarColumn.Width = new GridLength(280);
            SidebarTitle.Visibility = Visibility.Visible;
            SidebarSubtitle.Visibility = Visibility.Visible;
            if (sender is System.Windows.Controls.Button btn) btn.Content = "«";
        }
    }

    private async void RefreshButton_Click(object sender, RoutedEventArgs e)
    {
        try
        {
            StatusText.Text = "Refreshing...";

            // Check all server connections
            await _serverManager.CheckAllConnectionsAsync();

            RefreshServerList();
            UpdateStatusBar();

            StatusText.Text = "Refresh complete";
        }
        catch (Exception ex)
        {
            StatusText.Text = $"Refresh failed: {ex.Message}";
        }
    }

    private void CheckConnectionsAndNotify()
    {
        try
        {
            var servers = _serverManager.GetAllServers();
            bool needsRefresh = false;
            foreach (var server in servers)
            {
                var status = _serverManager.GetConnectionStatus(server.Id);
                server.IsOnline = status?.IsOnline;
                if (status?.IsOnline == null) continue;

                bool isOnline = status.IsOnline == true;
                bool hasErrors = _collectorService != null && isOnline
                    && _collectorService.GetHealthSummary(server).ErroringCollectors > 0;
                server.HasCollectorErrors = hasErrors;

                if (_previousConnectionStates.TryGetValue(server.Id, out var wasOnline))
                {
                    if (App.AlertsEnabled && App.NotifyConnectionChanges)
                    {
                        if (wasOnline && !isOnline)
                        {
                            _trayService?.ShowNotification(
                                "Server Offline",
                                $"{server.DisplayNameWithIntent} is unreachable: {status.ErrorMessage ?? "unknown error"}",
                                Hardcodet.Wpf.TaskbarNotification.BalloonIcon.Error);
                        }
                        else if (!wasOnline && isOnline)
                        {
                            _trayService?.ShowNotification(
                                "Server Online",
                                $"{server.DisplayNameWithIntent} is back online",
                                Hardcodet.Wpf.TaskbarNotification.BalloonIcon.Info);
                        }
                    }

                    if (wasOnline != isOnline)
                    {
                        needsRefresh = true;
                    }
                }
                else
                {
                    /* First time seeing this server's status — need to refresh */
                    needsRefresh = true;
                }

                if (_previousCollectorErrorStates.TryGetValue(server.Id, out var prevHasErrors) && prevHasErrors != hasErrors)
                    needsRefresh = true;

                _previousConnectionStates[server.Id] = isOnline;
                _previousCollectorErrorStates[server.Id] = hasErrors;
            }

            if (needsRefresh)
            {
                RefreshServerList();
            }
        }
        catch (Exception ex)
        {
            AppLogger.Error("ConnectionAlerts", $"Connection check notify failed: {ex.Message}");
        }
    }

    private async void CheckPerformanceAlerts(ServerSummaryItem summary)
    {
        if (!App.AlertsEnabled || _trayService == null) return;

        var key = summary.ServerId.ToString();
        var now = DateTime.UtcNow;
        var alertCooldown = TimeSpan.FromMinutes(App.AlertCooldownMinutes);

        /* Skip popup/email alerts if user has acknowledged or silenced this server */
        bool suppressPopups = !_alertStateService.ShouldShowAlerts(key);

        /* CPU alerts — uses the metric the user selected (Total non-idle CPU by default, or SQL Server only). */
        var alertCpuValue = summary.CpuPercentForAlert;
        string cpuMetricLabel = App.AlertCpuMode == CpuAlertMode.Total ? "Total CPU" : "SQL CPU";
        bool cpuExceeded = App.AlertCpuEnabled
            && alertCpuValue.HasValue
            && alertCpuValue.Value >= App.AlertCpuThreshold;

        if (cpuExceeded)
        {
            _activeCpuAlert[key] = true;
            if (!suppressPopups && (!_lastCpuAlert.TryGetValue(key, out var lastCpu) || now - lastCpu >= alertCooldown))
            {
                var muteCtx = new AlertMuteContext { ServerName = summary.DisplayName, MetricName = "High CPU" };
                bool isMuted = _muteRuleService.IsAlertMuted(muteCtx);
                _lastCpuAlert[key] = now;

                if (!isMuted)
                {
                    _trayService.ShowSnoozableNotification(
                        "High CPU",
                        $"{summary.DisplayName}: {cpuMetricLabel} at {alertCpuValue:F0}% (threshold: {App.AlertCpuThreshold}%)",
                        Hardcodet.Wpf.TaskbarNotification.BalloonIcon.Warning,
                        summary.DisplayName,
                        "High CPU",
                        _muteRuleService);
                }

                var cpuDetailText = $"  {cpuMetricLabel}: {alertCpuValue:F0}%\n  Threshold: {App.AlertCpuThreshold}%";

                await _emailAlertService.TrySendAlertEmailAsync(
                    "High CPU",
                    summary.DisplayName,
                    $"{alertCpuValue:F0}%",
                    $"{App.AlertCpuThreshold}%",
                    summary.ServerId,
                    muted: isMuted,
                    detailText: cpuDetailText);
            }
        }
        else if (_activeCpuAlert.TryGetValue(key, out var wasCpu) && wasCpu)
        {
            _activeCpuAlert[key] = false;
            _trayService.ShowNotification(
                "CPU Resolved",
                $"{summary.DisplayName}: {cpuMetricLabel} back to {alertCpuValue:F0}%",
                Hardcodet.Wpf.TaskbarNotification.BalloonIcon.Info);
        }

        /* Blocking alerts */
        var effectiveBlockingCount = summary.BlockingCount;
        if (App.AlertBlockingEnabled && App.AlertExcludedDatabases.Count > 0
            && summary.BlockingCount >= App.AlertBlockingThreshold && _dataService != null)
        {
            try
            {
                var blockingRows = await _dataService.GetRecentBlockedProcessReportsAsync(summary.ServerId, hoursBack: 1);
                effectiveBlockingCount = blockingRows
                    .Count(r => string.IsNullOrEmpty(r.DatabaseName) ||
                        !App.AlertExcludedDatabases.Any(e =>
                            string.Equals(e, r.DatabaseName, StringComparison.OrdinalIgnoreCase)));
            }
            catch (Exception ex)
            {
                AppLogger.Error("Alerts", $"Failed to filter blocking count for {summary.DisplayName}: {ex.Message}");
            }
        }

        bool blockingExceeded = App.AlertBlockingEnabled
            && effectiveBlockingCount >= App.AlertBlockingThreshold;

        if (blockingExceeded)
        {
            _activeBlockingAlert[key] = true;
            if (!suppressPopups && (!_lastBlockingAlert.TryGetValue(key, out var lastBlocking) || now - lastBlocking >= alertCooldown))
            {
                var muteCtx = new AlertMuteContext { ServerName = summary.DisplayName, MetricName = "Blocking Detected" };
                bool isMuted = _muteRuleService.IsAlertMuted(muteCtx);
                _lastBlockingAlert[key] = now;

                if (!isMuted)
                {
                    _trayService.ShowSnoozableNotification(
                        "Blocking Detected",
                        $"{summary.DisplayName}: {effectiveBlockingCount} blocking session(s)",
                        Hardcodet.Wpf.TaskbarNotification.BalloonIcon.Warning,
                        summary.DisplayName,
                        "Blocking Detected",
                        _muteRuleService);
                }

                var blockingContext = await BuildBlockingContextAsync(summary.ServerId);
                var detailText = ContextToDetailText(blockingContext);

                await _emailAlertService.TrySendAlertEmailAsync(
                    "Blocking Detected",
                    summary.DisplayName,
                    effectiveBlockingCount.ToString(),
                    App.AlertBlockingThreshold.ToString(),
                    summary.ServerId,
                    blockingContext,
                    muted: isMuted,
                    detailText: detailText);
            }
        }
        else if (_activeBlockingAlert.TryGetValue(key, out var wasBlocking) && wasBlocking)
        {
            _activeBlockingAlert[key] = false;
            _trayService.ShowNotification(
                "Blocking Cleared",
                $"{summary.DisplayName}: No active blocking",
                Hardcodet.Wpf.TaskbarNotification.BalloonIcon.Info);
        }

        /* Deadlock alerts */
        var effectiveDeadlockCount = summary.DeadlockCount;
        if (App.AlertDeadlockEnabled && App.AlertExcludedDatabases.Count > 0
            && summary.DeadlockCount >= App.AlertDeadlockThreshold && _dataService != null)
        {
            try
            {
                var deadlockRows = await _dataService.GetRecentDeadlocksAsync(summary.ServerId, hoursBack: 1);
                effectiveDeadlockCount = deadlockRows
                    .Count(r => !IsDeadlockExcluded(r, App.AlertExcludedDatabases));
            }
            catch (Exception ex)
            {
                AppLogger.Error("Alerts", $"Failed to filter deadlock count for {summary.DisplayName}: {ex.Message}");
            }
        }

        bool deadlocksExceeded = App.AlertDeadlockEnabled
            && effectiveDeadlockCount >= App.AlertDeadlockThreshold;

        if (deadlocksExceeded)
        {
            _activeDeadlockAlert[key] = true;
            if (!suppressPopups && (!_lastDeadlockAlert.TryGetValue(key, out var lastDeadlock) || now - lastDeadlock >= alertCooldown))
            {
                var muteCtx = new AlertMuteContext { ServerName = summary.DisplayName, MetricName = "Deadlocks Detected" };
                bool isMuted = _muteRuleService.IsAlertMuted(muteCtx);
                _lastDeadlockAlert[key] = now;

                if (!isMuted)
                {
                    _trayService.ShowSnoozableNotification(
                        "Deadlocks Detected",
                        $"{summary.DisplayName}: {effectiveDeadlockCount} deadlock(s) in the last hour",
                        Hardcodet.Wpf.TaskbarNotification.BalloonIcon.Error,
                        summary.DisplayName,
                        "Deadlocks Detected",
                        _muteRuleService);
                }

                var deadlockContext = await BuildDeadlockContextAsync(summary.ServerId);
                var detailText = ContextToDetailText(deadlockContext);

                await _emailAlertService.TrySendAlertEmailAsync(
                    "Deadlocks Detected",
                    summary.DisplayName,
                    effectiveDeadlockCount.ToString(),
                    App.AlertDeadlockThreshold.ToString(),
                    summary.ServerId,
                    deadlockContext,
                    muted: isMuted,
                    detailText: detailText);
            }
        }
        else if (_activeDeadlockAlert.TryGetValue(key, out var wasDeadlock) && wasDeadlock)
        {
            _activeDeadlockAlert[key] = false;
            _trayService.ShowNotification(
                "Deadlocks Cleared",
                $"{summary.DisplayName}: No deadlocks in the last hour",
                Hardcodet.Wpf.TaskbarNotification.BalloonIcon.Info);
        }

        /* Poison wait alerts */
        if (App.AlertPoisonWaitEnabled && _dataService != null)
        {
            try
            {
                var poisonWaits = await _dataService.GetLatestPoisonWaitAvgsAsync(summary.ServerId);
                var triggered = poisonWaits.FindAll(w => w.AvgMsPerWait >= App.AlertPoisonWaitThresholdMs);

                if (triggered.Count > 0)
                {
                    _activePoisonWaitAlert[key] = true;
                    if (!suppressPopups && (!_lastPoisonWaitAlert.TryGetValue(key, out var lastPoisonWait) || now - lastPoisonWait >= alertCooldown))
                    {
                        var worst = triggered[0];
                        var allWaitNames = string.Join(", ", triggered.ConvertAll(w => $"{w.WaitType} ({w.AvgMsPerWait:F0}ms)"));

                        /* Poison wait mute check uses the worst (highest avg ms/wait) triggered wait type.
                           Limitation: if a user mutes a specific wait type that isn't the worst, the alert
                           still fires. Conversely, muting the worst type suppresses the entire alert even
                           if other unmuted poison waits are present. */
                        var muteCtx = new AlertMuteContext { ServerName = summary.DisplayName, MetricName = "Poison Wait", WaitType = worst.WaitType };
                        bool isMuted = _muteRuleService.IsAlertMuted(muteCtx);
                        _lastPoisonWaitAlert[key] = now;

                        if (!isMuted)
                        {
                            _trayService.ShowSnoozableNotification(
                                "Poison Wait",
                                $"{summary.DisplayName}: {worst.WaitType} avg {worst.AvgMsPerWait:F0}ms/wait",
                                Hardcodet.Wpf.TaskbarNotification.BalloonIcon.Error,
                                summary.DisplayName,
                                "Poison Wait",
                                _muteRuleService);
                        }

                        var poisonContext = BuildPoisonWaitContext(triggered);
                        var detailText = ContextToDetailText(poisonContext);

                        await _emailAlertService.TrySendAlertEmailAsync(
                            "Poison Wait",
                            summary.DisplayName,
                            allWaitNames,
                            $"{App.AlertPoisonWaitThresholdMs}ms avg",
                            summary.ServerId,
                            poisonContext,
                            numericCurrentValue: worst.AvgMsPerWait,
                            numericThresholdValue: App.AlertPoisonWaitThresholdMs,
                            muted: isMuted,
                            detailText: detailText);
                    }
                }
                else if (_activePoisonWaitAlert.TryGetValue(key, out var wasPoisonWait) && wasPoisonWait)
                {
                    _activePoisonWaitAlert[key] = false;
                    _trayService.ShowNotification(
                        "Poison Waits Cleared",
                        $"{summary.DisplayName}: Poison wait avg below threshold",
                        Hardcodet.Wpf.TaskbarNotification.BalloonIcon.Info);
                }
            }
            catch (Exception ex)
            {
                AppLogger.Error("Alerts", $"Failed to check poison waits for {summary.DisplayName}: {ex.Message}");
            }
        }

        /* Long-running query alerts */
        if (App.AlertLongRunningQueryEnabled && _dataService != null)
        {
            try
            {
                var longRunning = await _dataService.GetLongRunningQueriesAsync(summary.ServerId, App.AlertLongRunningQueryThresholdMinutes, App.AlertLongRunningQueryMaxResults, App.AlertLongRunningQueryExcludeSpServerDiagnostics, App.AlertLongRunningQueryExcludeWaitFor, App.AlertLongRunningQueryExcludeBackups, App.AlertLongRunningQueryExcludeMiscWaits);

                if (App.AlertExcludedDatabases.Count > 0)
                {
                    longRunning = longRunning
                        .Where(q => string.IsNullOrEmpty(q.DatabaseName) ||
                            !App.AlertExcludedDatabases.Any(e =>
                                string.Equals(e, q.DatabaseName, StringComparison.OrdinalIgnoreCase)))
                        .ToList();
                }

                if (longRunning.Count > 0)
                {
                    _activeLongRunningQueryAlert[key] = true;
                    if (!suppressPopups && (!_lastLongRunningQueryAlert.TryGetValue(key, out var lastLrq) || now - lastLrq >= alertCooldown))
                    {
                        var worst = longRunning[0];
                        var elapsedMinutes = worst.ElapsedSeconds / 60;
                        var preview = TruncateText(worst.QueryText, 80);
                        var previewSuffix = string.IsNullOrEmpty(preview) ? "" : $" — {preview}";

                        var muteCtx = new AlertMuteContext
                        {
                            ServerName = summary.DisplayName,
                            MetricName = "Long-Running Query",
                            DatabaseName = worst.DatabaseName,
                            QueryText = worst.QueryText
                        };
                        bool isMuted = _muteRuleService.IsAlertMuted(muteCtx);
                        _lastLongRunningQueryAlert[key] = now;

                        if (!isMuted)
                        {
                            _trayService.ShowSnoozableNotification(
                                "Long-Running Query",
                                $"{summary.DisplayName}: Session #{worst.SessionId} running {elapsedMinutes}m{previewSuffix}",
                                Hardcodet.Wpf.TaskbarNotification.BalloonIcon.Warning,
                                summary.DisplayName,
                                "Long-Running Query",
                                _muteRuleService);
                        }

                        var lrqContext = BuildLongRunningQueryContext(longRunning);
                        var detailText = ContextToDetailText(lrqContext);

                        await _emailAlertService.TrySendAlertEmailAsync(
                            "Long-Running Query",
                            summary.DisplayName,
                            $"{longRunning.Count} query(s), longest {elapsedMinutes}m",
                            $"{App.AlertLongRunningQueryThresholdMinutes}m",
                            summary.ServerId,
                            lrqContext,
                            numericCurrentValue: elapsedMinutes,
                            numericThresholdValue: App.AlertLongRunningQueryThresholdMinutes,
                            muted: isMuted,
                            detailText: detailText);
                    }
                }
                else if (_activeLongRunningQueryAlert.TryGetValue(key, out var wasLongRunning) && wasLongRunning)
                {
                    _activeLongRunningQueryAlert[key] = false;
                    _trayService.ShowNotification(
                        "Long-Running Queries Cleared",
                        $"{summary.DisplayName}: No queries over threshold",
                        Hardcodet.Wpf.TaskbarNotification.BalloonIcon.Info);
                }
            }
            catch (Exception ex)
            {
                AppLogger.Error("Alerts", $"Failed to check long-running queries for {summary.DisplayName}: {ex.Message}");
            }
        }

        /* TempDB space alerts */
        if (App.AlertTempDbSpaceEnabled && _dataService != null)
        {
            try
            {
                var tempDb = await _dataService.GetLatestTempDbSpaceAsync(summary.ServerId);

                if (tempDb != null && tempDb.UsedPercent >= App.AlertTempDbSpaceThresholdPercent)
                {
                    _activeTempDbSpaceAlert[key] = true;
                    if (!suppressPopups && (!_lastTempDbSpaceAlert.TryGetValue(key, out var lastTempDb) || now - lastTempDb >= alertCooldown))
                    {
                        var muteCtx = new AlertMuteContext { ServerName = summary.DisplayName, MetricName = "TempDB Space" };
                        bool isMuted = _muteRuleService.IsAlertMuted(muteCtx);
                        _lastTempDbSpaceAlert[key] = now;

                        if (!isMuted)
                        {
                            _trayService.ShowSnoozableNotification(
                                "TempDB Space",
                                $"{summary.DisplayName}: TempDB {tempDb.UsedPercent:F0}% used",
                                Hardcodet.Wpf.TaskbarNotification.BalloonIcon.Warning,
                                summary.DisplayName,
                                "TempDB Space",
                                _muteRuleService);
                        }

                        var tempDbContext = BuildTempDbSpaceContext(tempDb);
                        var detailText = ContextToDetailText(tempDbContext);

                        await _emailAlertService.TrySendAlertEmailAsync(
                            "TempDB Space",
                            summary.DisplayName,
                            $"{tempDb.UsedPercent:F0}% used ({tempDb.TotalReservedMb:F0} MB)",
                            $"{App.AlertTempDbSpaceThresholdPercent}%",
                            summary.ServerId,
                            tempDbContext,
                            numericCurrentValue: tempDb.UsedPercent,
                            numericThresholdValue: App.AlertTempDbSpaceThresholdPercent,
                            muted: isMuted,
                            detailText: detailText);
                    }
                }
                else if (_activeTempDbSpaceAlert.TryGetValue(key, out var wasTempDb) && wasTempDb)
                {
                    _activeTempDbSpaceAlert[key] = false;
                    var pct = tempDb != null ? $"{tempDb.UsedPercent:F0}%" : "N/A";
                    _trayService.ShowNotification(
                        "TempDB Space Resolved",
                        $"{summary.DisplayName}: TempDB usage back to {pct}",
                        Hardcodet.Wpf.TaskbarNotification.BalloonIcon.Info);
                }
            }
            catch (Exception ex)
            {
                AppLogger.Error("Alerts", $"Failed to check TempDB space for {summary.DisplayName}: {ex.Message}");
            }
        }

        /* Anomalous Agent job alerts */
        if (App.AlertLongRunningJobEnabled && _dataService != null)
        {
            try
            {
                var anomalousJobs = await _dataService.GetAnomalousJobsAsync(summary.ServerId, App.AlertLongRunningJobMultiplier);

                if (anomalousJobs.Count > 0)
                {
                    _activeLongRunningJobAlert[key] = true;
                    var worst = anomalousJobs[0];
                    var jobKey = $"{key}:{worst.JobId}:{worst.StartTime:O}";

                    if (!suppressPopups && (!_lastLongRunningJobAlert.TryGetValue(jobKey, out var lastJob) || now - lastJob >= alertCooldown))
                    {
                        var currentMinutes = worst.CurrentDurationSeconds / 60;

                        var muteCtx = new AlertMuteContext { ServerName = summary.DisplayName, MetricName = "Long-Running Job", JobName = worst.JobName };
                        bool isMuted = _muteRuleService.IsAlertMuted(muteCtx);
                        _lastLongRunningJobAlert[jobKey] = now;

                        if (!isMuted)
                        {
                            _trayService.ShowSnoozableNotification(
                                "Long-Running Job",
                                $"{summary.DisplayName}: {worst.JobName} at {worst.PercentOfAverage:F0}% of avg ({currentMinutes}m)",
                                Hardcodet.Wpf.TaskbarNotification.BalloonIcon.Warning,
                                summary.DisplayName,
                                "Long-Running Job",
                                _muteRuleService);
                        }

                        var jobContext = BuildAnomalousJobContext(anomalousJobs);
                        var detailText = ContextToDetailText(jobContext);

                        await _emailAlertService.TrySendAlertEmailAsync(
                            "Long-Running Job",
                            summary.DisplayName,
                            $"{anomalousJobs.Count} job(s) exceeding {App.AlertLongRunningJobMultiplier}x average",
                            $"{App.AlertLongRunningJobMultiplier}x historical avg",
                            summary.ServerId,
                            jobContext,
                            numericCurrentValue: (double)(worst.PercentOfAverage ?? 0),
                            numericThresholdValue: App.AlertLongRunningJobMultiplier * 100,
                            muted: isMuted,
                            detailText: detailText);
                    }
                }
                else if (_activeLongRunningJobAlert.TryGetValue(key, out var wasJob) && wasJob)
                {
                    _activeLongRunningJobAlert[key] = false;
                    _trayService.ShowNotification(
                        "Long-Running Jobs Cleared",
                        $"{summary.DisplayName}: No jobs exceeding threshold",
                        Hardcodet.Wpf.TaskbarNotification.BalloonIcon.Info);
                }
            }
            catch (Exception ex)
            {
                AppLogger.Error("Alerts", $"Failed to check anomalous jobs for {summary.DisplayName}: {ex.Message}");
            }
        }
    }

        private static string TruncateText(string text, int maxLength = 300)
        {
            if (string.IsNullOrEmpty(text)) return "";
            text = text.Replace('\r', ' ').Replace('\n', ' ').Trim();
            return text.Length <= maxLength ? text : text.Substring(0, maxLength) + "...";
        }

        private static string? ContextToDetailText(AlertContext? context)
        {
            if (context == null || context.Details.Count == 0) return null;
            var sb = new System.Text.StringBuilder();
            foreach (var detail in context.Details)
            {
                if (sb.Length > 0) sb.AppendLine();
                sb.AppendLine(detail.Heading);
                foreach (var (label, value) in detail.Fields)
                    sb.AppendLine($"  {label}: {value}");
            }
            return sb.ToString().TrimEnd();
        }

        private async Task<AlertContext?> BuildBlockingContextAsync(int serverId)
        {
            try
            {
                if (_dataService == null) return null;

                var events = await _dataService.GetRecentBlockedProcessReportsAsync(serverId, hoursBack: 1);
                if (events == null || events.Count == 0) return null;

                if (App.AlertExcludedDatabases.Count > 0)
                {
                    events = events
                        .Where(e => string.IsNullOrEmpty(e.DatabaseName) ||
                            !App.AlertExcludedDatabases.Any(ex =>
                                string.Equals(ex, e.DatabaseName, StringComparison.OrdinalIgnoreCase)))
                        .ToList();
                    if (events.Count == 0) return null;
                }

                var context = new AlertContext();
                var firstXml = (string?)null;

                foreach (var e in events.Take(3))
                {
                    var item = new AlertDetailItem
                    {
                        Heading = $"Blocked #{e.BlockedSpid} by #{e.BlockingSpid}",
                        Fields = new()
                    };

                    if (!string.IsNullOrEmpty(e.DatabaseName))
                        item.Fields.Add(("Database", e.DatabaseName));
                    if (!string.IsNullOrEmpty(e.BlockedSqlText))
                        item.Fields.Add(("Blocked Query", TruncateText(e.BlockedSqlText)));
                    if (!string.IsNullOrEmpty(e.BlockingSqlText))
                        item.Fields.Add(("Blocking Query", TruncateText(e.BlockingSqlText)));
                    item.Fields.Add(("Wait Time", e.WaitTimeFormatted));
                    if (!string.IsNullOrEmpty(e.LockMode))
                        item.Fields.Add(("Lock Mode", e.LockMode));

                    context.Details.Add(item);
                    if (firstXml == null && e.HasReportXml)
                        firstXml = e.BlockedProcessReportXml;
                }

                if (!string.IsNullOrEmpty(firstXml))
                {
                    context.AttachmentXml = firstXml;
                    context.AttachmentFileName = "blocked_process_report.xml";
                }

                return context;
            }
            catch (Exception ex)
            {
                AppLogger.Error("EmailAlert", $"Failed to fetch blocking detail for email: {ex.Message}");
                return null;
            }
        }

        private async Task<AlertContext?> BuildDeadlockContextAsync(int serverId)
        {
            try
            {
                if (_dataService == null) return null;

                var deadlocks = await _dataService.GetRecentDeadlocksAsync(serverId, hoursBack: 1);
                if (deadlocks == null || deadlocks.Count == 0) return null;

                if (App.AlertExcludedDatabases.Count > 0)
                {
                    deadlocks = deadlocks
                        .Where(d => !IsDeadlockExcluded(d, App.AlertExcludedDatabases))
                        .ToList();
                    if (deadlocks.Count == 0) return null;
                }

                var context = new AlertContext();
                var firstGraph = (string?)null;

                foreach (var d in deadlocks.Take(3))
                {
                    var item = new AlertDetailItem
                    {
                        Heading = "Deadlock Victim",
                        Fields = new()
                    };

                    if (!string.IsNullOrEmpty(d.VictimSqlText))
                        item.Fields.Add(("Victim SQL", TruncateText(d.VictimSqlText)));
                    if (!string.IsNullOrEmpty(d.ProcessSummary))
                        item.Fields.Add(("Processes", d.ProcessSummary));

                    context.Details.Add(item);
                    if (firstGraph == null && d.HasDeadlockXml)
                        firstGraph = d.DeadlockGraphXml;
                }

                if (!string.IsNullOrEmpty(firstGraph))
                {
                    context.AttachmentXml = firstGraph;
                    context.AttachmentFileName = "deadlock_graph.xml";
                }

                return context;
            }
            catch (Exception ex)
            {
                AppLogger.Error("EmailAlert", $"Failed to fetch deadlock detail for email: {ex.Message}");
                return null;
            }
        }

        private static bool IsDeadlockExcluded(DeadlockRow row, List<string> excludedDatabases)
        {
            if (string.IsNullOrEmpty(row.DeadlockGraphXml)) return false;
            try
            {
                var doc = XElement.Parse(row.DeadlockGraphXml);
                var dbNames = doc.Descendants("process")
                    .Select(p => p.Attribute("currentdbname")?.Value)
                    .Where(n => !string.IsNullOrEmpty(n))
                    .Cast<string>()
                    .ToList();
                if (dbNames.Count == 0) return false;
                return dbNames.All(db => excludedDatabases.Any(e =>
                    string.Equals(e, db, StringComparison.OrdinalIgnoreCase)));
            }
            catch { return false; }
        }

        private static AlertContext? BuildPoisonWaitContext(List<PoisonWaitDelta> triggeredWaits)
        {
            if (triggeredWaits.Count == 0) return null;

            var context = new AlertContext();
            foreach (var w in triggeredWaits)
            {
                context.Details.Add(new AlertDetailItem
                {
                    Heading = w.WaitType,
                    Fields = new()
                    {
                        ("Avg ms/wait", $"{w.AvgMsPerWait:F1}"),
                        ("Delta wait ms", $"{w.DeltaMs:N0}"),
                        ("Delta tasks", $"{w.DeltaTasks:N0}")
                    }
                });
            }
            return context;
        }

        private static AlertContext? BuildLongRunningQueryContext(List<LongRunningQueryInfo> queries)
        {
            if (queries.Count == 0) return null;

            var context = new AlertContext();
            foreach (var q in queries.GetRange(0, Math.Min(3, queries.Count)))
            {
                var item = new AlertDetailItem
                {
                    Heading = $"Session #{q.SessionId} — {q.ElapsedSeconds / 60}m {q.ElapsedSeconds % 60}s",
                    Fields = new()
                };

                if (!string.IsNullOrEmpty(q.DatabaseName))
                    item.Fields.Add(("Database", q.DatabaseName));
                if (!string.IsNullOrEmpty(q.QueryText))
                    item.Fields.Add(("Query", TruncateText(q.QueryText)));
                item.Fields.Add(("CPU Time", $"{q.CpuTimeMs:N0} ms"));
                item.Fields.Add(("Reads", $"{q.Reads:N0}"));
                item.Fields.Add(("Writes", $"{q.Writes:N0}"));
                if (!string.IsNullOrEmpty(q.WaitType))
                    item.Fields.Add(("Wait Type", q.WaitType));
                if (q.BlockingSessionId.HasValue && q.BlockingSessionId.Value > 0)
                    item.Fields.Add(("Blocked By", $"Session #{q.BlockingSessionId.Value}"));

                context.Details.Add(item);
            }
            return context;
        }

        private static AlertContext? BuildTempDbSpaceContext(TempDbSpaceInfo tempDb)
        {
            var context = new AlertContext();
            context.Details.Add(new AlertDetailItem
            {
                Heading = $"TempDB — {tempDb.UsedPercent:F0}% Used",
                Fields = new()
                {
                    ("Total Reserved", $"{tempDb.TotalReservedMb:F0} MB"),
                    ("Unallocated", $"{tempDb.UnallocatedMb:F0} MB"),
                    ("User Objects", $"{tempDb.UserObjectReservedMb:F0} MB"),
                    ("Internal Objects", $"{tempDb.InternalObjectReservedMb:F0} MB"),
                    ("Version Store", $"{tempDb.VersionStoreReservedMb:F0} MB"),
                    ("Top Consumer", tempDb.TopConsumerSessionId > 0
                        ? $"Session #{tempDb.TopConsumerSessionId} ({tempDb.TopConsumerMb:F0} MB)"
                        : "None")
                }
            });
            return context;
        }

        private static AlertContext? BuildAnomalousJobContext(List<AnomalousJobInfo> jobs)
        {
            if (jobs.Count == 0) return null;

            var context = new AlertContext();
            foreach (var j in jobs.GetRange(0, Math.Min(3, jobs.Count)))
            {
                context.Details.Add(new AlertDetailItem
                {
                    Heading = j.JobName,
                    Fields = new()
                    {
                        ("Current Duration", FormatDuration(j.CurrentDurationSeconds)),
                        ("Avg Duration", FormatDuration(j.AvgDurationSeconds)),
                        ("P95 Duration", FormatDuration(j.P95DurationSeconds)),
                        ("% of Average", j.PercentOfAverage.HasValue ? $"{j.PercentOfAverage:F0}%" : "N/A"),
                        ("Started", j.StartTime.ToString("yyyy-MM-dd HH:mm:ss"))
                    }
                });
            }
            return context;
        }

        private static string FormatDuration(long seconds)
        {
            if (seconds < 60) return $"{seconds}s";
            if (seconds < 3600) return $"{seconds / 60}m {seconds % 60}s";
            return $"{seconds / 3600}h {(seconds % 3600) / 60}m";
        }

    }
