/* ---------------- ZeroDBA – Accidental DBA Starter Checks (v1-slim, JSON) ----------------
   SQL Server 2017  (primary target: 2017
   Output: a single JSON array (use SSMS "Results to Text" or grid then copy)
-------------------------------------------------------------------------------------------*/

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

/* Results bucket */
DECLARE @Results TABLE (
  status          nvarchar(10),
  title           nvarchar(200),
  whatItChecksFor nvarchar(max),
  whyThisResult   nvarchar(max),
  severityScore   int,
  fixScript       nvarchar(max),
  metadata        nvarchar(max)
);

/* Helpers */
DECLARE @cpu_count int;
SELECT @cpu_count = COUNT(*) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE';
IF @cpu_count IS NULL SET @cpu_count = 1;

/* Build a friendly OS string that works across versions */
DECLARE @osVersion nvarchar(400) =
(
  SELECT TOP (1)
    CASE
      WHEN EXISTS (SELECT 1 FROM sys.all_objects WHERE name = 'dm_os_host_info' AND type = 'V')
      THEN (SELECT TOP (1)
              RTRIM(
                CONCAT(
                  NULLIF(host_platform,''),' ',
                  NULLIF(host_distribution,''),' ',
                  NULLIF(host_release,'')
                )
              )
            FROM sys.dm_os_host_info)
      ELSE (SELECT TOP (1)
              RTRIM(
                CONCAT(
                  'Windows ',
                  CONVERT(nvarchar(20), windows_release),
                  CASE WHEN ISNULL(windows_service_pack_level,'') <> '' THEN CONCAT(' SP:', windows_service_pack_level) ELSE '' END
                )
              )
            FROM sys.dm_os_windows_info)
    END
);

/* ---------------- 1) Platform & Host Identity ---------------- */

INSERT INTO @Results
SELECT
  'PASS',
  'Server & OS identity',
  'Captures server and OS version so you can verify platform currency.',
  'Knowing exact OS build helps decide patching and feature support.',
  1,
  '',
  (
    SELECT
      currentValue = CONCAT(CAST(SERVERPROPERTY('MachineName') AS sysname), ' / ',
                            CAST(SERVERPROPERTY('ServerName')  AS sysname), ' | ',
                            ISNULL(@osVersion,'Unknown')),
      recommended  = 'Ensure OS build is within support window.',
      evidence     = 'SERVERPROPERTY, sys.dm_os_host_info OR sys.dm_os_windows_info',
      notes        = '',
      verifySql    = 'SELECT SERVERPROPERTY(''MachineName'') AS Machine, SERVERPROPERTY(''ServerName'') AS Instance; SELECT * FROM sys.dm_os_host_info;'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  );

INSERT INTO @Results
SELECT
  'INFO',
  'Power plan (manual)',
  'Suggests verifying Windows power plan when xp_cmdshell is disabled.',
  'Power plan impacts CPU behavior; verify it is High performance.',
  1,
  '',
  (
    SELECT
      currentValue = 'xp_cmdshell disabled',
      recommended  = 'Manually verify High performance',
      evidence     = 'sys.configurations (xp_cmdshell)',
      notes        = 'Use an agent helper or check directly on OS',
      verifySql    = 'SELECT name, value_in_use FROM sys.configurations WHERE name = ''xp_cmdshell'';'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  );

/* ---------------- 2) Instance Configuration ---------------- */

INSERT INTO @Results
SELECT
  CASE WHEN c.value_in_use IS NULL OR c.value_in_use >= 2147483647 THEN 'FAIL' ELSE 'PASS' END,
  'Max server memory',
  'Ensures server memory cap is set to prevent OS pressure.',
  'Unlimited memory can starve the OS and cause paging or instability.',
  CASE WHEN c.value_in_use IS NULL OR c.value_in_use >= 2147483647 THEN 5 ELSE 2 END,
  CASE WHEN c.value_in_use IS NULL OR c.value_in_use >= 2147483647
       THEN 'EXEC sys.sp_configure ''max server memory (MB)'', <RecommendedMB>; RECONFIGURE WITH OVERRIDE;'
       ELSE '' END,
  (
    SELECT
      currentValue = c.value_in_use,
      recommended  = 'Set to (Server RAM - 5–10GB) or policy',
      evidence     = 'sys.configurations',
      notes        = '',
      verifySql    = 'SELECT name, value_in_use FROM sys.configurations WHERE name = ''max server memory (MB)'';'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  )
FROM sys.configurations AS c
WHERE c.name = 'max server memory (MB)';

INSERT INTO @Results
SELECT
  'INFO',
  'Min server memory',
  'Optional lower bound to reduce cold-start thrash on steady workloads.',
  'A reasonable MIN can smooth memory behavior; not mandatory.',
  1,
  '',
  (
    SELECT
      currentValue = c.value_in_use,
      recommended  = 'Optional per workload',
      evidence     = 'sys.configurations',
      notes        = '',
      verifySql    = 'SELECT name, value_in_use FROM sys.configurations WHERE name = ''min server memory (MB)'';'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  )
FROM sys.configurations AS c
WHERE c.name = 'min server memory (MB)';

INSERT INTO @Results
SELECT
  CASE WHEN c.value_in_use = 0 THEN 'WARN' ELSE 'PASS' END,
  'MAXDOP (instance)',
  'Caps parallel threads per query.',
  'MAXDOP 0 can increase CXPACKET/CXCONSUMER waits in some workloads.',
  CASE WHEN c.value_in_use = 0 THEN 3 ELSE 1 END,
  CASE WHEN c.value_in_use = 0
       THEN 'EXEC sys.sp_configure ''max degree of parallelism'', 2; RECONFIGURE WITH OVERRIDE; -- adjust 2/4/8 per cores'
       ELSE '' END,
  (
    SELECT
      currentValue = c.value_in_use,
      recommended  = '2/4/8 based on cores & vendor guidance',
      evidence     = 'sys.configurations, sys.dm_os_schedulers',
      notes        = CONCAT('Visible schedulers: ', @cpu_count),
      verifySql    = 'SELECT name, value_in_use FROM sys.configurations WHERE name = ''max degree of parallelism'';'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  )
FROM sys.configurations AS c
WHERE c.name = 'max degree of parallelism';

INSERT INTO @Results
SELECT
  CASE WHEN c.value_in_use < 50 THEN 'WARN' ELSE 'PASS' END,
  'Cost Threshold for Parallelism',
  'Raises the cost at which queries go parallel.',
  'Very low thresholds can cause excessive parallelism & overhead.',
  CASE WHEN c.value_in_use < 50 THEN 3 ELSE 1 END,
  CASE WHEN c.value_in_use < 50
       THEN 'EXEC sys.sp_configure ''cost threshold for parallelism'', 50; RECONFIGURE WITH OVERRIDE;'
       ELSE '' END,
  (
    SELECT
      currentValue = c.value_in_use,
      recommended  = '≥ 50 (tune with evidence)',
      evidence     = 'sys.configurations',
      notes        = '',
      verifySql    = 'SELECT name, value_in_use FROM sys.configurations WHERE name = ''cost threshold for parallelism'';'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  )
FROM sys.configurations AS c
WHERE c.name = 'cost threshold for parallelism';

INSERT INTO @Results
SELECT
  CASE WHEN c.value_in_use = 1 THEN 'PASS' ELSE 'WARN' END,
  'Optimize for Ad Hoc Workloads',
  'Stores a stub on first execution to reduce plan cache bloat.',
  'Helps on ad-hoc heavy workloads; generally safe to enable.',
  CASE WHEN c.value_in_use = 1 THEN 1 ELSE 2 END,
  CASE WHEN c.value_in_use = 1 THEN '' ELSE 'EXEC sys.sp_configure ''optimize for ad hoc workloads'', 1; RECONFIGURE WITH OVERRIDE;' END,
  (
    SELECT
      currentValue = c.value_in_use,
      recommended  = 'ON',
      evidence     = 'sys.configurations',
      notes        = '',
      verifySql    = 'SELECT name, value_in_use FROM sys.configurations WHERE name = ''optimize for ad hoc workloads'';'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  )
FROM sys.configurations AS c
WHERE c.name = 'optimize for ad hoc workloads';

INSERT INTO @Results
SELECT
  CASE WHEN c.value_in_use = 1 THEN 'PASS' ELSE 'WARN' END,
  'Backup compression default',
  'Compress backups by default to save space/time (CPU tradeoff).',
  'Usually reduces backup time and I/O footprint significantly.',
  CASE WHEN c.value_in_use = 1 THEN 1 ELSE 2 END,
  CASE WHEN c.value_in_use = 1 THEN '' ELSE 'EXEC sys.sp_configure ''backup compression default'', 1; RECONFIGURE WITH OVERRIDE;' END,
  (
    SELECT
      currentValue = c.value_in_use,
      recommended  = 'ON (unless CPU bound)',
      evidence     = 'sys.configurations',
      notes        = '',
      verifySql    = 'SELECT name, value_in_use FROM sys.configurations WHERE name = ''backup compression default'';'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  )
FROM sys.configurations AS c
WHERE c.name = 'backup compression default';

/* ---------------- 3) Storage & I/O ---------------- */

INSERT INTO @Results
SELECT
  CASE WHEN SERVERPROPERTY('InstanceDefaultDataPath') IS NOT NULL
        AND SERVERPROPERTY('InstanceDefaultLogPath')  IS NOT NULL THEN 'PASS' ELSE 'WARN' END,
  'Default data/log paths',
  'Ensures default data/log paths are set to valid locations.',
  'Unset or invalid defaults can lead to files on wrong volumes.',
  CASE WHEN SERVERPROPERTY('InstanceDefaultDataPath') IS NOT NULL
        AND SERVERPROPERTY('InstanceDefaultLogPath')  IS NOT NULL THEN 1 ELSE 2 END,
  '',
  (
    SELECT
      currentValue = (
        SELECT
          data = CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS nvarchar(400)),
          log  = CAST(SERVERPROPERTY('InstanceDefaultLogPath')  AS nvarchar(400))
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
      ),
      recommended  = 'Point data/log to intended volumes',
      evidence     = 'SERVERPROPERTY(InstanceDefaultDataPath/LogPath)',
      notes        = '',
      verifySql    = 'SELECT CAST(SERVERPROPERTY(''InstanceDefaultDataPath'') AS nvarchar(400)) AS DataPath, CAST(SERVERPROPERTY(''InstanceDefaultLogPath'') AS nvarchar(400)) AS LogPath;'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  );

;WITH mf AS (
  SELECT db_name(database_id) AS dbname, type_desc, physical_name
  FROM sys.master_files
  WHERE database_id > 4
),
sep AS (
  SELECT dbname,
         MIN(CASE WHEN type_desc = 'ROWS' THEN LEFT(physical_name,2) END) AS data_drive,
         MIN(CASE WHEN type_desc = 'LOG'  THEN LEFT(physical_name,2) END) AS log_drive
  FROM mf
  GROUP BY dbname
),
bad AS (
  SELECT dbname, data_drive, log_drive
  FROM sep
  WHERE data_drive IS NULL OR log_drive IS NULL OR data_drive = log_drive
)
INSERT INTO @Results
SELECT
  CASE WHEN EXISTS(SELECT 1 FROM bad) THEN 'WARN' ELSE 'PASS' END,
  'Data/Log separation',
  'Verifies that data and log files are on distinct volumes (drive-letter heuristic).',
  'Separating log I/O reduces contention and improves reliability.',
  CASE WHEN EXISTS(SELECT 1 FROM bad) THEN 2 ELSE 1 END,
  '',
  (
    SELECT
      currentValue = (SELECT TOP (50) dbname, data_drive, log_drive FROM bad ORDER BY dbname FOR JSON PATH),
      recommended  = 'Place LOG on dedicated volume(s); verify mount points separately.',
      evidence     = 'sys.master_files',
      notes        = CONCAT('Non-separated DBs: ', (SELECT COUNT(*) FROM bad)),
      verifySql    = 'SELECT db_name(database_id) AS dbname, type_desc, physical_name FROM sys.master_files WHERE database_id > 4;'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  );

/* ==== CHECK: Auto-shrink (OFF) ===================================== */
;WITH offenders AS (
  SELECT name
  FROM sys.databases
  WHERE database_id > 4 AND is_auto_shrink_on = 1
),
totals AS (
  SELECT COUNT(*) AS cnt FROM offenders
)
INSERT INTO @Results
SELECT
  CASE WHEN t.cnt > 0 THEN 'WARN' ELSE 'PASS' END,                   -- status
  'Auto-shrink (OFF)',                                              -- title
  'Checks user databases for AUTO_SHRINK.',                         -- whatItChecksFor
  'AUTO_SHRINK causes fragmentation and performance instability.',  -- whyThisResult
  CASE WHEN t.cnt > 0 THEN 2 ELSE 1 END,                            -- severityScore
  '/* Disable auto-shrink per database */
ALTER DATABASE <db> SET AUTO_SHRINK OFF;',                          -- fixScript
  (
    SELECT
      currentValue = t.cnt,                                         -- keep count for summary
      offenders    = (SELECT name FROM offenders FOR JSON PATH),   -- <<< names here
      recommended  = 'AUTO_SHRINK OFF for all user DBs',
      evidence     = 'sys.databases',
      verifySql    = 'SELECT name, is_auto_shrink_on FROM sys.databases WHERE database_id > 4;'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  )
FROM totals AS t;

/* LPIM & IFI info */
/* ---------- Storage & I/O : Lock Pages in Memory (LPIM) ---------- */
INSERT INTO @Results
SELECT
  CASE WHEN (SELECT locked_page_allocations_kb FROM sys.dm_os_process_memory) > 0
       THEN 'PASS' ELSE 'INFO' END,
  'Lock Pages in Memory (LPIM)',
  'Reports whether SQL Server appears to be using locked pages.',
  'LPIM can prevent memory from being paged out on busy systems.',
  1,
  '',
  (
    SELECT 
      currentValue = (SELECT locked_page_allocations_kb FROM sys.dm_os_process_memory),
      recommended  = 'Enable LPIM for Enterprise/steady workloads',
      evidence     = 'sys.dm_os_process_memory.locked_page_allocations_kb',
      notes        = 'Exact LPIM privilege must be checked in OS policy',
      verifySql    = 'SELECT locked_page_allocations_kb FROM sys.dm_os_process_memory;'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  );


INSERT INTO @Results
SELECT
  'INFO',
  'Instant File Initialization (IFI)',
  'Indicates whether IFI is likely enabled (requires Perform Volume Maintenance Tasks).',
  'IFI speeds file growth/creation for data files; verify via OS policy.',
  1,
  '',
  (
    SELECT
      currentValue = 'Unknown from T-SQL',
      recommended  = 'Grant "Perform volume maintenance tasks" to SQL service account',
      evidence     = 'Manual/OS policy',
      notes        = 'Check via secpol.msc or run a small grow test off-hours',
      verifySql    = '/* Verify IFI privilege in OS: Local Security Policy -> User Rights Assignment */'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  );

/* ---------------- 4) TempDB ---------------- */

INSERT INTO @Results
SELECT
  CASE WHEN cnt >= CASE WHEN @cpu_count >= 8 THEN 8 ELSE @cpu_count END THEN 'PASS' ELSE 'WARN' END,
  'TempDB data file count',
  'TempDB data files should scale to cores (up to 8).',
  'Too few files can cause PFS/SGAM contention under concurrency.',
  CASE WHEN cnt >= CASE WHEN @cpu_count >= 8 THEN 8 ELSE @cpu_count END THEN 1 ELSE 3 END,
  '',
  (
    SELECT
      currentValue = cnt,
      recommended  = CONCAT('min(', @cpu_count, ', 8)'),
      evidence     = 'tempdb.sys.database_files, sys.dm_os_schedulers',
      notes        = '',
      verifySql    = 'SELECT COUNT(*) AS tempdb_rows_files FROM tempdb.sys.database_files WHERE type = 0;'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  )
FROM (SELECT COUNT(*) AS cnt FROM tempdb.sys.database_files WHERE type = 0) AS x;

INSERT INTO @Results
SELECT
  CASE WHEN s.size_min = s.size_max AND s.growth_min = s.growth_max AND s.pct_growth_files = 0 THEN 'PASS' ELSE 'WARN' END,
  'TempDB equal size & MB growth',
  'All TempDB data files should have equal size and MB growth.',
  'Uneven growth/size can recreate contention and fragmentation.',
  CASE WHEN s.size_min = s.size_max AND s.growth_min = s.growth_max AND s.pct_growth_files = 0 THEN 1 ELSE 2 END,
  '',
  (
    SELECT
      currentValue = (SELECT s.size_min, s.size_max, s.growth_min, s.growth_max, s.pct_growth_files FOR JSON PATH, WITHOUT_ARRAY_WRAPPER),
      recommended  = 'Equalize sizes; use fixed MB growth (no %)',
      evidence     = 'tempdb.sys.database_files',
      notes        = '',
      verifySql    = 'SELECT file_id, type_desc, size, growth, is_percent_growth FROM tempdb.sys.database_files WHERE type = 0;'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  )
FROM (
  SELECT
    size_min = MIN(size),
    size_max = MAX(size),
    growth_min = MIN(growth),
    growth_max = MAX(growth),
    pct_growth_files = SUM(CASE WHEN is_percent_growth = 1 THEN 1 ELSE 0 END)
  FROM tempdb.sys.database_files
  WHERE type = 0
) AS s;

INSERT INTO @Results
SELECT
  CASE WHEN fg.is_autogrow_all_files = 1 THEN 'PASS' ELSE 'WARN' END,
  'TempDB autogrow all files',
  'Ensures all TempDB data files grow together.',
  'Prevents one file from becoming a hot spot.',
  CASE WHEN fg.is_autogrow_all_files = 1 THEN 1 ELSE 2 END,
  '',
  (
    SELECT
      currentValue = fg.is_autogrow_all_files,
      recommended  = 'Enable autogrow all files',
      evidence     = 'tempdb.sys.filegroups',
      notes        = '',
      verifySql    = 'SELECT is_autogrow_all_files FROM tempdb.sys.filegroups WHERE name = ''PRIMARY'';'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  )
FROM tempdb.sys.filegroups AS fg
WHERE fg.name = 'PRIMARY';

INSERT INTO @Results
SELECT
  CASE WHEN d.is_mixed_page_allocation_on = 0 THEN 'PASS' ELSE 'WARN' END,
  'TempDB mixed-page allocation',
  'Mixed-page allocation should be OFF for modern best practices.',
  'Uniform extents improve allocation behavior under concurrency.',
  CASE WHEN d.is_mixed_page_allocation_on = 0 THEN 1 ELSE 2 END,
  '',
  (
    SELECT
      currentValue = d.is_mixed_page_allocation_on,
      recommended  = 'OFF',
      evidence     = 'sys.databases',
      notes        = '',
      verifySql    = 'SELECT is_mixed_page_allocation_on FROM sys.databases WHERE name = ''tempdb'';'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  )
FROM sys.databases AS d
WHERE d.name = 'tempdb';


 /* ==== CHECK: Page Verify (CHECKSUM) ===================================== */
;WITH d AS (
  SELECT name, page_verify_option_desc
  FROM sys.databases
  WHERE database_id > 4                    -- user DBs
),
offenders AS (
  SELECT name, page_verify_option_desc
  FROM d
  WHERE page_verify_option_desc <> 'CHECKSUM'
),
totals AS (
  SELECT offenders = COUNT(*) FROM offenders
)
INSERT INTO @Results
SELECT
  CASE WHEN t.offenders > 0 THEN 'WARN' ELSE 'PASS' END               AS [status],
  'Page Verify (CHECKSUM)'                                            AS [title],
  'Ensures databases use CHECKSUM page verification to detect corruption early.' AS [whatItChecksFor],
  'Non-CHECKSUM settings reduce corruption detection and increase recovery risk.' AS [whyThisResult],
  3                                                                   AS [severityScore],
  /* Optional default fix script (you also show a generated script in the UI) */
  NULL                                                                AS [fixScript],
  (
    SELECT
      totalOffenders = t.offenders,
      offenders = (
        SELECT TOP (100)
          name, page_verify_option_desc
        FROM offenders
        ORDER BY name
        FOR JSON PATH
      ),
      recommended = 'Set PAGE_VERIFY CHECKSUM for all user databases.',
      evidence    = 'sys.databases.page_verify_option_desc',
      verifySql   = 'SELECT name, page_verify_option_desc FROM sys.databases WHERE database_id > 4;'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ) AS [metadata]
FROM totals AS t;


/* ==== CHECK: xp_cmdshell disabled ======================================= */
INSERT INTO @Results
SELECT
  CASE WHEN c.value_in_use = 1 THEN 'WARN' ELSE 'PASS' END,
  'xp_cmdshell (disabled)',
  'Ensures xp_cmdshell is disabled unless there is a vetted need and controls.',
  'Enabling xp_cmdshell increases attack surface and exfiltration risk.',
  CASE WHEN c.value_in_use = 1 THEN 2 ELSE 1 END,
  CASE WHEN c.value_in_use = 1
       THEN 'EXEC sys.sp_configure ''xp_cmdshell'', 0; RECONFIGURE WITH OVERRIDE;' ELSE '' END,
  (
    SELECT
      currentValue = c.value_in_use,
      recommended  = '0 (disabled)',
      evidence     = 'sys.configurations',
      notes        = '',
      verifySql    = 'SELECT name, value_in_use FROM sys.configurations WHERE name = ''xp_cmdshell'';'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  )
FROM sys.configurations AS c
WHERE c.name = 'xp_cmdshell';

/* ==== CHECK: Backups recency (FULL/DIFF/LOG) =================================== */
DECLARE @full_days int = 7;     -- FULL must be within 7 days
DECLARE @diff_days int = 2;     -- DIFF within 2 days
DECLARE @log_mins  int = 60;    -- LOG within 60 minutes (FULL/BULK_LOGGED)

/* Last backup timestamps per DB */
;WITH last AS (
  SELECT d.name,
         d.recovery_model_desc,
         MAX(CASE WHEN bs.type = 'D' THEN bs.backup_finish_date END) AS last_full,
         MAX(CASE WHEN bs.type = 'I' THEN bs.backup_finish_date END) AS last_diff,
         MAX(CASE WHEN bs.type = 'L' THEN bs.backup_finish_date END) AS last_log
  FROM sys.databases d
  LEFT JOIN msdb.dbo.backupset bs
    ON bs.database_name = d.name
  WHERE d.database_id > 4
  GROUP BY d.name, d.recovery_model_desc
),
age AS (
  SELECT name, recovery_model_desc,
         last_full, DATEDIFF(DAY,    last_full, SYSDATETIME())  AS age_full_days,
         last_diff, DATEDIFF(DAY,    last_diff, SYSDATETIME())  AS age_diff_days,
         last_log,  DATEDIFF(MINUTE, last_log,  SYSDATETIME())  AS age_log_min
  FROM last
),
off_full AS (
  SELECT name, age_full_days
  FROM age
  WHERE last_full IS NULL OR age_full_days > @full_days
),
off_diff AS (
  SELECT name, age_diff_days
  FROM age
  WHERE last_diff IS NULL OR age_diff_days > @diff_days
),
off_log AS (
  SELECT name, age_log_min
  FROM age
  WHERE recovery_model_desc IN ('FULL','BULK_LOGGED')
    AND (last_log IS NULL OR age_log_min > @log_mins)
),
counts AS (
  SELECT
    tf = (SELECT COUNT(*) FROM off_full),
    td = (SELECT COUNT(*) FROM off_diff),
    tl = (SELECT COUNT(*) FROM off_log)
)
/* Insert result row */
INSERT INTO @Results
SELECT
  /* status */
  CASE WHEN (c.tf + c.td + c.tl) > 0 THEN 'FAIL' ELSE 'PASS' END,
  /* title */
  'Backups recency (FULL/DIFF/LOG)',
  /* whatItChecksFor */
  'Checks whether each database has recent FULL, DIFF, and (if FULL/BULK_LOGGED) LOG backups.',
  /* whyThisResult */
  'Stale or missing backups put RPO/RTO at risk and can prevent point-in-time recovery.',
  /* severityScore */
  CASE WHEN (c.tf + c.td + c.tl) > 0 THEN 5 ELSE 1 END,
  /* fixScript (leave empty; FRIENDLY copy lives in UI) */
  '',
  /* metadata JSON */
  (
    SELECT
      offenders_full = (
        SELECT name, age_full_days
        FROM off_full
        ORDER BY name
        FOR JSON PATH
      ),
      offenders_diff = (
        SELECT name, age_diff_days
        FROM off_diff
        ORDER BY name
        FOR JSON PATH
      ),
      offenders_log = (
        SELECT name, age_log_min
        FROM off_log
        ORDER BY name
        FOR JSON PATH
      ),
      recommended = CONCAT('FULL ≤ ', @full_days, 'd; DIFF ≤ ', @diff_days,
                           'd; LOG ≤ ', @log_mins, 'm (FULL/BULK_LOGGED).'),
      evidence     = 'msdb.dbo.backupset, sys.databases',
      notes        = 'Tune thresholds per SLA. LOG applies only to FULL/BULK_LOGGED.',
      verifySql    = '/* See UI verify snippet for full details; summary:
;WITH last AS (
  SELECT d.name, d.recovery_model_desc,
         MAX(CASE WHEN bs.type=''D'' THEN bs.backup_finish_date END) last_full,
         MAX(CASE WHEN bs.type=''I'' THEN bs.backup_finish_date END) last_diff,
         MAX(CASE WHEN bs.type=''L'' THEN bs.backup_finish_date END) last_log
  FROM sys.databases d
  LEFT JOIN msdb.dbo.backupset bs ON bs.database_name = d.name
  WHERE d.database_id > 4
  GROUP BY d.name, d.recovery_model_desc
)
SELECT name, recovery_model_desc, last_full, last_diff, last_log
FROM last ORDER BY name; */'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  )
FROM counts AS c;


/* ==== CHECK: CHECKDB Frequency (Job Cadence) ============================= */
;WITH cand_steps AS (
  SELECT j.job_id, j.name AS job_name, s.step_id, s.command
  FROM msdb.dbo.sysjobs j
  JOIN msdb.dbo.sysjobsteps s ON s.job_id = j.job_id
  WHERE s.command LIKE '%DBCC CHECKDB%' OR s.command LIKE '%DatabaseIntegrityCheck%'
),
sched AS (
  SELECT js.job_id, sc.freq_type, sc.freq_interval, sc.freq_subday_type,
         sc.freq_subday_interval, sc.freq_recurrence_factor
  FROM msdb.dbo.sysjobschedules js
  JOIN msdb.dbo.sysschedules sc ON sc.schedule_id = js.schedule_id
),
agg AS (
  SELECT c.job_id,
         MIN(c.job_name) AS job_name,
         MIN(COALESCE(s.freq_type,0)) AS freq_type,
         MIN(COALESCE(s.freq_recurrence_factor,0)) AS freq_recurrence_factor
  FROM cand_steps c
  LEFT JOIN sched s ON s.job_id = c.job_id
  GROUP BY c.job_id
),
rating AS (
  SELECT
    total_jobs      = COUNT(*),
    weekly_or_daily = COALESCE(SUM(CASE WHEN freq_type IN (4,8) THEN 1 ELSE 0 END), 0)
  FROM agg
)
INSERT INTO @Results
SELECT
  CASE
    WHEN r.total_jobs = 0 THEN 'WARN'
    WHEN r.weekly_or_daily >= 1 THEN 'PASS'
    ELSE 'INFO'
  END,
  'CHECKDB frequency (scheduled)',
  'Detects presence and cadence of a scheduled DBCC CHECKDB (or Ola IntegrityCheck) job.',
  'Without a recurring CHECKDB, corruption can go undetected; cadence should be at least weekly.',
  CASE
    WHEN r.total_jobs = 0 THEN 3
    WHEN r.weekly_or_daily >= 1 THEN 1
    ELSE 1
  END,
  CASE
    WHEN r.total_jobs = 0
      THEN '/* Create a weekly CHECKDB job (or Ola Hallengren DatabaseIntegrityCheck) targeting all user DBs. */'
      ELSE '' END,
  (
    SELECT
      jobs_found   = r.total_jobs,
      good_cadence = r.weekly_or_daily,
      recommended  = 'Schedule CHECKDB weekly or better (stagger large DBs).',
      evidence     = 'msdb.dbo.sysjobs, msdb.dbo.sysjobsteps, msdb.dbo.sysjobschedules, msdb.dbo.sysschedules',
      notes        = 'Frequency derived from Agent schedule metadata (freq_type 4=daily, 8=weekly).',
      verifySql    = 'SELECT j.name, s.step_id, s.command
                      FROM msdb.dbo.sysjobs j
                      JOIN msdb.dbo.sysjobsteps s ON s.job_id=j.job_id
                      WHERE s.command LIKE ''%DBCC CHECKDB%'' OR s.command LIKE ''%DatabaseIntegrityCheck%'';'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  )
FROM rating AS r;

/* ==== CHECK: Database compatibility level ========================= */
DECLARE @recommended int =
  CASE CONVERT(int, SERVERPROPERTY('ProductMajorVersion'))
       WHEN 13 THEN 130  -- SQL Server 2016
       WHEN 14 THEN 140  -- SQL Server 2017
       WHEN 15 THEN 150  -- SQL Server 2019
       WHEN 16 THEN 160  -- SQL Server 2022
       ELSE 160          -- default/future-proof
  END;

;WITH dbs AS (
  SELECT name, compatibility_level
  FROM sys.databases
  WHERE database_id > 4
),
offenders AS (
  SELECT name, compatibility_level
  FROM dbs
  WHERE compatibility_level < @recommended
),
totals AS (
  SELECT COUNT(*) AS offenders_count FROM offenders
)
INSERT INTO @Results
SELECT
  CASE WHEN t.offenders_count > 0 THEN 'WARN' ELSE 'PASS' END,              -- status
  'Database compatibility level',                                           -- title
  'Flags databases running below the instance''s recommended compatibility level.', -- whatItChecksFor
  'Lower compat levels can limit optimizer features; vendors sometimes require older levels—review per DB.',      -- whyThisResult
  CASE WHEN t.offenders_count > 0 THEN 2 ELSE 1 END,                         -- severityScore
  '/* To raise compat level after vendor testing */' + CHAR(10) +
  'ALTER DATABASE <db> SET COMPATIBILITY_LEVEL = ' + CAST(@recommended AS nvarchar(10)) + ';',                    -- fixScript (template)
  (
    SELECT
      serverDefault = @recommended,
      offenders     = (
        SELECT name, compatibility_level
        FROM offenders
        ORDER BY name
        FOR JSON PATH
      ),
      recommended   = 'Set to ' + CAST(@recommended AS nvarchar(10)) + ' unless application requires older level.',
      evidence      = 'sys.databases, SERVERPROPERTY(ProductMajorVersion)',
      notes         = 'Change only after testing; some apps rely on legacy CE/optimizer behavior.',
      verifySql     = 'SELECT name, compatibility_level FROM sys.databases WHERE database_id>4;'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  )
FROM totals t;


/* ==== CHECK: Availability Groups Health ================================= */
DECLARE @hadr_enabled int = TRY_CAST(SERVERPROPERTY('IsHadrEnabled') AS int);

;WITH have_ag AS (
  SELECT COUNT(*) AS ags FROM sys.availability_groups
),
db_state AS (
  SELECT
    drs.group_id,
    drs.database_id,
    drs.synchronization_state,      -- 0=NotSync,1=Partially,2=Synchronizing,3=Synchronized (note: on 2016 values 0..3; 2/3 are OK)
    drs.synchronization_state_desc,
    drs.is_suspended
  FROM sys.dm_hadr_database_replica_states AS drs
),
bad AS (
  SELECT * FROM db_state
  WHERE (synchronization_state NOT IN (2,3) OR is_suspended = 1)
),
summary AS (
  SELECT
    hadr_enabled = @hadr_enabled,
    ag_count     = (SELECT ags FROM have_ag),
    bad_rows     = (SELECT COUNT(*) FROM bad)
)
INSERT INTO @Results
SELECT
  CASE
    WHEN @hadr_enabled = 1 AND (SELECT ags FROM have_ag) > 0
         AND (SELECT COUNT(*) FROM bad) = 0 THEN 'PASS'
    WHEN @hadr_enabled = 1 AND (SELECT ags FROM have_ag) > 0
         AND (SELECT COUNT(*) FROM bad) > 0 THEN 'WARN'
    ELSE 'INFO'
  END,
  'Availability Groups health',
  'Checks AG presence and flags DB replicas not synchronized/synchronizing or suspended.',
  'Unhealthy AG replicas risk data loss and failover issues; suspended DBs are not protected.',
  CASE
    WHEN @hadr_enabled = 1 AND (SELECT ags FROM have_ag) > 0
         AND (SELECT COUNT(*) FROM bad) > 0 THEN 4
    WHEN @hadr_enabled = 1 AND (SELECT ags FROM have_ag) > 0 THEN 1
    ELSE 1
  END,
  CASE
    WHEN @hadr_enabled = 1 AND (SELECT ags FROM have_ag) > 0
         AND (SELECT COUNT(*) FROM bad) > 0 THEN
      '/* Investigate replicas with NOT SYNCHRONIZED or SUSPENDED; resolve network, log send, or storage issues. */'
    ELSE '' END,
  (
    SELECT
      hadrEnabled  = @hadr_enabled,
      ags          = (SELECT COUNT(*) FROM sys.availability_groups),
      offenders    = (SELECT TOP (50) database_id, synchronization_state_desc, is_suspended FROM bad FOR JSON PATH),
      recommended  = 'All replicas Synchronized/Synchronizing; none suspended.',
      evidence     = 'sys.availability_groups, sys.dm_hadr_database_replica_states',
      notes        = 'Also monitor queues: log_send_queue_size, redo_queue_size (additional perf check).',
      verifySql    = 'SELECT db_name(drs.database_id) AS db, drs.synchronization_state_desc, drs.is_suspended
                      FROM sys.dm_hadr_database_replica_states drs;'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  );

/* ==== CHECK: File growth settings (MB vs %) ================================ */
;WITH files AS (
  SELECT
    db_name      = DB_NAME(mf.database_id),
    logical_name = mf.name,
    file_type    = mf.type_desc,                      -- ROWS | LOG
    file_path    = mf.physical_name,
    size_mb      = (mf.size * 8.0) / 1024.0,
    is_pct       = mf.is_percent_growth,
    growth_raw   = mf.growth,
    growth_desc  = CASE
                     WHEN mf.is_percent_growth = 1
                       THEN CAST(mf.growth AS varchar(10)) + '%'
                     ELSE CAST((mf.growth * 8) / 1024 AS varchar(10)) + ' MB'
                   END
  FROM sys.master_files AS mf
  WHERE mf.database_id > 4                                -- user DBs only
),
offenders AS (
  SELECT *
  FROM files
  WHERE is_pct = 1
),
totals AS (
  SELECT offenders = COUNT(*) FROM offenders
)
INSERT INTO @Results
SELECT
  /* status */       CASE WHEN t.offenders > 0 THEN 'WARN' ELSE 'PASS' END,
  /* title */        'File growth settings (MB vs %)',
  /* whatItChecksFor*/'Flags percent-based growth which can cause over-large file expansions.',
  /* whyThisResult */ 'Use fixed MB growth to keep expansions predictable.',
  /* severityScore */ CASE WHEN t.offenders > 0 THEN 2 ELSE 1 END,
  /* fixScript */    '',   -- UI already supplies a template from the friendly dictionary
  /* metadata (JSON) */
  (
    SELECT
      currentValue = t.offenders,   -- keep the count (drives the small pill if we ever show it)
      offenders = JSON_QUERY((
        SELECT
          db           = o.db_name,
          logical_name = o.logical_name,
          [type]       = o.file_type,
          size_mb      = o.size_mb,
          growth       = o.growth_desc,
          [path]       = o.file_path
        FROM offenders AS o
        ORDER BY o.db_name, o.file_type, o.logical_name
        FOR JSON PATH
      )),
      recommended = 'Use MB growth (e.g., data 512–4096MB; log 256–1024MB)',
      evidence    = 'sys.master_files',
      notes       = '',
      verifySql   = 'SELECT DB_NAME(mf.database_id) AS db, mf.name AS logical_name, mf.type_desc, ' +
                    '(mf.size*8.0)/1024.0 AS size_mb, ' +
                    'CASE WHEN mf.is_percent_growth=1 THEN CONCAT(mf.growth, ''%'') ' +
                    'ELSE CONCAT((mf.growth*8)/1024, '' MB'') END AS growth, ' +
                    'mf.physical_name FROM sys.master_files mf ' +
                    'WHERE mf.database_id > 4 AND mf.is_percent_growth = 1 ' +
                    'ORDER BY db, type_desc, logical_name;'
    FROM totals AS t
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  )
FROM totals AS t;


/* ==== CHECK: SQL Server Agent status ===================================== */
/* PRE: expects @Results TABLE (status,title,whatItChecksFor,whyThisResult,severityScore,fixScript,metadata) */

BEGIN TRY
  -- DMV presence check (very old builds/limited SKUs)
  IF OBJECT_ID(N'sys.dm_server_services') IS NULL
  BEGIN
    INSERT INTO @Results
    SELECT
      'INFO',
      'SQL Server Agent status',
      'Checks whether the SQL Server Agent service is running.',
      'This instance does not expose sys.dm_server_services on this version/edition.',
      1,
      '',
      (
        SELECT
          reason       = 'DMV not available',
          recommended  = 'Verify status in Services.msc / SQL Server Configuration Manager; note Express has no SQL Server Agent.',
          evidence     = 'sys.dm_server_services (not found)',
          verifySql    = 'SELECT * FROM sys.all_objects WHERE name = ''dm_server_services'';'
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
      );
    RETURN;
  END;

  ;WITH svc AS (
     SELECT
       servicename,
       status_desc,            -- Running | Stopped | Paused
       startup_type_desc,      -- Automatic | Manual | Disabled
       last_startup_time,
       service_account,
       filename
     FROM sys.dm_server_services
     WHERE servicename LIKE 'SQL Server Agent%'
  ),
  installed AS (SELECT COUNT(*) AS cnt FROM svc)
  INSERT INTO @Results
  SELECT
    CASE
      WHEN i.cnt = 0 THEN 'INFO'
      WHEN s.status_desc = 'Running' THEN 'PASS'
      ELSE 'WARN'
    END                                       AS status,
    'SQL Server Agent status'                 AS title,
    'Checks whether the SQL Server Agent service is installed and running.' AS whatItChecksFor,
    'If the Agent is stopped, scheduled jobs (backups, CHECKDB, maintenance) won’t run.' AS whyThisResult,
    CASE
      WHEN i.cnt = 0 THEN 1
      WHEN s.status_desc = 'Running' THEN 1
      ELSE 2
    END                                       AS severityScore,
    CASE
      WHEN i.cnt = 0 THEN ''
      ELSE
        '/* Start SQL Server Agent (review/change-control):' + CHAR(10) +
        '1) Windows: Services.msc or SQL Server Configuration Manager' + CHAR(10) +
        '2) PowerShell (admin):  Start-Service -Name ''SQLServerAgent''' + CHAR(10) +
        '3) T-SQL (sysadmin; may be blocked):  EXEC xp_servicecontrol ''Start'', ''SQLServerAgent'';' + CHAR(10) +
        'Also set startup type to Automatic and investigate the stop reason. */'
    END                                       AS fixScript,
    (
      SELECT
        installed   = i.cnt,
        currentState = (
           SELECT TOP (1)
                  s.servicename, s.status_desc, s.startup_type_desc,
                  s.last_startup_time, s.service_account, s.filename
           FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        ),
        offenders   = (
           SELECT s2.servicename, s2.status_desc, s2.startup_type_desc,
                  s2.last_startup_time, s2.service_account
           FROM svc AS s2
           WHERE s2.status_desc <> 'Running'
           FOR JSON PATH
        ),
        recommended = CASE
                        WHEN i.cnt = 0
                          THEN 'This server has no SQL Server Agent (e.g., Express). Use an alternative scheduler or upgrade.'
                        WHEN EXISTS (SELECT 1 FROM svc WHERE status_desc <> 'Running')
                          THEN 'Agent should be RUNNING on production instances. Start it, set startup type to Automatic, and review Windows/Application/SQL logs for the stop reason.'
                        ELSE 'Agent is running. Keep startup type Automatic and monitor job failures.'
                      END,
        evidence    = 'sys.dm_server_services',
        verifySql   = 'SELECT servicename, status_desc, startup_type_desc, last_startup_time, service_account ' +
                      'FROM sys.dm_server_services WHERE servicename LIKE ''SQL Server Agent%'';'
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    )                                         AS metadata
  FROM installed AS i
  OUTER APPLY (SELECT TOP (1) * FROM svc) s;
END TRY
BEGIN CATCH
  INSERT INTO @Results
  SELECT
    'INFO',
    'SQL Server Agent status',
    'Checks whether the SQL Server Agent service is installed and running.',
    'Could not read service metadata.',
    1,
    '',
    (
      SELECT
        error_number   = ERROR_NUMBER(),
        error_severity = ERROR_SEVERITY(),
        error_state    = ERROR_STATE(),
        error_message  = ERROR_MESSAGE(),
        recommended    = 'Run as sysadmin; if Express edition, Agent is not available; otherwise verify via Services.msc/Configuration Manager.',
        verifySql      = 'SELECT servicename, status_desc FROM sys.dm_server_services WHERE servicename LIKE ''SQL Server Agent%'';'
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    );
END CATCH;

/* ==== CHECK: SQL Server memory dumps ===================================== */
;WITH d AS (
  SELECT
      [filename],
      creation_time,
      size_in_bytes
  FROM sys.dm_server_memory_dumps
),
totals AS (
  SELECT COUNT(*) AS dumpCount FROM d
)
INSERT INTO @Results
SELECT
  /* status */
  CASE WHEN t.dumpCount > 0 THEN 'WARN' ELSE 'PASS' END,
  /* title */
  'SQL Server memory dumps',
  /* whatItChecksFor */
  'Detects whether SQL Server has produced any memory dumps (crash dumps).',
  /* whyThisResult */
  'Memory dumps usually mean the SQL Server process hit a fatal exception or bug; these need investigation and patching.',
  /* severityScore */
  CASE WHEN t.dumpCount > 0 THEN 2 ELSE 1 END,
  /* fixScript (informational) */
  '',
  /* metadata (JSON) */
  (
    SELECT
      dumpCount    = t.dumpCount,
      offenders    = (
        SELECT TOP (100)
          [filename],
          creation_time,
          SizeMB = CONVERT(decimal(18,1), d.size_in_bytes/1048576.0)
        FROM d
        ORDER BY creation_time DESC
        FOR JSON PATH
      ),
      recommended  = 'No memory dumps should normally be present. Review ERRORLOG; apply latest CUs/drivers; open a Microsoft case if needed.',
      evidence     = 'sys.dm_server_memory_dumps',
      notes        = 'DMV persists until service restart; correlating timestamps with ERRORLOG helps. Older dumps may predate current patch level.',
      verifySql    = 'SELECT [filename], creation_time, size_in_bytes/1048576.0 AS [SizeMB] FROM sys.dm_server_memory_dumps ORDER BY creation_time DESC;'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  )
FROM totals AS t;


 /* ==== CHECK: Suspect Pages ================================================ */
;WITH sp AS (
  SELECT
      sp.database_id,
      DB_NAME(sp.database_id)       AS [db_name],
      sp.file_id,
      sp.page_id,
      sp.event_type,
      sp.error_count,
      sp.last_update_date,
      mf.name                       AS logical_name,
      mf.physical_name
  FROM msdb.dbo.suspect_pages AS sp
  JOIN sys.master_files        AS mf
    ON mf.database_id = sp.database_id
   AND mf.file_id     = sp.file_id
),
totals AS (
  SELECT COUNT(*) AS totalSuspect
  FROM sp
)
INSERT INTO @Results
SELECT
  /* status */
  CASE WHEN t.totalSuspect > 0 THEN 'WARN' ELSE 'PASS' END,
  /* title */
  'Suspect Pages',
  /* whatItChecksFor */
  'Checks the msdb.suspect_pages table for corruption signals (bad checksum, torn page, 823/824/825).',
  /* whyThisResult */
  'Suspect pages usually indicate I/O subsystem issues or corruption; even repaired/restored pages should be reviewed.',
  /* severityScore */
  CASE WHEN t.totalSuspect > 0 THEN 4 ELSE 1 END,
  /* fixScript (info only) */
  '',
  /* metadata (JSON) */
  (
    SELECT
      totalSuspect  = t.totalSuspect,
      offenders     = (
        SELECT TOP (100)
          s.db_name,
          s.file_id,
          s.page_id,
          s.event_type,
          s.error_count,
          s.last_update_date,
          s.logical_name,
          s.physical_name
        FROM sp AS s
        ORDER BY s.last_update_date DESC
        FOR JSON PATH
      ),
      recommended   = 'Suspect Pages table should be empty. Investigate immediately (DBCC CHECKDB, storage, restores).',
      evidence      = 'msdb.dbo.suspect_pages, sys.master_files',
      notes         = 'Table is capped at 1000 rows; older events may be truncated. Correlate with ERRORLOG.',
      verifySql     = 'SELECT DB_NAME(sp.database_id) AS [Database], sp.file_id, sp.page_id, sp.event_type, sp.error_count, sp.last_update_date, mf.name AS logical_name, mf.physical_name FROM msdb.dbo.suspect_pages sp JOIN sys.master_files mf ON mf.database_id = sp.database_id AND mf.file_id = sp.file_id;'
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  )
FROM totals AS t;


/* ==== CHECK: Volume free space on DB file LUNs ========================== */
;WITH vols AS (
  SELECT
    vs.volume_mount_point     AS mount_point,
    vs.file_system_type,
    vs.logical_volume_name,
    total_gb = CONVERT(decimal(18,2), vs.total_bytes/1073741824.0),
    free_gb  = CONVERT(decimal(18,2), vs.available_bytes/1073741824.0),
    free_pct = CONVERT(decimal(18,2), vs.available_bytes * 1.0 / NULLIF(vs.total_bytes,0) * 100.0),
    vs.supports_compression,
    vs.is_compressed,
    vs.supports_sparse_files,
    vs.supports_alternate_streams
  FROM sys.master_files AS f WITH (NOLOCK)
  CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs
  WHERE f.database_id > 4  -- user DBs only; include system DBs by removing this
),
agg AS (  -- collapse duplicate rows per volume
  SELECT
    mount_point,
    file_system_type         = MIN(file_system_type),
    logical_volume_name      = MIN(logical_volume_name),
    total_gb                 = MAX(total_gb),  -- total is constant per volume
    free_gb                  = MAX(free_gb),   -- same per volume
    free_pct                 = MAX(free_pct),  -- same per volume
    supports_compression     = MAX(CASE WHEN supports_compression=1 THEN 1 ELSE 0 END),
    is_compressed            = MAX(CASE WHEN is_compressed=1 THEN 1 ELSE 0 END),
    supports_sparse_files    = MAX(CASE WHEN supports_sparse_files=1 THEN 1 ELSE 0 END),
    supports_alternate_streams=MAX(CASE WHEN supports_alternate_streams=1 THEN 1 ELSE 0 END)
  FROM vols
  GROUP BY mount_point
),
offenders AS (
  SELECT *,
         fail_flag = CASE WHEN (free_pct < 10 OR free_gb < 5)  THEN 1 ELSE 0 END,
         warn_flag = CASE WHEN (free_pct < 20 OR free_gb < 20) THEN 1 ELSE 0 END
  FROM agg
),
totals AS (
  SELECT
    fail_cnt = SUM(CASE WHEN fail_flag = 1 THEN 1 ELSE 0 END),
    warn_cnt = SUM(CASE WHEN warn_flag = 1 AND fail_flag = 0 THEN 1 ELSE 0 END),
    vol_cnt  = COUNT(*)
  FROM offenders
)
INSERT INTO @Results
SELECT
  CASE
    WHEN t.fail_cnt > 0 THEN 'FAIL'
    WHEN t.warn_cnt > 0 THEN 'WARN'
    ELSE 'PASS'
  END,
  'Volume free space (DB LUNs)',
  'Checks free space on volumes hosting database files and flags low headroom.',
  'Low free space risks failed growths, long autogrowth stalls, excessive VLFs on logs, and outages.',
  CASE
    WHEN t.fail_cnt > 0 THEN 4
    WHEN t.warn_cnt > 0 THEN 2
    ELSE 1
  END,
  CASE
    WHEN t.fail_cnt > 0 OR t.warn_cnt > 0 THEN
      '/* Remediation options:' + CHAR(10) +
      '   1) Extend the LUN or add capacity.' + CHAR(10) +
      '   2) Pre-size DATA/LOG files to expected 3–6 months usage.' + CHAR(10) +
      '   3) Switch to fixed MB autogrowth; review backup/cleanup of old files.' + CHAR(10) +
      '   4) Consider moving tempdb/user files to less utilized volumes. */'
    ELSE '' END,
  (
    SELECT
      totalVolumes   = t.vol_cnt,
      failVolumes    = t.fail_cnt,
      warnVolumes    = t.warn_cnt,
      offenders      = (
        SELECT TOP (100)
          mount_point, logical_volume_name, file_system_type,
          total_gb, free_gb, free_pct,
          is_compressed, supports_compression, supports_sparse_files, supports_alternate_streams
        FROM offenders
        WHERE fail_flag = 1 OR (warn_flag = 1 AND fail_flag = 0)
        ORDER BY free_pct ASC, free_gb ASC
        FOR JSON PATH
      ),
      recommended    = 'Keep ≥20% and ≥20 GB free (minimum), with contingency for bursts; pre-size files to reduce growth events.',
      evidence       = 'sys.master_files, sys.dm_os_volume_stats',
      notes          = 'Thresholds are policy-tunable. Logs need extra headroom during index rebuilds/long transactions.',
      verifySql      = 'SELECT DISTINCT vs.volume_mount_point, vs.file_system_type, vs.logical_volume_name,' + CHAR(10) +
                       '  CONVERT(decimal(18,2), vs.total_bytes/1073741824.0) AS total_gb,' + CHAR(10) +
                       '  CONVERT(decimal(18,2), vs.available_bytes/1073741824.0) AS free_gb,' + CHAR(10) +
                       '  CONVERT(decimal(18,2), vs.available_bytes*1.0/NULLIF(vs.total_bytes,0)*100.0) AS free_pct' + CHAR(10) +
                       'FROM sys.master_files f CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) vs' + CHAR(10) +
                       'WHERE f.database_id > 4 ORDER BY vs.volume_mount_point;'
    FROM totals AS t
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  )
FROM totals AS t;

/* ---------------- Return JSON ---------------- */
------------------------------------------------------------
-- Final JSON output (single NVARCHAR(MAX) cell)  — ARRAY
------------------------------------------------------------
DECLARE @json NVARCHAR(MAX);

SELECT @json =
(
  SELECT
      status,
      title,
      whatItChecksFor,
      whyThisResult,
      severityScore,
      fixScript,
      -- metadata is already JSON text in your rows → wrap safely
      metadata = JSON_QUERY(
        CASE WHEN ISJSON(metadata)=1 THEN metadata ELSE NULL END
      )
  FROM @Results   -- or #Results if you used a temp table
  FOR JSON PATH, INCLUDE_NULL_VALUES
);

SELECT @json AS [JSON];

