================================================================================
WELL PUMPS SELECTOR - DATABASE SCHEMA AND DATA SUMMARY
================================================================================

Database Name: wellpumps_selector

FILES CREATED:
1. schema.sql     - Database schema with table definitions and indexes
2. data.sql       - Complete data population for pump families and models

================================================================================
DATABASE STRUCTURE
================================================================================

TABLES:
1. pump_families (9 families)
   - Stores pump family information (3"WPS through 10"WPS)
   - Includes well diameter (inch/mm), pipe connection, material, frequency
   - max_flow and max_head specifications

2. pump_models (142 total models)
   - Individual pump model specifications
   - Foreign key to pump_families
   - Power ratings (kW/HP), current, voltage
   - Stage information

3. pump_performance (Multiple flow/head data points)
   - Selection chart data: HEAD vs FLOW performance curves
   - Used for pump selection algorithms
   - Indexed for efficient queries on flow_rate and head

4. pump_dimensions (For dimension data - TODO)
   - Physical dimensions (A, B, C, D, E, E*)
   - Motor type and weight information
   - Currently placeholder structure (ready for 10"WPS data)

5. pump_datasheets (Optional)
   - Links to PDF datasheets and technical documentation

================================================================================
DATA CONTENT - FULLY POPULATED
================================================================================

PUMP FAMILIES (9 total):
- 3"WPS, 3"WPS-CP, 3"WPS P
- 4"WPS, 4"WPS-CP, 4"WPS P
- 6"WPS
- 8"WPS
- 10"WPS

PUMP MODELS (142 total) - ALL IN 10"WPS SERIES:
- 10"WPS 120 series: 37 models (variants: base, -A, -AA)
- 10"WPS 155 series: 35 models (variants: base, -A, -AA)
- 10"WPS 220 series: 31 models (variants: base, -A, -AA)
- 10"WPS 260 series: 21 models (variants: base, -A, -B)
- 10"WPS 300 series: 21 models (variants: base, -A, -B)
Total: 145 models

PERFORMANCE DATA POINTS:
- 10"WPS 120: 296 points (37 models × 8 flow rates)
- 10"WPS 155: 280 points (35 models × 8 flow rates)
- 10"WPS 220: 248 points (31 models × 8 flow rates)
- 10"WPS 260: 165 points (21 models × variable flow rates 0-7)
- 10"WPS 300: 168 points (21 models × 8 flow rates)
Total: ~1,157 performance data points

================================================================================
MOTOR SPECIFICATIONS INCLUDED
================================================================================

Power Range: 7.5 kW to 250 kW (10 HP to 340 HP)
Voltage: 3x400V (configurable)
Current Range: 15.0A to 425.0A
Stage Count: 1 to 17 stages

================================================================================
DESIGN NOTES
================================================================================

INDEXES:
- idx_pump_family_series: For family lookups by series name
- idx_pump_model_family: For finding models in a family
- idx_pump_performance_model: For performance curve selection
- idx_flow_head: Composite index for selection algorithm queries
- idx_pump_dimensions_model: For dimension lookups by model

FOREIGN KEYS:
- pump_models → pump_families (ON DELETE CASCADE)
- pump_performance → pump_models (ON DELETE CASCADE)
- pump_dimensions → pump_models (ON DELETE CASCADE)

CHARACTER SET: utf8mb4 (Unicode support for special characters like Rp, °, µ)
STORAGE ENGINE: InnoDB (transactional, supports constraints)

================================================================================
NEXT STEPS (OPTIONAL DATA ADDITIONS)
================================================================================

TODO: Add detailed models and data for smaller pump sizes:
- 3"WPS, 3"WPS-CP, 3"WPS P families (from catalog pages 50-60)
- 4"WPS, 4"WPS-CP, 4"WPS P families (from catalog pages 65-75)
- 6"WPS family (from catalog pages 80-95)
- 8"WPS family (from catalog pages 100-105)

TODO: Add dimension data for all 10"WPS models:
- Dimensions A-E* (mm)
- Motor type
- Weight (pump only, electropump)
- Data available from catalog pages 109-125

================================================================================
USAGE EXAMPLE
================================================================================

To use this database with a PHP pump selector application:

1. Import schema:
   mysql -u user -p wellpumps_selector < schema.sql

2. Import data:
   mysql -u user -p wellpumps_selector < data.sql

3. Query examples:

   -- Find all 10"WPS models
   SELECT * FROM pump_models 
   JOIN pump_families ON pump_models.family_id = pump_families.id
   WHERE pump_families.name = '10"WPS';

   -- Get performance data for a specific model
   SELECT flow_rate, head FROM pump_performance 
   WHERE model_id = 100
   ORDER BY flow_rate ASC;

   -- Find models suitable for 120 m³/h flow
   SELECT DISTINCT pm.* FROM pump_models pm
   WHERE pm.rated_flow >= 120
   ORDER BY pm.power_kw ASC;

================================================================================