acmcc/supabase/migrations/20260428024019_cdd3572c-2b1a-492f-b4b6-a6556b9f7655.sql

-- Function: recompute bill status from its approvals
CREATE OR REPLACE FUNCTION public.recompute_bill_status_from_approvals()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path TO 'public'
AS $$
DECLARE
  v_bill_id uuid;
  v_total int;
  v_approved int;
  v_denied int;
  v_current_status text;
  v_new_status text;
BEGIN
  v_bill_id := COALESCE(NEW.bill_id, OLD.bill_id);
  IF v_bill_id IS NULL THEN
    RETURN COALESCE(NEW, OLD);
  END IF;

  SELECT COUNT(*),
         COUNT(*) FILTER (WHERE status = 'approved'),
         COUNT(*) FILTER (WHERE status = 'denied')
    INTO v_total, v_approved, v_denied
  FROM public.bill_approvals
  WHERE bill_id = v_bill_id;

  IF v_total = 0 THEN
    RETURN COALESCE(NEW, OLD);
  END IF;

  SELECT status INTO v_current_status FROM public.bills WHERE id = v_bill_id;

  IF v_denied > 0 THEN
    v_new_status := 'denied';
  ELSIF v_approved = v_total THEN
    v_new_status := 'approved';
  ELSE
    v_new_status := NULL;
  END IF;

  IF v_new_status IS NOT NULL
     AND v_current_status IS DISTINCT FROM v_new_status
     AND COALESCE(v_current_status, '') NOT IN ('paid', 'cancelled') THEN
    UPDATE public.bills
    SET status = v_new_status,
        updated_at = now(),
        approved_date = CASE WHEN v_new_status = 'approved'
                             THEN COALESCE(approved_date, CURRENT_DATE)
                             ELSE approved_date END
    WHERE id = v_bill_id;
  END IF;

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

DROP TRIGGER IF EXISTS trg_bill_approvals_recompute_status ON public.bill_approvals;
CREATE TRIGGER trg_bill_approvals_recompute_status
AFTER INSERT OR UPDATE OF status OR DELETE ON public.bill_approvals
FOR EACH ROW EXECUTE FUNCTION public.recompute_bill_status_from_approvals();

-- Backfill existing bills whose approval state is already complete
UPDATE public.bills b
SET status = 'approved',
    approved_date = COALESCE(b.approved_date, CURRENT_DATE),
    updated_at = now()
WHERE b.status NOT IN ('approved', 'denied', 'paid', 'cancelled')
  AND EXISTS (SELECT 1 FROM public.bill_approvals ba WHERE ba.bill_id = b.id)
  AND NOT EXISTS (
    SELECT 1 FROM public.bill_approvals ba
    WHERE ba.bill_id = b.id AND ba.status <> 'approved'
  );

UPDATE public.bills b
SET status = 'denied',
    updated_at = now()
WHERE b.status NOT IN ('denied', 'paid', 'cancelled')
  AND EXISTS (
    SELECT 1 FROM public.bill_approvals ba
    WHERE ba.bill_id = b.id AND ba.status = 'denied'
  );