-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: May 23, 2024 at 11:30 PM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.2.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `max_bell`
--

-- --------------------------------------------------------

--
-- Table structure for table `compressors`
--

CREATE TABLE `compressors` (
  `id` int(11) NOT NULL,
  `compressor_name` varchar(40) NOT NULL,
  `operational` tinyint(4) NOT NULL,
  `site_id` int(11) DEFAULT NULL,
  `lead_ice_surface_id` int(11) DEFAULT NULL,
  `lag_ice_surface_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `compressors`
--

INSERT INTO `compressors` (`id`, `compressor_name`, `operational`, `site_id`, `lead_ice_surface_id`, `lag_ice_surface_id`) VALUES
(1, 'CM101', 1, 1, 1, 1),
(2, 'CM201', 1, 1, 2, 2),
(3, 'CM301', 1, 1, 1, 2);

-- --------------------------------------------------------

--
-- Table structure for table `compressor_readings`
--

CREATE TABLE `compressor_readings` (
  `id` int(11) NOT NULL,
  `active` tinyint(4) DEFAULT NULL,
  `runtime_hours` int(7) DEFAULT NULL,
  `suction` smallint(6) DEFAULT NULL,
  `discharge_pressure` smallint(6) DEFAULT NULL,
  `discharge_temperature` smallint(6) DEFAULT NULL,
  `oil_pressure` smallint(6) DEFAULT NULL,
  `oil_temperature` smallint(6) DEFAULT NULL,
  `water_temperature` smallint(6) DEFAULT NULL,
  `plant_readings_id` int(11) DEFAULT NULL,
  `compressor_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `ice_surfaces`
--

CREATE TABLE `ice_surfaces` (
  `id` int(11) NOT NULL,
  `ice_surface_name` varchar(30) NOT NULL,
  `site_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `ice_surfaces`
--

INSERT INTO `ice_surfaces` (`id`, `ice_surface_name`, `site_id`) VALUES
(1, 'Ken Bracko', 1),
(2, 'Perry Cavanagh', 1);

-- --------------------------------------------------------

--
-- Table structure for table `ice_temps`
--

CREATE TABLE `ice_temps` (
  `id` int(11) NOT NULL,
  `ice_temp` float DEFAULT NULL,
  `plant_readings_id` int(11) DEFAULT NULL,
  `ice_surface_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `log`
--

CREATE TABLE `log` (
  `id` int(11) NOT NULL,
  `made_by` varchar(30) NOT NULL,
  `e_id` int(11) DEFAULT NULL,
  `log_notes` text DEFAULT NULL,
  `tag` varchar(30) DEFAULT NULL,
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `plant_readings_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `plant_readings`
--

CREATE TABLE `plant_readings` (
  `id` int(11) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ammonia` int(5) DEFAULT NULL,
  `dolphin` int(5) DEFAULT NULL,
  `hot_brine` int(3) DEFAULT NULL,
  `cold_brine` int(3) DEFAULT NULL,
  `made_by` varchar(20) DEFAULT NULL,
  `e_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `sites`
--

CREATE TABLE `sites` (
  `id` int(11) NOT NULL,
  `site_name` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `sites`
--

INSERT INTO `sites` (`id`, `site_name`) VALUES
(1, ''),
(2, 'Max Bell');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `compressors`
--
ALTER TABLE `compressors`
  ADD PRIMARY KEY (`id`),
  ADD KEY `site_id` (`site_id`),
  ADD KEY `surface_lead_fk` (`lead_ice_surface_id`) USING BTREE,
  ADD KEY `surface_lag_fk` (`lag_ice_surface_id`) USING BTREE;

--
-- Indexes for table `compressor_readings`
--
ALTER TABLE `compressor_readings`
  ADD PRIMARY KEY (`id`),
  ADD KEY `plant_readings_id` (`plant_readings_id`),
  ADD KEY `compressor_id` (`compressor_id`);

--
-- Indexes for table `ice_surfaces`
--
ALTER TABLE `ice_surfaces`
  ADD PRIMARY KEY (`id`),
  ADD KEY `site_id` (`site_id`);

--
-- Indexes for table `ice_temps`
--
ALTER TABLE `ice_temps`
  ADD PRIMARY KEY (`id`),
  ADD KEY `plant_readings_id` (`plant_readings_id`),
  ADD KEY `ice_surface_id` (`ice_surface_id`);

--
-- Indexes for table `log`
--
ALTER TABLE `log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `plant_readings_id` (`plant_readings_id`);

--
-- Indexes for table `plant_readings`
--
ALTER TABLE `plant_readings`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `sites`
--
ALTER TABLE `sites`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `compressors`
--
ALTER TABLE `compressors`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `compressor_readings`
--
ALTER TABLE `compressor_readings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `ice_surfaces`
--
ALTER TABLE `ice_surfaces`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `ice_temps`
--
ALTER TABLE `ice_temps`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `log`
--
ALTER TABLE `log`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `plant_readings`
--
ALTER TABLE `plant_readings`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `sites`
--
ALTER TABLE `sites`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `compressors`
--
ALTER TABLE `compressors`
  ADD CONSTRAINT `compressors_ibfk_1` FOREIGN KEY (`site_id`) REFERENCES `sites` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `surface_fk` FOREIGN KEY (`lead_ice_surface_id`) REFERENCES `ice_surfaces` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `surface_fk_lag` FOREIGN KEY (`lag_ice_surface_id`) REFERENCES `ice_surfaces` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `compressor_readings`
--
ALTER TABLE `compressor_readings`
  ADD CONSTRAINT `compressor_readings_ibfk_1` FOREIGN KEY (`plant_readings_id`) REFERENCES `plant_readings` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `compressor_readings_ibfk_2` FOREIGN KEY (`compressor_id`) REFERENCES `compressors` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `ice_surfaces`
--
ALTER TABLE `ice_surfaces`
  ADD CONSTRAINT `ice_surfaces_ibfk_1` FOREIGN KEY (`site_id`) REFERENCES `sites` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `ice_temps`
--
ALTER TABLE `ice_temps`
  ADD CONSTRAINT `ice_temps_ibfk_1` FOREIGN KEY (`plant_readings_id`) REFERENCES `plant_readings` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `ice_temps_ibfk_2` FOREIGN KEY (`ice_surface_id`) REFERENCES `ice_surfaces` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `log`
--
ALTER TABLE `log`
  ADD CONSTRAINT `log_ibfk_1` FOREIGN KEY (`plant_readings_id`) REFERENCES `plant_readings` (`id`) ON DELETE CASCADE;

COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

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 generate_dummy_data()
BEGIN
    DECLARE cur_date DATETIME;
    DECLARE end_date DATETIME;
    DECLARE prev_reading_time DATETIME;
    DECLARE reading_time DATETIME;
    DECLARE compressor_runtime1 INT DEFAULT 8752;
    DECLARE compressor_runtime2 INT DEFAULT 14408;
    DECLARE compressor_runtime3 INT DEFAULT 10308;
    DECLARE rand_name VARCHAR(20);
    DECLARE active_status INT;
    DECLARE time_diff INT;
    DECLARE i INT;

    SET cur_date = CURDATE() - INTERVAL 100 DAY;
    SET end_date = NOW();  -- Include the current date and time

    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, increment2 * 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, increment3a * (10 / 6) / (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 the data
CALL generate_dummy_data();


