INSERT INTO plant_readings ()
VALUES ();
INSERT INTO compressor_readings (made_by, running, suction, discharge, discharge_pressure, oil_temperature, water_temperature, plant_readings_id)
VALUES ('Aaron', '1', '26', '250', '120', '110', '84', '1');
INSERT INTO compressor_readings (made_by, running, suction, discharge, discharge_pressure, oil_temperature, water_temperature, plant_readings_id)
VALUES ('Aaron', '1', '24', '240', '130', '108', '83', '1');
INSERT INTO compressor_readings (made_by, running, suction, discharge, discharge_pressure, oil_temperature, water_temperature, plant_readings_id)
VALUES ('Aaron', '1', '27', '236', '125', '105', '82', '1');

INSERT INTO plant_readings ()
VALUES ();
INSERT INTO compressor_readings (made_by, running, suction, discharge, discharge_pressure, oil_temperature, water_temperature, plant_readings_id)
VALUES ('Patrick', '1', '26', '250', '120', '110', '84', '5');
INSERT INTO compressor_readings (made_by, running, suction, discharge, discharge_pressure, oil_temperature, water_temperature, plant_readings_id)
VALUES ('Patrick', '1', '24', '240', '130', '108', '83', '5');
INSERT INTO compressor_readings (made_by, running, suction, discharge, discharge_pressure, oil_temperature, water_temperature, plant_readings_id)
VALUES ('Patrick', '1', '27', '236', '125', '105', '82', '5');

INSERT INTO plant_readings ()
VALUES ();
INSERT INTO compressor_readings (made_by, running, suction, discharge, discharge_pressure, oil_temperature, water_temperature, plant_readings_id)
VALUES ('Ken', '1', '26', '250', '120', '110', '84', '6');
INSERT INTO compressor_readings (made_by, running, suction, discharge, discharge_pressure, oil_temperature, water_temperature, plant_readings_id)
VALUES ('Ken', '1', '24', '240', '130', '108', '83', '6');
INSERT INTO compressor_readings (made_by, running, suction, discharge, discharge_pressure, oil_temperature, water_temperature, plant_readings_id)
VALUES ('Ken', '1', '27', '236', '125', '105', '82', '6');

INSERT INTO plant_readings ()
VALUES ();
INSERT INTO compressor_readings (made_by, running, plant_readings_id)
VALUES ('Ken', '0', '26', '7');
INSERT INTO compressor_readings (made_by, running, suction, discharge, discharge_pressure, oil_temperature, water_temperature, plant_readings_id)
VALUES ('Ken', '1', '24', '240', '130', '108', '83', '7');
INSERT INTO compressor_readings (made_by, running, suction, discharge, discharge_pressure, oil_temperature, water_temperature, plant_readings_id)
VALUES ('Ken', '1', '27', '236', '125', '105', '82', '7');

INSERT INTO compressor_readings (made_by, active, suction, discharge, discharge_pressure, oil_temperature, water_temperature, plant_readings_id, compressor_id)
VALUES ('Aaron', '1', '26', '250', '120', '110', '84', '4', '1');
INSERT INTO compressor_readings (made_by, active, suction, discharge, discharge_pressure, oil_temperature, water_temperature, plant_readings_id, compressor_id)
VALUES ('Aaron', '1', '24', '240', '130', '108', '83', '4', '2');
INSERT INTO compressor_readings (made_by, active, suction, discharge, discharge_pressure, oil_temperature, water_temperature, plant_readings_id, compressor_id)
VALUES ('Aaron', '1', '27', '236', '125', '105', '82', '4', '3');
INSERT INTO compressor_readings (made_by, active, suction, discharge, discharge_pressure, oil_temperature, water_temperature, plant_readings_id, compressor_id)
VALUES ('Patrick', '1', '26', '250', '120', '110', '84', '5', '1');
INSERT INTO compressor_readings (made_by, active, suction, discharge, discharge_pressure, oil_temperature, water_temperature, plant_readings_id, compressor_id)
VALUES ('Patrick', '1', '24', '240', '130', '108', '83', '5', '2');
INSERT INTO compressor_readings (made_by, active, suction, discharge, discharge_pressure, oil_temperature, water_temperature, plant_readings_id, compressor_id)
VALUES ('Patrick', '1', '27', '236', '125', '105', '82', '5', '3');
INSERT INTO compressor_readings (made_by, active, suction, discharge, discharge_pressure, oil_temperature, water_temperature, plant_readings_id, compressor_id)
VALUES ('Ken', '1', '26', '250', '120', '110', '84', '6', '1');
INSERT INTO compressor_readings (made_by, active, suction, discharge, discharge_pressure, oil_temperature, water_temperature, plant_readings_id, compressor_id)
VALUES ('Ken', '1', '24', '240', '130', '108', '83', '6', '2');
INSERT INTO compressor_readings (made_by, active, suction, discharge, discharge_pressure, oil_temperature, water_temperature, plant_readings_id, compressor_id)
VALUES ('Ken', '1', '27', '236', '125', '105', '82', '6', '3');
INSERT INTO compressor_readings (made_by, active, plant_readings_id, compressor_id)
VALUES ('Ken', '0', '7', '1');
INSERT INTO compressor_readings (made_by, active, suction, discharge, discharge_pressure, oil_temperature, water_temperature, plant_readings_id, compressor_id)
VALUES ('Ken', '1', '24', '240', '130', '108', '83', '7', '2');
INSERT INTO compressor_readings (made_by, active, suction, discharge, discharge_pressure, oil_temperature, water_temperature, plant_readings_id, compressor_id)
VALUES ('Ken', '1', '27', '236', '125', '105', '82', '7', '3');

INSERT INTO ice_temps(ice_temp, ice_surface_id, plant_readings_id)
VALUES('-6.7', '1', '4');
INSERT INTO ice_temps(ice_temp, ice_surface_id, plant_readings_id)
VALUES('-6.8', '2', '4');

INSERT INTO ice_temps(ice_temp, ice_surface_id, plant_readings_id)
VALUES('-6.7', '1', '5');
INSERT INTO ice_temps(ice_temp, ice_surface_id, plant_readings_id)
VALUES('-6.8', '2', '5');

INSERT INTO ice_temps(ice_temp, ice_surface_id, plant_readings_id)
VALUES('-6.8', '1', '6');
INSERT INTO ice_temps(ice_temp, ice_surface_id, plant_readings_id)
VALUES('-6.8', '2', '6');

INSERT INTO ice_temps(ice_temp, ice_surface_id, plant_readings_id)
VALUES('-6.9', '1', '7');
INSERT INTO ice_temps(ice_temp, ice_surface_id, plant_readings_id)
VALUES('-6.7', '2', '7');

SELECT compressors.compressor_name, compressors.operational FROM compressors RIGHT JOIN compressor_readings ON compressor_readings.compressor_id = compressors.id;

SELECT plant_readings.id, ice_temps.ice_temp, ice_surfaces.ice_surface_name FROM `ice_temps` INNER JOIN ice_surfaces ON ice_temps.ice_surface_id = ice_surfaces.id INNER JOIN plant_readings ON ice_temps.plant_readings_id = plant_readings.id ORDER by id asc;

SELECT LAST_INSERT_ID(Id) from plant_readings order by LAST_INSERT_ID(Id) desc limit 1;


SELECT r.id, r.created_at, r.made_by, c_r.active, c_r.suction, c_r.discharge, c_r.discharge_pressure, c_r.oil_temperature, c_r.water_temperature,
MAX(CASE i_s.ice_surface_name WHEN "Ken Bracko" THEN t.ice_temp END) AS ice_surface1, 
MAX(CASE i_s.ice_surface_name WHEN "Ken Bracko" THEN i_s.id END) AS ice_surface1_id, 
MAX(CASE i_s.ice_surface_name WHEN "Perry Cavanagh" THEN t.ice_temp END) AS ice_surface2, 
MAX(CASE i_s.ice_surface_name WHEN "Perry Cavanagh" THEN i_s.id END) AS ice_surface2_id, 
MAX(CASE c.compressor_name WHEN "CM101" THEN c_r.active END) AS active1, 
MAX(CASE c.compressor_name WHEN "CM101" THEN c_r.suction END) AS suction1, 
MAX(CASE c.compressor_name WHEN "CM101" THEN c_r.discharge END) AS discharge1, 
MAX(CASE c.compressor_name WHEN "CM101" THEN c_r.discharge_pressure END) AS discharge_pressure1, 
MAX(CASE c.compressor_name WHEN "CM101" THEN c_r.oil_temperature END) AS oil_temperature1, 
MAX(CASE c.compressor_name WHEN "CM101" THEN c_r.water_temperature END) AS water_temperature1,
MAX(CASE c.compressor_name WHEN "CM201" THEN c_r.active END) AS active2, 
MAX(CASE c.compressor_name WHEN "CM201" THEN c_r.suction END) AS suction2, 
MAX(CASE c.compressor_name WHEN "CM201" THEN c_r.discharge END) AS discharge2, 
MAX(CASE c.compressor_name WHEN "CM201" THEN c_r.discharge_pressure END) AS discharge_pressure2, 
MAX(CASE c.compressor_name WHEN "CM201" THEN c_r.oil_temperature END) AS oil_temperature2, 
MAX(CASE c.compressor_name WHEN "CM201" THEN c_r.water_temperature END) AS water_temperature2,
MAX(CASE c.compressor_name WHEN "CM301" THEN c_r.active END) AS active3, 
MAX(CASE c.compressor_name WHEN "CM301" THEN c_r.suction END) AS suction3, 
MAX(CASE c.compressor_name WHEN "CM301" THEN c_r.discharge END) AS discharge3, 
MAX(CASE c.compressor_name WHEN "CM301" THEN c_r.discharge_pressure END) AS discharge_pressure3, 
MAX(CASE c.compressor_name WHEN "CM301" THEN c_r.oil_temperature END) AS oil_temperature3, 
MAX(CASE c.compressor_name WHEN "CM301" THEN c_r.water_temperature END) AS water_temperature3
FROM plant_readings r 
LEFT JOIN ice_temps t ON r.id = t.plant_readings_id 
LEFT JOIN ice_surfaces i_s ON t.ice_surface_id = i_s.id 
LEFT JOIN compressor_readings c_r ON c_r.plant_readings_id = r.id
LEFT JOIN compressors c ON c_r.plant_readings_id = r.id
GROUP BY r.id DESC;

SELECT r.id, r.created_at, r.made_by, r.hot_brine, r.cold_brine,
MAX(CASE i_s.ice_surface_name WHEN "Ken Bracko" THEN t.ice_temp END) AS ice_surface1, 
MAX(CASE i_s.ice_surface_name WHEN "Ken Bracko" THEN i_s.id END) AS ice_surface1_id, 
MAX(CASE i_s.ice_surface_name WHEN "Perry Cavanagh" THEN t.ice_temp END) AS ice_surface2, 
MAX(CASE i_s.ice_surface_name WHEN "Perry Cavanagh" THEN i_s.id END) AS ice_surface2_id, 
MAX(CASE c.compressor_name WHEN "CM101" THEN c_r.active END) AS active1, 
MAX(CASE c.compressor_name WHEN "CM101" THEN c_r.suction END) AS suction1, 
MAX(CASE c.compressor_name WHEN "CM101" THEN c_r.discharge END) AS discharge1, 
MAX(CASE c.compressor_name WHEN "CM101" THEN c_r.discharge_pressure END) AS discharge_pressure1, 
MAX(CASE c.compressor_name WHEN "CM101" THEN c_r.oil_temperature END) AS oil_temperature1, 
MAX(CASE c.compressor_name WHEN "CM101" THEN c_r.water_temperature END) AS water_temperature1,
MAX(CASE c.compressor_name WHEN "CM101" THEN c_r.active END) AS active2, 
MAX(CASE c.compressor_name WHEN "CM101" THEN c_r.suction END) AS suction2, 
MAX(CASE c.compressor_name WHEN "CM101" THEN c_r.discharge END) AS discharge2, 
MAX(CASE c.compressor_name WHEN "CM101" THEN c_r.discharge_pressure END) AS discharge_pressure2, 
MAX(CASE c.compressor_name WHEN "CM101" THEN c_r.oil_temperature END) AS oil_temperature2, 
MAX(CASE c.compressor_name WHEN "CM101" THEN c_r.water_temperature END) AS water_temperature2,
MAX(CASE c.compressor_name WHEN "CM101" THEN c_r.active END) AS active3, 
MAX(CASE c.compressor_name WHEN "CM101" THEN c_r.suction END) AS suction3, 
MAX(CASE c.compressor_name WHEN "CM101" THEN c_r.discharge END) AS discharge3, 
MAX(CASE c.compressor_name WHEN "CM101" THEN c_r.discharge_pressure END) AS discharge_pressure3, 
MAX(CASE c.compressor_name WHEN "CM101" THEN c_r.oil_temperature END) AS oil_temperature3, 
MAX(CASE c.compressor_name WHEN "CM101" THEN c_r.water_temperature END) AS water_temperature3
FROM plant_readings r 
LEFT JOIN ice_temps t ON r.id = t.plant_readings_id 
LEFT JOIN ice_surfaces i_s ON t.ice_surface_id = i_s.id 
LEFT JOIN compressor_readings c_r ON c_r.plant_readings_id = r.id
LEFT JOIN compressors c ON c_r.plant_readings_id = r.id
GROUP BY r.id DESC;