acmcc/supabase/migrations/20260320004526_1e391bc0-8e60-45cd-81bc-572217323fb8.sql

-- Drop overly restrictive policies that only match by member_name
DROP POLICY IF EXISTS "Assigned approvers can view bills" ON public.bills;
DROP POLICY IF EXISTS "Assigned approvers can view bill_approvals" ON public.bill_approvals;
DROP POLICY IF EXISTS "Assigned approvers can update bill_approvals" ON public.bill_approvals;

-- Board members can view ALL bills for their association
CREATE POLICY "Board members can view association bills"
ON public.bills
FOR SELECT
TO authenticated
USING (
  association_id IN (
    SELECT bm.association_id
    FROM public.board_members bm
    WHERE bm.user_id = auth.uid()
  )
);

-- Board members can view ALL approvals for bills in their association
CREATE POLICY "Board members can view association bill_approvals"
ON public.bill_approvals
FOR SELECT
TO authenticated
USING (
  association_id IN (
    SELECT bm.association_id
    FROM public.board_members bm
    WHERE bm.user_id = auth.uid()
  )
);

-- Board members can only update their OWN approval row (matched by member_name)
CREATE POLICY "Board members can update own bill_approvals"
ON public.bill_approvals
FOR UPDATE
TO authenticated
USING (
  EXISTS (
    SELECT 1
    FROM public.board_members bm
    WHERE bm.association_id = bill_approvals.association_id
      AND bm.member_name = bill_approvals.vendor_name
      AND bm.user_id = auth.uid()
  )
)
WITH CHECK (
  EXISTS (
    SELECT 1
    FROM public.board_members bm
    WHERE bm.association_id = bill_approvals.association_id
      AND bm.member_name = bill_approvals.vendor_name
      AND bm.user_id = auth.uid()
  )
);

-- Board members can insert comments on bills in their association
CREATE POLICY "Board members can insert bill_comments"
ON public.bill_comments
FOR INSERT
TO authenticated
WITH CHECK (
  bill_id IN (
    SELECT b.id FROM public.bills b
    WHERE b.association_id IN (
      SELECT bm.association_id
      FROM public.board_members bm
      WHERE bm.user_id = auth.uid()
    )
  )
);