SQL Import Status

CREATE TABLE `amm_pools` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `asset1_currency` VARCHAR(40) NOT NULL,
  `asset1_issuer` VARCHAR(35) DEFAULT NULL, 
  `asset2_currency` VARCHAR(40) NOT NULL,
  `asset2_issuer` VARCHAR(35) DEFAULT NULL, 
  `ledger_index_created` INT UNSIGNED NOT NULL,
  `created_timestamp` TIMESTAMP NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_asset_pair` (`asset1_currency`, `asset1_issuer`, `asset2_currency`, `asset2_issuer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

CREATE TABLE `amm_pool_daily_snapshots` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `pool_id` INT UNSIGNED NOT NULL,
  `snapshot_date` DATE NOT NULL,
  `asset1_balance` DECIMAL(40,15) NOT NULL,
  `asset2_balance` DECIMAL(40,15) NOT NULL,
  `lp_token_balance` DECIMAL(40,15) NOT NULL,
  `daily_swap_volume_usd` DECIMAL(30,6) DEFAULT NULL, 
  `daily_fees_usd` DECIMAL(30,6) DEFAULT NULL, 
  `total_liquidity_usd` DECIMAL(30,6) DEFAULT NULL, 
  `calculated_apy` DECIMAL(10,4) DEFAULT NULL, 
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_pool_date` (`pool_id`, `snapshot_date`),
  FOREIGN KEY (`pool_id`) REFERENCES `amm_pools`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

CREATE TABLE `website_visits` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `site_identifier` VARCHAR(255) DEFAULT NULL,
  `page_url` VARCHAR(2083) NOT NULL,
  `visit_timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` TEXT DEFAULT NULL,
  `referrer_url` VARCHAR(2083) DEFAULT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_visit_timestamp` (`visit_timestamp`),
  INDEX `idx_site_page` (`site_identifier`, `page_url`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

CREATE TABLE ledgers (
    id INT AUTO_INCREMENT,
    ledger_index INT NOT NULL,
    ledger_hash VARCHAR(64) NOT NULL,
    account_hash VARCHAR(64) COMMENT 'Hash of the account state data',
    close_flags INT COMMENT 'Flags indicating special close conditions',
	close_time INT UNSIGNED COMMENT 'Ledger close time in Ripple epoch (seconds since 2000-01-01)',
    close_time_human VARCHAR(34) COMMENT 'Human-readable close time',
    close_time_resolution INT COMMENT 'Resolution of close time in seconds',
    close_time_iso VARCHAR(20) COMMENT 'Close time in ISO 8601 format',
    parent_close_time INT COMMENT 'Close time of the parent ledger',
    parent_hash VARCHAR(64) COMMENT 'Hash of the parent ledger',
    total_coins VARCHAR(20) COMMENT 'Total XRP drops in existence',
    transaction_hash VARCHAR(64) COMMENT 'Hash of the transactions in the ledger',
    closed TINYINT COMMENT 'Indicates if the ledger is closed (1 for true, 0 for false)',
    validated TINYINT COMMENT 'Indicates if the ledger is validated (1 for true, 0 for false)',
    PRIMARY KEY (id, close_time),
    UNIQUE KEY uk_ledger_index_close_time (ledger_index, close_time),
    UNIQUE KEY uk_ledger_hash_close_time (ledger_hash, close_time),
    INDEX idx_ledgers_parent_hash (parent_hash)
) COMMENT = 'Stores information about each ledger in the XRP Ledger' ENGINE=InnoDB

CREATE TABLE transactions (
    id BIGINT AUTO_INCREMENT,
    ledger_id INT NOT NULL COMMENT 'Foreign key referencing the ledgers table',
    hash CHAR(64) NOT NULL COMMENT 'Unique transaction hash',
    Account VARCHAR(35) NOT NULL COMMENT 'Initiating account address',
    TransactionType VARCHAR(20) NOT NULL COMMENT 'Type of transaction (e.g., Payment, OfferCreate)',
    Fee BIGINT NOT NULL COMMENT 'Transaction fee in drops',
    Sequence INT UNSIGNED NOT NULL COMMENT 'Transaction sequence number',
    SigningPubKey VARCHAR(66) NOT NULL COMMENT 'Public key used to sign the transaction',
    TxnSignature VARCHAR(128) NOT NULL COMMENT 'Transaction signature',
    TransactionIndex INT UNSIGNED COMMENT 'Position of the transaction within its ledger',
    TransactionResult VARCHAR(20) COMMENT 'Result code indicating success or failure (e.g., tesSUCCESS)',
    date INT UNSIGNED COMMENT 'Ledger close time (Ripple timestamp)', 
    validated TINYINT COMMENT 'Indicates if the transaction is in a validated ledger (0 or 1)',
    LastLedgerSequence BIGINT UNSIGNED DEFAULT NULL COMMENT 'Last ledger sequence for transaction validity',
    PRIMARY KEY (id, date),
    UNIQUE KEY uk_hash_date (hash, date),
	FOREIGN KEY (ledger_id, date) REFERENCES ledgers(id, close_time) ON DELETE CASCADE,
    INDEX idx_transactions_ledger_id (ledger_id),
    INDEX idx_transactions_transaction_type (TransactionType),
    INDEX idx_transactions_date_type (date, TransactionType),
    INDEX idx_transactions_account (Account),
    INDEX idx_t_result (TransactionResult)
) COMMENT = 'Base table for all XRPL transactions, storing common attributes' ENGINE=InnoDB

CREATE TABLE affected_nodes_base (
    base_id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key linking to the transactions table',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    node_index TINYINT UNSIGNED NOT NULL COMMENT 'Position of the node within the transaction''s AffectedNodes array',
    action ENUM('CreatedNode', 'ModifiedNode', 'DeletedNode') NOT NULL COMMENT 'Type of change made to the node',
    LedgerIndex CHAR(64) NOT NULL COMMENT 'Unique identifier of the ledger entry affected',
    LedgerEntryType VARCHAR(20) NOT NULL COMMENT 'Type of ledger entry (e.g., AccountRoot, Offer)',
    PRIMARY KEY (base_id, transaction_date),
    UNIQUE KEY uk_transaction_node_date (transaction_id, node_index, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE,
    INDEX idx_affected_nodes_base_transaction_id (transaction_id),
    INDEX idx_affected_nodes_base_ledger_entry_type (LedgerEntryType)
) COMMENT = 'Tracks metadata about nodes affected by transactions' ENGINE=InnoDB

CREATE TABLE top_wallets_history (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 'Unique identifier for each entry',
    wallet_address VARCHAR(35) NOT NULL COMMENT 'XRP account address of the wallet or "TOTALS" for aggregates',
    balance_drops BIGINT NOT NULL COMMENT 'Wallet balance in drops (1 XRP = 1,000,000 drops), 0 for totals',
    name VARCHAR(100) DEFAULT 'Unknown' COMMENT 'Associated name of the wallet, or "Totals" for aggregates',
    wallet_type ENUM('Exchange', 'Ripple', 'Other', 'Totals') NOT NULL DEFAULT 'Other' COMMENT 'Type of wallet: Exchange, Ripple, Other, or Totals',
    owner_count BIGINT DEFAULT NULL COMMENT 'Number of ledger objects owned, cached from XRPSCAN API',
    flags BIGINT DEFAULT NULL COMMENT 'Account flags, cached from XRPSCAN API',
    tx_count INT DEFAULT NULL COMMENT 'Number of recent transactions, cached from XRPSCAN API',
    total_xrp_all BIGINT NOT NULL DEFAULT 0 COMMENT 'Total XRP in drops for all wallets over 5M XRP',
    total_xrp_exchanges BIGINT NOT NULL DEFAULT 0 COMMENT 'Total XRP in drops for exchange wallets over 5M XRP',
    recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp when the balance was recorded',
    CONSTRAINT uk_wallet_address_recorded_at UNIQUE (wallet_address, recorded_at),
    INDEX idx_recorded_at (recorded_at),
    INDEX idx_wallet_address (wallet_address),
    INDEX idx_wallet_type (wallet_type)
) COMMENT = 'Stores historical balance data, wallet types, totals, and cached metadata for XRP wallets holding over 5 million XRP' ENGINE=InnoDB

CREATE TABLE `issuer_info_cache` (
  `issuer_address` VARCHAR(40) NOT NULL COMMENT 'The XRPL issuer address',
  `domain_hex` VARCHAR(512) DEFAULT NULL COMMENT 'Raw Domain field from account_info (hex)',
  `domain_decoded` VARCHAR(255) DEFAULT NULL COMMENT 'Decoded domain name',
  `toml_url` VARCHAR(2083) DEFAULT NULL COMMENT 'Full URL to the xrpl.toml file',
  `toml_last_fetched` TIMESTAMP NULL DEFAULT NULL COMMENT 'When the TOML file was last successfully fetched',
  `toml_fetch_error` TEXT DEFAULT NULL COMMENT 'Last error message if TOML fetch failed',
  `failed_toml_fetch_attempts` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Number of consecutive failed attempts to fetch TOML',
  `account_info_last_fetched` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'When account_info was last fetched for this issuer',
  `currency_info_json` TEXT DEFAULT NULL COMMENT 'JSON string of currency/token info from TOML for this issuer (e.g., [[TOKENS]])',
  PRIMARY KEY (`issuer_address`),
  INDEX `idx_domain_decoded` (`domain_decoded`(191)),  
  INDEX `idx_toml_url` (`toml_url`(191)),              
  INDEX `idx_toml_last_fetched` (`toml_last_fetched`),
  INDEX `idx_account_info_last_fetched` (`account_info_last_fetched`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC ENGINE=InnoDB

CREATE TABLE ripple_escrow_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Unique identifier for each record',
    account_address VARCHAR(35) UNIQUE NOT NULL COMMENT 'XRPL account address holding escrows',
    total_escrows INT NOT NULL COMMENT 'Number of active escrow objects',
    total_drops BIGINT NOT NULL COMMENT 'Total XRP held in escrow (in drops)',
    escrow_details TEXT COMMENT 'JSON-encoded details of each escrow object',
    updated_at DATETIME NOT NULL COMMENT 'Timestamp of last update'
) COMMENT 'Stores data about Ripple-controlled escrow accounts on the XRP Ledger' ENGINE=InnoDB

CREATE INDEX idx_ripple_escrow_accounts_updated_at ON ripple_escrow_accounts (updated_at)

CREATE TABLE total_escrowed (
    id INT PRIMARY KEY DEFAULT 1 COMMENT 'Unique identifier (single record)',
    total_drops BIGINT NOT NULL COMMENT 'Total XRP held in escrow across all Ripple accounts (in drops)',
    account_count INT NOT NULL COMMENT 'Number of Ripple accounts with active escrows',
    updated_at DATETIME NOT NULL COMMENT 'Timestamp of last update'
) COMMENT 'Stores the total XRP escrowed by Ripple accounts' ENGINE=InnoDB

INSERT INTO total_escrowed (id, total_drops, account_count, updated_at)
VALUES (1, 0, 0, NOW())
ON DUPLICATE KEY UPDATE total_drops = 0, account_count = 0, updated_at = NOW()

CREATE TABLE access_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tx_hash VARCHAR(64) UNIQUE NOT NULL,
    wallet_address VARCHAR(35),
    amount DECIMAL(10,2),
    access_start DATETIME NOT NULL,
    access_end DATETIME NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB

CREATE TABLE IF NOT EXISTS toml_cache (
    domain VARCHAR(255) CHARACTER SET latin1 PRIMARY KEY,
    parsed_data TEXT,
    fetched_at DATETIME,
    expires_at DATETIME
) ENGINE=InnoDB

CREATE TABLE xrpl_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    currency_code VARCHAR(40) NOT NULL COMMENT 'Token currency code (standard or hex)',
    issuer_address VARCHAR(35) NOT NULL COMMENT 'Issuing account address',
    issuer_domain VARCHAR(256) DEFAULT NULL COMMENT 'Domain associated with issuer account (first from issuers array)',
    token_name VARCHAR(100) DEFAULT NULL COMMENT 'Token name from tokens array',
    description TEXT DEFAULT NULL COMMENT 'Token description from tokens array',
    icon VARCHAR(256) DEFAULT NULL COMMENT 'Token icon URL from tokens array',
    weblinks JSON DEFAULT NULL COMMENT 'Array of weblinks from TOKENS.WEBLINKS in JSON format',
	toml_file_url VARCHAR(256) DEFAULT NULL COMMENT 'URL link to the toml file',
    fetched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'When the data was fetched',
    UNIQUE KEY unique_token (currency_code, issuer_address) COMMENT 'Prevent duplicate tokens'
) COMMENT='Stores discovered XRPL tokens and their details from TOML files' ENGINE=InnoDB

CREATE TABLE large_transactions (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL,
    hash CHAR(64) NOT NULL,
    date INT UNSIGNED NOT NULL, 
    ledger_index INT NOT NULL,
    amount_value DECIMAL(30,10) NOT NULL,
    amount_currency VARCHAR(40) NOT NULL,
    amount_issuer VARCHAR(35),
    xrp_equivalent DECIMAL(20,6) NOT NULL,
    PRIMARY KEY (id, date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, date),
    FOREIGN KEY (transaction_id, date) REFERENCES transactions(id, date) ON DELETE CASCADE,
    INDEX idx_xrp_equivalent (xrp_equivalent)
) COMMENT = 'Stores large transactions identified from the transactions table' ENGINE=InnoDB

CREATE TABLE processing_state (
    id INT PRIMARY KEY DEFAULT 1,
    last_ledger_index INT NOT NULL
) COMMENT 'Stores the last processed ledger index for transaction processing' ENGINE=InnoDB

INSERT INTO processing_state (id, last_ledger_index) VALUES (1, 0)
ON DUPLICATE KEY UPDATE last_ledger_index = last_ledger_index

CREATE TABLE doppler_wallet_balances (
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'Unique identifier for each record',
    wallet_address VARCHAR(35) COMMENT 'XRPL address of the wallet (null for general staking records)',
    currency VARCHAR(40) COMMENT 'Currency code of the balance (null for general staking records)',
    deposit_balance DECIMAL(20, 6) COMMENT 'User deposited XRP balance on Doppler.finance',
    total_staked DECIMAL(20, 6) COMMENT 'Total XRP staked across all users',
    staking_cap DECIMAL(20, 6) COMMENT 'Maximum staking limit for the protocol',
    boost_stage VARCHAR(50) COMMENT 'Name of the current boost stage',
    boost_point_ratio DECIMAL(10, 6) COMMENT 'Reward multiplier for the boost stage',
    token_price DECIMAL(20, 6) COMMENT 'Price of XRP in USD',
    fireblocks_treasury_balance DECIMAL(20, 6) COMMENT 'XRP balance in Fireblocks treasury wallet',
    apr DECIMAL(10, 6) COMMENT 'Annual percentage rate for staking',
    pending_withdrawal_amount DECIMAL(20, 6) COMMENT 'Total XRP pending withdrawal',
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Time when the data was recorded (in UTC)',
    record_date DATE GENERATED ALWAYS AS (DATE(timestamp)) STORED COMMENT 'Date of the record (in UTC)',
    UNIQUE KEY unique_user_balance (wallet_address, currency, timestamp)
) COMMENT 'Stores general staking info and user balances from Doppler.finance XRPfi protocol' ENGINE=InnoDB

CREATE INDEX idx_wallet_address ON doppler_wallet_balances (wallet_address)

CREATE INDEX idx_record_date ON doppler_wallet_balances (record_date)

CREATE TABLE affected_nodes_oracle (
    id BIGINT AUTO_INCREMENT,
    base_id BIGINT NOT NULL COMMENT 'Foreign key linking to affected_nodes_base',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Node_PreviousTxnID CHAR(64) COMMENT 'Transaction ID of the last modification to this node',
    Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification',
    New_Flags INT UNSIGNED COMMENT 'Initial flags for new Oracle nodes',
    New_Owner VARCHAR(35) COMMENT 'Initial owner account for new Oracle nodes',
    New_OracleDocumentID INT UNSIGNED COMMENT 'Initial Oracle document ID for new Oracle nodes',
    New_Provider VARCHAR(256) COMMENT 'Initial provider for new Oracle nodes',
    New_URI VARCHAR(256) COMMENT 'Initial URI for new Oracle nodes',
    New_AssetClass VARCHAR(16) COMMENT 'Initial asset class for new Oracle nodes',
    New_PriceData JSON COMMENT 'Initial price data for new Oracle nodes',
    New_LastUpdateTime INT UNSIGNED COMMENT 'Initial last update time for new Oracle nodes',
    New_OwnerNode VARCHAR(64) COMMENT 'Initial owner node identifier for new Oracle nodes',
    Final_Flags INT UNSIGNED COMMENT 'Flags after the transaction',
    Final_Owner VARCHAR(35) COMMENT 'Account that owns the Oracle after the transaction',
    Final_OracleDocumentID VARCHAR(64) COMMENT 'Unique identifier for the Oracle document after the transaction',
    Final_Provider VARCHAR(256) COMMENT 'Provider of the oracle data after the transaction',
    Final_URI VARCHAR(256) COMMENT 'URI pointing to additional data after the transaction',
    Final_PriceData JSON COMMENT 'JSON array of price data points after the transaction',
    Final_LastUpdateTime INT UNSIGNED COMMENT 'Timestamp of the last update after the transaction',
    Final_AssetClass VARCHAR(64) COMMENT 'Asset class after the transaction',
    Final_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier after the transaction',
    Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction',
    Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction',
    Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction',
    Previous_Owner VARCHAR(35) COMMENT 'Account that owned the Oracle before the transaction',
    Previous_OracleDocumentID VARCHAR(64) COMMENT 'Oracle document ID before the transaction',
    Previous_Provider VARCHAR(256) COMMENT 'Provider of the oracle data before the transaction',
    Previous_URI VARCHAR(256) COMMENT 'URI pointing to additional data before the transaction',
    Previous_PriceData JSON COMMENT 'JSON array of price data before the transaction',
    Previous_LastUpdateTime INT UNSIGNED COMMENT 'Timestamp of the last update before the transaction',
    Previous_AssetClass VARCHAR(64) COMMENT 'Asset class before the transaction',
    Previous_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier before the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_base_id_date (base_id, transaction_date),
    FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE
) COMMENT = 'Stores detailed information about Oracle ledger entries affected by transactions' ENGINE=InnoDB

CREATE TABLE affected_nodes_accountroot (
    id BIGINT AUTO_INCREMENT,
    base_id BIGINT NOT NULL COMMENT 'Foreign key linking to affected_nodes_base',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Node_PreviousTxnID CHAR(64) COMMENT 'Transaction ID of the last modification to this node',
    Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification',
    New_Account VARCHAR(35) COMMENT 'Account address for new AccountRoot nodes',
    New_Balance BIGINT COMMENT 'Initial account balance in drops for new AccountRoot nodes',
    New_Sequence INT UNSIGNED COMMENT 'Initial account sequence number for new AccountRoot nodes',
    New_AMMID CHAR(64) COMMENT 'AMM ID for new AccountRoot nodes',
    New_Flags INT UNSIGNED COMMENT 'Flags for new AccountRoot nodes',
    New_OwnerCount INT UNSIGNED COMMENT 'Owner count for new AccountRoot nodes',
    Final_Account VARCHAR(35) COMMENT 'Account address after the transaction',
    Final_Balance BIGINT COMMENT 'Account balance in drops after the transaction',
    Final_Flags INT UNSIGNED COMMENT 'Account flags after the transaction',
    Final_OwnerCount INT UNSIGNED COMMENT 'Number of owned objects after the transaction',
    Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction',
    Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction',
    Final_Sequence INT UNSIGNED COMMENT 'Account sequence number after the transaction',
    Final_TicketCount INT UNSIGNED COMMENT 'Number of tickets after the transaction',
    Final_AccountTxnID CHAR(64) COMMENT 'Account transaction ID after the transaction',
    Final_Domain VARCHAR(256) COMMENT 'Account domain after the transaction',
    Final_EmailHash CHAR(32) COMMENT 'Account email hash after the transaction',
    Final_MessageKey VARCHAR(66) COMMENT 'Account message key after the transaction',
    Final_TransferRate INT UNSIGNED COMMENT 'Transfer rate after the transaction',
    Final_TickSize TINYINT UNSIGNED COMMENT 'Tick size after the transaction',
    Final_NFTokenMinter VARCHAR(35) COMMENT 'NFToken minter after the transaction',
    Final_FirstNFTokenSequence INT UNSIGNED COMMENT 'First NFToken sequence after the transaction',
    Final_MintedNFTokens INT UNSIGNED COMMENT 'Number of minted NFTokens after the transaction',
    Final_BurnedNFTokens INT UNSIGNED COMMENT 'Number of burned NFTokens after the transaction',
    Final_AMMID CHAR(64) COMMENT 'AMM ID after the transaction',
    Final_WalletLocator CHAR(64) COMMENT 'Wallet locator after the transaction',
    Final_WalletSize INT UNSIGNED COMMENT 'Wallet size after the transaction',
    Final_RegularKey VARCHAR(35) COMMENT 'Regular key after the transaction',
    Final_TakerGetsIssuer VARCHAR(40) COMMENT 'Issuer of the taker gets currency after the transaction',
    Previous_Account VARCHAR(35) COMMENT 'Account address before the transaction',
    Previous_Balance BIGINT COMMENT 'Account balance in drops before the transaction',
    Previous_Flags INT UNSIGNED COMMENT 'Account flags before the transaction',
    Previous_OwnerCount INT UNSIGNED COMMENT 'Number of owned objects before the transaction',
    Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction',
    Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction',
    Previous_Sequence INT UNSIGNED COMMENT 'Account sequence number before the transaction',
    Previous_TicketCount INT UNSIGNED COMMENT 'Number of tickets before the transaction',
    Previous_AccountTxnID CHAR(64) COMMENT 'Account transaction ID before the transaction',
    Previous_Domain VARCHAR(256) COMMENT 'Account domain before the transaction',
    Previous_EmailHash CHAR(32) COMMENT 'Account email hash before the transaction',
    Previous_MessageKey VARCHAR(66) COMMENT 'Account message key before the transaction',
    Previous_TransferRate INT UNSIGNED COMMENT 'Transfer rate before the transaction',
    Previous_TickSize TINYINT UNSIGNED COMMENT 'Tick size before the transaction',
    Previous_NFTokenMinter VARCHAR(35) COMMENT 'NFToken minter before the transaction',
    Previous_FirstNFTokenSequence INT UNSIGNED COMMENT 'First NFToken sequence before the transaction',
    Previous_MintedNFTokens INT UNSIGNED COMMENT 'Number of minted NFTokens before the transaction',
    Previous_BurnedNFTokens INT UNSIGNED COMMENT 'Number of burned NFTokens before the transaction',
    Previous_WalletLocator CHAR(64) COMMENT 'Wallet locator before the transaction',
    Previous_WalletSize INT UNSIGNED COMMENT 'Wallet size before the transaction',
    Previous_RegularKey VARCHAR(35) COMMENT 'Regular key before the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_base_id_date (base_id, transaction_date),
    FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE,
    INDEX idx_affected_nodes_accountroot_final_balance (Final_Balance),
    INDEX idx_affected_nodes_accountroot_previous_balance (Previous_Balance),
    INDEX idx_affected_nodes_accountroot_base_id_final_balance (base_id, Final_Balance)
) COMMENT = 'Stores detailed information about AccountRoot ledger entries' ENGINE=InnoDB

CREATE TABLE affected_nodes_amendments (
    id BIGINT AUTO_INCREMENT,
    base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID that modified this node',
    Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification',
    Final_Amendments JSON COMMENT 'JSON array of enabled amendments post-transaction',
    Final_Majorities JSON COMMENT 'JSON array of amendments with majority support post-transaction',
    Previous_Amendments JSON COMMENT 'JSON array of enabled amendments before the transaction',
    Previous_Majorities JSON COMMENT 'JSON array of amendments with majority support before the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_base_id_date (base_id, transaction_date),
    FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE
) COMMENT = 'Tracks changes to Amendments ledger entries' ENGINE=InnoDB

CREATE TABLE affected_nodes_check (
    id BIGINT AUTO_INCREMENT,
    base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node',
    Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification',
    New_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier for new DirectoryNode',
    New_Account VARCHAR(35) COMMENT 'Account that created the check for CreatedNode',
    New_Destination VARCHAR(35) COMMENT 'Destination account for CreatedNode',
    New_SendMax DECIMAL(30,10) COMMENT 'SendMax for CreatedNode',
    New_SendMax_Value DECIMAL(30,10) COMMENT 'SendMax value for CreatedNode',
    New_SendMax_Currency VARCHAR(40) COMMENT 'SendMax currency for CreatedNode',
    New_SendMax_Issuer VARCHAR(35) COMMENT 'SendMax issuer for CreatedNode',
    New_Sequence INT UNSIGNED COMMENT 'Sequence for CreatedNode',
    New_SourceTag INT UNSIGNED COMMENT 'Source tag for CreatedNode',
    New_DestinationNode VARCHAR(64) COMMENT 'Destination node identifier for new nodes',
    New_Expiration INT UNSIGNED COMMENT 'Expiration time for new Offer nodes',
    New_DestinationTag INT UNSIGNED COMMENT 'Destination tag after the transaction',
	New_InvoiceID CHAR(64) COMMENT 'Invoice ID associated with the check',
    Final_Account VARCHAR(35) COMMENT 'Account that created the check',
    Final_Destination VARCHAR(35) COMMENT 'Account eligible to cash the check',
    Final_Flags INT UNSIGNED COMMENT 'Flags set on the check after the transaction',
    Final_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier after the transaction',
    Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction',
    Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction',
    Final_SendMax DECIMAL(30,10) COMMENT 'Maximum amount value that can be cashed',
	Final_SendMax_Value DECIMAL(30,10) COMMENT 'Maximum amount value that can be cashed',
    Final_SendMax_Currency VARCHAR(40) COMMENT 'Currency of the maximum amount',
    Final_SendMax_Issuer VARCHAR(35) COMMENT 'Issuer of the maximum amount',
    Final_Sequence INT UNSIGNED COMMENT 'Sequence number of the check',
    Final_DestinationNode VARCHAR(64) COMMENT 'Destination node identifier after the transaction',
    Final_DestinationTag INT UNSIGNED COMMENT 'Destination tag after the transaction',
    Final_Expiration INT UNSIGNED COMMENT 'Check expiration time',
    Final_InvoiceID CHAR(64) COMMENT 'Invoice ID associated with the check',
    Final_SourceTag INT UNSIGNED COMMENT 'Source tag after the transaction',
    Previous_Account VARCHAR(35) COMMENT 'Account that created the check before the transaction',
    Previous_Destination VARCHAR(35) COMMENT 'Account eligible to cash the check before the transaction',
    Previous_Flags INT UNSIGNED COMMENT 'Flags set on the check before the transaction',
    Previous_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier before the transaction',
    Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction',
    Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction',
    Previous_SendMax_Value DECIMAL(30,10) COMMENT 'Maximum amount value before the transaction',
    Previous_SendMax_Currency VARCHAR(40) COMMENT 'Currency of the maximum amount before the transaction',
    Previous_SendMax_Issuer VARCHAR(35) COMMENT 'Issuer of the maximum amount before the transaction',
    Previous_Sequence INT UNSIGNED COMMENT 'Sequence number before the transaction',
    Previous_DestinationNode VARCHAR(64) COMMENT 'Destination node identifier before the transaction',
    Previous_DestinationTag INT UNSIGNED COMMENT 'Destination tag before the transaction',
    Previous_Expiration INT UNSIGNED COMMENT 'Expiration time before the transaction',
    Previous_InvoiceID CHAR(64) COMMENT 'Invoice ID before the transaction',
    Previous_SourceTag INT UNSIGNED COMMENT 'Source tag before the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_base_id_date (base_id, transaction_date),
    FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE
) COMMENT = 'Stores details about Check ledger entries' ENGINE=InnoDB

CREATE TABLE affected_nodes_depositpreauth (
    id BIGINT AUTO_INCREMENT,
    base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node',
    Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification',
	New_Account VARCHAR(35) COMMENT 'Account that created the check for CreatedNode',
	New_OwnerNode VARCHAR(64) COMMENT 'Initial owner node identifier for new Oracle nodes',
	New_Authorize VARCHAR(35) COMMENT 'Account preauthorized for deposits',
    Final_Account VARCHAR(35) COMMENT 'Account granting the preauthorization',
    Final_Authorize VARCHAR(35) COMMENT 'Account preauthorized for deposits',
    Final_Flags INT UNSIGNED COMMENT 'Flags set on the preauthorization',
    Final_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier after the transaction',
    Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction',
    Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction',
    Previous_Account VARCHAR(35) COMMENT 'Account granting preauthorization before the transaction',
    Previous_Authorize VARCHAR(35) COMMENT 'Account preauthorized before the transaction',
    Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction',
    Previous_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier before the transaction',
    Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction',
    Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_base_id_date (base_id, transaction_date),
    FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE
) COMMENT = 'Records changes to DepositPreauth ledger entries' ENGINE=InnoDB

CREATE TABLE affected_nodes_directorynode (
    id BIGINT AUTO_INCREMENT,
    base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node',
    Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification',
    New_ExchangeRate VARCHAR(16) COMMENT 'Exchange rate for new DirectoryNode',
    New_RootIndex CHAR(64) COMMENT 'Root index for new DirectoryNode',
    New_TakerGetsCurrency VARCHAR(40) COMMENT 'Currency the taker gets for new DirectoryNode',
    New_TakerGetsIssuer VARCHAR(40) COMMENT 'Issuer of the taker gets currency for new DirectoryNode',
    New_TakerPaysCurrency VARCHAR(40) COMMENT 'Currency the taker pays for new DirectoryNode',
    New_TakerPaysIssuer VARCHAR(40) COMMENT 'Issuer of the taker pays currency for new DirectoryNode',
    New_Flags INT UNSIGNED COMMENT 'Flags for new DirectoryNode',
    New_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier for new DirectoryNode',
    New_IndexPrevious INT UNSIGNED COMMENT 'Previous index for new DirectoryNode',
    New_Owner VARCHAR(35) COMMENT 'Owner of the new DirectoryNode',
    New_NFTokenID CHAR(64) COMMENT 'ID of the NFT involved for new nodes',
    New_Destination VARCHAR(35) COMMENT 'Destination account for new Offer nodes',
    New_IndexNext INT UNSIGNED COMMENT 'Next index for new DirectoryNode',
    New_SendMax_Value DECIMAL(30,10) COMMENT 'Maximum amount to send value',
    New_SendMax_Currency VARCHAR(40) COMMENT 'Currency of SendMax',
    New_SendMax_Issuer VARCHAR(35) COMMENT 'Issuer of SendMax',
    Final_Flags INT UNSIGNED COMMENT 'Flags set on the directory node',
    Final_Indexes JSON COMMENT 'JSON array of ledger entry indexes in the directory',
    Final_Owner VARCHAR(35) COMMENT 'Owner of the directory node',
    Final_RootIndex CHAR(64) COMMENT 'Root index of the directory',
    Final_NFTokenID CHAR(64) COMMENT 'NFToken ID after the transaction',
    Final_ExchangeRate VARCHAR(16) COMMENT 'Exchange rate after the transaction',
    Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction',
    Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction',
    Final_IndexNext INT UNSIGNED COMMENT 'Next index in the directory after the transaction',
    Final_IndexPrevious INT UNSIGNED COMMENT 'Previous index in the directory after the transaction',
    Final_TakerPays VARCHAR(40) COMMENT 'Raw TakerPays amount after modification (XRP or currency object)',
    Final_TakerGets VARCHAR(40) COMMENT 'Raw TakerGets amount after modification (XRP or currency object)',
    Final_TakerPays_Value DECIMAL(30,10) COMMENT 'Amount the taker pays after modification',
    Final_TakerPays_Currency VARCHAR(40) COMMENT 'Currency the taker pays after modification',
    Final_TakerPays_Issuer VARCHAR(35) COMMENT 'Issuer of the taker pays amount after modification',
    Final_TakerGets_Value DECIMAL(30,10) COMMENT 'Amount the taker gets after modification',
    Final_TakerGets_Currency VARCHAR(40) COMMENT 'Currency the taker gets after modification',
    Final_TakerGets_Issuer VARCHAR(35) COMMENT 'Issuer of the taker gets amount after modification',
    Final_TakerGetsCurrency VARCHAR(40) COMMENT 'Currency the taker gets after the transaction',
    Final_TakerPaysCurrency VARCHAR(40) COMMENT 'Currency the taker pays after the transaction',
    Final_TakerPaysIssuer VARCHAR(40) COMMENT 'Issuer of the taker pays currency after the transaction',
    Final_TakerGetsIssuer VARCHAR(40) COMMENT 'Issuer of the taker gets currency after the transaction',
    Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction',
    Previous_Indexes JSON COMMENT 'JSON array of indexes before the transaction',
    Previous_Owner VARCHAR(35) COMMENT 'Owner before the transaction',
    Previous_RootIndex CHAR(64) COMMENT 'Root index before the transaction',
    Previous_ExchangeRate VARCHAR(16) COMMENT 'Exchange rate before the transaction',
    Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction',
    Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction',
    Previous_IndexNext INT UNSIGNED COMMENT 'Next index before the transaction',
    Previous_IndexPrevious INT UNSIGNED COMMENT 'Previous index before the transaction',
    Previous_TakerPays VARCHAR(40) COMMENT 'Raw TakerPays amount before the transaction (e.g., XRP in drops)',
    Previous_TakerGets VARCHAR(40) COMMENT 'Raw TakerGets amount before the transaction (e.g., XRP in drops)',
    Previous_TakerGetsCurrency VARCHAR(40) COMMENT 'Currency the taker gets before the transaction',
    Previous_TakerPaysCurrency VARCHAR(40) COMMENT 'Currency the taker pays before the transaction',
    Previous_TakerPaysIssuer VARCHAR(40) COMMENT 'Issuer of the taker pays currency before the transaction',
    Previous_TakerGetsIssuer VARCHAR(40) COMMENT 'Issuer of the taker gets currency before the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_base_id_date (base_id, transaction_date),
    FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE
) COMMENT = 'Tracks changes to DirectoryNode ledger entries' ENGINE=InnoDB

CREATE TABLE affected_nodes_escrow (
    id BIGINT AUTO_INCREMENT,
    base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node',
    Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification',
    New_Account VARCHAR(35) COMMENT 'Account creating the escrow for new Escrow nodes',
    New_Amount BIGINT COMMENT 'Amount of XRP in escrow for new Escrow nodes',
    New_Destination VARCHAR(35) COMMENT 'Account receiving the escrowed funds for new Escrow nodes',
	New_DestinationNode VARCHAR(64) COMMENT 'Destination node identifier for new nodes',
    New_FinishAfter INT UNSIGNED COMMENT 'Time after which escrow can be finished for new Escrow nodes',
    New_Condition VARCHAR(256) COMMENT 'Condition for fund release for new Escrow nodes',
    New_CancelAfter INT UNSIGNED COMMENT 'Time after which escrow can be canceled for new Escrow nodes',
    New_OwnerNode VARCHAR(64) COMMENT 'Initial owner node identifier for new Oracle nodes',
	New_DestinationTag INT UNSIGNED COMMENT 'Destination tag after the transaction',
    Final_Account VARCHAR(35) COMMENT 'Account creating the escrow',
    Final_Destination VARCHAR(35) COMMENT 'Account receiving the escrowed funds',
    Final_Amount BIGINT COMMENT 'Amount of XRP in escrow',
    Final_Condition VARCHAR(256) COMMENT 'Condition for fund release',
    Final_CancelAfter INT UNSIGNED COMMENT 'Time after which escrow can be canceled',
    Final_FinishAfter INT UNSIGNED COMMENT 'Time after which escrow can be finished',
    Final_Flags INT UNSIGNED COMMENT 'Flags set on the escrow after the transaction',
    Final_SourceTag INT UNSIGNED COMMENT 'Source tag after the transaction',
    Final_DestinationTag INT UNSIGNED COMMENT 'Destination tag after the transaction',
    Final_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier after the transaction',
    Final_DestinationNode VARCHAR(64) COMMENT 'Destination node identifier after the transaction',
    Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction',
    Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction',
    Previous_Account VARCHAR(35) COMMENT 'Account creating the escrow before the transaction',
    Previous_Destination VARCHAR(35) COMMENT 'Account receiving funds before the transaction',
    Previous_Amount BIGINT COMMENT 'Amount in escrow before the transaction',
    Previous_Condition VARCHAR(256) COMMENT 'Condition before the transaction',
    Previous_CancelAfter INT UNSIGNED COMMENT 'Cancel time before the transaction',
    Previous_FinishAfter INT UNSIGNED COMMENT 'Finish time before the transaction',
    Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction',
    Previous_SourceTag INT UNSIGNED COMMENT 'Source tag before the transaction',
    Previous_DestinationTag INT UNSIGNED COMMENT 'Destination tag before the transaction',
    Previous_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier before the transaction',
    Previous_DestinationNode VARCHAR(64) COMMENT 'Destination node identifier before the transaction',
    Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction',
    Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_base_id_date (base_id, transaction_date),
    FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE
) COMMENT = 'Stores details about Escrow ledger entries' ENGINE=InnoDB

CREATE TABLE affected_nodes_feesettings (
    id BIGINT AUTO_INCREMENT,
    base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node',
    Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification',
    Final_BaseFee BIGINT COMMENT 'Base transaction fee post-transaction',
    Final_ReferenceFeeUnits INT UNSIGNED COMMENT 'Reference fee units post-transaction',
    Final_ReserveBase BIGINT COMMENT 'Base reserve amount for accounts',
    Final_ReserveIncrement BIGINT COMMENT 'Reserve increment per owned object',
    Previous_BaseFee BIGINT COMMENT 'Base fee before the transaction',
    Previous_ReferenceFeeUnits INT UNSIGNED COMMENT 'Reference fee units before the transaction',
    Previous_ReserveBase BIGINT COMMENT 'Base reserve before the transaction',
    Previous_ReserveIncrement BIGINT COMMENT 'Reserve increment before the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_base_id_date (base_id, transaction_date),
    FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE
) COMMENT = 'Tracks changes to FeeSettings ledger entries' ENGINE=InnoDB

CREATE TABLE affected_nodes_ledgerhashes (
    id BIGINT AUTO_INCREMENT,
    base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node',
    Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification',
    Final_Hashes JSON COMMENT 'JSON array of previous ledger hashes',
    Final_LastLedgerSequence INT UNSIGNED COMMENT 'Sequence number of the last ledger',
    Previous_Hashes JSON COMMENT 'JSON array of hashes before the transaction',
    Previous_LastLedgerSequence INT UNSIGNED COMMENT 'Last ledger sequence before the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_base_id_date (base_id, transaction_date),
    FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE
) COMMENT = 'Records changes to LedgerHashes ledger entries' ENGINE=InnoDB

CREATE TABLE affected_nodes_negativeunl (
    id BIGINT AUTO_INCREMENT,
    base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node',
    Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification',
    New_ValidatorToDisable VARCHAR(40) COMMENT 'Validator marked for disabling in CreatedNode actions',
    New_ValidatorToReEnable VARCHAR(40) COMMENT 'Validator marked for re-enabling in CreatedNode actions',
    Final_DisabledValidators JSON COMMENT 'JSON array of disabled validators',
    Final_ValidatorToDisable VARCHAR(40) COMMENT 'Validator marked for disabling',
    Final_ValidatorToReEnable VARCHAR(40) COMMENT 'Validator marked for re-enabling',
    Final_Flags INT UNSIGNED COMMENT 'Flags after the transaction',
    Previous_DisabledValidators JSON COMMENT 'JSON array of disabled validators before the transaction',
    Previous_ValidatorToDisable VARCHAR(40) COMMENT 'Validator to disable before the transaction',
    Previous_ValidatorToReEnable VARCHAR(40) COMMENT 'Validator to re-enable before the transaction',
    Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_base_id_date (base_id, transaction_date),
    FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE
) COMMENT = 'Tracks changes to NegativeUNL ledger entries' ENGINE=InnoDB

CREATE TABLE affected_nodes_nftokenoffer (
    id BIGINT AUTO_INCREMENT,
    base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node',
    Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification',
    New_Owner VARCHAR(35) COMMENT 'Owner of the NFT offer for new nodes',
    New_NFTokenID CHAR(64) COMMENT 'ID of the NFT involved for new nodes',
    New_Flags INT UNSIGNED COMMENT 'Flags for new Offer nodes',
    New_Destination VARCHAR(35) COMMENT 'Destination account for new Offer nodes',
    New_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier for new nodes',
    New_Amount VARCHAR(40) COMMENT 'Raw offer amount for new nodes',
    New_Amount_Value DECIMAL(30,10) COMMENT 'Parsed offer amount value for new nodes',
    New_Amount_Currency VARCHAR(40) COMMENT 'Parsed currency of the offer amount for new nodes',
    New_Amount_Issuer VARCHAR(35) COMMENT 'Parsed issuer of the offer amount for new nodes',
    New_Expiration INT UNSIGNED COMMENT 'Expiration time for new Offer nodes',
    New_NFTokenOfferNode VARCHAR(64) COMMENT 'NFToken offer node for new Offer nodes',
    Final_Amount VARCHAR(40) COMMENT 'Raw offer amount after modification',
    Final_Owner VARCHAR(35) COMMENT 'Owner of the NFT offer',
    Final_NFTokenID CHAR(64) COMMENT 'ID of the NFT involved',
    Final_Amount_Value DECIMAL(30,10) COMMENT 'Offer amount value',
    Final_Amount_Currency VARCHAR(40) COMMENT 'Currency of the offer amount',
    Final_Amount_Issuer VARCHAR(35) COMMENT 'Issuer of the offer amount',
    Final_Flags INT UNSIGNED COMMENT 'Flags set on the offer',
    Final_TransferFee INT UNSIGNED COMMENT 'Transfer fee for the NFT',
    Final_Destination VARCHAR(35) COMMENT 'Destination account for the offer',
    Final_Expiration INT UNSIGNED COMMENT 'Offer expiration time',
    Final_OfferID CHAR(64) COMMENT 'Unique identifier of the offer',
    Final_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier after modification',
    Final_NFTokenOfferNode VARCHAR(64) COMMENT 'NFToken offer node after modification',
    Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction',
    Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction',
    Previous_Owner VARCHAR(35) COMMENT 'Owner before the transaction',
    Previous_NFTokenID CHAR(64) COMMENT 'NFT ID before the transaction',
    Previous_Amount_Value DECIMAL(30,10) COMMENT 'Offer amount before the transaction',
    Previous_Amount_Currency VARCHAR(40) COMMENT 'Currency before the transaction',
    Previous_Amount_Issuer VARCHAR(35) COMMENT 'Issuer before the transaction',
    Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction',
    Previous_TransferFee INT UNSIGNED COMMENT 'Transfer fee before the transaction',
    Previous_Destination VARCHAR(35) COMMENT 'Destination before the transaction',
    Previous_Expiration INT UNSIGNED COMMENT 'Expiration before the transaction',
    Previous_OfferID CHAR(64) COMMENT 'Offer ID before the transaction',
    Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction',
    Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_base_id_date (base_id, transaction_date),
    FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE
) COMMENT = 'Stores details about NFTokenOffer ledger entries' ENGINE=InnoDB

CREATE TABLE affected_nodes_nftokenpage (
    id BIGINT AUTO_INCREMENT,
    base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node',
    Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification',
    New_NFTokenPageEntries JSON COMMENT 'JSON array of NFTokens for newly created nodes',
    New_NextPageMin CHAR(64) COMMENT 'Next page minimum index for new nodes',
    New_PreviousPageMin CHAR(64) COMMENT 'Previous page minimum index for new nodes',
    Final_Flags INT UNSIGNED COMMENT 'Flags set on the page after modification',
    Final_PreviousPageMin CHAR(64) COMMENT 'Minimum index of the previous page after modification',
    Final_NextPageMin CHAR(64) COMMENT 'Minimum index of the next page after modification',
    Final_NFTokenPageEntries JSON COMMENT 'JSON array of NFTokens on the page after modification',
    Final_PreviousTxnID CHAR(64) COMMENT 'Transaction ID of the last modification after the transaction',
    Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification after the transaction',
    Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction',
    Previous_PreviousPageMin CHAR(64) COMMENT 'Previous page minimum before the transaction',
    Previous_NextPageMin CHAR(64) COMMENT 'Next page minimum before the transaction',
    Previous_NFTokenPageEntries JSON COMMENT 'JSON array of NFTokens before the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_base_id_date (base_id, transaction_date),
    FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE
) COMMENT = 'Tracks changes to NFTokenPage ledger entries' ENGINE=InnoDB

CREATE TABLE affected_nodes_offer (
    id BIGINT AUTO_INCREMENT,
    base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node',
    Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification',
    New_Account VARCHAR(35) COMMENT 'Account creating the offer for new nodes',
    New_Sequence INT UNSIGNED COMMENT 'Offer sequence number for new nodes',
    New_TakerPays VARCHAR(40) COMMENT 'Raw TakerPays amount for new nodes (e.g., XRP in drops)',
    New_TakerPays_Value DECIMAL(30,10) COMMENT 'Amount the taker pays for new nodes',
    New_TakerPays_Currency VARCHAR(3) COMMENT 'Currency the taker pays for new nodes',
    New_TakerPays_Issuer VARCHAR(35) COMMENT 'Issuer of the taker pays amount for new nodes',
    New_TakerGets VARCHAR(40) COMMENT 'Raw TakerGets amount for new nodes (e.g., XRP in drops)',
    New_TakerGets_Value DECIMAL(30,10) COMMENT 'Amount the taker gets for new nodes',
    New_TakerGets_Currency VARCHAR(3) COMMENT 'Currency the taker gets for new nodes',
    New_TakerGets_Issuer VARCHAR(35) COMMENT 'Issuer of the taker gets amount for new nodes',
    New_BookDirectory CHAR(64) COMMENT 'Book directory for new nodes',
    New_BookNode VARCHAR(64) COMMENT 'Book node identifier for new nodes',
    New_Flags INT UNSIGNED COMMENT 'Flags for new Offer nodes',
    New_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier for new Offer nodes',
    New_Expiration INT UNSIGNED COMMENT 'Expiration time for new Offer nodes',
    Final_Account VARCHAR(35) COMMENT 'Account creating the offer after modification',
    Final_Sequence INT UNSIGNED COMMENT 'Offer sequence number after modification',
    Final_TakerPays VARCHAR(40) COMMENT 'Raw TakerPays amount after modification (XRP or currency object)',
    Final_TakerGets VARCHAR(40) COMMENT 'Raw TakerGets amount after modification (XRP or currency object)',
    Final_TakerPays_Value DECIMAL(30,10) COMMENT 'Amount the taker pays after modification',
    Final_TakerPays_Currency VARCHAR(3) COMMENT 'Currency the taker pays after modification',
    Final_TakerPays_Issuer VARCHAR(35) COMMENT 'Issuer of the taker pays amount after modification',
    Final_TakerGets_Value DECIMAL(30,10) COMMENT 'Amount the taker gets after modification',
    Final_TakerGets_Currency VARCHAR(3) COMMENT 'Currency the taker gets after modification',
    Final_TakerGets_Issuer VARCHAR(35) COMMENT 'Issuer of the taker gets amount after modification',
    Final_TakerGetsCurrency VARCHAR(40) COMMENT 'Currency the taker gets after the transaction',
    Final_TakerGetsIssuer VARCHAR(40) COMMENT 'Issuer of the taker gets currency after the transaction',
    Final_TakerPaysCurrency VARCHAR(40) COMMENT 'Currency the taker pays after the transaction',
    Final_TakerPaysIssuer VARCHAR(40) COMMENT 'Issuer of the taker pays currency after the transaction',
    Final_BookDirectory CHAR(64) COMMENT 'Book directory after the transaction',
    Final_BookNode VARCHAR(64) COMMENT 'Book node identifier after the transaction',
    Final_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier after the transaction',
    Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction',
    Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction',
    Final_Flags INT UNSIGNED COMMENT 'Flags set on the offer after modification',
    Final_Expiration INT UNSIGNED COMMENT 'Offer expiration time after modification',
    Previous_Account VARCHAR(35) COMMENT 'Account creating the offer before the transaction',
    Previous_Sequence INT UNSIGNED COMMENT 'Sequence number before the transaction',
    Previous_TakerPays VARCHAR(40) COMMENT 'Raw TakerPays amount before the transaction (e.g., XRP in drops)',
    Previous_TakerGets VARCHAR(40) COMMENT 'Raw TakerGets amount before the transaction (e.g., XRP in drops)',
    Previous_TakerPays_Value DECIMAL(30,10) COMMENT 'Taker pays amount before the transaction',
    Previous_TakerPays_Currency VARCHAR(3) COMMENT 'Taker pays currency before the transaction',
    Previous_TakerPays_Issuer VARCHAR(35) COMMENT 'Taker pays issuer before the transaction',
    Previous_TakerGets_Value DECIMAL(30,10) COMMENT 'Taker gets amount before the transaction',
    Previous_TakerGets_Currency VARCHAR(3) COMMENT 'Currency the taker gets before the transaction',
    Previous_TakerGets_Issuer VARCHAR(35) COMMENT 'Taker gets issuer before the transaction',
    Previous_BookDirectory CHAR(64) COMMENT 'Book directory before the transaction',
    Previous_BookNode VARCHAR(64) COMMENT 'Book node before the transaction',
    Previous_OwnerNode VARCHAR(64) COMMENT 'Owner node before the transaction',
    Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction',
    Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction',
    Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction',
    Previous_Expiration INT UNSIGNED COMMENT 'Expiration before the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_base_id_date (base_id, transaction_date),
    FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE
) COMMENT = 'Stores details about Offer ledger entries' ENGINE=InnoDB

CREATE TABLE affected_nodes_paychannel (
    id BIGINT AUTO_INCREMENT,
    base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node',
    Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification',
    New_Account VARCHAR(35) COMMENT 'Account creating the payment channel for new nodes',
    New_Amount BIGINT COMMENT 'Total amount allocated to the channel for new nodes',
    New_Destination VARCHAR(35) COMMENT 'Account receiving payments for new nodes',
    New_DestinationNode VARCHAR(64) COMMENT 'Destination node identifier for new nodes',
    New_PublicKey VARCHAR(66) COMMENT 'Public key of the channel for new nodes',
    New_SettleDelay INT UNSIGNED COMMENT 'Time delay before settlement for new nodes',
	New_CancelAfter INT UNSIGNED COMMENT 'Time after which escrow can be canceled for new Escrow nodes',
    New_OwnerNode VARCHAR(64) COMMENT 'Initial owner node identifier for new Oracle nodes',
    New_SourceTag INT UNSIGNED COMMENT 'Source tag for CreatedNode',
    Final_Account VARCHAR(35) COMMENT 'Account creating the payment channel after modification',
    Final_Destination VARCHAR(35) COMMENT 'Account receiving payments after modification',
    Final_Amount BIGINT COMMENT 'Total amount allocated to the channel after modification',
    Final_Balance BIGINT COMMENT 'Current balance of the channel after modification',
    Final_Flags INT UNSIGNED COMMENT 'Flags set on the trust line',
    Final_PublicKey VARCHAR(66) COMMENT 'Public key of the channel after modification',
    Final_SettleDelay INT UNSIGNED COMMENT 'Time delay before settlement after modification',
    Final_Expiration INT UNSIGNED COMMENT 'Channel expiration time after modification',
    Final_CancelAfter INT UNSIGNED COMMENT 'Time after which channel can be canceled after modification',
    Final_SourceTag INT UNSIGNED COMMENT 'Source tag after the transaction',
    Final_DestinationTag INT UNSIGNED COMMENT 'Destination tag after the transaction',
    Final_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier after the transaction',
    Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction',
    Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction',
    Final_DestinationNode VARCHAR(64) COMMENT 'Destination node identifier after the transaction',
    Previous_Account VARCHAR(35) COMMENT 'Account creating the channel before the transaction',
    Previous_Destination VARCHAR(35) COMMENT 'Recipient before the transaction',
    Previous_Amount BIGINT COMMENT 'Allocated amount before the transaction',
    Previous_Balance BIGINT COMMENT 'Balance before the transaction',
    Previous_PublicKey VARCHAR(66) COMMENT 'Public key before the transaction',
    Previous_SettleDelay INT UNSIGNED COMMENT 'Settle delay before the transaction',
    Previous_Expiration INT UNSIGNED COMMENT 'Expiration before the transaction',
    Previous_CancelAfter INT UNSIGNED COMMENT 'Cancel time before the transaction',
    Previous_SourceTag INT UNSIGNED COMMENT 'Source tag before the transaction',
    Previous_DestinationTag INT UNSIGNED COMMENT 'Destination tag before the transaction',
    Previous_OwnerNode VARCHAR(64) COMMENT 'Owner node before the transaction',
    Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction',
    Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_base_id_date (base_id, transaction_date),
    FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE
) COMMENT = 'Tracks changes to PayChannel ledger entries' ENGINE=InnoDB

CREATE TABLE affected_nodes_ripplestate (
    id BIGINT AUTO_INCREMENT,
    base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node',
    Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification',
    New_Balance_Value DECIMAL(30,10) COMMENT 'Initial trust line balance value',
    New_Balance_Currency VARCHAR(40) COMMENT 'Initial trust line balance currency',
    New_Balance_Issuer VARCHAR(35) COMMENT 'Initial trust line balance issuer',
    New_Flags INT UNSIGNED COMMENT 'Initial flags set on the trust line',
    New_HighLimit_Value DECIMAL(30,10) COMMENT 'Initial high account’s trust limit value',
    New_HighLimit_Currency VARCHAR(40) COMMENT 'Initial high account’s trust limit currency',
    New_HighLimit_Issuer VARCHAR(35) COMMENT 'Initial high account’s trust limit issuer',
    New_HighNode VARCHAR(64) COMMENT 'Initial high account node identifier',
    New_LowLimit_Value DECIMAL(30,10) COMMENT 'Initial low account’s trust limit value',
    New_LowLimit_Currency VARCHAR(40) COMMENT 'Initial low account’s trust limit currency',
    New_LowLimit_Issuer VARCHAR(35) COMMENT 'Initial low account’s trust limit issuer',
    New_LowNode VARCHAR(64) COMMENT 'Initial low account node identifier',
    Final_Balance_Value DECIMAL(30,10) COMMENT 'Current trust line balance',
    Final_Balance_Currency VARCHAR(40) COMMENT 'Currency of the balance',
    Final_Balance_Issuer VARCHAR(35) COMMENT 'Issuer of the balance',
    Final_Flags INT UNSIGNED COMMENT 'Flags set on the trust line',
    Final_HighLimit_Value DECIMAL(30,10) COMMENT 'High account''s trust limit value',
    Final_HighLimit_Currency VARCHAR(40) COMMENT 'High limit currency',
    Final_HighLimit_Issuer VARCHAR(35) COMMENT 'High limit issuer',
    Final_LowLimit_Value DECIMAL(30,10) COMMENT 'Low account''s trust limit value',
    Final_LowLimit_Currency VARCHAR(40) COMMENT 'Low limit currency',
    Final_LowLimit_Issuer VARCHAR(35) COMMENT 'Low limit issuer',
    Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction',
    Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction',
    Final_HighNode VARCHAR(64) COMMENT 'High account node identifier',
    Final_LowNode VARCHAR(64) COMMENT 'Low account node identifier',
    Final_HighQualityIn INT UNSIGNED COMMENT 'High account quality in',
    Final_HighQualityOut INT UNSIGNED COMMENT 'High account quality out',
    Final_LowQualityIn INT UNSIGNED COMMENT 'Low account quality in',
    Final_LowQualityOut INT UNSIGNED COMMENT 'Low account quality out',
    Final_TakerGetsIssuer VARCHAR(40) COMMENT 'Issuer of the taker gets currency after the transaction',
    Previous_Balance_Value DECIMAL(30,10) COMMENT 'Balance before the transaction',
    Previous_Balance_Currency VARCHAR(40) COMMENT 'Balance currency before the transaction',
    Previous_Balance_Issuer VARCHAR(35) COMMENT 'Balance issuer before the transaction',
    Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction',
    Previous_HighLimit_Value DECIMAL(30,10) COMMENT 'High limit value before the transaction',
    Previous_HighLimit_Currency VARCHAR(40) COMMENT 'High limit currency before the transaction',
    Previous_HighLimit_Issuer VARCHAR(35) COMMENT 'High limit issuer before the transaction',
    Previous_LowLimit_Value DECIMAL(30,10) COMMENT 'Low limit value before the transaction',
    Previous_LowLimit_Currency VARCHAR(40) COMMENT 'Low limit currency before the transaction',
    Previous_LowLimit_Issuer VARCHAR(35) COMMENT 'Low limit issuer before the transaction',
    Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction',
    Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction',
    Previous_HighNode VARCHAR(64) COMMENT 'High node before the transaction',
    Previous_LowNode VARCHAR(64) COMMENT 'Low node before the transaction',
    Previous_HighQualityIn INT UNSIGNED COMMENT 'High quality in before the transaction',
    Previous_HighQualityOut INT UNSIGNED COMMENT 'High quality out before the transaction',
    Previous_LowQualityIn INT UNSIGNED COMMENT 'Low quality in before the transaction',
    Previous_LowQualityOut INT UNSIGNED COMMENT 'Low quality out before the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_base_id_date (base_id, transaction_date),
    FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE
) COMMENT = 'Stores details about RippleState ledger entries' ENGINE=InnoDB

CREATE TABLE affected_nodes_signerlist (
    id BIGINT AUTO_INCREMENT,
    base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    New_Flags INT UNSIGNED COMMENT 'Flags for new SignerList nodes',
    New_SignerEntries JSON COMMENT 'JSON array of signers for new SignerList nodes',
    New_SignerQuorum INT UNSIGNED COMMENT 'Quorum for new SignerList nodes',
	New_OwnerNode VARCHAR(64) COMMENT 'Initial owner node identifier for new Oracle nodes',
    Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node',
    Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification',
    Final_Flags INT UNSIGNED COMMENT 'Flags after the transaction',
    Final_OwnerNode VARCHAR(64) COMMENT 'Node linking to the owning account after the transaction',
    Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction',
    Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction',
    Final_SignerEntries JSON COMMENT 'JSON array of authorized signers after the transaction',
    Final_SignerListID INT UNSIGNED COMMENT 'Identifier of the signer list after the transaction',
    Final_SignerQuorum INT UNSIGNED COMMENT 'Required quorum for multi-signing after the transaction',
    Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction',
    Previous_OwnerNode VARCHAR(64) COMMENT 'Owner node before the transaction',
    Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction',
    Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction',
    Previous_SignerEntries JSON COMMENT 'Signers before the transaction',
    Previous_SignerListID INT UNSIGNED COMMENT 'Signer list ID before the transaction',
    Previous_SignerQuorum INT UNSIGNED COMMENT 'Quorum before the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_base_id_date (base_id, transaction_date),
    FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE
) COMMENT = 'Tracks changes to SignerList ledger entries' ENGINE=InnoDB

CREATE TABLE affected_nodes_ticket (
    id BIGINT AUTO_INCREMENT,
    base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node',
    Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification',
    New_Account VARCHAR(35) COMMENT 'Account owning the ticket for new nodes',
    New_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier for new nodes',
    New_TicketSequence INT UNSIGNED COMMENT 'Sequence number of the ticket for new nodes',
    Final_Account VARCHAR(35) COMMENT 'Account owning the ticket',
    Final_Flags INT UNSIGNED COMMENT 'Flags set on the ticket',
    Final_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier after the transaction',
    Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction',
    Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction',
    Final_TicketSequence INT UNSIGNED COMMENT 'Sequence number of the ticket',
    Previous_Account VARCHAR(35) COMMENT 'Account owning the ticket before the transaction',
    Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction',
    Previous_OwnerNode VARCHAR(64) COMMENT 'Owner node before the transaction',
    Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction',
    Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction',
    Previous_TicketSequence INT UNSIGNED COMMENT 'Ticket sequence before the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_base_id_date (base_id, transaction_date),
    FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE
) COMMENT = 'Stores details about Ticket ledger entries' ENGINE=InnoDB

CREATE TABLE affected_nodes_did (
    id BIGINT AUTO_INCREMENT,
    base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node',
    Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification',
	New_Account VARCHAR(35) COMMENT 'Account creating the AMM for new nodes',
	New_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier for new nodes',
	New_URI VARCHAR(256) COMMENT 'URI associated with the DID',
    Final_Account VARCHAR(35) COMMENT 'Account owning the DID',
    Final_DIDDocument TEXT COMMENT 'DID document content',
    Final_Data TEXT COMMENT 'Additional DID data',
    Final_URI VARCHAR(256) COMMENT 'URI associated with the DID',
    Final_Flags INT UNSIGNED COMMENT 'Flags set on the DID',
    Final_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier after the transaction',
    Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction',
    Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction',
    Previous_Account VARCHAR(35) COMMENT 'Account owning the DID before the transaction',
    Previous_DIDDocument TEXT COMMENT 'DID document before the transaction',
    Previous_Data TEXT COMMENT 'Data before the transaction',
    Previous_URI VARCHAR(256) COMMENT 'URI before the transaction',
    Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction',
    Previous_OwnerNode VARCHAR(64) COMMENT 'Owner node before the transaction',
    Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction',
    Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_base_id_date (base_id, transaction_date),
    FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE
) COMMENT = 'Tracks changes to DID ledger entries' ENGINE=InnoDB

CREATE TABLE affected_nodes_amm (
    id BIGINT AUTO_INCREMENT,
    base_id BIGINT NOT NULL COMMENT 'Foreign key to affected_nodes_base',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Node_PreviousTxnID CHAR(64) COMMENT 'Last transaction ID modifying the node',
    Node_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Ledger sequence of the last modification',
    New_Account VARCHAR(35) COMMENT 'Account creating the AMM for new nodes',
    New_Asset_Currency VARCHAR(3) COMMENT 'First asset currency for new nodes',
    New_Asset_Issuer VARCHAR(35) COMMENT 'First asset issuer for new nodes',
    New_Asset2_Currency VARCHAR(40) COMMENT 'Second asset currency for new nodes',
    New_Asset2_Issuer VARCHAR(35) COMMENT 'Second asset issuer for new nodes',
    New_AuctionSlot JSON COMMENT 'Auction slot details for new nodes',
    New_LPTokenBalance_Value DECIMAL(30,10) COMMENT 'LP token balance value for new nodes',
    New_LPTokenBalance_Currency VARCHAR(40) COMMENT 'LP token currency for new nodes',
    New_LPTokenBalance_Issuer VARCHAR(35) COMMENT 'LP token issuer for new nodes',
    New_TradingFee INT UNSIGNED COMMENT 'Trading fee percentage for new nodes',
    New_VoteSlots JSON COMMENT 'Vote slots for new nodes',
    Final_Account VARCHAR(35) COMMENT 'Account creating the AMM',
    Final_Asset_Currency VARCHAR(3) COMMENT 'First asset currency',
    Final_Asset_Issuer VARCHAR(35) COMMENT 'First asset issuer',
    Final_Asset2_Currency VARCHAR(40) COMMENT 'Second asset currency',
    Final_Asset2_Issuer VARCHAR(35) COMMENT 'Second asset issuer',
    Final_AuctionSlot JSON COMMENT 'Auction slot details after the transaction',
    Final_LPTokenBalance_Value DECIMAL(30,10) COMMENT 'Liquidity pool token balance value',
    Final_LPTokenBalance_Currency VARCHAR(40) COMMENT 'LP token currency',
    Final_LPTokenBalance_Issuer VARCHAR(35) COMMENT 'LP token issuer',
    Final_TradingFee INT UNSIGNED COMMENT 'Trading fee percentage',
    Final_VoteSlots JSON COMMENT 'Vote slots after the transaction',
    Final_Flags INT UNSIGNED COMMENT 'Flags set on the AMM',
    Final_OwnerNode VARCHAR(64) COMMENT 'Owner node identifier after the transaction',
    Final_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID after the transaction',
    Final_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence after the transaction',
    Previous_Account VARCHAR(35) COMMENT 'Account creating the AMM before the transaction',
    Previous_Asset_Currency VARCHAR(3) COMMENT 'First asset currency before the transaction',
    Previous_Asset_Issuer VARCHAR(35) COMMENT 'First asset issuer before the transaction',
    Previous_Asset2_Currency VARCHAR(40) COMMENT 'Second asset currency before the transaction',
    Previous_Asset2_Issuer VARCHAR(35) COMMENT 'Second asset issuer before the transaction',
    Previous_AuctionSlot JSON COMMENT 'Auction slot before the transaction',
    Previous_LPTokenBalance_Value DECIMAL(30,10) COMMENT 'LP token balance before the transaction',
    Previous_LPTokenBalance_Currency VARCHAR(40) COMMENT 'LP token currency before the transaction',
    Previous_LPTokenBalance_Issuer VARCHAR(35) COMMENT 'LP token issuer before the transaction',
    Previous_TradingFee INT UNSIGNED COMMENT 'Trading fee before the transaction',
    Previous_VoteSlots JSON COMMENT 'Vote slots before the transaction',
    Previous_Flags INT UNSIGNED COMMENT 'Flags before the transaction',
    Previous_OwnerNode VARCHAR(64) COMMENT 'Owner node before the transaction',
    Previous_PreviousTxnID CHAR(64) COMMENT 'Previous transaction ID before the transaction',
    Previous_PreviousTxnLgrSeq INT UNSIGNED COMMENT 'Previous transaction ledger sequence before the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_base_id_date (base_id, transaction_date),
    FOREIGN KEY (base_id, transaction_date) REFERENCES affected_nodes_base(base_id, transaction_date) ON DELETE CASCADE
) COMMENT = 'Stores details about AMM ledger entries' ENGINE=InnoDB

CREATE TABLE transactions_payment (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Amount_Value DECIMAL(30,10) COMMENT 'Intended transfer amount value',
    Amount_Currency VARCHAR(40) COMMENT 'Currency of the intended amount',
    Amount_Issuer VARCHAR(35) COMMENT 'Issuer of the intended amount',
    Destination VARCHAR(35) NOT NULL COMMENT 'Recipient account',
    DestinationTag INT UNSIGNED COMMENT 'Destination tag',
    SendMax_Value DECIMAL(30,10) COMMENT 'Maximum amount to send value',
    SendMax_Currency VARCHAR(40) COMMENT 'Currency of SendMax',
    SendMax_Issuer VARCHAR(35) COMMENT 'Issuer of SendMax',
    DeliverMin_Value DECIMAL(30,10) COMMENT 'Minimum amount to deliver value',
    DeliverMin_Currency VARCHAR(40) COMMENT 'Currency of DeliverMin',
    DeliverMin_Issuer VARCHAR(35) COMMENT 'Issuer of DeliverMin',
    DeliverMax_Value DECIMAL(30,10) COMMENT 'Maximum amount to deliver value',
    DeliverMax_Currency VARCHAR(40) COMMENT 'Currency of DeliverMax',
    DeliverMax_Issuer VARCHAR(35) COMMENT 'Issuer of DeliverMax',
    Paths JSON COMMENT 'JSON array of payment paths for multi-hop payments',
    Flags INT UNSIGNED COMMENT 'Original flags integer from the transaction',
    PartialPayment TINYINT COMMENT 'Flag for partial payment allowance',
    NoDirectRipple TINYINT COMMENT 'Flag to disable direct ripple',
    LimitQuality TINYINT COMMENT 'Flag to limit quality',
    SourceTag INT UNSIGNED COMMENT 'Source tag',
    DeliveredAmount_Value DECIMAL(30,10) COMMENT 'Actual delivered amount value',
    DeliveredAmount_Currency VARCHAR(40) COMMENT 'Currency of delivered amount',
    DeliveredAmount_Issuer VARCHAR(35) COMMENT 'Issuer of delivered amount',
    InvoiceID CHAR(64) DEFAULT NULL COMMENT 'Invoice ID associated with the payment',
    TicketSequence INT UNSIGNED DEFAULT NULL COMMENT 'Ticket sequence number used in the transaction',
    Signers JSON COMMENT 'JSON array of signers for the transaction',
    AccountTxnID CHAR(64) DEFAULT NULL COMMENT 'Hash of a previous transaction from the same account',
    PreviousTxnID CHAR(64) DEFAULT NULL COMMENT 'Hash of the previous transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE,
    INDEX idx_transactions_payment_delivered_amount_value (DeliveredAmount_Value),
    INDEX idx_transactions_payment_destination (Destination),
    INDEX idx_transactions_payment_amount_value (Amount_Value)
) COMMENT = 'Stores specific details for Payment transactions' ENGINE=InnoDB

CREATE TABLE transaction_signers (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    account VARCHAR(35) COMMENT 'Signer''s XRPL account address',
    signing_pub_key VARCHAR(66) COMMENT 'Public key used for signing',
    txn_signature VARCHAR(128) COMMENT 'Signature provided by the signer',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_signer_date (transaction_id, account, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE,
    INDEX idx_transaction_signers_transaction_id (transaction_id)
) COMMENT = 'Stores signer details for multi-signed transactions in XRPL' ENGINE=InnoDB

CREATE TABLE transaction_memos (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    memo_index INT NOT NULL COMMENT 'Index of the memo in the Memos array',
    memo_type VARCHAR(255) COMMENT 'Type of the memo (hexadecimal)',
    memo_data TEXT COMMENT 'Content of the memo (hexadecimal)',
    memo_format VARCHAR(255) COMMENT 'Format of the memo (hexadecimal)',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_memo_date (transaction_id, memo_index, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE,
    INDEX idx_transaction_memos_transaction_id (transaction_id)
) COMMENT = 'Stores memo details attached to XRPL transactions' ENGINE=InnoDB

CREATE TABLE transactions_paymentchannelcreate (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Amount_Value DECIMAL(30,10) COMMENT 'Amount allocated to the channel',
    Amount_Currency VARCHAR(3) COMMENT 'Currency of the channel amount',
    Amount_Issuer VARCHAR(35) COMMENT 'Issuer of the channel amount',
    Destination VARCHAR(35) NOT NULL COMMENT 'Recipient account',
    SettleDelay INT UNSIGNED NOT NULL COMMENT 'Delay before settlement',
    PublicKey VARCHAR(66) NOT NULL COMMENT 'Channel''s public key',
    CancelAfter INT UNSIGNED COMMENT 'Time after which channel can be canceled',
    DestinationTag INT UNSIGNED COMMENT 'Destination tag',
    SourceTag INT UNSIGNED COMMENT 'Source tag',
    Flags INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Flags set for the transaction',
	TicketSequence INT UNSIGNED COMMENT 'Ticket sequence number used',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE
) COMMENT = 'Stores details for PaymentChannelCreate transactions' ENGINE=InnoDB

CREATE TABLE transactions_paymentchannelfund (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Channel CHAR(64) NOT NULL COMMENT 'ID of the payment channel',
    Amount_Value DECIMAL(30,10) COMMENT 'Additional amount to fund',
    Amount_Currency VARCHAR(40) COMMENT 'Currency of the funding amount',
    Amount_Issuer VARCHAR(35) COMMENT 'Issuer of the funding amount',
    SettleDelay INT UNSIGNED COMMENT 'Optional new settle delay',
    Expiration INT UNSIGNED COMMENT 'Expiration time of the payment channel',
    SourceTag INT UNSIGNED COMMENT 'Source tag for the transaction',
    Flags INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Flags set for the transaction',
    TicketSequence INT UNSIGNED COMMENT 'Ticket sequence number used',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE
) COMMENT = 'Stores details for PaymentChannelFund transactions' ENGINE=InnoDB

CREATE TABLE transactions_paymentchannelclaim (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Channel CHAR(64) NOT NULL COMMENT 'ID of the payment channel',
    Balance BIGINT COMMENT 'Updated channel balance',
    Amount_Value DECIMAL(30,10) COMMENT 'Amount claimed',
    Amount_Currency VARCHAR(40) COMMENT 'Currency of the claimed amount',
    Amount_Issuer VARCHAR(35) COMMENT 'Issuer of the claimed amount',
    Signature VARCHAR(128) COMMENT 'Claim signature',
    PublicKey VARCHAR(66) COMMENT 'Public key for the claim',
    SourceTag INT UNSIGNED COMMENT 'Source tag',
    Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE
) COMMENT = 'Stores details for PaymentChannelClaim transactions' ENGINE=InnoDB

CREATE TABLE transactions_escrowcreate (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Amount_Value DECIMAL(30,10) COMMENT 'Escrowed amount',
    Amount_Currency VARCHAR(40) COMMENT 'Currency of the escrowed amount',
    Amount_Issuer VARCHAR(35) COMMENT 'Issuer of the escrowed amount',
    Destination VARCHAR(35) NOT NULL COMMENT 'Recipient account',
    CancelAfter INT UNSIGNED COMMENT 'Time after which escrow can be canceled',
    FinishAfter INT UNSIGNED COMMENT 'Time after which escrow can be finished',
	Signers JSON COMMENT 'JSON array of signers for the transaction',
    `Condition` VARCHAR(256) COMMENT 'Release condition',
    DestinationTag INT UNSIGNED COMMENT 'Destination tag',
    SourceTag INT UNSIGNED COMMENT 'Source tag',
    Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags',
	PreviousTxnID CHAR(64) DEFAULT NULL COMMENT 'Hash of the previous transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE
) COMMENT = 'Stores details for EscrowCreate transactions' ENGINE=InnoDB

CREATE TABLE transactions_escrowfinish (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags',
    OfferSequence INT UNSIGNED NOT NULL COMMENT 'Sequence number of the escrow creation',
    Owner VARCHAR(35) NOT NULL COMMENT 'Escrow owner',
    `Condition` VARCHAR(256) COMMENT 'Release condition',
    Fulfillment VARCHAR(256) COMMENT 'Proof meeting the condition',
	PreviousTxnID CHAR(64) DEFAULT NULL COMMENT 'Hash of the previous transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE
) COMMENT = 'Stores details for EscrowFinish transactions' ENGINE=InnoDB

CREATE TABLE transactions_escrowcancel (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags',
    OfferSequence INT UNSIGNED NOT NULL COMMENT 'Sequence number of the escrow creation',
    Owner VARCHAR(35) NOT NULL COMMENT 'Escrow owner',
    PreviousTxnID CHAR(64) DEFAULT NULL COMMENT 'Hash of the previous transaction',
	PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE
) COMMENT = 'Stores details for EscrowCancel transactions' ENGINE=InnoDB

CREATE TABLE transactions_checkcreate (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags',
    Destination VARCHAR(35) NOT NULL COMMENT 'Check recipient',
    SendMax_Value DECIMAL(30,10) COMMENT 'Maximum check amount',
    SendMax_Currency VARCHAR(40) COMMENT 'Currency of the maximum amount',
    SendMax_Issuer VARCHAR(35) COMMENT 'Issuer of the maximum amount',
    DestinationTag INT UNSIGNED COMMENT 'Destination tag',
    Expiration INT UNSIGNED COMMENT 'Check expiration time',
    InvoiceID CHAR(64) COMMENT 'Invoice ID associated with the check',
    SourceTag INT UNSIGNED COMMENT 'Source tag after the transaction',
	TicketSequence INT UNSIGNED DEFAULT NULL COMMENT 'Ticket sequence number used in the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE
) COMMENT = 'Stores details for CheckCreate transactions' ENGINE=InnoDB

CREATE TABLE transactions_checkcash (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags',
    CheckID CHAR(64) NOT NULL COMMENT 'ID of the check being cashed',
    Amount_Value DECIMAL(30,10) COMMENT 'Amount to cash',
    Amount_Currency VARCHAR(40) COMMENT 'Currency of the cashed amount',
    Amount_Issuer VARCHAR(35) COMMENT 'Issuer of the cashed amount',
    DeliverMin_Value DECIMAL(30,10) COMMENT 'Minimum amount to deliver',
    DeliverMin_Currency VARCHAR(40) COMMENT 'Currency of DeliverMin',
    DeliverMin_Issuer VARCHAR(35) COMMENT 'Issuer of DeliverMin',
	SourceTag INT UNSIGNED COMMENT 'Source tag after the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE
) COMMENT = 'Stores details for CheckCash transactions' ENGINE=InnoDB

CREATE TABLE transactions_checkcancel (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    CheckID CHAR(64) NOT NULL COMMENT 'ID of the check to cancel',
    SourceTag INT UNSIGNED COMMENT 'Source tag after the transaction',
    Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE
) COMMENT = 'Stores details for CheckCancel transactions' ENGINE=InnoDB

CREATE TABLE transactions_accountset (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    SetFlag INT UNSIGNED COMMENT 'Flag to enable',
    ClearFlag INT UNSIGNED COMMENT 'Flag to disable',
    Domain VARCHAR(256) COMMENT 'Account domain to set',
    EmailHash CHAR(32) COMMENT 'Account email hash',
    MessageKey VARCHAR(66) COMMENT 'Account message key',
    TransferRate INT UNSIGNED COMMENT 'Fee rate for transfers',
    TickSize TINYINT UNSIGNED COMMENT 'Tick size for the account',
    NFTokenMinter VARCHAR(35) COMMENT 'NFToken minter account',
    WalletLocator CHAR(64) COMMENT 'Wallet locator',
    WalletSize INT UNSIGNED COMMENT 'Wallet size',
    TicketSequence INT UNSIGNED DEFAULT NULL COMMENT 'Ticket sequence number used in the transaction',
    Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags',
    OperationLimit INT UNSIGNED COMMENT 'Operation limit for the account',
    SourceTag INT UNSIGNED COMMENT 'Source tag after the transaction',
	Signers JSON COMMENT 'JSON array of signers for the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE
) COMMENT = 'Stores details for AccountSet transactions' ENGINE=InnoDB

CREATE TABLE transactions_setregularkey (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    RegularKey VARCHAR(35) COMMENT 'New regular key to set',
    Flags INT COMMENT 'Transaction flags indicating specific options or behaviors',
    SourceTag INT UNSIGNED COMMENT 'Source tag after the transaction',
	OperationLimit INT UNSIGNED COMMENT 'Operation limit for the account',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
	FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE
) COMMENT = 'Stores details for SetRegularKey transactions' ENGINE=InnoDB

CREATE TABLE transactions_signerlistset (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Signers JSON COMMENT 'JSON array of signers for the transaction',
    SignerQuorum INT UNSIGNED NOT NULL COMMENT 'Required number of signatures',
    SignerEntries JSON COMMENT 'JSON array of signers',
    Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags',
	SourceTag INT UNSIGNED COMMENT 'Source tag after the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE
) COMMENT = 'Stores details for SignerListSet transactions' ENGINE=InnoDB

CREATE TABLE transactions_trustset (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    LimitAmount_Value DECIMAL(30,10) COMMENT 'Trust line limit amount',
    LimitAmount_Currency VARCHAR(40) COMMENT 'Currency of the trust limit',
    LimitAmount_Issuer VARCHAR(35) COMMENT 'Issuer of the trust limit',
    QualityIn INT UNSIGNED COMMENT 'Quality for incoming payments',
    QualityOut INT UNSIGNED COMMENT 'Quality for outgoing payments',
    Flags INT UNSIGNED COMMENT 'Flags set on the trust line',
    Ripple TINYINT COMMENT 'Ripple flag',
    Authorized TINYINT COMMENT 'Authorization flag',
    SourceTag INT UNSIGNED DEFAULT NULL COMMENT 'Source tag identifying the transaction source',
    TicketSequence INT UNSIGNED DEFAULT NULL COMMENT 'Ticket sequence number used in the transaction',
    Signers JSON COMMENT 'JSON array of signers for the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE
) COMMENT = 'Stores details for TrustSet transactions' ENGINE=InnoDB

CREATE TABLE transactions_ammvote (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Asset_Currency VARCHAR(50) COMMENT 'Currency code of the first asset in the AMM pair',
    Asset_Issuer VARCHAR(34) COMMENT 'Issuer address of the first asset, if applicable',
    Asset2_Currency VARCHAR(50) COMMENT 'Currency code of the second asset in the AMM pair',
    Asset2_Issuer VARCHAR(34) COMMENT 'Issuer address of the second asset, if applicable',
    TradingFee INT COMMENT 'Proposed trading fee in basis points (0-1000)',
    Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags',
    SourceTag INT UNSIGNED DEFAULT NULL COMMENT 'Source tag identifying the transaction source',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE,
    INDEX idx_transactions_ammvote_asset_currency (Asset_Currency),
    INDEX idx_transactions_ammvote_asset2_currency (Asset2_Currency)
) COMMENT = 'Stores information about AMMVote transactions' ENGINE=InnoDB

CREATE TABLE transactions_accountdelete (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Destination VARCHAR(35) NOT NULL COMMENT 'Account receiving remaining XRP',
    DestinationTag INT UNSIGNED COMMENT 'Tag for the destination',
    Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags',
    Signers JSON COMMENT 'JSON array of signers for the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE
) COMMENT = 'Stores details for AccountDelete transactions' ENGINE=InnoDB

CREATE TABLE transactions_offercreate (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    TakerPays_Value DECIMAL(30,10) COMMENT 'Amount offered to pay',
    TakerPays_Currency VARCHAR(40) COMMENT 'Currency offered to pay',
    TakerPays_Issuer VARCHAR(35) COMMENT 'Issuer of the taker pays amount',
    TakerGets_Value DECIMAL(30,10) COMMENT 'Amount offered to receive',
    TakerGets_Currency VARCHAR(40) COMMENT 'Currency offered to receive',
    TakerGets_Issuer VARCHAR(35) COMMENT 'Issuer of the taker gets amount',
    Flags INT UNSIGNED COMMENT 'Flags set on the offer',
    Expiration INT UNSIGNED COMMENT 'Offer expiration time',
    OfferSequence INT UNSIGNED COMMENT 'Sequence number of the offer',
    SourceTag INT UNSIGNED COMMENT 'Source tag',
    TicketSequence INT UNSIGNED DEFAULT NULL COMMENT 'Ticket sequence used in the transaction',
    Signers JSON COMMENT 'JSON array of signers for the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
	FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE,
    INDEX idx_transactions_offercreate_taker_gets_value (TakerGets_Value),
    INDEX idx_transactions_offercreate_taker_pays_value (TakerPays_Value),
    INDEX idx_oc_expiration (Expiration),
    INDEX idx_oc_transaction_id (transaction_id)
) COMMENT = 'Stores details for OfferCreate transactions' ENGINE=InnoDB

CREATE TABLE transactions_offercancel (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    OfferSequence INT UNSIGNED NOT NULL COMMENT 'Sequence number of the offer to cancel',
    Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags',
    SourceTag INT UNSIGNED DEFAULT NULL COMMENT 'Source tag identifying the transaction source',
    TicketSequence INT UNSIGNED DEFAULT NULL COMMENT 'Ticket sequence number used in the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE
) COMMENT = 'Stores details for OfferCancel transactions' ENGINE=InnoDB

CREATE TABLE transactions_nftokenmint (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Amount_Value DECIMAL(30,10) COMMENT 'Offer amount',
    Amount_Currency VARCHAR(40) COMMENT 'Currency of the offer amount',
    Amount_Issuer VARCHAR(35) COMMENT 'Issuer of the offer amount',
	Destination VARCHAR(35) COMMENT 'Target account for the offer',
	URI VARCHAR(256) COMMENT 'NFT metadata URI',
    Flags INT UNSIGNED COMMENT 'NFT flags',
    TransferFee INT UNSIGNED COMMENT 'Fee for NFT transfers',
    Issuer VARCHAR(35) COMMENT 'Issuer of the NFT',
    NFTokenTaxon INT UNSIGNED DEFAULT NULL COMMENT 'Taxon associated with the NFT',
    TicketSequence INT UNSIGNED DEFAULT NULL COMMENT 'Ticket sequence number used in the transaction',
    SourceTag INT UNSIGNED DEFAULT NULL COMMENT 'Source tag identifying the transaction source',
    name VARCHAR(255) DEFAULT NULL COMMENT 'NFT name from metadata',
    description TEXT DEFAULT NULL COMMENT 'NFT description from metadata',
    image_url VARCHAR(512) DEFAULT NULL COMMENT 'NFT image URL from metadata (gateway resolved)',
    metadata_json JSON DEFAULT NULL COMMENT 'Raw JSON metadata fetched from IPFS',
    Signers JSON COMMENT 'JSON array of signers for the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE
) COMMENT = 'Stores details for NFTokenMint transactions including fetched IPFS metadata' ENGINE=InnoDB

CREATE TABLE transactions_nftokenburn (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    NFTokenID CHAR(64) NOT NULL COMMENT 'ID of the NFT to burn',
    Flags BIGINT UNSIGNED DEFAULT NULL COMMENT 'Flags for the transaction',
    Owner VARCHAR(35) DEFAULT NULL COMMENT 'Owner of the NFT',
    SourceTag INT UNSIGNED DEFAULT NULL COMMENT 'Source tag identifying the transaction source',
	Signers JSON COMMENT 'JSON array of signers for the transaction',
	TicketSequence INT UNSIGNED DEFAULT NULL COMMENT 'Ticket sequence number used in the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE
) COMMENT = 'Stores details for NFTokenBurn transactions' ENGINE=InnoDB

CREATE TABLE transactions_nftokencreateoffer (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Amount_Value DECIMAL(30,10) COMMENT 'Offer amount',
    Amount_Currency VARCHAR(40) COMMENT 'Currency of the offer amount',
    Amount_Issuer VARCHAR(35) COMMENT 'Issuer of the offer amount',
    Destination VARCHAR(35) COMMENT 'Target account for the offer',
    Expiration INT UNSIGNED COMMENT 'Offer validity deadline',
    NFTokenID CHAR(64) COMMENT 'ID of the NFT',
    Flags INT UNSIGNED DEFAULT NULL COMMENT 'Flags set on the transaction',
    SourceTag INT UNSIGNED DEFAULT NULL COMMENT 'Source tag identifying the transaction source',
    TicketSequence INT UNSIGNED DEFAULT NULL COMMENT 'Ticket sequence number used in the transaction',
    Owner VARCHAR(35) DEFAULT NULL COMMENT 'Owner of the NFT',
    Signers JSON COMMENT 'JSON array of signers for the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE
) COMMENT = 'Stores details for NFTokenCreateOffer transactions' ENGINE=InnoDB

CREATE TABLE transactions_nftokenacceptoffer (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    NFTokenBuyOffer CHAR(64) COMMENT 'Identifier of the buy offer',
    NFTokenSellOffer CHAR(64) COMMENT 'Identifier of the sell offer',
    Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags',
    SourceTag INT UNSIGNED DEFAULT NULL COMMENT 'Source tag identifying the transaction source',
    NFTokenBrokerFee_Value DECIMAL(30,10) COMMENT 'Broker fee value',
    NFTokenBrokerFee_Currency VARCHAR(40) COMMENT 'Currency of the broker fee',
    NFTokenBrokerFee_Issuer VARCHAR(35) COMMENT 'Issuer of the broker fee',
    TicketSequence INT UNSIGNED DEFAULT NULL COMMENT 'Ticket sequence number used in the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE
) COMMENT = 'Stores details for NFTokenAcceptOffer transactions' ENGINE=InnoDB

CREATE TABLE transactions_nftokencanceloffer (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    NFTokenOffers JSON COMMENT 'JSON array of offer IDs to cancel',
    SourceTag INT UNSIGNED COMMENT 'Source tag',
    Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags',
	Signers JSON COMMENT 'JSON array of signers for the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE
) COMMENT = 'Stores details for NFTokenCancelOffer transactions' ENGINE=InnoDB

CREATE TABLE transactions_ammcreate (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Amount_Value DECIMAL(30,10) COMMENT 'Initial pool funding amount for the first asset',
    Amount_Currency VARCHAR(40) COMMENT 'Currency of the first funding amount',
    Amount_Issuer VARCHAR(35) COMMENT 'Issuer of the first funding amount',
    Amount2_Value DECIMAL(30,10) COMMENT 'Initial pool funding amount for the second asset',
    Amount2_Currency VARCHAR(40) COMMENT 'Currency of the second funding amount',
    Amount2_Issuer VARCHAR(35) COMMENT 'Issuer of the second funding amount',
    TradingFee INT UNSIGNED COMMENT 'Trading fee percentage',
    Asset_Currency VARCHAR(40) COMMENT 'First asset currency',
    Asset_Issuer VARCHAR(35) COMMENT 'First asset issuer',
    Asset2_Currency VARCHAR(40) COMMENT 'Second asset currency',
    Asset2_Issuer VARCHAR(35) COMMENT 'Second asset issuer',
    Flags INT UNSIGNED COMMENT 'Transaction flags',
    SourceTag INT UNSIGNED COMMENT 'Source tag',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE
) COMMENT = 'Stores details for AMMCreate transactions' ENGINE=InnoDB

CREATE TABLE transactions_ammdeposit (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Amount_Value DECIMAL(30,10) COMMENT 'Deposit amount',
    Amount_Currency VARCHAR(40) COMMENT 'Currency of the deposit amount',
    Amount_Issuer VARCHAR(35) COMMENT 'Issuer of the deposit amount',
    Amount2_Value DECIMAL(30,10) COMMENT 'Second deposit amount',
    Amount2_Currency VARCHAR(40) COMMENT 'Currency of the second deposit amount',
    Amount2_Issuer VARCHAR(35) COMMENT 'Issuer of the second deposit amount',
    EPrice_Value DECIMAL(30,10) COMMENT 'Expected price of the deposit',
    EPrice_Currency VARCHAR(40) COMMENT 'Currency of the expected price',
    EPrice_Issuer VARCHAR(35) COMMENT 'Issuer of the expected price',
    Asset_Currency VARCHAR(40) COMMENT 'First asset currency',
    Asset_Issuer VARCHAR(35) COMMENT 'First asset issuer',
    Asset2_Currency VARCHAR(40) COMMENT 'Second asset currency',
    Asset2_Issuer VARCHAR(35) COMMENT 'Second asset issuer',
    Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags',
    SourceTag INT UNSIGNED DEFAULT NULL COMMENT 'Source tag',
    TicketSequence INT UNSIGNED DEFAULT NULL COMMENT 'Ticket sequence number used in the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE,
    INDEX idx_transactions_ammdeposit_amount_value (Amount_Value)
) COMMENT = 'Stores details for AMMDeposit transactions' ENGINE=InnoDB

CREATE TABLE transactions_ammwithdraw (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Amount_Value DECIMAL(30,10) COMMENT 'Withdrawal amount',
    Amount_Currency VARCHAR(40) COMMENT 'Currency of the withdrawal amount',
    Amount_Issuer VARCHAR(35) COMMENT 'Issuer of the withdrawal amount',
    Amount2_Value DECIMAL(30,10) COMMENT 'Second withdrawal amount',
    Amount2_Currency VARCHAR(40) COMMENT 'Currency of the second withdrawal amount',
    Amount2_Issuer VARCHAR(35) COMMENT 'Issuer of the second withdrawal amount',
    EPrice_Value DECIMAL(30,10) COMMENT 'Expected price of withdrawal',
    EPrice_Currency VARCHAR(40) COMMENT 'Currency of the expected price',
    EPrice_Issuer VARCHAR(35) COMMENT 'Issuer of the expected price',
    Asset_Currency VARCHAR(40) COMMENT 'First asset currency',
    Asset_Issuer VARCHAR(35) COMMENT 'First asset issuer',
    Asset2_Currency VARCHAR(40) COMMENT 'Second asset currency',
    Asset2_Issuer VARCHAR(35) COMMENT 'Second asset issuer',
    Flags INT UNSIGNED DEFAULT NULL COMMENT 'Transaction flags',
    LPTokenIn_Value DECIMAL(30,10) COMMENT 'LP token input value',
    LPTokenIn_Currency VARCHAR(40) COMMENT 'LP token input currency',
    LPTokenIn_Issuer VARCHAR(35) COMMENT 'LP token input issuer',
    SourceTag INT UNSIGNED DEFAULT NULL COMMENT 'Source tag identifying the transaction source',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE,
    INDEX idx_transactions_ammwithdraw_amount_value (Amount_Value)
) COMMENT = 'Stores details for AMMWithdraw transactions' ENGINE=InnoDB

CREATE TABLE transactions_ticketcreate (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    TicketCount INT UNSIGNED NOT NULL COMMENT 'Number of tickets to create',
    Flags INT UNSIGNED DEFAULT NULL COMMENT 'Flags set on the transaction',
    TicketSequence INT UNSIGNED DEFAULT NULL COMMENT 'Ticket sequence number used in the transaction',
    SourceTag INT UNSIGNED DEFAULT NULL COMMENT 'Source tag identifying the transaction source',
	Signers JSON COMMENT 'JSON array of signers for the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE
) COMMENT = 'Stores details for TicketCreate transactions' ENGINE=InnoDB

CREATE TABLE transactions_depositpreauth (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    Authorize VARCHAR(35) COMMENT 'Account to authorize for deposits',
    Flags INT UNSIGNED DEFAULT NULL COMMENT 'Flags set on the transaction',
    Unauthorize VARCHAR(35) COMMENT 'Account to remove authorization from',
    Signers JSON COMMENT 'JSON array of signers for the transaction',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE
) COMMENT = 'Stores details for DepositPreauth transactions' ENGINE=InnoDB

CREATE TABLE transactions_unlmodify (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key referencing the transactions table',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    UNLModifyEnable VARCHAR(40) COMMENT 'Validator to enable',
    UNLModifyDisabling TINYINT COMMENT 'Flag indicating whether to disable (1) or enable (0) the validator',
    UNLModifyValidator VARCHAR(66) COMMENT 'Identifier of the validator being modified',
    Validator VARCHAR(66) COMMENT 'Identifier of the validator being modified',
    LedgerSequence INT UNSIGNED COMMENT 'Ledger index at which the change takes effect',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE,
    INDEX idx_transactions_unlmodify_UNLModifyValidator (UNLModifyValidator),
    INDEX idx_transactions_unlmodify_ledger_sequence (LedgerSequence)
) COMMENT = 'Stores specific details for UNLModify transactions' ENGINE=InnoDB

CREATE TABLE transactions_oracleset (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    AssetClass VARCHAR(64) COMMENT 'Asset class of the oracle data',
    OracleDocumentID VARCHAR(64) COMMENT 'Unique identifier for the oracle document',
    Provider VARCHAR(64) COMMENT 'Identifier of the data provider',
    LastUpdateTime INT UNSIGNED COMMENT 'Timestamp of the last update (Ripple epoch time)',
    URI VARCHAR(256) COMMENT 'URI pointing to additional oracle data',
    PriceDataSeries JSON COMMENT 'JSON array containing price data points',
    Flags INT UNSIGNED DEFAULT 0 COMMENT 'Transaction flags',
    LastLedgerSequence BIGINT UNSIGNED DEFAULT NULL COMMENT 'Last ledger sequence for transaction validity',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE,
    INDEX idx_transactions_oracleset_oracle_document_id (OracleDocumentID)
) COMMENT = 'Stores specific details for OracleSet transactions' ENGINE=InnoDB

CREATE TABLE transactions_oracledelete (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp from parent transactions.date, for partitioning',
    OracleDocumentID VARCHAR(64) COMMENT 'Unique identifier for the oracle document to delete',
    Flags INT UNSIGNED DEFAULT 0 COMMENT 'Transaction flags',
    LastLedgerSequence BIGINT UNSIGNED DEFAULT NULL COMMENT 'Last ledger sequence for transaction validity',
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE,
    INDEX idx_transactions_oracledelete_oracle_document_id (OracleDocumentID)
) COMMENT = 'Stores specific details for OracleDelete transactions' ENGINE=InnoDB

CREATE TABLE delivered_amounts (
    id BIGINT AUTO_INCREMENT,
    transaction_id BIGINT NOT NULL COMMENT 'Foreign key to transactions table (transactions.id)',
    transaction_hash CHAR(64) NOT NULL COMMENT 'Transaction hash for easier lookup',
    transaction_amount DECIMAL(38, 18) NULL COMMENT 'Original amount specified in the transaction (value)',
    transaction_currency VARCHAR(40) NULL COMMENT 'Original currency specified in the transaction',
    transaction_issuer VARCHAR(35) NULL COMMENT 'Issuer for original issued currency in transaction',
    delivered_amount DECIMAL(38, 18) NULL COMMENT 'Actual delivered amount (value) from meta',
    delivered_currency VARCHAR(40) NULL COMMENT 'Actual delivered currency code (e.g., XRP, USD, hex) from meta',
    delivered_issuer VARCHAR(35) NULL COMMENT 'Issuer for actual delivered issued currency from meta',
    recipient_xrp_balance_change DECIMAL(20, 6) NULL COMMENT 'Calculated change in recipient AccountRoot XRP balance (drops / 1,000,000). Not an equivalent for issued currencies.',
    transaction_date INT UNSIGNED NOT NULL COMMENT 'Ripple Epoch timestamp of the transaction, for partitioning', 
    PRIMARY KEY (id, transaction_date),
    UNIQUE KEY uk_transaction_id_date (transaction_id, transaction_date),
    FOREIGN KEY (transaction_id, transaction_date) REFERENCES transactions(id, date) ON DELETE CASCADE,
    INDEX idx_delivered_tx_hash (transaction_hash),
    INDEX idx_tx_amount (transaction_amount),
    INDEX idx_tx_currency (transaction_currency),
    INDEX idx_tx_issuer (transaction_issuer),
    INDEX idx_delivered_actual_amount (delivered_amount),
    INDEX idx_delivered_actual_currency (delivered_currency),
    INDEX idx_delivered_actual_issuer (delivered_issuer),
    INDEX idx_delivered_xrp_change (recipient_xrp_balance_change)
) COMMENT = 'Stores original tx amount, actual delivered amount/currency from metadata AND calculated recipient XRP balance change for Payment transactions.' ENGINE=InnoDB

CREATE TABLE xrpl_flags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    flag_value BIGINT UNSIGNED NOT NULL COMMENT 'The numeric (integer) value of the flag bitmask',
    flag_name VARCHAR(100) NOT NULL COMMENT 'The common name of the flag (e.g., tfPartialPayment)',
    context VARCHAR(50) NOT NULL COMMENT 'Context where flag applies (e.g., Global, Payment, OfferCreate, AccountSet, RippleState, AccountRoot)',
    description TEXT COMMENT 'Explanation of what the flag does',
    is_deprecated BOOLEAN DEFAULT FALSE COMMENT 'Indicates if the flag is deprecated',
    UNIQUE KEY unique_flag_context (flag_value, context) COMMENT 'Prevent duplicate flag values within the same context',
    INDEX idx_context (context)
) COMMENT='Definitions and descriptions of known XRP Ledger flags' ENGINE=InnoDB

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (2147483648, 'tfFullyCanonicalSig', 'Global', 'Has no effect since the RequireFullyCanonicalSig amendment. (Deprecated)', TRUE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (65536, 'tfNoRippleDirect', 'Payment', 'Do not use the default path, only use paths included in the Paths field. This is intended to force the transaction to take arbitrage opportunities. Most clients do not need this.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (131072, 'tfPartialPayment', 'Payment', 'If the specified Amount cannot be sent without spending more than SendMax, reduce the received amount instead of failing outright. See Partial Payments for more details.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (262144, 'tfLimitQuality', 'Payment', 'Only take paths where all the conversions have an input:output ratio that is equal or better than the ratio of Amount:SendMax. See Limit Quality for details.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (65536, 'tfPassive', 'OfferCreate', 'If enabled, the offer never consumes an offer on the books that has the same or worse quality (price). As a consequence, enabling this flag causes this transaction to create an Offer object in the ledger even if it could consume a matching offer in full. However, if this transaction does consume one or more crossing offers, it consumes as much as possible from the crossing offers with the best quality (price), even if doing so would consume an offer with worse quality than the passive offer.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (131072, 'tfImmediateOrCancel', 'OfferCreate', 'Treat the offer as an Immediate or Cancel order. If enabled, the offer never creates an Offer object in the ledger: it only consumes at most one offer already in the ledger. If no such offer exists to consume or the offer cannot be fully consumed, the transaction is rejected with tesSUCCESS and no effect. If the offer can be fully executed against an offer on the books, it does so without creating an Offer object.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (262144, 'tfFillOrKill', 'OfferCreate', 'Treat the offer as a Fill or Kill order. If enabled, the offer must be fully filled or not at all. If the offer cannot be fully filled, the transaction fails with tesSUCCESS and no effect. If the offer is fully filled, it is consumed and does not create an Offer object in the ledger. This flag is incompatible with tfPassive and tfImmediateOrCancel.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (524288, 'tfSell', 'OfferCreate', 'Exchange the entire TakerGets amount, even if it means obtaining more than the TakerPays amount in exchange.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (65536, 'tfRequireDestTag', 'AccountSet', 'Require a destination tag to be specified for payments to this account. This flag is replaced by the asfRequireDest flag and SetFlag field in the AccountSet transaction type.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (131072, 'tfOptionalDestTag', 'AccountSet', 'Do not require a destination tag for payments to this account. This flag is replaced by clearing the asfRequireDest flag in the AccountSet transaction type.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (262144, 'tfRequireAuth', 'AccountSet', 'Require authorization for users to hold balances issued by this account. Can only be enabled if the account has no trust lines connected to it. This flag is replaced by the asfRequireAuth flag and SetFlag field in the AccountSet transaction type.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (524288, 'tfOptionalAuth', 'AccountSet', 'Do not require authorization for users to hold balances issued by this account. This flag is replaced by clearing the asfRequireAuth flag in the AccountSet transaction type.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (1048576, 'tfDisallowXRP', 'AccountSet', 'Discourage users from sending XRP to this account. This flag is replaced by the asfDisallowXRP flag and SetFlag field in the AccountSet transaction type.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (2097152, 'tfAllowXRP', 'AccountSet', 'Allow users to send XRP to this account. This flag is replaced by clearing the asfDisallowXRP flag in the AccountSet transaction type.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (65536, 'lsfLowReserve', 'RippleState', 'This RippleState object contributes to the low account''s owner reserve.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (131072, 'lsfHighReserve', 'RippleState', 'This RippleState object contributes to the high account''s owner reserve.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (262144, 'lsfLowAuth', 'RippleState', 'The account on the low side of this relationship has authorized the other account to hold tokens it issues.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (524288, 'lsfHighAuth', 'RippleState', 'The account on the high side of this relationship has authorized the other account to hold tokens it issues.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (1048576, 'lsfLowNoRipple', 'RippleState', 'The account on the low side of this relationship has disabled rippling on this trust line.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (2097152, 'lsfHighNoRipple', 'RippleState', 'The account on the high side of this relationship has disabled rippling on this trust line.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (4194304, 'lsfLowFreeze', 'RippleState', 'The account on the low side of this relationship has frozen the trust line, preventing the other account from transferring the asset.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (8388608, 'lsfHighFreeze', 'RippleState', 'The account on the high side of this relationship has frozen the trust line, preventing the other account from transferring the asset.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (16777216, 'lsfAMMNode', 'RippleState', 'This RippleState object is associated with an Automated Market Maker (AMM) instance.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (65536, 'lsfPasswordSpent', 'AccountRoot', 'The account has used its free SetRegularKey transaction.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (131072, 'lsfRequireDestTag', 'AccountRoot', 'The account requires a destination tag for incoming payments.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (262144, 'lsfRequireAuth', 'AccountRoot', 'The account requires authorization to hold balances issued by this account.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (524288, 'lsfDisallowXRP', 'AccountRoot', 'The account discourages users from sending XRP to it.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (1048576, 'lsfDisableMaster', 'AccountRoot', 'The account has disabled the master key pair.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (2097152, 'lsfNoFreeze', 'AccountRoot', 'The account has permanently given up the ability to freeze trust lines.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (4194304, 'lsfGlobalFreeze', 'AccountRoot', 'The account has frozen all assets it issues.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (8388608, 'lsfDefaultRipple', 'AccountRoot', 'The account has enabled rippling on its trust lines by default.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (16777216, 'lsfDepositAuth', 'AccountRoot', 'The account has enabled Deposit Authorization.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (33554432, 'lsfAllowTrustLineClawback', 'AccountRoot', 'The account has enabled clawback on its trust lines.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (67108864, 'lsfDisallowIncomingNFTokenOffer', 'AccountRoot', 'The account disallows incoming NFTokenOffers.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (134217728, 'lsfDisallowIncomingCheck', 'AccountRoot', 'The account disallows incoming Checks.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (268435456, 'lsfDisallowIncomingPayChan', 'AccountRoot', 'The account disallows incoming Payment Channels.', FALSE)

INSERT INTO xrpl_flags (flag_value, flag_name, context, description, is_deprecated)
VALUES (536870912, 'lsfDisallowIncomingTrustline', 'AccountRoot', 'The account disallows incoming Trust Lines.', FALSE)

CREATE INDEX idx_oc_expiration_transaction_id ON transactions_offercreate (Expiration, transaction_id) ALGORITHM=INPLACE