-- MySQL schema for chem-api

CREATE TABLE IF NOT EXISTS User (
    uid INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    email VARCHAR(255),
    phone VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS Pool (
    id INT AUTO_INCREMENT PRIMARY KEY,
    pool_name VARCHAR(255) NOT NULL,
    pool_size INT,
    userId INT,
    filter_type VARCHAR(100),
    filter_brand VARCHAR(100),
    filter_model VARCHAR(100),
    filter_capacity INT,
    filter_media VARCHAR(100),
    filter_media_unit VARCHAR(50),
    filter_cartridge_brand VARCHAR(100),
    filter_cartridge_model VARCHAR(100),
    filter_cartridge_capacity INT,
    filter_de_brand VARCHAR(100),
    filter_de_model VARCHAR(100),
    filter_de_capacity INT,
    filter_de_media VARCHAR(100),
    filter_de_media_unit VARCHAR(50),
    chlorinator_type VARCHAR(100),
    chlorinator_brand VARCHAR(100),
    chlorinator_model VARCHAR(100),
    chlorinator_capacity INT,
    chlorinator_output INT,
    chlorinator_output_unit VARCHAR(50),
    chlorinator_salt_level INT,
    chlorinator_tablet_brand VARCHAR(100),
    chlorinator_tablet_model VARCHAR(100),
    chlorinator_tablet_capacity INT,
    chlorinator_tablet_output INT,
    chlorinator_tablet_output_unit VARCHAR(50),
    heater_type VARCHAR(100),
    heater_brand VARCHAR(100),
    heater_model VARCHAR(100),
    heater_capacity INT,
    heater_capacity_unit VARCHAR(50),
    heater_output INT,
    heater_output_unit VARCHAR(50),
    heater_fuel_type VARCHAR(50),
    pump_brand VARCHAR(100),
    pump_model VARCHAR(100),
    pump_output INT,
    pump_output_unit VARCHAR(50),
    pump_speed INT,
    pump_type VARCHAR(100),
    pump_horsepower INT,
    pump_voltage INT,
    pump_amperage INT,
    pump_service_factor INT,
    pump_service_factor_unit VARCHAR(50),
    controller_brand VARCHAR(100),
    controller_model VARCHAR(100),
    controller_type VARCHAR(100),
    pool_notes TEXT,
    zip_code INT,
    pool_liner_type VARCHAR(100),
    pool_cover_type VARCHAR(100),
    uv_sanitizer_type VARCHAR(100),
    uv_sanitizer_brand VARCHAR(100),
    uv_sanitizer_model VARCHAR(100),
    uv_sanitizer_capacity INT,
    uv_sanitizer_output INT,
    uv_sanitizer_output_unit VARCHAR(50),
    uv_sanitizer_bulb_brand VARCHAR(100),
    uv_sanitizer_bulb_model VARCHAR(100),
    sunlight_exposure VARCHAR(100),
    FOREIGN KEY (userId) REFERENCES User(uid) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS Records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    pool_id INT,
    dateTime VARCHAR(50),
    water_temp DOUBLE,
    water_temp_unit VARCHAR(20),
    air_temp DOUBLE,
    air_temp_unit VARCHAR(20),
    ph_level DOUBLE,
    chlorine_level DOUBLE,
    alkalinity_level DOUBLE,
    calcium_level DOUBLE,
    cyanuric_acid_level DOUBLE,
    salt_level INT,
    total_dissolved_solids INT,
    uv_sanitizer_output INT,
    filter_pressure INT,
    filter_pressure_unit VARCHAR(20),
    filter_flow_rate INT,
    chlorinator_output INT,
    chlorinator_salt_level INT,
    pool_notes TEXT,
    FOREIGN KEY (pool_id) REFERENCES Pool(id) ON DELETE CASCADE
);
