{"-- Edited to remove CREATE POLICY (not valid syntax)
-- 1) Table to store AI analysis prompts/responses per camera
CREATE TABLE IF NOT EXISTS 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()
)","-- 2) Enable RLS
ALTER TABLE public.ai_analysis_logs ENABLE ROW LEVEL SECURITY","-- 3) Policies mirroring events visibility rules
-- Users can view logs for cameras in their organization
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()
  )
)","-- Users can insert logs for cameras in their organization
CREATE POLICY \"Users can insert AI analysis logs in their org\"
ON public.ai_analysis_logs
FOR INSERT
WITH CHECK (
  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()
  )
)","-- Optional: allow updates by org members (not strictly needed). We skip UPDATE/DELETE to keep immutable logs."}