-- GNEFSHNG - Database Functions

-- Generate sequential order number
CREATE OR REPLACE FUNCTION generate_order_number()
RETURNS TEXT AS $$
DECLARE
  next_val BIGINT;
BEGIN
  SELECT nextval('order_number_seq') INTO next_val;
  RETURN 'GNE-' || LPAD(next_val::TEXT, 6, '0');
END;
$$ LANGUAGE plpgsql;

-- Atomic stock decrement
-- p_items: JSON array of { "variant_id": "uuid", "quantity": int }
-- Returns false if insufficient stock for any item
CREATE OR REPLACE FUNCTION decrement_stock(p_items JSONB)
RETURNS BOOLEAN AS $$
DECLARE
  item JSONB;
  v_current_stock INTEGER;
  v_variant_id UUID;
  v_quantity INTEGER;
BEGIN
  FOR item IN SELECT * FROM jsonb_array_elements(p_items)
  LOOP
    v_variant_id := (item->>'variant_id')::UUID;
    v_quantity := (item->>'quantity')::INTEGER;

    -- Lock the row to prevent race conditions
    SELECT stock_qty INTO v_current_stock
    FROM product_variants
    WHERE id = v_variant_id
    FOR UPDATE;

    IF v_current_stock IS NULL THEN
      RAISE EXCEPTION 'Variant % not found', v_variant_id;
    END IF;

    IF v_current_stock < v_quantity THEN
      RETURN false;
    END IF;

    UPDATE product_variants
    SET stock_qty = stock_qty - v_quantity
    WHERE id = v_variant_id;
  END LOOP;

  RETURN true;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Restore stock (for cancellations)
CREATE OR REPLACE FUNCTION restore_stock(p_order_id UUID)
RETURNS VOID AS $$
BEGIN
  UPDATE product_variants pv
  SET stock_qty = pv.stock_qty + oi.qty
  FROM order_items oi
  WHERE oi.order_id = p_order_id
  AND oi.variant_id = pv.id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
