CREATE OR REPLACE PROCEDURE GenerateNotifications(NumRows IN NUMBER) IS
    i NUMBER := 1;
    minUserId NUMBER;
    maxUserId NUMBER;
    createdStart NUMBER;
    readStart NUMBER;
    timeCreated NUMBER;
    timeRead NUMBER;
    fromUser NUMBER;
    toUser NUMBER;
BEGIN
    -- Calculate timestamp values
    SELECT MIN(id), MAX(id) INTO minUserId, maxUserId FROM MDL_USER;

    createdStart := (EXTRACT(DAY FROM (SYSTIMESTAMP - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))) * 86400) - NumRows;
    readStart := (EXTRACT(DAY FROM (SYSTIMESTAMP - TO_TIMESTAMP('1970-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'))) * 86400) + 86400 - NumRows;

    -- Loop to generate and insert notifications
    WHILE i < NumRows LOOP
            -- Conditional timeRead calculation
            IF MOD(i, 4) = 0 THEN
                timeRead := readStart + (FLOOR(i / 40) * 40);
            ELSE
                timeRead := NULL;
            END IF;

            -- TimeCreated calculation
            timeCreated := createdStart + (FLOOR(i / 10) * 10);

            fromUser := FLOOR(DBMS_RANDOM.VALUE(minUserId, maxUserId + 1));
            toUser := FLOOR(DBMS_RANDOM.VALUE(minUserId, maxUserId + 1));

            -- Insert into mdl_notifications
            INSERT INTO mdl_notifications (
                useridfrom,
                useridto,
                subject,
                fullmessage,
                fullmessagehtml,
                smallmessage,
                component,
                eventtype,
                contexturl,
                contexturlname,
                timeread,
                timecreated,
                customdata
            )
            VALUES (
               fromUser,
               toUser,
               'Message subject ' || TO_CHAR(i),
               'Full message ' || TO_CHAR(i),
               '<p>Full message</p> ' || TO_CHAR(i),
               'small message' || TO_CHAR(i),
               'core',
               'notification',
               'http://example.com',
               'example',
               timeRead,
               timeCreated,
               '{"foo":"bar"}'
           );

            -- Increment loop counter
            i := i + 1;
        END LOOP;
END GenerateNotifications;

CALL GenerateNotifications(4000000);
