-- Drones data model.
-- A "survey" is one drone flight over an area; "detections" are georeferenced
-- animals/objects found in that survey. The demo runs off the live simulator,
-- but real drone footage lands here via POST /api/ingest.

CREATE TABLE IF NOT EXISTS surveys (
  id            TEXT PRIMARY KEY,
  name          TEXT,
  center_lon    REAL NOT NULL,
  center_lat    REAL NOT NULL,
  radius_m      REAL NOT NULL,
  sensor        TEXT,                 -- 'thermal' | 'rgb' | 'thermal+rgb'
  drone         TEXT,                 -- e.g. 'DJI Mavic 2 Enterprise Advanced'
  started_at    TEXT,                 -- ISO8601
  ended_at      TEXT,
  created_at    TEXT DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS flight_points (
  survey_id     TEXT NOT NULL,
  seq           INTEGER NOT NULL,
  lon           REAL NOT NULL,
  lat           REAL NOT NULL,
  agl_m         REAL,                 -- altitude above ground
  t             TEXT,                 -- ISO8601 timestamp at this point
  PRIMARY KEY (survey_id, seq)
);

CREATE TABLE IF NOT EXISTS detections (
  id            TEXT PRIMARY KEY,
  survey_id     TEXT NOT NULL,
  type          TEXT NOT NULL,        -- deer, fox, rabbit, boar, kangaroo, person, vehicle, unknown
  lon           REAL NOT NULL,
  lat           REAL NOT NULL,
  confidence    REAL NOT NULL,        -- 0..1
  temp_c        REAL,                 -- thermal apparent temperature (null for rgb-only)
  t             TEXT,                 -- ISO8601 time of detection
  frame         INTEGER,              -- source video frame index
  created_at    TEXT DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_detections_survey ON detections(survey_id);
CREATE INDEX IF NOT EXISTS idx_flight_survey ON flight_points(survey_id);