Schema.cs

using System.Collections.Generic;

namespace PerformanceMonitorLite.Database;

/// <summary>
/// Contains all DuckDB table schema definitions as SQL constants.
/// </summary>
public static class Schema
{
    public const string CreateServersTable = @"
CREATE TABLE IF NOT EXISTS servers (
    server_id INTEGER PRIMARY KEY,
    server_name VARCHAR NOT NULL,
    display_name VARCHAR,
    use_windows_auth BOOLEAN NOT NULL DEFAULT TRUE,
    username VARCHAR,
    is_enabled BOOLEAN NOT NULL DEFAULT TRUE,
    created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    modified_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

    public const string CreateCollectionScheduleTable = @"
CREATE TABLE IF NOT EXISTS collection_schedule (
    schedule_id INTEGER PRIMARY KEY,
    collector_name VARCHAR NOT NULL UNIQUE,
    enabled BOOLEAN NOT NULL DEFAULT TRUE,
    frequency_minutes INTEGER NOT NULL DEFAULT 15,
    last_run_time TIMESTAMP,
    next_run_time TIMESTAMP,
    max_duration_minutes INTEGER DEFAULT 5,
    retention_days INTEGER DEFAULT 30,
    description VARCHAR,
    created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    modified_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";

    public const string CreateCollectionLogTable = @"
CREATE TABLE IF NOT EXISTS collection_log (
    log_id BIGINT PRIMARY KEY,
    server_id INTEGER NOT NULL,
    server_name VARCHAR,
    collector_name VARCHAR NOT NULL,
    collection_time TIMESTAMP NOT NULL,
    duration_ms INTEGER,
    status VARCHAR NOT NULL,
    error_message VARCHAR,
    rows_collected INTEGER,
    sql_duration_ms INTEGER,
    duckdb_duration_ms INTEGER
)";

    public const string CreateWaitStatsTable = @"
CREATE TABLE IF NOT EXISTS wait_stats (
    collection_id BIGINT PRIMARY KEY,
    collection_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    wait_type VARCHAR NOT NULL,
    waiting_tasks_count BIGINT,
    wait_time_ms BIGINT,
    signal_wait_time_ms BIGINT,
    delta_waiting_tasks BIGINT,
    delta_wait_time_ms BIGINT,
    delta_signal_wait_time_ms BIGINT
)";

    public const string CreateQueryStatsTable = @"
CREATE TABLE IF NOT EXISTS query_stats (
    collection_id BIGINT PRIMARY KEY,
    collection_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    database_name VARCHAR,
    query_hash VARCHAR,
    query_plan_hash VARCHAR,
    creation_time TIMESTAMP,
    last_execution_time TIMESTAMP,
    execution_count BIGINT,
    total_worker_time BIGINT,
    total_elapsed_time BIGINT,
    total_logical_reads BIGINT,
    total_logical_writes BIGINT,
    total_physical_reads BIGINT,
    total_clr_time BIGINT,
    total_rows BIGINT,
    total_spills BIGINT,
    min_worker_time BIGINT,
    max_worker_time BIGINT,
    min_elapsed_time BIGINT,
    max_elapsed_time BIGINT,
    min_physical_reads BIGINT,
    max_physical_reads BIGINT,
    min_rows BIGINT,
    max_rows BIGINT,
    min_dop BIGINT,
    max_dop BIGINT,
    min_grant_kb BIGINT,
    max_grant_kb BIGINT,
    min_used_grant_kb BIGINT,
    max_used_grant_kb BIGINT,
    min_ideal_grant_kb BIGINT,
    max_ideal_grant_kb BIGINT,
    min_reserved_threads BIGINT,
    max_reserved_threads BIGINT,
    min_used_threads BIGINT,
    max_used_threads BIGINT,
    min_spills BIGINT,
    max_spills BIGINT,
    query_text VARCHAR,
    query_plan_xml VARCHAR,
    sql_handle VARCHAR,
    plan_handle VARCHAR,
    delta_execution_count BIGINT,
    delta_worker_time BIGINT,
    delta_elapsed_time BIGINT,
    delta_logical_reads BIGINT,
    delta_logical_writes BIGINT,
    delta_physical_reads BIGINT,
    delta_rows BIGINT,
    delta_spills BIGINT
)";

    public const string CreateCpuUtilizationStatsTable = @"
CREATE TABLE IF NOT EXISTS cpu_utilization_stats (
    collection_id BIGINT PRIMARY KEY,
    collection_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    sample_time TIMESTAMP NOT NULL,
    sqlserver_cpu_utilization INTEGER,
    other_process_cpu_utilization INTEGER
)";

    public const string CreateFileIoStatsTable = @"
CREATE TABLE IF NOT EXISTS file_io_stats (
    collection_id BIGINT PRIMARY KEY,
    collection_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    database_name VARCHAR,
    file_name VARCHAR,
    file_type VARCHAR,
    physical_name VARCHAR,
    size_mb DECIMAL(18,2),
    num_of_reads BIGINT,
    num_of_writes BIGINT,
    read_bytes BIGINT,
    write_bytes BIGINT,
    io_stall_read_ms BIGINT,
    io_stall_write_ms BIGINT,
    io_stall_queued_read_ms BIGINT,
    io_stall_queued_write_ms BIGINT,
    delta_reads BIGINT,
    delta_writes BIGINT,
    delta_read_bytes BIGINT,
    delta_write_bytes BIGINT,
    delta_stall_read_ms BIGINT,
    delta_stall_write_ms BIGINT,
    delta_stall_queued_read_ms BIGINT,
    delta_stall_queued_write_ms BIGINT
)";

    public const string CreateMemoryStatsTable = @"
CREATE TABLE IF NOT EXISTS memory_stats (
    collection_id BIGINT PRIMARY KEY,
    collection_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    total_physical_memory_mb DECIMAL(18,2),
    available_physical_memory_mb DECIMAL(18,2),
    total_page_file_mb DECIMAL(18,2),
    available_page_file_mb DECIMAL(18,2),
    system_memory_state VARCHAR,
    sql_memory_model VARCHAR,
    target_server_memory_mb DECIMAL(18,2),
    total_server_memory_mb DECIMAL(18,2),
    buffer_pool_mb DECIMAL(18,2),
    plan_cache_mb DECIMAL(18,2),
    max_workers_count INTEGER,
    current_workers_count INTEGER
)";

    public const string CreateMemoryClerksTable = @"
CREATE TABLE IF NOT EXISTS memory_clerks (
    collection_id BIGINT PRIMARY KEY,
    collection_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    clerk_type VARCHAR NOT NULL,
    memory_mb DECIMAL(18,2)
)";

    public const string CreateDeadlocksTable = @"
CREATE TABLE IF NOT EXISTS deadlocks (
    deadlock_id BIGINT PRIMARY KEY,
    collection_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    deadlock_time TIMESTAMP,
    victim_process_id VARCHAR,
    victim_sql_text VARCHAR,
    deadlock_graph_xml VARCHAR
)";

    public const string CreateProcedureStatsTable = @"
CREATE TABLE IF NOT EXISTS procedure_stats (
    collection_id BIGINT PRIMARY KEY,
    collection_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    database_name VARCHAR,
    schema_name VARCHAR,
    object_name VARCHAR,
    object_type VARCHAR,
    cached_time TIMESTAMP,
    last_execution_time TIMESTAMP,
    execution_count BIGINT,
    total_worker_time BIGINT,
    total_elapsed_time BIGINT,
    total_logical_reads BIGINT,
    total_physical_reads BIGINT,
    total_logical_writes BIGINT,
    min_worker_time BIGINT,
    max_worker_time BIGINT,
    min_elapsed_time BIGINT,
    max_elapsed_time BIGINT,
    min_logical_reads BIGINT,
    max_logical_reads BIGINT,
    min_physical_reads BIGINT,
    max_physical_reads BIGINT,
    min_logical_writes BIGINT,
    max_logical_writes BIGINT,
    total_spills BIGINT,
    min_spills BIGINT,
    max_spills BIGINT,
    sql_handle VARCHAR,
    plan_handle VARCHAR,
    delta_execution_count BIGINT,
    delta_worker_time BIGINT,
    delta_elapsed_time BIGINT,
    delta_logical_reads BIGINT,
    delta_logical_writes BIGINT,
    delta_physical_reads BIGINT
)";

    public const string CreateQueryStoreStatsTable = @"
CREATE TABLE IF NOT EXISTS query_store_stats (
    collection_id BIGINT PRIMARY KEY,
    collection_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    database_name VARCHAR NOT NULL,
    query_id BIGINT,
    plan_id BIGINT,
    execution_type_desc VARCHAR,
    first_execution_time TIMESTAMP,
    last_execution_time TIMESTAMP,
    module_name VARCHAR,
    query_text VARCHAR,
    query_hash VARCHAR,
    execution_count BIGINT,
    avg_duration_us BIGINT,
    min_duration_us BIGINT,
    max_duration_us BIGINT,
    avg_cpu_time_us BIGINT,
    min_cpu_time_us BIGINT,
    max_cpu_time_us BIGINT,
    avg_logical_io_reads BIGINT,
    min_logical_io_reads BIGINT,
    max_logical_io_reads BIGINT,
    avg_logical_io_writes BIGINT,
    min_logical_io_writes BIGINT,
    max_logical_io_writes BIGINT,
    avg_physical_io_reads BIGINT,
    min_physical_io_reads BIGINT,
    max_physical_io_reads BIGINT,
    avg_clr_time_us BIGINT,
    min_clr_time_us BIGINT,
    max_clr_time_us BIGINT,
    min_dop BIGINT,
    max_dop BIGINT,
    avg_query_max_used_memory BIGINT,
    min_query_max_used_memory BIGINT,
    max_query_max_used_memory BIGINT,
    avg_rowcount BIGINT,
    min_rowcount BIGINT,
    max_rowcount BIGINT,
    avg_num_physical_io_reads BIGINT,
    min_num_physical_io_reads BIGINT,
    max_num_physical_io_reads BIGINT,
    avg_log_bytes_used BIGINT,
    min_log_bytes_used BIGINT,
    max_log_bytes_used BIGINT,
    avg_tempdb_space_used BIGINT,
    min_tempdb_space_used BIGINT,
    max_tempdb_space_used BIGINT,
    plan_type VARCHAR,
    plan_forcing_type VARCHAR,
    is_forced_plan BOOLEAN,
    force_failure_count BIGINT,
    last_force_failure_reason VARCHAR,
    compatibility_level INTEGER,
    query_plan_text VARCHAR,
    query_plan_hash VARCHAR
)";

    public const string CreateQuerySnapshotsTable = @"
CREATE TABLE IF NOT EXISTS query_snapshots (
    collection_id BIGINT PRIMARY KEY,
    collection_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    session_id INTEGER,
    database_name VARCHAR,
    elapsed_time_formatted VARCHAR,
    query_text VARCHAR,
    query_plan VARCHAR,
    live_query_plan VARCHAR,
    status VARCHAR,
    blocking_session_id INTEGER,
    wait_type VARCHAR,
    wait_time_ms BIGINT,
    wait_resource VARCHAR,
    cpu_time_ms BIGINT,
    total_elapsed_time_ms BIGINT,
    reads BIGINT,
    writes BIGINT,
    logical_reads BIGINT,
    granted_query_memory_gb DECIMAL(18,2),
    transaction_isolation_level VARCHAR,
    dop INTEGER,
    parallel_worker_count INTEGER,
    login_name VARCHAR,
    host_name VARCHAR,
    program_name VARCHAR,
    open_transaction_count INTEGER,
    percent_complete DECIMAL(5,2)
)";

    public const string CreateTempdbStatsTable = @"
CREATE TABLE IF NOT EXISTS tempdb_stats (
    collection_id BIGINT PRIMARY KEY,
    collection_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    user_object_reserved_mb DECIMAL(18,2),
    internal_object_reserved_mb DECIMAL(18,2),
    version_store_reserved_mb DECIMAL(18,2),
    total_reserved_mb DECIMAL(18,2),
    unallocated_mb DECIMAL(18,2),
    total_sessions_using_tempdb BIGINT,
    top_session_id INTEGER,
    top_session_tempdb_mb DECIMAL(18,2)
)";

    public const string CreatePerfmonStatsTable = @"
CREATE TABLE IF NOT EXISTS perfmon_stats (
    collection_id BIGINT PRIMARY KEY,
    collection_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    object_name VARCHAR NOT NULL,
    counter_name VARCHAR NOT NULL,
    instance_name VARCHAR,
    cntr_value BIGINT,
    delta_cntr_value BIGINT,
    sample_interval_seconds INTEGER
)";

    public const string CreateServerConfigTable = @"
CREATE TABLE IF NOT EXISTS server_config (
    config_id BIGINT PRIMARY KEY,
    capture_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    configuration_name VARCHAR NOT NULL,
    value_configured BIGINT,
    value_in_use BIGINT,
    is_dynamic BOOLEAN,
    is_advanced BOOLEAN
)";

    public const string CreateMemoryGrantStatsTable = @"
CREATE TABLE IF NOT EXISTS memory_grant_stats (
    collection_id BIGINT PRIMARY KEY,
    collection_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    resource_semaphore_id SMALLINT,
    pool_id INTEGER,
    target_memory_mb DECIMAL(18,2),
    max_target_memory_mb DECIMAL(18,2),
    total_memory_mb DECIMAL(18,2),
    available_memory_mb DECIMAL(18,2),
    granted_memory_mb DECIMAL(18,2),
    used_memory_mb DECIMAL(18,2),
    grantee_count INTEGER,
    waiter_count INTEGER,
    timeout_error_count BIGINT,
    forced_grant_count BIGINT,
    timeout_error_count_delta BIGINT,
    forced_grant_count_delta BIGINT
)";

    public const string CreateWaitingTasksTable = @"
CREATE TABLE IF NOT EXISTS waiting_tasks (
    collection_id BIGINT PRIMARY KEY,
    collection_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    session_id INTEGER,
    wait_type VARCHAR,
    wait_duration_ms BIGINT,
    blocking_session_id INTEGER,
    resource_description VARCHAR,
    database_name VARCHAR
)";

    public const string CreateBlockedProcessReportsTable = @"
CREATE TABLE IF NOT EXISTS blocked_process_reports (
    blocked_report_id BIGINT PRIMARY KEY,
    collection_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    event_time TIMESTAMP,
    database_name VARCHAR,
    blocked_spid INTEGER,
    blocked_ecid INTEGER,
    blocking_spid INTEGER,
    blocking_ecid INTEGER,
    wait_time_ms BIGINT,
    wait_resource VARCHAR,
    lock_mode VARCHAR,
    blocked_status VARCHAR,
    blocked_isolation_level VARCHAR,
    blocked_log_used BIGINT,
    blocked_transaction_count INTEGER,
    blocked_client_app VARCHAR,
    blocked_host_name VARCHAR,
    blocked_login_name VARCHAR,
    blocked_sql_text VARCHAR,
    blocking_status VARCHAR,
    blocking_isolation_level VARCHAR,
    blocking_client_app VARCHAR,
    blocking_host_name VARCHAR,
    blocking_login_name VARCHAR,
    blocking_sql_text VARCHAR,
    blocked_transaction_name VARCHAR,
    blocking_transaction_name VARCHAR,
    blocked_last_tran_started TIMESTAMP,
    blocking_last_tran_started TIMESTAMP,
    blocked_last_batch_started TIMESTAMP,
    blocking_last_batch_started TIMESTAMP,
    blocked_last_batch_completed TIMESTAMP,
    blocking_last_batch_completed TIMESTAMP,
    blocked_priority INTEGER,
    blocking_priority INTEGER,
    blocked_process_report_xml VARCHAR
)";

    public const string CreateDatabaseConfigTable = @"
CREATE TABLE IF NOT EXISTS database_config (
    config_id BIGINT PRIMARY KEY,
    capture_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    database_name VARCHAR NOT NULL,
    state_desc VARCHAR,
    compatibility_level INTEGER,
    collation_name VARCHAR,
    recovery_model VARCHAR,
    is_read_only BOOLEAN,
    is_auto_close_on BOOLEAN,
    is_auto_shrink_on BOOLEAN,
    is_auto_create_stats_on BOOLEAN,
    is_auto_update_stats_on BOOLEAN,
    is_auto_update_stats_async_on BOOLEAN,
    is_read_committed_snapshot_on BOOLEAN,
    snapshot_isolation_state VARCHAR,
    is_parameterization_forced BOOLEAN,
    is_query_store_on BOOLEAN,
    is_encrypted BOOLEAN,
    is_trustworthy_on BOOLEAN,
    is_db_chaining_on BOOLEAN,
    is_broker_enabled BOOLEAN,
    is_cdc_enabled BOOLEAN,
    is_mixed_page_allocation_on BOOLEAN,
    log_reuse_wait_desc VARCHAR,
    page_verify_option VARCHAR,
    target_recovery_time_seconds INTEGER,
    delayed_durability VARCHAR,
    is_accelerated_database_recovery_on BOOLEAN,
    is_memory_optimized_enabled BOOLEAN,
    is_optimized_locking_on BOOLEAN
)";

    // Index definitions
    public const string CreateWaitStatsIndex = @"
CREATE INDEX IF NOT EXISTS idx_wait_stats_time ON wait_stats(server_id, collection_time)";

    public const string CreateQueryStatsIndex = @"
CREATE INDEX IF NOT EXISTS idx_query_stats_time ON query_stats(server_id, collection_time)";

    public const string CreateProcedureStatsIndex = @"
CREATE INDEX IF NOT EXISTS idx_procedure_stats_time ON procedure_stats(server_id, collection_time)";

    public const string CreateQueryStoreIndex = @"
CREATE INDEX IF NOT EXISTS idx_query_store_time ON query_store_stats(server_id, collection_time)";

    public const string CreateQuerySnapshotsIndex = @"
CREATE INDEX IF NOT EXISTS idx_query_snapshots_time ON query_snapshots(server_id, collection_time)";

    public const string CreateCpuIndex = @"
CREATE INDEX IF NOT EXISTS idx_cpu_time ON cpu_utilization_stats(server_id, collection_time)";

    public const string CreateFileIoIndex = @"
CREATE INDEX IF NOT EXISTS idx_file_io_time ON file_io_stats(server_id, collection_time)";

    public const string CreateMemoryIndex = @"
CREATE INDEX IF NOT EXISTS idx_memory_time ON memory_stats(server_id, collection_time)";

    public const string CreateTempdbIndex = @"
CREATE INDEX IF NOT EXISTS idx_tempdb_time ON tempdb_stats(server_id, collection_time)";

    public const string CreatePerfmonIndex = @"
CREATE INDEX IF NOT EXISTS idx_perfmon_time ON perfmon_stats(server_id, collection_time)";

    public const string CreateDeadlocksIndex = @"
CREATE INDEX IF NOT EXISTS idx_deadlocks_time ON deadlocks(server_id, collection_time)";

    public const string CreateCollectionLogIndex = @"
CREATE INDEX IF NOT EXISTS idx_collection_log_time ON collection_log(server_id, collection_time)";

    public const string CreateMemoryGrantStatsIndex = @"
CREATE INDEX IF NOT EXISTS idx_memory_grant_stats_time ON memory_grant_stats(server_id, collection_time)";

    public const string CreateWaitingTasksIndex = @"
CREATE INDEX IF NOT EXISTS idx_waiting_tasks_time ON waiting_tasks(server_id, collection_time)";

    public const string CreateBlockedProcessReportsIndex = @"
CREATE INDEX IF NOT EXISTS idx_blocked_process_reports_time ON blocked_process_reports(server_id, collection_time)";

    public const string CreateMemoryClerksIndex = @"
CREATE INDEX IF NOT EXISTS idx_memory_clerks_time ON memory_clerks(server_id, collection_time)";

    public const string CreateDatabaseScopedConfigTable = @"
CREATE TABLE IF NOT EXISTS database_scoped_config (
    config_id BIGINT PRIMARY KEY,
    capture_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    database_name VARCHAR NOT NULL,
    configuration_name VARCHAR NOT NULL,
    value VARCHAR,
    value_for_secondary VARCHAR
)";

    public const string CreateDatabaseScopedConfigIndex = @"
CREATE INDEX IF NOT EXISTS idx_database_scoped_config_time ON database_scoped_config(server_id, capture_time)";

    public const string CreateTraceFlagsTable = @"
CREATE TABLE IF NOT EXISTS trace_flags (
    config_id BIGINT PRIMARY KEY,
    capture_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    trace_flag INTEGER NOT NULL,
    status BOOLEAN NOT NULL,
    is_global BOOLEAN NOT NULL,
    is_session BOOLEAN NOT NULL
)";

    public const string CreateTraceFlagsIndex = @"
CREATE INDEX IF NOT EXISTS idx_trace_flags_time ON trace_flags(server_id, capture_time)";

    public const string CreateRunningJobsTable = @"
CREATE TABLE IF NOT EXISTS running_jobs (
    collection_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    job_name VARCHAR NOT NULL,
    job_id VARCHAR NOT NULL,
    job_enabled BOOLEAN NOT NULL,
    start_time TIMESTAMP NOT NULL,
    current_duration_seconds BIGINT NOT NULL,
    avg_duration_seconds BIGINT NOT NULL,
    p95_duration_seconds BIGINT NOT NULL,
    successful_run_count BIGINT NOT NULL,
    is_running_long BOOLEAN NOT NULL,
    percent_of_average DECIMAL(10,1)
)";

    public const string CreateRunningJobsIndex = @"
CREATE INDEX IF NOT EXISTS idx_running_jobs_time ON running_jobs(server_id, collection_time)";

    public const string CreateDatabaseSizeStatsTable = @"
CREATE TABLE IF NOT EXISTS database_size_stats (
    collection_id BIGINT PRIMARY KEY,
    collection_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    database_name VARCHAR NOT NULL,
    database_id INTEGER NOT NULL,
    file_id INTEGER NOT NULL,
    file_type_desc VARCHAR NOT NULL,
    file_name VARCHAR NOT NULL,
    physical_name VARCHAR NOT NULL,
    total_size_mb DECIMAL(19,2) NOT NULL,
    used_size_mb DECIMAL(19,2),
    auto_growth_mb DECIMAL(19,2),
    max_size_mb DECIMAL(19,2),
    recovery_model_desc VARCHAR,
    compatibility_level INTEGER,
    state_desc VARCHAR,
    volume_mount_point VARCHAR,
    volume_total_mb DECIMAL(19,2),
    volume_free_mb DECIMAL(19,2),
    is_percent_growth BOOLEAN,
    growth_pct INTEGER,
    vlf_count INTEGER
)";

    public const string CreateDatabaseSizeStatsIndex = @"
CREATE INDEX IF NOT EXISTS idx_database_size_stats_time ON database_size_stats(server_id, collection_time)";

    public const string CreateServerPropertiesTable = @"
CREATE TABLE IF NOT EXISTS server_properties (
    collection_id BIGINT PRIMARY KEY,
    collection_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    edition VARCHAR NOT NULL,
    product_version VARCHAR NOT NULL,
    product_level VARCHAR NOT NULL,
    product_update_level VARCHAR,
    engine_edition INTEGER NOT NULL,
    cpu_count INTEGER NOT NULL,
    hyperthread_ratio INTEGER NOT NULL,
    physical_memory_mb BIGINT NOT NULL,
    socket_count INTEGER,
    cores_per_socket INTEGER,
    is_hadr_enabled BOOLEAN,
    is_clustered BOOLEAN,
    enterprise_features VARCHAR,
    service_objective VARCHAR
)";

    public const string CreateServerPropertiesIndex = @"
CREATE INDEX IF NOT EXISTS idx_server_properties_time ON server_properties(server_id, collection_time)";

    public const string CreateSessionStatsTable = @"
CREATE TABLE IF NOT EXISTS session_stats (
    collection_id BIGINT PRIMARY KEY,
    collection_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    program_name VARCHAR NOT NULL,
    connection_count BIGINT NOT NULL,
    running_count INTEGER NOT NULL,
    sleeping_count INTEGER NOT NULL,
    dormant_count INTEGER NOT NULL,
    total_cpu_time_ms BIGINT,
    total_reads BIGINT,
    total_writes BIGINT,
    total_logical_reads BIGINT
)";

    public const string CreateSessionStatsIndex = @"
CREATE INDEX IF NOT EXISTS idx_session_stats_time ON session_stats(server_id, collection_time)";

    public const string CreateAlertLogTable = @"
CREATE TABLE IF NOT EXISTS config_alert_log (
    alert_time TIMESTAMP NOT NULL,
    server_id INTEGER NOT NULL,
    server_name VARCHAR NOT NULL,
    metric_name VARCHAR NOT NULL,
    current_value DOUBLE NOT NULL,
    threshold_value DOUBLE NOT NULL,
    alert_sent BOOLEAN NOT NULL DEFAULT false,
    notification_type VARCHAR NOT NULL DEFAULT 'tray',
    send_error VARCHAR,
    dismissed BOOLEAN NOT NULL DEFAULT false,
    muted BOOLEAN NOT NULL DEFAULT false,
    detail_text VARCHAR
)";

    public const string CreateMuteRulesTable = @"
CREATE TABLE IF NOT EXISTS config_mute_rules (
    id VARCHAR NOT NULL PRIMARY KEY,
    enabled BOOLEAN NOT NULL DEFAULT true,
    created_at_utc TIMESTAMP NOT NULL,
    expires_at_utc TIMESTAMP,
    reason VARCHAR,
    server_name VARCHAR,
    metric_name VARCHAR,
    database_pattern VARCHAR,
    query_text_pattern VARCHAR,
    wait_type_pattern VARCHAR,
    job_name_pattern VARCHAR
)";

    /// <summary>
    /// Returns all table creation statements in order.
    /// </summary>
    public static IEnumerable<string> GetAllTableStatements()
    {
        yield return CreateServersTable;
        yield return CreateCollectionScheduleTable;
        yield return CreateCollectionLogTable;
        yield return CreateWaitStatsTable;
        yield return CreateQueryStatsTable;
        yield return CreateCpuUtilizationStatsTable;
        yield return CreateFileIoStatsTable;
        yield return CreateMemoryStatsTable;
        yield return CreateMemoryClerksTable;
        yield return CreateDeadlocksTable;
        yield return CreateProcedureStatsTable;
        yield return CreateQueryStoreStatsTable;
        yield return CreateQuerySnapshotsTable;
        yield return CreateTempdbStatsTable;
        yield return CreatePerfmonStatsTable;
        yield return CreateServerConfigTable;
        yield return CreateDatabaseConfigTable;
        yield return CreateMemoryGrantStatsTable;
        yield return CreateWaitingTasksTable;
        yield return CreateBlockedProcessReportsTable;
        yield return CreateDatabaseScopedConfigTable;
        yield return CreateTraceFlagsTable;
        yield return CreateRunningJobsTable;
        yield return CreateDatabaseSizeStatsTable;
        yield return CreateServerPropertiesTable;
        yield return CreateSessionStatsTable;
        yield return CreateAlertLogTable;
        yield return CreateMuteRulesTable;
    }

    /// <summary>
    /// Returns all index creation statements.
    /// </summary>
    public static IEnumerable<string> GetAllIndexStatements()
    {
        yield return CreateWaitStatsIndex;
        yield return CreateQueryStatsIndex;
        yield return CreateProcedureStatsIndex;
        yield return CreateQueryStoreIndex;
        yield return CreateQuerySnapshotsIndex;
        yield return CreateCpuIndex;
        yield return CreateFileIoIndex;
        yield return CreateMemoryIndex;
        yield return CreateTempdbIndex;
        yield return CreatePerfmonIndex;
        yield return CreateDeadlocksIndex;
        yield return CreateCollectionLogIndex;
        yield return CreateMemoryGrantStatsIndex;
        yield return CreateWaitingTasksIndex;
        yield return CreateBlockedProcessReportsIndex;
        yield return CreateMemoryClerksIndex;
        yield return CreateDatabaseScopedConfigIndex;
        yield return CreateTraceFlagsIndex;
        yield return CreateRunningJobsIndex;
        yield return CreateDatabaseSizeStatsIndex;
        yield return CreateServerPropertiesIndex;
        yield return CreateSessionStatsIndex;
    }
}