-- 0010: counter-lock ordering fix for set_next_invoice_no() (0008) and
-- set_next_check_no() (0009) — forward-only; both earlier migrations are
-- already applied everywhere (including the dev DB, which has real
-- customer data), so this is CREATE OR REPLACE, never an edit to an earlier file.
--
-- TOCTOU (owner review finding, shared by AR and AP): both functions read
-- max(used) BEFORE locking their own counter row. A concurrent
-- issue_invoice() / assign_check_numbers() call can commit a freshly-issued
-- number in the window between that read and the counter row's own
-- lock+update, letting the setter's "greater than the highest used" check
-- pass against a now-STALE max — the setter can then fast-forward to a
-- value at or below a number that was JUST issued/assigned concurrently.
-- The eventual duplicate is still contained (UNIQUE (company_id,
-- invoice_no) / bill_payments_check_no_unique refuse it), but only by
-- causing an AVOIDABLE failure later — the next invoice issued, or the next
-- check printed, can spuriously collide with a number the setter just
-- fast-forwarded past, in the wrong direction, for no reason a user could
-- ever anticipate or explain.
--
-- Fix: lock the counter row FIRST — INSERT ... ON CONFLICT DO NOTHING, then
-- (a separate) SELECT ... FOR UPDATE — the exact ordering issue_invoice()
-- (0005) and assign_check_numbers() (0009) already use for their own
-- counter reads. THEN read max(used), THEN validate, THEN update. Locking
-- the counter row first means a concurrent issue_invoice()/
-- assign_check_numbers() call (which locks that SAME row before it does
-- anything else) can never interleave with the setter anymore: whichever
-- transaction gets there first fully finishes — issuing/assigning a number,
-- or setting the counter — before the other's max(used) read ever runs, so
-- the setter's validation is always against a truly final max, never a
-- stale one. See tests/invariants/ar.test.ts's "DECISION 2" describe block
-- and tests/invariants/ap.test.ts's "set_next_check_no" describe block for
-- the new concurrency regression tests this migration is verified against.

CREATE OR REPLACE FUNCTION set_next_invoice_no(p_company uuid, p_next bigint, p_user uuid) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_max bigint;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  -- Lock the counter row FIRST (mirrors issue_invoice()'s own ordering —
  -- see this migration's header for why this is what makes the max(used)
  -- read below race-safe against a concurrent issue_invoice() call).
  INSERT INTO invoice_counters (company_id) VALUES (p_company)
    ON CONFLICT (company_id) DO NOTHING;
  PERFORM 1 FROM invoice_counters WHERE company_id = p_company FOR UPDATE;

  SELECT coalesce(max(invoice_no), 0) INTO v_max FROM invoices
   WHERE company_id = p_company;

  IF p_next <= v_max THEN
    RAISE EXCEPTION 'next invoice number must be greater than the highest used (%)', v_max;
  END IF;

  UPDATE invoice_counters SET next_invoice_no = p_next WHERE company_id = p_company;

  INSERT INTO audit_log (company_id, user_id, action, entity, entity_id, detail)
  VALUES (p_company, p_user, 'set_next_invoice_no', 'invoice_counters', p_company::text,
          jsonb_build_object('next_invoice_no', p_next));
END $$;

CREATE OR REPLACE FUNCTION set_next_check_no(
  p_company uuid, p_account bigint, p_next bigint, p_user uuid
) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_max bigint;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  -- Lock the counter row FIRST (mirrors assign_check_numbers()'s own
  -- ordering — see this migration's header for why this is what makes the
  -- max(used) read below race-safe against a concurrent
  -- assign_check_numbers() call on the same account).
  INSERT INTO check_counters (company_id, money_account_id) VALUES (p_company, p_account)
    ON CONFLICT (company_id, money_account_id) DO NOTHING;
  PERFORM 1 FROM check_counters
   WHERE company_id = p_company AND money_account_id = p_account FOR UPDATE;

  SELECT coalesce(max(check_no), 0) INTO v_max FROM bill_payments
   WHERE company_id = p_company AND money_account_id = p_account;

  IF p_next <= v_max THEN
    RAISE EXCEPTION 'next check number must be greater than the highest used (%)', v_max;
  END IF;

  UPDATE check_counters SET next_check_no = p_next
   WHERE company_id = p_company AND money_account_id = p_account;

  INSERT INTO audit_log (company_id, user_id, action, entity, entity_id, detail)
  VALUES (p_company, p_user, 'set_next_check_no', 'check_counters', p_account::text,
          jsonb_build_object('next_check_no', p_next));
END $$;
