-- 0025: opening quantities must not double-count a migrated company's stock.
-- Spec (binding): docs/superpowers/specs/2026-08-03-inventory-design.md,
-- "Opening quantities for a company that imported full history"; CLAUDE.md §7
-- ("do not post an opening balance... doing both double-counts everything").
-- Plan: docs/superpowers/plans/2026-08-03-inventory.md Task 5 (review fixes).
-- Forward-only. 0023/0024 are applied to the dev database and are NOT edited.
--
-- ---------------------------------------------------------------------------
-- WHAT THE REVIEW FOUND. Task 5's opening-quantities screen asks the user
-- whether these are fresh books or a migration, and explains the difference at
-- length — but that answer was never sent anywhere. record_opening_quantities()
-- had no notion of it, so the entire fresh-vs-migrated distinction was prose.
-- A migrated company that picked "fresh books" and Opening Balance Equity was
-- accepted by every layer and double-counted its inventory. The user was only
-- caught if they happened to reuse the OLD, history-funded account — i.e. only
-- when they had already got it right.
--
-- A rule that lives in the UI is not a rule (CLAUDE.md §12), so the guard goes
-- here, where the two halves of the fact are already written together.
--
-- WHY DROP AND CREATE rather than CREATE OR REPLACE. Adding a defaulted
-- parameter through CREATE OR REPLACE does not replace anything — it creates a
-- SECOND overload, and every existing 6-argument call keeps resolving to the
-- OLD, unguarded function by exact-arity match. The unguarded door would stay
-- wide open while looking closed. Dropping the 6-argument signature first is
-- what makes this a fix rather than a decoration.
--
-- BASE TEXT: 0024's record_opening_quantities(), reproduced verbatim (extracted
-- from that file, not retyped). 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, the worth-nothing refusal,
-- the account lookup, the not-any-item's-inventory-account refusal, the
-- equity-or-asset type refusal, 0024's [F1] bank/A-R/reconciled refusals and
-- [F2] as-at-the-date balance check, the empty-or-backed target check, pass two,
-- post_entry(), and the audit row.
--
-- ADDED, and only this:
--   1. a defaulted p_acknowledged parameter;
--   2. one refusal, for an EQUITY source on a company whose ledger carries
--      imported entries, naming the risk and the alternative.
-- ---------------------------------------------------------------------------

-- The old 6-argument signature goes, so nothing can resolve past the guard.
DROP FUNCTION IF EXISTS record_opening_quantities(uuid, date, bigint, jsonb, text, uuid);

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,
  -- 0025: the caller has been TOLD this company imported history and says it
  -- means to create the value anyway. Defaulted so every existing call site
  -- keeps its meaning: silence is "I have not been warned", never "go ahead".
  p_acknowledged boolean DEFAULT false
) 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;

  -- 0025 [F-MW1]: THE DOUBLE-COUNT GUARD.
  --
  -- CLAUDE.md §7 is unambiguous: a company that imports full transaction
  -- history must NOT also post an opening balance, because "history either
  -- replaces it or does not happen. Doing both double-counts everything."
  -- Opening QUANTITIES are that same trap wearing a different hat — the
  -- imported history already recorded every purchase of that stock, so its
  -- value is already in the books.
  --
  -- 0024 closed the door on draining a bank account or a reconciled one, and
  -- 0021/0022 refuse an item ADOPTING an account that holds unbacked value. But
  -- a migrated company that points its items at a BRAND NEW inventory account
  -- and takes the opening value out of Opening Balance Equity walks past every
  -- one of those: the new account is empty (0021 passes), equity is a legal
  -- source (0023 passes), it is not a bank account (0024 passes), and the tie
  -- reads a serene 0.0000 throughout — because the tie only ever asserts that
  -- stock and ledger agree, and here they agree on a number that is counted
  -- twice. Total assets are simply overstated by the whole opening value,
  -- permanently, and the entry carries movements so reverse_entry() correctly
  -- refuses to undo it.
  --
  -- journal_entries.source is written by exactly one thing — post_batch()
  -- (0003), from import_batches.source ('quickbooks_journal', 'universal:%',
  -- 'adp_gl') — so it is a precise signal for "this company brought its history
  -- in from somewhere else", with no false positives from ordinary bookkeeping.
  --
  -- This is a WARNING, not a wall: a company can legitimately have imported one
  -- year of payroll and still be starting its stock from nothing, and only the
  -- user knows that. So it refuses ONCE, in English that names the risk and the
  -- alternative, and proceeds if the caller comes back having said it
  -- understands.
  IF v_src_type = 'equity' AND NOT coalesce(p_acknowledged, false) AND EXISTS (
    SELECT 1 FROM journal_entries e
     WHERE e.company_id = p_company_id
       AND e.status = 'posted'
       AND e.source IS NOT NULL
  ) THEN
    RAISE EXCEPTION
      'This company brought in transaction history, which already recorded what you paid for stock. Taking opening quantities out of ''%'' as well would count the same inventory twice — use the account your imported history put the value in instead, so the value moves across rather than being created a second time.',
      v_src_name;
  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 $$;
