-- Normalisiertes Fachmodell: Kamerastatus, Bildeigenschaften, Objektklassifikationen. -- Idempotent; wird vom gundi-receiver beim Start unter Advisory-Lock angewendet. -- Setzt das bestehende Schema remote_cam voraus (die App-Rolle hat kein CREATE auf DB-Ebene). -- Statusupdates / Kameraeigenschaften: eine Zeile je Statusmeldung (Zeitreihe). CREATE TABLE IF NOT EXISTS remote_cam.camera_status ( status_id bigserial PRIMARY KEY, imei text NOT NULL, reported_at timestamptz NOT NULL, received_at timestamptz NOT NULL DEFAULT now(), iccid text, init_key text, camera_name text, camera_model text, firmware_version text, camera_language text, customer_timezone text, mcc text, mnc text, lac text, cell_id text, camera_network text, battery_pct integer, signal_strength integer, temperature_c integer, coordinate_switch boolean, work_period boolean, work_start text, work_end text, image_format text, image_night text, thumbnail_size text, pir_interval text, time_scan text, lat double precision, lon double precision, settings_changed_at timestamptz, source text NOT NULL CHECK (source IN ('gundi', 'entrance')), source_ref text, raw jsonb ); -- Legacy hat 173 doppelte (imei, servertime)-Paare, daher keine globale Eindeutigkeit. CREATE UNIQUE INDEX IF NOT EXISTS camera_status_gundi_uq ON remote_cam.camera_status (imei, reported_at) WHERE source = 'gundi'; CREATE UNIQUE INDEX IF NOT EXISTS camera_status_entrance_uq ON remote_cam.camera_status (source_ref) WHERE source = 'entrance'; CREATE INDEX IF NOT EXISTS camera_status_imei_reported_idx ON remote_cam.camera_status (imei, reported_at DESC); -- Bildeigenschaften. CREATE TABLE IF NOT EXISTS remote_cam.image ( image_uuid uuid PRIMARY KEY, imei text NOT NULL, servertime timestamptz NOT NULL, ocr_ts timestamptz, ocr_state text NOT NULL DEFAULT 'PENDING' CHECK (ocr_state IN ('PENDING', 'OK', 'FAIL')), ocr_attempts integer NOT NULL DEFAULT 0, ocr_error text, camera_ts timestamptz, camera_name text, camera_make text, lat double precision, lon double precision, size_bytes integer, processed_key text, thumbnail_key text, source text NOT NULL CHECK (source IN ('gundi', 'entrance')), source_ref text, imported_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(), raw jsonb ); CREATE INDEX IF NOT EXISTS image_imei_servertime_idx ON remote_cam.image (imei, servertime DESC); CREATE INDEX IF NOT EXISTS image_servertime_idx ON remote_cam.image (servertime DESC); CREATE INDEX IF NOT EXISTS image_ocr_pending_idx ON remote_cam.image (updated_at) WHERE ocr_state = 'PENDING'; -- Objektklassifikationen. CREATE TABLE IF NOT EXISTS remote_cam.detection ( detection_id bigserial PRIMARY KEY, image_uuid uuid NOT NULL REFERENCES remote_cam.image (image_uuid) ON DELETE CASCADE, category text NOT NULL, confidence double precision CHECK (confidence BETWEEN 0 AND 1), bbox_x integer, bbox_y integer, bbox_w integer, bbox_h integer, model_version text, source text NOT NULL CHECK (source IN ('gundi', 'entrance')) ); CREATE INDEX IF NOT EXISTS detection_image_confidence_idx ON remote_cam.detection (image_uuid, confidence DESC); CREATE INDEX IF NOT EXISTS detection_category_idx ON remote_cam.detection (category); -- Einheitliches Label je Rohkategorie. detection.category bleibt die Rohbezeichnung der -- Quelle (ICU-Gundi: "Deer-Like", Legacy-Klassifikator: "deer"); Auswertungen nutzen das Label. CREATE TABLE IF NOT EXISTS remote_cam.category_label ( category text PRIMARY KEY, label text NOT NULL ); INSERT INTO remote_cam.category_label (category, label) VALUES ('Deer-Like', 'deer'), ('deer', 'deer'), ('person', 'human'), ('human', 'human'), ('car', 'machine'), ('machine', 'machine'), ('Bird', 'bird'), ('bird', 'bird'), ('Fox', 'fox'), ('fox', 'fox'), ('Wild Boar', 'wild boar'), ('wild boar', 'wild boar'), ('racoon', 'raccoon'), ('raccoon', 'raccoon') ON CONFLICT (category) DO NOTHING; -- Ohne Eintrag in category_label gilt die kleingeschriebene Rohbezeichnung. CREATE OR REPLACE VIEW remote_cam.detection_labeled AS SELECT d.*, COALESCE(cl.label, lower(d.category)) AS label FROM remote_cam.detection d LEFT JOIN remote_cam.category_label cl ON cl.category = d.category; -- Gundi-Bilder, die waehrend des Parallelbetriebs auch ueber den Legacy-Pfad kamen und -- zugunsten des Legacy-Zwillings (traegt die KI-Ergebnisse) entfernt wurden. Haelt die -- geloeschte image-Zeile samt Detections fuer Nachvollziehbarkeit und Rollback. CREATE TABLE IF NOT EXISTS remote_cam.image_superseded ( image_uuid uuid PRIMARY KEY, superseded_by uuid NOT NULL, removed_at timestamptz NOT NULL DEFAULT now(), image_row jsonb NOT NULL, detections jsonb ); -- Transportschicht des Push-Pfads: Rohevents + Zustandsmaschine. CREATE TABLE IF NOT EXISTS remote_cam.gundi_inbox ( object_id uuid PRIMARY KEY, event_type text NOT NULL, source text, recorded_at timestamptz, payload jsonb NOT NULL, received_at timestamptz NOT NULL DEFAULT now(), attachment_key text, attachment_received_at timestamptz, state text NOT NULL CHECK (state IN ( 'AWAITING_ATTACHMENT', 'RECEIVED', 'INGESTED', 'FAILED', 'ABANDONED', 'IGNORED')), attempts integer NOT NULL DEFAULT 0, last_error text, updated_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX IF NOT EXISTS gundi_inbox_open_idx ON remote_cam.gundi_inbox (state, updated_at) WHERE state IN ('AWAITING_ATTACHMENT', 'RECEIVED'); -- Bild + der zum Aufnahme-Serverzeitpunkt gueltige Kamerastatus. -- Tie-Breaker bei gleichem reported_at: der aus derselben Legacy-Zeile abgespaltene Status. CREATE OR REPLACE VIEW remote_cam.image_with_status AS SELECT i.image_uuid, i.imei, i.servertime, i.ocr_ts, i.ocr_state, i.camera_ts, i.camera_name, i.camera_make, i.lat, i.lon, i.size_bytes, i.processed_key, i.thumbnail_key, i.source, i.imported_at, s.status_id, s.reported_at AS status_reported_at, s.iccid, s.init_key, s.camera_name AS status_camera_name, s.camera_model, s.firmware_version, s.camera_language, s.customer_timezone, s.mcc, s.mnc, s.lac, s.cell_id, s.camera_network, s.battery_pct, s.signal_strength, s.temperature_c, s.coordinate_switch, s.work_period, s.work_start, s.work_end, s.image_format, s.image_night, s.thumbnail_size, s.pir_interval, s.time_scan, s.settings_changed_at FROM remote_cam.image i LEFT JOIN LATERAL ( SELECT cs.* FROM remote_cam.camera_status cs WHERE cs.imei = i.imei AND cs.reported_at <= i.servertime ORDER BY cs.reported_at DESC, (cs.source_ref = i.image_uuid::text) DESC NULLS LAST, cs.status_id DESC LIMIT 1 ) s ON TRUE;