CREATE TABLE IF NOT EXISTS process_snapshot (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    captured_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    equipment_id TEXT NOT NULL,
    running INTEGER NOT NULL,
    fault_active INTEGER NOT NULL,
    speed_actual REAL NOT NULL,
    pressure_bar REAL NOT NULL,
    temperature_c REAL NOT NULL,
    flow_m3h REAL NOT NULL
);

INSERT INTO process_snapshot (
    equipment_id,
    running,
    fault_active,
    speed_actual,
    pressure_bar,
    temperature_c,
    flow_m3h
) VALUES (
    :equipment_id,
    :running,
    :fault_active,
    :speed_actual,
    :pressure_bar,
    :temperature_c,
    :flow_m3h
);

SELECT
    id, captured_at, equipment_id, running, fault_active,
    speed_actual, pressure_bar, temperature_c, flow_m3h
FROM process_snapshot
WHERE equipment_id = :equipment_id
ORDER BY id DESC
LIMIT 1;

SELECT
    captured_at, running, fault_active, speed_actual,
    pressure_bar, temperature_c, flow_m3h
FROM process_snapshot
WHERE equipment_id = :equipment_id
  AND captured_at >= datetime(
      'now',
      '-' || :lookback_minutes || ' minutes'
  )
ORDER BY captured_at DESC;

-- equipment_id: String (Pump_01)
-- lookback_minutes: Int4 (10)