DELIMITER $$

CREATE PROCEDURE generate_additional_dummy_data()
BEGIN
    DECLARE cur_date DATETIME;
    DECLARE end_date DATETIME;
    DECLARE prev_reading_time DATETIME;
    DECLARE reading_time DATETIME;
    DECLARE compressor_runtime1 INT;
    DECLARE compressor_runtime2 INT;
    DECLARE compressor_runtime3 INT;
    DECLARE rand_name VARCHAR(20);
    DECLARE active_status INT;
    DECLARE time_diff INT;
    DECLARE i INT;

    -- Get the last generated reading time
    SET cur_date = (SELECT MAX(created_at) FROM plant_readings);
    SET end_date = NOW();  -- Include the current date and time

    -- Get the last runtime values for each compressor
    SELECT runtime_hours INTO compressor_runtime1 FROM compressor_readings WHERE compressor_id = 1 ORDER BY id DESC LIMIT 1;
    SELECT runtime_hours INTO compressor_runtime2 FROM compressor_readings WHERE compressor_id = 2 ORDER BY id DESC LIMIT 1;
    SELECT runtime_hours INTO compressor_runtime3 FROM compressor_readings WHERE compressor_id = 3 ORDER BY id DESC LIMIT 1;

    WHILE cur_date <= end_date DO  -- Loop until the current time
        -- Insert readings in specified time ranges with random minutes
        SET prev_reading_time = cur_date;
        SET reading_time = cur_date + INTERVAL (FLOOR(RAND() * (9 - 5 + 1)) + 5) HOUR + INTERVAL (FLOOR(RAND() * 60)) MINUTE;
        IF reading_time <= end_date THEN
            CALL insert_plant_reading(reading_time, prev_reading_time, compressor_runtime1, compressor_runtime2, compressor_runtime3);
        END IF;
        SET prev_reading_time = reading_time;

        SET reading_time = cur_date + INTERVAL (FLOOR(RAND() * (15 - 11 + 1)) + 11) HOUR + INTERVAL (FLOOR(RAND() * 60)) MINUTE;
        IF reading_time <= end_date THEN
            CALL insert_plant_reading(reading_time, prev_reading_time, compressor_runtime1, compressor_runtime2, compressor_runtime3);
        END IF;
        SET prev_reading_time = reading_time;

        SET reading_time = cur_date + INTERVAL (FLOOR(RAND() * (20 - 16 + 1)) + 16) HOUR + INTERVAL (FLOOR(RAND() * 60)) MINUTE;
        IF reading_time <= end_date THEN
            CALL insert_plant_reading(reading_time, prev_reading_time, compressor_runtime1, compressor_runtime2, compressor_runtime3);
        END IF;
        SET prev_reading_time = reading_time;

        SET reading_time = cur_date + INTERVAL (FLOOR(RAND() * (23.5 - 21 + 1)) + 21) HOUR + INTERVAL (FLOOR(RAND() * 60)) MINUTE;
        IF reading_time <= end_date THEN
            CALL insert_plant_reading(reading_time, prev_reading_time, compressor_runtime1, compressor_runtime2, compressor_runtime3);
        END IF;
        SET prev_reading_time = reading_time;

        -- Move to the next day
        SET cur_date = cur_date + INTERVAL 1 DAY;
    END WHILE;
END$$

DELIMITER ;

DELIMITER $$

CREATE PROCEDURE insert_plant_reading(
    reading_time DATETIME, 
    prev_reading_time DATETIME, 
    INOUT compressor_runtime1 INT, 
    INOUT compressor_runtime2 INT, 
    INOUT compressor_runtime3 INT
)
BEGIN
    DECLARE rand_name VARCHAR(20);
    DECLARE active_status INT;
    DECLARE i INT;
    DECLARE time_diff INT;
    DECLARE increment1 FLOAT;
    DECLARE increment2 FLOAT;
    DECLARE increment3 FLOAT;

    SET rand_name = ELT(FLOOR(RAND() * 7) + 1, 'Ken', 'Aaron', 'Patrick', 'Jon A', 'Crystal', 'Tehsin', 'Austin');

    -- Insert a new plant_reading with ammonia set to a random number between 0 and 5
    INSERT INTO plant_readings (created_at, dolphin, hot_brine, cold_brine, made_by, ammonia)
    VALUES (reading_time, 650, 1, 1, rand_name, FLOOR(RAND() * 6));

    SET @plant_reading_id = LAST_INSERT_ID();

    -- Insert ice_temps for each ice_surface
    INSERT INTO ice_temps (ice_temp, plant_readings_id, ice_surface_id)
    VALUES ((RAND() * (7.5 - 6.5) + 6.5) * -1, @plant_reading_id, 1),
           ((RAND() * (7.5 - 6.5) + 6.5) * -1, @plant_reading_id, 2);

    -- Calculate time difference in hours between readings
    SET time_diff = TIMESTAMPDIFF(HOUR, prev_reading_time, reading_time);

    -- Ensure runtime increments do not exceed the time difference
    SET increment3 = LEAST(time_diff, GREATEST(0, FLOOR(RAND() * 2 * 1.5))); -- Compressor 3 increment
    SET increment2 = LEAST(time_diff, increment3 * 4 / (0.5 + RAND())); -- Compressor 2 increment with ±50% randomness around 8 hours for every 2 hours of compressor 3
    SET increment1 = LEAST(time_diff, increment2 * 10 / 8 / (0.75 + RAND())); -- Compressor 1 increment with ±25% randomness around 10 hours for every 8 hours of compressor 2

    -- Ensure total runtime does not exceed time_diff
    IF increment1 + increment2 + increment3 > time_diff THEN
        SET increment1 = increment1 * time_diff / (increment1 + increment2 + increment3);
        SET increment2 = increment2 * time_diff / (increment1 + increment2 + increment3);
        SET increment3 = increment3 * time_diff / (increment1 + increment2 + increment3);
    END IF;

    -- Insert compressor_readings for each compressor
    FOR i IN 1..3 DO
        SET active_status = IF(RAND() < 0.6667, 1, 0);
        INSERT INTO compressor_readings (compressor_id, plant_readings_id, suction, discharge_pressure, discharge_temperature, oil_pressure, oil_temperature, water_temperature, active, runtime_hours)
        VALUES (
            i,
            @plant_reading_id,
            IF(active_status = 1, FLOOR(RAND() * (29 - 23 + 1)) + 23, NULL),
            IF(active_status = 1, FLOOR(RAND() * (163 - 148 + 1)) + 148, NULL),
            IF(active_status = 1, FLOOR(RAND() * (216 - 195 + 1)) + 195, NULL),
            IF(active_status = 1, FLOOR(RAND() * (43 - 39 + 1)) + 39, NULL),
            IF(active_status = 1, FLOOR(RAND() * (114 - 102 + 1)) + 102, NULL),
            IF(active_status = 1, FLOOR(RAND() * (87 - 78 + 1)) + 78, NULL),
            active_status,
            CASE
                WHEN i = 1 THEN compressor_runtime1
                WHEN i = 2 THEN compressor_runtime2
                WHEN i = 3 THEN compressor_runtime3
            END
        );
    END FOR;

    -- Increment runtime hours with specified randomness and ratios
    SET compressor_runtime1 = compressor_runtime1 + increment1;
    SET compressor_runtime2 = compressor_runtime2 + increment2;
    SET compressor_runtime3 = compressor_runtime3 + increment3;
END$$

DELIMITER ;

-- Call the procedure to generate additional data
CALL generate_additional_dummy_data();
