acmcc/supabase/migrations/20260407171531_98b330c1-bc34-4f8a-a7ce-ce28eefdde5a.sql

-- Auto-create owner updates when collections are created or updated
CREATE OR REPLACE FUNCTION public.auto_owner_update_from_collection()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO 'public'
AS $function$
DECLARE
  v_unit_id UUID;
  v_title TEXT;
  v_content TEXT;
BEGIN
  v_unit_id := NEW.unit_id;

  IF TG_OP = 'INSERT' THEN
    v_title := 'Collection Record Created';
    v_content := 'A new collection record has been created. Amount owed: $' || COALESCE(NEW.amount_owed::text, '0') || '. Status: ' || COALESCE(NEW.status, 'open') || '.';
  ELSE
    v_title := 'Collection Record Updated';
    v_content := 'Collection record updated. Amount owed: $' || COALESCE(NEW.amount_owed::text, '0') || '. Status: ' || COALESCE(NEW.status, 'open') || '.';
    IF OLD.status IS DISTINCT FROM NEW.status THEN
      v_content := v_content || ' Status changed from "' || COALESCE(OLD.status, 'unknown') || '" to "' || COALESCE(NEW.status, 'unknown') || '".';
    END IF;
  END IF;

  IF COALESCE(NEW.notes, '') <> '' THEN
    v_content := v_content || ' Notes: ' || LEFT(NEW.notes, 200);
  END IF;

  INSERT INTO public.owner_updates (association_id, unit_id, title, content, tags, collection_ids)
  VALUES (
    NEW.association_id,
    v_unit_id,
    v_title,
    v_content,
    '[{"name":"Collections","color":"#ef4444"}]'::jsonb,
    jsonb_build_array(NEW.id::text)
  );

  RETURN NEW;
END;
$function$;

CREATE TRIGGER trg_owner_update_from_collection_insert
AFTER INSERT ON public.collections
FOR EACH ROW EXECUTE FUNCTION public.auto_owner_update_from_collection();

CREATE TRIGGER trg_owner_update_from_collection_update
AFTER UPDATE ON public.collections
FOR EACH ROW EXECUTE FUNCTION public.auto_owner_update_from_collection();

-- Auto-create owner updates when violations are created or updated
CREATE OR REPLACE FUNCTION public.auto_owner_update_from_violation()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO 'public'
AS $function$
DECLARE
  v_title TEXT;
  v_content TEXT;
BEGIN
  IF TG_OP = 'INSERT' THEN
    v_title := 'Violation Issued: ' || COALESCE(NEW.title, NEW.violation_type, 'Untitled');
    v_content := 'A new violation has been recorded. Category: ' || COALESCE(NEW.category, 'N/A') || '. Status: ' || COALESCE(NEW.status, 'open') || '.';
  ELSE
    v_title := 'Violation Updated: ' || COALESCE(NEW.title, NEW.violation_type, 'Untitled');
    v_content := 'Violation record updated. Category: ' || COALESCE(NEW.category, 'N/A') || '. Status: ' || COALESCE(NEW.status, 'open') || '.';
    IF OLD.status IS DISTINCT FROM NEW.status THEN
      v_content := v_content || ' Status changed from "' || COALESCE(OLD.status, 'unknown') || '" to "' || COALESCE(NEW.status, 'unknown') || '".';
    END IF;
    IF OLD.stage IS DISTINCT FROM NEW.stage THEN
      v_content := v_content || ' Stage changed from "' || COALESCE(OLD.stage, 'unknown') || '" to "' || COALESCE(NEW.stage, 'unknown') || '".';
    END IF;
  END IF;

  INSERT INTO public.owner_updates (association_id, unit_id, title, content, tags, violation_ids)
  VALUES (
    NEW.association_id,
    NEW.unit_id,
    v_title,
    v_content,
    '[{"name":"Violations","color":"#f59e0b"}]'::jsonb,
    jsonb_build_array(NEW.id::text)
  );

  RETURN NEW;
END;
$function$;

CREATE TRIGGER trg_owner_update_from_violation_insert
AFTER INSERT ON public.violations
FOR EACH ROW EXECUTE FUNCTION public.auto_owner_update_from_violation();

CREATE TRIGGER trg_owner_update_from_violation_update
AFTER UPDATE ON public.violations
FOR EACH ROW EXECUTE FUNCTION public.auto_owner_update_from_violation();

-- Auto-create owner updates when legal matters are created or updated
CREATE OR REPLACE FUNCTION public.auto_owner_update_from_legal_matter()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO 'public'
AS $function$
DECLARE
  v_title TEXT;
  v_content TEXT;
BEGIN
  IF TG_OP = 'INSERT' THEN
    v_title := 'Legal Matter Opened: ' || COALESCE(NEW.title, 'Untitled');
    v_content := 'A new legal matter has been created. Category: ' || COALESCE(NEW.category, 'N/A') || '. Stage: ' || COALESCE(NEW.current_stage, 'N/A') || '. Status: ' || COALESCE(NEW.status, 'open') || '.';
  ELSE
    v_title := 'Legal Matter Updated: ' || COALESCE(NEW.title, 'Untitled');
    v_content := 'Legal matter updated. Category: ' || COALESCE(NEW.category, 'N/A') || '. Stage: ' || COALESCE(NEW.current_stage, 'N/A') || '. Status: ' || COALESCE(NEW.status, 'open') || '.';
    IF OLD.status IS DISTINCT FROM NEW.status THEN
      v_content := v_content || ' Status changed from "' || COALESCE(OLD.status, 'unknown') || '" to "' || COALESCE(NEW.status, 'unknown') || '".';
    END IF;
    IF OLD.current_stage IS DISTINCT FROM NEW.current_stage THEN
      v_content := v_content || ' Stage changed from "' || COALESCE(OLD.current_stage, 'unknown') || '" to "' || COALESCE(NEW.current_stage, 'unknown') || '".';
    END IF;
  END IF;

  INSERT INTO public.owner_updates (association_id, unit_id, title, content, tags)
  VALUES (
    NEW.association_id,
    NEW.unit_id,
    v_title,
    v_content,
    '[{"name":"Legal","color":"#8b5cf6"}]'::jsonb
  );

  RETURN NEW;
END;
$function$;

CREATE TRIGGER trg_owner_update_from_legal_matter_insert
AFTER INSERT ON public.legal_matters
FOR EACH ROW EXECUTE FUNCTION public.auto_owner_update_from_legal_matter();

CREATE TRIGGER trg_owner_update_from_legal_matter_update
AFTER UPDATE ON public.legal_matters
FOR EACH ROW EXECUTE FUNCTION public.auto_owner_update_from_legal_matter();