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'
        WHEN SQLVersion LIKE '13.%' THEN 'KB5068401'
        ELSE 'Unknown'
    END,
    @PatchFilePath = CASE 
        WHEN SQLVersion LIKE '16.%' THEN 'E:\Patches\SQL2022\sqlserver2022-kb5068450-x64_70c032e1bd645698a0048c2cccb2e7f8faa1b8c7.exe'
        WHEN SQLVersion LIKE '15.%' THEN 'E:\Patches\SQL2019\sqlserver2019-kb5068404-x64_745b3298ef3ef6ac4e67b4b7ccc80278632fa9e5.exe'
        WHEN SQLVersion LIKE '13.%' THEN 'E:\Patches\SQL2016\sqlserver2016-kb5068401-x64_17127e890f7d66f017bd760768ee8fdfd24e81d3.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
