using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using DuckDB.NET.Data;
using PerformanceMonitor.Analysis;
using PerformanceMonitorLite.Database;

namespace PerformanceMonitorLite.Analysis;

/// <summary>
/// Seeds DuckDB with synthetic data for controlled analysis testing.
/// Each scenario method clears test data and inserts known values
/// so engine output is deterministic and verifiable.
/// Only available when analysis is enabled.
/// </summary>
public class TestDataSeeder
{
    private readonly DuckDbInitializer _duckDb;

    /// <summary>
    /// Negative server_id to avoid collisions with real servers (hash-based positive IDs).
    /// </summary>
    public const int TestServerId = -999;
    public const string TestServerName = "TestServer-ErikAI";

    /// <summary>
    /// Test scenarios use a 4-hour window ending "now" so the data
    /// falls within any reasonable time range query.
    /// Captured once so all references use identical boundaries.
    /// </summary>
    private static readonly DateTime _periodEnd = DateTime.UtcNow;
    public static DateTime TestPeriodEnd => _periodEnd;
    public static DateTime TestPeriodStart => _periodEnd.AddHours(-4);
    public static double TestPeriodDurationMs => (TestPeriodEnd - TestPeriodStart).TotalMilliseconds;

    /// <summary>
    /// Baseline period for anomaly detection: 24 hours before the analysis window.
    /// </summary>
    public static DateTime BaselineStart => TestPeriodStart.AddHours(-24);
    public static DateTime BaselineEnd => TestPeriodStart;

    private long _nextId = -1_000_000;

    public TestDataSeeder(DuckDbInitializer duckDb)
    {
        _duckDb = duckDb;
    }

    /// <summary>
    /// Builds an AnalysisContext matching the test data time range.
    /// </summary>
    public static AnalysisContext CreateTestContext()
    {
        return new AnalysisContext
        {
            ServerId = TestServerId,
            ServerName = TestServerName,
            TimeRangeStart = TestPeriodStart,
            TimeRangeEnd = TestPeriodEnd
        };
    }

    /// <summary>
    /// Memory-starved server: high PAGEIOLATCH, moderate SOS, some CXPACKET.
    /// Buffer pool undersized, max memory misconfigured.
    ///
    /// Expected stories:
    ///   PAGEIOLATCH_SH → buffer_pool → max_memory → physical_memory
    ///
    /// Wait fractions (of 4-hour period = 14,400,000 ms):
    ///   PAGEIOLATCH_SH:        10,000,000 ms = 69.4%
    ///   SOS_SCHEDULER_YIELD:    3,000,000 ms = 20.8%
    ///   CXPACKET:               1,500,000 ms = 10.4%
    ///   WRITELOG:                  200,000 ms =  1.4%
    /// </summary>
    public async Task SeedMemoryStarvedServerAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        var waits = new Dictionary<string, (long waitTimeMs, long waitingTasks, long signalMs)>
        {
            ["PAGEIOLATCH_SH"]      = (10_000_000, 5_000_000, 100_000),
            ["PAGEIOLATCH_EX"]      = (   500_000,   200_000,  10_000),
            ["SOS_SCHEDULER_YIELD"] = ( 3_000_000, 8_000_000,       0),
            ["CXPACKET"]            = ( 1_500_000, 2_000_000,       0),
            ["WRITELOG"]            = (   200_000,   100_000,  20_000),
        };

        await SeedWaitStatsAsync(waits);
        await SeedServerConfigAsync(ctfp: 50, maxdop: 8, maxMemoryMb: 57344);
        await SeedMemoryStatsAsync(totalPhysicalMb: 65_536, bufferPoolMb: 56_000, targetMb: 57_344);
        await SeedFileSizeAsync(totalDataSizeMb: 512_000); // 500GB data on 64GB RAM
        await SeedServerEditionAsync(edition: 2, majorVersion: 16); // Standard 2022

        // Corroborating context from new collectors
        await SeedCpuUtilizationAsync(85, 5);
        await SeedIoLatencyAsync(totalReads: 1_000_000, stallReadMs: 35_000_000, // 35ms avg read
                                  totalWrites: 200_000, stallWriteMs: 2_000_000); // 10ms avg write
        await SeedPerfmonAsync();
        await SeedMemoryClerksAsync(new Dictionary<string, double>
        {
            ["MEMORYCLERK_SQLBUFFERPOOL"] = 54_000,
            ["MEMORYCLERK_SQLQUERYPLAN"] = 1_500,
        });
        await SeedTempDbAsync(reservedMb: 600, unallocatedMb: 400); // 60% — moderate
        await SeedMemoryGrantsAsync(maxWaiters: 3);
        await SeedServerPropertiesAsync(cpuCount: 16, htRatio: 2, physicalMemMb: 65_536);
        await SeedDiskSpaceAsync(("D:\\", 1_000_000, 150_000)); // 15% free
    }

    /// <summary>
    /// Bad parallelism config: CTFP=5, MAXDOP=0, high CX and SOS waits.
    ///
    /// Expected stories:
    ///   CXPACKET → parallelism_config → CTFP(5), MAXDOP(0)
    ///
    /// Wait fractions (of 4-hour period):
    ///   CXPACKET:               8,000,000 ms = 55.6%
    ///   SOS_SCHEDULER_YIELD:    6,000,000 ms = 41.7%
    ///   CXCONSUMER:             2,000,000 ms = 13.9%
    /// </summary>
    public async Task SeedBadParallelismServerAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        var waits = new Dictionary<string, (long waitTimeMs, long waitingTasks, long signalMs)>
        {
            ["CXPACKET"]            = (8_000_000, 4_000_000,       0),
            ["SOS_SCHEDULER_YIELD"] = (6_000_000, 12_000_000,      0),
            ["CXCONSUMER"]          = (2_000_000, 1_000_000,       0),
            ["THREADPOOL"]          = (   50_000,       20,        0),
        };

        await SeedWaitStatsAsync(waits);
        await SeedServerConfigAsync(ctfp: 5, maxdop: 0); // Bad defaults
        await SeedMemoryStatsAsync(totalPhysicalMb: 131_072, bufferPoolMb: 122_880, targetMb: 122_880);
        await SeedFileSizeAsync(totalDataSizeMb: 204_800); // 200GB
        await SeedServerEditionAsync(edition: 3, majorVersion: 16); // Enterprise 2022

        // Corroborating context: high CPU, high DOP queries
        await SeedCpuUtilizationAsync(90, 5);
        await SeedQueryStatsAsync(totalSpills: 500, highDopQueryCount: 15);
        await SeedServerPropertiesAsync(cpuCount: 32, htRatio: 2, physicalMemMb: 131_072,
            edition: "Enterprise Edition");
        await SeedPerfmonAsync();
    }

    /// <summary>
    /// Clean server: low waits across the board. Should produce only absolution.
    ///
    /// Wait fractions (of 4-hour period):
    ///   SOS_SCHEDULER_YIELD:      100,000 ms = 0.7%
    ///   WRITELOG:                   50,000 ms = 0.3%
    ///   PAGEIOLATCH_SH:            30,000 ms = 0.2%
    /// </summary>
    public async Task SeedCleanServerAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        var waits = new Dictionary<string, (long waitTimeMs, long waitingTasks, long signalMs)>
        {
            ["SOS_SCHEDULER_YIELD"] = (100_000, 500_000,     0),
            ["WRITELOG"]            = ( 50_000,  30_000, 5_000),
            ["PAGEIOLATCH_SH"]      = ( 30_000,  15_000, 1_000),
        };

        await SeedWaitStatsAsync(waits);
        await SeedServerConfigAsync(ctfp: 50, maxdop: 8, maxMemoryMb: 122_880);
        await SeedMemoryStatsAsync(totalPhysicalMb: 131_072, bufferPoolMb: 100_000, targetMb: 122_880);
        await SeedFileSizeAsync(totalDataSizeMb: 102_400); // 100GB
        await SeedServerEditionAsync(edition: 3, majorVersion: 16); // Enterprise 2022

        // Clean server context — all healthy values (very low to keep severities near zero)
        await SeedCpuUtilizationAsync(5, 3);
        await SeedIoLatencyAsync(totalReads: 500_000, stallReadMs: 500_000, // 1ms avg read
                                  totalWrites: 200_000, stallWriteMs: 100_000); // 0.5ms avg write
        await SeedTempDbAsync(reservedMb: 100, unallocatedMb: 900); // 10% — healthy
        await SeedPerfmonAsync();
        await SeedDatabaseConfigAsync(
            ("AppDB1", true, false, false, "CHECKSUM"),
            ("AppDB2", true, false, false, "CHECKSUM"));
        await SeedServerPropertiesAsync(cpuCount: 16, htRatio: 2, physicalMemMb: 131_072,
            edition: "Enterprise Edition");
        await SeedDiskSpaceAsync(("D:\\", 2_000_000, 900_000)); // 45% free — healthy
    }

    /// <summary>
    /// Thread exhaustion: THREADPOOL dominant with CXPACKET as root cause.
    /// The "emergency — connect via DAC" scenario. Parallel queries consumed
    /// the entire worker thread pool.
    ///
    /// Expected stories:
    ///   THREADPOOL → CXPACKET → SOS_SCHEDULER_YIELD
    ///
    /// Wait fractions (of 4-hour period):
    ///   THREADPOOL:              5,400,000 ms = 37.5%  (avg 270s/wait — severe)
    ///   CXPACKET:                5,000,000 ms = 34.7%
    ///   SOS_SCHEDULER_YIELD:     4,000,000 ms = 27.8%
    ///   CXCONSUMER:              1,000,000 ms =  6.9%
    /// </summary>
    public async Task SeedThreadExhaustionServerAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        var waits = new Dictionary<string, (long waitTimeMs, long waitingTasks, long signalMs)>
        {
            ["THREADPOOL"]          = (5_400_000,     4_000,       0), // avg 1350ms/wait, >1h and >1s floors
            ["CXPACKET"]            = (5_000_000, 3_000_000,       0),
            ["SOS_SCHEDULER_YIELD"] = (4_000_000, 9_000_000,       0),
            ["CXCONSUMER"]          = (1_000_000,   500_000,       0),
        };

        await SeedWaitStatsAsync(waits);
    }

    /// <summary>
    /// Blocking-driven thread exhaustion: THREADPOOL caused by heavy lock contention.
    /// Stuck queries holding exclusive locks, consuming all available worker threads.
    /// Unlike the parallelism scenario, this is caused by blocking, not DOP.
    ///
    /// Expected stories:
    ///   THREADPOOL → LCK (blocking holding threads)
    ///
    /// Wait fractions (of 4-hour period):
    ///   THREADPOOL:              5,400,000 ms = 37.5%  (avg 270s/wait — severe)
    ///   LCK_M_X:                 4,000,000 ms = 27.8%
    ///   LCK_M_U:                 2,000,000 ms = 13.9%
    ///   LCK_M_IX:                  800,000 ms =  5.6%
    ///   SOS_SCHEDULER_YIELD:       500,000 ms =  3.5%
    /// </summary>
    public async Task SeedBlockingThreadExhaustionServerAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        var waits = new Dictionary<string, (long waitTimeMs, long waitingTasks, long signalMs)>
        {
            ["THREADPOOL"]          = (5_400_000,     4_000,       0), // avg 1350ms/wait, >1h and >1s floors
            ["LCK_M_X"]            = (4_000_000,   300_000,  50_000),
            ["LCK_M_U"]            = (2_000_000,   150_000,  25_000),
            ["LCK_M_IX"]           = (  800_000,   400_000,  10_000),
            ["SOS_SCHEDULER_YIELD"] = (  500_000, 2_000_000,       0),
        };

        await SeedWaitStatsAsync(waits);
        // 200 blocking events (~50/hr) — heavy, at critical threshold
        await SeedBlockingEventsAsync(200, avgWaitTimeMs: 60_000, sleepingBlockerCount: 40, distinctBlockers: 8);
        // 15 deadlocks (~3.75/hr) — escalating
        await SeedDeadlocksAsync(15);
    }

    /// <summary>
    /// Heavy lock contention: LCK_M_X and LCK_M_U dominant.
    /// Writers blocking writers — classic OLTP contention pattern.
    ///
    /// Expected stories:
    ///   LCK_M_X (exclusive lock waits, highest)
    ///   LCK_M_U (update lock waits)
    ///
    /// Wait fractions (of 4-hour period):
    ///   LCK_M_X:                 3,000,000 ms = 20.8%
    ///   LCK_M_U:                 1,500,000 ms = 10.4%
    ///   LCK_M_IX:                  800,000 ms =  5.6%
    ///   SOS_SCHEDULER_YIELD:       500,000 ms =  3.5%
    ///   WRITELOG:                   400,000 ms =  2.8%
    /// </summary>
    public async Task SeedLockContentionServerAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        var waits = new Dictionary<string, (long waitTimeMs, long waitingTasks, long signalMs)>
        {
            ["LCK_M_X"]            = (3_000_000,   200_000,  50_000),
            ["LCK_M_U"]            = (1_500_000,   100_000,  25_000),
            ["LCK_M_IX"]           = (  800_000,   300_000,  10_000),
            ["SOS_SCHEDULER_YIELD"] = (  500_000, 2_000_000,       0),
            ["WRITELOG"]            = (  400_000,   200_000,  30_000),
        };

        await SeedWaitStatsAsync(waits);
        // 60 blocking events (~15/hr) — confirmed write-write blocking
        await SeedBlockingEventsAsync(60, avgWaitTimeMs: 30_000, sleepingBlockerCount: 5, distinctBlockers: 4);
    }

    /// <summary>
    /// Reader/writer blocking: LCK_M_S and LCK_M_IS dominant.
    /// Readers blocked by writers — the "enable RCSI" scenario.
    ///
    /// Expected stories:
    ///   LCK_M_S → recommendation to enable RCSI
    ///   LCK_M_IS
    ///
    /// Wait fractions (of 4-hour period):
    ///   LCK_M_S:                 4,000,000 ms = 27.8%
    ///   LCK_M_IS:                2,000,000 ms = 13.9%
    ///   LCK_M_X:                   500,000 ms =  3.5%
    ///   WRITELOG:                   300,000 ms =  2.1%
    /// </summary>
    public async Task SeedReaderWriterBlockingServerAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        var waits = new Dictionary<string, (long waitTimeMs, long waitingTasks, long signalMs)>
        {
            ["LCK_M_S"]  = (4_000_000,   800_000,  40_000),
            ["LCK_M_IS"] = (2_000_000,   500_000,  20_000),
            ["LCK_M_X"]  = (  500_000,    30_000,   5_000),
            ["WRITELOG"]  = (  300_000,   150_000,  25_000),
        };

        await SeedWaitStatsAsync(waits);
        // 40 blocking events (~10/hr) — reader/writer blocking confirmed
        await SeedBlockingEventsAsync(40, avgWaitTimeMs: 20_000, sleepingBlockerCount: 3, distinctBlockers: 6);
        // 8 deadlocks (~2/hr) — reader/writer deadlocks (RCSI would eliminate)
        await SeedDeadlocksAsync(8);

        // RCSI off on multiple databases — the key recommendation
        await SeedDatabaseConfigAsync(
            ("AppDB1", false, false, false, "CHECKSUM"),
            ("AppDB2", false, false, false, "CHECKSUM"),
            ("ReportDB", false, false, false, "CHECKSUM"));
    }

    /// <summary>
    /// Serializable isolation abuse: range lock modes present.
    /// Someone has SERIALIZABLE on a high-traffic table — unnecessary and destructive.
    ///
    /// Expected stories:
    ///   LCK_M_RIn_X → "SERIALIZABLE or REPEATABLE READ isolation"
    ///   LCK_M_RS_S
    ///
    /// Wait fractions (of 4-hour period):
    ///   LCK_M_RIn_X:              800,000 ms =  5.6%
    ///   LCK_M_RS_S:               600,000 ms =  4.2%
    ///   LCK_M_RIn_S:              400,000 ms =  2.8%
    ///   LCK_M_S:                   200,000 ms =  1.4%
    ///   LCK_M_X:                   100,000 ms =  0.7%
    /// </summary>
    public async Task SeedSerializableAbuseServerAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        var waits = new Dictionary<string, (long waitTimeMs, long waitingTasks, long signalMs)>
        {
            ["LCK_M_RIn_X"] = (800_000,  50_000,  5_000),
            ["LCK_M_RS_S"]  = (600_000,  40_000,  3_000),
            ["LCK_M_RIn_S"] = (400_000,  30_000,  2_000),
            ["LCK_M_S"]     = (200_000,  60_000,  1_000),
            ["LCK_M_X"]     = (100_000,  10_000,    500),
        };

        await SeedWaitStatsAsync(waits);
        // 25 deadlocks (~6.25/hr) — serializable often causes deadlocks
        await SeedDeadlocksAsync(25);
    }

    /// <summary>
    /// Log write pressure: WRITELOG dominant with some lock contention.
    /// Storage can't keep up with transaction log writes — shared storage
    /// or undersized log disks.
    ///
    /// Expected stories:
    ///   WRITELOG → log write latency
    ///
    /// Wait fractions (of 4-hour period):
    ///   WRITELOG:                 5,000,000 ms = 34.7%
    ///   LCK_M_X:                   600,000 ms =  4.2%
    ///   SOS_SCHEDULER_YIELD:       400,000 ms =  2.8%
    /// </summary>
    public async Task SeedLogWritePressureServerAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        var waits = new Dictionary<string, (long waitTimeMs, long waitingTasks, long signalMs)>
        {
            ["WRITELOG"]            = (5_000_000, 2_000_000, 500_000),
            ["LCK_M_X"]            = (  600_000,    40_000,   5_000),
            ["SOS_SCHEDULER_YIELD"] = (  400_000, 1_500_000,       0),
        };

        await SeedWaitStatsAsync(waits);
    }

    /// <summary>
    /// Resource semaphore cascade: memory grant waits causing buffer pool
    /// starvation and downstream PAGEIOLATCH. Queries requesting too much memory.
    ///
    /// Expected stories:
    ///   RESOURCE_SEMAPHORE → PAGEIOLATCH_SH (cascade)
    ///
    /// Wait fractions (of 4-hour period):
    ///   RESOURCE_SEMAPHORE:      1,500,000 ms = 10.4%
    ///   PAGEIOLATCH_SH:          6,000,000 ms = 41.7%
    ///   PAGEIOLATCH_EX:            500_000 ms =  3.5%
    ///   SOS_SCHEDULER_YIELD:       800,000 ms =  5.6%
    /// </summary>
    public async Task SeedResourceSemaphoreCascadeServerAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        var waits = new Dictionary<string, (long waitTimeMs, long waitingTasks, long signalMs)>
        {
            ["RESOURCE_SEMAPHORE"]  = (1_500_000,     5_000,       0), // avg 300s/wait — severe
            ["PAGEIOLATCH_SH"]      = (6_000_000, 3_000_000,  50_000),
            ["PAGEIOLATCH_EX"]      = (  500_000,   200_000,  10_000),
            ["SOS_SCHEDULER_YIELD"] = (  800_000, 3_000_000,       0),
        };

        await SeedWaitStatsAsync(waits);
        await SeedServerConfigAsync(ctfp: 50, maxdop: 8, maxMemoryMb: 57_344);
        await SeedMemoryStatsAsync(totalPhysicalMb: 65_536, bufferPoolMb: 40_000, targetMb: 57_344);
        await SeedFileSizeAsync(totalDataSizeMb: 307_200); // 300GB
        await SeedServerEditionAsync(edition: 2, majorVersion: 16); // Standard 2022

        // Cascade evidence: grant waiters + spills + I/O
        await SeedMemoryGrantsAsync(maxWaiters: 5, timeoutErrors: 3);
        await SeedQueryStatsAsync(totalSpills: 2_000, highDopQueryCount: 5);
        await SeedIoLatencyAsync(totalReads: 800_000, stallReadMs: 28_000_000, // 35ms avg read
                                  totalWrites: 200_000, stallWriteMs: 3_000_000);
        await SeedPerfmonAsync();
        await SeedServerPropertiesAsync(cpuCount: 16, htRatio: 2, physicalMemMb: 65_536);
    }

    /// <summary>
    /// Parameter-sensitive server: three cached plans whose per-execution worker time
    /// varies wildly — one plan serving very different parameter values. The worst is a
    /// ~1000x spread, also diverging on memory grant and on spills.
    ///
    /// Expected: a PARAMETER_SENSITIVITY finding. The worst ratio (1000x) drives
    /// BaseSeverity to 1.0 on its own; offender_count = 3 fires the "systemic" amplifier.
    /// </summary>
    public async Task SeedParameterSensitiveServerAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        // Hash, executions, min/max worker time (us), min/max grant (kb), min/max spills.
        var plans = new (string Hash, long Execs, long MinWorker, long MaxWorker,
                         long MinGrant, long MaxGrant, long MinSpills, long MaxSpills)[]
        {
            ("0xSENS0001", 5_000, 20_000, 20_000_000, 1_024, 1_048_576, 0, 50), // ~1000x — catastrophic
            ("0xSENS0002", 1_000, 50_000,  2_000_000, 4_096,     8_192, 0,  0), // ~40x
            ("0xSENS0003",   200, 15_000,    300_000, 2_048,     2_048, 0,  0), // ~20x
        };

        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        for (var i = 0; i < plans.Length; i++)
        {
            var p = plans[i];
            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO query_stats
    (collection_id, collection_time, server_id, server_name, database_name,
     query_hash, query_plan_hash, creation_time, execution_count,
     min_worker_time, max_worker_time, min_grant_kb, max_grant_kb,
     min_spills, max_spills, query_text, delta_execution_count)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17)";

            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestPeriodStart.AddMinutes(30 + i * 20) });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = "SensitiveDb" });
            cmd.Parameters.Add(new DuckDBParameter { Value = $"0xQH{i:D6}" });
            cmd.Parameters.Add(new DuckDBParameter { Value = p.Hash });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestPeriodStart.AddDays(-3) }); // compiled before window
            cmd.Parameters.Add(new DuckDBParameter { Value = p.Execs });
            cmd.Parameters.Add(new DuckDBParameter { Value = p.MinWorker });
            cmd.Parameters.Add(new DuckDBParameter { Value = p.MaxWorker });
            cmd.Parameters.Add(new DuckDBParameter { Value = p.MinGrant });
            cmd.Parameters.Add(new DuckDBParameter { Value = p.MaxGrant });
            cmd.Parameters.Add(new DuckDBParameter { Value = p.MinSpills });
            cmd.Parameters.Add(new DuckDBParameter { Value = p.MaxSpills });
            cmd.Parameters.Add(new DuckDBParameter { Value = $"SELECT * FROM dbo.SensitiveQuery{i} WHERE col = @p" });
            cmd.Parameters.Add(new DuckDBParameter { Value = 500L }); // delta_execution_count > 0 — active in window
            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Plan-regression server: one query (query_id 101) that switched from a fast plan
    /// to a plan ~12x more expensive per execution. Sourced from Query Store.
    ///
    /// Expected: a PLAN_REGRESSION finding. The worst factor (12x) is past the critical
    /// threshold so BaseSeverity reaches 1.0.
    /// </summary>
    public async Task SeedPlanRegressionServerAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        // plan_id, query_plan_hash, avg cpu (us), avg duration (us), last exec, first exec.
        var plans = new (long PlanId, string Hash, long AvgCpu, long AvgDur,
                         DateTime LastExec, DateTime FirstExec)[]
        {
            (1, "0xGOODPLAN",   100_000,   120_000, TestPeriodStart.AddDays(-5), TestPeriodStart.AddDays(-6)),
            (2, "0xBADPLAN",  1_200_000, 1_350_000, TestPeriodEnd,               TestPeriodStart.AddDays(-1)),
        };

        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        foreach (var p in plans)
        {
            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO query_store_stats
    (collection_id, collection_time, server_id, server_name, database_name,
     query_id, plan_id, execution_type_desc, first_execution_time, last_execution_time,
     query_text, query_hash, execution_count, avg_cpu_time_us, avg_duration_us,
     query_plan_hash, is_forced_plan, force_failure_count)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18)";

            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestPeriodEnd });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = "RegressionDb" });
            cmd.Parameters.Add(new DuckDBParameter { Value = 101L });
            cmd.Parameters.Add(new DuckDBParameter { Value = p.PlanId });
            cmd.Parameters.Add(new DuckDBParameter { Value = "Regular" });
            cmd.Parameters.Add(new DuckDBParameter { Value = p.FirstExec });
            cmd.Parameters.Add(new DuckDBParameter { Value = p.LastExec });
            cmd.Parameters.Add(new DuckDBParameter { Value = "SELECT * FROM dbo.Orders WHERE CustomerId = @id" });
            cmd.Parameters.Add(new DuckDBParameter { Value = "0xREGRESSQH" });
            cmd.Parameters.Add(new DuckDBParameter { Value = 100L }); // executions per plan — above the 25 floor
            cmd.Parameters.Add(new DuckDBParameter { Value = p.AvgCpu });
            cmd.Parameters.Add(new DuckDBParameter { Value = p.AvgDur });
            cmd.Parameters.Add(new DuckDBParameter { Value = p.Hash });
            cmd.Parameters.Add(new DuckDBParameter { Value = false });
            cmd.Parameters.Add(new DuckDBParameter { Value = 0L });
            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Everything on fire: multiple high-severity categories competing.
    /// Memory pressure, CPU pressure, parallelism, lock contention, log writes.
    /// Tests that the engine produces multiple stories in priority order.
    ///
    /// Expected stories (multiple, ordered by severity):
    ///   1. PAGEIOLATCH_SH (memory pressure, amplified by SOS)
    ///   2. CXPACKET (parallelism, amplified by SOS + THREADPOOL)
    ///   3. LCK_M_X (lock contention)
    ///   4. WRITELOG (log writes)
    ///
    /// Wait fractions (of 4-hour period):
    ///   PAGEIOLATCH_SH:          8,000,000 ms = 55.6%
    ///   CXPACKET:                6,000,000 ms = 41.7%
    ///   SOS_SCHEDULER_YIELD:     5,000,000 ms = 34.7%
    ///   LCK_M_X:                 2,000,000 ms = 13.9%
    ///   THREADPOOL:              4,000,000 ms = 27.8%
    ///   WRITELOG:                 1,500,000 ms = 10.4%
    ///   RESOURCE_SEMAPHORE:        300,000 ms =  2.1%
    /// </summary>
    public async Task SeedEverythingOnFireServerAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        var waits = new Dictionary<string, (long waitTimeMs, long waitingTasks, long signalMs)>
        {
            ["PAGEIOLATCH_SH"]      = (8_000_000, 4_000_000, 100_000),
            ["CXPACKET"]            = (6_000_000, 3_000_000,       0),
            ["SOS_SCHEDULER_YIELD"] = (5_000_000, 10_000_000,      0),
            ["LCK_M_X"]            = (2_000_000,   150_000,  20_000),
            ["THREADPOOL"]          = (4_000_000,     3_000,       0), // avg 1333ms/wait, >1h and >1s floors
            ["WRITELOG"]            = (1_500_000,   700_000, 150_000),
            ["RESOURCE_SEMAPHORE"]  = (  300_000,     1_000,       0), // avg 300s/wait
        };

        await SeedWaitStatsAsync(waits);
        // 100 blocking events (~25/hr) — systemic blocking
        await SeedBlockingEventsAsync(100, avgWaitTimeMs: 40_000, sleepingBlockerCount: 15, distinctBlockers: 10);
        // 30 deadlocks (~7.5/hr) — escalating
        await SeedDeadlocksAsync(30);
        await SeedServerConfigAsync(ctfp: 5, maxdop: 0, maxMemoryMb: 2_147_483_647); // All defaults
        await SeedMemoryStatsAsync(totalPhysicalMb: 65_536, bufferPoolMb: 58_000, targetMb: 65_536);
        await SeedFileSizeAsync(totalDataSizeMb: 1_024_000); // 1TB
        await SeedServerEditionAsync(edition: 2, majorVersion: 15); // Standard 2019

        // New collectors — full coverage
        await SeedCpuUtilizationAsync(95, 10); // 95% SQL + 10% other = pegged
        await SeedIoLatencyAsync(totalReads: 2_000_000, stallReadMs: 100_000_000, // 50ms avg read
                                  totalWrites: 500_000, stallWriteMs: 15_000_000); // 30ms avg write
        await SeedTempDbAsync(reservedMb: 9_000, unallocatedMb: 1_000); // 90% full
        await SeedMemoryGrantsAsync(maxWaiters: 8, maxGrantees: 5, timeoutErrors: 10, forcedGrants: 5);
        await SeedQueryStatsAsync(totalSpills: 5_000, highDopQueryCount: 20);
        await SeedPerfmonAsync();
        await SeedMemoryClerksAsync(new Dictionary<string, double>
        {
            ["MEMORYCLERK_SQLBUFFERPOOL"] = 50_000,
            ["MEMORYCLERK_SQLQUERYPLAN"] = 4_000,
            ["MEMORYCLERK_SQLOPTIMIZER"] = 1_500,
            ["CACHESTORE_OBJCP"] = 2_000,
            ["CACHESTORE_SQLCP"] = 3_500,
        });
        await SeedDatabaseConfigAsync(
            ("AppDB1", false, true, false, "NONE"),       // RCSI off, auto_shrink, bad page_verify
            ("AppDB2", false, false, true, "CHECKSUM"),    // RCSI off, auto_close
            ("AppDB3", true, false, false, "CHECKSUM"));   // OK
        await SeedProcedureStatsAsync(distinctProcs: 25, totalExecs: 500_000, totalCpuUs: 50_000_000_000);
        await SeedActiveQueriesAsync(longRunning: 8, blocked: 5, parallel: 6, maxElapsedMs: 300_000, maxDop: 16);
        await SeedRunningJobsAsync(totalJobs: 5, runningLong: 3, maxPctAvg: 400, maxDurationSeconds: 10_800);
        await SeedSessionStatsAsync(
            ("WebApp", 200, 15, 180),
            ("ReportingService", 50, 8, 40),
            ("SQLAgent", 10, 3, 7));
        await SeedTraceFlagsAsync(1118, 3226, 2371);
        await SeedServerPropertiesAsync(cpuCount: 16, htRatio: 2, physicalMemMb: 65_536);
        await SeedDiskSpaceAsync(
            ("C:\\", 500_000, 35_000),   // 7% free — critical
            ("D:\\", 2_000_000, 140_000)); // 7% free — critical
    }

    /// <summary>
    /// CPU spike anomaly: server normally runs at 10% CPU, then spikes to 95%.
    /// Baseline: 24h of steady ~10% CPU.
    /// Analysis window: 4h with 95% peak CPU.
    ///
    /// Expected: ANOMALY_CPU_SPIKE with high deviation (~10σ+).
    /// </summary>
    public async Task SeedCpuSpikeAnomalyAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        // Baseline: 24h of steady low CPU (10% avg, small variance)
        await SeedCpuUtilizationInRangeAsync(BaselineStart, BaselineEnd, avgCpu: 10, variance: 3, samples: 96);

        // Analysis window: spike to 95%
        await SeedCpuUtilizationInRangeAsync(TestPeriodStart, TestPeriodEnd, avgCpu: 95, variance: 5, samples: 16);

        // Need basic config for the analysis to run
        await SeedServerConfigAsync(ctfp: 50, maxdop: 8, maxMemoryMb: 122_880);
        await SeedMemoryStatsAsync(totalPhysicalMb: 131_072, bufferPoolMb: 100_000, targetMb: 122_880);
        await SeedFileSizeAsync(totalDataSizeMb: 102_400);
        await SeedServerEditionAsync(edition: 3, majorVersion: 16);
        await SeedServerPropertiesAsync(cpuCount: 8, htRatio: 1, physicalMemMb: 131_072);
    }

    /// <summary>
    /// Blocking spike anomaly: normally no blocking, then sudden burst.
    /// Baseline: 24h with 0 blocking events.
    /// Analysis window: 4h with 50 blocking events and 10 deadlocks.
    ///
    /// Expected: ANOMALY_BLOCKING_SPIKE, ANOMALY_DEADLOCK_SPIKE.
    /// </summary>
    public async Task SeedBlockingSpikeAnomalyAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        // Baseline: some normal wait activity (no blocking/deadlocks)
        await SeedWaitStatsInRangeAsync(BaselineStart, BaselineEnd,
            new Dictionary<string, (long waitTimeMs, long waitingTasks, long signalMs)>
            {
                ["SOS_SCHEDULER_YIELD"] = (100_000, 500_000, 0),
            }, samples: 24);

        // Analysis window: sudden blocking burst
        await SeedBlockingEventsAsync(50, avgWaitTimeMs: 15_000, sleepingBlockerCount: 5);
        await SeedDeadlocksAsync(10);

        // Some lock waits to corroborate
        var waits = new Dictionary<string, (long waitTimeMs, long waitingTasks, long signalMs)>
        {
            ["LCK_M_X"]  = (5_000_000, 200_000, 50_000),
            ["LCK_M_S"]  = (1_000_000, 100_000, 10_000),
        };
        await SeedWaitStatsAsync(waits);

        await SeedServerConfigAsync(ctfp: 50, maxdop: 8, maxMemoryMb: 122_880);
        await SeedMemoryStatsAsync(totalPhysicalMb: 131_072, bufferPoolMb: 100_000, targetMb: 122_880);
        await SeedFileSizeAsync(totalDataSizeMb: 102_400);
        await SeedServerEditionAsync(edition: 3, majorVersion: 16);
        await SeedServerPropertiesAsync(cpuCount: 8, htRatio: 1, physicalMemMb: 131_072);
        await SeedDatabaseConfigAsync(
            ("AppDB1", false, false, false, "CHECKSUM"),
            ("AppDB2", false, false, false, "CHECKSUM"));
    }

    /// <summary>
    /// Wait spike anomaly: normally low waits, then sudden PAGEIOLATCH flood.
    /// Baseline: 24h with minimal PAGEIOLATCH.
    /// Analysis window: 4h with massive PAGEIOLATCH.
    ///
    /// Expected: ANOMALY_WAIT_PAGEIOLATCH_SH with high ratio.
    /// </summary>
    public async Task SeedWaitSpikeAnomalyAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        // Baseline: 24h with minimal PAGEIOLATCH
        await SeedWaitStatsInRangeAsync(BaselineStart, BaselineEnd,
            new Dictionary<string, (long waitTimeMs, long waitingTasks, long signalMs)>
            {
                ["PAGEIOLATCH_SH"] = (50_000, 25_000, 1_000),  // 50 seconds over 24h = noise
                ["SOS_SCHEDULER_YIELD"] = (100_000, 500_000, 0),
            }, samples: 24);

        // Analysis window: massive PAGEIOLATCH spike
        var waits = new Dictionary<string, (long waitTimeMs, long waitingTasks, long signalMs)>
        {
            ["PAGEIOLATCH_SH"] = (8_000_000, 4_000_000, 100_000),  // 8 million ms in 4h
            ["SOS_SCHEDULER_YIELD"] = (100_000, 500_000, 0),        // Normal
        };
        await SeedWaitStatsAsync(waits);

        await SeedServerConfigAsync(ctfp: 50, maxdop: 8, maxMemoryMb: 122_880);
        await SeedMemoryStatsAsync(totalPhysicalMb: 131_072, bufferPoolMb: 100_000, targetMb: 122_880);
        await SeedFileSizeAsync(totalDataSizeMb: 102_400);
        await SeedServerEditionAsync(edition: 3, majorVersion: 16);
        await SeedServerPropertiesAsync(cpuCount: 8, htRatio: 1, physicalMemMb: 131_072);
    }

    /// <summary>
    /// Seeds CPU utilization data in a specific time range with variance.
    /// Used for baseline + spike anomaly scenarios.
    /// </summary>
    internal async Task SeedCpuUtilizationInRangeAsync(DateTime start, DateTime end,
        int avgCpu, int variance, int samples)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        var interval = (end - start).TotalMinutes / samples;
        var rng = new Random(42); // Deterministic for reproducibility

        for (var i = 0; i < samples; i++)
        {
            var t = start.AddMinutes(i * interval);
            var cpu = Math.Clamp(avgCpu + rng.Next(-variance, variance + 1), 0, 100);

            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO cpu_utilization_stats
    (collection_id, collection_time, server_id, server_name,
     sample_time, sqlserver_cpu_utilization, other_process_cpu_utilization)
VALUES ($1, $2, $3, $4, $5, $6, $7)";

            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = t });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = t });
            cmd.Parameters.Add(new DuckDBParameter { Value = cpu });
            cmd.Parameters.Add(new DuckDBParameter { Value = 2 }); // other CPU

            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Seeds wait stats in a specific time range. Used for baseline periods
    /// in anomaly detection scenarios.
    /// </summary>
    internal async Task SeedWaitStatsInRangeAsync(DateTime start, DateTime end,
        Dictionary<string, (long waitTimeMs, long waitingTasks, long signalMs)> waits, int samples)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        var interval = (end - start).TotalMinutes / samples;

        foreach (var (waitType, (totalWaitTimeMs, totalWaitingTasks, totalSignalMs)) in waits)
        {
            var perSampleWaitMs = totalWaitTimeMs / samples;
            var perSampleTasks = totalWaitingTasks / samples;
            var perSampleSignal = totalSignalMs / samples;

            for (var i = 0; i < samples; i++)
            {
                var t = start.AddMinutes(i * interval);

                using var cmd = connection.CreateCommand();
                cmd.CommandText = @"
INSERT INTO wait_stats
    (collection_id, collection_time, server_id, server_name,
     wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms,
     delta_waiting_tasks, delta_wait_time_ms, delta_signal_wait_time_ms)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)";

                cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
                cmd.Parameters.Add(new DuckDBParameter { Value = t });
                cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
                cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
                cmd.Parameters.Add(new DuckDBParameter { Value = waitType });
                cmd.Parameters.Add(new DuckDBParameter { Value = totalWaitingTasks }); // cumulative
                cmd.Parameters.Add(new DuckDBParameter { Value = totalWaitTimeMs });   // cumulative
                cmd.Parameters.Add(new DuckDBParameter { Value = totalSignalMs });     // cumulative
                cmd.Parameters.Add(new DuckDBParameter { Value = perSampleTasks });    // delta
                cmd.Parameters.Add(new DuckDBParameter { Value = perSampleWaitMs });   // delta
                cmd.Parameters.Add(new DuckDBParameter { Value = perSampleSignal });   // delta

                await cmd.ExecuteNonQueryAsync();
            }
        }
    }

    /// <summary>
    /// Removes all test data across all tables.
    /// </summary>
    internal async Task ClearTestDataAsync()
    {
        var tables = new[]
        {
            "wait_stats", "memory_stats", "server_config", "database_config",
            "cpu_utilization_stats", "file_io_stats", "memory_clerks",
            "query_stats", "procedure_stats", "query_store_stats",
            "query_snapshots", "tempdb_stats", "perfmon_stats",
            "blocked_process_reports", "deadlocks", "memory_grant_stats",
            "waiting_tasks", "servers", "running_jobs", "session_stats",
            "trace_flags", "server_properties", "database_size_stats"
        };

        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        foreach (var table in tables)
        {
            try
            {
                using var cmd = connection.CreateCommand();
                cmd.CommandText = $"DELETE FROM {table} WHERE server_id = {TestServerId}";
                await cmd.ExecuteNonQueryAsync();
            }
            catch
            {
                /* Table may not exist yet — that's fine */
            }
        }
    }

    /// <summary>
    /// Registers the test server in the servers table.
    /// </summary>
    internal async Task SeedTestServerAsync()
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        using var cmd = connection.CreateCommand();
        cmd.CommandText = @"
INSERT INTO servers (server_id, server_name, display_name, use_windows_auth, is_enabled)
VALUES ($1, $2, $3, true, true)";
        cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
        cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
        cmd.Parameters.Add(new DuckDBParameter { Value = "ErikAI Test Server" });
        await cmd.ExecuteNonQueryAsync();
    }

    /// <summary>
    /// Seeds blocked_process_reports with synthetic blocking events.
    /// </summary>
    internal async Task SeedBlockingEventsAsync(int count, long avgWaitTimeMs,
        int sleepingBlockerCount = 0, int distinctBlockers = 3)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        var intervalMinutes = 240.0 / count; // Spread across 4-hour window

        for (var i = 0; i < count; i++)
        {
            var eventTime = TestPeriodStart.AddMinutes(i * intervalMinutes);
            var id = _nextId--;
            var isSleeping = i < sleepingBlockerCount;
            var blockerSpid = 50 + (i % distinctBlockers);

            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO blocked_process_reports
    (blocked_report_id, collection_time, server_id, server_name,
     event_time, blocked_spid, blocked_last_tran_started,
     blocking_spid, blocking_last_tran_started, wait_time_ms,
     lock_mode, blocked_status, blocking_status)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)";

            cmd.Parameters.Add(new DuckDBParameter { Value = id });
            cmd.Parameters.Add(new DuckDBParameter { Value = eventTime });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = eventTime });
            cmd.Parameters.Add(new DuckDBParameter { Value = 100 + i }); // blocked spid — distinct per event
            cmd.Parameters.Add(new DuckDBParameter { Value = eventTime }); // blocked tran — distinct per blocked session
            cmd.Parameters.Add(new DuckDBParameter { Value = blockerSpid });
            // Blocking tran — stable per blocker SPID, so all events for one blocker map to one
            // session node (a head blocker with many victims), not many one-victim chains.
            cmd.Parameters.Add(new DuckDBParameter { Value = TestPeriodStart.AddSeconds(blockerSpid) });
            cmd.Parameters.Add(new DuckDBParameter { Value = avgWaitTimeMs });
            cmd.Parameters.Add(new DuckDBParameter { Value = "X" });
            cmd.Parameters.Add(new DuckDBParameter { Value = "suspended" });
            cmd.Parameters.Add(new DuckDBParameter { Value = isSleeping ? "sleeping" : "running" });

            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Seeds blocked_process_reports with a known, reconstructable set of blocking chains:
    /// a depth-4 line (sleeping apex 200), a depth-1 / 5-victim fan-out (apex 300), a SPID-reuse
    /// case (spid 201 with a different transaction start — must NOT splice into the depth-4
    /// chain), and a 1900-01-01 sentinel transaction start.
    /// </summary>
    internal async Task SeedBlockingChainAsync()
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        var baseTran = TestPeriodStart;
        DateTime Tran(int spid) => baseTran.AddSeconds(spid);

        // blocked spid, blocked tran, blocking spid, blocking tran, blocking status, blocked ecid
        var pairs = new (int BlockedSpid, DateTime BlockedTran, int BlockingSpid, DateTime BlockingTran, string BlockingStatus, int BlockedEcid)[]
        {
            // Chain A — depth 4, apex 200 sleeping
            (201, Tran(201), 200, Tran(200), "sleeping", 0),
            (202, Tran(202), 201, Tran(201), "running", 0),
            (203, Tran(203), 202, Tran(202), "running", 0),
            (204, Tran(204), 203, Tran(203), "running", 0),
            // Chain B — depth 1, apex 300, five victims
            (301, Tran(301), 300, Tran(300), "running", 0),
            (302, Tran(302), 300, Tran(300), "running", 0),
            (303, Tran(303), 300, Tran(300), "running", 0),
            (304, Tran(304), 300, Tran(300), "running", 0),
            (305, Tran(305), 300, Tran(300), "running", 0),
            // SPID reuse — spid 201 again on a DIFFERENT execution context (ecid 1); the (spid, ecid) key
            // keeps this distinct session off Chain A (transaction start is no longer the disambiguator).
            (201, Tran(201).AddHours(2), 210, Tran(210), "running", 1),
            // 1900-01-01 sentinel transaction start on the blocked session
            (220, new DateTime(1900, 1, 1), 221, Tran(221), "running", 0),
        };

        for (var i = 0; i < pairs.Length; i++)
        {
            var p = pairs[i];
            var eventTime = TestPeriodStart.AddMinutes(10 + i);

            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO blocked_process_reports
    (blocked_report_id, collection_time, server_id, server_name,
     event_time, database_name, blocked_spid, blocked_last_tran_started,
     blocking_spid, blocking_last_tran_started, wait_time_ms,
     lock_mode, blocked_status, blocking_status, blocked_sql_text, blocking_sql_text,
     blocked_ecid)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17)";

            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = eventTime });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = eventTime });
            cmd.Parameters.Add(new DuckDBParameter { Value = "ChainDb" });
            cmd.Parameters.Add(new DuckDBParameter { Value = p.BlockedSpid });
            cmd.Parameters.Add(new DuckDBParameter { Value = p.BlockedTran });
            cmd.Parameters.Add(new DuckDBParameter { Value = p.BlockingSpid });
            cmd.Parameters.Add(new DuckDBParameter { Value = p.BlockingTran });
            cmd.Parameters.Add(new DuckDBParameter { Value = 30_000L });
            cmd.Parameters.Add(new DuckDBParameter { Value = "X" });
            cmd.Parameters.Add(new DuckDBParameter { Value = "suspended" });
            cmd.Parameters.Add(new DuckDBParameter { Value = p.BlockingStatus });
            cmd.Parameters.Add(new DuckDBParameter { Value = $"SELECT * FROM dbo.T WHERE id = {p.BlockedSpid}" });
            cmd.Parameters.Add(new DuckDBParameter { Value = $"UPDATE dbo.T SET v = 1 WHERE id = {p.BlockingSpid}" });
            cmd.Parameters.Add(new DuckDBParameter { Value = p.BlockedEcid });
            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Deep blocking chain scenario: a depth-4 chain with a sleeping apex, plus corroborating
    /// lock and thread-exhaustion waits and deadlocks. Expected: a BLOCKING_CHAIN finding
    /// (depth 4, apex 200) that traverses to LCK / THREADPOOL.
    /// </summary>
    public async Task SeedDeepBlockingChainServerAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        var waits = new Dictionary<string, (long waitTimeMs, long waitingTasks, long signalMs)>
        {
            ["THREADPOOL"]          = (5_400_000,     4_000,       0),
            ["LCK_M_X"]             = (4_000_000,   300_000,  50_000),
            ["SOS_SCHEDULER_YIELD"] = (  500_000, 2_000_000,       0),
        };

        await SeedWaitStatsAsync(waits);
        await SeedBlockingChainAsync();
        await SeedDeadlocksAsync(15);
    }

    /// <summary>
    /// Seeds deadlocks table with synthetic deadlock events.
    /// </summary>
    internal async Task SeedDeadlocksAsync(int count)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        var intervalMinutes = 240.0 / count;

        for (var i = 0; i < count; i++)
        {
            var eventTime = TestPeriodStart.AddMinutes(i * intervalMinutes);
            var id = _nextId--;

            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO deadlocks
    (deadlock_id, collection_time, server_id, server_name, deadlock_time)
VALUES ($1, $2, $3, $4, $5)";

            cmd.Parameters.Add(new DuckDBParameter { Value = id });
            cmd.Parameters.Add(new DuckDBParameter { Value = eventTime });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = eventTime });

            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Seeds wait_stats with the given wait type values.
    /// Distributes data across 16 collection points (every 15 minutes)
    /// so the data looks realistic in trend queries.
    /// </summary>
    internal async Task SeedWaitStatsAsync(
        Dictionary<string, (long waitTimeMs, long waitingTasks, long signalMs)> waits)
    {
        const int collectionPoints = 16;

        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        foreach (var (waitType, totals) in waits)
        {
            var deltaWaitPerPoint = totals.waitTimeMs / collectionPoints;
            var deltaTasksPerPoint = totals.waitingTasks / collectionPoints;
            var deltaSignalPerPoint = totals.signalMs / collectionPoints;

            long cumulativeWait = 0;
            long cumulativeTasks = 0;
            long cumulativeSignal = 0;

            for (var i = 0; i < collectionPoints; i++)
            {
                cumulativeWait += deltaWaitPerPoint;
                cumulativeTasks += deltaTasksPerPoint;
                cumulativeSignal += deltaSignalPerPoint;

                var collectionTime = TestPeriodStart.AddMinutes(i * 15);
                var id = _nextId--;

                using var cmd = connection.CreateCommand();
                cmd.CommandText = @"
INSERT INTO wait_stats
    (collection_id, collection_time, server_id, server_name, wait_type,
     waiting_tasks_count, wait_time_ms, signal_wait_time_ms,
     delta_waiting_tasks, delta_wait_time_ms, delta_signal_wait_time_ms)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)";

                cmd.Parameters.Add(new DuckDBParameter { Value = id });
                cmd.Parameters.Add(new DuckDBParameter { Value = collectionTime });
                cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
                cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
                cmd.Parameters.Add(new DuckDBParameter { Value = waitType });
                cmd.Parameters.Add(new DuckDBParameter { Value = cumulativeTasks });
                cmd.Parameters.Add(new DuckDBParameter { Value = cumulativeWait });
                cmd.Parameters.Add(new DuckDBParameter { Value = cumulativeSignal });
                cmd.Parameters.Add(new DuckDBParameter { Value = deltaTasksPerPoint });
                cmd.Parameters.Add(new DuckDBParameter { Value = deltaWaitPerPoint });
                cmd.Parameters.Add(new DuckDBParameter { Value = deltaSignalPerPoint });

                await cmd.ExecuteNonQueryAsync();
            }
        }
    }

    /// <summary>
    /// Seeds memory_stats with physical memory, buffer pool, and target memory values
    /// across 16 collection points so 7-day P95 queries (used by FinOps memory
    /// recommendations) have enough samples to fire.
    /// </summary>
    internal async Task SeedMemoryStatsAsync(double totalPhysicalMb, double bufferPoolMb, double targetMb)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        for (var i = 0; i < 16; i++)
        {
            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO memory_stats
    (collection_id, collection_time, server_id, server_name,
     total_physical_memory_mb, available_physical_memory_mb,
     target_server_memory_mb, total_server_memory_mb, buffer_pool_mb)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)";

            var t = TestPeriodStart.AddMinutes(i * 15);
            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = t });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = totalPhysicalMb });
            cmd.Parameters.Add(new DuckDBParameter { Value = totalPhysicalMb - bufferPoolMb }); // available = total - used
            cmd.Parameters.Add(new DuckDBParameter { Value = targetMb });
            cmd.Parameters.Add(new DuckDBParameter { Value = bufferPoolMb });
            cmd.Parameters.Add(new DuckDBParameter { Value = bufferPoolMb });

            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Seeds file_io_stats with a total database size entry.
    /// Creates a single "data" file entry representing the total data footprint.
    /// </summary>
    internal async Task SeedFileSizeAsync(double totalDataSizeMb)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        using var cmd = connection.CreateCommand();
        cmd.CommandText = @"
INSERT INTO file_io_stats
    (collection_id, collection_time, server_id, server_name,
     database_name, file_name, file_type, size_mb,
     num_of_reads, num_of_writes, read_bytes, write_bytes,
     io_stall_read_ms, io_stall_write_ms)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, 0, 0, 0, 0, 0, 0)";

        cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
        cmd.Parameters.Add(new DuckDBParameter { Value = TestPeriodEnd });
        cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
        cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
        cmd.Parameters.Add(new DuckDBParameter { Value = "AllDatabases" });
        cmd.Parameters.Add(new DuckDBParameter { Value = "aggregate_data" });
        cmd.Parameters.Add(new DuckDBParameter { Value = "ROWS" });
        cmd.Parameters.Add(new DuckDBParameter { Value = totalDataSizeMb });

        await cmd.ExecuteNonQueryAsync();
    }

    /// <summary>
    /// Updates the test server's edition and major version in the servers table.
    /// </summary>
    internal async Task SeedServerEditionAsync(int edition, int majorVersion)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        using var cmd = connection.CreateCommand();
        cmd.CommandText = @"
UPDATE servers
SET sql_engine_edition = $1,
    sql_major_version = $2
WHERE server_id = $3";

        cmd.Parameters.Add(new DuckDBParameter { Value = edition });
        cmd.Parameters.Add(new DuckDBParameter { Value = majorVersion });
        cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });

        await cmd.ExecuteNonQueryAsync();
    }

    /// <summary>
    /// Seeds server_config with specific CTFP and MAXDOP values for testing.
    /// </summary>
    internal async Task SeedServerConfigAsync(int ctfp = 50, int maxdop = 8, int maxMemoryMb = 57344)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        var configs = new (string name, int value)[]
        {
            ("cost threshold for parallelism", ctfp),
            ("max degree of parallelism", maxdop),
            ("max server memory (MB)", maxMemoryMb),
            ("max worker threads", 0)
        };

        foreach (var (name, value) in configs)
        {
            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO server_config
    (config_id, capture_time, server_id, server_name, configuration_name,
     value_configured, value_in_use, is_dynamic, is_advanced)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)";

            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestPeriodEnd });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = name });
            cmd.Parameters.Add(new DuckDBParameter { Value = value });
            cmd.Parameters.Add(new DuckDBParameter { Value = value });
            cmd.Parameters.Add(new DuckDBParameter { Value = true });
            cmd.Parameters.Add(new DuckDBParameter { Value = true });

            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Seeds cpu_utilization_stats across 16 collection points.
    /// </summary>
    internal async Task SeedCpuUtilizationAsync(int avgSqlCpu, int avgOtherCpu)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        for (var i = 0; i < 16; i++)
        {
            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO cpu_utilization_stats
    (collection_id, collection_time, server_id, server_name,
     sample_time, sqlserver_cpu_utilization, other_process_cpu_utilization)
VALUES ($1, $2, $3, $4, $5, $6, $7)";

            var t = TestPeriodStart.AddMinutes(i * 15);
            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = t });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = t });
            cmd.Parameters.Add(new DuckDBParameter { Value = avgSqlCpu });
            cmd.Parameters.Add(new DuckDBParameter { Value = avgOtherCpu });

            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Seeds file_io_stats with I/O latency delta data across 16 collection points.
    /// totalReads/totalWrites are the total I/O count over the period;
    /// stallReadMs/stallWriteMs are total stall times.
    /// Average latency = stallMs / ioCount.
    /// </summary>
    internal async Task SeedIoLatencyAsync(long totalReads, long stallReadMs,
        long totalWrites, long stallWriteMs)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        var deltaReads = totalReads / 16;
        var deltaStallRead = stallReadMs / 16;
        var deltaWrites = totalWrites / 16;
        var deltaStallWrite = stallWriteMs / 16;

        for (var i = 0; i < 16; i++)
        {
            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO file_io_stats
    (collection_id, collection_time, server_id, server_name,
     database_name, file_name, file_type, size_mb,
     num_of_reads, num_of_writes, read_bytes, write_bytes,
     io_stall_read_ms, io_stall_write_ms,
     delta_reads, delta_writes, delta_stall_read_ms, delta_stall_write_ms)
VALUES ($1, $2, $3, $4, $5, $6, $7, 0,
        $8, $9, 0, 0, $10, $11, $12, $13, $14, $15)";

            var t = TestPeriodStart.AddMinutes(i * 15);
            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = t });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = "UserDB" });
            cmd.Parameters.Add(new DuckDBParameter { Value = "UserDB.mdf" });
            cmd.Parameters.Add(new DuckDBParameter { Value = "ROWS" });
            cmd.Parameters.Add(new DuckDBParameter { Value = (long)(deltaReads * (i + 1)) }); // cumulative
            cmd.Parameters.Add(new DuckDBParameter { Value = (long)(deltaWrites * (i + 1)) });
            cmd.Parameters.Add(new DuckDBParameter { Value = (long)(deltaStallRead * (i + 1)) });
            cmd.Parameters.Add(new DuckDBParameter { Value = (long)(deltaStallWrite * (i + 1)) });
            cmd.Parameters.Add(new DuckDBParameter { Value = deltaReads });
            cmd.Parameters.Add(new DuckDBParameter { Value = deltaWrites });
            cmd.Parameters.Add(new DuckDBParameter { Value = deltaStallRead });
            cmd.Parameters.Add(new DuckDBParameter { Value = deltaStallWrite });

            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Seeds tempdb_stats across 16 collection points.
    /// </summary>
    internal async Task SeedTempDbAsync(double reservedMb, double unallocatedMb,
        double userObjectMb = 0, double internalObjectMb = 0, double versionStoreMb = 0)
    {
        if (userObjectMb == 0) userObjectMb = reservedMb * 0.6;
        if (internalObjectMb == 0) internalObjectMb = reservedMb * 0.3;
        if (versionStoreMb == 0) versionStoreMb = reservedMb * 0.1;

        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        for (var i = 0; i < 16; i++)
        {
            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO tempdb_stats
    (collection_id, collection_time, server_id, server_name,
     user_object_reserved_mb, internal_object_reserved_mb,
     version_store_reserved_mb, total_reserved_mb, unallocated_mb)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)";

            var t = TestPeriodStart.AddMinutes(i * 15);
            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = t });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = userObjectMb });
            cmd.Parameters.Add(new DuckDBParameter { Value = internalObjectMb });
            cmd.Parameters.Add(new DuckDBParameter { Value = versionStoreMb });
            cmd.Parameters.Add(new DuckDBParameter { Value = reservedMb });
            cmd.Parameters.Add(new DuckDBParameter { Value = unallocatedMb });

            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Seeds memory_grant_stats across 16 collection points.
    /// </summary>
    internal async Task SeedMemoryGrantsAsync(int maxWaiters, int maxGrantees = 10,
        long timeoutErrors = 0, long forcedGrants = 0)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        var timeoutDeltaPerPoint = timeoutErrors / 16;
        var forcedDeltaPerPoint = forcedGrants / 16;

        for (var i = 0; i < 16; i++)
        {
            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO memory_grant_stats
    (collection_id, collection_time, server_id, server_name,
     resource_semaphore_id, waiter_count, grantee_count,
     timeout_error_count_delta, forced_grant_count_delta)
VALUES ($1, $2, $3, $4, 0, $5, $6, $7, $8)";

            var t = TestPeriodStart.AddMinutes(i * 15);
            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = t });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = maxWaiters });
            cmd.Parameters.Add(new DuckDBParameter { Value = maxGrantees });
            cmd.Parameters.Add(new DuckDBParameter { Value = timeoutDeltaPerPoint });
            cmd.Parameters.Add(new DuckDBParameter { Value = forcedDeltaPerPoint });

            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Seeds query_stats with aggregate spill and DOP data.
    /// Creates individual query entries that the collector aggregates.
    /// </summary>
    internal async Task SeedQueryStatsAsync(long totalSpills, int highDopQueryCount,
        long totalExecutions = 10_000)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        // Spilling queries
        var spillingQueries = Math.Max(1, (int)(totalSpills / 100)); // ~100 spills per query
        var spillsPerQuery = totalSpills / spillingQueries;
        var execsPerQuery = totalExecutions / (spillingQueries + highDopQueryCount + 5);

        var totalQueries = spillingQueries + highDopQueryCount;
        var intervalMinutes = 240.0 / Math.Max(totalQueries, 1);

        for (var i = 0; i < spillingQueries; i++)
        {
            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO query_stats
    (collection_id, collection_time, server_id, server_name,
     query_hash, delta_spills, max_dop, delta_execution_count,
     delta_worker_time, delta_elapsed_time)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)";

            var t = TestPeriodStart.AddMinutes(i * intervalMinutes);
            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = t });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = $"0xSPILL{i:D4}" });
            cmd.Parameters.Add(new DuckDBParameter { Value = spillsPerQuery });
            cmd.Parameters.Add(new DuckDBParameter { Value = 4 }); // normal DOP
            cmd.Parameters.Add(new DuckDBParameter { Value = execsPerQuery });
            cmd.Parameters.Add(new DuckDBParameter { Value = execsPerQuery * 50_000L }); // 50ms avg CPU
            cmd.Parameters.Add(new DuckDBParameter { Value = execsPerQuery * 100_000L });

            await cmd.ExecuteNonQueryAsync();
        }

        // High-DOP queries
        for (var i = 0; i < highDopQueryCount; i++)
        {
            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO query_stats
    (collection_id, collection_time, server_id, server_name,
     query_hash, delta_spills, max_dop, delta_execution_count,
     delta_worker_time, delta_elapsed_time)
VALUES ($1, $2, $3, $4, $5, 0, $6, $7, $8, $9)";

            var t = TestPeriodStart.AddMinutes((spillingQueries + i) * intervalMinutes);
            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = t });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = $"0xHDOP{i:D4}" });
            cmd.Parameters.Add(new DuckDBParameter { Value = 16 + (i % 16) }); // DOP > 8
            cmd.Parameters.Add(new DuckDBParameter { Value = execsPerQuery });
            cmd.Parameters.Add(new DuckDBParameter { Value = execsPerQuery * 200_000L });
            cmd.Parameters.Add(new DuckDBParameter { Value = execsPerQuery * 50_000L });

            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Seeds perfmon_stats with the collected rate counters (batch requests, compilations,
    /// recompilations); all use delta_cntr_value.
    /// </summary>
    internal async Task SeedPerfmonAsync(long batchReqSec = 500,
        long compilationsSec = 50, long recompilationsSec = 5)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        var counters = new (string name, long cntrValue, long deltaValue)[]
        {
            ("Batch Requests/sec", batchReqSec * 60, batchReqSec), // cntr = cumulative, delta = rate
            ("SQL Compilations/sec", compilationsSec * 60, compilationsSec),
            ("SQL Re-Compilations/sec", recompilationsSec * 60, recompilationsSec)
        };

        foreach (var (name, cntr, delta) in counters)
        {
            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO perfmon_stats
    (collection_id, collection_time, server_id, server_name,
     object_name, counter_name, cntr_value, delta_cntr_value, sample_interval_seconds)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, 60)";

            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestPeriodEnd });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = "SQLServer:Buffer Manager" });
            cmd.Parameters.Add(new DuckDBParameter { Value = name });
            cmd.Parameters.Add(new DuckDBParameter { Value = cntr });
            cmd.Parameters.Add(new DuckDBParameter { Value = delta });

            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Seeds memory_clerks with clerk type → MB mappings.
    /// </summary>
    internal async Task SeedMemoryClerksAsync(Dictionary<string, double> clerks)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        foreach (var (clerkType, memoryMb) in clerks)
        {
            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO memory_clerks
    (collection_id, collection_time, server_id, server_name, clerk_type, memory_mb)
VALUES ($1, $2, $3, $4, $5, $6)";

            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestPeriodEnd });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = clerkType });
            cmd.Parameters.Add(new DuckDBParameter { Value = memoryMb });

            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Seeds database_config with per-database configuration flags.
    /// </summary>
    internal async Task SeedDatabaseConfigAsync(
        params (string dbName, bool rcsiOn, bool autoShrink, bool autoClose, string pageVerify)[] databases)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        foreach (var (dbName, rcsiOn, autoShrink, autoClose, pageVerify) in databases)
        {
            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO database_config
    (config_id, capture_time, server_id, server_name, database_name,
     recovery_model, is_auto_shrink_on, is_auto_close_on,
     is_read_committed_snapshot_on, is_auto_create_stats_on,
     is_auto_update_stats_on, page_verify_option, is_query_store_on)
VALUES ($1, $2, $3, $4, $5, 'FULL', $6, $7, $8, true, true, $9, false)";

            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestPeriodEnd });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = dbName });
            cmd.Parameters.Add(new DuckDBParameter { Value = autoShrink });
            cmd.Parameters.Add(new DuckDBParameter { Value = autoClose });
            cmd.Parameters.Add(new DuckDBParameter { Value = rcsiOn });
            cmd.Parameters.Add(new DuckDBParameter { Value = pageVerify });

            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Seeds procedure_stats with aggregate execution data.
    /// </summary>
    internal async Task SeedProcedureStatsAsync(int distinctProcs, long totalExecs, long totalCpuUs)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        var execsPerProc = totalExecs / distinctProcs;
        var cpuPerProc = totalCpuUs / distinctProcs;

        for (var i = 0; i < distinctProcs; i++)
        {
            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO procedure_stats
    (collection_id, collection_time, server_id, server_name,
     database_name, schema_name, object_name,
     delta_execution_count, delta_worker_time, delta_elapsed_time, delta_logical_reads)
VALUES ($1, $2, $3, $4, $5, 'dbo', $6, $7, $8, $9, $10)";

            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestPeriodEnd.AddMinutes(-i) });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = "UserDB" });
            cmd.Parameters.Add(new DuckDBParameter { Value = $"usp_TestProc_{i}" });
            cmd.Parameters.Add(new DuckDBParameter { Value = execsPerProc });
            cmd.Parameters.Add(new DuckDBParameter { Value = cpuPerProc });
            cmd.Parameters.Add(new DuckDBParameter { Value = cpuPerProc * 2 }); // elapsed ~2x CPU
            cmd.Parameters.Add(new DuckDBParameter { Value = execsPerProc * 1000L }); // 1000 reads/exec

            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Seeds query_snapshots (active queries) with snapshot data.
    /// </summary>
    internal async Task SeedActiveQueriesAsync(int longRunning, int blocked,
        int parallel, long maxElapsedMs = 120_000, int maxDop = 8)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        var total = longRunning + blocked + parallel + 5; // +5 short normal queries

        for (var i = 0; i < total; i++)
        {
            var isLongRunning = i < longRunning;
            var isBlocked = i >= longRunning && i < longRunning + blocked;
            var isParallel = i >= longRunning + blocked && i < longRunning + blocked + parallel;

            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO query_snapshots
    (collection_id, collection_time, server_id, server_name,
     session_id, total_elapsed_time_ms, blocking_session_id,
     dop, status, cpu_time_ms)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)";

            var t = TestPeriodStart.AddMinutes(i * 5);
            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = t });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = 50 + i });
            cmd.Parameters.Add(new DuckDBParameter { Value = isLongRunning ? maxElapsedMs : 5_000L });
            cmd.Parameters.Add(new DuckDBParameter { Value = isBlocked ? 51 : 0 });
            cmd.Parameters.Add(new DuckDBParameter { Value = isParallel ? maxDop : 1 });
            cmd.Parameters.Add(new DuckDBParameter { Value = isBlocked ? "suspended" : "running" });
            cmd.Parameters.Add(new DuckDBParameter { Value = isLongRunning ? maxElapsedMs / 2 : 2_000L });

            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Seeds running_jobs with job execution data.
    /// </summary>
    internal async Task SeedRunningJobsAsync(int totalJobs, int runningLong,
        double maxPctAvg = 300, long maxDurationSeconds = 7200)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        for (var i = 0; i < totalJobs; i++)
        {
            var isLong = i < runningLong;

            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO running_jobs
    (collection_time, server_id, server_name, job_name, job_id,
     job_enabled, start_time, current_duration_seconds,
     avg_duration_seconds, p95_duration_seconds, successful_run_count,
     is_running_long, percent_of_average)
VALUES ($1, $2, $3, $4, $5, true, $6, $7, $8, $9, 100, $10, $11)";

            var t = TestPeriodEnd.AddMinutes(-10);
            cmd.Parameters.Add(new DuckDBParameter { Value = t });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = $"Test Job {i}" });
            cmd.Parameters.Add(new DuckDBParameter { Value = Guid.NewGuid().ToString() });
            cmd.Parameters.Add(new DuckDBParameter { Value = t.AddSeconds(-(isLong ? maxDurationSeconds : 300)) });
            cmd.Parameters.Add(new DuckDBParameter { Value = isLong ? maxDurationSeconds : 300L });
            cmd.Parameters.Add(new DuckDBParameter { Value = isLong ? maxDurationSeconds / 3 : 300L }); // avg
            cmd.Parameters.Add(new DuckDBParameter { Value = isLong ? maxDurationSeconds / 2 : 400L }); // p95
            cmd.Parameters.Add(new DuckDBParameter { Value = isLong });
            cmd.Parameters.Add(new DuckDBParameter { Value = isLong ? maxPctAvg : 100.0 });

            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Seeds session_stats with per-application connection data.
    /// </summary>
    internal async Task SeedSessionStatsAsync(
        params (string appName, int connections, int running, int sleeping)[] apps)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        foreach (var (appName, conns, running, sleeping) in apps)
        {
            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO session_stats
    (collection_id, collection_time, server_id, server_name,
     program_name, connection_count, running_count, sleeping_count, dormant_count)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, 0)";

            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestPeriodEnd });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = appName });
            cmd.Parameters.Add(new DuckDBParameter { Value = conns });
            cmd.Parameters.Add(new DuckDBParameter { Value = running });
            cmd.Parameters.Add(new DuckDBParameter { Value = sleeping });

            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Seeds trace_flags with active global flags.
    /// </summary>
    internal async Task SeedTraceFlagsAsync(params int[] flags)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        foreach (var flag in flags)
        {
            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO trace_flags
    (config_id, capture_time, server_id, server_name,
     trace_flag, status, is_global, is_session)
VALUES ($1, $2, $3, $4, $5, true, true, false)";

            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestPeriodEnd });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = flag });

            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Seeds server_properties with hardware/edition info.
    /// </summary>
    internal async Task SeedServerPropertiesAsync(int cpuCount, int htRatio,
        long physicalMemMb, int socketCount = 2, int coresPerSocket = 0,
        bool hadrEnabled = false, string edition = "Standard Edition",
        int engineEdition = 2, string? serviceObjective = null, int? vcoreCount = null)
    {
        if (coresPerSocket == 0) coresPerSocket = cpuCount / (socketCount * 2); // assume HT

        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        using var cmd = connection.CreateCommand();
        cmd.CommandText = @"
INSERT INTO server_properties
    (collection_id, collection_time, server_id, server_name,
     edition, product_version, product_level, engine_edition,
     cpu_count, hyperthread_ratio, physical_memory_mb,
     socket_count, cores_per_socket, is_hadr_enabled,
     service_objective, vcore_count)
VALUES ($1, $2, $3, $4, $5, '16.0.4150.1', 'RTM', $12,
        $6, $7, $8, $9, $10, $11, $13, $14)";

        cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
        cmd.Parameters.Add(new DuckDBParameter { Value = TestPeriodEnd });
        cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
        cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
        cmd.Parameters.Add(new DuckDBParameter { Value = edition });
        cmd.Parameters.Add(new DuckDBParameter { Value = cpuCount });
        cmd.Parameters.Add(new DuckDBParameter { Value = htRatio });
        cmd.Parameters.Add(new DuckDBParameter { Value = physicalMemMb });
        cmd.Parameters.Add(new DuckDBParameter { Value = socketCount });
        cmd.Parameters.Add(new DuckDBParameter { Value = coresPerSocket });
        cmd.Parameters.Add(new DuckDBParameter { Value = hadrEnabled });
        cmd.Parameters.Add(new DuckDBParameter { Value = engineEdition });
        cmd.Parameters.Add(new DuckDBParameter { Value = (object?)serviceObjective ?? DBNull.Value });
        cmd.Parameters.Add(new DuckDBParameter { Value = (object?)vcoreCount ?? DBNull.Value });

        await cmd.ExecuteNonQueryAsync();
    }

    /// <summary>
    /// Seeds database_size_stats with volume-level disk space data.
    /// </summary>
    internal async Task SeedDiskSpaceAsync(
        params (string mountPoint, double totalMb, double freeMb)[] volumes)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        foreach (var (mountPoint, totalMb, freeMb) in volumes)
        {
            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO database_size_stats
    (collection_id, collection_time, server_id, server_name,
     database_name, database_id, file_id, file_type_desc, file_name, physical_name,
     total_size_mb, used_size_mb,
     volume_mount_point, volume_total_mb, volume_free_mb)
VALUES ($1, $2, $3, $4, 'UserDB', 5, 1, 'ROWS', 'UserDB', 'D:\Data\UserDB.mdf',
        1000, 800, $5, $6, $7)";

            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestPeriodEnd });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = mountPoint });
            cmd.Parameters.Add(new DuckDBParameter { Value = totalMb });
            cmd.Parameters.Add(new DuckDBParameter { Value = freeMb });

            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// WS3: seeds database_size_stats file rows for the percent-autogrowth-on-large-files
    /// fact. Each tuple is (database, logicalName, fileType, totalSizeMb, isPercentGrowth,
    /// growthPct). Only large (&gt;= 10 GB) percent-growth files in NON-system databases should
    /// drive the FILE_AUTOGROWTH_PERCENT fact.
    /// </summary>
    internal async Task SeedPercentAutogrowthFilesAsync(
        params (string database, string logicalName, string fileType, double totalSizeMb, bool isPercentGrowth, int growthPct)[] files)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        var fileId = 1;
        foreach (var (database, logicalName, fileType, totalSizeMb, isPercentGrowth, growthPct) in files)
        {
            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO database_size_stats
    (collection_id, collection_time, server_id, server_name,
     database_name, database_id, file_id, file_type_desc, file_name, physical_name,
     total_size_mb, used_size_mb, is_percent_growth, growth_pct)
VALUES ($1, $2, $3, $4, $5, 7, $6, $7, $8, 'X:\Data\file.mdf', $9, NULL, $10, $11)";

            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestPeriodEnd });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = database });
            cmd.Parameters.Add(new DuckDBParameter { Value = fileId++ });
            cmd.Parameters.Add(new DuckDBParameter { Value = fileType });
            cmd.Parameters.Add(new DuckDBParameter { Value = logicalName });
            cmd.Parameters.Add(new DuckDBParameter { Value = totalSizeMb });
            cmd.Parameters.Add(new DuckDBParameter { Value = isPercentGrowth });
            cmd.Parameters.Add(new DuckDBParameter { Value = growthPct });

            await cmd.ExecuteNonQueryAsync();
        }
    }

    // ============================================
    // FinOps Test Scenarios
    // ============================================

    /// <summary>
    /// Scenario 1: Over-provisioned Enterprise server.
    /// 32 cores, 256GB RAM, but avg CPU 8%, buffer pool only 40GB of 256GB.
    ///
    /// Expected recommendations:
    ///   - CPU right-sizing (P95 &lt; 30%, many cores)
    ///   - Memory right-sizing (buffer pool &lt; 50% of physical RAM)
    /// </summary>
    public async Task SeedOverProvisionedEnterpriseAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        // 32 cores, 256GB RAM, but avg CPU 8%, buffer pool only 40GB of 256GB
        await SeedCpuUtilizationAsync(8, 2);
        await SeedMemoryStatsAsync(totalPhysicalMb: 262_144, bufferPoolMb: 40_960, targetMb: 245_760);
        await SeedServerPropertiesAsync(cpuCount: 32, htRatio: 2, physicalMemMb: 262_144,
            edition: "Enterprise Edition");
        await SeedFileSizeAsync(totalDataSizeMb: 51_200); // 50GB — tiny for 256GB RAM
    }

    /// <summary>
    /// Scenario 2: Idle databases with cost impact.
    /// 3 databases seeded — only 1 has query activity, the other 2 are idle.
    ///
    /// Expected recommendations:
    ///   - Dormant database detection (2 idle databases)
    /// </summary>
    public async Task SeedIdleDatabasesAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        // Seed database sizes for 3 databases + query activity for only 1
        await SeedDatabaseSizesForIdleTestAsync();
        await SeedQueryStatsForDatabaseAsync("ActiveDB", executions: 5000, cpuMs: 100_000);
    }

    /// <summary>
    /// Scenario 3: High impact query skew — one query consuming 80%+ of CPU.
    ///
    /// Expected: HighImpactScorer.Score() returns query "AAAA" with dominant CpuShare.
    /// </summary>
    public async Task SeedHighImpactQuerySkewAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        // 5 queries: one uses 80% CPU, rest split the remaining 20%
        await SeedQueryStatsForHighImpactAsync();
    }

    /// <summary>
    /// Scenario 4: Dev/test databases on a production server.
    /// Seeds database_size_stats with databases named "staging_app", "dev_analytics", "test_warehouse".
    ///
    /// NOTE: The recommendation engine detects dev/test databases via a LIVE SQL query
    /// (sys.databases WHERE name LIKE '%dev%'). This won't fire against DuckDB test data.
    /// The scenario documents the expected behavior but the live check will silently fail.
    /// Use this scenario to test the idle-database detection path instead.
    /// </summary>
    public async Task SeedDevTestDatabasesAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        await SeedDatabaseSizesWithNamesAsync("staging_app", "dev_analytics", "test_warehouse", "ProductionDB");
    }

    /// <summary>
    /// Scenario 5: Long-running maintenance jobs.
    /// Seeds running_jobs with a job that ran long 5+ times in 7 days.
    ///
    /// Expected recommendations:
    ///   - Maintenance window efficiency warning
    /// </summary>
    public async Task SeedLongRunningJobsAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        await SeedRunningJobsForMaintenanceTestAsync();
    }

    /// <summary>
    /// Scenario 6: Clean FinOps server — no recommendations expected.
    /// Healthy CPU (50%), good buffer pool ratio (75%), no idle databases.
    ///
    /// Expected: empty or minimal recommendation list.
    /// </summary>
    public async Task SeedCleanFinOpsServerAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        // Healthy: 50% CPU, 75% buffer pool ratio, no idle databases
        await SeedCpuUtilizationAsync(50, 5);
        await SeedMemoryStatsAsync(totalPhysicalMb: 65_536, bufferPoolMb: 49_152, targetMb: 57_344);
        await SeedServerPropertiesAsync(cpuCount: 8, htRatio: 2, physicalMemMb: 65_536,
            edition: "Developer Edition");
        await SeedFileSizeAsync(totalDataSizeMb: 204_800); // 200GB
    }

    // ============================================
    // FinOps Test Runner Methods
    // ============================================

    /// <summary>
    /// Runs the FinOps recommendation engine against test data.
    /// Pass empty strings for connectionString/utilityConnectionString to skip live SQL checks.
    /// </summary>
    public async Task<List<PerformanceMonitorLite.Services.RecommendationRow>> RunFinOpsRecommendationsAsync(
        PerformanceMonitorLite.Services.LocalDataService dataService, decimal monthlyCost = 10000m)
    {
        return await dataService.GetRecommendationsAsync(TestServerId, "", "", monthlyCost);
    }

    /// <summary>
    /// Runs the High Impact scorer against test data.
    /// </summary>
    public async Task<List<PerformanceMonitorLite.Services.HighImpactQueryRow>> RunHighImpactAnalysisAsync(
        PerformanceMonitorLite.Services.LocalDataService dataService, int hoursBack = 24)
    {
        return await dataService.GetHighImpactQueriesAsync(TestServerId, hoursBack);
    }

    // ============================================
    // FinOps Seed Helpers
    // ============================================

    /// <summary>
    /// Seeds database_size_stats with 3 databases for idle-database testing.
    /// "ActiveDB" will have query_stats activity (seeded separately).
    /// "OldReportsDB" (50GB) and "ArchiveDB" (100GB) have no activity — should be detected as idle.
    /// </summary>
    internal async Task SeedDatabaseSizesForIdleTestAsync()
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        var databases = new (string name, int dbId, decimal totalSizeMb)[]
        {
            ("ActiveDB",      10, 20_480),   // 20GB — active
            ("OldReportsDB",  11, 51_200),   // 50GB — idle
            ("ArchiveDB",     12, 102_400),  // 100GB — idle
        };

        foreach (var (name, dbId, totalSizeMb) in databases)
        {
            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO database_size_stats
    (collection_id, collection_time, server_id, server_name,
     database_name, database_id, file_id, file_type_desc, file_name, physical_name,
     total_size_mb, used_size_mb)
VALUES ($1, $2, $3, $4, $5, $6, 1, 'ROWS', $7, $8, $9, $10)";

            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestPeriodEnd });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = name });
            cmd.Parameters.Add(new DuckDBParameter { Value = dbId });
            cmd.Parameters.Add(new DuckDBParameter { Value = $"{name}.mdf" });
            cmd.Parameters.Add(new DuckDBParameter { Value = $"D:\\Data\\{name}.mdf" });
            cmd.Parameters.Add(new DuckDBParameter { Value = totalSizeMb });
            cmd.Parameters.Add(new DuckDBParameter { Value = totalSizeMb * 0.8m }); // 80% used

            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Seeds query_stats with activity for a specific database.
    /// Used to mark a database as "active" so it's excluded from idle detection.
    /// </summary>
    internal async Task SeedQueryStatsForDatabaseAsync(string databaseName, long executions, long cpuMs)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        // Spread across 16 collection points so it falls within time-range queries
        var execsPerPoint = executions / 16;
        var cpuPerPoint = cpuMs * 1000 / 16; // convert ms to microseconds for delta_worker_time

        for (var i = 0; i < 16; i++)
        {
            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO query_stats
    (collection_id, collection_time, server_id, server_name,
     database_name, query_hash, delta_execution_count,
     delta_worker_time, delta_elapsed_time, delta_logical_reads)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)";

            var t = TestPeriodStart.AddMinutes(i * 15);
            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = t });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = databaseName });
            cmd.Parameters.Add(new DuckDBParameter { Value = $"0xACTIVE{i:D4}" });
            cmd.Parameters.Add(new DuckDBParameter { Value = execsPerPoint });
            cmd.Parameters.Add(new DuckDBParameter { Value = cpuPerPoint });
            cmd.Parameters.Add(new DuckDBParameter { Value = cpuPerPoint * 2 });
            cmd.Parameters.Add(new DuckDBParameter { Value = execsPerPoint * 500L });

            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Seeds query_stats for high-impact skew testing.
    /// 5 queries with one dominant (80% CPU):
    ///   AAAA — 800,000ms CPU, 10,000 executions (the monster)
    ///   BBBB — 50,000ms CPU, 5,000 executions
    ///   CCCC — 50,000ms CPU, 2,000 executions
    ///   DDDD — 50,000ms CPU, 1,000 executions
    ///   EEEE — 50,000ms CPU, 500 executions
    /// </summary>
    internal async Task SeedQueryStatsForHighImpactAsync()
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        var queries = new (string hash, long cpuMs, long executions, long reads, long writes, long memoryKb)[]
        {
            ("AAAA", 800_000, 10_000, 50_000_000, 1_000_000, 512_000),  // The monster
            ("BBBB",  50_000,  5_000,  5_000_000,   100_000,  64_000),
            ("CCCC",  50_000,  2_000,  3_000_000,    50_000,  32_000),
            ("DDDD",  50_000,  1_000,  2_000_000,    25_000,  16_000),
            ("EEEE",  50_000,    500,  1_000_000,    10_000,   8_000),
        };

        foreach (var (hash, cpuMs, executions, reads, writes, memoryKb) in queries)
        {
            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO query_stats
    (collection_id, collection_time, server_id, server_name,
     database_name, query_hash, query_text,
     delta_execution_count, delta_worker_time, delta_elapsed_time,
     delta_logical_reads, delta_logical_writes, max_grant_kb)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)";

            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestPeriodEnd.AddMinutes(-30) }); // recent
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = "UserDB" });
            cmd.Parameters.Add(new DuckDBParameter { Value = hash });
            cmd.Parameters.Add(new DuckDBParameter { Value = $"SELECT /* {hash} */ * FROM dbo.SomeTable" });
            cmd.Parameters.Add(new DuckDBParameter { Value = executions });
            cmd.Parameters.Add(new DuckDBParameter { Value = cpuMs * 1000L }); // microseconds
            cmd.Parameters.Add(new DuckDBParameter { Value = cpuMs * 2000L }); // elapsed ~2x CPU
            cmd.Parameters.Add(new DuckDBParameter { Value = reads });
            cmd.Parameters.Add(new DuckDBParameter { Value = writes });
            cmd.Parameters.Add(new DuckDBParameter { Value = memoryKb });

            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Seeds database_size_stats with named databases.
    /// Used for dev/test detection testing and general size seeding.
    /// </summary>
    internal async Task SeedDatabaseSizesWithNamesAsync(params string[] databaseNames)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        for (var i = 0; i < databaseNames.Length; i++)
        {
            var name = databaseNames[i];
            var sizeMb = 10_240m + (i * 5_120m); // 10GB, 15GB, 20GB, 25GB, ...

            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO database_size_stats
    (collection_id, collection_time, server_id, server_name,
     database_name, database_id, file_id, file_type_desc, file_name, physical_name,
     total_size_mb, used_size_mb)
VALUES ($1, $2, $3, $4, $5, $6, 1, 'ROWS', $7, $8, $9, $10)";

            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestPeriodEnd });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = name });
            cmd.Parameters.Add(new DuckDBParameter { Value = 10 + i }); // database_id
            cmd.Parameters.Add(new DuckDBParameter { Value = $"{name}.mdf" });
            cmd.Parameters.Add(new DuckDBParameter { Value = $"D:\\Data\\{name}.mdf" });
            cmd.Parameters.Add(new DuckDBParameter { Value = sizeMb });
            cmd.Parameters.Add(new DuckDBParameter { Value = sizeMb * 0.7m }); // 70% used

            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Seeds running_jobs for maintenance window testing.
    /// Creates a "Weekly Index Rebuild" job that ran long 5 times in 7 days,
    /// and a normal "Stats Update" job for contrast.
    /// </summary>
    internal async Task SeedRunningJobsForMaintenanceTestAsync()
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        // "Weekly Index Rebuild" — ran long 5 times
        var jobId = Guid.NewGuid().ToString();
        for (var i = 0; i < 5; i++)
        {
            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO running_jobs
    (collection_time, server_id, server_name, job_name, job_id,
     job_enabled, start_time, current_duration_seconds,
     avg_duration_seconds, p95_duration_seconds, successful_run_count,
     is_running_long, percent_of_average)
VALUES ($1, $2, $3, $4, $5, true, $6, $7, $8, $9, 50, true, $10)";

            // Spread collections across the 7-day window the recommendation engine queries
            var collectionTime = DateTime.UtcNow.AddDays(-6).AddDays(i * 1.2);
            cmd.Parameters.Add(new DuckDBParameter { Value = collectionTime });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = "Weekly Index Rebuild" });
            cmd.Parameters.Add(new DuckDBParameter { Value = jobId });
            cmd.Parameters.Add(new DuckDBParameter { Value = collectionTime.AddSeconds(-900) }); // started 15min ago
            cmd.Parameters.Add(new DuckDBParameter { Value = 900L });   // current_duration_seconds (15min)
            cmd.Parameters.Add(new DuckDBParameter { Value = 300L });   // avg_duration_seconds (5min historical)
            cmd.Parameters.Add(new DuckDBParameter { Value = 450L });   // p95_duration_seconds
            cmd.Parameters.Add(new DuckDBParameter { Value = 300.0 });  // percent_of_average = 300%

            await cmd.ExecuteNonQueryAsync();
        }

        // "Stats Update" — normal job, not running long
        using var normalCmd = connection.CreateCommand();
        normalCmd.CommandText = @"
INSERT INTO running_jobs
    (collection_time, server_id, server_name, job_name, job_id,
     job_enabled, start_time, current_duration_seconds,
     avg_duration_seconds, p95_duration_seconds, successful_run_count,
     is_running_long, percent_of_average)
VALUES ($1, $2, $3, $4, $5, true, $6, 120, 100, 130, 200, false, 120.0)";

        normalCmd.Parameters.Add(new DuckDBParameter { Value = TestPeriodEnd.AddMinutes(-5) });
        normalCmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
        normalCmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
        normalCmd.Parameters.Add(new DuckDBParameter { Value = "Stats Update" });
        normalCmd.Parameters.Add(new DuckDBParameter { Value = Guid.NewGuid().ToString() });
        normalCmd.Parameters.Add(new DuckDBParameter { Value = TestPeriodEnd.AddMinutes(-7) });

        await normalCmd.ExecuteNonQueryAsync();
    }

    // ============================================
    // Phase 3 FinOps Test Scenarios
    // ============================================

    /// <summary>
    /// Scenario 7: VM Right-Sizing Target.
    /// 32 cores, 256GB RAM, but P95 CPU only 12%, buffer pool 50GB of 256GB (19%).
    ///
    /// Expected recommendations:
    ///   - CPU: reduce from 32 to 8 cores (P95 &lt; 15% → /4)
    ///   - Memory: reduce from 256GB to 64GB (ratio &lt; 25% → /4)
    /// </summary>
    public async Task SeedVmRightSizingTargetAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        // 32 cores, 256GB RAM, but P95 CPU only 12%, buffer pool 50GB of 256GB (19%)
        // Should recommend: 8 cores (P95 < 15%), 64GB RAM (ratio < 25%)
        await SeedCpuUtilizationAsync(12, 2);
        await SeedMemoryStatsAsync(totalPhysicalMb: 262_144, bufferPoolMb: 51_200, targetMb: 245_760);
        await SeedServerPropertiesAsync(cpuCount: 32, htRatio: 2, physicalMemMb: 262_144);
        await SeedFileSizeAsync(totalDataSizeMb: 51_200);
    }

    /// <summary>
    /// Scenario 8: Low IO Latency (Storage Tier).
    /// Avg read latency 1ms, avg write latency 0.5ms — doesn't need premium storage.
    ///
    /// Expected recommendations:
    ///   - Storage tier: database(s) with low IO latency — standard storage may suffice
    /// </summary>
    public async Task SeedLowIoLatencyAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        // Avg read latency 1ms, avg write latency 0.5ms — doesn't need premium storage
        await SeedIoLatencyAsync(totalReads: 1_000_000, stallReadMs: 1_000_000,
                                  totalWrites: 200_000, stallWriteMs: 100_000);
    }

    /// <summary>
    /// Scenario 9: Stable CPU for Reserved Capacity.
    /// 24+ samples all between 38-42% — very low variance, CV ~0.04.
    ///
    /// Expected recommendations:
    ///   - Reserved capacity candidate (CV &lt; 0.15, avg &gt; 20%)
    /// </summary>
    public async Task SeedStableCpuForReservedCapacityAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        // 24+ samples all between 38-42% — very low variance, CV ~0.04
        // Should trigger reserved capacity recommendation with "High" confidence
        await SeedCpuUtilizationWithVarianceAsync(mean: 40, variance: 2);
    }

    /// <summary>
    /// Scenario 10: Bursty CPU — should NOT trigger reserved capacity.
    /// Alternating 5% and 85% — high CV, should NOT fire.
    ///
    /// Expected: no reserved capacity recommendation.
    /// </summary>
    public async Task SeedBurstyCpuAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        // Alternating 5% and 85% — high CV, should NOT trigger
        await SeedCpuUtilizationAlternatingAsync(low: 5, high: 85);
    }

    /// <summary>
    /// Scenario 11: Azure SQL DB vCore tier.
    /// sys.dm_os_sys_info reports 20 cores (compute node total), but the database
    /// is provisioned as HS_Gen5_14 (14 vCores). FinOps should use 14, not 20.
    ///
    /// Expected: CPU right-sizing uses vcore_count (14) instead of cpu_count (20).
    /// </summary>
    public async Task SeedAzureSqlDbVcoreAsync()
    {
        await ClearTestDataAsync();
        await SeedTestServerAsync();

        // Azure SQL DB: node has 20 cores, but this DB has HS_Gen5_14 (14 vCores)
        // CPU at 8% avg — overprovisioned relative to 14 vCores
        await SeedCpuUtilizationAsync(8, 2);
        await SeedMemoryStatsAsync(totalPhysicalMb: 65_536, bufferPoolMb: 40_960, targetMb: 57_344);
        await SeedServerPropertiesAsync(cpuCount: 20, htRatio: 1, physicalMemMb: 65_536,
            edition: "SQL Azure", engineEdition: 5,
            serviceObjective: "HS_Gen5_14", vcoreCount: 14);
        await SeedFileSizeAsync(totalDataSizeMb: 51_200);
    }

    // ============================================
    // Phase 3 Seed Helpers
    // ============================================

    /// <summary>
    /// Seeds CPU utilization with controlled variance around a mean value.
    /// Produces 32 samples (8 hours at 15-min intervals) with values cycling
    /// through mean-variance, mean, mean+variance, mean in a deterministic pattern.
    /// </summary>
    internal async Task SeedCpuUtilizationWithVarianceAsync(int mean, int variance)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        // Pattern: mean-variance, mean, mean+variance, mean — repeating
        var offsets = new[] { -variance, 0, variance, 0 };

        for (var i = 0; i < 32; i++)
        {
            var cpuValue = Math.Clamp(mean + offsets[i % 4], 0, 100);

            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO cpu_utilization_stats
    (collection_id, collection_time, server_id, server_name,
     sample_time, sqlserver_cpu_utilization, other_process_cpu_utilization)
VALUES ($1, $2, $3, $4, $5, $6, $7)";

            var t = TestPeriodStart.AddMinutes(i * 15);
            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = t });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = t });
            cmd.Parameters.Add(new DuckDBParameter { Value = cpuValue });
            cmd.Parameters.Add(new DuckDBParameter { Value = 2 });

            await cmd.ExecuteNonQueryAsync();
        }
    }

    /// <summary>
    /// Seeds CPU utilization alternating between low and high values.
    /// Produces 32 samples with high coefficient of variation — should NOT
    /// trigger reserved capacity recommendations.
    /// </summary>
    internal async Task SeedCpuUtilizationAlternatingAsync(int low, int high)
    {
        using var readLock = _duckDb.AcquireReadLock();
        using var connection = _duckDb.CreateConnection();
        await connection.OpenAsync();

        for (var i = 0; i < 32; i++)
        {
            var cpuValue = i % 2 == 0 ? low : high;

            using var cmd = connection.CreateCommand();
            cmd.CommandText = @"
INSERT INTO cpu_utilization_stats
    (collection_id, collection_time, server_id, server_name,
     sample_time, sqlserver_cpu_utilization, other_process_cpu_utilization)
VALUES ($1, $2, $3, $4, $5, $6, $7)";

            var t = TestPeriodStart.AddMinutes(i * 15);
            cmd.Parameters.Add(new DuckDBParameter { Value = _nextId-- });
            cmd.Parameters.Add(new DuckDBParameter { Value = t });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerId });
            cmd.Parameters.Add(new DuckDBParameter { Value = TestServerName });
            cmd.Parameters.Add(new DuckDBParameter { Value = t });
            cmd.Parameters.Add(new DuckDBParameter { Value = cpuValue });
            cmd.Parameters.Add(new DuckDBParameter { Value = 2 });

            await cmd.ExecuteNonQueryAsync();
        }
    }
}
