-- 0023: selling stocked items, bills of materials, builds, inventory
-- adjustments and opening quantities.
-- Spec (binding): docs/superpowers/specs/2026-08-03-inventory-design.md,
-- especially "As-built decisions from Task 1" and "Opening quantities for a
-- company that imported full history".
-- Plan: docs/superpowers/plans/2026-08-03-inventory.md Task 3.
-- Forward-only. 0018-0022 are committed, applied everywhere (including the dev
-- database, which holds real books) and are NOT edited; every function replaced
-- below reproduces its newest definition verbatim with the new blocks marked,
-- and everything else is new.
--
-- Design in one paragraph. Task 2 made a bill the door stock comes IN through;
-- this is the three doors it goes OUT through — a sale, a build, and an
-- adjustment — plus the one door it comes in through on day one (opening
-- quantities). Every one of them owns BOTH halves of the fact: the movement and
-- the journal entry, in the same transaction, on the same entry, dated the same
-- day, so 0018's per-account check and 0022's stock-backed check both see a
-- complete picture and neither can be satisfied by accident. Nothing here
-- computes a balance, caches a cost, or takes a cost from a caller on the way
-- out: the running average IS the cost of stock leaving, and it is
-- record_movement that says so.
--
-- Six things, in the order they appear:
--
--   1. items_frozen_while_drafted() gains the SALES half of its clause.
--   2. issue_invoice() — its THIRD replacement — sells stocked items.
--   3. void_invoice() gives sold stock back, at the cost that left.
--   4. Builds: build_preview(), post_build(), reverse_build(), and
--      assert_build_posting() — the one place the structural tie has a hole,
--      closed here by an assertion and a deferred constraint trigger.
--   5. record_opening_quantities() — ONE entry, Dr new inventory / Cr the
--      source account the caller names.
--   6. item_profitability() — the report, derived, never stored.

-- ---------------------------------------------------------------------------
-- 1. an item's kind cannot change out from under a draft INVOICE either
-- ---------------------------------------------------------------------------

-- BASE TEXT: db/migrations/0020_inventory_receiving_fixes.sql's
-- items_frozen_while_drafted(). Grepped across 0021-0022: never replaced, so
-- 0020 is the newest definition. Its early-out and its bill clause (including
-- the exact RAISE text) are preserved verbatim; ADDED is the matching clause
-- for a DRAFT INVOICE line.
--
-- WHAT THIS IS AND IS NOT. On the PURCHASING side the flip is exploitable in a
-- way the tie cannot see: a draft bill line's category account is frozen at
-- draft time, so flipping the item to non_inventory afterwards leaves the bill
-- posting money into an account that is, by then, nobody's inventory account —
-- outside check_inventory_ties()'s field of view entirely (0020's header
-- explains the mechanism in full).
--
-- The SALES side is not symmetric in that respect, and this comment says so
-- rather than implying a hole that has not been demonstrated: selling stock
-- requires stock on hand, stock on hand means the item already has movements,
-- and items_frozen_once_moved() (0018) has already welded kind and inventory
-- account shut by then. A draft invoice line naming a MOVEMENT-LESS item can
-- only be issued into "You only have 0 of X on hand".
--
-- It is still refused here, for two reasons that do not depend on finding an
-- exploit. First, a draft invoice is built against one shape of an item and
-- would issue against another — the seller picked a price and a category from a
-- stocked item and would post a sale of something that is no longer stocked,
-- silently and with no cost of sale. Second, the argument above is a chain of
-- three separate guards holding hands; the day any one of them is relaxed for
-- some other good reason, this becomes the hole the purchasing side already is.
-- A guard that costs one clause and removes a class of reasoning is worth more
-- than the reasoning.
CREATE OR REPLACE FUNCTION items_frozen_while_drafted() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE v_id 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_id
    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;

  -- 0023: the same clause on the sales side.
  SELECT inv.id INTO v_id
    FROM invoice_lines l
    JOIN invoices inv ON inv.company_id = l.company_id AND inv.id = l.invoice_id
   WHERE l.company_id = OLD.company_id AND l.item_id = OLD.id
     AND inv.status = 'draft'
   ORDER BY inv.id
   LIMIT 1;

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

  RETURN NEW;
END $$;

-- ---------------------------------------------------------------------------
-- 2. issue_invoice — selling stocked items
-- ---------------------------------------------------------------------------

-- BASE TEXT: db/migrations/0015_sales_tax.sql's issue_invoice(), which is
-- itself 0008's text plus tax handling. Grepped across 0016-0022: never
-- replaced, so 0015 is the newest definition and this is its THIRD version.
-- PRESERVED VERBATIM, in order, every one of them:
--
--   (a) the session-company guard and its exact RAISE text
--       ('session company does not match invoice company')
--   (b) SELECT ... FOR UPDATE on the invoice, and 'invoice % not found'
--   (c) the draft-only guard, 'invoice % is already %'
--   (d) the at-least-one-line guard, 'invoice % has no lines', and v_total as
--       the sum of invoice_lines.amount
--   (e) THE WHOLE TAX BLOCK, unchanged and in place: the "tax amount without a
--       rate" refusal; the ENABLED GATE (sales tax must be on for this company);
--       the rate lookup and the ACTIVE check; the RECOMPUTE of
--       round(taxable_total * rate / 100, 2) over `taxable` lines and its
--       refusal on mismatch. It still runs for BOTH document kinds, because a
--       document_only invoice's tax fields are document facts too.
--   (f) DECISION 1 (0008): document_only's OWN period-lock check, using the
--       same text post_entry() raises, because document_only never reaches
--       post_entry and errors.ts has one mapping for both.
--   (g) THE COUNTER ORDERING, byte for byte: INSERT ... ON CONFLICT DO NOTHING,
--       then SELECT next_invoice_no ... FOR UPDATE, then UPDATE. It still runs
--       for both document kinds, before either branch, so a document_only
--       invoice gets its number exactly as before.
--   (h) the document_only branch: status/invoice_no UPDATE, NO posting.
--   (i) the posting branch verbatim: ensure_ar_account(), the sales-tax-account
--       lookup and its 'no Sales Tax Payable account is configured' refusal,
--       the entry with the invoice's own date and the 'Invoice #<no>' memo, the
--       AR debit of GRAND total (lines + tax), ONE credit per income account,
--       the single tax credit when tax_amount > 0 (and none when it is zero,
--       §3.1), post_entry(), and only then the invoices UPDATE that stamps
--       posted_entry_id.
--   (j) RETURNS v_no.
--
-- ADDED, and only this:
--   1. document_only invoices refuse stocked-item lines. A document_only
--      invoice posts NOTHING, and 0018 forbids a movement without a posted
--      entry, so "sold but never posted" has to be impossible rather than
--      merely unlikely. Mirrors issue_bill's rule exactly (0019 addition 1).
--   2. One SALE movement per stocked line, at the CURRENT AVERAGE — the nine-
--      argument record_movement() with a NULL cost, because "the cost of stock
--      going out is set by the running average, not by the caller". Recorded on
--      the entry the invoice is already posting, in the same transaction, dated
--      the same day (spec as-built decision 1).
--   3. The COGS leg, on that SAME entry: Dr each item's cost account, Cr each
--      item's inventory account, for what the movements actually took out.
--      Grouped per account and read back FROM inventory_movements rather than
--      recomputed, so the money and the stock are the same number by
--      construction and 0018's per-account check and 0022's stock-backed check
--      both pass. Two independent groupings of one set of values, so they sum
--      to the same total and the entry stays balanced.
--
-- Note on the ORDER of the additions: the movements are recorded BEFORE
-- post_entry() deliberately, exactly as in issue_bill — record_movement's own
-- period-lock check uses post_entry's wording, so a locked-period invoice is
-- refused with the same sentence whichever guard sees it first, and the
-- negative-stock refusal happens before any counter or entry work is wasted.
--
-- Note on ZERO-VALUE stock: stock received at no cost sells at no cost, and the
-- HAVING clauses below drop the resulting zero-amount journal lines (§3.1
-- forbids them). The invoice itself still posts its AR and income, so unlike an
-- adjustment there is no empty entry to refuse — the sale is real, its cost is
-- genuinely nothing, and the per-account check reads 0 = 0 on that account.
CREATE OR REPLACE FUNCTION issue_invoice(p_company_id uuid, p_invoice_id bigint, p_user_id uuid)
RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_invoice invoices%ROWTYPE;
  v_line_count int;
  v_total numeric(19,4);
  v_ar_account bigint;
  v_no bigint;
  v_entry_id bigint;
  v_lock date;
  v_rate tax_rates%ROWTYPE;
  v_enabled boolean;
  v_taxable_total numeric(19,4);
  v_expected_tax numeric(19,4);
  v_tax_account bigint;
  v_stocked_lines int;          -- 0023
  v_line record;                -- 0023
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match invoice company';
  END IF;

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

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

  -- 0023: how many lines will move stock, read from the items table AS IT IS
  -- NOW rather than as it was when the draft was written.
  SELECT count(*) INTO v_stocked_lines
    FROM invoice_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.invoice_id = p_invoice_id
     AND i.kind = 'inventory';

  -- Tax validation (both document kinds — a document_only invoice's tax
  -- fields are document facts too and must be just as coherent).
  IF v_invoice.tax_rate_id IS NULL THEN
    IF v_invoice.tax_amount <> 0 THEN
      RAISE EXCEPTION 'invoice % has a tax amount but no tax rate', p_invoice_id;
    END IF;
  ELSE
    -- Sales tax must actually be ON for this company: without this check,
    -- a disabled company could still be handed a taxed invoice (rates and
    -- drafts are plain app-writable rows). void_invoice() is deliberately
    -- NOT gated the same way — voiding a previously taxed invoice reverses
    -- its stored entry and must keep working after a disable.
    SELECT sales_tax_enabled INTO v_enabled FROM company_settings
     WHERE company_id = p_company_id;
    IF v_enabled IS DISTINCT FROM true THEN
      RAISE EXCEPTION
        'sales tax is turned off for this company — turn it on in Settings before issuing a taxed invoice';
    END IF;

    SELECT * INTO v_rate FROM tax_rates
     WHERE company_id = p_company_id AND id = v_invoice.tax_rate_id;
    IF NOT FOUND THEN
      RAISE EXCEPTION 'tax rate % not found', v_invoice.tax_rate_id;
    END IF;
    IF NOT v_rate.active THEN
      RAISE EXCEPTION
        'tax rate ''%'' is inactive — reactivate it or pick another rate', v_rate.name;
    END IF;

    SELECT coalesce(sum(amount), 0) INTO v_taxable_total
      FROM invoice_lines
     WHERE company_id = p_company_id AND invoice_id = p_invoice_id AND taxable;
    -- Integer-cents HALF-UP: numeric round() is half-away-from-zero, which
    -- for non-negative amounts is HALF-UP — identical to the server's math.
    v_expected_tax := round(v_taxable_total * v_rate.rate / 100, 2);
    IF v_invoice.tax_amount <> v_expected_tax THEN
      RAISE EXCEPTION
        'invoice % tax amount % does not match the % rate on its taxable lines (should be %)',
        p_invoice_id, v_invoice.tax_amount, v_rate.rate, v_expected_tax;
    END IF;
  END IF;

  -- DECISION 1 (0008): document_only never reaches post_entry() below, so
  -- its own lock check has to run here instead — same text post_entry
  -- raises, so errors.ts maps it exactly the same way for both kinds.
  IF v_invoice.document_only THEN
    -- 0023 ADDITION 1: a document_only invoice posts nothing, and stock never
    -- moves without a posted entry behind it (0018's movements_entry_required
    -- plus check_movement_entry_posted's POSTED assertion). Refused BEFORE the
    -- lock check, mirroring issue_bill, so the more specific sentence wins.
    IF v_stocked_lines > 0 THEN
      RAISE EXCEPTION
        'invoice % is recorded for tracking only, so it cannot move stock — enter it as a normal invoice if it changes the quantity on hand',
        p_invoice_id;
    END IF;

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

  INSERT INTO invoice_counters (company_id) VALUES (p_company_id)
    ON CONFLICT (company_id) DO NOTHING;
  SELECT next_invoice_no INTO v_no FROM invoice_counters
   WHERE company_id = p_company_id FOR UPDATE;
  UPDATE invoice_counters SET next_invoice_no = v_no + 1
   WHERE company_id = p_company_id;

  IF v_invoice.document_only THEN
    UPDATE invoices SET status = 'open', invoice_no = v_no
     WHERE company_id = p_company_id AND id = p_invoice_id;
  ELSE
    v_ar_account := ensure_ar_account(p_company_id);

    IF v_invoice.tax_amount > 0 THEN
      SELECT sales_tax_account_id INTO v_tax_account FROM company_settings
       WHERE company_id = p_company_id;
      IF v_tax_account IS NULL THEN
        RAISE EXCEPTION
          'no Sales Tax Payable account is configured — turn sales tax on in Settings first';
      END IF;
    END IF;

    INSERT INTO journal_entries (company_id, entry_date, memo, created_by)
    VALUES (p_company_id, v_invoice.invoice_date, 'Invoice #' || v_no, p_user_id)
    RETURNING id INTO v_entry_id;

    -- AR debit is the GRAND total: lines plus tax.
    INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit)
    VALUES (p_company_id, v_entry_id, v_ar_account, v_total + v_invoice.tax_amount, 0);

    INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit)
    SELECT p_company_id, v_entry_id, income_account_id, 0, sum(amount)
      FROM invoice_lines
     WHERE company_id = p_company_id AND invoice_id = p_invoice_id
     GROUP BY income_account_id;

    IF v_invoice.tax_amount > 0 THEN
      INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit)
      VALUES (p_company_id, v_entry_id, v_tax_account, 0, v_invoice.tax_amount);
    END IF;

    -- 0023 ADDITION 2: the stock half of the same fact. NULL cost — the
    -- running average is what stock going out is worth, and letting a caller
    -- name it would be a second source of truth (0018/0020's rule).
    FOR v_line IN
      SELECT l.id, l.item_id, l.quantity
        FROM invoice_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.invoice_id = p_invoice_id
         AND i.kind = 'inventory'
       ORDER BY l.id
    LOOP
      PERFORM record_movement(
        p_company_id, v_line.item_id, v_invoice.invoice_date, -v_line.quantity,
        NULL::numeric, 'sale', 'invoice_line', v_line.id, v_entry_id);
    END LOOP;

    -- 0023 ADDITION 3: the COGS leg, on the SAME entry, from what the
    -- movements actually took out. Sale movement values are negative, so
    -- -sum(value) is the positive cost of what left. The two groupings below
    -- partition one set of values, so their totals are equal and the entry
    -- stays balanced; the inventory grouping is the one 0018's per-account
    -- check compares against, and it matches by construction because both read
    -- the same rows.
    IF v_stocked_lines > 0 THEN
      INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit)
      SELECT p_company_id, v_entry_id, i.cogs_account_id, (-sum(m.value))::numeric(19,4), 0
        FROM inventory_movements m
        JOIN items i ON i.company_id = m.company_id AND i.id = m.item_id
       WHERE m.company_id = p_company_id AND m.entry_id = v_entry_id
       GROUP BY i.cogs_account_id
      HAVING sum(m.value) <> 0;

      INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit)
      SELECT p_company_id, v_entry_id, i.inventory_account_id, 0, (-sum(m.value))::numeric(19,4)
        FROM inventory_movements m
        JOIN items i ON i.company_id = m.company_id AND i.id = m.item_id
       WHERE m.company_id = p_company_id AND m.entry_id = v_entry_id
       GROUP BY i.inventory_account_id
      HAVING sum(m.value) <> 0;
    END IF;

    PERFORM post_entry(p_company_id, v_entry_id);

    UPDATE invoices SET status = 'open', invoice_no = v_no, posted_entry_id = v_entry_id
     WHERE company_id = p_company_id AND id = p_invoice_id;
  END IF;

  RETURN v_no;
END $$;

-- ---------------------------------------------------------------------------
-- 3. void_invoice — giving sold stock back
-- ---------------------------------------------------------------------------

-- BASE TEXT: db/migrations/0008_ar_closeout.sql's void_invoice(). Grepped
-- across 0009-0022: never replaced, so 0008 is the newest definition.
-- PRESERVED VERBATIM: the session-company guard, the FOR UPDATE lookup and
-- 'invoice % not found', the open-only guard, the payments-applied refusal, and
-- DECISION 1's document_only branch entire (its own period-lock check in
-- post_entry's wording, its reversal-date check in reverse_entry_internal's
-- wording, and the status/voided_on UPDATE with no posting).
--
-- ADDED: the branch for an invoice that sold stock. 0018 makes reverse_entry()
-- refuse a movement-bearing entry outright, which would otherwise leave a
-- stocked invoice permanently un-voidable. The fix is the pattern
-- reverse_intercompany() and void_bill() already use: create and post the
-- reversal entry FIRST (its id and date are what the compensating movements
-- need), then write one compensating movement per original.
--
-- THE RULE FOR VOIDING A SALE, stated because it differs from a receipt's.
-- reverse_movement() refuses to take stock back off the shelf that is no longer
-- there — that is what stops a received bill being voided after its stock has
-- been used. A SALE reverses the other way: the original movement had a
-- NEGATIVE quantity, so the compensating one is POSITIVE and puts units back.
-- reverse_movement's guard is `on_hand - original_qty < 0`, which for a
-- negative original is `on_hand + |qty| < 0` — impossible while on_hand is not
-- itself negative, and it never is. So voiding a sale always succeeds
-- arithmetically, and there is deliberately no refusal here to match the
-- receipt's.
--
-- WHAT THE RETURNED UNITS COST is the other half of that rule, and it is not
-- the current average: reverse_movement COPIES the row it reverses, so the
-- exact value that left comes back, even if the average has moved several times
-- since. That is what makes the reversal journal entry — which
-- reverse_entry_internal() writes as the exact mirror of the original — tie to
-- the movements it accompanies. It is also simply correct: the sale took $30 of
-- value out of Inventory and charged $30 to cost of sales, so undoing it must
-- put $30 back and take $30 off, whatever the shelf is worth today.
CREATE OR REPLACE FUNCTION void_invoice(
  p_company_id uuid, p_invoice_id bigint, p_void_date date, p_user_id uuid
) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_invoice invoices%ROWTYPE;
  v_app_count int;
  v_rev_id bigint;
  v_lock date;
  v_move record;              -- 0023
  v_has_movements boolean;    -- 0023
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match invoice company';
  END IF;

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

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

  IF v_invoice.document_only THEN
    -- DECISION 1: document_only never reaches reverse_entry() below, so
    -- neither of its two date guards ever ran for this branch. Both are
    -- re-implemented here with the SAME RAISE text as their ledger-side
    -- counterparts (post_entry's lock message / reverse_entry_internal's
    -- ordering message), so errors.ts's existing mappings fire unchanged.
    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_invoice.invoice_date THEN
      RAISE EXCEPTION 'reversal date cannot precede the entry it reverses';
    END IF;

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

    IF v_has_movements THEN
      -- 0018 decision 4: write the compensating movements and then use
      -- reverse_entry_internal(), the same door reverse_intercompany() uses.
      -- It 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_invoice.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_invoice.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_invoice.posted_entry_id, p_void_date, p_user_id);
    END IF;

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

-- ---------------------------------------------------------------------------
-- 4. builds
-- ---------------------------------------------------------------------------

-- build_preview() — what a build WOULD draw, without drawing it.
--
-- The draw value mirrors record_movement()'s own arithmetic exactly, including
-- 0018 decision 5: draining an item to exactly zero assigns the WHOLE remaining
-- value to that last movement rather than round(qty x average, 4), so a
-- fraction of a cent is never stranded in the Inventory account with no
-- quantity left to carry it out. The spec says in so many words that reports
-- and previews must not re-derive that number as qty x average; this is that
-- rule, written once, here.
CREATE FUNCTION build_preview(
  p_company_id uuid, p_assembly_item_id bigint, p_qty numeric
) RETURNS TABLE (
  component_item_id bigint,
  sku text,
  name text,
  qty_per numeric(19,4),
  qty_needed numeric(19,4),
  qty_on_hand numeric(19,4),
  average_cost numeric(19,6),
  draw_value numeric(19,4),
  sufficient boolean
)
LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public AS $$
DECLARE v_qty numeric(19,4);
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;
  IF p_qty IS NULL OR p_qty = 'NaN'::numeric THEN
    RAISE EXCEPTION 'enter how many you are building';
  END IF;
  v_qty := round(p_qty, 4);
  IF v_qty <= 0 THEN
    RAISE EXCEPTION 'enter how many you are building';
  END IF;

  RETURN QUERY
  WITH pos AS (
    SELECT a.component_item_id AS cid,
           a.qty_per,
           round(a.qty_per * v_qty, 4) AS needed,
           coalesce(sum(m.qty), 0)::numeric(19,4) AS on_hand,
           coalesce(sum(m.value), 0)::numeric(19,4) AS total_value
      FROM assemblies a
      LEFT JOIN inventory_movements m
        ON m.company_id = a.company_id AND m.item_id = a.component_item_id
     WHERE a.company_id = p_company_id AND a.assembly_item_id = p_assembly_item_id
     GROUP BY a.component_item_id, a.qty_per
  )
  SELECT pos.cid, i.sku, i.name, pos.qty_per, pos.needed, pos.on_hand,
         CASE WHEN pos.on_hand = 0 THEN 0::numeric(19,6)
              ELSE round(pos.total_value / pos.on_hand, 6) END,
         CASE WHEN pos.needed > pos.on_hand OR pos.on_hand = 0 THEN 0::numeric(19,4)
              WHEN pos.on_hand - pos.needed = 0 THEN pos.total_value
              ELSE round(pos.needed * round(pos.total_value / pos.on_hand, 6), 4)
         END,
         (pos.needed <= pos.on_hand)
    FROM pos
    JOIN items i ON i.company_id = p_company_id AND i.id = pos.cid
   ORDER BY i.name;
END $$;

-- assert_build_posting() — THE ONE PLACE THE STRUCTURAL TIE HAS A HOLE.
--
-- Spec as-built decision 3, second paragraph, is explicit about this and it is
-- worth restating: for a SAME-ACCOUNT build the per-account deferred check
-- degenerates to 0 = 0. The movements net to zero on that account (components
-- out, assembly in, same account) and so do the wash entry's two lines, so a
-- wash of 999,999.00 against 10.00 of real stock commits with
-- inventory_out_of_tie() AND ledger_out_of_balance() both reading a serene
-- 0.0000. Nothing at the tie's layer can see it, because there is nothing
-- wrong with the tie — the account's balance is genuinely unchanged and
-- genuinely equal to the stock behind it. What is wrong is the SIZE of a
-- transaction that appears in the account register and in every drill-through.
--
-- So the build asserts its own posting, completely rather than only the wash:
--
--   * the assembly's inventory account must be DEBITED exactly the build's
--     value — the value read back from the immutable build_in movement row,
--     not from a variable this function was handed;
--   * every account the components came out of must be CREDITED exactly what
--     came out of it;
--   * and no other account may appear on the entry at all.
--
-- Written that way it covers the same-account wash (Dr IA v / Cr IA v), the
-- different-account build (Dr assembly's v / Cr components'), the MIXED build
-- where the assembly shares an account with only some of its components (Dr IA
-- v / Cr IA <drawn from IA> / Cr <others>, which nets correctly and whose gross
-- amounts are still pinned), and it refuses padding lines that would net out.
--
-- It runs BOTH as an explicit call inside post_build() — fail fast, before
-- post_entry(), with the numbers in the message — and as a DEFERRED CONSTRAINT
-- TRIGGER on `builds`, so the rule belongs to the database rather than to one
-- function (CLAUDE.md §12). The trigger is what makes it testable: a build row
-- and a wrong wash written by hand, in one transaction, are refused at COMMIT.
CREATE FUNCTION assert_build_posting(p_company_id uuid, p_build_id bigint)
RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_build builds%ROWTYPE;
  v_assembly items%ROWTYPE;
  v_value numeric(19,4);
  v_bad record;
BEGIN
  SELECT * INTO v_build FROM builds
   WHERE company_id = p_company_id AND id = p_build_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'build % not found', p_build_id;
  END IF;

  SELECT * INTO v_assembly FROM items
   WHERE company_id = p_company_id AND id = v_build.assembly_item_id;

  -- A build header hung on an entry that moved NO STOCK AT ALL has nothing to
  -- check here, and is not a hole: 0022's check_entry_stock_backed() already
  -- refuses any entry that puts money into or out of an inventory account
  -- without the movements behind it, so such an entry cannot touch inventory in
  -- the first place. This function's job is the one thing 0022 and 0018 between
  -- them cannot see — the AMOUNT of a build that really did move stock, whose
  -- per-account net is zero because everything shares an account. (0018 has
  -- always permitted a movement-less build row; tests/invariants/inventory.test.ts
  -- uses exactly that shape as a fixture for the immutability, RLS and
  -- numbering rules, none of which are about posting.)
  IF NOT EXISTS (
    SELECT 1 FROM inventory_movements m
     WHERE m.company_id = p_company_id AND m.entry_id = v_build.entry_id
  ) THEN
    RETURN;
  END IF;

  -- What the build was worth, straight out of the movement row — the immutable
  -- record of what actually happened, and a different table from the one being
  -- checked. Once ANY stock moved on this entry, a build that recorded nothing
  -- coming in is half a fact and is refused.
  SELECT coalesce(sum(m.value), 0)::numeric(19,4) INTO v_value
    FROM inventory_movements m
   WHERE m.company_id = p_company_id AND m.entry_id = v_build.entry_id
     AND m.reason = 'build_in' AND m.item_id = v_build.assembly_item_id
     AND m.reverses_movement_id IS NULL;

  IF v_value <= 0 THEN
    RAISE EXCEPTION
      'build #% did not record what it was worth, so its journal entry cannot be checked against anything',
      v_build.build_no;
  END IF;

  FOR v_bad IN
    WITH expected AS (
      SELECT v_assembly.inventory_account_id AS account_id,
             v_value AS dr, 0::numeric(19,4) AS cr
      UNION ALL
      SELECT i.inventory_account_id, 0::numeric(19,4), (-sum(m.value))::numeric(19,4)
        FROM inventory_movements m
        JOIN items i ON i.company_id = m.company_id AND i.id = m.item_id
       WHERE m.company_id = p_company_id AND m.entry_id = v_build.entry_id
         AND m.reason = 'build_out' AND m.reverses_movement_id IS NULL
       GROUP BY i.inventory_account_id
    ),
    want AS (
      SELECT account_id, sum(dr)::numeric(19,4) AS dr, sum(cr)::numeric(19,4) AS cr
        FROM expected GROUP BY account_id
    ),
    got AS (
      SELECT l.account_id, sum(l.debit)::numeric(19,4) AS dr,
             sum(l.credit)::numeric(19,4) AS cr
        FROM journal_lines l
       WHERE l.company_id = p_company_id AND l.entry_id = v_build.entry_id
       GROUP BY l.account_id
    )
    SELECT coalesce(w.account_id, g.account_id) AS account_id,
           coalesce(w.dr, 0)::numeric(19,4) AS want_dr,
           coalesce(w.cr, 0)::numeric(19,4) AS want_cr,
           coalesce(g.dr, 0)::numeric(19,4) AS got_dr,
           coalesce(g.cr, 0)::numeric(19,4) AS got_cr
      FROM want w
      FULL JOIN got g ON g.account_id = w.account_id
     WHERE coalesce(w.dr, 0) <> coalesce(g.dr, 0)
        OR coalesce(w.cr, 0) <> coalesce(g.cr, 0)
     ORDER BY 1
     LIMIT 1
  LOOP
    RAISE EXCEPTION
      'build #% is worth %, but its journal entry puts % in and % out of account % instead of % and % — a build''s entry has to say exactly what the build was worth, on the accounts the parts came out of and the finished item went into',
      v_build.build_no, v_value, v_bad.got_dr, v_bad.got_cr, v_bad.account_id,
      v_bad.want_dr, v_bad.want_cr;
  END LOOP;
END $$;

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

CREATE FUNCTION check_build_posting() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  PERFORM assert_build_posting(NEW.company_id, NEW.id);
  RETURN NULL;
END $$;

-- DEFERRED, like its 0018 and 0022 counterparts: post_build() opens the entry,
-- inserts the build row (which is what the movements name as their source),
-- records the movements, writes the lines and posts — and all of that has to be
-- finished by COMMIT, not before.
--
-- INSERT only. A build's one permitted later change is the posted -> reversed
-- flip (0018's builds_immutable), which touches neither the original entry nor
-- its movements.
CREATE CONSTRAINT TRIGGER builds_entry_matches
  AFTER INSERT ON builds
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION check_build_posting();

-- post_build() — the app-facing door onto a build. Owns BOTH halves: the
-- movements and the journal entry, in one transaction, on one entry, all dated
-- the day of the build.
--
-- A build costs EXACTLY its components (spec non-goals: no labor, no overhead,
-- no routings). Components leave at the running average — record_movement
-- refuses to be told a cost on the way out — and the assembly arrives at the
-- total of what left, passed as a VALUE rather than a unit cost (0020's
-- ten-argument overload) so the assembly's unit cost is DERIVED from the money
-- and the two can never disagree by a rounding step.
--
-- Returns the build id.
CREATE FUNCTION post_build(
  p_company_id uuid,
  p_assembly_item_id bigint,
  p_qty numeric,
  p_built_on date,
  p_memo text,
  p_user_id uuid
) RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_assembly items%ROWTYPE;
  v_qty numeric(19,4);
  v_no bigint;
  v_entry_id bigint;
  v_build_id bigint;
  v_comp record;
  v_movement_id bigint;
  v_value numeric(19,4);
  v_total numeric(19,4) := 0;
  v_components int;
  v_memo text;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  IF p_qty IS NULL OR p_qty = 'NaN'::numeric THEN
    RAISE EXCEPTION 'enter how many you are building';
  END IF;
  v_qty := round(p_qty, 4);
  IF v_qty <= 0 THEN
    RAISE EXCEPTION 'enter how many you are building';
  END IF;

  -- LOCK FIRST, and lock every item this build will touch in ONE statement, in
  -- id order. record_movement() takes these same row locks one at a time as it
  -- goes; two concurrent builds sharing a component could otherwise take them
  -- in opposite orders and deadlock. Taking them all up front, in a fixed
  -- order, makes that impossible. (The lock is also what the on-hand and
  -- average reads below and inside record_movement are protected by — the same
  -- reason record_movement locks at all.)
  PERFORM 1 FROM items
   WHERE company_id = p_company_id
     AND (id = p_assembly_item_id
          OR id IN (SELECT component_item_id FROM assemblies
                     WHERE company_id = p_company_id
                       AND assembly_item_id = p_assembly_item_id))
   ORDER BY id
   FOR UPDATE;

  SELECT * INTO v_assembly FROM items
   WHERE company_id = p_company_id AND id = p_assembly_item_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'item % not found', p_assembly_item_id;
  END IF;
  IF v_assembly.kind <> 'inventory' THEN
    RAISE EXCEPTION
      '''%'' is not a stocked item, so it has no quantity on hand', v_assembly.name;
  END IF;

  SELECT count(*) INTO v_components FROM assemblies
   WHERE company_id = p_company_id AND assembly_item_id = p_assembly_item_id;
  IF v_components = 0 THEN
    RAISE EXCEPTION
      '''%'' has no parts list yet — say what it is made from before you build it',
      v_assembly.name;
  END IF;

  v_memo := nullif(btrim(coalesce(p_memo, '')), '');
  v_no := allocate_build_no(p_company_id);

  INSERT INTO journal_entries (company_id, entry_date, memo, created_by)
  VALUES (p_company_id, p_built_on,
          coalesce(v_memo,
                   'Build #' || v_no || ' — ' || format_quantity(v_qty)
                   || ' ' || v_assembly.name),
          p_user_id)
  RETURNING id INTO v_entry_id;

  -- The build row exists before its movements do, because they name it as
  -- their source. builds.entry_id is NOT NULL and the entry above satisfies it
  -- while still a draft; the deferred trigger checks the whole picture at
  -- COMMIT, by which time everything below has run.
  INSERT INTO builds
    (company_id, build_no, assembly_item_id, qty, built_on, entry_id, memo, created_by)
  VALUES
    (p_company_id, v_no, p_assembly_item_id, v_qty, p_built_on, v_entry_id,
     v_memo, p_user_id)
  RETURNING id INTO v_build_id;

  -- Components out, at the running average. Negative stock is refused inside
  -- record_movement, in English, naming the part and both quantities.
  FOR v_comp IN
    SELECT a.component_item_id AS item_id,
           round(a.qty_per * v_qty, 4) AS needed,
           i.name AS item_name
      FROM assemblies a
      JOIN items i ON i.company_id = a.company_id AND i.id = a.component_item_id
     WHERE a.company_id = p_company_id AND a.assembly_item_id = p_assembly_item_id
     ORDER BY a.component_item_id
  LOOP
    IF v_comp.needed = 0 THEN
      RAISE EXCEPTION
        'building % of ''%'' works out to no ''%'' at all — build more of them at once',
        format_quantity(v_qty), v_assembly.name, v_comp.item_name;
    END IF;

    v_movement_id := record_movement(
      p_company_id, v_comp.item_id, p_built_on, -v_comp.needed, NULL::numeric,
      'build_out', 'build', v_build_id, v_entry_id);

    SELECT value INTO v_value FROM inventory_movements
     WHERE company_id = p_company_id AND id = v_movement_id;
    v_total := v_total - v_value;      -- v_value is negative: stock going out
  END LOOP;

  -- §3.1 forbids a zero-amount journal line and every movement must ride on a
  -- posted entry, so a build whose parts are all valueless has nowhere to go.
  -- (Spec known limit 8(b), the build half of it.)
  IF v_total = 0 THEN
    RAISE EXCEPTION
      'the parts for ''%'' have no value on hand, so this build would move no money at all — there is nothing to post',
      v_assembly.name;
  END IF;

  -- The assembly in, worth exactly what the parts were worth.
  PERFORM record_movement(
    p_company_id, p_assembly_item_id, p_built_on, v_qty, NULL::numeric,
    'build_in', 'build', v_build_id, v_entry_id, v_total);

  -- Dr the assembly's inventory account for the build value; Cr each account
  -- the parts actually came out of, for what came out of it. When they are the
  -- same account this is the spec's wash entry, arrived at by the same rule
  -- rather than as a special case: two lines, each with exactly one non-zero
  -- side, netting zero on the account and putting the build in the register
  -- where an auditor can see it.
  INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit, memo)
  VALUES (p_company_id, v_entry_id, v_assembly.inventory_account_id, v_total, 0, v_memo);

  INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit, memo)
  SELECT p_company_id, v_entry_id, i.inventory_account_id, 0,
         (-sum(m.value))::numeric(19,4), v_memo
    FROM inventory_movements m
    JOIN items i ON i.company_id = m.company_id AND i.id = m.item_id
   WHERE m.company_id = p_company_id AND m.entry_id = v_entry_id
     AND m.reason = 'build_out'
   GROUP BY i.inventory_account_id
  HAVING sum(m.value) <> 0;

  -- Fail fast, with the numbers, before anything is posted. The deferred
  -- trigger above says the same thing at COMMIT for anyone who did not come
  -- through this door.
  PERFORM assert_build_posting(p_company_id, v_build_id);

  PERFORM post_entry(p_company_id, v_entry_id);

  INSERT INTO audit_log (company_id, user_id, action, entity, entity_id, detail)
  VALUES (p_company_id, p_user_id, 'build', 'build', v_build_id::text,
          -- money and quantities as TEXT, never JSON numbers: jsonb keeps
          -- arbitrary precision but every JSON reader turns them into floats.
          jsonb_build_object('build_no', v_no, 'qty', v_qty::text,
                             'value', v_total::text, 'entry_id', v_entry_id));

  RETURN v_build_id;
END $$;

-- reverse_build() — undoing a build. Compensating movements BOTH ways: the
-- finished units come back off the shelf, the parts go back on it, each at the
-- exact cost that moved, because reverse_movement() copies the row it reverses.
CREATE FUNCTION reverse_build(
  p_company_id uuid, p_build_id bigint, p_reversal_date date, p_user_id uuid
) RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_build builds%ROWTYPE;
  v_assembly items%ROWTYPE;
  v_on_hand numeric(19,4);
  v_rev_id bigint;
  v_move record;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  SELECT * INTO v_build FROM builds
   WHERE company_id = p_company_id AND id = p_build_id
   FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'build % not found', p_build_id;
  END IF;
  IF v_build.status <> 'posted' THEN
    RAISE EXCEPTION 'build #% has already been reversed', v_build.build_no;
  END IF;

  SELECT * INTO v_assembly FROM items
   WHERE company_id = p_company_id AND id = v_build.assembly_item_id
   FOR UPDATE;

  -- Undoing a build takes the finished units back OFF the shelf. If they have
  -- already been sold, or built into something else, there is nothing to take
  -- back and average cost is meaningless below zero. reverse_movement() refuses
  -- this too — it is the real guard — but its sentence is written for a
  -- RECEIPT, and a user who clicked "undo this build" should be told about a
  -- build. So this is the earlier and better-worded of the two.
  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_build.assembly_item_id;
  IF v_on_hand < v_build.qty THEN
    RAISE EXCEPTION
      'You can''t undo build #% — it made % of ''%'' and only % are still on hand. Record an inventory adjustment instead.',
      v_build.build_no, format_quantity(v_build.qty), v_assembly.name,
      format_quantity(v_on_hand);
  END IF;

  -- Order matters, exactly as in void_bill: the reversal entry is created AND
  -- POSTED first, because the compensating movements need its id and must share
  -- its date.
  v_rev_id := reverse_entry_internal(
    p_company_id, v_build.entry_id, p_reversal_date, p_user_id);

  FOR v_move IN
    SELECT id FROM inventory_movements
     WHERE company_id = p_company_id AND entry_id = v_build.entry_id
     ORDER BY id
  LOOP
    PERFORM reverse_movement(p_company_id, v_move.id, v_rev_id, p_reversal_date);
  END LOOP;

  UPDATE builds SET status = 'reversed'
   WHERE company_id = p_company_id AND id = p_build_id;

  INSERT INTO audit_log (company_id, user_id, action, entity, entity_id, detail)
  VALUES (p_company_id, p_user_id, 'reverse', 'build', p_build_id::text,
          jsonb_build_object('build_no', v_build.build_no, 'entry_id', v_rev_id));

  RETURN v_rev_id;
END $$;

-- ---------------------------------------------------------------------------
-- 5. opening quantities — ONE entry
-- ---------------------------------------------------------------------------

-- Spec, "Opening quantities for a company that imported full history": this
-- must be ONE entry —
--
--     Dr <new inventory account>    Cr <the source account the caller names>
--
-- — rather than the two-step path 0021's English steers to today. The two-step
-- path is legal and ties throughout, but between its two steps TOTAL ASSETS ARE
-- OVERSTATED: the new inventory account already holds the value while the old
-- history-funded account has not yet been drained. One entry has no window.
--
-- WHY THIS CANNOT BE record_adjustment(). That function refuses an ASSET on the
-- other side, and it is right to: shrinkage posted to Cash misstates the bank
-- balance and corrupts the next reconciliation. But a migrated company's source
-- IS an asset — the old "1300 Inventory" that imported history funded — so
-- opening quantities need their own door with its own rule about what a
-- sensible source is.
--
-- WHAT A SENSIBLE SOURCE IS, and every refusal below in one place:
--   * not any item's inventory account. Crediting one inventory account to
--     debit another moves value between two accounts in the tie set with
--     movements behind only one of them; 0018's per-account check cannot see it
--     (it builds its account set from the movements, and the credited account
--     has none on this entry). Same rule record_adjustment() applies, and for
--     the same reason.
--   * EQUITY (Opening Balance Equity, for a fresh set of books) or ASSET (the
--     account imported history already put the value in). Income and expense
--     are refused: stock you already own did not arrive as revenue or as this
--     period's cost. Liability is refused: you are not borrowing it either.
--   * when the source is an ASSET, it must actually hold at least what is being
--     moved out of it. A short count is fine and stays in the source account —
--     the spec says so explicitly, and it is the correct answer — but an
--     over-count would credit an asset below zero, and a negative Inventory
--     account is not a thing that can be explained to anybody.
--   * every TARGET account must be empty-or-backed, 0021's rule, in 0021's
--     words. It is 0021's guard that got the items onto a clean account in the
--     first place; re-stating it here means the endpoint's own precondition is
--     checked at the moment it matters rather than assumed from history.
--   * every item must be stocked, appear once, and have NO stock history — this
--     is an OPENING count, not a correction. A correction is
--     record_adjustment().
--
-- Returns the journal entry id.
CREATE FUNCTION record_opening_quantities(
  p_company_id uuid,
  p_moved_on date,
  p_source_account_id bigint,
  -- [{"item_id": 1, "qty": "400", "unit_cost": "7.50"}, ...]. Money and
  -- quantities as JSON STRINGS: jsonb numbers survive Postgres exactly, but
  -- every JSON writer on the way in turns them into floats first (§2).
  p_items jsonb,
  p_memo text,
  p_user_id uuid
) RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_item items%ROWTYPE;
  v_row record;
  v_bad record;
  v_src_type text;
  v_src_name text;
  v_src_balance numeric(19,4);
  v_expected_total numeric(19,4);
  v_total numeric(19,4) := 0;
  v_entry_id bigint;
  v_movement_id bigint;
  v_value numeric(19,4);
  v_diff numeric(19,4);
  v_amount text;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  IF p_memo IS NULL OR btrim(p_memo) = '' THEN
    RAISE EXCEPTION
      'say where these opening quantities came from — a count nobody explained is impossible to audit later';
  END IF;

  IF p_items IS NULL OR jsonb_typeof(p_items) <> 'array'
     OR jsonb_array_length(p_items) = 0 THEN
    RAISE EXCEPTION 'list at least one item and how many of it you have on hand';
  END IF;

  FOR v_bad IN
    SELECT (e->>'item_id')::bigint AS item_id
      FROM jsonb_array_elements(p_items) e
     GROUP BY 1 HAVING count(*) > 1
     ORDER BY 1 LIMIT 1
  LOOP
    RAISE EXCEPTION 'each item can only be counted once — item % is listed twice',
      v_bad.item_id;
  END LOOP;

  -- Pass one: validate everything before anything is written, so a bad line
  -- eight items down never leaves half a count recorded.
  v_expected_total := 0;
  FOR v_row IN
    SELECT (e->>'item_id')::bigint AS item_id,
           (e->>'qty')::numeric AS qty,
           (e->>'unit_cost')::numeric AS unit_cost
      FROM jsonb_array_elements(p_items) e
     ORDER BY 1
  LOOP
    SELECT * INTO v_item FROM items
     WHERE company_id = p_company_id AND id = v_row.item_id;
    IF NOT FOUND THEN
      RAISE EXCEPTION 'item % not found', v_row.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;
    IF v_row.qty IS NULL OR v_row.qty = 'NaN'::numeric OR round(v_row.qty, 4) <= 0 THEN
      RAISE EXCEPTION
        'an opening count of ''%'' has to be more than zero — leave the item out if you have none of it',
        v_item.name;
    END IF;
    IF v_row.unit_cost IS NULL OR v_row.unit_cost = 'NaN'::numeric OR v_row.unit_cost < 0 THEN
      RAISE EXCEPTION
        'enter what ''%'' cost you — an opening count needs a cost as well as a quantity',
        v_item.name;
    END IF;
    IF EXISTS (SELECT 1 FROM inventory_movements
                WHERE company_id = p_company_id AND item_id = v_item.id) THEN
      RAISE EXCEPTION
        '''%'' already has stock history, so this is not its opening count — record an inventory adjustment instead',
        v_item.name;
    END IF;
    -- record_movement()'s own arithmetic, so the total checked against the
    -- source account below is the total that will actually post.
    v_expected_total := v_expected_total
      + round(round(v_row.qty, 4) * round(v_row.unit_cost, 6), 4);
  END LOOP;

  IF v_expected_total <= 0 THEN
    RAISE EXCEPTION
      'these opening quantities are worth nothing at all, so there is nothing to record';
  END IF;

  -- The source account.
  SELECT type, name INTO v_src_type, v_src_name FROM accounts
   WHERE company_id = p_company_id AND id = p_source_account_id;
  IF v_src_type IS NULL THEN
    RAISE EXCEPTION 'account % not found', p_source_account_id;
  END IF;

  IF EXISTS (SELECT 1 FROM items i
              WHERE i.company_id = p_company_id
                AND i.inventory_account_id = p_source_account_id) THEN
    RAISE EXCEPTION
      '''%'' is an item''s inventory account, so the opening value cannot come out of it — use Opening Balance Equity, or the account your imported history already put the value in.',
      v_src_name;
  END IF;

  IF v_src_type NOT IN ('equity', 'asset') THEN
    RAISE EXCEPTION
      'opening quantities come from Opening Balance Equity on a fresh set of books, or from the account your imported history already put the value in — ''%'' is a% % account.',
      v_src_name,
      CASE WHEN v_src_type IN ('income', 'expense') THEN 'n' ELSE '' END, v_src_type;
  END IF;

  IF v_src_type = 'asset' THEN
    SELECT coalesce(sum(l.debit - l.credit), 0)::numeric(19,4) INTO v_src_balance
      FROM journal_lines l
      JOIN journal_entries e ON e.company_id = l.company_id AND e.id = l.entry_id
     WHERE l.company_id = p_company_id AND l.account_id = p_source_account_id
       AND e.status = 'posted';
    IF v_src_balance < v_expected_total THEN
      RAISE EXCEPTION
        '''%'' only holds $%, but these opening quantities come to $% — that would leave it with a negative balance. Check the counts, or bring the difference in from Opening Balance Equity.',
        v_src_name,
        to_char(v_src_balance, 'FM999999999990.00'),
        to_char(v_expected_total, 'FM999999999990.00');
    END IF;
  END IF;

  -- Every TARGET account must be empty-or-backed — 0021's rule, restated at
  -- the moment it matters, in 0021/0022's own words so errors.ts maps it
  -- identically.
  FOR v_bad IN
    WITH targets AS (
      SELECT DISTINCT i.inventory_account_id AS account_id
        FROM jsonb_array_elements(p_items) e
        JOIN items i ON i.company_id = p_company_id
                    AND i.id = (e->>'item_id')::bigint
       WHERE i.inventory_account_id IS NOT NULL
    )
    SELECT a.name,
           (SELECT coalesce(sum(l.debit - l.credit), 0)::numeric(19,4)
              FROM journal_lines l
              JOIN journal_entries je
                ON je.company_id = l.company_id AND je.id = l.entry_id
             WHERE l.company_id = p_company_id
               AND l.account_id = t.account_id
               AND je.status = 'posted') AS ledger,
           (SELECT coalesce(sum(m.value), 0)::numeric(19,4)
              FROM inventory_movements m
              JOIN items i2 ON i2.company_id = m.company_id AND i2.id = m.item_id
             WHERE m.company_id = p_company_id
               AND i2.inventory_account_id = t.account_id) AS backed
      FROM targets t
      JOIN accounts a ON a.company_id = p_company_id AND a.id = t.account_id
     ORDER BY a.code
  LOOP
    IF v_bad.ledger <> v_bad.backed THEN
      v_diff := abs(v_bad.ledger - v_bad.backed);
      v_amount := CASE
        WHEN v_diff = round(v_diff, 2) THEN to_char(v_diff, 'FM999999999990.00')
        ELSE to_char(v_diff, 'FM999999999990.0000')
      END;
      IF v_bad.ledger > v_bad.backed THEN
        RAISE EXCEPTION
          '''%'' already holds $% that no stock is recorded behind. Point this item at a new inventory account and record your opening quantities there — that moves the value across and keeps your stock and your books matched.',
          v_bad.name, v_amount;
      END IF;
      RAISE EXCEPTION
        '''%'' is $% short of the stock already recorded in it, so another item cannot be added to it until that is put right.',
        v_bad.name, v_amount;
    END IF;
  END LOOP;

  -- Pass two: write it, as ONE entry.
  INSERT INTO journal_entries (company_id, entry_date, memo, created_by)
  VALUES (p_company_id, p_moved_on, p_memo, p_user_id)
  RETURNING id INTO v_entry_id;

  FOR v_row IN
    SELECT (e->>'item_id')::bigint AS item_id,
           (e->>'qty')::numeric AS qty,
           (e->>'unit_cost')::numeric AS unit_cost
      FROM jsonb_array_elements(p_items) e
     ORDER BY 1
  LOOP
    v_movement_id := record_movement(
      p_company_id, v_row.item_id, p_moved_on, v_row.qty, v_row.unit_cost,
      'opening', NULL, NULL, v_entry_id);
    SELECT value INTO v_value FROM inventory_movements
     WHERE company_id = p_company_id AND id = v_movement_id;
    v_total := v_total + v_value;
  END LOOP;

  IF v_total <> v_expected_total THEN
    RAISE EXCEPTION
      'the opening value that was recorded (%) is not the one that was checked against ''%'' (%)',
      v_total, v_src_name, v_expected_total;
  END IF;

  INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit, memo)
  SELECT p_company_id, v_entry_id, i.inventory_account_id,
         sum(m.value)::numeric(19,4), 0, p_memo
    FROM inventory_movements m
    JOIN items i ON i.company_id = m.company_id AND i.id = m.item_id
   WHERE m.company_id = p_company_id AND m.entry_id = v_entry_id
   GROUP BY i.inventory_account_id
  HAVING sum(m.value) <> 0;

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

  PERFORM post_entry(p_company_id, v_entry_id);

  INSERT INTO audit_log (company_id, user_id, action, entity, entity_id, detail)
  VALUES (p_company_id, p_user_id, 'opening_quantities', 'journal_entry',
          v_entry_id::text,
          jsonb_build_object('value', v_total::text,
                             'source_account_id', p_source_account_id,
                             'items', jsonb_array_length(p_items)));

  RETURN v_entry_id;
END $$;

-- ---------------------------------------------------------------------------
-- 6. item_profitability — derived, never stored
-- ---------------------------------------------------------------------------

-- Per item over a date range: units sold, revenue, cost of sales, margin.
--
-- ONE LENS FOR BOTH SIDES, and it is the one sales_tax_summary() already uses:
-- invoices that are ISSUED and NOT CURRENTLY VOID, on an invoice-date basis.
-- Revenue comes from the invoice lines; cost comes from the SALE MOVEMENTS
-- those exact lines produced (source_kind = 'invoice_line'), never from a
-- re-multiplication of quantity by an average, so the number here is the number
-- the ledger was posted. A voided invoice drops out of both halves together:
-- its lines are excluded by status, and so are its movements, because they are
-- reached THROUGH those lines. Mixing lenses — current-state revenue against
-- date-filtered movements — would let a void dated outside the range subtract
-- cost from revenue it no longer has.
--
-- document_only invoices are excluded: they post nothing and cannot carry
-- stocked lines at all (issue_invoice refuses that outright), so they have no
-- cost of sales, and including their revenue would overstate every margin on
-- migrated history.
--
-- Every item that sold appears, stocked or not — a service or a drop-shipped
-- part simply has a cost of zero, which is the truth about what inventory knows
-- of it, and saying so is more useful than leaving the line out.
CREATE FUNCTION item_profitability(p_company_id uuid, p_from date, p_to date)
RETURNS TABLE (
  item_id bigint,
  sku text,
  name text,
  kind text,
  units_sold numeric(19,4),
  revenue numeric(19,4),
  cogs numeric(19,4),
  margin numeric(19,4)
)
LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public AS $$
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  RETURN QUERY
  WITH sold AS (
    SELECT l.id AS line_id, l.item_id, l.quantity, l.amount
      FROM invoice_lines l
      JOIN invoices inv ON inv.company_id = l.company_id AND inv.id = l.invoice_id
     WHERE l.company_id = p_company_id
       AND l.item_id IS NOT NULL
       AND inv.status = 'open'
       AND inv.document_only = false
       AND (p_from IS NULL OR inv.invoice_date >= p_from)
       AND (p_to IS NULL OR inv.invoice_date <= p_to)
  ),
  rev AS (
    SELECT s.item_id,
           sum(s.quantity)::numeric(19,4) AS units,
           sum(s.amount)::numeric(19,4) AS revenue
      FROM sold s GROUP BY s.item_id
  ),
  cost AS (
    SELECT s.item_id, (-sum(m.value))::numeric(19,4) AS cogs
      FROM sold s
      JOIN inventory_movements m
        ON m.company_id = p_company_id
       AND m.source_kind = 'invoice_line'
       AND m.source_id = s.line_id
       -- Never the compensating rows: reverse_movement() copies source_kind and
       -- source_id, so an un-voided line's own movement is the one with no
       -- original behind it.
       AND m.reverses_movement_id IS NULL
     GROUP BY s.item_id
  )
  SELECT i.id, i.sku, i.name, i.kind,
         rev.units, rev.revenue,
         coalesce(c.cogs, 0)::numeric(19,4),
         (rev.revenue - coalesce(c.cogs, 0))::numeric(19,4)
    FROM rev
    JOIN items i ON i.company_id = p_company_id AND i.id = rev.item_id
    LEFT JOIN cost c ON c.item_id = rev.item_id
   ORDER BY i.name;
END $$;
