Avgoro

async function avgAuthLock(name, acquireTimeout, fn) {
  if (!(navigator.locks && navigator.locks.request)) return await fn();
  let started = false;
  try {
    return await navigator.locks.request(name, { mode: 'exclusive' }, async () => {
      started = true;
      return await fn();
    });
  } catch (e) {
    if (started) throw e;       // fn itself failed: don't run it twice
    return await fn();          // the lock could not be acquired
  }
}

CREATE POLICY "Owner or admin reads producer_kyc"
  ON public.producer_kyc FOR SELECT TO authenticated
  USING (
    public.is_admin()
    OR producer_id IN (SELECT id FROM public.producer_profiles WHERE user_id = auth.uid())
  );

const groups = new Map<string, Grp>();
for (const [pid, qty] of requested) {
  const p = byId.get(pid)!;
  const g = groups.get(p.producer_id) ?? { lines: [], subtotalCents: 0, deliveryCents: feeFor(p.producer_profiles) };
  g.lines.push({
    product_id: p.id, producer_id: p.producer_id, farm_name: p.producer_profiles.farm_name,
    type: p.type, unit: p.unit, price: Number(p.price), qty,
  });
  g.subtotalCents += cents(Number(p.price)) * qty;
  groups.set(p.producer_id, g);
}

// Sorted by product_id so concurrent checkouts take stock locks in the
// same order (no deadlocks). The enforce_order_item_price trigger
// re-derives price, producer, farm name, type and unit from products.
const sortedLines = [...g.lines].sort((a, b) => (a.product_id < b.product_id ? -1 : 1));
const { error: itemsErr } = await supabaseAdmin
  .from("order_items").insert(sortedLines.map((l) => ({ ...l, order_id: order.id })));
if (itemsErr) {
  if (/insufficient_stock/.test(itemsErr.message)) {
    throw Object.assign(new Error("insufficient_stock"), { userFacing: true });
  }
  throw new Error(`items insert: ${itemsErr.message}`);
}

CREATE OR REPLACE FUNCTION public.recompute_order(p_order_id uuid)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
DECLARE
  s public.platform_settings;
  o public.orders;
  sub numeric;
  rate numeric;
  fee numeric;
BEGIN
  SELECT * INTO o FROM public.orders WHERE id = p_order_id;
  IF o.id IS NULL OR o.status IS DISTINCT FROM 'pending' THEN
    RETURN;
  END IF;
  SELECT * INTO s FROM public.platform_settings WHERE id = 1;

  SELECT COALESCE(SUM(line_price), 0) INTO sub FROM public.order_items WHERE order_id = p_order_id;
  rate := CASE WHEN o.order_type = 'b2b' THEN COALESCE(s.commission_b2b, 0) ELSE COALESCE(s.commission_b2c, 0) END;

  -- Per-farm flat delivery fee (producer-ships: the farm pays its own courier).
  -- create-checkout makes one order per producer, so every line in this order
  -- belongs to the same farm and the first match is it. NULL = platform default.
  SELECT pp.delivery_fee INTO fee
    FROM public.order_items oi
    JOIN public.producer_profiles pp ON pp.id = oi.producer_id
   WHERE oi.order_id = p_order_id
   LIMIT 1;
  fee := COALESCE(fee, s.delivery_fee, 0);

  UPDATE public.orders SET
    subtotal     = sub,
    commission   = round(sub * rate, 2),
    delivery_fee = fee,
    total        = sub + fee
  WHERE id = p_order_id;
END;
$$;

-- An odd fee no platform setting would hold, so a match can only come from the farm.
UPDATE public.producer_profiles SET delivery_fee = 7.13 WHERE id = v_prod.producer_id;
INSERT INTO public.order_items (order_id, product_id, qty) VALUES (v_order, v_prod.id, 1);

SELECT * INTO o FROM public.orders WHERE id = v_order;
PERFORM pg_temp.expect(o.delivery_fee = 7.13 AND o.total = o.subtotal + 7.13,
  'a pending order charges the farm''s own delivery fee');

UPDATE public.orders SET status = 'paid' WHERE id = v_order;
UPDATE public.producer_profiles SET delivery_fee = 2.00 WHERE id = v_prod.producer_id;
PERFORM public.recompute_order(v_order);
SELECT * INTO o FROM public.orders WHERE id = v_order;
PERFORM pg_temp.expect(o.delivery_fee = 7.13, 'recompute_order leaves a paid order''s amounts alone');

IF v_current = p_status THEN
  RETURN;  -- idempotent no-op
END IF;

IF p_status = 'cancelled' AND v_current <> 'pending' THEN
  RAISE EXCEPTION 'paid_order_needs_refund'
    USING ERRCODE = 'check_violation',
          HINT = 'A paid order is cancelled by refunding it (create-refund).';
END IF;

-- Admins may correct mistakes between fulfilment steps, but never mark an
-- order paid (only the payment webhook does), reopen a cancelled order
-- (its stock was already released) or send it back to pending.
IF public.is_admin() THEN
  IF v_current = 'cancelled' OR p_status IN ('pending', 'paid') THEN
    RAISE EXCEPTION 'Illegal status change: % -> %', v_current, p_status;
  END IF;
  UPDATE public.orders SET status = p_status WHERE id = p_order_id;
  RETURN;
END IF;

IF NOT public._is_legal_transition(v_current, p_status) THEN
  RAISE EXCEPTION 'Illegal status transition: % -> %', v_current, p_status;
END IF;