-- Parallelbetrieb Legacy + Gundi: dasselbe Bild liegt doppelt in remote_cam.image. -- Behalten wird der Legacy-Zwilling (source='entrance'): an seiner UUID haengen die -- KI-Ergebnisse (ai_image/ai_object). Der Gundi-Zwilling wandert nach image_superseded. -- -- Zwilling = gleiche imei, gleiche Dateigroesse, servertime hoechstens 10 min auseinander. -- Gemessen am 2026-10-16: 4291 Paare, jedes Bild hat hoechstens einen Partner. -- Nur Paare, deren Legacy-Seite schon migriert ist; Paare mit mehreren Kandidaten bleiben -- unangetastet. Wiederholbar. tools/migrate_legacy.py --dedupe fuehrt die Schritte in EINER -- Transaktion aus. -- step: twins CREATE TEMP TABLE _twins ON COMMIT DROP AS WITH pairs AS ( SELECT g.image_uuid AS gundi_uuid, e.image_uuid AS legacy_uuid FROM remote_cam.image g JOIN remote_cam.image e ON e.source = 'entrance' AND e.imei = g.imei AND e.size_bytes = g.size_bytes AND e.servertime BETWEEN g.servertime - interval '10 minutes' AND g.servertime + interval '10 minutes' WHERE g.source = 'gundi' ) SELECT gundi_uuid, legacy_uuid FROM (SELECT p.*, count(*) OVER (PARTITION BY p.gundi_uuid) AS n_gundi, count(*) OVER (PARTITION BY p.legacy_uuid) AS n_legacy FROM pairs p) x WHERE n_gundi = 1 AND n_legacy = 1; -- step: archive INSERT INTO remote_cam.image_superseded (image_uuid, superseded_by, image_row, detections) SELECT t.gundi_uuid, t.legacy_uuid, to_jsonb(i), (SELECT jsonb_agg(to_jsonb(d)) FROM remote_cam.detection d WHERE d.image_uuid = t.gundi_uuid) FROM _twins t JOIN remote_cam.image i ON i.image_uuid = t.gundi_uuid ON CONFLICT (image_uuid) DO NOTHING; -- step: delete -- detection haengt per ON DELETE CASCADE am image. DELETE FROM remote_cam.image i USING _twins t WHERE i.image_uuid = t.gundi_uuid;