
CREATE OR REPLACE FUNCTION public.list_reorder_candidates()
RETURNS TABLE(
  product_id UUID, product_name TEXT, quantity_on_hand INTEGER,
  reorder_point INTEGER, reorder_quantity INTEGER,
  vendor_id UUID, vendor_name TEXT,
  unit_price_cents INTEGER, lead_time_days INTEGER, min_order_quantity INTEGER,
  estimated_cost_cents BIGINT
)
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public
AS $$
  SELECT
    p.id, p.name, ps.quantity_on_hand, ps.reorder_point,
    GREATEST(ps.reorder_quantity, COALESCE(vp.min_order_quantity, 1)) AS reorder_qty,
    vp.vendor_id, v.name,
    vp.unit_price_cents, vp.lead_time_days, vp.min_order_quantity,
    GREATEST(ps.reorder_quantity, COALESCE(vp.min_order_quantity, 1))::BIGINT
      * COALESCE(vp.unit_price_cents, 0)::BIGINT
  FROM public.product_stock ps
  JOIN public.products p ON p.id = ps.product_id
  LEFT JOIN public.vendor_products vp ON vp.product_id = p.id AND vp.is_preferred = true
  LEFT JOIN public.vendors v ON v.id = vp.vendor_id AND v.is_active = true
  WHERE ps.auto_reorder = true
    AND ps.quantity_on_hand <= ps.reorder_point
    AND ps.reorder_quantity > 0
  ORDER BY (ps.reorder_point - ps.quantity_on_hand) DESC;
$$;

CREATE OR REPLACE FUNCTION public.auto_generate_purchase_orders(p_dry_run BOOLEAN DEFAULT false)
RETURNS TABLE(
  po_id UUID, po_number TEXT, vendor_id UUID, vendor_name TEXT,
  line_count INTEGER, total_cents BIGINT, status TEXT
)
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public
AS $$
DECLARE
  v_vendor RECORD; v_line RECORD;
  v_po_id UUID; v_po_number TEXT;
  v_subtotal BIGINT; v_tax BIGINT;
  v_line_count INTEGER; v_skipped_count INTEGER;
BEGIN
  IF NOT (public.has_role(auth.uid(), 'admin'::public.app_role) OR public.has_role(auth.uid(), 'approver'::public.app_role)) THEN
    RAISE EXCEPTION 'Only admins/approvers can auto-generate purchase orders';
  END IF;

  SELECT COUNT(*) INTO v_skipped_count
  FROM public.list_reorder_candidates() c WHERE c.vendor_id IS NULL;

  IF v_skipped_count > 0 THEN
    po_id := NULL; po_number := NULL; vendor_id := NULL;
    vendor_name := v_skipped_count::TEXT || ' product(s) skipped — no preferred vendor';
    line_count := 0; total_cents := 0; status := 'skipped';
    RETURN NEXT;
  END IF;

  FOR v_vendor IN
    SELECT c.vendor_id AS v_id, MAX(c.vendor_name) AS v_name
    FROM public.list_reorder_candidates() c
    WHERE c.vendor_id IS NOT NULL
    GROUP BY c.vendor_id
  LOOP
    v_subtotal := 0; v_tax := 0; v_line_count := 0;

    IF NOT p_dry_run THEN
      INSERT INTO public.purchase_orders (vendor_id, status, order_date, notes, created_by)
      VALUES (v_vendor.v_id, 'draft', CURRENT_DATE,
              'Auto-generated by inventory reorder loop on ' || CURRENT_DATE, auth.uid())
      RETURNING id, purchase_orders.po_number INTO v_po_id, v_po_number;

      FOR v_line IN
        SELECT * FROM public.list_reorder_candidates() c WHERE c.vendor_id = v_vendor.v_id
      LOOP
        INSERT INTO public.purchase_order_lines (
          purchase_order_id, product_id, description, quantity, unit_price_cents, tax_rate, total_cents)
        VALUES (
          v_po_id, v_line.product_id, v_line.product_name,
          v_line.reorder_quantity, v_line.unit_price_cents, 25.00,
          v_line.reorder_quantity * v_line.unit_price_cents);
        v_subtotal := v_subtotal + v_line.estimated_cost_cents;
        v_line_count := v_line_count + 1;
      END LOOP;

      v_tax := ROUND(v_subtotal * 0.25);
      UPDATE public.purchase_orders
      SET subtotal_cents = v_subtotal, tax_cents = v_tax,
          total_cents = v_subtotal + v_tax, updated_at = now()
      WHERE id = v_po_id;
    ELSE
      v_po_id := NULL;
      v_po_number := '(dry-run)';
      SELECT COUNT(*), COALESCE(SUM(c.estimated_cost_cents), 0)
        INTO v_line_count, v_subtotal
      FROM public.list_reorder_candidates() c WHERE c.vendor_id = v_vendor.v_id;
      v_tax := ROUND(v_subtotal * 0.25);
    END IF;

    po_id := v_po_id;
    po_number := v_po_number;
    vendor_id := v_vendor.v_id;
    vendor_name := v_vendor.v_name;
    line_count := v_line_count;
    total_cents := v_subtotal + v_tax;
    status := CASE WHEN p_dry_run THEN 'preview' ELSE 'created' END;
    RETURN NEXT;
  END LOOP;
END;
$$;

INSERT INTO public.agent_skills (name, description, category, handler, scope, mcp_exposed, tool_definition, instructions)
VALUES
  ('list_reorder_candidates',
   'List products at or below their reorder point with preferred vendor info. Use when: reviewing what needs reordering, "vad behöver beställas?". NOT for: actually placing orders (use auto_generate_purchase_orders).',
   'commerce', 'rpc:list_reorder_candidates', 'external', true,
   jsonb_build_object('type','function','function', jsonb_build_object(
     'name','list_reorder_candidates',
     'description','List products needing reorder with vendor pricing',
     'parameters', jsonb_build_object('type','object','properties', jsonb_build_object()))),
   'Read-only. Returns product, current stock, reorder point, preferred vendor, unit price, estimated cost.'),

  ('auto_generate_purchase_orders',
   'Group reorder candidates by preferred vendor and auto-create one draft PO per vendor. Use when: nightly reorder run, "skapa inköpsordrar". Closes procure-to-pay loop. NOT for: single manual POs (use create_purchase_order).',
   'commerce', 'rpc:auto_generate_purchase_orders', 'external', true,
   jsonb_build_object('type','function','function', jsonb_build_object(
     'name','auto_generate_purchase_orders',
     'description','Bulk-create draft POs from inventory reorder needs',
     'parameters', jsonb_build_object('type','object',
       'properties', jsonb_build_object(
         'dry_run', jsonb_build_object('type','boolean','description','Preview without creating, default false'))))),
   'Creates one PO per vendor in draft status. Reports skipped products without preferred vendor. Quantities respect min_order_quantity. Tax 25%. Admin reviews before sending.')
ON CONFLICT (name) DO UPDATE SET
  description = EXCLUDED.description,
  tool_definition = EXCLUDED.tool_definition,
  instructions = EXCLUDED.instructions,
  handler = EXCLUDED.handler,
  scope = EXCLUDED.scope,
  category = EXCLUDED.category,
  mcp_exposed = true,
  updated_at = now();
