acmcc/supabase/migrations/20260322195515_d6e753af-3db5-49ea-8f88-326b3a9cf3fb.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;
  v_summary TEXT;
  v_address TEXT;
  v_vid_short TEXT;
BEGIN
  SELECT
    v.association_id,
    LEFT(v.id::text, 8),
    COALESCE(v.address, ''),
    COALESCE(v.category, v.violation_type, v.title, 'Unknown')
  INTO v_assoc_id, v_vid_short, v_address, v_title
  FROM public.violations v
  WHERE v.id = NEW.violation_id;

  v_summary := 'Violation ID: V-' || UPPER(v_vid_short);
  IF v_address IS NOT NULL AND v_address <> '' THEN
    v_summary := v_summary || ' | Address: ' || v_address;
  END IF;
  IF NEW.response_text IS NOT NULL THEN
    v_summary := v_summary || ' | ' || LEFT(NEW.response_text, 150);
  END IF;

  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,
    'Violation Response - ' || v_title,
    NEW.respondent_name, NEW.respondent_email, v_summary
  );

  RETURN NEW;
END;
$function$;