harmoniis_wallet/

wallet.rs

use std::collections::HashSet;
use std::path::{Path, PathBuf};

use rusqlite::{params, Connection};
use serde::{Deserialize, Serialize};

use crate::{
    crypto::generate_secret_hex,
    error::{Error, Result},
    identity::Identity,
    keychain::{
        HdKeychain, KEY_MODEL_VERSION_V3, MAX_VAULT_KEYS, SLOT_FAMILY_HARMONIA_VAULT,
        SLOT_FAMILY_VAULT,
    },
    types::{Certificate, Contract, ContractStatus, ContractType, Role},
};

const META_RGB_PRIVATE_KEY_HEX: &str = "rgb_private_key_hex";
const META_ROOT_PRIVATE_KEY_HEX: &str = "root_private_key_hex";
const META_ROOT_MNEMONIC: &str = "root_mnemonic";
const META_NICKNAME: &str = "nickname";
const META_WALLET_LABEL: &str = "wallet_label";
const META_KEY_MODEL_VERSION: &str = "key_model_version";

pub const MAX_PGP_KEYS: u32 = 1_000;
const MASTER_DB_FILENAME: &str = "master.db";
const RGB_DB_FILENAME: &str = "rgb.db";
const VOUCHER_DB_FILENAME: &str = "voucher.db";
const WALLET_DB_FILENAME: &str = "wallet.db";
const RGB_SHARD_DIR: &str = "identities";

/// SQLite-backed Harmoniis wallet.
pub struct RgbWallet {
    master_conn: Connection,
    rgb_conn: Connection,
}

struct WalletDiskPaths {
    base_dir: PathBuf,
    master_path: PathBuf,
    rgb_path: PathBuf,
    wallet_migration_path: PathBuf,
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct PgpIdentityRecord {
    pub label: String,
    pub key_index: u32,
    pub public_key_hex: String,
    pub is_active: bool,
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct PgpIdentitySnapshot {
    pub label: String,
    pub key_index: u32,
    pub private_key_hex: String,
    pub is_active: bool,
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct WalletSlotRecord {
    pub family: String,
    pub slot_index: u32,
    pub descriptor: String,
    pub db_rel_path: Option<String>,
    pub label: Option<String>,
    pub created_at: String,
    pub updated_at: String,
}

/// Serializable snapshot for backup/restore.
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct WalletSnapshot {
    pub private_key_hex: String,
    #[serde(default)]
    pub root_private_key_hex: Option<String>,
    #[serde(default)]
    pub root_mnemonic: Option<String>,
    #[serde(default)]
    pub wallet_label: Option<String>,
    #[serde(default)]
    pub pgp_identities: Vec<PgpIdentitySnapshot>,
    pub nickname: Option<String>,
    pub contracts: Vec<Contract>,
    pub certificates: Vec<Certificate>,
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct PaymentAttemptRecord {
    pub attempt_id: String,
    pub created_at: String,
    pub updated_at: String,
    pub service_origin: String,
    pub endpoint_path: String,
    pub method: String,
    pub rail: String,
    pub action_hint: String,
    pub required_amount: String,
    pub payment_unit: String,
    pub payment_reference: Option<String>,
    pub request_hash: String,
    pub response_status: Option<u16>,
    pub response_code: Option<String>,
    pub response_body: Option<String>,
    pub recovery_state: String,
    pub final_state: String,
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct PaymentLossRecord {
    pub loss_id: String,
    pub attempt_id: String,
    pub created_at: String,
    pub service_origin: String,
    pub endpoint_path: String,
    pub method: String,
    pub rail: String,
    pub amount: String,
    pub payment_reference: Option<String>,
    pub failure_stage: String,
    pub response_status: Option<u16>,
    pub response_code: Option<String>,
    pub response_body: Option<String>,
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct PaymentBlacklistRecord {
    pub service_origin: String,
    pub endpoint_path: String,
    pub method: String,
    pub rail: String,
    pub blacklisted_until: Option<String>,
    pub reason: String,
    pub triggered_by_loss_id: Option<String>,
    pub created_at: String,
    pub updated_at: String,
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct PaymentTransactionRecord {
    pub txn_id: String,
    pub attempt_id: Option<String>,
    pub created_at: String,
    pub updated_at: String,
    pub occurred_at: String,
    pub direction: String,
    pub role: String,
    pub source_system: String,
    pub service_origin: Option<String>,
    pub frontend_kind: Option<String>,
    pub transport_kind: Option<String>,
    pub endpoint_path: Option<String>,
    pub method: Option<String>,
    pub session_id: Option<String>,
    pub action_kind: String,
    pub resource_ref: Option<String>,
    pub contract_ref: Option<String>,
    pub invoice_ref: Option<String>,
    pub challenge_id: Option<String>,
    pub rail: String,
    pub payment_unit: String,
    pub quoted_amount: Option<String>,
    pub settled_amount: Option<String>,
    pub fee_amount: Option<String>,
    pub proof_ref: Option<String>,
    pub proof_kind: Option<String>,
    pub payer_ref: Option<String>,
    pub payee_ref: Option<String>,
    pub request_hash: Option<String>,
    pub response_code: Option<String>,
    pub status: String,
    pub metadata_json: Option<String>,
}

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct PaymentTransactionEventRecord {
    pub event_id: String,
    pub txn_id: String,
    pub created_at: String,
    pub event_type: String,
    pub status: String,
    pub actor: String,
    pub details_json: Option<String>,
}

#[derive(Debug, Clone)]
pub struct NewPaymentAttempt<'a> {
    pub service_origin: &'a str,
    pub endpoint_path: &'a str,
    pub method: &'a str,
    pub rail: &'a str,
    pub action_hint: &'a str,
    pub required_amount: &'a str,
    pub payment_unit: &'a str,
    pub payment_reference: Option<&'a str>,
    pub request_hash: &'a str,
}

#[derive(Debug, Clone)]
pub struct PaymentAttemptUpdate<'a> {
    pub payment_reference: Option<&'a str>,
    pub response_status: Option<u16>,
    pub response_code: Option<&'a str>,
    pub response_body: Option<&'a str>,
    pub recovery_state: &'a str,
    pub final_state: &'a str,
}

#[derive(Debug, Clone)]
pub struct NewPaymentTransaction<'a> {
    pub attempt_id: Option<&'a str>,
    pub occurred_at: Option<&'a str>,
    pub direction: &'a str,
    pub role: &'a str,
    pub source_system: &'a str,
    pub service_origin: Option<&'a str>,
    pub frontend_kind: Option<&'a str>,
    pub transport_kind: Option<&'a str>,
    pub endpoint_path: Option<&'a str>,
    pub method: Option<&'a str>,
    pub session_id: Option<&'a str>,
    pub action_kind: &'a str,
    pub resource_ref: Option<&'a str>,
    pub contract_ref: Option<&'a str>,
    pub invoice_ref: Option<&'a str>,
    pub challenge_id: Option<&'a str>,
    pub rail: &'a str,
    pub payment_unit: &'a str,
    pub quoted_amount: Option<&'a str>,
    pub settled_amount: Option<&'a str>,
    pub fee_amount: Option<&'a str>,
    pub proof_ref: Option<&'a str>,
    pub proof_kind: Option<&'a str>,
    pub payer_ref: Option<&'a str>,
    pub payee_ref: Option<&'a str>,
    pub request_hash: Option<&'a str>,
    pub response_code: Option<&'a str>,
    pub status: &'a str,
    pub metadata_json: Option<&'a str>,
}

#[derive(Debug, Clone)]
pub struct PaymentTransactionUpdate<'a> {
    pub occurred_at: Option<&'a str>,
    pub service_origin: Option<&'a str>,
    pub frontend_kind: Option<&'a str>,
    pub transport_kind: Option<&'a str>,
    pub endpoint_path: Option<&'a str>,
    pub method: Option<&'a str>,
    pub session_id: Option<&'a str>,
    pub action_kind: Option<&'a str>,
    pub resource_ref: Option<&'a str>,
    pub contract_ref: Option<&'a str>,
    pub invoice_ref: Option<&'a str>,
    pub challenge_id: Option<&'a str>,
    pub quoted_amount: Option<&'a str>,
    pub settled_amount: Option<&'a str>,
    pub fee_amount: Option<&'a str>,
    pub proof_ref: Option<&'a str>,
    pub proof_kind: Option<&'a str>,
    pub payer_ref: Option<&'a str>,
    pub payee_ref: Option<&'a str>,
    pub request_hash: Option<&'a str>,
    pub response_code: Option<&'a str>,
    pub status: &'a str,
    pub metadata_json: Option<&'a str>,
}

#[derive(Debug, Clone)]
pub struct NewPaymentTransactionEvent<'a> {
    pub txn_id: &'a str,
    pub event_type: &'a str,
    pub status: &'a str,
    pub actor: &'a str,
    pub details_json: Option<&'a str>,
}

impl RgbWallet {
    fn resolve_disk_paths(path: &Path) -> Result<WalletDiskPaths> {
        let normalized = if path
            .file_name()
            .and_then(|n| n.to_str())
            .unwrap_or_default()
            .is_empty()
        {
            PathBuf::from(MASTER_DB_FILENAME)
        } else {
            path.to_path_buf()
        };
        let file_name = normalized
            .file_name()
            .and_then(|n| n.to_str())
            .unwrap_or(MASTER_DB_FILENAME);
        let base_dir = normalized
            .parent()
            .map(ToOwned::to_owned)
            .unwrap_or_else(|| PathBuf::from("."));
        let master_path = if file_name.eq_ignore_ascii_case(MASTER_DB_FILENAME) {
            normalized.clone()
        } else if file_name.eq_ignore_ascii_case(RGB_DB_FILENAME)
            || file_name.eq_ignore_ascii_case(WALLET_DB_FILENAME)
        {
            base_dir.join(MASTER_DB_FILENAME)
        } else {
            normalized.clone()
        };
        Ok(WalletDiskPaths {
            base_dir: base_dir.clone(),
            master_path,
            rgb_path: base_dir.join(RGB_DB_FILENAME),
            wallet_migration_path: base_dir.join(WALLET_DB_FILENAME),
        })
    }

    fn derive_wallet_label(path: &Path) -> String {
        let stem = path
            .file_stem()
            .and_then(|x| x.to_str())
            .unwrap_or("wallet");
        if stem.eq_ignore_ascii_case("master")
            || stem.eq_ignore_ascii_case("rgb")
            || stem.eq_ignore_ascii_case("wallet")
        {
            path.parent()
                .and_then(|p| p.file_name())
                .and_then(|x| x.to_str())
                .map(ToString::to_string)
                .unwrap_or_else(|| "wallet".to_string())
        } else {
            stem.to_string()
        }
    }

    /// Create master-database tables and indexes (DDL only, no key
    /// materialisation).  Safe to call on every open — all statements use
    /// `CREATE … IF NOT EXISTS`.
    fn init_master_tables(conn: &Connection) -> Result<()> {
        conn.execute_batch(
            "
            PRAGMA journal_mode=WAL;
            PRAGMA foreign_keys=ON;

            CREATE TABLE IF NOT EXISTS wallet_metadata (
                key   TEXT PRIMARY KEY,
                value TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS pgp_identities (
                label           TEXT PRIMARY KEY,
                key_index       INTEGER NOT NULL UNIQUE,
                private_key_hex TEXT NOT NULL,
                public_key_hex  TEXT NOT NULL,
                created_at      TEXT NOT NULL,
                is_active       INTEGER NOT NULL DEFAULT 0
            );

            CREATE TABLE IF NOT EXISTS wallet_slots (
                family      TEXT NOT NULL,
                slot_index  INTEGER NOT NULL,
                descriptor  TEXT NOT NULL,
                db_rel_path TEXT,
                label       TEXT,
                created_at  TEXT NOT NULL,
                updated_at  TEXT NOT NULL,
                PRIMARY KEY (family, slot_index)
            );

            CREATE TABLE IF NOT EXISTS payment_attempts (
                attempt_id        TEXT PRIMARY KEY,
                created_at        TEXT NOT NULL,
                updated_at        TEXT NOT NULL,
                service_origin    TEXT NOT NULL,
                endpoint_path     TEXT NOT NULL,
                method            TEXT NOT NULL,
                rail              TEXT NOT NULL,
                action_hint       TEXT NOT NULL,
                required_amount   TEXT NOT NULL,
                payment_unit      TEXT NOT NULL,
                payment_reference TEXT,
                request_hash      TEXT NOT NULL,
                response_status   INTEGER,
                response_code     TEXT,
                response_body     TEXT,
                recovery_state    TEXT NOT NULL,
                final_state       TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS payment_losses (
                loss_id            TEXT PRIMARY KEY,
                attempt_id         TEXT NOT NULL,
                created_at         TEXT NOT NULL,
                service_origin     TEXT NOT NULL,
                endpoint_path      TEXT NOT NULL,
                method             TEXT NOT NULL,
                rail               TEXT NOT NULL,
                amount             TEXT NOT NULL,
                payment_reference  TEXT,
                failure_stage      TEXT NOT NULL,
                response_status    INTEGER,
                response_code      TEXT,
                response_body      TEXT
            );

            CREATE TABLE IF NOT EXISTS payment_blacklist (
                service_origin      TEXT NOT NULL,
                endpoint_path       TEXT NOT NULL,
                method              TEXT NOT NULL,
                rail                TEXT NOT NULL,
                blacklisted_until   TEXT,
                reason              TEXT NOT NULL,
                triggered_by_loss_id TEXT,
                created_at          TEXT NOT NULL,
                updated_at          TEXT NOT NULL,
                PRIMARY KEY (service_origin, endpoint_path, method, rail)
            );

            CREATE TABLE IF NOT EXISTS payment_transactions (
                txn_id          TEXT PRIMARY KEY,
                attempt_id      TEXT,
                created_at      TEXT NOT NULL,
                updated_at      TEXT NOT NULL,
                occurred_at     TEXT NOT NULL,
                direction       TEXT NOT NULL,
                role            TEXT NOT NULL,
                source_system   TEXT NOT NULL,
                service_origin  TEXT,
                frontend_kind   TEXT,
                transport_kind  TEXT,
                endpoint_path   TEXT,
                method          TEXT,
                session_id      TEXT,
                action_kind     TEXT NOT NULL,
                resource_ref    TEXT,
                contract_ref    TEXT,
                invoice_ref     TEXT,
                challenge_id    TEXT,
                rail            TEXT NOT NULL,
                payment_unit    TEXT NOT NULL,
                quoted_amount   TEXT,
                settled_amount  TEXT,
                fee_amount      TEXT,
                proof_ref       TEXT,
                proof_kind      TEXT,
                payer_ref       TEXT,
                payee_ref       TEXT,
                request_hash    TEXT,
                response_code   TEXT,
                status          TEXT NOT NULL,
                metadata_json   TEXT
            );

            CREATE TABLE IF NOT EXISTS payment_transaction_events (
                event_id       TEXT PRIMARY KEY,
                txn_id         TEXT NOT NULL,
                created_at     TEXT NOT NULL,
                event_type     TEXT NOT NULL,
                status         TEXT NOT NULL,
                actor          TEXT NOT NULL,
                details_json   TEXT NOT NULL DEFAULT '{}'
            );

            CREATE INDEX IF NOT EXISTS idx_payment_transactions_created_at
                ON payment_transactions(created_at DESC);
            CREATE INDEX IF NOT EXISTS idx_payment_transactions_status
                ON payment_transactions(status, created_at DESC);
            CREATE INDEX IF NOT EXISTS idx_payment_transactions_direction
                ON payment_transactions(direction, created_at DESC);
            CREATE INDEX IF NOT EXISTS idx_payment_transactions_action_kind
                ON payment_transactions(action_kind, created_at DESC);
            CREATE UNIQUE INDEX IF NOT EXISTS idx_payment_transactions_proof_ref
                ON payment_transactions(direction, rail, proof_ref)
                WHERE proof_ref IS NOT NULL AND proof_ref != '';
            CREATE INDEX IF NOT EXISTS idx_payment_transaction_events_txn_id
                ON payment_transaction_events(txn_id, created_at ASC);
            ",
        )?;
        Ok(())
    }

    /// Create tables **and** materialise root key material.
    ///
    /// `allow_generate` – when `true` a brand-new keychain is created if no
    /// root mnemonic / entropy hex exists yet (first-time wallet creation).
    /// When `false` the call returns `Error::KeyMaterialMissing` instead of
    /// silently generating new keys — protecting existing wallets from
    /// accidental key replacement.
    fn init_master_schema(conn: &Connection, allow_generate: bool) -> Result<()> {
        Self::init_master_tables(conn)?;
        ensure_root_and_identity_materialized(conn, allow_generate)?;
        ensure_default_pgp_identity(conn)?;
        Ok(())
    }

    fn init_identity_schema(conn: &Connection) -> Result<()> {
        conn.execute_batch(
            "
            PRAGMA journal_mode=WAL;
            PRAGMA foreign_keys=ON;

            CREATE TABLE IF NOT EXISTS contracts (
                contract_id        TEXT PRIMARY KEY,
                contract_type      TEXT NOT NULL DEFAULT 'service',
                status             TEXT NOT NULL DEFAULT 'issued',
                witness_secret     TEXT,
                witness_proof      TEXT,
                amount_units       INTEGER NOT NULL DEFAULT 0,
                work_spec          TEXT NOT NULL DEFAULT '',
                buyer_fingerprint  TEXT NOT NULL DEFAULT '',
                seller_fingerprint TEXT,
                reference_post     TEXT,
                delivery_deadline  TEXT,
                role               TEXT NOT NULL DEFAULT 'buyer',
                delivered_text     TEXT,
                certificate_id     TEXT,
                created_at         TEXT NOT NULL,
                updated_at         TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS certificates (
                certificate_id  TEXT PRIMARY KEY,
                contract_id     TEXT,
                witness_secret  TEXT,
                witness_proof   TEXT,
                created_at      TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS timeline_posts (
                post_id      TEXT PRIMARY KEY,
                created_at   TEXT NOT NULL,
                updated_at   TEXT NOT NULL,
                metadata_json TEXT NOT NULL DEFAULT '{}'
            );

            CREATE TABLE IF NOT EXISTS timeline_comments (
                comment_id    TEXT PRIMARY KEY,
                post_id       TEXT NOT NULL DEFAULT '',
                created_at    TEXT NOT NULL,
                updated_at    TEXT NOT NULL,
                metadata_json TEXT NOT NULL DEFAULT '{}'
            );

            CREATE TABLE IF NOT EXISTS timeline_bids (
                bid_post_id    TEXT PRIMARY KEY,
                contract_id    TEXT NOT NULL DEFAULT '',
                service_post_id TEXT NOT NULL DEFAULT '',
                created_at     TEXT NOT NULL,
                metadata_json  TEXT NOT NULL DEFAULT '{}'
            );
            ",
        )?;
        migrate_identity_schema(conn)?;
        Ok(())
    }

    fn with_identity_conn<T, F>(&self, f: F) -> Result<T>
    where
        F: FnOnce(&Connection) -> Result<T>,
    {
        f(&self.rgb_conn)
    }

    fn refresh_slot_registry(&self) -> Result<()> {
        let now = chrono::Utc::now().to_rfc3339();
        let root_hex = self.derive_slot_hex("root", 0)?;
        let rgb_hex = self.derive_slot_hex("rgb", 0)?;
        let webcash_hex = self.derive_slot_hex("webcash", 0)?;
        let voucher_hex = self.derive_slot_hex("voucher", 0)?;
        let bitcoin_hex = self.derive_slot_hex("bitcoin", 0)?;
        let vault_hex = self.derive_slot_hex(SLOT_FAMILY_VAULT, 0)?;

        self.master_conn.execute(
            "INSERT OR REPLACE INTO wallet_slots (family, slot_index, descriptor, db_rel_path, label, created_at, updated_at)
             VALUES ('rgb', 0, ?1, NULL, (SELECT label FROM wallet_slots WHERE family='rgb' AND slot_index=0), COALESCE((SELECT created_at FROM wallet_slots WHERE family='rgb' AND slot_index=0), ?2), ?2)",
            params![rgb_hex, now],
        )?;
        self.master_conn.execute(
            "INSERT OR REPLACE INTO wallet_slots (family, slot_index, descriptor, db_rel_path, label, created_at, updated_at)
             VALUES ('webcash', 0, ?1, NULL, (SELECT label FROM wallet_slots WHERE family='webcash' AND slot_index=0), COALESCE((SELECT created_at FROM wallet_slots WHERE family='webcash' AND slot_index=0), ?2), ?2)",
            params![webcash_hex, now],
        )?;
        self.master_conn.execute(
            "INSERT OR REPLACE INTO wallet_slots (family, slot_index, descriptor, db_rel_path, label, created_at, updated_at)
             VALUES ('bitcoin', 0, ?1, NULL, (SELECT label FROM wallet_slots WHERE family='bitcoin' AND slot_index=0), COALESCE((SELECT created_at FROM wallet_slots WHERE family='bitcoin' AND slot_index=0), ?2), ?2)",
            params![bitcoin_hex, now],
        )?;
        self.master_conn.execute(
            "INSERT OR REPLACE INTO wallet_slots (family, slot_index, descriptor, db_rel_path, label, created_at, updated_at)
             VALUES ('voucher', 0, ?1, ?2, (SELECT label FROM wallet_slots WHERE family='voucher' AND slot_index=0), COALESCE((SELECT created_at FROM wallet_slots WHERE family='voucher' AND slot_index=0), ?3), ?3)",
            params![voucher_hex, Some(VOUCHER_DB_FILENAME.to_string()), now],
        )?;
        self.master_conn.execute(
            "INSERT OR REPLACE INTO wallet_slots (family, slot_index, descriptor, db_rel_path, label, created_at, updated_at)
             VALUES ('root', 0, ?1, NULL, (SELECT label FROM wallet_slots WHERE family='root' AND slot_index=0), COALESCE((SELECT created_at FROM wallet_slots WHERE family='root' AND slot_index=0), ?2), ?2)",
            params![root_hex, now],
        )?;
        self.master_conn.execute(
            "INSERT OR REPLACE INTO wallet_slots (family, slot_index, descriptor, db_rel_path, label, created_at, updated_at)
             VALUES (?1, 0, ?2, NULL, (SELECT label FROM wallet_slots WHERE family=?1 AND slot_index=0), COALESCE((SELECT created_at FROM wallet_slots WHERE family=?1 AND slot_index=0), ?3), ?3)",
            params![SLOT_FAMILY_VAULT, vault_hex, now],
        )?;

        let mut vault_stmt = self.master_conn.prepare(
            "SELECT slot_index, label, created_at
             FROM wallet_slots
             WHERE family = ?1 AND slot_index > 0
             ORDER BY slot_index ASC",
        )?;
        let vault_rows = vault_stmt.query_map(params![SLOT_FAMILY_VAULT], |row| {
            Ok((
                row.get::<_, u32>(0)?,
                row.get::<_, Option<String>>(1)?,
                row.get::<_, String>(2)?,
            ))
        })?;
        let vault_rows = vault_rows.collect::<std::result::Result<Vec<_>, _>>()?;
        drop(vault_stmt);
        for row in vault_rows {
            let (slot_index, label, created_at) = row;
            let public_key_hex = self
                .derive_vault_identity_for_index(slot_index)?
                .public_key_hex();
            self.master_conn.execute(
                "INSERT OR REPLACE INTO wallet_slots (family, slot_index, descriptor, db_rel_path, label, created_at, updated_at)
                 VALUES (?1, ?2, ?3, NULL, ?4, ?5, ?6)",
                params![
                    SLOT_FAMILY_VAULT,
                    i64::from(slot_index),
                    public_key_hex,
                    label,
                    created_at,
                    now
                ],
            )?;
        }

        let mut stmt = self.master_conn.prepare(
            "SELECT key_index, public_key_hex, label FROM pgp_identities ORDER BY key_index ASC",
        )?;
        let rows = stmt.query_map([], |row| {
            let key_index_i: i64 = row.get(0)?;
            let key_index = u32::try_from(key_index_i)
                .map_err(|_| rusqlite::Error::IntegralValueOutOfRange(0, key_index_i))?;
            let public_key_hex: String = row.get(1)?;
            let label: String = row.get(2)?;
            Ok((key_index, public_key_hex, label))
        })?;
        for row in rows {
            let (key_index, public_key_hex, label) = row?;
            self.master_conn.execute(
                "INSERT OR REPLACE INTO wallet_slots (family, slot_index, descriptor, db_rel_path, label, created_at, updated_at)
                 VALUES ('pgp', ?1, ?2, ?3, ?4, COALESCE((SELECT created_at FROM wallet_slots WHERE family='pgp' AND slot_index=?1), ?5), ?5)",
                params![
                    i64::from(key_index),
                    public_key_hex,
                    Some(RGB_DB_FILENAME.to_string()),
                    label,
                    now
                ],
            )?;
        }
        Ok(())
    }

    fn import_previous_layout(paths: &WalletDiskPaths) -> Result<()> {
        let source_path = if paths.rgb_path.exists() {
            paths.rgb_path.clone()
        } else if paths.wallet_migration_path.exists() {
            paths.wallet_migration_path.clone()
        } else {
            return Err(Error::NotFound("no wallet data source found".to_string()));
        };

        let source_conn = Connection::open(&source_path)?;
        migrate_identity_schema_if_present(&source_conn)?;

        let master_conn = Connection::open(&paths.master_path)?;
        Self::init_master_tables(&master_conn)?;

        if table_exists(&source_conn, "wallet_metadata")? {
            let mut stmt = source_conn.prepare("SELECT key, value FROM wallet_metadata")?;
            let rows = stmt.query_map([], |row| {
                Ok((row.get::<_, String>(0)?, row.get::<_, String>(1)?))
            })?;
            for row in rows {
                let (key, value) = row?;
                master_conn.execute(
                    "INSERT OR REPLACE INTO wallet_metadata (key, value) VALUES (?1, ?2)",
                    params![key, value],
                )?;
            }
        }

        if table_exists(&source_conn, "pgp_identities")? {
            let mut stmt = source_conn.prepare(
                "SELECT label, key_index, private_key_hex, public_key_hex, created_at, is_active
                 FROM pgp_identities",
            )?;
            let rows = stmt.query_map([], |row| {
                Ok((
                    row.get::<_, String>(0)?,
                    row.get::<_, i64>(1)?,
                    row.get::<_, String>(2)?,
                    row.get::<_, String>(3)?,
                    row.get::<_, String>(4)?,
                    row.get::<_, i64>(5)?,
                ))
            })?;
            master_conn.execute("DELETE FROM pgp_identities", [])?;
            for row in rows {
                let (label, key_index, private_key_hex, public_key_hex, created_at, is_active) =
                    row?;
                master_conn.execute(
                    "INSERT OR REPLACE INTO pgp_identities
                     (label, key_index, private_key_hex, public_key_hex, created_at, is_active)
                     VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
                    params![
                        label,
                        key_index,
                        private_key_hex,
                        public_key_hex,
                        created_at,
                        is_active
                    ],
                )?;
            }
        }
        ensure_root_and_identity_materialized(&master_conn, false)?;
        ensure_default_pgp_identity(&master_conn)?;
        drop(master_conn);

        let rgb_conn = Connection::open(&paths.rgb_path)?;
        Self::init_identity_schema(&rgb_conn)?;
        if !same_path(&source_path, &paths.rgb_path) {
            migrate_rgb_state(&source_conn, &rgb_conn)?;
        }
        Self::merge_sharded_rgb_data(&paths.base_dir, &rgb_conn)?;
        Ok(())
    }

    fn merge_sharded_rgb_data(base_dir: &Path, rgb_conn: &Connection) -> Result<()> {
        let shard_dir = base_dir.join(RGB_SHARD_DIR);
        if !shard_dir.exists() {
            return Ok(());
        }
        for entry in std::fs::read_dir(&shard_dir)
            .map_err(|e| Error::Other(anyhow::anyhow!("cannot read rgb shard dir: {e}")))?
        {
            let entry = entry
                .map_err(|e| Error::Other(anyhow::anyhow!("cannot read rgb shard entry: {e}")))?;
            let path = entry.path();
            let Some(name) = path.file_name().and_then(|n| n.to_str()) else {
                continue;
            };
            if !name.starts_with("identity-") || !name.ends_with(".db") {
                continue;
            }
            let shard_conn = Connection::open(&path)?;
            migrate_identity_schema_if_present(&shard_conn)?;
            migrate_rgb_state(&shard_conn, rgb_conn)?;
        }
        Ok(())
    }

    fn open_from_disk(path: &Path, allow_create: bool) -> Result<Self> {
        let paths = Self::resolve_disk_paths(path)?;
        std::fs::create_dir_all(&paths.base_dir)
            .map_err(|e| Error::Other(anyhow::anyhow!("cannot create wallet dir: {e}")))?;

        if !paths.master_path.exists() {
            if paths.rgb_path.exists() || paths.wallet_migration_path.exists() {
                Self::import_previous_layout(&paths)?;
            } else if !allow_create {
                return Err(Error::NotFound(format!(
                    "master wallet database not found at {}",
                    paths.master_path.display()
                )));
            }
        }

        let master_conn = Connection::open(&paths.master_path)?;
        Self::init_master_schema(&master_conn, allow_create)?;
        let rgb_conn = Connection::open(&paths.rgb_path)?;
        Self::init_identity_schema(&rgb_conn)?;
        Self::merge_sharded_rgb_data(&paths.base_dir, &rgb_conn)?;
        let wallet = Self {
            master_conn,
            rgb_conn,
        };
        wallet.refresh_slot_registry()?;

        if wallet.wallet_label()?.as_deref().unwrap_or("default") == "default" {
            let derived = Self::derive_wallet_label(path);
            wallet.set_wallet_label(&derived)?;
            let _ = wallet.rename_pgp_label("default", &derived);
            wallet.refresh_slot_registry()?;
        }
        Ok(wallet)
    }

    /// Create a new wallet at the given path, generating a fresh root key.
    pub fn create(path: &Path) -> Result<Self> {
        Self::open_from_disk(path, true)
    }

    /// Open an existing wallet at the given path.
    pub fn open(path: &Path) -> Result<Self> {
        Self::open_from_disk(path, false)
    }

    /// Open an in-memory wallet (for tests).
    pub fn open_memory() -> Result<Self> {
        let master_conn = Connection::open_in_memory()?;
        Self::init_master_schema(&master_conn, true)?;
        let rgb_conn = Connection::open_in_memory()?;
        Self::init_identity_schema(&rgb_conn)?;
        let wallet = Self {
            master_conn,
            rgb_conn,
        };
        if wallet.wallet_label()?.as_deref().unwrap_or("default") == "default" {
            wallet.set_wallet_label("memory-wallet")?;
            let _ = wallet.rename_pgp_label("default", "memory-wallet");
        }
        wallet.refresh_slot_registry()?;
        Ok(wallet)
    }

    // ── Identity material ────────────────────────────────────────────────────

    pub fn root_private_key_hex(&self) -> Result<String> {
        metadata_value(&self.master_conn, META_ROOT_PRIVATE_KEY_HEX)?
            .ok_or_else(|| Error::Other(anyhow::anyhow!("missing master entropy hex")))
    }

    fn keychain(&self) -> Result<HdKeychain> {
        if let Some(words) = metadata_value(&self.master_conn, META_ROOT_MNEMONIC)? {
            return HdKeychain::from_mnemonic_words(&words);
        }
        let entropy_hex = self.root_private_key_hex()?;
        HdKeychain::from_entropy_hex(&entropy_hex)
    }

    fn set_master_keychain_material(&self, keychain: &HdKeychain) -> Result<()> {
        set_metadata_value(
            &self.master_conn,
            META_ROOT_PRIVATE_KEY_HEX,
            &keychain.entropy_hex(),
        )?;
        set_metadata_value(
            &self.master_conn,
            META_ROOT_MNEMONIC,
            &keychain.mnemonic_words(),
        )?;
        set_metadata_value(
            &self.master_conn,
            META_RGB_PRIVATE_KEY_HEX,
            &keychain.derive_slot_hex("rgb", 0)?,
        )?;
        set_metadata_value(
            &self.master_conn,
            META_KEY_MODEL_VERSION,
            KEY_MODEL_VERSION_V3,
        )?;
        Ok(())
    }

    pub fn identity(&self) -> Result<Identity> {
        self.rgb_identity()
    }

    pub fn rgb_identity(&self) -> Result<Identity> {
        let hex = self.derive_slot_hex("rgb", 0)?;
        Identity::from_hex(&hex)
    }

    pub fn derive_webcash_master_secret_hex(&self) -> Result<String> {
        self.derive_slot_hex("webcash", 0)
    }

    pub fn derive_voucher_master_secret_hex(&self) -> Result<String> {
        self.derive_slot_hex("voucher", 0)
    }

    pub fn derive_bitcoin_master_key_hex(&self) -> Result<String> {
        self.derive_slot_hex("bitcoin", 0)
    }

    pub fn derive_vault_master_key_hex(&self) -> Result<String> {
        self.derive_slot_hex(SLOT_FAMILY_VAULT, 0)
    }

    /// Backward-compatible alias retained for Harmonia integration naming.
    pub fn derive_harmonia_vault_master_key_hex(&self) -> Result<String> {
        self.derive_slot_hex(SLOT_FAMILY_HARMONIA_VAULT, 0)
    }

    pub fn derive_vault_identity_for_index(&self, key_index: u32) -> Result<Identity> {
        if key_index == 0 {
            return Err(Error::Other(anyhow::anyhow!(
                "vault key index 0 is reserved for the vault root"
            )));
        }
        let private_key_hex = self.derive_slot_hex(SLOT_FAMILY_VAULT, key_index)?;
        Identity::from_hex(&private_key_hex)
    }

    pub fn create_vault_identity(&self, label: Option<&str>) -> Result<WalletSlotRecord> {
        let key_index = self.next_vault_key_index()?;
        self.ensure_vault_identity_index(key_index, label)
    }

    pub fn ensure_vault_identity_index(
        &self,
        key_index: u32,
        preferred_label: Option<&str>,
    ) -> Result<WalletSlotRecord> {
        if key_index == 0 || key_index >= MAX_VAULT_KEYS {
            return Err(Error::Other(anyhow::anyhow!(
                "vault key index out of range (valid: 1..{})",
                MAX_VAULT_KEYS - 1
            )));
        }

        let fallback_label = format!("vault-{key_index}-key-pairs");
        let desired_raw = preferred_label.unwrap_or(fallback_label.as_str());
        let desired = canonical_label(desired_raw)?;
        let label = self.unique_vault_label(&desired, key_index)?;
        let identity = self.derive_vault_identity_for_index(key_index)?;
        let public_key_hex = identity.public_key_hex();

        let tx = self.master_conn.unchecked_transaction()?;
        tx.execute(
            "DELETE FROM wallet_slots WHERE family = ?1 AND slot_index = ?2",
            params![SLOT_FAMILY_VAULT, i64::from(key_index)],
        )?;
        tx.execute(
            "DELETE FROM wallet_slots WHERE family = ?1 AND label = ?2",
            params![SLOT_FAMILY_VAULT, label.clone()],
        )?;
        tx.execute(
            "INSERT INTO wallet_slots (family, slot_index, descriptor, db_rel_path, label, created_at, updated_at)
             VALUES (?1, ?2, ?3, NULL, ?4, ?5, ?5)",
            params![
                SLOT_FAMILY_VAULT,
                i64::from(key_index),
                public_key_hex,
                label,
                chrono::Utc::now().to_rfc3339(),
            ],
        )?;
        tx.commit()?;
        self.refresh_slot_registry()?;
        self.vault_identity_by_index(key_index)
    }

    pub fn list_vault_identities(&self) -> Result<Vec<WalletSlotRecord>> {
        self.list_wallet_slots(Some(SLOT_FAMILY_VAULT))
            .map(|items| {
                items
                    .into_iter()
                    .filter(|item| item.slot_index > 0)
                    .collect()
            })
    }

    pub fn vault_identity_by_label(&self, label: &str) -> Result<WalletSlotRecord> {
        let canonical = canonical_label(label)?;
        self.list_vault_identities()?
            .into_iter()
            .find(|item| item.label.as_deref() == Some(canonical.as_str()))
            .ok_or_else(|| Error::NotFound(format!("vault identity label '{canonical}' not found")))
    }

    pub fn vault_identity_by_index(&self, key_index: u32) -> Result<WalletSlotRecord> {
        self.list_vault_identities()?
            .into_iter()
            .find(|item| item.slot_index == key_index)
            .ok_or_else(|| Error::NotFound(format!("vault identity index '{key_index}' not found")))
    }

    pub fn derive_slot_hex(&self, family: &str, index: u32) -> Result<String> {
        self.keychain()?.derive_slot_hex(family, index)
    }

    pub fn export_master_key_hex(&self) -> Result<String> {
        self.root_private_key_hex()
    }

    pub fn export_master_key_mnemonic(&self) -> Result<String> {
        self.keychain().map(|k| k.mnemonic_words())
    }

    /// Export the canonical recovery phrase stored in wallet metadata.
    ///
    /// New wallets store a 12-word BIP39 phrase at creation time. Existing wallets
    /// may fall back to the reversible 24-word representation of the root key.
    pub fn export_recovery_mnemonic(&self) -> Result<String> {
        if let Some(mnemonic) = metadata_value(&self.master_conn, META_ROOT_MNEMONIC)? {
            return Ok(mnemonic);
        }
        self.export_master_key_mnemonic()
    }

    pub fn apply_master_key_hex(&self, root_private_key_hex: &str) -> Result<()> {
        let keychain = HdKeychain::from_entropy_hex(root_private_key_hex)?;
        self.set_master_keychain_material(&keychain)?;

        let wallet_label = self
            .wallet_label()?
            .unwrap_or_else(|| "default".to_string());
        let label = canonical_label(&wallet_label)?;
        let pgp0_hex = keychain.derive_slot_hex("pgp", 0)?;
        let pgp0 = Identity::from_hex(&pgp0_hex)?;
        let tx = self.master_conn.unchecked_transaction()?;
        tx.execute("DELETE FROM pgp_identities", [])?;
        tx.execute(
            "INSERT INTO pgp_identities (label, key_index, private_key_hex, public_key_hex, created_at, is_active)
             VALUES (?1, 0, ?2, ?3, ?4, 1)",
            params![
                label,
                pgp0_hex,
                pgp0.public_key_hex(),
                chrono::Utc::now().to_rfc3339(),
            ],
        )?;
        tx.commit()?;
        self.refresh_slot_registry()?;
        Ok(())
    }

    pub fn apply_master_key_mnemonic(&self, mnemonic: &str) -> Result<()> {
        let keychain = HdKeychain::from_mnemonic_words(mnemonic)?;
        self.apply_master_key_hex(&keychain.entropy_hex())
    }

    pub fn has_local_state(&self) -> Result<bool> {
        let (contracts, certs) = self.with_identity_conn(|conn| {
            let mut stmt = conn.prepare("SELECT COUNT(*) FROM contracts")?;
            let contracts: i64 = stmt.query_row([], |row| row.get(0))?;
            let mut stmt = conn.prepare("SELECT COUNT(*) FROM certificates")?;
            let certs: i64 = stmt.query_row([], |row| row.get(0))?;
            Ok((contracts, certs))
        })?;
        Ok(contracts > 0 || certs > 0)
    }

    // ── PGP identities (labeled, multi-key) ─────────────────────────────────

    pub fn active_pgp_identity(&self) -> Result<(PgpIdentityRecord, Identity)> {
        let mut stmt = self.master_conn.prepare(
            "SELECT label, key_index, private_key_hex, public_key_hex, is_active
             FROM pgp_identities
             WHERE is_active = 1
             ORDER BY key_index ASC
             LIMIT 1",
        )?;
        let mut rows = stmt.query([])?;
        let row = rows
            .next()?
            .ok_or_else(|| Error::Other(anyhow::anyhow!("no active PGP identity")))?;

        let label: String = row.get(0)?;
        let key_index_i: i64 = row.get(1)?;
        let private_key_hex: String = row.get(2)?;
        let public_key_hex: String = row.get(3)?;
        let is_active_i: i64 = row.get(4)?;
        let key_index = u32::try_from(key_index_i)
            .map_err(|_| Error::Other(anyhow::anyhow!("invalid PGP key index in wallet")))?;
        let identity = Identity::from_hex(&private_key_hex)?;

        Ok((
            PgpIdentityRecord {
                label,
                key_index,
                public_key_hex,
                is_active: is_active_i == 1,
            },
            identity,
        ))
    }

    pub fn pgp_identity_by_label(&self, label: &str) -> Result<(PgpIdentityRecord, Identity)> {
        let canonical = canonical_label(label)?;
        let mut stmt = self.master_conn.prepare(
            "SELECT label, key_index, private_key_hex, public_key_hex, is_active
             FROM pgp_identities WHERE label = ?1 LIMIT 1",
        )?;
        let mut rows = stmt.query(params![canonical])?;
        let row = rows
            .next()?
            .ok_or_else(|| Error::NotFound(format!("PGP identity label '{label}' not found")))?;

        let label: String = row.get(0)?;
        let key_index_i: i64 = row.get(1)?;
        let private_key_hex: String = row.get(2)?;
        let public_key_hex: String = row.get(3)?;
        let is_active_i: i64 = row.get(4)?;
        let key_index = u32::try_from(key_index_i)
            .map_err(|_| Error::Other(anyhow::anyhow!("invalid PGP key index in wallet")))?;
        let identity = Identity::from_hex(&private_key_hex)?;

        Ok((
            PgpIdentityRecord {
                label,
                key_index,
                public_key_hex,
                is_active: is_active_i == 1,
            },
            identity,
        ))
    }

    pub fn list_pgp_identities(&self) -> Result<Vec<PgpIdentityRecord>> {
        let mut stmt = self.master_conn.prepare(
            "SELECT label, key_index, public_key_hex, is_active
             FROM pgp_identities
             ORDER BY key_index ASC",
        )?;
        let rows = stmt.query_map([], |row| {
            let key_index: u32 = row.get(1)?;
            Ok(PgpIdentityRecord {
                label: row.get(0)?,
                key_index,
                public_key_hex: row.get(2)?,
                is_active: row.get::<_, i64>(3)? == 1,
            })
        })?;
        rows.collect::<std::result::Result<Vec<_>, _>>()
            .map_err(Error::Storage)
    }

    pub fn create_pgp_identity(&self, label: &str) -> Result<PgpIdentityRecord> {
        let canonical = canonical_label(label)?;
        let mut exists_stmt = self
            .master_conn
            .prepare("SELECT COUNT(*) FROM pgp_identities WHERE label = ?1")?;
        let exists: i64 = exists_stmt.query_row(params![canonical.clone()], |row| row.get(0))?;
        if exists > 0 {
            return Err(Error::Other(anyhow::anyhow!(
                "PGP identity label '{canonical}' already exists"
            )));
        }

        let key_index = self.next_pgp_key_index()?;
        let private_key_hex = self.derive_slot_hex("pgp", key_index)?;
        let identity = Identity::from_hex(&private_key_hex)?;
        let public_key_hex = identity.public_key_hex();

        self.master_conn.execute(
            "INSERT INTO pgp_identities (label, key_index, private_key_hex, public_key_hex, created_at, is_active)
             VALUES (?1, ?2, ?3, ?4, ?5, 0)",
            params![
                canonical,
                i64::from(key_index),
                private_key_hex,
                public_key_hex,
                chrono::Utc::now().to_rfc3339(),
            ],
        )?;

        self.refresh_slot_registry()?;
        self.pgp_identity_by_label(label).map(|(meta, _)| meta)
    }

    pub fn derive_pgp_identity_for_index(&self, key_index: u32) -> Result<Identity> {
        let private_key_hex = self.derive_slot_hex("pgp", key_index)?;
        Identity::from_hex(&private_key_hex)
    }

    pub fn ensure_pgp_identity_index(
        &self,
        key_index: u32,
        preferred_label: Option<&str>,
        set_active: bool,
    ) -> Result<PgpIdentityRecord> {
        if key_index >= MAX_PGP_KEYS {
            return Err(Error::Other(anyhow::anyhow!(
                "PGP key index out of range (max {})",
                MAX_PGP_KEYS - 1
            )));
        }

        let fallback_label = format!("pgp-{key_index}");
        let desired_raw = preferred_label.unwrap_or(fallback_label.as_str());
        let desired = canonical_label(desired_raw)?;
        let label = self.unique_pgp_label(&desired, key_index)?;
        let identity = self.derive_pgp_identity_for_index(key_index)?;
        let public_key_hex = identity.public_key_hex();

        let tx = self.master_conn.unchecked_transaction()?;
        tx.execute(
            "DELETE FROM pgp_identities WHERE key_index = ?1",
            params![i64::from(key_index)],
        )?;
        tx.execute(
            "DELETE FROM pgp_identities WHERE label = ?1",
            params![label.clone()],
        )?;
        if set_active {
            tx.execute("UPDATE pgp_identities SET is_active = 0", [])?;
        }
        tx.execute(
            "INSERT INTO pgp_identities (label, key_index, private_key_hex, public_key_hex, created_at, is_active)
             VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
            params![
                label.clone(),
                i64::from(key_index),
                identity.private_key_hex(),
                public_key_hex.clone(),
                chrono::Utc::now().to_rfc3339(),
                if set_active { 1 } else { 0 },
            ],
        )?;
        tx.commit()?;
        self.refresh_slot_registry()?;
        Ok(PgpIdentityRecord {
            label,
            key_index,
            public_key_hex,
            is_active: set_active,
        })
    }

    pub fn set_active_pgp_identity(&self, label: &str) -> Result<()> {
        let canonical = canonical_label(label)?;
        let mut exists_stmt = self
            .master_conn
            .prepare("SELECT COUNT(*) FROM pgp_identities WHERE label = ?1")?;
        let exists: i64 = exists_stmt.query_row(params![canonical.clone()], |row| row.get(0))?;
        if exists == 0 {
            return Err(Error::NotFound(format!(
                "PGP identity label '{canonical}' not found"
            )));
        }
        let tx = self.master_conn.unchecked_transaction()?;
        tx.execute("UPDATE pgp_identities SET is_active = 0", [])?;
        tx.execute(
            "UPDATE pgp_identities SET is_active = 1 WHERE label = ?1",
            params![canonical],
        )?;
        tx.commit()?;
        self.refresh_slot_registry()?;
        Ok(())
    }

    pub fn rename_pgp_label(&self, from: &str, to: &str) -> Result<()> {
        let from_c = canonical_label(from)?;
        let to_c = canonical_label(to)?;
        if from_c == to_c {
            return Ok(());
        }
        self.master_conn.execute(
            "UPDATE pgp_identities SET label = ?1 WHERE label = ?2",
            params![to_c, from_c],
        )?;
        self.refresh_slot_registry()?;
        Ok(())
    }

    fn next_pgp_key_index(&self) -> Result<u32> {
        let mut stmt = self
            .master_conn
            .prepare("SELECT COALESCE(MAX(key_index), -1) FROM pgp_identities")?;
        let max_idx: i64 = stmt.query_row([], |row| row.get(0))?;
        let next = max_idx.saturating_add(1);
        let next = u32::try_from(next)
            .map_err(|_| Error::Other(anyhow::anyhow!("too many PGP identities in wallet")))?;
        if next >= MAX_PGP_KEYS {
            return Err(Error::Other(anyhow::anyhow!(
                "PGP key index out of range (max {})",
                MAX_PGP_KEYS - 1
            )));
        }
        Ok(next)
    }

    fn next_vault_key_index(&self) -> Result<u32> {
        let mut stmt = self
            .master_conn
            .prepare("SELECT COALESCE(MAX(slot_index), 0) FROM wallet_slots WHERE family = ?1")?;
        let max_idx: i64 = stmt.query_row(params![SLOT_FAMILY_VAULT], |row| row.get(0))?;
        let next = max_idx.saturating_add(1);
        let next = u32::try_from(next)
            .map_err(|_| Error::Other(anyhow::anyhow!("too many vault identities in wallet")))?;
        if next >= MAX_VAULT_KEYS {
            return Err(Error::Other(anyhow::anyhow!(
                "vault key index out of range (max {})",
                MAX_VAULT_KEYS - 1
            )));
        }
        Ok(next)
    }

    fn unique_pgp_label(&self, desired: &str, key_index: u32) -> Result<String> {
        let mut candidate = desired.to_string();
        let mut suffix = 1u32;
        loop {
            let mut stmt = self
                .master_conn
                .prepare("SELECT key_index FROM pgp_identities WHERE label = ?1 LIMIT 1")?;
            let mut rows = stmt.query(params![candidate.clone()])?;
            let Some(row) = rows.next()? else {
                return Ok(candidate);
            };
            let existing: u32 = row.get(0)?;
            if existing == key_index {
                return Ok(candidate);
            }
            candidate = format!("{desired}-{suffix}");
            suffix = suffix.saturating_add(1);
        }
    }

    fn unique_vault_label(&self, desired: &str, key_index: u32) -> Result<String> {
        let mut candidate = desired.to_string();
        let mut suffix = 1u32;
        loop {
            let mut stmt = self.master_conn.prepare(
                "SELECT slot_index FROM wallet_slots
                 WHERE family = ?1 AND label = ?2
                 LIMIT 1",
            )?;
            let mut rows = stmt.query(params![SLOT_FAMILY_VAULT, candidate.clone()])?;
            let Some(row) = rows.next()? else {
                return Ok(candidate);
            };
            let existing_i: i64 = row.get(0)?;
            let existing = u32::try_from(existing_i).map_err(|_| {
                Error::Other(anyhow::anyhow!("invalid vault key index in wallet_slots"))
            })?;
            if existing == key_index {
                return Ok(candidate);
            }
            candidate = format!("{desired}-{suffix}");
            suffix = suffix.saturating_add(1);
        }
    }

    // ── Wallet metadata ──────────────────────────────────────────────────────

    pub fn fingerprint(&self) -> Result<String> {
        Ok(self.rgb_identity()?.fingerprint())
    }

    pub fn nickname(&self) -> Result<Option<String>> {
        metadata_value(&self.master_conn, META_NICKNAME)
    }

    pub fn set_nickname(&self, nick: &str) -> Result<()> {
        set_metadata_value(&self.master_conn, META_NICKNAME, nick)
    }

    pub fn wallet_label(&self) -> Result<Option<String>> {
        metadata_value(&self.master_conn, META_WALLET_LABEL)
    }

    pub fn set_wallet_label(&self, label: &str) -> Result<()> {
        let canonical = canonical_label(label)?;
        let out = set_metadata_value(&self.master_conn, META_WALLET_LABEL, &canonical);
        if out.is_ok() {
            let _ = self.refresh_slot_registry();
        }
        out
    }

    pub fn list_wallet_slots(&self, family: Option<&str>) -> Result<Vec<WalletSlotRecord>> {
        let sql = if family.is_some() {
            "SELECT family, slot_index, descriptor, db_rel_path, label, created_at, updated_at
             FROM wallet_slots
             WHERE family = ?1
             ORDER BY family ASC, slot_index ASC"
        } else {
            "SELECT family, slot_index, descriptor, db_rel_path, label, created_at, updated_at
             FROM wallet_slots
             ORDER BY family ASC, slot_index ASC"
        };
        let mut stmt = self.master_conn.prepare(sql)?;
        let mapper = |row: &rusqlite::Row<'_>| -> rusqlite::Result<WalletSlotRecord> {
            Ok(WalletSlotRecord {
                family: row.get(0)?,
                slot_index: row.get(1)?,
                descriptor: row.get(2)?,
                db_rel_path: row.get(3)?,
                label: row.get(4)?,
                created_at: row.get(5)?,
                updated_at: row.get(6)?,
            })
        };
        let rows = match family {
            Some(name) => stmt.query_map(params![name], mapper)?,
            None => stmt.query_map([], mapper)?,
        };
        rows.collect::<std::result::Result<Vec<_>, _>>()
            .map_err(Error::Storage)
    }

    // ── Payment audit ────────────────────────────────────────────────────────

    pub fn record_payment_attempt_start(&self, input: &NewPaymentAttempt<'_>) -> Result<String> {
        let attempt_id = format!("pay_{}", generate_secret_hex());
        let now = chrono::Utc::now().to_rfc3339();
        self.master_conn.execute(
            "INSERT INTO payment_attempts (
                attempt_id, created_at, updated_at, service_origin, endpoint_path,
                method, rail, action_hint, required_amount, payment_unit,
                payment_reference, request_hash, response_status, response_code,
                response_body, recovery_state, final_state
            ) VALUES (?1, ?2, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, NULL, NULL, NULL, 'pending', 'pending')",
            params![
                attempt_id,
                now,
                input.service_origin,
                input.endpoint_path,
                input.method,
                input.rail,
                input.action_hint,
                input.required_amount,
                input.payment_unit,
                input.payment_reference,
                input.request_hash,
            ],
        )?;
        Ok(attempt_id)
    }

    pub fn update_payment_attempt(
        &self,
        attempt_id: &str,
        update: &PaymentAttemptUpdate<'_>,
    ) -> Result<()> {
        self.master_conn.execute(
            "UPDATE payment_attempts
             SET updated_at = ?2,
                 payment_reference = COALESCE(?3, payment_reference),
                 response_status = ?4,
                 response_code = ?5,
                 response_body = ?6,
                 recovery_state = ?7,
                 final_state = ?8
             WHERE attempt_id = ?1",
            params![
                attempt_id,
                chrono::Utc::now().to_rfc3339(),
                update.payment_reference,
                update.response_status.map(i64::from),
                update.response_code,
                update.response_body,
                update.recovery_state,
                update.final_state,
            ],
        )?;
        Ok(())
    }

    pub fn store_payment_loss(
        &self,
        attempt_id: &str,
        service_origin: &str,
        endpoint_path: &str,
        method: &str,
        rail: &str,
        amount: &str,
        payment_reference: Option<&str>,
        failure_stage: &str,
        response_status: Option<u16>,
        response_code: Option<&str>,
        response_body: Option<&str>,
    ) -> Result<String> {
        let loss_id = format!("loss_{}", generate_secret_hex());
        let now = chrono::Utc::now().to_rfc3339();
        self.master_conn.execute(
            "INSERT INTO payment_losses (
                loss_id, attempt_id, created_at, service_origin, endpoint_path,
                method, rail, amount, payment_reference, failure_stage,
                response_status, response_code, response_body
             ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13)",
            params![
                loss_id,
                attempt_id,
                now,
                service_origin,
                endpoint_path,
                method,
                rail,
                amount,
                payment_reference,
                failure_stage,
                response_status.map(i64::from),
                response_code,
                response_body,
            ],
        )?;
        self.blacklist_if_needed(service_origin, endpoint_path, method, rail, &loss_id)?;
        Ok(loss_id)
    }

    pub fn list_payment_losses(&self) -> Result<Vec<PaymentLossRecord>> {
        let mut stmt = self.master_conn.prepare(
            "SELECT loss_id, attempt_id, created_at, service_origin, endpoint_path,
                    method, rail, amount, payment_reference, failure_stage,
                    response_status, response_code, response_body
             FROM payment_losses
             ORDER BY created_at DESC",
        )?;
        let rows = stmt.query_map([], |row| {
            Ok(PaymentLossRecord {
                loss_id: row.get(0)?,
                attempt_id: row.get(1)?,
                created_at: row.get(2)?,
                service_origin: row.get(3)?,
                endpoint_path: row.get(4)?,
                method: row.get(5)?,
                rail: row.get(6)?,
                amount: row.get(7)?,
                payment_reference: row.get(8)?,
                failure_stage: row.get(9)?,
                response_status: row
                    .get::<_, Option<i64>>(10)?
                    .and_then(|v| u16::try_from(v).ok()),
                response_code: row.get(11)?,
                response_body: row.get(12)?,
            })
        })?;
        rows.collect::<std::result::Result<Vec<_>, _>>()
            .map_err(Error::Storage)
    }

    pub fn list_payment_blacklist(&self) -> Result<Vec<PaymentBlacklistRecord>> {
        let mut stmt = self.master_conn.prepare(
            "SELECT service_origin, endpoint_path, method, rail,
                    blacklisted_until, reason, triggered_by_loss_id, created_at, updated_at
             FROM payment_blacklist
             ORDER BY updated_at DESC",
        )?;
        let rows = stmt.query_map([], |row| {
            Ok(PaymentBlacklistRecord {
                service_origin: row.get(0)?,
                endpoint_path: row.get(1)?,
                method: row.get(2)?,
                rail: row.get(3)?,
                blacklisted_until: row.get(4)?,
                reason: row.get(5)?,
                triggered_by_loss_id: row.get(6)?,
                created_at: row.get(7)?,
                updated_at: row.get(8)?,
            })
        })?;
        rows.collect::<std::result::Result<Vec<_>, _>>()
            .map_err(Error::Storage)
    }

    pub fn clear_payment_blacklist(
        &self,
        service_origin: &str,
        endpoint_path: &str,
        method: &str,
        rail: &str,
    ) -> Result<bool> {
        let changed = self.master_conn.execute(
            "DELETE FROM payment_blacklist
             WHERE service_origin = ?1 AND endpoint_path = ?2 AND method = ?3 AND rail = ?4",
            params![service_origin, endpoint_path, method, rail],
        )?;
        Ok(changed > 0)
    }

    pub fn payment_blacklist_entry(
        &self,
        service_origin: &str,
        endpoint_path: &str,
        method: &str,
        rail: &str,
    ) -> Result<Option<PaymentBlacklistRecord>> {
        let mut stmt = self.master_conn.prepare(
            "SELECT service_origin, endpoint_path, method, rail,
                    blacklisted_until, reason, triggered_by_loss_id, created_at, updated_at
             FROM payment_blacklist
             WHERE service_origin = ?1 AND endpoint_path = ?2 AND method = ?3 AND rail = ?4",
        )?;
        let mut rows = stmt.query(params![service_origin, endpoint_path, method, rail])?;
        let Some(row) = rows.next()? else {
            return Ok(None);
        };
        Ok(Some(PaymentBlacklistRecord {
            service_origin: row.get(0)?,
            endpoint_path: row.get(1)?,
            method: row.get(2)?,
            rail: row.get(3)?,
            blacklisted_until: row.get(4)?,
            reason: row.get(5)?,
            triggered_by_loss_id: row.get(6)?,
            created_at: row.get(7)?,
            updated_at: row.get(8)?,
        }))
    }

    pub fn is_payment_blacklisted(
        &self,
        service_origin: &str,
        endpoint_path: &str,
        method: &str,
        rail: &str,
    ) -> Result<bool> {
        let Some(entry) =
            self.payment_blacklist_entry(service_origin, endpoint_path, method, rail)?
        else {
            return Ok(false);
        };
        Ok(match entry.blacklisted_until.as_deref() {
            Some(until) => until >= chrono::Utc::now().to_rfc3339().as_str(),
            None => true,
        })
    }

    fn blacklist_if_needed(
        &self,
        service_origin: &str,
        endpoint_path: &str,
        method: &str,
        rail: &str,
        loss_id: &str,
    ) -> Result<()> {
        let cutoff = (chrono::Utc::now() - chrono::TimeDelta::hours(24)).to_rfc3339();
        let mut stmt = self.master_conn.prepare(
            "SELECT COUNT(*)
             FROM payment_losses
             WHERE service_origin = ?1
               AND endpoint_path = ?2
               AND method = ?3
               AND rail = ?4
               AND created_at >= ?5",
        )?;
        let count: i64 = stmt.query_row(
            params![service_origin, endpoint_path, method, rail, cutoff],
            |row| row.get(0),
        )?;
        if count < 3 {
            return Ok(());
        }

        let now = chrono::Utc::now().to_rfc3339();
        self.master_conn.execute(
            "INSERT OR REPLACE INTO payment_blacklist (
                service_origin, endpoint_path, method, rail, blacklisted_until,
                reason, triggered_by_loss_id, created_at, updated_at
             ) VALUES (
                ?1, ?2, ?3, ?4, NULL,
                'service returned errors after consuming payment 3 times in the last 24 hours',
                ?5,
                COALESCE((SELECT created_at FROM payment_blacklist
                          WHERE service_origin = ?1 AND endpoint_path = ?2 AND method = ?3 AND rail = ?4), ?6),
                ?6
             )",
            params![service_origin, endpoint_path, method, rail, loss_id, now],
        )?;
        Ok(())
    }

    pub fn record_payment_transaction(&self, input: &NewPaymentTransaction<'_>) -> Result<String> {
        let txn_id = format!("txn_{}", generate_secret_hex());
        let now = chrono::Utc::now().to_rfc3339();
        self.master_conn.execute(
            "INSERT INTO payment_transactions (
                txn_id, attempt_id, created_at, updated_at, occurred_at, direction, role,
                source_system, service_origin, frontend_kind, transport_kind, endpoint_path,
                method, session_id, action_kind, resource_ref, contract_ref, invoice_ref,
                challenge_id, rail, payment_unit, quoted_amount, settled_amount, fee_amount,
                proof_ref, proof_kind, payer_ref, payee_ref, request_hash, response_code,
                status, metadata_json
             ) VALUES (
                ?1, ?2, ?3, ?3, ?4, ?5, ?6,
                ?7, ?8, ?9, ?10, ?11,
                ?12, ?13, ?14, ?15, ?16, ?17,
                ?18, ?19, ?20, ?21, ?22, ?23,
                ?24, ?25, ?26, ?27, ?28, ?29,
                ?30, ?31
             )",
            params![
                txn_id,
                input.attempt_id,
                now,
                input.occurred_at.unwrap_or(&now),
                input.direction,
                input.role,
                input.source_system,
                input.service_origin,
                input.frontend_kind,
                input.transport_kind,
                input.endpoint_path,
                input.method,
                input.session_id,
                input.action_kind,
                input.resource_ref,
                input.contract_ref,
                input.invoice_ref,
                input.challenge_id,
                input.rail,
                input.payment_unit,
                input.quoted_amount,
                input.settled_amount,
                input.fee_amount,
                input.proof_ref,
                input.proof_kind,
                input.payer_ref,
                input.payee_ref,
                input.request_hash,
                input.response_code,
                input.status,
                input.metadata_json,
            ],
        )?;
        Ok(txn_id)
    }

    pub fn update_payment_transaction(
        &self,
        txn_id: &str,
        update: &PaymentTransactionUpdate<'_>,
    ) -> Result<()> {
        self.master_conn.execute(
            "UPDATE payment_transactions
             SET updated_at = ?2,
                 occurred_at = COALESCE(?3, occurred_at),
                 service_origin = COALESCE(?4, service_origin),
                 frontend_kind = COALESCE(?5, frontend_kind),
                 transport_kind = COALESCE(?6, transport_kind),
                 endpoint_path = COALESCE(?7, endpoint_path),
                 method = COALESCE(?8, method),
                 session_id = COALESCE(?9, session_id),
                 action_kind = COALESCE(?10, action_kind),
                 resource_ref = COALESCE(?11, resource_ref),
                 contract_ref = COALESCE(?12, contract_ref),
                 invoice_ref = COALESCE(?13, invoice_ref),
                 challenge_id = COALESCE(?14, challenge_id),
                 quoted_amount = COALESCE(?15, quoted_amount),
                 settled_amount = COALESCE(?16, settled_amount),
                 fee_amount = COALESCE(?17, fee_amount),
                 proof_ref = COALESCE(?18, proof_ref),
                 proof_kind = COALESCE(?19, proof_kind),
                 payer_ref = COALESCE(?20, payer_ref),
                 payee_ref = COALESCE(?21, payee_ref),
                 request_hash = COALESCE(?22, request_hash),
                 response_code = COALESCE(?23, response_code),
                 status = ?24,
                 metadata_json = COALESCE(?25, metadata_json)
             WHERE txn_id = ?1",
            params![
                txn_id,
                chrono::Utc::now().to_rfc3339(),
                update.occurred_at,
                update.service_origin,
                update.frontend_kind,
                update.transport_kind,
                update.endpoint_path,
                update.method,
                update.session_id,
                update.action_kind,
                update.resource_ref,
                update.contract_ref,
                update.invoice_ref,
                update.challenge_id,
                update.quoted_amount,
                update.settled_amount,
                update.fee_amount,
                update.proof_ref,
                update.proof_kind,
                update.payer_ref,
                update.payee_ref,
                update.request_hash,
                update.response_code,
                update.status,
                update.metadata_json,
            ],
        )?;
        Ok(())
    }

    pub fn append_payment_transaction_event(
        &self,
        event: &NewPaymentTransactionEvent<'_>,
    ) -> Result<String> {
        let event_id = format!("txe_{}", generate_secret_hex());
        let now = chrono::Utc::now().to_rfc3339();
        self.master_conn.execute(
            "INSERT INTO payment_transaction_events (
                event_id, txn_id, created_at, event_type, status, actor, details_json
             ) VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7)",
            params![
                event_id,
                event.txn_id,
                now,
                event.event_type,
                event.status,
                event.actor,
                event.details_json.unwrap_or("{}"),
            ],
        )?;
        Ok(event_id)
    }

    pub fn list_payment_transactions(&self) -> Result<Vec<PaymentTransactionRecord>> {
        let mut stmt = self.master_conn.prepare(
            "SELECT txn_id, attempt_id, created_at, updated_at, occurred_at, direction, role,
                    source_system, service_origin, frontend_kind, transport_kind, endpoint_path,
                    method, session_id, action_kind, resource_ref, contract_ref, invoice_ref,
                    challenge_id, rail, payment_unit, quoted_amount, settled_amount, fee_amount,
                    proof_ref, proof_kind, payer_ref, payee_ref, request_hash, response_code,
                    status, metadata_json
             FROM payment_transactions
             ORDER BY occurred_at DESC, created_at DESC",
        )?;
        let rows = stmt.query_map([], |row| {
            Ok(PaymentTransactionRecord {
                txn_id: row.get(0)?,
                attempt_id: row.get(1)?,
                created_at: row.get(2)?,
                updated_at: row.get(3)?,
                occurred_at: row.get(4)?,
                direction: row.get(5)?,
                role: row.get(6)?,
                source_system: row.get(7)?,
                service_origin: row.get(8)?,
                frontend_kind: row.get(9)?,
                transport_kind: row.get(10)?,
                endpoint_path: row.get(11)?,
                method: row.get(12)?,
                session_id: row.get(13)?,
                action_kind: row.get(14)?,
                resource_ref: row.get(15)?,
                contract_ref: row.get(16)?,
                invoice_ref: row.get(17)?,
                challenge_id: row.get(18)?,
                rail: row.get(19)?,
                payment_unit: row.get(20)?,
                quoted_amount: row.get(21)?,
                settled_amount: row.get(22)?,
                fee_amount: row.get(23)?,
                proof_ref: row.get(24)?,
                proof_kind: row.get(25)?,
                payer_ref: row.get(26)?,
                payee_ref: row.get(27)?,
                request_hash: row.get(28)?,
                response_code: row.get(29)?,
                status: row.get(30)?,
                metadata_json: row.get(31)?,
            })
        })?;
        rows.collect::<std::result::Result<Vec<_>, _>>()
            .map_err(Error::Storage)
    }

    pub fn list_payment_transaction_events(
        &self,
        txn_id: Option<&str>,
    ) -> Result<Vec<PaymentTransactionEventRecord>> {
        let sql = if txn_id.is_some() {
            "SELECT event_id, txn_id, created_at, event_type, status, actor, details_json
             FROM payment_transaction_events
             WHERE txn_id = ?1
             ORDER BY created_at ASC"
        } else {
            "SELECT event_id, txn_id, created_at, event_type, status, actor, details_json
             FROM payment_transaction_events
             ORDER BY created_at ASC"
        };
        let mut stmt = self.master_conn.prepare(sql)?;
        let mapper = |row: &rusqlite::Row<'_>| -> std::result::Result<PaymentTransactionEventRecord, rusqlite::Error> {
            Ok(PaymentTransactionEventRecord {
                event_id: row.get(0)?,
                txn_id: row.get(1)?,
                created_at: row.get(2)?,
                event_type: row.get(3)?,
                status: row.get(4)?,
                actor: row.get(5)?,
                details_json: row
                    .get::<_, Option<String>>(6)?
                    .filter(|value| !value.is_empty() && value != "{}"),
            })
        };
        let rows = match txn_id {
            Some(id) => stmt.query_map(params![id], mapper)?,
            None => stmt.query_map([], mapper)?,
        };
        rows.collect::<std::result::Result<Vec<_>, _>>()
            .map_err(Error::Storage)
    }

    // ── Contracts ─────────────────────────────────────────────────────────────

    pub fn store_contract(&self, c: &Contract) -> Result<()> {
        self.with_identity_conn(|conn| {
            conn.execute(
                "INSERT OR REPLACE INTO contracts (
                contract_id, contract_type, status, witness_secret, witness_proof,
                amount_units, work_spec, buyer_fingerprint, seller_fingerprint,
                reference_post, delivery_deadline, role, delivered_text,
                certificate_id, created_at, updated_at
            ) VALUES (?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13,?14,?15,?16)",
                params![
                    c.contract_id,
                    c.contract_type.as_str(),
                    c.status.as_str(),
                    c.witness_secret,
                    c.witness_proof,
                    c.amount_units as i64,
                    c.work_spec,
                    c.buyer_fingerprint,
                    c.seller_fingerprint,
                    c.reference_post,
                    c.delivery_deadline,
                    c.role.as_str(),
                    c.delivered_text,
                    c.certificate_id,
                    c.created_at,
                    c.updated_at,
                ],
            )?;
            Ok(())
        })
    }

    pub fn update_contract(&self, c: &Contract) -> Result<()> {
        self.store_contract(c)
    }

    pub fn get_contract(&self, id: &str) -> Result<Option<Contract>> {
        self.with_identity_conn(|conn| {
            let mut stmt = conn.prepare(
                "SELECT contract_id, contract_type, status, witness_secret, witness_proof,
                    amount_units, work_spec, buyer_fingerprint, seller_fingerprint,
                    reference_post, delivery_deadline, role, delivered_text,
                    certificate_id, created_at, updated_at
                 FROM contracts WHERE contract_id = ?1",
            )?;
            let mut rows = stmt.query(params![id])?;
            if let Some(row) = rows.next()? {
                Ok(Some(row_to_contract(row)?))
            } else {
                Ok(None)
            }
        })
    }

    pub fn list_contracts(&self) -> Result<Vec<Contract>> {
        self.with_identity_conn(|conn| {
            let mut stmt = conn.prepare(
                "SELECT contract_id, contract_type, status, witness_secret, witness_proof,
                    amount_units, work_spec, buyer_fingerprint, seller_fingerprint,
                    reference_post, delivery_deadline, role, delivered_text,
                    certificate_id, created_at, updated_at
                 FROM contracts ORDER BY created_at DESC",
            )?;
            let rows = stmt.query_map([], |row| {
                row_to_contract(row)
                    .map_err(|e| rusqlite::Error::ToSqlConversionFailure(Box::new(e)))
            })?;
            rows.collect::<std::result::Result<Vec<_>, _>>()
                .map_err(Error::Storage)
        })
    }

    // ── Certificates ──────────────────────────────────────────────────────────

    pub fn store_certificate(&self, cert: &Certificate) -> Result<()> {
        self.with_identity_conn(|conn| {
            conn.execute(
                "INSERT OR REPLACE INTO certificates (
                certificate_id, contract_id, witness_secret, witness_proof, created_at
            ) VALUES (?1,?2,?3,?4,?5)",
                params![
                    cert.certificate_id,
                    cert.contract_id,
                    cert.witness_secret,
                    cert.witness_proof,
                    cert.created_at,
                ],
            )?;
            Ok(())
        })
    }

    pub fn list_certificates(&self) -> Result<Vec<Certificate>> {
        self.with_identity_conn(|conn| {
            let mut stmt = conn.prepare(
                "SELECT certificate_id, contract_id, witness_secret, witness_proof, created_at
             FROM certificates ORDER BY created_at DESC",
            )?;
            let rows = stmt.query_map([], |row| {
                Ok(Certificate {
                    certificate_id: row.get(0)?,
                    contract_id: row.get(1)?,
                    witness_secret: row.get(2)?,
                    witness_proof: row.get(3)?,
                    created_at: row.get(4)?,
                })
            })?;
            rows.collect::<std::result::Result<Vec<_>, _>>()
                .map_err(Error::Storage)
        })
    }

    // ── Snapshot ──────────────────────────────────────────────────────────────

    pub fn export_snapshot(&self) -> Result<WalletSnapshot> {
        let rgb_id = self.rgb_identity()?;
        let root = self.root_private_key_hex()?;
        let mut stmt = self.master_conn.prepare(
            "SELECT label, key_index, private_key_hex, is_active
             FROM pgp_identities
             ORDER BY key_index ASC",
        )?;
        let pgp_rows = stmt.query_map([], |row| {
            let key_index: u32 = row.get(1)?;
            Ok(PgpIdentitySnapshot {
                label: row.get(0)?,
                key_index,
                private_key_hex: row.get(2)?,
                is_active: row.get::<_, i64>(3)? == 1,
            })
        })?;

        let pgp_identities = pgp_rows
            .collect::<std::result::Result<Vec<_>, _>>()
            .map_err(Error::Storage)?;

        Ok(WalletSnapshot {
            private_key_hex: rgb_id.private_key_hex(),
            root_private_key_hex: Some(root),
            root_mnemonic: Some(self.export_recovery_mnemonic()?),
            wallet_label: self.wallet_label()?,
            pgp_identities,
            nickname: self.nickname()?,
            contracts: self.list_contracts()?,
            certificates: self.list_certificates()?,
        })
    }

    pub fn import_snapshot(&self, snap: &WalletSnapshot) -> Result<()> {
        let keychain = if let Some(words) = snap
            .root_mnemonic
            .as_deref()
            .map(str::trim)
            .filter(|s| !s.is_empty())
        {
            HdKeychain::from_mnemonic_words(words)?
        } else {
            let root = snap.root_private_key_hex.clone().ok_or_else(|| {
                Error::Other(anyhow::anyhow!(
                    "snapshot missing root mnemonic/entropy; master key is mandatory"
                ))
            })?;
            HdKeychain::from_entropy_hex(&root)?
        };
        if let Some(root_hex) = &snap.root_private_key_hex {
            if !root_hex.trim().is_empty()
                && !root_hex.eq_ignore_ascii_case(&keychain.entropy_hex())
            {
                return Err(Error::Other(anyhow::anyhow!(
                    "snapshot root entropy does not match mnemonic entropy"
                )));
            }
        }
        let derived_rgb = keychain.derive_slot_hex("rgb", 0)?;
        if !snap.private_key_hex.trim().is_empty()
            && !snap.private_key_hex.eq_ignore_ascii_case(&derived_rgb)
        {
            return Err(Error::Other(anyhow::anyhow!(
                "snapshot RGB key does not match the derived RGB slot from root key"
            )));
        }

        self.set_master_keychain_material(&keychain)?;

        if let Some(label) = &snap.wallet_label {
            self.set_wallet_label(label)?;
        }

        if let Some(nick) = &snap.nickname {
            self.set_nickname(nick)?;
        }

        let tx = self.master_conn.unchecked_transaction()?;
        tx.execute("DELETE FROM pgp_identities", [])?;
        if snap.pgp_identities.is_empty() {
            let wallet_label = self
                .wallet_label()?
                .unwrap_or_else(|| "default".to_string());
            let private_key_hex = keychain.derive_slot_hex("pgp", 0)?;
            let id = Identity::from_hex(&private_key_hex)?;
            tx.execute(
                "INSERT INTO pgp_identities (label, key_index, private_key_hex, public_key_hex, created_at, is_active)
                 VALUES (?1, 0, ?2, ?3, ?4, 1)",
                params![
                    wallet_label,
                    private_key_hex,
                    id.public_key_hex(),
                    chrono::Utc::now().to_rfc3339(),
                ],
            )?;
        } else {
            let mut saw_active = false;
            for rec in &snap.pgp_identities {
                let label = canonical_label(&rec.label)?;
                let id = Identity::from_hex(&rec.private_key_hex)?;
                let active = if rec.is_active && !saw_active {
                    saw_active = true;
                    1
                } else {
                    0
                };
                tx.execute(
                    "INSERT INTO pgp_identities (label, key_index, private_key_hex, public_key_hex, created_at, is_active)
                     VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
                    params![
                        label,
                        i64::from(rec.key_index),
                        rec.private_key_hex,
                        id.public_key_hex(),
                        chrono::Utc::now().to_rfc3339(),
                        active,
                    ],
                )?;
            }
            if !saw_active {
                tx.execute(
                    "UPDATE pgp_identities SET is_active = 1 WHERE key_index = (
                        SELECT MIN(key_index) FROM pgp_identities
                    )",
                    [],
                )?;
            }
        }
        tx.commit()?;

        self.with_identity_conn(|conn| {
            conn.execute("DELETE FROM contracts", [])?;
            conn.execute("DELETE FROM certificates", [])?;
            for c in &snap.contracts {
                conn.execute(
                    "INSERT OR REPLACE INTO contracts (
                        contract_id, contract_type, status, witness_secret, witness_proof,
                        amount_units, work_spec, buyer_fingerprint, seller_fingerprint,
                        reference_post, delivery_deadline, role, delivered_text,
                        certificate_id, created_at, updated_at
                    ) VALUES (?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13,?14,?15,?16)",
                    params![
                        c.contract_id,
                        c.contract_type.as_str(),
                        c.status.as_str(),
                        c.witness_secret,
                        c.witness_proof,
                        c.amount_units as i64,
                        c.work_spec,
                        c.buyer_fingerprint,
                        c.seller_fingerprint,
                        c.reference_post,
                        c.delivery_deadline,
                        c.role.as_str(),
                        c.delivered_text,
                        c.certificate_id,
                        c.created_at,
                        c.updated_at,
                    ],
                )?;
            }
            for cert in &snap.certificates {
                conn.execute(
                    "INSERT OR REPLACE INTO certificates (
                        certificate_id, contract_id, witness_secret, witness_proof, created_at
                    ) VALUES (?1,?2,?3,?4,?5)",
                    params![
                        cert.certificate_id,
                        cert.contract_id,
                        cert.witness_secret,
                        cert.witness_proof,
                        cert.created_at,
                    ],
                )?;
            }
            Ok(())
        })?;

        self.refresh_slot_registry()?;
        Ok(())
    }
}

fn same_path(a: &Path, b: &Path) -> bool {
    match (std::fs::canonicalize(a), std::fs::canonicalize(b)) {
        (Ok(left), Ok(right)) => left == right,
        _ => a == b,
    }
}

fn migrate_rgb_state(source_conn: &Connection, target_conn: &Connection) -> Result<()> {
    if table_exists(source_conn, "contracts")? {
        let mut stmt = source_conn.prepare(
            "SELECT contract_id, contract_type, status, witness_secret, witness_proof,
                    amount_units, work_spec, buyer_fingerprint, seller_fingerprint,
                    reference_post, delivery_deadline, role, delivered_text,
                    certificate_id, created_at, updated_at
             FROM contracts",
        )?;
        let rows = stmt.query_map([], |row| {
            Ok(Contract {
                contract_id: row.get(0)?,
                contract_type: ContractType::parse(&row.get::<_, String>(1)?)
                    .map_err(|e| rusqlite::Error::ToSqlConversionFailure(Box::new(e)))?,
                status: ContractStatus::parse(&row.get::<_, String>(2)?)
                    .map_err(|e| rusqlite::Error::ToSqlConversionFailure(Box::new(e)))?,
                witness_secret: row.get(3)?,
                witness_proof: row.get(4)?,
                amount_units: row.get::<_, i64>(5)? as u64,
                work_spec: row.get(6)?,
                buyer_fingerprint: row.get(7)?,
                seller_fingerprint: row.get(8)?,
                reference_post: row.get(9)?,
                delivery_deadline: row.get(10)?,
                role: Role::parse(&row.get::<_, String>(11)?)
                    .map_err(|e| rusqlite::Error::ToSqlConversionFailure(Box::new(e)))?,
                delivered_text: row.get(12)?,
                certificate_id: row.get(13)?,
                created_at: row.get(14)?,
                updated_at: row.get(15)?,
                arbitration_profit_wats: None,
                seller_value_wats: None,
            })
        })?;
        for row in rows {
            let c = row?;
            target_conn.execute(
                "INSERT OR REPLACE INTO contracts (
                    contract_id, contract_type, status, witness_secret, witness_proof,
                    amount_units, work_spec, buyer_fingerprint, seller_fingerprint,
                    reference_post, delivery_deadline, role, delivered_text,
                    certificate_id, created_at, updated_at
                ) VALUES (?1,?2,?3,?4,?5,?6,?7,?8,?9,?10,?11,?12,?13,?14,?15,?16)",
                params![
                    c.contract_id,
                    c.contract_type.as_str(),
                    c.status.as_str(),
                    c.witness_secret,
                    c.witness_proof,
                    c.amount_units as i64,
                    c.work_spec,
                    c.buyer_fingerprint,
                    c.seller_fingerprint,
                    c.reference_post,
                    c.delivery_deadline,
                    c.role.as_str(),
                    c.delivered_text,
                    c.certificate_id,
                    c.created_at,
                    c.updated_at,
                ],
            )?;
        }
    }

    if table_exists(source_conn, "certificates")? {
        let mut stmt = source_conn.prepare(
            "SELECT certificate_id, contract_id, witness_secret, witness_proof, created_at
             FROM certificates",
        )?;
        let rows = stmt.query_map([], |row| {
            Ok(Certificate {
                certificate_id: row.get(0)?,
                contract_id: row.get(1)?,
                witness_secret: row.get(2)?,
                witness_proof: row.get(3)?,
                created_at: row.get(4)?,
            })
        })?;
        for row in rows {
            let cert = row?;
            target_conn.execute(
                "INSERT OR REPLACE INTO certificates (
                    certificate_id, contract_id, witness_secret, witness_proof, created_at
                ) VALUES (?1,?2,?3,?4,?5)",
                params![
                    cert.certificate_id,
                    cert.contract_id,
                    cert.witness_secret,
                    cert.witness_proof,
                    cert.created_at,
                ],
            )?;
        }
    }

    if table_exists(source_conn, "timeline_posts")? {
        let mut stmt = source_conn
            .prepare("SELECT post_id, created_at, updated_at, metadata_json FROM timeline_posts")?;
        let rows = stmt.query_map([], |row| {
            Ok((
                row.get::<_, String>(0)?,
                row.get::<_, String>(1)?,
                row.get::<_, String>(2)?,
                row.get::<_, String>(3)?,
            ))
        })?;
        for row in rows {
            let (post_id, created_at, updated_at, metadata_json) = row?;
            target_conn.execute(
                "INSERT OR REPLACE INTO timeline_posts (post_id, created_at, updated_at, metadata_json)
                 VALUES (?1, ?2, ?3, ?4)",
                params![post_id, created_at, updated_at, metadata_json],
            )?;
        }
    }

    if table_exists(source_conn, "timeline_comments")? {
        let mut stmt = source_conn.prepare(
            "SELECT comment_id, post_id, created_at, updated_at, metadata_json FROM timeline_comments",
        )?;
        let rows = stmt.query_map([], |row| {
            Ok((
                row.get::<_, String>(0)?,
                row.get::<_, String>(1)?,
                row.get::<_, String>(2)?,
                row.get::<_, String>(3)?,
                row.get::<_, String>(4)?,
            ))
        })?;
        for row in rows {
            let (comment_id, post_id, created_at, updated_at, metadata_json) = row?;
            target_conn.execute(
                "INSERT OR REPLACE INTO timeline_comments (comment_id, post_id, created_at, updated_at, metadata_json)
                 VALUES (?1, ?2, ?3, ?4, ?5)",
                params![comment_id, post_id, created_at, updated_at, metadata_json],
            )?;
        }
    }

    if table_exists(source_conn, "timeline_bids")? {
        let mut stmt = source_conn.prepare(
            "SELECT bid_post_id, contract_id, service_post_id, created_at, metadata_json FROM timeline_bids",
        )?;
        let rows = stmt.query_map([], |row| {
            Ok((
                row.get::<_, String>(0)?,
                row.get::<_, String>(1)?,
                row.get::<_, String>(2)?,
                row.get::<_, String>(3)?,
                row.get::<_, String>(4)?,
            ))
        })?;
        for row in rows {
            let (bid_post_id, contract_id, service_post_id, created_at, metadata_json) = row?;
            target_conn.execute(
                "INSERT OR REPLACE INTO timeline_bids (bid_post_id, contract_id, service_post_id, created_at, metadata_json)
                 VALUES (?1, ?2, ?3, ?4, ?5)",
                params![
                    bid_post_id,
                    contract_id,
                    service_post_id,
                    created_at,
                    metadata_json
                ],
            )?;
        }
    }
    Ok(())
}

fn migrate_identity_schema(conn: &Connection) -> Result<()> {
    ensure_columns(
        conn,
        "contracts",
        &[
            ("contract_type", "TEXT NOT NULL DEFAULT 'service'"),
            ("status", "TEXT NOT NULL DEFAULT 'issued'"),
            ("witness_secret", "TEXT"),
            ("witness_proof", "TEXT"),
            ("amount_units", "INTEGER NOT NULL DEFAULT 0"),
            ("work_spec", "TEXT NOT NULL DEFAULT ''"),
            ("buyer_fingerprint", "TEXT NOT NULL DEFAULT ''"),
            ("seller_fingerprint", "TEXT"),
            ("reference_post", "TEXT"),
            ("delivery_deadline", "TEXT"),
            ("role", "TEXT NOT NULL DEFAULT 'buyer'"),
            ("delivered_text", "TEXT"),
            ("certificate_id", "TEXT"),
            ("created_at", "TEXT NOT NULL DEFAULT ''"),
            ("updated_at", "TEXT NOT NULL DEFAULT ''"),
        ],
    )?;
    ensure_columns(
        conn,
        "certificates",
        &[
            ("contract_id", "TEXT"),
            ("witness_secret", "TEXT"),
            ("witness_proof", "TEXT"),
            ("created_at", "TEXT NOT NULL DEFAULT ''"),
        ],
    )?;
    Ok(())
}

fn migrate_identity_schema_if_present(conn: &Connection) -> Result<()> {
    if table_exists(conn, "contracts")? {
        ensure_columns(
            conn,
            "contracts",
            &[
                ("contract_type", "TEXT NOT NULL DEFAULT 'service'"),
                ("status", "TEXT NOT NULL DEFAULT 'issued'"),
                ("witness_secret", "TEXT"),
                ("witness_proof", "TEXT"),
                ("amount_units", "INTEGER NOT NULL DEFAULT 0"),
                ("work_spec", "TEXT NOT NULL DEFAULT ''"),
                ("buyer_fingerprint", "TEXT NOT NULL DEFAULT ''"),
                ("seller_fingerprint", "TEXT"),
                ("reference_post", "TEXT"),
                ("delivery_deadline", "TEXT"),
                ("role", "TEXT NOT NULL DEFAULT 'buyer'"),
                ("delivered_text", "TEXT"),
                ("certificate_id", "TEXT"),
                ("created_at", "TEXT NOT NULL DEFAULT ''"),
                ("updated_at", "TEXT NOT NULL DEFAULT ''"),
            ],
        )?;
    }
    if table_exists(conn, "certificates")? {
        ensure_columns(
            conn,
            "certificates",
            &[
                ("contract_id", "TEXT"),
                ("witness_secret", "TEXT"),
                ("witness_proof", "TEXT"),
                ("created_at", "TEXT NOT NULL DEFAULT ''"),
            ],
        )?;
    }
    Ok(())
}

/// Write a metadata value only when it differs from what is already stored,
/// avoiding unnecessary SQLite writes on every wallet open.
fn set_metadata_if_changed(conn: &Connection, key: &str, value: &str) -> Result<()> {
    if metadata_value(conn, key)?.as_deref() == Some(value) {
        return Ok(());
    }
    set_metadata_value(conn, key, value)
}

fn ensure_root_and_identity_materialized(conn: &Connection, allow_generate: bool) -> Result<()> {
    let mnemonic = metadata_value(conn, META_ROOT_MNEMONIC)?
        .map(|v| v.trim().to_string())
        .filter(|v| !v.is_empty());
    let entropy_hex = metadata_value(conn, META_ROOT_PRIVATE_KEY_HEX)?
        .map(|v| v.trim().to_string())
        .filter(|v| !v.is_empty());
    let keychain = if let Some(words) = mnemonic.as_deref() {
        HdKeychain::from_mnemonic_words(words)?
    } else if let Some(root_hex) = entropy_hex.as_deref() {
        HdKeychain::from_entropy_hex(root_hex)?
    } else if allow_generate {
        HdKeychain::generate_new()?
    } else {
        return Err(Error::KeyMaterialMissing(
            "root_mnemonic / root_private_key_hex missing; \
             refusing to generate new keys for an existing wallet \
             — restore from backup or re-import your mnemonic"
                .into(),
        ));
    };

    set_metadata_if_changed(conn, META_ROOT_PRIVATE_KEY_HEX, &keychain.entropy_hex())?;
    set_metadata_if_changed(conn, META_ROOT_MNEMONIC, &keychain.mnemonic_words())?;
    set_metadata_if_changed(
        conn,
        META_RGB_PRIVATE_KEY_HEX,
        &keychain.derive_slot_hex("rgb", 0)?,
    )?;
    set_metadata_if_changed(conn, META_KEY_MODEL_VERSION, KEY_MODEL_VERSION_V3)?;

    if metadata_value(conn, META_WALLET_LABEL)?.is_none() {
        set_metadata_value(conn, META_WALLET_LABEL, "default")?;
    }

    Ok(())
}

fn ensure_default_pgp_identity(conn: &Connection) -> Result<()> {
    let keychain = keychain_from_metadata(conn)?;
    let mut count_stmt = conn.prepare("SELECT COUNT(*) FROM pgp_identities")?;
    let count: i64 = count_stmt.query_row([], |row| row.get(0))?;
    if count > 0 {
        // Canonicalize all PGP private/public keys from deterministic BIP32 slots.
        let mut stmt = conn.prepare(
            "SELECT label, key_index, created_at, is_active
             FROM pgp_identities
             ORDER BY key_index ASC",
        )?;
        let rows = stmt.query_map([], |row| {
            Ok((
                row.get::<_, String>(0)?,
                row.get::<_, u32>(1)?,
                row.get::<_, String>(2)?,
                row.get::<_, i64>(3)?,
            ))
        })?;
        let mut entries = Vec::new();
        for row in rows {
            let (label, key_index, created_at, is_active) = row?;
            let private_key_hex = keychain.derive_slot_hex("pgp", key_index)?;
            let identity = Identity::from_hex(&private_key_hex)?;
            entries.push((
                canonical_label(&label)?,
                key_index,
                private_key_hex,
                identity.public_key_hex(),
                created_at,
                is_active,
            ));
        }
        let tx = conn.unchecked_transaction()?;
        tx.execute("DELETE FROM pgp_identities", [])?;
        let mut saw_active = false;
        for (label, key_index, private_key_hex, public_key_hex, created_at, was_active) in entries {
            let is_active = if was_active == 1 && !saw_active {
                saw_active = true;
                1
            } else {
                0
            };
            tx.execute(
                "INSERT INTO pgp_identities (label, key_index, private_key_hex, public_key_hex, created_at, is_active)
                 VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
                params![
                    label,
                    i64::from(key_index),
                    private_key_hex,
                    public_key_hex,
                    created_at,
                    is_active,
                ],
            )?;
        }
        if !saw_active {
            tx.execute(
                "UPDATE pgp_identities SET is_active = 1 WHERE key_index = (
                    SELECT MIN(key_index) FROM pgp_identities
                )",
                [],
            )?;
        }
        tx.commit()?;
        return Ok(());
    }

    let wallet_label =
        metadata_value(conn, META_WALLET_LABEL)?.unwrap_or_else(|| "default".to_string());
    let private_key_hex = keychain.derive_slot_hex("pgp", 0)?;

    let identity = Identity::from_hex(&private_key_hex)?;
    conn.execute(
        "INSERT INTO pgp_identities (label, key_index, private_key_hex, public_key_hex, created_at, is_active)
         VALUES (?1, 0, ?2, ?3, ?4, 1)",
        params![
            canonical_label(&wallet_label)?,
            private_key_hex,
            identity.public_key_hex(),
            chrono::Utc::now().to_rfc3339(),
        ],
    )?;
    Ok(())
}

fn keychain_from_metadata(conn: &Connection) -> Result<HdKeychain> {
    if let Some(words) = metadata_value(conn, META_ROOT_MNEMONIC)? {
        return HdKeychain::from_mnemonic_words(&words);
    }
    let entropy_hex = metadata_value(conn, META_ROOT_PRIVATE_KEY_HEX)?.ok_or_else(|| {
        Error::Other(anyhow::anyhow!("missing master entropy in wallet metadata"))
    })?;
    HdKeychain::from_entropy_hex(&entropy_hex)
}

fn set_metadata_value(conn: &Connection, key: &str, value: &str) -> Result<()> {
    conn.execute(
        "INSERT OR REPLACE INTO wallet_metadata (key, value) VALUES (?1, ?2)",
        params![key, value],
    )?;
    Ok(())
}

fn metadata_value(conn: &Connection, key: &str) -> Result<Option<String>> {
    let mut stmt = conn.prepare("SELECT value FROM wallet_metadata WHERE key = ?1")?;
    let mut rows = stmt.query(params![key])?;
    let Some(row) = rows.next()? else {
        return Ok(None);
    };
    Ok(Some(row.get(0)?))
}

fn table_exists(conn: &Connection, table: &str) -> Result<bool> {
    let mut stmt = conn
        .prepare("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name = ?1 LIMIT 1")?;
    let count: i64 = stmt.query_row(params![table], |row| row.get(0))?;
    Ok(count > 0)
}

fn canonical_label(label: &str) -> Result<String> {
    let canonical = label.trim();
    if canonical.is_empty() {
        return Err(Error::Other(anyhow::anyhow!("label cannot be empty")));
    }
    if canonical.len() > 64 {
        return Err(Error::Other(anyhow::anyhow!(
            "label too long (max 64 chars)"
        )));
    }
    Ok(canonical.to_string())
}

fn ensure_columns(conn: &Connection, table: &str, required: &[(&str, &str)]) -> Result<()> {
    let existing = current_columns(conn, table)?;
    for (name, ddl) in required {
        if existing.contains(*name) {
            continue;
        }
        let sql = format!("ALTER TABLE {table} ADD COLUMN {name} {ddl}");
        conn.execute(&sql, [])?;
    }
    Ok(())
}

fn current_columns(conn: &Connection, table: &str) -> Result<HashSet<String>> {
    let mut columns = HashSet::new();
    let sql = format!("PRAGMA table_info({table})");
    let mut stmt = conn.prepare(&sql)?;
    let mut rows = stmt.query([])?;
    while let Some(row) = rows.next()? {
        let name: String = row.get(1)?;
        columns.insert(name);
    }
    Ok(columns)
}

fn row_to_contract(row: &rusqlite::Row<'_>) -> Result<Contract> {
    let amount_units_i: i64 = row.get(5)?;
    Ok(Contract {
        contract_id: row.get(0)?,
        contract_type: ContractType::parse(&row.get::<_, String>(1)?)?,
        status: ContractStatus::parse(&row.get::<_, String>(2)?)?,
        witness_secret: row.get(3)?,
        witness_proof: row.get(4)?,
        amount_units: amount_units_i as u64,
        work_spec: row.get(6)?,
        buyer_fingerprint: row.get(7)?,
        seller_fingerprint: row.get(8)?,
        reference_post: row.get(9)?,
        delivery_deadline: row.get(10)?,
        role: Role::parse(&row.get::<_, String>(11)?)?,
        delivered_text: row.get(12)?,
        certificate_id: row.get(13)?,
        created_at: row.get(14)?,
        updated_at: row.get(15)?,
        arbitration_profit_wats: None,
        seller_value_wats: None,
    })
}

#[cfg(test)]
mod tests {
    use super::{
        NewPaymentTransaction, NewPaymentTransactionEvent, PaymentTransactionUpdate, RgbWallet,
    };

    #[test]
    fn payment_transactions_round_trip_in_memory_wallet() {
        let wallet = RgbWallet::open_memory().expect("memory wallet");
        let txn_id = wallet
            .record_payment_transaction(&NewPaymentTransaction {
                attempt_id: Some("pay_123"),
                occurred_at: Some("2026-03-17T10:00:00Z"),
                direction: "inbound",
                role: "payee",
                source_system: "harmonia",
                service_origin: Some("https://node.example"),
                frontend_kind: Some("http2"),
                transport_kind: Some("http2"),
                endpoint_path: Some("/v1/session"),
                method: Some("POST"),
                session_id: Some("session-1"),
                action_kind: "identity-claim",
                resource_ref: Some("identity:alice"),
                contract_ref: None,
                invoice_ref: None,
                challenge_id: Some("challenge-1"),
                rail: "webcash",
                payment_unit: "wats",
                quoted_amount: Some("42"),
                settled_amount: None,
                fee_amount: None,
                proof_ref: None,
                proof_kind: None,
                payer_ref: Some("payer:alice"),
                payee_ref: Some("payee:harmonia"),
                request_hash: Some("hash-1"),
                response_code: Some("payment_required"),
                status: "challenge_received",
                metadata_json: Some("{\"carrier\":\"http2\"}"),
            })
            .expect("record transaction");
        wallet
            .append_payment_transaction_event(&NewPaymentTransactionEvent {
                txn_id: &txn_id,
                event_type: "challenge_received",
                status: "challenge_received",
                actor: "gateway",
                details_json: Some("{\"price\":\"42\"}"),
            })
            .expect("append event");
        wallet
            .update_payment_transaction(
                &txn_id,
                &PaymentTransactionUpdate {
                    occurred_at: None,
                    service_origin: None,
                    frontend_kind: None,
                    transport_kind: None,
                    endpoint_path: None,
                    method: None,
                    session_id: None,
                    action_kind: None,
                    resource_ref: None,
                    contract_ref: None,
                    invoice_ref: None,
                    challenge_id: Some("challenge-1"),
                    quoted_amount: None,
                    settled_amount: Some("42"),
                    fee_amount: Some("1"),
                    proof_ref: Some("proof-hash-1"),
                    proof_kind: Some("webcash_secret_hash"),
                    payer_ref: None,
                    payee_ref: None,
                    request_hash: None,
                    response_code: Some("accepted"),
                    status: "succeeded",
                    metadata_json: Some("{\"settled\":true}"),
                },
            )
            .expect("update transaction");

        let txns = wallet
            .list_payment_transactions()
            .expect("list transactions");
        assert_eq!(txns.len(), 1);
        let txn = &txns[0];
        assert_eq!(txn.txn_id, txn_id);
        assert_eq!(txn.direction, "inbound");
        assert_eq!(txn.role, "payee");
        assert_eq!(txn.action_kind, "identity-claim");
        assert_eq!(txn.challenge_id.as_deref(), Some("challenge-1"));
        assert_eq!(txn.settled_amount.as_deref(), Some("42"));
        assert_eq!(txn.fee_amount.as_deref(), Some("1"));
        assert_eq!(txn.proof_kind.as_deref(), Some("webcash_secret_hash"));
        assert_eq!(txn.proof_ref.as_deref(), Some("proof-hash-1"));
        assert_eq!(txn.status, "succeeded");

        let events = wallet
            .list_payment_transaction_events(Some(&txn_id))
            .expect("list txn events");
        assert_eq!(events.len(), 1);
        assert_eq!(events[0].event_type, "challenge_received");
        assert_eq!(events[0].status, "challenge_received");
        assert_eq!(events[0].actor, "gateway");
    }

    #[test]
    fn payment_transactions_enforce_unique_proof_refs_per_direction_and_rail() {
        let wallet = RgbWallet::open_memory().expect("memory wallet");
        wallet
            .record_payment_transaction(&NewPaymentTransaction {
                attempt_id: None,
                occurred_at: None,
                direction: "inbound",
                role: "payee",
                source_system: "harmonia",
                service_origin: Some("https://node.example"),
                frontend_kind: Some("http2"),
                transport_kind: Some("http2"),
                endpoint_path: Some("/v1/session"),
                method: Some("POST"),
                session_id: Some("session-1"),
                action_kind: "post",
                resource_ref: None,
                contract_ref: None,
                invoice_ref: None,
                challenge_id: None,
                rail: "voucher",
                payment_unit: "credits",
                quoted_amount: Some("10"),
                settled_amount: Some("10"),
                fee_amount: None,
                proof_ref: Some("proof-ref-1"),
                proof_kind: Some("voucher_public_hash"),
                payer_ref: None,
                payee_ref: None,
                request_hash: None,
                response_code: None,
                status: "succeeded",
                metadata_json: None,
            })
            .expect("insert first proof ref");

        let duplicate = wallet.record_payment_transaction(&NewPaymentTransaction {
            attempt_id: None,
            occurred_at: None,
            direction: "inbound",
            role: "payee",
            source_system: "harmonia",
            service_origin: Some("https://node.example"),
            frontend_kind: Some("mqtt"),
            transport_kind: Some("mqtt"),
            endpoint_path: Some("/topic/posts"),
            method: Some("PUBLISH"),
            session_id: Some("session-2"),
            action_kind: "comment",
            resource_ref: None,
            contract_ref: None,
            invoice_ref: None,
            challenge_id: None,
            rail: "voucher",
            payment_unit: "credits",
            quoted_amount: Some("10"),
            settled_amount: Some("10"),
            fee_amount: None,
            proof_ref: Some("proof-ref-1"),
            proof_kind: Some("voucher_public_hash"),
            payer_ref: None,
            payee_ref: None,
            request_hash: None,
            response_code: None,
            status: "succeeded",
            metadata_json: None,
        });

        assert!(duplicate.is_err(), "duplicate proof ref should be rejected");
    }
}