-- DBAOps Enterprise™ - Create Views
-- Generated: 2026-02-05 08:20:54
-- Views: 207
USE [DBAOpsRepository];
GO

-- [ai].[vw_AICostSummary]

CREATE VIEW ai.vw_AICostSummary
AS
SELECT 
    CAST(RequestDate AS DATE) AS UsageDate,
    COUNT(*) AS TotalRequests,
    SUM(PromptTokens) AS TotalPromptTokens,
    SUM(CompletionTokens) AS TotalCompletionTokens,
    SUM(TotalTokens) AS TotalTokens,
    SUM(EstimatedCostUSD) AS DailyCostUSD,
    AVG(ResponseTimeMS) AS AvgResponseTimeMS,
    SUM(CASE WHEN StatusCode = 200 THEN 1 ELSE 0 END) AS SuccessCount,
    SUM(CASE WHEN StatusCode != 200 OR StatusCode IS NULL THEN 1 ELSE 0 END) AS ErrorCount,
    (SELECT SUM(EstimatedCostUSD) FROM ai.APIUsageLog 
     WHERE MONTH(RequestDate) = MONTH(GETDATE()) AND YEAR(RequestDate) = YEAR(GETDATE())) AS MonthToDateCostUSD,
    (SELECT ConfigValue FROM ai.Configuration WHERE ConfigKey = 'AI_MonthlyBudgetLimit') AS MonthlyBudgetUSD
FROM ai.APIUsageLog
GROUP BY CAST(RequestDate AS DATE);

GO

-- [ai].[vw_AIDashboard]

CREATE   VIEW AI.vw_AIDashboard
AS
SELECT 
    'Recommendations' AS Category,
    Status,
    COUNT(*) AS Count
FROM AI.Recommendations
GROUP BY Status

UNION ALL

SELECT 
    'Anomalies',
    CASE WHEN IsAcknowledged = 1 THEN 'Acknowledged' ELSE 'New' END,
    COUNT(*)
FROM AI.AnomalyDetections
WHERE DetectedDate >= DATEADD(DAY, -7, GETDATE())
GROUP BY IsAcknowledged

UNION ALL

SELECT 
    'Searches Today',
    'Executed',
    COUNT(*)
FROM AI.SearchHistory
WHERE CAST(SearchTime AS DATE) = CAST(GETDATE() AS DATE)

UNION ALL

SELECT 
    'Diagnostics Today',
    Status,
    COUNT(*)
FROM AI.DiagnosticLog
WHERE CAST(RequestTime AS DATE) = CAST(GETDATE() AS DATE)
GROUP BY Status;

GO

-- [alerts].[vw_RecentAlerts]
CREATE OR ALTER VIEW alerts.vw_RecentAlerts AS
SELECT TOP 100 PERCENT
    AlertID,
    ServerName,
    AlertTime,
    Severity,
    AlertType,
    AlertSource,
    AlertMessage,
    DatabaseName,
    ObjectName,
    MetricValue,
    ThresholdValue,
    IsResolved,
    ResolvedDate,
    ResolvedBy,
    AcknowledgedDate,
    AcknowledgedBy,
    Notes,
    CollectedDate
FROM alerts.RecentAlerts
WHERE AlertTime >= DATEADD(HOUR, -24, GETDATE());

GO

-- [Assessment].[vw_ArchitectureFindings]

CREATE   VIEW [Assessment].[vw_ArchitectureFindings] AS
SELECT 
    COALESCE(Category, 'General') AS FindingType,
    CAST(NULL AS NVARCHAR(128)) AS ServerName,
    CAST(NULL AS NVARCHAR(128)) AS DatabaseName,
    COALESCE(Finding, SubCategory) AS Description,
    CurrentState AS Impact,
    RecommendedState AS Recommendation,
    Severity,
    COALESCE(Status, 'Open') AS Status,
    AssessmentDate AS FoundDate
FROM Assessment.ArchitectureFindings;

GO

-- [Assessment].[vw_ComplianceFindings]

CREATE   VIEW [Assessment].[vw_ComplianceFindings] AS
SELECT 
    cf.FrameworkName,
    cc.ControlID,
    COALESCE(cc.ControlName, ccr.ControlCode) AS ControlName,
    cc.Category,
    COALESCE(cc.Severity, 'Medium') AS Severity,
    ccr.ControlStatus AS Status,
    COUNT(DISTINCT ccr.ServerName) AS AffectedServers,
    cc.RemediationSteps AS Recommendation,
    MAX(ccr.Finding) AS Finding,
    MAX(ccr.AssessmentDate) AS LastAssessmentDate
FROM Assessment.ComplianceControlResults ccr
INNER JOIN Assessment.ComplianceFrameworks cf ON ccr.FrameworkCode = cf.FrameworkCode
LEFT JOIN Assessment.ComplianceControls cc ON cc.FrameworkID = cf.FrameworkID AND cc.ControlCode = ccr.ControlCode
WHERE ccr.ControlStatus IN ('FAIL', 'WARNING')
AND EXISTS (
    SELECT 1 FROM config.ServerInventory cms 
    WHERE cms.ServerName = ccr.ServerName AND cms.IsActive = 1
)
GROUP BY cf.FrameworkName, cc.ControlID, cc.ControlName, ccr.ControlCode, cc.Category, cc.Severity, ccr.ControlStatus, cc.RemediationSteps;

GO

-- [Assessment].[vw_ComplianceScorecard]

CREATE   VIEW [Assessment].[vw_ComplianceScorecard] AS
WITH FrameworkResults AS (
    SELECT 
        ccr.FrameworkCode,
        cf.FrameworkName,
        COUNT(*) AS TotalControls,
        SUM(CASE WHEN ccr.ControlStatus = 'PASS' THEN 1 ELSE 0 END) AS PassedControls,
        SUM(CASE WHEN ccr.ControlStatus = 'FAIL' THEN 1 ELSE 0 END) AS FailedControls,
        SUM(CASE WHEN ccr.ControlStatus IN ('ACCEPTED', 'N/A', 'WAIVED') THEN 1 ELSE 0 END) AS AcceptedRisks,
        MAX(ccr.AssessmentDate) AS LastAssessment
    FROM Assessment.ComplianceControlResults ccr
    INNER JOIN Assessment.ComplianceFrameworks cf 
        ON ccr.FrameworkCode = cf.FrameworkCode
    WHERE EXISTS (
        SELECT 1 FROM config.ServerInventory cms 
        WHERE cms.ServerName = ccr.ServerName AND cms.IsActive = 1
    )
    GROUP BY ccr.FrameworkCode, cf.FrameworkName
)
SELECT 
    FrameworkCode,
    FrameworkName,
    CASE WHEN TotalControls > 0 
        THEN CAST((PassedControls + AcceptedRisks) * 100.0 / TotalControls AS DECIMAL(5,2))
        ELSE 0.00 
    END AS OverallScore,
    TotalControls,
    PassedControls,
    FailedControls,
    AcceptedRisks,
    LastAssessment
FROM FrameworkResults;

GO

-- [Assessment].[vw_ComplianceTrend]

CREATE VIEW Assessment.vw_ComplianceTrend
AS
/*
    Compliance Trend - Historical scores for trending charts
    Returns: Daily/weekly aggregated scores for trend analysis
*/
SELECT 
    CAST(ScoreDate AS DATE) AS AssessmentDate,
    COUNT(DISTINCT ServerName) AS ServersAssessed,
    AVG(OverallScore) AS AvgOverallScore,
    AVG(SecurityScore) AS AvgSecurityScore,
    AVG(PerformanceScore) AS AvgPerformanceScore,
    AVG(BackupScore) AS AvgBackupScore,
    AVG(ConfigurationScore) AS AvgConfigurationScore,
    AVG(ArchitectureScore) AS AvgArchitectureScore,
    SUM(CriticalFindings) AS TotalCriticalFindings,
    SUM(HighFindings) AS TotalHighFindings,
    SUM(CriticalFindings + HighFindings + MediumFindings + LowFindings) AS TotalFindings,
    -- Compliance rate (% of servers >= 90)
    CAST(SUM(CASE WHEN OverallScore >= 90 THEN 1 ELSE 0 END) * 100.0 / 
         NULLIF(COUNT(*), 0) AS DECIMAL(5,2)) AS ComplianceRate
FROM Assessment.ServerComplianceScores
WHERE ScoreDate >= DATEADD(DAY, -90, GETDATE())  -- Last 90 days
GROUP BY CAST(ScoreDate AS DATE);

GO

-- [Assessment].[vw_ServerComplianceStatus]

CREATE   VIEW [Assessment].[vw_ServerComplianceStatus] AS
WITH ServerFrameworkScores AS (
    SELECT 
        ccr.ServerName,
        ccr.FrameworkCode,
        COUNT(*) AS TotalControls,
        SUM(CASE WHEN ccr.ControlStatus = 'PASS' THEN 1 ELSE 0 END) AS PassedControls,
        SUM(CASE WHEN ccr.ControlStatus = 'FAIL' THEN 1 ELSE 0 END) AS FailedControls,
        SUM(CASE WHEN ccr.ControlStatus IN ('ACCEPTED', 'N/A', 'WAIVED') THEN 1 ELSE 0 END) AS AcceptedRisks,
        MAX(ccr.AssessmentDate) AS LastAssessment
    FROM Assessment.ComplianceControlResults ccr
    WHERE EXISTS (
        SELECT 1 FROM config.ServerInventory cms 
        WHERE cms.ServerName = ccr.ServerName AND cms.IsActive = 1
    )
    GROUP BY ccr.ServerName, ccr.FrameworkCode
),
ServerScores AS (
    SELECT 
        ServerName,
        MAX(CASE WHEN FrameworkCode = 'CJIS' THEN CASE WHEN TotalControls > 0 THEN CAST((PassedControls + AcceptedRisks) * 100.0 / TotalControls AS DECIMAL(5,2)) ELSE 0 END END) AS CJISScore,
        MAX(CASE WHEN FrameworkCode = 'NIST' THEN CASE WHEN TotalControls > 0 THEN CAST((PassedControls + AcceptedRisks) * 100.0 / TotalControls AS DECIMAL(5,2)) ELSE 0 END END) AS NISTScore,
        MAX(CASE WHEN FrameworkCode = 'STIG' THEN CASE WHEN TotalControls > 0 THEN CAST((PassedControls + AcceptedRisks) * 100.0 / TotalControls AS DECIMAL(5,2)) ELSE 0 END END) AS STIGScore,
        MAX(CASE WHEN FrameworkCode = 'DISA' THEN CASE WHEN TotalControls > 0 THEN CAST((PassedControls + AcceptedRisks) * 100.0 / TotalControls AS DECIMAL(5,2)) ELSE 0 END END) AS DISAScore,
        SUM(PassedControls) AS PassedControls,
        SUM(FailedControls) AS FailedControls,
        SUM(AcceptedRisks) AS AcceptedRisks,
        SUM(FailedControls) AS OpenFindings,
        MAX(LastAssessment) AS LastAssessment
    FROM ServerFrameworkScores
    GROUP BY ServerName
)
SELECT 
    ss.ServerName,
    COALESCE(cms.Environment, cms.CMSEnvironment, 'Unknown') AS Environment,
    cms.SQLVersion,
    COALESCE(cms.ServerType, 'SQL Server') AS ServerType,
    CAST((COALESCE(ss.CJISScore, 0) + COALESCE(ss.NISTScore, 0) + COALESCE(ss.STIGScore, 0) + COALESCE(ss.DISAScore, 0)) / 
        NULLIF((CASE WHEN ss.CJISScore IS NOT NULL THEN 1 ELSE 0 END + 
                CASE WHEN ss.NISTScore IS NOT NULL THEN 1 ELSE 0 END + 
                CASE WHEN ss.STIGScore IS NOT NULL THEN 1 ELSE 0 END + 
                CASE WHEN ss.DISAScore IS NOT NULL THEN 1 ELSE 0 END), 0) AS DECIMAL(5,2)) AS OverallScore,
    ss.CJISScore,
    ss.NISTScore,
    ss.STIGScore,
    ss.DISAScore,
    ss.PassedControls,
    ss.FailedControls,
    ss.AcceptedRisks,
    ss.OpenFindings,
    COALESCE(cms.ComplianceTier, 'Unclassified') AS ComplianceTier,
    ss.LastAssessment
FROM ServerScores ss
INNER JOIN config.ServerInventory cms ON ss.ServerName = cms.ServerName;

GO

-- [Assessment].[vw_Summary]
CREATE OR ALTER VIEW Assessment.vw_Summary AS
WITH LatestScores AS (
    SELECT 
        ServerName,
        OverallScore,
        SecurityScore,
        PerformanceScore,
        BackupScore,
        ConfigurationScore,
        ArchitectureScore,
        CriticalFindings,
        HighFindings,
        MediumFindings,
        LowFindings,
        ScoreDate,
        ROW_NUMBER() OVER (PARTITION BY ServerName ORDER BY ScoreDate DESC) AS rn
    FROM Assessment.ServerComplianceScores
)
SELECT
    ServerName,
    OverallScore,
    SecurityScore,
    PerformanceScore,
    BackupScore,
    ConfigurationScore,
    ArchitectureScore,
    CriticalFindings,
    HighFindings,
    MediumFindings,
    LowFindings,
    ScoreDate
FROM LatestScores
WHERE rn = 1;

GO

-- [change].[vw_ChangesBeforeIncidents]

-- =============================================================================
-- VIEW: change.vw_ChangesBeforeIncidents
-- Correlation between changes and incidents
-- =============================================================================
CREATE   VIEW change.vw_ChangesBeforeIncidents
AS
SELECT 
    i.IncidentID,
    i.IncidentNumber,
    i.Title AS IncidentTitle,
    i.Severity,
    i.CreatedDate AS IncidentDate,
    t.TimelineID,
    ct.ChangeTypeName,
    ct.ChangeCategory,
    t.ChangeDateTime,
    t.ChangeDescription,
    t.ChangedBy,
    DATEDIFF(MINUTE, t.ChangeDateTime, i.CreatedDate) AS MinutesBeforeIncident
FROM correlation.Incidents i
JOIN change.UnifiedTimeline t ON t.ServerName = i.PrimaryServerName
    AND t.ChangeDateTime BETWEEN DATEADD(HOUR, -24, i.CreatedDate) AND i.CreatedDate
JOIN change.ChangeTypes ct ON t.ChangeTypeID = ct.ChangeTypeID
WHERE i.CreatedDate >= DATEADD(DAY, -30, GETDATE());

GO

-- [change].[vw_ChangesByCategory]

-- =============================================================================
-- VIEW: change.vw_ChangesByCategory
-- Summary by category for dashboard
-- =============================================================================
CREATE   VIEW change.vw_ChangesByCategory
AS
SELECT 
    ct.ChangeCategory,
    ct.Color,
    COUNT(*) AS TotalChanges,
    SUM(CASE WHEN t.ChangeDateTime >= DATEADD(DAY, -1, GETDATE()) THEN 1 ELSE 0 END) AS Last24Hours,
    SUM(CASE WHEN t.ChangeDateTime >= DATEADD(DAY, -7, GETDATE()) THEN 1 ELSE 0 END) AS Last7Days,
    SUM(CASE WHEN t.ImpactLevel = 3 THEN 1 ELSE 0 END) AS HighImpactChanges
FROM change.UnifiedTimeline t
JOIN change.ChangeTypes ct ON t.ChangeTypeID = ct.ChangeTypeID
WHERE t.ChangeDateTime >= DATEADD(DAY, -30, GETDATE())
GROUP BY ct.ChangeCategory, ct.Color;

GO

-- [change].[vw_RecentChanges]

-- =============================================================================
-- VIEW: change.vw_RecentChanges
-- Dashboard view of recent changes with CMS enrichment
-- =============================================================================
CREATE   VIEW change.vw_RecentChanges
AS
SELECT TOP 500
    t.TimelineID,
    ct.ChangeTypeName,
    ct.ChangeCategory,
    ct.Icon,
    ct.Color,
    t.ServerName,
    t.CMSGroupName,
    t.CMSCriticality,
    t.DatabaseName,
    t.ChangeDateTime,
    t.ChangeDescription,
    t.ChangedBy,
    t.SourceSystem,
    t.ImpactLevel,
    CASE t.ImpactLevel WHEN 1 THEN 'Low' WHEN 2 THEN 'Medium' WHEN 3 THEN 'High' END AS ImpactName,
    t.IsPlanned,
    DATEDIFF(HOUR, t.ChangeDateTime, GETDATE()) AS HoursAgo
FROM change.UnifiedTimeline t
JOIN change.ChangeTypes ct ON t.ChangeTypeID = ct.ChangeTypeID
WHERE t.ChangeDateTime >= DATEADD(DAY, -30, GETDATE())
ORDER BY t.ChangeDateTime DESC;

GO

-- [compliance].[vw_ArchitectureFindings]

-- ============================================================================
-- View: Compliance.vw_ArchitectureFindings
-- ============================================================================
CREATE   VIEW Compliance.vw_ArchitectureFindings
AS
SELECT 
    FindingID,
    ServerName,
    DatabaseName,
    FindingType,
    Severity,
    Description,
    Impact,
    Recommendation,
    FoundDate,
    Status
FROM Compliance.ArchitectureFindings
WHERE Status = 'Open';

GO

-- [compliance].[vw_ComplianceScoreSummary]

-- ============================================================================
-- View: Compliance.vw_ComplianceScoreSummary
-- ============================================================================
CREATE   VIEW Compliance.vw_ComplianceScoreSummary
AS
SELECT 
    FrameworkName,
    COUNT(*) AS ServerCount,
    AVG(OverallScore) AS AvgScore,
    SUM(TotalControls) AS TotalControls,
    SUM(PassedControls) AS PassedControls,
    SUM(FailedControls) AS FailedControls,
    SUM(AcceptedRisks) AS AcceptedRisks,
    MAX(LastAssessment) AS LastAssessment
FROM Compliance.ComplianceScores
GROUP BY FrameworkName;

GO

-- [compliance].[vw_CurrentStatus]

-- =============================================================================
-- VIEW: compliance.vw_CurrentStatus
-- Real-time compliance dashboard by framework
-- =============================================================================
CREATE   VIEW compliance.vw_CurrentStatus
AS
WITH LatestEvidence AS (
    SELECT 
        Framework, ControlID, ServerName, CMSGroupName, ComplianceStatus,
        CollectionDateTime, Findings,
        ROW_NUMBER() OVER (PARTITION BY Framework, ControlID, ServerName ORDER BY CollectionDateTime DESC) AS rn
    FROM compliance.EvidenceStore
    WHERE IsArchived = 0 AND ValidUntil >= GETDATE()
)
SELECT 
    Framework,
    COUNT(DISTINCT ControlID) AS TotalControls,
    COUNT(DISTINCT ServerName) AS TotalServers,
    SUM(CASE WHEN ComplianceStatus = 'Pass' THEN 1 ELSE 0 END) AS Passing,
    SUM(CASE WHEN ComplianceStatus = 'Fail' THEN 1 ELSE 0 END) AS Failing,
    SUM(CASE WHEN ComplianceStatus = 'Warning' THEN 1 ELSE 0 END) AS Warning,
    SUM(CASE WHEN ComplianceStatus = 'Exception' THEN 1 ELSE 0 END) AS Exception,
    CAST(SUM(CASE WHEN ComplianceStatus = 'Pass' THEN 1.0 ELSE 0 END) / 
         NULLIF(COUNT(*), 0) * 100 AS DECIMAL(5,2)) AS ComplianceScore,
    MAX(CollectionDateTime) AS LastEvaluated
FROM LatestEvidence
WHERE rn = 1
GROUP BY Framework;

GO

-- [compliance].[vw_ExpiringEvidence]

-- =============================================================================
-- VIEW: compliance.vw_ExpiringEvidence
-- Evidence expiring soon (needs re-collection)
-- =============================================================================
CREATE   VIEW compliance.vw_ExpiringEvidence
AS
SELECT 
    Framework,
    ControlID,
    ControlName,
    ServerName,
    CMSGroupName,
    CollectionDateTime,
    ValidUntil,
    DATEDIFF(DAY, GETDATE(), ValidUntil) AS DaysUntilExpiry,
    ComplianceStatus
FROM compliance.EvidenceStore
WHERE IsArchived = 0
  AND ValidUntil BETWEEN GETDATE() AND DATEADD(DAY, 14, GETDATE());

GO

-- [compliance].[vw_OpenFindings]

-- ============================================================================
-- View: Compliance.vw_OpenFindings
-- ============================================================================
CREATE   VIEW Compliance.vw_OpenFindings
AS
SELECT 
    FrameworkName,
    ControlID,
    ControlName,
    Severity,
    COUNT(DISTINCT ServerName) AS AffectedServers,
    MAX(Recommendation) AS Recommendation
FROM Compliance.ComplianceFindings
WHERE Status = 'Open'
GROUP BY FrameworkName, ControlID, ControlName, Severity;

GO

-- [compliance].[vw_SecurityMetrics]

-- ============================================================================
-- View: Compliance.vw_SecurityMetrics
-- ============================================================================
CREATE   VIEW Compliance.vw_SecurityMetrics
AS
SELECT 
    COALESCE(SUM(PassedControls), 0) AS PassedControls,
    COALESCE(SUM(FailedControls), 0) AS FailedControls,
    COALESCE(SUM(AcceptedRisks), 0) AS AcceptedRisks,
    COUNT(DISTINCT ServerName) AS ServersAssessed,
    CASE 
        WHEN SUM(TotalControls) > 0 
        THEN CAST(SUM(PassedControls) * 100.0 / SUM(TotalControls) AS DECIMAL(5,2))
        ELSE 0 
    END AS OverallCompliancePercent
FROM Compliance.ComplianceScores;

GO

-- [compliance].[vw_ServerComplianceSummary]

-- ============================================================================
-- View: Compliance.vw_ServerComplianceSummary
-- ============================================================================
CREATE   VIEW Compliance.vw_ServerComplianceSummary
AS
SELECT 
    si.ServerName,
    si.Environment,
    COALESCE(scd.ComplianceTier, si.ComplianceTier) AS ComplianceTier,
    scd.CJISScore,
    scd.NISTScore,
    scd.STIGScore,
    scd.DISAScore,
    COALESCE(scd.OpenFindings, 0) AS OpenFindings,
    scd.LastAssessment
FROM config.ServerInventory si
LEFT JOIN Compliance.ServerComplianceDetail scd ON si.ServerName = scd.ServerName
WHERE si.IsActive = 1;

GO

-- [compliance].[vw_StatusByCMSGroup]

-- =============================================================================
-- VIEW: compliance.vw_StatusByCMSGroup
-- Compliance by CMS group (for group-level reporting)
-- =============================================================================
CREATE   VIEW compliance.vw_StatusByCMSGroup
AS
WITH LatestEvidence AS (
    SELECT 
        Framework, CMSGroupName, CMSCriticality, ComplianceStatus,
        ROW_NUMBER() OVER (PARTITION BY Framework, CMSGroupName, ControlID ORDER BY CollectionDateTime DESC) AS rn
    FROM compliance.EvidenceStore
    WHERE IsArchived = 0 AND ValidUntil >= GETDATE()
)
SELECT 
    Framework,
    CMSGroupName,
    CMSCriticality,
    COUNT(*) AS TotalEvaluations,
    SUM(CASE WHEN ComplianceStatus = 'Pass' THEN 1 ELSE 0 END) AS Passing,
    SUM(CASE WHEN ComplianceStatus = 'Fail' THEN 1 ELSE 0 END) AS Failing,
    CAST(SUM(CASE WHEN ComplianceStatus = 'Pass' THEN 1.0 ELSE 0 END) / 
         NULLIF(COUNT(*), 0) * 100 AS DECIMAL(5,2)) AS ComplianceScore
FROM LatestEvidence
WHERE rn = 1
GROUP BY Framework, CMSGroupName, CMSCriticality;

GO

-- [config].[MonitoredServers]

-- Create the view pointing to inventory.MonitoredServers
CREATE VIEW config.MonitoredServers AS
SELECT ServerName, IsActive
FROM inventory.MonitoredServers;

GO

-- [config].[vw_CMS_ServerInventory_Current]

CREATE   VIEW config.vw_CMS_ServerInventory_Current
AS
WITH d AS
(
    SELECT
        si.*,
        ROW_NUMBER() OVER
        (
            PARTITION BY si.ServerName
            ORDER BY
                ISNULL(si.IsActive, 0) DESC,
                CASE WHEN si.SQLVersion IS NOT NULL AND LTRIM(RTRIM(si.SQLVersion)) <> '' THEN 1 ELSE 0 END DESC,
                ISNULL(si.LastInventoryUpdate, '19000101') DESC,
                ISNULL(si.ModifiedDate, '19000101') DESC,
                ISNULL(si.CMSLastSyncDate, '19000101') DESC,
                si.ServerID DESC
        ) AS rn
    FROM config.ServerInventory si
)
SELECT *
FROM d
WHERE rn = 1;

GO

-- [config].[vw_CMS_SQLServerInventory_Current]

CREATE   VIEW config.vw_CMS_SQLServerInventory_Current
AS
SELECT *
FROM config.vw_CMS_ServerInventory_Current
WHERE SQLVersion IS NOT NULL
  AND LTRIM(RTRIM(SQLVersion)) <> '';

GO

-- [config].[vw_CMS_SQLServerTargets]
CREATE OR ALTER VIEW config.vw_CMS_SQLServerTargets
AS
SELECT
    ServerName,
    InstanceName,
    Environment,
    SQLVersion,
    SQLEdition,
    FQDN,
    SourceSystem,
    LastInventoryUpdate,
    SqlConnectionName =
        CASE
            WHEN InstanceName IS NULL OR LTRIM(RTRIM(InstanceName)) = '' OR InstanceName = 'MSSQLSERVER'
                THEN ServerName
            ELSE ServerName + N'\' + InstanceName
        END
FROM config.vw_CMS_SQLServerInventory_Current;

GO

-- [config].[vw_CMS_SQLServerTargets_Final]

CREATE   VIEW config.vw_CMS_SQLServerTargets_Final
AS
SELECT
    cShows.*,
    SqlConnectionName_Final =
        CASE
            WHEN o.InstanceName IS NOT NULL AND LTRIM(RTRIM(o.InstanceName)) <> ''
                THEN cShows.ServerName + N'\' + o.InstanceName
            ELSE cShows.SqlConnectionName
        END
FROM config.vw_CMS_SQLServerTargets cShows
LEFT JOIN config.SqlInstanceOverrides o
    ON o.ServerName = cShows.ServerName;

GO

-- [config].[vw_GovernmentCompliance]

-- View for active compliance frameworks
CREATE   VIEW config.vw_GovernmentCompliance
AS
SELECT 
    ComplianceID,
    ComplianceName,
    ComplianceCode,
    Description,
    AppliesTo,
    AuthorizationLevel,
    SortOrder
FROM config.GovernmentCompliance
WHERE IsActive = 1;

GO

-- [config].[vw_MonitoredServers]

CREATE VIEW config.vw_MonitoredServers
AS
/*
    CMS-Based Monitored Servers View
    Source of Truth: Central Management Server (CMS)
    
    Usage: SELECT * FROM config.vw_MonitoredServers
    
    This replaces direct queries to config.ServerInventory for getting
    the list of servers to monitor/collect/report on.
*/
SELECT DISTINCT
    cms.server_name AS ServerName,
    cms.server_group_id AS GroupId,
    g.name AS GroupName,
    1 AS IsActive,
    GETDATE() AS LastVerified
FROM msdb.dbo.sysmanagement_shared_registered_servers_internal cms
LEFT JOIN msdb.dbo.sysmanagement_shared_server_groups_internal g
    ON cms.server_group_id = g.server_group_id;

GO

-- [config].[vw_ServerInventory_Current]

CREATE   VIEW config.vw_ServerInventory_Current
AS
WITH ranked AS
(
    SELECT
        s.*,
        rn = ROW_NUMBER() OVER
        (
            PARTITION BY
                UPPER(LTRIM(RTRIM(s.ServerName))),
                UPPER(LTRIM(RTRIM(ISNULL(s.InstanceName,''))))
            ORDER BY
                CASE WHEN UPPER(ISNULL(s.SourceSystem,'')) = 'CMS' THEN 0 ELSE 1 END,
                ISNULL(s.IsActive,0) DESC,
                ISNULL(s.LastInventoryUpdate, s.ModifiedDate) DESC,
                ISNULL(s.ModifiedDate, s.CreatedDate) DESC
        )
    FROM config.ServerInventory s
)
SELECT *
FROM ranked
WHERE rn = 1;

GO

-- [config].[vw_ServerInventoryWithRoles]

CREATE VIEW config.vw_ServerInventoryWithRoles
AS
/*
PURPOSE: Adds role information to existing ServerInventory
USAGE: SELECT * FROM config.vw_ServerInventoryWithRoles WHERE PrimaryRole = 'Business Intelligence'
*/
SELECT 
    -- All existing ServerInventory columns
    inv.ServerID,
    inv.ServerName,
    inv.InstanceName,
    inv.Environment,
    inv.IPAddress,
    inv.OSVersion,
    inv.SQLVersion,
    inv.SQLEdition,
    inv.CPUCount,
    inv.MaxMemoryMB,
    inv.TotalMemoryMB,
    inv.TotalMemoryGB,
    inv.LastPatchDate,
    inv.LastSeen,
    inv.IsActive,
    inv.MaxDOP,
    inv.CostThreshold,
    inv.FQDN,
    inv.SqlBuild,
    inv.SqlCumulativeUpdate,
    inv.WindowsVersion,
    inv.WindowsBuild,
    inv.AppCategory,
    inv.IsVM,
    inv.IsClustered,
    inv.Notes,
    inv.SourceSystem,
    inv.CreatedDate,
    inv.HasSSISDB,
    inv.HasSSRS,
    inv.ModifiedDate,
    inv.LastInventoryUpdate,
    
    -- Add role information (NEW)
    primary_role.ServerRole AS PrimaryRole,
    secondary_roles.SecondaryRoles,
    ISNULL(role_count.RoleCount, 0) AS RoleCount,
    CASE WHEN role_count.RoleCount > 1 THEN 1 ELSE 0 END AS HasMultipleRoles,
    
    -- Add ETL flag
    CASE WHEN etl.ServerName IS NOT NULL THEN 1 ELSE 0 END AS IsETLServer,
    
    -- Add CMS flag
    CASE WHEN cms.server_name IS NOT NULL THEN 1 ELSE 0 END AS IsInCMS

FROM config.ServerInventory inv

-- Primary role
LEFT JOIN config.ServerRoleMapping primary_role
    ON inv.ServerName = primary_role.ServerName
    AND primary_role.IsPrimaryRole = 1
    AND primary_role.IsActive = 1

-- Secondary roles
LEFT JOIN (
    SELECT 
        ServerName,
        SecondaryRoles = STRING_AGG(ServerRole, ', ') WITHIN GROUP (ORDER BY ServerRole)
    FROM config.ServerRoleMapping
    WHERE IsPrimaryRole = 0 AND IsActive = 1
    GROUP BY ServerName
) secondary_roles ON inv.ServerName = secondary_roles.ServerName

-- Role count
LEFT JOIN (
    SELECT ServerName, RoleCount = COUNT(*)
    FROM config.ServerRoleMapping
    WHERE IsActive = 1
    GROUP BY ServerName
) role_count ON inv.ServerName = role_count.ServerName

-- ETL servers
LEFT JOIN etl.dim_ETL_Servers etl ON inv.ServerName = etl.ServerName

-- CMS
LEFT JOIN msdb.dbo.sysmanagement_shared_registered_servers cms
    ON inv.ServerName = cms.server_name

WHERE inv.IsActive = 1;

GO

-- [config].[vw_ServerRoleSummary]

CREATE VIEW config.vw_ServerRoleSummary
AS
/*
PURPOSE: Summary of servers by role (for dashboard KPIs)
USAGE: SELECT * FROM config.vw_ServerRoleSummary ORDER BY TotalServers DESC
*/
SELECT 
    ServerRole = ISNULL(srm.ServerRole, 'Unassigned'),
    TotalServers = COUNT(DISTINCT inv.ServerName),
    ProductionServers = SUM(CASE WHEN inv.Environment = 'Production' THEN 1 ELSE 0 END),
    NonProdServers = SUM(CASE WHEN inv.Environment IN ('Development', 'Test', 'UAT') THEN 1 ELSE 0 END),
    WithSSIS = SUM(CASE WHEN inv.HasSSISDB = 1 THEN 1 ELSE 0 END),
    WithSSRS = SUM(CASE WHEN inv.HasSSRS = 1 THEN 1 ELSE 0 END),
    IsVirtual = SUM(CASE WHEN inv.IsVM = 1 THEN 1 ELSE 0 END),
    IsClustered = SUM(CASE WHEN inv.IsClustered = 1 THEN 1 ELSE 0 END),
    AvgMemoryGB = AVG(inv.TotalMemoryGB),
    AvgCPUCount = AVG(inv.CPUCount)
FROM config.ServerInventory inv
LEFT JOIN config.ServerRoleMapping srm 
    ON inv.ServerName = srm.ServerName 
    AND srm.IsPrimaryRole = 1
    AND srm.IsActive = 1
WHERE inv.IsActive = 1
GROUP BY srm.ServerRole;

GO

-- [config].[vw_ServersICanSee]

CREATE VIEW config.vw_ServersICanSee
AS
/*
    Returns only servers the current user has permission to access
    Using INTERNAL domain (not YOURORG)
*/
SELECT 
    si.ServerID,
    si.ServerName,
    si.InstanceName,
    si.Environment,
    si.IsActive
FROM config.ServerInventory si
WHERE 
    -- Admins see everything
    IS_MEMBER('INTERNAL\DBAOps_Admins') = 1
    OR IS_MEMBER('INTERNAL\DBAOpsAdmin AD') = 1
    OR IS_MEMBER('INTERNAL\Domain Admins') = 1
    -- Auditors see everything (read-only)
    OR IS_MEMBER('INTERNAL\DBAOps_Auditors') = 1
    OR IS_MEMBER('INTERNAL\DBAOps_ReadOnly') = 1
    -- Operators see Production and Test
    OR (IS_MEMBER('INTERNAL\DBAOps_Operators') = 1 
        AND si.Environment IN ('Production', 'Test', 'QA'))
    -- Developers see only Dev and Test
    OR (IS_MEMBER('INTERNAL\DBAOps_Developers') = 1 
        AND si.Environment IN ('Development', 'Test', 'QA'));

GO

-- [control].[vw_CurrentThrottleStatus]

CREATE   VIEW control.vw_CurrentThrottleStatus
AS
SELECT 
    e.Server,
    e.ProcessName,
    e.ProcessDisplayName,
    e.CurrentState,
    e.LastStateChange,
    e.LastEvaluationTime,
    e.ThrottleWhenSQLCPUAbove AS ThrottleThreshold,
    e.RestoreWhenSQLCPUBelow AS RestoreThreshold,
    e.ConsecutiveThrottleCount,
    DATEDIFF(MINUTE, e.LastStateChange, GETDATE()) AS MinutesInCurrentState,
    e.ThrottleEnabled,
    ds.Environment,
    ds.SqlEdition,
    ds.TotalMemoryGB,
    ds.CpuCount
FROM control.ExternalProcessConfig e
INNER JOIN dim.DimServer ds ON e.ServerKey = ds.ServerKey;

GO

-- [control].[vw_ServerInventory]

CREATE   VIEW control.vw_ServerInventory
AS
SELECT 
    ServerName AS Server,
    InstanceName,
    COALESCE(Environment, CMSEnvironment, 'Unknown') AS Environment,
    SQLVersion,
    SQLEdition,
    OSVersion,
    CPUCount,
    TotalMemoryGB,
    IsActive,
    ComplianceTier,
    CMSGroupName
FROM config.ServerInventory;

GO

-- [control].[vw_ThrottleSummaryByServer]

CREATE   VIEW control.vw_ThrottleSummaryByServer
AS
SELECT 
    e.Server,
    ds.Environment,
    COUNT(*) AS TotalProcesses,
    SUM(CASE WHEN e.CurrentState = 'Throttled' THEN 1 ELSE 0 END) AS ThrottledProcesses,
    SUM(CASE WHEN e.CurrentState = 'Normal' THEN 1 ELSE 0 END) AS NormalProcesses,
    MAX(e.LastStateChange) AS LastStateChange
FROM control.ExternalProcessConfig e
INNER JOIN dim.DimServer ds ON e.ServerKey = ds.ServerKey
WHERE e.ThrottleEnabled = 1
GROUP BY e.Server, ds.Environment;

GO

-- [correlation].[vw_ActiveIncidents]

CREATE VIEW correlation.vw_ActiveIncidents
AS
SELECT 
    i.IncidentID,
    i.IncidentNumber,
    i.Title,
    i.Description,
    i.Severity,
    i.Status,
    i.PrimaryServerName,
    i.AffectedServers,
    i.CMSGroupName,
    i.Environment,
    i.AlertCount,
    i.CorrelationRuleID,
    i.RootCauseAlertID,
    i.CreatedDate,
    i.AcknowledgedDate,
    i.AcknowledgedBy,
    i.PlaybookID,
    i.TrackITTicket,
    -- Calculated fields for escalation logic
    DATEDIFF(MINUTE, i.CreatedDate, GETDATE()) AS AgeMinutes,
    DATEDIFF(HOUR, i.CreatedDate, GETDATE()) AS AgeHours,
    DATEDIFF(MINUTE, i.AcknowledgedDate, GETDATE()) AS MinutesSinceAck,
    -- Escalation info
    ep.EscalationLevel,
    ep.EscalationMinutes,
    ep.NotificationChannel,
    ep.NotificationTarget,
    -- Correlation rule info
    cr.RuleName AS CorrelationRuleName
FROM correlation.Incidents i
LEFT JOIN correlation.EscalationPaths ep 
    ON (i.CMSGroupName LIKE ep.CMSGroupPattern OR ep.CMSGroupPattern = '%')
    AND i.Severity = ep.Severity
    AND ep.IsEnabled = 1
LEFT JOIN correlation.CorrelationRules cr 
    ON i.CorrelationRuleID = cr.RuleID
WHERE i.Status IN ('Open', 'Acknowledged', 'InProgress');

GO

-- [correlation].[vw_IncidentsByCMSGroup]

-- =============================================================================
-- VIEW: correlation.vw_IncidentsByCMSGroup
-- =============================================================================
CREATE   VIEW correlation.vw_IncidentsByCMSGroup
AS
SELECT 
    ISNULL(i.CMSGroupName, 'UNKNOWN') AS CMSGroup,
    COUNT(*) AS TotalIncidents,
    SUM(CASE WHEN i.Status = 'Open' THEN 1 ELSE 0 END) AS OpenIncidents,
    SUM(CASE WHEN i.Severity = 1 THEN 1 ELSE 0 END) AS CriticalCount,
    SUM(CASE WHEN i.Severity = 2 THEN 1 ELSE 0 END) AS HighCount,
    AVG(i.MTTR_Minutes) AS AvgMTTR
FROM correlation.Incidents i
WHERE i.CreatedDate >= DATEADD(DAY, -30, GETDATE())
GROUP BY i.CMSGroupName;

GO

-- [ctl].[vw_JobHistoryDailyStats]
CREATE OR ALTER VIEW ctl.vw_JobHistoryDailyStats AS
SELECT
    JobName,
    CAST(RunDate AS date) AS RunDay,
    COUNT(*) AS TotalRuns,
    AVG(DurationSeconds) AS AvgDurationSec,
    MAX(DurationSeconds) AS MaxDurationSec,
    MIN(DurationSeconds) AS MinDurationSec
FROM ctl.JobHistory
GROUP BY JobName, CAST(RunDate AS date);

GO

-- [ctl].[vw_JobHistoryLatest]
CREATE OR ALTER VIEW ctl.vw_JobHistoryLatest AS
SELECT *
FROM (
    SELECT 
        JobName,
        RunDate,
        RunStatus,
        Duration,
        ROW_NUMBER() OVER (PARTITION BY JobName ORDER BY RunDate DESC) AS rn
    FROM ctl.JobHistory
) a
WHERE rn = 1;

GO

-- [dashboard].[vw_ActiveCriticalAlerts]

CREATE VIEW dashboard.vw_ActiveCriticalAlerts
AS
SELECT 
    a.AlertID,
    a.ServerName,
    a.AlertType,
    a.AlertMessage,
    a.Severity,
    a.AlertTime,
    a.Processed,
    a.ProcessedBy,
    a.ProcessedDate,
    DATEDIFF(MINUTE, a.AlertTime, GETDATE()) AS MinutesSinceAlert,
    CASE 
        WHEN a.Severity = 1 AND a.Processed = 0 THEN 'BLINK_RED' -- Critical unprocessed
        WHEN a.Severity = 1 AND a.Processed = 1 THEN 'SOLID_RED' -- Critical processed
        WHEN a.Severity = 2 THEN 'SOLID_ORANGE' -- Warning
        WHEN a.Severity = 3 THEN 'SOLID_YELLOW' -- Info
        ELSE 'SOLID_GRAY' -- Unknown
    END AS DisplayStatus
FROM dbo.fact_Alerts a
WHERE a.AlertTime >= DATEADD(HOUR, -24, GETDATE()) -- Last 24 hours
    AND (a.Processed = 0 OR a.ProcessedDate >= DATEADD(HOUR, -4, GETDATE())) -- Unprocessed OR recently processed
;

GO

-- [dashboard].[vw_AlertStatistics]

-- View: Alert Statistics
CREATE   VIEW dashboard.vw_AlertStatistics AS
SELECT 
    Channel,
    Priority,
    Status,
    COUNT(*) AS AlertCount,
    MAX(SentAt) AS LastAlert
FROM alerts.NotificationLog
WHERE SentAt >= DATEADD(DAY, -30, GETDATE())
GROUP BY Channel, Priority, Status;

GO

-- [dashboard].[vw_AlertSummary]

CREATE VIEW dashboard.vw_AlertSummary
AS
SELECT 
    COUNT(*) AS TotalAlerts,
    SUM(CASE WHEN Severity = 'Critical' THEN 1 ELSE 0 END) AS CriticalCount,
    SUM(CASE WHEN Severity = 'High' THEN 1 ELSE 0 END) AS HighCount,
    SUM(CASE WHEN Severity = 'Medium' THEN 1 ELSE 0 END) AS MediumCount,
    SUM(CASE WHEN Severity = 'Low' THEN 1 ELSE 0 END) AS LowCount,
    SUM(CASE WHEN Severity = 'Info' THEN 1 ELSE 0 END) AS InfoCount,
    SUM(CASE WHEN IsResolved = 0 THEN 1 ELSE 0 END) AS UnresolvedCount,
    MAX(AlertTime) AS LatestAlert
FROM alerts.RecentAlerts
WHERE AlertTime >= DATEADD(DAY, -1, GETDATE());

GO

-- [dashboard].[vw_AppWebservers]

CREATE VIEW dashboard.vw_AppWebservers
AS
SELECT 
    ServerName,
    Environment,
    AppCategory,
    ServerType,
    LastSeen,
    CMSGroupName,
    Notes,
    CASE 
        WHEN LastSeen >= DATEADD(HOUR, -24, GETDATE()) THEN 'Online'
        WHEN LastSeen >= DATEADD(DAY, -7, GETDATE()) THEN 'Warning'
        ELSE 'Offline'
    END AS Status
FROM config.ServerInventory
WHERE IsActive = 1
AND ServerType = 'App Webserver';

GO

-- [dashboard].[vw_CapacityForecast]

CREATE VIEW dashboard.vw_CapacityForecast
AS
SELECT 
    sf.ServerName,
    sf.DriveLetter,
    sf.CurrentSizeGB,
    sf.CurrentUsedGB,
    sf.CurrentSizeGB - sf.CurrentUsedGB AS FreeGB,
    CAST((sf.CurrentUsedGB / sf.CurrentSizeGB) * 100 AS DECIMAL(5,2)) AS UsedPercent,
    sf.GrowthRateGBPerDay,
    sf.PredictedFullDate,
    DATEDIFF(DAY, GETDATE(), sf.PredictedFullDate) AS DaysUntilFull,
    CASE 
        WHEN DATEDIFF(DAY, GETDATE(), sf.PredictedFullDate) < 30 THEN 'CRITICAL'
        WHEN DATEDIFF(DAY, GETDATE(), sf.PredictedFullDate) < 90 THEN 'WARNING'
        ELSE 'OK'
    END AS ForecastStatus,
    sf.ForecastDate AS LastForecast
FROM capacity.StorageForecasts sf
WHERE sf.ForecastDate = (SELECT MAX(ForecastDate) FROM capacity.StorageForecasts sf2 WHERE sf2.ServerName = sf.ServerName AND sf2.DriveLetter = sf.DriveLetter);

GO

-- [dashboard].[vw_ChangeTimeline24h]

-- =============================================================================
-- VIEW: dashboard.vw_ChangeTimeline24h
-- For dashboard recent changes widget
-- =============================================================================
CREATE   VIEW dashboard.vw_ChangeTimeline24h
AS
SELECT TOP 50
    t.TimelineID,
    ct.ChangeTypeName,
    ct.ChangeCategory,
    ct.Icon,
    ct.Color,
    t.ServerName,
    t.CMSGroupName,
    t.DatabaseName,
    t.ChangeDateTime,
    t.ChangeDescription,
    t.ChangedBy,
    t.ImpactLevel,
    DATEDIFF(MINUTE, t.ChangeDateTime, GETDATE()) AS MinutesAgo
FROM change.UnifiedTimeline t
JOIN change.ChangeTypes ct ON t.ChangeTypeID = ct.ChangeTypeID
WHERE t.ChangeDateTime >= DATEADD(HOUR, -24, GETDATE())
ORDER BY t.ChangeDateTime DESC;

GO

-- [dashboard].[vw_ComplianceByServer]
-- Fix the view to show most recent assessment, not just today
CREATE VIEW dashboard.vw_ComplianceByServer
AS
SELECT 
    r.ServerName,
    r.FrameworkCode,
    COUNT(*) AS TotalControls,
    SUM(CASE WHEN r.ControlStatus = 'PASS' THEN 1 ELSE 0 END) AS PassedControls,
    SUM(CASE WHEN r.ControlStatus = 'FAIL' THEN 1 ELSE 0 END) AS FailedControls,
    CAST(SUM(CASE WHEN r.ControlStatus = 'PASS' THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(*), 0) AS DECIMAL(5,2)) AS ComplianceScore,
    r.AssessmentDate
FROM Assessment.ComplianceControlResults r
WHERE r.AssessmentDate = (SELECT MAX(AssessmentDate) FROM Assessment.ComplianceControlResults)
GROUP BY r.ServerName, r.FrameworkCode, r.AssessmentDate;

GO

-- [dashboard].[vw_ComplianceFindings]

-- Create view for compliance findings (failures)
CREATE   VIEW dashboard.vw_ComplianceFindings
AS
SELECT 
    r.ServerName,
    r.FrameworkCode,
    r.ControlCode,
    c.ControlName,
    c.Severity,
    r.ControlStatus,
    r.Finding,
    r.AssessmentDate
FROM Assessment.ComplianceControlResults r
LEFT JOIN Assessment.ComplianceControls c ON r.ControlCode = c.ControlCode
WHERE r.ControlStatus = 'FAIL'
  AND CAST(r.AssessmentDate AS DATE) = CAST(GETDATE() AS DATE);

GO

-- [dashboard].[vw_ComplianceForDashboard]
-- Create a view that the dashboard can consume
CREATE   VIEW dashboard.vw_ComplianceForDashboard
AS
SELECT 
    h.FrameworkCode,
    f.FrameworkName,
    h.ComplianceScore,
    h.ComplianceGrade,
    h.TotalControls,
    h.PassedControls,
    h.FailedControls,
    h.ScoreChange,
    CASE 
        WHEN h.ScoreChange > 0 THEN 'up'
        WHEN h.ScoreChange < 0 THEN 'down'
        ELSE 'stable'
    END AS Trend,
    h.AssessmentDate,
    -- Server breakdown
    (SELECT COUNT(DISTINCT ServerName) FROM Assessment.ComplianceControlResults r 
     WHERE r.FrameworkCode = h.FrameworkCode 
       AND CAST(r.AssessmentDate AS DATE) = CAST(h.AssessmentDate AS DATE)) AS ServersAssessed,
    (SELECT COUNT(DISTINCT ServerName) FROM Assessment.ComplianceControlResults r 
     WHERE r.FrameworkCode = h.FrameworkCode 
       AND r.ControlStatus = 'FAIL'
       AND CAST(r.AssessmentDate AS DATE) = CAST(h.AssessmentDate AS DATE)) AS ServersWithFailures
FROM Assessment.ComplianceScoreHistory h
JOIN Assessment.ComplianceFrameworks f ON h.FrameworkCode = f.FrameworkCode
WHERE h.AssessmentDate = (
    SELECT MAX(AssessmentDate) 
    FROM Assessment.ComplianceScoreHistory h2 
    WHERE h2.FrameworkCode = h.FrameworkCode
);

GO

-- [dashboard].[vw_ComplianceScorecard]

-- 10. Create Dashboard View for Compliance Scores
CREATE   VIEW dashboard.vw_ComplianceScorecard
AS
SELECT 
    h.FrameworkCode,
    f.FrameworkName,
    h.ComplianceScore,
    h.ComplianceGrade,
    h.TotalControls,
    h.PassedControls,
    h.FailedControls,
    h.ScoreChange,
    CASE 
        WHEN h.ScoreChange > 0 THEN 'IMPROVING'
        WHEN h.ScoreChange < 0 THEN 'DEGRADING'
        ELSE 'STABLE'
    END AS Trend,
    CASE 
        WHEN h.ComplianceScore >= 95 THEN '#28a745'
        WHEN h.ComplianceScore >= 85 THEN '#9acd32'
        WHEN h.ComplianceScore >= 75 THEN '#ffc107'
        WHEN h.ComplianceScore >= 65 THEN '#fd7e14'
        ELSE '#dc3545'
    END AS ScoreColor,
    h.AssessmentDate
FROM Assessment.ComplianceScoreHistory h
JOIN Assessment.ComplianceFrameworks f ON h.FrameworkCode = f.FrameworkCode
WHERE h.AssessmentDate = (
    SELECT MAX(AssessmentDate) 
    FROM Assessment.ComplianceScoreHistory h2 
    WHERE h2.FrameworkCode = h.FrameworkCode
);

GO

-- [dashboard].[vw_ComplianceSummary]

-- =============================================================================
-- VIEW: dashboard.vw_ComplianceSummary
-- For dashboard compliance widget
-- =============================================================================
CREATE   VIEW dashboard.vw_ComplianceSummary
AS
SELECT 
    Framework,
    TotalControls,
    Passing,
    Failing,
    Warning,
    Exception,
    ComplianceScore,
    CASE 
        WHEN ComplianceScore >= 95 THEN '#10b981'
        WHEN ComplianceScore >= 80 THEN '#f59e0b'
        ELSE '#ef4444'
    END AS ScoreColor,
    LastEvaluated
FROM compliance.vw_CurrentStatus;

GO

-- [dashboard].[vw_CurrentOnCall]

-- View: Current On-Call
CREATE   VIEW dashboard.vw_CurrentOnCall AS
SELECT TOP 1
    PrimaryDBA,
    PrimaryPhone,
    PrimaryEmail,
    BackupDBA,
    BackupPhone,
    BackupEmail,
    RotationStartDate,
    RotationEndDate,
    DATEDIFF(DAY, GETDATE(), RotationEndDate) AS DaysRemaining
FROM config.OnCallRotation
WHERE IsActive = 1 
AND GETDATE() BETWEEN RotationStartDate AND RotationEndDate;

GO

-- [dashboard].[vw_DashboardMetrics]

-- =============================================================================
-- VIEW: dashboard.vw_DashboardMetrics
-- Combined metrics for single JSON fetch
-- =============================================================================
CREATE   VIEW dashboard.vw_DashboardMetrics
AS
SELECT 
    -- Incident Metrics
    (SELECT COUNT(*) FROM correlation.Incidents WHERE Status NOT IN ('Resolved', 'Closed', 'Archived')) AS Incidents_Open,
    (SELECT COUNT(*) FROM correlation.Incidents WHERE Status NOT IN ('Resolved', 'Closed', 'Archived') AND Severity = 1) AS Incidents_Critical,
    (SELECT COUNT(*) FROM correlation.Incidents WHERE Status NOT IN ('Resolved', 'Closed', 'Archived') AND AcknowledgedBy IS NULL) AS Incidents_Unacknowledged,
    
    -- Change Metrics
    (SELECT COUNT(*) FROM change.UnifiedTimeline WHERE ChangeDateTime >= DATEADD(HOUR, -24, GETDATE())) AS Changes_24h,
    (SELECT COUNT(*) FROM change.UnifiedTimeline WHERE ChangeDateTime >= DATEADD(HOUR, -24, GETDATE()) AND ImpactLevel = 3) AS Changes_HighImpact_24h,
    
    -- Compliance Metrics
    (SELECT AVG(ComplianceScore) FROM compliance.vw_CurrentStatus) AS Compliance_AvgScore,
    (SELECT COUNT(*) FROM compliance.vw_ExpiringEvidence WHERE DaysUntilExpiry <= 7) AS Compliance_ExpiringEvidence,
    
    -- Playbook Metrics
    (SELECT COUNT(*) FROM playbook.Playbooks WHERE IsActive = 1) AS Playbooks_Available,
    (SELECT COUNT(*) FROM playbook.ExecutionHistory WHERE Status = 'Running') AS Playbooks_Running,
    
    -- Server Metrics (from CMS-synced inventory)
    (SELECT COUNT(*) FROM config.ServerInventory WHERE IsActive = 1) AS Servers_Total,
    (SELECT COUNT(*) FROM config.ServerInventory WHERE IsActive = 1 AND CMSCriticality = 'HIGH') AS Servers_HighCriticality,
    
    -- Timestamp
    GETDATE() AS GeneratedAt;

GO

-- [dashboard].[vw_DiskSpaceAlertStatus]

CREATE   VIEW dashboard.vw_DiskSpaceAlertStatus
AS
WITH LatestDisk AS (
    SELECT 
        ServerName,
        DriveLetter,
        VolumeName,
        TotalSizeGB,
        FreeSpaceGB,
        FreeSpacePercent,
        CollectedAt,
        ROW_NUMBER() OVER (PARTITION BY ServerName, DriveLetter ORDER BY CollectedAt DESC) AS rn
    FROM dbo.DiskSpaceInventory
)
SELECT 
    d.ServerName,
    d.DriveLetter,
    d.VolumeName,
    d.TotalSizeGB,
    d.FreeSpaceGB,
    d.FreeSpacePercent,
    d.CollectedAt,
    -- Alert Level
    CASE 
        WHEN d.FreeSpacePercent < 5 THEN 'Critical'
        WHEN d.FreeSpacePercent < 25 THEN 'Warning'
        WHEN d.FreeSpacePercent < 50 THEN 'Caution'
        ELSE 'Healthy'
    END AS AlertLevel,
    -- Color for Dashboard
    CASE 
        WHEN d.FreeSpacePercent < 5 THEN 'Red'
        WHEN d.FreeSpacePercent < 25 THEN 'Orange'
        WHEN d.FreeSpacePercent < 50 THEN 'Yellow'
        ELSE 'Green'
    END AS DashboardColor,
    -- Alert Interval
    CASE 
        WHEN d.FreeSpacePercent < 5 THEN 5
        WHEN d.FreeSpacePercent < 25 THEN 15
        WHEN d.FreeSpacePercent < 50 THEN 30
        ELSE NULL
    END AS AlertIntervalMinutes,
    -- Used Percent for progress bars
    100 - d.FreeSpacePercent AS UsedPercent,
    -- CMS Group
    cms.GroupName AS CMSGroup
FROM LatestDisk d
INNER JOIN config.vw_MonitoredServers cms 
    ON d.ServerName = cms.ServerName AND cms.IsActive = 1
WHERE d.rn = 1;

GO

-- [dashboard].[vw_DiskSpaceMonitor]

CREATE   VIEW dashboard.vw_DiskSpaceMonitor
AS
/*
    Deduplicated Disk Space Monitoring View
    - Returns only ONE row per ServerName/DriveLetter combination
    - Uses most recent CollectedAt timestamp
    - Prioritizes highest-priority CMS group when server is in multiple groups
    - Includes alert levels and colors for dashboard display
*/
WITH LatestDiskData AS (
    SELECT 
        d.DiskSpaceID,
        d.ServerName,
        d.DriveLetter,
        d.VolumeName,
        d.TotalSizeGB,
        d.FreeSpaceGB,
        d.FreeSpacePercent,
        d.CollectedAt,
        ROW_NUMBER() OVER (
            PARTITION BY d.ServerName, d.DriveLetter 
            ORDER BY d.CollectedAt DESC
        ) AS DataRank
    FROM dbo.DiskSpaceInventory d
),
ServerCMSPriority AS (
    SELECT 
        cms.ServerName,
        cms.GroupName,
        ISNULL(p.Priority, 99) AS GroupPriority,
        ROW_NUMBER() OVER (
            PARTITION BY cms.ServerName 
            ORDER BY ISNULL(p.Priority, 99)
        ) AS PriorityRank
    FROM config.vw_MonitoredServers cms
    LEFT JOIN config.CMSGroupPriority p ON cms.GroupName = p.GroupName
    WHERE cms.IsActive = 1
)
SELECT 
    d.DiskSpaceID,
    d.ServerName,
    d.DriveLetter,
    d.VolumeName,
    d.TotalSizeGB,
    d.FreeSpaceGB,
    d.FreeSpacePercent,
    d.CollectedAt,
    -- Alert Level based on free space
    CASE 
        WHEN d.FreeSpacePercent < 5 THEN 'Critical'
        WHEN d.FreeSpacePercent < 25 THEN 'Warning'
        WHEN d.FreeSpacePercent < 50 THEN 'Caution'
        ELSE 'Healthy'
    END AS AlertLevel,
    -- Dashboard Color
    CASE 
        WHEN d.FreeSpacePercent < 5 THEN 'Red'
        WHEN d.FreeSpacePercent < 25 THEN 'Orange'
        WHEN d.FreeSpacePercent < 50 THEN 'Yellow'
        ELSE 'Green'
    END AS DashboardColor,
    -- Alert Interval (minutes)
    CASE 
        WHEN d.FreeSpacePercent < 5 THEN 5
        WHEN d.FreeSpacePercent < 25 THEN 15
        WHEN d.FreeSpacePercent < 50 THEN 30
        ELSE NULL
    END AS AlertIntervalMinutes,
    -- Used Percent for progress bars
    100 - d.FreeSpacePercent AS UsedPercent,
    -- CMS Group (highest priority only)
    cms.GroupName AS CMSGroup,
    cms.GroupPriority
FROM LatestDiskData d
INNER JOIN ServerCMSPriority cms ON d.ServerName = cms.ServerName AND cms.PriorityRank = 1
WHERE d.DataRank = 1;

GO

-- [dashboard].[vw_DiskSpaceSummary]

-- ============================================================================
-- SECTION 4: Disk Space Summary View (for Dashboard)
-- ============================================================================
CREATE   VIEW dashboard.vw_DiskSpaceSummary
AS
SELECT 
    COUNT(DISTINCT ServerName) AS TotalServers,
    COUNT(*) AS TotalDrives,
    SUM(CASE WHEN AlertLevel = 'Critical' THEN 1 ELSE 0 END) AS CriticalCount,
    SUM(CASE WHEN AlertLevel = 'Warning' THEN 1 ELSE 0 END) AS WarningCount,
    SUM(CASE WHEN AlertLevel = 'Caution' THEN 1 ELSE 0 END) AS CautionCount,
    SUM(CASE WHEN AlertLevel = 'Healthy' THEN 1 ELSE 0 END) AS HealthyCount,
    CAST(SUM(TotalSizeGB) AS DECIMAL(18,2)) AS TotalCapacityTB,
    CAST(SUM(FreeSpaceGB) AS DECIMAL(18,2)) AS TotalFreeSpaceTB,
    CAST(AVG(FreeSpacePercent) AS DECIMAL(5,2)) AS AvgFreePercent,
    MIN(FreeSpacePercent) AS MinFreePercent,
    (SELECT TOP 1 ServerName + ' ' + DriveLetter FROM dashboard.vw_DiskSpaceMonitor ORDER BY FreeSpacePercent) AS MostCriticalDrive
FROM dashboard.vw_DiskSpaceMonitor;

GO

-- [dashboard].[vw_DriftAlerts]

-- View: Drift Alerts (corrected column names)
CREATE   VIEW dashboard.vw_DriftAlerts AS
SELECT 
    di.DriftItemId,
    dh.SqlInstance,
    dh.DatabaseName,
    di.DriftType,
    di.ObjectType,
    di.ObjectName,
    di.Severity,
    dh.ScanTime
FROM drift.DriftItems di
JOIN drift.DriftDetectionHistory dh ON di.DriftDetectionId = dh.DriftDetectionId
WHERE di.RemediatedAt IS NULL;

GO

-- [dashboard].[vw_EOS_Alerts]

-- =====================================================================
-- 6. DASHBOARD ALERTS VIEW
-- =====================================================================
CREATE   VIEW [dashboard].[vw_EOS_Alerts]
AS
SELECT TOP 20
    ServerName,
    SQLVersion,
    UrgencyLevel,
    DaysUntilEOS,
    CASE 
        WHEN UrgencyLevel = 'END OF LIFE' THEN 'CRITICAL: ' + ServerName + ' is running ' + ISNULL(SQLVersion, 'Unknown') + ' which is END OF LIFE!'
        WHEN UrgencyLevel = 'CRITICAL (<90 days)' THEN 'URGENT: ' + ServerName + ' (' + ISNULL(SQLVersion, '') + ') - Only ' + CAST(ISNULL(DaysUntilEOS, 0) AS VARCHAR) + ' days until EOS!'
        WHEN UrgencyLevel = 'HIGH (<180 days)' THEN 'WARNING: ' + ServerName + ' (' + ISNULL(SQLVersion, '') + ') - ' + CAST(ISNULL(DaysUntilEOS, 0) AS VARCHAR) + ' days until EOS'
        ELSE 'INFO: ' + ServerName + ' (' + ISNULL(SQLVersion, '') + ') - Plan upgrade within ' + CAST(ISNULL(DaysUntilEOS, 0) AS VARCHAR) + ' days'
    END AS AlertMessage,
    CASE 
        WHEN UrgencyLevel = 'END OF LIFE' THEN 'danger'
        WHEN UrgencyLevel = 'CRITICAL (<90 days)' THEN 'danger'
        WHEN UrgencyLevel = 'HIGH (<180 days)' THEN 'warning'
        ELSE 'info'
    END AS AlertType
FROM dbo.vw_SQLServer_EOS_Status
WHERE UrgencyLevel NOT IN ('OK - Supported', 'LOW (<2 years)')
ORDER BY 
    CASE UrgencyLevel
        WHEN 'END OF LIFE' THEN 1
        WHEN 'CRITICAL (<90 days)' THEN 2
        WHEN 'HIGH (<180 days)' THEN 3
        ELSE 4
    END,
    DaysUntilEOS;

GO

-- [dashboard].[vw_EOS_ByVersion]

CREATE VIEW dashboard.vw_EOS_ByVersion 
AS
WITH VersionGroups AS (
    SELECT 
        CASE 
            WHEN SQLVersion LIKE '17.%' THEN 'SQL Server 2025'
            WHEN SQLVersion LIKE '16.%' THEN 'SQL Server 2022'
            WHEN SQLVersion LIKE '15.%' THEN 'SQL Server 2019'
            WHEN SQLVersion LIKE '14.%' THEN 'SQL Server 2017'
            WHEN SQLVersion LIKE '13.%' THEN 'SQL Server 2016'
            WHEN SQLVersion LIKE '12.%' THEN 'SQL Server 2014'
            WHEN SQLVersion LIKE '11.%' THEN 'SQL Server 2012'
            ELSE 'Unknown'
        END AS SQLVersionName,
        CASE 
            WHEN SQLVersion LIKE '17.%' THEN 17
            WHEN SQLVersion LIKE '16.%' THEN 16
            WHEN SQLVersion LIKE '15.%' THEN 15
            WHEN SQLVersion LIKE '14.%' THEN 14
            WHEN SQLVersion LIKE '13.%' THEN 13
            WHEN SQLVersion LIKE '12.%' THEN 12
            WHEN SQLVersion LIKE '11.%' THEN 11
            ELSE 0
        END AS SQLMajorVersion
    FROM config.ServerInventory
    WHERE IsActive = 1 
      AND SQLVersion IS NOT NULL 
      AND SQLVersion <> ''
)
SELECT 
    SQLVersionName,
    SQLMajorVersion,
    COUNT(*) AS ServerCount,
    CASE SQLMajorVersion
        WHEN 17 THEN CAST('2030-01-14' AS DATE)
        WHEN 16 THEN CAST('2027-11-11' AS DATE)
        WHEN 15 THEN CAST('2030-01-08' AS DATE)
        WHEN 14 THEN CAST('2027-10-12' AS DATE)
        WHEN 13 THEN CAST('2026-07-14' AS DATE)
        WHEN 12 THEN CAST('2024-07-09' AS DATE)
        WHEN 11 THEN CAST('2022-07-12' AS DATE)
        ELSE NULL
    END AS EndOfSupport,
    CASE SQLMajorVersion
        WHEN 17 THEN DATEDIFF(DAY, GETDATE(), '2030-01-14')
        WHEN 16 THEN DATEDIFF(DAY, GETDATE(), '2027-11-11')
        WHEN 15 THEN DATEDIFF(DAY, GETDATE(), '2030-01-08')
        WHEN 14 THEN DATEDIFF(DAY, GETDATE(), '2027-10-12')
        WHEN 13 THEN DATEDIFF(DAY, GETDATE(), '2026-07-14')
        WHEN 12 THEN DATEDIFF(DAY, GETDATE(), '2024-07-09')
        WHEN 11 THEN DATEDIFF(DAY, GETDATE(), '2022-07-12')
        ELSE 0
    END AS DaysUntilEOS,
    CASE WHEN SQLMajorVersion IN (11, 12) THEN 0 ELSE 1 END AS IsSupported,
    CASE 
        WHEN SQLMajorVersion IN (11, 12) THEN 'End of Life'
        WHEN SQLMajorVersion IN (13, 14) THEN 'Extended Support'
        ELSE 'Mainstream Support'
    END AS SupportStatus,
    CASE 
        WHEN SQLMajorVersion IN (11, 12) THEN 'ðŸ”´'
        WHEN SQLMajorVersion = 13 THEN 'ðŸŸ '
        WHEN SQLMajorVersion = 14 THEN 'ðŸŸ¡'
        ELSE 'ðŸŸ¢'
    END AS StatusIcon
FROM VersionGroups
WHERE SQLVersionName <> 'Unknown'
GROUP BY SQLVersionName, SQLMajorVersion;

GO

-- [dashboard].[vw_EOS_KPISummary]

CREATE   VIEW [dashboard].[vw_EOS_KPISummary] AS
SELECT 
    COUNT(*) AS TotalServers,
    SUM(CASE WHEN br.IsSupported = 0 THEN 1 ELSE 0 END) AS EndOfLife,
    SUM(CASE WHEN br.IsSupported = 1 AND DATEDIFF(DAY, GETDATE(), br.EndOfSupport) <= 180 THEN 1 ELSE 0 END) AS Critical,
    SUM(CASE WHEN br.IsSupported = 1 AND DATEDIFF(DAY, GETDATE(), br.EndOfSupport) BETWEEN 181 AND 365 THEN 1 ELSE 0 END) AS Warning,
    SUM(CASE WHEN br.IsSupported = 1 AND DATEDIFF(DAY, GETDATE(), br.EndOfSupport) > 365 THEN 1 ELSE 0 END) AS Supported
FROM config.ServerInventory cms
LEFT JOIN Patching.SQLServerBuildReference br 
    ON cms.SQLVersion LIKE CAST(br.SQLMajorVersion AS VARCHAR) + '.%'
WHERE cms.IsActive = 1
AND (cms.ServerType = 'SQL Server' OR cms.ServerType IS NULL);

GO

-- [dashboard].[vw_EOS_ServerList]

CREATE   VIEW [dashboard].[vw_EOS_ServerList] AS
SELECT 
    cms.ServerName,
    br.SQLVersionName AS SQLVersion,
    COALESCE(cms.Environment, cms.CMSEnvironment, 'Unknown') AS Environment,
    cms.SqlCumulativeUpdate AS CurrentCU,
    br.EndOfSupport AS EndOfSupportDate,
    DATEDIFF(DAY, GETDATE(), br.EndOfSupport) AS DaysUntilEOS,
    CASE 
        WHEN br.IsSupported = 0 THEN 'End of Life'
        WHEN DATEDIFF(DAY, GETDATE(), br.EndOfSupport) <= 180 THEN 'Critical'
        WHEN DATEDIFF(DAY, GETDATE(), br.EndOfSupport) <= 365 THEN 'Warning'
        ELSE 'Supported'
    END AS UrgencyLevel,
    CASE 
        WHEN br.IsSupported = 0 THEN 'End of Life'
        WHEN DATEDIFF(DAY, GETDATE(), br.EndOfSupport) <= 180 THEN 'Critical'
        WHEN DATEDIFF(DAY, GETDATE(), br.EndOfSupport) <= 365 THEN 'Warning'
        ELSE 'Supported'
    END AS SupportStatus,
    CASE 
        WHEN br.IsSupported = 0 THEN 'Upgrade immediately'
        WHEN DATEDIFF(DAY, GETDATE(), br.EndOfSupport) <= 180 THEN 'Plan upgrade now'
        WHEN cms.SqlCumulativeUpdate <> br.LatestCU THEN 'Apply ' + ISNULL(br.LatestCU, 'latest CU')
        ELSE 'No action required'
    END AS RecommendedAction,
    br.LatestCU AS AvailableCU,
    CASE 
        WHEN br.IsSupported = 0 THEN 1
        WHEN DATEDIFF(DAY, GETDATE(), br.EndOfSupport) <= 180 THEN 2
        WHEN DATEDIFF(DAY, GETDATE(), br.EndOfSupport) <= 365 THEN 3
        WHEN cms.SqlCumulativeUpdate <> br.LatestCU THEN 4
        ELSE 5
    END AS SortPriority,
    cms.ComplianceTier
FROM config.ServerInventory cms
LEFT JOIN Patching.SQLServerBuildReference br 
    ON cms.SQLVersion LIKE CAST(br.SQLMajorVersion AS VARCHAR) + '.%'
WHERE cms.IsActive = 1
AND (cms.ServerType = 'SQL Server' OR cms.ServerType IS NULL);

GO

-- [dashboard].[vw_EOS_Timeline]

-- =====================================================================
-- 4. DASHBOARD TIMELINE - For timeline/gantt chart
-- =====================================================================
CREATE   VIEW [dashboard].[vw_EOS_Timeline]
AS
SELECT 
    SQLVersionName AS [Version],
    ServerCount AS [Servers],
    EndOfSupport AS [EOSDate],
    CONVERT(VARCHAR, EndOfSupport, 107) AS [EOSDateFormatted],
    DaysUntilEOS AS [DaysRemaining],
    CASE 
        WHEN DaysUntilEOS < 0 THEN 'Past EOS'
        WHEN DaysUntilEOS <= 90 THEN 'Critical'
        WHEN DaysUntilEOS <= 365 THEN 'Warning'
        ELSE 'OK'
    END AS [TimelineStatus],
    CASE 
        WHEN DaysUntilEOS < 0 THEN 100
        WHEN DaysUntilEOS <= 365 THEN 100 - (DaysUntilEOS * 100 / 365)
        ELSE 0
    END AS [UrgencyPercent]
FROM dbo.vw_SQLServer_EOS_Summary
WHERE SQLVersionName IS NOT NULL;

GO

-- [dashboard].[vw_EOS_VersionDistribution]

-- =====================================================================
-- 4. DASHBOARD VERSION DISTRIBUTION VIEW
-- =====================================================================
CREATE   VIEW [dashboard].[vw_EOS_VersionDistribution]
AS
SELECT 
    ISNULL(SQLVersionName, 'Unknown') AS SQLVersion,
    ServerCount,
    EndOfSupport,
    DaysUntilEOS,
    Status,
    CASE 
        WHEN Status = 'END OF LIFE' THEN '#FF4444'
        WHEN Status = 'ACTION REQUIRED' THEN '#FFA500'
        ELSE '#44AA44'
    END AS StatusColor,
    CASE 
        WHEN Status = 'END OF LIFE' THEN 1
        WHEN Status = 'ACTION REQUIRED' THEN 2
        ELSE 3
    END AS SortOrder
FROM dbo.vw_SQLServer_EOS_Summary;

GO

-- [dashboard].[vw_HelpDeskIntelligence]
-- Create Help Desk Intelligence View
CREATE   VIEW dashboard.vw_HelpDeskIntelligence AS
SELECT 
    si.ServerName,
    si.Environment,
    si.SQLVersion,
    si.CMSGroupName,
    si.ComplianceTier,
    ISNULL(rs.IsOnline, 0) AS IsOnline,
    rs.LastChecked,
    CASE 
        WHEN rs.IsOnline = 0 THEN 'Critical'
        ELSE 'Healthy'
    END AS HealthStatus
FROM config.ServerInventory si
LEFT JOIN config.ReachabilityStatus rs ON si.ServerName = rs.ServerName
WHERE si.IsActive = 1 AND si.IsSQLServer = 1;
GO

-- [dashboard].[vw_HelpDeskStatus]

CREATE   VIEW dashboard.vw_HelpDeskStatus
AS
/*
    Help Desk Dashboard View - Comprehensive server status for Tier 1 support
    Includes: Server health, compliance status, disk space, patching, backups
*/
WITH ServerHealth AS (
    SELECT 
        cms.ServerName,
        cms.GroupName AS CMSGroup,
        ISNULL(p.Priority, 99) AS GroupPriority,
        -- Determine Environment
        CASE 
            WHEN cms.GroupName IN ('HIGH_CRITICALITY', 'MEDIUM_CRITICALITY', 'LOW_CRITICALITY', 'PUBLISHERS') THEN 'Production'
            ELSE 'Non-Production'
        END AS Environment,
        -- Determine Tier
        CASE 
            WHEN cms.GroupName = 'HIGH_CRITICALITY' THEN 'Tier 1 - CJIS Critical'
            WHEN cms.GroupName = 'MEDIUM_CRITICALITY' THEN 'Tier 2 - CJ Operations'
            WHEN cms.GroupName = 'LOW_CRITICALITY' THEN 'Tier 3 - Administrative'
            ELSE 'Tier 4 - Dev/Test'
        END AS TierLevel,
        ROW_NUMBER() OVER (PARTITION BY cms.ServerName ORDER BY ISNULL(p.Priority, 99)) AS PriorityRank
    FROM config.vw_MonitoredServers cms
    LEFT JOIN config.CMSGroupPriority p ON cms.GroupName = p.GroupName
    WHERE cms.IsActive = 1
),
DiskAlerts AS (
    SELECT 
        ServerName,
        COUNT(*) AS DiskAlertCount,
        MIN(FreeSpacePercent) AS MinFreePercent,
        MAX(CASE WHEN AlertLevel = 'Critical' THEN 1 ELSE 0 END) AS HasCriticalDisk
    FROM dashboard.vw_DiskSpaceMonitor
    WHERE AlertLevel IN ('Critical', 'Warning')
    GROUP BY ServerName
),
PatchStatus AS (
    SELECT 
        ServerName,
        ComplianceStatus,
        MissingPatches,
        DaysSinceLastPatch
    FROM Patching.TaniumPatchStatus
    WHERE IsActive = 1
)
SELECT DISTINCT
    sh.ServerName,
    sh.CMSGroup,
    sh.Environment,
    sh.TierLevel,
    sh.GroupPriority,
    -- Disk Status
    ISNULL(da.DiskAlertCount, 0) AS DiskAlertCount,
    ISNULL(da.MinFreePercent, 100) AS MinDiskFreePercent,
    ISNULL(da.HasCriticalDisk, 0) AS HasCriticalDisk,
    -- Patch Status
    ISNULL(ps.ComplianceStatus, 'Unknown') AS PatchStatus,
    ISNULL(ps.MissingPatches, 0) AS MissingPatches,
    ISNULL(ps.DaysSinceLastPatch, 999) AS DaysSinceLastPatch,
    -- Overall Health Status
    CASE 
        WHEN da.HasCriticalDisk = 1 THEN 'Critical'
        WHEN ps.ComplianceStatus = 'Non-Compliant' THEN 'Warning'
        WHEN da.DiskAlertCount > 0 THEN 'Warning'
        WHEN ps.MissingPatches > 3 THEN 'Warning'
        ELSE 'Healthy'
    END AS OverallStatus,
    -- Dashboard Color
    CASE 
        WHEN da.HasCriticalDisk = 1 THEN 'Red'
        WHEN ps.ComplianceStatus = 'Non-Compliant' THEN 'Orange'
        WHEN da.DiskAlertCount > 0 THEN 'Yellow'
        ELSE 'Green'
    END AS DashboardColor,
    -- Government Compliance (all servers in Azure Gov are compliant)
    'FedRAMP High, CJIS, DoD IL4/IL5' AS ComplianceFrameworks,
    'Azure Government' AS CloudEnvironment
FROM ServerHealth sh
LEFT JOIN DiskAlerts da ON sh.ServerName = da.ServerName
LEFT JOIN PatchStatus ps ON sh.ServerName = ps.ServerName
WHERE sh.PriorityRank = 1;

GO

-- [dashboard].[vw_IncidentsByCMSGroup]

-- =============================================================================
-- VIEW: dashboard.vw_IncidentsByCMSGroup
-- For dashboard chart - incidents by server group
-- =============================================================================
CREATE   VIEW dashboard.vw_IncidentsByCMSGroup
AS
SELECT 
    ISNULL(CMSGroupName, 'Unknown') AS CMSGroup,
    COUNT(*) AS TotalIncidents,
    SUM(CASE WHEN Status NOT IN ('Resolved', 'Closed', 'Archived') THEN 1 ELSE 0 END) AS OpenIncidents,
    SUM(CASE WHEN Severity = 1 THEN 1 ELSE 0 END) AS CriticalCount
FROM correlation.Incidents
WHERE CreatedDate >= DATEADD(DAY, -30, GETDATE())
GROUP BY CMSGroupName;

GO

-- [dashboard].[vw_IncidentSummary]

-- =============================================================================
-- VIEW: dashboard.vw_IncidentSummary
-- For main dashboard incident widget
-- =============================================================================
CREATE   VIEW dashboard.vw_IncidentSummary
AS
SELECT 
    (SELECT COUNT(*) FROM correlation.Incidents WHERE Status NOT IN ('Resolved', 'Closed', 'Archived')) AS OpenIncidents,
    (SELECT COUNT(*) FROM correlation.Incidents WHERE Status NOT IN ('Resolved', 'Closed', 'Archived') AND Severity = 1) AS CriticalIncidents,
    (SELECT COUNT(*) FROM correlation.Incidents WHERE Status NOT IN ('Resolved', 'Closed', 'Archived') AND Severity = 2) AS HighIncidents,
    (SELECT COUNT(*) FROM correlation.Incidents WHERE Status NOT IN ('Resolved', 'Closed', 'Archived') AND AcknowledgedBy IS NULL) AS UnacknowledgedIncidents,
    (SELECT COUNT(*) FROM correlation.Incidents WHERE Status = 'Resolved' AND ResolvedDate >= DATEADD(DAY, -1, GETDATE())) AS ResolvedLast24h,
    (SELECT AVG(MTTR_Minutes) FROM correlation.Incidents WHERE MTTR_Minutes IS NOT NULL AND ResolvedDate >= DATEADD(DAY, -30, GETDATE())) AS AvgMTTR_30Day;

GO

-- [dashboard].[vw_InfrastructureMetrics]

CREATE   VIEW dashboard.vw_InfrastructureMetrics
AS
/*
    Comprehensive Infrastructure Metrics for Dashboard
    Government Compliance: FedRAMP High, CJIS, DoD IL4/IL5/IL6
*/
SELECT 
    -- Server Counts
    (SELECT COUNT(DISTINCT ServerName) FROM config.vw_MonitoredServers WHERE IsActive = 1) AS TotalServers,
    (SELECT COUNT(DISTINCT ServerName) FROM config.vw_MonitoredServers WHERE IsActive = 1 AND GroupName IN ('HIGH_CRITICALITY', 'MEDIUM_CRITICALITY', 'LOW_CRITICALITY')) AS ProductionServers,
    (SELECT COUNT(DISTINCT ServerName) FROM config.vw_MonitoredServers WHERE IsActive = 1 AND GroupName = 'NONPROD_APP') AS NonProdServers,
    
    -- Disk Space
    (SELECT COUNT(*) FROM dashboard.vw_DiskSpaceMonitor WHERE AlertLevel = 'Critical') AS DiskCritical,
    (SELECT COUNT(*) FROM dashboard.vw_DiskSpaceMonitor WHERE AlertLevel = 'Warning') AS DiskWarning,
    (SELECT COUNT(*) FROM dashboard.vw_DiskSpaceMonitor WHERE AlertLevel = 'Caution') AS DiskCaution,
    (SELECT COUNT(*) FROM dashboard.vw_DiskSpaceMonitor WHERE AlertLevel = 'Healthy') AS DiskHealthy,
    
    -- Patching
    (SELECT COUNT(*) FROM Patching.TaniumPatchStatus WHERE IsActive = 1 AND ComplianceStatus = 'Compliant') AS PatchCompliant,
    (SELECT COUNT(*) FROM Patching.TaniumPatchStatus WHERE IsActive = 1 AND ComplianceStatus = 'Non-Compliant') AS PatchNonCompliant,
    (SELECT COUNT(*) FROM Patching.TaniumPatchStatus WHERE IsActive = 1 AND MissingCriticalPatches > 0) AS CriticalPatchesMissing,
    
    -- Backup Compliance
    (SELECT COUNT(*) FROM ctl.BackupCompliance WHERE IsCompliant = 1) AS BackupCompliant,
    (SELECT COUNT(*) FROM ctl.BackupCompliance WHERE IsCompliant = 0) AS BackupNonCompliant,
    
    -- Health
    (SELECT COUNT(*) FROM dashboard.vw_HelpDeskStatus WHERE OverallStatus = 'Healthy') AS ServersHealthy,
    (SELECT COUNT(*) FROM dashboard.vw_HelpDeskStatus WHERE OverallStatus = 'Warning') AS ServersWarning,
    (SELECT COUNT(*) FROM dashboard.vw_HelpDeskStatus WHERE OverallStatus = 'Critical') AS ServersCritical,
    
    -- Compliance Frameworks
    'Azure Government' AS CloudEnvironment,
    'FedRAMP High, CJIS, DoD IL4, DoD IL5, DoD IL6' AS ComplianceFrameworks,
    
    -- Timestamps
    GETDATE() AS GeneratedAt;

GO

-- [dashboard].[vw_JobsMonitor]

CREATE   VIEW dashboard.vw_JobsMonitor
AS
SELECT
    ServerName   = h.ServerName,
    JobName      = h.JobName,
    StepName     = h.StepName,
    EventTimeUtc =
        COALESCE
        (
            TRY_CONVERT(datetime2(0), h.RunDateTime),
            TRY_CONVERT(datetime2(0), DATEADD(SECOND, DATEDIFF(SECOND, '00:00:00', TRY_CONVERT(time(0), h.RunTime)), TRY_CONVERT(datetime2(0), h.RunDate))),
            TRY_CONVERT(datetime2(0), h.CollectionTime)
        ),
    RunStatus    = h.RunStatus,
    Message      = TRY_CONVERT(nvarchar(max), COALESCE(h.ErrorMessage, ''))
FROM metrics.FACT_JobHistory h;

GO

-- [dashboard].[vw_JobsSummary]
CREATE OR ALTER VIEW [dashboard].[vw_JobsSummary] AS
SELECT 
    COUNT(*) AS TotalJobs,
    SUM(CASE WHEN LastRunOutcome = 'Succeeded' THEN 1 ELSE 0 END) AS SucceededCount,
    SUM(CASE WHEN LastRunOutcome = 'Failed' THEN 1 ELSE 0 END) AS FailedCount,
    SUM(CASE WHEN LastRunOutcome = 'Cancelled' THEN 1 ELSE 0 END) AS CancelledCount,
    SUM(CASE WHEN LastRunOutcome NOT IN ('Succeeded', 'Failed', 'Cancelled') OR LastRunOutcome IS NULL THEN 1 ELSE 0 END) AS UnknownCount,
    COUNT(DISTINCT ServerName) AS ServersWithJobs,
    MAX(LastRunDate) AS LatestRun
FROM jobs.SQLAgentJobs
WHERE LastRunDate >= DATEADD(DAY, -1, GETDATE());

GO

-- [dashboard].[vw_LinkedServersBySource]

-- Per-server breakdown view
CREATE   VIEW dashboard.vw_LinkedServersBySource
AS
SELECT 
    ServerName AS SourceServer,
    COUNT(*) AS LinkedServerCount,
    COUNT(DISTINCT provider) AS ProviderTypes,
    STRING_AGG(RmtServer, ', ') WITHIN GROUP (ORDER BY RmtServer) AS LinkedServerList,
    MAX(ISNULL(Modified, Created)) AS LastUpdated
FROM inventory.Linked_Servers
GROUP BY ServerName;

GO

-- [dashboard].[vw_LinkedServersInventory]

-- ============================================================================
-- 7. Create Dashboard Views
-- ============================================================================

-- Main inventory view
CREATE   VIEW dashboard.vw_LinkedServersInventory
AS
SELECT 
    ServerName AS SourceServer,
    RmtServer AS LinkedServerName,
    provider AS Provider,
    Data_Source AS DataSource,
    SrvProduct AS ServerProduct,
    CASE 
        WHEN rmtusername IS NOT NULL AND rmtusername != '' THEN rmtusername
        WHEN uses_self_credential = 1 THEN 'Self Credential'
        ELSE 'Integrated Security'
    END AS AuthenticationMethod,
    Remote_Login AS MappedLogin,
    Created AS FirstDiscovered,
    Modified AS LastSeen,
    DATEDIFF(DAY, ISNULL(Modified, Created), GETDATE()) AS DaysSinceLastSeen
FROM inventory.Linked_Servers;

GO

-- [dashboard].[vw_LinkedServersProviderDistribution]

-- Provider distribution view (for pie chart)
CREATE   VIEW dashboard.vw_LinkedServersProviderDistribution
AS
SELECT 
    provider AS Provider,
    COUNT(*) AS LinkedServerCount,
    CAST(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER() AS DECIMAL(5,2)) AS Percentage
FROM inventory.Linked_Servers
GROUP BY provider;

GO

-- [dashboard].[vw_LinkedServersStaleAlert]

-- Stale records alert view
CREATE   VIEW dashboard.vw_LinkedServersStaleAlert
AS
SELECT 
    ServerName AS SourceServer,
    RmtServer AS LinkedServerName,
    provider AS Provider,
    ISNULL(Modified, Created) AS LastSeen,
    DATEDIFF(DAY, ISNULL(Modified, Created), GETDATE()) AS DaysSinceLastSeen,
    CASE 
        WHEN DATEDIFF(DAY, ISNULL(Modified, Created), GETDATE()) > 30 THEN 'Critical'
        WHEN DATEDIFF(DAY, ISNULL(Modified, Created), GETDATE()) > 14 THEN 'Warning'
        ELSE 'OK'
    END AS AlertLevel
FROM inventory.Linked_Servers
WHERE DATEDIFF(DAY, ISNULL(Modified, Created), GETDATE()) > 7;

GO

-- [dashboard].[vw_LinkedServersSummary]

-- Summary statistics view
CREATE   VIEW dashboard.vw_LinkedServersSummary
AS
SELECT 
    COUNT(DISTINCT ServerName) AS TotalSourceServers,
    COUNT(*) AS TotalLinkedServers,
    COUNT(DISTINCT RmtServer) AS UniqueTargetServers,
    SUM(CASE WHEN provider = 'SQLNCLI11' THEN 1 ELSE 0 END) AS SQLNCLI11_Count,
    SUM(CASE WHEN provider = 'MSOLEDBSQL19' THEN 1 ELSE 0 END) AS MSOLEDBSQL19_Count,
    SUM(CASE WHEN provider NOT IN ('SQLNCLI11', 'MSOLEDBSQL19') THEN 1 ELSE 0 END) AS OtherProvider_Count,
    SUM(CASE WHEN uses_self_credential = 1 THEN 1 ELSE 0 END) AS SelfCredential_Count,
    SUM(CASE WHEN Remote_Login IS NOT NULL AND Remote_Login != '' THEN 1 ELSE 0 END) AS MappedLogin_Count,
    SUM(CASE WHEN DATEDIFF(DAY, ISNULL(Modified, Created), GETDATE()) > 7 THEN 1 ELSE 0 END) AS StaleRecords,
    MAX(ISNULL(Modified, Created)) AS LastCollectionDate
FROM inventory.Linked_Servers;

GO

-- [dashboard].[vw_LinkedServersTargetAnalysis]

-- Target server analysis view
CREATE   VIEW dashboard.vw_LinkedServersTargetAnalysis
AS
SELECT 
    RmtServer AS TargetServer,
    COUNT(*) AS InboundLinkCount,
    COUNT(DISTINCT ServerName) AS SourceServerCount,
    STRING_AGG(ServerName, ', ') WITHIN GROUP (ORDER BY ServerName) AS LinkedFromServers
FROM inventory.Linked_Servers
GROUP BY RmtServer;

GO

-- [dashboard].[vw_NonSqlServers]
CREATE OR ALTER VIEW dashboard.vw_NonSqlServers
AS
SELECT
    c.ServerName,
    c.CMSEnvironment,
    c.CMSGroupName,
    c.CMSParentGroupName,
    c.CMSCriticality,
    n.NonSqlType,
    n.AppCategory,
    n.LogicalGroupName,
    n.Notes
FROM config.vw_CMS_ServerInventory_Current c
LEFT JOIN config.NonSqlServerClassification n
    ON n.ServerName = c.ServerName
WHERE c.SQLVersion IS NULL OR LTRIM(RTRIM(c.SQLVersion)) = '';

GO

-- [dashboard].[vw_PatchDeploymentStatus]

CREATE VIEW dashboard.vw_PatchDeploymentStatus
AS
SELECT 
    p.PatchID,
    p.ServerName,
    p.PatchType,
    p.ToBuild,
    p.PatchStatus,
    p.ScheduledTime,
    p.StartTime,
    p.Duration,
    p.InitiatedBy,
    p.ApprovedBy,
    p.Environment,
    p.IsProduction,
    p.HealthCheckPassed,
    CASE 
        WHEN p.PatchStatus = 'Success' AND p.HealthCheckPassed = 1 THEN 'SUCCESS_GREEN'
        WHEN p.PatchStatus = 'Failed' OR p.PatchStatus = 'RolledBack' THEN 'FAILED_RED'
        WHEN p.PatchStatus = 'InProgress' THEN 'INPROGRESS_BLUE'
        WHEN p.PatchStatus = 'Pending' AND p.ScheduledTime <= DATEADD(HOUR, 24, GETDATE()) THEN 'UPCOMING_YELLOW'
        ELSE 'SCHEDULED_GRAY'
    END AS StatusDisplay,
    CASE 
        WHEN p.ScheduledTime IS NOT NULL THEN DATEDIFF(MINUTE, GETDATE(), p.ScheduledTime)
        ELSE NULL
    END AS MinutesUntilScheduled
FROM patch.fact_PatchHistory p
WHERE p.StartTime >= DATEADD(DAY, -30, GETDATE()) -- Last 30 days
    OR p.PatchStatus IN ('Pending', 'Approved', 'InProgress')
;

GO

-- [dashboard].[vw_PBI_DimDatabases]

CREATE VIEW dashboard.vw_PBI_DimDatabases
AS
SELECT DISTINCT
    ServerName + '|' + DatabaseName AS DatabaseKey,
    ServerName,
    DatabaseName,
    DatabaseState,
    RecoveryModel,
    CompatibilityLevel
FROM fact.DatabaseStatus
WHERE CollectedDate = (SELECT MAX(CollectedDate) FROM fact.DatabaseStatus);

GO

-- [dashboard].[vw_PBI_PermissionChanges]

CREATE VIEW dashboard.vw_PBI_PermissionChanges
AS
SELECT 
    CAST(ChangeTime AS DATE) AS ChangeDate,
    ServerName,
    DatabaseName,
    ChangeType,
    PrincipalName,
    PermissionName,
    ChangedBy,
    ChangeTime,
    1 AS ChangeCount
FROM change.PermissionChanges
WHERE ChangeTime > DATEADD(DAY, -90, GETDATE());

GO

-- [dashboard].[vw_PBI_SchemaChanges]

CREATE VIEW dashboard.vw_PBI_SchemaChanges
AS
SELECT 
    CAST(ChangeTime AS DATE) AS ChangeDate,
    ServerName,
    DatabaseName,
    SchemaName,
    ObjectName,
    ObjectType,
    ChangeType,
    ChangedBy,
    ChangeTime,
    1 AS ChangeCount
FROM change.SchemaChanges
WHERE ChangeTime > DATEADD(DAY, -90, GETDATE());

GO

-- [dashboard].[vw_PlaybookSummary]

-- =============================================================================
-- VIEW: dashboard.vw_PlaybookSummary
-- For dashboard playbook widget
-- =============================================================================
CREATE   VIEW dashboard.vw_PlaybookSummary
AS
SELECT 
    (SELECT COUNT(*) FROM playbook.Playbooks WHERE IsActive = 1) AS AvailablePlaybooks,
    (SELECT COUNT(*) FROM playbook.ExecutionHistory WHERE StartedDate >= DATEADD(DAY, -7, GETDATE())) AS ExecutionsLast7Days,
    (SELECT COUNT(*) FROM playbook.ExecutionHistory WHERE Status = 'Completed' AND StartedDate >= DATEADD(DAY, -7, GETDATE())) AS SuccessfulLast7Days,
    (SELECT COUNT(*) FROM playbook.ExecutionHistory WHERE Status = 'Failed' AND StartedDate >= DATEADD(DAY, -7, GETDATE())) AS FailedLast7Days,
    (SELECT TOP 1 PlaybookName FROM playbook.Playbooks p 
     JOIN playbook.ExecutionHistory eh ON p.PlaybookID = eh.PlaybookID 
     GROUP BY p.PlaybookID, p.PlaybookName ORDER BY COUNT(*) DESC) AS MostUsedPlaybook;

GO

-- [dashboard].[vw_RecentDeployments]

-- View: Recent Deployments
CREATE   VIEW dashboard.vw_RecentDeployments AS
SELECT TOP 100
    DeploymentHistoryId,
    DeploymentId,
    ScriptPath,
    SqlInstance,
    DatabaseName,
    Environment,
    Status,
    StartTime,
    EndTime,
    DeployedBy,
    TicketNumber,
    DurationSeconds
FROM pipeline.DeploymentHistory
ORDER BY StartTime DESC;

GO

-- [dashboard].[vw_RecentIncidents]

-- =============================================================================
-- VIEW: dashboard.vw_RecentIncidents
-- For dashboard incident list
-- =============================================================================
CREATE   VIEW dashboard.vw_RecentIncidents
AS
SELECT TOP 20
    i.IncidentID,
    i.IncidentNumber,
    i.Title,
    i.Severity,
    CASE i.Severity WHEN 1 THEN 'Critical' WHEN 2 THEN 'High' WHEN 3 THEN 'Medium' ELSE 'Low' END AS SeverityName,
    CASE i.Severity WHEN 1 THEN '#ef4444' WHEN 2 THEN '#f59e0b' WHEN 3 THEN '#3b82f6' ELSE '#6b7280' END AS SeverityColor,
    i.Status,
    i.PrimaryServerName,
    i.CMSGroupName,
    i.AlertCount,
    i.CreatedDate,
    DATEDIFF(MINUTE, i.CreatedDate, GETDATE()) AS AgeMinutes,
    i.AcknowledgedBy,
    i.TrackITTicket
FROM correlation.Incidents i
WHERE i.Status NOT IN ('Resolved', 'Closed', 'Archived')
ORDER BY i.Severity, i.CreatedDate DESC;

GO

-- [dashboard].[vw_RecentNotifications]

-- View: Recent Notifications
CREATE   VIEW dashboard.vw_RecentNotifications AS
SELECT TOP 100
    NotificationId,
    AlertId,
    Channel,
    Recipient,
    LEFT(Message, 200) AS MessagePreview,
    Priority,
    Status,
    SentAt
FROM alerts.NotificationLog
ORDER BY SentAt DESC;

GO

-- [dashboard].[vw_ServerHealthSummary]
-- Recreate vw_ServerHealthSummary with Edition
CREATE VIEW dashboard.vw_ServerHealthSummary
AS
WITH LatestPatches AS (
    SELECT 
        ServerName,
        StartTime AS LastPatchDate,
        PatchStatus AS LastPatchStatus,
        ROW_NUMBER() OVER (PARTITION BY ServerName ORDER BY StartTime DESC) AS rn
    FROM patch.fact_PatchHistory
)
SELECT 
    s.ServerName,
    s.Environment,
    s.Edition, -- ADDED!
    s.SQLVersion, -- ADDED!
    -- Alert summary
    COUNT(DISTINCT a.AlertID) AS ActiveAlertCount,
    CAST(MAX(CASE WHEN a.Severity = 1 THEN 1 ELSE 0 END) AS BIT) AS HasCriticalAlert,
    -- AI Diagnostic summary
    COUNT(DISTINCT d.DiagnosticID) AS OpenDiagnosticCount,
    CAST(MAX(CASE WHEN d.Severity = 1 AND d.Status = 'Open' THEN 1 ELSE 0 END) AS BIT) AS HasCriticalDiagnostic,
    -- Build status
    MAX(b.CurrentBuild) AS CurrentBuild,
    MAX(b.BuildsBehind) AS BuildsBehind,
    MAX(b.DaysTilEOL) AS DaysTilEOL,
    MAX(b.UpdatePriority) AS UpdatePriority,
    -- Patch status
    MAX(p.LastPatchDate) AS LastPatchDate,
    MAX(p.LastPatchStatus) AS LastPatchStatus,
    -- Overall health
    CASE 
        WHEN COUNT(CASE WHEN a.Severity = 1 THEN 1 END) > 0
             OR COUNT(CASE WHEN d.Severity = 1 AND d.Status = 'Open' THEN 1 END) > 0
        THEN 'CRITICAL'
        WHEN COUNT(CASE WHEN a.Severity = 2 THEN 1 END) > 0
             OR MAX(ISNULL(b.BuildsBehind, 0)) >= 5
        THEN 'WARNING'
        WHEN MAX(ISNULL(b.DaysTilEOL, 9999)) < 365
             OR MAX(ISNULL(b.BuildsBehind, 0)) > 0
        THEN 'ADVISORY'
        ELSE 'HEALTHY'
    END AS OverallHealth
FROM 
    dbo.dim_Servers s
    LEFT JOIN dbo.fact_Alerts a ON s.ServerName = a.ServerName 
        AND a.Processed = 0 
        AND a.AlertTime >= DATEADD(DAY, -1, GETDATE())
    LEFT JOIN ai.fact_Diagnostics d ON s.ServerName = d.ServerName 
        AND d.Status IN ('Open', 'InProgress')
    LEFT JOIN inventory.dim_ServerBuilds b ON s.ServerName = b.ServerName
    LEFT JOIN LatestPatches p ON s.ServerName = p.ServerName AND p.rn = 1
WHERE s.IsActive = 1
GROUP BY 
    s.ServerName,
    s.Environment,
    s.Edition,
    s.SQLVersion;

GO

-- [dashboard].[vw_ServersNeedingAttention]

CREATE VIEW dashboard.vw_ServersNeedingAttention
AS
SELECT 
    ServerName,
    Environment,
    AppCategory,
    SQLVersion,
    LastSeen,
    Notes,
    CASE 
        WHEN SQLVersion IS NULL OR SQLVersion = '' THEN 'Missing SQL Version - Inventory not collected'
        WHEN LastSeen < DATEADD(DAY, -7, GETDATE()) THEN 'Stale Data (>7 days)'
        WHEN LastSeen < DATEADD(DAY, -1, GETDATE()) THEN 'Data >24 hours old'
        ELSE 'Check Notes'
    END AS Issue,
    CASE 
        WHEN SQLVersion IS NULL OR SQLVersion = '' THEN 1
        WHEN LastSeen < DATEADD(DAY, -7, GETDATE()) THEN 2
        ELSE 3
    END AS Priority
FROM config.ServerInventory
WHERE IsActive = 1
AND (ServerType = 'SQL Server' OR ServerType IS NULL)
AND (
    SQLVersion IS NULL 
    OR SQLVersion = ''
    OR LastSeen < DATEADD(DAY, -1, GETDATE())
);

GO

-- [dashboard].[vw_ServerStatus]

CREATE   VIEW dashboard.vw_ServerStatus
AS
SELECT 
    si.ServerID,
    si.ServerName,
    si.InstanceName,
    si.Environment,
    si.SQLVersion,
    si.SqlBuild,
    si.SQLEdition,
    si.IsActive,
    si.AppCategory,
    si.ComplianceTier,
    si.CMSCriticality,
    si.CMSEnvironment,
    CASE 
        WHEN si.IsActive = 0 THEN 0
        WHEN si.LastSeen >= DATEADD(HOUR, -2, GETDATE()) THEN 1
        ELSE 0
    END AS IsOnline,
    si.LastSeen,
    si.LastInventoryUpdate,
    CASE 
        WHEN si.IsActive = 0 THEN 'Inactive'
        WHEN si.LastSeen >= DATEADD(HOUR, -2, GETDATE()) THEN 'Online'
        WHEN si.LastSeen >= DATEADD(HOUR, -24, GETDATE()) THEN 'Warning'
        ELSE 'Offline'
    END AS Status,
    CASE 
        WHEN si.IsActive = 0 THEN '#6c757d'
        WHEN si.LastSeen >= DATEADD(HOUR, -2, GETDATE()) THEN '#28a745'
        WHEN si.LastSeen >= DATEADD(HOUR, -24, GETDATE()) THEN '#ffc107'
        ELSE '#dc3545'
    END AS StatusColor,
    DATEDIFF(MINUTE, si.LastSeen, GETDATE()) AS MinutesSinceLastSeen,
    si.ServerType,
    si.FQDN
FROM config.ServerInventory si;

GO

-- [dashboard].[vw_ServerTypeSummary]

CREATE VIEW dashboard.vw_ServerTypeSummary
AS
SELECT 
    COUNT(*) AS TotalServers,
    COUNT(CASE WHEN ServerType = 'SQL Server' OR ServerType IS NULL THEN 1 END) AS SQLServers,
    COUNT(CASE WHEN ServerType = 'App Webserver' THEN 1 END) AS AppWebservers,
    COUNT(CASE WHEN (ServerType = 'SQL Server' OR ServerType IS NULL) 
                AND SQLVersion IS NOT NULL AND SQLVersion != '' THEN 1 END) AS SQLWithVersion,
    COUNT(CASE WHEN (ServerType = 'SQL Server' OR ServerType IS NULL) 
                AND (SQLVersion IS NULL OR SQLVersion = '') THEN 1 END) AS SQLMissingVersion,
    COUNT(CASE WHEN Environment = 'Production' AND (ServerType = 'SQL Server' OR ServerType IS NULL) THEN 1 END) AS ProdSQLServers,
    COUNT(CASE WHEN Environment != 'Production' AND (ServerType = 'SQL Server' OR ServerType IS NULL) THEN 1 END) AS NonProdSQLServers
FROM config.ServerInventory
WHERE IsActive = 1;

GO

-- [dashboard].[vw_SQLVersionDistribution]
CREATE OR ALTER VIEW [dashboard].[vw_SQLVersionDistribution] AS
SELECT 
    CASE 
        WHEN SQLVersion LIKE '17.%' THEN 'SQL Server 2022'
        WHEN SQLVersion LIKE '16.%' THEN 'SQL Server 2022'
        WHEN SQLVersion LIKE '15.%' THEN 'SQL Server 2019'
        WHEN SQLVersion LIKE '14.%' THEN 'SQL Server 2017'
        WHEN SQLVersion LIKE '13.%' THEN 'SQL Server 2016'
        WHEN SQLVersion LIKE '12.%' THEN 'SQL Server 2014'
        WHEN SQLVersion LIKE '11.%' THEN 'SQL Server 2012'
        WHEN SQLVersion IS NULL OR SQLVersion = '' THEN 'Unknown/Pending'
        ELSE 'Other'
    END AS VersionName,
    COUNT(*) AS ServerCount,
    CAST(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER() AS DECIMAL(5,1)) AS Percentage
FROM config.ServerInventory
WHERE IsActive = 1
  AND (ServerType = 'SQL Server' OR ServerType IS NULL)
GROUP BY 
    CASE 
        WHEN SQLVersion LIKE '17.%' THEN 'SQL Server 2022'
        WHEN SQLVersion LIKE '16.%' THEN 'SQL Server 2022'
        WHEN SQLVersion LIKE '15.%' THEN 'SQL Server 2019'
        WHEN SQLVersion LIKE '14.%' THEN 'SQL Server 2017'
        WHEN SQLVersion LIKE '13.%' THEN 'SQL Server 2016'
        WHEN SQLVersion LIKE '12.%' THEN 'SQL Server 2014'
        WHEN SQLVersion LIKE '11.%' THEN 'SQL Server 2012'
        WHEN SQLVersion IS NULL OR SQLVersion = '' THEN 'Unknown/Pending'
        ELSE 'Other'
    END;

GO

-- [dashboard].[vw_VeeamRepositoryDashboard]

CREATE VIEW dashboard.vw_VeeamRepositoryDashboard
AS
SELECT 
    RepositoryID,
    RepositoryName,
    RepoVolume,
    RepoTotalTB,
    RepoFreeTB,
    CAST(RepoTotalTB - RepoFreeTB AS DECIMAL(18,2)) AS RepoUsedTB,
    RepoFreePercent,
    CAST(100 - RepoFreePercent AS DECIMAL(5,2)) AS RepoUsedPercent,
    CASE 
        WHEN RepoFreePercent >= 30 THEN 'Healthy'
        WHEN RepoFreePercent >= 20 THEN 'Warning'
        WHEN RepoFreePercent >= 10 THEN 'Critical'
        ELSE 'Emergency'
    END AS HealthStatus,
    CASE 
        WHEN RepoFreePercent >= 30 THEN 0
        WHEN RepoFreePercent >= 20 THEN 1
        WHEN RepoFreePercent >= 10 THEN 2
        ELSE 3
    END AS SeverityLevel,
    IsActive,
    LastUpdated,
    DATEDIFF(MINUTE, LastUpdated, GETDATE()) AS MinutesSinceUpdate
FROM [config].[RepositoryAssignment]
WHERE IsActive = 1

GO

-- [dashboard].[vw_VeeamRepositorySummary]

CREATE VIEW dashboard.vw_VeeamRepositorySummary
AS
SELECT 
    'Veeam Repository' AS Category,
    COUNT(*) AS Total,
    SUM(CASE WHEN RepoFreePercent >= 30 THEN 1 ELSE 0 END) AS Healthy,
    SUM(CASE WHEN RepoFreePercent < 30 AND RepoFreePercent >= 20 THEN 1 ELSE 0 END) AS Warning,
    SUM(CASE WHEN RepoFreePercent < 20 THEN 1 ELSE 0 END) AS Critical,
    CAST(AVG(RepoFreePercent) AS DECIMAL(5,2)) AS AvgFreePercent,
    CAST(SUM(RepoTotalTB) AS DECIMAL(18,2)) AS TotalCapacityTB,
    CAST(SUM(RepoFreeTB) AS DECIMAL(18,2)) AS TotalFreeTB,
    CAST(SUM(RepoTotalTB - RepoFreeTB) AS DECIMAL(18,2)) AS TotalUsedTB,
    MAX(LastUpdated) AS LastCheck
FROM [config].[RepositoryAssignment]
WHERE IsActive = 1

GO

-- [DBAOps].[vw_OlaDeploymentStatus]

CREATE   VIEW [DBAOps].[vw_OlaDeploymentStatus] AS
SELECT 
    cms.ServerName,
    CASE 
        WHEN cms.LastSeen >= DATEADD(DAY, -1, GETDATE()) THEN 'Deployed'
        WHEN cms.LastSeen >= DATEADD(DAY, -7, GETDATE()) THEN 'Partial'
        ELSE 'Not Deployed'
    END AS DeploymentStatus,
    cms.LastSeen AS LastSuccessfulCollection,
    DATEDIFF(DAY, cms.LastSeen, GETDATE()) AS DaysSinceCollection,
    1 AS JobsDeployed,
    1 AS JobsEnabled,
    CASE 
        WHEN cms.LastSeen >= DATEADD(DAY, -1, GETDATE()) THEN 'HEALTHY'
        WHEN cms.LastSeen >= DATEADD(DAY, -7, GETDATE()) THEN 'STALE'
        ELSE 'CRITICAL'
    END AS CollectionStatus
FROM config.ServerInventory cms
WHERE cms.IsActive = 1
AND (cms.ServerType = 'SQL Server' OR cms.ServerType IS NULL);

GO

-- [dbo].[BackupComplianceDashboard]
CREATE OR ALTER VIEW dbo.BackupComplianceDashboard
AS
SELECT
    ServerName,
    BackupType,
    LastBackupTime,
    Status,
    IsCompliant,
    ComplianceScore,
    AnomalyScore,
    ForecastFailureHours,
    LoadDate
FROM ctl.BackupCompliance;

GO

-- [dbo].[vJobIntegrityDashboard]

CREATE   VIEW dbo.vJobIntegrityDashboard
AS
SELECT
    SQLInstance,
    JobName,
    ScheduleName,
    IsScheduled,
    IsScheduleEnabled,
    IsJobEnabled,
    HasDuplicateSched,
    LastRunDate,
    LastRunStatus,
    RunDuration,
    NextRunDate,
    IssueDetected,
    CaptureDate,

    -- Health Flags
    CASE 
        WHEN IssueDetected = 'Healthy' THEN 1
        ELSE 0
    END AS IsHealthy,

    CASE 
        WHEN IssueDetected IN (
            'Schedule Disabled',
            'Job Disabled',
            'Duplicate Schedule Detected',
            'Last Run Failed',
            'Job Has No Schedule'
        ) THEN 1
        ELSE 0
    END AS IsCritical,

    CASE 
        WHEN LastRunDate < DATEADD(HOUR,-12,GETDATE()) THEN 1
        ELSE 0
    END AS IsStale
FROM dbo.JobIntegrityAudit
WHERE CaptureDate = (
        SELECT MAX(CaptureDate)
        FROM dbo.JobIntegrityAudit AS J2
        WHERE J2.SQLInstance = JobIntegrityAudit.SQLInstance
          AND J2.JobName = JobIntegrityAudit.JobName
      );

GO

-- [dbo].[vJobScheduleCompliance]

CREATE   VIEW dbo.vJobScheduleCompliance AS
SELECT 
    js.JobName,
    js.ScheduleName AS ExpectedSchedule,
    ms.actualSchedule,
    ms.schedule_id,
    ms.enabled,
    CASE 
        WHEN ms.schedule_id IS NULL THEN 'Missing Schedule'
        WHEN ms.enabled = 0 THEN 'Disabled'
        WHEN js.ScheduleName <> ms.actualSchedule THEN 'Schedule Drift'
        ELSE 'Compliant'
    END AS ComplianceStatus,
    GETDATE() AS CaptureDate
FROM dbo.DBAOps_JobScheduleMap js
LEFT JOIN (
    SELECT 
        j.name AS JobName,
        s.name AS actualSchedule,
        s.schedule_id,
        s.enabled
    FROM msdb.dbo.sysjobs j
    LEFT JOIN msdb.dbo.sysjobschedules jsched 
        ON j.job_id = jsched.job_id
    LEFT JOIN msdb.dbo.sysschedules s 
        ON jsched.schedule_id = s.schedule_id
) ms ON js.JobName = ms.JobName;

GO

-- [dbo].[vJobScheduleDriftDashboard]

CREATE   VIEW dbo.vJobScheduleDriftDashboard AS
SELECT TOP (100) PERCENT
    DriftID,
    JobName,
    ExpectedSchedule,
    ActualSchedule,
    ComplianceStatus,
    HealAction,
    CaptureDate
FROM dbo.factJobScheduleDriftLog
ORDER BY DriftID DESC;

GO

-- [dbo].[vServers]

CREATE   VIEW dbo.vServers
AS
SELECT 
    ServerName AS Server,
    COALESCE(Environment, CMSEnvironment, 'Unknown') AS Environment,
    IsActive,
    SQLVersion,
    SQLEdition,
    OSVersion,
    ComplianceTier,
    CMSGroupName,
    LastSeen
FROM config.ServerInventory
WHERE IsActive = 1;

GO

-- [dbo].[vw_A2_Legacy]

CREATE VIEW dbo.vw_A2_Legacy AS
-- This view provides backward compatibility for any code still expecting A2 structure
-- It maps CMS columns to the old A2 column names
SELECT 
    cms.ServerName AS Server,
    COALESCE(cms.Environment, cms.CMSEnvironment, 'Unknown') AS Environment,
    cms.SQLVersion,
    cms.SqlBuild AS Build,
    cms.SqlCumulativeUpdate AS CU,
    cms.SQLEdition AS Edition,
    cms.ComplianceTier AS Tier,
    cms.AppCategory AS Category,
    cms.IsActive AS Active,
    cms.LastSeen AS LastUpdated,
    cms.OSVersion,
    cms.TotalMemoryGB AS Memory,
    cms.CPUCount AS CPU,
    cms.IsVM,
    cms.IsClustered,
    cms.ServerType
FROM config.ServerInventory cms
WHERE cms.IsActive = 1;

GO

-- [dbo].[vw_AuditLog_Recent]

CREATE VIEW dbo.vw_AuditLog_Recent
AS
SELECT TOP 1000
    *
FROM audit.AuditLog
ORDER BY 1 DESC;

GO

-- [dbo].[vw_BackupCompliance_Current]

CREATE VIEW dbo.vw_BackupCompliance_Current
AS
SELECT 
    ServerName,
    DatabaseName,
    BackupType,
    LastBackupTime,
    IsCompliant,
    DATEDIFF(HOUR, LastBackupTime, GETDATE()) AS HoursSinceBackup
FROM fact.BackupCompliance
WHERE LastBackupTime >= DATEADD(DAY, -1, GETDATE());

GO

-- [dbo].[vw_BackupCompliance_Summary]

CREATE VIEW dbo.vw_BackupCompliance_Summary
AS
SELECT 
    CAST(LastBackupTime AS DATE) AS BackupDate,
    COUNT(*) AS TotalDatabases,
    SUM(CASE WHEN IsCompliant = 1 THEN 1 ELSE 0 END) AS CompliantCount,
    SUM(CASE WHEN IsCompliant = 0 THEN 1 ELSE 0 END) AS NonCompliantCount,
    CAST(SUM(CASE WHEN IsCompliant = 1 THEN 1.0 ELSE 0 END) / COUNT(*) * 100 AS DECIMAL(5,2)) AS CompliancePercent
FROM fact.BackupCompliance
WHERE LastBackupTime >= DATEADD(DAY, -7, GETDATE())
GROUP BY CAST(LastBackupTime AS DATE);

GO

-- [dbo].[vw_CMS_Servers]

CREATE   VIEW dbo.vw_CMS_Servers
AS
SELECT 
    RS.server_name AS Server,
    RS.server_name AS ServerName,
    RS.description AS Notes,
    RG.name AS CMSGroup,
    CASE 
        WHEN RG.name LIKE '%PROD%' OR RG.name LIKE '%HIGH%' THEN 'PROD'
        WHEN RG.name LIKE '%DEV%' OR RG.name LIKE '%TEST%' OR RG.name LIKE '%NONPROD%' THEN 'NON-PROD'
        ELSE 'PROD'
    END AS Environment,
    1 AS IsActive,
    1 AS IsSQLServer
FROM msdb.dbo.sysmanagement_shared_registered_servers_internal AS RS
INNER JOIN msdb.dbo.sysmanagement_shared_server_groups_internal AS RG
    ON RS.server_group_id = RG.server_group_id;

GO

-- [dbo].[vw_DatabaseGrowthDaily]
CREATE OR ALTER VIEW dbo.vw_DatabaseGrowthDaily AS
SELECT 
    ServerName,
    DatabaseName,
    CONVERT(date, CaptureTime) AS CaptureDate,
    AVG(SizeMB) AS SizeMB
FROM dbo.fact_DatabaseInventory
GROUP BY 
    ServerName,
    DatabaseName,
    CONVERT(date, CaptureTime);

GO

-- [dbo].[vw_DatabaseInventory_History]
CREATE OR ALTER VIEW dbo.vw_DatabaseInventory_History AS
SELECT 
    ServerName,
    DatabaseName,
    StateDesc,
    RecoveryModel,
    CompatibilityLevel,
    SizeMB,
    CreateDate,
    CaptureTime,
    CONVERT(date, CaptureTime) AS CaptureDate
FROM dbo.fact_DatabaseInventory;

GO

-- [dbo].[vw_DBAOps_DashboardFailures]
CREATE OR ALTER VIEW dbo.vw_DBAOps_DashboardFailures
AS
SELECT *
FROM dbo.vw_DBAOps_DashboardLatest
WHERE Status = 'FAIL';

GO

-- [dbo].[vw_DBAOps_DashboardLatest]
CREATE OR ALTER VIEW dbo.vw_DBAOps_DashboardLatest
AS
WITH x AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY ServerName, InstanceName, Category, MetricName 
                              ORDER BY CollectionDate DESC) AS rn
    FROM dbo.DBAOps_ValidationDashboard
)
SELECT *
FROM x
WHERE rn = 1;

GO

-- [dbo].[vw_DBAOps_DashboardSummary]

CREATE   VIEW dbo.vw_DBAOps_DashboardSummary
AS
SELECT 
    COUNT(*) AS TotalServers,
    SUM(CASE WHEN IsActive = 1 THEN 1 ELSE 0 END) AS ActiveServers,
    SUM(CASE WHEN IsActive = 0 THEN 1 ELSE 0 END) AS InactiveServers,
    SUM(CASE WHEN COALESCE(Environment, CMSEnvironment) = 'Production' THEN 1 ELSE 0 END) AS ProductionServers,
    SUM(CASE WHEN COALESCE(Environment, CMSEnvironment) <> 'Production' OR Environment IS NULL THEN 1 ELSE 0 END) AS NonProductionServers,
    SUM(CASE WHEN SQLVersion LIKE '17.%' THEN 1 ELSE 0 END) AS SQL2025Count,
    SUM(CASE WHEN SQLVersion LIKE '16.%' THEN 1 ELSE 0 END) AS SQL2022Count,
    SUM(CASE WHEN SQLVersion LIKE '15.%' THEN 1 ELSE 0 END) AS SQL2019Count,
    SUM(CASE WHEN SQLVersion LIKE '14.%' THEN 1 ELSE 0 END) AS SQL2017Count,
    SUM(CASE WHEN SQLVersion LIKE '13.%' THEN 1 ELSE 0 END) AS SQL2016Count,
    SUM(CASE WHEN SQLVersion LIKE '12.%' OR SQLVersion LIKE '11.%' OR SQLVersion LIKE '10.%' THEN 1 ELSE 0 END) AS LegacyCount
FROM config.ServerInventory
WHERE (ServerType = 'SQL Server' OR ServerType IS NULL);

GO

-- [dbo].[vw_DBAOpsServerList]

CREATE   VIEW dbo.vw_DBAOpsServerList
AS
SELECT 
    ServerName AS Server,
    InstanceName,
    COALESCE(Environment, CMSEnvironment, 'Unknown') AS Environment,
    SQLVersion,
    SQLEdition,
    OSVersion,
    CPUCount,
    TotalMemoryGB,
    IsActive,
    IsVM,
    IsClustered,
    ComplianceTier,
    CMSGroupName,
    CMSCriticality,
    LastSeen,
    LastInventoryUpdate
FROM config.ServerInventory
WHERE (ServerType = 'SQL Server' OR ServerType IS NULL);

GO

-- [dbo].[vw_FactServerHealthReadable]
CREATE OR ALTER VIEW dbo.vw_FactServerHealthReadable AS
SELECT
    ServerName,
    CaptureTime,
    ISNULL(CAST(CPU_Utilization AS VARCHAR(20)), 'Not Supported') AS CPU_Utilization,
    ISNULL(CAST(CPU_SignalWaitPct AS VARCHAR(20)), 'Not Supported') AS CPU_SignalWaitPct,
    ISNULL(CAST(RunnableTasksCount AS VARCHAR(20)), 'Not Supported') AS RunnableTasksCount,
    ISNULL(CAST(Memory_PLE AS VARCHAR(20)), 'Not Supported') AS Memory_PLE,
    ISNULL(CAST(Memory_GrantsPending AS VARCHAR(20)), 'Not Supported') AS Memory_GrantsPending,
    ISNULL(CAST(Memory_TotalMB AS VARCHAR(20)), 'Not Supported') AS Memory_TotalMB,
    ISNULL(CAST(Memory_UsedMB AS VARCHAR(20)), 'Not Supported') AS Memory_UsedMB,
    ISNULL(CAST(TempDB_UsageMB AS VARCHAR(20)), 'Not Supported') AS TempDB_UsageMB,
    ISNULL(CAST(TempDB_VersionStoreMB AS VARCHAR(20)), 'Not Supported') AS TempDB_VersionStoreMB,
    ISNULL(CAST(ActiveSessions AS VARCHAR(20)), 'Not Supported') AS ActiveSessions,
    ISNULL(CAST(BlockingSessions AS VARCHAR(20)), 'Not Supported') AS BlockingSessions,
    ISNULL(CAST(LongRunningRequests AS VARCHAR(20)), 'Not Supported') AS LongRunningRequests,
    ISNULL(CAST(ErrorLog_CriticalCount AS VARCHAR(20)), 'Not Supported') AS ErrorLog_CriticalCount,
    InstanceVersion,
    Edition,
    ISNULL(CAST(UptimeHours AS VARCHAR(20)), 'Not Supported') AS UptimeHours,
    ISNULL(CAST(BackupAge_Full_Hours AS VARCHAR(20)), 'Not Supported') AS BackupAge_Full_Hours,
    ISNULL(CAST(BackupAge_Diff_Hours AS VARCHAR(20)), 'Not Supported') AS BackupAge_Diff_Hours,
    ISNULL(CAST(BackupAge_Log_Hours AS VARCHAR(20)), 'Not Supported') AS BackupAge_Log_Hours
FROM dbo.FACT_ServerHealth;

GO

-- [dbo].[vw_FailedJobs_Last24Hours]

CREATE VIEW dbo.vw_FailedJobs_Last24Hours
AS
SELECT 
    j.name AS JobName,
    msdb.dbo.agent_datetime(jh.run_date, jh.run_time) AS FailureTime,
    jh.message AS ErrorMessage
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id
WHERE jh.step_id = 0
AND jh.run_status = 0
AND msdb.dbo.agent_datetime(jh.run_date, jh.run_time) >= DATEADD(HOUR, -24, GETDATE())
AND j.enabled = 1;

GO

-- [dbo].[vw_FrameworkHealth_Dashboard]

CREATE VIEW dbo.vw_FrameworkHealth_Dashboard
AS
SELECT 
    'Total Servers' AS Metric,
    CAST(COUNT(*) AS VARCHAR) AS Value
FROM config.ServerInventory WHERE IsActive = 1
UNION ALL
SELECT 'PROD Servers', CAST(COUNT(*) AS VARCHAR) FROM config.ServerInventory WHERE Environment = 'PROD' AND IsActive = 1
UNION ALL
SELECT 'NONPROD Servers', CAST(COUNT(*) AS VARCHAR) FROM config.ServerInventory WHERE Environment = 'NONPROD' AND IsActive = 1
UNION ALL
SELECT 'Total Jobs', CAST(COUNT(*) AS VARCHAR) FROM msdb.dbo.sysjobs WHERE name LIKE 'DBAOps%' AND enabled = 1
UNION ALL
SELECT 'Failed Jobs (24h)', CAST(COUNT(*) AS VARCHAR) FROM dbo.vw_FailedJobs_Last24Hours;

GO

-- [dbo].[vw_JobStatus_Current]

CREATE VIEW dbo.vw_JobStatus_Current
AS
SELECT 
    j.name AS JobName,
    j.enabled AS IsEnabled,
    CASE jh.run_status
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        WHEN 4 THEN 'In Progress'
        ELSE 'Never Run'
    END AS LastStatus,
    msdb.dbo.agent_datetime(jh.run_date, jh.run_time) AS LastRunTime,
    jh.run_duration AS DurationSeconds
FROM msdb.dbo.sysjobs j
LEFT JOIN (
    SELECT job_id, run_date, run_time, run_status, 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 LIKE 'DBAOps%';

GO

-- [dbo].[vw_LatestBackupStatus]

CREATE VIEW dbo.vw_LatestBackupStatus
AS
/*
    View: vw_LatestBackupStatus
    Purpose: Latest backup status for dashboard
    Author: RHOYLLE INNOVATION EMPIRE™ - DBAOps Enterprise™ v1.0
*/
SELECT 
    ServerName,
    DatabaseName,
    BackupType AS backup_type,
    BackupFinishDate AS backup_finish_date,
    CAST(ISNULL(BackupSizeBytes, 0) / 1048576.0 AS DECIMAL(18,2)) AS backup_size_mb
FROM (
    SELECT 
        ServerName,
        DatabaseName,
        BackupType,
        BackupFinishDate,
        BackupSizeBytes,
        ROW_NUMBER() OVER (
            PARTITION BY ServerName, DatabaseName, BackupType 
            ORDER BY BackupFinishDate DESC
        ) AS rn
    FROM dbo.BackupHistory
    WHERE BackupFinishDate >= DATEADD(DAY, -30, GETDATE())
      AND IsSuccessful = 1
) sub
WHERE rn = 1;

GO

-- [dbo].[vw_LatestDatabaseFiles]
CREATE OR ALTER VIEW dbo.vw_LatestDatabaseFiles AS
WITH x AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY ServerName, DatabaseName, FileLogicalName
               ORDER BY CaptureTime DESC
           ) AS rn
    FROM dbo.fact_DatabaseFiles
)
SELECT *
FROM x
WHERE rn = 1;

GO

-- [dbo].[vw_LatestDatabaseInventory]
CREATE OR ALTER VIEW dbo.vw_LatestDatabaseInventory AS
WITH latest AS (
    SELECT *,
        ROW_NUMBER() OVER (
            PARTITION BY ServerName, DatabaseName
            ORDER BY CaptureTime DESC
        ) AS rn
    FROM dbo.fact_DatabaseInventory
)
SELECT
    InventoryFactID,
    ServerName,
    DatabaseName,
    StateDesc,
    RecoveryModel,
    CompatibilityLevel,
    SizeMB,
    CreateDate,
    CaptureTime
FROM latest
WHERE rn = 1;

GO

-- [dbo].[vw_LatestDiskInventory]
CREATE OR ALTER VIEW dbo.vw_LatestDiskInventory AS
SELECT *
FROM dbo.fact_DiskInventory d
WHERE CaptureTime = (
    SELECT MAX(CaptureTime)
    FROM dbo.fact_DiskInventory d2
    WHERE d2.ServerName = d.ServerName
);

GO

-- [dbo].[vw_LatestInstanceInventory]
CREATE OR ALTER VIEW dbo.vw_LatestInstanceInventory AS
SELECT *
FROM dbo.fact_InstanceInventory fi
WHERE CaptureTime = (
    SELECT MAX(CaptureTime)
    FROM dbo.fact_InstanceInventory f2
    WHERE f2.ServerName = fi.ServerName
);

GO

-- [dbo].[vw_LatestPerformanceSnapshot]

CREATE   VIEW dbo.vw_LatestPerformanceSnapshot AS
WITH x AS (
    SELECT *,
           ROW_NUMBER() OVER (
              PARTITION BY ServerName
              ORDER BY SnapshotTime DESC
           ) AS rn
    FROM dbo.fact_PerformanceSnapshot
)
SELECT *
FROM x
WHERE rn = 1;

GO

-- [dbo].[vw_MaintenanceDashboard]

CREATE VIEW dbo.vw_MaintenanceDashboard
AS
SELECT 
    m.ServerName,
    m.Environment,
    m.LastCollectionDate,
    m.LastCollectionStatus,
    m.LastErrorMessage,
    ISNULL((
        SELECT COUNT(*) FROM dbo.CommandLog_Central c 
        WHERE c.SourceServer = m.ServerName 
          AND c.StartTime >= DATEADD(HOUR, -24, GETDATE())
    ), 0) AS Last24HrOperations,
    ISNULL((
        SELECT COUNT(*) FROM dbo.CommandLog_Central c 
        WHERE c.SourceServer = m.ServerName 
          AND c.StartTime >= DATEADD(HOUR, -24, GETDATE())
          AND c.ErrorNumber <> 0
    ), 0) AS Last24HrErrors,
    (
        SELECT MAX(c.StartTime) FROM dbo.CommandLog_Central c 
        WHERE c.SourceServer = m.ServerName 
          AND c.CommandType IN ('ALTER_INDEX', 'INDEX_REBUILD', 'INDEX_REORGANIZE')
    ) AS LastIndexOptimize,
    (
        SELECT MAX(c.StartTime) FROM dbo.CommandLog_Central c 
        WHERE c.SourceServer = m.ServerName 
          AND c.CommandType = 'UPDATE_STATISTICS'
    ) AS LastStatsUpdate,
    (
        SELECT MAX(c.StartTime) FROM dbo.CommandLog_Central c 
        WHERE c.SourceServer = m.ServerName 
          AND c.CommandType IN ('DBCC_CHECKDB', 'INTEGRITY_CHECK')
    ) AS LastIntegrityCheck,
    CASE 
        WHEN m.LastCollectionStatus = 'Error' THEN 'CRITICAL'
        WHEN m.LastCollectionStatus = 'No CommandLog' THEN 'NOT DEPLOYED'
        WHEN m.LastCollectionStatus = 'No Linked Server' THEN 'NO LINKED SERVER'
        WHEN m.LastCollectionStatus IS NULL THEN 'PENDING'
        WHEN m.LastCollectionStatus = 'Success' AND EXISTS (
            SELECT 1 FROM dbo.CommandLog_Central c 
            WHERE c.SourceServer = m.ServerName 
              AND c.StartTime >= DATEADD(DAY, -7, GETDATE())
        ) THEN 'HEALTHY'
        WHEN m.LastCollectionStatus = 'Success' THEN 'STALE'
        ELSE 'UNKNOWN'
    END AS HealthStatus
FROM dbo.MaintenanceServers m
WHERE m.IsActive = 1;

GO

-- [dbo].[vw_MaintenanceErrors]

CREATE VIEW dbo.vw_MaintenanceErrors
AS
SELECT 
    CentralID,
    SourceServer,
    DatabaseName,
    ObjectName,
    IndexName,
    CommandType,
    StartTime,
    EndTime,
    ErrorNumber,
    ErrorMessage,
    Command
FROM dbo.CommandLog_Central
WHERE ErrorNumber IS NOT NULL AND ErrorNumber <> 0;

GO

-- [dbo].[vw_PerformanceTrends_24Hours]
CREATE OR ALTER VIEW dbo.vw_PerformanceTrends_24Hours AS
SELECT *
FROM dbo.fact_PerformanceSnapshot
WHERE SnapshotTime >= DATEADD(HOUR, -24, SYSUTCDATETIME());

GO

-- [dbo].[vw_ServerHealth_Latest]
CREATE OR ALTER VIEW dbo.vw_ServerHealth_Latest AS
WITH Latest AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY ServerName ORDER BY CaptureTime DESC) rn
    FROM dbo.vw_ServerHealth_Severity
)
SELECT *
FROM Latest
WHERE rn = 1;

GO

-- [dbo].[vw_ServerHealth_Severity]

CREATE   VIEW dbo.vw_ServerHealth_Severity AS
SELECT
    ServerName,
    CaptureTime,

    CPU_SignalWaitPct,
    RunnableTasksCount,
    Memory_PLE,
    Memory_GrantsPending,
    TempDB_UsageMB,

    Disk_Latency_ReadMS,
    Disk_Latency_WriteMS,
    Disk_FreeSpaceMB,

    BlockingSessions,
    BackupAge_Log_Hours,
    InstanceVersion,
    Edition,

    -- Severity logic
    CASE 
        WHEN CPU_SignalWaitPct > 25 THEN 'RED'
        WHEN RunnableTasksCount > 4 THEN 'RED'
        WHEN Memory_PLE < 300 THEN 'RED'
        WHEN Memory_GrantsPending > 0 THEN 'RED'
        WHEN Disk_Latency_ReadMS > 20 OR Disk_Latency_WriteMS > 20 THEN 'RED'
        WHEN Disk_FreeSpaceMB < 2048 THEN 'RED'
        WHEN BackupAge_Log_Hours > 2 THEN 'RED'

        WHEN CPU_SignalWaitPct BETWEEN 15 AND 25 THEN 'YELLOW'
        WHEN RunnableTasksCount BETWEEN 2 AND 4 THEN 'YELLOW'
        WHEN Memory_PLE BETWEEN 300 AND 600 THEN 'YELLOW'
        WHEN Disk_Latency_ReadMS BETWEEN 10 AND 20 THEN 'YELLOW'
        WHEN Disk_Latency_WriteMS BETWEEN 10 AND 20 THEN 'YELLOW'
        WHEN Disk_FreeSpaceMB BETWEEN 2048 AND 5120 THEN 'YELLOW'
        WHEN BackupAge_Log_Hours BETWEEN 1 AND 2 THEN 'YELLOW'
        
        ELSE 'GREEN'
    END AS Severity
FROM dbo.FACT_ServerHealth;

GO

-- [dbo].[vw_ServerHealth_Trend30Day]
CREATE OR ALTER VIEW dbo.vw_ServerHealth_Trend30Day AS
SELECT
    ServerName,
    CaptureTime,
    CPU_SignalWaitPct,
    RunnableTasksCount,
    Memory_PLE,
    TempDB_UsageMB,
    Disk_Latency_ReadMS,
    Disk_Latency_WriteMS,
    BlockingSessions,
    BackupAge_Log_Hours
FROM dbo.FACT_ServerHealth
WHERE CaptureTime >= DATEADD(DAY, -30, GETDATE());

GO

-- [dbo].[vw_ServerHealth_Trend7Day]
CREATE OR ALTER VIEW dbo.vw_ServerHealth_Trend7Day AS
SELECT
    ServerName,
    CaptureTime,
    CPU_SignalWaitPct,
    RunnableTasksCount,
    Memory_PLE,
    TempDB_UsageMB,
    Disk_Latency_ReadMS,
    Disk_Latency_WriteMS,
    BlockingSessions,
    BackupAge_Log_Hours
FROM dbo.FACT_ServerHealth
WHERE CaptureTime >= DATEADD(DAY, -7, GETDATE());

GO

-- [dbo].[vw_ServerInventory_Summary]

CREATE VIEW dbo.vw_ServerInventory_Summary
AS
SELECT 
    Environment,
    COUNT(*) AS ServerCount,
    SUM(CASE WHEN IsActive = 1 THEN 1 ELSE 0 END) AS ActiveServers,
    SUM(CASE WHEN IsActive = 0 THEN 1 ELSE 0 END) AS InactiveServers,
    MAX(LastSeen) AS LastUpdated
FROM config.ServerInventory
GROUP BY Environment;

GO

-- [dbo].[vw_SQLServer_EOS_Status]
CREATE OR ALTER VIEW [dbo].[vw_SQLServer_EOS_Status]
AS
SELECT 
    p.ServerName,
    ISNULL(CAST(NULL AS VARCHAR(128)), '') AS InstanceName,
    ISNULL(p.SQLVersion, 'Unknown') AS SQLVersion,
    CASE 
        WHEN p.SQLVersion LIKE '%2012%' THEN 11
        WHEN p.SQLVersion LIKE '%2014%' THEN 12
        WHEN p.SQLVersion LIKE '%2016%' THEN 13
        WHEN p.SQLVersion LIKE '%2017%' THEN 14
        WHEN p.SQLVersion LIKE '%2019%' THEN 15
        WHEN p.SQLVersion LIKE '%2022%' THEN 16
        WHEN p.SQLVersion LIKE '%2025%' THEN 17
        ELSE 0
    END AS SQLVersionMajor,
    ISNULL(CAST(NULL AS VARCHAR(50)), '') AS SQLBuild,
    ISNULL(p.CurrentCU, 'Unknown') AS SQLCumulativeUpdate,
    ISNULL(CAST(NULL AS VARCHAR(100)), '') AS Edition,
    ISNULL(CAST(NULL AS VARCHAR(100)), '') AS WindowsVersion,
    CAST(0 AS BIT) AS IsClustered,
    CAST(0 AS BIT) AS IsAGNode,
    p.SupportEnds AS EndOfSupport,
    ISNULL(p.DaysUntilEOS, 0) AS DaysUntilEOS,
    CASE 
        WHEN p.SupportStatus = 'UNKNOWN' THEN 'UNKNOWN'
        WHEN p.DaysUntilEOS IS NULL THEN 'UNKNOWN'
        WHEN p.DaysUntilEOS < 0 THEN 'END OF LIFE'
        WHEN p.DaysUntilEOS <= 90 THEN 'CRITICAL (<90 days)'
        WHEN p.DaysUntilEOS <= 180 THEN 'HIGH (<180 days)'
        WHEN p.DaysUntilEOS <= 365 THEN 'MEDIUM (<1 year)'
        WHEN p.DaysUntilEOS <= 730 THEN 'LOW (<2 years)'
        ELSE 'OK - Supported'
    END AS UrgencyLevel,
    ISNULL(p.SupportStatus, 'Unknown') AS SupportStatus,
    ISNULL(p.TargetCU, 'Unknown') AS LatestCU,
    ISNULL(CAST(NULL AS VARCHAR(50)), '') AS LatestBuildNumber,
    CASE 
        WHEN p.SupportStatus = 'UNKNOWN' THEN 'Validate SQL version data'
        WHEN p.DaysUntilEOS IS NULL THEN 'Check SQL version compatibility'
        WHEN p.DaysUntilEOS < 0 THEN 'URGENT: Upgrade to SQL Server 2019 or 2022 immediately'
        WHEN p.DaysUntilEOS <= 180 THEN 'Plan upgrade to SQL Server 2022 within 3 months'
        WHEN p.DaysUntilEOS <= 365 THEN 'Schedule upgrade to SQL Server 2022 this year'
        ELSE 'No immediate action required - continue patching'
    END AS RecommendedAction,
    ISNULL(p.Environment, 'Unknown') AS Environment,
    ISNULL(p.PatchStatus, 'Unknown') AS PatchStatus,
    CASE 
        WHEN p.DaysUntilEOS < 0 THEN '[X]'
        WHEN p.DaysUntilEOS <= 90 THEN '[!]'
        WHEN p.DaysUntilEOS <= 365 THEN '[~]'
        ELSE '[OK]'
    END AS StatusIcon,
    ISNULL(p.StatusColor, 'gray') AS StatusColor
FROM Patching.vw_PatchComplianceDetail p;

GO

-- [dbo].[vw_SQLServer_EOS_Summary]

CREATE   VIEW [dbo].[vw_SQLServer_EOS_Summary] AS
SELECT 
    br.SQLVersionName,
    br.SQLMajorVersion,
    COUNT(*) AS ServerCount,
    br.EndOfSupport,
    DATEDIFF(DAY, GETDATE(), br.EndOfSupport) AS DaysUntilEOS,
    br.IsSupported,
    CASE 
        WHEN br.IsSupported = 0 THEN 'End of Life'
        WHEN DATEDIFF(DAY, GETDATE(), br.EndOfSupport) <= 180 THEN 'Critical'
        WHEN DATEDIFF(DAY, GETDATE(), br.EndOfSupport) <= 365 THEN 'Warning'
        ELSE 'Supported'
    END AS SupportStatus,
    CASE 
        WHEN br.IsSupported = 0 THEN 'EOL'
        WHEN DATEDIFF(DAY, GETDATE(), br.EndOfSupport) <= 180 THEN 'Critical'
        WHEN DATEDIFF(DAY, GETDATE(), br.EndOfSupport) <= 365 THEN 'Warning'
        ELSE 'OK'
    END AS Status
FROM config.ServerInventory cms
INNER JOIN Patching.SQLServerBuildReference br 
    ON cms.SQLVersion LIKE CAST(br.SQLMajorVersion AS VARCHAR) + '.%'
WHERE cms.IsActive = 1
AND (cms.ServerType = 'SQL Server' OR cms.ServerType IS NULL)
GROUP BY br.SQLVersionName, br.SQLMajorVersion, br.EndOfSupport, br.IsSupported;

GO

-- [deployment].[vw_ExistingObjectsPreserved]

CREATE VIEW deployment.vw_ExistingObjectsPreserved AS
SELECT 
    bl.ServerName,
    bl.LoginName,
    bl.LoginType,
    'Will be PRESERVED - not managed by DBAOps' AS Action
FROM baseline.ServerLogins bl
WHERE bl.CaptureDate = (
    SELECT MAX(CaptureDate) 
    FROM baseline.ServerLogins 
    WHERE ServerName = bl.ServerName
)
AND bl.LoginName NOT IN (
    SELECT ADGroupName FROM security.ADGroups
)
AND bl.IsSystemObject = 0;

GO

-- [deployment].[vw_MissingLogins]

CREATE   VIEW deployment.vw_MissingLogins
AS
SELECT 
    ac.ServerName,
    ac.ComplianceTier,
    ac.ADGroupName,
    ac.AccessLevel
FROM security.vw_ServerAccessControl ac
LEFT JOIN baseline.ServerLogins sl ON 
    ac.ServerName = sl.ServerName 
    AND ac.ADGroupName = sl.LoginName
WHERE sl.LoginName IS NULL;

GO

-- [deployment].[vw_MissingRoleMemberships]

CREATE VIEW deployment.vw_MissingRoleMemberships AS
SELECT 
    ac.ServerName,
    ac.ComplianceTier,
    ac.ADGroupName,
    ac.DesiredServerRole,
    'Role membership missing - NEEDS ASSIGNMENT' AS Action
FROM security.vw_ServerAccessControl ac
WHERE NOT EXISTS (
    SELECT 1
    FROM baseline.ServerRoleMembers brm
    WHERE brm.ServerName = ac.ServerName
      AND brm.MemberName = ac.ADGroupName
      AND brm.RoleName = ac.DesiredServerRole
      AND brm.CaptureDate = (
          SELECT MAX(CaptureDate) 
          FROM baseline.ServerRoleMembers 
          WHERE ServerName = brm.ServerName
      )
)
AND EXISTS (
    SELECT 1
    FROM baseline.ServerLogins bl
    WHERE bl.ServerName = ac.ServerName
      AND bl.LoginName = ac.ADGroupName
      AND bl.CaptureDate = (
          SELECT MAX(CaptureDate) 
          FROM baseline.ServerLogins 
          WHERE ServerName = bl.ServerName
      )
);

GO

-- [dim].[vw_DIM_Server]

CREATE VIEW dim.vw_DIM_Server AS
SELECT 
    ServerID,
    ServerName,
    Environment,
    ServerID AS ServerKey  -- Legacy column name some code might expect
FROM dim.Server;

GO

-- [dim].[vw_Servers]

CREATE VIEW dim.vw_Servers AS
SELECT * FROM dim.Server;

GO

-- [etl].[vw_ComplianceSummary]

CREATE VIEW etl.vw_ComplianceSummary AS
SELECT 
    Framework,
    COUNT(*) AS TotalControls,
    SUM(CASE WHEN ComplianceStatus = 'PASS' THEN 1 ELSE 0 END) AS PassCount,
    SUM(CASE WHEN ComplianceStatus = 'FAIL' THEN 1 ELSE 0 END) AS FailCount,
    SUM(CASE WHEN ComplianceStatus = 'WAIVED' THEN 1 ELSE 0 END) AS WaivedCount,
    SUM(CASE WHEN ComplianceStatus = 'NOT_APPLICABLE' THEN 1 ELSE 0 END) AS NACount,
    CAST(SUM(CASE WHEN ComplianceStatus = 'PASS' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS DECIMAL(5,2)) AS CompliancePercentage,
    MAX(LastChecked) AS LastAssessmentDate,
    STRING_AGG(
        CASE WHEN ComplianceStatus = 'FAIL' THEN ControlID ELSE NULL END, 
        ', '
    ) AS FailedControls
FROM etl.dim_ComplianceMetrics
GROUP BY Framework;

GO

-- [etl].[vw_ETL_Dashboard_KPIs]

CREATE VIEW etl.vw_ETL_Dashboard_KPIs AS
SELECT 
    -- Server Counts
    (SELECT COUNT(*) FROM etl.dim_ETL_Servers WHERE IsActive = 1) AS TotalETLServers,
    (SELECT COUNT(*) FROM etl.dim_ETL_Servers WHERE IsActive = 1 AND ETLJobCount > 0) AS ServersWithJobs,
    (SELECT COUNT(*) FROM etl.dim_ETL_Servers WHERE IsActive = 1 AND HasSSRS = 1) AS SSRSServers,
    
    -- Job Metrics (Last 24 hours)
    (SELECT COUNT(DISTINCT JobName) FROM etl.fact_JobExecutionHistory 
     WHERE CAST(CAST(RunDate AS VARCHAR(8)) + ' ' + 
           STUFF(STUFF(RIGHT('000000' + CAST(RunTime AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':')
           AS DATETIME2) >= DATEADD(HOUR, -24, GETDATE())) AS JobsExecuted24h,
    
    (SELECT COUNT(*) FROM etl.fact_JobExecutionHistory 
     WHERE CAST(CAST(RunDate AS VARCHAR(8)) + ' ' + 
           STUFF(STUFF(RIGHT('000000' + CAST(RunTime AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':')
           AS DATETIME2) >= DATEADD(HOUR, -24, GETDATE()) 
     AND RunStatus = 1) AS JobsSucceeded24h,
    
    (SELECT COUNT(*) FROM etl.fact_JobExecutionHistory 
     WHERE CAST(CAST(RunDate AS VARCHAR(8)) + ' ' + 
           STUFF(STUFF(RIGHT('000000' + CAST(RunTime AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':')
           AS DATETIME2) >= DATEADD(HOUR, -24, GETDATE()) 
     AND RunStatus = 0) AS JobsFailed24h,
    
    (SELECT COUNT(*) FROM etl.fact_JobExecutionHistory 
     WHERE CAST(CAST(RunDate AS VARCHAR(8)) + ' ' + 
           STUFF(STUFF(RIGHT('000000' + CAST(RunTime AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':')
           AS DATETIME2) >= DATEADD(HOUR, -24, GETDATE()) 
     AND RunStatus = 0 AND ReviewedDate IS NULL) AS FailuresUnreviewed,
    
    -- SSRS Metrics (Last 24 hours)
    (SELECT COUNT(*) FROM etl.fact_SSRS_AccessLog 
     WHERE TimeStart >= DATEADD(HOUR, -24, GETDATE())) AS ReportsRendered24h,
    
    (SELECT COUNT(*) FROM etl.fact_SSRS_AccessLog 
     WHERE TimeStart >= DATEADD(HOUR, -24, GETDATE()) 
     AND Status <> 'rsSuccess') AS ReportsFailed24h,
    
    (SELECT COUNT(*) FROM etl.fact_SSRS_AccessLog 
     WHERE TimeStart >= DATEADD(HOUR, -24, GETDATE()) 
     AND ContainsPII = 1) AS ReportsWithPII24h,
    
    -- Security Events (Last 24 hours)
    (SELECT COUNT(*) FROM etl.fact_SecurityEvents 
     WHERE EventTime >= DATEADD(HOUR, -24, GETDATE()) 
     AND Severity = 'CRITICAL') AS CriticalEvents24h,
    
    (SELECT COUNT(*) FROM etl.fact_SecurityEvents 
     WHERE EventTime >= DATEADD(HOUR, -24, GETDATE()) 
     AND Severity = 'WARNING') AS WarningEvents24h,
    
    (SELECT COUNT(*) FROM etl.fact_SecurityEvents 
     WHERE ResponseRequired = 1 AND ResponseTime IS NULL) AS OpenSecurityEvents,
    
    -- Compliance
    (SELECT AVG(CompliancePercentage) FROM etl.vw_ComplianceSummary) AS OverallCompliancePercentage,
    (SELECT SUM(FailCount) FROM etl.vw_ComplianceSummary) AS TotalFailedControls;

GO

-- [etl].[vw_FailedJobsSummary]

CREATE VIEW etl.vw_FailedJobsSummary AS
SELECT 
    ServerName,
    JobName,
    COUNT(*) AS FailureCount,
    MAX(CAST(CAST(RunDate AS VARCHAR(8)) + ' ' + 
        STUFF(STUFF(RIGHT('000000' + CAST(RunTime AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':')
        AS DATETIME2)) AS LastFailure,
    MIN(CAST(CAST(RunDate AS VARCHAR(8)) + ' ' + 
        STUFF(STUFF(RIGHT('000000' + CAST(RunTime AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':')
        AS DATETIME2)) AS FirstFailure,
    SUM(CASE WHEN ReviewedDate IS NOT NULL THEN 1 ELSE 0 END) AS ReviewedCount,
    SUM(CASE WHEN ReviewedDate IS NULL THEN 1 ELSE 0 END) AS UnreviewedCount
FROM etl.fact_JobExecutionHistory
WHERE RunStatus = 0
  AND CAST(CAST(RunDate AS VARCHAR(8)) + ' ' + 
      STUFF(STUFF(RIGHT('000000' + CAST(RunTime AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':')
      AS DATETIME2) >= DATEADD(DAY, -7, GETDATE())
GROUP BY ServerName, JobName
HAVING SUM(CASE WHEN ReviewedDate IS NULL THEN 1 ELSE 0 END) > 0
;

GO

-- [etl].[vw_SecurityEventsSummary]

CREATE VIEW etl.vw_SecurityEventsSummary AS
SELECT 
    Severity,
    EventCategory,
    COUNT(*) AS EventCount,
    SUM(CASE WHEN ResponseRequired = 1 AND ResponseTime IS NULL THEN 1 ELSE 0 END) AS OpenEvents,
    SUM(CASE WHEN ResponseRequired = 1 AND ResponseTime IS NOT NULL THEN 1 ELSE 0 END) AS ClosedEvents,
    AVG(CASE 
        WHEN ResponseRequired = 1 AND ResponseTime IS NOT NULL 
        THEN DATEDIFF(MINUTE, EventTime, ResponseTime) 
    END) AS AvgResponseMinutes,
    MAX(EventTime) AS MostRecentEvent
FROM etl.fact_SecurityEvents
WHERE EventTime >= DATEADD(DAY, -30, GETDATE())
GROUP BY Severity, EventCategory;

GO

-- [fact].[vw_DiskSpaceMonitor]

CREATE   VIEW fact.vw_DiskSpaceMonitor AS
SELECT 
    ServerName,
    DriveLetter,
    VolumeLabel,
    TotalSpaceGB,
    FreeSpaceGB,
    FreePercent,
    CaptureDate,
    CASE
        WHEN FreePercent < 10 THEN 'CRITICAL'
        WHEN FreePercent < 20 THEN 'WARNING'
        ELSE 'HEALTHY'
    END AS HealthStatus
FROM fact.DiskSpaceMetrics;

GO

-- [inventory].[vw_Dashboard_SqlBuildIntelligence_Base]

CREATE   VIEW inventory.vw_Dashboard_SqlBuildIntelligence_Base
AS
SELECT
    cms.ServerName,
    cms.CMSEnvironment,
    cms.CMSGroupName,
    si.SQLVersion,
    si.SqlBuild,
    si.SqlCumulativeUpdate,
    si.SQLEdition,
    si.WindowsVersion,
    si.WindowsBuild,
    si.LastSeen,
    si.LastInventoryUpdate,
    CASE
        WHEN si.SqlBuild IS NULL OR LTRIM(RTRIM(si.SqlBuild)) = '' THEN 1
        ELSE 0
    END AS IsMissingBuildDetail
FROM config.vw_CMS_SQLServerInventory_Current cms
LEFT JOIN config.ServerInventory si
    ON si.ServerName = cms.ServerName
   AND si.IsActive = 1;

GO

-- [Logging].[vw_LogSummary]

-- ============================================================================
-- View: Logging.vw_LogSummary
-- Summary statistics for dashboard
-- ============================================================================
CREATE   VIEW Logging.vw_LogSummary
AS
WITH Last24Hours AS (
    SELECT 
        LogLevel,
        COUNT(*) AS LogCount
    FROM Logging.OperationLog
    WHERE LogTimestamp >= DATEADD(HOUR, -24, SYSDATETIME())
    GROUP BY LogLevel
),
AlertStats AS (
    SELECT 
        COUNT(*) AS TotalAlerts,
        SUM(CASE WHEN DeliveryStatus = 'Sent' THEN 1 ELSE 0 END) AS SentAlerts,
        SUM(CASE WHEN DeliveryStatus = 'Failed' THEN 1 ELSE 0 END) AS FailedAlerts,
        SUM(CASE WHEN Acknowledged = 0 AND Severity = 'Critical' THEN 1 ELSE 0 END) AS UnacknowledgedCritical
    FROM Logging.AlertLog
    WHERE AlertTimestamp >= DATEADD(HOUR, -24, SYSDATETIME())
),
ErrorStats AS (
    SELECT COUNT(*) AS ErrorCount
    FROM Logging.ErrorLog
    WHERE ErrorTimestamp >= DATEADD(HOUR, -24, SYSDATETIME())
)
SELECT 
    COALESCE((SELECT LogCount FROM Last24Hours WHERE LogLevel = 'INFO'), 0) AS InfoLogs24h,
    COALESCE((SELECT LogCount FROM Last24Hours WHERE LogLevel = 'WARN'), 0) AS WarnLogs24h,
    COALESCE((SELECT LogCount FROM Last24Hours WHERE LogLevel = 'ERROR'), 0) AS ErrorLogs24h,
    COALESCE((SELECT LogCount FROM Last24Hours WHERE LogLevel = 'CRITICAL'), 0) AS CriticalLogs24h,
    a.TotalAlerts AS Alerts24h,
    a.SentAlerts,
    a.FailedAlerts,
    a.UnacknowledgedCritical,
    e.ErrorCount AS Errors24h
FROM AlertStats a
CROSS JOIN ErrorStats e;

GO

-- [Logging].[vw_RecentActivity]

-- ============================================================================
-- View: Logging.vw_RecentActivity
-- Dashboard view for recent operations
-- ============================================================================
CREATE   VIEW Logging.vw_RecentActivity
AS
SELECT TOP 1000
    LogID,
    LogTimestamp,
    LogLevel,
    Category,
    Operation,
    ServerName,
    DatabaseName,
    Message,
    DurationMS,
    RowsAffected,
    SourceJob,
    SourceScript,
    CASE LogLevel
        WHEN 'CRITICAL' THEN '#dc3545'
        WHEN 'ERROR' THEN '#dc3545'
        WHEN 'WARN' THEN '#ffc107'
        WHEN 'INFO' THEN '#28a745'
        WHEN 'DEBUG' THEN '#6c757d'
        ELSE '#6c757d'
    END AS LevelColor
FROM Logging.OperationLog
ORDER BY LogTimestamp DESC;

GO

-- [Logging].[vw_RecentAlerts]

-- ============================================================================
-- View: Logging.vw_RecentAlerts
-- Dashboard view for recent alerts
-- ============================================================================
CREATE   VIEW Logging.vw_RecentAlerts
AS
SELECT TOP 500
    AlertID,
    AlertTimestamp,
    AlertType,
    Severity,
    Category,
    Subject,
    LEFT(Message, 500) AS MessagePreview,
    ServerName,
    DatabaseName,
    DeliveryStatus,
    DeliveryTimestamp,
    RetryCount,
    Acknowledged,
    AcknowledgedBy,
    CASE Severity
        WHEN 'Critical' THEN '#dc3545'
        WHEN 'Warning' THEN '#ffc107'
        WHEN 'Info' THEN '#17a2b8'
        ELSE '#6c757d'
    END AS SeverityColor,
    CASE DeliveryStatus
        WHEN 'Sent' THEN '#28a745'
        WHEN 'Failed' THEN '#dc3545'
        WHEN 'Pending' THEN '#ffc107'
        ELSE '#6c757d'
    END AS StatusColor
FROM Logging.AlertLog
ORDER BY AlertTimestamp DESC;

GO

-- [Logging].[vw_RecentErrors]

-- ============================================================================
-- View: Logging.vw_RecentErrors
-- Dashboard view for recent errors
-- ============================================================================
CREATE   VIEW Logging.vw_RecentErrors
AS
SELECT TOP 500
    ErrorID,
    ErrorTimestamp,
    ErrorNumber,
    ErrorSeverity,
    ErrorProcedure,
    ErrorLine,
    ErrorMessage,
    ServerName,
    DatabaseName,
    UserName,
    SourceJob,
    SourceScript
FROM Logging.ErrorLog
ORDER BY ErrorTimestamp DESC;

GO

-- [meta].[vFACT_DailySummary]
CREATE OR ALTER VIEW meta.vFACT_DailySummary
AS
SELECT 
    -- Use the actual column names from the table schema:
    ServerName AS FactName,          -- Renamed the actual column to your desired alias
    CPUPercent AS [RowCount],        -- Renamed the actual column to your desired alias
    MemoryPercent AS Status,         -- Renamed the actual column to your desired alias
    TimeCaptured AS CheckedOn        -- Renamed the actual column to your desired alias (or use GETDATE())
FROM fact.ServerHealth;

GO

-- [meta].[vw_RecentThrottleActivity]

CREATE   VIEW meta.vw_RecentThrottleActivity
AS
SELECT TOP 100
    h.ServerName,
    h.ProcessName,
    h.Action,
    h.PreviousState,
    h.NewState,
    h.SQLCPUAtAction,
    h.ActionTime,
    h.ActionBy,
    h.ExecutionResult,
    DATEDIFF(MINUTE, h.ActionTime, GETDATE()) AS MinutesAgo
FROM meta.ExternalProcessThrottleHistory h
ORDER BY h.ActionTime DESC;

GO

-- [metrics].[v_LatestMetrics]

CREATE   VIEW [metrics].[v_LatestMetrics]
AS
WITH LatestCapture AS (
    SELECT
        [BaselineID],
        [ServerName],
        ROW_NUMBER() OVER (
            PARTITION BY [ServerName] 
            ORDER BY [CollectedDate] DESC, [CaptureTime] DESC
        ) AS rn
    FROM [DBAOpsRepository].[metrics].[PerformanceBaseline]
)
SELECT
    p.*
FROM [DBAOpsRepository].[metrics].[PerformanceBaseline] AS p
INNER JOIN LatestCapture AS l
    ON p.[BaselineID] = l.[BaselineID]
WHERE l.rn = 1; -- Selects only the most recent row for each server

GO

-- [metrics].[vw_FailedJobs24h]

-- =============================================
-- 2. CREATE VIEW FOR FAILED JOBS (24h)
-- =============================================
CREATE   VIEW metrics.vw_FailedJobs24h
AS
SELECT 
    ServerName,
    JobName,
    ErrorMessage,
    RunDateTime AS LastFailedTime,
    COUNT(*) OVER (PARTITION BY ServerName, JobName) AS FailureCount
FROM metrics.FACT_JobHistory
WHERE RunStatus = 0
  AND StepID > 0
  AND RunDateTime >= DATEADD(HOUR, -24, GETDATE());

GO

-- [metrics].[vwBackupCompliance]

-- Create a simple version without the backup join for now
CREATE   VIEW metrics.vwBackupCompliance
AS
SELECT 
    cms.ServerName,
    COALESCE(cms.Environment, cms.CMSEnvironment, 'Unknown') AS Environment,
    cms.ComplianceTier,
    cms.IsActive
FROM config.ServerInventory cms
WHERE cms.IsActive = 1
AND (cms.ServerType = 'SQL Server' OR cms.ServerType IS NULL);

GO

-- [metrics].[vwBackupPrediction]

CREATE   VIEW metrics.vwBackupPrediction
AS
WITH Trend AS (
    SELECT 
        ServerName,
        CaptureTime,
        BackupAge_Full_Hours,
        BackupAge_Log_Hours,
        ROW_NUMBER() OVER (PARTITION BY ServerName ORDER BY CaptureTime DESC) AS rn
    FROM metrics.FACT_ServerHealth
    WHERE CaptureTime > DATEADD(DAY, -7, SYSDATETIME())  -- 7-day trend
),
Pairs AS (
    SELECT 
        a.ServerName,
        a.BackupAge_Full_Hours AS FullAge_Today,
        b.BackupAge_Full_Hours AS FullAge_Yesterday,
        a.BackupAge_Log_Hours AS LogAge_Today,
        b.BackupAge_Log_Hours AS LogAge_Yesterday
    FROM Trend a
    LEFT JOIN Trend b 
        ON a.ServerName = b.ServerName 
        AND a.rn = b.rn - 1
    WHERE a.rn = 1
)
SELECT 
    p.ServerName,

    -- LINEAR PREDICTION FOR NEXT 24 HOURS
    p.FullAge_Today +
        (p.FullAge_Today - p.FullAge_Yesterday) AS PredictedFullAge_24h,

    p.LogAge_Today +
        (p.LogAge_Today - p.LogAge_Yesterday) AS PredictedLogAge_24h,

    -- HEALTH SCORE OUT OF 100
    CASE 
        WHEN (p.FullAge_Today - p.FullAge_Yesterday) IS NULL THEN 80
        WHEN p.FullAge_Today < 24 AND p.LogAge_Today < 1 THEN 95
        ELSE 100 - (
                (p.FullAge_Today - p.FullAge_Yesterday) * 1.5 +
                (p.LogAge_Today - p.LogAge_Yesterday) * 4
            )
    END AS PredictionHealthScore,

    -- RISK CATEGORY
    CASE
        WHEN (p.LogAge_Today + (p.LogAge_Today - p.LogAge_Yesterday)) > 8 THEN 'CRITICAL'
        WHEN (p.FullAge_Today + (p.FullAge_Today - p.FullAge_Yesterday)) > 72 THEN 'HIGH'
        WHEN (p.LogAge_Today + (p.LogAge_Today - p.LogAge_Yesterday)) > 4 THEN 'MODERATE'
        ELSE 'LOW'
    END AS PredictedRiskLevel

FROM Pairs p;

GO

-- [metrics].[vwFACT_ServerHealth]

------------------------------------------------------------
-- Create Enterprise View for FACT_ServerHealth
-- Auto-adjusted to match table columns (no ID column)
------------------------------------------------------------
CREATE   VIEW metrics.vwFACT_ServerHealth
AS
SELECT 
      ServerName,
      CaptureTime,
      CPU_Utilization,
      CPU_SignalWaitPct,
      RunnableTasksCount,
      Memory_PLE,
      Memory_GrantsPending,
      Memory_TotalMB,
      Memory_UsedMB,
      TempDB_UsageMB,
      TempDB_VersionStoreMB,

      -- Computed free memory (optional metric)
      CASE 
          WHEN Memory_TotalMB IS NOT NULL AND Memory_UsedMB IS NOT NULL 
               THEN Memory_TotalMB - Memory_UsedMB
          ELSE NULL
      END AS Memory_FreeMB,

      ActiveSessions,
      BlockingSessions,
      LongRunningQueries,
      InstanceVersion,
      Edition,
      UptimeHours,
      BackupAge_Full_Hours,
      BackupAge_Diff_Hours,
      BackupAge_Log_Hours,
      ErrorLog_CriticalCount
FROM metrics.FACT_ServerHealth;

GO

-- [metrics].[vwFACT_ServerHealth_Latest]
CREATE OR ALTER VIEW metrics.vwFACT_ServerHealth_Latest
AS
WITH Latest AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY ServerName ORDER BY CaptureTime DESC) AS rn
    FROM metrics.FACT_ServerHealth
)
SELECT *
FROM Latest
WHERE rn = 1;

GO

-- [metrics].[vwLatestServerHealth]

CREATE   VIEW metrics.vwLatestServerHealth
AS
WITH Latest AS (
    SELECT *
    FROM metrics.FACT_ServerHealth AS F
    WHERE CaptureTime = (
        SELECT MAX(CaptureTime)
        FROM metrics.FACT_ServerHealth
        WHERE ServerName = F.ServerName
    )
)
SELECT
    ServerName,
    CaptureTime,
    CPU_Utilization,
    CPU_SignalWaitPct,
    RunnableTasksCount,
    Memory_PLE,
    Memory_GrantsPending,
    Memory_TotalMB,
    Memory_UsedMB,
    (Memory_TotalMB - Memory_UsedMB) AS Memory_FreeMB,
    TempDB_UsageMB,
    TempDB_VersionStoreMB,
    ActiveSessions,
    BlockingSessions,
    LongRunningQueries,
    InstanceVersion,
    Edition,
    UptimeHours,
    BackupAge_Full_Hours,
    BackupAge_Diff_Hours,
    BackupAge_Log_Hours,
    ErrorLog_CriticalCount,

    -------------------------------------------------------------------
    -- CPU HEALTH CLASSIFICATION
    -------------------------------------------------------------------
    CASE 
        WHEN CPU_Utilization >= 95 THEN 'RED'
        WHEN CPU_Utilization BETWEEN 85 AND 94 THEN 'YELLOW'
        ELSE 'GREEN'
    END AS CPU_Health,

    -------------------------------------------------------------------
    -- MEMORY HEALTH CLASSIFICATION
    -------------------------------------------------------------------
    CASE
        WHEN Memory_PLE < 300 THEN 'RED'   -- PLE < 5 min
        WHEN Memory_PLE BETWEEN 300 AND 900 THEN 'YELLOW'
        ELSE 'GREEN'
    END AS Memory_Health,

    -------------------------------------------------------------------
    -- TEMPDB HEALTH CLASSIFICATION
    -------------------------------------------------------------------
    CASE
        WHEN TempDB_UsageMB > (Memory_TotalMB * 0.75) THEN 'RED'
        WHEN TempDB_UsageMB > (Memory_TotalMB * 0.50) THEN 'YELLOW'
        ELSE 'GREEN'
    END AS TempDB_Health,

    -------------------------------------------------------------------
    -- BACKUP COMPLIANCE
    -------------------------------------------------------------------
    CASE
        WHEN BackupAge_Full_Hours IS NULL THEN 'NO FULL BACKUP'
        WHEN BackupAge_Full_Hours > 48 THEN 'RED'
        WHEN BackupAge_Full_Hours BETWEEN 25 AND 48 THEN 'YELLOW'
        ELSE 'GREEN'
    END AS FullBackupStatus,

    CASE
        WHEN BackupAge_Log_Hours IS NULL THEN 'N/A'
        WHEN BackupAge_Log_Hours > 4 THEN 'RED'
        WHEN BackupAge_Log_Hours BETWEEN 1 AND 4 THEN 'YELLOW'
        ELSE 'GREEN'
    END AS LogBackupStatus,

    -------------------------------------------------------------------
    -- OVERALL HEALTH SCORE
    -------------------------------------------------------------------
    CASE
        WHEN (
               CPU_Utilization >= 95
            OR Memory_PLE < 300
            OR TempDB_UsageMB > (Memory_TotalMB * 0.75)
            OR BlockingSessions > 10
            OR LongRunningQueries > 50
        )
            THEN 'RED'
        WHEN (
               CPU_Utilization BETWEEN 85 AND 94
            OR Memory_PLE BETWEEN 300 AND 900
            OR TempDB_UsageMB > (Memory_TotalMB * 0.50)
            OR BlockingSessions BETWEEN 1 AND 10
            OR LongRunningQueries BETWEEN 10 AND 50
        )
            THEN 'YELLOW'
        ELSE 'GREEN'
    END AS OverallHealth

FROM Latest;

GO

-- [Monitoring].[vw_JobComplianceNonCompliant]

-- ============================================================================
-- VIEW: monitoring.vw_JobComplianceNonCompliant
-- List of non-compliant servers for dashboard alerts
-- ============================================================================
CREATE   VIEW monitoring.vw_JobComplianceNonCompliant
AS
SELECT
    cs.ServerName,
    cs.JobName,
    d.RuleName,
    d.Severity,
    cs.ComplianceStatus,
    cs.IssueDescription,
    cs.LastCheckedDate,
    DATEDIFF(HOUR, cs.LastCheckedDate, GETDATE()) AS HoursSinceLastCheck
FROM monitoring.JobComplianceCurrentStatus cs
JOIN monitoring.JobComplianceDefinitions d ON cs.DefinitionID = d.DefinitionID
WHERE cs.ComplianceStatus IN ('NonCompliant', 'FixFailed')
  AND d.IsActive = 1;

GO

-- [Monitoring].[vw_JobComplianceSummary]

-- ============================================================================
-- VIEW: monitoring.vw_JobComplianceSummary
-- Summary view for dashboard
-- ============================================================================
CREATE   VIEW monitoring.vw_JobComplianceSummary
AS
SELECT
    d.RuleName,
    d.Description AS RuleDescription,
    d.Severity,
    COUNT(DISTINCT cs.ServerName) AS TotalServers,
    SUM(CASE WHEN cs.ComplianceStatus = 'Compliant' THEN 1 ELSE 0 END) AS Compliant,
    SUM(CASE WHEN cs.ComplianceStatus = 'NonCompliant' THEN 1 ELSE 0 END) AS NonCompliant,
    SUM(CASE WHEN cs.ComplianceStatus = 'Fixed' THEN 1 ELSE 0 END) AS Fixed,
    SUM(CASE WHEN cs.ComplianceStatus = 'FixFailed' THEN 1 ELSE 0 END) AS FixFailed,
    SUM(CASE WHEN cs.ComplianceStatus = 'Unreachable' THEN 1 ELSE 0 END) AS Unreachable,
    CAST(
        CASE WHEN COUNT(*) > 0 
        THEN (SUM(CASE WHEN cs.ComplianceStatus IN ('Compliant', 'Fixed') THEN 1 ELSE 0 END) * 100.0 / COUNT(*))
        ELSE 100 END
    AS DECIMAL(5,2)) AS CompliancePercentage,
    MAX(cs.LastCheckedDate) AS LastCheckedDate
FROM monitoring.JobComplianceDefinitions d
LEFT JOIN monitoring.JobComplianceCurrentStatus cs ON d.DefinitionID = cs.DefinitionID
WHERE d.IsActive = 1
GROUP BY d.RuleName, d.Description, d.Severity;

GO

-- [patch].[vw_PatchComplianceByServer]
CREATE OR ALTER VIEW patch.vw_PatchComplianceByServer AS
SELECT 
    si.ServerName,
    pc.ComplianceScore,
    pc.ComplianceStatus,
    pc.MissingCriticalPatches,
    pc.CollectedDate
FROM patch.PatchCompliance pc
JOIN config.ServerInventory si ON pc.ServerID = si.ServerID;
GO

-- [Patching].[vw_DashboardKPI]

/*------------------------------------------------------------
7) Dashboard KPI View - Single row for dashboard widgets
------------------------------------------------------------*/
CREATE   VIEW Patching.vw_DashboardKPI
AS
SELECT
    TotalServers = COUNT(*),
    UpToDate = SUM(CASE WHEN PatchCurrencyStatus = N'UP_TO_DATE' THEN 1 ELSE 0 END),
    NeedsPatch = SUM(CASE WHEN PatchCurrencyStatus = N'NEEDS_PATCH' THEN 1 ELSE 0 END),
    NeedsReview = SUM(CASE WHEN PatchCurrencyStatus IN (N'UNKNOWN_CU', N'REVIEW', N'UNKNOWN_VERSION', N'UNMATCHED') THEN 1 ELSE 0 END),
    SQL2022Count = SUM(CASE WHEN SQLVersion LIKE N'%2022%' THEN 1 ELSE 0 END),
    SQL2019Count = SUM(CASE WHEN SQLVersion LIKE N'%2019%' THEN 1 ELSE 0 END),
    SQL2016Count = SUM(CASE WHEN SQLVersion LIKE N'%2016%' THEN 1 ELSE 0 END),
    ProdServers = SUM(CASE WHEN ServerEnvironment = N'Production' THEN 1 ELSE 0 END),
    NonProdServers = SUM(CASE WHEN ServerEnvironment = N'Non-Prod' THEN 1 ELSE 0 END),
    CompliancePercent = CAST(
        ROUND(100.0 * SUM(CASE WHEN PatchCurrencyStatus = N'UP_TO_DATE' THEN 1 ELSE 0 END) / 
        NULLIF(SUM(CASE WHEN PatchCurrencyStatus IN (N'UP_TO_DATE', N'NEEDS_PATCH') THEN 1 ELSE 0 END), 0), 2) AS decimal(5,2)),
    LastUpdated = GETDATE()
FROM report.usp_SqlServicingCurrency_Assessment_View;

GO

-- [Patching].[vw_DeploymentHistory]

CREATE   VIEW Patching.vw_DeploymentHistory
AS
SELECT 
    h.DeploymentID, 
    h.ServerName, 
    COALESCE(cms.Environment, cms.CMSEnvironment, 'Unknown') AS Environment,
    h.PreDeployBuild, 
    h.PreDeployCU, 
    h.PostDeployBuild, 
    h.PostDeployCU,
    h.TargetCU, 
    h.TargetKB, 
    h.DeploymentStatus,
    h.DeploymentStartTime, 
    h.DeploymentEndTime, 
    h.DurationMinutes,
    h.ExitCode, 
    h.RebootRequired, 
    h.OSVersion, 
    h.DiskFreeGB,
    h.DeployedBy, 
    h.ErrorMessage, 
    h.BatchID
FROM Patching.DeploymentHistory h
LEFT JOIN config.ServerInventory cms ON h.ServerName = cms.ServerName;

GO

-- [Patching].[vw_DeploymentQueue]

CREATE   VIEW Patching.vw_DeploymentQueue
AS
SELECT 
    q.QueueID, 
    q.ServerName, 
    COALESCE(cms.Environment, cms.CMSEnvironment, 'Unknown') AS Environment, 
    p.SQLVersion, 
    p.CUName AS TargetCU, 
    p.KBNumber,
    q.Status, 
    q.Priority, 
    q.ScheduledDate, 
    q.MaintenanceWindow,
    q.PreCheckPassed, 
    q.RequestedBy, 
    q.RequestedDate, 
    q.ApprovedBy, 
    q.ApprovedDate
FROM Patching.DeploymentQueue q
JOIN Patching.CUPackages p ON q.PackageID = p.PackageID
LEFT JOIN config.ServerInventory cms ON q.ServerName = cms.ServerName;

GO

-- [Patching].[vw_PatchCalendarWithSchedules]

-- Calendar View with Scheduled Patches
CREATE   VIEW Patching.vw_PatchCalendarWithSchedules AS
SELECT 
    c.CalendarID,
    c.CalendarYear,
    c.CalendarMonth,
    c.CalendarDay,
    c.CalendarDate,
    c.DayOfWeek,
    c.WeekOfMonth,
    c.IsWeekend,
    c.IsHoliday,
    c.HolidayName,
    c.IsPatchTuesday,
    s.ScheduleID,
    s.ScheduleName,
    s.ScheduleType,
    s.ServerName,
    g.GroupName,
    s.PatchType,
    s.TargetCU,
    s.KBNumber,
    s.Status AS PatchStatus,
    s.ApprovalStatus,
    s.ScheduledStartTime,
    s.ScheduledEndTime
FROM Patching.PatchCalendar c
LEFT JOIN Patching.ScheduledPatches s ON c.CalendarDate = s.ScheduledDate
LEFT JOIN Patching.ServerPatchGroups g ON s.GroupID = g.GroupID;

GO

-- [Patching].[vw_PatchComplianceByEnvironment]

CREATE VIEW Patching.vw_PatchComplianceByEnvironment
AS
/*
    Patch Compliance by Environment (PROD vs NON-PROD)
    Source: dbo.A2 (CMS Source of Truth)
    Updated: January 22, 2026
*/
WITH ComplianceCalc AS (
    SELECT 
        Server,
        ISNULL(Environment, 'Unknown') AS Environment,
        SqlVersionMajor,
        SqlBuild,
        CASE 
            WHEN SqlVersionMajor = 17 THEN 'UpToDate'
            WHEN SqlVersionMajor = 16 AND SqlBuild >= '16.0.4225.2' THEN 'UpToDate'
            WHEN SqlVersionMajor = 16 AND SqlBuild < '16.0.4225.2' THEN 'NeedsPatch'
            WHEN SqlVersionMajor = 15 AND SqlBuild >= '15.0.4455.2' THEN 'UpToDate'
            WHEN SqlVersionMajor = 15 AND SqlBuild < '15.0.4455.2' THEN 'NeedsPatch'
            WHEN SqlVersionMajor = 14 AND SqlBuild >= '14.0.3485.1' THEN 'UpToDate'
            WHEN SqlVersionMajor = 14 AND SqlBuild < '14.0.3485.1' THEN 'NeedsPatch'
            WHEN SqlVersionMajor = 13 AND SqlBuild >= '13.0.6300' THEN 'UpToDate'
            WHEN SqlVersionMajor = 13 AND SqlBuild < '13.0.6300' THEN 'NeedsPatch'
            WHEN SqlBuild IS NULL THEN 'UnknownCU'
            ELSE 'Review'
        END AS PatchStatus
    FROM dbo.A2
    WHERE IsActive = 1 
      AND IsSQLServer = 1
)
SELECT 
    Environment,
    COUNT(*) AS TotalServers,
    SUM(CASE WHEN PatchStatus = 'UpToDate' THEN 1 ELSE 0 END) AS UpToDate,
    SUM(CASE WHEN PatchStatus = 'NeedsPatch' THEN 1 ELSE 0 END) AS NeedsPatch,
    SUM(CASE WHEN PatchStatus = 'UnknownCU' THEN 1 ELSE 0 END) AS UnknownCU,
    SUM(CASE WHEN PatchStatus = 'Review' THEN 1 ELSE 0 END) AS Unmatched,
    CAST(ROUND(
        SUM(CASE WHEN PatchStatus = 'UpToDate' THEN 1.0 ELSE 0 END) * 100.0 / NULLIF(COUNT(*), 0)
    , 2) AS DECIMAL(5,2)) AS CompliancePercent
FROM ComplianceCalc
GROUP BY Environment;

GO

-- [Patching].[vw_PatchComplianceByVersion]

CREATE VIEW Patching.vw_PatchComplianceByVersion
AS
/*
    Patch Compliance by SQL Version
    Source: dbo.A2 (CMS Source of Truth)
    Updated: January 22, 2026
*/
WITH ComplianceCalc AS (
    SELECT 
        Server,
        Environment,
        SqlVersionMajor,
        SqlBuild,
        CASE SqlVersionMajor
            WHEN 17 THEN 'SQL Server 2025'
            WHEN 16 THEN 'SQL Server 2022'
            WHEN 15 THEN 'SQL Server 2019'
            WHEN 14 THEN 'SQL Server 2017'
            WHEN 13 THEN 'SQL Server 2016'
            WHEN 12 THEN 'SQL Server 2014'
            WHEN 11 THEN 'SQL Server 2012'
            ELSE 'Other'
        END AS SQLVersion,
        -- Compliance check
        CASE 
            WHEN SqlVersionMajor = 17 THEN 'UpToDate'
            WHEN SqlVersionMajor = 16 AND SqlBuild >= '16.0.4225.2' THEN 'UpToDate'
            WHEN SqlVersionMajor = 16 AND SqlBuild < '16.0.4225.2' THEN 'NeedsPatch'
            WHEN SqlVersionMajor = 15 AND SqlBuild >= '15.0.4455.2' THEN 'UpToDate'
            WHEN SqlVersionMajor = 15 AND SqlBuild < '15.0.4455.2' THEN 'NeedsPatch'
            WHEN SqlVersionMajor = 14 AND SqlBuild >= '14.0.3485.1' THEN 'UpToDate'
            WHEN SqlVersionMajor = 14 AND SqlBuild < '14.0.3485.1' THEN 'NeedsPatch'
            WHEN SqlVersionMajor = 13 AND SqlBuild >= '13.0.6300' THEN 'UpToDate'
            WHEN SqlVersionMajor = 13 AND SqlBuild < '13.0.6300' THEN 'NeedsPatch'
            WHEN SqlBuild IS NULL THEN 'UnknownCU'
            ELSE 'Review'
        END AS PatchStatus,
        -- Support Status
        CASE SqlVersionMajor
            WHEN 17 THEN 'Supported'
            WHEN 16 THEN 'Supported'
            WHEN 15 THEN 'Supported'
            WHEN 14 THEN 'Extended'
            WHEN 13 THEN 'Extended'
            WHEN 12 THEN 'OutOfSupport'
            WHEN 11 THEN 'OutOfSupport'
            ELSE 'Unknown'
        END AS SupportStatus,
        -- Support End Date
        CASE SqlVersionMajor
            WHEN 17 THEN '2035-01-01'
            WHEN 16 THEN '2033-01-11'
            WHEN 15 THEN '2030-01-08'
            WHEN 14 THEN '2027-10-12'
            WHEN 13 THEN '2026-07-14'
            ELSE NULL
        END AS SupportEnds
    FROM dbo.A2
    WHERE IsActive = 1 
      AND IsSQLServer = 1
)
SELECT 
    SQLVersion,
    COUNT(*) AS TotalServers,
    SUM(CASE WHEN PatchStatus = 'UpToDate' THEN 1 ELSE 0 END) AS UpToDate,
    SUM(CASE WHEN PatchStatus = 'NeedsPatch' THEN 1 ELSE 0 END) AS NeedsPatch,
    SUM(CASE WHEN PatchStatus = 'UnknownCU' THEN 1 ELSE 0 END) AS UnknownCU,
    SupportStatus,
    MAX(SupportEnds) AS SupportEnds,
    CAST(ROUND(
        SUM(CASE WHEN PatchStatus = 'UpToDate' THEN 1.0 ELSE 0 END) * 100.0 / NULLIF(COUNT(*), 0)
    , 2) AS DECIMAL(5,2)) AS CompliancePercent
FROM ComplianceCalc
GROUP BY SQLVersion, SupportStatus;

GO

-- [Patching].[vw_PatchComplianceDetail]
CREATE OR ALTER VIEW Patching.vw_PatchComplianceDetail
AS
/*
    Patch Compliance Full Detail
    Source: config.ServerInventory (CMS Source of Truth)
    Updated: January 29, 2026
*/
SELECT
    s.ServerName,
    s.Environment,
    CASE 
        WHEN s.SQLVersion LIKE '17.%' THEN 'SQL Server 2025'
        WHEN s.SQLVersion LIKE '16.%' THEN 'SQL Server 2022'
        WHEN s.SQLVersion LIKE '15.%' THEN 'SQL Server 2019'
        WHEN s.SQLVersion LIKE '14.%' THEN 'SQL Server 2017'
        WHEN s.SQLVersion LIKE '13.%' THEN 'SQL Server 2016'
        ELSE 'Other'
    END AS SQLVersion,
    s.SqlBuild AS CurrentBuild,
    ISNULL(s.SqlCumulativeUpdate, 'Unknown') AS CurrentCU,
    CASE 
        WHEN s.SQLVersion LIKE '17.%' THEN 'RTM'
        WHEN s.SQLVersion LIKE '16.%' THEN 'CU22'
        WHEN s.SQLVersion LIKE '15.%' THEN 'CU32+GDR'
        WHEN s.SQLVersion LIKE '14.%' THEN 'CU31+GDR'
        WHEN s.SQLVersion LIKE '13.%' THEN 'SP3+GDR'
        ELSE 'N/A'
    END AS TargetCU,
    CASE
        WHEN s.SQLVersion LIKE '17.%' THEN 'Up to Date'
        WHEN s.SQLVersion LIKE '16.%' AND s.SqlBuild >= '16.0.4225.2' THEN 'Up to Date'
        WHEN s.SQLVersion LIKE '16.%' AND s.SqlBuild < '16.0.4225.2' THEN 'Needs Patch'
        WHEN s.SQLVersion LIKE '15.%' AND s.SqlBuild >= '15.0.4455.2' THEN 'Up to Date'
        WHEN s.SQLVersion LIKE '15.%' AND s.SqlBuild < '15.0.4455.2' THEN 'Needs Patch'
        WHEN s.SQLVersion LIKE '14.%' AND s.SqlBuild >= '14.0.3485.1' THEN 'Up to Date'
        WHEN s.SQLVersion LIKE '14.%' AND s.SqlBuild < '14.0.3485.1' THEN 'Needs Patch'
        WHEN s.SQLVersion LIKE '13.%' AND s.SqlBuild >= '13.0.6300' THEN 'Up to Date'
        WHEN s.SQLVersion LIKE '13.%' AND s.SqlBuild < '13.0.6300' THEN 'Needs Patch'
        WHEN s.SqlBuild IS NULL THEN 'Unknown'
        ELSE 'Review'
    END AS PatchStatus,
    CASE 
        WHEN s.SQLVersion LIKE '17.%' THEN 'Supported'
        WHEN s.SQLVersion LIKE '16.%' THEN 'Supported'
        WHEN s.SQLVersion LIKE '15.%' THEN 'Supported'
        WHEN s.SQLVersion LIKE '14.%' THEN 'Extended Support'
        WHEN s.SQLVersion LIKE '13.%' THEN 'Extended Support'
        ELSE 'Unknown'
    END AS SupportStatus,
    CASE 
        WHEN s.SQLVersion LIKE '17.%' THEN CAST('2035-01-01' AS DATE)
        WHEN s.SQLVersion LIKE '16.%' THEN CAST('2033-01-11' AS DATE)
        WHEN s.SQLVersion LIKE '15.%' THEN CAST('2030-01-08' AS DATE)
        WHEN s.SQLVersion LIKE '14.%' THEN CAST('2027-10-12' AS DATE)
        WHEN s.SQLVersion LIKE '13.%' THEN CAST('2026-07-14' AS DATE)
        ELSE NULL
    END AS SupportEnds,
    CASE 
        WHEN s.SQLVersion LIKE '17.%' THEN DATEDIFF(DAY, GETDATE(), '2035-01-01')
        WHEN s.SQLVersion LIKE '16.%' THEN DATEDIFF(DAY, GETDATE(), '2033-01-11')
        WHEN s.SQLVersion LIKE '15.%' THEN DATEDIFF(DAY, GETDATE(), '2030-01-08')
        WHEN s.SQLVersion LIKE '14.%' THEN DATEDIFF(DAY, GETDATE(), '2027-10-12')
        WHEN s.SQLVersion LIKE '13.%' THEN DATEDIFF(DAY, GETDATE(), '2026-07-14')
        ELSE NULL
    END AS DaysUntilEOS,
    CASE
        WHEN s.SQLVersion LIKE '17.%' THEN 'Current - No action needed'
        WHEN s.SQLVersion LIKE '16.%' AND s.SqlBuild >= '16.0.4225.2' THEN 'Current - No action needed'
        WHEN s.SQLVersion LIKE '16.%' THEN 'Apply KB5068450 (CU22)'
        WHEN s.SQLVersion LIKE '15.%' AND s.SqlBuild >= '15.0.4455.2' THEN 'Current - No action needed'
        WHEN s.SQLVersion LIKE '15.%' THEN 'Apply KB5068404 (CU32+GDR)'
        WHEN s.SQLVersion LIKE '14.%' AND s.SqlBuild >= '14.0.3485.1' THEN 'Plan migration to 2019/2022'
        WHEN s.SQLVersion LIKE '14.%' THEN 'Apply KB5068403 (CU31+GDR)'
        WHEN s.SQLVersion LIKE '13.%' AND s.SqlBuild >= '13.0.6300' THEN 'Plan migration before July 2026'
        WHEN s.SQLVersion LIKE '13.%' THEN 'Apply SP3+GDR and plan migration'
        ELSE 'Investigate'
    END AS Recommendation,
    CASE
        WHEN s.SQLVersion LIKE '16.%' AND s.SqlBuild >= '16.0.4225.2' THEN 'green'
        WHEN s.SQLVersion LIKE '15.%' AND s.SqlBuild >= '15.0.4455.2' THEN 'green'
        WHEN s.SQLVersion LIKE '17.%' THEN 'green'
        WHEN s.SQLVersion LIKE '13.%' AND s.SqlBuild >= '13.0.6300' THEN 'yellow'
        WHEN s.SQLVersion LIKE '14.%' AND s.SqlBuild >= '14.0.3485.1' THEN 'yellow'
        WHEN s.SqlBuild IS NULL THEN 'gray'
        ELSE 'red'
    END AS StatusColor,
    CASE
        WHEN s.SQLVersion LIKE '16.%' AND s.SqlBuild < '16.0.4225.2' THEN 1
        WHEN s.SQLVersion LIKE '15.%' AND s.SqlBuild < '15.0.4455.2' THEN 2
        WHEN s.SQLVersion LIKE '13.%' THEN 3
        WHEN s.SqlBuild IS NULL THEN 4
        ELSE 5
    END AS SortOrder
FROM config.ServerInventory s
WHERE s.IsActive = 1
  AND s.IsSQLServer = 1;
GO

-- [Patching].[vw_PatchComplianceSummary]

CREATE VIEW Patching.vw_PatchComplianceSummary
AS
/*
    Patch Compliance Summary View
    Source: dbo.A2 (CMS Source of Truth)
    Target Builds: SQL 2022 CU22 (16.0.4225.2), SQL 2019 CU32+GDR (15.0.4455.2), SQL 2016 SP3 (13.0.6300)
    Updated: January 22, 2026
*/
WITH ComplianceCalc AS (
    SELECT 
        Server,
        Environment,
        SqlVersionMajor,
        SqlBuild,
        -- Determine compliance based on build number comparison
        CASE 
            -- SQL 2022: Target 16.0.4225.2 (CU22)
            WHEN SqlVersionMajor = 16 AND SqlBuild >= '16.0.4225.2' THEN 'UpToDate'
            WHEN SqlVersionMajor = 16 AND SqlBuild < '16.0.4225.2' THEN 'NeedsPatch'
            -- SQL 2019: Target 15.0.4455.2 (CU32+GDR)
            WHEN SqlVersionMajor = 15 AND SqlBuild >= '15.0.4455.2' THEN 'UpToDate'
            WHEN SqlVersionMajor = 15 AND SqlBuild < '15.0.4455.2' THEN 'NeedsPatch'
            -- SQL 2017: Target 14.0.3485.1 (CU31+GDR)
            WHEN SqlVersionMajor = 14 AND SqlBuild >= '14.0.3485.1' THEN 'UpToDate'
            WHEN SqlVersionMajor = 14 AND SqlBuild < '14.0.3485.1' THEN 'NeedsPatch'
            -- SQL 2016: Target 13.0.6300 (SP3 baseline)
            WHEN SqlVersionMajor = 13 AND SqlBuild >= '13.0.6300' THEN 'UpToDate'
            WHEN SqlVersionMajor = 13 AND SqlBuild < '13.0.6300' THEN 'NeedsPatch'
            -- SQL 2025 (Preview - always current)
            WHEN SqlVersionMajor = 17 THEN 'UpToDate'
            -- Unknown/NULL
            WHEN SqlBuild IS NULL THEN 'UnknownCU'
            ELSE 'Review'
        END AS PatchStatus,
        -- Support Status
        CASE 
            WHEN SqlVersionMajor = 16 THEN 'Supported'      -- 2022: Until 2033
            WHEN SqlVersionMajor = 15 THEN 'Supported'      -- 2019: Until 2030
            WHEN SqlVersionMajor = 14 THEN 'Extended'       -- 2017: Until 2027
            WHEN SqlVersionMajor = 13 THEN 'NearEOS'        -- 2016: July 2026
            WHEN SqlVersionMajor = 17 THEN 'Supported'      -- 2025: Preview
            WHEN SqlVersionMajor IS NULL THEN 'Unknown'
            ELSE 'OutOfSupport'
        END AS SupportStatus
    FROM dbo.A2
    WHERE IsActive = 1 
      AND IsSQLServer = 1
)
SELECT 
    COUNT(*) AS TotalServers,
    SUM(CASE WHEN PatchStatus = 'UpToDate' THEN 1 ELSE 0 END) AS UpToDate,
    SUM(CASE WHEN PatchStatus = 'NeedsPatch' THEN 1 ELSE 0 END) AS NeedsPatch,
    SUM(CASE WHEN PatchStatus = 'UnknownCU' THEN 1 ELSE 0 END) AS UnknownCU,
    SUM(CASE WHEN PatchStatus = 'Review' THEN 1 ELSE 0 END) AS Review,
    SUM(CASE WHEN SqlVersionMajor IS NULL OR SqlVersionMajor NOT IN (13,14,15,16,17) THEN 1 ELSE 0 END) AS Unmatched,
    0 AS UnknownVersion,
    SUM(CASE WHEN SupportStatus = 'OutOfSupport' THEN 1 ELSE 0 END) AS OutOfSupport,
    SUM(CASE WHEN SupportStatus = 'NearEOS' THEN 1 ELSE 0 END) AS NearEOS,
    SUM(CASE WHEN SupportStatus IN ('Supported', 'Extended') THEN 1 ELSE 0 END) AS Supported,
    CAST(ROUND(
        SUM(CASE WHEN PatchStatus = 'UpToDate' THEN 1.0 ELSE 0 END) * 100.0 / NULLIF(COUNT(*), 0)
    , 2) AS DECIMAL(5,2)) AS CompliancePercent
FROM ComplianceCalc;

GO

-- [Patching].[vw_PatchHistoryReport]

-- Patch History Report View
CREATE   VIEW Patching.vw_PatchHistoryReport AS
SELECT 
    h.ExecutionID,
    h.ServerName,
    cms.Environment,
    cms.ComplianceTier,
    h.PatchType,
    h.KBNumber,
    h.BeforeBuild,
    h.BeforeCU,
    h.AfterBuild,
    h.AfterCU,
    h.StartTime,
    h.EndTime,
    h.DurationMinutes,
    h.Status,
    h.RebootRequired,
    h.ExecutedBy,
    h.ExecutionMethod,
    s.ScheduleName,
    s.ChangeTicketNumber
FROM Patching.PatchExecutionHistory h
LEFT JOIN config.ServerInventory cms ON h.ServerName = cms.ServerName
LEFT JOIN Patching.ScheduledPatches s ON h.ScheduleID = s.ScheduleID;

GO

-- [Patching].[vw_PendingPatchRequests]

CREATE VIEW Patching.vw_PendingPatchRequests AS
SELECT 
    ps.ScheduleID AS RequestID,
    CASE 
        WHEN ps.Notes LIKE '%bulk%' THEN 'Bulk'
        ELSE 'Single'
    END AS RequestType,
    ps.ServerName,
    COALESCE(ps.ComplianceTier, si.ComplianceTier, 'Unassigned') AS ServerGroup,
    COALESCE(ps.Environment, si.Environment, 'Unknown') AS GroupType,
    ps.TargetCU,
    ps.KBNumber AS KBArticle,
    mw.WindowName AS MaintenanceWindow,
    ps.ScheduledDate AS ScheduledStart,
    DATEADD(MINUTE, ISNULL(mw.DurationMinutes, 240), ps.ScheduledDate) AS ScheduledEnd,
    ps.Status,
    ps.CreatedBy AS RequestedBy,
    ps.CreatedDate AS RequestedDate,
    ps.ApprovedBy,
    ps.ApprovedDate,
    1 AS ServersTotal,
    ps.Notes
FROM Patching.PatchSchedule ps
LEFT JOIN config.MaintenanceWindows mw ON ps.MaintenanceWindowID = mw.WindowID
LEFT JOIN config.ServerInventory si ON ps.ServerName = si.ServerName
WHERE ps.Status IN ('Scheduled', 'Approved', 'Pending', 'InProgress');

GO

-- [Patching].[vw_ServerBuildStatus]

-- View: Current vs Latest Build comparison
CREATE VIEW Patching.vw_ServerBuildStatus AS
SELECT 
    si.ServerName,
    si.Environment,
    si.ComplianceTier,
    si.AppCategory,
    si.SQLVersion AS CurrentBuild,
    CASE 
        WHEN LEFT(si.SQLVersion, 2) = '17' THEN 'SQL Server 2025'
        WHEN LEFT(si.SQLVersion, 2) = '16' THEN 'SQL Server 2022'
        WHEN LEFT(si.SQLVersion, 2) = '15' THEN 'SQL Server 2019'
        WHEN LEFT(si.SQLVersion, 2) = '14' THEN 'SQL Server 2017'
        WHEN LEFT(si.SQLVersion, 2) = '13' THEN 'SQL Server 2016'
        WHEN LEFT(si.SQLVersion, 2) = '12' THEN 'SQL Server 2014'
        ELSE 'Unknown'
    END AS VersionName,
    CASE WHEN ISNUMERIC(LEFT(si.SQLVersion, 2)) = 1 THEN CAST(LEFT(si.SQLVersion, 2) AS INT) ELSE 0 END AS MajorVersion,
    curr.CumulativeUpdate AS CurrentCU,
    curr.KBArticle AS CurrentKB,
    curr.ReleaseDate AS CurrentReleaseDate,
    latest.BuildNumber AS LatestBuild,
    latest.CumulativeUpdate AS LatestCU,
    latest.KBArticle AS LatestKB,
    latest.ReleaseDate AS LatestReleaseDate,
    latest.IsSecurityUpdate AS LatestIsSecurityUpdate,
    CASE 
        WHEN si.SQLVersion = latest.BuildNumber THEN 'Current'
        WHEN curr.BuildNumber IS NULL THEN 'Unknown Build'
        WHEN latest.IsSecurityUpdate = 1 THEN 'Security Update Available'
        ELSE 'Update Available'
    END AS PatchStatus,
    CASE 
        WHEN si.SQLVersion = latest.BuildNumber THEN 0
        WHEN curr.CumulativeUpdate IS NOT NULL AND latest.CumulativeUpdate IS NOT NULL 
             AND ISNUMERIC(REPLACE(REPLACE(latest.CumulativeUpdate, 'CU', ''), '+GDR', '')) = 1 
             AND ISNUMERIC(REPLACE(REPLACE(curr.CumulativeUpdate, 'CU', ''), '+GDR', '')) = 1 THEN
            CAST(REPLACE(REPLACE(latest.CumulativeUpdate, 'CU', ''), '+GDR', '') AS INT) - 
            CAST(REPLACE(REPLACE(curr.CumulativeUpdate, 'CU', ''), '+GDR', '') AS INT)
        ELSE NULL
    END AS CUsBehind,
    latest.SupportStatus,
    latest.EndOfSupportDate,
    DATEDIFF(DAY, GETDATE(), latest.EndOfSupportDate) AS DaysUntilEOS,
    si.LastPatchDate,
    DATEDIFF(DAY, si.LastPatchDate, GETDATE()) AS DaysSinceLastPatch
FROM config.ServerInventory si
LEFT JOIN Patching.SQLBuildReference curr ON si.SQLVersion = curr.BuildNumber
LEFT JOIN Patching.SQLBuildReference latest ON 
    CASE WHEN ISNUMERIC(LEFT(si.SQLVersion, 2)) = 1 THEN CAST(LEFT(si.SQLVersion, 2) AS INT) ELSE 0 END = latest.SQLMajorVersion 
    AND latest.IsLatest = 1
WHERE si.IsActive = 1 
  AND si.SQLVersion IS NOT NULL;

GO

-- [Patching].[vw_ServerPatchStatus]

-- Server Patch Status View
CREATE   VIEW Patching.vw_ServerPatchStatus AS
SELECT 
    cms.ServerName,
    COALESCE(cms.Environment, cms.CMSEnvironment, 'Unknown') AS Environment,
    cms.ComplianceTier,
    cms.SQLVersion AS CurrentBuild,
    cms.SqlCumulativeUpdate AS CurrentCU,
    br.LatestCU AS TargetCU,
    br.LatestBuildNumber AS TargetBuild,
    kb.KBNumber AS LatestKB,
    kb.DownloadURL,
    kb.IsSecurityUpdate,
    kb.CVENumbers,
    CASE 
        WHEN cms.SqlCumulativeUpdate = br.LatestCU THEN 'Current'
        WHEN cms.SqlCumulativeUpdate IS NULL THEN 'Unknown'
        ELSE 'Needs Patch'
    END AS PatchStatus,
    g.GroupID,
    g.GroupName,
    sp.ScheduleID AS NextScheduledPatch,
    sp.ScheduledDate AS NextPatchDate,
    sp.Status AS NextPatchStatus
FROM config.ServerInventory cms
LEFT JOIN Patching.SQLServerBuildReference br 
    ON cms.SQLVersion LIKE CAST(br.SQLMajorVersion AS VARCHAR) + '.%'
LEFT JOIN Patching.MicrosoftKBReference kb 
    ON br.SQLMajorVersion = kb.SQLMajorVersion AND kb.IsLatest = 1
LEFT JOIN Patching.ServerGroupMembers sgm ON cms.ServerName = sgm.ServerName AND sgm.IsActive = 1
LEFT JOIN Patching.ServerPatchGroups g ON sgm.GroupID = g.GroupID
LEFT JOIN Patching.ScheduledPatches sp ON cms.ServerName = sp.ServerName 
    AND sp.Status IN ('Pending', 'Approved') 
    AND sp.ScheduledDate >= CAST(GETDATE() AS DATE)
WHERE cms.IsActive = 1
AND (cms.ServerType = 'SQL Server' OR cms.ServerType IS NULL);

GO

-- [Patching].[vw_ServersNeedingPatches]

CREATE VIEW Patching.vw_ServersNeedingPatches
AS
/*
    Servers Needing Patches
    Source: dbo.A2 (CMS Source of Truth)
    Updated: January 22, 2026
*/
SELECT 
    a.Server AS ServerName,
    a.Environment,
    CASE a.SqlVersionMajor
        WHEN 17 THEN 'SQL Server 2025'
        WHEN 16 THEN 'SQL Server 2022'
        WHEN 15 THEN 'SQL Server 2019'
        WHEN 14 THEN 'SQL Server 2017'
        WHEN 13 THEN 'SQL Server 2016'
        ELSE 'Other'
    END AS SQLVersion,
    ISNULL(a.SqlCumulativeUpdate, 
        CASE 
            WHEN a.SqlBuild LIKE '16.0.4225%' THEN 'CU22'
            WHEN a.SqlBuild LIKE '16.0.4222%' THEN 'CU21+GDR'
            WHEN a.SqlBuild LIKE '16.0.4215%' THEN 'CU21'
            WHEN a.SqlBuild LIKE '16.0.4210%' THEN 'CU20+GDR'
            WHEN a.SqlBuild LIKE '16.0.4205%' THEN 'CU20'
            WHEN a.SqlBuild LIKE '16.0.1%' THEN 'RTM'
            WHEN a.SqlBuild LIKE '15.0.4455%' THEN 'CU32+GDR'
            WHEN a.SqlBuild LIKE '15.0.4430%' THEN 'CU32'
            WHEN a.SqlBuild LIKE '15.0.2155%' THEN 'RTM+GDR'
            WHEN a.SqlBuild LIKE '13.0.6%' THEN 'SP3'
            WHEN a.SqlBuild LIKE '13.0.7%' THEN 'SP3+CU'
            ELSE 'Unknown'
        END
    ) AS CurrentCU,
    CASE a.SqlVersionMajor
        WHEN 16 THEN 'CU22'
        WHEN 15 THEN 'CU32+GDR'
        WHEN 14 THEN 'CU31+GDR'
        WHEN 13 THEN 'SP3+GDR'
        ELSE 'N/A'
    END AS TargetCU,
    -- Calculate CUs behind (approximate)
    CASE 
        WHEN a.SqlVersionMajor = 16 THEN 
            CASE 
                WHEN a.SqlBuild >= '16.0.4225' THEN 0
                WHEN a.SqlBuild >= '16.0.4215' THEN 1
                WHEN a.SqlBuild >= '16.0.4205' THEN 2
                WHEN a.SqlBuild >= '16.0.4195' THEN 3
                WHEN a.SqlBuild LIKE '16.0.1%' THEN 22  -- RTM branch
                ELSE 5
            END
        WHEN a.SqlVersionMajor = 15 THEN 
            CASE 
                WHEN a.SqlBuild >= '15.0.4455' THEN 0
                WHEN a.SqlBuild >= '15.0.4430' THEN 1
                WHEN a.SqlBuild LIKE '15.0.2%' THEN 32  -- RTM branch
                ELSE 5
            END
        ELSE 0
    END AS CUsBehind,
    CASE a.SqlVersionMajor
        WHEN 16 THEN 'KB5068450'
        WHEN 15 THEN 'KB5068404'
        WHEN 14 THEN 'KB5068403'
        WHEN 13 THEN 'KB5029187'
        ELSE NULL
    END AS TargetKB
FROM dbo.A2 a
WHERE a.IsActive = 1 
  AND a.IsSQLServer = 1
  AND (
      -- SQL 2022 not at CU22
      (a.SqlVersionMajor = 16 AND (a.SqlBuild < '16.0.4225.2' OR a.SqlBuild IS NULL))
      -- SQL 2019 not at CU32+GDR
      OR (a.SqlVersionMajor = 15 AND (a.SqlBuild < '15.0.4455.2' OR a.SqlBuild IS NULL))
      -- SQL 2017 not at CU31+GDR
      OR (a.SqlVersionMajor = 14 AND (a.SqlBuild < '14.0.3485.1' OR a.SqlBuild IS NULL))
      -- SQL 2016 not at SP3
      OR (a.SqlVersionMajor = 13 AND (a.SqlBuild < '13.0.6300' OR a.SqlBuild IS NULL))
      -- Unknown version
      OR a.SqlVersionMajor IS NULL
      OR a.SqlVersionMajor NOT IN (13, 14, 15, 16, 17)
  );

GO

-- [Patching].[vw_SQL2016EndOfSupport]

/*------------------------------------------------------------
6) SQL 2016 End of Support View - For planning
------------------------------------------------------------*/
CREATE   VIEW Patching.vw_SQL2016EndOfSupport
AS
SELECT
    ServerName = ISNULL(ServerName, InventoryServerKey),
    Environment = ISNULL(ServerEnvironment, N'UNMATCHED'),
    SQLVersion,
    SupportEnds = ExtendedSupportEnd,
    DaysRemaining = DATEDIFF(day, GETDATE(), ExtendedSupportEnd),
    Urgency = CASE 
        WHEN DATEDIFF(day, GETDATE(), ExtendedSupportEnd) <= 90 THEN N'CRITICAL'
        WHEN DATEDIFF(day, GETDATE(), ExtendedSupportEnd) <= 180 THEN N'HIGH'
        WHEN DATEDIFF(day, GETDATE(), ExtendedSupportEnd) <= 365 THEN N'MEDIUM'
        ELSE N'LOW'
    END
FROM report.usp_SqlServicingCurrency_Assessment_View
WHERE SQLVersion LIKE N'%2016%'
  AND ServerName IS NOT NULL;

GO

-- [Patching].[vw_SQL2016Servers]

CREATE   VIEW [Patching].[vw_SQL2016Servers] AS
SELECT 
    cms.ServerName,
    COALESCE(cms.Environment, cms.CMSEnvironment, 'Unknown') AS Environment,
    cms.SQLVersion,
    cms.SQLEdition,
    cms.SqlCumulativeUpdate AS CurrentCU,
    cms.ComplianceTier,
    cms.CMSGroupName,
    cms.CMSCriticality,
    CASE 
        WHEN COALESCE(cms.Environment, cms.CMSEnvironment) = 'Production' THEN 1
        ELSE 0
    END AS IsProduction,
    CASE 
        WHEN cms.SQLEdition LIKE '%Enterprise%' THEN 'Enterprise'
        WHEN cms.SQLEdition LIKE '%Standard%' THEN 'Standard'
        ELSE 'Other'
    END AS EditionCategory
FROM config.ServerInventory cms
WHERE cms.IsActive = 1
AND cms.SQLVersion LIKE '13.%'  -- SQL Server 2016
AND (cms.ServerType = 'SQL Server' OR cms.ServerType IS NULL);

GO

-- [Patching].[vw_SQLBuildIntelligence]

CREATE VIEW Patching.vw_SQLBuildIntelligence
AS
/*
    SQL Build Intelligence View for Dashboard
    Source: dbo.A2 (CMS Source of Truth)
    Used by: Get-DBAOpsDashboardData.ps1 -> SQL Intelligence section
    Updated: January 22, 2026
    
    Target Builds:
      SQL 2025: 17.0.1000.7 (RTM)
      SQL 2022: 16.0.4225.2 (CU22, KB5068450)
      SQL 2019: 15.0.4455.2 (CU32+GDR, KB5068404)
      SQL 2017: 14.0.3485.1 (CU31+GDR, KB5068403)
      SQL 2016: 13.0.6300.2 (SP3, KB5003279)
*/
WITH ServerData AS (
    SELECT 
        Server,
        Environment,
        SqlVersionMajor,
        SqlBuild,
        -- Version Name
        CASE SqlVersionMajor
            WHEN 17 THEN 'SQL Server 2025'
            WHEN 16 THEN 'SQL Server 2022'
            WHEN 15 THEN 'SQL Server 2019'
            WHEN 14 THEN 'SQL Server 2017'
            WHEN 13 THEN 'SQL Server 2016'
            WHEN 12 THEN 'SQL Server 2014'
            WHEN 11 THEN 'SQL Server 2012'
            ELSE 'Unknown'
        END AS VersionName,
        -- Is Current (compliant with target build)
        CASE 
            WHEN SqlVersionMajor = 17 THEN 1  -- 2025 Preview always current
            WHEN SqlVersionMajor = 16 AND SqlBuild >= '16.0.4225.2' THEN 1
            WHEN SqlVersionMajor = 15 AND SqlBuild >= '15.0.4455.2' THEN 1
            WHEN SqlVersionMajor = 14 AND SqlBuild >= '14.0.3485.1' THEN 1
            WHEN SqlVersionMajor = 13 AND SqlBuild >= '13.0.6300' THEN 1
            ELSE 0
        END AS IsCurrent,
        -- Needs Patching
        CASE 
            WHEN SqlVersionMajor = 17 THEN 0
            WHEN SqlVersionMajor = 16 AND SqlBuild >= '16.0.4225.2' THEN 0
            WHEN SqlVersionMajor = 15 AND SqlBuild >= '15.0.4455.2' THEN 0
            WHEN SqlVersionMajor = 14 AND SqlBuild >= '14.0.3485.1' THEN 0
            WHEN SqlVersionMajor = 13 AND SqlBuild >= '13.0.6300' THEN 0
            ELSE 1
        END AS NeedsPatch,
        -- Unknown Build
        CASE WHEN SqlBuild IS NULL THEN 1 ELSE 0 END AS UnknownBuild,
        -- Needs Security Update (CVE tracking)
        CASE 
            WHEN SqlVersionMajor = 15 AND SqlBuild < '15.0.4455.2' THEN 1  -- CVE-2025-59499
            WHEN SqlVersionMajor = 16 AND SqlBuild < '16.0.4225.2' THEN 1
            ELSE 0
        END AS NeedsSecurityUpdate
    FROM dbo.A2
    WHERE IsActive = 1 
      AND IsSQLServer = 1
)
SELECT 
    VersionName,
    ISNULL(SqlVersionMajor, 0) AS MajorVersion,
    COUNT(*) AS ServerCount,
    -- Latest Build Info
    CASE SqlVersionMajor
        WHEN 17 THEN '17.0.1000.7'
        WHEN 16 THEN '16.0.4225.2'
        WHEN 15 THEN '15.0.4455.2'
        WHEN 14 THEN '14.0.3485.1'
        WHEN 13 THEN '13.0.6435.1'
        ELSE NULL
    END AS LatestBuild,
    CASE SqlVersionMajor
        WHEN 17 THEN 'RTM'
        WHEN 16 THEN 'CU22'
        WHEN 15 THEN 'CU32+GDR'
        WHEN 14 THEN 'CU31+GDR'
        WHEN 13 THEN 'SP3+GDR'
        ELSE NULL
    END AS LatestCU,
    CASE SqlVersionMajor
        WHEN 17 THEN NULL
        WHEN 16 THEN 'KB5068450'
        WHEN 15 THEN 'KB5068404'
        WHEN 14 THEN 'KB5068403'
        WHEN 13 THEN 'KB5029187'
        ELSE NULL
    END AS LatestKB,
    CASE SqlVersionMajor
        WHEN 17 THEN CAST('2025-11-01' AS DATE)
        WHEN 16 THEN CAST('2025-11-13' AS DATE)
        WHEN 15 THEN CAST('2025-11-11' AS DATE)
        WHEN 14 THEN CAST('2025-11-11' AS DATE)
        WHEN 13 THEN CAST('2023-10-10' AS DATE)
        ELSE NULL
    END AS LatestReleaseDate,
    -- Security Update Flag
    CASE WHEN SqlVersionMajor IN (15, 16) THEN 1 ELSE 0 END AS HasSecurityUpdate,
    -- Compliance Counts
    SUM(IsCurrent) AS CurrentCount,
    SUM(NeedsSecurityUpdate) AS NeedsSecurityUpdate,
    SUM(NeedsPatch) AS NeedsPatching,
    SUM(UnknownBuild) AS UnknownBuild,
    -- Patch Percent
    CAST(ROUND(
        SUM(IsCurrent) * 100.0 / NULLIF(COUNT(*), 0)
    , 1) AS DECIMAL(5,1)) AS PatchPercent,
    -- Support Status
    CASE SqlVersionMajor
        WHEN 17 THEN 'Supported'
        WHEN 16 THEN 'Supported'
        WHEN 15 THEN 'Supported'
        WHEN 14 THEN 'Extended'
        WHEN 13 THEN 'Extended'
        WHEN 12 THEN 'End of Life'
        WHEN 11 THEN 'End of Life'
        ELSE 'Unknown'
    END AS SupportStatus,
    -- End of Support Date
    CASE SqlVersionMajor
        WHEN 17 THEN CAST('2035-01-01' AS DATE)
        WHEN 16 THEN CAST('2033-01-11' AS DATE)
        WHEN 15 THEN CAST('2030-01-08' AS DATE)
        WHEN 14 THEN CAST('2027-10-12' AS DATE)
        WHEN 13 THEN CAST('2026-07-14' AS DATE)
        ELSE NULL
    END AS EndOfSupportDate,
    -- Days Until EOS
    CASE SqlVersionMajor
        WHEN 17 THEN DATEDIFF(DAY, GETDATE(), '2035-01-01')
        WHEN 16 THEN DATEDIFF(DAY, GETDATE(), '2033-01-11')
        WHEN 15 THEN DATEDIFF(DAY, GETDATE(), '2030-01-08')
        WHEN 14 THEN DATEDIFF(DAY, GETDATE(), '2027-10-12')
        WHEN 13 THEN DATEDIFF(DAY, GETDATE(), '2026-07-14')
        ELSE NULL
    END AS DaysUntilEOS,
    -- Status for Dashboard
    CASE 
        WHEN SUM(NeedsPatch) = 0 THEN 'CURRENT'
        WHEN SUM(NeedsSecurityUpdate) > 0 THEN 'SECURITY UPDATE REQUIRED'
        ELSE 'NEEDS PATCHING'
    END AS Status
FROM ServerData
GROUP BY 
    VersionName,
    SqlVersionMajor;

GO

-- [Patching].[vw_TaniumPatchingCompliance]

CREATE VIEW Patching.vw_TaniumPatchingCompliance
AS
/*
â•”â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•—
â•‘ DBAOps Monitoring and Compliance Framework                                â•‘
â•‘ View: vw_TaniumPatchingCompliance                                         â•‘
â•‘ Author: John Bosco RHOYLLE INNOVATION EMPIRE™, Sr SQL DBA/DBAOps Engineer                   â•‘
â•šâ•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•
*/
SELECT 
    ps.ServerName,
    ps.SQLVersion,
    ps.CurrentCULevel,
    ps.ComplianceStatus AS PatchComplianceStatus,
    ps.ServerEnvironment,
    ps.TaniumPatchingTag,
    ps.TaniumPatchingConfigured,
    ps.TaniumTagLastChecked,
    ps.IsReplicationPublisher,
    ps.IsReplicationSubscriber,
    ps.TaniumAgentStatus,
    -- Determine expected tag
    CASE 
        WHEN ps.IsReplicationSubscriber = 1 THEN 'SERVER_SQLSERVER_SUBSCRIBER'
        WHEN ps.IsReplicationPublisher = 1 THEN 'SERVER_SQLSERVER_PUBLISHER'
        WHEN ps.ServerEnvironment = 'Non-Prod' THEN 'SERVER_SQLSERVER_NON-PROD'
        ELSE 'SERVER_SQLSERVER_PRODUCTION'
    END AS ExpectedTag,
    -- Determine expected tag code for setup
    CASE 
        WHEN ps.IsReplicationSubscriber = 1 THEN 4  -- Subscriber = Production window
        WHEN ps.IsReplicationPublisher = 1 THEN 4   -- Publisher = Production window
        WHEN ps.ServerEnvironment = 'Non-Prod' THEN 3
        ELSE 4
    END AS ExpectedTagCode,
    -- Tanium automation compliance status
    CASE 
        WHEN ps.TaniumAgentStatus = 'Offline' THEN 'Unknown - Server Offline'
        WHEN ps.TaniumPatchingConfigured = 1 THEN 'Configured'
        WHEN ps.TaniumPatchingConfigured = 0 OR ps.TaniumPatchingConfigured IS NULL THEN 'NOT CONFIGURED'
        ELSE 'Unknown'
    END AS TaniumAutomationStatus,
    -- Overall compliance (both patch and automation)
    CASE 
        WHEN ps.TaniumAgentStatus = 'Offline' THEN 'Unknown'
        WHEN ps.ComplianceStatus IN ('Compliant') AND ps.TaniumPatchingConfigured = 1 THEN 'Fully Compliant'
        WHEN ps.ComplianceStatus IN ('Compliant') AND (ps.TaniumPatchingConfigured = 0 OR ps.TaniumPatchingConfigured IS NULL) THEN 'Patched - Automation Missing'
        WHEN ps.ComplianceStatus = 'Warning' AND ps.TaniumPatchingConfigured = 1 THEN 'Behind - Automation OK'
        WHEN ps.ComplianceStatus = 'Warning' AND (ps.TaniumPatchingConfigured = 0 OR ps.TaniumPatchingConfigured IS NULL) THEN 'Behind - Automation Missing'
        ELSE 'Non-Compliant'
    END AS OverallComplianceStatus
FROM Patching.TaniumPatchStatus ps
WHERE ps.IsActive = 1;

GO

-- [Patching].[vw_UpcomingMaintenanceWindows]

CREATE VIEW Patching.vw_UpcomingMaintenanceWindows AS
SELECT 
    mw.WindowID,
    mw.WindowName,
    mw.[Description],
    mw.StartTime,
    mw.DurationMinutes,
    mw.DayOfWeek,
    mw.WeekOfMonth,
    mw.Environment,
    mw.ComplianceTier,
    mw.AppCategory,
    mw.RequiresApproval,
    mw.AutoReboot,
    mw.NotifyEmail,
    ISNULL(mw.IsActive, 1) AS IsActive,
    CASE mw.DayOfWeek
        WHEN 1 THEN 'Sunday'
        WHEN 2 THEN 'Monday'
        WHEN 3 THEN 'Tuesday'
        WHEN 4 THEN 'Wednesday'
        WHEN 5 THEN 'Thursday'
        WHEN 6 THEN 'Friday'
        WHEN 7 THEN 'Saturday'
        ELSE 'Unknown'
    END AS DayName,
    CASE ISNULL(mw.WeekOfMonth, 0)
        WHEN 0 THEN 'Every'
        WHEN 1 THEN '1st'
        WHEN 2 THEN '2nd'
        WHEN 3 THEN '3rd'
        WHEN 4 THEN '4th'
        ELSE 'Last'
    END AS WeekName,
    (SELECT COUNT(*) FROM Patching.PatchSchedule ps 
     WHERE ps.MaintenanceWindowID = mw.WindowID 
     AND ps.Status IN ('Scheduled', 'Approved')) AS PendingRequests
FROM config.MaintenanceWindows mw
WHERE ISNULL(mw.IsActive, 1) = 1;

GO

-- [pbi].[DailyServerSummary]

CREATE VIEW pbi.DailyServerSummary
AS
SELECT 
    CONVERT(INT, FORMAT(CAST(CollectionTime AS DATE), 'yyyyMMdd')) AS DateKey,
    ServerName,
    
    -- Server Count
    1 AS ServerCount,
    
    -- Backup Summary
    COUNT(DISTINCT CASE WHEN bc.BackupType = 'FULL' THEN bc.DatabaseName END) AS DatabasesWithFullBackup,
    COUNT(DISTINCT CASE WHEN bc.BackupType = 'LOG' THEN bc.DatabaseName END) AS DatabasesWithLogBackup,
    SUM(CASE WHEN bc.IsCompliant = 1 THEN 1 ELSE 0 END) AS CompliantBackups,
    SUM(CASE WHEN bc.IsCompliant = 0 THEN 1 ELSE 0 END) AS NonCompliantBackups,
    
    -- Latest Collection
    MAX(CollectionTime) AS LastCollectionTime
FROM fact.BackupComplianceLog bc
GROUP BY 
    CONVERT(INT, FORMAT(CAST(CollectionTime AS DATE), 'yyyyMMdd')),
    ServerName;

GO

-- [pbi].[DimEnvironment]

CREATE   VIEW pbi.DimEnvironment
AS
SELECT DISTINCT
    COALESCE(Environment, CMSEnvironment, 'Unknown') AS Environment,
    COUNT(*) AS ServerCount
FROM config.ServerInventory
WHERE IsActive = 1
GROUP BY COALESCE(Environment, CMSEnvironment, 'Unknown');

GO

-- [pbi].[DimServer]
-- =============================================
-- STEP 2: Repoint pbi.DimServer to CMS
-- =============================================

CREATE VIEW pbi.DimServer
AS
SELECT 
    si.ServerID AS ServerKey,
    si.ServerName,
    ISNULL(si.InstanceName, si.ServerName) AS InstanceName,
    ISNULL(si.FQDN, si.ServerName + '.internal.YOURORG.ad') AS FQDN,
    ISNULL(si.SQLVersion, 'Not Collected') AS SQLVersion,
    CASE 
        WHEN si.SQLVersion LIKE '16.%' THEN 'SQL Server 2022'
        WHEN si.SQLVersion LIKE '15.%' THEN 'SQL Server 2019'
        WHEN si.SQLVersion LIKE '14.%' THEN 'SQL Server 2017'
        WHEN si.SQLVersion LIKE '13.%' THEN 'SQL Server 2016'
        WHEN si.SQLVersion LIKE '12.%' THEN 'SQL Server 2014'
        WHEN si.SQLVersion LIKE '11.%' THEN 'SQL Server 2012'
        ELSE 'Unknown'
    END AS SQLVersionName,
    ISNULL(si.SQLEdition, 'Not Collected') AS Edition,
    CASE 
        WHEN si.SQLEdition LIKE '%Enterprise%' THEN 'Enterprise'
        WHEN si.SQLEdition LIKE '%Standard%' THEN 'Standard'
        WHEN si.SQLEdition LIKE '%Developer%' THEN 'Developer'
        WHEN si.SQLEdition LIKE '%Express%' THEN 'Express'
        WHEN si.SQLEdition LIKE '%Web%' THEN 'Web'
        ELSE 'Other'
    END AS EditionCategory,
    ISNULL(si.SqlBuild, '') AS SQLBuild,
    ISNULL(si.SqlCumulativeUpdate, '') AS CumulativeUpdate,
    ISNULL(si.CPUCount, 0) AS CpuCount,
    ISNULL(si.MaxMemoryMB, 0) AS MaxMemoryMB,
    ISNULL(CAST(si.TotalMemoryGB AS DECIMAL(10,2)), 0) AS TotalMemoryGB,
    ISNULL(si.WindowsVersion, 'Not Collected') AS WindowsVersion,
    ISNULL(si.WindowsBuild, '') AS WindowsBuild,
    ISNULL(si.IsClustered, 0) AS IsClustered,
    ISNULL(si.IsVM, 0) AS IsVM,
    si.IsActive,
    ISNULL(si.CMSCriticality, 'Unknown') AS Criticality,
    ISNULL(si.CMSEnvironment, 'Unknown') AS Environment,
    ISNULL(si.CMSGroupName, 'Unassigned') AS CMSGroupName,
    ISNULL(si.CMSParentGroupName, 'Unknown') AS CMSParentGroupName,
    ISNULL(si.AppCategory, 'Uncategorized') AS AppCategory,
    ISNULL(si.DataClassification, 'Unclassified') AS DataClassification,
    ISNULL(si.ComplianceTier, 'Standard') AS ComplianceTier,
    si.LastSeen,
    si.CreatedDate,
    si.CMSLastSyncDate
FROM config.ServerInventory si
WHERE si.IsActive = 1;

GO

-- [pbi].[ExecutiveKPISummary]
-- =============================================
-- 2. Fix pbi.ExecutiveKPISummary - point to CMS
-- =============================================

CREATE VIEW pbi.ExecutiveKPISummary
AS
WITH LatestData AS (
    SELECT 
        'Total Servers' AS MetricName,
        COUNT(DISTINCT ServerName) AS MetricValue,
        'Count' AS MetricUnit,
        1 AS SortOrder
    FROM config.ServerInventory
    WHERE IsActive = 1

    UNION ALL

    SELECT 
        'Backup Compliance Rate',
        CASE WHEN COUNT(*) > 0 
            THEN CAST(SUM(CASE WHEN IsCompliant = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS DECIMAL(5,2))
            ELSE 0 
        END,
        'Percent',
        2
    FROM fact.BackupComplianceLog
    WHERE CollectionTime > DATEADD(DAY, -1, GETDATE())

    UNION ALL

    SELECT 
        'Disk Space Warnings',
        COUNT(*),
        'Count',
        3
    FROM DBAOps.DiskSpaceHistory
    WHERE CollectionTime > DATEADD(DAY, -1, GETDATE())
      AND PercentFree < 20

    UNION ALL

    SELECT 
        'Job Success Rate',
        CASE WHEN COUNT(*) > 0 
            THEN CAST(SUM(CASE WHEN IsCompliant = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS DECIMAL(5,2))
            ELSE 0 
        END,
        'Percent',
        4
    FROM fact.JobComplianceLog
    WHERE TimeCaptured > DATEADD(DAY, -1, GETDATE()) 
       OR CaptureTime > DATEADD(DAY, -1, GETDATE())
)
SELECT 
    MetricName,
    MetricValue,
    MetricUnit,
    SortOrder,
    GETDATE() AS RefreshTime
FROM LatestData;

GO

-- [pbi].[FactAlertSummary]

CREATE VIEW pbi.FactAlertSummary
AS
SELECT 
    -- Keys
    CONVERT(INT, FORMAT(SummaryDate, 'yyyyMMdd')) AS DateKey,
    
    -- Measures
    ISNULL(TotalAlerts, 0) AS TotalAlerts,
    ISNULL(HighAlerts, 0) AS HighAlerts,
    ISNULL(MediumAlerts, 0) AS MediumAlerts,
    ISNULL(LowAlerts, 0) AS LowAlerts,
    ISNULL(UniqueServers, 0) AS UniqueServersWithAlerts,
    
    -- Time
    SummaryDate,
    ReportGeneratedOn
FROM dbo.fact_DailyAlertSummary;

GO

-- [pbi].[FactBackupCompliance]

CREATE   VIEW pbi.FactBackupCompliance
AS
SELECT 
    cms.ServerName,
    COALESCE(cms.Environment, cms.CMSEnvironment, 'Unknown') AS Environment,
    cms.ComplianceTier,
    cms.IsActive
FROM config.ServerInventory cms
WHERE (cms.ServerType = 'SQL Server' OR cms.ServerType IS NULL);

GO

-- [pbi].[FactDashboardSummary]

CREATE VIEW pbi.FactDashboardSummary
AS
SELECT 
    -- Keys
    CONVERT(INT, FORMAT(CAST(LastUpdated AS DATE), 'yyyyMMdd')) AS DateKey,
    Server AS ServerName,
    Environment,
    Category,
    
    -- Measures
    TotalChecks,
    PassCount,
    FailCount,
    WarningCount,
    
    -- Calculated
    CASE 
        WHEN TotalChecks > 0 
        THEN (CAST(PassCount AS FLOAT) / TotalChecks) * 100 
        ELSE 0 
    END AS PassRate,
    CASE 
        WHEN TotalChecks > 0 
        THEN (CAST(FailCount AS FLOAT) / TotalChecks) * 100 
        ELSE 0 
    END AS FailRate,
    
    -- Status
    CASE 
        WHEN FailCount > 0 THEN 'Failed'
        WHEN WarningCount > 0 THEN 'Warning'
        ELSE 'Passed'
    END AS OverallStatus,
    
    -- Time
    LastUpdated
FROM dbo.vw_DBAOps_DashboardSummary;

GO

-- [pbi].[FactDiskSpace]

CREATE VIEW pbi.FactDiskSpace
AS
SELECT 
    CONVERT(INT, FORMAT(CAST(CollectionTime AS DATE), 'yyyyMMdd')) AS DateKey,
    ServerName,
    DriveLetter,
    DriveLabel AS VolumeName,
    TotalSpaceGB,
    FreeSpaceGB,
    UsedSpaceGB,
    PercentFree AS FreeSpacePct,
    PercentUsed AS UsedSpacePct,
    CASE 
        WHEN PercentFree < 10 THEN 'Critical'
        WHEN PercentFree < 20 THEN 'Warning'
        ELSE 'Healthy'
    END AS DiskStatus,
    CASE WHEN PercentFree < 10 THEN 1 ELSE 0 END AS IsCritical,
    CASE WHEN PercentFree < 20 THEN 1 ELSE 0 END AS IsWarning,
    CollectionTime
FROM DBAOps.DiskSpaceHistory
WHERE CollectionTime IS NOT NULL;

GO

-- [pbi].[FactJobCompliance]

CREATE VIEW pbi.FactJobCompliance
AS
SELECT 
    -- Keys
    CONVERT(INT, FORMAT(CAST(ISNULL(TimeCaptured, CaptureTime) AS DATE), 'yyyyMMdd')) AS DateKey,
    ServerName,
    
    -- Job Info
    JobName,
    JobEnabled,
    ScheduleEnabled,
    
    -- Execution
    LastRunDate,
    LastRunOutcome,
    NextRunDate,
    
    -- Compliance
    IsCompliant,
    CASE WHEN IsCompliant = 1 THEN 'Compliant' ELSE 'Non-Compliant' END AS ComplianceStatus,
    ComplianceIssue,
    Status,
    
    -- Measures
    1 AS JobCount,
    CASE WHEN IsCompliant = 1 THEN 1 ELSE 0 END AS CompliantJobCount,
    CASE WHEN IsCompliant = 0 THEN 1 ELSE 0 END AS NonCompliantJobCount,
    CASE WHEN JobEnabled = 1 THEN 1 ELSE 0 END AS EnabledJobCount,
    CASE WHEN JobEnabled = 0 THEN 1 ELSE 0 END AS DisabledJobCount,
    
    -- Time
    ISNULL(TimeCaptured, CaptureTime) AS CollectionTime
FROM fact.JobComplianceLog;

GO

-- [pbi].[FactServerHealth]

CREATE VIEW pbi.FactServerHealth
AS
SELECT 
    -- Keys
    CONVERT(INT, FORMAT(CAST(SummaryDate AS DATE), 'yyyyMMdd')) AS DateKey,
    ServerName,
    
    -- Performance Metrics
    AvgCPUPercent,
    MaxCPUPercent,
    AvgMemoryPercent,
    MaxMemoryPercent,
    AvgDiskLatencyMs,
    MaxDiskLatencyMs,
    TotalBatchRequests,
    TotalCompilations,
    PageLifeExpectancy,
    BufferCacheHitRatio,
    
    -- Health Status
    CASE 
        WHEN MaxCPUPercent > 90 OR MaxMemoryPercent > 95 OR MaxDiskLatencyMs > 100 THEN 'Critical'
        WHEN MaxCPUPercent > 75 OR MaxMemoryPercent > 85 OR MaxDiskLatencyMs > 50 THEN 'Warning'
        ELSE 'Healthy'
    END AS HealthStatus,
    
    -- Alert Flags
    CASE WHEN MaxCPUPercent > 90 THEN 1 ELSE 0 END AS CPUCritical,
    CASE WHEN MaxMemoryPercent > 95 THEN 1 ELSE 0 END AS MemoryCritical,
    CASE WHEN MaxDiskLatencyMs > 100 THEN 1 ELSE 0 END AS DiskCritical,
    CASE WHEN PageLifeExpectancy < 300 THEN 1 ELSE 0 END AS PLEWarning,
    
    -- Time
    SummaryDate,
    CollectionTime
FROM dbo.fact_DailyPerformanceSummary
WHERE SummaryDate IS NOT NULL;

GO

-- [playbook].[vw_AvailablePlaybooks]

-- =============================================================================
-- VIEW: playbook.vw_AvailablePlaybooks
-- =============================================================================
CREATE   VIEW playbook.vw_AvailablePlaybooks
AS
SELECT 
    p.PlaybookID,
    p.PlaybookCode,
    p.PlaybookName,
    p.Description,
    c.CategoryName,
    c.Icon AS CategoryIcon,
    r.RiskName,
    r.RiskLevelID,
    r.Color AS RiskColor,
    p.EstimatedMinutes,
    p.TriggerAlertTypes,
    p.AllowedCMSGroups,
    p.RequiresCJISApproval,
    p.Tags,
    p.Version,
    (SELECT COUNT(*) FROM playbook.Steps WHERE PlaybookID = p.PlaybookID) AS TotalSteps,
    (SELECT COUNT(*) FROM playbook.ExecutionHistory WHERE PlaybookID = p.PlaybookID) AS TimesExecuted,
    (SELECT COUNT(*) FROM playbook.ExecutionHistory WHERE PlaybookID = p.PlaybookID AND Status = 'Completed') AS SuccessfulExecutions
FROM playbook.Playbooks p
JOIN playbook.Categories c ON p.CategoryID = c.CategoryID
JOIN playbook.RiskLevels r ON p.OverallRiskLevel = r.RiskLevelID
WHERE p.IsActive = 1;

GO

-- [report].[usp_SqlServicingCurrency_Assessment_View]

CREATE   VIEW report.usp_SqlServicingCurrency_Assessment_View
AS
SELECT 
    cms.ServerName,
    COALESCE(cms.Environment, cms.CMSEnvironment, 'Unknown') AS Environment,
    cms.SQLVersion,
    cms.SqlBuild,
    cms.SqlCumulativeUpdate,
    cms.ComplianceTier
FROM config.ServerInventory cms
WHERE cms.IsActive = 1
AND (cms.ServerType = 'SQL Server' OR cms.ServerType IS NULL);

GO

-- [report].[vw_ServerReachabilityStatus]
CREATE OR ALTER VIEW [report].[vw_ServerReachabilityStatus] AS
SELECT 
    m.ServerName,
    m.InstanceName,
    m.TcpPort,
    s.Reachable,
    s.ErrorMessage,
    s.CheckedOn
FROM config.MonitoredInstances m
LEFT JOIN (
    SELECT ServerName, MAX(CheckID) AS LatestCheck
    FROM config.ServerReachability
    GROUP BY ServerName
) AS latest ON m.ServerName = latest.ServerName
LEFT JOIN config.ServerReachability s
    ON s.ServerName = latest.ServerName
    AND s.CheckID = latest.LatestCheck
WHERE m.IsEnabled = 1;

GO

-- [rpt].[vw_ApplicationDatabaseUsage]

CREATE VIEW rpt.vw_ApplicationDatabaseUsage
/*
================================================================================
   DBAOps Monitoring and Compliance Framework
   Author: John Bosco RHOYLLE INNOVATION EMPIRE™, Sr SQL DBA/DBAOps Engineer
================================================================================
*/
AS
SELECT 
    ServerName,
    DatabaseName,
    ApplicationName,
    LoginName,
    HostName,
    SUM(ConnectionCount) AS TotalConnections,
    MAX(LastSeen) AS LastActivity,
    MAX(CollectedAt) AS CollectedAt
FROM inventory.ApplicationMapping
GROUP BY ServerName, DatabaseName, ApplicationName, LoginName, HostName;

GO

-- [rpt].[vw_ComplianceTrends]

CREATE VIEW rpt.vw_ComplianceTrends
AS
/*
    Purpose: Historical trend analysis with calendar dimensions
    Use: Time series charts, drill-through, trend analysis
*/
SELECT 
    f.RecordID,
    f.BackupSource,
    f.ServerName,
    f.VmName,
    f.DatabaseName,
    f.BackupType,
    f.LastBackupTime,
    f.Status,
    f.IsCompliant,
    f.ComplianceScore,
    f.Repository,
    f.RepoTotalTB,
    f.RepoFreeTB,
    f.RepoFreePercent,
    f.JobName,
    f.BackupMode,
    f.BackupSizeGB,
    f.ExpectedFrequency,
    f.AgeMinutes,
    f.SLA_Minutes,
    -- Snapshot info
    ISNULL(f.SnapshotDate, CAST(f.LoadDate AS DATE)) AS SnapshotDate,
    ISNULL(f.SnapshotHour, DATEPART(HOUR, f.LoadDate)) AS SnapshotHour,
    ISNULL(f.DateKey, CAST(FORMAT(f.LoadDate, 'yyyyMMdd') AS INT)) AS DateKey,
    f.LoadDate,
    f.InsertTime,
    -- Calendar dimensions
    cal.CalendarYear,
    cal.CalendarQuarter,
    cal.CalendarMonth,
    cal.MonthName,
    cal.CalendarDay,
    cal.DayName,
    cal.WeekOfYear,
    cal.IsWeekend,
    cal.IsBusinessDay,
    cal.FiscalYear,
    cal.FiscalQuarter,
    -- Server dimension
    ISNULL(s.Environment, 'Unknown') AS EnvironmentType
FROM fact.BackupCompliance f
LEFT JOIN dim.Calendar cal ON ISNULL(f.DateKey, CAST(FORMAT(f.LoadDate, 'yyyyMMdd') AS INT)) = cal.DateKey
LEFT JOIN dim.[Server] s ON f.ServerName = s.ServerName;

GO

-- [rpt].[vw_CurrentCompliance]

CREATE VIEW rpt.vw_CurrentCompliance
AS
SELECT 
    c.ComplianceID,
    c.ServerName,
    c.VmName,
    c.DatabaseName,
    c.BackupType,
    c.LastBackupTime,
    c.Status,
    c.IsCompliant,
    CAST(c.ComplianceScore AS INT) AS ComplianceScore,
    c.JobName,
    c.BackupSizeGB,
    c.Repository,
    c.RepoFreePercent,
    c.LoadDate,
    
    -- Calculated fields
    ISNULL(c.AgeMinutes, DATEDIFF(MINUTE, c.LastBackupTime, GETDATE())) AS AgeMinutes,
    ISNULL(c.AgeMinutes, DATEDIFF(MINUTE, c.LastBackupTime, GETDATE())) / 60.0 AS AgeHours,
    ISNULL(c.AgeMinutes, DATEDIFF(MINUTE, c.LastBackupTime, GETDATE())) / 1440.0 AS AgeDays,
    
    -- Environment from dimension
    ISNULL(s.Environment, 'Unknown') AS EnvironmentType,
    
    -- SLA based on environment
    CASE 
        WHEN s.Environment IN ('PROD', 'PROD-CRITICAL', 'Production') THEN 84
        ELSE 168
    END AS SLA_Hours,
    
    -- Status color
    CASE c.Status
        WHEN 'Success' THEN '#28A745'
        WHEN 'Warning' THEN '#FFC107'
        WHEN 'Stale' THEN '#FD7E14'
        WHEN 'Critical' THEN '#DC3545'
        WHEN 'Failed' THEN '#DC3545'
        WHEN 'Missing' THEN '#6C757D'
        ELSE '#17A2B8'
    END AS StatusColor,
    
    -- Compliance category
    CASE 
        WHEN c.ComplianceScore >= 90 THEN 'Excellent'
        WHEN c.ComplianceScore >= 70 THEN 'Good'
        WHEN c.ComplianceScore >= 50 THEN 'Fair'
        WHEN c.ComplianceScore >= 30 THEN 'Poor'
        ELSE 'Critical'
    END AS ComplianceCategory

FROM ctl.BackupCompliance c
LEFT JOIN dim.[Server] s ON c.ServerName = s.ServerName;

GO

-- [rpt].[vw_DailySummary]

CREATE VIEW rpt.vw_DailySummary
AS
/*
    Purpose: Daily aggregated metrics for KPI tracking
    Use: KPI cards, trend lines, daily comparison
*/
SELECT 
    d.SummaryID,
    d.SummaryDate,
    d.DateKey,
    d.TotalServers,
    d.CompliantServers,
    d.NonCompliantServers,
    d.UnknownServers,
    d.CompliancePercent,
    d.AvgComplianceScore,
    d.MinComplianceScore,
    d.MaxComplianceScore,
    d.SuccessCount,
    d.WarningCount,
    d.StaleCount,
    d.CriticalCount,
    d.FailedCount,
    ISNULL(d.MissingCount, 0) AS MissingCount,
    d.AvgRepoFreePercent,
    d.ReposBelow20Percent,
    d.ReposBelow10Percent,
    d.AnomaliesDetected,
    d.HighSeverityAnomalies,
    d.GeneratedDate,
    -- Calendar dimensions
    cal.CalendarYear,
    cal.CalendarQuarter,
    cal.CalendarMonth,
    cal.MonthName,
    cal.WeekOfYear,
    cal.DayName,
    cal.IsWeekend,
    cal.IsBusinessDay
FROM fact.DailyComplianceSummary d
LEFT JOIN dim.Calendar cal ON d.DateKey = cal.DateKey;

GO

-- [rpt].[vw_DBAOpsDashboard]
-- Fix the rpt.vw_DBAOpsDashboard view to include Veeam Repository
CREATE VIEW rpt.vw_DBAOpsDashboard
AS
-- Backup Compliance Summary
SELECT 
    'Backup Compliance' AS Category,
    COUNT(*) AS Total,
    SUM(CASE WHEN IsCompliant = 1 THEN 1 ELSE 0 END) AS Healthy,
    SUM(CASE WHEN IsCompliant = 0 THEN 1 ELSE 0 END) AS Unhealthy,
    CAST(SUM(CASE WHEN IsCompliant = 1 THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(*), 0) AS DECIMAL(5,2)) AS HealthPct,
    MAX(CollectionTime) AS LastCheck
FROM ctl.BackupCompliance

UNION ALL

-- Replication Summary
SELECT 
    'Replication' AS Category,
    COUNT(*) AS Total,
    SUM(CASE WHEN Status = 'Healthy' THEN 1 ELSE 0 END) AS Healthy,
    SUM(CASE WHEN Status != 'Healthy' THEN 1 ELSE 0 END) AS Unhealthy,
    CAST(SUM(CASE WHEN Status = 'Healthy' THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(*), 0) AS DECIMAL(5,2)) AS HealthPct,
    MAX(CollectionTime) AS LastCheck
FROM ctl.ReplicationHealth

UNION ALL

-- Veeam Repository Summary (NEW)
SELECT 
    'Veeam Repository' AS Category,
    COUNT(*) AS Total,
    SUM(CASE WHEN RepoFreePercent >= 30 THEN 1 ELSE 0 END) AS Healthy,
    SUM(CASE WHEN RepoFreePercent < 30 THEN 1 ELSE 0 END) AS Unhealthy,
    CAST(SUM(CASE WHEN RepoFreePercent >= 30 THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(*), 0) AS DECIMAL(5,2)) AS HealthPct,
    MAX(LastUpdated) AS LastCheck
FROM [config].[RepositoryAssignment]
WHERE IsActive = 1

GO

-- [rpt].[vw_ExecutiveSummary]

CREATE VIEW rpt.vw_ExecutiveSummary
AS
/*
    Purpose: High-level executive KPIs - single row summary
    Use: Top-level KPI cards, executive dashboard
*/
SELECT 
    CAST(GETDATE() AS DATE) AS ReportDate,
    CAST(GETDATE() AS DATETIME) AS ReportDateTime,
    COUNT(DISTINCT ServerName) AS TotalServers,
    COUNT(*) AS TotalBackupRecords,
    SUM(CASE WHEN IsCompliant = 1 THEN 1 ELSE 0 END) AS CompliantCount,
    SUM(CASE WHEN IsCompliant = 0 THEN 1 ELSE 0 END) AS NonCompliantCount,
    CAST(SUM(CASE WHEN IsCompliant = 1 THEN 1.0 ELSE 0 END) / NULLIF(COUNT(*), 0) * 100 AS DECIMAL(5,2)) AS CompliancePercent,
    AVG(CAST(ComplianceScore AS DECIMAL(5,2))) AS AvgComplianceScore,
    MIN(CAST(ComplianceScore AS INT)) AS MinComplianceScore,
    MAX(CAST(ComplianceScore AS INT)) AS MaxComplianceScore,
    SUM(CASE WHEN Status = 'Success' THEN 1 ELSE 0 END) AS SuccessCount,
    SUM(CASE WHEN Status = 'Warning' THEN 1 ELSE 0 END) AS WarningCount,
    SUM(CASE WHEN Status = 'Stale' THEN 1 ELSE 0 END) AS StaleCount,
    SUM(CASE WHEN Status = 'Critical' THEN 1 ELSE 0 END) AS CriticalCount,
    SUM(CASE WHEN Status = 'Failed' THEN 1 ELSE 0 END) AS FailedCount,
    SUM(CASE WHEN Status IN ('Critical', 'Failed', 'Missing') THEN 1 ELSE 0 END) AS HighPriorityCount,
    COUNT(DISTINCT Repository) AS TotalRepositories,
    AVG(RepoFreePercent) AS AvgRepoFreePercent,
    SUM(CASE WHEN RepoFreePercent < 20 AND RepoFreePercent IS NOT NULL THEN 1 ELSE 0 END) AS ReposBelow20Pct,
    SUM(CASE WHEN RepoFreePercent < 10 AND RepoFreePercent IS NOT NULL THEN 1 ELSE 0 END) AS ReposBelow10Pct,
    MAX(LoadDate) AS DataAsOf,
    DATEDIFF(MINUTE, MAX(LoadDate), GETDATE()) AS DataAgeMinutes
FROM ctl.BackupCompliance;

GO

-- [rpt].[vw_FileGrowthCompliance]

CREATE VIEW rpt.vw_FileGrowthCompliance
/*
================================================================================
   DBAOps Monitoring and Compliance Framework
   Author: John Bosco RHOYLLE INNOVATION EMPIRE™, Sr SQL DBA/DBAOps Engineer
================================================================================
*/
AS
SELECT 
    ServerName,
    DatabaseName,
    FileName,
    FileType,
    SizeMB,
    GrowthDescription,
    CASE IsPercentGrowth 
        WHEN 1 THEN 'PERCENTAGE (Non-Compliant)'
        ELSE 'MEGABYTES (Compliant)'
    END AS GrowthType,
    IsCompliant,
    ComplianceNote,
    CollectedAt
FROM inventory.FileGrowthConfig
WHERE CollectedAt = (SELECT MAX(CollectedAt) FROM inventory.FileGrowthConfig);

GO

-- [rpt].[vw_PermissionAudit]

CREATE VIEW rpt.vw_PermissionAudit
AS
SELECT 
    sp.ServerName,
    'Server' AS PermissionScope,
    NULL AS DatabaseName,
    sp.PrincipalName,
    sp.PrincipalType,
    sp.PermissionName,
    sp.PermissionState,
    CASE WHEN pb.BaselineID IS NOT NULL THEN 'Baselined' ELSE 'Not Baselined' END AS BaselineStatus,
    CASE WHEN pd.DriftID IS NOT NULL THEN 'DRIFT DETECTED' ELSE 'OK' END AS DriftStatus,
    sp.CollectedDate
FROM security.ServerPermissions sp
LEFT JOIN security.PermissionBaseline pb ON sp.ServerName = pb.ServerName 
    AND sp.PrincipalName = pb.PrincipalName 
    AND sp.PermissionName = pb.PermissionName
    AND pb.PermissionScope = 'Server'
LEFT JOIN security.PermissionDrift pd ON sp.ServerName = pd.ServerName 
    AND sp.PrincipalName = pd.PrincipalName 
    AND sp.PermissionName = pd.PermissionName
    AND pd.IsResolved = 0

UNION ALL

SELECT 
    dp.ServerName,
    'Database' AS PermissionScope,
    dp.DatabaseName,
    dp.PrincipalName,
    dp.PrincipalType,
    ISNULL(dp.RoleName, dp.PermissionName),
    dp.PermissionState,
    CASE WHEN pb.BaselineID IS NOT NULL THEN 'Baselined' ELSE 'Not Baselined' END AS BaselineStatus,
    CASE WHEN pd.DriftID IS NOT NULL THEN 'DRIFT DETECTED' ELSE 'OK' END AS DriftStatus,
    dp.CollectedDate
FROM security.DatabasePermissions dp
LEFT JOIN security.PermissionBaseline pb ON dp.ServerName = pb.ServerName 
    AND dp.DatabaseName = pb.DatabaseName
    AND dp.PrincipalName = pb.PrincipalName 
    AND pb.PermissionScope = 'Database'
LEFT JOIN security.PermissionDrift pd ON dp.ServerName = pd.ServerName 
    AND dp.DatabaseName = pd.DatabaseName
    AND dp.PrincipalName = pd.PrincipalName 
    AND pd.IsResolved = 0;

GO

-- [rpt].[vw_PermissionMatrix]

CREATE VIEW rpt.vw_PermissionMatrix
/*
================================================================================
   DBAOps Monitoring and Compliance Framework
   Author: John Bosco RHOYLLE INNOVATION EMPIRE™, Sr SQL DBA/DBAOps Engineer
================================================================================
*/
AS
SELECT 
    l.ServerName,
    l.LoginName,
    l.LoginType,
    l.IsDisabled,
    l.DefaultDatabase,
    CASE WHEN l.IsSysAdmin = 1 THEN 'Yes' ELSE 'No' END AS IsSysAdmin,
    CASE WHEN l.IsSecurityAdmin = 1 THEN 'Yes' ELSE 'No' END AS IsSecurityAdmin,
    STUFF((
        SELECT ', ' + RoleName 
        FROM security.RoleMembership rm 
        WHERE rm.MemberName = l.LoginName 
        AND rm.RoleType = 'Server'
        AND rm.CollectedAt = (SELECT MAX(CollectedAt) FROM security.RoleMembership)
        FOR XML PATH('')
    ), 1, 2, '') AS ServerRoles,
    l.CollectedAt
FROM security.LoginInventory l
WHERE l.CollectedAt = (SELECT MAX(CollectedAt) FROM security.LoginInventory);

GO

-- [rpt].[vw_ProblemServers]

CREATE VIEW rpt.vw_ProblemServers
AS
/*
    Purpose: Servers requiring attention - sorted by priority
    Use: Action items table, alerts, drill-through from KPIs
*/
SELECT 
    c.ComplianceID,
    c.ServerName,
    c.VmName,
    c.DatabaseName,
    c.JobName,
    c.BackupType,
    c.BackupSource,
    c.LastBackupTime,
    ISNULL(c.AgeMinutes, DATEDIFF(MINUTE, c.LastBackupTime, GETDATE())) AS AgeMinutes,
    ISNULL(c.AgeMinutes, DATEDIFF(MINUTE, c.LastBackupTime, GETDATE())) / 60 AS AgeHours,
    ISNULL(c.AgeMinutes, DATEDIFF(MINUTE, c.LastBackupTime, GETDATE())) / 1440 AS AgeDays,
    c.Status,
    c.ComplianceScore,
    c.AnomalyScore,
    c.ForecastFailureHours,
    c.Repository,
    c.RepoFreePercent,
    c.RPOHours,
    c.ExpectedFrequency,
    ISNULL(s.Environment, 'Unknown') AS EnvironmentType,
    ISNULL(st.DisplayColor, '#DC3545') AS StatusColor,
    CASE 
        WHEN c.Status = 'Failed' THEN 1
        WHEN c.Status = 'Critical' THEN 2
        WHEN c.Status = 'Missing' THEN 3
        WHEN c.Status = 'Stale' THEN 4
        WHEN c.Status = 'Warning' THEN 5
        ELSE 6
    END AS PriorityOrder,
    CASE 
        WHEN c.Status = 'Failed' THEN 'Immediate Action Required'
        WHEN c.Status = 'Critical' THEN 'Urgent - Over 72 hours'
        WHEN c.Status = 'Missing' THEN 'No Backup Found'
        WHEN c.Status = 'Stale' THEN 'Overdue - 48-72 hours'
        WHEN c.Status = 'Warning' THEN 'Approaching SLA - 24-48 hours'
        ELSE 'Review Required'
    END AS ActionRequired,
    CASE 
        WHEN c.Status = 'Failed' THEN 'High'
        WHEN c.Status = 'Critical' THEN 'High'
        WHEN c.Status = 'Missing' THEN 'High'
        WHEN c.Status = 'Stale' THEN 'Medium'
        WHEN c.Status = 'Warning' THEN 'Low'
        ELSE 'Info'
    END AS PriorityLevel,
    c.LoadDate
FROM ctl.BackupCompliance c
LEFT JOIN dim.[Server] s ON c.ServerName = s.ServerName
LEFT JOIN dim.Status st ON c.Status = st.StatusCode
WHERE c.IsCompliant = 0
   OR c.Status IN ('Failed', 'Critical', 'Stale', 'Warning', 'Missing');

GO

-- [rpt].[vw_RecentChanges]

CREATE VIEW rpt.vw_RecentChanges
AS
SELECT 
    ServerName,
    DatabaseName,
    'Schema' AS ChangeCategory,
    ObjectName,
    ChangeType,
    ChangedBy,
    ChangeTime,
    ObjectType AS Details
FROM change.SchemaChanges
WHERE ChangeTime > DATEADD(DAY, -7, GETDATE())

UNION ALL

SELECT 
    ServerName,
    DatabaseName,
    'Permission' AS ChangeCategory,
    PrincipalName AS ObjectName,
    ChangeType,
    ChangedBy,
    ChangeTime,
    PermissionName AS Details
FROM change.PermissionChanges
WHERE ChangeTime > DATEADD(DAY, -7, GETDATE())

UNION ALL

SELECT 
    ServerName,
    NULL AS DatabaseName,
    'Configuration' AS ChangeCategory,
    ConfigName AS ObjectName,
    'Modified' AS ChangeType,
    ChangedBy,
    ChangeTime,
    'Old: ' + ISNULL(OldValue, 'N/A') + ' | New: ' + ISNULL(NewValue, 'N/A') AS Details
FROM change.ConfigurationChanges
WHERE ChangeTime > DATEADD(DAY, -7, GETDATE());

GO

-- [rpt].[vw_ReplicationDashboard]

CREATE VIEW rpt.vw_ReplicationDashboard
AS
SELECT 
    p.PublisherServer,
    p.PublisherDatabase,
    p.PublicationName,
    p.PublicationType,
    p.SubscriberServer,
    p.SubscriberDatabase,
    p.ArticleCount,
    p.LatencyThresholdSeconds,
    p.PendingCmdsThreshold,
    h.Status AS HealthStatus,
    p.IsActive,
    CASE 
        WHEN h.Status = 'Healthy' THEN 'OK'
        WHEN h.Status = 'Warning' THEN 'WARN'
        ELSE 'UNKNOWN'
    END AS CurrentStatus
FROM config.ReplicationPublications p
LEFT JOIN ctl.ReplicationHealth h ON h.PublisherServer = p.PublisherServer
WHERE p.IsActive = 1;

GO

-- [rpt].[vw_ReplicationMetricsDashboard]

CREATE VIEW rpt.vw_ReplicationMetricsDashboard
AS
/*
â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•
DBAOps Monitoring and Compliance Framework
View: rpt.vw_ReplicationMetricsDashboard
Author: John Bosco RHOYLLE INNOVATION EMPIRE™, Sr SQL DBA/DBAOps Engineer, Microsoft Certified
â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•
*/
SELECT 
    m.PublisherServer,
    m.PublisherDatabase,
    m.PublicationName,
    m.SubscriberServer,
    m.SubscriberDatabase,
    ISNULL(m.PendingCmdCount, 0) AS PendingCmds,
    ISNULL(m.LatencySeconds, 0) AS LatencySeconds,
    ISNULL(m.DeliveryRate, 0) AS DeliveryRate,
    m.AgentStatus,
    m.Status,
    m.StatusMessage,
    m.CollectionTime,
    CASE 
        WHEN ISNULL(m.PendingCmdCount, 0) > 50000 THEN 'Critical'
        WHEN ISNULL(m.PendingCmdCount, 0) > 10000 THEN 'Warning'
        ELSE 'OK'
    END AS PendingStatus,
    CASE 
        WHEN ISNULL(m.LatencySeconds, 0) > 300 THEN 'Critical'
        WHEN ISNULL(m.LatencySeconds, 0) > 60 THEN 'Warning'
        ELSE 'OK'
    END AS LatencyStatus,
    s.Environment,
    c.LastBackupTime AS PublisherLastBackup,
    c.IsCompliant AS PublisherBackupCompliant
FROM ctl.ReplicationMetrics m
LEFT JOIN dim.Server s ON m.PublisherServer = s.ServerName
LEFT JOIN ctl.BackupCompliance c ON m.PublisherServer = c.ServerName;

GO

-- [rpt].[vw_ReplicationSummary]

CREATE VIEW rpt.vw_ReplicationSummary
AS
SELECT 
    PublisherServer,
    COUNT(DISTINCT PublisherDatabase) AS PublisherDBs,
    COUNT(DISTINCT SubscriberServer) AS Subscribers,
    COUNT(DISTINCT SubscriberDatabase) AS SubscriberDBs,
    COUNT(*) AS Publications,
    SUM(ISNULL(ArticleCount, 1)) AS TotalArticles,
    MAX(LatencyThresholdSeconds) AS LatencyThreshold,
    MAX(PendingCmdsThreshold) AS PendingCmdsThreshold
FROM config.ReplicationPublications
WHERE IsActive = 1
GROUP BY PublisherServer;

GO

-- [rpt].[vw_RepositoryHealth]

CREATE VIEW rpt.vw_RepositoryHealth
AS
/*
    Purpose: Storage/repository capacity monitoring
    Use: Storage gauges, capacity alerts, repository comparison
*/
SELECT 
    Repository,
    MAX(RepoTotalTB) AS TotalCapacityTB,
    MAX(RepoTotalTB) - MAX(RepoFreeTB) AS UsedCapacityTB,
    MAX(RepoFreeTB) AS FreeCapacityTB,
    MAX(RepoFreePercent) AS FreePercent,
    100 - MAX(RepoFreePercent) AS UsedPercent,
    COUNT(DISTINCT ServerName) AS ServersUsingRepo,
    COUNT(*) AS TotalBackupRecords,
    SUM(CASE WHEN IsCompliant = 1 THEN 1 ELSE 0 END) AS CompliantBackups,
    SUM(CASE WHEN IsCompliant = 0 THEN 1 ELSE 0 END) AS NonCompliantBackups,
    AVG(CAST(ComplianceScore AS FLOAT)) AS AvgComplianceScore,
    CASE 
        WHEN MAX(RepoFreePercent) < 10 THEN 'Critical'
        WHEN MAX(RepoFreePercent) < 20 THEN 'Warning'
        ELSE 'Healthy'
    END AS HealthStatus,
    CASE 
        WHEN MAX(RepoFreePercent) < 10 THEN '#DC3545'
        WHEN MAX(RepoFreePercent) < 20 THEN '#FFC107'
        ELSE '#28A745'
    END AS HealthColor,
    CASE 
        WHEN MAX(RepoFreePercent) < 10 THEN 3
        WHEN MAX(RepoFreePercent) < 20 THEN 2
        ELSE 1
    END AS HealthSortOrder,
    MAX(LoadDate) AS LastUpdated
FROM ctl.BackupCompliance
WHERE Repository IS NOT NULL
GROUP BY Repository;

GO

-- [rpt].[vw_SecurityChangesAudit]

CREATE VIEW rpt.vw_SecurityChangesAudit
/*
================================================================================
   DBAOps Monitoring and Compliance Framework
   Author: John Bosco RHOYLLE INNOVATION EMPIRE™, Sr SQL DBA/DBAOps Engineer
================================================================================
*/
AS
-- Permission Changes
SELECT 
    'Permission Change' AS ChangeType,
    ServerName,
    DatabaseName,
    ObjectName,
    UserName AS Principal,
    ChangeType AS Action,
    EventTime,
    CommandText
FROM audit.PermissionChanges
WHERE EventTime >= DATEADD(DAY, -30, GETDATE())

UNION ALL

-- Object/DDL Changes
SELECT 
    'DDL Change' AS ChangeType,
    ServerName,
    DatabaseName,
    ObjectName,
    LoginName AS Principal,
    EventType AS Action,
    EventTime,
    CommandText
FROM audit.ObjectChanges
WHERE EventTime >= DATEADD(DAY, -30, GETDATE());

GO

-- [rpt].[vw_ServerCompliance]

CREATE VIEW rpt.vw_ServerCompliance
AS
SELECT 
    c.ServerName,
    ISNULL(s.Environment, 'Unknown') AS EnvironmentType,
    COUNT(*) AS BackupCount,
    SUM(CASE WHEN c.IsCompliant = 1 THEN 1 ELSE 0 END) AS CompliantBackups,
    SUM(CASE WHEN c.IsCompliant = 0 THEN 1 ELSE 0 END) AS NonCompliantBackups,
    CAST(SUM(CASE WHEN c.IsCompliant = 1 THEN 1.0 ELSE 0 END) / NULLIF(COUNT(*), 0) * 100 AS DECIMAL(5,2)) AS CompliancePercent,
    AVG(CAST(c.ComplianceScore AS DECIMAL(5,2))) AS AvgComplianceScore,
    MIN(CAST(c.ComplianceScore AS INT)) AS MinComplianceScore,
    MAX(CAST(c.ComplianceScore AS INT)) AS MaxComplianceScore,
    MIN(c.LastBackupTime) AS OldestBackup,
    MAX(c.LastBackupTime) AS NewestBackup,
    MAX(ISNULL(c.AgeMinutes, DATEDIFF(MINUTE, c.LastBackupTime, GETDATE()))) AS MaxAgeMinutes,
    CASE 
        WHEN SUM(CASE WHEN c.Status IN ('Failed', 'Critical', 'Missing') THEN 1 ELSE 0 END) > 0 THEN 'Critical'
        WHEN SUM(CASE WHEN c.Status IN ('Stale', 'Warning') THEN 1 ELSE 0 END) > 0 THEN 'Warning'
        ELSE 'Healthy'
    END AS OverallHealth,
    MAX(c.LoadDate) AS LastUpdated
FROM ctl.BackupCompliance c
LEFT JOIN dim.[Server] s ON c.ServerName = s.ServerName
GROUP BY c.ServerName, s.Environment;

GO

-- [schemaDoc].[vw_DocumentationStatus]

-- â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•
-- 3. Create Current Status View
-- â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•
CREATE   VIEW schemaDoc.vw_DocumentationStatus
AS
WITH LatestDocs AS (
    SELECT 
        ServerName,
        DatabaseName,
        GeneratedAt,
        TableCount,
        ViewCount,
        ProcedureCount,
        FunctionCount,
        ColumnCount,
        RelationshipCount,
        Status,
        DurationSeconds,
        OutputPath,
        ROW_NUMBER() OVER (PARTITION BY ServerName, DatabaseName ORDER BY GeneratedAt DESC) AS rn
    FROM schemaDoc.DocumentationLog
)
SELECT 
    ServerName,
    DatabaseName,
    GeneratedAt,
    TableCount,
    ViewCount,
    ProcedureCount,
    FunctionCount,
    ColumnCount,
    RelationshipCount,
    Status,
    DurationSeconds,
    OutputPath,
    DATEDIFF(DAY, GeneratedAt, GETDATE()) AS DaysSinceGenerated,
    CASE 
        WHEN DATEDIFF(DAY, GeneratedAt, GETDATE()) <= 7 THEN 'Current'
        WHEN DATEDIFF(DAY, GeneratedAt, GETDATE()) <= 30 THEN 'Stale'
        ELSE 'Outdated'
    END AS FreshnessStatus
FROM LatestDocs
WHERE rn = 1;

GO

-- [schemaDoc].[vw_DocumentationSummary]

-- â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•
-- 4. Create Summary View
-- â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•â•
CREATE   VIEW schemaDoc.vw_DocumentationSummary
AS
SELECT 
    COUNT(DISTINCT ServerName) AS ServersDocumented,
    COUNT(DISTINCT DatabaseName) AS DatabasesDocumented,
    SUM(TableCount) AS TotalTables,
    SUM(ColumnCount) AS TotalColumns,
    SUM(ViewCount) AS TotalViews,
    SUM(ProcedureCount) AS TotalProcedures,
    SUM(RelationshipCount) AS TotalRelationships,
    MAX(GeneratedAt) AS LastGeneration,
    SUM(CASE WHEN FreshnessStatus = 'Current' THEN 1 ELSE 0 END) AS CurrentDocs,
    SUM(CASE WHEN FreshnessStatus = 'Stale' THEN 1 ELSE 0 END) AS StaleDocs,
    SUM(CASE WHEN FreshnessStatus = 'Outdated' THEN 1 ELSE 0 END) AS OutdatedDocs
FROM schemaDoc.vw_DocumentationStatus;

GO

-- [security].[vw_CurrentUserInfo]

CREATE VIEW security.vw_CurrentUserInfo
AS
SELECT 
    SUSER_SNAME() AS LoginName,
    USER_NAME() AS DatabaseUser,
    CASE 
        WHEN IS_MEMBER('INTERNAL\DBAOps_Admins') = 1 THEN 'Admin'
        WHEN IS_MEMBER('INTERNAL\DBAOpsAdmin AD') = 1 THEN 'Admin (Legacy)'
        WHEN IS_MEMBER('INTERNAL\DBAOps_Operators') = 1 THEN 'Operator'
        WHEN IS_MEMBER('INTERNAL\DBAOps_Auditors') = 1 THEN 'Auditor'
        WHEN IS_MEMBER('INTERNAL\DBAOps_Developers') = 1 THEN 'Developer'
        WHEN IS_MEMBER('INTERNAL\DBAOps_Executives') = 1 THEN 'Executive'
        WHEN IS_MEMBER('INTERNAL\DBAOps_ReadOnly') = 1 THEN 'ReadOnly'
        ELSE 'Unknown'
    END AS DBAOpsRole,
    CASE 
        WHEN IS_SRVROLEMEMBER('sysadmin') = 1 THEN 'Yes'
        ELSE 'No'
    END AS IsSysAdmin,
    (SELECT COUNT(*) FROM config.vw_ServersICanSee) AS ServersICanSee;

GO

-- [security].[vw_MissingLocalAdmins]
CREATE OR ALTER VIEW security.vw_MissingLocalAdmins AS
SELECT
    e.Description       AS ServerDescription,
    e.AccountName       AS RequiredAccount,
    e.IsActive,
    l.AccountName       AS ActualAccount,
    l.CollectedTime,
    CASE WHEN l.AccountName IS NULL THEN 1 ELSE 0 END AS IsMissing
FROM security.ExpectedLocalAdmins e
LEFT JOIN security.LocalAdminMembership l
       ON l.ServerName = e.Description
      AND l.AccountName = e.AccountName
WHERE e.IsActive = 1
  AND l.AccountName IS NULL;

GO

-- [security].[vw_ServerAccessControl]

CREATE   VIEW security.vw_ServerAccessControl
AS
SELECT 
    si.ServerName,
    si.ComplianceTier,
    si.Environment,
    si.DataClassification,
    ag.ADGroupName,
    ag.AccessLevel,
    ag.DesiredServerRole
FROM config.ServerInventory si
CROSS JOIN config.ADGroupStandards ag
WHERE si.IsActive = 1
  AND ag.IsActive = 1;

GO

