-- final_sync_public.sql

-- 1. Ensure Identities exist in public (using video_identities table)
DO $$
BEGIN
    -- Check/Add Audrey Hepburn
    IF NOT EXISTS (SELECT 1 FROM public.video_identities WHERE name = 'Audrey Hepburn' AND uuid = '384b0ff44aaaa1f1') THEN
        INSERT INTO public.video_identities (uuid, name, metadata)
        VALUES ('384b0ff44aaaa1f1', 'Audrey Hepburn', '{"role": "Reggie Lampert"}');
    END IF;
END $$;

-- 2. Sync Appearances (Copy from dev to public)
-- Since public.person_appearances is empty, we can just copy.
INSERT INTO public.person_appearances (person_id, video_uuid, start_time, end_time, duration, confidence)
SELECT person_id, video_uuid, start_time, end_time, duration, confidence
FROM dev.person_appearances
WHERE video_uuid = '384b0ff44aaaa1f1'
AND person_id IN ('Person_17', 'Person_4'); -- Only sync the main ones we merged

-- 3. Update Person Counts and Names
-- Audrey Hepburn (Person_17)
UPDATE public.person_identities
SET name = 'Audrey Hepburn',
    appearance_count = (SELECT count(*) FROM public.person_appearances WHERE person_id = 'Person_17' AND video_uuid = '384b0ff44aaaa1f1')
WHERE person_id = 'Person_17' AND video_uuid = '384b0ff44aaaa1f1';

-- Cary Grant (Person_4)
UPDATE public.person_identities
SET name = 'Cary Grant',
    appearance_count = (SELECT count(*) FROM public.person_appearances WHERE person_id = 'Person_4' AND video_uuid = '384b0ff44aaaa1f1')
WHERE person_id = 'Person_4' AND video_uuid = '384b0ff44aaaa1f1';

-- 4. Sync Bindings
-- First, get the ID of the new Audrey Hepburn identity
DO $$
DECLARE
    audrey_id INT;
    cary_id INT;
BEGIN
    -- Get IDs
    SELECT id INTO audrey_id FROM public.video_identities WHERE name = 'Audrey Hepburn' AND uuid = '384b0ff44aaaa1f1' LIMIT 1;
    SELECT id INTO cary_id FROM public.video_identities WHERE name = 'Cary Grant' AND uuid = '384b0ff44aaaa1f1' LIMIT 1;

    -- Bind Person_17 to Audrey
    INSERT INTO public.identity_bindings (identity_id, uuid, binding_type, binding_value)
    VALUES (audrey_id, '384b0ff44aaaa1f1', 'person', 'Person_17')
    ON CONFLICT (uuid, binding_type, binding_value) DO UPDATE SET identity_id = audrey_id;

    -- Bind Person_4 to Cary
    INSERT INTO public.identity_bindings (identity_id, uuid, binding_type, binding_value)
    VALUES (cary_id, '384b0ff44aaaa1f1', 'person', 'Person_4')
    ON CONFLICT (uuid, binding_type, binding_value) DO UPDATE SET identity_id = cary_id;
END $$;