-- 0018: inventory & manufacturing — items, movements, purchase orders,
-- assemblies (BOM), builds, and average costing.
-- Spec (binding): docs/superpowers/specs/2026-08-03-inventory-design.md.
-- Plan: docs/superpowers/plans/2026-08-03-inventory.md Task 1.
-- Forward-only; every earlier migration is already applied everywhere
-- (including the dev DB, which has real data), so this file is pure DDL plus
-- new tables — no data rewrites, no edits to an earlier file.
--
-- Design in one paragraph. CLAUDE.md §3.4 — NO BALANCES ARE EVER STORED — is
-- the defining constraint of this milestone. There is deliberately no
-- `quantity` and no `value` column on `items`: quantity on hand and inventory
-- value derive from `inventory_movements` at query time, exactly the way every
-- account balance derives from `journal_lines`. What each movement DOES store
-- is what that transaction DID — the unit cost applied at that moment — a
-- document fact of the same kind as an invoice's tax_amount. Two independent
-- derivations of the same number then exist, from different tables by
-- different code paths, and check_inventory_ties() asserts they agree. That
-- tie is this milestone's ledger_out_of_balance().

-- ---------------------------------------------------------------------------
-- Small shared helper: quantities in English error messages
-- ---------------------------------------------------------------------------

-- "12" not "12.0000", "0.0042" not "0.004200" (CLAUDE.md §9: errors in
-- English). The numeric(19,4) cast is load-bearing — it guarantees a decimal
-- point is present, so the rtrim of trailing zeros can never eat the integer
-- part of a scale-0 numeric (100 -> "1").
CREATE FUNCTION format_quantity(p_qty numeric) RETURNS text
LANGUAGE sql IMMUTABLE AS $$
  SELECT CASE WHEN p_qty IS NULL THEN NULL
              ELSE rtrim(rtrim((p_qty::numeric(19,4))::text, '0'), '.') END
$$;

-- ---------------------------------------------------------------------------
-- items
-- ---------------------------------------------------------------------------

-- kind:
--   'inventory'     — stocked; quantity and value tracked through movements
--   'non_inventory' — bought and sold, never stocked (a part drop-shipped, a
--                     one-off purchase); has a cost account, no inventory
--   'service'       — labor; neither stocked nor costed through inventory
CREATE TABLE items (
  company_id uuid NOT NULL REFERENCES companies (id),
  id bigint GENERATED ALWAYS AS IDENTITY,
  sku text,
  name text NOT NULL,
  kind text NOT NULL CHECK (kind IN ('inventory', 'non_inventory', 'service')),
  income_account_id bigint,       -- must be type 'income'   (trigger below)
  cogs_account_id bigint,         -- must be type 'expense'  (trigger below)
  inventory_account_id bigint,    -- must be type 'asset'    (trigger below)
  sales_price numeric(19,4) CHECK (sales_price IS NULL OR sales_price >= 0),
  purchase_cost numeric(19,4) CHECK (purchase_cost IS NULL OR purchase_cost >= 0),
  description text,
  active boolean NOT NULL DEFAULT true,   -- no deletes once used — deactivate
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (id),
  UNIQUE (company_id, id),                -- composite-FK target (§4)
  UNIQUE (company_id, name),
  FOREIGN KEY (company_id, income_account_id)    REFERENCES accounts (company_id, id),
  FOREIGN KEY (company_id, cogs_account_id)      REFERENCES accounts (company_id, id),
  FOREIGN KEY (company_id, inventory_account_id) REFERENCES accounts (company_id, id),
  -- An inventory account is required for, and only for, a stocked item.
  CONSTRAINT items_inventory_account_iff_inventory
    CHECK ((kind = 'inventory') = (inventory_account_id IS NOT NULL)),
  -- Anything that has a cost of sale needs somewhere to put it.
  CONSTRAINT items_cogs_account_required
    CHECK (kind = 'service' OR cogs_account_id IS NOT NULL)
);
-- SKU is optional, but unique when present.
CREATE UNIQUE INDEX items_sku_unique ON items (company_id, sku) WHERE sku IS NOT NULL;
CREATE INDEX items_by_company ON items (company_id);

-- ---------------------------------------------------------------------------
-- inventory_movements — append-only; the source of truth for qty and value
-- ---------------------------------------------------------------------------

CREATE TABLE inventory_movements (
  company_id uuid NOT NULL REFERENCES companies (id),
  id bigint GENERATED ALWAYS AS IDENTITY,
  item_id bigint NOT NULL,
  moved_on date NOT NULL,
  -- signed: + into stock, - out of stock
  qty numeric(19,4) NOT NULL,
  -- the cost applied to THIS movement. Sub-cent unit costs are real (a washer
  -- costs $0.0042), hence six decimals where money is four.
  unit_cost numeric(19,6) NOT NULL CHECK (unit_cost >= 0),
  -- round(qty * unit_cost, 4), HALF-UP, computed in record_movement().
  -- The journal entry uses this same rounded number, so the ledger and the
  -- movement agree exactly by construction.
  value numeric(19,4) NOT NULL,
  reason text NOT NULL CHECK (reason IN
    ('receipt', 'sale', 'build_in', 'build_out', 'adjustment', 'opening')),
  source_kind text,               -- 'bill_line', 'invoice_line', 'build', ...
  source_id bigint,
  -- NOT NULL at the column level, not only via the CHECK below: this
  -- declaration is what someone implementing a document flow actually reads.
  entry_id bigint NOT NULL,       -- the journal entry this movement rode in on
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (id),
  UNIQUE (company_id, id),
  FOREIGN KEY (company_id, item_id)  REFERENCES items (company_id, id),
  FOREIGN KEY (company_id, entry_id) REFERENCES journal_entries (company_id, id),
  CONSTRAINT movements_source_paired CHECK ((source_kind IS NULL) = (source_id IS NULL)),
  -- A movement must move QUANTITY. record_movement() refuses qty = 0 outright,
  -- and the table says the same thing rather than permitting a shape no door
  -- can produce. (An earlier draft allowed qty = 0 with a non-zero value to
  -- leave room for a value-only write-down; that path was never built, and a
  -- constraint that describes an imaginary feature is just a lie in SQL. If a
  -- value-only adjustment is ever wanted, it arrives with its own migration.)
  CONSTRAINT movements_not_inert CHECK (qty <> 0),
  -- Value and quantity point the same way: stock in is value in.
  CONSTRAINT movements_value_follows_qty
    CHECK ((qty > 0 AND value >= 0) OR (qty < 0 AND value <= 0)),
  -- NaN is numeric's trapdoor: Postgres orders it ABOVE every real number and
  -- equal to itself, so a NaN quantity satisfies every ordering CHECK above,
  -- passes the negative-stock guard, and then poisons SUM() forever — with
  -- immutability sealing both repair paths. `x <> 'NaN'` is false for NaN
  -- (NaN = NaN is true), so these three CHECKs are what keep it out of the
  -- table no matter which door the row came through.
  CONSTRAINT movements_finite CHECK (
    qty <> 'NaN'::numeric AND unit_cost <> 'NaN'::numeric AND value <> 'NaN'::numeric),
  -- EVERY movement names the journal entry it rode in on. No exemption, for
  -- any reason, ever: `reason` is a label the caller chooses, so a rule that
  -- branched on it would be no rule at all — one mislabelled call and stock
  -- moves with no ledger counterpart, permanently (movements are immutable).
  -- The case that tempted an exemption — a build whose component and assembly
  -- share one inventory account — instead posts a wash entry, Dr <inventory> /
  -- Cr <inventory> for the build value: two lines, each with exactly one
  -- non-zero side, summing to zero. Structurally legal under §3.1, and it puts
  -- every build in the account register where an auditor can see it.
  CONSTRAINT movements_entry_required CHECK (entry_id IS NOT NULL)
);
CREATE INDEX inventory_movements_by_item
  ON inventory_movements (company_id, item_id, moved_on);
CREATE INDEX inventory_movements_by_entry
  ON inventory_movements (company_id, entry_id);

-- ---------------------------------------------------------------------------
-- purchase orders — NON-POSTING documents (a PO is a promise, not a
-- transaction; nothing about it ever touches the ledger)
-- ---------------------------------------------------------------------------

-- §4: per-company PO numbering via SELECT ... FOR UPDATE; mirrors
-- entry_counters / invoice_counters / check_counters.
CREATE TABLE po_counters (
  company_id uuid PRIMARY KEY REFERENCES companies (id),
  next_po_no bigint NOT NULL DEFAULT 1
);

CREATE TABLE purchase_orders (
  company_id uuid NOT NULL REFERENCES companies (id),
  id bigint GENERATED ALWAYS AS IDENTITY,
  vendor_id bigint NOT NULL,
  po_no bigint,                    -- assigned at issue; NULL while draft
  order_date date NOT NULL,
  expected_date date,
  -- 'draft' / 'open' / 'cancelled' are the STORED lifecycle. 'partial' and
  -- 'closed' are receipt progress, which Task 2 DERIVES by summing the bill
  -- lines that fulfil each PO line — never stored stale (§3.4 in spirit:
  -- nothing computable is cached). They live in the CHECK because the spec
  -- names all five and a future explicit close is a legitimate stored state.
  status text NOT NULL DEFAULT 'draft'
    CHECK (status IN ('draft', 'open', 'partial', 'closed', 'cancelled')),
  memo text,
  created_by uuid NOT NULL REFERENCES users (id),
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (id),
  UNIQUE (company_id, id),
  UNIQUE (company_id, po_no),      -- multiple NULLs (drafts) coexist fine
  FOREIGN KEY (company_id, vendor_id) REFERENCES vendors (company_id, id),
  -- Only an ISSUED purchase order carries a number. A draft has not been
  -- given one yet, and a draft abandoned before it was ever sent is cancelled
  -- without ever having had one — burning a number on a PO no vendor saw
  -- would leave a permanent gap nobody could explain.
  CHECK (status IN ('draft', 'cancelled') OR po_no IS NOT NULL)
);
CREATE INDEX purchase_orders_by_vendor ON purchase_orders (company_id, vendor_id);
CREATE INDEX purchase_orders_by_status ON purchase_orders (company_id, status);

CREATE TABLE purchase_order_lines (
  company_id uuid NOT NULL REFERENCES companies (id),
  id bigint GENERATED ALWAYS AS IDENTITY,
  po_id bigint NOT NULL,
  item_id bigint,                  -- NULL: a plain description line
  description text,
  qty_ordered numeric(19,4) NOT NULL CHECK (qty_ordered > 0),
  unit_cost numeric(19,6) NOT NULL CHECK (unit_cost >= 0),
  -- NOTE: there is deliberately NO qty_received column. Received quantity is
  -- derived by summing the bill lines that point at this PO line (§3.4).
  PRIMARY KEY (id),
  UNIQUE (company_id, id),         -- composite-FK target for bill_lines
  -- A PO line has no existence apart from its purchase order, so deleting a
  -- (draft, never-issued) PO takes its lines with it. Without the cascade the
  -- user meets raw Postgres foreign-key text, which §9 does not permit.
  FOREIGN KEY (company_id, po_id)   REFERENCES purchase_orders (company_id, id)
    ON DELETE CASCADE,
  FOREIGN KEY (company_id, item_id) REFERENCES items (company_id, id),
  CHECK (item_id IS NOT NULL OR description IS NOT NULL)
);
CREATE INDEX purchase_order_lines_by_po ON purchase_order_lines (company_id, po_id);

-- ---------------------------------------------------------------------------
-- assemblies (bill of materials) and builds
-- ---------------------------------------------------------------------------

CREATE TABLE assemblies (
  company_id uuid NOT NULL REFERENCES companies (id),
  id bigint GENERATED ALWAYS AS IDENTITY,
  assembly_item_id bigint NOT NULL,
  component_item_id bigint NOT NULL,
  qty_per numeric(19,4) NOT NULL CHECK (qty_per > 0),
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (id),
  UNIQUE (company_id, id),
  UNIQUE (company_id, assembly_item_id, component_item_id),
  FOREIGN KEY (company_id, assembly_item_id)  REFERENCES items (company_id, id),
  FOREIGN KEY (company_id, component_item_id) REFERENCES items (company_id, id),
  CONSTRAINT assemblies_not_self CHECK (assembly_item_id <> component_item_id)
);
CREATE INDEX assemblies_by_assembly ON assemblies (company_id, assembly_item_id);
CREATE INDEX assemblies_by_component ON assemblies (company_id, component_item_id);

CREATE TABLE build_counters (
  company_id uuid PRIMARY KEY REFERENCES companies (id),
  next_build_no bigint NOT NULL DEFAULT 1
);

CREATE TABLE builds (
  company_id uuid NOT NULL REFERENCES companies (id),
  id bigint GENERATED ALWAYS AS IDENTITY,
  build_no bigint NOT NULL,
  assembly_item_id bigint NOT NULL,
  qty numeric(19,4) NOT NULL CHECK (qty > 0),
  built_on date NOT NULL,
  status text NOT NULL DEFAULT 'posted' CHECK (status IN ('posted', 'reversed')),
  -- Every build posts an entry, including one whose component and assembly
  -- share an inventory account (that one posts a wash, Dr <inventory> /
  -- Cr <inventory>). A build always moves stock, and stock never moves without
  -- a journal entry — so this is NOT NULL, and Task 3 cannot accidentally
  -- reintroduce the entry-less build this schema used to allow.
  entry_id bigint NOT NULL,
  memo text,
  created_by uuid NOT NULL REFERENCES users (id),
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (id),
  UNIQUE (company_id, id),
  UNIQUE (company_id, build_no),
  FOREIGN KEY (company_id, assembly_item_id) REFERENCES items (company_id, id),
  FOREIGN KEY (company_id, entry_id) REFERENCES journal_entries (company_id, id)
);
CREATE INDEX builds_by_item ON builds (company_id, assembly_item_id);

-- ---------------------------------------------------------------------------
-- Column additions on existing document lines (pure DDL; all nullable, so
-- every existing row is untouched and every existing code path is unaffected)
-- ---------------------------------------------------------------------------

-- bill_lines: an inventory receipt is a bill line that names an item.
-- DEVIATION FROM THE SPEC's letter, deliberately: the spec asked for a new
-- `qty numeric(19,4)` column here, but bill_lines ALREADY has
-- `quantity numeric(12,4) NOT NULL DEFAULT 1 CHECK (quantity > 0)` (0009) —
-- the exact column invoice_lines has, which the spec itself says to reuse on
-- the AR side. Two quantity columns on one table is precisely the sort of
-- thing that eventually posts the wrong number, so the received quantity is
-- bill_lines.quantity, and no second column exists.
ALTER TABLE bill_lines ADD COLUMN item_id bigint;
ALTER TABLE bill_lines ADD COLUMN po_line_id bigint;
-- Widened from numeric(12,4) so a purchase order can never order a quantity
-- the bill that receives it is unable to hold: purchase_order_lines.qty_ordered
-- and inventory_movements.qty are both numeric(19,4). Widening a numeric's
-- precision keeps every existing value bit-for-bit and needs no table rewrite.
ALTER TABLE bill_lines ALTER COLUMN quantity TYPE numeric(19,4);
ALTER TABLE bill_lines
  ADD CONSTRAINT bill_lines_item_fk
  FOREIGN KEY (company_id, item_id) REFERENCES items (company_id, id);
ALTER TABLE bill_lines
  ADD CONSTRAINT bill_lines_po_line_fk
  FOREIGN KEY (company_id, po_line_id) REFERENCES purchase_order_lines (company_id, id);
-- A PO line can only be fulfilled by a line that says what it is fulfilling
-- it WITH: no item, no receipt.
ALTER TABLE bill_lines
  ADD CONSTRAINT bill_lines_po_line_requires_item
  CHECK (po_line_id IS NULL OR item_id IS NOT NULL);

-- invoice_lines: selling an item. quantity already exists (0005) and is reused.
ALTER TABLE invoice_lines ADD COLUMN item_id bigint;
ALTER TABLE invoice_lines
  ADD CONSTRAINT invoice_lines_item_fk
  FOREIGN KEY (company_id, item_id) REFERENCES items (company_id, id);

-- ---------------------------------------------------------------------------
-- Armor: RLS (ENABLE + FORCE, keyed to current_company_id()) and TRUNCATE
-- ---------------------------------------------------------------------------

DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY[
    'items', 'inventory_movements', 'po_counters', 'purchase_orders',
    'purchase_order_lines', 'assemblies', 'build_counters', 'builds'
  ] LOOP
    EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', t);
    EXECUTE format('ALTER TABLE %I FORCE ROW LEVEL SECURITY', t);
    EXECUTE format(
      'CREATE POLICY %I_isolation ON %I USING (company_id = current_company_id())',
      t, t);
    EXECUTE format(
      'CREATE TRIGGER %I_no_truncate BEFORE TRUNCATE ON %I'
      || ' FOR EACH STATEMENT EXECUTE FUNCTION forbid_truncate()', t, t);
  END LOOP;
END $$;

-- ---------------------------------------------------------------------------
-- Armor: grants
-- ---------------------------------------------------------------------------

GRANT SELECT, INSERT, DELETE ON items TO app;
GRANT UPDATE (sku, name, kind, income_account_id, cogs_account_id,
              inventory_account_id, sales_price, purchase_cost, description,
              active) ON items TO app;

-- inventory_movements: NO INSERT. record_movement() is the only way a row
-- enters this table — it is the one place that takes the item lock, computes
-- the average, refuses negative stock, and rounds the value. UPDATE and
-- DELETE ARE granted so that an app attempt hits the immutability trigger's
-- English refusal rather than a bare permission error (AR's 0006 FIX 3,
-- baked in from the start the way 0009 does for bill_payments).
GRANT SELECT, UPDATE, DELETE ON inventory_movements TO app;

-- Counter rows are written only inside SECURITY DEFINER functions.
GRANT SELECT ON po_counters, build_counters TO app;

GRANT SELECT, INSERT, DELETE ON purchase_orders TO app;
GRANT UPDATE (vendor_id, order_date, expected_date, status, memo)
  ON purchase_orders TO app;
GRANT SELECT, INSERT, UPDATE, DELETE ON purchase_order_lines TO app;

GRANT SELECT, INSERT, UPDATE, DELETE ON assemblies TO app;

-- builds are created by Task 3's posting function alongside their movements
-- and entry, so there is no app INSERT. UPDATE and DELETE are granted, and the
-- builds_immutable trigger below — not the privilege system — is what decides
-- what may actually change: everything is frozen except the one posted ->
-- reversed transition Task 3 needs. A privilege alone could not express that.
GRANT SELECT, UPDATE, DELETE ON builds TO app;

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app;

-- ---------------------------------------------------------------------------
-- Armor: item account typing
-- ---------------------------------------------------------------------------

-- A composite FK can only say "an account of this company", never "the right
-- KIND of account" — so the kind is a trigger, exactly like
-- invoice_lines_income_account_only (0005) and
-- company_settings_sales_tax_account_is_liability (0015).
CREATE FUNCTION items_accounts_typed() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE v_type text;
BEGIN
  IF NEW.inventory_account_id IS NOT NULL THEN
    SELECT type INTO v_type FROM accounts
     WHERE company_id = NEW.company_id AND id = NEW.inventory_account_id;
    IF v_type IS NULL THEN
      RAISE EXCEPTION 'account % not found', NEW.inventory_account_id;
    END IF;
    IF v_type <> 'asset' THEN
      RAISE EXCEPTION
        'the inventory account for ''%'' must be an asset account (account % is %)',
        NEW.name, NEW.inventory_account_id, v_type;
    END IF;
  END IF;

  IF NEW.cogs_account_id IS NOT NULL THEN
    SELECT type INTO v_type FROM accounts
     WHERE company_id = NEW.company_id AND id = NEW.cogs_account_id;
    IF v_type IS NULL THEN
      RAISE EXCEPTION 'account % not found', NEW.cogs_account_id;
    END IF;
    IF v_type <> 'expense' THEN
      RAISE EXCEPTION
        'the cost account for ''%'' must be an expense account (account % is %)',
        NEW.name, NEW.cogs_account_id, v_type;
    END IF;
  END IF;

  IF NEW.income_account_id IS NOT NULL THEN
    SELECT type INTO v_type FROM accounts
     WHERE company_id = NEW.company_id AND id = NEW.income_account_id;
    IF v_type IS NULL THEN
      RAISE EXCEPTION 'account % not found', NEW.income_account_id;
    END IF;
    IF v_type <> 'income' THEN
      RAISE EXCEPTION
        'the income account for ''%'' must be an income account (account % is %)',
        NEW.name, NEW.income_account_id, v_type;
    END IF;
  END IF;

  RETURN NEW;
END $$;

CREATE TRIGGER items_accounts_typed
  BEFORE INSERT OR UPDATE ON items
  FOR EACH ROW EXECUTE FUNCTION items_accounts_typed();

-- Once an item has moved, its KIND and its INVENTORY ACCOUNT are history.
-- This is not tidiness — it is what makes check_inventory_ties() meaningful.
-- The tie sums the ledger balance of every account that is currently some
-- item's inventory account; re-pointing an item after its movements have
-- already posted to the old account would silently break the tie forever,
-- and movements can never be edited to repair it.
CREATE FUNCTION items_frozen_once_moved() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.kind IS NOT DISTINCT FROM OLD.kind
     AND NEW.inventory_account_id IS NOT DISTINCT FROM OLD.inventory_account_id THEN
    RETURN NEW;
  END IF;
  IF EXISTS (SELECT 1 FROM inventory_movements
              WHERE company_id = OLD.company_id AND item_id = OLD.id) THEN
    RAISE EXCEPTION
      '''%'' already has stock history — its item type and inventory account cannot be changed. Create a new item instead.',
      OLD.name;
  END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER items_frozen_once_moved
  BEFORE UPDATE ON items
  FOR EACH ROW EXECUTE FUNCTION items_frozen_once_moved();

-- Deleting an item with history is refused in English rather than as a raw
-- foreign-key violation (§9: errors in English).
CREATE FUNCTION items_deletable_only_when_unused() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF EXISTS (SELECT 1 FROM inventory_movements
              WHERE company_id = OLD.company_id AND item_id = OLD.id) THEN
    RAISE EXCEPTION
      '''%'' has stock history and cannot be deleted — mark it inactive instead.',
      OLD.name;
  END IF;
  RETURN OLD;
END $$;

CREATE TRIGGER items_deletable_only_when_unused
  BEFORE DELETE ON items
  FOR EACH ROW EXECUTE FUNCTION items_deletable_only_when_unused();

-- ---------------------------------------------------------------------------
-- Armor: movements are append-only, forever
-- ---------------------------------------------------------------------------

-- Mirrors forbid_posted_entry_change / forbid_posted_line_change (0002), but
-- unconditional: there is no draft state for a movement, so there is nothing
-- to except. A row trigger binds everyone, superuser included; TRUNCATE never
-- fires row triggers and is forbidden separately (above).
CREATE FUNCTION forbid_movement_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION
    'stock movements are a permanent record; record an offsetting movement instead of changing one';
END $$;

CREATE TRIGGER movements_immutable
  BEFORE UPDATE OR DELETE ON inventory_movements
  FOR EACH ROW EXECUTE FUNCTION forbid_movement_change();

-- ---------------------------------------------------------------------------
-- THE TIE, MADE STRUCTURAL
-- ---------------------------------------------------------------------------

-- check_inventory_ties() REPORTS whether inventory agrees with the ledger.
-- This trigger is what makes it agree: a commit-time check, per journal entry,
-- that the stock that moved on that entry and the money that entry put into
-- the corresponding inventory accounts are THE SAME NUMBER. Without it the tie
-- is only a convention that every caller has to remember; with it, an entry
-- that posts the wrong amount, posts to the wrong account, or posts nothing at
-- all simply cannot commit. Four things are asserted:
--
--   1. POSTED — the ledger side of the tie counts only posted entries, so a
--      movement hanging off an abandoned draft would break the tie permanently
--      (movements can never be deleted).
--   2. SAME DATE — the two sides are filtered by moved_on and entry_date, so
--      different dates break the tie for every as-of date in between.
--   3. SAME AMOUNT, 4. SAME ACCOUNT — per inventory account touched by the
--      moved items: SUM(movement value) must equal SUM(debit - credit) on that
--      entry. (This is per-account, which subsumes the aggregate: an entry
--      moving two items with different inventory accounts cannot have one
--      account's error cancel the other's.)
--   5. ONE TRANSACTION — every movement on an entry shares one created_at.
--      created_at defaults to now(), which is transaction_timestamp(), so one
--      transaction's rows all match and a LATER transaction's never can. Posted
--      entries are immutable, but nothing otherwise stopped a second
--      transaction from hanging extra movements off one — a stock transfer
--      between two items that nets to zero on the account would satisfy checks
--      3 and 4 while leaving no record of itself anywhere in the ledger.
--
-- DEFERRED, like entry_balanced_on_lines (0002), because the natural posting
-- order inside issue_bill()/issue_invoice()/post_build() is: open a draft
-- entry, record the movement (which is what COMPUTES the cost the entry
-- needs), write the lines from that value, then post. Every one of those must
-- have completed by COMMIT.
--
-- SECURITY DEFINER because a commit-time integrity check must see every row it
-- validates even when the mutating session is a definer function operating
-- outside its own RLS scope (the same reason check_entry_balanced() is). RLS
-- is not what protects this function — it takes no user input, reads only the
-- row's own company, and returns nothing to the caller.
CREATE FUNCTION check_movement_entry_posted() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_entry journal_entries%ROWTYPE;
  v_bad record;
  v_batches int;
BEGIN
  -- entry_id is NOT NULL (movements_entry_required), so there is no
  -- entry-less case to fall through to any more.
  SELECT * INTO v_entry FROM journal_entries
   WHERE company_id = NEW.company_id AND id = NEW.entry_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'entry % not found', NEW.entry_id;
  END IF;
  IF v_entry.status <> 'posted' THEN
    RAISE EXCEPTION
      'stock movement % refers to journal entry %, which was never posted',
      NEW.id, NEW.entry_id;
  END IF;
  IF v_entry.entry_date <> NEW.moved_on THEN
    RAISE EXCEPTION
      'a stock movement and the journal entry it rides on must share a date (movement dated %, entry dated %)',
      NEW.moved_on, v_entry.entry_date;
  END IF;

  SELECT count(DISTINCT created_at) INTO v_batches FROM inventory_movements
   WHERE company_id = NEW.company_id AND entry_id = NEW.entry_id;
  IF v_batches > 1 THEN
    RAISE EXCEPTION
      'journal entry % already has stock movements from an earlier transaction — an entry and the stock it moved are recorded together, once',
      NEW.entry_id;
  END IF;

  FOR v_bad IN
    WITH involved AS (
      SELECT DISTINCT i.inventory_account_id AS account_id
        FROM inventory_movements m
        JOIN items i ON i.company_id = m.company_id AND i.id = m.item_id
       WHERE m.company_id = NEW.company_id AND m.entry_id = NEW.entry_id
         AND i.inventory_account_id IS NOT NULL
    ),
    moved AS (
      SELECT i.inventory_account_id AS account_id,
             sum(m.value)::numeric(19,4) AS stock_value
        FROM inventory_movements m
        JOIN items i ON i.company_id = m.company_id AND i.id = m.item_id
       WHERE m.company_id = NEW.company_id AND m.entry_id = NEW.entry_id
         AND i.inventory_account_id IS NOT NULL
       GROUP BY i.inventory_account_id
    ),
    posted AS (
      SELECT l.account_id, sum(l.debit - l.credit)::numeric(19,4) AS ledger_value
        FROM journal_lines l
       WHERE l.company_id = NEW.company_id AND l.entry_id = NEW.entry_id
         AND l.account_id IN (SELECT account_id FROM involved)
       GROUP BY l.account_id
    )
    SELECT inv.account_id,
           coalesce(mv.stock_value, 0)::numeric(19,4) AS stock_value,
           coalesce(pv.ledger_value, 0)::numeric(19,4) AS ledger_value
      FROM involved inv
      LEFT JOIN moved  mv ON mv.account_id = inv.account_id
      LEFT JOIN posted pv ON pv.account_id = inv.account_id
     WHERE coalesce(mv.stock_value, 0) <> coalesce(pv.ledger_value, 0)
  LOOP
    RAISE EXCEPTION
      'journal entry % moves % of stock through inventory account %, but posts % to it — the books and the shelf have to say the same thing',
      NEW.entry_id, v_bad.stock_value, v_bad.account_id, v_bad.ledger_value;
  END LOOP;

  RETURN NULL;
END $$;

CREATE CONSTRAINT TRIGGER movements_entry_posted
  AFTER INSERT ON inventory_movements
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION check_movement_entry_posted();

-- ---------------------------------------------------------------------------
-- Derivation: quantity on hand and average cost (§3.4 — never a cached column)
-- ---------------------------------------------------------------------------

-- p_as_of NULL means "all of history", matching ledger_out_of_balance() and
-- trial_balance() (0002). Future-dated movements are therefore included by
-- default, exactly as future-dated entries are in the trial balance — the two
-- sides of the tie must be filtered identically or the tie means nothing.
CREATE FUNCTION item_qty_on_hand(
  p_company_id uuid, p_item_id bigint, p_as_of date DEFAULT NULL
) RETURNS numeric(19,4)
LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public AS $$
DECLARE v_qty numeric(19,4);
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;
  SELECT coalesce(sum(qty), 0)::numeric(19,4) INTO v_qty
    FROM inventory_movements
   WHERE company_id = p_company_id AND item_id = p_item_id
     AND (p_as_of IS NULL OR moved_on <= p_as_of);
  RETURN v_qty;
END $$;

-- Weighted average: SUM(value) / SUM(qty), 0 when the quantity is 0. Six
-- decimals, matching inventory_movements.unit_cost — sub-cent costs are real.
-- There is no cached average anywhere; this is the only definition.
CREATE FUNCTION item_average_cost(
  p_company_id uuid, p_item_id bigint, p_as_of date DEFAULT NULL
) RETURNS numeric(19,6)
LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_qty numeric(19,4);
  v_value numeric(19,4);
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;
  SELECT coalesce(sum(qty), 0)::numeric(19,4), coalesce(sum(value), 0)::numeric(19,4)
    INTO v_qty, v_value
    FROM inventory_movements
   WHERE company_id = p_company_id AND item_id = p_item_id
     AND (p_as_of IS NULL OR moved_on <= p_as_of);
  IF v_qty = 0 THEN
    RETURN 0::numeric(19,6);
  END IF;
  RETURN round(v_value / v_qty, 6);
END $$;

-- ---------------------------------------------------------------------------
-- record_movement — the ONLY way a row enters inventory_movements
-- ---------------------------------------------------------------------------

-- The item row is locked FIRST (SELECT ... FOR UPDATE), before anything is
-- read, so two concurrent issues of the same item serialize: the second one
-- reads an average that already includes the first, and cannot drift.
--
-- Inbound movements carry the cost the caller paid. OUTBOUND movements do not
-- accept a cost at all — the running average IS the cost, and letting a caller
-- pass one would be a second source of truth. Negative stock is refused
-- outright: average cost is meaningless below zero, and a business that
-- oversells has a data problem worth stopping at the door.
--
-- Arithmetic is plpgsql NUMERIC throughout — never a float (CLAUDE.md §2/§12).
-- Postgres round(numeric, int) is half-away-from-zero, i.e. HALF-UP in the
-- accounting sense, in both directions (the same fact 0015 relies on for tax).
CREATE FUNCTION record_movement(
  p_company_id uuid,
  p_item_id bigint,
  p_moved_on date,
  p_qty numeric,
  p_unit_cost numeric,             -- required inbound; must be NULL outbound
  p_reason text,
  p_source_kind text DEFAULT NULL,
  p_source_id bigint DEFAULT NULL,
  p_entry_id bigint DEFAULT NULL
) RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_item items%ROWTYPE;
  v_qty numeric(19,4);
  v_on_hand numeric(19,4);
  v_value_total numeric(19,4);
  v_unit_cost numeric(19,6);
  v_value numeric(19,4);
  v_lock date;
  v_id bigint;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  -- LOCK FIRST. Everything below reads state this lock is protecting.
  SELECT * INTO v_item FROM items
   WHERE company_id = p_company_id AND id = p_item_id
   FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'item % not found', p_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;

  -- NaN first, before any comparison can be fooled by it: Postgres sorts NaN
  -- above every real number, so `v_on_hand + NaN < 0` is FALSE and the
  -- negative-stock guard below would wave it through. One NaN row makes every
  -- quantity, average and valuation for that item NaN forever, and movement
  -- immutability then seals both repair paths. The table's movements_finite
  -- CHECK says the same thing at the other door.
  IF p_qty IS NULL OR p_qty = 'NaN'::numeric OR p_qty = 0 THEN
    RAISE EXCEPTION 'a stock movement has to change the quantity by something';
  END IF;
  IF p_unit_cost = 'NaN'::numeric THEN
    RAISE EXCEPTION 'a unit cost has to be a real number';
  END IF;

  -- Round the quantity to the column's own scale ONCE, here, and use the
  -- rounded value for every decision below. Rounding only at the INSERT would
  -- let 1.00005 be tested and costed as 1.00005 but STORED as 1.0001 — a row
  -- whose own qty x unit_cost no longer equals its own value, and an average
  -- that is wrong from that moment on.
  v_qty := round(p_qty, 4);
  IF v_qty = 0 THEN
    RAISE EXCEPTION 'a stock movement has to change the quantity by something';
  END IF;

  IF v_qty > 0 THEN
    IF p_unit_cost IS NULL THEN
      RAISE EXCEPTION 'receiving ''%'' needs a unit cost', v_item.name;
    END IF;
    IF p_unit_cost < 0 THEN
      RAISE EXCEPTION 'a unit cost cannot be negative';
    END IF;
    v_unit_cost := round(p_unit_cost, 6);
  ELSE
    IF p_unit_cost IS NOT NULL THEN
      RAISE EXCEPTION
        'the cost of stock going out is set by the running average, not by the caller';
    END IF;
  END IF;

  -- §3.3: nothing goes into a locked period — including stock. post_entry()
  -- enforces this too, and every movement now rides on an entry, so this is
  -- not the only guard. It is kept because it is the EARLIER and better one:
  -- record_movement runs before the entry is posted, so the caller is stopped
  -- before it has computed costs and written lines, and the message names the
  -- lock rather than surfacing from somewhere deeper. Same English as
  -- post_entry(), so errors.ts maps both identically.
  v_lock := effective_locked_through(p_company_id);
  IF v_lock IS NOT NULL AND p_moved_on <= v_lock THEN
    RAISE EXCEPTION 'cannot post into locked period (locked through %)', v_lock;
  END IF;

  SELECT coalesce(sum(qty), 0)::numeric(19,4),
         coalesce(sum(value), 0)::numeric(19,4)
    INTO v_on_hand, v_value_total
    FROM inventory_movements
   WHERE company_id = p_company_id AND item_id = p_item_id;

  IF v_qty > 0 THEN
    v_value := round(v_qty * v_unit_cost, 4);
  ELSE
    IF v_on_hand + v_qty < 0 THEN
      RAISE EXCEPTION
        'You only have % of % on hand — this would use %.',
        format_quantity(v_on_hand), v_item.name, format_quantity(-v_qty);
    END IF;
    IF v_on_hand = 0 THEN
      v_unit_cost := 0::numeric(19,6);
      v_value := 0::numeric(19,4);
    ELSIF v_on_hand + v_qty = 0 THEN
      -- Emptying the shelf takes the WHOLE remaining value with it. A rounded
      -- per-unit cost would otherwise strand a fraction of a cent of value on
      -- an item with zero quantity — money sitting in the Inventory account
      -- with nothing left to attach it to, and no average cost that could ever
      -- carry it back out.
      v_value := -v_value_total;
      v_unit_cost := round(v_value / v_qty, 6);
    ELSE
      v_unit_cost := round(v_value_total / v_on_hand, 6);
      v_value := round(v_qty * v_unit_cost, 4);
    END IF;
  END IF;

  INSERT INTO inventory_movements
    (company_id, item_id, moved_on, qty, unit_cost, value, reason,
     source_kind, source_id, entry_id)
  VALUES
    (p_company_id, p_item_id, p_moved_on, v_qty, v_unit_cost,
     v_value, p_reason, p_source_kind, p_source_id, p_entry_id)
  RETURNING id INTO v_id;

  RETURN v_id;
END $$;

-- record_movement is INTERNAL. It writes half of a two-sided fact — stock
-- without its money — and can only ever be called by something that is also
-- writing the other half, in the same transaction. The app role gets the
-- document-shaped doors instead (record_adjustment() below today; issue_bill,
-- issue_invoice and post_build in Tasks 2 and 3), each of which owns BOTH
-- sides. Mirrors reverse_entry_internal (0002) and ensure_ar_account (0005).
REVOKE EXECUTE ON FUNCTION record_movement(
  uuid, bigint, date, numeric, numeric, text, text, bigint, bigint) FROM PUBLIC;

-- ---------------------------------------------------------------------------
-- record_adjustment — the app-facing door onto a stock correction
-- ---------------------------------------------------------------------------

-- Shrinkage, a physical count correction, a breakage write-off. Writes the
-- journal entry AND the movement together, in one transaction, so the two can
-- never disagree — which is exactly why it lives in the database and not in
-- the API. Positive quantity means stock found (Dr Inventory / Cr the
-- adjustment account); negative means stock gone (the reverse), valued at the
-- running average like any other issue.
--
-- Returns the journal entry id.
CREATE FUNCTION record_adjustment(
  p_company_id uuid,
  p_item_id bigint,
  p_moved_on date,
  p_qty numeric,
  p_unit_cost numeric,               -- required when adding stock; NULL when removing
  p_adjustment_account_id bigint,
  p_memo text,
  p_user_id uuid
) RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_item items%ROWTYPE;
  v_acct_type text;
  v_acct_name text;
  v_entry_id bigint;
  v_movement_id bigint;
  v_qty numeric(19,4);
  v_value numeric(19,4);
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 why the quantity is changing — an adjustment without a reason is impossible to audit later';
  END IF;

  SELECT * INTO v_item FROM items
   WHERE company_id = p_company_id AND id = p_item_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'item % not found', p_item_id;
  END IF;

  SELECT type, name INTO v_acct_type, v_acct_name FROM accounts
   WHERE company_id = p_company_id AND id = p_adjustment_account_id;
  IF v_acct_type IS NULL THEN
    RAISE EXCEPTION 'account % not found', p_adjustment_account_id;
  END IF;

  -- The other side of an adjustment may not be ANY item's inventory account —
  -- not just this item's. Posting item P's shrinkage to item F's inventory
  -- account breaks the tie on F's account, and the deferred per-entry check
  -- cannot see it: that check builds its account set from the movements, and
  -- the only movement here belongs to P. A manufacturer with "Inventory - Raw
  -- Materials" and "Inventory - Finished Goods" picks the wrong dropdown row
  -- and the books quietly stop tying. Same set check_inventory_ties() uses.
  IF p_adjustment_account_id = v_item.inventory_account_id THEN
    RAISE EXCEPTION
      '''%'' is this item''s own inventory account — pick the account that explains the change, like Shrinkage.',
      v_acct_name;
  END IF;
  IF EXISTS (
    SELECT 1 FROM items i
     WHERE i.company_id = p_company_id
       AND i.inventory_account_id = p_adjustment_account_id
  ) THEN
    RAISE EXCEPTION
      '''%'' is another item''s inventory account — pick the account that explains the change, like Shrinkage.',
      v_acct_name;
  END IF;

  -- ...and it has to be an account that can absorb a gain or a loss. Shrinkage
  -- posted to Cash silently misstates the bank balance and corrupts the next
  -- reconciliation; posted to A/R it corrupts the aging. Equity is allowed
  -- because the spec's opening-quantities path is Dr Inventory / Cr Opening
  -- Balance Equity, and income because a found-inventory gain is legitimately
  -- income on some charts.
  IF v_acct_type NOT IN ('expense', 'income', 'equity') THEN
    RAISE EXCEPTION
      'an inventory adjustment posts to an expense, income or equity account — ''%'' is a% % account',
      v_acct_name, CASE WHEN v_acct_type = 'asset' THEN 'n' ELSE '' END, v_acct_type;
  END IF;

  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;

  v_movement_id := record_movement(
    p_company_id, p_item_id, p_moved_on, p_qty, p_unit_cost,
    'adjustment', NULL, NULL, v_entry_id);

  -- Read back what was actually STORED (record_movement rounds the quantity to
  -- the column's scale), so the audit log records the movement that happened
  -- rather than the number the caller happened to type.
  SELECT qty, value INTO v_qty, v_value FROM inventory_movements
   WHERE company_id = p_company_id AND id = v_movement_id;

  -- §3.1 forbids a zero-amount journal line, and every movement must ride on a
  -- posted entry — so an adjustment that moves no money has nowhere to go.
  -- Only reachable for stock carrying no value at all (received at zero cost).
  IF v_value = 0 THEN
    RAISE EXCEPTION
      '''%'' has no value on hand, so this adjustment would move no money at all — there is nothing to post',
      v_item.name;
  END IF;

  IF v_value > 0 THEN
    INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit, memo)
    VALUES (p_company_id, v_entry_id, v_item.inventory_account_id, v_value, 0, p_memo),
           (p_company_id, v_entry_id, p_adjustment_account_id, 0, v_value, p_memo);
  ELSE
    INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit, memo)
    VALUES (p_company_id, v_entry_id, p_adjustment_account_id, -v_value, 0, p_memo),
           (p_company_id, v_entry_id, v_item.inventory_account_id, 0, -v_value, p_memo);
  END IF;

  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, 'adjust', 'item', p_item_id::text,
          -- money and quantities as TEXT, never JSON numbers: jsonb keeps
          -- arbitrary precision but every JSON reader turns them into floats.
          jsonb_build_object('qty', v_qty::text, 'value', v_value::text,
                             'entry_id', v_entry_id, 'movement_id', v_movement_id));

  RETURN v_entry_id;
END $$;

-- ---------------------------------------------------------------------------
-- Bill of materials: an item can never be its own ancestor
-- ---------------------------------------------------------------------------

-- True when making p_component_item_id a component of p_assembly_item_id would
-- create a cycle — i.e. when the assembly is already somewhere beneath the
-- component. UNION (not UNION ALL) makes the walk terminate even if the table
-- somehow already contained a cycle.
CREATE FUNCTION assembly_would_recurse(
  p_company_id uuid, p_assembly_item_id bigint, p_component_item_id bigint
) RETURNS boolean
LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public AS $$
DECLARE v_found boolean;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;
  IF p_assembly_item_id = p_component_item_id THEN
    RETURN true;
  END IF;

  WITH RECURSIVE descendants AS (
    SELECT a.component_item_id
      FROM assemblies a
     WHERE a.company_id = p_company_id
       AND a.assembly_item_id = p_component_item_id
    UNION
    SELECT a.component_item_id
      FROM assemblies a
      JOIN descendants d ON d.component_item_id = a.assembly_item_id
     WHERE a.company_id = p_company_id
  )
  SELECT EXISTS (SELECT 1 FROM descendants d
                  WHERE d.component_item_id = p_assembly_item_id)
    INTO v_found;

  RETURN v_found;
END $$;

CREATE FUNCTION assemblies_no_recursion() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_assembly_name text;
  v_component_name text;
  v_kind text;
BEGIN
  SELECT name, kind INTO v_assembly_name, v_kind FROM items
   WHERE company_id = NEW.company_id AND id = NEW.assembly_item_id;
  IF v_kind IS DISTINCT FROM 'inventory' THEN
    RAISE EXCEPTION
      'only a stocked item can be built from parts — ''%'' is not one', v_assembly_name;
  END IF;

  SELECT name, kind INTO v_component_name, v_kind FROM items
   WHERE company_id = NEW.company_id AND id = NEW.component_item_id;
  -- A component has to be something we can actually draw from stock at a
  -- cost. A service or non-stocked item has no quantity on hand, so a build
  -- could never consume it (the spec's non-goals rule out absorbing labor
  -- and overhead into build cost: a build costs exactly its components).
  IF v_kind IS DISTINCT FROM 'inventory' THEN
    RAISE EXCEPTION
      '''%'' is not a stocked item, so it cannot be a part of ''%''',
      v_component_name, v_assembly_name;
  END IF;

  -- The CHECK constraint refuses this too, but a BEFORE trigger runs first,
  -- so the self-reference gets its own sentence rather than the (nonsensical)
  -- "'X' is already made from 'X'".
  IF NEW.assembly_item_id = NEW.component_item_id THEN
    RAISE EXCEPTION '''%'' cannot be a part of itself', v_assembly_name;
  END IF;

  IF assembly_would_recurse(NEW.company_id, NEW.assembly_item_id, NEW.component_item_id) THEN
    RAISE EXCEPTION
      '''%'' is already made from ''%'' — adding it back would make the item a part of itself',
      v_component_name, v_assembly_name;
  END IF;

  RETURN NEW;
END $$;

CREATE TRIGGER assemblies_no_recursion
  BEFORE INSERT OR UPDATE ON assemblies
  FOR EACH ROW EXECUTE FUNCTION assemblies_no_recursion();

-- ---------------------------------------------------------------------------
-- Per-company numbering (0010's lock-first ordering, mirrored exactly)
-- ---------------------------------------------------------------------------

-- Internal: no session-company guard of its own (callers establish or verify
-- it), and not callable by app — mirrors ensure_ar_account (0005).
CREATE FUNCTION allocate_po_no(p_company_id uuid) RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE v_no bigint;
BEGIN
  INSERT INTO po_counters (company_id) VALUES (p_company_id)
    ON CONFLICT (company_id) DO NOTHING;
  SELECT next_po_no INTO v_no FROM po_counters
   WHERE company_id = p_company_id FOR UPDATE;
  UPDATE po_counters SET next_po_no = v_no + 1 WHERE company_id = p_company_id;
  RETURN v_no;
END $$;

REVOKE EXECUTE ON FUNCTION allocate_po_no(uuid) FROM PUBLIC;

CREATE FUNCTION allocate_build_no(p_company_id uuid) RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE v_no bigint;
BEGIN
  INSERT INTO build_counters (company_id) VALUES (p_company_id)
    ON CONFLICT (company_id) DO NOTHING;
  SELECT next_build_no INTO v_no FROM build_counters
   WHERE company_id = p_company_id FOR UPDATE;
  UPDATE build_counters SET next_build_no = v_no + 1 WHERE company_id = p_company_id;
  RETURN v_no;
END $$;

REVOKE EXECUTE ON FUNCTION allocate_build_no(uuid) FROM PUBLIC;

-- POs are born drafts; issue_purchase_order() is the only path to a number
-- (mirrors invoices_born_draft in 0005).
CREATE FUNCTION purchase_orders_born_draft() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.status <> 'draft' OR NEW.po_no IS NOT NULL THEN
    RAISE EXCEPTION
      'purchase orders are created as drafts; use issue_purchase_order() to issue';
  END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER purchase_orders_born_draft
  BEFORE INSERT ON purchase_orders
  FOR EACH ROW EXECUTE FUNCTION purchase_orders_born_draft();

-- ...and being born a draft is worth nothing if an issued PO can be pushed
-- back into one — or simply deleted. A number, once issued, is permanent:
-- flipping back to draft, or deleting the row outright, frees the counter to
-- hand the SAME number to a second purchase order while the first one is
-- already sitting on a vendor's desk. DELETE is covered here for exactly the
-- reason invoices_immutable_when_issued (0005) covers it.
CREATE 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;

  RETURN NEW;
END $$;

CREATE TRIGGER purchase_orders_forward_only
  BEFORE UPDATE OR DELETE ON purchase_orders
  FOR EACH ROW EXECUTE FUNCTION purchase_orders_forward_only();

-- Builds are history the moment they exist: the movements and the journal
-- entry they produced are both already immutable, so letting the build header
-- be rewritten (or deleted) would leave the story of WHY that stock moved
-- disagreeing with the stock itself. The single permitted change is the
-- posted -> reversed status flip that Task 3's reversal performs, after it has
-- written the compensating movements.
CREATE FUNCTION builds_immutable() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'DELETE' THEN
    RAISE EXCEPTION
      'build #% is a permanent record; reverse it instead of deleting it', OLD.build_no;
  END IF;

  IF OLD.status = 'posted' AND NEW.status = 'reversed'
     AND NEW.company_id       IS NOT DISTINCT FROM OLD.company_id
     AND NEW.id               IS NOT DISTINCT FROM OLD.id
     AND NEW.build_no         IS NOT DISTINCT FROM OLD.build_no
     AND NEW.assembly_item_id IS NOT DISTINCT FROM OLD.assembly_item_id
     AND NEW.qty              IS NOT DISTINCT FROM OLD.qty
     AND NEW.built_on         IS NOT DISTINCT FROM OLD.built_on
     AND NEW.entry_id         IS NOT DISTINCT FROM OLD.entry_id
     AND NEW.memo             IS NOT DISTINCT FROM OLD.memo
     AND NEW.created_by       IS NOT DISTINCT FROM OLD.created_by
  THEN
    RETURN NEW;
  END IF;

  RAISE EXCEPTION
    'build #% is a permanent record; reverse it instead of editing it', OLD.build_no;
END $$;

CREATE TRIGGER builds_immutable
  BEFORE UPDATE OR DELETE ON builds
  FOR EACH ROW EXECUTE FUNCTION builds_immutable();

-- Issuing a PO posts NOTHING. A purchase order is a promise, not a
-- transaction: no journal entry, no movement, no balance changes anywhere.
CREATE FUNCTION issue_purchase_order(
  p_company_id uuid, p_po_id bigint, p_user_id uuid
) RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_po purchase_orders%ROWTYPE;
  v_lines int;
  v_no bigint;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  SELECT * INTO v_po FROM purchase_orders
   WHERE company_id = p_company_id AND id = p_po_id
   FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'purchase order % not found', p_po_id;
  END IF;
  IF v_po.status <> 'draft' THEN
    RAISE EXCEPTION 'purchase order % is already %', p_po_id, v_po.status;
  END IF;

  SELECT count(*) INTO v_lines FROM purchase_order_lines
   WHERE company_id = p_company_id AND po_id = p_po_id;
  IF v_lines < 1 THEN
    RAISE EXCEPTION 'purchase order % has nothing on it', p_po_id;
  END IF;

  v_no := allocate_po_no(p_company_id);

  UPDATE purchase_orders SET status = 'open', po_no = v_no
   WHERE company_id = p_company_id AND id = p_po_id;

  INSERT INTO audit_log (company_id, user_id, action, entity, entity_id, detail)
  VALUES (p_company_id, p_user_id, 'issue', 'purchase_order', p_po_id::text,
          jsonb_build_object('po_no', v_no));

  RETURN v_no;
END $$;

-- Counter setters. 0010's ordering is mandatory, not stylistic: lock the
-- counter row FIRST, then read max(used), then validate, then update — so a
-- concurrent allocation (which locks that same row before doing anything)
-- can never commit a fresh number into the gap between the max read and the
-- update, leaving the setter validating against a stale maximum.
CREATE FUNCTION set_next_po_no(p_company_id uuid, p_next bigint, p_user_id 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_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  INSERT INTO po_counters (company_id) VALUES (p_company_id)
    ON CONFLICT (company_id) DO NOTHING;
  PERFORM 1 FROM po_counters WHERE company_id = p_company_id FOR UPDATE;

  SELECT coalesce(max(po_no), 0) INTO v_max FROM purchase_orders
   WHERE company_id = p_company_id;

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

  UPDATE po_counters SET next_po_no = p_next WHERE company_id = p_company_id;

  INSERT INTO audit_log (company_id, user_id, action, entity, entity_id, detail)
  VALUES (p_company_id, p_user_id, 'set_next_po_no', 'po_counters',
          p_company_id::text, jsonb_build_object('next_po_no', p_next));
END $$;

CREATE FUNCTION set_next_build_no(p_company_id uuid, p_next bigint, p_user_id 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_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  INSERT INTO build_counters (company_id) VALUES (p_company_id)
    ON CONFLICT (company_id) DO NOTHING;
  PERFORM 1 FROM build_counters WHERE company_id = p_company_id FOR UPDATE;

  SELECT coalesce(max(build_no), 0) INTO v_max FROM builds
   WHERE company_id = p_company_id;

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

  UPDATE build_counters SET next_build_no = p_next WHERE company_id = p_company_id;

  INSERT INTO audit_log (company_id, user_id, action, entity, entity_id, detail)
  VALUES (p_company_id, p_user_id, 'set_next_build_no', 'build_counters',
          p_company_id::text, jsonb_build_object('next_build_no', p_next));
END $$;

-- ---------------------------------------------------------------------------
-- Reports: valuation, and THE TIE
-- ---------------------------------------------------------------------------

-- Per item: quantity, average cost, value — all derived, nothing stored.
-- `value` is SUM(value), not qty * average: the sum is what actually happened
-- and what the ledger was posted, so it is the number that ties. Multiplying
-- the rounded average back out would reintroduce a rounding difference that
-- has no counterpart in the books.
CREATE FUNCTION inventory_valuation(p_company_id uuid, p_as_of date DEFAULT NULL)
RETURNS TABLE (
  item_id bigint, sku text, name text,
  qty_on_hand numeric(19,4), average_cost numeric(19,6), value numeric(19,4)
)
LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public AS $$
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  RETURN QUERY
  SELECT i.id, i.sku, i.name,
         coalesce(m.qty, 0)::numeric(19,4),
         CASE WHEN coalesce(m.qty, 0) = 0 THEN 0::numeric(19,6)
              ELSE round(m.value / m.qty, 6) END,
         coalesce(m.value, 0)::numeric(19,4)
    FROM items i
    LEFT JOIN (
      SELECT im.item_id, sum(im.qty) AS qty, sum(im.value) AS value
        FROM inventory_movements im
       WHERE im.company_id = p_company_id
         AND (p_as_of IS NULL OR im.moved_on <= p_as_of)
       GROUP BY im.item_id
    ) m ON m.item_id = i.id
   WHERE i.company_id = p_company_id AND i.kind = 'inventory'
   ORDER BY i.name;
END $$;

-- THE HEADLINE INVARIANT of this milestone, and the inventory equivalent of
-- ledger_out_of_balance(): two independent derivations of the same number,
-- from different tables, by different code paths.
--   ledger_value   — that account's balance, straight out of journal_lines
--   movement_value — SUM(value) over the movements of the items that belong
--                    to that account
-- They must be equal at every date, for every account, for every company.
--
-- ONE ROW PER INVENTORY ACCOUNT — deliberately, not one total. A single
-- aggregate can be fooled by an error that moves value BETWEEN two inventory
-- accounts: account 1300 short by 20 and account 1310 over by 20 sum to a
-- serene difference of zero while both are individually wrong. The enforcement
-- trigger above is per-account, so the report has to be too, or the report
-- would be quieter than the constraint. Task 5's valuation report shows these
-- rows; inventory_out_of_tie() below is the single number, and it sums
-- ABSOLUTE differences precisely so the same cancellation cannot hide there.
CREATE FUNCTION check_inventory_ties(p_company_id uuid, p_as_of date DEFAULT NULL)
RETURNS TABLE (
  account_id bigint,
  code text,
  name text,
  ledger_value numeric(19,4),
  movement_value numeric(19,4),
  difference numeric(19,4)
)
LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public AS $$
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;

  RETURN QUERY
  WITH accts AS (
    SELECT DISTINCT i.inventory_account_id AS acct
      FROM items i
     WHERE i.company_id = p_company_id AND i.inventory_account_id IS NOT NULL
  ),
  led AS (
    SELECT l.account_id AS acct, sum(l.debit - l.credit)::numeric(19,4) AS v
      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 e.status = 'posted'
       AND (p_as_of IS NULL OR e.entry_date <= p_as_of)
       AND l.account_id IN (SELECT acct FROM accts)
     GROUP BY l.account_id
  ),
  mov AS (
    SELECT i.inventory_account_id AS acct, sum(m.value)::numeric(19,4) AS v
      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 (p_as_of IS NULL OR m.moved_on <= p_as_of)
       AND i.inventory_account_id IS NOT NULL
     GROUP BY i.inventory_account_id
  )
  SELECT a.id, a.code, a.name,
         coalesce(led.v, 0)::numeric(19,4),
         coalesce(mov.v, 0)::numeric(19,4),
         (coalesce(led.v, 0) - coalesce(mov.v, 0))::numeric(19,4)
    FROM accts
    JOIN accounts a ON a.company_id = p_company_id AND a.id = accts.acct
    LEFT JOIN led ON led.acct = accts.acct
    LEFT JOIN mov ON mov.acct = accts.acct
   ORDER BY a.code;
END $$;

-- The single number, for tests and the health panel: 0.0000 exactly when every
-- inventory account ties. ABSOLUTE differences, so two accounts wrong in
-- opposite directions can never cancel into a clean bill of health. This is
-- inventory's ledger_out_of_balance().
CREATE FUNCTION inventory_out_of_tie(p_company_id uuid, p_as_of date DEFAULT NULL)
RETURNS numeric(19,4)
LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public AS $$
DECLARE v numeric(19,4);
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match';
  END IF;
  SELECT coalesce(sum(abs(t.difference)), 0)::numeric(19,4) INTO v
    FROM check_inventory_ties(p_company_id, p_as_of) t;
  RETURN v;
END $$;

-- ---------------------------------------------------------------------------
-- reverse_entry: a bare reversal must not be able to unpost stock
-- ---------------------------------------------------------------------------

-- reverse_entry() is app-executable and exposed at POST /api/c/entries/:id/
-- reverse. Pointed at an entry that carried stock movements, it writes the
-- mirror image straight out of the Inventory account and — because it knows
-- nothing about inventory — leaves the quantity on hand exactly where it was.
-- The tie breaks, and it breaks IRRECOVERABLY: restoring it would mean
-- destroying real quantity that is physically still on the shelf.
--
-- So a movement-bearing entry is refused here, exactly the way an
-- intercompany leg already is (0002's guard, immediately above this one and
-- preserved verbatim). The document flows are unaffected: void_invoice(),
-- void_bill() and Task 3's build reversal write their compensating movements
-- first and then call reverse_entry_internal(), which is the same door
-- reverse_intercompany() has always used.
--
-- Everything below is 0002's reverse_entry() text — the newest definition;
-- grepped across 0003-0017, it has never been replaced (0011 replaced
-- reverse_entry_INTERNAL, not this) — with the one block added.
CREATE OR REPLACE FUNCTION reverse_entry(
  p_company_id uuid, p_entry_id bigint, p_reversal_date date, p_user_id uuid
) RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match entry company';
  END IF;

  IF EXISTS (
    SELECT 1 FROM intercompany_postings ip
     WHERE (ip.from_company_id = p_company_id AND ip.from_entry_id = p_entry_id)
        OR (ip.to_company_id = p_company_id AND ip.to_entry_id = p_entry_id)
  ) THEN
    RAISE EXCEPTION
      'entry % is one leg of an intercompany posting; use reverse_intercompany()',
      p_entry_id;
  END IF;

  -- 0018: stock moved on this entry. Reversing the money alone would leave the
  -- quantity on hand untouched and the books permanently disagreeing with the
  -- shelf.
  IF EXISTS (
    SELECT 1 FROM inventory_movements m
     WHERE m.company_id = p_company_id AND m.entry_id = p_entry_id
  ) THEN
    RAISE EXCEPTION
      'entry % moved stock, so reversing it on its own would leave the quantity on hand wrong — void the document it came from, or record an inventory adjustment instead',
      p_entry_id;
  END IF;

  RETURN reverse_entry_internal(p_company_id, p_entry_id, p_reversal_date, p_user_id);
END $$;
