-- Migration: Add stranger_id column for unmatched face traces
-- Unmatched traces get stranger_id = trace_id (per-file sequential integer)
-- Unique constraint: (file_uuid, stranger_id) WHERE stranger_id IS NOT NULL
-- Date: 2026-05-16
-- Usage: psql -U accusys -d momentry -f migrate_add_stranger_id.sql

ALTER TABLE face_detections ADD COLUMN IF NOT EXISTS stranger_id INTEGER;
CREATE UNIQUE INDEX IF NOT EXISTS idx_face_detections_stranger
    ON face_detections (file_uuid, stranger_id) WHERE stranger_id IS NOT NULL;

-- Assign stranger_id = trace_id for existing unmatched traces
UPDATE face_detections SET stranger_id = trace_id
WHERE trace_id IS NOT NULL AND identity_id IS NULL AND stranger_id IS NULL;