-- 0021: an inventory account must be EMPTY OR ALREADY BACKED before an item
-- may adopt it, plus an idempotence guard on reverse_movement().
-- 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, 0019 and 0020 are applied to the dev database (which
-- holds real books) and are NOT edited; this file only adds.
--
-- ---------------------------------------------------------------------------
-- THE HOLE THIS CLOSES.
--
-- check_inventory_ties() builds its account set from items.inventory_account_id
-- and compares each account's LEDGER balance against the movement-backed value
-- of the items pointing at it. Every guard so far has policed what happens
-- AFTER an account is in that set. Nothing policed the moment it JOINS it — so
-- an account that already carries money nobody recorded stock for can be
-- adopted, and the tie breaks the instant it is:
--
--   (a) create an item on 1330, flip it to non_inventory while it still has no
--       movements (allowed), post an ordinary $700 category line into 1330,
--       flip the item back to inventory/1330  ->  difference 700.0000
--
--   (b) the one that matters, because it is the DOCUMENTED migration path:
--       CLAUDE.md §7 imports full transaction history, so "1300 Inventory"
--       already carries a real balance BEFORE any item exists. Create the first
--       item against it  ->  difference 5000.0000, on a company that did
--       everything right. And it is then unfixable in place, because
--       items_frozen_once_moved welds the pointer shut on the first movement.
--
-- THE RULE. At adoption — an INSERT carrying an inventory_account_id, or an
-- UPDATE that changes one — the account's posted ledger balance must EQUAL the
-- movement-backed value of the items already pointing at it. A fresh company's
-- $0 account passes. An account holding $500 of properly-backed stock passes
-- when a second item adopts it. A funded-but-unbacked account is refused.
--
-- (The item being adopted contributes nothing to either side: on INSERT it has
-- no movements, and on UPDATE items_frozen_once_moved has already refused to
-- move the pointer of an item that has any. So the comparison is exactly the
-- one check_inventory_ties() will make one statement later.)
--
-- THE ENGLISH STEERS TO THE REAL CURE, which is a reclassification, not a
-- workaround. A company that imported full history should point its items at a
-- NEW inventory account and record its opening quantities there; that moves the
-- value across, drains the history-funded account to zero, leaves total
-- inventory value and the ledger's balance unchanged, and ties by construction
-- (the old account is nobody's inventory account, so it is not in the set).
-- Written up in the spec's opening-quantities section for Task 3 to implement
-- as one entry; today it is two legal steps, both already available:
--
--   1. opening quantities per item, via record_adjustment() against Opening
--      Balance Equity            Dr <new inventory>  / Cr <equity>
--   2. one reclassifying journal entry draining the old account
--                                Dr <equity>         / Cr <old account>
--
-- Equity nets to zero, the old account lands on zero, and the new account is
-- backed to the penny.
-- ---------------------------------------------------------------------------

CREATE FUNCTION items_inventory_account_backed() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_ledger numeric(19,4);
  v_backed numeric(19,4);
  v_name text;
BEGIN
  IF NEW.inventory_account_id IS NULL THEN
    RETURN NEW;
  END IF;
  -- Only ADOPTION is policed. An item that keeps the account it already had
  -- changes nothing about who backs it.
  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;

  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, to_char(v_ledger - v_backed, 'FM999999999990.00');
  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, to_char(v_backed - v_ledger, 'FM999999999990.00');
END $$;

-- Runs after items_accounts_typed (alphabetical order among BEFORE triggers),
-- so "that has to be an asset account" is always said first.
CREATE TRIGGER items_inventory_account_backed
  BEFORE INSERT OR UPDATE ON items
  FOR EACH ROW EXECUTE FUNCTION items_inventory_account_backed();

-- ---------------------------------------------------------------------------
-- reverse_movement: a movement can be reversed at most ONCE
-- ---------------------------------------------------------------------------

-- Today this is defended in depth — EXECUTE is revoked, void_bill is the only
-- caller, a bill cannot be voided twice, and 0018's deferred check would refuse
-- the resulting entry at COMMIT. Task 3 adds void_invoice and build reversal as
-- callers, and "defended by the fact that nobody calls it twice" stops being
-- true the moment somebody does. So the guard is structural rather than
-- argued: a column and a unique index, exactly the shape
-- journal_entries.reverses_entry_id already uses, and the same
-- unique_violation-to-English conversion reverse_entry_internal() uses.
ALTER TABLE inventory_movements ADD COLUMN reverses_movement_id bigint;
ALTER TABLE inventory_movements
  ADD CONSTRAINT inventory_movements_reverses_fk
  FOREIGN KEY (company_id, reverses_movement_id)
  REFERENCES inventory_movements (company_id, id);
CREATE UNIQUE INDEX inventory_movements_reverses_unique
  ON inventory_movements (company_id, reverses_movement_id)
  WHERE reverses_movement_id IS NOT NULL;

-- BASE TEXT: db/migrations/0020_inventory_receiving_fixes.sql's
-- reverse_movement(). Every line preserved: the session-company guard, the
-- movement lookup, the LOCK-FIRST item lock, the period-lock check in
-- post_entry's exact wording, the on-hand read and the negative-stock refusal
-- with its English. ADDED: reverses_movement_id is stamped on the compensating
-- row, and the unique index turns a second attempt into a sentence.
CREATE OR REPLACE FUNCTION reverse_movement(
  p_company_id uuid,
  p_movement_id bigint,
  p_entry_id bigint,
  p_moved_on date
) 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. It also serialises two concurrent reversals of the
  -- same movement, so the unique index below is reached rather than raced.
  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;

  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;

  BEGIN
    INSERT INTO inventory_movements
      (company_id, item_id, moved_on, qty, unit_cost, value, reason,
       source_kind, source_id, entry_id, reverses_movement_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, p_movement_id)
    RETURNING id INTO v_id;
  EXCEPTION WHEN unique_violation THEN
    RAISE EXCEPTION 'stock movement % has already been reversed', p_movement_id;
  END;

  RETURN v_id;
END $$;
