CREATE TABLE sites (
id INT(11) NOT NULL AUTO_INCREMENT,
site_name VARCHAR(30) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE ice_surfaces (
id INT(11) NOT NULL AUTO_INCREMENT,
ice_surface_name VARCHAR(30) NOT NULL,
site_id INT(11),
PRIMARY KEY (id),
FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE SET NULL
);

CREATE TABLE compressors (
id INT(11) NOT NULL AUTO_INCREMENT,
compressor_name VARCHAR(40) NOT NULL,
operational TINYINT,
runtime_hours INT(7),
site_id INT(11),
PRIMARY KEY (id),
FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE SET NULL
);

CREATE TABLE plant_readings (
id INT(11) NOT NULL AUTO_INCREMENT,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIME,
made_by VARCHAR(30),
e_id INT(11),
time_of_day VARCHAR(30),
dolphin INT(5),
hot_brine INT(3),
cold_brine INT(3),
PRIMARY KEY (id)
);

CREATE TABLE compressor_readings (
id INT(11) NOT NULL AUTO_INCREMENT,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIME,
active TINYINT,
runtime_hours INT(7),
suction SMALLINT,
discharge SMALLINT,
discharge_pressure SMALLINT,
oil_temperature SMALLINT,
water_temperature SMALLINT,
plant_readings_id INT(11),
compressor_id INT(11),
PRIMARY KEY (id),
FOREIGN KEY (plant_readings_id) REFERENCES plant_readings (id) ON DELETE SET NULL,
FOREIGN KEY (compressor_id) REFERENCES compressors (id) ON DELETE SET NULL
);

CREATE TABLE ice_temps (
id INT(11) NOT NULL AUTO_INCREMENT,
ice_temp SMALLINT(3),
plant_readings_id INT(11),
ice_surface_id int(11),
PRIMARY KEY (id),
FOREIGN KEY (plant_readings_id) REFERENCES plant_readings (id) ON DELETE SET NULL,
FOREIGN KEY (ice_surface_id) REFERENCES ice_surfaces (id) ON DELETE SET NULL
);

CREATE TABLE comments (
id INT(11) NOT NULL AUTO_INCREMENT,
made_by VARCHAR(30) NOT NULL,
comment_text TEXT NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIME,
plant_readings_id INT(11),
PRIMARY KEY (id),
FOREIGN KEY (plant_readings_id) REFERENCES plant_readings (id) ON DELETE SET NULL
);