#!/bin/bash

DB="/home/kot/birdnet-go-app/data/birdnet.db"
OUT="/home/kot/birdnet-display/status.json"

sqlite3 -json "$DB" "
SELECT
  d.id,
  datetime(d.detected_at, 'unixepoch', 'localtime') AS time,
  l.scientific_name AS species,
  ROUND(d.confidence * 100, 1) AS confidence,
  s.display_name AS source,
  m.name || ' ' || m.version AS model,
  d.clip_name AS clip
FROM detections d
LEFT JOIN labels l ON d.label_id = l.id
LEFT JOIN audio_sources s ON d.source_id = s.id
LEFT JOIN ai_models m ON d.model_id = m.id
ORDER BY d.detected_at DESC
LIMIT 1;
" > "$OUT"