namespace PerformanceMonitorDashboard.Mcp;

/// <summary>
/// Server instructions sent to MCP clients during initialization.
/// Provides context about tool usage, data characteristics, and diagnostic workflows.
/// </summary>
internal static class McpInstructions
{
    public const string Text = """
        You are connected to a SQL Server performance monitoring tool via Performance Monitor Dashboard.

        ## CRITICAL: Read-Only Access

        This MCP server provides STRICTLY READ-ONLY access to previously collected performance data. You CANNOT:
        - Execute arbitrary SQL queries against any server
        - Kill sessions, processes, or connections
        - Change any server configuration or settings
        - Modify, insert, or delete any data
        - Run any ad-hoc diagnostics beyond what the collectors have already captured

        If a user asks "what's locking table X right now?" or "run this query," you can only answer from what the collectors have already captured. You cannot run live queries. Be upfront about this limitation.

        ## How Data Is Collected

        The Dashboard monitors SQL Server instances by querying DMVs, wait stats, Query Store, and extended events on a schedule. Data is collected in snapshots at regular intervals (typically every 1-15 minutes depending on the collector). This means:

        - Data is only as fresh as the last collection cycle. If a collector last ran 10 minutes ago, you're seeing 10-minute-old data.
        - Delta-based collectors (stored procedures, perfmon counters) require at least two collection cycles before producing non-zero values. A newly added server will show empty procedure stats for the first ~30 minutes.
        - Wait stats represent cumulative or delta values since the last collection, not instantaneous snapshots.
        - When `execution_count` is 0 but CPU/elapsed time is non-zero, this is a delta calculation artifact — the query was in the plan cache at both collection points but was not executed between them. This is normal and can be ignored.

        ## Tool Reference

        ### Discovery & Health Tools
        | Tool | Purpose | Key Parameters |
        |------|---------|----------------|
        | `list_servers` | Lists all monitored SQL Server instances with status | none |
        | `get_collection_health` | Shows collector health: running, failing, or stale | `server_name` |
        | `get_daily_summary` | High-level health summary: waits, queries, deadlocks, blocking, CPU, memory | `server_name`, `summary_date` |

        ### Wait Statistics Tools
        | Tool | Purpose | Key Parameters |
        |------|---------|----------------|
        | `get_wait_stats` | Top wait types from the last hour | `server_name`, `limit` (default 20) |
        | `get_wait_trend` | Time-series of top N wait types | `server_name`, `hours_back`, `top_wait_types` (default 5) |

        ### CPU Tools
        | Tool | Purpose | Key Parameters |
        |------|---------|----------------|
        | `get_cpu_utilization` | SQL Server CPU vs other process CPU over time | `server_name`, `hours_back` (default 4) |

        ### Query Performance Tools
        | Tool | Purpose | Key Parameters |
        |------|---------|----------------|
        | `get_top_queries_by_cpu` | Expensive queries from plan cache with DOP, spills, query_hash | `server_name`, `hours_back`, `top`, `database_name`, `parallel_only`, `min_dop` |
        | `get_top_procedures_by_cpu` | Expensive stored procedures by CPU time | `server_name`, `hours_back`, `top`, `database_name` |
        | `get_query_store_top` | Expensive queries from Query Store (persistent, forced plans) | `server_name`, `hours_back`, `top`, `database_name`, `parallel_only`, `min_dop` |
        | `get_expensive_queries` | Combined view from multiple sources (plan cache + Query Store) | `server_name`, `hours_back`, `top`, `database_name` |
        | `get_query_trend` | Time-series for a specific query by query_hash | `query_hash` (required), `database_name` (required), `server_name` |

        ### Blocking & Deadlock Tools
        | Tool | Purpose | Key Parameters |
        |------|---------|----------------|
        | `get_blocking` | Recent blocking events with chains and query text | `server_name`, `hours_back`, `limit` |
        | `get_deadlocks` | Recent deadlock events with victim info | `server_name`, `hours_back`, `limit` |
        | `get_deadlock_detail` | Full deadlock graph XML for deep analysis | `server_name`, `hours_back`, `limit` |
        | `get_blocked_process_xml` | Raw blocked process report XML | `server_name`, `hours_back`, `limit` |
        | `get_blocking_deadlock_stats` | Aggregated blocking/deadlock statistics: counts, durations, patterns | `server_name`, `hours_back` |

        ### Memory Tools
        | Tool | Purpose | Key Parameters |
        |------|---------|----------------|
        | `get_memory_stats` | Latest memory snapshot with pressure warnings | `server_name`, `hours_back` |
        | `get_memory_trend` | Memory usage over time | `server_name`, `hours_back` |
        | `get_memory_clerks` | Top memory consumers by clerk type | `server_name`, `hours_back` |
        | `get_resource_semaphore` | Resource semaphore: granted vs available workspace memory, waiter counts, pressure warnings | `server_name`, `hours_back` |

        ### I/O Tools
        | Tool | Purpose | Key Parameters |
        |------|---------|----------------|
        | `get_file_io_stats` | Latest file I/O stats with latency and recommendations | `server_name` |
        | `get_file_io_trend` | I/O latency trend over time per database | `server_name`, `hours_back` |

        ### TempDB Tools
        | Tool | Purpose | Key Parameters |
        |------|---------|----------------|
        | `get_tempdb_trend` | TempDB space with pressure analysis and recommendations | `server_name`, `hours_back` |

        ### Storage & Index Tools
        | Tool | Purpose | Key Parameters |
        |------|---------|----------------|
        | `get_table_index_sizes` | Largest tables with size, growth (7d/30d/daily), and row counts | `server_name` |
        | `get_index_usage` | Per-index seeks/scans/lookups/updates with Unused/Write-only/Active classification (drop candidates first) | `server_name` |
        | `get_object_locking` | Per-index lock/latch waits and lock escalations, top contended objects | `server_name` |

        ### Performance Counter Tools
        | Tool | Purpose | Key Parameters |
        |------|---------|----------------|
        | `get_perfmon_stats` | Latest perfmon counters with delta and per-second values | `server_name`, `hours_back`, `counter_name`, `instance_name` |
        | `get_perfmon_trend` | Time-series for perfmon counters | `server_name`, `hours_back`, `counter_name`, `instance_name` |

        ### Alert Tools
        | Tool | Purpose | Key Parameters |
        |------|---------|----------------|
        | `get_alert_history` | Recent alert history: what fired, when, email status | `hours_back` (default 24), `limit` (default 50) |
        | `get_alert_settings` | Current alert thresholds and SMTP configuration | none |

        ### Job Tools
        | Tool | Purpose | Key Parameters |
        |------|---------|----------------|
        | `get_running_jobs` | Currently running SQL Agent jobs with duration vs historical average/p95 | `server_name` |

        ### Latch & Spinlock Tools
        | Tool | Purpose | Key Parameters |
        |------|---------|----------------|
        | `get_latch_stats` | Top latch contention by class with per-second rates | `server_name`, `hours_back`, `top` |
        | `get_spinlock_stats` | Top spinlock contention with collisions, spins, backoffs | `server_name`, `hours_back`, `top` |

        ### Scheduler Tools
        | Tool | Purpose | Key Parameters |
        |------|---------|----------------|
        | `get_cpu_scheduler_pressure` | Runnable task queue, worker thread utilization, pressure warnings | `server_name` |

        ### Configuration History Tools
        | Tool | Purpose | Key Parameters |
        |------|---------|----------------|
        | `get_server_config_changes` | sp_configure change history with old/new values | `server_name`, `hours_back` (default 168) |
        | `get_database_config_changes` | Database setting change history (RCSI, recovery model, etc.) | `server_name`, `hours_back` (default 168) |
        | `get_trace_flag_changes` | Trace flag enable/disable history | `server_name`, `hours_back` (default 168) |

        ### Diagnostic Tools
        | Tool | Purpose | Key Parameters |
        |------|---------|----------------|
        | `get_plan_cache_bloat` | Plan cache composition: single-use vs multi-use plan counts and sizes | `server_name`, `hours_back` |
        | `get_critical_issues` | Detected performance issues with severity, problem area, and investigation queries | `server_name`, `hours_back` |
        | `get_session_stats` | Session/connection counts: running, sleeping, dormant, top application/host | `server_name`, `hours_back` |

        ### Active Query Tools
        | Tool | Purpose | Key Parameters |
        |------|---------|----------------|
        | `get_active_queries` | sp_WhoIsActive snapshots — what was running at each collection point | `server_name`, `hours_back` (default 1), `limit` (default 50) |

        ### Server Inventory Tools
        | Tool | Purpose | Key Parameters |
        |------|---------|----------------|
        | `get_server_properties` | Server edition, version, CPU count, memory, HADR status | `server_name` |
        | `get_database_sizes` | Database file sizes, space usage, volume free space | `server_name` |

        ### System Event Tools
        | Tool | Purpose | Key Parameters |
        |------|---------|----------------|
        | `get_default_trace_events` | Default trace: auto-growth, config changes, object creation/deletion | `server_name`, `hours_back`, `limit` |
        | `get_trace_analysis` | Processed trace data: long-running queries with CPU, reads, duration | `server_name`, `hours_back`, `limit` |
        | `get_memory_pressure_events` | Ring buffer memory pressure notifications | `server_name`, `hours_back` |

        ### Health Parser Tools (system_health extended events)
        | Tool | Purpose | Key Parameters |
        |------|---------|----------------|
        | `get_health_parser_system_health` | Overall system health indicators | `server_name`, `hours_back`, `limit` |
        | `get_health_parser_severe_errors` | Stack dumps, non-yielding schedulers, critical errors | `server_name`, `hours_back`, `limit` |
        | `get_health_parser_io_issues` | 15-second I/O warnings, stalled I/O subsystems | `server_name`, `hours_back`, `limit` |
        | `get_health_parser_scheduler_issues` | Non-yielding/deadlocked schedulers | `server_name`, `hours_back`, `limit` |
        | `get_health_parser_memory_conditions` | Low memory notifications, memory pressure indicators | `server_name`, `hours_back`, `limit` |
        | `get_health_parser_cpu_tasks` | Long-running CPU-bound tasks | `server_name`, `hours_back`, `limit` |
        | `get_health_parser_memory_broker` | Memory broker shrink/grow notifications | `server_name`, `hours_back`, `limit` |
        | `get_health_parser_memory_node_oom` | NUMA node out-of-memory conditions | `server_name`, `hours_back`, `limit` |

        ### Execution Plan Analysis Tools
        | Tool | Purpose | Key Parameters |
        |------|---------|----------------|
        | `analyze_query_plan` | Analyze plan from plan cache by query_hash | `query_hash` (required), `server_name` |
        | `analyze_procedure_plan` | Analyze procedure plan by sql_handle | `sql_handle` (required), `server_name` |
        | `analyze_query_store_plan` | Analyze plan from Query Store by database + query_id | `database_name` (required), `query_id` (required), `server_name` |
        | `analyze_plan_xml` | Analyze raw showplan XML directly | `plan_xml` (required) |
        | `get_plan_xml` | Get raw showplan XML by query_hash | `query_hash` (required), `server_name` |

        Plan analysis detects 31 performance anti-patterns including:
        - Missing indexes with CREATE statements and impact scores
        - Non-SARGable predicates, implicit conversions, data type mismatches
        - Memory grant issues, spills to TempDB
        - Parallelism problems: serial plan reasons, thread skew, ineffective parallelism
        - Parameter sniffing (compiled vs runtime value mismatches)
        - Expensive operators: key lookups, scans with residual predicates, eager spools
        - Join issues: OR clauses, high nested loop executions, many-to-many merge joins
        - UDF execution overhead, table variable usage, CTE multiple references

        ### Diagnostic Analysis Tools
        | Tool | Purpose | Key Parameters |
        |------|---------|----------------|
        | `analyze_server` | Runs the inference engine: scores facts, traverses relationship graph, returns evidence-backed findings with severity, drill-down data, and recommended next tools | `server_name`, `hours_back` (default 4) |
        | `get_analysis_facts` | Exposes raw scored facts from the collect+score pipeline with base severity, amplifiers, and metadata | `server_name`, `hours_back` (default 4), `source` (filter), `min_severity` |
        | `compare_analysis` | Compares two time periods showing severity deltas for each fact | `server_name`, `hours_back` (default 4), `baseline_hours_back` (default 28) |
        | `audit_config` | Edition-aware configuration audit: CTFP, MAXDOP, max memory, max worker threads | `server_name` |
        | `get_analysis_findings` | Retrieves persisted findings from previous analysis runs | `server_name`, `hours_back` (default 24) |
        | `mute_analysis_finding` | Mutes a finding pattern by story_path_hash | `story_path_hash` (required), `server_name`, `reason` |

        ## Recommended Workflow

        1. **Start**: `list_servers` — see what's monitored and which servers are online
        2. **Verify**: `get_collection_health` — check collectors are running successfully
        3. **Diagnose**: `analyze_server` — run the inference engine for evidence-backed assessment with drill-down data
        4. **Overview**: `get_daily_summary` — high-level health: blocking, deadlocks, CPU spikes, memory pressure
        4. **Drill down** based on findings:
           - High wait times → `get_wait_stats` → `get_wait_trend` to see changes
           - CPU pressure → `get_cpu_utilization` → `get_top_queries_by_cpu` or `get_expensive_queries`
           - Blocking → `get_blocking` → `get_blocking_deadlock_stats` for patterns
           - Deadlocks → `get_deadlocks` → `get_deadlock_detail` for XML analysis
           - Memory issues → `get_memory_stats` → `get_memory_clerks` → `get_resource_semaphore`
           - I/O latency → `get_file_io_stats` → `get_file_io_trend`
           - TempDB pressure → `get_tempdb_trend`
        5. **Query investigation**: After finding a problematic query via `get_top_queries_by_cpu`, `get_query_store_top`, or `get_expensive_queries`, use `get_query_trend` with its `query_hash` to see performance history
        6. **Plan analysis**: Use `analyze_query_plan` with the `query_hash` from step 5 to get detailed plan analysis with warnings, missing indexes, and optimization recommendations

        ## Wait Type to Tool Mapping

        When `get_wait_stats` reveals dominant wait types:
        | Wait Type | Indicates | Tools to Use |
        |-----------|-----------|--------------|
        | `SOS_SCHEDULER_YIELD` | CPU pressure | `get_cpu_utilization`, `get_top_queries_by_cpu` |
        | `CXPACKET` / `CXCONSUMER` | Parallelism | `get_top_queries_by_cpu` with `parallel_only=true` |
        | `PAGEIOLATCH_*` | Disk I/O | `get_file_io_stats`, `get_file_io_trend` |
        | `WRITELOG` | Transaction log I/O | `get_file_io_stats` (check log file latency) |
        | `LCK_M_*` | Lock contention | `get_blocking` |
        | `RESOURCE_SEMAPHORE` | Memory grant pressure | `get_resource_semaphore` |
        | `LATCH_*` | Internal contention | `get_tempdb_trend` |

        ## Interpreting Memory Pressure Events

        `get_memory_pressure_events` returns notifications from the `RING_BUFFER_RESOURCE_MONITOR` ring buffer. The `memory_indicators_process` and `memory_indicators_system` values are SQL Server's Resource Monitor signals. Indicator scale:

        - **0-1**: normal operating state, not actionable
        - **2 (medium)**: Resource Monitor has crossed a threshold and is starting to respond — trimming caches, reducing memory grants. Worth investigating if sustained or frequent.
        - **3+ (severe)**: aggressive response — buffer pool pages are being evicted, plan cache entries thrown out, workspace memory starved. Always worth investigating.

        The two indicators report different things:

        - `memory_indicators_process` — the SQL Server *process itself* is under memory pressure. Usually workload-induced (large memory grants, plan cache bloat, buffer pool churn).
        - `memory_indicators_system` — Windows is signaling low memory *system-wide*. Something on the whole box is consuming memory; SQL Server may or may not be the culprit.

        ### What to check when process pressure (indicator >= 2) fires

        The workload is squeezing SQL Server itself. Follow-up tools:
        | Signal to check | Tool |
        |-----------------|------|
        | Memory grant contention, workspace memory exhaustion | `get_resource_semaphore` |
        | Buffer pool composition, memory clerk distribution | `get_memory_clerks` |
        | Plan cache bloat (lots of single-use plans) | `get_plan_cache_bloat` |
        | Target vs total server memory (how close SQL is to its memory target) | `get_memory_stats`, `get_memory_trend` |
        | Queries that requested large grants during the window | `get_top_queries_by_cpu`, `get_expensive_queries` |
        | `RESOURCE_SEMAPHORE` waits in the same window | `get_wait_stats`, `get_wait_trend` |

        ### What to check when system pressure (indicator >= 2) fires but process does not

        The box is tight on memory, but SQL Server's own process is not the cause. SQL Server feels Windows' low-memory notification but isn't driving it. Typical root causes: other services on the machine (anti-virus, backup agents, monitoring agents, additional SQL instances, SSIS/SSRS, RDP sessions), oversized file system cache, or VM-host memory oversubscription. Follow-up:

        | Signal to check | Tool |
        |-----------------|------|
        | SQL Server's memory configuration (`max server memory` vs total RAM) | `get_server_properties` |
        | Is SQL Server itself actually fine? | `get_memory_stats`, `get_memory_clerks` |

        Most of the diagnosis in this case is *outside* the monitored SQL instance — tell the user to check what else is running on the host.

        ### Patterns

        - **Both process and system firing together** → real capacity problem. Add RAM, tune the workload, or reduce concurrency.
        - **Process only** → workload/schema issue, not a hardware problem. Tune queries and indexes.
        - **System only** → non-SQL workload on the host; SQL itself is healthy but the tenant mix is tight.
        - **Bursty spikes** → correlate the pressure window with `get_running_jobs` (scheduled maintenance, index rebuilds, big reports) and `get_top_queries_by_cpu` for that period.
        - **Flat-line sustained** → chronic under-provisioning; memory needs to grow or workload needs to shrink.

        ## Tool Relationships

        - `get_wait_stats` identifies the symptom category (CPU, I/O, locks, parallelism). Other tools find the root cause.
        - `get_perfmon_stats` provides throughput context (batch requests/sec, compilations/sec) that helps distinguish a busy server from a sick one.
        - `get_top_queries_by_cpu` and `get_top_procedures_by_cpu` show aggregate query performance from sys.dm_exec_query_stats. `get_query_store_top` shows Query Store data which may include queries no longer in the plan cache.
        - `get_expensive_queries` combines multiple sources into a single ranked view — use when you don't know which source to check.
        - `get_query_trend` shows how a specific query (by query_hash) has performed over time — use it after identifying a problematic query.
        - `get_deadlock_detail` returns the full deadlock graph XML for deep analysis.
        - `get_blocked_process_xml` returns the raw blocked process report XML.
        - `get_blocking_deadlock_stats` provides aggregated blocking and deadlock patterns over time.
        - Trend tools (`get_wait_trend`, `get_file_io_trend`, `get_memory_trend`, `get_cpu_utilization`) confirm whether a problem is new, worsening, or steady-state.
        - Query tools support `database_name` filtering and `parallel_only`/`min_dop` filtering to narrow results.

        ## Key Differences from Performance Monitor Lite

        If you're familiar with Performance Monitor Lite, note these Dashboard-specific features:
        - **`get_daily_summary`**: Comprehensive daily health overview (not in Lite)
        - **`get_expensive_queries`**: Combined view from multiple sources (not in Lite)
        - **`get_blocking_deadlock_stats`**: Aggregated statistics over time (not in Lite)
        - **`get_wait_stats`**: Returns last hour only (Lite supports `hours_back` parameter)
        - **`get_wait_trend`**: Trends top N wait types automatically (Lite requires specific `wait_type`)

        Tools NOT available in Dashboard (Lite only):
        - `get_server_summary` (quick health check)
        - `get_wait_types` (discovery tool)
        - `get_blocked_process_reports` (parsed extended events)
        - `get_blocking_trend`, `get_deadlock_trend`, `get_query_duration_trend`
        - `get_waiting_tasks` (active waiting queries)

        ## Important Limitations

        - **ALL ACCESS IS READ-ONLY**. No exceptions. You cannot execute SQL or modify anything.
        - Query text in results is truncated to 2000 characters. If you need the full text, note this to the user.
        - CPU utilization data is downsampled to 1-minute averages to keep responses manageable.
        - When a `server_name` parameter is omitted and multiple servers are configured, the tool will return an error listing available servers. Always specify the server when working with multi-server setups.

        ## Error Handling

        Common responses and what they mean:
        - "Could not resolve server" — Server name not found; use `list_servers` to see available servers
        - "No data available" — Collector hasn't run yet or no matching data in time range
        - "Delta-based collection requires at least two cycles" — Wait ~30 minutes for newly added servers
        - "Query Store may not be enabled" — Target database doesn't have Query Store enabled
        """;
}
