import { createHash } from 'node:crypto';
import type { Pool, PoolClient } from 'pg';

export const CUSTODY_PG_VERSION = 14;
export const CUSTODY_WRITE_LOCK = '7140824361';
export const MONERO_OBSERVER_WORKER_LOCK = '7140824362';
export const CUSTODY_SCHEMA_V1 = `
CREATE SCHEMA IF NOT EXISTS wk_custody;
REVOKE ALL ON SCHEMA wk_custody FROM PUBLIC;
SET LOCAL search_path = wk_custody, pg_catalog;
CREATE TABLE schema_versions(version integer PRIMARY KEY, digest text NOT NULL);
CREATE TABLE custody_meta(key text PRIMARY KEY, value text NOT NULL);
CREATE TABLE accounts(id text PRIMARY KEY, normal text NOT NULL CHECK(normal IN ('debit','credit')),
  balance numeric NOT NULL DEFAULT 0 CHECK(balance>=0 AND balance<1e30 AND balance=trunc(balance)));
CREATE TABLE journals(sequence bigint GENERATED ALWAYS AS IDENTITY UNIQUE, id text PRIMARY KEY,
  kind text NOT NULL, created bigint NOT NULL CHECK(created>=0), posting_tx bigint NOT NULL DEFAULT txid_current());
CREATE TABLE entries(journal text NOT NULL REFERENCES journals(id), position integer NOT NULL CHECK(position>=0),
  account text NOT NULL REFERENCES accounts(id), debit numeric NOT NULL, credit numeric NOT NULL,
  PRIMARY KEY(journal,position), CHECK(debit>=0 AND credit>=0 AND debit<1e30 AND credit<1e30),
  CHECK(debit=trunc(debit) AND credit=trunc(credit) AND ((debit>0 AND credit=0) OR (credit>0 AND debit=0))));
CREATE INDEX entries_account ON entries(account,journal);
CREATE TABLE listings(sequence bigint GENERATED BY DEFAULT AS IDENTITY UNIQUE, id uuid PRIMARY KEY, card text NOT NULL,
  status text NOT NULL CHECK(status IN ('open','reserved','cancelled','sold')), data jsonb NOT NULL);
CREATE UNIQUE INDEX one_active_card ON listings(card) WHERE status IN ('open','reserved');
CREATE TABLE orders(sequence bigint GENERATED BY DEFAULT AS IDENTITY UNIQUE, id uuid PRIMARY KEY,
  buyer text NOT NULL, seller text NOT NULL, status text NOT NULL CHECK(status IN ('awaiting-lock','cancelled','settled')), data jsonb NOT NULL);
CREATE INDEX orders_buyer ON orders(buyer,sequence DESC);
CREATE INDEX orders_seller ON orders(seller,sequence DESC);
CREATE TABLE withdrawals(sequence bigint GENERATED BY DEFAULT AS IDENTITY UNIQUE, id uuid PRIMARY KEY, actor text NOT NULL,
  status text NOT NULL CHECK(status IN ('reserved','prepared','complete','cancelled')), data jsonb NOT NULL);
CREATE INDEX withdrawals_actor ON withdrawals(actor,sequence DESC);
CREATE TABLE receipts(id text PRIMARY KEY, digest text NOT NULL, data jsonb NOT NULL);
CREATE TABLE outbox(id uuid PRIMARY KEY, kind text NOT NULL CHECK(kind IN ('nft-lock','withdrawal')), status text NOT NULL, data text NOT NULL);
CREATE TABLE credits(actor text PRIMARY KEY);
CREATE FUNCTION immutable_history() RETURNS trigger LANGUAGE plpgsql SET search_path=wk_custody,pg_catalog AS $$
BEGIN RAISE EXCEPTION 'Immutable financial history'; END $$;
CREATE TRIGGER entries_immutable BEFORE UPDATE OR DELETE ON entries FOR EACH ROW EXECUTE FUNCTION immutable_history();
CREATE TRIGGER journals_immutable BEFORE UPDATE OR DELETE ON journals FOR EACH ROW EXECUTE FUNCTION immutable_history();
CREATE TRIGGER receipts_immutable BEFORE UPDATE OR DELETE ON receipts FOR EACH ROW EXECUTE FUNCTION immutable_history();
CREATE FUNCTION apply_entry() RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path=wk_custody,pg_catalog AS $$
DECLARE n text; amount numeric;
BEGIN
  IF NOT EXISTS(SELECT 1 FROM journals WHERE id=NEW.journal AND posting_tx=txid_current()) THEN
    RAISE EXCEPTION 'Cannot append to a committed journal';
  END IF;
  SELECT normal INTO STRICT n FROM accounts WHERE id=NEW.account FOR UPDATE;
  amount := CASE WHEN n='debit' THEN NEW.debit-NEW.credit ELSE NEW.credit-NEW.debit END;
  UPDATE accounts SET balance=balance+amount WHERE id=NEW.account;
  RETURN NEW;
END $$;
CREATE TRIGGER entry_projection AFTER INSERT ON entries FOR EACH ROW EXECUTE FUNCTION apply_entry();
CREATE FUNCTION balanced_journal() RETURNS trigger LANGUAGE plpgsql SET search_path=wk_custody,pg_catalog AS $$
DECLARE total numeric; n bigint; journal_id text;
BEGIN
  IF TG_TABLE_NAME='entries' THEN journal_id := NEW.journal; ELSE journal_id := NEW.id; END IF;
  SELECT count(*),sum(debit-credit) INTO n,total FROM entries WHERE journal=journal_id;
  IF n=0 OR total<>0 THEN RAISE EXCEPTION 'Unbalanced financial journal'; END IF;
  RETURN NULL;
END $$;
CREATE CONSTRAINT TRIGGER journal_balance AFTER INSERT ON journals DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION balanced_journal();
CREATE CONSTRAINT TRIGGER entry_balance AFTER INSERT ON entries DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW EXECUTE FUNCTION balanced_journal();
REVOKE ALL ON ALL TABLES IN SCHEMA wk_custody FROM PUBLIC;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA wk_custody FROM PUBLIC;
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA wk_custody FROM PUBLIC;
`;
export const CUSTODY_SCHEMA_V1_DIGEST = createHash('sha256').update(CUSTODY_SCHEMA_V1).digest('hex');
export const CUSTODY_MIGRATION_V2 = `
CREATE TABLE wk_custody.monero_observer_state(
  wallet_fingerprint text PRIMARY KEY CHECK(wallet_fingerprint ~ '^[a-f0-9]{64}$'),
  network text NOT NULL CHECK(network='mainnet'),
  scan_height bigint NOT NULL CHECK(scan_height>=0),
  updated_at bigint NOT NULL CHECK(updated_at>=0)
);
CREATE TABLE wk_custody.monero_transfer_observations(
  wallet_fingerprint text NOT NULL REFERENCES wk_custody.monero_observer_state(wallet_fingerprint),
  observation_id text NOT NULL,
  txid text NOT NULL CHECK(txid ~ '^[a-f0-9]{64}$'),
  monero_address text NOT NULL CHECK(length(monero_address)=95),
  account_index bigint NOT NULL CHECK(account_index>=0),
  address_index bigint NOT NULL CHECK(address_index>=0),
  amount_atomic_xmr numeric(20,0) NOT NULL CHECK(amount_atomic_xmr>0 AND amount_atomic_xmr<=18446744073709551615),
  block_height bigint NOT NULL CHECK(block_height>0),
  wallet_height bigint NOT NULL CHECK(wallet_height>=block_height),
  confirmations bigint NOT NULL CHECK(confirmations=wallet_height-block_height+1),
  timestamp bigint NOT NULL CHECK(timestamp>=0),
  unlock_time text NOT NULL CHECK(unlock_time ~ '^(0|[1-9][0-9]{0,19})$'),
  locked boolean NOT NULL,
  double_spend_seen boolean NOT NULL,
  payload_digest text NOT NULL CHECK(payload_digest ~ '^[a-f0-9]{64}$'),
  currently_present boolean NOT NULL DEFAULT true,
  last_seen_scan_id uuid NOT NULL,
  first_observed_at bigint NOT NULL CHECK(first_observed_at>=0),
  last_observed_at bigint NOT NULL CHECK(last_observed_at>=first_observed_at),
  PRIMARY KEY(wallet_fingerprint,observation_id),
  UNIQUE(wallet_fingerprint,txid,account_index,address_index)
);
CREATE INDEX monero_observations_height ON wk_custody.monero_transfer_observations(wallet_fingerprint,block_height,observation_id);
CREATE TABLE wk_custody.monero_observation_events(
  sequence bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  wallet_fingerprint text NOT NULL,
  observation_id text NOT NULL,
  scan_id uuid NOT NULL,
  event_type text NOT NULL CHECK(event_type IN ('seen','changed','reappeared','not-seen')),
  payload_digest text NOT NULL CHECK(payload_digest ~ '^[a-f0-9]{64}$'),
  wallet_height bigint NOT NULL CHECK(wallet_height>=0),
  observed_at bigint NOT NULL CHECK(observed_at>=0),
  UNIQUE(wallet_fingerprint,observation_id,scan_id,event_type),
  FOREIGN KEY(wallet_fingerprint,observation_id)
    REFERENCES wk_custody.monero_transfer_observations(wallet_fingerprint,observation_id)
);
CREATE TABLE wk_custody.monero_observation_scans(
  scan_id uuid PRIMARY KEY,
  wallet_fingerprint text NOT NULL REFERENCES wk_custody.monero_observer_state(wallet_fingerprint),
  from_height bigint NOT NULL CHECK(from_height>=0),
  through_height bigint NOT NULL CHECK(through_height>=from_height),
  payload_digest text NOT NULL CHECK(payload_digest ~ '^[a-f0-9]{64}$'),
  completed_at bigint NOT NULL CHECK(completed_at>=0)
);
CREATE INDEX monero_scans_wallet_height ON wk_custody.monero_observation_scans(wallet_fingerprint,through_height DESC);
CREATE FUNCTION wk_custody.monero_observation_events_immutable() RETURNS trigger LANGUAGE plpgsql SET search_path=wk_custody,pg_catalog AS $$
BEGIN RAISE EXCEPTION 'Monero observation event history is append-only'; END $$;
CREATE TRIGGER monero_observation_events_no_update BEFORE UPDATE OR DELETE ON wk_custody.monero_observation_events
  FOR EACH ROW EXECUTE FUNCTION wk_custody.monero_observation_events_immutable();
CREATE TRIGGER monero_observation_scans_no_update BEFORE UPDATE OR DELETE ON wk_custody.monero_observation_scans
  FOR EACH ROW EXECUTE FUNCTION wk_custody.monero_observation_events_immutable();
`;
export const CUSTODY_SCHEMA_V2_DIGEST = createHash('sha256')
  .update(`${CUSTODY_SCHEMA_V1}\n-- custody migration v2 --\n${CUSTODY_MIGRATION_V2}`).digest('hex');
export const CUSTODY_MIGRATION_V3 = `
CREATE TABLE wk_custody.monero_deposit_allocations(
  allocation_id uuid PRIMARY KEY,
  request_id uuid NOT NULL UNIQUE,
  custodian_id uuid NOT NULL,
  market_network text NOT NULL CHECK(market_network IN ('wikideck-randomx-mainnet-1','wikideck-randomx-testnet-6')),
  market_chain_id text NOT NULL CHECK(market_chain_id ~ '^[a-f0-9]{64}$'),
  beneficiary text NOT NULL CHECK(beneficiary ~ '^[a-f0-9]{64}$'),
  wallet_generation_id uuid NOT NULL,
  wallet_fingerprint text NOT NULL CHECK(wallet_fingerprint ~ '^[a-f0-9]{64}$'),
  monero_network text NOT NULL CHECK(monero_network='mainnet'),
  account_index bigint NOT NULL CHECK(account_index BETWEEN 0 AND 4294967295),
  address_index bigint NOT NULL CHECK(address_index BETWEEN 0 AND 4294967295),
  status text NOT NULL CHECK(status IN ('reserved','registered')),
  monero_address text UNIQUE CHECK(monero_address IS NULL OR length(monero_address)=95),
  request_digest text NOT NULL CHECK(request_digest ~ '^[a-f0-9]{64}$'),
  reserved_at bigint NOT NULL CHECK(reserved_at>=0),
  registered_at bigint CHECK(registered_at IS NULL OR registered_at>=reserved_at),
  CHECK((status='reserved' AND monero_address IS NULL AND registered_at IS NULL) OR
        (status='registered' AND monero_address IS NOT NULL AND registered_at IS NOT NULL)),
  UNIQUE(wallet_generation_id,account_index,address_index),
  UNIQUE(custodian_id,market_network,market_chain_id,beneficiary,wallet_generation_id)
);
CREATE INDEX monero_deposit_allocations_beneficiary
  ON wk_custody.monero_deposit_allocations(custodian_id,market_network,market_chain_id,beneficiary);
CREATE FUNCTION wk_custody.monero_deposit_allocations_immutable() RETURNS trigger LANGUAGE plpgsql
  SET search_path=wk_custody,pg_catalog AS $$
BEGIN
  IF TG_OP='DELETE' THEN RAISE EXCEPTION 'Monero deposit allocation history cannot be deleted'; END IF;
  IF OLD.status<>'reserved' OR NEW.status<>'registered' OR OLD.monero_address IS NOT NULL OR NEW.monero_address IS NULL OR
     NEW.allocation_id IS DISTINCT FROM OLD.allocation_id OR NEW.request_id IS DISTINCT FROM OLD.request_id OR
     NEW.custodian_id IS DISTINCT FROM OLD.custodian_id OR NEW.market_network IS DISTINCT FROM OLD.market_network OR
     NEW.market_chain_id IS DISTINCT FROM OLD.market_chain_id OR NEW.beneficiary IS DISTINCT FROM OLD.beneficiary OR
     NEW.wallet_generation_id IS DISTINCT FROM OLD.wallet_generation_id OR NEW.wallet_fingerprint IS DISTINCT FROM OLD.wallet_fingerprint OR
     NEW.monero_network IS DISTINCT FROM OLD.monero_network OR NEW.account_index IS DISTINCT FROM OLD.account_index OR
     NEW.address_index IS DISTINCT FROM OLD.address_index OR NEW.request_digest IS DISTINCT FROM OLD.request_digest OR
     NEW.reserved_at IS DISTINCT FROM OLD.reserved_at OR NEW.registered_at IS NULL OR NEW.registered_at<OLD.reserved_at THEN
    RAISE EXCEPTION 'Monero deposit allocation identity is immutable';
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER monero_deposit_allocations_immutable BEFORE UPDATE OR DELETE ON wk_custody.monero_deposit_allocations
  FOR EACH ROW EXECUTE FUNCTION wk_custody.monero_deposit_allocations_immutable();
REVOKE ALL ON wk_custody.monero_deposit_allocations FROM PUBLIC;
REVOKE ALL ON FUNCTION wk_custody.monero_deposit_allocations_immutable() FROM PUBLIC;
`;
export const CUSTODY_SCHEMA_V3_DIGEST = createHash('sha256')
  .update(`${CUSTODY_SCHEMA_V1}\n-- custody migration v2 --\n${CUSTODY_MIGRATION_V2}\n-- custody migration v3 --\n${CUSTODY_MIGRATION_V3}`).digest('hex');
export const CUSTODY_MIGRATION_V4 = `
DO $$
DECLARE old_constraint text;
BEGIN
  SELECT conname INTO old_constraint FROM pg_constraint
    WHERE conrelid='wk_custody.monero_transfer_observations'::regclass AND contype='u'
      AND pg_get_constraintdef(oid)='UNIQUE (wallet_fingerprint, txid, account_index, address_index)';
  IF old_constraint IS NOT NULL THEN
    EXECUTE format('ALTER TABLE wk_custody.monero_transfer_observations DROP CONSTRAINT %I',old_constraint);
  END IF;
END $$;
ALTER TABLE wk_custody.monero_transfer_observations
  ADD COLUMN output_public_key text CHECK(output_public_key IS NULL OR output_public_key ~ '^[a-f0-9]{64}$'),
  ADD COLUMN global_output_index bigint CHECK(global_output_index IS NULL OR global_output_index>=0),
  ADD CONSTRAINT monero_observation_output_identity CHECK(
    (output_public_key IS NULL AND global_output_index IS NULL) OR
    (output_public_key IS NOT NULL AND global_output_index IS NOT NULL AND observation_id=(txid || ':' || output_public_key)));
CREATE UNIQUE INDEX monero_observations_output_key
  ON wk_custody.monero_transfer_observations(wallet_fingerprint,output_public_key)
  WHERE output_public_key IS NOT NULL;
`;
export const CUSTODY_SCHEMA_V4_DIGEST = createHash('sha256')
  .update(`${CUSTODY_SCHEMA_V1}\n-- custody migration v2 --\n${CUSTODY_MIGRATION_V2}\n-- custody migration v3 --\n${CUSTODY_MIGRATION_V3}\n-- custody migration v4 --\n${CUSTODY_MIGRATION_V4}`).digest('hex');
export const CUSTODY_MIGRATION_V5 = `
-- Deferred balance enforcement runs with the schema owner's read access; the
-- fixed search_path and static table reference keep the trigger safe for the
-- narrowly privileged pending-credit coordinator.
ALTER FUNCTION wk_custody.balanced_journal() SECURITY DEFINER;
CREATE TABLE wk_custody.monero_deposit_credit_events(
  credit_id uuid PRIMARY KEY,
  wallet_fingerprint text NOT NULL,
  observation_id text NOT NULL,
  allocation_id uuid NOT NULL REFERENCES wk_custody.monero_deposit_allocations(allocation_id),
  beneficiary text NOT NULL CHECK(beneficiary ~ '^[a-f0-9]{64}$'),
  amount_atomic_xmr numeric(20,0) NOT NULL CHECK(amount_atomic_xmr>0 AND amount_atomic_xmr<=18446744073709551615),
  journal_id text NOT NULL UNIQUE REFERENCES wk_custody.journals(id),
  observation_digest text NOT NULL CHECK(observation_digest ~ '^[a-f0-9]{64}$'),
  last_observation_event_sequence bigint NOT NULL CHECK(last_observation_event_sequence>=0),
  reviewer_owner text NOT NULL CHECK(reviewer_owner ~ '^[a-f0-9]{64}$'),
  reviewer_public_key text NOT NULL CHECK(length(reviewer_public_key) BETWEEN 1 AND 256),
  review_nonce uuid NOT NULL,
  review_expires_at bigint NOT NULL CHECK(review_expires_at>=0),
  review_signature text NOT NULL CHECK(length(review_signature) BETWEEN 1 AND 256),
  review_subject jsonb NOT NULL,
  review_digest text NOT NULL CHECK(review_digest ~ '^[a-f0-9]{64}$'),
  credited_at bigint NOT NULL CHECK(credited_at>=0),
  minimum_confirmations integer NOT NULL CHECK(minimum_confirmations>=10),
  UNIQUE(wallet_fingerprint,observation_id),
  UNIQUE(reviewer_owner,review_nonce),
  FOREIGN KEY(wallet_fingerprint,observation_id)
    REFERENCES wk_custody.monero_transfer_observations(wallet_fingerprint,observation_id)
);
ALTER TABLE wk_custody.monero_observation_events
  ADD CONSTRAINT monero_observation_event_identity UNIQUE(sequence,wallet_fingerprint,observation_id,event_type);
CREATE TABLE wk_custody.monero_deposit_reorg_holds(
  hold_id uuid PRIMARY KEY,
  credit_id uuid NOT NULL REFERENCES wk_custody.monero_deposit_credit_events(credit_id),
  wallet_fingerprint text NOT NULL,
  observation_id text NOT NULL,
  observation_event_sequence bigint NOT NULL REFERENCES wk_custody.monero_observation_events(sequence),
  event_type text NOT NULL CHECK(event_type IN ('changed','not-seen','reappeared')),
  created_at bigint NOT NULL CHECK(created_at>=0),
  UNIQUE(credit_id,observation_event_sequence),
  FOREIGN KEY(observation_event_sequence,wallet_fingerprint,observation_id,event_type)
    REFERENCES wk_custody.monero_observation_events(sequence,wallet_fingerprint,observation_id,event_type)
);
CREATE INDEX monero_deposit_holds_credit ON wk_custody.monero_deposit_reorg_holds(credit_id,observation_event_sequence);
CREATE FUNCTION wk_custody.monero_deposit_credit_history_immutable() RETURNS trigger LANGUAGE plpgsql
  SET search_path=wk_custody,pg_catalog AS $$
BEGIN RAISE EXCEPTION 'Monero deposit credit/reorg history is append-only'; END $$;
CREATE TRIGGER monero_deposit_credit_events_no_update BEFORE UPDATE OR DELETE ON wk_custody.monero_deposit_credit_events
  FOR EACH ROW EXECUTE FUNCTION wk_custody.monero_deposit_credit_history_immutable();
CREATE TRIGGER monero_deposit_reorg_holds_no_update BEFORE UPDATE OR DELETE ON wk_custody.monero_deposit_reorg_holds
  FOR EACH ROW EXECUTE FUNCTION wk_custody.monero_deposit_credit_history_immutable();
REVOKE ALL ON wk_custody.monero_deposit_credit_events,wk_custody.monero_deposit_reorg_holds FROM PUBLIC;
REVOKE ALL ON FUNCTION wk_custody.monero_deposit_credit_history_immutable() FROM PUBLIC;
`;
export const CUSTODY_SCHEMA_V5_DIGEST = createHash('sha256')
  .update(`${CUSTODY_SCHEMA_V1}\n-- custody migration v2 --\n${CUSTODY_MIGRATION_V2}\n-- custody migration v3 --\n${CUSTODY_MIGRATION_V3}\n-- custody migration v4 --\n${CUSTODY_MIGRATION_V4}\n-- custody migration v5 --\n${CUSTODY_MIGRATION_V5}`).digest('hex');
export const CUSTODY_MIGRATION_V6 = `
CREATE TABLE wk_custody.monero_deposit_release_events(
  release_id uuid PRIMARY KEY,
  credit_id uuid NOT NULL UNIQUE REFERENCES wk_custody.monero_deposit_credit_events(credit_id),
  journal_id text NOT NULL UNIQUE REFERENCES wk_custody.journals(id),
  reviewer_owner text NOT NULL CHECK(reviewer_owner ~ '^[a-f0-9]{64}$'),
  reviewer_public_key text NOT NULL CHECK(length(reviewer_public_key) BETWEEN 1 AND 256),
  review_nonce uuid NOT NULL,
  review_expires_at bigint NOT NULL CHECK(review_expires_at>=0),
  review_signature text NOT NULL CHECK(length(review_signature) BETWEEN 1 AND 256),
  review_subject jsonb NOT NULL,
  review_digest text NOT NULL CHECK(review_digest ~ '^[a-f0-9]{64}$'),
  released_at bigint NOT NULL CHECK(released_at>=0),
  UNIQUE(reviewer_owner,review_nonce)
);
CREATE TABLE wk_custody.monero_deposit_reorg_resolutions(
  resolution_id uuid PRIMARY KEY,
  hold_id uuid NOT NULL UNIQUE REFERENCES wk_custody.monero_deposit_reorg_holds(hold_id),
  credit_id uuid NOT NULL REFERENCES wk_custody.monero_deposit_credit_events(credit_id),
  release_id uuid NOT NULL REFERENCES wk_custody.monero_deposit_release_events(release_id),
  resolved_by text NOT NULL CHECK(resolved_by ~ '^[a-f0-9]{64}$'),
  resolved_at bigint NOT NULL CHECK(resolved_at>=0),
  disposition text NOT NULL CHECK(disposition='revalidated-for-release'),
  resolution_kind text NOT NULL CHECK(resolution_kind IN ('release-review','post-release-review')),
  reviewer_public_key text NOT NULL CHECK(length(reviewer_public_key) BETWEEN 1 AND 256),
  review_nonce uuid NOT NULL,
  review_expires_at bigint NOT NULL CHECK(review_expires_at>=0),
  review_signature text NOT NULL CHECK(length(review_signature) BETWEEN 1 AND 256),
  review_subject jsonb NOT NULL,
  review_digest text NOT NULL CHECK(review_digest ~ '^[a-f0-9]{64}$'),
  UNIQUE(hold_id,release_id),
  UNIQUE(resolved_by,review_nonce)
);
CREATE INDEX monero_deposit_resolutions_credit ON wk_custody.monero_deposit_reorg_resolutions(credit_id,hold_id);
CREATE FUNCTION wk_custody.monero_deposit_release_history_immutable() RETURNS trigger LANGUAGE plpgsql
  SET search_path=wk_custody,pg_catalog AS $$
BEGIN RAISE EXCEPTION 'Monero deposit release/resolution history is append-only'; END $$;
CREATE TRIGGER monero_deposit_release_events_no_update BEFORE UPDATE OR DELETE ON wk_custody.monero_deposit_release_events
  FOR EACH ROW EXECUTE FUNCTION wk_custody.monero_deposit_release_history_immutable();
CREATE TRIGGER monero_deposit_reorg_resolutions_no_update BEFORE UPDATE OR DELETE ON wk_custody.monero_deposit_reorg_resolutions
  FOR EACH ROW EXECUTE FUNCTION wk_custody.monero_deposit_release_history_immutable();
REVOKE ALL ON wk_custody.monero_deposit_release_events,wk_custody.monero_deposit_reorg_resolutions FROM PUBLIC;
REVOKE ALL ON FUNCTION wk_custody.monero_deposit_release_history_immutable() FROM PUBLIC;
`;
export const CUSTODY_SCHEMA_V6_DIGEST = createHash('sha256')
  .update(`${CUSTODY_SCHEMA_V1}\n-- custody migration v2 --\n${CUSTODY_MIGRATION_V2}\n-- custody migration v3 --\n${CUSTODY_MIGRATION_V3}\n-- custody migration v4 --\n${CUSTODY_MIGRATION_V4}\n-- custody migration v5 --\n${CUSTODY_MIGRATION_V5}\n-- custody migration v6 --\n${CUSTODY_MIGRATION_V6}`).digest('hex');
export const CUSTODY_MIGRATION_V7 = `
ALTER TABLE wk_custody.monero_deposit_reorg_resolutions
  DROP CONSTRAINT monero_deposit_reorg_resolutions_resolved_by_review_nonce_key;
`;
export const CUSTODY_SCHEMA_V7_DIGEST = createHash('sha256')
  .update(`${CUSTODY_SCHEMA_V1}\n-- custody migration v2 --\n${CUSTODY_MIGRATION_V2}\n-- custody migration v3 --\n${CUSTODY_MIGRATION_V3}\n-- custody migration v4 --\n${CUSTODY_MIGRATION_V4}\n-- custody migration v5 --\n${CUSTODY_MIGRATION_V5}\n-- custody migration v6 --\n${CUSTODY_MIGRATION_V6}\n-- custody migration v7 --\n${CUSTODY_MIGRATION_V7}`).digest('hex');
export const CUSTODY_MIGRATION_V8 = `
CREATE FUNCTION wk_custody.monero_deposit_allocation_insert_guard() RETURNS trigger LANGUAGE plpgsql
  SET search_path=wk_custody,pg_catalog AS $$
BEGIN
  IF NEW.status<>'reserved' OR NEW.monero_address IS NOT NULL OR NEW.registered_at IS NOT NULL THEN
    RAISE EXCEPTION 'Monero deposit allocations must be inserted as address-free reservations';
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER monero_deposit_allocations_insert_reserved
  BEFORE INSERT ON wk_custody.monero_deposit_allocations
  FOR EACH ROW EXECUTE FUNCTION wk_custody.monero_deposit_allocation_insert_guard();
REVOKE ALL ON FUNCTION wk_custody.monero_deposit_allocation_insert_guard() FROM PUBLIC;
`;
export const CUSTODY_SCHEMA_V8_DIGEST = createHash('sha256')
  .update(`${CUSTODY_SCHEMA_V1}\n-- custody migration v2 --\n${CUSTODY_MIGRATION_V2}\n-- custody migration v3 --\n${CUSTODY_MIGRATION_V3}\n-- custody migration v4 --\n${CUSTODY_MIGRATION_V4}\n-- custody migration v5 --\n${CUSTODY_MIGRATION_V5}\n-- custody migration v6 --\n${CUSTODY_MIGRATION_V6}\n-- custody migration v7 --\n${CUSTODY_MIGRATION_V7}\n-- custody migration v8 --\n${CUSTODY_MIGRATION_V8}`).digest('hex');
export const CUSTODY_MIGRATION_V9 = `
CREATE TABLE wk_custody.monero_withdrawal_intents(
  request_id uuid PRIMARY KEY,
  custodian_id uuid NOT NULL,
  market_network text NOT NULL CHECK(market_network='wikideck-randomx-mainnet-1'),
  market_chain_id text NOT NULL CHECK(market_chain_id ~ '^[a-f0-9]{64}$'),
  beneficiary text NOT NULL CHECK(beneficiary ~ '^[a-f0-9]{64}$'),
  destination text NOT NULL CHECK(length(destination)=95),
  amount_atomic_xmr numeric(20,0) NOT NULL CHECK(amount_atomic_xmr>0 AND amount_atomic_xmr<=18446744073709551615),
  max_fee_atomic_xmr numeric(20,0) NOT NULL CHECK(max_fee_atomic_xmr>=0 AND max_fee_atomic_xmr<=18446744073709551615),
  public_key text NOT NULL CHECK(length(public_key) BETWEEN 1 AND 256),
  expires_at bigint NOT NULL CHECK(expires_at>=0),
  request_signature text NOT NULL CHECK(length(request_signature) BETWEEN 1 AND 256),
  request_digest text NOT NULL CHECK(request_digest ~ '^[a-f0-9]{64}$'),
  status text NOT NULL CHECK(status IN ('reserved','cancelled')),
  created_at bigint NOT NULL CHECK(created_at>=0),
  cancel_request_id uuid UNIQUE,
  cancel_public_key text CHECK(cancel_public_key IS NULL OR length(cancel_public_key) BETWEEN 1 AND 256),
  cancel_expires_at bigint CHECK(cancel_expires_at IS NULL OR cancel_expires_at>=0),
  cancel_signature text CHECK(cancel_signature IS NULL OR length(cancel_signature) BETWEEN 1 AND 256),
  cancelled_at bigint CHECK(cancelled_at IS NULL OR cancelled_at>=created_at),
  CHECK((status='reserved' AND cancel_request_id IS NULL AND cancel_signature IS NULL AND cancelled_at IS NULL) OR
        (status='cancelled' AND cancel_request_id IS NOT NULL AND cancel_public_key IS NOT NULL AND
         cancel_expires_at IS NOT NULL AND cancel_signature IS NOT NULL AND cancelled_at IS NOT NULL))
);
CREATE FUNCTION wk_custody.monero_withdrawal_intents_immutable() RETURNS trigger LANGUAGE plpgsql
  SET search_path=wk_custody,pg_catalog AS $$
BEGIN
  IF TG_OP='DELETE' THEN RAISE EXCEPTION 'Monero withdrawal intent history cannot be deleted'; END IF;
  IF OLD.status<>'reserved' OR NEW.status<>'cancelled' OR NEW.request_id IS DISTINCT FROM OLD.request_id OR
     NEW.custodian_id IS DISTINCT FROM OLD.custodian_id OR NEW.market_network IS DISTINCT FROM OLD.market_network OR
     NEW.market_chain_id IS DISTINCT FROM OLD.market_chain_id OR NEW.beneficiary IS DISTINCT FROM OLD.beneficiary OR
     NEW.destination IS DISTINCT FROM OLD.destination OR NEW.amount_atomic_xmr IS DISTINCT FROM OLD.amount_atomic_xmr OR
     NEW.max_fee_atomic_xmr IS DISTINCT FROM OLD.max_fee_atomic_xmr OR NEW.public_key IS DISTINCT FROM OLD.public_key OR
     NEW.expires_at IS DISTINCT FROM OLD.expires_at OR NEW.request_signature IS DISTINCT FROM OLD.request_signature OR
     NEW.request_digest IS DISTINCT FROM OLD.request_digest OR NEW.created_at IS DISTINCT FROM OLD.created_at OR
     NEW.cancel_request_id IS NULL OR NEW.cancel_public_key IS NULL OR NEW.cancel_expires_at IS NULL OR
     NEW.cancel_signature IS NULL OR NEW.cancelled_at IS NULL THEN
    RAISE EXCEPTION 'Monero withdrawal intent identity is immutable';
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER monero_withdrawal_intents_immutable BEFORE UPDATE OR DELETE ON wk_custody.monero_withdrawal_intents
  FOR EACH ROW EXECUTE FUNCTION wk_custody.monero_withdrawal_intents_immutable();
REVOKE ALL ON wk_custody.monero_withdrawal_intents FROM PUBLIC;
REVOKE ALL ON FUNCTION wk_custody.monero_withdrawal_intents_immutable() FROM PUBLIC;
`;
export const CUSTODY_SCHEMA_V9_DIGEST = createHash('sha256')
  .update(`${CUSTODY_SCHEMA_V1}\n-- custody migration v2 --\n${CUSTODY_MIGRATION_V2}\n-- custody migration v3 --\n${CUSTODY_MIGRATION_V3}\n-- custody migration v4 --\n${CUSTODY_MIGRATION_V4}\n-- custody migration v5 --\n${CUSTODY_MIGRATION_V5}\n-- custody migration v6 --\n${CUSTODY_MIGRATION_V6}\n-- custody migration v7 --\n${CUSTODY_MIGRATION_V7}\n-- custody migration v8 --\n${CUSTODY_MIGRATION_V8}\n-- custody migration v9 --\n${CUSTODY_MIGRATION_V9}`).digest('hex');
export const CUSTODY_MIGRATION_V10 = `
CREATE TABLE wk_custody.pilot_custody_identity(
  singleton smallint PRIMARY KEY DEFAULT 1 CHECK(singleton=1),
  version integer NOT NULL CHECK(version=1),
  mode text NOT NULL CHECK(mode='pilot-custody'),
  policy text NOT NULL CHECK(policy='pilot-custody-v1'),
  market_network text NOT NULL CHECK(market_network='wikideck-randomx-pilot-1'),
  market_chain_id text NOT NULL CHECK(market_chain_id ~ '^[a-f0-9]{64}$'),
  monero_network text NOT NULL CHECK(monero_network='mainnet'),
  custodian_id uuid NOT NULL UNIQUE,
  activation_status text NOT NULL CHECK(activation_status='identity-only'),
  initialized_at bigint NOT NULL CHECK(initialized_at>=0),
  UNIQUE(custodian_id,market_network,market_chain_id,monero_network)
);
CREATE FUNCTION wk_custody.pilot_custody_identity_immutable() RETURNS trigger LANGUAGE plpgsql
  SET search_path=wk_custody,pg_catalog AS $$
BEGIN RAISE EXCEPTION 'Pilot custody identity is immutable'; END $$;
CREATE TRIGGER pilot_custody_identity_immutable BEFORE UPDATE OR DELETE ON wk_custody.pilot_custody_identity
  FOR EACH ROW EXECUTE FUNCTION wk_custody.pilot_custody_identity_immutable();
REVOKE ALL ON wk_custody.pilot_custody_identity FROM PUBLIC;
REVOKE ALL ON FUNCTION wk_custody.pilot_custody_identity_immutable() FROM PUBLIC;
`;
export const CUSTODY_SCHEMA_V10_DIGEST = createHash('sha256')
  .update(`${CUSTODY_SCHEMA_V1}\n-- custody migration v2 --\n${CUSTODY_MIGRATION_V2}\n-- custody migration v3 --\n${CUSTODY_MIGRATION_V3}\n-- custody migration v4 --\n${CUSTODY_MIGRATION_V4}\n-- custody migration v5 --\n${CUSTODY_MIGRATION_V5}\n-- custody migration v6 --\n${CUSTODY_MIGRATION_V6}\n-- custody migration v7 --\n${CUSTODY_MIGRATION_V7}\n-- custody migration v8 --\n${CUSTODY_MIGRATION_V8}\n-- custody migration v9 --\n${CUSTODY_MIGRATION_V9}\n-- custody migration v10 --\n${CUSTODY_MIGRATION_V10}`).digest('hex');
export const CUSTODY_MIGRATION_V11 = `
CREATE TABLE wk_custody.pilot_monero_deposit_reservations(
  allocation_id uuid PRIMARY KEY,
  request_id uuid NOT NULL UNIQUE,
  custodian_id uuid NOT NULL,
  market_network text NOT NULL CHECK(market_network='wikideck-randomx-pilot-1'),
  market_chain_id text NOT NULL CHECK(market_chain_id ~ '^[a-f0-9]{64}$'),
  monero_network text NOT NULL CHECK(monero_network='mainnet'),
  beneficiary text NOT NULL CHECK(beneficiary ~ '^[a-f0-9]{64}$'),
  wallet_generation_id uuid NOT NULL,
  wallet_fingerprint text NOT NULL CHECK(wallet_fingerprint ~ '^[a-f0-9]{64}$'),
  account_index bigint NOT NULL CHECK(account_index BETWEEN 0 AND 4294967295),
  address_index bigint NOT NULL CHECK(address_index BETWEEN 0 AND 4294967295),
  request_digest text NOT NULL CHECK(request_digest ~ '^[a-f0-9]{64}$'),
  allocation_digest text NOT NULL CHECK(allocation_digest ~ '^[a-f0-9]{64}$'),
  reserved_at bigint NOT NULL CHECK(reserved_at>=0),
  UNIQUE(wallet_generation_id,account_index,address_index),
  UNIQUE(custodian_id,market_network,market_chain_id,beneficiary,wallet_generation_id),
  FOREIGN KEY(custodian_id,market_network,market_chain_id,monero_network)
    REFERENCES wk_custody.pilot_custody_identity(custodian_id,market_network,market_chain_id,monero_network)
);
CREATE INDEX pilot_deposit_reservations_wallet_index
  ON wk_custody.pilot_monero_deposit_reservations(wallet_generation_id,account_index,address_index);
CREATE FUNCTION wk_custody.pilot_monero_deposit_reservations_immutable() RETURNS trigger LANGUAGE plpgsql
  SET search_path=wk_custody,pg_catalog AS $$
BEGIN RAISE EXCEPTION 'Pilot Monero deposit reservation history is immutable'; END $$;
CREATE TRIGGER pilot_monero_deposit_reservations_immutable
  BEFORE UPDATE OR DELETE ON wk_custody.pilot_monero_deposit_reservations
  FOR EACH ROW EXECUTE FUNCTION wk_custody.pilot_monero_deposit_reservations_immutable();
REVOKE ALL ON wk_custody.pilot_monero_deposit_reservations FROM PUBLIC;
REVOKE ALL ON FUNCTION wk_custody.pilot_monero_deposit_reservations_immutable() FROM PUBLIC;
CREATE FUNCTION wk_custody.pilot_reservation_database_isolated() RETURNS boolean LANGUAGE sql STABLE SECURITY DEFINER
  SET search_path=wk_custody,pg_catalog AS $$
  SELECT NOT (
    EXISTS(SELECT 1 FROM wk_custody.custody_meta) OR
    EXISTS(SELECT 1 FROM wk_custody.accounts) OR
    EXISTS(SELECT 1 FROM wk_custody.journals) OR
    EXISTS(SELECT 1 FROM wk_custody.entries) OR
    EXISTS(SELECT 1 FROM wk_custody.listings) OR
    EXISTS(SELECT 1 FROM wk_custody.orders) OR
    EXISTS(SELECT 1 FROM wk_custody.withdrawals) OR
    EXISTS(SELECT 1 FROM wk_custody.receipts) OR
    EXISTS(SELECT 1 FROM wk_custody.outbox) OR
    EXISTS(SELECT 1 FROM wk_custody.credits) OR
    EXISTS(SELECT 1 FROM wk_custody.monero_observer_state) OR
    EXISTS(SELECT 1 FROM wk_custody.monero_transfer_observations) OR
    EXISTS(SELECT 1 FROM wk_custody.monero_observation_scans) OR
    EXISTS(SELECT 1 FROM wk_custody.monero_deposit_allocations) OR
    EXISTS(SELECT 1 FROM wk_custody.monero_deposit_credit_events) OR
    EXISTS(SELECT 1 FROM wk_custody.monero_deposit_reorg_holds) OR
    EXISTS(SELECT 1 FROM wk_custody.monero_deposit_release_events) OR
    EXISTS(SELECT 1 FROM wk_custody.monero_deposit_reorg_resolutions) OR
    EXISTS(SELECT 1 FROM wk_custody.monero_withdrawal_intents)
  )
$$;
REVOKE ALL ON FUNCTION wk_custody.pilot_reservation_database_isolated() FROM PUBLIC;
`;
export const CUSTODY_SCHEMA_V11_DIGEST = createHash('sha256')
  .update(`${CUSTODY_SCHEMA_V1}\n-- custody migration v2 --\n${CUSTODY_MIGRATION_V2}\n-- custody migration v3 --\n${CUSTODY_MIGRATION_V3}\n-- custody migration v4 --\n${CUSTODY_MIGRATION_V4}\n-- custody migration v5 --\n${CUSTODY_MIGRATION_V5}\n-- custody migration v6 --\n${CUSTODY_MIGRATION_V6}\n-- custody migration v7 --\n${CUSTODY_MIGRATION_V7}\n-- custody migration v8 --\n${CUSTODY_MIGRATION_V8}\n-- custody migration v9 --\n${CUSTODY_MIGRATION_V9}\n-- custody migration v10 --\n${CUSTODY_MIGRATION_V10}\n-- custody migration v11 --\n${CUSTODY_MIGRATION_V11}`).digest('hex');
export const CUSTODY_MIGRATION_V12 = `
DROP TRIGGER pilot_monero_deposit_reservations_immutable ON wk_custody.pilot_monero_deposit_reservations;
DROP FUNCTION wk_custody.pilot_monero_deposit_reservations_immutable();
ALTER TABLE wk_custody.pilot_monero_deposit_reservations
  ADD COLUMN status text NOT NULL DEFAULT 'reserved' CHECK(status IN ('reserved','registered')),
  ADD COLUMN monero_address text CHECK(monero_address IS NULL OR length(monero_address)=95),
  ADD COLUMN registered_at bigint CHECK(registered_at IS NULL OR registered_at>=reserved_at),
  ADD CONSTRAINT pilot_deposit_reservation_registration_state CHECK(
    (status='reserved' AND monero_address IS NULL AND registered_at IS NULL) OR
    (status='registered' AND monero_address IS NOT NULL AND registered_at IS NOT NULL));
CREATE UNIQUE INDEX pilot_monero_deposit_address_unique
  ON wk_custody.pilot_monero_deposit_reservations(monero_address) WHERE monero_address IS NOT NULL;
CREATE FUNCTION wk_custody.pilot_monero_deposit_registration_guard() RETURNS trigger LANGUAGE plpgsql
  SET search_path=wk_custody,pg_catalog AS $$
BEGIN
  IF TG_OP='DELETE' THEN RAISE EXCEPTION 'Pilot Monero deposit reservations cannot be deleted'; END IF;
  IF OLD.status<>'reserved' OR NEW.status<>'registered' OR NEW.monero_address IS NULL OR NEW.registered_at IS NULL OR
     NEW.registered_at<OLD.reserved_at OR NEW.allocation_id IS DISTINCT FROM OLD.allocation_id OR
     NEW.request_id IS DISTINCT FROM OLD.request_id OR NEW.custodian_id IS DISTINCT FROM OLD.custodian_id OR
     NEW.market_network IS DISTINCT FROM OLD.market_network OR NEW.market_chain_id IS DISTINCT FROM OLD.market_chain_id OR
     NEW.monero_network IS DISTINCT FROM OLD.monero_network OR NEW.beneficiary IS DISTINCT FROM OLD.beneficiary OR
     NEW.wallet_generation_id IS DISTINCT FROM OLD.wallet_generation_id OR NEW.wallet_fingerprint IS DISTINCT FROM OLD.wallet_fingerprint OR
     NEW.account_index IS DISTINCT FROM OLD.account_index OR NEW.address_index IS DISTINCT FROM OLD.address_index OR
     NEW.request_digest IS DISTINCT FROM OLD.request_digest OR NEW.allocation_digest IS DISTINCT FROM OLD.allocation_digest OR
     NEW.reserved_at IS DISTINCT FROM OLD.reserved_at THEN
    RAISE EXCEPTION 'Pilot Monero reservation permits only one-time address registration';
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER pilot_monero_deposit_registration_guard
  BEFORE UPDATE OR DELETE ON wk_custody.pilot_monero_deposit_reservations
  FOR EACH ROW EXECUTE FUNCTION wk_custody.pilot_monero_deposit_registration_guard();
REVOKE ALL ON FUNCTION wk_custody.pilot_monero_deposit_registration_guard() FROM PUBLIC;
`;
export const CUSTODY_SCHEMA_V12_DIGEST = createHash('sha256')
  .update(`${CUSTODY_SCHEMA_V1}\n-- custody migration v2 --\n${CUSTODY_MIGRATION_V2}\n-- custody migration v3 --\n${CUSTODY_MIGRATION_V3}\n-- custody migration v4 --\n${CUSTODY_MIGRATION_V4}\n-- custody migration v5 --\n${CUSTODY_MIGRATION_V5}\n-- custody migration v6 --\n${CUSTODY_MIGRATION_V6}\n-- custody migration v7 --\n${CUSTODY_MIGRATION_V7}\n-- custody migration v8 --\n${CUSTODY_MIGRATION_V8}\n-- custody migration v9 --\n${CUSTODY_MIGRATION_V9}\n-- custody migration v10 --\n${CUSTODY_MIGRATION_V10}\n-- custody migration v11 --\n${CUSTODY_MIGRATION_V11}\n-- custody migration v12 --\n${CUSTODY_MIGRATION_V12}`).digest('hex');
export const CUSTODY_MIGRATION_V13 = `
DROP TRIGGER pilot_monero_deposit_registration_guard ON wk_custody.pilot_monero_deposit_reservations;
DROP FUNCTION wk_custody.pilot_monero_deposit_registration_guard();
ALTER TABLE wk_custody.pilot_monero_deposit_reservations
  ADD COLUMN create_started_at bigint CHECK(create_started_at IS NULL OR create_started_at>=reserved_at);
UPDATE wk_custody.pilot_monero_deposit_reservations
  SET create_started_at=registered_at WHERE status='registered';
ALTER TABLE wk_custody.pilot_monero_deposit_reservations
  DROP CONSTRAINT pilot_monero_deposit_reservations_status_check,
  ADD CONSTRAINT pilot_monero_deposit_reservations_status_check CHECK(status IN ('reserved','creating','registered')),
  DROP CONSTRAINT pilot_deposit_reservation_registration_state,
  ADD CONSTRAINT pilot_deposit_reservation_registration_state CHECK(
    (status='reserved' AND create_started_at IS NULL AND monero_address IS NULL AND registered_at IS NULL) OR
    (status='creating' AND create_started_at IS NOT NULL AND monero_address IS NULL AND registered_at IS NULL) OR
    (status='registered' AND create_started_at IS NOT NULL AND monero_address IS NOT NULL AND
      registered_at IS NOT NULL AND registered_at>=create_started_at));
CREATE FUNCTION wk_custody.pilot_monero_deposit_registration_guard() RETURNS trigger LANGUAGE plpgsql
  SET search_path=wk_custody,pg_catalog AS $$
BEGIN
  IF TG_OP='DELETE' THEN RAISE EXCEPTION 'Pilot Monero deposit reservations cannot be deleted'; END IF;
  IF NEW.allocation_id IS DISTINCT FROM OLD.allocation_id OR NEW.request_id IS DISTINCT FROM OLD.request_id OR
     NEW.custodian_id IS DISTINCT FROM OLD.custodian_id OR NEW.market_network IS DISTINCT FROM OLD.market_network OR
     NEW.market_chain_id IS DISTINCT FROM OLD.market_chain_id OR NEW.monero_network IS DISTINCT FROM OLD.monero_network OR
     NEW.beneficiary IS DISTINCT FROM OLD.beneficiary OR NEW.wallet_generation_id IS DISTINCT FROM OLD.wallet_generation_id OR
     NEW.wallet_fingerprint IS DISTINCT FROM OLD.wallet_fingerprint OR NEW.account_index IS DISTINCT FROM OLD.account_index OR
     NEW.address_index IS DISTINCT FROM OLD.address_index OR NEW.request_digest IS DISTINCT FROM OLD.request_digest OR
     NEW.allocation_digest IS DISTINCT FROM OLD.allocation_digest OR NEW.reserved_at IS DISTINCT FROM OLD.reserved_at THEN
    RAISE EXCEPTION 'Pilot Monero reservation terms are immutable';
  END IF;
  IF OLD.status='reserved' AND NEW.status='creating' AND OLD.create_started_at IS NULL AND
     NEW.create_started_at IS NOT NULL AND NEW.monero_address IS NULL AND NEW.registered_at IS NULL THEN
    RETURN NEW;
  END IF;
  IF OLD.status='creating' AND NEW.status='registered' AND OLD.create_started_at IS NOT NULL AND
     NEW.create_started_at IS NOT DISTINCT FROM OLD.create_started_at AND NEW.monero_address IS NOT NULL AND
     OLD.monero_address IS NULL AND NEW.registered_at IS NOT NULL AND NEW.registered_at>=OLD.create_started_at THEN
    RETURN NEW;
  END IF;
  RAISE EXCEPTION 'Pilot Monero reservation permits only one-time create intent and address registration';
END $$;
REVOKE ALL ON FUNCTION wk_custody.pilot_monero_deposit_registration_guard() FROM PUBLIC;
`;
export const CUSTODY_SCHEMA_V13_DIGEST = createHash('sha256')
  .update(`${CUSTODY_SCHEMA_V1}\n-- custody migration v2 --\n${CUSTODY_MIGRATION_V2}\n-- custody migration v3 --\n${CUSTODY_MIGRATION_V3}\n-- custody migration v4 --\n${CUSTODY_MIGRATION_V4}\n-- custody migration v5 --\n${CUSTODY_MIGRATION_V5}\n-- custody migration v6 --\n${CUSTODY_MIGRATION_V6}\n-- custody migration v7 --\n${CUSTODY_MIGRATION_V7}\n-- custody migration v8 --\n${CUSTODY_MIGRATION_V8}\n-- custody migration v9 --\n${CUSTODY_MIGRATION_V9}\n-- custody migration v10 --\n${CUSTODY_MIGRATION_V10}\n-- custody migration v11 --\n${CUSTODY_MIGRATION_V11}\n-- custody migration v12 --\n${CUSTODY_MIGRATION_V12}\n-- custody migration v13 --\n${CUSTODY_MIGRATION_V13}`).digest('hex');
export const CUSTODY_MIGRATION_V14 = `
CREATE TABLE wk_custody.pilot_monero_observer_state(
  wallet_generation_id uuid PRIMARY KEY,
  wallet_fingerprint text NOT NULL CHECK(wallet_fingerprint ~ '^[a-f0-9]{64}$'),
  network text NOT NULL CHECK(network='mainnet'),
  scan_height bigint NOT NULL CHECK(scan_height>=0),
  updated_at bigint NOT NULL CHECK(updated_at>=0)
);
CREATE TABLE wk_custody.pilot_monero_observation_scans(
  scan_id uuid PRIMARY KEY,
  wallet_generation_id uuid NOT NULL REFERENCES wk_custody.pilot_monero_observer_state(wallet_generation_id),
  from_height bigint NOT NULL CHECK(from_height>=0),
  through_height bigint NOT NULL CHECK(through_height>=from_height),
  payload_digest text NOT NULL CHECK(payload_digest ~ '^[a-f0-9]{64}$'),
  completed_at bigint NOT NULL CHECK(completed_at>=0)
);
CREATE INDEX pilot_monero_scans_wallet_height
  ON wk_custody.pilot_monero_observation_scans(wallet_generation_id,through_height DESC);
CREATE TABLE wk_custody.pilot_monero_observations(
  wallet_generation_id uuid NOT NULL REFERENCES wk_custody.pilot_monero_observer_state(wallet_generation_id),
  observation_id text NOT NULL,
  allocation_id uuid REFERENCES wk_custody.pilot_monero_deposit_reservations(allocation_id),
  classification text NOT NULL CHECK(classification IN ('assigned','unallocated')),
  txid text NOT NULL CHECK(txid ~ '^[a-f0-9]{64}$'),
  monero_address text NOT NULL CHECK(length(monero_address)=95),
  amount_atomic_xmr numeric(20,0) NOT NULL CHECK(amount_atomic_xmr>0 AND amount_atomic_xmr<=18446744073709551615),
  block_height bigint NOT NULL CHECK(block_height>0),
  wallet_height bigint NOT NULL CHECK(wallet_height>=block_height),
  confirmations bigint NOT NULL CHECK(confirmations=wallet_height-block_height+1),
  timestamp bigint NOT NULL CHECK(timestamp>=0),
  unlock_time text NOT NULL CHECK(unlock_time ~ '^(0|[1-9][0-9]{0,19})$'),
  account_index bigint NOT NULL CHECK(account_index BETWEEN 0 AND 4294967295),
  address_index bigint NOT NULL CHECK(address_index BETWEEN 0 AND 4294967295),
  output_public_key text NOT NULL CHECK(output_public_key ~ '^[a-f0-9]{64}$'),
  global_output_index bigint NOT NULL CHECK(global_output_index>=0),
  locked boolean NOT NULL,
  double_spend_seen boolean NOT NULL,
  payload_digest text NOT NULL CHECK(payload_digest ~ '^[a-f0-9]{64}$'),
  currently_present boolean NOT NULL DEFAULT true,
  last_seen_scan_id uuid NOT NULL REFERENCES wk_custody.pilot_monero_observation_scans(scan_id),
  first_observed_at bigint NOT NULL CHECK(first_observed_at>=0),
  last_observed_at bigint NOT NULL CHECK(last_observed_at>=first_observed_at),
  PRIMARY KEY(wallet_generation_id,observation_id),
  UNIQUE(wallet_generation_id,output_public_key),
  UNIQUE(wallet_generation_id,txid,account_index,address_index,output_public_key),
  CHECK((classification='assigned' AND allocation_id IS NOT NULL) OR
        (classification='unallocated' AND allocation_id IS NULL)),
  CHECK(observation_id=(txid || ':' || output_public_key))
);
CREATE FUNCTION wk_custody.pilot_monero_observation_guard() RETURNS trigger LANGUAGE plpgsql
  SET search_path=wk_custody,pg_catalog AS $$
DECLARE primary_fingerprint text;
BEGIN
  SELECT wallet_fingerprint INTO STRICT primary_fingerprint FROM wk_custody.pilot_monero_observer_state
    WHERE wallet_generation_id=NEW.wallet_generation_id;
  IF NEW.classification='assigned' THEN
    IF NEW.allocation_id IS NULL OR NOT EXISTS(
      SELECT 1 FROM wk_custody.pilot_monero_deposit_reservations r
      WHERE r.allocation_id=NEW.allocation_id AND r.wallet_generation_id=NEW.wallet_generation_id AND
        r.wallet_fingerprint=primary_fingerprint AND r.status='registered' AND r.monero_address=NEW.monero_address AND
        r.account_index=NEW.account_index AND r.address_index=NEW.address_index AND r.monero_network='mainnet') THEN
      RAISE EXCEPTION 'Pilot observation does not match a registered wallet reservation';
    END IF;
  ELSIF NEW.allocation_id IS NOT NULL THEN
    RAISE EXCEPTION 'Unallocated pilot observation cannot name a reservation';
  END IF;
  IF TG_OP='UPDATE' AND (
      NEW.wallet_generation_id IS DISTINCT FROM OLD.wallet_generation_id OR
      NEW.observation_id IS DISTINCT FROM OLD.observation_id OR NEW.allocation_id IS DISTINCT FROM OLD.allocation_id OR
      NEW.classification IS DISTINCT FROM OLD.classification OR NEW.txid IS DISTINCT FROM OLD.txid OR
      NEW.monero_address IS DISTINCT FROM OLD.monero_address OR NEW.account_index IS DISTINCT FROM OLD.account_index OR
      NEW.address_index IS DISTINCT FROM OLD.address_index OR NEW.output_public_key IS DISTINCT FROM OLD.output_public_key OR
      NEW.global_output_index IS DISTINCT FROM OLD.global_output_index OR NEW.first_observed_at IS DISTINCT FROM OLD.first_observed_at) THEN
    RAISE EXCEPTION 'Pilot observation identity is immutable';
  END IF;
  RETURN NEW;
END $$;
CREATE TRIGGER pilot_monero_observations_guard BEFORE INSERT OR UPDATE ON wk_custody.pilot_monero_observations
  FOR EACH ROW EXECUTE FUNCTION wk_custody.pilot_monero_observation_guard();
CREATE INDEX pilot_monero_observations_height
  ON wk_custody.pilot_monero_observations(wallet_generation_id,block_height,observation_id);
CREATE TABLE wk_custody.pilot_monero_observation_events(
  sequence bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  wallet_generation_id uuid NOT NULL,
  observation_id text NOT NULL,
  scan_id uuid NOT NULL REFERENCES wk_custody.pilot_monero_observation_scans(scan_id),
  event_type text NOT NULL CHECK(event_type IN ('seen','changed','reappeared','not-seen')),
  payload_digest text NOT NULL CHECK(payload_digest ~ '^[a-f0-9]{64}$'),
  wallet_height bigint NOT NULL CHECK(wallet_height>=0),
  observed_at bigint NOT NULL CHECK(observed_at>=0),
  UNIQUE(wallet_generation_id,observation_id,scan_id,event_type),
  FOREIGN KEY(wallet_generation_id,observation_id)
    REFERENCES wk_custody.pilot_monero_observations(wallet_generation_id,observation_id)
);
CREATE FUNCTION wk_custody.pilot_monero_observation_history_immutable() RETURNS trigger LANGUAGE plpgsql
  SET search_path=wk_custody,pg_catalog AS $$
BEGIN RAISE EXCEPTION 'Pilot Monero observation history is append-only'; END $$;
CREATE TRIGGER pilot_monero_observation_scans_no_update BEFORE UPDATE OR DELETE ON wk_custody.pilot_monero_observation_scans
  FOR EACH ROW EXECUTE FUNCTION wk_custody.pilot_monero_observation_history_immutable();
CREATE TRIGGER pilot_monero_observation_events_no_update BEFORE UPDATE OR DELETE ON wk_custody.pilot_monero_observation_events
  FOR EACH ROW EXECUTE FUNCTION wk_custody.pilot_monero_observation_history_immutable();
REVOKE ALL ON FUNCTION wk_custody.pilot_monero_observation_guard() FROM PUBLIC;
REVOKE ALL ON wk_custody.pilot_monero_observer_state,wk_custody.pilot_monero_observation_scans,
  wk_custody.pilot_monero_observations,wk_custody.pilot_monero_observation_events FROM PUBLIC;
REVOKE ALL ON FUNCTION wk_custody.pilot_monero_observation_history_immutable() FROM PUBLIC;
`;
export const CUSTODY_SCHEMA_V14_DIGEST = createHash('sha256')
  .update(`${CUSTODY_SCHEMA_V1}\n-- custody migration v2 --\n${CUSTODY_MIGRATION_V2}\n-- custody migration v3 --\n${CUSTODY_MIGRATION_V3}\n-- custody migration v4 --\n${CUSTODY_MIGRATION_V4}\n-- custody migration v5 --\n${CUSTODY_MIGRATION_V5}\n-- custody migration v6 --\n${CUSTODY_MIGRATION_V6}\n-- custody migration v7 --\n${CUSTODY_MIGRATION_V7}\n-- custody migration v8 --\n${CUSTODY_MIGRATION_V8}\n-- custody migration v9 --\n${CUSTODY_MIGRATION_V9}\n-- custody migration v10 --\n${CUSTODY_MIGRATION_V10}\n-- custody migration v11 --\n${CUSTODY_MIGRATION_V11}\n-- custody migration v12 --\n${CUSTODY_MIGRATION_V12}\n-- custody migration v13 --\n${CUSTODY_MIGRATION_V13}\n-- custody migration v14 --\n${CUSTODY_MIGRATION_V14}`).digest('hex');
/** Retained alias for callers that need the initial schema definition in disposable fixtures. */
export const CUSTODY_SCHEMA = CUSTODY_SCHEMA_V1;
export const CUSTODY_SCHEMA_DIGEST = CUSTODY_SCHEMA_V10_DIGEST;

/** Explicit operator migration; the HTTP runtime never creates or upgrades schema. */
export async function migrateCustody(pool: Pool): Promise<void> {
  const c = await pool.connect();
  try {
    await c.query('BEGIN');
    await c.query('SELECT pg_advisory_xact_lock($1::bigint)', [CUSTODY_WRITE_LOCK]);
    const exists = await c.query("SELECT to_regclass('wk_custody.schema_versions') AS name");
    let current = 0;
    if (!exists.rows[0].name) {
      await c.query(CUSTODY_SCHEMA_V1);
      await c.query('INSERT INTO wk_custody.schema_versions VALUES(1,$1)', [CUSTODY_SCHEMA_V1_DIGEST]);
      current = 1;
    } else current = await verifyCustodySchemaHistory(c);
    if (current === 1) {
      await c.query(CUSTODY_MIGRATION_V2);
      await c.query('INSERT INTO wk_custody.schema_versions VALUES(2,$1)', [CUSTODY_SCHEMA_V2_DIGEST]);
      current = 2;
    }
    if (current === 2) {
      await c.query(CUSTODY_MIGRATION_V3);
      await c.query('INSERT INTO wk_custody.schema_versions VALUES(3,$1)', [CUSTODY_SCHEMA_V3_DIGEST]);
      current = 3;
    }
    if (current === 3) {
      await c.query(CUSTODY_MIGRATION_V4);
      await c.query('INSERT INTO wk_custody.schema_versions VALUES(4,$1)', [CUSTODY_SCHEMA_V4_DIGEST]);
      current = 4;
    }
    if (current === 4) {
      await c.query(CUSTODY_MIGRATION_V5);
      await c.query('INSERT INTO wk_custody.schema_versions VALUES(5,$1)', [CUSTODY_SCHEMA_V5_DIGEST]);
      current = 5;
    }
    if (current === 5) {
      await c.query(CUSTODY_MIGRATION_V6);
      await c.query('INSERT INTO wk_custody.schema_versions VALUES(6,$1)', [CUSTODY_SCHEMA_V6_DIGEST]);
      current = 6;
    }
    if (current === 6) {
      await c.query(CUSTODY_MIGRATION_V7);
      await c.query('INSERT INTO wk_custody.schema_versions VALUES(7,$1)', [CUSTODY_SCHEMA_V7_DIGEST]);
      current = 7;
    }
    if (current === 7) {
      await c.query(CUSTODY_MIGRATION_V8);
      await c.query('INSERT INTO wk_custody.schema_versions VALUES(8,$1)', [CUSTODY_SCHEMA_V8_DIGEST]);
      current = 8;
    }
    if (current === 8) {
      await c.query(CUSTODY_MIGRATION_V9);
      await c.query('INSERT INTO wk_custody.schema_versions VALUES(9,$1)', [CUSTODY_SCHEMA_V9_DIGEST]);
      current = 9;
    }
    if (current === 9) {
      await c.query(CUSTODY_MIGRATION_V10);
      await c.query('INSERT INTO wk_custody.schema_versions VALUES(10,$1)', [CUSTODY_SCHEMA_V10_DIGEST]);
      current = 10;
    }
    if (current === 10) {
      await c.query(CUSTODY_MIGRATION_V11);
      await c.query('INSERT INTO wk_custody.schema_versions VALUES(11,$1)', [CUSTODY_SCHEMA_V11_DIGEST]);
      current = 11;
    }
    if (current === 11) {
      await c.query(CUSTODY_MIGRATION_V12);
      await c.query('INSERT INTO wk_custody.schema_versions VALUES(12,$1)', [CUSTODY_SCHEMA_V12_DIGEST]);
      current = 12;
    }
    if (current === 12) {
      await c.query(CUSTODY_MIGRATION_V13);
      await c.query('INSERT INTO wk_custody.schema_versions VALUES(13,$1)', [CUSTODY_SCHEMA_V13_DIGEST]);
      current = 13;
    }
    if (current === 13) {
      await c.query(CUSTODY_MIGRATION_V14);
      await c.query('INSERT INTO wk_custody.schema_versions VALUES(14,$1)', [CUSTODY_SCHEMA_V14_DIGEST]);
    }
    if (current > CUSTODY_PG_VERSION) throw new Error('Custody PostgreSQL schema is newer than this application');
    await verifyCustodySchema(c);
    await c.query('COMMIT');
  } catch (e) { await c.query('ROLLBACK').catch(() => {}); throw e; }
  finally { c.release(); }
}
export async function verifyCustodySchema(c: PoolClient): Promise<void> {
  const version = await verifyCustodySchemaHistory(c);
  if (version !== CUSTODY_PG_VERSION) throw new Error('Custody PostgreSQL schema migration required');
}
async function verifyCustodySchemaHistory(c: PoolClient): Promise<number> {
  const rows = await c.query('SELECT version,digest FROM wk_custody.schema_versions ORDER BY version');
  const expected = [CUSTODY_SCHEMA_V1_DIGEST, CUSTODY_SCHEMA_V2_DIGEST, CUSTODY_SCHEMA_V3_DIGEST,
    CUSTODY_SCHEMA_V4_DIGEST, CUSTODY_SCHEMA_V5_DIGEST, CUSTODY_SCHEMA_V6_DIGEST, CUSTODY_SCHEMA_V7_DIGEST,
    CUSTODY_SCHEMA_V8_DIGEST, CUSTODY_SCHEMA_V9_DIGEST, CUSTODY_SCHEMA_V10_DIGEST, CUSTODY_SCHEMA_V11_DIGEST,
    CUSTODY_SCHEMA_V12_DIGEST, CUSTODY_SCHEMA_V13_DIGEST, CUSTODY_SCHEMA_V14_DIGEST];
  if (!rows.rowCount || rows.rowCount > expected.length || rows.rows.some((row, index) =>
    Number(row.version) !== index + 1 || row.digest !== expected[index])) {
    throw new Error('Custody PostgreSQL schema version/digest history mismatch; explicit migration required');
  }
  return Number(rows.rows.at(-1).version);
}

/** Role must already exist (operator creates credentials out-of-band); never accept SQL identifiers from HTTP. */
export async function grantCustodyRuntime(pool: Pool, role: string): Promise<void> {
  if (!/^[a-z][a-z0-9_]{0,62}$/.test(role)) throw new Error('Invalid runtime role identifier');
  const name = `"${role}"`;
  const c = await pool.connect();
  try {
    await c.query('BEGIN');
    await verifyCustodySchema(c);
    const r = await c.query('SELECT rolsuper,rolcreatedb,rolcreaterole,rolreplication,rolbypassrls FROM pg_roles WHERE rolname=$1', [role]);
    if (!r.rowCount || Object.values(r.rows[0]).some(Boolean)) throw new Error('Runtime role must exist without administrative privileges');
    const owner = await c.query("SELECT pg_get_userbyid(nspowner) AS owner FROM pg_namespace WHERE nspname='wk_custody'");
    if (owner.rows[0].owner === role) throw new Error('Runtime role must not own the custody schema');
    await c.query(`GRANT USAGE ON SCHEMA wk_custody TO ${name};
      GRANT SELECT ON ALL TABLES IN SCHEMA wk_custody TO ${name};
      GRANT USAGE,SELECT ON ALL SEQUENCES IN SCHEMA wk_custody TO ${name};
      GRANT INSERT(id,normal) ON wk_custody.accounts TO ${name};
      GRANT INSERT(id,kind,created) ON wk_custody.journals TO ${name};
      GRANT INSERT ON wk_custody.entries,wk_custody.receipts,wk_custody.credits,wk_custody.custody_meta TO ${name};
      GRANT INSERT,UPDATE ON wk_custody.listings,wk_custody.orders,wk_custody.withdrawals,wk_custody.outbox TO ${name};`);
    await c.query(`REVOKE ALL PRIVILEGES ON wk_custody.monero_observer_state,wk_custody.monero_transfer_observations,
      wk_custody.monero_observation_events,wk_custody.monero_observation_scans FROM ${name};
      REVOKE ALL PRIVILEGES ON wk_custody.monero_deposit_allocations FROM ${name};
      REVOKE ALL PRIVILEGES ON wk_custody.monero_deposit_credit_events,wk_custody.monero_deposit_reorg_holds,
        wk_custody.monero_deposit_release_events,wk_custody.monero_deposit_reorg_resolutions FROM ${name};
      GRANT SELECT ON wk_custody.monero_deposit_credit_events,wk_custody.monero_deposit_reorg_holds,
        wk_custody.monero_deposit_release_events,wk_custody.monero_deposit_reorg_resolutions TO ${name};
      REVOKE ALL PRIVILEGES ON SEQUENCE wk_custody.monero_observation_events_sequence_seq FROM ${name};`);
    await c.query('COMMIT');
  } catch (e) { await c.query('ROLLBACK').catch(() => {}); throw e; }
  finally { c.release(); }
}

/** Separate least-privilege role for private wallet observations; it cannot read balances/journals or post credits. */
export async function grantMoneroObserverRuntime(pool: Pool, role: string, custodyRuntimeRole = 'custody_runtime'): Promise<void> {
  if (!/^[a-z][a-z0-9_]{0,62}$/.test(role)) throw new Error('Invalid observer role identifier');
  if (role === custodyRuntimeRole) throw new Error('Monero observer requires a separate role from custody runtime');
  const name = `"${role}"`;
  const c = await pool.connect();
  try {
    await c.query('BEGIN'); await verifyCustodySchema(c);
    const r = await c.query('SELECT rolsuper,rolcreatedb,rolcreaterole,rolreplication,rolbypassrls FROM pg_roles WHERE rolname=$1', [role]);
    if (!r.rowCount || Object.values(r.rows[0]).some(Boolean)) throw new Error('Observer role must exist without administrative privileges');
    const memberships = await c.query(`SELECT 1 FROM pg_auth_members WHERE member=(SELECT oid FROM pg_roles WHERE rolname=$1) LIMIT 1`, [role]);
    if (memberships.rowCount) throw new Error('Observer role must not inherit privileges through role membership');
    const owner = await c.query("SELECT pg_get_userbyid(nspowner) AS owner FROM pg_namespace WHERE nspname='wk_custody'");
    if (owner.rows[0].owner === role) throw new Error('Observer role must not own the custody schema');
    await c.query(`GRANT USAGE ON SCHEMA wk_custody TO ${name};
      REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA wk_custody FROM ${name};
      REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA wk_custody FROM ${name};
      GRANT SELECT,INSERT,UPDATE ON wk_custody.monero_observer_state,wk_custody.monero_transfer_observations TO ${name};
      GRANT SELECT,INSERT ON wk_custody.monero_observation_events,wk_custody.monero_observation_scans TO ${name};
      GRANT USAGE,SELECT ON SEQUENCE wk_custody.monero_observation_events_sequence_seq TO ${name};`);
    await c.query('COMMIT');
  } catch (error) { await c.query('ROLLBACK').catch(() => {}); throw error; }
  finally { c.release(); }
}

/** Separate least-privilege role for reviewed pending-credit posting and deposit reorg evidence. */
export async function grantMoneroDepositCreditRuntime(pool: Pool, role: string,
  custodyRuntimeRole = 'custody_runtime', observerRole = 'monero_observer',
  allocatorRole = 'monero_deposit_allocator'): Promise<void> {
  if (!/^[a-z][a-z0-9_]{0,62}$/.test(role)) throw new Error('Invalid deposit-credit role identifier');
  if ([custodyRuntimeRole, observerRole, allocatorRole].includes(role)) {
    throw new Error('Deposit-credit coordinator requires a separate role from custody runtime, observer, and allocator');
  }
  const name = `"${role}"`;
  const c = await pool.connect();
  try {
    await c.query('BEGIN'); await verifyCustodySchema(c);
    const r = await c.query('SELECT rolsuper,rolcreatedb,rolcreaterole,rolreplication,rolbypassrls FROM pg_roles WHERE rolname=$1', [role]);
    if (!r.rowCount || Object.values(r.rows[0]).some(Boolean)) throw new Error('Deposit-credit role must exist without administrative privileges');
    const memberships = await c.query('SELECT 1 FROM pg_auth_members WHERE member=(SELECT oid FROM pg_roles WHERE rolname=$1) LIMIT 1', [role]);
    if (memberships.rowCount) throw new Error('Deposit-credit role must not inherit privileges through role membership');
    const owner = await c.query("SELECT pg_get_userbyid(nspowner) AS owner FROM pg_namespace WHERE nspname='wk_custody'");
    if (owner.rows[0].owner === role) throw new Error('Deposit-credit role must not own the custody schema');
    await c.query(`GRANT USAGE ON SCHEMA wk_custody TO ${name};
      REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA wk_custody FROM ${name};
      REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA wk_custody FROM ${name};
      GRANT SELECT ON wk_custody.custody_meta,wk_custody.monero_observer_state,wk_custody.monero_transfer_observations,
        wk_custody.monero_observation_events,wk_custody.monero_deposit_allocations,
        wk_custody.monero_deposit_credit_events,wk_custody.monero_deposit_reorg_holds,
        wk_custody.monero_deposit_release_events,wk_custody.monero_deposit_reorg_resolutions TO ${name};
      GRANT SELECT(id,normal) ON wk_custody.accounts TO ${name};
      GRANT INSERT(id,normal) ON wk_custody.accounts TO ${name};
      GRANT INSERT(id,kind,created) ON wk_custody.journals TO ${name};
      GRANT INSERT ON wk_custody.entries,wk_custody.monero_deposit_credit_events,wk_custody.monero_deposit_reorg_holds,
        wk_custody.monero_deposit_release_events,wk_custody.monero_deposit_reorg_resolutions TO ${name};`);
    await c.query('COMMIT');
  } catch (error) { await c.query('ROLLBACK').catch(() => {}); throw error; }
  finally { c.release(); }
}

/** Separate API role for immutable customer-bound subaddress allocation; it cannot read balances or post journals. */
export async function grantMoneroDepositAllocatorRuntime(pool: Pool, role: string,
  custodyRuntimeRole = 'custody_runtime', observerRole = 'monero_observer',
  creditRole = 'monero_deposit_credit'): Promise<void> {
  if (!/^[a-z][a-z0-9_]{0,62}$/.test(role)) throw new Error('Invalid deposit-allocator role identifier');
  if ([custodyRuntimeRole, observerRole, creditRole].includes(role)) {
    throw new Error('Deposit allocator requires a separate role from custody, observer, and credit roles');
  }
  const name = `"${role}"`;
  const c = await pool.connect();
  try {
    await c.query('BEGIN'); await verifyCustodySchema(c);
    const r = await c.query('SELECT rolsuper,rolcreatedb,rolcreaterole,rolreplication,rolbypassrls FROM pg_roles WHERE rolname=$1', [role]);
    if (!r.rowCount || Object.values(r.rows[0]).some(Boolean)) throw new Error('Deposit allocator role must exist without administrative privileges');
    const memberships = await c.query('SELECT 1 FROM pg_auth_members WHERE member=(SELECT oid FROM pg_roles WHERE rolname=$1) LIMIT 1', [role]);
    if (memberships.rowCount) throw new Error('Deposit allocator role must not inherit privileges through role membership');
    const owner = await c.query("SELECT pg_get_userbyid(nspowner) AS owner FROM pg_namespace WHERE nspname='wk_custody'");
    if (owner.rows[0].owner === role) throw new Error('Deposit allocator role must not own the custody schema');
    await c.query(`GRANT USAGE ON SCHEMA wk_custody TO ${name};
      REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA wk_custody FROM ${name};
      REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA wk_custody FROM ${name};
      GRANT SELECT,INSERT ON wk_custody.monero_deposit_allocations TO ${name};
      GRANT UPDATE(status,monero_address,registered_at) ON wk_custody.monero_deposit_allocations TO ${name};`);
    await c.query('COMMIT');
  } catch (error) { await c.query('ROLLBACK').catch(() => {}); throw error; }
  finally { c.release(); }
}

/** Pilot reservation-only role. It can allocate durable indexes but cannot register addresses or access financial tables. */
export async function grantPilotDepositReservationRuntime(pool: Pool, role: string,
  custodyRuntimeRole = 'custody_runtime', observerRole = 'monero_observer',
  creditRole = 'monero_deposit_credit', mainnetAllocatorRole = 'monero_deposit_allocator'): Promise<void> {
  if (!/^[a-z][a-z0-9_]{0,62}$/.test(role)) throw new Error('Invalid pilot deposit reservation role identifier');
  if ([custodyRuntimeRole, observerRole, creditRole, mainnetAllocatorRole].includes(role)) {
    throw new Error('Pilot deposit reservation role must be separate from other custody roles');
  }
  const name = `"${role}"`;
  const c = await pool.connect();
  try {
    await c.query('BEGIN'); await verifyCustodySchema(c);
    const r = await c.query('SELECT rolsuper,rolcreatedb,rolcreaterole,rolreplication,rolbypassrls FROM pg_roles WHERE rolname=$1', [role]);
    if (!r.rowCount || Object.values(r.rows[0]).some(Boolean)) throw new Error('Pilot reservation role must exist without administrative privileges');
    const memberships = await c.query('SELECT 1 FROM pg_auth_members WHERE member=(SELECT oid FROM pg_roles WHERE rolname=$1) LIMIT 1', [role]);
    if (memberships.rowCount) throw new Error('Pilot reservation role must not inherit privileges through role membership');
    const owner = await c.query("SELECT pg_get_userbyid(nspowner) AS owner FROM pg_namespace WHERE nspname='wk_custody'");
    if (owner.rows[0].owner === role) throw new Error('Pilot reservation role must not own the custody schema');
    await c.query(`GRANT USAGE ON SCHEMA wk_custody TO ${name};
      REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA wk_custody FROM ${name};
      REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA wk_custody FROM ${name};
      GRANT SELECT ON wk_custody.schema_versions TO ${name};
      GRANT SELECT ON wk_custody.pilot_custody_identity TO ${name};
      GRANT SELECT,INSERT ON wk_custody.pilot_monero_deposit_reservations TO ${name};
      GRANT EXECUTE ON FUNCTION wk_custody.pilot_reservation_database_isolated() TO ${name};`);
    await c.query('COMMIT');
  } catch (error) { await c.query('ROLLBACK').catch(() => {}); throw error; }
  finally { c.release(); }
}

/** Separate pilot address allocator: only the reserved-to-registered column transition is writable. */
export async function grantPilotDepositAddressRuntime(pool: Pool, role: string,
  reservationRole = 'pilot_deposit_reservation', custodyRuntimeRole = 'custody_runtime',
  observerRole = 'monero_observer', creditRole = 'monero_deposit_credit'): Promise<void> {
  if (!/^[a-z][a-z0-9_]{0,62}$/.test(role)) throw new Error('Invalid pilot deposit address role identifier');
  if ([reservationRole, custodyRuntimeRole, observerRole, creditRole].includes(role)) {
    throw new Error('Pilot address role must be separate from reservation and other custody roles');
  }
  const name = `"${role}"`;
  const c = await pool.connect();
  try {
    await c.query('BEGIN'); await verifyCustodySchema(c);
    const r = await c.query('SELECT rolsuper,rolcreatedb,rolcreaterole,rolreplication,rolbypassrls FROM pg_roles WHERE rolname=$1', [role]);
    if (!r.rowCount || Object.values(r.rows[0]).some(Boolean)) throw new Error('Pilot address role must exist without administrative privileges');
    const memberships = await c.query('SELECT 1 FROM pg_auth_members WHERE member=(SELECT oid FROM pg_roles WHERE rolname=$1) LIMIT 1', [role]);
    if (memberships.rowCount) throw new Error('Pilot address role must not inherit privileges through role membership');
    const owner = await c.query("SELECT pg_get_userbyid(nspowner) AS owner FROM pg_namespace WHERE nspname='wk_custody'");
    if (owner.rows[0].owner === role) throw new Error('Pilot address role must not own the custody schema');
    await c.query(`GRANT USAGE ON SCHEMA wk_custody TO ${name};
      REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA wk_custody FROM ${name};
      REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA wk_custody FROM ${name};
      GRANT SELECT ON wk_custody.schema_versions,wk_custody.pilot_custody_identity TO ${name};
      GRANT SELECT,INSERT ON wk_custody.pilot_monero_deposit_reservations TO ${name};
      GRANT UPDATE(status,create_started_at,monero_address,registered_at) ON wk_custody.pilot_monero_deposit_reservations TO ${name};
      GRANT EXECUTE ON FUNCTION wk_custody.pilot_reservation_database_isolated() TO ${name};`);
    await c.query('COMMIT');
  } catch (error) { await c.query('ROLLBACK').catch(() => {}); throw error; }
  finally { c.release(); }
}

/** Pilot observation-only role. It cannot allocate addresses, access mainnet custody, or post financial entries. */
export async function grantPilotMoneroObserverRuntime(pool: Pool, role: string,
  addressRole = 'pilot_deposit_address', reservationRole = 'pilot_deposit_reservation',
  custodyRuntimeRole = 'custody_runtime', creditRole = 'monero_deposit_credit'): Promise<void> {
  if (!/^[a-z][a-z0-9_]{0,62}$/.test(role)) throw new Error('Invalid pilot Monero observer role identifier');
  if ([addressRole, reservationRole, custodyRuntimeRole, creditRole].includes(role)) {
    throw new Error('Pilot observer role must be separate from address, reservation, custody, and credit roles');
  }
  const name = `"${role}"`;
  const c = await pool.connect();
  try {
    await c.query('BEGIN'); await verifyCustodySchema(c);
    const r = await c.query('SELECT rolsuper,rolcreatedb,rolcreaterole,rolreplication,rolbypassrls FROM pg_roles WHERE rolname=$1', [role]);
    if (!r.rowCount || Object.values(r.rows[0]).some(Boolean)) throw new Error('Pilot observer role must exist without administrative privileges');
    const memberships = await c.query('SELECT 1 FROM pg_auth_members WHERE member=(SELECT oid FROM pg_roles WHERE rolname=$1) LIMIT 1', [role]);
    if (memberships.rowCount) throw new Error('Pilot observer role must not inherit privileges through role membership');
    const owner = await c.query("SELECT pg_get_userbyid(nspowner) AS owner FROM pg_namespace WHERE nspname='wk_custody'");
    if (owner.rows[0].owner === role) throw new Error('Pilot observer role must not own the custody schema');
    await c.query(`GRANT USAGE ON SCHEMA wk_custody TO ${name};
      REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA wk_custody FROM ${name};
      REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA wk_custody FROM ${name};
      GRANT SELECT ON wk_custody.schema_versions,wk_custody.pilot_custody_identity,
        wk_custody.pilot_monero_deposit_reservations TO ${name};
      GRANT SELECT,INSERT ON wk_custody.pilot_monero_observer_state TO ${name};
      GRANT UPDATE(scan_height,updated_at) ON wk_custody.pilot_monero_observer_state TO ${name};
      GRANT SELECT,INSERT ON wk_custody.pilot_monero_observation_scans TO ${name};
      GRANT SELECT,INSERT ON wk_custody.pilot_monero_observation_events TO ${name};
      GRANT USAGE,SELECT ON SEQUENCE wk_custody.pilot_monero_observation_events_sequence_seq TO ${name};
      GRANT SELECT,INSERT ON wk_custody.pilot_monero_observations TO ${name};
      GRANT UPDATE(amount_atomic_xmr,block_height,wallet_height,confirmations,timestamp,unlock_time,locked,
        double_spend_seen,payload_digest,currently_present,last_seen_scan_id,last_observed_at)
        ON wk_custody.pilot_monero_observations TO ${name};`);
    await c.query('COMMIT');
  } catch (error) { await c.query('ROLLBACK').catch(() => {}); throw error; }
  finally { c.release(); }
}

/** Read-only pilot review role. It can inspect persisted evidence, but not observer or financial state. */
export async function grantPilotMoneroDepositReviewRuntime(pool: Pool, role: string,
  addressRole = 'pilot_deposit_address', reservationRole = 'pilot_deposit_reservation',
  observerRole = 'pilot_monero_observer', custodyRuntimeRole = 'custody_runtime',
  creditRole = 'monero_deposit_credit'): Promise<void> {
  if (!/^[a-z][a-z0-9_]{0,62}$/.test(role)) throw new Error('Invalid pilot deposit review role identifier');
  if ([addressRole, reservationRole, observerRole, custodyRuntimeRole, creditRole].includes(role)) {
    throw new Error('Pilot deposit review role must be separate from observer, allocator, custody, and credit roles');
  }
  const name = `"${role}"`;
  const c = await pool.connect();
  try {
    await c.query('BEGIN'); await verifyCustodySchema(c);
    const r = await c.query('SELECT rolsuper,rolcreatedb,rolcreaterole,rolreplication,rolbypassrls FROM pg_roles WHERE rolname=$1', [role]);
    if (!r.rowCount || Object.values(r.rows[0]).some(Boolean)) throw new Error('Pilot deposit review role must exist without administrative privileges');
    const memberships = await c.query('SELECT 1 FROM pg_auth_members WHERE member=(SELECT oid FROM pg_roles WHERE rolname=$1) LIMIT 1', [role]);
    if (memberships.rowCount) throw new Error('Pilot deposit review role must not inherit privileges through role membership');
    const owner = await c.query("SELECT pg_get_userbyid(nspowner) AS owner FROM pg_namespace WHERE nspname='wk_custody'");
    if (owner.rows[0].owner === role) throw new Error('Pilot deposit review role must not own the custody schema');
    const owned = await c.query(`SELECT 1 FROM pg_class rel JOIN pg_namespace n ON n.oid=rel.relnamespace
      WHERE n.nspname='wk_custody' AND rel.relowner=(SELECT oid FROM pg_roles WHERE rolname=$1) LIMIT 1`, [role]);
    if (owned.rowCount) throw new Error('Pilot deposit review role must not own custody tables or sequences');
    const databaseCreate = await c.query('SELECT has_database_privilege($1,current_database(),\'CREATE\') AS allowed', [role]);
    if (databaseCreate.rows[0].allowed) throw new Error('Pilot deposit review role must not create database objects');
    await c.query(`GRANT USAGE ON SCHEMA wk_custody TO ${name};
      REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA wk_custody FROM ${name};
      REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA wk_custody FROM ${name};
      GRANT SELECT ON wk_custody.schema_versions,wk_custody.pilot_custody_identity,
        wk_custody.pilot_monero_deposit_reservations,wk_custody.pilot_monero_observer_state,
        wk_custody.pilot_monero_observation_scans,wk_custody.pilot_monero_observations,
        wk_custody.pilot_monero_observation_events TO ${name};`);
    await c.query('COMMIT');
  } catch (error) { await c.query('ROLLBACK').catch(() => {}); throw error; }
  finally { c.release(); }
}

/** Mainnet customer withdrawal-intent role. It can reserve/cancel liabilities but has no signing or relay authority. */
export async function grantMoneroWithdrawalRuntime(pool: Pool, role: string,
  custodyRuntimeRole = 'custody_runtime', observerRole = 'monero_observer',
  creditRole = 'monero_deposit_credit', allocatorRole = 'monero_deposit_allocator'): Promise<void> {
  if (!/^[a-z][a-z0-9_]{0,62}$/.test(role)) throw new Error('Invalid Monero withdrawal role identifier');
  if ([custodyRuntimeRole, observerRole, creditRole, allocatorRole].includes(role)) {
    throw new Error('Monero withdrawal role must be separate from custody, observer, credit, and allocator roles');
  }
  const name = `"${role}"`;
  const c = await pool.connect();
  try {
    await c.query('BEGIN'); await verifyCustodySchema(c);
    const r = await c.query('SELECT rolsuper,rolcreatedb,rolcreaterole,rolreplication,rolbypassrls FROM pg_roles WHERE rolname=$1', [role]);
    if (!r.rowCount || Object.values(r.rows[0]).some(Boolean)) throw new Error('Monero withdrawal role must exist without administrative privileges');
    const memberships = await c.query('SELECT 1 FROM pg_auth_members WHERE member=(SELECT oid FROM pg_roles WHERE rolname=$1) LIMIT 1', [role]);
    if (memberships.rowCount) throw new Error('Monero withdrawal role must not inherit privileges through role membership');
    const owner = await c.query("SELECT pg_get_userbyid(nspowner) AS owner FROM pg_namespace WHERE nspname='wk_custody'");
    if (owner.rows[0].owner === role) throw new Error('Monero withdrawal role must not own the custody schema');
    await c.query(`GRANT USAGE ON SCHEMA wk_custody TO ${name};
      REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA wk_custody FROM ${name};
      REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA wk_custody FROM ${name};
      GRANT SELECT ON wk_custody.custody_meta,wk_custody.accounts,wk_custody.monero_deposit_credit_events,
        wk_custody.monero_deposit_release_events,wk_custody.monero_deposit_reorg_holds,
        wk_custody.monero_deposit_reorg_resolutions TO ${name};
      GRANT SELECT,INSERT ON wk_custody.monero_withdrawal_intents TO ${name};
      GRANT UPDATE(status,cancel_request_id,cancel_public_key,cancel_expires_at,cancel_signature,cancelled_at)
        ON wk_custody.monero_withdrawal_intents TO ${name};
      GRANT INSERT(id,normal) ON wk_custody.accounts TO ${name};
      GRANT INSERT(id,kind,created) ON wk_custody.journals TO ${name};
      GRANT INSERT ON wk_custody.entries TO ${name};`);
    await c.query('COMMIT');
  } catch (error) { await c.query('ROLLBACK').catch(() => {}); throw error; }
  finally { c.release(); }
}