acmcc/supabase/migrations/20260423164319_c751a173-e84c-4527-8367-98f6f1f4a20d.sql

CREATE OR REPLACE FUNCTION public.is_collection_legal_matter(
  p_case_type text,
  p_category text,
  p_collection_id uuid
)
RETURNS boolean
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT p_collection_id IS NOT NULL
    OR lower(coalesce(p_case_type, '')) = 'collection'
    OR lower(coalesce(p_category, '')) = 'collections';
$$;

CREATE OR REPLACE FUNCTION public.calculate_legal_matter_ledger_amount(p_legal_matter_id uuid)
RETURNS numeric
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  WITH matter AS (
    SELECT lm.id, lm.unit_id AS matter_unit_id, lm.collection_id, c.owner_id AS collection_owner_id, c.unit_id AS collection_unit_id
    FROM public.legal_matters lm
    LEFT JOIN public.collections c ON c.id = lm.collection_id
    WHERE lm.id = p_legal_matter_id
      AND public.is_collection_legal_matter(lm.case_type, lm.category, lm.collection_id)
  ), target_owners AS (
    SELECT DISTINCT o.id
    FROM matter m
    JOIN public.owners o ON o.status <> 'archived'
      AND (
        (m.collection_owner_id IS NOT NULL AND o.id = m.collection_owner_id)
        OR (coalesce(m.collection_unit_id, m.matter_unit_id) IS NOT NULL AND o.unit_id = coalesce(m.collection_unit_id, m.matter_unit_id))
      )
  )
  SELECT COALESCE(ROUND(SUM(COALESCE(ole.debit, 0) - COALESCE(ole.credit, 0))::numeric, 2), 0)
  FROM public.owner_ledger_entries ole
  JOIN target_owners target ON target.id = ole.owner_id;
$$;

CREATE OR REPLACE FUNCTION public.sync_collection_legal_matter_amounts_for_owner_unit(
  p_owner_id uuid DEFAULT NULL,
  p_unit_id uuid DEFAULT NULL
)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  UPDATE public.legal_matters lm
  SET amount_due = public.calculate_legal_matter_ledger_amount(lm.id),
      updated_at = now()
  WHERE public.is_collection_legal_matter(lm.case_type, lm.category, lm.collection_id)
    AND (
      (p_unit_id IS NOT NULL AND lm.unit_id = p_unit_id)
      OR EXISTS (
        SELECT 1
        FROM public.collections c
        WHERE c.id = lm.collection_id
          AND (
            (p_owner_id IS NOT NULL AND c.owner_id = p_owner_id)
            OR (p_unit_id IS NOT NULL AND c.unit_id = p_unit_id)
          )
      )
      OR EXISTS (
        SELECT 1
        FROM public.owners o
        WHERE p_owner_id IS NOT NULL
          AND o.id = p_owner_id
          AND o.unit_id IS NOT NULL
          AND lm.unit_id = o.unit_id
      )
    );
END;
$$;

CREATE OR REPLACE FUNCTION public.sync_collection_legal_matter_amounts_from_ledger()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  IF TG_OP IN ('INSERT', 'UPDATE') THEN
    PERFORM public.sync_collection_legal_matter_amounts_for_owner_unit(NEW.owner_id, NEW.unit_id);
  END IF;

  IF TG_OP IN ('UPDATE', 'DELETE') THEN
    PERFORM public.sync_collection_legal_matter_amounts_for_owner_unit(OLD.owner_id, OLD.unit_id);
  END IF;

  RETURN COALESCE(NEW, OLD);
END;
$$;

DROP TRIGGER IF EXISTS trg_sync_collection_legal_matter_amounts_from_ledger ON public.owner_ledger_entries;
CREATE TRIGGER trg_sync_collection_legal_matter_amounts_from_ledger
AFTER INSERT OR UPDATE OR DELETE ON public.owner_ledger_entries
FOR EACH ROW
EXECUTE FUNCTION public.sync_collection_legal_matter_amounts_from_ledger();

CREATE OR REPLACE FUNCTION public.sync_collection_legal_matter_amount_after_link_change()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  IF public.is_collection_legal_matter(NEW.case_type, NEW.category, NEW.collection_id) THEN
    UPDATE public.legal_matters
    SET amount_due = public.calculate_legal_matter_ledger_amount(NEW.id),
        updated_at = now()
    WHERE id = NEW.id;
  END IF;

  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_sync_collection_legal_matter_amount_after_link_change ON public.legal_matters;
CREATE TRIGGER trg_sync_collection_legal_matter_amount_after_link_change
AFTER INSERT OR UPDATE OF collection_id, unit_id, case_type, category ON public.legal_matters
FOR EACH ROW
EXECUTE FUNCTION public.sync_collection_legal_matter_amount_after_link_change();

CREATE OR REPLACE FUNCTION public.sync_collection_legal_matter_amounts_from_collection()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  IF TG_OP IN ('INSERT', 'UPDATE') THEN
    UPDATE public.legal_matters lm
    SET amount_due = public.calculate_legal_matter_ledger_amount(lm.id),
        updated_at = now()
    WHERE lm.collection_id = NEW.id
      AND public.is_collection_legal_matter(lm.case_type, lm.category, lm.collection_id);
  END IF;

  IF TG_OP IN ('UPDATE', 'DELETE') THEN
    UPDATE public.legal_matters lm
    SET amount_due = public.calculate_legal_matter_ledger_amount(lm.id),
        updated_at = now()
    WHERE lm.collection_id = OLD.id
      AND public.is_collection_legal_matter(lm.case_type, lm.category, lm.collection_id);
  END IF;

  RETURN COALESCE(NEW, OLD);
END;
$$;

DROP TRIGGER IF EXISTS trg_sync_collection_legal_matter_amounts_from_collection ON public.collections;
CREATE TRIGGER trg_sync_collection_legal_matter_amounts_from_collection
AFTER INSERT OR UPDATE OR DELETE ON public.collections
FOR EACH ROW
EXECUTE FUNCTION public.sync_collection_legal_matter_amounts_from_collection();