-- ============================================================
-- Public Schema Migration: V3.x → V4.0
-- Purpose: Sync public schema with dev schema for v1.0.0 release
-- Date: 2026-04-30
-- ============================================================

BEGIN;

-- ============================================================
-- 1. videos table: Add missing columns
-- ============================================================

-- Add parent_uuid column (V4.0: for parent-child video relationships)
ALTER TABLE public.videos
ADD COLUMN IF NOT EXISTS parent_uuid VARCHAR(255);

-- ============================================================
-- 2. chunks table: Add missing columns
-- ============================================================

-- Add summary_text (V4.0: LLM-generated summaries)
ALTER TABLE public.chunks
ADD COLUMN IF NOT EXISTS summary_text TEXT;

-- Add metadata_version (V4.0: track metadata updates)
ALTER TABLE public.chunks
ADD COLUMN IF NOT EXISTS metadata_version INTEGER DEFAULT 0;

-- Add content_version (V4.0: track content updates)
ALTER TABLE public.chunks
ADD COLUMN IF NOT EXISTS content_version INTEGER DEFAULT 0;

-- ============================================================
-- 3. face_detections table: Migrate V3.x → V4.0 structure
-- ============================================================

-- Step 3a: Add new V4.0 columns
ALTER TABLE public.face_detections
ADD COLUMN IF NOT EXISTS file_uuid VARCHAR(255);

ALTER TABLE public.face_detections
ADD COLUMN IF NOT EXISTS bbox JSONB;

-- Step 3b: Migrate video_uuid → file_uuid
UPDATE public.face_detections
SET file_uuid = video_uuid
WHERE file_uuid IS NULL AND video_uuid IS NOT NULL;

-- Step 3c: Create bbox JSONB from x/y/width/height columns
UPDATE public.face_detections
SET bbox = jsonb_build_object(
    'x', x,
    'y', y,
    'width', width,
    'height', height
)
WHERE bbox IS NULL AND x IS NOT NULL;

-- Step 3d: Add NOT NULL constraint to file_uuid (after migration)
ALTER TABLE public.face_detections
ALTER COLUMN file_uuid SET NOT NULL;

-- Step 3e: Add index for new file_uuid column
CREATE INDEX IF NOT EXISTS idx_face_detections_file_uuid
ON public.face_detections(file_uuid);

-- Note: We keep old columns (video_uuid, x, y, width, height, timestamp_secs, 
-- embedding, identity_confidence, cluster_id) for backward compatibility.
-- They can be removed in a future cleanup after verifying no dependencies.

-- ============================================================
-- 4. Verify migration
-- ============================================================

-- Check video count
SELECT COUNT(*) AS total_videos FROM public.videos;

-- Check chunk columns
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'chunks'
AND column_name IN ('summary_text', 'metadata_version', 'content_version')
ORDER BY column_name;

-- Check face_detections migration
SELECT 
    COUNT(*) AS total_faces,
    COUNT(CASE WHEN file_uuid IS NOT NULL THEN 1 END) AS with_file_uuid,
    COUNT(CASE WHEN bbox IS NOT NULL THEN 1 END) AS with_bbox
FROM public.face_detections;

COMMIT;