-- 0019: receiving stock BY entering the bill.
-- Spec (binding): docs/superpowers/specs/2026-08-03-inventory-design.md,
-- especially "As-built decisions from Task 1" 1-3.
-- Plan: docs/superpowers/plans/2026-08-03-inventory.md Task 2.
-- Forward-only. 0018 is committed and applied everywhere and is NOT edited;
-- everything here is either a new object or a CREATE OR REPLACE of a function
-- whose newest definition is reproduced verbatim below with the new block
-- added (each such replacement says where its base text came from).
--
-- Design in one paragraph. Spec decision 1 says receiving happens BY entering
-- the bill: the receipt date IS the bill date, there is no separate receipt
-- document, and there is no second journal entry. So the whole of "receiving"
-- is one addition to issue_bill(): for every bill line that names a stocked
-- item, record a receipt movement on the entry the bill was already posting.
-- The MONEY side needs no new code at all, because a bill line for a stocked
-- item carries that item's inventory account in its own category_account_id
-- and issue_bill's existing "one journal line per category account" SQL
-- therefore already debits the right account with the right amount. What makes
-- that safe rather than merely conventional is the new
-- bill_lines_item_consistent() trigger below: an inventory account is
-- reachable from a bill line ONLY through the item it belongs to, and an item
-- line may only post to its own item's account. 0018's deferred per-account
-- check then has both halves of the fact in one transaction and asserts they
-- are the same number.

-- ---------------------------------------------------------------------------
-- bill_lines: items, inventory accounts, and purchase order links
-- ---------------------------------------------------------------------------

-- Three separate facts, one trigger, because they are all "this line's item,
-- account and PO line have to describe the same purchase":
--
--   1. An INVENTORY account may only be reached through an item. 0018's
--      deferred check cannot catch this on its own: it is AFTER INSERT ON
--      inventory_movements and builds its account set FROM the movements, so
--      an entry that posts money to an inventory account while moving no stock
--      at all never fires it. A bill coded straight to "1300 Inventory" would
--      break the tie silently and permanently. (bill_lines_category_not_ap()
--      from 0009 is the same shape of guard for the A/P account, and this is
--      deliberately written to look like it.)
--   2. An item line for a stocked item must post to THAT item's inventory
--      account — not another item's. Without this the deferred check still
--      catches it, but with the raw "moves X of stock through inventory
--      account N, but posts Y to it" text; caught here it gets a sentence that
--      names the item.
--   3. A bill line that fulfils a purchase order line must receive the same
--      item, from the same vendor, on a PO that was actually issued. Derived
--      fulfilment (Task 2 sums bill_lines.quantity per po_line_id) is only
--      meaningful if these hold.
CREATE FUNCTION bill_lines_item_consistent() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_item items%ROWTYPE;
  v_acct_name text;
  v_po_line purchase_order_lines%ROWTYPE;
  v_po purchase_orders%ROWTYPE;
  v_bill_vendor bigint;
BEGIN
  IF NEW.item_id IS NOT NULL THEN
    SELECT * INTO v_item FROM items
     WHERE company_id = NEW.company_id AND id = NEW.item_id;
    IF NOT FOUND THEN
      RAISE EXCEPTION 'item % not found', NEW.item_id;
    END IF;
  END IF;

  IF v_item.kind = 'inventory' THEN
    -- (2) an item line posts to its own item's inventory account
    IF NEW.category_account_id IS DISTINCT FROM v_item.inventory_account_id THEN
      RAISE EXCEPTION
        'a line for ''%'' has to go to that item''s own inventory account, so the quantity and the value stay together',
        v_item.name;
    END IF;
  ELSE
    -- (1) no item (or a non-stocked one): the account may not be ANY item's
    -- inventory account. Same account set check_inventory_ties() uses.
    SELECT a.name INTO v_acct_name
      FROM accounts a
     WHERE a.company_id = NEW.company_id
       AND a.id = NEW.category_account_id
       AND EXISTS (SELECT 1 FROM items i
                    WHERE i.company_id = NEW.company_id
                      AND i.inventory_account_id = a.id);
    IF FOUND THEN
      RAISE EXCEPTION
        '''%'' is an inventory account — pick the item you are buying instead, so the quantity and the value move together',
        v_acct_name;
    END IF;
  END IF;

  -- (3) the purchase order line this fulfils.
  --
  -- Deliberately skipped when item_id IS NULL: bill_lines_po_line_requires_item
  -- (0018's own CHECK) is the guard for THAT case, and a BEFORE trigger runs
  -- first, so raising here would make that constraint permanently unreachable
  -- — a guard that can never fire is a guard nobody can test.
  IF NEW.po_line_id IS NOT NULL AND NEW.item_id IS NOT NULL THEN
    SELECT * INTO v_po_line FROM purchase_order_lines
     WHERE company_id = NEW.company_id AND id = NEW.po_line_id;
    IF NOT FOUND THEN
      RAISE EXCEPTION 'purchase order line % not found', NEW.po_line_id;
    END IF;
    IF v_po_line.item_id IS DISTINCT FROM NEW.item_id THEN
      RAISE EXCEPTION
        'this line receives a different item than the purchase order line it is filling';
    END IF;

    SELECT * INTO v_po FROM purchase_orders
     WHERE company_id = NEW.company_id AND id = v_po_line.po_id;
    IF v_po.status = 'draft' THEN
      RAISE EXCEPTION
        'that purchase order has not been sent to the vendor yet — issue it before receiving against it';
    END IF;
    IF v_po.status = 'cancelled' THEN
      RAISE EXCEPTION 'purchase order #% was cancelled', v_po.po_no;
    END IF;

    SELECT vendor_id INTO v_bill_vendor FROM bills
     WHERE company_id = NEW.company_id AND id = NEW.bill_id;
    IF v_bill_vendor IS DISTINCT FROM v_po.vendor_id THEN
      RAISE EXCEPTION
        'this bill and purchase order #% belong to different vendors', v_po.po_no;
    END IF;
  END IF;

  RETURN NEW;
END $$;

CREATE TRIGGER bill_lines_item_consistent
  BEFORE INSERT OR UPDATE ON bill_lines
  FOR EACH ROW EXECUTE FUNCTION bill_lines_item_consistent();

-- ---------------------------------------------------------------------------
-- purchase order lines freeze the moment the PO is sent
-- ---------------------------------------------------------------------------

-- 0018 made an issued purchase order's NUMBER and STATUS forward-only, but
-- said nothing about its lines: an issued PO's quantities and costs could
-- still be rewritten after the vendor had the paper, which makes derived
-- fulfilment ("is this PO fully received?") a comparison against a moving
-- target. Mirrors bill_lines_immutable_when_issued() (0009), including its
-- FOR SHARE lookup.
--
-- The NOT FOUND branch is the ON DELETE CASCADE case: deleting a never-issued
-- purchase order takes its lines with it, and by the time this trigger runs on
-- those child rows the parent is already gone.
CREATE FUNCTION purchase_order_lines_immutable_when_issued() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_company uuid;
  v_po bigint;
  v_status text;
BEGIN
  IF TG_OP = 'INSERT' THEN
    v_company := NEW.company_id; v_po := NEW.po_id;
  ELSE
    v_company := OLD.company_id; v_po := OLD.po_id;
  END IF;

  SELECT status INTO v_status FROM purchase_orders p
   WHERE p.company_id = v_company AND p.id = v_po
   FOR SHARE;
  IF FOUND AND v_status <> 'draft' THEN
    RAISE EXCEPTION
      'this purchase order has already been sent to the vendor — cancel it and create a new one instead of changing it';
  END IF;

  IF TG_OP = 'UPDATE' AND (NEW.po_id <> OLD.po_id OR NEW.company_id <> OLD.company_id) THEN
    SELECT status INTO v_status FROM purchase_orders p
     WHERE p.company_id = NEW.company_id AND p.id = NEW.po_id
     FOR SHARE;
    IF FOUND AND v_status <> 'draft' THEN
      RAISE EXCEPTION
        'this purchase order has already been sent to the vendor — cancel it and create a new one instead of changing it';
    END IF;
  END IF;

  IF TG_OP = 'DELETE' THEN RETURN OLD; END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER purchase_order_lines_immutable_when_issued
  BEFORE INSERT OR UPDATE OR DELETE ON purchase_order_lines
  FOR EACH ROW EXECUTE FUNCTION purchase_order_lines_immutable_when_issued();

-- ...and the header's own fields, for the same reason. Everything below is
-- 0018's purchase_orders_forward_only() text verbatim (it is the only and
-- therefore newest definition) with the one header-freeze block added.
CREATE OR REPLACE FUNCTION purchase_orders_forward_only() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    IF OLD.po_no IS NOT NULL THEN
      RAISE EXCEPTION
        'purchase order % has been issued — cancel it instead of deleting it', OLD.po_no;
    END IF;
    RETURN OLD;
  END IF;

  IF OLD.po_no IS NOT NULL AND NEW.po_no IS DISTINCT FROM OLD.po_no THEN
    RAISE EXCEPTION 'purchase order #% keeps its number', OLD.po_no;
  END IF;

  IF NEW.status IS DISTINCT FROM OLD.status
     AND NOT (
          (OLD.status = 'draft'   AND NEW.status IN ('open', 'cancelled'))
       OR (OLD.status = 'open'    AND NEW.status IN ('partial', 'closed', 'cancelled'))
       OR (OLD.status = 'partial' AND NEW.status IN ('closed', 'cancelled'))
     )
  THEN
    RAISE EXCEPTION
      'a purchase order cannot go from % back to %', OLD.status, NEW.status;
  END IF;

  -- 0019: once the vendor has the paper, only the status may still move.
  -- issue_purchase_order() itself is exempt because it changes status and
  -- po_no while OLD.status is still 'draft'.
  IF OLD.status <> 'draft' AND (
       NEW.vendor_id     IS DISTINCT FROM OLD.vendor_id
    OR NEW.order_date    IS DISTINCT FROM OLD.order_date
    OR NEW.expected_date IS DISTINCT FROM OLD.expected_date
    OR NEW.memo          IS DISTINCT FROM OLD.memo)
  THEN
    RAISE EXCEPTION
      'this purchase order has already been sent to the vendor — cancel it and create a new one instead of changing it';
  END IF;

  RETURN NEW;
END $$;

-- ---------------------------------------------------------------------------
-- issue_bill — the receiving door
-- ---------------------------------------------------------------------------

-- BASE TEXT: db/migrations/0009_ap.sql's issue_bill(). Grepped across
-- 0010-0018: it has never been replaced, so 0009 is the newest definition and
-- the diff below is against it. Preserved unchanged, in order:
--
--   (a) the session-company guard and its exact RAISE text
--   (b) SELECT ... FOR UPDATE on the bill, and 'bill % not found'
--   (c) the draft-only guard, 'bill % is already %'
--   (d) the at-least-one-line guard, 'bill % has no lines'
--   (e) the document_only branch: its OWN period-lock check using the SAME
--       text post_entry() uses (0008 DECISION 1 — document_only never reaches
--       post_entry, so errors.ts's single mapping has to fire for both), and
--       its status/issued_on UPDATE with NO posting
--   (f) the posting branch verbatim: ensure_ap_account(), the entry with the
--       bill's own date and the 'Bill <ref_no>' memo, ONE journal line per
--       category account (this is what now debits an item line's inventory
--       account, unchanged), the single A/P credit for the line total,
--       post_entry(), and only then the bills UPDATE that stamps
--       posted_entry_id
--   (g) the counter ordering is untouched because there is none — bills carry
--       the vendor's own ref_no and issue_bill assigns no number (its RETURNS
--       void is why). Nothing here allocates from any counter table.
--   (h) vendor-credit interactions are untouched: issue_bill has never looked
--       at bill_payments / bill_payment_applications, and pay_bills(),
--       apply_vendor_credit() and check_bill_payment_application() all read
--       bill_lines.amount, which this migration does not change the meaning of
--       — an inventory line's amount is still what the vendor charged, so
--       every A/P balance, aging bucket and over-application guard sees
--       exactly what it saw before.
--
-- ADDED, and only this:
--   1. document_only bills refuse stocked-item lines. A document_only bill
--      posts NOTHING, and 0018 forbids a movement without a posted entry, so
--      "received but never posted" has to be impossible rather than merely
--      unlikely. (These bills exist to carry already-migrated history, whose
--      stock arrived through the opening-quantities path instead.)
--   2. After the journal lines are written and BEFORE post_entry(), a receipt
--      movement per stocked-item line, at the line's own unit price, on the
--      entry the bill is already posting. record_movement() is internal and
--      this function is SECURITY DEFINER, which is exactly the arrangement
--      0018 describes: only something writing BOTH halves may call it.
--   3. An assertion that each movement's value equals its line's amount. The
--      API computes an item line's amount as the exact 4-decimal product of
--      quantity and unit price precisely so this always holds; the assertion
--      is here because CLAUDE.md §12 says the database, not the application,
--      is the guard. Without it a hand-written row would reach 0018's deferred
--      check and surface as an account-level mismatch that names no line.
CREATE OR REPLACE FUNCTION issue_bill(p_company_id uuid, p_bill_id bigint, p_user_id uuid)
RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_bill bills%ROWTYPE;
  v_line_count int;
  v_total numeric(19,4);
  v_ap_account bigint;
  v_entry_id bigint;
  v_lock date;
  v_stocked_lines int;
  v_line record;
  v_movement_id bigint;
  v_value numeric(19,4);
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match bill company';
  END IF;

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

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

  SELECT count(*) INTO v_stocked_lines
    FROM bill_lines l
    JOIN items i ON i.company_id = l.company_id AND i.id = l.item_id
   WHERE l.company_id = p_company_id AND l.bill_id = p_bill_id
     AND i.kind = 'inventory';

  IF v_bill.document_only THEN
    IF v_stocked_lines > 0 THEN
      RAISE EXCEPTION
        'bill % is recorded for tracking only, so it cannot bring stock in — enter it as a normal bill if it changes the quantity on hand',
        p_bill_id;
    END IF;

    v_lock := effective_locked_through(p_company_id);
    IF v_lock IS NOT NULL AND v_bill.bill_date <= v_lock THEN
      RAISE EXCEPTION 'cannot post into locked period (locked through %)', v_lock;
    END IF;

    UPDATE bills SET status = 'open', issued_on = CURRENT_DATE
     WHERE company_id = p_company_id AND id = p_bill_id;
  ELSE
    v_ap_account := ensure_ap_account(p_company_id);

    INSERT INTO journal_entries (company_id, entry_date, memo, created_by)
    VALUES (p_company_id, v_bill.bill_date,
            coalesce('Bill ' || v_bill.ref_no, 'Bill'), p_user_id)
    RETURNING id INTO v_entry_id;

    INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit)
    SELECT p_company_id, v_entry_id, category_account_id, sum(amount), 0
      FROM bill_lines
     WHERE company_id = p_company_id AND bill_id = p_bill_id
     GROUP BY category_account_id;

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

    -- The stock half of the same fact, in the same transaction, on the same
    -- entry, dated the same day (spec decision 1: the receipt date IS the bill
    -- date). record_movement() runs BEFORE post_entry() deliberately — its own
    -- period-lock check uses post_entry's exact wording, so a locked-period
    -- bill is refused with the same sentence whichever guard sees it first.
    FOR v_line IN
      SELECT l.id, l.item_id, l.quantity, l.unit_price, l.amount, i.name AS item_name
        FROM bill_lines l
        JOIN items i ON i.company_id = l.company_id AND i.id = l.item_id
       WHERE l.company_id = p_company_id AND l.bill_id = p_bill_id
         AND i.kind = 'inventory'
       ORDER BY l.id
    LOOP
      v_movement_id := record_movement(
        p_company_id, v_line.item_id, v_bill.bill_date, v_line.quantity,
        v_line.unit_price, 'receipt', 'bill_line', v_line.id, v_entry_id);

      SELECT value INTO v_value FROM inventory_movements
       WHERE company_id = p_company_id AND id = v_movement_id;
      IF v_value <> v_line.amount THEN
        RAISE EXCEPTION
          'the % of ''%'' on this bill is worth %, but the line says % — the quantity, the cost and the amount have to agree',
          format_quantity(v_line.quantity), v_line.item_name, v_value, v_line.amount;
      END IF;
    END LOOP;

    PERFORM post_entry(p_company_id, v_entry_id);

    UPDATE bills SET status = 'open', issued_on = CURRENT_DATE, posted_entry_id = v_entry_id
     WHERE company_id = p_company_id AND id = p_bill_id;
  END IF;
END $$;
