-- Well Pumps Selector Database Schema
-- MySQL Database for pump selection and performance data

CREATE DATABASE IF NOT EXISTS wellpumps_selector;
USE wellpumps_selector;

-- Table: pump_families
-- Stores pump family information (e.g., 3"WPS, 4"WPS, 10"WPS)
CREATE TABLE IF NOT EXISTS wps_pump_families (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE COMMENT 'e.g., 3"WPS, 4"WPS, 6"WPS, 8"WPS, 10"WPS',
    series VARCHAR(50) NOT NULL COMMENT 'e.g., WPS, WPS-CP, WPS P',
    well_diameter_inch DECIMAL(4,1) NOT NULL COMMENT 'Minimum well diameter in inches',
    well_diameter_mm INT NOT NULL COMMENT 'Minimum well diameter in mm',
    pipe_connection VARCHAR(20) COMMENT 'e.g., Rp 1¼", Rp 1½", Rp 2", Rp 2½", Rp 4", Rp 5", Rp 6"',
    frequency INT DEFAULT 50 COMMENT '50 Hz or 60 Hz',
    material VARCHAR(100) COMMENT 'e.g., SS AISI304/316, SS+composite, polycarbonate',
    max_flow DECIMAL(8,1) COMMENT 'Maximum flow in m³/h for the family',
    max_head DECIMAL(8,1) COMMENT 'Maximum head in meters for the family',
    description TEXT COMMENT 'Family description and features',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_series (series)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: pump_models
-- Stores individual pump model information
CREATE TABLE IF NOT EXISTS wps_pump_models (
    id INT AUTO_INCREMENT PRIMARY KEY,
    family_id INT NOT NULL,
    model_name VARCHAR(80) NOT NULL UNIQUE COMMENT 'e.g., 10"WPS 120-1-A',
    rated_flow DECIMAL(8,1) COMMENT 'Rated flow in m³/h (from family, e.g., 120 for 10"WPS 120)',
    stages INT COMMENT 'Number of stages/impellers',
    variant VARCHAR(10) COMMENT 'Model variant: empty, A, AA, B, etc.',
    power_kw DECIMAL(8,1) COMMENT 'Motor power in kW',
    power_hp DECIMAL(8,1) COMMENT 'Motor power in HP',
    full_load_current DECIMAL(8,1) COMMENT 'Full load current in A',
    voltage VARCHAR(20) DEFAULT '3x400V' COMMENT 'Motor voltage',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (family_id) REFERENCES wps_pump_families(id) ON DELETE CASCADE,
    INDEX idx_family_id (family_id),
    INDEX idx_model_name (model_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: pump_performance
-- Stores pump performance data (HEAD at different FLOW rates)
-- This is the selection chart data from product datasheets
CREATE TABLE IF NOT EXISTS wps_pump_performance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    model_id INT NOT NULL,
    flow_rate DECIMAL(8,1) NOT NULL COMMENT 'Flow in m³/h',
    head DECIMAL(8,1) NOT NULL COMMENT 'Head in meters at this flow rate',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (model_id) REFERENCES wps_pump_models(id) ON DELETE CASCADE,
    INDEX idx_model_id (model_id),
    INDEX idx_flow_rate (flow_rate),
    INDEX idx_flow_head (flow_rate, head)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: pump_dimensions
-- Stores physical dimensions and weights for each pump model
CREATE TABLE IF NOT EXISTS wps_pump_dimensions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    model_id INT NOT NULL UNIQUE,
    dim_a_mm INT COMMENT 'Dimension A in mm',
    dim_b_mm INT COMMENT 'Dimension B in mm',
    dim_c_mm INT COMMENT 'Dimension C (overall length) in mm',
    dim_d_mm INT COMMENT 'Dimension D in mm',
    dim_e_mm INT COMMENT 'Dimension E in mm',
    dim_e_star_mm INT COMMENT 'Dimension E* in mm',
    motor_type VARCHAR(10) COMMENT 'Motor type/size, e.g., 6", 8"',
    weight_pump_kg DECIMAL(8,1) COMMENT 'Weight of pump assembly only in kg',
    weight_electropump_kg DECIMAL(8,1) COMMENT 'Total weight of pump with motor in kg',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (model_id) REFERENCES wps_pump_models(id) ON DELETE CASCADE,
    INDEX idx_model_id (model_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: pump_datasheets
-- Optional: Links to datasheets and specifications
CREATE TABLE IF NOT EXISTS wps_pump_datasheets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    family_id INT,
    datasheet_name VARCHAR(255),
    datasheet_url VARCHAR(500),
    page_number INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (family_id) REFERENCES wps_pump_families(id) ON DELETE SET NULL,
    INDEX idx_family_id (family_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Note: All indexes are defined inline within the CREATE TABLE statements above.
