USE [msdb];
GO

CREATE OR ALTER PROCEDURE dbo.usp_CreateWeeklySchedules
    @Env VARCHAR(10) = 'DEVL'  -- Added parameter with default value
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE 
        @JobName SYSNAME,
        @FrequencyPattern NVARCHAR(MAX),
        @StartTime VARCHAR(50),
        @ScheduleName SYSNAME,
        @StartTimeInt INT,
        @FreqInterval INT,
        @ActiveDate INT,
        @computedFreq INT,
        @dbName SYSNAME;  -- Added variable for database name

    -- Determine database name based on environment
    SET @dbName = CASE @Env
                    WHEN 'DEVL' THEN 'DevlDUTASJobSchedule'
                    WHEN 'TEST' THEN 'TestDUTASJobSchedule'
                    WHEN 'PROD' THEN 'ProdDUTASJobSchedule'
                  END;

    SET @ActiveDate = CONVERT(INT, CONVERT(CHAR(8), GETDATE(), 112));

    --------------------------------------------------------------------------
    -- Dynamic SQL for cursor to fetch active weekly jobs from appropriate database
    --------------------------------------------------------------------------
    DECLARE @Sql NVARCHAR(MAX);
    SET @Sql = N'
    DECLARE job_cursor CURSOR FAST_FORWARD FOR
    SELECT 
        JobName, 
        FrequencyPattern, 
        ScheduledStartTime
    FROM ' + QUOTENAME(@dbName) + N'.dbo.JobControl
    WHERE UPPER(Frequency) = ''WEEKLY''
      AND IsActive = 1;';

    -- Execute dynamic SQL to declare cursor
    EXEC sp_executesql @Sql;

    OPEN job_cursor;
    FETCH NEXT FROM job_cursor INTO @JobName, @FrequencyPattern, @StartTime;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        BEGIN TRY
            ------------------------------------------------------------------
            -- Reset per-job variables
            ------------------------------------------------------------------
            SET @FreqInterval = 0;
            SET @computedFreq = 0;
            SET @StartTimeInt = NULL;
            SET @ScheduleName = NULL;

            ------------------------------------------------------------------
            -- Correctly format the start time (HHMMSS without extra zeros)
            ------------------------------------------------------------------
            SET @StartTime = LTRIM(RTRIM(ISNULL(@StartTime, '00:00:00')));
            
            -- Parse time and convert to HHMMSS integer
            DECLARE @TimeParts TABLE (part NVARCHAR(10), idx INT);
            INSERT INTO @TimeParts (part, idx)
            SELECT value, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
            FROM STRING_SPLIT(LEFT(@StartTime, 8), ':');
            
            DECLARE @Hours INT, @Minutes INT, @Seconds INT;
            
            SELECT @Hours = CAST(part AS INT) FROM @TimeParts WHERE idx = 0;
            SELECT @Minutes = CAST(part AS INT) FROM @TimeParts WHERE idx = 1;
            SELECT @Seconds = CAST(part AS INT) FROM @TimeParts WHERE idx = 2;
            
            -- Ensure valid time ranges
            SET @Hours = ISNULL(@Hours, 0);
            SET @Minutes = ISNULL(@Minutes, 0);
            SET @Seconds = ISNULL(@Seconds, 0);
            
            IF @Hours < 0 OR @Hours > 23 SET @Hours = 0;
            IF @Minutes < 0 OR @Minutes > 59 SET @Minutes = 0;
            IF @Seconds < 0 OR @Seconds > 59 SET @Seconds = 0;
            
            SET @StartTimeInt = (@Hours * 10000) + (@Minutes * 100) + @Seconds;

            SET @ScheduleName = CONCAT(@JobName, '_WeeklySchedule');

            ------------------------------------------------------------------
            -- Parse JSON days using a local temporary table
            ------------------------------------------------------------------
            CREATE TABLE #Days (DayName NVARCHAR(10));
            
            INSERT INTO #Days (DayName)
            SELECT TRIM(value)
            FROM OPENJSON(ISNULL(@FrequencyPattern, '{}'), '$.Days');

            ------------------------------------------------------------------
            -- Compute bitmask correctly (values should be 1-127)
            ------------------------------------------------------------------
            SELECT @computedFreq = ISNULL(SUM(
                CASE UPPER(DayName)
                    WHEN 'SUN' THEN 1
                    WHEN 'MON' THEN 2
                    WHEN 'TUE' THEN 4
                    WHEN 'WED' THEN 8
                    WHEN 'THU' THEN 16
                    WHEN 'FRI' THEN 32
                    WHEN 'SAT' THEN 64
                    ELSE 0
                END
            ), 0)
            FROM #Days;

            DROP TABLE #Days; -- Explicitly drop the temp table

            SET @FreqInterval = ISNULL(@computedFreq, 0);

            ------------------------------------------------------------------
            -- Validate freq_interval (must be 1..127)
            ------------------------------------------------------------------
            IF @FreqInterval < 1 OR @FreqInterval > 127
            BEGIN
                PRINT 'Warning: Weekly job ' + @JobName + 
                      ' has invalid freq_interval (' + ISNULL(CAST(@FreqInterval AS VARCHAR(10)), 'NULL') + 
                      '). Days found: ' + ISNULL(@FrequencyPattern, 'None') + '. Skipping.';
                FETCH NEXT FROM job_cursor INTO @JobName, @FrequencyPattern, @StartTime;
                CONTINUE;
            END

            ------------------------------------------------------------------
            -- Debug output to verify calculations
            ------------------------------------------------------------------
            PRINT 'Job: ' + @JobName + 
                  ', Days: ' + @FrequencyPattern +
                  ', FreqInterval: ' + CAST(@FreqInterval AS VARCHAR(3)) +
                  ', StartTime: ' + CAST(@StartTimeInt AS VARCHAR(6));

            ------------------------------------------------------------------
            -- Safely detach and delete any existing schedule with this name
            -- Use TRY/CATCH for schedule operations since non-existence is expected
            ------------------------------------------------------------------
            BEGIN TRY
                DECLARE @schedule_id INT;
                DECLARE @attach_count INT;

                -- Check if schedule exists and get its ID
                SELECT @schedule_id = s.schedule_id
                FROM msdb.dbo.sysschedules s
                JOIN msdb.dbo.sysjobschedules js ON s.schedule_id = js.schedule_id
                JOIN msdb.dbo.sysjobs j ON j.job_id = js.job_id
                WHERE j.name = @JobName AND s.name = @ScheduleName;

                IF @schedule_id IS NOT NULL
                BEGIN
                    -- Detach from this job (if attached)
                    EXEC msdb.dbo.sp_detach_schedule 
                        @job_name = @JobName, 
                        @schedule_name = @ScheduleName;

                    -- Check if schedule still attached to other jobs
                    SELECT @attach_count = COUNT(*) 
                    FROM msdb.dbo.sysjobschedules js
                    WHERE js.schedule_id = @schedule_id;

                    IF @attach_count = 0
                    BEGIN
                        EXEC msdb.dbo.sp_delete_schedule 
                            @schedule_name = @ScheduleName;
                        PRINT 'Existing schedule deleted: ' + @ScheduleName;
                    END
                    ELSE
                    BEGIN
                        PRINT 'Notice: schedule ' + @ScheduleName + ' could not be deleted because it is still attached to other jobs.';
                    END
                END
            END TRY
            BEGIN CATCH
                -- Schedule operations might fail if schedule doesn't exist, which is fine
                PRINT 'Notice: ' + ERROR_MESSAGE() + ' - proceeding with new schedule creation.';
            END CATCH

            ------------------------------------------------------------------
            -- Create the unified weekly schedule with subday config
            ------------------------------------------------------------------
            EXEC msdb.dbo.sp_add_schedule
                @schedule_name = @ScheduleName,
                @enabled = 1,
                @freq_type = 8,                  -- Weekly
                @freq_interval = @FreqInterval,  -- Bitmask of weekdays
                @freq_recurrence_factor = 1,     -- Every week
                @freq_subday_type = 1,           -- Once per day
                @freq_subday_interval = 1,
                @active_start_time = @StartTimeInt,
                @active_start_date = @ActiveDate;

            ------------------------------------------------------------------
            -- Attach the schedule to the job
            ------------------------------------------------------------------
            EXEC msdb.dbo.sp_attach_schedule
                @job_name = @JobName,
                @schedule_name = @ScheduleName;

            PRINT 'Weekly schedule created for job: ' + @JobName + 
                  ' (Days=' + CAST(@FreqInterval AS VARCHAR(10)) + 
                  ', Time=' + RIGHT('000000' + CAST(@StartTimeInt AS VARCHAR(6)), 6) + ')';

        END TRY
        BEGIN CATCH
            PRINT 'Error scheduling job ' + ISNULL(@JobName, 'Unknown') + ': ' + ERROR_MESSAGE();
        END CATCH;

        FETCH NEXT FROM job_cursor INTO @JobName, @FrequencyPattern, @StartTime;
    END

    CLOSE job_cursor;
    DEALLOCATE job_cursor;

    PRINT 'All weekly job schedules processed for ' + @dbName;
END;
GO