/* ============================================================================
   ZeroDBA Analyzer – 50 Checks (Deduped + Friendly)
   Version: 2025-11-09 (consolidated categories + polished text)
   Notes:
     • Category normalization per user:
         - Platform Support + Power Plan  → Hardware & OS / Instance & OS
         - Data/Log separation + IFI      → Instance Configuration / Storage & I/O
         - TempDB category remains; Networking & Protocols remains
     • Output: single row with [JSON] = array of all checks.
     * Tested in SQL 2017/2019/2022
   ============================================================================ */

SET NOCOUNT ON;

/* 0) Temp table */
IF OBJECT_ID('tempdb..#checks') IS NOT NULL DROP TABLE #checks;
CREATE TABLE #checks (
  title         nvarchar(200),
  category      nvarchar(120),
  section       nvarchar(120),
  status        varchar(10),     -- PASS/WARN/FAIL/INFO
  severityScore int,
  metadata      nvarchar(max)    -- JSON object (WITHOUT_ARRAY_WRAPPER)
);

/* ============================================================================
   ORIGINAL CHECKS (UNTOUCHED where not duplicated)
   (If an original check title duplicates a standardized one below, the original
    is omitted to avoid collisions.)
   ============================================================================ */

-------------------------------------------------------------------------------
-- 1) Auto-shrink (OFF)
;WITH AutoShrink AS (
  SELECT d.name, d.is_auto_shrink_on
  FROM sys.databases AS d
  WHERE d.state = 0
),
AutoShrink_Offenders AS (
  SELECT name, is_auto_shrink_on
  FROM AutoShrink
  WHERE is_auto_shrink_on = 1
)
INSERT INTO #checks (title, category, section, status, severityScore, metadata)
SELECT
  N'Auto-Shrink Setting',
  N'Storage & I/O',                 -- keep original category
  N'Database Settings',
  CASE WHEN EXISTS (SELECT 1 FROM AutoShrink_Offenders) THEN 'FAIL' ELSE 'PASS' END,
  4,
  JSON_QUERY((
    SELECT
      (SELECT COUNT(1) FROM AutoShrink_Offenders) AS currentValue,
      (SELECT name FROM AutoShrink_Offenders FOR JSON PATH) AS offenders,
      (SELECT name, is_auto_shrink_on FROM AutoShrink_Offenders FOR JSON PATH) AS offenders_full
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ));

-------------------------------------------------------------------------------
-- 2) File growth settings (MB vs %)
;WITH FilesRaw AS (
  SELECT
    DB_NAME(mf.database_id) AS db,
    mf.name                 AS logical_name,
    mf.is_percent_growth,
    mf.growth               AS growth_raw
  FROM sys.master_files AS mf
  WHERE DB_NAME(mf.database_id) IS NOT NULL
),
PercentOffenders AS (
  SELECT db, logical_name, is_percent_growth, growth_raw AS growth_percent
  FROM FilesRaw
  WHERE is_percent_growth = 1
),
ItemsPerDb AS (
  SELECT
    db,
    (SELECT logical_name,
            1 AS is_percent_growth,
            growth_percent AS [growth]
     FROM PercentOffenders f2
     WHERE f2.db = f.db
     FOR JSON PATH) AS files_json
  FROM (SELECT DISTINCT db FROM PercentOffenders) AS f
)
INSERT INTO #checks (title, category, section, status, severityScore, metadata)
SELECT
  N'File Growth Settings Check',
  N'Storage & I/O',
  N'Database Files',
  CASE WHEN EXISTS (SELECT 1 FROM PercentOffenders) THEN 'FAIL' ELSE 'PASS' END,
  3,
  JSON_QUERY((
    SELECT
      (SELECT COUNT(DISTINCT db) FROM PercentOffenders) AS currentValue,
      (SELECT db, logical_name, CAST(1 AS bit) AS is_percent_growth, growth_percent AS growth
       FROM PercentOffenders FOR JSON PATH) AS offenders,
      (SELECT i.db AS [database], JSON_QUERY(i.files_json) AS files
       FROM ItemsPerDb i FOR JSON PATH) AS items
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ));

-------------------------------------------------------------------------------
-- 3) Cost Threshold for Parallelism
;WITH CTP AS (
  SELECT CAST(value_in_use AS int) AS val
  FROM sys.configurations
  WHERE name = 'cost threshold for parallelism'
)
INSERT INTO #checks (title, category, section, status, severityScore, metadata)
SELECT
  N'Cost Threshold for Parallelism Setting',
  N'Performance',
  N'Instance Configuration',
  CASE WHEN (SELECT val FROM CTP) < 50 THEN 'FAIL' ELSE 'PASS' END,
  3,
  JSON_QUERY((
    SELECT (SELECT val FROM CTP) AS currentValue, 50 AS desiredValue
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ));

-------------------------------------------------------------------------------
-- 4) Backup compression default
;WITH BCD AS (
  SELECT CAST(value_in_use AS int) AS val
  FROM sys.configurations
  WHERE name = 'backup compression default'
)
INSERT INTO #checks (title, category, section, status, severityScore, metadata)
SELECT
  N'Backup Compression Default Setting',
  N'Reliability',
  N'Backups',
  CASE WHEN (SELECT val FROM BCD) = 1 THEN 'PASS' ELSE 'WARN' END,
  2,
  JSON_QUERY((
    SELECT (SELECT val FROM BCD) AS currentValue
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ));

-------------------------------------------------------------------------------
-- 5) Sysadmin role members (bloat)
;WITH SA AS (
  SELECT spm.name
  FROM sys.server_role_members rm
  JOIN sys.server_principals r   ON r.principal_id   = rm.role_principal_id AND r.name = 'sysadmin'
  JOIN sys.server_principals spm ON spm.principal_id = rm.member_principal_id
  WHERE spm.type IN ('S','U','G')
)
INSERT INTO #checks (title, category, section, status, severityScore, metadata)
SELECT
  N'Sysadmin Role Members Check',
  N'Security',
  N'Server Principals',
  CASE WHEN (SELECT COUNT(1) FROM SA) > 2 THEN 'WARN' ELSE 'PASS' END,
  3,
  JSON_QUERY((
    SELECT
      (SELECT COUNT(1) FROM SA) AS currentValue,
      (SELECT name FROM SA FOR JSON PATH) AS members
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ));

-------------------------------------------------------------------------------
-- 6) MaxDOP (instance)
;WITH MD AS (
  SELECT CAST(value_in_use AS int) AS val
  FROM sys.configurations WHERE name = 'max degree of parallelism'
), CPU AS (
  SELECT cpu_count FROM sys.dm_os_sys_info
)
INSERT INTO #checks (title,category,section,status,severityScore,metadata)
SELECT
  N'Max Degree of Parallelism (MaxDOP) Setting',
  N'Performance',
  N'Instance Configuration',
  CASE WHEN (SELECT val FROM MD) = 0 THEN 'FAIL' ELSE 'WARN' END,
  3,
  JSON_QUERY((
    SELECT (SELECT val FROM MD) AS currentValue,
           (SELECT cpu_count FROM CPU) AS cpuCount
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ));

-------------------------------------------------------------------------------
-- 7) Optimize for Ad Hoc Workloads (should be ON)
;WITH OA AS (
  SELECT CAST(value_in_use AS int) AS val
  FROM sys.configurations WHERE name = 'optimize for ad hoc workloads'
)
INSERT INTO #checks (title,category,section,status,severityScore,metadata)
SELECT
  N'Optimize for Ad Hoc Workloads Setting',
  N'Performance',
  N'Instance Configuration',
  CASE WHEN (SELECT val FROM OA) = 1 THEN 'PASS' ELSE 'WARN' END,
  2,
  JSON_QUERY((
    SELECT (SELECT val FROM OA) AS currentValue, 1 AS desiredValue
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ));

-------------------------------------------------------------------------------
-- 8) Page Verify (CHECKSUM)
;WITH PV AS (
  SELECT name, page_verify_option_desc
  FROM sys.databases
  WHERE state = 0
),
OffendersPV AS (
  SELECT name, page_verify_option_desc
  FROM PV WHERE page_verify_option_desc <> 'CHECKSUM'
)
INSERT INTO #checks (title,category,section,status,severityScore,metadata)
SELECT
  N'Page Verify Setting (Checksum)',
  N'Reliability',
  N'Database Settings',
  CASE WHEN EXISTS (SELECT 1 FROM OffendersPV) THEN 'FAIL' ELSE 'PASS' END,
  3,
  JSON_QUERY((
    SELECT
      (SELECT COUNT(1) FROM OffendersPV) AS currentValue,
      (SELECT name, page_verify_option_desc FROM OffendersPV FOR JSON PATH) AS offenders
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ));

-------------------------------------------------------------------------------
-- 9) Auto-Close (OFF)
;WITH AC AS (
  SELECT name, is_auto_close_on FROM sys.databases WHERE state = 0
),
OffendersAC AS (
  SELECT name FROM AC WHERE is_auto_close_on = 1
)
INSERT INTO #checks (title,category,section,status,severityScore,metadata)
SELECT
  N'Auto-Close Setting',
  N'Reliability',
  N'Database Settings',
  CASE WHEN EXISTS (SELECT 1 FROM OffendersAC) THEN 'FAIL' ELSE 'PASS' END,
  3,
  JSON_QUERY((
    SELECT (SELECT COUNT(1) FROM OffendersAC) AS currentValue,
           (SELECT name FROM OffendersAC FOR JSON PATH) AS offenders
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ));

-------------------------------------------------------------------------------
-- 10) TempDB file count
;WITH CPU AS (SELECT cpu_count FROM sys.dm_os_sys_info),
T AS (SELECT COUNT(*) AS tempdb_files FROM sys.master_files WHERE database_id = 2 AND type = 0)
INSERT INTO #checks (title,category,section,status,severityScore,metadata)
SELECT
  N'TempDB File Count Check',
  N'Performance',
  N'TempDB',
  CASE
    WHEN (SELECT tempdb_files FROM T) < CASE WHEN (SELECT cpu_count FROM CPU) <= 8 THEN (SELECT cpu_count FROM CPU) ELSE 8 END
      THEN 'WARN' ELSE 'PASS' END,
  2,
  JSON_QUERY((
    SELECT (SELECT tempdb_files FROM T) AS currentValue,
           (SELECT cpu_count FROM CPU) AS cpuCount,
           (SELECT CASE WHEN cpu_count <= 8 THEN cpu_count ELSE 8 END FROM CPU) AS recommendedMin
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ));

-------------------------------------------------------------------------------
-- 11) TempDB file growth (MB vs %)
;WITH TFiles AS (
  SELECT name AS logical_name, is_percent_growth, growth
  FROM tempdb.sys.database_files
  WHERE type IN (0,1)
),
Pct AS (SELECT logical_name, growth FROM TFiles WHERE is_percent_growth = 1)
INSERT INTO #checks (title,category,section,status,severityScore,metadata)
SELECT
  N'TempDB file growth (MB vs %)',
  N'Performance',
  N'TempDB',
  CASE WHEN EXISTS (SELECT 1 FROM Pct) THEN 'FAIL' ELSE 'PASS' END,
  3,
  JSON_QUERY((
    SELECT
      (SELECT COUNT(1) FROM Pct) AS currentValue,
      (SELECT logical_name, CAST(1 AS bit) AS is_percent_growth, growth
       FROM Pct FOR JSON PATH) AS offenders
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ));

-------------------------------------------------------------------------------
-- 12) Remote admin connections (DAC)
;WITH DAC AS (
  SELECT CAST(value_in_use AS int) AS val
  FROM sys.configurations WHERE name = 'remote admin connections'
)
INSERT INTO #checks (title,category,section,status,severityScore,metadata)
SELECT
  N'Remote admin connections (DAC)',
  N'Reliability',
  N'Instance Configuration',
  CASE WHEN (SELECT val FROM DAC) = 1 THEN 'PASS' ELSE 'WARN' END,
  2,
  JSON_QUERY((
    SELECT (SELECT val FROM DAC) AS currentValue, 1 AS desiredValue
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ));

-------------------------------------------------------------------------------
-- 13) xp_cmdshell disabled
;WITH XPC AS (
  SELECT CAST(value_in_use AS int) AS val
  FROM sys.configurations WHERE name = 'xp_cmdshell'
)
INSERT INTO #checks (title,category,section,status,severityScore,metadata)
SELECT
  N'xp_cmdshell disabled',
  N'Security',
  N'Instance Configuration',
  CASE WHEN (SELECT val FROM XPC) = 1 THEN 'FAIL' ELSE 'PASS' END,
  4,
  JSON_QUERY((
    SELECT (SELECT val FROM XPC) AS currentValue, 0 AS desiredValue
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ));

-------------------------------------------------------------------------------
-- 14) Max Server Memory (MB)
;WITH MSM AS (
  SELECT CAST(value_in_use AS int) AS val
  FROM sys.configurations WHERE name = 'max server memory (MB)'
),
MEM AS (
  SELECT CAST(total_physical_memory_kb/1024.0 AS int) AS physMB
  FROM sys.dm_os_sys_memory
),
REC AS (
  SELECT CAST(physMB*0.8 AS int) AS recMB FROM MEM
)
INSERT INTO #checks (title,category,section,status,severityScore,metadata)
SELECT
  N'Max Server Memory (MB)',
  N'Performance',
  N'Instance Configuration',
  CASE WHEN (SELECT val FROM MSM) >= (SELECT physMB*0.9 FROM MEM) OR (SELECT val FROM MSM) = 2147483647
       THEN 'WARN' ELSE 'PASS' END,
  3,
  JSON_QUERY((
    SELECT (SELECT val FROM MSM) AS currentValue,
           (SELECT physMB FROM MEM) AS physicalMemoryMB,
           (SELECT recMB FROM REC) AS recommendedMaxMB
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ));

-------------------------------------------------------------------------------
-- 15) Min Server Memory sanity
;WITH MINM AS (
  SELECT CAST(value_in_use AS int) AS val
  FROM sys.configurations WHERE name = 'min server memory (MB)'
),
MAXM AS (
  SELECT CAST(value_in_use AS int) AS val
  FROM sys.configurations WHERE name = 'max server memory (MB)'
),
MEM2 AS (
  SELECT CAST(total_physical_memory_kb/1024.0 AS int) AS physMB
  FROM sys.dm_os_sys_memory
)
INSERT INTO #checks (title,category,section,status,severityScore,metadata)
SELECT
  N'Min Server Memory (MB)',
  N'Performance',
  N'Instance Configuration',
  CASE WHEN (SELECT val FROM MINM) >= (SELECT val FROM MAXM)
            OR (SELECT val FROM MINM) > (SELECT physMB*0.5 FROM MEM2)
       THEN 'WARN' ELSE 'PASS' END,
  2,
  JSON_QUERY((
    SELECT (SELECT val FROM MINM) AS currentValue,
           (SELECT val FROM MAXM) AS maxValue
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ));

-------------------------------------------------------------------------------
-- 16) CLR enabled
;WITH CLR AS (
  SELECT CAST(value_in_use AS int) AS val
  FROM sys.configurations WHERE name = 'clr enabled'
)
INSERT INTO #checks (title,category,section,status,severityScore,metadata)
SELECT
  N'CLR enabled',
  N'Security',
  N'Instance Configuration',
  CASE WHEN (SELECT val FROM CLR) = 1 THEN 'WARN' ELSE 'PASS' END,
  3,
  JSON_QUERY((
    SELECT (SELECT val FROM CLR) AS currentValue, 0 AS desiredValue
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ));

-------------------------------------------------------------------------------
-- 17) Cross-DB ownership chaining (server+db)
;WITH Srv AS (
  SELECT CAST(value_in_use AS int) AS val
  FROM sys.configurations WHERE name = 'cross db ownership chaining'
),
Dbc AS (
  SELECT name FROM sys.databases WHERE is_db_chaining_on = 1 AND database_id > 4
)
INSERT INTO #checks (title,category,section,status,severityScore,metadata)
SELECT
  N'Cross-DB ownership chaining',
  N'Security',
  N'Instance & Database',
  CASE WHEN (SELECT val FROM Srv) = 1 OR EXISTS (SELECT 1 FROM Dbc) THEN 'FAIL' ELSE 'PASS' END,
  4,
  JSON_QUERY((
    SELECT (SELECT val FROM Srv) AS serverLevel,
           (SELECT name FROM Dbc FOR JSON PATH) AS offenders
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ));

-------------------------------------------------------------------------------
-- 18) TRUSTWORTHY (OFF)
;WITH TW AS (SELECT name FROM sys.databases WHERE is_trustworthy_on = 1 AND database_id > 4)
INSERT INTO #checks (title,category,section,status,severityScore,metadata)
SELECT
  N'TRUSTWORTHY (OFF)',
  N'Security',
  N'Database Settings',
  CASE WHEN EXISTS (SELECT 1 FROM TW) THEN 'FAIL' ELSE 'PASS' END,
  4,
  JSON_QUERY((
    SELECT (SELECT name FROM TW FOR JSON PATH) AS offenders
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ));

-------------------------------------------------------------------------------
-- 19) Ad Hoc Distributed Queries
;WITH ADH AS (
  SELECT CAST(value_in_use AS int) AS val
  FROM sys.configurations WHERE name = 'Ad Hoc Distributed Queries'
)
INSERT INTO #checks (title,category,section,status,severityScore,metadata)
SELECT
  N'Ad Hoc Distributed Queries',
  N'Security',
  N'Instance Configuration',
  CASE WHEN (SELECT val FROM ADH) = 1 THEN 'WARN' ELSE 'PASS' END,
  3,
  JSON_QUERY((
    SELECT (SELECT val FROM ADH) AS currentValue, 0 AS desiredValue
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ));

-------------------------------------------------------------------------------
-- 20) SQL Agent failed jobs (24h)
IF DB_ID('msdb') IS NOT NULL
BEGIN
  ;WITH J AS (
    SELECT j.job_id, j.name,
           msdb.dbo.agent_datetime(h.run_date, h.run_time) AS run_dt,
           h.run_status
    FROM msdb.dbo.sysjobs AS j
    JOIN msdb.dbo.sysjobhistory AS h
      ON h.job_id = j.job_id AND h.step_id = 0
    WHERE msdb.dbo.agent_datetime(h.run_date, h.run_time) >= DATEADD(HOUR, -24, SYSUTCDATETIME())
  ),
  F AS (SELECT DISTINCT name FROM J WHERE run_status = 0)
  INSERT INTO #checks (title,category,section,status,severityScore,metadata)
  SELECT
    N'SQL Agent failed jobs (24h)',
    N'Reliability',
    N'Agent Jobs',
    CASE WHEN EXISTS (SELECT 1 FROM F) THEN 'WARN' ELSE 'PASS' END,
    2,
    JSON_QUERY((
      SELECT (SELECT name FROM F FOR JSON PATH) AS offenders
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ));
END;

-------------------------------------------------------------------------------
-- 21) Backup recency (FULL/DIFF/LOG)
IF DB_ID('msdb') IS NOT NULL
BEGIN
  ;WITH last_bk AS (
    SELECT
      bs.database_name,
      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 msdb.dbo.backupset AS bs
    GROUP BY bs.database_name
  ),
  dbs AS (
    SELECT d.name,
           d.recovery_model_desc,
           lb.last_full, lb.last_diff, lb.last_log,
           DATEDIFF(DAY, lb.last_full,  GETDATE()) AS full_days,
           DATEDIFF(DAY, lb.last_diff,  GETDATE()) AS diff_days,
           DATEDIFF(HOUR,lb.last_log,   GETDATE()) AS log_hours
    FROM sys.databases d
    LEFT JOIN last_bk lb ON lb.database_name = d.name
    WHERE d.database_id > 4
  ),
  offenders AS (
    SELECT name, recovery_model_desc,
           full_days, diff_days, log_hours,
           last_full, last_diff, last_log
    FROM dbs
    WHERE (last_full IS NULL OR full_days > 7)
       OR (recovery_model_desc = 'FULL' AND (last_log IS NULL OR log_hours > 4))
  )
  INSERT INTO #checks (title,category,section,status,severityScore,metadata)
  SELECT
    N'Backup recency (FULL/DIFF/LOG)',
    N'Reliability',
    N'Backups',
    CASE WHEN EXISTS (SELECT 1 FROM offenders WHERE last_full IS NULL OR full_days > 14 OR (recovery_model_desc='FULL' AND (last_log IS NULL OR log_hours > 12)))
      THEN 'FAIL'
      WHEN EXISTS (SELECT 1 FROM offenders)
      THEN 'WARN'
      ELSE 'PASS' END,
    4,
    JSON_QUERY((
      SELECT
        (SELECT COUNT(1) FROM offenders) AS currentValue,
        (SELECT name,
                recovery_model_desc AS recovery_model,
                full_days, diff_days, log_hours,
                last_full, last_diff, last_log
         FROM offenders FOR JSON PATH) AS offenders
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ));
END;

-------------------------------------------------------------------------------
-- 22) CHECKDB recency (best-effort)
IF DB_ID('msdb') IS NOT NULL
BEGIN
  ;WITH suspect AS (SELECT COUNT(*) AS cnt FROM msdb.dbo.suspect_pages)
  INSERT INTO #checks (title,category,section,status,severityScore,metadata)
  SELECT
    N'CHECKDB recency',
    N'Reliability',
    N'Integrity',
    CASE WHEN (SELECT cnt FROM suspect) > 0 THEN 'WARN' ELSE 'INFO' END,
    2,
    JSON_QUERY((
      SELECT
        (SELECT cnt FROM suspect) AS suspectPageCount,
        'No standard CHECKDB history table detected; integrate maintenance history for precise recency.' AS note
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ));
END;

-------------------------------------------------------------------------------
-- 23) Backup CHECKSUM usage
IF DB_ID('msdb') IS NOT NULL
BEGIN
  ;WITH last_full AS (
    SELECT bs.database_name, MAX(bs.backup_set_id) AS last_full_id
    FROM msdb.dbo.backupset bs
    WHERE bs.type='D'
    GROUP BY bs.database_name
  ),
  usage AS (
    SELECT lf.database_name, bs.has_backup_checksums
    FROM last_full lf
    JOIN msdb.dbo.backupset bs ON bs.backup_set_id = lf.last_full_id
  ),
  summary AS (
    SELECT
      SUM(CASE WHEN has_backup_checksums = 1 THEN 1 ELSE 0 END) AS checksummed,
      COUNT(*) AS total
    FROM usage
  )
  INSERT INTO #checks (title,category,section,status,severityScore,metadata)
  SELECT
    N'Backup CHECKSUM usage',
    N'Reliability',
    N'Backups',
    CASE WHEN (SELECT checksummed*1.0/NULLIF(total,0) FROM summary) < 0.8 THEN 'WARN' ELSE 'PASS' END,
    2,
    JSON_QUERY((
      SELECT
        (SELECT checksummed FROM summary) AS checksummedFullBackups,
        (SELECT total FROM summary)       AS totalFullBackups,
        (SELECT database_name, has_backup_checksums FROM usage FOR JSON PATH) AS items
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ));
END;

-------------------------------------------------------------------------------
-- 24) Auto Update Stats (ASYNC ON)
;WITH A AS (
  SELECT name, is_auto_update_stats_async_on
  FROM sys.databases
  WHERE database_id > 4 AND state = 0
),
offendersA AS (SELECT name FROM A WHERE is_auto_update_stats_async_on = 0)
INSERT INTO #checks (title,category,section,status,severityScore,metadata)
SELECT
  N'Auto Update Stats (ASYNC ON)',
  N'Performance',
  N'Database Settings',
  CASE WHEN EXISTS (SELECT 1 FROM offendersA) THEN 'WARN' ELSE 'PASS' END,
  2,
  JSON_QUERY((
    SELECT
      (SELECT COUNT(1) FROM offendersA) AS currentValue,
      (SELECT name FROM offendersA FOR JSON PATH) AS offenders
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ));

-------------------------------------------------------------------------------
-- 25) Compatibility level (target)
;WITH svr AS (SELECT CAST(SERVERPROPERTY('ProductMajorVersion') AS int) AS major),
target AS (
  SELECT CASE
           WHEN (SELECT major FROM svr) >= 16 THEN 160
           WHEN (SELECT major FROM svr) = 15 THEN 150
           WHEN (SELECT major FROM svr) = 14 THEN 140
           WHEN (SELECT major FROM svr) = 13 THEN 130
           WHEN (SELECT major FROM svr) = 12 THEN 120
           ELSE 110
         END AS compat_target
),
lag AS (
  SELECT d.name, d.compatibility_level, t.compat_target
  FROM sys.databases d
  CROSS JOIN target t
  WHERE d.database_id > 4 AND d.state = 0 AND d.compatibility_level < t.compat_target
)
INSERT INTO #checks (title,category,section,status,severityScore,metadata)
SELECT
  N'Compatibility level (target)',
  N'Performance',
  N'Database Settings',
  CASE WHEN EXISTS (SELECT 1 FROM lag) THEN 'WARN' ELSE 'PASS' END,
  2,
  JSON_QUERY((
    SELECT
      (SELECT COUNT(1) FROM lag) AS currentValue,
      (SELECT name, compatibility_level, compat_target FROM lag FOR JSON PATH) AS offenders
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ));

-------------------------------------------------------------------------------
-- 26) TempDB equal size & growth (MB)
;WITH f AS (
  SELECT name, type, size_mb = CAST(size/128.0 AS decimal(18,1)),
         is_percent_growth, growth_mb = CASE WHEN is_percent_growth=1 THEN NULL ELSE growth*8/1024 END
  FROM tempdb.sys.database_files
),
dataf AS (SELECT * FROM f WHERE type = 0),
agg AS (
  SELECT
    COUNT(*) AS files,
    MIN(size_mb) AS min_sz, MAX(size_mb) AS max_sz,
    SUM(CASE WHEN is_percent_growth=1 THEN 1 ELSE 0 END) AS pct_grow,
    MIN(growth_mb) AS min_g, MAX(growth_mb) AS max_g
  FROM dataf
),
size_mismatch AS (
  SELECT name, size_mb FROM dataf
  WHERE (SELECT min_sz FROM agg) <> (SELECT max_sz FROM agg)
),
growth_mismatch AS (
  SELECT name, is_percent_growth, growth_mb FROM dataf
  WHERE (SELECT pct_grow FROM agg) > 0 OR (SELECT min_g FROM agg) <> (SELECT max_g FROM agg)
)
INSERT INTO #checks (title,category,section,status,severityScore,metadata)
SELECT
  N'TempDB equal size & growth (MB)',
  N'Performance',
  N'TempDB',
  CASE WHEN (SELECT pct_grow FROM agg) > 0 OR EXISTS (SELECT 1 FROM size_mismatch) OR EXISTS (SELECT 1 FROM growth_mismatch)
       THEN 'WARN' ELSE 'PASS' END,
  3,
  JSON_QUERY((
    SELECT
      (SELECT files FROM agg) AS fileCount,
      (SELECT min_sz FROM agg) AS minSizeMB,
      (SELECT max_sz FROM agg) AS maxSizeMB,
      (SELECT pct_grow FROM agg) AS percentGrowthFiles,
      (SELECT name, size_mb FROM size_mismatch FOR JSON PATH) AS sizeOffenders,
      (SELECT name, is_percent_growth, growth_mb FROM growth_mismatch FOR JSON PATH) AS growthOffenders
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ));

-------------------------------------------------------------------------------
-- 27) Log VLF count (threshold)
IF EXISTS (SELECT 1 FROM sys.all_objects WHERE name = 'dm_db_log_info')
BEGIN
  IF OBJECT_ID('tempdb..#vlf') IS NOT NULL DROP TABLE #vlf;
  CREATE TABLE #vlf (db sysname, vlf_count int);

  DECLARE @sql_vlf nvarchar(max) = N'';
  SELECT @sql_vlf = STRING_AGG(
    N'INSERT INTO #vlf(db, vlf_count)
      SELECT N'''+REPLACE(name,'''','''''')+''',
             COUNT(*) FROM sys.dm_db_log_info(DB_ID(N'''+REPLACE(name,'''','''''')+'''));',
    CHAR(10)
  )
  FROM sys.databases WHERE database_id > 4 AND state = 0;
  IF (LEN(@sql_vlf) > 0) EXEC sp_executesql @sql_vlf;

  INSERT INTO #checks (title,category,section,status,severityScore,metadata)
  SELECT
    N'Log VLF count (threshold)',
    N'Performance',
    N'Logs',
    CASE WHEN EXISTS (SELECT 1 FROM #vlf WHERE vlf_count > 1000) THEN 'WARN' ELSE 'PASS' END,
    2,
    JSON_QUERY((
      SELECT
        (SELECT COUNT(1) FROM #vlf WHERE vlf_count > 1000) AS currentValue,
        (SELECT db AS name, vlf_count FROM #vlf WHERE vlf_count > 1000 FOR JSON PATH) AS offenders
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ));
END;

-------------------------------------------------------------------------------
-- 28) Database owner (sa)
;WITH owners AS (
  SELECT name, SUSER_SNAME(owner_sid) AS owner_name, owner_sid
  FROM sys.databases
  WHERE database_id > 4
),
offendersDO AS (SELECT name, owner_name FROM owners WHERE owner_sid <> SUSER_SID('sa'))
INSERT INTO #checks (title,category,section,status,severityScore,metadata)
SELECT
  N'Database owner (sa)',
  N'Security',
  N'Database Settings',
  CASE WHEN EXISTS (SELECT 1 FROM offendersDO) THEN 'WARN' ELSE 'PASS' END,
  2,
  JSON_QUERY((
    SELECT
      (SELECT COUNT(1) FROM offendersDO) AS currentValue,
      (SELECT name, owner_name FROM offendersDO FOR JSON PATH) AS offenders
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
  ));

/* ============================================================================
   NEW 20 CHECKS (FRIENDLY + DEDUPED)
   (These are the versions we KEEP for any titles that had duplicates.)
   Category adjustments per your instruction are baked in.
   ============================================================================ */

/* 29) Power Plan (High Performance) — unified category */
IF NOT EXISTS (SELECT 1 FROM #checks WHERE title = N'Power Plan (High Performance)')
BEGIN
  DECLARE @hostPlat nvarchar(128) = CAST(SERVERPROPERTY('HostPlatform') AS nvarchar(128));
  INSERT INTO #checks(title,category,section,status,severityScore,metadata)
  SELECT
    N'Power Plan (High Performance)',
    N'Hardware & OS',         -- unified
    N'Instance & OS',         -- unified
    N'INFO', 3,
    JSON_QUERY((
      SELECT
        CONCAT(
          'Host platform: ', ISNULL(@hostPlat,''), '. Confirm Windows power plan is set to High Performance.'
        ) AS currentValue,
        'Balanced/Power Saver can down-clock CPUs and cause latency spikes, especially for OLTP workloads.' AS whyThisMatters,
        'Active Windows power plan on the SQL host (should be High Performance).'                            AS whatItChecksFor,
        'Set High Performance in Windows and disable CPU power-saving in BIOS/firmware.'                    AS whatToDo,
        'powercfg /getactivescheme  -- confirm
powercfg /list            -- list
powercfg /setactive SCHEME_MIN  -- set High Performance'                                                     AS fixScript,
        'powercfg /getactivescheme'                                                                          AS validationQuery
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ));
END

/* 30) Platform support: Windows & SQL version/edition — unified category */
IF NOT EXISTS (SELECT 1 FROM #checks WHERE title = N'Platform support: Windows & SQL version/edition')
BEGIN
  DECLARE 
    @mach     sysname       = CONVERT(sysname, SERVERPROPERTY('MachineName')),
    @inst     sysname       = CONVERT(sysname, SERVERPROPERTY('InstanceName')),
    @srv      sysname       = CONVERT(sysname, SERVERPROPERTY('ServerName')),
    @ed       nvarchar(256) = CONVERT(nvarchar(256), SERVERPROPERTY('Edition')),
    @pv       nvarchar(256) = CONVERT(nvarchar(256), SERVERPROPERTY('ProductVersion')),
    @pl       nvarchar(64)  = CONVERT(nvarchar(64),  SERVERPROPERTY('ProductLevel')),
    @maj      int           = TRY_CONVERT(int, PARSENAME(CONVERT(varchar(50), SERVERPROPERTY('ProductVersion')),4)),
    @winRel   nvarchar(256) = NULL,
    @winVer   nvarchar(256) = NULL,
    @cpuCount int           = NULL,
    @memGb    decimal(10,1) = NULL,
    @dbCount  int           = NULL,
    @tempdbFiles int        = NULL;

  /* Windows host info (2016+), if available */
  IF OBJECT_ID('sys.dm_os_host_info') IS NOT NULL
    SELECT 
      @winRel = host_distribution, 
      @winVer = host_release + N' (' + host_distribution + N')'
    FROM sys.dm_os_host_info;

  /* CPU count, memory, database count, TempDB file count – all optional but helpful */
  IF OBJECT_ID('sys.dm_os_schedulers') IS NOT NULL
    SELECT @cpuCount = COUNT(*) 
    FROM sys.dm_os_schedulers 
    WHERE status = 'VISIBLE ONLINE' AND scheduler_id < 1048576;

  IF OBJECT_ID('sys.dm_os_sys_info') IS NOT NULL
    SELECT @memGb = CAST(physical_memory_kb / 1024.0 / 1024.0 AS decimal(10,1))
    FROM sys.dm_os_sys_info;

  SELECT @dbCount = COUNT(*) 
  FROM sys.databases 
  WHERE database_id > 4 AND state = 0;

  SELECT @tempdbFiles = COUNT(*) 
  FROM tempdb.sys.database_files 
  WHERE type_desc = 'ROWS';

  DECLARE @reasons TABLE(reason nvarchar(400) NOT NULL);
  DECLARE @ok bit = 1;

  IF @maj IS NULL OR @maj < 14 
  BEGIN 
    SET @ok = 0; 
    INSERT @reasons VALUES(N'SQL Server major < 2017'); 
  END;

  IF @ed LIKE N'%Express%' 
  BEGIN 
    SET @ok = 0; 
    INSERT @reasons VALUES(N'Edition is Express'); 
  END;

  IF @winRel IS NULL 
     OR (@winRel NOT LIKE '%2016%' AND @winRel NOT LIKE '%2019%' AND @winRel NOT LIKE '%2022%')
  BEGIN 
    SET @ok = 0; 
    INSERT @reasons VALUES(N'Windows release appears out of policy'); 
  END;

  INSERT INTO #checks (title, category, section, status, severityScore, metadata)
  SELECT
    N'Platform support: Windows & SQL version/edition',
    N'Hardware & OS',        -- unified
    N'Instance & OS',        -- unified
    CASE WHEN @ok = 1 THEN N'PASS' ELSE N'FAIL' END,
    CASE WHEN @ok = 1 THEN 1 ELSE 5 END,
    JSON_QUERY((
      SELECT
        /* Friendly text fields (unchanged) */
        CONCAT(
          N'Machine ', @mach, 
          N'; SQL ', @pv, N' ', @pl, N' (', @ed, N'). Windows: ', 
          ISNULL(@winVer, N'(unknown)')
        ) AS currentValue,
        N'Unsupported OS/engine/edition increases risk and blocks security fixes and features.' AS whyThisMatters,
        N'SQL Server major version, edition (no Express), and supported Windows release.'     AS whatItChecksFor,
        N'Plan upgrades to supported versions/editions aligned with your patch/maintenance policy.' AS whatToDo,
        N'Follow your upgrade runbook (side-by-side or in-place) with rollback and testing.'  AS fixScript,
        N'SELECT SERVERPROPERTY(''ProductVersion''), SERVERPROPERTY(''Edition''), SERVERPROPERTY(''ProductLevel'');' AS validationQuery,

        /* Structured environment / server object for the report UI */
        N'PROD' AS environment,  -- change to TEST/DEV/etc. if you want to tag it differently

        JSON_QUERY((
          SELECT
            ISNULL(@srv, @mach)                                               AS serverName,
            ISNULL(@inst, N'MSSQLSERVER')                                     AS instanceName,
            CONCAT(@pv, N' ', @pl)                                            AS engineVersion,
            @ed                                                               AS edition,
            @winVer                                                           AS osVersion,
            @cpuCount                                                         AS cpuCount,
            @memGb                                                            AS memoryGb,
            @dbCount                                                          AS databaseCount,
            @tempdbFiles                                                      AS tempdbFiles
          FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        )) AS server,

        (SELECT reason FROM @reasons FOR JSON PATH) AS reasons
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ));
END;


/* 31) Data/Log separation (distinct volumes) — moved to Instance Configuration */
IF NOT EXISTS (SELECT 1 FROM #checks WHERE title = N'Data/Log separation (distinct volumes)')
BEGIN
  ;WITH mf AS (
    SELECT DB_NAME(mf.database_id) AS db, mf.type_desc,
           CASE WHEN mf.physical_name LIKE '[A-Z]:\%' THEN UPPER(LEFT(mf.physical_name,2)) END AS drive
    FROM sys.master_files mf
    JOIN sys.databases d ON d.database_id = mf.database_id
    WHERE d.database_id > 4 AND d.state = 0
  ),
  ddrv AS (SELECT db, drive FROM mf WHERE type_desc='ROWS' AND drive IS NOT NULL GROUP BY db, drive),
  ldrv AS (SELECT db, drive FROM mf WHERE type_desc='LOG'  AND drive IS NOT NULL GROUP BY db, drive),
  overlap AS (SELECT d.db, d.drive AS shared_drive FROM ddrv d JOIN ldrv l ON l.db=d.db AND l.drive=d.drive)
  INSERT INTO #checks(title,category,section,status,severityScore,metadata)
  SELECT
    N'Data/Log separation (distinct volumes)',
    N'Instance Configuration',     -- moved
    N'Storage & I/O',              -- section
    CASE WHEN EXISTS(SELECT 1 FROM overlap) THEN 'WARN' ELSE 'PASS' END, 3,
    JSON_QUERY((
      SELECT
        CASE WHEN EXISTS(SELECT 1 FROM overlap) THEN
          CONCAT('Databases sharing a drive for data and log: ', (SELECT COUNT(*) FROM overlap),
                 '. (Separate volumes reduce IO contention and speed up recovery.)')
        ELSE
          'Data and log are on different volumes for all user databases.'
        END AS currentValue,
        'Data and log on the same volume compete for IO and can extend crash recovery time.'                 AS whyThisMatters,
        'Whether user DB data (.mdf/.ndf) and log (.ldf) files share a drive letter.'                        AS whatItChecksFor,
        'Place logs on their own volume where possible (separate from data and TempDB).'                     AS whatToDo,
        'ALTER DATABASE ... MODIFY FILE (NAME=..., FILENAME=...); plan outage/backup-restore if needed.'     AS fixScript,
        'SELECT DB_NAME(database_id) db, type_desc, physical_name FROM sys.master_files WHERE database_id>4;' AS validationQuery,
        (SELECT db AS database_name, shared_drive FROM overlap FOR JSON PATH) AS offenders
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ));
END

/* 31-A) SQL volume free space (data/log/backups) */
--IF OBJECT_ID('sys.dm_os_volume_stats') IS NOT NULL
--BEGIN
--  ;WITH vols AS (
--    SELECT DISTINCT
--      vs.volume_mount_point,
--      vs.logical_volume_name,
--      vs.total_bytes,
--      vs.available_bytes
--    FROM sys.master_files AS mf
--    CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
--    WHERE DB_NAME(mf.database_id) IS NOT NULL
--  ),
--  calc AS (
--    SELECT
--      volume_mount_point,
--      logical_volume_name,
--      totalGB = CAST(v.total_bytes    / 1024.0 / 1024.0 / 1024.0 AS decimal(18,1)),
--      freeGB  = CAST(v.available_bytes / 1024.0 / 1024.0 / 1024.0 AS decimal(18,1)),
--      freePct = CAST(v.available_bytes * 100.0 / NULLIF(v.total_bytes,0) AS decimal(5,1))
--    FROM vols AS v
--  ),
--  warn AS (
--    SELECT * FROM calc WHERE freePct < 20.0
--  ),
--  fail AS (
--    SELECT * FROM calc WHERE freePct < 10.0
--  )
--  INSERT INTO #checks (title, category, section, status, severityScore, metadata)
--  SELECT
--    N'SQL volume free space (data/log/backups)',
--    N'Storage & I/O',
--    N'Volumes',
--    CASE 
--      WHEN EXISTS (SELECT 1 FROM fail) THEN N'FAIL'
--      WHEN EXISTS (SELECT 1 FROM warn) THEN N'WARN'
--      ELSE N'PASS'
--    END,
--    4,
--    JSON_QUERY((
--      SELECT
--        (SELECT COUNT(*) FROM fail) AS criticalVolumeCount,
--        (SELECT COUNT(*) FROM warn) AS lowVolumeCount,
--        10.0 AS failThresholdPercent,
--        20.0 AS warnThresholdPercent,
--        (SELECT
--           volume_mount_point,
--           logical_volume_name,
--           totalGB,
--           freeGB,
--           freePct
--         FROM calc
--         FOR JSON PATH) AS volumes,
--        (SELECT
--           volume_mount_point,
--           logical_volume_name,
--           totalGB,
--           freeGB,
--           freePct
--         FROM fail
--         FOR JSON PATH) AS criticalVolumes
--      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
--    ));
--END
--ELSE
--BEGIN
--  INSERT INTO #checks (title, category, section, status, severityScore, metadata)
--  SELECT
--    N'SQL volume free space (data/log/backups)',
--    N'Storage & I/O',
--    N'Volumes',
--    N'INFO',
--    1,
--    JSON_QUERY('{
--      "currentValue":"sys.dm_os_volume_stats not available on this instance.",
--      "whyThisMatters":"Low free space on SQL data/log/backup volumes can cause outages and failed growth/restores.",
--      "whatItChecksFor":"Normally: free space (GB and %) for volumes that host SQL data, log, and backup files.",
--      "whatToDo":"On this instance, run a manual disk-space check for the drives that host SQL files and backups."
--    }');
--END;


/* 32) Instant File Initialization (IFI) — moved to Instance Configuration */
IF NOT EXISTS (SELECT 1 FROM #checks WHERE title = N'Instant File Initialization (IFI)')
BEGIN
  DECLARE @ifiAcct nvarchar(256) =
    (SELECT TOP(1) service_account FROM sys.dm_server_services WHERE servicename LIKE 'SQL Server (%');

  INSERT INTO #checks(title,category,section,status,severityScore,metadata)
  SELECT
    N'Instant File Initialization (IFI)',
    N'Instance Configuration',      -- moved
    N'Storage & I/O',               -- section
    N'INFO', 3,
    JSON_QUERY((
      SELECT
        CONCAT('SQL Server service account: ', ISNULL(@ifiAcct,'(unknown)'),
               '. IFI lets data files grow/restores complete faster by skipping zero-fill.') AS currentValue,
        'Without IFI, growing/creating data files must be zero-initialized, which can stall workloads.'      AS whyThisMatters,
        'Whether the SQL Server service account has “Perform volume maintenance tasks” (SeManageVolumePrivilege).' AS whatItChecksFor,
        'Grant the privilege to the SQL Server service account (only data files; log files still zero-fill).' AS whatToDo,
        'Local Security Policy → Local Policies → User Rights Assignment → Perform volume maintenance tasks → add SQL service account.' AS fixScript,
        'SELECT service_account FROM sys.dm_server_services WHERE servicename LIKE ''SQL Server (%'';'        AS validationQuery
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ));
END

/* 32-A) Local disk free space (SQL volumes) */
IF EXISTS (SELECT 1 FROM sys.all_objects WHERE name = 'dm_os_volume_stats')
BEGIN
  ;WITH vols AS (
    SELECT DISTINCT
      vs.volume_mount_point,
      vs.logical_volume_name,
      totalGB = CAST(vs.total_bytes / 1024.0 / 1024 / 1024 AS decimal(18,1)),
      freeGB  = CAST(vs.available_bytes / 1024.0 / 1024 / 1024 AS decimal(18,1))
    FROM sys.master_files AS mf
    CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) AS vs
  ),
  calc AS (
    SELECT
      volume_mount_point,
      logical_volume_name,
      totalGB,
      freeGB,
      pctFree = CASE
                  WHEN totalGB > 0
                    THEN CAST((freeGB * 100.0) / totalGB AS decimal(5,1))
                  ELSE NULL
                END
    FROM vols
  ),
  offenders AS (
    -- "Bad" volumes: < 10% free OR < 10 GB free
    SELECT *
    FROM calc
    WHERE pctFree IS NOT NULL
      AND (pctFree < 10 OR freeGB < 10)
  ),
  warners AS (
    -- "Warn" volumes: < 20% free OR < 20 GB free but not already in offenders
    SELECT c.*
    FROM calc AS c
    WHERE c.pctFree IS NOT NULL
      AND (c.pctFree < 20 OR c.freeGB < 20)
      AND NOT EXISTS (
            SELECT 1
            FROM offenders o
            WHERE o.volume_mount_point = c.volume_mount_point
          )
  )
  INSERT INTO #checks (title, category, section, status, severityScore, metadata)
  SELECT
    N'Local disk free space (SQL volumes)',
    N'Storage & I/O',
    N'Volumes',
    CASE
      WHEN EXISTS (SELECT 1 FROM offenders) THEN N'FAIL'
      WHEN EXISTS (SELECT 1 FROM warners)   THEN N'WARN'
      ELSE N'PASS'
    END,
    4,
    JSON_QUERY((
      SELECT
        (SELECT COUNT(1) FROM offenders) AS currentValue,
        (SELECT
           volume_mount_point,
           logical_volume_name,
           totalGB,
           freeGB,
           pctFree
         FROM calc
         FOR JSON PATH) AS items,
        (SELECT
           volume_mount_point,
           logical_volume_name,
           totalGB,
           freeGB,
           pctFree
         FROM offenders
         FOR JSON PATH) AS offenders,
        (SELECT
           volume_mount_point,
           logical_volume_name,
           totalGB,
           freeGB,
           pctFree
         FROM warners
         FOR JSON PATH) AS warners
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ));
END


/* 33) SQL Agent alerting basics — friendly */
IF NOT EXISTS (SELECT 1 FROM #checks WHERE title = N'SQL Agent alerting basics')
BEGIN
  DECLARE @dbmail int  = (SELECT COUNT(*) FROM msdb.dbo.sysmail_account);
  DECLARE @opers  int  = (SELECT COUNT(*) FROM msdb.dbo.sysoperators);
  DECLARE @sevAl  int  = (SELECT COUNT(*) FROM msdb.dbo.sysalerts WHERE severity BETWEEN 20 AND 25);
  DECLARE @missing nvarchar(300) =
    CONCAT(
      CASE WHEN ISNULL(@dbmail,0)=0 THEN 'DB Mail account; ' ELSE '' END,
      CASE WHEN ISNULL(@opers,0)=0  THEN 'Operator; ' ELSE '' END,
      CASE WHEN ISNULL(@sevAl,0)=0  THEN 'severity 20–25 alert(s); ' ELSE '' END
    );

  INSERT INTO #checks(title,category,section,status,severityScore,metadata)
  SELECT
    N'SQL Agent alerting basics',
    N'Monitoring & Alerts',
    N'Alerts',
    CASE WHEN @dbmail>0 AND @opers>0 AND @sevAl>0 THEN 'PASS' ELSE 'WARN' END, 3,
    JSON_QUERY((
      SELECT
        CASE WHEN @dbmail>0 AND @opers>0 AND @sevAl>0 THEN
          CONCAT('Alerting in place (DB Mail: ',@dbmail,', Operators: ',@opers,', sev 20–25 alerts: ',@sevAl,').')
        ELSE
          CONCAT('Missing pieces: ',
                 CASE WHEN LEN(@missing)>2 THEN LEFT(@missing, LEN(@missing)-2) ELSE '(none)' END,
                 ' — add these so engine errors send email.')
        END AS currentValue,
        'If a SQL job fails and nobody gets an email, issues can run quietly and risk data loss/outages.'      AS whyThisMatters,
        'Presence of DB Mail, at least one Operator, and severity 20–25 alerts.'                              AS whatItChecksFor,
        'Set up DB Mail → define an Operator → create severity 20–25 alerts targeting that Operator.'         AS whatToDo,
        'EXEC msdb..sysmail_add_account_sp ...; EXEC msdb..sp_add_operator ...; EXEC msdb..sp_add_alert ...;' AS fixScript,
        'SELECT (SELECT COUNT(*) FROM msdb..sysmail_account) dbMail,
                (SELECT COUNT(*) FROM msdb..sysoperators) operators,
                (SELECT COUNT(*) FROM msdb..sysalerts WHERE severity BETWEEN 20 AND 25) sevAlerts;'            AS validationQuery
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ));
END

/* 34) Query Store enabled & configured — friendly */
IF NOT EXISTS (SELECT 1 FROM #checks WHERE title = N'Query Store enabled & configured')
BEGIN
  ;WITH qs AS (
    SELECT d.name AS database_name,
           CAST(DATABASEPROPERTYEX(d.name,'IsQueryStoreOn') AS int)         AS is_on,
           CAST(DATABASEPROPERTYEX(d.name,'QueryStoreDesiredState') AS int) AS desired_state
    FROM sys.databases d WHERE d.database_id>4 AND d.state=0
  ),
  bad AS (
    SELECT * FROM qs WHERE ISNULL(is_on,0)=0 OR ISNULL(desired_state,1)<>1
  )
  INSERT INTO #checks(title,category,section,status,severityScore,metadata)
  SELECT
    N'Query Store enabled & configured',
    N'Monitoring & Alerts',
    N'Query Store',
    CASE WHEN EXISTS(SELECT 1 FROM bad) THEN 'WARN' ELSE 'PASS' END, 2,
    JSON_QUERY((
      SELECT
        CASE WHEN EXISTS(SELECT 1 FROM bad) THEN
          CONCAT('Query Store not ready in ', (SELECT COUNT(*) FROM bad), ' database(s): ',
                 ISNULL((SELECT STRING_AGG(QUOTENAME(database_name), ', ') FROM bad), '(list unavailable)'), '.')
        ELSE
          'All user databases have Query Store ON and set to READ_WRITE.'
        END AS currentValue,
        'Query Store captures query/plan history so you can spot regressions and quickly stabilize performance.' AS whyThisMatters,
        'Each user DB should have Query Store ON and READ_WRITE.'                                               AS whatItChecksFor,
        'Enable Query Store in READ_WRITE and set size/cleanup thresholds appropriate to your environment.'     AS whatToDo,
        'ALTER DATABASE <db> SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);'                               AS fixScript,
        'SELECT DB_NAME(database_id) db, actual_state_desc, desired_state_desc FROM sys.database_query_store_options;' AS validationQuery,
        (SELECT database_name FROM bad FOR JSON PATH) AS offenders
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ));
END

/* 35) Error log cycling policy — friendly */
IF NOT EXISTS (SELECT 1 FROM #checks WHERE title = N'Error log cycling policy')
BEGIN
  DECLARE @retained int = NULL, @hasJob bit = 0;
  BEGIN TRY
    CREATE TABLE #enum(Archive int, LogDate nvarchar(64), SizeMB int);
    INSERT #enum EXEC master.dbo.xp_enumerrorlogs;
    SELECT @retained = COUNT(*)-1 FROM #enum WHERE Archive>=1;
    DROP TABLE #enum;
  END TRY BEGIN CATCH
    SET @retained = NULL;
  END CATCH;
  IF DB_ID('msdb') IS NOT NULL
    IF EXISTS (
      SELECT 1 FROM msdb.dbo.sysjobs j JOIN msdb.dbo.sysjobsteps s ON j.job_id=s.job_id
      WHERE s.command LIKE '%sp_cycle_errorlog%' OR s.command LIKE '%sp_cycle_agent_errorlog%'
    ) SET @hasJob = 1;

  INSERT INTO #checks(title,category,section,status,severityScore,metadata)
  SELECT
    N'Error log cycling policy',
    N'Monitoring & Alerts',
    N'Logs',
    CASE WHEN @retained IS NULL THEN 'INFO' WHEN @retained>=12 AND @hasJob=1 THEN 'PASS' ELSE 'WARN' END, 2,
    JSON_QUERY((
      SELECT
        CASE
          WHEN @retained IS NULL THEN 'Could not read error-log archives (permissions or feature unavailable).'
          WHEN @hasJob=1 AND @retained>=12 THEN CONCAT('Good: ',@retained,' archives retained; cycling job detected.')
          WHEN @hasJob=0 THEN CONCAT('Cycling job is missing. Current retained archives: ',COALESCE(CONVERT(varchar,@retained),'(unknown)'),' — add a weekly job.')
          ELSE CONCAT('Low retention (',@retained,') or config unknown — target ≥12 archives and add a weekly cycling job.')
        END AS currentValue,
        'Uncycled / oversized error logs are hard to scan and can hide new errors.'                                AS whyThisMatters,
        'Presence of a regular error-log cycling job and a sensible retention count (≥12).'                        AS whatItChecksFor,
        'Create a weekly job to run sp_cycle_errorlog (and sp_cycle_agent_errorlog) and keep ~12 archives.'        AS whatToDo,
        'EXEC sp_cycle_errorlog; schedule weekly; configure NumberOfLogFiles if policy requires.'                   AS fixScript,
        'EXEC xp_enumerrorlogs;'                                                                                   AS validationQuery
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ));
END

/* 36) Recovery model vs backup strategy alignment — friendly */
IF NOT EXISTS (SELECT 1 FROM #checks WHERE title = N'Recovery model vs backup strategy alignment')
BEGIN
  ;WITH dbs AS (
    SELECT d.name, d.recovery_model_desc
    FROM sys.databases d
    WHERE d.database_id > 4 AND d.state = 0
  ),
  lastLog AS (
    SELECT bs.database_name, MAX(bs.backup_finish_date) AS last_log_backup
    FROM msdb.dbo.backupset bs WHERE bs.type = 'L' GROUP BY bs.database_name
  ),
  missing AS (
    SELECT b.name, b.recovery_model_desc, l.last_log_backup
    FROM dbs b
    LEFT JOIN lastLog l ON l.database_name = b.name
    WHERE b.recovery_model_desc = 'FULL'
      AND (l.last_log_backup IS NULL OR l.last_log_backup < DATEADD(HOUR,-24,GETDATE()))
  )
  INSERT INTO #checks (title, category, section, status, severityScore, metadata)
  SELECT
    N'Recovery model vs backup strategy alignment',
    N'Reliability',
    N'Backups',
    CASE WHEN EXISTS (SELECT 1 FROM missing) THEN N'FAIL' ELSE N'PASS' END,
    4,
    JSON_QUERY((
      SELECT
        CASE WHEN EXISTS (SELECT 1 FROM missing) THEN
          'Some databases are in FULL recovery but did not have a log backup in the last 24 hours. '
          + 'Without frequent LOG backups, the transaction log grows continuously and you lose point-in-time restore.'
         ELSE
          'All FULL recovery databases have a recent LOG backup (≤ 24 hours).'
        END AS currentValue,
        'RPO = how much data you can afford to lose. LOG backups keep your RPO low and the log small.'                 AS whyThisMatters,
        'Databases in FULL recovery whose last LOG backup was >24h ago (or never).'                                    AS whatItChecksFor,
        'Run frequent LOG backups (e.g., every 5–15 minutes for critical DBs) or switch to SIMPLE if PITR is not required.' AS whatToDo,
        'Use Ola Hallengren or SQL Agent jobs to schedule regular LOG backups; monitor failures.'                       AS fixScript,
        'SELECT database_name, MAX(backup_finish_date) FROM msdb..backupset WHERE type=''L'' GROUP BY database_name;'  AS validationQuery,
        (SELECT name, recovery_model_desc, last_log_backup FROM missing FOR JSON PATH) AS offenders
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ));
END

/* 37) Public role grants (server & databases) — friendly */
IF NOT EXISTS (SELECT 1 FROM #checks WHERE title = N'Public role grants (server & databases)')
BEGIN
  IF OBJECT_ID('tempdb..#db_public_grants') IS NOT NULL DROP TABLE #db_public_grants;
  CREATE TABLE #db_public_grants(database_name sysname, bad_grants int NOT NULL);

  DECLARE @pdb sysname, @psql nvarchar(max);
  DECLARE curP CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM sys.databases WHERE database_id>4 AND state=0;
  OPEN curP; FETCH NEXT FROM curP INTO @pdb;
  WHILE @@FETCH_STATUS=0
  BEGIN
    SET @psql = N'
      INSERT INTO #db_public_grants(database_name,bad_grants)
      SELECT N''' + REPLACE(@pdb,'''','''''') + ''', COUNT(*)
      FROM ' + QUOTENAME(@pdb) + N'.sys.database_permissions
      WHERE grantee_principal_id = USER_ID(''public'')
        AND state_desc=''GRANT'' AND permission_name NOT IN (''CONNECT'');';
    EXEC sp_executesql @psql;
    FETCH NEXT FROM curP INTO @pdb;
  END
  CLOSE curP; DEALLOCATE curP;

  ;WITH srv AS (
    SELECT COUNT(*) AS badServerGrants
    FROM sys.server_permissions p
    JOIN sys.server_principals g ON p.grantee_principal_id=g.principal_id
    WHERE g.name='public' AND p.state_desc='GRANT' AND p.permission_name NOT IN ('CONNECT')
  ),
  dbg AS (SELECT SUM(bad_grants) AS badDbGrants FROM #db_public_grants)
  INSERT INTO #checks(title,category,section,status,severityScore,metadata)
  SELECT
    N'Public role grants (server & databases)',
    N'Security',
    N'Principals & Permissions',
    CASE WHEN (SELECT badServerGrants FROM srv)>0 OR ISNULL((SELECT badDbGrants FROM dbg),0)>0 THEN 'FAIL' ELSE 'PASS' END,
    4,
    JSON_QUERY((
      SELECT
        CASE 
          WHEN (SELECT badServerGrants FROM srv)>0 OR ISNULL((SELECT badDbGrants FROM dbg),0)>0
          THEN CONCAT(
            'Unsafe grants to "public": server-level=',(SELECT badServerGrants FROM srv),
            ', database-level=',ISNULL((SELECT badDbGrants FROM dbg),0),'. Revoke to reduce exposure.'
          )
          ELSE 'No unsafe permissions granted to the built-in "public" role.'
        END AS currentValue,
        'Any permission granted to "public" is inherited by every login/user. A single mistake can expose data.' AS whyThisMatters,
        'Server/DB permissions granted to "public" beyond CONNECT.'                                              AS whatItChecksFor,
        'Revoke from "public" and grant only to named roles/logins with least-privilege.'                        AS whatToDo,
        'REVOKE <perm> FROM public; check sys.server_permissions and sys.database_permissions.'                  AS fixScript,
        '/* Server */ SELECT * FROM sys.server_permissions p JOIN sys.server_principals g ON p.grantee_principal_id=g.principal_id WHERE g.name=''public''; /* DB */ SELECT DB_NAME() db, * FROM sys.database_permissions WHERE grantee_principal_id=USER_ID(''public'');' AS validationQuery,
        (SELECT database_name, bad_grants FROM #db_public_grants WHERE bad_grants>0 FOR JSON PATH) AS offenders
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ));
END

/* 38) Fixed server role sprawl (non-sysadmin) */
IF NOT EXISTS (SELECT 1 FROM #checks WHERE title = N'Fixed server role sprawl (non-sysadmin)')
BEGIN
  ;WITH m AS (
    SELECT r.name AS role_name, p.name AS member_name
    FROM sys.server_role_members rm
    JOIN sys.server_principals r ON rm.role_principal_id=r.principal_id
    JOIN sys.server_principals p ON rm.member_principal_id=p.principal_id
    WHERE r.name IN ('securityadmin','serveradmin','processadmin','diskadmin','dbcreator','bulkadmin')
      AND p.type_desc IN ('SQL_LOGIN','WINDOWS_LOGIN','WINDOWS_GROUP')
  )
  INSERT INTO #checks(title,category,section,status,severityScore,metadata)
  SELECT
    N'Fixed server role sprawl (non-sysadmin)',
    N'Security',
    N'Server Principals',
    CASE WHEN EXISTS(SELECT 1 FROM m) THEN 'WARN' ELSE 'PASS' END, 3,
    JSON_QUERY((
      SELECT
        CONCAT('Members in powerful fixed roles: ', (SELECT COUNT(*) FROM m), '.') AS currentValue,
        'Broad fixed roles widen attack surface; prune to least-privilege.'          AS whyThisMatters,
        'Who belongs to built-in fixed server roles (except sysadmin tracked elsewhere).' AS whatItChecksFor,
        'Remove unneeded memberships; prefer custom roles with minimal rights.'      AS whatToDo,
        'EXEC sp_dropsrvrolemember ''<role>'',''<login>''; (use sp_addsrvrolemember sparingly).' AS fixScript,
        'SELECT r.name role_name, p.name member_name FROM sys.server_role_members rm JOIN sys.server_principals r ON rm.role_principal_id=r.principal_id JOIN sys.server_principals p ON rm.member_principal_id=p.principal_id;' AS validationQuery,
        (SELECT role_name, member_name FROM m FOR JSON PATH) AS members
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ));
END

/* 39) Error log current size (MB) */
IF NOT EXISTS (SELECT 1 FROM #checks WHERE title = N'Error log current size (MB)')
BEGIN
  DECLARE @elogMB int = NULL;
  BEGIN TRY
    CREATE TABLE #e(Archive int, LogDate nvarchar(64), SizeMB int);
    INSERT #e EXEC master.dbo.xp_enumerrorlogs;
    SELECT @elogMB = SizeMB FROM #e WHERE Archive=0; DROP TABLE #e;
  END TRY BEGIN CATCH
    SET @elogMB = NULL;
  END CATCH;

  INSERT INTO #checks(title,category,section,status,severityScore,metadata)
  SELECT
    N'Error log current size (MB)',
    N'Monitoring & Alerts',
    N'Logs',
    CASE WHEN @elogMB IS NULL THEN 'INFO' WHEN @elogMB>256 THEN 'WARN' ELSE 'PASS' END, 2,
    JSON_QUERY((
      SELECT
        CASE WHEN @elogMB IS NULL THEN 'Current error-log size unknown.'
             ELSE CONCAT('Current error-log size: ', CONVERT(varchar,@elogMB), ' MB.')
        END AS currentValue,
        'Very large current log slows investigation and suggests missing cycling.' AS whyThisMatters,
        'The size of the active SQL error log.'                                  AS whatItChecksFor,
        'Set up regular cycling and retain manageable archives.'                  AS whatToDo,
        'EXEC sp_cycle_errorlog; schedule weekly; keep ≥12 archives.'             AS fixScript,
        'EXEC xp_enumerrorlogs;'                                                  AS validationQuery
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ));
END

/* 40) Lock Pages in Memory (LPIM) — friendly */
IF NOT EXISTS (SELECT 1 FROM #checks WHERE title = N'Lock Pages in Memory (LPIM)')
BEGIN
  DECLARE @ed2 nvarchar(256)=CAST(SERVERPROPERTY('Edition') AS nvarchar(256));
  DECLARE @isExp bit = CASE WHEN @ed2 LIKE '%Express%' THEN 1 ELSE 0 END;
  DECLARE @physMB bigint = (SELECT CAST(total_physical_memory_kb/1024 AS bigint) FROM sys.dm_os_sys_memory);
  DECLARE @model nvarchar(60)=NULL, @lockedKB bigint=NULL;
  IF COL_LENGTH('sys.dm_os_sys_info','sql_memory_model_desc') IS NOT NULL
    SELECT @model=sql_memory_model_desc FROM sys.dm_os_sys_info;
  IF OBJECT_ID('sys.dm_os_process_memory') IS NOT NULL
    SELECT @lockedKB=locked_page_allocations_kb FROM sys.dm_os_process_memory;
  DECLARE @lpim bit = CASE WHEN UPPER(ISNULL(@model,'')) IN ('LOCK_PAGES','LARGE_PAGES') OR ISNULL(@lockedKB,0)>0 THEN 1 ELSE 0 END;
  DECLARE @big  bit = CASE WHEN ISNULL(@physMB,0)>=16384 THEN 1 ELSE 0 END;
  DECLARE @st  varchar(5) = CASE WHEN @lpim=1 THEN 'PASS' WHEN @big=1 AND @isExp=0 THEN 'WARN' ELSE 'INFO' END;

  INSERT INTO #checks(title,category,section,status,severityScore,metadata)
  SELECT
    N'Lock Pages in Memory (LPIM)',
    N'Performance',
    N'Memory',
    @st, 2,
    JSON_QUERY((
      SELECT
        CONCAT('SQL memory model: ', ISNULL(@model,'(unknown)'), '; locked page KB: ', ISNULL(CONVERT(varchar,@lockedKB),'0'), '.') AS currentValue,
        'LPIM helps keep the buffer pool from being paged out under OS pressure.'                                                AS whyThisMatters,
        'Whether LPIM is in effect for SQL Server.'                                                                              AS whatItChecksFor,
        'Grant “Lock pages in memory” to the SQL service account on sizeable hosts (≥16 GB); set sane max/min memory.'           AS whatToDo,
        'Grant right in Local Security Policy; restart SQL; validate with DMVs.'                                                 AS fixScript,
        'SELECT * FROM sys.dm_os_process_memory; SELECT sql_memory_model_desc FROM sys.dm_os_sys_info;'                          AS validationQuery
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ));
END

/* 41) TempDB file sizing & growth sanity — already covered earlier as original #14 equivalent
      (kept once; no duplicate creation) */

/* 42) CHECKDB maintenance job present — friendly kept once (no duplicate) */
IF NOT EXISTS (SELECT 1 FROM #checks WHERE title = N'CHECKDB maintenance job present')
BEGIN
  DECLARE @hasCheckdb bit =
    CASE WHEN EXISTS (
      SELECT 1 FROM msdb.dbo.sysjobs j
      JOIN msdb.dbo.sysjobsteps s ON j.job_id=s.job_id
      WHERE s.command LIKE '%CHECKDB%'
    ) THEN 1 ELSE 0 END;

  INSERT INTO #checks(title,category,section,status,severityScore,metadata)
  SELECT
    N'CHECKDB maintenance job present',
    N'Maintenance & Jobs',
    N'Integrity',
    CASE WHEN @hasCheckdb=1 THEN 'PASS' ELSE 'WARN' END, 3,
    JSON_QUERY((
      SELECT
        CASE WHEN @hasCheckdb=1 THEN 'A CHECKDB job was found.' ELSE 'No CHECKDB job was found.' END AS currentValue,
        'Without routine CHECKDB, corruption can linger undetected and backups may be useless.'        AS whyThisMatters,
        'Whether a recurring Agent job executes DBCC CHECKDB.'                                         AS whatItChecksFor,
        'Create a regular CHECKDB job with exclusions/scheduling appropriate to size.'                 AS whatToDo,
        'Use Ola Hallengren or custom Agent job to run DBCC CHECKDB.'                                  AS fixScript,
        'SELECT j.name,s.step_id,s.command FROM msdb..sysjobs j JOIN msdb..sysjobsteps s ON j.job_id=s.job_id WHERE s.command LIKE ''%CHECKDB%'';' AS validationQuery
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ));
END

/* 43) Index & Stats maintenance job present */
IF NOT EXISTS (SELECT 1 FROM #checks WHERE title = N'Index & Stats maintenance job present')
BEGIN
  DECLARE @hasIx bit =
    CASE WHEN EXISTS (
      SELECT 1 FROM msdb.dbo.sysjobs j
      JOIN msdb.dbo.sysjobsteps s ON j.job_id=s.job_id
      WHERE s.command LIKE '%IndexOptimize%' OR s.command LIKE '%ALTER INDEX%' OR s.command LIKE '%UPDATE STATISTICS%'
    ) THEN 1 ELSE 0 END;

  INSERT INTO #checks(title,category,section,status,severityScore,metadata)
  SELECT
    N'Index & Stats maintenance job present',
    N'Maintenance & Jobs',
    N'Index/Stats',
    CASE WHEN @hasIx=1 THEN 'PASS' ELSE 'WARN' END, 2,
    JSON_QUERY((
      SELECT
        CASE WHEN @hasIx=1 THEN 'Index/Stats job detected.' ELSE 'No Index/Stats job detected.' END AS currentValue,
        'Regular maintenance helps avoid long-term degradation and bad plans.'                        AS whyThisMatters,
        'Presence of a job that rebuilds/reorganizes indexes and updates stats.'                     AS whatItChecksFor,
        'Install/schedule a maintenance solution (Ola or tuned in-house jobs).'                      AS whatToDo,
        'EXECUTE IndexOptimize (Ola) or Agent jobs with ALTER INDEX/UPDATE STATISTICS.'              AS fixScript,
        'SELECT j.name,s.step_id,s.command FROM msdb..sysjobs j JOIN msdb..sysjobsteps s ON j.job_id=s.job_id;' AS validationQuery
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ));
END

/* 44) Orphaned database users */
IF NOT EXISTS (SELECT 1 FROM #checks WHERE title = N'Orphaned database users')
BEGIN
  IF OBJECT_ID('tempdb..#orph') IS NOT NULL DROP TABLE #orph;
  CREATE TABLE #orph(database_name sysname, user_name sysname, type_desc sysname);

  DECLARE @odb sysname, @osql nvarchar(max);
  DECLARE curO CURSOR LOCAL FAST_FORWARD FOR SELECT name FROM sys.databases WHERE database_id>4 AND state=0;
  OPEN curO; FETCH NEXT FROM curO INTO @odb;
  WHILE @@FETCH_STATUS=0
  BEGIN
    SET @osql = N'
      INSERT INTO #orph(database_name,user_name,type_desc)
      SELECT N''' + REPLACE(@odb,'''','''''') + ''', dp.name, dp.type_desc
      FROM ' + QUOTENAME(@odb) + N'.sys.database_principals dp
      LEFT JOIN sys.server_principals sp ON dp.sid = sp.sid
      WHERE dp.type IN (''S'',''U'') AND dp.authentication_type<>2 AND dp.sid IS NOT NULL AND sp.sid IS NULL;';
    EXEC sp_executesql @osql;
    FETCH NEXT FROM curO INTO @odb;
  END
  CLOSE curO; DEALLOCATE curO;

  INSERT INTO #checks(title,category,section,status,severityScore,metadata)
  SELECT
    N'Orphaned database users',
    N'Security',
    N'Principals & Permissions',
    CASE WHEN EXISTS(SELECT 1 FROM #orph) THEN 'WARN' ELSE 'PASS' END, 3,
    JSON_QUERY((
      SELECT
        CONCAT('Orphaned users found: ', (SELECT COUNT(*) FROM #orph), '.') AS currentValue,
        'Users without matching server logins break access after restores/migrations.'      AS whyThisMatters,
        'DB principals whose SIDs don’t map to any server principal SID.'                  AS whatItChecksFor,
        'Relink users to their logins or recreate logins with original SID.'               AS whatToDo,
        'ALTER USER <user> WITH LOGIN = <login>; or CREATE LOGIN ... WITH SID = <sid>.'    AS fixScript,
        '/* Example */ SELECT name, sid FROM sys.server_principals WHERE name = ''<login>'';' AS validationQuery,
        (SELECT database_name, user_name, type_desc FROM #orph FOR JSON PATH) AS offenders
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ));
END

/* 45) Network transport (TCP presence) */
IF NOT EXISTS (SELECT 1 FROM #checks WHERE title = N'Network transport (TCP presence)')
BEGIN
  ;WITH t AS (SELECT net_transport, COUNT(*) AS sessions FROM sys.dm_exec_connections GROUP BY net_transport)
  INSERT INTO #checks(title,category,section,status,severityScore,metadata)
  SELECT
    N'Network transport (TCP presence)',
    N'Networking & Protocols',
    N'TCP',
    CASE WHEN EXISTS(SELECT 1 FROM t WHERE net_transport='TCP') THEN 'PASS' ELSE 'WARN' END, 1,
    JSON_QUERY((
      SELECT
        CASE WHEN EXISTS(SELECT 1 FROM t WHERE net_transport='TCP')
             THEN 'Clients are connecting over TCP (remote connectivity OK).'
             ELSE 'No TCP connections observed; enable TCP/IP for remote clients.'
        END AS currentValue,
        'Non-TCP transports can hide misconfigurations and block remote clients.'        AS whyThisMatters,
        'That TCP/IP is enabled and used for client connections.'                        AS whatItChecksFor,
        'Enable TCP/IP in SQL Server Configuration Manager; restart if needed.'          AS whatToDo,
        'Use SQL Server Configuration Manager → SQL Server Network Configuration → Protocols → TCP/IP (Enabled).' AS fixScript,
        'SELECT net_transport, COUNT(*) FROM sys.dm_exec_connections GROUP BY net_transport;' AS validationQuery,
        (SELECT * FROM t FOR JSON PATH) AS breakdown
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ));
END

/* 45-A) Shared Memory / Named Pipes protocol state */
/* 45-A) Shared Memory / Named Pipes protocol state */
IF EXISTS (SELECT 1 FROM sys.all_objects WHERE name = 'dm_server_registry')
BEGIN
    -- Materialize protocol state into a temp table so we can reuse it
    IF OBJECT_ID('tempdb..#prot') IS NOT NULL DROP TABLE #prot;

    SELECT
        protocol =
            CASE
                WHEN registry_key LIKE '%\SuperSocketNetLib\Sm'  THEN 'Shared Memory'
                WHEN registry_key LIKE '%\SuperSocketNetLib\Np'  THEN 'Named Pipes'
                WHEN registry_key LIKE '%\SuperSocketNetLib\Tcp' THEN 'TCP'
            END,
        enabled = TRY_CAST(value_data AS int)   -- <== correct column name
    INTO #prot
    FROM sys.dm_server_registry
    WHERE value_name = 'Enabled'
      AND registry_key LIKE '%\SuperSocketNetLib\%';

    -----------------------------------------------------------------------
    -- Shared Memory protocol (local)
    -----------------------------------------------------------------------
    INSERT INTO #checks (title, category, section, status, severityScore, metadata)
    SELECT
        N'Shared Memory protocol (local)',
        N'Networking & Protocols',
        N'Shared Memory',
        CASE
            WHEN EXISTS (SELECT 1 FROM #prot p WHERE p.protocol = 'Shared Memory' AND p.enabled = 1)
                THEN N'PASS'
            ELSE N'WARN'
        END,
        1,
        JSON_QUERY((
            SELECT
                (SELECT TOP (1) p.enabled
                 FROM #prot p
                 WHERE p.protocol = 'Shared Memory') AS currentValue,
                'Shared Memory is a local-only transport used by tools on the same host. It is usually safe to leave enabled, but some hardened builds turn it off intentionally.' AS whyThisMatters,
                'Whether the Shared Memory protocol is enabled for this SQL instance.' AS whatItChecksFor,
                'Confirm your vendor/tools requirements. For most environments, Shared Memory can stay enabled; if you disable it, verify all local tools use TCP.' AS whatToDo
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        ));

    -----------------------------------------------------------------------
    -- Named Pipes protocol (legacy)
    -----------------------------------------------------------------------
    INSERT INTO #checks (title, category, section, status, severityScore, metadata)
    SELECT
        N'Named Pipes protocol (legacy)',
        N'Networking & Protocols',
        N'Named Pipes',
        CASE
            WHEN EXISTS (SELECT 1 FROM #prot p WHERE p.protocol = 'Named Pipes' AND p.enabled = 1)
                THEN N'WARN'   -- Modern best practice: OFF unless required
            ELSE N'PASS'
        END,
        2,
        JSON_QUERY((
            SELECT
                (SELECT TOP (1) p.enabled
                 FROM #prot p
                 WHERE p.protocol = 'Named Pipes') AS currentValue,
                'Named Pipes is mostly for legacy or special-case clients. Leaving it enabled widens the surface area and can complicate troubleshooting.' AS whyThisMatters,
                'Whether the Named Pipes protocol is enabled for this SQL instance.' AS whatItChecksFor,
                'Disable Named Pipes unless a specific vendor or legacy client requires it. Prefer TCP for all normal client connections.' AS whatToDo
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        ));
END



/* 46) Agent job failure notifications configured */
IF NOT EXISTS (SELECT 1 FROM #checks WHERE title = N'Agent job failure notifications configured')
BEGIN
  ;WITH j AS (
    SELECT j.job_id, j.name, ISNULL(j.notify_email_operator_id,0) AS notifyOp
    FROM msdb.dbo.sysjobs j
  ),
  missing AS (SELECT name FROM j WHERE notifyOp=0)
  INSERT INTO #checks(title,category,section,status,severityScore,metadata)
  SELECT
    N'Agent job failure notifications configured',
    N'Maintenance & Jobs',
    N'Alerts',
    CASE WHEN EXISTS(SELECT 1 FROM missing) THEN N'WARN' ELSE N'PASS' END,
    2,
    JSON_QUERY((
      SELECT
        CASE WHEN EXISTS(SELECT 1 FROM missing)
          THEN N'These jobs do not email on failure: '
               + STUFF((SELECT N', ' + m.name FROM missing m FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,N'')
          ELSE N'All SQL Agent jobs email an operator when they fail.'
        END AS currentValue,
        N'If a job fails silently, backups or maintenance can stop without anyone knowing.' AS whyThisMatters,
        N'Checks for an Operator configured for “Email when the job fails”.'               AS whatItChecksFor,
        N'Assign an Operator to each important job’s failure notification.'                 AS whatToDo,
        N'In SSMS: SQL Agent → Jobs → <job> → Properties → Notifications → “Email” on failure.' AS fixScript,
        N'SELECT name, notify_email_operator_id FROM msdb..sysjobs;'                       AS validationQuery
      FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ));
END

/* 47) Mixed-page allocation OFF (user DBs) — kept once via friendly block above */
/* 48) Autogrowth reasonableness (data/log) — kept once via friendly block above */
/* 49) TempDB file sizing & growth sanity — kept once via friendly block above */

/* 50) AG health signals (lite) */
IF NOT EXISTS (SELECT 1 FROM #checks WHERE title = N'AG health signals (lite)')
BEGIN
  DECLARE @hasDMV bit = CASE WHEN OBJECT_ID('sys.dm_hadr_database_replica_states') IS NOT NULL THEN 1 ELSE 0 END;
  DECLARE @isOn  int = TRY_CONVERT(int, SERVERPROPERTY('IsHadrEnabled'));
  DECLARE @hasAG bit = 0;
  IF (@hasDMV=1 AND ISNULL(@isOn,0)=1)
     SET @hasAG = CASE WHEN EXISTS(SELECT 1 FROM sys.availability_groups) THEN 1 ELSE 0 END;

  IF (@hasDMV=0)
    INSERT INTO #checks VALUES (N'AG health signals (lite)', N'Availability & DR', N'AG Health', N'INFO', 1,
      JSON_QUERY('{
        "currentValue":"AG DMVs not available on this instance.",
        "whyThisMatters":"Availability Groups protect HA/DR; quick health signals highlight risk.",
        "whatItChecksFor":"Replica roles, sync state, and basic queue sizes.",
        "whatToDo":"Use AG dashboard and DMVs to confirm state; remediate suspended DBs and queue buildup.",
        "fixScript":"Review Always On Dashboard; investigate suspended databases and network/storage bottlenecks.",
        "validationQuery":"SELECT * FROM sys.dm_hadr_database_replica_states;"
      }'));
  ELSE IF (ISNULL(@isOn,0)=0)
    INSERT INTO #checks VALUES (N'AG health signals (lite)', N'Availability & DR', N'AG Health', N'INFO', 1,
      JSON_QUERY('{
        "currentValue":"Always On Availability Groups (HADR) is not enabled on this instance.",
        "whyThisMatters":"If AGs are required for HA/DR, feature must be enabled and configured.",
        "whatItChecksFor":"HADR feature enablement and basic state.",
        "whatToDo":"If AGs are desired, enable HADR and configure an AG; otherwise ignore.",
        "fixScript":"EXEC sp_configure ''hadr enabled'', 1; RECONFIGURE; -- then restart SQL service",
        "validationQuery":"SELECT SERVERPROPERTY(''IsHadrEnabled'') AS IsHadrEnabled;"
      }'));
  ELSE IF (@hasAG=0)
    INSERT INTO #checks VALUES (N'AG health signals (lite)', N'Availability & DR', N'AG Health', N'INFO', 1,
      JSON_QUERY('{
        "currentValue":"No availability groups are defined on this instance.",
        "whyThisMatters":"AGs provide HA/DR. If not required, this is informational.",
        "whatItChecksFor":"Presence of availability groups.",
        "whatToDo":"If HA/DR is a goal, create an AG; otherwise ignore.",
        "fixScript":"CREATE AVAILABILITY GROUP ... ;",
        "validationQuery":"SELECT * FROM sys.availability_groups;"
      }'));
  ELSE
  BEGIN
    ;WITH r AS (
      SELECT ar.replica_server_name, rs.role_desc, rs.operational_state_desc, rs.connected_state_desc
      FROM sys.availability_replicas ar
      JOIN sys.dm_hadr_availability_replica_states rs ON rs.replica_id=ar.replica_id
    ),
    d AS (
      SELECT DB_NAME(dr.database_id) AS database_name, dr.synchronization_state_desc,
             ISNULL(dr.is_suspended,0) AS is_suspended,
             ISNULL(dr.log_send_queue_size,0) AS log_send_queue_size,
             ISNULL(dr.redo_queue_size,0)     AS redo_queue_size
      FROM sys.dm_hadr_database_replica_states dr
    ),
    bad AS (
      SELECT TOP(1) 1 hasIssues
      FROM d
      WHERE is_suspended=1
         OR synchronization_state_desc NOT IN ('SYNCHRONIZED','SYNCHRONIZING')
         OR log_send_queue_size>0 OR redo_queue_size>0
    )
    INSERT INTO #checks(title,category,section,status,severityScore,metadata)
    SELECT
      N'AG health signals (lite)', N'Availability & DR', N'AG Health',
      CASE WHEN EXISTS(SELECT 1 FROM bad) THEN 'WARN' ELSE 'PASS' END, 3,
      JSON_QUERY((
        SELECT
          CASE WHEN EXISTS(SELECT 1 FROM bad)
               THEN 'Issues detected (suspension/unsync/queues > 0).'
               ELSE 'All replicas synchronized; no lag reported.' END                                          AS currentValue,
          'AGs must be synchronized and queue-free to guarantee HA/DR SLAs.'                                    AS whyThisMatters,
          'Replica roles and DB sync state; presence of suspended DBs and queue buildup.'                       AS whatItChecksFor,
          'Resume suspended DBs; fix network/storage bottlenecks; confirm SYNCHRONIZED and near-zero queues.'   AS whatToDo,
          '-- sample DMV:
SELECT DB_NAME(database_id) db, synchronization_state_desc, is_suspended, log_send_queue_size, redo_queue_size
FROM sys.dm_hadr_database_replica_states;'                                                                      AS fixScript,
          'SELECT * FROM sys.dm_hadr_database_replica_states;'                                                  AS validationQuery,
          (SELECT * FROM r FOR JSON PATH) AS replicas,
          (SELECT * FROM d FOR JSON PATH) AS dbStates
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
      ));
  END
END

/* ============================================================================
   FINAL JSON OUTPUT (single row/column [JSON])
   ============================================================================ */
DECLARE @json nvarchar(max);
SELECT @json =
(
  SELECT
      title,
      category,
      section,
      status,
      severityScore,
      metadata = JSON_QUERY(CASE WHEN ISJSON(metadata) = 1 THEN metadata ELSE NULL END)
  FROM #checks
  FOR JSON PATH, INCLUDE_NULL_VALUES
);
SELECT @json AS [JSON];
