-- ============================================================
-- Public Schema Migration V4.0 (Complete)
-- Purpose: Sync public schema with dev schema for v1.0.0 release
-- Date: 2026-04-30
-- ============================================================

BEGIN;

-- ============================================================
-- 1. videos table
-- ============================================================
ALTER TABLE public.videos ADD COLUMN IF NOT EXISTS parent_uuid VARCHAR(255);

-- ============================================================
-- 2. chunks table
-- ============================================================
ALTER TABLE public.chunks ADD COLUMN IF NOT EXISTS summary_text TEXT;
ALTER TABLE public.chunks ADD COLUMN IF NOT EXISTS metadata_version INTEGER DEFAULT 0;
ALTER TABLE public.chunks ADD COLUMN IF NOT EXISTS content_version INTEGER DEFAULT 0;

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

-- Add 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;

-- Migrate data from old columns to new columns
UPDATE public.face_detections 
SET file_uuid = video_uuid 
WHERE file_uuid IS NULL AND video_uuid IS NOT NULL;

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;

-- Make file_uuid NOT NULL after migration
ALTER TABLE public.face_detections ALTER COLUMN file_uuid SET NOT NULL;

-- Add indexes
CREATE INDEX IF NOT EXISTS idx_face_detections_file_uuid ON public.face_detections(file_uuid);
CREATE INDEX IF NOT EXISTS idx_face_detections_identity_id ON public.face_detections(identity_id) WHERE identity_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_face_detections_candidates ON public.face_detections(confidence DESC) WHERE identity_id IS NULL;

-- ============================================================
-- 4. pre_chunks table
-- ============================================================
ALTER TABLE public.pre_chunks ADD COLUMN IF NOT EXISTS timestamp DOUBLE PRECISION;

-- ============================================================
-- 5. file_identities table (V4.0 N:N relationship)
-- ============================================================
CREATE TABLE IF NOT EXISTS public.file_identities (
    id SERIAL PRIMARY KEY,
    file_uuid VARCHAR(255) NOT NULL,
    identity_id INTEGER NOT NULL REFERENCES public.identities(id),
    face_count INTEGER DEFAULT 0,
    speaker_count INTEGER DEFAULT 0,
    first_appearance BIGINT,
    last_appearance BIGINT,
    confidence DOUBLE PRECISION DEFAULT 1.0,
    metadata JSONB,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(file_uuid, identity_id)
);
CREATE INDEX IF NOT EXISTS idx_file_identities_file ON public.file_identities(file_uuid);
CREATE INDEX IF NOT EXISTS idx_file_identities_identity ON public.file_identities(identity_id);

-- ============================================================
-- 6. jobs table updates
-- ============================================================
ALTER TABLE public.jobs ADD COLUMN IF NOT EXISTS error_message TEXT;
ALTER TABLE public.jobs ADD COLUMN IF NOT EXISTS processor_list JSONB;

-- ============================================================
-- Verification
-- ============================================================
SELECT 'Migration Complete' AS status;

-- Check column counts
SELECT table_name, COUNT(*) AS column_count
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name IN ('videos', 'chunks', 'face_detections', 'pre_chunks', 'file_identities', 'jobs')
GROUP BY table_name
ORDER BY table_name;

-- Check data 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;