-- 0020: closing the holes adversarial review found in 0019's receiving path.
-- Spec (binding): docs/superpowers/specs/2026-08-03-inventory-design.md.
-- Plan: docs/superpowers/plans/2026-08-03-inventory.md Task 2.
-- Forward-only. 0018 and 0019 are committed and applied and are NOT edited;
-- every function replaced below reproduces its newest definition verbatim with
-- the new blocks marked, and every new object is new.
--
-- Five things, in the order they appear:
--
--   1. record_movement gains a TEN-argument sibling that takes the VALUE the
--      caller already knows instead of a unit cost (F2). The nine-argument
--      form every existing caller uses is untouched in behaviour.
--   2. reverse_movement() — the compensating movement a voided document needs
--      (F4). It copies the row it reverses, so it cannot lie about the cost.
--   3. items_frozen_while_drafted() — an item's kind and inventory account
--      cannot change while a DRAFT bill line already names it (F1/F1b).
--   4. issue_bill re-validates at issue time (F1's other half, F3).
--   5. void_bill can undo a receipt (F4).

-- ---------------------------------------------------------------------------
-- 1. record_movement, told the value instead of the cost         [review F2]
-- ---------------------------------------------------------------------------

-- WHY. 0019 rounded a stocked bill line's amount to FOUR decimals so that
-- round(quantity x unit_price, 4) — what record_movement computes — would equal
-- the amount by construction. That kept the tie, and produced an UNPAYABLE
-- BILL: 0.5 x $99.99 is 49.9950, the payment API accepts whole cents, and
-- $0.0050 sits open in A/P aging forever with no way to clear it. The feature's
-- headline case does it too (333 washers at $0.0042 = $1.3986).
--
-- DECISION: money is always whole cents. The stocked line's AMOUNT rounds to
-- cents like every other money value in this system, the movement takes that
-- exact amount as its value, and the UNIT COST is DERIVED from it —
-- value / quantity, at inventory_movements.unit_cost's own six decimals, which
-- is precisely the headroom that column exists to provide. The tie then holds
-- by construction (movement value IS the line amount, not a recomputation of
-- it), bills are always payable to the cent, and the per-unit cost reflects the
-- money actually owed, which is what an average cost should mean.
--
-- 333 washers at $0.0042: amount 1.3986 -> $1.40; movement value 1.4000;
-- unit_cost round(1.40 / 333, 6) = 0.004204. Note that
-- round(333 x 0.004204, 4) = 1.3999 — which is exactly why the value has to be
-- GIVEN rather than recomputed. No 6-decimal unit cost exists that reproduces
-- 1.4000 from 333 units; for any quantity above 100 the rounding interval is
-- narrower than the column's own resolution.
--
-- SHAPE: a ten-argument OVERLOAD, not a replacement. The nine-argument
-- signature keeps its defaults and keeps working for every existing caller
-- (record_adjustment, the invariant tests) — it now delegates. The new one has
-- NO default on p_value, so a nine-argument call is never ambiguous. Nothing
-- was dropped, and no committed migration was edited.

CREATE FUNCTION record_movement(
  p_company_id uuid,
  p_item_id bigint,
  p_moved_on date,
  p_qty numeric,
  p_unit_cost numeric,             -- inbound cost; NULL when p_value is given
  p_reason text,
  p_source_kind text,
  p_source_id bigint,
  p_entry_id bigint,
  -- The money this movement moved, when the caller already knows it because it
  -- is a document line's own amount. Mutually exclusive with p_unit_cost, and
  -- inbound only: the cost of stock going OUT is the running average, and
  -- letting a caller name it would be a second source of truth (0018's rule,
  -- preserved below).
  p_value numeric
) RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_item items%ROWTYPE;
  v_qty numeric(19,4);
  v_on_hand numeric(19,4);
  v_value_total numeric(19,4);
  v_unit_cost numeric(19,6);
  v_value numeric(19,4);
  v_lock date;
  v_id bigint;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  -- LOCK FIRST. Everything below reads state this lock is protecting.
  SELECT * INTO v_item FROM items
   WHERE company_id = p_company_id AND id = p_item_id
   FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'item % not found', p_item_id;
  END IF;
  IF v_item.kind <> 'inventory' THEN
    RAISE EXCEPTION
      '''%'' is not a stocked item, so it has no quantity on hand', v_item.name;
  END IF;

  -- NaN first, before any comparison can be fooled by it (0018's note).
  IF p_qty IS NULL OR p_qty = 'NaN'::numeric OR p_qty = 0 THEN
    RAISE EXCEPTION 'a stock movement has to change the quantity by something';
  END IF;
  IF p_unit_cost = 'NaN'::numeric THEN
    RAISE EXCEPTION 'a unit cost has to be a real number';
  END IF;
  IF p_value = 'NaN'::numeric THEN
    RAISE EXCEPTION 'a value has to be a real number';
  END IF;
  IF p_value IS NOT NULL AND p_unit_cost IS NOT NULL THEN
    RAISE EXCEPTION
      'a stock movement is costed either by its unit cost or by its total value, never by both';
  END IF;

  v_qty := round(p_qty, 4);
  IF v_qty = 0 THEN
    RAISE EXCEPTION 'a stock movement has to change the quantity by something';
  END IF;

  IF v_qty > 0 THEN
    IF p_value IS NOT NULL THEN
      IF p_value < 0 THEN
        RAISE EXCEPTION 'stock coming in cannot be worth less than nothing';
      END IF;
      -- The value is the document's own money, already rounded to the cent by
      -- the caller. The unit cost is derived FROM it, never the other way
      -- round, so the ledger and the movement are the same number.
      v_value := round(p_value, 4);
      v_unit_cost := round(v_value / v_qty, 6);
    ELSE
      IF p_unit_cost IS NULL THEN
        RAISE EXCEPTION 'receiving ''%'' needs a unit cost', v_item.name;
      END IF;
      IF p_unit_cost < 0 THEN
        RAISE EXCEPTION 'a unit cost cannot be negative';
      END IF;
      v_unit_cost := round(p_unit_cost, 6);
    END IF;
  ELSE
    IF p_unit_cost IS NOT NULL OR p_value IS NOT NULL THEN
      RAISE EXCEPTION
        'the cost of stock going out is set by the running average, not by the caller';
    END IF;
  END IF;

  -- Section 3.3: nothing goes into a locked period — including stock.
  v_lock := effective_locked_through(p_company_id);
  IF v_lock IS NOT NULL AND p_moved_on <= v_lock THEN
    RAISE EXCEPTION 'cannot post into locked period (locked through %)', v_lock;
  END IF;

  SELECT coalesce(sum(qty), 0)::numeric(19,4),
         coalesce(sum(value), 0)::numeric(19,4)
    INTO v_on_hand, v_value_total
    FROM inventory_movements
   WHERE company_id = p_company_id AND item_id = p_item_id;

  IF v_qty > 0 THEN
    IF p_value IS NULL THEN
      v_value := round(v_qty * v_unit_cost, 4);
    END IF;
  ELSE
    IF v_on_hand + v_qty < 0 THEN
      RAISE EXCEPTION
        'You only have % of % on hand — this would use %.',
        format_quantity(v_on_hand), v_item.name, format_quantity(-v_qty);
    END IF;
    IF v_on_hand = 0 THEN
      v_unit_cost := 0::numeric(19,6);
      v_value := 0::numeric(19,4);
    ELSIF v_on_hand + v_qty = 0 THEN
      -- Emptying the shelf takes the WHOLE remaining value with it (0018's
      -- decision 5).
      v_value := -v_value_total;
      v_unit_cost := round(v_value / v_qty, 6);
    ELSE
      v_unit_cost := round(v_value_total / v_on_hand, 6);
      v_value := round(v_qty * v_unit_cost, 4);
    END IF;
  END IF;

  INSERT INTO inventory_movements
    (company_id, item_id, moved_on, qty, unit_cost, value, reason,
     source_kind, source_id, entry_id)
  VALUES
    (p_company_id, p_item_id, p_moved_on, v_qty, v_unit_cost,
     v_value, p_reason, p_source_kind, p_source_id, p_entry_id)
  RETURNING id INTO v_id;

  RETURN v_id;
END $$;

REVOKE EXECUTE ON FUNCTION record_movement(
  uuid, bigint, date, numeric, numeric, text, text, bigint, bigint, numeric)
  FROM PUBLIC;

-- The nine-argument door every existing caller uses, now a delegation. Its
-- signature, defaults and behaviour are unchanged — p_value is simply always
-- NULL, which is the branch that holds 0018's original arithmetic verbatim.
CREATE OR REPLACE FUNCTION record_movement(
  p_company_id uuid,
  p_item_id bigint,
  p_moved_on date,
  p_qty numeric,
  p_unit_cost numeric,
  p_reason text,
  p_source_kind text DEFAULT NULL,
  p_source_id bigint DEFAULT NULL,
  p_entry_id bigint DEFAULT NULL
) RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  RETURN record_movement(p_company_id, p_item_id, p_moved_on, p_qty,
                         p_unit_cost, p_reason, p_source_kind, p_source_id,
                         p_entry_id, NULL::numeric);
END $$;

REVOKE EXECUTE ON FUNCTION record_movement(
  uuid, bigint, date, numeric, numeric, text, text, bigint, bigint) FROM PUBLIC;

-- ---------------------------------------------------------------------------
-- 2. reverse_movement — the compensating movement of a voided document  [F4]
-- ---------------------------------------------------------------------------

-- Movements are immutable, so undoing a document writes the MIRROR of what it
-- wrote (the spec's "Reversal of the causing document writes a compensating
-- movement; it never edits history").
--
-- It takes a MOVEMENT ID, not a quantity and a cost, and copies the row it is
-- reversing. That is the whole point: a caller cannot lie about the cost,
-- because the cost comes from an immutable row rather than from an argument.
-- It also has to be the ORIGINAL cost rather than the running average — the
-- reversal journal entry is the exact mirror of the original, so any other
-- number would not tie. Reversing at the original cost is also simply correct:
-- receive 10 at 5.00, receive 10 at 7.00, void the first, and what is left is
-- 10 at 7.00 exactly, not 10 at the blended 6.00.
--
-- Internal, for the same reason record_movement is: it writes half of a
-- two-sided fact.
CREATE FUNCTION reverse_movement(
  p_company_id uuid,
  p_movement_id bigint,
  p_entry_id bigint,        -- the reversal entry this rides on
  p_moved_on date           -- the reversal entry's date; they must match
) RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_move inventory_movements%ROWTYPE;
  v_item items%ROWTYPE;
  v_on_hand numeric(19,4);
  v_lock date;
  v_id bigint;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  SELECT * INTO v_move FROM inventory_movements
   WHERE company_id = p_company_id AND id = p_movement_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'stock movement % not found', p_movement_id;
  END IF;

  -- LOCK FIRST, exactly as record_movement does: the on-hand read below is
  -- what this lock protects.
  SELECT * INTO v_item FROM items
   WHERE company_id = p_company_id AND id = v_move.item_id
   FOR UPDATE;

  v_lock := effective_locked_through(p_company_id);
  IF v_lock IS NOT NULL AND p_moved_on <= v_lock THEN
    RAISE EXCEPTION 'cannot post into locked period (locked through %)', v_lock;
  END IF;

  SELECT coalesce(sum(qty), 0)::numeric(19,4) INTO v_on_hand
    FROM inventory_movements
   WHERE company_id = p_company_id AND item_id = v_move.item_id;

  -- Giving back stock that has already been sold or built into something would
  -- drive the quantity on hand negative, and average cost is meaningless below
  -- zero. Say what actually happened rather than quoting a rule.
  IF v_on_hand - v_move.qty < 0 THEN
    RAISE EXCEPTION
      'You can''t undo this receipt — % of the % of ''%'' it brought in have already been used. Correct it with an inventory adjustment instead.',
      format_quantity(v_move.qty - v_on_hand), format_quantity(v_move.qty),
      v_item.name;
  END IF;

  INSERT INTO inventory_movements
    (company_id, item_id, moved_on, qty, unit_cost, value, reason,
     source_kind, source_id, entry_id)
  VALUES
    (p_company_id, v_move.item_id, p_moved_on, -v_move.qty, v_move.unit_cost,
     -v_move.value, v_move.reason, v_move.source_kind, v_move.source_id,
     p_entry_id)
  RETURNING id INTO v_id;

  RETURN v_id;
END $$;

REVOKE EXECUTE ON FUNCTION reverse_movement(uuid, bigint, bigint, date) FROM PUBLIC;

-- ---------------------------------------------------------------------------
-- 3. an item's kind cannot change out from under a draft bill line  [F1/F1b]
-- ---------------------------------------------------------------------------

-- THE HOLE. bill_lines.category_account_id is chosen when the DRAFT is written
-- and frozen there; issue_bill decides what moves stock by looking at the
-- item's kind AT ISSUE TIME. Flip the kind in between and the bill posts money
-- into an inventory account with no movement behind it:
--
--   POST /items {kind:'inventory', inventoryAccountId:1300}  -- line gets 1300
--   POST /bills {lines:[{itemId, quantity:10, unitPrice:1.00}]}
--   PATCH /items/:id {kind:'non_inventory'}   -- allowed: no movements yet
--   POST /bills/:id/issue                     -- debits 1300 by 10.00, no stock
--
-- and if that item was the ONLY user of 1300, the account leaves
-- check_inventory_ties()'s account set along with it — the difference is not
-- merely zero, the account is not looked at. THE INVARIANT CANNOT SEE IT.
--
-- That last part is why this guard lives HERE, on the item, and not only at
-- issue time. No check performed while issuing the bill can distinguish the
-- orphaned 1300 debit from an ordinary asset purchase, because after the flip
-- nothing anywhere still says 1300 was ever an inventory account. The only
-- moment the truth is still on record is the moment of the flip. So the flip is
-- what gets refused.
--
-- (The mirror case — repointing inventory_account_id while staying
-- kind='inventory' — is already caught by 0018's deferred per-account check,
-- because a movement IS recorded and lands on the new account while the money
-- lands on the old one. It is refused here too, one transaction earlier and
-- with a better sentence.)
CREATE FUNCTION items_frozen_while_drafted() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE v_bill bigint;
BEGIN
  IF NEW.kind IS NOT DISTINCT FROM OLD.kind
     AND NEW.inventory_account_id IS NOT DISTINCT FROM OLD.inventory_account_id THEN
    RETURN NEW;
  END IF;

  SELECT b.id INTO v_bill
    FROM bill_lines l
    JOIN bills b ON b.company_id = l.company_id AND b.id = l.bill_id
   WHERE l.company_id = OLD.company_id AND l.item_id = OLD.id
     AND b.status = 'draft'
   ORDER BY b.id
   LIMIT 1;

  IF FOUND THEN
    RAISE EXCEPTION
      '''%'' is on a bill that hasn''t been entered yet, so its item type and inventory account can''t change. Enter or delete that bill first.',
      OLD.name;
  END IF;

  RETURN NEW;
END $$;

CREATE TRIGGER items_frozen_while_drafted
  BEFORE UPDATE ON items
  FOR EACH ROW EXECUTE FUNCTION items_frozen_while_drafted();

-- ---------------------------------------------------------------------------
-- 4. issue_bill: re-validate at issue time                     [F1, F2, F3]
-- ---------------------------------------------------------------------------

-- BASE TEXT: db/migrations/0019_inventory_receiving.sql's issue_bill(), which
-- is itself 0009's text plus the receipt loop. Every guard from both is
-- preserved verbatim and in order: the session-company check, the FOR UPDATE
-- lookup and 'bill % not found', draft-only, at-least-one-line, the
-- document_only branch with its own period-lock check in post_entry's exact
-- wording and NO posting, and then ensure_ap_account, the entry, one journal
-- line per category account, the single A/P credit, the receipt loop,
-- post_entry, and the bills UPDATE — in that order. issue_bill still allocates
-- from no counter (bills carry the vendor's own ref_no) and still never reads
-- bill_payments, so vendor credits and every A/P balance are untouched.
--
-- THREE ADDITIONS:
--
--   (a) [F3] The purchase order a line fulfils is re-checked AT ISSUE. 0019
--       checked it when the draft line was written, which is time-of-check
--       only: draft the line against an open PO, cancel the PO, issue, and the
--       cancelled PO would then report the stock as received.
--   (b) [F1] Every line is re-validated against the CURRENT items table: a line
--       may only post into an account that is some item's inventory account if
--       it actually moved stock into it on this entry. This closes the other
--       direction of the same race — a plain expense line whose account BECAME
--       an inventory account after the draft was written, because somebody
--       created an item pointing at it.
--   (c) [F2] The receipt takes the line's AMOUNT as the movement's value
--       instead of recomputing quantity x unit price, so the two are the same
--       number by construction and a stocked bill is payable to the cent.
CREATE OR REPLACE FUNCTION issue_bill(p_company_id uuid, p_bill_id bigint, p_user_id uuid)
RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_bill bills%ROWTYPE;
  v_line_count int;
  v_total numeric(19,4);
  v_ap_account bigint;
  v_entry_id bigint;
  v_lock date;
  v_stocked_lines int;
  v_line record;
  v_movement_id bigint;
  v_value numeric(19,4);
  v_bad record;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match bill company';
  END IF;

  SELECT * INTO v_bill FROM bills
   WHERE company_id = p_company_id AND id = p_bill_id
   FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'bill % not found', p_bill_id;
  END IF;
  IF v_bill.status <> 'draft' THEN
    RAISE EXCEPTION 'bill % is already %', p_bill_id, v_bill.status;
  END IF;

  SELECT count(*), coalesce(sum(amount), 0) INTO v_line_count, v_total
    FROM bill_lines WHERE company_id = p_company_id AND bill_id = p_bill_id;
  IF v_line_count < 1 THEN
    RAISE EXCEPTION 'bill % has no lines', p_bill_id;
  END IF;

  -- (a) F3: the purchase order may have been cancelled since the draft was
  -- written. 'draft' is unreachable (a PO never goes back) but is checked
  -- anyway, with the same sentence 0019's trigger uses.
  FOR v_bad IN
    SELECT p.status, p.po_no
      FROM bill_lines l
      JOIN purchase_order_lines pl
        ON pl.company_id = l.company_id AND pl.id = l.po_line_id
      JOIN purchase_orders p ON p.company_id = pl.company_id AND p.id = pl.po_id
     WHERE l.company_id = p_company_id AND l.bill_id = p_bill_id
       AND p.status IN ('draft', 'cancelled')
     ORDER BY p.id
     LIMIT 1
  LOOP
    IF v_bad.status = 'cancelled' THEN
      RAISE EXCEPTION 'purchase order #% was cancelled', v_bad.po_no;
    END IF;
    RAISE EXCEPTION
      'that purchase order has not been sent to the vendor yet — issue it before receiving against it';
  END LOOP;

  SELECT count(*) INTO v_stocked_lines
    FROM bill_lines l
    JOIN items i ON i.company_id = l.company_id AND i.id = l.item_id
   WHERE l.company_id = p_company_id AND l.bill_id = p_bill_id
     AND i.kind = 'inventory';

  IF v_bill.document_only THEN
    IF v_stocked_lines > 0 THEN
      RAISE EXCEPTION
        'bill % is recorded for tracking only, so it cannot bring stock in — enter it as a normal bill if it changes the quantity on hand',
        p_bill_id;
    END IF;

    v_lock := effective_locked_through(p_company_id);
    IF v_lock IS NOT NULL AND v_bill.bill_date <= v_lock THEN
      RAISE EXCEPTION 'cannot post into locked period (locked through %)', v_lock;
    END IF;

    UPDATE bills SET status = 'open', issued_on = CURRENT_DATE
     WHERE company_id = p_company_id AND id = p_bill_id;
  ELSE
    v_ap_account := ensure_ap_account(p_company_id);

    INSERT INTO journal_entries (company_id, entry_date, memo, created_by)
    VALUES (p_company_id, v_bill.bill_date,
            coalesce('Bill ' || v_bill.ref_no, 'Bill'), p_user_id)
    RETURNING id INTO v_entry_id;

    INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit)
    SELECT p_company_id, v_entry_id, category_account_id, sum(amount), 0
      FROM bill_lines
     WHERE company_id = p_company_id AND bill_id = p_bill_id
     GROUP BY category_account_id;

    INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit)
    VALUES (p_company_id, v_entry_id, v_ap_account, 0, v_total);

    -- The stock half of the same fact, in the same transaction, on the same
    -- entry, dated the same day (spec decision 1: the receipt date IS the bill
    -- date). record_movement() runs BEFORE post_entry() deliberately — its own
    -- period-lock check uses post_entry's exact wording, so a locked-period
    -- bill is refused with the same sentence whichever guard sees it first.
    FOR v_line IN
      SELECT l.id, l.item_id, l.quantity, l.unit_price, l.amount,
             i.name AS item_name
        FROM bill_lines l
        JOIN items i ON i.company_id = l.company_id AND i.id = l.item_id
       WHERE l.company_id = p_company_id AND l.bill_id = p_bill_id
         AND i.kind = 'inventory'
       ORDER BY l.id
    LOOP
      -- The line has to be internally coherent before any of it is believed.
      -- The API computes amount as the cent-rounded product; asserting the same
      -- thing here is what makes the database the guard rather than the app
      -- (CLAUDE.md §12), and it is the ONLY check that can still fire now that
      -- the movement's value is the amount itself.
      v_value := round(v_line.quantity * v_line.unit_price, 2);
      IF v_line.amount <> v_value THEN
        RAISE EXCEPTION
          'the % of ''%'' on this bill cost % each, which comes to %, but the line says % — the quantity, the cost and the amount have to agree',
          format_quantity(v_line.quantity), v_line.item_name,
          v_line.unit_price, v_value, v_line.amount;
      END IF;

      -- (c) F2: the LINE'S AMOUNT is the movement's value, passed in rather
      -- than recomputed from the unit price. Recomputing reintroduces a
      -- rounding difference the ledger has no counterpart for, and the only way
      -- to hide that difference is to push the amount off whole cents — which
      -- makes the bill unpayable. The per-unit cost is derived from the value
      -- inside record_movement, at six decimals.
      v_movement_id := record_movement(
        p_company_id, v_line.item_id, v_bill.bill_date, v_line.quantity,
        NULL::numeric, 'receipt', 'bill_line', v_line.id, v_entry_id,
        v_line.amount);
    END LOOP;

    -- (b) F1: re-validated against the items table AS IT IS NOW, not as it was
    -- when the draft was written. Any line landing on an account that is
    -- currently some item's inventory account must have moved stock into it on
    -- this very entry.
    FOR v_bad IN
      SELECT l.id, l.item_id, a.name AS account_name, i.name AS item_name
        FROM bill_lines l
        JOIN accounts a ON a.company_id = l.company_id AND a.id = l.category_account_id
        LEFT JOIN items i ON i.company_id = l.company_id AND i.id = l.item_id
       WHERE l.company_id = p_company_id AND l.bill_id = p_bill_id
         AND EXISTS (SELECT 1 FROM items i2
                      WHERE i2.company_id = l.company_id
                        AND i2.inventory_account_id = l.category_account_id)
         AND NOT EXISTS (SELECT 1 FROM inventory_movements m
                          WHERE m.company_id = l.company_id
                            AND m.entry_id = v_entry_id
                            AND m.source_kind = 'bill_line'
                            AND m.source_id = l.id)
       ORDER BY l.id
       LIMIT 1
    LOOP
      IF v_bad.item_id IS NOT NULL THEN
        RAISE EXCEPTION
          '''%'' is not a stocked item any more, so this bill cannot put its cost into ''%'' — delete that line and enter it again',
          v_bad.item_name, v_bad.account_name;
      END IF;
      RAISE EXCEPTION
        '''%'' is an inventory account — pick the item you are buying instead, so the quantity and the value move together',
        v_bad.account_name;
    END LOOP;

    PERFORM post_entry(p_company_id, v_entry_id);

    UPDATE bills SET status = 'open', issued_on = CURRENT_DATE, posted_entry_id = v_entry_id
     WHERE company_id = p_company_id AND id = p_bill_id;
  END IF;
END $$;

-- ---------------------------------------------------------------------------
-- 5. void_bill: undoing a receipt                                      [F4]
-- ---------------------------------------------------------------------------

-- BASE TEXT: db/migrations/0009_ap.sql's void_bill(). Grepped across 0010-0019:
-- never replaced, so 0009 is the newest definition. Preserved verbatim: the
-- session-company guard, 'bill % not found', the open-only guard, the
-- payments-applied refusal, the document_only branch (its own period-lock check
-- in post_entry's wording, its reversal-date check, and the status/voided_on
-- UPDATE with no posting), and the ordinary branch's reverse_entry() call plus
-- the void UPDATE.
--
-- ADDED: the branch for a bill that brought stock in. 0018 makes reverse_entry()
-- refuse a movement-bearing entry outright, which left a received bill
-- permanently un-voidable — a received purchase order could never reopen and an
-- over-receipt could never be corrected. The fix is the pattern
-- reverse_intercompany() already uses: write the compensating movements and
-- then call reverse_entry_INTERNAL, which is the same door.
--
-- Order matters. reverse_entry_internal() creates AND POSTS the reversal entry
-- first, because the compensating movements need its id and must share its
-- date; 0018's deferred check then sees a posted entry, matching dates, one
-- transaction, and per-account amounts that mirror the original exactly.
CREATE OR REPLACE FUNCTION void_bill(
  p_company_id uuid, p_bill_id bigint, p_void_date date, p_user_id uuid
) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_bill bills%ROWTYPE;
  v_app_count int;
  v_rev_id bigint;
  v_lock date;
  v_move record;
  v_has_movements boolean;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match bill company';
  END IF;

  SELECT * INTO v_bill FROM bills
   WHERE company_id = p_company_id AND id = p_bill_id
   FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'bill % not found', p_bill_id;
  END IF;
  IF v_bill.status <> 'open' THEN
    RAISE EXCEPTION 'bill % is % — only open bills can be voided',
      p_bill_id, v_bill.status;
  END IF;

  SELECT count(*) INTO v_app_count FROM bill_payment_applications
   WHERE company_id = p_company_id AND bill_id = p_bill_id;
  IF v_app_count > 0 THEN
    RAISE EXCEPTION
      'bill % has payments applied — void the payments applied to it first',
      p_bill_id;
  END IF;

  IF v_bill.document_only THEN
    v_lock := effective_locked_through(p_company_id);
    IF v_lock IS NOT NULL AND p_void_date <= v_lock THEN
      RAISE EXCEPTION 'cannot post into locked period (locked through %)', v_lock;
    END IF;
    IF p_void_date < v_bill.bill_date THEN
      RAISE EXCEPTION 'reversal date cannot precede the entry it reverses';
    END IF;

    UPDATE bills SET status = 'void', voided_on = p_void_date
     WHERE company_id = p_company_id AND id = p_bill_id;
  ELSE
    SELECT EXISTS (
      SELECT 1 FROM inventory_movements m
       WHERE m.company_id = p_company_id AND m.entry_id = v_bill.posted_entry_id
    ) INTO v_has_movements;

    IF v_has_movements THEN
      -- 0018 decision 4: void and reversal paths write their compensating
      -- movements and then call reverse_entry_internal(), exactly as
      -- reverse_intercompany() does. reverse_entry_internal keeps every guard
      -- reverse_entry has except the inventory one this branch is satisfying:
      -- posted-only, reversal-date ordering, already-reversed, and both
      -- reconciled-through assertions.
      v_rev_id := reverse_entry_internal(
        p_company_id, v_bill.posted_entry_id, p_void_date, p_user_id);

      FOR v_move IN
        SELECT id FROM inventory_movements
         WHERE company_id = p_company_id AND entry_id = v_bill.posted_entry_id
         ORDER BY id
      LOOP
        PERFORM reverse_movement(p_company_id, v_move.id, v_rev_id, p_void_date);
      END LOOP;
    ELSE
      v_rev_id := reverse_entry(p_company_id, v_bill.posted_entry_id, p_void_date, p_user_id);
    END IF;

    UPDATE bills SET status = 'void', void_entry_id = v_rev_id, voided_on = p_void_date
     WHERE company_id = p_company_id AND id = p_bill_id;
  END IF;
END $$;
