-- ================================================================================
-- Verification Scripts for Dashboard Inventory System
-- Author: John Bosco RHOYLLE INNOVATION EMPIRE™ (JB)
-- Created: 2026-01-13
-- ================================================================================

-- ============================================================
-- 1. VERIFY DATA COLLECTION
-- ============================================================
PRINT '1. Checking server counts...';
SELECT 
    'CMS Servers' AS Source, 
    COUNT(DISTINCT server_name) AS ServerCount
FROM msdb.dbo.sysmanagement_shared_registered_servers_internal
UNION ALL
SELECT 
    'dim_Servers (Active)' AS Source, 
    COUNT(*) AS ServerCount
FROM dbo.dim_Servers 
WHERE IsActive = 1
UNION ALL
SELECT 
    'dim_ServerBuilds' AS Source, 
    COUNT(*) AS ServerCount
FROM inventory.dim_ServerBuilds
UNION ALL
SELECT 
    'Dashboard View' AS Source, 
    COUNT(*) AS ServerCount
FROM dashboard.vw_ServerHealthSummary;
GO

-- ============================================================
-- 2. VERIFY DATA QUALITY
-- ============================================================
PRINT '2. Checking data quality...';
SELECT 
    'Servers with NULL SQLVersion' AS [Check],
    COUNT(*) AS Count
FROM dbo.dim_Servers 
WHERE SQLVersion IS NULL AND IsActive = 1
UNION ALL
SELECT 
    'Servers with NULL Edition' AS [Check],
    COUNT(*) AS Count
FROM dbo.dim_Servers 
WHERE Edition IS NULL AND IsActive = 1
UNION ALL
SELECT 
    'Servers with NULL BuildsBehind' AS [Check],
    COUNT(*) AS Count
FROM inventory.dim_ServerBuilds 
WHERE BuildsBehind IS NULL;
GO

-- ============================================================
-- 3. VERIFY DATA FRESHNESS
-- ============================================================
PRINT '3. Checking data freshness...';
SELECT 
    'dim_ServerBuilds' AS TableName,
    MIN(LastUpdated) AS OldestUpdate,
    MAX(LastUpdated) AS MostRecentUpdate,
    DATEDIFF(MINUTE, MAX(LastUpdated), GETDATE()) AS MinutesSinceLastUpdate
FROM inventory.dim_ServerBuilds
WHERE LastUpdated IS NOT NULL
UNION ALL
SELECT 
    'dim_Servers' AS TableName,
    MIN(ModifiedDate) AS OldestUpdate,
    MAX(ModifiedDate) AS MostRecentUpdate,
    DATEDIFF(MINUTE, MAX(ModifiedDate), GETDATE()) AS MinutesSinceLastUpdate
FROM dbo.dim_Servers
WHERE ModifiedDate IS NOT NULL;
GO

-- ============================================================
-- 4. VIEW SAMPLE DATA
-- ============================================================
PRINT '4. Sample data from dashboard view...';
SELECT TOP 10
    ServerName,
    Environment,
    Edition,
    SQLVersion,
    CurrentBuild,
    BuildsBehind,
    DaysTilEOL,
    UpdatePriority,
    OverallHealth
FROM dashboard.vw_ServerHealthSummary
ORDER BY OverallHealth DESC, BuildsBehind DESC;
GO

-- ============================================================
-- 5. CHECK SQL AGENT JOB STATUS
-- ============================================================
PRINT '5. Checking SQL Agent job status...';
SELECT 
    j.name AS JobName,
    j.enabled AS IsEnabled,
    CASE 
        WHEN ja.run_requested_date IS NOT NULL THEN 'RUNNING'
        WHEN jh.run_status = 1 THEN 'SUCCESS'
        WHEN jh.run_status = 0 THEN 'FAILED'
        ELSE 'NEVER RUN'
    END AS LastRunStatus,
    jh.run_date AS LastRunDate,
    jh.run_time AS LastRunTime,
    jh.run_duration AS LastRunDuration,
    STUFF(STUFF(RIGHT('000000' + CAST(jh.run_duration AS VARCHAR), 6), 5, 0, ':'), 3, 0, ':') AS DurationFormatted
FROM msdb.dbo.sysjobs j
LEFT JOIN msdb.dbo.sysjobactivity ja ON j.job_id = ja.job_id
    AND ja.run_requested_date IS NOT NULL
    AND ja.stop_execution_date IS NULL
LEFT JOIN (
    SELECT job_id, run_status, run_date, run_time, run_duration,
           ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY run_date DESC, run_time DESC) AS rn
    FROM msdb.dbo.sysjobhistory
    WHERE step_id = 0
) jh ON j.job_id = jh.job_id AND jh.rn = 1
WHERE j.name = 'DBAOps - Dashboard Inventory Refresh';
GO

-- ============================================================
-- 6. SERVERS NEEDING ATTENTION
-- ============================================================
PRINT '6. Servers needing attention...';
SELECT 
    ServerName,
    Environment,
    SQLVersion,
    BuildsBehind,
    DaysTilEOL,
    OverallHealth
FROM dashboard.vw_ServerHealthSummary
WHERE OverallHealth IN ('CRITICAL', 'WARNING')
ORDER BY 
    CASE OverallHealth 
        WHEN 'CRITICAL' THEN 1 
        WHEN 'WARNING' THEN 2 
    END,
    BuildsBehind DESC;
GO

PRINT '';
PRINT '================================================================================';
PRINT 'Verification Complete';
PRINT '================================================================================';