acmcc/supabase/migrations/20260322194748_fa154382-95d9-4488-8a90-c20ca96a0264.sql

CREATE OR REPLACE FUNCTION public.create_form_inbox_entry_from_violation_response()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
DECLARE
  v_assoc_id UUID;
  v_title TEXT;
BEGIN
  SELECT
    v.association_id,
    'Violation Response - ' || COALESCE(v.category, v.violation_type, v.title, 'Unknown')
  INTO v_assoc_id, v_title
  FROM public.violations v
  WHERE v.id = NEW.violation_id;

  INSERT INTO public.form_inbox (
    source_type,
    source_id,
    association_id,
    title,
    submitter_name,
    submitter_email,
    summary
  )
  VALUES (
    'violation_response',
    NEW.id,
    v_assoc_id,
    v_title,
    NEW.respondent_name,
    NEW.respondent_email,
    LEFT(COALESCE(NEW.response_text, ''), 200)
  );

  RETURN NEW;
END;
$function$;