-- ============================================
-- DBAOps Database Updates - January 29, 2026
-- ============================================

-- 1. Patching.PatchDownloadURLs Table
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'patching' AND TABLE_NAME = 'PatchDownloadURLs')
CREATE TABLE patching.PatchDownloadURLs (
    URLID INT IDENTITY PRIMARY KEY,
    SQLVersion VARCHAR(20),
    CUVersion VARCHAR(20),
    KBNumber VARCHAR(20),
    DownloadURL VARCHAR(1000),
    FileName VARCHAR(200),
    FileSizeMB INT,
    ReleaseDate DATE,
    IsActive BIT DEFAULT 1
);

-- 2. Stored Procedure: Patching.usp_SchedulePatch
CREATE PROCEDURE Patching.usp_SchedulePatch
    @ServerName NVARCHAR(128),
    @ScheduledDate DATETIME = NULL,
    @MaintenanceWindowID INT = NULL,
    @PatchType VARCHAR(20) = 'CU',
    @Priority VARCHAR(20) = 'Normal',
    @ChangeTicket VARCHAR(50) = '',
    @Notes VARCHAR(500) = '',
    @CreatedBy VARCHAR(100) = 'Dashboard'
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @TargetCU VARCHAR(20);
    DECLARE @KBArticle VARCHAR(20);
    DECLARE @PatchFilePath VARCHAR(500);
    
    SELECT @TargetCU = CASE 
        WHEN SQLVersion LIKE '16.%' THEN 'CU22'
        WHEN SQLVersion LIKE '15.%' THEN 'CU32+GDR'
        WHEN SQLVersion LIKE '14.%' THEN 'CU31+GDR'
        WHEN SQLVersion LIKE '13.%' THEN 'SP3+GDR'
        ELSE 'Latest'
    END,
    @KBArticle = CASE 
        WHEN SQLVersion LIKE '16.%' THEN 'KB5068450'
        WHEN SQLVersion LIKE '15.%' THEN 'KB5068404'
        WHEN SQLVersion LIKE '14.%' THEN 'KB5068403'
        ELSE 'Unknown'
    END,
    @PatchFilePath = CASE 
        WHEN SQLVersion LIKE '16.%' THEN '\\\\YOURSERVER\\Patches\\SQL2022\\SQLServer2022-KB5068450-x64.exe'
        WHEN SQLVersion LIKE '15.%' THEN '\\\\YOURSERVER\\Patches\\SQL2019\\SQLServer2019-KB5068404-x64.exe'
        WHEN SQLVersion LIKE '14.%' THEN '\\\\YOURSERVER\\Patches\\SQL2017\\SQLServer2017-KB5068403-x64.exe'
        ELSE 'TBD'
    END
    FROM config.ServerInventory WHERE ServerName = @ServerName;
    
    INSERT INTO Patching.PatchRequests (
        RequestType, ServerName, TargetCU, KBArticle,
        MaintenanceWindowID, ScheduledStart, 
        Notes, RequestedBy, RequestedDate, Status
    )
    VALUES (
        'Single', @ServerName, @TargetCU, @KBArticle,
        @MaintenanceWindowID, ISNULL(@ScheduledDate, GETDATE()),
        @Notes, @CreatedBy, GETDATE(), 'Pending'
    );
    
    DECLARE @RequestID INT = SCOPE_IDENTITY();
    
    IF @ScheduledDate IS NULL OR @ScheduledDate <= DATEADD(MINUTE, 5, GETDATE())
    BEGIN
        INSERT INTO Patching.PatchExecutionQueue (
            ServerName, PatchFilePath, KBNumber, PatchType, QueueStatus, 
            Priority, QueuedDate, QueuedBy, ScheduledExecutionTime
        )
        SELECT @ServerName, @PatchFilePath, @KBArticle, @PatchType, 'Queued', 
               1, GETDATE(), @CreatedBy, GETDATE()
        WHERE NOT EXISTS (
            SELECT 1 FROM Patching.PatchExecutionQueue 
            WHERE ServerName = @ServerName AND QueueStatus IN ('Queued', 'Running')
        );
    END
    
    SELECT @RequestID AS RequestID, @TargetCU AS TargetCU, @KBArticle AS KBArticle;
END

-- 3. Updated View: Patching.vw_PatchComplianceDetail  
CREATE 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;

-- 4. Alert table columns added
ALTER TABLE alert.DiskSpaceAlertLog ADD AlertSentAt DATETIME NULL;
ALTER TABLE alert.DiskSpaceAlertLog ADD UsedPercent DECIMAL(5,2) NULL;
ALTER TABLE alert.DiskSpaceAlertLog ADD Severity NVARCHAR(20) NULL;
ALTER TABLE alert.DiskSpaceAlertLog ALTER COLUMN FreePercent DECIMAL(5,2) NULL;
ALTER TABLE alert.DiskSpaceAlertLog ALTER COLUMN AlertLevel NVARCHAR(20) NULL;
ALTER TABLE alert.DiskSpaceAlertLog ALTER COLUMN AlertColor NVARCHAR(20) NULL;
