-- 0008: AR milestone closeout (owner review, two decisions) — forward-only;
-- 0005/0006/0007 are already applied everywhere (including the dev DB, which
-- has real customer data), so every change here is CREATE OR REPLACE / a new
-- function, never an edit to an earlier migration.
--
-- DECISION 1 — locks must freeze document_only invoices too. Before this
-- migration, issue_invoice()'s document_only branch never called post_entry()
-- (there's nothing to post), so post_entry's own period-lock check never ran
-- for it — a migrated-history invoice could be backdated straight into a
-- locked period with nothing to stop it. Same story for void_invoice()'s
-- document_only branch: it never calls reverse_entry(), so neither
-- reverse_entry_internal's lock check (via the reversing entry's post_entry
-- call) nor its "reversal date cannot precede the entry it reverses" guard
-- ever ran. CLAUDE.md §3.3 ("nothing posts into a locked period") and the
-- reversal-ordering invariant are about the ACCOUNTING PERIOD a document
-- belongs to, not merely about ledger postings — a document_only invoice
-- still occupies a place in that company's history and its aging reports,
-- so both guards are re-implemented here for that branch, using the SAME
-- RAISE text as their ledger-side counterparts (post_entry's lock message /
-- reverse_entry_internal's ordering message) so errors.ts's EXISTING
-- mappings fire unchanged — no new error-mapping code needed for this half
-- of the migration.
--
-- Non-document_only invoices are completely unaffected: post_entry() and
-- reverse_entry() already owned both checks for them, and that code path is
-- untouched below.
--
-- DECISION 2 — settable next invoice number. set_next_invoice_no() lets an
-- owner fast-forward (never rewind) a company's invoice_counters row — e.g.
-- to continue QuickBooks' own numbering after a history migration. Refuses
-- (loud English via errors.ts) if the requested number would not exceed the
-- highest invoice_no this company has ever actually issued — issuing a
-- duplicate invoice_no is already impossible (UNIQUE (company_id,
-- invoice_no) in 0005), but this guard turns that into a friendly refusal
-- *before* the wasted round trip through issue_invoice() to discover it.
-- SECURITY DEFINER (writes invoice_counters, which 0005 grants app SELECT
-- only on — mirrors every other AR posting function's pattern of "the app
-- role can call the function, but can't touch the row by hand"). EXECUTE is
-- left at its default (granted to PUBLIC, never revoked) — same convention
-- as issue_invoice/void_invoice/receive_payment/etc. in 0005, none of which
-- revoke EXECUTE either; only the truly-internal ensure_ar_account() does.

CREATE OR REPLACE FUNCTION issue_invoice(p_company_id uuid, p_invoice_id bigint, p_user_id uuid)
RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_invoice invoices%ROWTYPE;
  v_line_count int;
  v_total numeric(19,4);
  v_ar_account bigint;
  v_no bigint;
  v_entry_id bigint;
  v_lock date;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match invoice company';
  END IF;

  SELECT * INTO v_invoice FROM invoices
   WHERE company_id = p_company_id AND id = p_invoice_id
   FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'invoice % not found', p_invoice_id;
  END IF;
  IF v_invoice.status <> 'draft' THEN
    RAISE EXCEPTION 'invoice % is already %', p_invoice_id, v_invoice.status;
  END IF;

  SELECT count(*), coalesce(sum(amount), 0) INTO v_line_count, v_total
    FROM invoice_lines WHERE company_id = p_company_id AND invoice_id = p_invoice_id;
  IF v_line_count < 1 THEN
    RAISE EXCEPTION 'invoice % has no lines', p_invoice_id;
  END IF;

  -- DECISION 1: document_only never reaches post_entry() below, so its own
  -- lock check has to run here instead — same text post_entry raises, so
  -- errors.ts maps it exactly the same way for both kinds of invoice.
  IF v_invoice.document_only THEN
    v_lock := effective_locked_through(p_company_id);
    IF v_lock IS NOT NULL AND v_invoice.invoice_date <= v_lock THEN
      RAISE EXCEPTION 'cannot post into locked period (locked through %)', v_lock;
    END IF;
  END IF;

  INSERT INTO invoice_counters (company_id) VALUES (p_company_id)
    ON CONFLICT (company_id) DO NOTHING;
  SELECT next_invoice_no INTO v_no FROM invoice_counters
   WHERE company_id = p_company_id FOR UPDATE;
  UPDATE invoice_counters SET next_invoice_no = v_no + 1
   WHERE company_id = p_company_id;

  IF v_invoice.document_only THEN
    UPDATE invoices SET status = 'open', invoice_no = v_no
     WHERE company_id = p_company_id AND id = p_invoice_id;
  ELSE
    v_ar_account := ensure_ar_account(p_company_id);

    INSERT INTO journal_entries (company_id, entry_date, memo, created_by)
    VALUES (p_company_id, v_invoice.invoice_date, 'Invoice #' || v_no, p_user_id)
    RETURNING id INTO v_entry_id;

    INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit)
    VALUES (p_company_id, v_entry_id, v_ar_account, v_total, 0);

    INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit)
    SELECT p_company_id, v_entry_id, income_account_id, 0, sum(amount)
      FROM invoice_lines
     WHERE company_id = p_company_id AND invoice_id = p_invoice_id
     GROUP BY income_account_id;

    PERFORM post_entry(p_company_id, v_entry_id);

    UPDATE invoices SET status = 'open', invoice_no = v_no, posted_entry_id = v_entry_id
     WHERE company_id = p_company_id AND id = p_invoice_id;
  END IF;

  RETURN v_no;
END $$;

-- void_invoice: 0007's version plus DECISION 1's two document_only guards.
-- Everything else (payment-application guard, non-document_only branch) is
-- identical to 0007.
CREATE OR REPLACE FUNCTION void_invoice(
  p_company_id uuid, p_invoice_id bigint, p_void_date date, p_user_id uuid
) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_invoice invoices%ROWTYPE;
  v_app_count int;
  v_rev_id bigint;
  v_lock date;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match invoice company';
  END IF;

  SELECT * INTO v_invoice FROM invoices
   WHERE company_id = p_company_id AND id = p_invoice_id
   FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'invoice % not found', p_invoice_id;
  END IF;
  IF v_invoice.status <> 'open' THEN
    RAISE EXCEPTION 'invoice % is % — only open invoices can be voided',
      p_invoice_id, v_invoice.status;
  END IF;

  SELECT count(*) INTO v_app_count FROM payment_applications
   WHERE company_id = p_company_id AND invoice_id = p_invoice_id;
  IF v_app_count > 0 THEN
    RAISE EXCEPTION
      'invoice % has payments applied — void the payments applied to it first',
      p_invoice_id;
  END IF;

  IF v_invoice.document_only THEN
    -- DECISION 1: document_only never reaches reverse_entry() below, so
    -- neither of its two date guards ever ran for this branch. Both are
    -- re-implemented here with the SAME RAISE text as their ledger-side
    -- counterparts (post_entry's lock message / reverse_entry_internal's
    -- ordering message), so errors.ts's existing mappings fire unchanged.
    v_lock := effective_locked_through(p_company_id);
    IF v_lock IS NOT NULL AND p_void_date <= v_lock THEN
      RAISE EXCEPTION 'cannot post into locked period (locked through %)', v_lock;
    END IF;
    IF p_void_date < v_invoice.invoice_date THEN
      RAISE EXCEPTION 'reversal date cannot precede the entry it reverses';
    END IF;

    UPDATE invoices SET status = 'void', voided_on = p_void_date
     WHERE company_id = p_company_id AND id = p_invoice_id;
  ELSE
    v_rev_id := reverse_entry(p_company_id, v_invoice.posted_entry_id, p_void_date, p_user_id);
    UPDATE invoices SET status = 'void', void_entry_id = v_rev_id, voided_on = p_void_date
     WHERE company_id = p_company_id AND id = p_invoice_id;
  END IF;
END $$;

-- DECISION 2: settable next invoice number — fast-forward only, never
-- rewind. p_next must exceed every invoice_no this company has ever issued
-- (coalesce to 0 when none have — a brand-new company can still be set to
-- start anywhere above 0).
CREATE 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;

  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;

  INSERT INTO invoice_counters (company_id, next_invoice_no) VALUES (p_company, p_next)
    ON CONFLICT (company_id) DO UPDATE SET next_invoice_no = excluded.next_invoice_no;

  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 $$;
