-- Migration: Create independent cuts table
-- Cut = 同鏡頭連續拍攝的一組 frame
-- Date: 2026-05-16
-- Usage: psql -U accusys -d momentry -f migrate_create_cuts_table.sql

CREATE TABLE IF NOT EXISTS cuts (
    id SERIAL PRIMARY KEY,
    file_uuid VARCHAR(32) NOT NULL,
    cut_number INTEGER NOT NULL,
    start_frame BIGINT NOT NULL,
    end_frame BIGINT NOT NULL,
    start_time DOUBLE PRECISION,
    end_time DOUBLE PRECISION,
    fps DOUBLE PRECISION,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(file_uuid, cut_number)
);

-- Migrate from chunk (chunk_type='cut')
INSERT INTO cuts (file_uuid, cut_number, start_frame, end_frame, start_time, end_time, fps, metadata)
SELECT c.file_uuid, 
       (c.content->>'scene_number')::int as cut_number,
       c.start_frame, c.end_frame,
       c.start_time, c.end_time,
       c.fps,
       c.metadata
FROM chunk c
WHERE c.chunk_type = 'cut'
ON CONFLICT (file_uuid, cut_number) DO NOTHING;

-- Update face_detections.cut_id to reference cuts.id
UPDATE face_detections fd
SET cut_id = cs.id
FROM cuts cs
WHERE cs.file_uuid = fd.file_uuid
  AND fd.frame_number BETWEEN cs.start_frame AND cs.end_frame
  AND (fd.cut_id IS NULL OR fd.cut_id != cs.id);