acmcc/supabase/migrations/20260519013944_b6729a31-7213-440a-b36e-f234283bc6ee.sql

CREATE OR REPLACE FUNCTION public.auto_link_violation_owner()
 RETURNS trigger
 LANGUAGE plpgsql
 SECURITY DEFINER
 SET search_path TO 'public'
AS $function$
DECLARE
  v_owner_id UUID;
  v_unit_id UUID;
  v_norm TEXT;
BEGIN
  IF NEW.address IS NOT NULL AND btrim(NEW.address) <> '' THEN
    -- Aggressive normalization: lowercase, strip all whitespace and common punctuation
    v_norm := regexp_replace(lower(NEW.address), '[[:space:].,#\-/]+', '', 'g');

    -- 1) Exact normalized match on units.address
    IF NEW.unit_id IS NULL THEN
      SELECT u.id INTO v_unit_id
      FROM public.units u
      WHERE u.association_id = NEW.association_id
        AND regexp_replace(lower(coalesce(u.address,'')), '[[:space:].,#\-/]+', '', 'g') = v_norm
      ORDER BY u.created_at
      LIMIT 1;

      -- 2) Fallback: match by unit_number or account_number
      IF v_unit_id IS NULL THEN
        SELECT u.id INTO v_unit_id
        FROM public.units u
        WHERE u.association_id = NEW.association_id
          AND (
            regexp_replace(lower(coalesce(u.unit_number,'')), '[[:space:].,#\-/]+', '', 'g') = v_norm
            OR regexp_replace(lower(coalesce(u.account_number,'')), '[[:space:].,#\-/]+', '', 'g') = v_norm
          )
        ORDER BY u.created_at
        LIMIT 1;
      END IF;

      -- 3) Fallback: substring match on address
      IF v_unit_id IS NULL AND length(v_norm) >= 4 THEN
        SELECT u.id INTO v_unit_id
        FROM public.units u
        WHERE u.association_id = NEW.association_id
          AND regexp_replace(lower(coalesce(u.address,'')), '[[:space:].,#\-/]+', '', 'g') LIKE '%' || v_norm || '%'
        ORDER BY u.created_at
        LIMIT 1;
      END IF;

      IF v_unit_id IS NOT NULL THEN NEW.unit_id := v_unit_id; END IF;
    END IF;

    -- Owner: exact normalized match on property_address
    IF NEW.owner_id IS NULL THEN
      SELECT o.id INTO v_owner_id
      FROM public.owners o
      WHERE o.association_id = NEW.association_id
        AND regexp_replace(lower(coalesce(o.property_address,'')), '[[:space:].,#\-/]+', '', 'g') = v_norm
      ORDER BY (CASE WHEN o.status = 'active' THEN 0 ELSE 1 END), o.created_at
      LIMIT 1;

      -- Fallback: substring match
      IF v_owner_id IS NULL AND length(v_norm) >= 4 THEN
        SELECT o.id INTO v_owner_id
        FROM public.owners o
        WHERE o.association_id = NEW.association_id
          AND regexp_replace(lower(coalesce(o.property_address,'')), '[[:space:].,#\-/]+', '', 'g') LIKE '%' || v_norm || '%'
        ORDER BY (CASE WHEN o.status = 'active' THEN 0 ELSE 1 END), o.created_at
        LIMIT 1;
      END IF;

      IF v_owner_id IS NOT NULL THEN NEW.owner_id := v_owner_id; END IF;
    END IF;
  END IF;

  -- If unit known but owner unknown, derive owner from unit
  IF NEW.owner_id IS NULL AND NEW.unit_id IS NOT NULL THEN
    SELECT o.id INTO v_owner_id
    FROM public.owners o
    WHERE o.association_id = NEW.association_id
      AND o.unit_id = NEW.unit_id
    ORDER BY (CASE WHEN o.status = 'active' THEN 0 ELSE 1 END), o.created_at
    LIMIT 1;
    IF v_owner_id IS NOT NULL THEN NEW.owner_id := v_owner_id; END IF;
  END IF;

  -- If owner known but unit unknown, derive unit from owner
  IF NEW.unit_id IS NULL AND NEW.owner_id IS NOT NULL THEN
    SELECT o.unit_id INTO v_unit_id
    FROM public.owners o
    WHERE o.id = NEW.owner_id;
    IF v_unit_id IS NOT NULL THEN NEW.unit_id := v_unit_id; END IF;
  END IF;

  -- Backfill address from owner/unit if address is empty
  IF (NEW.address IS NULL OR btrim(NEW.address) = '') THEN
    IF NEW.owner_id IS NOT NULL THEN
      SELECT o.property_address INTO NEW.address FROM public.owners o WHERE o.id = NEW.owner_id;
    END IF;
    IF (NEW.address IS NULL OR btrim(NEW.address) = '') AND NEW.unit_id IS NOT NULL THEN
      SELECT u.address INTO NEW.address FROM public.units u WHERE u.id = NEW.unit_id;
    END IF;
  END IF;

  -- Auto-fill violation_type / title
  IF NEW.violation_type IS NULL OR btrim(NEW.violation_type) = '' THEN
    NEW.violation_type := NULLIF(btrim(COALESCE(NEW.category, NEW.title)), '');
  END IF;
  IF NEW.title IS NULL OR btrim(NEW.title) = '' THEN
    NEW.title := COALESCE(NEW.violation_type, 'Violation');
  END IF;

  RETURN NEW;
END;
$function$;

DROP TRIGGER IF EXISTS auto_link_violation_owner_trigger ON public.violations;
CREATE TRIGGER auto_link_violation_owner_trigger
  BEFORE INSERT OR UPDATE ON public.violations
  FOR EACH ROW
  EXECUTE FUNCTION public.auto_link_violation_owner();

-- Backfill existing violations: re-run the trigger logic via no-op update
UPDATE public.violations
SET updated_at = updated_at
WHERE (owner_id IS NULL OR unit_id IS NULL)
  AND address IS NOT NULL
  AND btrim(address) <> '';