-- 0002: ledger core — chart of accounts, journal, period locks,
-- per-company numbering, intercompany pairs, audit log.
-- Invariants live HERE, in the database (CLAUDE.md §3).

CREATE TABLE accounts (
  company_id uuid NOT NULL REFERENCES companies (id),
  id bigint GENERATED ALWAYS AS IDENTITY,
  code text NOT NULL,
  name text NOT NULL,
  type text NOT NULL
    CHECK (type IN ('asset', 'liability', 'equity', 'income', 'expense')),
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (id),
  UNIQUE (company_id, id),
  UNIQUE (company_id, code)
);

CREATE TABLE journal_entries (
  company_id uuid NOT NULL REFERENCES companies (id),
  id bigint GENERATED ALWAYS AS IDENTITY,
  status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'posted')),
  entry_no bigint,                -- assigned at posting, per company, from 1
  entry_date date NOT NULL,
  memo text,
  source text,                    -- e.g. 'quickbooks_journal', 'adp_gl'
  external_ref text,              -- e.g. QuickBooks Trans #
  reverses_entry_id bigint,
  created_by uuid NOT NULL REFERENCES users (id),
  created_at timestamptz NOT NULL DEFAULT now(),
  posted_at timestamptz,
  PRIMARY KEY (id),
  UNIQUE (company_id, id),
  UNIQUE (company_id, entry_no),
  UNIQUE (company_id, source, external_ref),
  FOREIGN KEY (company_id, reverses_entry_id)
    REFERENCES journal_entries (company_id, id),
  CHECK (status = 'draft' OR entry_no IS NOT NULL),
  CHECK ((source IS NULL) = (external_ref IS NULL))
);

CREATE TABLE journal_lines (
  company_id uuid NOT NULL REFERENCES companies (id),
  id bigint GENERATED ALWAYS AS IDENTITY,
  entry_id bigint NOT NULL,
  account_id bigint NOT NULL,
  debit numeric(19,4) NOT NULL DEFAULT 0 CHECK (debit >= 0),
  credit numeric(19,4) NOT NULL DEFAULT 0 CHECK (credit >= 0),
  memo text,
  PRIMARY KEY (id),
  UNIQUE (company_id, id),
  -- §3.1: exactly one of debit/credit non-zero per line
  CHECK ((debit > 0 AND credit = 0) OR (credit > 0 AND debit = 0)),
  -- §4: cross-company lines are physically impossible
  FOREIGN KEY (company_id, entry_id) REFERENCES journal_entries (company_id, id),
  FOREIGN KEY (company_id, account_id) REFERENCES accounts (company_id, id)
);
CREATE INDEX journal_lines_by_account ON journal_lines (company_id, account_id);
CREATE INDEX journal_lines_by_entry ON journal_lines (company_id, entry_id);

-- §3.3: append-only log; the effective lock is the latest row.
CREATE TABLE period_locks (
  company_id uuid NOT NULL REFERENCES companies (id),
  id bigint GENERATED ALWAYS AS IDENTITY,
  locked_through date NOT NULL,
  created_by uuid NOT NULL REFERENCES users (id),
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (id),
  UNIQUE (company_id, id)
);

-- §4: per-company entry numbering via SELECT ... FOR UPDATE.
CREATE TABLE entry_counters (
  company_id uuid PRIMARY KEY REFERENCES companies (id),
  next_entry_no bigint NOT NULL DEFAULT 1
);

-- §4: one directed row per intercompany relationship.
CREATE TABLE intercompany_pairs (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  from_company_id uuid NOT NULL REFERENCES companies (id),
  to_company_id uuid NOT NULL REFERENCES companies (id),
  due_from_account_id bigint NOT NULL,  -- asset account in the FROM company
  due_to_account_id bigint NOT NULL,    -- liability account in the TO company
  UNIQUE (from_company_id, to_company_id),
  CHECK (from_company_id <> to_company_id),
  FOREIGN KEY (from_company_id, due_from_account_id)
    REFERENCES accounts (company_id, id),
  FOREIGN KEY (to_company_id, due_to_account_id)
    REFERENCES accounts (company_id, id)
);

CREATE TABLE audit_log (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  company_id uuid NOT NULL REFERENCES companies (id),
  user_id uuid REFERENCES users (id),
  action text NOT NULL,
  entity text NOT NULL,
  entity_id text NOT NULL,
  detail jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- Row-level security: ENABLE + FORCE everywhere, keyed to current_company_id().
DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY[
    'accounts', 'journal_entries', 'journal_lines',
    'period_locks', 'entry_counters', 'audit_log'
  ] 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);
  END LOOP;
END $$;

-- intercompany_pairs spans two companies by nature: visible from either side.
ALTER TABLE intercompany_pairs ENABLE ROW LEVEL SECURITY;
ALTER TABLE intercompany_pairs FORCE ROW LEVEL SECURITY;
CREATE POLICY intercompany_pairs_isolation ON intercompany_pairs
  USING (from_company_id = current_company_id()
      OR to_company_id = current_company_id());

GRANT SELECT, INSERT, UPDATE, DELETE ON accounts, journal_entries, journal_lines TO app;
GRANT SELECT, INSERT ON period_locks, audit_log TO app;
GRANT SELECT, INSERT, UPDATE ON entry_counters TO app;
GRANT SELECT ON intercompany_pairs TO app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app;

-- §3 invariant 1: every entry balances; minimum two lines. Deferred to commit
-- so a draft entry and its lines can be inserted in any order in a transaction.
CREATE FUNCTION assert_entry_balanced(p_company_id uuid, p_entry_id bigint)
RETURNS void LANGUAGE plpgsql AS $$
DECLARE
  v_line_count int;
  v_diff numeric(19,4);
BEGIN
  -- the whole entry may have been deleted in this transaction (draft cleanup)
  IF NOT EXISTS (
    SELECT 1 FROM journal_entries e
     WHERE e.company_id = p_company_id AND e.id = p_entry_id
  ) THEN
    RETURN;
  END IF;

  SELECT count(*), coalesce(sum(l.debit - l.credit), 0)
    INTO v_line_count, v_diff
    FROM journal_lines l
   WHERE l.company_id = p_company_id AND l.entry_id = p_entry_id;

  IF v_line_count < 2 THEN
    RAISE EXCEPTION 'journal entry % must have at least two lines', p_entry_id;
  END IF;
  IF v_diff <> 0 THEN
    RAISE EXCEPTION 'journal entry % is out of balance by %', p_entry_id, v_diff;
  END IF;
END $$;

CREATE FUNCTION check_entry_balanced() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF TG_TABLE_NAME = 'journal_entries' THEN
    PERFORM assert_entry_balanced(NEW.company_id, NEW.id);
  ELSIF TG_OP = 'INSERT' THEN
    PERFORM assert_entry_balanced(NEW.company_id, NEW.entry_id);
  ELSIF TG_OP = 'DELETE' THEN
    PERFORM assert_entry_balanced(OLD.company_id, OLD.entry_id);
  ELSE
    -- UPDATE: a line may have moved between entries; recheck BOTH sides
    PERFORM assert_entry_balanced(NEW.company_id, NEW.entry_id);
    IF OLD.entry_id IS DISTINCT FROM NEW.entry_id
       OR OLD.company_id IS DISTINCT FROM NEW.company_id THEN
      PERFORM assert_entry_balanced(OLD.company_id, OLD.entry_id);
    END IF;
  END IF;
  RETURN NULL;
END $$;

CREATE CONSTRAINT TRIGGER entry_balanced_on_lines
  AFTER INSERT OR UPDATE OR DELETE ON journal_lines
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION check_entry_balanced();

CREATE CONSTRAINT TRIGGER entry_balanced_on_entries
  AFTER INSERT ON journal_entries
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION check_entry_balanced();

-- §3 invariant 3: the effective lock is the latest row of the append-only log.
CREATE FUNCTION effective_locked_through(p_company_id uuid) RETURNS date
LANGUAGE sql STABLE AS $$
  SELECT locked_through FROM period_locks
   WHERE company_id = p_company_id
   ORDER BY created_at DESC, id DESC
   LIMIT 1
$$;

CREATE FUNCTION forbid_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION '% is append-only', TG_TABLE_NAME;
END $$;

CREATE TRIGGER period_locks_append_only
  BEFORE UPDATE OR DELETE ON period_locks
  FOR EACH ROW EXECUTE FUNCTION forbid_change();

-- §4: per-company numbering. Counter row locked FOR UPDATE; volume is low
-- enough that serializing posts per company is irrelevant (CLAUDE.md §4).
CREATE FUNCTION post_entry(p_company_id uuid, p_entry_id bigint) RETURNS bigint
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_entry journal_entries%ROWTYPE;
  v_no bigint;
  v_lock date;
BEGIN
  IF current_company_id() IS DISTINCT FROM p_company_id THEN
    RAISE EXCEPTION 'session company does not match entry company';
  END IF;

  SELECT * INTO v_entry FROM journal_entries
   WHERE company_id = p_company_id AND id = p_entry_id
   FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'entry % not found', p_entry_id;
  END IF;
  IF v_entry.status = 'posted' THEN
    RAISE EXCEPTION 'entry % is already posted', p_entry_id;
  END IF;

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

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

  UPDATE journal_entries
     SET status = 'posted', entry_no = v_no, posted_at = now()
   WHERE company_id = p_company_id AND id = p_entry_id;

  INSERT INTO audit_log (company_id, user_id, action, entity, entity_id, detail)
  VALUES (p_company_id, v_entry.created_by, 'post', 'journal_entry',
          p_entry_id::text, jsonb_build_object('entry_no', v_no));

  RETURN v_no;
END $$;

-- Harden posting: the ONLY path to posted status is post_entry().
-- app may not touch status/entry_no/posted_at directly:
REVOKE UPDATE ON journal_entries FROM app;
GRANT UPDATE (entry_date, memo) ON journal_entries TO app;

-- entries are born drafts; posting fields are set only by post_entry():
CREATE FUNCTION entries_born_draft() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF NEW.status <> 'draft' OR NEW.entry_no IS NOT NULL
     OR NEW.posted_at IS NOT NULL THEN
    RAISE EXCEPTION 'entries are created as drafts; use post_entry() to post';
  END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER entries_born_draft
  BEFORE INSERT ON journal_entries
  FOR EACH ROW EXECUTE FUNCTION entries_born_draft();

-- counter and audit writes happen only inside post_entry() (SECURITY DEFINER):
REVOKE INSERT, UPDATE ON entry_counters FROM app;
REVOKE INSERT ON audit_log FROM app;

-- §3 invariant 2: posted entries are immutable. Corrections are reversing
-- entries. post_entry()'s own draft->posted UPDATE passes because
-- OLD.status = 'draft'. Row triggers bind everyone, superuser included;
-- TRUNCATE never fires row triggers and is forbidden separately below.
CREATE FUNCTION forbid_posted_entry_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  IF OLD.status = 'posted' THEN
    RAISE EXCEPTION
      'posted entries are immutable; create a reversing entry instead';
  END IF;
  IF TG_OP = 'DELETE' THEN RETURN OLD; END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER entries_immutable_when_posted
  BEFORE UPDATE OR DELETE ON journal_entries
  FOR EACH ROW EXECUTE FUNCTION forbid_posted_entry_change();

CREATE FUNCTION forbid_posted_line_change() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
  v_company uuid;
  v_entry bigint;
  v_status text;
BEGIN
  IF TG_OP = 'INSERT' THEN
    v_company := NEW.company_id; v_entry := NEW.entry_id;
  ELSE
    v_company := OLD.company_id; v_entry := OLD.entry_id;
  END IF;
  SELECT status INTO v_status FROM journal_entries e
   WHERE e.company_id = v_company AND e.id = v_entry
   FOR SHARE;
  IF v_status = 'posted' THEN
    RAISE EXCEPTION
      'lines of a posted entry are immutable; create a reversing entry instead';
  END IF;
  -- an UPDATE must not move a line onto a posted entry either
  IF TG_OP = 'UPDATE' AND (NEW.entry_id <> OLD.entry_id
      OR NEW.company_id <> OLD.company_id) THEN
    SELECT status INTO v_status FROM journal_entries e
     WHERE e.company_id = NEW.company_id AND e.id = NEW.entry_id
     FOR SHARE;
    IF v_status = 'posted' THEN
      RAISE EXCEPTION
        'lines of a posted entry are immutable; create a reversing entry instead';
    END IF;
  END IF;
  IF TG_OP = 'DELETE' THEN RETURN OLD; END IF;
  RETURN NEW;
END $$;

CREATE TRIGGER lines_immutable_when_posted
  BEFORE INSERT OR UPDATE OR DELETE ON journal_lines
  FOR EACH ROW EXECUTE FUNCTION forbid_posted_line_change();

-- TRUNCATE never fires row triggers; forbid it outright on ledger history.
CREATE FUNCTION forbid_truncate() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'TRUNCATE is not allowed on %', TG_TABLE_NAME;
END $$;

CREATE TRIGGER journal_entries_no_truncate
  BEFORE TRUNCATE ON journal_entries
  FOR EACH STATEMENT EXECUTE FUNCTION forbid_truncate();
CREATE TRIGGER journal_lines_no_truncate
  BEFORE TRUNCATE ON journal_lines
  FOR EACH STATEMENT EXECUTE FUNCTION forbid_truncate();
CREATE TRIGGER period_locks_no_truncate
  BEFORE TRUNCATE ON period_locks
  FOR EACH STATEMENT EXECUTE FUNCTION forbid_truncate();
CREATE TRIGGER audit_log_no_truncate
  BEFORE TRUNCATE ON audit_log
  FOR EACH STATEMENT EXECUTE FUNCTION forbid_truncate();

-- §3 invariant 2: corrections are reversing entries — the mirror image,
-- linked back via reverses_entry_id. SECURITY DEFINER: RLS does NOT protect
-- this function — the explicit current_company_id() guard below does.
-- post_entry re-validates period locks for the reversal date.
CREATE 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;
  RETURN reverse_entry_internal(p_company_id, p_entry_id, p_reversal_date, p_user_id);
END $$;

-- one reversal per entry, enforced physically
CREATE UNIQUE INDEX journal_entries_reversal_once
  ON journal_entries (company_id, reverses_entry_id)
  WHERE reverses_entry_id IS NOT NULL;

-- §3 invariant 4: no stored balances — everything derives from journal_lines
-- at query time.
CREATE FUNCTION ledger_out_of_balance(p_company_id uuid, p_as_of date DEFAULT NULL)
RETURNS numeric(19,4)
LANGUAGE sql STABLE AS $$
  SELECT coalesce(sum(l.debit - l.credit), 0)::numeric(19,4)
    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)
$$;

-- NULL p_as_of means "all of history", matching ledger_out_of_balance.
CREATE FUNCTION trial_balance(p_company_id uuid, p_as_of date DEFAULT NULL)
RETURNS TABLE (
  account_id bigint, code text, name text, type text,
  debit_total numeric(19,4), credit_total numeric(19,4), balance numeric(19,4)
)
LANGUAGE sql STABLE AS $$
  SELECT a.id, a.code, a.name, a.type,
         coalesce(t.debit_total, 0)::numeric(19,4),
         coalesce(t.credit_total, 0)::numeric(19,4),
         coalesce(t.debit_total - t.credit_total, 0)::numeric(19,4)
    FROM accounts a
    LEFT JOIN (
      SELECT l.account_id,
             sum(l.debit) AS debit_total,
             sum(l.credit) AS credit_total
        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)
       GROUP BY l.account_id
    ) t ON t.account_id = a.id
   WHERE a.company_id = p_company_id
   ORDER BY a.code
$$;

-- Intercompany legs are linked so they can only be reversed together.
CREATE TABLE intercompany_postings (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  from_company_id uuid NOT NULL,
  from_entry_id bigint NOT NULL,
  to_company_id uuid NOT NULL,
  to_entry_id bigint NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  FOREIGN KEY (from_company_id, from_entry_id) REFERENCES journal_entries (company_id, id),
  FOREIGN KEY (to_company_id, to_entry_id) REFERENCES journal_entries (company_id, id),
  UNIQUE (from_company_id, from_entry_id),
  UNIQUE (to_company_id, to_entry_id)
);
ALTER TABLE intercompany_postings ENABLE ROW LEVEL SECURITY;
ALTER TABLE intercompany_postings FORCE ROW LEVEL SECURITY;
CREATE POLICY intercompany_postings_isolation ON intercompany_postings
  USING (from_company_id = current_company_id()
      OR to_company_id = current_company_id());
GRANT SELECT ON intercompany_postings TO app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app;

-- Commit-time integrity checks must see ALL rows they validate, regardless of
-- the mutating session's RLS scope (a definer function may legitimately write
-- another company's half of an intercompany transaction). They take explicit
-- company ids and leak nothing.
ALTER FUNCTION assert_entry_balanced(uuid, bigint) SECURITY DEFINER SET search_path = public;
ALTER FUNCTION check_entry_balanced() SECURITY DEFINER SET search_path = public;

REVOKE EXECUTE ON FUNCTION assert_entry_balanced(uuid, bigint) FROM PUBLIC;

-- §4: intercompany helper — both halves in one transaction.
-- SECURITY DEFINER because the app role's RLS scope is one company at a time.
-- Guard: the calling session's company must be one side of the pair.
-- The transaction-local GUC is switched per half so post_entry's session
-- guard passes, then restored to the caller's scope.
CREATE FUNCTION post_intercompany(
  p_from_company_id uuid,
  p_to_company_id uuid,
  p_amount numeric(19,4),
  p_entry_date date,
  p_memo text,
  p_user_id uuid,
  p_from_offset_account_id bigint,
  p_to_offset_account_id bigint
) RETURNS TABLE (from_entry_id bigint, to_entry_id bigint)
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_pair intercompany_pairs%ROWTYPE;
  v_caller uuid;
  v_from bigint;
  v_to bigint;
BEGIN
  v_caller := current_company_id();
  IF v_caller IS NULL
     OR v_caller NOT IN (p_from_company_id, p_to_company_id) THEN
    RAISE EXCEPTION 'session company is not a party to this intercompany transaction';
  END IF;
  IF p_amount <= 0 THEN
    RAISE EXCEPTION 'amount must be positive';
  END IF;

  SELECT * INTO v_pair FROM intercompany_pairs
   WHERE from_company_id = p_from_company_id
     AND to_company_id = p_to_company_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'no intercompany pair configured for this direction';
  END IF;

  -- FROM half: due-from asset rises, offset (e.g. cash) falls
  PERFORM set_config('app.company_id', p_from_company_id::text, true);
  INSERT INTO journal_entries (company_id, entry_date, memo, created_by)
  VALUES (p_from_company_id, p_entry_date, p_memo, p_user_id)
  RETURNING id INTO v_from;
  INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit)
  VALUES
    (p_from_company_id, v_from, v_pair.due_from_account_id, p_amount, 0),
    (p_from_company_id, v_from, p_from_offset_account_id, 0, p_amount);
  PERFORM post_entry(p_from_company_id, v_from);

  -- TO half: offset (e.g. expense) rises, due-to liability rises
  PERFORM set_config('app.company_id', p_to_company_id::text, true);
  INSERT INTO journal_entries (company_id, entry_date, memo, created_by)
  VALUES (p_to_company_id, p_entry_date, p_memo, p_user_id)
  RETURNING id INTO v_to;
  INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit)
  VALUES
    (p_to_company_id, v_to, p_to_offset_account_id, p_amount, 0),
    (p_to_company_id, v_to, v_pair.due_to_account_id, 0, p_amount);
  PERFORM post_entry(p_to_company_id, v_to);

  INSERT INTO intercompany_postings
    (from_company_id, from_entry_id, to_company_id, to_entry_id)
  VALUES (p_from_company_id, v_from, p_to_company_id, v_to);

  -- restore caller scope (set_config local=true also reverts at txn end)
  PERFORM set_config('app.company_id', v_caller::text, true);

  RETURN QUERY SELECT v_from, v_to;
END $$;

-- Internal reversal worker: no session-company guard, not callable by app.
-- reverse_entry() and reverse_intercompany() are the only entry points, and
-- both establish the correct session scope (or verify it) before calling in.
CREATE FUNCTION reverse_entry_internal(
  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 $$
DECLARE
  v_entry journal_entries%ROWTYPE;
  v_new_id bigint;
BEGIN
  SELECT * INTO v_entry FROM journal_entries
   WHERE company_id = p_company_id AND id = p_entry_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'entry % not found', p_entry_id;
  END IF;
  IF v_entry.status <> 'posted' THEN
    RAISE EXCEPTION 'only posted entries can be reversed';
  END IF;
  IF p_reversal_date < v_entry.entry_date THEN
    RAISE EXCEPTION 'reversal date cannot precede the entry it reverses';
  END IF;

  BEGIN
    INSERT INTO journal_entries
      (company_id, entry_date, memo, created_by, reverses_entry_id)
    VALUES
      (p_company_id, p_reversal_date,
       'Reversal of entry #' || v_entry.entry_no, p_user_id, p_entry_id)
    RETURNING id INTO v_new_id;
  EXCEPTION WHEN unique_violation THEN
    RAISE EXCEPTION 'entry % has already been reversed', p_entry_id;
  END;

  INSERT INTO journal_lines (company_id, entry_id, account_id, debit, credit, memo)
  SELECT company_id, v_new_id, account_id, credit, debit, memo  -- swapped
    FROM journal_lines
   WHERE company_id = p_company_id AND entry_id = p_entry_id;

  PERFORM post_entry(p_company_id, v_new_id);
  RETURN v_new_id;
END $$;

REVOKE EXECUTE ON FUNCTION reverse_entry_internal(uuid, bigint, date, uuid) FROM PUBLIC;

-- §4: intercompany reversal — both legs unwind together, or not at all.
-- SECURITY DEFINER: switches session scope per leg like post_intercompany.
CREATE FUNCTION reverse_intercompany(
  p_from_company_id uuid, p_from_entry_id bigint,
  p_reversal_date date, p_user_id uuid
) RETURNS TABLE (from_reversal_id bigint, to_reversal_id bigint)
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE
  v_posting intercompany_postings%ROWTYPE;
  v_caller uuid;
  v_from_rev bigint;
  v_to_rev bigint;
BEGIN
  v_caller := current_company_id();
  SELECT * INTO v_posting FROM intercompany_postings
   WHERE from_company_id = p_from_company_id AND from_entry_id = p_from_entry_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'no intercompany posting found for entry %', p_from_entry_id;
  END IF;
  IF v_caller IS NULL
     OR v_caller NOT IN (v_posting.from_company_id, v_posting.to_company_id) THEN
    RAISE EXCEPTION 'session company is not a party to this intercompany posting';
  END IF;

  PERFORM set_config('app.company_id', v_posting.from_company_id::text, true);
  v_from_rev := reverse_entry_internal(
    v_posting.from_company_id, v_posting.from_entry_id, p_reversal_date, p_user_id);
  PERFORM set_config('app.company_id', v_posting.to_company_id::text, true);
  v_to_rev := reverse_entry_internal(
    v_posting.to_company_id, v_posting.to_entry_id, p_reversal_date, p_user_id);
  PERFORM set_config('app.company_id', v_caller::text, true);

  -- the reversal pair is itself linked, so ITS legs are equally protected
  INSERT INTO intercompany_postings
    (from_company_id, from_entry_id, to_company_id, to_entry_id)
  VALUES (v_posting.from_company_id, v_from_rev, v_posting.to_company_id, v_to_rev);

  RETURN QUERY SELECT v_from_rev, v_to_rev;
END $$;
