-- 0024: what an opening count may take its value OUT of, and as at when.
-- Spec (binding): docs/superpowers/specs/2026-08-03-inventory-design.md,
-- "Opening quantities for a company that imported full history".
-- Plan: docs/superpowers/plans/2026-08-03-inventory.md Task 3 (review fixes).
-- Forward-only. 0023 is applied to the dev database and is NOT edited; the one
-- function replaced below reproduces its text verbatim with the new blocks
-- marked.
--
-- ---------------------------------------------------------------------------
-- TWO HOLES ADVERSARIAL REVIEW FOUND IN record_opening_quantities().
--
-- [F1] IT ACCEPTED ANY ASSET SOURCE, INCLUDING A BANK ACCOUNT. 0023 allowed an
-- asset source deliberately and correctly — a migrated company's opening value
-- comes OUT of the account imported history put it in, which is an asset, and
-- that is exactly why this endpoint exists rather than reusing
-- record_adjustment(). But "an asset" was too wide by a mile:
--
--   POST /api/c/inventory-opening  { sourceAccountId: <Cash> }   -> 201
--
-- and the bank balance fell from 10,000 to 5,000 with "opening count" sitting
-- in the account register. record_adjustment() refuses that shape outright, and
-- 0018's decision 6 states the reason in so many words: "shrinkage posted to
-- Cash silently misstates the bank balance and corrupts the next
-- reconciliation". 0022 then closed the manual-entry route into the same harm.
-- This endpoint had quietly become the only remaining door.
--
-- THE RULE. An asset source may not be a BANK account or a CUSTOMER-BALANCES
-- account, by the subtype the app itself maintains (0016), and may not be an
-- account that has ever been RECONCILED against a bank statement, whatever its
-- subtype says. The second half matters because subtype is NULLable: an
-- untagged asset account is indistinguishable from any other by subtype alone,
-- but the moment it has a reconciliation it is unmistakably a money account and
-- is refused on that evidence instead. Stated plainly rather than overclaimed:
-- an untagged, never-reconciled bank account still passes both, and the
-- backstop for that case is where it has always been — post_entry()'s
-- assert_not_reconciled() refuses to touch a reconciled account inside its
-- window, and the spec's own wording ("the account your imported history
-- already put the value in") is what the English below steers to.
--
-- [F2] THE BALANCE CHECK WAS DATE-BLIND. 0023 refuses an opening count worth
-- more than the source account holds, so that draining it cannot leave a
-- negative asset. It summed EVERY posted entry with no date filter, while the
-- opening entry is dated p_moved_on:
--
--   account funded 2026-06-01, opening count dated 2020-01-01  -> 201
--
-- and that account reads NEGATIVE on every balance sheet from 2020 until
-- mid-2026 — precisely what the rule was invented to prevent. The realistic way
-- in is a migrated company dating its opening count at the start of imported
-- history rather than at cutover, and it is not undoable afterwards: the entry
-- carries movements, so reverse_entry() correctly refuses it.
--
-- THE FIX is one clause — the balance is as at the opening date, like every
-- other balance in this system — plus naming that date in the sentence, so the
-- answer to "but it has $3,000 in it" is on the screen rather than in someone's
-- head.
-- ---------------------------------------------------------------------------

-- BASE TEXT: db/migrations/0023_inventory_selling_builds.sql's
-- record_opening_quantities(), reproduced verbatim. Preserved unchanged and in
-- order: the session-company guard, the required-memo refusal, the array-shape
-- refusal, the duplicate-item refusal, the whole of pass one (item exists, is
-- stocked, positive quantity, non-negative cost, no existing stock history, and
-- v_expected_total accumulated with record_movement()'s own arithmetic), the
-- worth-nothing refusal, the account lookup, the not-any-item's-inventory-
-- account refusal, the equity-or-asset type refusal, the empty-or-backed target
-- check in 0021/0022's exact words and amount formatting, pass two (the single
-- entry, the movements, the expected-vs-recorded assertion, one debit per
-- target account with HAVING, the single source credit), post_entry(), and the
-- audit row.
--
-- ADDED, and only this:
--   1. [F1] v_src_subtype is read alongside the type, and an asset source that
--      is a bank or customer-balances account — or that has any reconciliation
--      history at all — is refused, in English that names the two sources that
--      ARE right.
--   2. [F2] the source-balance sum gains `AND e.entry_date <= p_moved_on`, and
--      its message gains the date and "check the date". The fragment
--      errors.ts matches on ("but these opening quantities come to $") is
--      unchanged, so the existing mapping still fires.
CREATE OR REPLACE 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_subtype text;                 -- 0024 [F1]
  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, subtype INTO v_src_type, v_src_name, v_src_subtype
    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;

  -- 0024 [F1]: ...and an asset source may not be MONEY. Stock you already own
  -- did not come out of the bank (that purchase is history, and history is what
  -- funded the account this is draining) and it certainly did not come out of
  -- what customers owe you. Draining either one silently misstates a balance
  -- somebody reconciles — 0018's decision 6, applied to the one door that has
  -- to accept an asset at all.
  IF v_src_subtype IN ('bank', 'accounts-receivable') THEN
    RAISE EXCEPTION
      '''%'' is %, so the opening value cannot come out of it — use Opening Balance Equity, or the inventory account your imported history already put the value in.',
      v_src_name,
      CASE v_src_subtype WHEN 'bank' THEN 'a bank account'
                         ELSE 'what customers owe you' END;
  END IF;
  -- subtype is NULLable, so it cannot be the only signal. An account with any
  -- reconciliation history is a money account no matter what it is tagged as,
  -- and that is the case where the harm is certain rather than likely.
  IF v_src_type = 'asset' AND EXISTS (
    SELECT 1 FROM account_reconciliations r
     WHERE r.company_id = p_company_id AND r.money_account_id = p_source_account_id
  ) THEN
    RAISE EXCEPTION
      '''%'' has been reconciled against a bank statement, so the opening value cannot come out of it — use Opening Balance Equity, or the inventory account your imported history already put the value in.',
      v_src_name;
  END IF;

  IF v_src_type = 'asset' THEN
    -- 0024 [F2]: AS AT THE OPENING DATE. Every other balance in this system is
    -- as-of a date; this one was not, so an account funded in the future read
    -- as rich enough today while going negative on the date the entry actually
    -- lands — and stayed negative on every balance sheet in between.
    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'
       AND e.entry_date <= p_moved_on;
    IF v_src_balance < v_expected_total THEN
      RAISE EXCEPTION
        '''%'' only holds $% as at %, but these opening quantities come to $% — that would leave it with a negative balance. Check the counts, check the date, or bring the difference in from Opening Balance Equity.',
        v_src_name,
        to_char(v_src_balance, 'FM999999999990.00'),
        p_moved_on,
        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 $$;
