CalejoControl/database/init.sql

-- Calejo Control Adapter Database Initialization
-- This script creates the necessary tables and initial data

-- Create pump_stations table
CREATE TABLE IF NOT EXISTS pump_stations (
    station_id VARCHAR(50) PRIMARY KEY,
    station_name VARCHAR(100) NOT NULL,
    location VARCHAR(200),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create pumps table
CREATE TABLE IF NOT EXISTS pumps (
    station_id VARCHAR(50) NOT NULL,
    pump_id VARCHAR(50) NOT NULL,
    pump_name VARCHAR(100) NOT NULL,
    control_type VARCHAR(50) NOT NULL,
    default_setpoint_hz DECIMAL(5,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (station_id, pump_id),
    FOREIGN KEY (station_id) REFERENCES pump_stations(station_id)
);

-- Create pump_safety_limits table
CREATE TABLE IF NOT EXISTS pump_safety_limits (
    station_id VARCHAR(50) NOT NULL,
    pump_id VARCHAR(50) NOT NULL,
    hard_min_speed_hz DECIMAL(5,2) NOT NULL,
    hard_max_speed_hz DECIMAL(5,2) NOT NULL,
    hard_min_level_m DECIMAL(5,2),
    hard_max_level_m DECIMAL(5,2),
    hard_max_power_kw DECIMAL(8,2),
    hard_max_flow_m3h DECIMAL(8,2),
    emergency_stop_level_m DECIMAL(5,2),
    dry_run_protection_level_m DECIMAL(5,2),
    max_speed_change_hz_per_min DECIMAL(5,2) DEFAULT 10.0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (station_id, pump_id),
    FOREIGN KEY (station_id, pump_id) REFERENCES pumps(station_id, pump_id)
);

-- Create pump_plans table
CREATE TABLE IF NOT EXISTS pump_plans (
    plan_id SERIAL PRIMARY KEY,
    station_id VARCHAR(50) NOT NULL,
    pump_id VARCHAR(50) NOT NULL,
    interval_start TIMESTAMP NOT NULL,
    interval_end TIMESTAMP NOT NULL,
    suggested_speed_hz DECIMAL(5,2),
    target_flow_m3h DECIMAL(8,2),
    target_power_kw DECIMAL(8,2),
    target_level_m DECIMAL(5,2),
    plan_version INTEGER DEFAULT 1,
    plan_status VARCHAR(20) DEFAULT 'ACTIVE',
    optimization_run_id VARCHAR(100),
    plan_created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    plan_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (station_id, pump_id) REFERENCES pumps(station_id, pump_id)
);

-- Create emergency_stops table
CREATE TABLE IF NOT EXISTS emergency_stops (
    stop_id SERIAL PRIMARY KEY,
    station_id VARCHAR(50),
    pump_id VARCHAR(50),
    triggered_by VARCHAR(100) NOT NULL,
    triggered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    reason TEXT NOT NULL,
    cleared_by VARCHAR(100),
    cleared_at TIMESTAMP,
    notes TEXT,
    FOREIGN KEY (station_id, pump_id) REFERENCES pumps(station_id, pump_id)
);

-- Create audit_logs table
CREATE TABLE IF NOT EXISTS audit_logs (
    log_id SERIAL PRIMARY KEY,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    user_id VARCHAR(100),
    action VARCHAR(100) NOT NULL,
    resource_type VARCHAR(50),
    resource_id VARCHAR(100),
    details JSONB,
    ip_address INET,
    user_agent TEXT
);

-- Create users table for authentication
CREATE TABLE IF NOT EXISTS users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    hashed_password VARCHAR(255) NOT NULL,
    full_name VARCHAR(200),
    role VARCHAR(50) DEFAULT 'operator',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create discovery_results table
CREATE TABLE IF NOT EXISTS discovery_results (
    scan_id VARCHAR(100) PRIMARY KEY,
    status VARCHAR(50) NOT NULL,
    discovered_endpoints JSONB,
    scan_started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    scan_completed_at TIMESTAMP,
    error_message TEXT
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_pump_plans_station_pump ON pump_plans(station_id, pump_id);
CREATE INDEX IF NOT EXISTS idx_pump_plans_interval ON pump_plans(interval_start, interval_end);
CREATE INDEX IF NOT EXISTS idx_pump_plans_status ON pump_plans(plan_status);
CREATE INDEX IF NOT EXISTS idx_emergency_stops_cleared ON emergency_stops(cleared_at);
CREATE INDEX IF NOT EXISTS idx_audit_logs_timestamp ON audit_logs(timestamp);
CREATE INDEX IF NOT EXISTS idx_audit_logs_user ON audit_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_discovery_results_status ON discovery_results(status);
CREATE INDEX IF NOT EXISTS idx_discovery_results_timestamp ON discovery_results(scan_started_at);

-- Insert sample data for testing
INSERT INTO pump_stations (station_id, station_name, location) VALUES
    ('STATION_001', 'Main Pump Station', 'Downtown Area'),
    ('STATION_002', 'North Pump Station', 'Industrial Zone')
ON CONFLICT (station_id) DO NOTHING;

INSERT INTO pumps (station_id, pump_id, pump_name, control_type, default_setpoint_hz) VALUES
    ('STATION_001', 'PUMP_001', 'Main Pump 1', 'DIRECT_SPEED', 35.0),
    ('STATION_001', 'PUMP_002', 'Main Pump 2', 'LEVEL_CONTROLLED', 40.0),
    ('STATION_002', 'PUMP_003', 'North Pump 1', 'POWER_CONTROLLED', 45.0)
ON CONFLICT (station_id, pump_id) DO NOTHING;

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, hard_max_power_kw, hard_max_flow_m3h,
    emergency_stop_level_m, dry_run_protection_level_m, max_speed_change_hz_per_min
) VALUES
    ('STATION_001', 'PUMP_001', 20.0, 70.0, 0.5, 5.0, 100.0, 500.0, 4.8, 0.6, 10.0),
    ('STATION_001', 'PUMP_002', 25.0, 65.0, 0.5, 4.5, 90.0, 450.0, 4.3, 0.6, 10.0),
    ('STATION_002', 'PUMP_003', 30.0, 60.0, 0.5, 4.0, 80.0, 400.0, 3.8, 0.6, 10.0)
ON CONFLICT (station_id, pump_id) DO NOTHING;

-- Create default admin user (password: admin123)
INSERT INTO users (username, email, hashed_password, full_name, role) VALUES
    ('admin', 'admin@calejo-control.com', '$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewdBPj6UKmR7qQO2', 'System Administrator', 'admin')
ON CONFLICT (username) DO NOTHING;