{"-- Create table for Frigate recordings (doesn't exist yet)
CREATE TABLE IF NOT EXISTS public.frigate_recordings (
    id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
    recording_id TEXT NOT NULL,
    frigate_instance_id UUID NOT NULL,
    camera TEXT NOT NULL,
    start_time TIMESTAMP WITH TIME ZONE NOT NULL,
    end_time TIMESTAMP WITH TIME ZONE NOT NULL,
    duration INTEGER,
    size BIGINT,
    path TEXT,
    url TEXT,
    metadata JSONB,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    UNIQUE(recording_id, frigate_instance_id)
)","-- Create table for Frigate previews (doesn't exist yet)
CREATE TABLE IF NOT EXISTS public.frigate_previews (
    id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
    preview_id TEXT NOT NULL,
    frigate_instance_id UUID NOT NULL,
    camera TEXT NOT NULL,
    start_time TIMESTAMP WITH TIME ZONE NOT NULL,
    end_time TIMESTAMP WITH TIME ZONE NOT NULL,
    path TEXT,
    url TEXT,
    metadata JSONB,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    UNIQUE(preview_id, frigate_instance_id)
)","-- Create table for Frigate exports (doesn't exist yet)
CREATE TABLE IF NOT EXISTS public.frigate_exports (
    id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
    export_id TEXT NOT NULL,
    frigate_instance_id UUID NOT NULL,
    camera TEXT NOT NULL,
    start_time TIMESTAMP WITH TIME ZONE NOT NULL,
    end_time TIMESTAMP WITH TIME ZONE NOT NULL,
    path TEXT,
    status TEXT,
    url TEXT,
    metadata JSONB,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    UNIQUE(export_id, frigate_instance_id)
)","-- Create table for Frigate reviews (doesn't exist yet)
CREATE TABLE IF NOT EXISTS public.frigate_reviews (
    id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
    review_id TEXT NOT NULL,
    frigate_instance_id UUID NOT NULL,
    camera TEXT NOT NULL,
    start_time TIMESTAMP WITH TIME ZONE NOT NULL,
    end_time TIMESTAMP WITH TIME ZONE NOT NULL,
    severity TEXT,
    has_been_reviewed BOOLEAN DEFAULT false,
    data JSONB,
    metadata JSONB,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    UNIQUE(review_id, frigate_instance_id)
)","-- Add new columns to existing frigate_timeline table to support our sync data
ALTER TABLE public.frigate_timeline 
ADD COLUMN IF NOT EXISTS frigate_instance_id UUID,
ADD COLUMN IF NOT EXISTS class_type TEXT,
ADD COLUMN IF NOT EXISTS data JSONB,
ADD COLUMN IF NOT EXISTS metadata JSONB,
ADD COLUMN IF NOT EXISTS entry_id TEXT","-- Enable Row Level Security on new tables
ALTER TABLE public.frigate_recordings ENABLE ROW LEVEL SECURITY","ALTER TABLE public.frigate_previews ENABLE ROW LEVEL SECURITY","ALTER TABLE public.frigate_exports ENABLE ROW LEVEL SECURITY","ALTER TABLE public.frigate_reviews ENABLE ROW LEVEL SECURITY","-- Create policies for authenticated users to access all data
CREATE POLICY \"Users can view all frigate_recordings\" ON public.frigate_recordings FOR SELECT USING (auth.role() = 'authenticated')","CREATE POLICY \"Users can insert frigate_recordings\" ON public.frigate_recordings FOR INSERT WITH CHECK (auth.role() = 'authenticated')","CREATE POLICY \"Users can update frigate_recordings\" ON public.frigate_recordings FOR UPDATE USING (auth.role() = 'authenticated')","CREATE POLICY \"Users can delete frigate_recordings\" ON public.frigate_recordings FOR DELETE USING (auth.role() = 'authenticated')","CREATE POLICY \"Users can view all frigate_previews\" ON public.frigate_previews FOR SELECT USING (auth.role() = 'authenticated')","CREATE POLICY \"Users can insert frigate_previews\" ON public.frigate_previews FOR INSERT WITH CHECK (auth.role() = 'authenticated')","CREATE POLICY \"Users can update frigate_previews\" ON public.frigate_previews FOR UPDATE USING (auth.role() = 'authenticated')","CREATE POLICY \"Users can delete frigate_previews\" ON public.frigate_previews FOR DELETE USING (auth.role() = 'authenticated')","CREATE POLICY \"Users can view all frigate_exports\" ON public.frigate_exports FOR SELECT USING (auth.role() = 'authenticated')","CREATE POLICY \"Users can insert frigate_exports\" ON public.frigate_exports FOR INSERT WITH CHECK (auth.role() = 'authenticated')","CREATE POLICY \"Users can update frigate_exports\" ON public.frigate_exports FOR UPDATE USING (auth.role() = 'authenticated')","CREATE POLICY \"Users can delete frigate_exports\" ON public.frigate_exports FOR DELETE USING (auth.role() = 'authenticated')","CREATE POLICY \"Users can view all frigate_reviews\" ON public.frigate_reviews FOR SELECT USING (auth.role() = 'authenticated')","CREATE POLICY \"Users can insert frigate_reviews\" ON public.frigate_reviews FOR INSERT WITH CHECK (auth.role() = 'authenticated')","CREATE POLICY \"Users can update frigate_reviews\" ON public.frigate_reviews FOR UPDATE USING (auth.role() = 'authenticated')","CREATE POLICY \"Users can delete frigate_reviews\" ON public.frigate_reviews FOR DELETE USING (auth.role() = 'authenticated')","-- Add foreign key constraints where tables don't already exist
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'frigate_recordings') THEN
        ALTER TABLE public.frigate_recordings 
        ADD CONSTRAINT fk_frigate_recordings_instance 
        FOREIGN KEY (frigate_instance_id) REFERENCES public.frigate_instances(id) ON DELETE CASCADE;
    END IF;

    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'frigate_previews') THEN
        ALTER TABLE public.frigate_previews 
        ADD CONSTRAINT fk_frigate_previews_instance 
        FOREIGN KEY (frigate_instance_id) REFERENCES public.frigate_instances(id) ON DELETE CASCADE;
    END IF;

    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'frigate_exports') THEN
        ALTER TABLE public.frigate_exports 
        ADD CONSTRAINT fk_frigate_exports_instance 
        FOREIGN KEY (frigate_instance_id) REFERENCES public.frigate_instances(id) ON DELETE CASCADE;
    END IF;

    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'frigate_reviews') THEN
        ALTER TABLE public.frigate_reviews 
        ADD CONSTRAINT fk_frigate_reviews_instance 
        FOREIGN KEY (frigate_instance_id) REFERENCES public.frigate_instances(id) ON DELETE CASCADE;
    END IF;

    -- Add foreign key to existing timeline table if column was added
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'frigate_timeline' AND column_name = 'frigate_instance_id') THEN
        ALTER TABLE public.frigate_timeline 
        ADD CONSTRAINT fk_frigate_timeline_instance 
        FOREIGN KEY (frigate_instance_id) REFERENCES public.frigate_instances(id) ON DELETE CASCADE;
    END IF;
END
$$","-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_frigate_recordings_instance_camera ON public.frigate_recordings(frigate_instance_id, camera)","CREATE INDEX IF NOT EXISTS idx_frigate_recordings_time ON public.frigate_recordings(start_time, end_time)","CREATE INDEX IF NOT EXISTS idx_frigate_previews_instance_camera ON public.frigate_previews(frigate_instance_id, camera)","CREATE INDEX IF NOT EXISTS idx_frigate_previews_time ON public.frigate_previews(start_time, end_time)","CREATE INDEX IF NOT EXISTS idx_frigate_exports_instance_camera ON public.frigate_exports(frigate_instance_id, camera)","CREATE INDEX IF NOT EXISTS idx_frigate_exports_status ON public.frigate_exports(status)","CREATE INDEX IF NOT EXISTS idx_frigate_reviews_instance_camera ON public.frigate_reviews(frigate_instance_id, camera)","CREATE INDEX IF NOT EXISTS idx_frigate_reviews_severity ON public.frigate_reviews(severity)","CREATE INDEX IF NOT EXISTS idx_frigate_reviews_reviewed ON public.frigate_reviews(has_been_reviewed)","-- Create triggers for updating timestamps
CREATE OR REPLACE FUNCTION public.update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql","DROP TRIGGER IF EXISTS update_frigate_recordings_updated_at ON public.frigate_recordings","CREATE TRIGGER update_frigate_recordings_updated_at
    BEFORE UPDATE ON public.frigate_recordings
    FOR EACH ROW
    EXECUTE FUNCTION public.update_modified_column()","DROP TRIGGER IF EXISTS update_frigate_previews_updated_at ON public.frigate_previews","CREATE TRIGGER update_frigate_previews_updated_at
    BEFORE UPDATE ON public.frigate_previews
    FOR EACH ROW
    EXECUTE FUNCTION public.update_modified_column()","DROP TRIGGER IF EXISTS update_frigate_exports_updated_at ON public.frigate_exports","CREATE TRIGGER update_frigate_exports_updated_at
    BEFORE UPDATE ON public.frigate_exports
    FOR EACH ROW
    EXECUTE FUNCTION public.update_modified_column()","DROP TRIGGER IF EXISTS update_frigate_reviews_updated_at ON public.frigate_reviews","CREATE TRIGGER update_frigate_reviews_updated_at
    BEFORE UPDATE ON public.frigate_reviews
    FOR EACH ROW
    EXECUTE FUNCTION public.update_modified_column()"}