-- 0022: the OTHER half of the tie — no entry may put money into an inventory
-- account without moving the stock that money represents.
-- 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-0021 are applied to the dev database and are NOT edited.
--
-- ---------------------------------------------------------------------------
-- WHY THIS EXISTS, AND WHY IT IS THE LAST ONE OF ITS KIND.
--
-- 0018's check_movement_entry_posted() fires on MOVEMENT insert and asserts
-- that an entry's movements match what it posts to each inventory account. It
-- is a complete guard for entries that move stock — and it is blind, by
-- construction, to entries that DON'T: it is an AFTER INSERT trigger on
-- inventory_movements, so an entry that debits an inventory account while
-- moving nothing never fires anything at all.
--
-- Four doors into that gap have been closed one at a time during this task:
-- a bill line coded straight to an inventory account (0019), an item's kind
-- flipped under a draft bill line (0020), an item adopting an account that
-- already held money (0021)... and then review found a fifth, needing no
-- concurrency and no items at all:
--
--   POST /api/c/entries {kind:"transfer", toAccountId:<inventory account>}
--
-- entries.ts refuses an asset category for money-in and money-out, but a
-- TRANSFER requires both sides to be assets, so an inventory account is a
-- perfectly legal destination: Dr Inventory / Cr Cash, no movement behind it,
-- inventory_out_of_tie 900.0000 while ledger_out_of_balance stays 0.0000.
--
-- Closing that door by name would be the fifth enumeration, and the sixth
-- would be found later. So the CLASS is closed instead. With both halves in
-- place — movements must match the money (0018), and money must match the
-- movements (here) — no path can put unbacked value into an inventory account:
-- not a transfer, not a manual entry, not an import, not anything built later.
-- The application layer stops needing to remember.
--
-- ON journal_entries, NOT journal_lines. The check only means anything for a
-- POSTED entry, and posting is a status transition on journal_entries that
-- touches no journal_lines row: post_entry() does
-- `UPDATE journal_entries SET status = 'posted', ...` (0002). A trigger on
-- journal_lines would therefore fire when the lines were written — for a draft
-- entry, correctly passing — and never fire again when that draft was posted in
-- a LATER transaction, which the ledger fully supports (journal_entries has a
-- draft state, and tests/helpers/db.ts's insertDraftEntry/postEntry pair does
-- exactly that). Hanging the check on journal_entries means the row that
-- records "this is now real" is the row that gets checked at the moment it says
-- so. Lines cannot be added afterwards either — forbid_posted_line_change
-- (0002) already sees to that — so there is no window on the other side.
--
-- DEFERRED, like its 0018 counterpart, because the natural order inside
-- issue_bill()/record_adjustment()/post_build() is: open the entry, write the
-- lines, record the movements, post. All of it has to be finished by COMMIT,
-- and none of it has to be finished before then.
--
-- WHAT THIS MUST STILL ALLOW, all three tested:
--   * the same-account build wash (Task 3): movements net zero on that account
--     and the entry's two lines net zero on it too — 0 = 0 passes;
--   * the migration reclassification DRAIN (Dr equity / Cr the old
--     history-funded account): that account is nobody's inventory_account_id,
--     so it is not in the set and is never looked at;
--   * record_adjustment(): the movement is there and matches.
-- ---------------------------------------------------------------------------

CREATE FUNCTION check_entry_stock_backed() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE v_bad record;
BEGIN
  -- Only a posted entry is part of any balance, and therefore of the tie.
  IF NEW.status <> 'posted' THEN
    RETURN NULL;
  END IF;

  -- Fast exit for the overwhelming majority of companies and entries: no
  -- stocked items at all means no inventory accounts to protect. One indexed
  -- probe, so bulk history imports pay almost nothing for this.
  IF NOT EXISTS (
    SELECT 1 FROM items
     WHERE company_id = NEW.company_id AND inventory_account_id IS NOT NULL
  ) THEN
    RETURN NULL;
  END IF;

  FOR v_bad IN
    WITH inv_accounts AS (
      SELECT DISTINCT i.inventory_account_id AS account_id
        FROM items i
       WHERE i.company_id = NEW.company_id AND i.inventory_account_id IS NOT NULL
    ),
    -- Only accounts this entry actually touches, and only the ones that are
    -- currently some item's inventory account. Everything else is none of this
    -- trigger's business — including the drained history account.
    posted AS (
      SELECT l.account_id, sum(l.debit - l.credit)::numeric(19,4) AS ledger_value
        FROM journal_lines l
       WHERE l.company_id = NEW.company_id AND l.entry_id = NEW.id
         AND l.account_id IN (SELECT account_id FROM inv_accounts)
       GROUP BY l.account_id
    ),
    moved AS (
      SELECT i.inventory_account_id AS account_id,
             sum(m.value)::numeric(19,4) AS stock_value
        FROM inventory_movements m
        JOIN items i ON i.company_id = m.company_id AND i.id = m.item_id
       WHERE m.company_id = NEW.company_id AND m.entry_id = NEW.id
         AND i.inventory_account_id IS NOT NULL
       GROUP BY i.inventory_account_id
    )
    SELECT a.name AS account_name,
           p.ledger_value,
           coalesce(mv.stock_value, 0)::numeric(19,4) AS stock_value
      FROM posted p
      JOIN accounts a ON a.company_id = NEW.company_id AND a.id = p.account_id
      LEFT JOIN moved mv ON mv.account_id = p.account_id
     WHERE p.ledger_value <> coalesce(mv.stock_value, 0)
     ORDER BY a.code
  LOOP
    -- A net of zero on the account with no movements passes above, so anything
    -- reaching here really did move money the shelf knows nothing about.
    RAISE EXCEPTION
      'You can''t move money into or out of ''%'' directly — it is an inventory account. Receive stock on a bill, or record an inventory adjustment, so the quantity and the value move together.',
      v_bad.account_name;
  END LOOP;

  RETURN NULL;
END $$;

CREATE CONSTRAINT TRIGGER entry_stock_backed
  AFTER INSERT OR UPDATE ON journal_entries
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION check_entry_stock_backed();

-- ---------------------------------------------------------------------------
-- L1: a sub-cent shortfall must not print as "$0.00"
-- ---------------------------------------------------------------------------

-- BASE TEXT: 0021's items_inventory_account_backed(), unchanged except that the
-- amount is rendered with enough decimal places to be TRUE. numeric(19,4) can
-- hold a difference smaller than a cent, and 'FM999999999990.00' rendered it as
-- "$0.00" — refusing for a reason the sentence then denied.
CREATE OR REPLACE FUNCTION items_inventory_account_backed() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_ledger numeric(19,4);
  v_backed numeric(19,4);
  v_diff numeric(19,4);
  v_amount text;
  v_name text;
BEGIN
  IF NEW.inventory_account_id IS NULL THEN
    RETURN NEW;
  END IF;
  IF TG_OP = 'UPDATE'
     AND NEW.inventory_account_id IS NOT DISTINCT FROM OLD.inventory_account_id THEN
    RETURN NEW;
  END IF;

  SELECT coalesce(sum(l.debit - l.credit), 0)::numeric(19,4) INTO v_ledger
    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 = NEW.company_id
     AND l.account_id = NEW.inventory_account_id
     AND e.status = 'posted';

  SELECT coalesce(sum(m.value), 0)::numeric(19,4) INTO v_backed
    FROM inventory_movements m
    JOIN items i ON i.company_id = m.company_id AND i.id = m.item_id
   WHERE m.company_id = NEW.company_id
     AND i.inventory_account_id = NEW.inventory_account_id;

  IF v_ledger = v_backed THEN
    RETURN NEW;
  END IF;

  SELECT name INTO v_name FROM accounts
   WHERE company_id = NEW.company_id AND id = NEW.inventory_account_id;

  v_diff := abs(v_ledger - v_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_ledger > v_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_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_name, v_amount;
END $$;
