-- Migration: Add cut_id column for per-cut trace scoping
-- Date: 2026-05-16
-- Usage: psql -U accusys -d momentry -f migrate_add_cut_id.sql
-- Note: runs with current search_path

ALTER TABLE face_detections ADD COLUMN IF NOT EXISTS cut_id INTEGER;

-- Back-fill existing data where cuts exist
UPDATE face_detections fd
SET cut_id = c.id
FROM chunk c
WHERE c.file_uuid = fd.file_uuid
  AND c.chunk_type = 'cut'
  AND fd.frame_number BETWEEN c.start_frame AND c.end_frame
  AND fd.cut_id IS NULL;