Avgoro

// auth.lock: supabase-js coordinates token refresh across tabs with the
// Navigator LockManager. Keeping that coordination matters: with refresh-token
// rotation, two tabs refreshing at once can trip reuse detection and log the
// user out. In some contexts (certain local servers, backgrounded tabs) the
// lock request itself rejects ("Acquiring an exclusive Navigator LockManager
// lock … immediately failed"); only then do we run the function unlocked.
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
  }
}

ALTER TABLE public.producer_kyc ENABLE ROW LEVEL SECURITY;

-- Only the owning producer or an admin may read. There are deliberately NO
-- write policies: all writes go through submit_kyc (SECURITY DEFINER).
DROP POLICY IF EXISTS "Owner or admin reads producer_kyc" ON public.producer_kyc;
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())
  );

SELECT pg_temp.expect(NOT has_function_privilege('anon', 'public._log_admin_event(text,text,text)', 'EXECUTE'),
  'anon cannot forge admin audit events');
SELECT pg_temp.expect(NOT has_function_privilege('authenticated', 'public.recompute_order(uuid)', 'EXECUTE'),
  'authenticated cannot recompute order fees');
-- recompute_order must be step 39's single definition. Two migrations used to
-- define it, and whichever ran last won: step 39's platform-only copy silently
-- dropped per-farm delivery fees at payment, and step 38d's copy dropped the
-- pending guard. Both properties are asserted so neither regression can return.
SELECT pg_temp.expect(pg_get_functiondef('public.recompute_order(uuid)'::regprocedure) LIKE '%pp.delivery_fee%',
  'recompute_order charges the farm''s own delivery fee');
SELECT pg_temp.expect(pg_get_functiondef('public.recompute_order(uuid)'::regprocedure) LIKE '%IS DISTINCT FROM ''pending''%',
  'recompute_order never rewrites a non-pending order''s amounts');
SELECT pg_temp.expect(pg_get_functiondef('public.recompute_order(uuid)'::regprocedure) LIKE '%pg_temp%',
  'recompute_order keeps the hardened search_path');
SELECT pg_temp.expect(NOT has_function_privilege('anon', 'public.sweep_abandoned_orders()', 'EXECUTE'),
  'anon cannot run the order sweep');
SELECT pg_temp.expect(NOT has_function_privilege('anon', 'public.set_order_status(uuid,text)', 'EXECUTE'),
  'anon cannot call set_order_status');
SELECT pg_temp.expect(has_function_privilege('anon', 'public.get_order_public(uuid,uuid)', 'EXECUTE'),
  'anon CAN call get_order_public (guest tracking)');

// create-checkout — recompute amounts server-side, create ONE order per
// producer in the cart, and open a single Stripe PaymentIntent for the whole
// cart. Payouts use SEPARATE CHARGES & TRANSFERS: the platform is charged the
// full amount, then the webhook transfers each producer's share on success.
//
// Request body (JSON):
//   { items: [{ productId, qty }], orderType: "b2c"|"b2b",
//     customer: { name, phone, email, address, city?, postcode?, notes? },
//     invoiceRequested?, invoiceDetails?,
//     replaceClientSecret?: "pi_..._secret_..." }  ← previous attempt from the same checkout
//
// Returns: { clientSecret, orderIds, paymentIntentId }
//
// SECURITY: prices, subtotal, commission and delivery fee are recomputed from
// the database here — the client's numbers are never trusted. Orders can only
// be created here (the browser has no INSERT on orders / order_items).
// Every producer in the cart must be trade-ready and not hidden, and every
// product must be live (not archived).
//
// Deploy: supabase functions deploy create-checkout

import { buildCorsHeaders, json } from "../_shared/cors.ts";

  if (orderErr) throw new Error(`order insert: ${orderErr.message}`);
  createdOrderIds.push(order.id);

  // 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}`);
  }
}

// ---- 5. Authoritative grand total (triggers may have recomputed) ----
const { data: dbOrders, error: totErr } = await supabaseAdmin
  .from("orders").select("total").in("id", createdOrderIds);

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

  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;

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

-- 4) Triggers: recompute when items change, or when order_type changes
CREATE OR REPLACE FUNCTION public.trg_recompute_from_items()
RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  PERFORM public.recompute_order(COALESCE(NEW.order_id, OLD.order_id));
  RETURN NULL;
END;
$$;

CREATE OR REPLACE FUNCTION public.set_order_status(p_order_id uuid, p_status text)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
DECLARE
  v_current text;
  v_owner   uuid;
BEGIN
  IF p_status IS NULL OR p_status NOT IN
     ('pending', 'paid', 'confirmed', 'preparing', 'out_for_delivery', 'delivered', 'cancelled') THEN
    RAISE EXCEPTION 'Invalid status: %', p_status;
  END IF;

  SELECT status, user_id INTO v_current, v_owner
    FROM public.orders WHERE id = p_order_id
     FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'Order not found';
  END IF;

  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;

  IF EXISTS (
    SELECT 1 FROM public.order_items oi
      JOIN public.producer_profiles pp ON pp.id = oi.producer_id
     WHERE oi.order_id = p_order_id AND pp.user_id = auth.uid()
  ) THEN
    IF p_status NOT IN ('preparing', 'out_for_delivery', 'delivered', 'cancelled') THEN
      RAISE EXCEPTION 'Producers cannot set status to %', p_status;
    END IF;
    UPDATE public.orders SET status = p_status WHERE id = p_order_id;
    RETURN;
  END IF;

  -- Customer: only cancel their own unpaid order.
  IF p_status = 'cancelled' AND v_owner IS NOT NULL AND v_owner = auth.uid() THEN
    UPDATE public.orders SET status = 'cancelled' WHERE id = p_order_id AND status = 'pending';
    RETURN;
  END IF;

  -- Customer: confirm receipt of their own dispatched order. Only the buyer
  -- knows when a parcel actually arrived, and under producer-ships the farm
  -- only knows when it handed the box to the courier. No money depends on this
  -- (transfers happen at payment_intent.succeeded), so it governs review
  -- eligibility and closing the order, not funds. The status guard keeps it to
  -- the one legal transition; guests have no user_id and are closed by
  -- sweep_delivered_orders() instead.
  IF p_status = 'delivered' AND v_owner IS NOT NULL AND v_owner = auth.uid() THEN
    UPDATE public.orders SET status = 'delivered'
     WHERE id = p_order_id AND status = 'out_for_delivery';
    RETURN;
  END IF;

  RAISE EXCEPTION 'Not authorized to change order status';
END;
$$;