CalejoControl/database/test_data.sql

-- Calejo Control Adapter Test Data
-- Version: 2.0
-- Date: October 26, 2025

-- Insert test pump stations
INSERT INTO pump_stations (station_id, station_name, location, latitude, longitude, timezone) VALUES
('STATION_001', 'North Wastewater Treatment Plant', 'Via Roma 123, Milano', 45.4642035, 9.189982, 'Europe/Rome'),
('STATION_002', 'South Pumping Station', 'Corso Italia 456, Milano', 45.448759, 9.163420, 'Europe/Rome'),
('STATION_003', 'Industrial Zone Station', 'Via Industria 789, Milano', 45.485649, 9.204041, 'Europe/Rome');

-- Insert test pumps
INSERT INTO pumps (
    station_id, pump_id, pump_name, pump_type, control_type, 
    manufacturer, model, rated_power_kw, min_speed_hz, max_speed_hz, 
    default_setpoint_hz, control_parameters
) VALUES
-- Station 001 - Level controlled pumps
('STATION_001', 'PUMP_001', 'Main Pump 1', 'SUBMERSIBLE', 'LEVEL_CONTROLLED',
 'KSB', 'Amarex N 200-400', 75.0, 20.0, 50.0, 35.0,
 '{"pid_gains": {"kp": 2.5, "ki": 0.1, "kd": 0.5}, "level_setpoint": 2.5, "deadband": 0.2}'::JSONB),

('STATION_001', 'PUMP_002', 'Main Pump 2', 'SUBMERSIBLE', 'LEVEL_CONTROLLED',
 'KSB', 'Amarex N 200-400', 75.0, 20.0, 50.0, 35.0,
 '{"pid_gains": {"kp": 2.5, "ki": 0.1, "kd": 0.5}, "level_setpoint": 2.5, "deadband": 0.2}'::JSONB),

-- Station 002 - Power controlled pumps
('STATION_002', 'PUMP_001', 'Booster Pump 1', 'CENTRIFUGAL', 'POWER_CONTROLLED',
 'Grundfos', 'CR 45-3-2', 45.0, 25.0, 45.0, 30.0,
 '{"power_setpoint": 35.0, "efficiency_target": 0.85, "ramp_rate": 2.0}'::JSONB),

('STATION_002', 'PUMP_002', 'Booster Pump 2', 'CENTRIFUGAL', 'POWER_CONTROLLED',
 'Grundfos', 'CR 45-3-2', 45.0, 25.0, 45.0, 30.0,
 '{"power_setpoint": 35.0, "efficiency_target": 0.85, "ramp_rate": 2.0}'::JSONB),

-- Station 003 - Direct speed controlled pumps
('STATION_003', 'PUMP_001', 'Process Pump 1', 'SUBMERSIBLE', 'DIRECT_SPEED',
 'Flygt', 'N-Pump 3085', 55.0, 20.0, 50.0, 40.0,
 '{"speed_ramp_rate": 5.0}'::JSONB),

('STATION_003', 'PUMP_002', 'Process Pump 2', 'SUBMERSIBLE', 'DIRECT_SPEED',
 'Flygt', 'N-Pump 3085', 55.0, 20.0, 50.0, 40.0,
 '{"speed_ramp_rate": 5.0}'::JSONB);

-- Insert safety limits
INSERT INTO pump_safety_limits (
    station_id, pump_id, hard_min_speed_hz, hard_max_speed_hz,
    hard_min_level_m, hard_max_level_m, emergency_stop_level_m, dry_run_protection_level_m,
    hard_max_power_kw, hard_max_flow_m3h, max_speed_change_hz_per_min,
    set_by, approved_by, approved_at
) VALUES
-- Station 001 pumps
('STATION_001', 'PUMP_001', 20.0, 50.0, 1.0, 4.0, 4.5, 0.8, 80.0, 400.0, 5.0,
 'system_admin', 'safety_engineer', NOW()),

('STATION_001', 'PUMP_002', 20.0, 50.0, 1.0, 4.0, 4.5, 0.8, 80.0, 400.0, 5.0,
 'system_admin', 'safety_engineer', NOW()),

-- Station 002 pumps
('STATION_002', 'PUMP_001', 25.0, 45.0, 0.5, 3.5, 4.0, 0.5, 50.0, 250.0, 3.0,
 'system_admin', 'safety_engineer', NOW()),

('STATION_002', 'PUMP_002', 25.0, 45.0, 0.5, 3.5, 4.0, 0.5, 50.0, 250.0, 3.0,
 'system_admin', 'safety_engineer', NOW()),

-- Station 003 pumps
('STATION_003', 'PUMP_001', 20.0, 50.0, 0.8, 3.8, 4.2, 0.6, 60.0, 300.0, 4.0,
 'system_admin', 'safety_engineer', NOW()),

('STATION_003', 'PUMP_002', 20.0, 50.0, 0.8, 3.8, 4.2, 0.6, 60.0, 300.0, 4.0,
 'system_admin', 'safety_engineer', NOW());

-- Insert sample pump plans with version-based updates
INSERT INTO pump_plans (
    station_id, pump_id, interval_start, interval_end,
    target_flow_m3h, target_power_kw, target_level_m, suggested_speed_hz,
    optimization_run_id, plan_version, plan_status
) VALUES
-- Initial plans for all pumps (version 1)
('STATION_001', 'PUMP_001', NOW() - INTERVAL '5 minutes', NOW() + INTERVAL '55 minutes', 320.5, 65.2, 2.5, 42.3, 1001, 1, 'ACTIVE'),
('STATION_001', 'PUMP_002', NOW() - INTERVAL '5 minutes', NOW() + INTERVAL '55 minutes', 315.8, 63.8, 2.5, 41.7, 1001, 1, 'ACTIVE'),
('STATION_002', 'PUMP_001', NOW() - INTERVAL '5 minutes', NOW() + INTERVAL '55 minutes', 180.2, 32.5, 1.8, 35.2, 1001, 1, 'ACTIVE'),
('STATION_002', 'PUMP_002', NOW() - INTERVAL '5 minutes', NOW() + INTERVAL '55 minutes', 175.8, 31.8, 1.8, 34.8, 1001, 1, 'ACTIVE'),
('STATION_003', 'PUMP_001', NOW() - INTERVAL '5 minutes', NOW() + INTERVAL '55 minutes', 245.6, 48.3, 2.2, 38.5, 1001, 1, 'ACTIVE'),
('STATION_003', 'PUMP_002', NOW() - INTERVAL '5 minutes', NOW() + INTERVAL '55 minutes', 242.1, 47.6, 2.2, 38.1, 1001, 1, 'ACTIVE'),

-- Future plans
('STATION_001', 'PUMP_001', NOW() + INTERVAL '1 hour', NOW() + INTERVAL '2 hours', 335.2, 68.1, 2.6, 43.5, 1001, 1, 'ACTIVE'),
('STATION_002', 'PUMP_001', NOW() + INTERVAL '1 hour', NOW() + INTERVAL '2 hours', 185.6, 33.8, 1.9, 36.2, 1001, 1, 'ACTIVE'),
('STATION_003', 'PUMP_001', NOW() + INTERVAL '1 hour', NOW() + INTERVAL '2 hours', 252.3, 49.8, 2.3, 39.2, 1001, 1, 'ACTIVE');

-- Insert sample optimization_plans for demonstration
INSERT INTO optimization_plans (
    station_id, resource_id, resource_type, interval_start, interval_end,
    optimization_targets, optimization_run_id, plan_version
) VALUES
-- Example pump optimization plan with JSON targets
('STATION_001', 'PUMP_001', 'PUMP', NOW() - INTERVAL '5 minutes', NOW() + INTERVAL '55 minutes',
 '{"target_type": "SPEED", "target_value": 42.3, "secondary_targets": {"power_kw": 65.2, "flow_m3h": 320.5, "level_m": 2.5}, "constraints": {"max_rate_of_change": 5.0, "deadband": 0.2}}'::JSONB,
 1001, 1),

-- Example valve optimization plan
('STATION_001', 'VALVE_001', 'VALVE', NOW() - INTERVAL '5 minutes', NOW() + INTERVAL '55 minutes',
 '{"target_type": "POSITION", "target_value": 75.0, "secondary_targets": {"flow_m3h": 200.0, "pressure_bar": 1.5}, "constraints": {"max_rate_of_change": 10.0, "deadband": 1.0}}'::JSONB,
 1001, 1),

-- Example blower optimization plan
('STATION_002', 'BLOWER_001', 'BLOWER', NOW() - INTERVAL '5 minutes', NOW() + INTERVAL '55 minutes',
 '{"target_type": "SPEED", "target_value": 35.0, "secondary_targets": {"power_kw": 25.0, "pressure_bar": 1.2}, "constraints": {"max_rate_of_change": 3.0, "deadband": 0.5}}'::JSONB,
 1001, 1);

-- Insert sample feedback data
INSERT INTO pump_feedback (
    station_id, pump_id, timestamp,
    actual_speed_hz, actual_power_kw, actual_flow_m3h, wet_well_level_m, pump_running,
    alarm_active, alarm_code
) VALUES
-- Recent feedback for all pumps
('STATION_001', 'PUMP_001', NOW() - INTERVAL '2 minutes', 42.1, 64.8, 318.2, 2.48, TRUE, FALSE, NULL),
('STATION_001', 'PUMP_002', NOW() - INTERVAL '2 minutes', 41.5, 63.2, 312.5, 2.51, TRUE, FALSE, NULL),
('STATION_002', 'PUMP_001', NOW() - INTERVAL '2 minutes', 35.0, 32.1, 178.6, 1.79, TRUE, FALSE, NULL),
('STATION_002', 'PUMP_002', NOW() - INTERVAL '2 minutes', 34.6, 31.4, 173.2, 1.82, TRUE, FALSE, NULL),
('STATION_003', 'PUMP_001', NOW() - INTERVAL '2 minutes', 38.3, 47.9, 242.8, 2.21, TRUE, FALSE, NULL),
('STATION_003', 'PUMP_002', NOW() - INTERVAL '2 minutes', 37.9, 47.2, 239.5, 2.19, TRUE, FALSE, NULL),

-- Historical feedback (for testing trends)
('STATION_001', 'PUMP_001', NOW() - INTERVAL '1 hour', 40.2, 62.1, 305.8, 2.45, TRUE, FALSE, NULL),
('STATION_002', 'PUMP_001', NOW() - INTERVAL '1 hour', 33.5, 30.2, 170.1, 1.75, TRUE, FALSE, NULL),
('STATION_003', 'PUMP_001', NOW() - INTERVAL '1 hour', 37.2, 45.8, 235.6, 2.15, TRUE, FALSE, NULL);

-- Insert sample audit log entries
INSERT INTO audit_log (
    event_type, severity, station_id, pump_id, user_id, ip_address,
    protocol, action, resource, result, event_data
) VALUES
('SYSTEM_STARTUP', 'INFO', NULL, NULL, 'system', '127.0.0.1',
 'INTERNAL', 'startup', 'system', 'SUCCESS',
 '{"version": "2.0.0", "components_initialized": ["database", "safety", "protocols"]}'::JSONB),

('SAFETY_LIMITS_LOADED', 'INFO', NULL, NULL, 'system', '127.0.0.1',
 'INTERNAL', 'load_safety_limits', 'safety_framework', 'SUCCESS',
 '{"pump_count": 6, "limits_loaded": 6}'::JSONB),

('AUTO_DISCOVERY', 'INFO', NULL, NULL, 'system', '127.0.0.1',
 'INTERNAL', 'discover_pumps', 'auto_discovery', 'SUCCESS',
 '{"stations_discovered": 3, "pumps_discovered": 6}'::JSONB),

('SETPOINT_UPDATE', 'INFO', 'STATION_001', 'PUMP_001', 'optimization_engine', '192.168.1.100',
 'REST_API', 'update_setpoint', 'pump_control', 'SUCCESS',
 '{"requested_setpoint": 42.3, "enforced_setpoint": 42.3, "violations": []}'::JSONB);

-- Verify data insertion
SELECT 'Pump Stations:' as "Verification", COUNT(*) as count FROM pump_stations
UNION ALL
SELECT 'Pumps:', COUNT(*) FROM pumps
UNION ALL
SELECT 'Safety Limits:', COUNT(*) FROM pump_safety_limits
UNION ALL
SELECT 'Pump Plans:', COUNT(*) FROM pump_plans
UNION ALL
SELECT 'Feedback Records:', COUNT(*) FROM pump_feedback
UNION ALL
SELECT 'Audit Log Entries:', COUNT(*) FROM audit_log;