{"-- Allow users to mark events as reviewed only within their organization and restrict updates to the reviewed flag

-- Create a trigger function to restrict updates
CREATE OR REPLACE FUNCTION public.restrict_events_update()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  -- Only allow setting reviewed to true
  IF NEW.reviewed IS DISTINCT FROM TRUE THEN
    RAISE EXCEPTION 'Only allowed to set reviewed=true';
  END IF;
  -- Prevent re-reviewing
  IF OLD.reviewed = TRUE THEN
    RAISE EXCEPTION 'Event already reviewed';
  END IF;
  -- Prevent modifications to other columns
  IF NEW.id <> OLD.id OR
     NEW.camera_id IS DISTINCT FROM OLD.camera_id OR
     NEW.metadata IS DISTINCT FROM OLD.metadata OR
     NEW.detected_at IS DISTINCT FROM OLD.detected_at OR
     NEW.severity IS DISTINCT FROM OLD.severity OR
     NEW.snapshot_url IS DISTINCT FROM OLD.snapshot_url OR
     NEW.event_type IS DISTINCT FROM OLD.event_type THEN
     RAISE EXCEPTION 'Only the reviewed field may be updated';
  END IF;

  RETURN NEW;
END;
$$","-- Create or replace the trigger on events
DROP TRIGGER IF EXISTS trg_restrict_events_update ON public.events","CREATE TRIGGER trg_restrict_events_update
BEFORE UPDATE ON public.events
FOR EACH ROW
EXECUTE FUNCTION public.restrict_events_update()","-- RLS policy to allow users to set reviewed=true for events in their organization
DROP POLICY IF EXISTS \"Users can update event reviewed\" ON public.events","CREATE POLICY \"Users can update event reviewed\"
ON public.events
FOR UPDATE
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 = public.events.camera_id AND ur.user_id = auth.uid()
  )
)
WITH CHECK (
  reviewed = true
)"}