{"-- Create table for AI analysis logs (idempotent)
DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema='public' AND table_name='ai_analysis_logs') THEN
    CREATE TABLE public.ai_analysis_logs (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      camera_id uuid NOT NULL REFERENCES public.cameras(id) ON DELETE CASCADE,
      alert_template_id uuid REFERENCES public.alert_templates(id) ON DELETE SET NULL,
      prompt text NOT NULL,
      response_text text,
      model text,
      image_url text,
      metadata jsonb DEFAULT '{}'::jsonb,
      created_at timestamptz NOT NULL DEFAULT now()
    );

    ALTER TABLE public.ai_analysis_logs ENABLE ROW LEVEL SECURITY;
  END IF;
END $$","-- Drop existing policies
DO $$
DECLARE r record;
BEGIN
  FOR r IN SELECT policyname FROM pg_policies WHERE tablename='ai_analysis_logs' AND schemaname='public' LOOP
    EXECUTE format('DROP POLICY %I ON public.ai_analysis_logs', r.policyname);
  END LOOP;
END $$","-- Policies
CREATE POLICY \"Users can view AI analysis logs in their org\" ON public.ai_analysis_logs
FOR SELECT USING (
  EXISTS (
    SELECT 1
    FROM public.cameras c
    JOIN public.branches b ON c.branch_id = b.id
    JOIN public.user_roles ur ON ur.organization_id = b.organization_id
    WHERE c.id = ai_analysis_logs.camera_id AND ur.user_id = auth.uid()
  )
)","CREATE POLICY \"Super admin can manage AI analysis logs\" ON public.ai_analysis_logs
FOR ALL USING (public.is_current_user_super_admin()) WITH CHECK (public.is_current_user_super_admin())"}