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

using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using DuckDB.NET.Data;
using Microsoft.Data.SqlClient;

namespace PerformanceMonitorLite.Services;

public partial class LocalDataService
{
    /// <summary>
    /// Gets the latest Query Store snapshot for a server, aggregated across all databases.
    /// Shows top queries by total duration (execution_count * avg_duration).
    /// </summary>
    public async Task<List<Models.TimeSliceBucket>> GetQueryStoreSlicerDataAsync(
        int serverId, int hoursBack, DateTime? fromDate = null, DateTime? toDate = null)
    {
        using var connection = await OpenConnectionAsync();
        using var command = connection.CreateCommand();
        var (startTime, endTime) = GetTimeRange(hoursBack, fromDate, toDate);

        command.CommandText = @"
SELECT
    date_trunc('hour', collection_time) AS bucket,
    COUNT(DISTINCT query_id) AS query_count,
    COALESCE(SUM(CAST(avg_cpu_time_us AS DOUBLE) * execution_count), 0) / 1000.0 AS total_cpu_ms,
    COALESCE(SUM(CAST(avg_duration_us AS DOUBLE) * execution_count), 0) / 1000.0 AS total_duration_ms,
    COALESCE(SUM(CAST(avg_logical_io_reads AS DOUBLE) * execution_count), 0) AS total_reads,
    COALESCE(SUM(CAST(avg_logical_io_writes AS DOUBLE) * execution_count), 0) AS total_writes,
    COALESCE(SUM(CAST(avg_physical_io_reads AS DOUBLE) * execution_count), 0) AS total_physical_reads
FROM v_query_store_stats
WHERE server_id = $1
AND   collection_time >= $2
AND   collection_time <= $3
GROUP BY date_trunc('hour', collection_time)
ORDER BY bucket";

        command.Parameters.Add(new DuckDBParameter { Value = serverId });
        command.Parameters.Add(new DuckDBParameter { Value = startTime });
        command.Parameters.Add(new DuckDBParameter { Value = endTime });

        var items = new List<Models.TimeSliceBucket>();
        using var reader = await command.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            items.Add(new Models.TimeSliceBucket
            {
                BucketTimeUtc = reader.GetDateTime(0),
                SessionCount = reader.IsDBNull(1) ? 0 : Convert.ToInt64(reader.GetValue(1)),
                TotalCpu = reader.IsDBNull(2) ? 0 : ToDouble(reader.GetValue(2)),
                TotalElapsed = reader.IsDBNull(3) ? 0 : ToDouble(reader.GetValue(3)),
                TotalReads = reader.IsDBNull(4) ? 0 : ToDouble(reader.GetValue(4)),
                TotalWrites = reader.IsDBNull(5) ? 0 : ToDouble(reader.GetValue(5)),
                TotalLogicalReads = reader.IsDBNull(4) ? 0 : ToDouble(reader.GetValue(4)),
                Value = reader.IsDBNull(2) ? 0 : ToDouble(reader.GetValue(2)),
            });
        }
        return items;
    }

    public async Task<List<QueryStoreRow>> GetQueryStoreTopQueriesAsync(int serverId, int hoursBack = 24, int top = 50, DateTime? fromDate = null, DateTime? toDate = null, string? databaseName = null)
    {
        using var _q = TimeQuery("GetQueryStoreTopQueriesAsync", "v_query_store_stats top N");
        using var connection = await OpenConnectionAsync();
        using var command = connection.CreateCommand();

        var (startTime, endTime) = GetTimeRange(hoursBack, fromDate, toDate);

        command.CommandText = @"
WITH ranked AS (
    SELECT
        database_name,
        query_id,
        plan_id,
        query_hash,
        MAX(module_name) AS module_name,
        SUM(execution_count) AS total_executions,
        AVG(CAST(avg_duration_us AS DOUBLE)) / 1000.0 AS avg_duration_ms,
        AVG(CAST(avg_cpu_time_us AS DOUBLE)) / 1000.0 AS avg_cpu_time_ms,
        AVG(CAST(avg_logical_io_reads AS DOUBLE)) AS avg_logical_reads,
        AVG(CAST(avg_logical_io_writes AS DOUBLE)) AS avg_logical_writes,
        AVG(CAST(avg_physical_io_reads AS DOUBLE)) AS avg_physical_reads,
        AVG(CAST(avg_rowcount AS DOUBLE)) AS avg_rowcount,
        MIN(min_dop) AS min_dop,
        MAX(max_dop) AS max_dop,
        MAX(last_execution_time) AS last_execution_time,
        MAX(query_plan_hash) AS query_plan_hash,
        MAX(CASE WHEN is_forced_plan THEN TRUE ELSE FALSE END) AS is_forced_plan,
        MAX(plan_forcing_type) AS plan_forcing_type,
        MAX(execution_type_desc) AS execution_type_desc,
        MIN(first_execution_time) AS first_execution_time,
        AVG(CAST(avg_clr_time_us AS DOUBLE)) / 1000.0 AS avg_clr_time_ms,
        AVG(CAST(avg_tempdb_space_used AS DOUBLE)) AS avg_tempdb_space_used,
        AVG(CAST(avg_log_bytes_used AS DOUBLE)) AS avg_log_bytes_used,
        MAX(plan_type) AS plan_type,
        MAX(force_failure_count) AS force_failure_count,
        MAX(last_force_failure_reason) AS last_force_failure_reason,
        MAX(compatibility_level) AS compatibility_level
    FROM v_query_store_stats
    WHERE server_id = $1
    AND   collection_time >= $2
    AND   collection_time <= $3
    AND   ($5 IS NULL OR database_name = $5)
    GROUP BY database_name, query_id, plan_id, query_hash
    ORDER BY SUM(execution_count) * AVG(CAST(avg_duration_us AS DOUBLE)) DESC
    LIMIT $4 + 5
)
SELECT
    r.database_name,
    r.query_id,
    r.plan_id,
    r.query_hash,
    t.query_text,
    r.module_name,
    r.total_executions,
    r.avg_duration_ms,
    r.avg_cpu_time_ms,
    r.avg_logical_reads,
    r.avg_logical_writes,
    r.avg_physical_reads,
    r.avg_rowcount,
    r.min_dop,
    r.max_dop,
    r.last_execution_time,
    r.query_plan_hash,
    r.is_forced_plan,
    r.plan_forcing_type,
    NULL AS query_plan_text,
    r.execution_type_desc,
    r.first_execution_time,
    r.avg_clr_time_ms,
    r.avg_tempdb_space_used,
    r.avg_log_bytes_used,
    r.plan_type,
    r.force_failure_count,
    r.last_force_failure_reason,
    r.compatibility_level
FROM ranked r
LEFT JOIN LATERAL (
    SELECT query_text
    FROM v_query_store_stats
    WHERE server_id = $1
    AND   query_id = r.query_id
    AND   database_name = r.database_name
    AND   query_text IS NOT NULL
    ORDER BY collection_time DESC
    LIMIT 1
) t ON TRUE
WHERE t.query_text IS NULL OR t.query_text NOT LIKE 'WAITFOR%'
ORDER BY r.total_executions * r.avg_duration_ms DESC
LIMIT $4";

        command.Parameters.Add(new DuckDBParameter { Value = serverId });
        command.Parameters.Add(new DuckDBParameter { Value = startTime });
        command.Parameters.Add(new DuckDBParameter { Value = endTime });
        command.Parameters.Add(new DuckDBParameter { Value = top });
        command.Parameters.Add(new DuckDBParameter { Value = (object?)databaseName ?? DBNull.Value });

        var items = new List<QueryStoreRow>();
        using var reader = await command.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            items.Add(new QueryStoreRow
            {
                DatabaseName = reader.IsDBNull(0) ? "" : reader.GetString(0),
                QueryId = reader.IsDBNull(1) ? 0 : reader.GetInt64(1),
                PlanId = reader.IsDBNull(2) ? 0 : reader.GetInt64(2),
                QueryHash = reader.IsDBNull(3) ? "" : reader.GetString(3),
                QueryText = reader.IsDBNull(4) ? "" : reader.GetString(4),
                ModuleName = reader.IsDBNull(5) ? "" : reader.GetString(5),
                TotalExecutions = reader.IsDBNull(6) ? 0 : reader.GetInt64(6),
                AvgDurationMs = reader.IsDBNull(7) ? 0 : ToDouble(reader.GetValue(7)),
                AvgCpuTimeMs = reader.IsDBNull(8) ? 0 : ToDouble(reader.GetValue(8)),
                AvgLogicalReads = reader.IsDBNull(9) ? 0 : ToDouble(reader.GetValue(9)),
                AvgLogicalWrites = reader.IsDBNull(10) ? 0 : ToDouble(reader.GetValue(10)),
                AvgPhysicalReads = reader.IsDBNull(11) ? 0 : ToDouble(reader.GetValue(11)),
                AvgRowcount = reader.IsDBNull(12) ? 0 : ToDouble(reader.GetValue(12)),
                MinDop = reader.IsDBNull(13) ? 0 : Convert.ToInt64(reader.GetValue(13)),
                MaxDop = reader.IsDBNull(14) ? 0 : Convert.ToInt64(reader.GetValue(14)),
                LastExecutionTime = reader.IsDBNull(15) ? (DateTime?)null : reader.GetDateTime(15),
                QueryPlanHash = reader.IsDBNull(16) ? "" : reader.GetString(16),
                IsForcedPlan = !reader.IsDBNull(17) && reader.GetBoolean(17),
                PlanForcingType = reader.IsDBNull(18) ? "" : reader.GetString(18),
                QueryPlanText = reader.IsDBNull(19) ? null : reader.GetString(19),
                ExecutionTypeDesc = reader.IsDBNull(20) ? "" : reader.GetString(20),
                FirstExecutionTime = reader.IsDBNull(21) ? (DateTime?)null : reader.GetDateTime(21),
                AvgClrTimeMs = reader.IsDBNull(22) ? 0 : ToDouble(reader.GetValue(22)),
                AvgTempdbSpaceUsed = reader.IsDBNull(23) ? 0 : ToDouble(reader.GetValue(23)),
                AvgLogBytesUsed = reader.IsDBNull(24) ? 0 : ToDouble(reader.GetValue(24)),
                PlanType = reader.IsDBNull(25) ? "" : reader.GetString(25),
                ForceFailureCount = reader.IsDBNull(26) ? 0 : Convert.ToInt64(reader.GetValue(26)),
                LastForceFailureReason = reader.IsDBNull(27) ? "" : reader.GetString(27),
                CompatibilityLevel = reader.IsDBNull(28) ? 0 : Convert.ToInt32(reader.GetValue(28))
            });
        }

        return items;
    }

    /// <summary>
    /// Gets query store comparison between a current time range and a baseline range.
    /// Uses weighted averages (execution_count * avg_metric) for accurate aggregation.
    /// </summary>
    public async Task<List<Models.QueryStatsComparisonItem>> GetQueryStoreComparisonAsync(
        int serverId,
        DateTime currentStart, DateTime currentEnd,
        DateTime baselineStart, DateTime baselineEnd)
    {
        using var _q = TimeQuery("GetQueryStoreComparisonAsync", "v_query_store_stats comparison");
        using var connection = await OpenConnectionAsync();
        using var command = connection.CreateCommand();

        command.CommandText = @"
WITH top_current AS (
    SELECT database_name, query_hash
    FROM v_query_store_stats
    WHERE server_id = $1
    AND   collection_time >= $2 AND collection_time <= $3
    AND   execution_count > 0
    GROUP BY database_name, query_hash
    ORDER BY SUM(execution_count) DESC
    LIMIT 100
),
top_baseline AS (
    SELECT database_name, query_hash
    FROM v_query_store_stats
    WHERE server_id = $1
    AND   collection_time >= $4 AND collection_time <= $5
    AND   execution_count > 0
    GROUP BY database_name, query_hash
    ORDER BY SUM(execution_count) DESC
    LIMIT 100
),
top_hashes AS (
    SELECT DISTINCT database_name, query_hash
    FROM (
        SELECT * FROM top_current
        UNION ALL
        SELECT * FROM top_baseline
    ) combined
),
current_period AS (
    SELECT th.database_name, th.query_hash,
           SUM(qs.execution_count) AS exec_count,
           SUM(qs.execution_count * qs.avg_duration_us::DOUBLE) / NULLIF(SUM(qs.execution_count), 0) / 1000.0 AS avg_duration_ms,
           SUM(qs.execution_count * qs.avg_cpu_time_us::DOUBLE) / NULLIF(SUM(qs.execution_count), 0) / 1000.0 AS avg_cpu_ms,
           SUM(qs.execution_count * qs.avg_logical_io_reads::DOUBLE) / NULLIF(SUM(qs.execution_count), 0) AS avg_reads,
           MAX(qs.query_text) AS query_text
    FROM top_hashes th
    INNER JOIN v_query_store_stats qs
      ON  qs.query_hash IS NOT DISTINCT FROM th.query_hash
      AND qs.database_name IS NOT DISTINCT FROM th.database_name
    WHERE qs.server_id = $1
    AND   qs.collection_time >= $2 AND qs.collection_time <= $3
    AND   qs.execution_count > 0
    GROUP BY th.database_name, th.query_hash
),
baseline_period AS (
    SELECT th.database_name, th.query_hash,
           SUM(qs.execution_count) AS exec_count,
           SUM(qs.execution_count * qs.avg_duration_us::DOUBLE) / NULLIF(SUM(qs.execution_count), 0) / 1000.0 AS avg_duration_ms,
           SUM(qs.execution_count * qs.avg_cpu_time_us::DOUBLE) / NULLIF(SUM(qs.execution_count), 0) / 1000.0 AS avg_cpu_ms,
           SUM(qs.execution_count * qs.avg_logical_io_reads::DOUBLE) / NULLIF(SUM(qs.execution_count), 0) AS avg_reads,
           MAX(qs.query_text) AS query_text
    FROM top_hashes th
    INNER JOIN v_query_store_stats qs
      ON  qs.query_hash IS NOT DISTINCT FROM th.query_hash
      AND qs.database_name IS NOT DISTINCT FROM th.database_name
    WHERE qs.server_id = $1
    AND   qs.collection_time >= $4 AND qs.collection_time <= $5
    AND   qs.execution_count > 0
    GROUP BY th.database_name, th.query_hash
)
SELECT COALESCE(c.database_name, b.database_name) AS database_name,
       COALESCE(c.query_hash, b.query_hash) AS query_hash,
       COALESCE(c.query_text, b.query_text) AS query_text,
       c.exec_count, c.avg_duration_ms, c.avg_cpu_ms, c.avg_reads,
       b.exec_count AS baseline_exec_count,
       b.avg_duration_ms AS baseline_avg_duration_ms,
       b.avg_cpu_ms AS baseline_avg_cpu_ms,
       b.avg_reads AS baseline_avg_reads
FROM current_period c
FULL OUTER JOIN baseline_period b
  ON  c.database_name IS NOT DISTINCT FROM b.database_name
  AND c.query_hash IS NOT DISTINCT FROM b.query_hash;";

        command.Parameters.Add(new DuckDBParameter { Value = serverId });
        command.Parameters.Add(new DuckDBParameter { Value = currentStart });
        command.Parameters.Add(new DuckDBParameter { Value = currentEnd });
        command.Parameters.Add(new DuckDBParameter { Value = baselineStart });
        command.Parameters.Add(new DuckDBParameter { Value = baselineEnd });

        var items = new List<Models.QueryStatsComparisonItem>();
        using var reader = await command.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            items.Add(new Models.QueryStatsComparisonItem
            {
                DatabaseName = reader.IsDBNull(0) ? "" : reader.GetString(0),
                QueryHash = reader.IsDBNull(1) ? "" : reader.GetString(1),
                QueryText = reader.IsDBNull(2) ? "" : reader.GetString(2),
                ExecutionCount = reader.IsDBNull(3) ? 0 : ToInt64(reader.GetValue(3)),
                AvgDurationMs = reader.IsDBNull(4) ? 0 : ToDouble(reader.GetValue(4)),
                AvgCpuMs = reader.IsDBNull(5) ? 0 : ToDouble(reader.GetValue(5)),
                AvgReads = reader.IsDBNull(6) ? 0 : ToDouble(reader.GetValue(6)),
                BaselineExecutionCount = reader.IsDBNull(7) ? 0 : ToInt64(reader.GetValue(7)),
                BaselineAvgDurationMs = reader.IsDBNull(8) ? 0 : ToDouble(reader.GetValue(8)),
                BaselineAvgCpuMs = reader.IsDBNull(9) ? 0 : ToDouble(reader.GetValue(9)),
                BaselineAvgReads = reader.IsDBNull(10) ? 0 : ToDouble(reader.GetValue(10)),
            });
        }

        return items;
    }

    /// <summary>
    /// Gets collection-level history for a specific Query Store query (for drilldown).
    /// </summary>
    public async Task<List<QueryStoreHistoryRow>> GetQueryStoreHistoryAsync(int serverId, string databaseName, long queryId, long planId, int hoursBack = 24, DateTime? fromDate = null, DateTime? toDate = null)
    {
        using var connection = await OpenConnectionAsync();
        using var command = connection.CreateCommand();
        var (startTime, endTime) = GetTimeRange(hoursBack, fromDate, toDate);
        command.CommandText = @"
SELECT
    collection_time,
    execution_count,
    avg_duration_us / 1000.0 AS avg_duration_ms,
    avg_cpu_time_us / 1000.0 AS avg_cpu_time_ms,
    CAST(avg_logical_io_reads AS DOUBLE) AS avg_logical_reads,
    CAST(avg_logical_io_writes AS DOUBLE) AS avg_logical_writes,
    CAST(avg_physical_io_reads AS DOUBLE) AS avg_physical_reads,
    CAST(avg_rowcount AS DOUBLE) AS avg_rowcount,
    last_execution_time,
    min_dop,
    max_dop
FROM v_query_store_stats
WHERE server_id = $1
AND   database_name = $2
AND   query_id = $3
AND   plan_id = $4
AND   collection_time >= $5
AND   collection_time <= $6
ORDER BY collection_time";

        command.Parameters.Add(new DuckDBParameter { Value = serverId });
        command.Parameters.Add(new DuckDBParameter { Value = databaseName });
        command.Parameters.Add(new DuckDBParameter { Value = queryId });
        command.Parameters.Add(new DuckDBParameter { Value = planId });
        command.Parameters.Add(new DuckDBParameter { Value = startTime });
        command.Parameters.Add(new DuckDBParameter { Value = endTime });

        var items = new List<QueryStoreHistoryRow>();
        using var reader = await command.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            items.Add(new QueryStoreHistoryRow
            {
                CollectionTime = reader.GetDateTime(0),
                ExecutionCount = reader.IsDBNull(1) ? 0 : reader.GetInt64(1),
                AvgDurationMs = reader.IsDBNull(2) ? 0 : ToDouble(reader.GetValue(2)),
                AvgCpuTimeMs = reader.IsDBNull(3) ? 0 : ToDouble(reader.GetValue(3)),
                AvgLogicalReads = reader.IsDBNull(4) ? 0 : ToDouble(reader.GetValue(4)),
                AvgLogicalWrites = reader.IsDBNull(5) ? 0 : ToDouble(reader.GetValue(5)),
                AvgPhysicalReads = reader.IsDBNull(6) ? 0 : ToDouble(reader.GetValue(6)),
                AvgRowcount = reader.IsDBNull(7) ? 0 : ToDouble(reader.GetValue(7)),
                LastExecutionTime = reader.IsDBNull(8) ? (DateTime?)null : reader.GetDateTime(8),
                MinDop = reader.IsDBNull(9) ? 0 : Convert.ToInt64(reader.GetValue(9)),
                MaxDop = reader.IsDBNull(10) ? 0 : Convert.ToInt64(reader.GetValue(10))
            });
        }

        return items;
    }

    /// <summary>
    /// Gets distinct databases that have Query Store data collected.
    /// </summary>
    public async Task<List<string>> GetQueryStoreDatabasesAsync(int serverId, int hoursBack = 24)
    {
        using var connection = await OpenConnectionAsync();
        using var command = connection.CreateCommand();
        command.CommandText = @"
SELECT DISTINCT database_name
FROM v_query_store_stats
WHERE server_id = $1
AND   collection_time >= $2
ORDER BY database_name";

        command.Parameters.Add(new DuckDBParameter { Value = serverId });
        command.Parameters.Add(new DuckDBParameter { Value = DateTime.UtcNow.AddHours(-hoursBack) });

        var items = new List<string>();
        using var reader = await command.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            items.Add(reader.GetString(0));
        }
        return items;
    }

    /// <summary>
    /// Fetches a query plan on-demand from Query Store by plan_id.
    /// Uses three-part naming with sp_executesql for Azure SQL DB compatibility.
    /// </summary>
    public static async Task<string?> FetchQueryStorePlanAsync(string connectionString, string databaseName, long planId)
    {
        using var connection = new SqlConnection(connectionString);
        await connection.OpenAsync();

        /*
        Validate the database name exists and get the QUOTENAME'd version
        to prevent SQL injection via malicious database names
        */
        var quotedDbName = await GetValidatedDatabaseNameAsync(connection, databaseName);
        if (quotedDbName == null)
        {
            return null;
        }

        /*
        Use three-part naming (database.sys.sp_executesql) instead of USE statement
        for Azure SQL DB compatibility
        */
        var query = $@"
EXECUTE {quotedDbName}.sys.sp_executesql
    N'
SELECT TOP (1)
    query_plan = CONVERT(nvarchar(max), qsp.query_plan)
FROM sys.query_store_plan AS qsp
WHERE qsp.plan_id = @plan_id
AND   qsp.query_plan IS NOT NULL
OPTION(RECOMPILE);',
    N'@plan_id bigint',
    @plan_id;";

        using var command = new SqlCommand(query, connection) { CommandTimeout = 30 };
        command.Parameters.Add(new SqlParameter("@plan_id", SqlDbType.BigInt) { Value = planId });
        var result = await command.ExecuteScalarAsync();
        return result as string;
    }
    /// <summary>
    /// Gets Query Store duration trend — SUM(execution_count * avg_duration) per collection snapshot.
    /// </summary>
    public async Task<List<QueryTrendPoint>> GetQueryStoreDurationTrendAsync(int serverId, int hoursBack = 24, DateTime? fromDate = null, DateTime? toDate = null)
    {
        using var connection = await OpenConnectionAsync();
        using var command = connection.CreateCommand();

        var (startTime, endTime) = GetTimeRange(hoursBack, fromDate, toDate);

        command.CommandText = @"
WITH raw AS
(
    SELECT
        collection_time,
        SUM(execution_count * avg_duration_us / 1000.0) AS total_duration_ms,
        SUM(execution_count) AS total_executions,
        date_diff('second', LAG(collection_time) OVER (ORDER BY collection_time), collection_time) AS interval_seconds
    FROM v_query_store_stats
    WHERE server_id = $1
    AND   collection_time >= $2
    AND   collection_time <= $3
    GROUP BY collection_time
)
SELECT
    collection_time,
    CASE WHEN interval_seconds > 0 THEN total_duration_ms / interval_seconds ELSE 0 END AS duration_ms_per_second,
    CASE WHEN interval_seconds > 0 THEN CAST(total_executions AS DOUBLE) / interval_seconds ELSE 0 END AS executions_per_second
FROM raw
ORDER BY collection_time";

        command.Parameters.Add(new DuckDBParameter { Value = serverId });
        command.Parameters.Add(new DuckDBParameter { Value = startTime });
        command.Parameters.Add(new DuckDBParameter { Value = endTime });

        var items = new List<QueryTrendPoint>();
        using var reader = await command.ExecuteReaderAsync();
        while (await reader.ReadAsync())
        {
            items.Add(new QueryTrendPoint
            {
                CollectionTime = reader.GetDateTime(0),
                Value = reader.IsDBNull(1) ? 0 : ToDouble(reader.GetValue(1)),
                ExecutionCount = reader.IsDBNull(2) ? 0 : (long)ToDouble(reader.GetValue(2))
            });
        }
        return items;
    }
}

public class QueryStoreRow
{
    public string DatabaseName { get; set; } = "";
    public long QueryId { get; set; }
    public long PlanId { get; set; }
    public string QueryHash { get; set; } = "";
    public string QueryText { get; set; } = "";
    public string ModuleName { get; set; } = "";
    public long TotalExecutions { get; set; }
    public double AvgDurationMs { get; set; }
    public double AvgCpuTimeMs { get; set; }
    public double AvgLogicalReads { get; set; }
    public double AvgLogicalWrites { get; set; }
    public double AvgPhysicalReads { get; set; }
    public double AvgRowcount { get; set; }
    public long MinDop { get; set; }
    public long MaxDop { get; set; }
    public DateTime? LastExecutionTime { get; set; }
    public string QueryPlanHash { get; set; } = "";
    public bool IsForcedPlan { get; set; }
    public string PlanForcingType { get; set; } = "";
    public string? QueryPlanText { get; set; }
    public string ExecutionTypeDesc { get; set; } = "";
    public DateTime? FirstExecutionTime { get; set; }
    public double AvgClrTimeMs { get; set; }
    public double AvgTempdbSpaceUsed { get; set; }
    public double AvgLogBytesUsed { get; set; }
    public string PlanType { get; set; } = "";
    public long ForceFailureCount { get; set; }
    public string LastForceFailureReason { get; set; } = "";
    public int CompatibilityLevel { get; set; }
    public bool HasQueryPlan => !string.IsNullOrEmpty(QueryPlanText);
    public string FirstExecutionTimeLocal => ServerTimeHelper.FormatServerTime(FirstExecutionTime);
    public string LastExecutionTimeLocal => ServerTimeHelper.FormatServerTime(LastExecutionTime);
    public double TotalCpuMs => TotalExecutions * AvgCpuTimeMs;
    public double TotalDurationMs => TotalExecutions * AvgDurationMs;
}

public class QueryStoreHistoryRow
{
    public DateTime CollectionTime { get; set; }
    public long ExecutionCount { get; set; }
    public double AvgDurationMs { get; set; }
    public double AvgCpuTimeMs { get; set; }
    public double AvgLogicalReads { get; set; }
    public double AvgLogicalWrites { get; set; }
    public double AvgPhysicalReads { get; set; }
    public double AvgRowcount { get; set; }
    public DateTime? LastExecutionTime { get; set; }
    public long MinDop { get; set; }
    public long MaxDop { get; set; }
    public double TotalDurationMs => ExecutionCount * AvgDurationMs;
    public double TotalCpuMs => ExecutionCount * AvgCpuTimeMs;
    public string CollectionTimeLocal => ServerTimeHelper.FormatServerTime(CollectionTime);
    public string LastExecutionTimeLocal => ServerTimeHelper.FormatServerTime(LastExecutionTime);
}
