CUB-228: add battery_calibration_offset to cameras table

- Add column to 001_create_tables.sql for fresh databases
- Add migration 002 for existing databases (idempotent via
  pragma_table_info check)
- Implement runIncrementalMigrations in db.go
- Add BatteryCalibrationOffset to Camera model
- Update all camera SELECT queries (cameras List, detail, MQTT
  subscriber getCamera, register)

internal/api/cameras.go

@@ -132,10 +132,10 @@ func GetCameraDetail(database *db.DB) http.HandlerFunc {
 		// Get camera info
 		var c models.Camera
 		err := database.QueryRowContext(r.Context(), `
-			SELECT camera_id, friendly_name, mac_address, created_at, updated_at
+			SELECT camera_id, friendly_name, mac_address, battery_calibration_offset, created_at, updated_at
 			FROM cameras WHERE camera_id = ?
 		`, cameraID).Scan(
-			&c.CameraID, &c.FriendlyName, &c.MacAddress,
+			&c.CameraID, &c.FriendlyName, &c.MacAddress, &c.BatteryCalibrationOffset,
 			&c.CreatedAt, &c.UpdatedAt,
 		)
 		if err == sql.ErrNoRows {

internal/db/db.go

@@ -14,6 +14,9 @@ import (
 //go:embed migrations/001_create_tables.sql
 var migration001 string
 
+//go:embed migrations/002_add_camera_calibration.sql
+var migration002 string
+
 // DB wraps the sql.DB with connection-level settings.
 type DB struct {
 	*sql.DB
@@ -62,6 +65,12 @@ func Open(path string) (*DB, error) {
 			return nil, err
 		}
 		log.Println("Migrations complete")
+	} else {
+		// Run incremental migrations on existing databases
+		if err := runIncrementalMigrations(db); err != nil {
+			db.Close()
+			return nil, err
+		}
 	}
 
 	return &DB{db}, nil
@@ -83,6 +92,25 @@ func migrate(db *sql.DB, sql string) error {
 	return nil
 }
 
+// runIncrementalMigrations applies migrations that haven't been run yet on
+// an existing database (one where the 001 schema already exists).
+func runIncrementalMigrations(db *sql.DB) error {
+	// Migration 002: add battery_calibration_offset if it doesn't exist
+	var colCount int
+	err := db.QueryRow(`SELECT COUNT(*) FROM pragma_table_info('cameras') WHERE name = 'battery_calibration_offset'`).Scan(&colCount)
+	if err != nil {
+		return err
+	}
+	if colCount == 0 {
+		log.Println("Running migration 002: add battery_calibration_offset")
+		if err := migrate(db, migration002); err != nil {
+			return err
+		}
+	}
+
+	return nil
+}
+
 // splitSQL splits a SQL string on semicolons, respecting quoted strings.
 func splitSQL(sql string) []string {
 	var stmts []string

internal/db/migrations/001_create_tables.sql

@@ -6,6 +6,7 @@ CREATE TABLE IF NOT EXISTS cameras (
     camera_id                    TEXT PRIMARY KEY,
     friendly_name                TEXT NOT NULL,
     mac_address                  TEXT UNIQUE,
+    battery_calibration_offset   REAL,
     created_at                   DATETIME NOT NULL DEFAULT (datetime('now')),
     updated_at                   DATETIME NOT NULL DEFAULT (datetime('now'))
 );

internal/db/migrations/002_add_camera_calibration.sql

@@ -0,0 +1,5 @@
+-- Migration 002: Add battery_calibration_offset to cameras table
+-- This column stores a per-camera calibration value for converting raw battery
+-- readings (e.g. GoPro Hero 3 byte at offset 57) into percentage values.
+
+ALTER TABLE cameras ADD COLUMN battery_calibration_offset REAL;

internal/mqtt/subscriber.go

@@ -404,9 +404,9 @@ func extractCameraID(topic string) string {
 func getCamera(db *db.DB, cameraID string) (models.Camera, error) {
 	var cam models.Camera
 	err := db.QueryRow(
-		"SELECT camera_id, friendly_name, COALESCE(mac_address,''), created_at, updated_at FROM cameras WHERE camera_id = ?",
+		"SELECT camera_id, friendly_name, COALESCE(mac_address,''), COALESCE(battery_calibration_offset, NULL), created_at, updated_at FROM cameras WHERE camera_id = ?",
 		cameraID,
-	).Scan(&cam.CameraID, &cam.FriendlyName, &cam.MacAddress, &cam.CreatedAt, &cam.UpdatedAt)
+	).Scan(&cam.CameraID, &cam.FriendlyName, &cam.MacAddress, &cam.BatteryCalibrationOffset, &cam.CreatedAt, &cam.UpdatedAt)
 	return cam, err
 }
 

pkg/models/camera.go

@@ -10,6 +10,7 @@ type Camera struct {
 	CameraID                  string    `json:"camera_id"`
 	FriendlyName              string    `json:"friendly_name"`
 	MacAddress                string    `json:"mac_address,omitempty"`
+	BatteryCalibrationOffset  *float64  `json:"battery_calibration_offset,omitempty"`
 	CreatedAt                 time.Time `json:"created_at"`
 	UpdatedAt                 time.Time `json:"updated_at"`
 }