# FineType v2 — Finance Domain Definitions
#
# Hierarchy: domain.category.type (locale is a field, not in the key)
# Full label at inference time: domain.category.type.LOCALE
#
# Each definition is a transformation contract:
#   - broad_type:     Target DuckDB type
#   - format_string:  DuckDB strptime format (null if not strptime-based)
#   - transform:      DuckDB SQL expression ({col} = column placeholder)
#   - transform_ext:  Enhanced transform requiring a DuckDB extension
#   - validation:     JSON Schema fragment for data quality checks
#   - tier:           Path from root to parent in the inference graph
#   - decompose:      Optional struct expansion for multi-field output
#
# Finance domain covers:
#   - banking identifiers (IBAN, SWIFT/BIC)
#   - currency amounts (period decimal, comma decimal, locale variants)
#   - currency metadata (codes, symbols)
#   - securities identifiers (CUSIP, ISIN, SEDOL, LEI)
#   - cryptocurrency addresses (Bitcoin, Ethereum)
#   - payment instruments (credit cards, PayPal)
# ─────────────────────────────────────────────────────────────────────

# ═════════════════════════════════════════════════════════════════════
# CATEGORY: banking
#
# International banking identifiers.
# ═════════════════════════════════════════════════════════════════════

finance.banking.swift_bic:
  title: "SWIFT/BIC"
  description: >
    Society for Worldwide Interbank Financial Telecommunication code (also called
    Bank Identifier Code). 8 or 11 character code identifying financial institutions.
    Format: 4-letter bank code + 2-letter country code (ISO 3166) + 2-character
    location code + optional 3-character branch code.
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "UPPER(CAST({col} AS VARCHAR))"
  transform_ext: null
  decompose:
    bank_code: "SUBSTRING({col}, 1, 4)"
    country_code: "SUBSTRING({col}, 5, 2)"
    location_code: "SUBSTRING({col}, 7, 2)"
    branch_code: "CASE WHEN LENGTH({col}) = 11 THEN SUBSTRING({col}, 9, 3) ELSE 'XXX' END"
  validation:
    type: string
    pattern: "^[A-Z]{4}[A-Z]{2}[A-Z0-9]{2}([A-Z0-9]{3})?$"
  tier: [VARCHAR, payment]
  aliases: [bic, swift_code]
  samples:
    - "DEUTDEFF"
    - "BNPAFRPP"
    - "CHASUS33XXX"
    - "BOFAUS3N"
  icon: building-bank
  canonical: "https://www.iso.org/standard/84108.html"
  references: null
  notes: >
    New in v2. SWIFT/BIC codes are used for international wire transfers.
    8-character codes identify the head office; 11-character codes include a
    branch identifier. "XXX" as branch code also means head office.

finance.banking.iban:
  title: "IBAN"
  description: >
    International Bank Account Number (ISO 13616). Up to 34 alphanumeric
    characters: 2-letter country code (ISO 3166-1 alpha-2) + 2 check digits
    + up to 30 alphanumeric BBAN (Basic Bank Account Number) characters.
    Used for international money transfers. The mod-97 check digit algorithm
    provides strong detection signal.
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "UPPER(CAST({col} AS VARCHAR))"
  transform_ext: null
  decompose:
    country_code: "SUBSTRING({col}, 1, 2)"
    check_digits: "SUBSTRING({col}, 3, 2)"
    bban: "SUBSTRING({col}, 5)"
  validation:
    type: string
    pattern: "^[A-Z]{2}[0-9]{2}[A-Z0-9]{11,30}$"
    minLength: 15
    maxLength: 34
  checksum: iban
  tier: [VARCHAR, banking]
  aliases: [international_bank_account_number]
  samples:
    - "GB29NWBK60161331926819"
    - "DE89370400440532013000"
    - "FR7630006000011234567890189"
    - "NL91ABNA0417164300"
    - "ES9121000418450200051332"
    - "IT60X0542811101000000123456"
  icon: building-bank
  canonical: "https://www.iso.org/standard/81090.html"
  references: null
  notes: >
    New in v0.5.1. IBAN check digit algorithm (ISO 7064 Mod 97-10):
    move first 4 characters to end, convert letters to numbers (A=10..Z=35),
    compute mod 97 — result must equal 1. Country-specific BBAN lengths and
    formats exist but the regex validates the general structure.

# ═════════════════════════════════════════════════════════════════════
# CATEGORY: currency
#
# Currency amounts with format-specific transformations.
# Each format produces a distinct DuckDB transform, following the
# datetime principle where distinct formats deserve distinct types.
# ═════════════════════════════════════════════════════════════════════

finance.currency.amount:
  title: "Currency Amount (US Format)"
  description: >
    Currency amount with US/international formatting: optional currency symbol
    prefix ($, £, ¥, etc.), optional negative sign or parentheses for negative,
    comma thousands separator, period decimal separator.
    Examples: $1,234.56, -$999.99, £10,000.00, $0.50, ¥1,000
  designation: universal
  locales: [UNIVERSAL]
  broad_type: DECIMAL
  frictionless:
    type: number
  format_string: null
  transform: "CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(CASE WHEN {col} LIKE '(%' THEN '-' || REPLACE(REPLACE({col}, '(', ''), ')', '') ELSE {col} END, '^[^0-9\\-]+', ''), '[^0-9\\.\\-]', '', 'g'), ',', '', 'g'), '\\s+', '', 'g') AS DECIMAL(18,2))"
  transform_ext: null
  decompose:
    symbol: "REGEXP_EXTRACT({col}, '^([^0-9\\-\\(]+)', 1)"
    numeric_value: "CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(CASE WHEN {col} LIKE '(%' THEN '-' || REPLACE(REPLACE({col}, '(', ''), ')', '') ELSE {col} END, '^[^0-9\\-]+', ''), '[^0-9\\.\\-]', '', 'g'), ',', '', 'g'), '\\s+', '', 'g') AS DECIMAL(18,2))"
  validation:
    type: string
    # Four alternations in order:
    #   1. US/international: optional $/£/¥/₹/₩/₿/currency-symbol prefix, optional
    #      sign, comma-grouped thousands, period decimal (e.g. "$1,234.56", "10,000.00").
    #   2. Currency-prefix with negative outside: "-$999.99", "-£10.50".
    #   3. Accounting parens for negative: "($1,234.56)".
    #   4. Plain bare decimal — borrowed verbatim from
    #      representation.numeric.decimal_number's canonical pattern at
    #      labels/definitions_representation.yaml:79. Closes the format-diversity
    #      gap surfaced by ecommerce_orders.csv total_price (63 rejects → 0)
    #      where 4-digit unsymboled values like "1914.96" failed the comma-required
    #      alternations. See MADR 0078 — validator alternations may compose
    #      canonical sibling-type patterns when the shape is canonical to that
    #      sibling type. Widened in v0.6.20 under spec
    #      spec 2026-04-29-validate-corpus-iter4 ac-01.
    pattern: "^[\\$£¥₹₩₿\\p{Sc}]?\\s?-?\\s?[0-9]{1,3}(,[0-9]{3})*(\\.[0-9]{1,2})?$|^-?[\\$£¥₹₩₿\\p{Sc}]\\s?[0-9]{1,3}(,[0-9]{3})*(\\.[0-9]{1,2})?$|^\\([\\$£¥₹₩₿\\p{Sc}]?[0-9]{1,3}(,[0-9]{3})*(\\.[0-9]{1,2})?\\)$|^-?[0-9]+(\\.[0-9]+)?([eE][+-]?[0-9]+)?$"
  tier: [VARCHAR, currency]
  aliases: [amount_us, currency_amount, money_us, price]
  samples:
    - "$1,234.56"
    - "-$999.99"
    - "£10,000.00"
    - "$0.50"
    - "¥1,000"
    - "$42.00"
    - "$1,000,000.00"
    - "£250.75"
    - "($1,234.56)"
    - "($999.99)"
  icon: cash
  references: null
  notes: >
    New in v0.5.1. US/international format uses comma as thousands
    separator and period as decimal separator. Transform handles both standard
    negatives (-$X) and accounting notation (($X)). Casts to DECIMAL(18,2).
    Distinct from amount_comma which uses the inverted separator convention.

finance.currency.amount_comma:
  title: "Currency Amount (EU Format)"
  description: >
    Currency amount with European formatting: optional currency symbol
    (prefix or suffix), period thousands separator, comma decimal separator.
    Examples: €1.234,56, 1.234,56 €, -€999,99, €0,50
  designation: universal
  locales: [UNIVERSAL]
  broad_type: DECIMAL
  frictionless:
    type: number
  format_string: null
  transform: "CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, '[€\\$£¥₹₩₿\\p{Sc}]', '', 'g'), '\\s+', '', 'g'), '\\.', '', 'g'), ',', '.') AS DECIMAL(18,2))"
  transform_ext: null
  decompose:
    symbol: "REGEXP_EXTRACT({col}, '([€\\$£¥₹₩₿\\p{Sc}])', 1)"
    numeric_value: "CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, '[€\\$£¥₹₩₿\\p{Sc}]', '', 'g'), '\\s+', '', 'g'), '\\.', '', 'g'), ',', '.') AS DECIMAL(18,2))"
  validation:
    type: string
    pattern: "^[€\\$£¥₹₩₿\\p{Sc}]?\\s?-?\\s?[0-9]{1,3}(\\.[0-9]{3})*(,[0-9]{1,2})?\\s?[€\\$£¥₹₩₿\\p{Sc}]?$|^-?[€\\$£¥₹₩₿\\p{Sc}]?\\s?[0-9]{1,3}(\\.[0-9]{3})*(,[0-9]{1,2})?\\s?[€\\$£¥₹₩₿\\p{Sc}]?$"
  tier: [VARCHAR, currency]
  aliases: [amount_eu, currency_amount_eu, money_eu, price_eu]
  samples:
    - "€1.234,56"
    - "1.234,56 €"
    - "-€999,99"
    - "€0,50"
    - "€42,00"
    - "€1.000.000,00"
    - "1.250,75 €"
    - "€100,00"
  icon: cash
  references: null
  notes: >
    New in v0.5.1. European format uses period as thousands separator
    and comma as decimal separator. Transform strips currency symbol, removes
    period thousands separators, replaces comma with period, then casts to
    DECIMAL(18,2). Distinct from amount (period decimal) which uses US separator convention.

finance.currency.currency_code:
  title: "Currency Code (ISO 4217)"
  description: >
    Three-letter currency code as defined by ISO 4217. Examples: USD, EUR, GBP, JPY.
    Used in financial data, trading systems, and international commerce.
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "UPPER(CAST({col} AS VARCHAR))"
  transform_ext: null
  decompose: null
  validation:
    type: string
    # ISO 4217 membership (SIX list one plus the historic set below), case-folded.
    # Replaces the prior `^[A-Z]{3}$` catch-all, which confirmed ANY three
    # uppercase letters (UDP, TCP, EDT, team/airport codes) - the Precision
    # Principle's "validation that confirms 90% of random input is not a
    # validation". Regenerated by scripts/fetch_iso4217.py.
    # iso4217:enum begin (generated by scripts/fetch_iso4217.py)
    enum: [
      ADP, AED, AFA, AFN, ALL, AMD, ANG, AOA, AON, AOR, ARS, ATS, AUD, AWG,
      AYM, AZM, AZN, BAM, BBD, BDT, BEF, BGL, BGN, BHD, BIF, BMD, BND, BOB,
      BOV, BRL, BSD, BTN, BWP, BYB, BYN, BYR, BZD, CAD, CDF, CHC, CHE, CHF,
      CHW, CLF, CLP, CNY, COP, COU, CRC, CSD, CUC, CUP, CVE, CYP, CZK, DEM,
      DJF, DKK, DOP, DZD, ECS, ECV, EEK, EGP, ERN, ESP, ETB, EUR, FIM, FJD,
      FKP, FRF, GBP, GEL, GHC, GHP, GHS, GIP, GMD, GNF, GRD, GTQ, GYD, HKD,
      HNL, HRK, HTG, HUF, IDR, IEP, ILS, INR, IQD, IRR, ISK, ITL, JMD, JOD,
      JPY, KES, KGS, KHR, KMF, KPW, KRW, KWD, KYD, KZT, LAK, LBP, LKR, LRD,
      LSL, LTL, LUF, LVL, LYD, MAD, MDL, MGA, MGF, MKD, MMK, MNT, MOP, MRO,
      MRU, MTL, MUR, MVR, MWK, MXN, MXV, MYR, MZM, MZN, NAD, NGN, NIO, NLG,
      NOK, NPR, NZD, OMR, PAB, PEN, PGK, PHP, PKR, PLN, PTE, PYG, QAR, ROL,
      RON, RSD, RUB, RUR, RWF, SAR, SBD, SCR, SDD, SDG, SEK, SGD, SHP, SIT,
      SKK, SLE, SLL, SOS, SRD, SRG, SSP, STD, STN, SVC, SYP, SZL, THB, TJR,
      TJS, TMM, TMT, TND, TOP, TPE, TRL, TRY, TTD, TWD, TZS, UAH, UGX, USD,
      USN, USS, UYI, UYU, UYW, UZS, VEB, VED, VEF, VES, VND, VUV, WST, XAD,
      XAF, XAG, XAU, XBA, XBB, XBC, XBD, XCD, XCG, XDR, XEU, XFO, XFU, XOF,
      XPD, XPF, XPT, XRE, XSU, XTS, XUA, XXX, YER, YUM, ZAR, ZMK, ZMW, ZRN,
      ZWD, ZWG, ZWL, ZWN, ZWR]
    # iso4217:enum end
  validation_notes:
    # iso4217:historic begin (generated by scripts/fetch_iso4217.py)
    historic:
      - { value: "ADP", until: "2003-07" }
      - { value: "AFA", until: "2003-01" }
      - { value: "ANG", until: "2025-03" }
      - { value: "AON", until: "2000-02" }
      - { value: "AOR", until: "2000-02" }
      - { value: "ATS", until: "2002-03" }
      - { value: "AYM", until: "2005-10" }
      - { value: "AZM", until: "2005-12" }
      - { value: "BEF", until: "2002-03" }
      - { value: "BGL", until: "2003-11" }
      - { value: "BGN", until: "2026-01" }
      - { value: "BYB", until: "2001-01" }
      - { value: "BYR", until: "2017-01" }
      - { value: "CHC", until: "2004-11" }
      - { value: "CSD", until: "2006-10" }
      - { value: "CUC", until: "2021-06" }
      - { value: "CYP", until: "2008-01" }
      - { value: "DEM", until: "2002-03" }
      - { value: "ECS", until: "2000-09" }
      - { value: "ECV", until: "2000-09" }
      - { value: "EEK", until: "2011-01" }
      - { value: "ESP", until: "2002-03" }
      - { value: "FIM", until: "2002-03" }
      - { value: "FRF", until: "2002-03" }
      - { value: "GHC", until: "2008-01" }
      - { value: "GHP", until: "2007-06" }
      - { value: "GRD", until: "2002-03" }
      - { value: "HRK", until: "2023-01" }
      - { value: "IEP", until: "2002-03" }
      - { value: "ITL", until: "2002-03" }
      - { value: "LTL", until: "2014-12" }
      - { value: "LUF", until: "2002-03" }
      - { value: "LVL", until: "2014-01" }
      - { value: "MGF", until: "2004-12" }
      - { value: "MRO", until: "2017-12" }
      - { value: "MTL", until: "2008-01" }
      - { value: "MZM", until: "2006-06" }
      - { value: "NLG", until: "2002-03" }
      - { value: "PTE", until: "2002-03" }
      - { value: "ROL", until: "2005-06" }
      - { value: "RUR", until: "2004-01" }
      - { value: "SDD", until: "2007-07" }
      - { value: "SIT", until: "2007-01" }
      - { value: "SKK", until: "2009-01" }
      - { value: "SLL", until: "2023-12" }
      - { value: "SRG", until: "2003-12" }
      - { value: "STD", until: "2017-12" }
      - { value: "TJR", until: "2001-04" }
      - { value: "TMM", until: "2009-01" }
      - { value: "TPE", until: "2002-11" }
      - { value: "TRL", until: "2005-12" }
      - { value: "USS", until: "2014-03" }
      - { value: "VEB", until: "2008-01" }
      - { value: "VEF", until: "2018-08" }
      - { value: "XEU", until: "1999-01" }
      - { value: "XFO", until: "2006-10" }
      - { value: "XFU", until: "2013-11" }
      - { value: "XRE", until: "1999-11" }
      - { value: "YUM", until: "2003-07" }
      - { value: "ZMK", until: "2012-12" }
      - { value: "ZRN", until: "1999-06" }
      - { value: "ZWD", until: "2008-08" }
      - { value: "ZWL", until: "2024-09" }
      - { value: "ZWN", until: "2006-09" }
      - { value: "ZWR", until: "2009-06" }
    # iso4217:historic end
  tier: [VARCHAR, payment]
  aliases: [iso_4217, currency_iso]
  samples:
    - "USD"
    - "EUR"
    - "GBP"
    - "JPY"
    - "CHF"
    - "AUD"
  icon: cash
  canonical: "https://www.iso.org/iso-4217-currency-codes.html"
  references: null
  notes: >
    v1 migration: Was finance.currency_iso_code. Three uppercase letters.
    First two letters usually match ISO 3166-1 country code (US→USD, GB→GBP),
    third letter is typically initial of currency name (D→Dollar, P→Pound).

finance.currency.currency_symbol:
  title: "Currency Symbol"
  description: >
    Unicode currency symbol character. Examples: $, €, £, ¥, ₹, ₩, ₿.
    Single character (or short sequence) representing a currency.
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "CAST({col} AS VARCHAR)"
  transform_ext: null
  decompose: null
  validation:
    type: string
    pattern: "^[\\p{Sc}]+$"
  tier: [VARCHAR, payment]
  aliases: [currency_sign]
  samples:
    - "$"
    - "€"
    - "£"
    - "¥"
    - "₹"
    - "₩"
    - "₿"
  icon: cash
  references: null
  notes: >
    v1 migration: Was finance.currency_symbol. Uses Unicode currency symbol
    category (\\p{Sc}). Short strings — typically 1 character. Low priority
    due to ambiguity ($ could be USD, AUD, CAD, etc.).

# ═════════════════════════════════════════════════════════════════════
# CATEGORY: payment
#
# Payment instruments and card network identifiers.
# Sensitive PII; handle with care.
# ═════════════════════════════════════════════════════════════════════

finance.payment.credit_card_number:
  title: "Credit Card Number"
  description: >
    Credit card or debit card primary account number (PAN).
    Typically 13-19 digits. Last digit is check digit (Luhn algorithm).
    HIGHLY SENSITIVE — encrypt immediately in production.
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "CAST({col} AS VARCHAR)"
  transform_ext: null
  decompose:
    issuer_identification_number: "REGEXP_EXTRACT({col}, '^([0-9]{6})', 1)"
    card_type: "CASE WHEN {col} LIKE '4%' THEN 'Visa' WHEN regexp_matches({col}, '^5[1-5]') THEN 'Mastercard' WHEN regexp_matches({col}, '^3[47]') THEN 'Amex' ELSE 'Other' END"
    last_four: "REGEXP_EXTRACT({col}, '([0-9]{4})$', 1)"
  validation:
    type: string
    pattern: "^[0-9]{13,19}$"
  checksum: luhn
  pii: true
  tier: [VARCHAR, payment]
  aliases: [pan, credit_card]
  samples:
    - "4532015112830366"
    - "5425233430109903"
    - "374245455400126"
  icon: credit-card
  canonical: "https://www.iso.org/standard/70484.html"
  references: null
  notes: >
    v1 migration: Was payment.credit_card_number. Check digit uses Luhn algorithm
    (validated in crate, not SQL). MUST be encrypted/tokenized in production.
    PCI DSS compliance required. Decompose extracts issuer ID, card type, last 4.

# ═════════════════════════════════════════════════════════════════════
# CATEGORY: securities
#
# Securities identification numbers (CUSIP, ISIN, SEDOL, LEI).
# Governed by ISO standards for trading and clearing.
# ═════════════════════════════════════════════════════════════════════

finance.securities.cusip:
  title: "CUSIP"
  description: >
    Committee on Uniform Securities Identification Procedures identifier.
    9-character alphanumeric code for US and Canadian securities. Format:
    6-character issuer code + 2-character issue number + 1 check digit.
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "CAST({col} AS VARCHAR)"
  transform_ext: null
  decompose:
    issuer: "SUBSTRING({col}, 1, 6)"
    issue: "SUBSTRING({col}, 7, 2)"
    check_digit: "SUBSTRING({col}, 9, 1)"
  validation:
    type: string
    pattern: "^[A-Z0-9]{8}[0-9]$"
  checksum: cusip
  tier: [VARCHAR, payment]
  aliases: null
  samples:
    - "037833100"
    - "17275R102"
    - "594918104"
  icon: chart-line
  references: null
  notes: >
    New in v2. CUSIP is the primary identifier for US/Canadian securities.
    Check digit uses a custom weighted algorithm where characters at even positions
    are doubled (similar to Luhn but with alphanumeric conversion).

finance.securities.isin:
  title: "ISIN"
  description: >
    International Securities Identification Number. 12-character alphanumeric code
    that uniquely identifies a specific securities issue. Format: 2-letter country
    code (ISO 3166-1) + 9-character alphanumeric NSIN + 1 check digit (Luhn on
    alpha-to-numeric conversion where A=10, B=11, ..., Z=35).
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "CAST({col} AS VARCHAR)"
  transform_ext: null
  decompose:
    country: "SUBSTRING({col}, 1, 2)"
    nsin: "SUBSTRING({col}, 3, 9)"
    check_digit: "SUBSTRING({col}, 12, 1)"
  validation:
    type: string
    pattern: "^[A-Z]{2}[A-Z0-9]{9}[0-9]$"
  checksum: isin
  tier: [VARCHAR, payment]
  aliases: null
  samples:
    - "US0378331005"
    - "GB0002634946"
    - "JP3633400001"
    - "DE0007164600"
  icon: chart-line
  canonical: "https://www.iso.org/standard/78502.html"
  references: null
  notes: >
    New in v2. ISIN is the global standard for securities identification (ISO 6166).
    The check digit uses the Luhn algorithm applied to the full string after converting
    letters to numbers (A=10, B=11, ..., Z=35). Country code follows ISO 3166-1 alpha-2.

finance.securities.sedol:
  title: "SEDOL"
  description: >
    Stock Exchange Daily Official List code. 7-character identifier for
    UK and Irish securities. Format: 6 alphanumeric characters + 1 check digit
    (weighted sum with weights 1,3,1,7,3,9 mod 10).
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "CAST({col} AS VARCHAR)"
  transform_ext: null
  decompose: null
  validation:
    type: string
    pattern: "^[B-DF-HJ-NP-TV-Z0-9]{6}[0-9]$"
  checksum: sedol
  tier: [VARCHAR, payment]
  aliases: null
  samples:
    - "0263494"
    - "B0WNLY7"
    - "3134865"
  icon: chart-line
  references: null
  notes: >
    New in v2. SEDOL codes never contain vowels (to avoid forming words).
    The check digit is computed as (10 - (weighted_sum mod 10)) mod 10
    with weights [1, 3, 1, 7, 3, 9] applied to the first 6 characters
    (digits 0-9 have face value, letters B=11, C=12, ..., Z=35, skipping vowels).

finance.securities.figi:
  title: "FIGI"
  description: >
    Financial Instrument Global Identifier. 12-character alphanumeric code
    assigned by Bloomberg and the Object Management Group. Format: 2 consonants
    (no vowels) + 'G' + 8 alphanumeric characters (no vowels) + 1 check digit.
    Used globally for securities identification in trading and clearing systems.
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "UPPER(CAST({col} AS VARCHAR))"
  transform_ext: null
  decompose:
    prefix: "SUBSTRING({col}, 1, 2)"
    body: "SUBSTRING({col}, 4, 8)"
    check_digit: "SUBSTRING({col}, 12, 1)"
  validation:
    type: string
    pattern: "^[BCDFGHJKLMNPQRSTVWXYZ]{2}G[BCDFGHJKLMNPQRSTVWXYZ0-9]{8}\\d$"
  checksum: figi
  tier: [VARCHAR, payment]
  aliases: [bloomberg_id, open_figi]
  samples:
    - "BBG000BLNQ16"
    - "BBG000B9XRY4"
    - "BBG000BVPV84"
    - "BBG000BPH459"
    - "BBG000GZQ728"
  icon: chart-line
  references: null
  notes: >
    New in taxonomy expansion. FIGI is the open standard for
    financial instrument identification, replacing proprietary Bloomberg tickers
    for many use cases. The 'G' in position 3 is always present. No vowels
    allowed in consonant positions (prevents forming words, like SEDOL).

finance.securities.lei:
  title: "LEI"
  description: >
    Legal Entity Identifier. 20-character alphanumeric code that uniquely identifies
    legal entities participating in financial transactions. Format (ISO 17442):
    4-character alphanumeric LOU (Local Operating Unit) prefix + 14-character entity
    identifier + 2 check digits (ISO 7064 Mod 97-10, same algorithm as IBAN check
    digits).
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "CAST({col} AS VARCHAR)"
  transform_ext: null
  decompose:
    lou_prefix: "SUBSTRING({col}, 1, 4)"
    entity_id: "SUBSTRING({col}, 5, 14)"
    check_digits: "SUBSTRING({col}, 19, 2)"
  validation:
    type: string
    pattern: "^[A-Z0-9]{4}[A-Z0-9]{14}[0-9]{2}$"
  checksum: lei
  tier: [VARCHAR, payment]
  aliases: [legal_entity_identifier]
  samples:
    - "529900T8BM49AURSDO55"
    - "213800WSGIIZCXF1P572"
    - "549300MLUDYVRQOOXS22"
    - "HWUPKR0MPOU8FGXBT394"
    - "001GPB6A9XPE8XJICC14"
  icon: chart-line
  canonical: "https://www.iso.org/standard/78829.html"
  references: null
  notes: >
    New in v2. LEI was introduced after the 2008 financial crisis for entity
    identification in financial transactions. Check digits use ISO 7064 Mod 97-10:
    convert letters to numbers (A=10..Z=35), compute mod 97, check digits = 98 - remainder.

finance.securities.ticker:
  title: "Stock Ticker"
  description: >
    Stock ticker symbol — the short exchange identifier for a listed security
    (AAPL, MSFT, BRK-B). 1-7 uppercase letters, optionally with a class-share
    suffix (BRK-B, BF.B). No check digit, and the shape confirms every short
    uppercase token (a US state code, a currency, an arbitrary code all clear
    it), so the published listed-symbol set is the substance (Precision
    Principle). US-listed for now (SEC company_tickers.json); other venues
    compose additively behind this same type.
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "CAST({col} AS VARCHAR)"
  transform_ext: null
  decompose: null
  validation:
    type: string
    pattern: "^[A-Z]{1,7}([./-][A-Z]{1,4})?$"
  # Shape-only pattern (any 1-7 upper token); membership against the SEC listed
  # set is what distinguishes a real ticker. Set: labels/sets/us_tickers.txt.
  membership: us_tickers
  tier: [VARCHAR, payment]
  aliases: [ticker, ticker_symbol, stock_symbol, symbol]
  samples:
    - "AAPL"
    - "MSFT"
    - "NVDA"
    - "BRK-B"
    - "TSLA"
  icon: chart-line
  references: null
  notes: >
    New in v0.6.49. Mined from the company-reference external band (EDGAR
    `ticker` was over-emitted as geography.location.state_code). No checksum;
    list membership is the substance. The 244-dim model does not predict it —
    recovered at profile time by the header-gated `ticker_membership_recovery`
    Sharpen guard (ticker/symbol header + >=90% set membership + >=3 distinct).
    The 2-letter state/ticker collision (MA, TX, … are both) is why the guard
    gates on the header, not membership alone.

finance.banking.aba_routing:
  title: "ABA Routing Number"
  description: >
    US ABA (American Bankers Association) routing transit number. 9-digit
    identifier for financial institutions in the US Federal Reserve system.
    First two digits are a valid Federal Reserve prefix (01-12, 21-32, 61-72, 80).
    The 9th digit is a weighted checksum (weights 3,7,1 repeating, sum mod 10 = 0).
  designation: locale_specific
  locales: [EN_US]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "CAST({col} AS VARCHAR)"
  transform_ext: null
  decompose:
    federal_reserve_district: "SUBSTRING({col}, 1, 2)"
    institution_id: "SUBSTRING({col}, 3, 4)"
    check_digit: "SUBSTRING({col}, 9, 1)"
  validation:
    type: string
    pattern: "^(0[0-9]|1[0-2]|2[1-9]|3[0-2]|6[1-9]|7[0-2]|80)\\d{7}$"
  checksum: aba
  tier: [VARCHAR, banking]
  aliases: [routing_number, routing_transit_number, aba_number]
  samples:
    - "021000021"
    - "111000025"
    - "021200025"
    - "071000013"
    - "011401533"
    - "121000358"
  icon: building-bank
  references: null
  notes: >
    New in taxonomy expansion. ABA routing numbers identify US
    financial institutions for ACH and wire transfers. The first two digits
    indicate the Federal Reserve district. Check digit: sum of
    (3*d1 + 7*d2 + 1*d3 + 3*d4 + 7*d5 + 1*d6 + 3*d7 + 7*d8 + 1*d9) mod 10 = 0.

finance.banking.bsb:
  title: "BSB Number"
  description: >
    Australian Bank-State-Branch number. 6-digit identifier formatted as
    ###-### that identifies a bank branch in the Australian financial system.
    First two digits identify the bank, third digit identifies the state,
    last three digits identify the branch.
  designation: locale_specific
  locales: [EN_AU]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "CAST({col} AS VARCHAR)"
  transform_ext: null
  decompose:
    bank_code: "SUBSTRING({col}, 1, 2)"
    state_code: "SUBSTRING({col}, 3, 1)"
    branch_code: "SUBSTRING({col}, 5, 3)"
  validation:
    type: string
    pattern: "^\\d{3}-\\d{3}$"
  tier: [VARCHAR, banking]
  aliases: [bank_state_branch]
  samples:
    - "062-000"
    - "033-001"
    - "012-003"
    - "082-001"
    - "063-010"
    - "013-252"
  icon: building-bank
  references: null
  notes: >
    New in taxonomy expansion. BSB numbers are used in Australian
    domestic payments (BECS). The hyphen in position 4 is always present.
    Bank codes: 01=ANZ, 03=WBC, 06=CBA, 08=NAB, 01x=other. State: 2=NSW,
    3=VIC, 4=QLD, 5=SA, 6=WA, 7=TAS.

# ═════════════════════════════════════════════════════════════════════
# CATEGORY: crypto
#
# Cryptocurrency wallet and contract addresses.
# ═════════════════════════════════════════════════════════════════════

finance.crypto.bitcoin_address:
  title: "Bitcoin Address"
  description: >
    Bitcoin wallet address. P2PKH format starts with 1, P2SH with 3, P2WPKH with bc1.
    26-35 characters. Base58Check encoding.
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "CAST({col} AS VARCHAR)"
  transform_ext: null
  decompose:
    address_type: "CASE WHEN {col} LIKE '1%' THEN 'P2PKH' WHEN {col} LIKE '3%' THEN 'P2SH' WHEN {col} LIKE 'bc1%' THEN 'P2WPKH' ELSE 'Unknown' END"
  validation:
    type: string
    pattern: "^[13][a-km-zA-HJ-NP-Z1-9]{25,34}$|^bc1[a-z0-9]{39,59}$"
  tier: [VARCHAR, payment]
  aliases: null
  samples:
    - "1A1z7agoat2GPFH7pPPPP..."
    - "3J98t1WpEZ73CNmYviecrnyiWrnqRhWNLy"
    - "bc1qw508d6qejxtdg4y5r3zarvary0c5xw7kv8f3t4"
  icon: coin
  references: null
  notes: >
    v1 migration: Was payment.bitcoin_address. Pattern covers P2PKH (1..),
    P2SH (3..), P2WPKH (bc1...). Addresses are not case-sensitive but typically
    use mixed case for checksums.

finance.crypto.ethereum_address:
  title: "Ethereum Address"
  description: >
    Ethereum wallet or contract address. 42 characters: 0x + 40 hex digits.
    May be EIP-55 checksummed (mixed case) or not.
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "LOWER(CAST({col} AS VARCHAR))"
  transform_ext: null
  decompose: null
  validation:
    type: string
    pattern: "^0x[a-fA-F0-9]{40}$"
  tier: [VARCHAR, payment]
  aliases: null
  samples:
    - "0x742d35Cc6634C0532925a3b844Bc3e7c1f2B9C2C"
    - "0x0000000000000000000000000000000000000000"
    - "0xd9965507d3b0ce0ce6932b0337db0c11b85fcf7d"
  icon: coin
  references: null
  notes: >
    v1 migration: Was payment.ethereum_address. Transform lowercases.
    EIP-55 checksummed addresses use mixed case but lowercase canonical form
    is preferable for storage.

# ═════════════════════════════════════════════════════════════════════
# NEW CURRENCY FORMATS (Format Coverage Expansion)
#
# 11 additional currency amount formats covering:
#   - Accounting notation (parentheses for negatives)
#   - Non-Western grouping (Indian lakh/crore, Swiss apostrophe)
#   - Symbol position variants (EU suffix, code prefix)
#   - Zero-decimal currencies (JPY, KRW, VND)
#   - Minor unit representation (Stripe/Adyen integer cents)
#   - Multi-character currency symbols (R$, HK$, kr, Kč)
#   - Cryptocurrency high-precision amounts
#   - Alternative negative notation (trailing minus, CR/DR)
# ═════════════════════════════════════════════════════════════════════

finance.currency.amount_accounting:
  title: "Currency Amount (US Accounting)"
  description: >
    US accounting format where negative values are enclosed in parentheses
    rather than using a minus sign. Standard in 10-K filings, ledgers,
    SAP, Smartsheet, and Dataiku exports. Comma thousands, period decimal.
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE({col}, '(', '-'), ')', ''), '$', ''), ',', ''), ' ', '') AS DECIMAL(18,2))"
  transform_ext: null
  decompose:
    is_negative: "CASE WHEN {col} LIKE '(%' THEN true ELSE false END"
    numeric_value: "CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE({col}, '(', '-'), ')', ''), '$', ''), ',', ''), ' ', '') AS DECIMAL(18,2))"
  validation:
    type: string
    pattern: "^\\(?\\$?[0-9]{1,3}(,[0-9]{3})*(\\.[0-9]{1,2})?\\)?$"
  tier: [VARCHAR, currency]
  aliases: [amount_accounting_us, accounting, gaap_amount]
  samples:
    - "$1,234.56"
    - "($1,234.56)"
    - "$0.50"
    - "($999.99)"
    - "$10,000.00"
  icon: cash
  references: null
  notes: >
    New in format coverage expansion. Parentheses = negative is universal
    in US GAAP accounting. Transform strips parens→minus, symbol, commas.

finance.currency.amount_comma_suffix:
  title: "Currency Amount (EU Suffix)"
  description: >
    European format with currency symbol as suffix. Period for thousands,
    comma for decimal, symbol after amount with optional space.
    CLDR default for Germany, France, Spain, Italy, Netherlands.
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, '[€$£¥₹₩₿\\p{Sc}]', '', 'g'), '\\s+', '', 'g'), '\\.', '', 'g'), ',', '.') AS DECIMAL(18,2))"
  transform_ext: null
  decompose:
    symbol: "REGEXP_EXTRACT({col}, '([€$£¥₹₩₿\\p{Sc}]+)\\s*$', 1)"
    numeric_value: "CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, '[€$£¥₹₩₿\\p{Sc}]', '', 'g'), '\\s+', '', 'g'), '\\.', '', 'g'), ',', '.') AS DECIMAL(18,2))"
  validation:
    type: string
    pattern: "^-?[0-9]{1,3}(\\.[0-9]{3})*(,[0-9]{1,2})?\\s?[€$£¥₹₩₿\\p{Sc}]$"
  tier: [VARCHAR, currency]
  aliases: [amount_eu_suffix, eu_suffix]
  samples:
    - "1.234,56 €"
    - "999,99 €"
    - "-1.234,56 €"
    - "0,50 €"
    - "10.000,00 €"
  icon: cash
  references: null
  notes: >
    New in format coverage expansion. CLDR default for most eurozone countries.
    Distinct from amount_comma which allows prefix symbols.

finance.currency.amount_space:
  title: "Currency Amount (Space-Separated Thousands)"
  description: >
    European variant using thin space or regular space for thousands
    grouping, comma for decimal, symbol as suffix. French, Polish,
    Czech formatting convention. Watch for NBSP (U+00A0).
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, '[€$£¥₹₩₿\\p{Sc}]', '', 'g'), '[\\s\\x{00A0}]+', '', 'g'), ',', '.'), '\\.$', '') AS DECIMAL(18,2))"
  transform_ext: null
  decompose:
    symbol: "REGEXP_EXTRACT({col}, '([€$£¥₹₩₿\\p{Sc}]+)', 1)"
    numeric_value: "CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, '[€$£¥₹₩₿\\p{Sc}]', '', 'g'), '[\\s\\x{00A0}]+', '', 'g'), ',', '.'), '\\.$', '') AS DECIMAL(18,2))"
  validation:
    type: string
    pattern: "^-?[0-9]{1,3}([\\s\\x{00A0}][0-9]{3})*(,[0-9]{1,2})?\\s?[€$£¥₹₩₿\\p{Sc}]?$"
  tier: [VARCHAR, currency]
  aliases: [amount_space_sep, fr_amount]
  samples:
    - "1 234,56 €"
    - "999,99 €"
    - "-1 234,56 €"
    - "10 000,00 €"
  icon: cash
  references: null
  notes: >
    New in format coverage expansion. French/Polish/Czech convention.
    Must handle both regular space and NBSP (U+00A0) as grouping separator.

finance.currency.amount_lakh:
  title: "Currency Amount (Indian Lakh/Crore)"
  description: >
    Indian numbering system with irregular grouping: first three digits,
    then groups of two (XX,XX,XXX pattern). Lakh = 1,00,000 (100k),
    Crore = 1,00,00,000 (10M). Serves 1.4 billion people. Rupee symbol
    prefix. Period decimal separator.
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "CAST(REGEXP_REPLACE(REGEXP_REPLACE({col}, '^(₹|Rs\\.?)\\s*', ''), ',', '', 'g') AS DECIMAL(18,2))"
  transform_ext: null
  decompose:
    symbol: "REGEXP_EXTRACT({col}, '^(₹|Rs\\.?)', 1)"
    numeric_value: "CAST(REGEXP_REPLACE(REGEXP_REPLACE({col}, '^(₹|Rs\\.?)\\s*', ''), ',', '', 'g') AS DECIMAL(18,2))"
  validation:
    type: string
    pattern: "^[₹]?(?:Rs\\.?\\s?)?[0-9]{1,2}(,[0-9]{2})*(,[0-9]{3})(\\.[0-9]{1,2})?$"
  tier: [VARCHAR, currency]
  aliases: [amount_indian, lakh_crore, inr_amount]
  samples:
    - "₹12,34,567.89"
    - "₹1,00,000.00"
    - "₹99,999.50"
    - "Rs. 1,23,456.78"
    - "₹1,00,00,000.00"
  icon: cash
  references: null
  notes: >
    New in format coverage expansion. Indian numbering (2,2,3 grouping).
    Key distinguishing signal is irregular comma placement.

finance.currency.amount_apostrophe:
  title: "Currency Amount (Swiss Apostrophe)"
  description: >
    Swiss/Liechtenstein format using apostrophe for thousands separator
    and period for decimal. Supports both straight apostrophe (U+0027)
    and right single quotation mark (U+2019).
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, 'CHF\\s?', ''), '[''\\x{2019}]', '', 'g'), '\\s+', '', 'g') AS DECIMAL(18,2))"
  transform_ext: null
  decompose:
    symbol: "CASE WHEN {col} LIKE '%CHF%' THEN 'CHF' ELSE NULL END"
    numeric_value: "CAST(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, 'CHF\\s?', ''), '[''\\x{2019}]', '', 'g'), '\\s+', '', 'g') AS DECIMAL(18,2))"
  validation:
    type: string
    pattern: "^(CHF\\s?)?-?[0-9]{1,3}(['\\x{2019}][0-9]{3})*(\\.[0-9]{1,2})?(\\s?CHF)?$"
  tier: [VARCHAR, currency]
  aliases: [amount_ch, swiss_amount]
  samples:
    - "CHF 1'234.56"
    - "1'234.56 CHF"
    - "CHF 10'000.00"
    - "-CHF 999.99"
  icon: cash
  references: null
  notes: >
    New in format coverage expansion. Must handle both U+0027 (apostrophe)
    and U+2019 (right single quotation mark) as thousands separator.

finance.currency.amount_nodecimal:
  title: "Currency Amount (Zero-Decimal Currency)"
  description: >
    Currency amounts for zero-decimal currencies (JPY, KRW, VND, CLP,
    ISK, HUF, and ~15 others). No fractional part. Comma thousands
    grouping with symbol prefix or suffix. broad_type is INTEGER.
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "CAST(REGEXP_REPLACE(REGEXP_REPLACE({col}, '[¥₩\\p{Sc}]', '', 'g'), ',', '', 'g') AS INTEGER)"
  transform_ext: null
  decompose:
    symbol: "REGEXP_EXTRACT({col}, '([¥₩\\p{Sc}])', 1)"
    numeric_value: "CAST(REGEXP_REPLACE(REGEXP_REPLACE({col}, '[¥₩\\p{Sc}]', '', 'g'), ',', '', 'g') AS INTEGER)"
  validation:
    type: string
    pattern: "^[¥₩\\p{Sc}]?-?[0-9]{1,3}(,[0-9]{3})*[¥₩\\p{Sc}]?$"
  tier: [VARCHAR, currency]
  aliases: [jpy_amount, zero_decimal]
  samples:
    - "¥1,234"
    - "₩10,000"
    - "¥100,000"
    - "₩1,234,567"
  icon: cash
  references: null
  notes: >
    New in format coverage expansion. Zero-decimal currencies per ISO 4217:
    JPY, KRW, VND, CLP, ISK, HUF, etc. No fractional component.

finance.currency.amount_code_prefix:
  title: "Currency Amount (ISO Code Prefix)"
  description: >
    ISO 4217 three-letter currency code as prefix, followed by amount.
    Standard in SWIFT messages, banking/FX systems, and international
    financial data. Separator conventions vary by currency.
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "CAST(REGEXP_REPLACE(REGEXP_REPLACE({col}, '^[A-Z]{3}\\s?', ''), ',', '', 'g') AS DECIMAL(18,2))"
  transform_ext: null
  decompose:
    currency_code: "REGEXP_EXTRACT({col}, '^([A-Z]{3})', 1)"
    numeric_value: "CAST(REGEXP_REPLACE(REGEXP_REPLACE({col}, '^[A-Z]{3}\\s?', ''), ',', '', 'g') AS DECIMAL(18,2))"
  validation:
    type: string
    pattern: "^[A-Z]{3}\\s?-?[0-9]{1,3}([,.]?[0-9]{3})*(\\.[0-9]{1,2})?$"
  tier: [VARCHAR, currency]
  aliases: [iso_code_amount]
  samples:
    - "USD 1,234.56"
    - "EUR 1.234,56"
    - "GBP 999.99"
    - "JPY 10,000"
  icon: cash
  references: null
  notes: >
    New in format coverage expansion. SWIFT, FX, banking standard.

finance.currency.amount_crypto:
  title: "Currency Amount (Cryptocurrency)"
  description: >
    Cryptocurrency amount with ticker symbol suffix. Supports high-precision
    decimals (up to 8+ decimal places). Common in exchange exports and
    portfolio tracking data.
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "CAST(REGEXP_REPLACE({col}, '\\s*[A-Z]{2,5}$', '') AS DECIMAL(18,8))"
  transform_ext: null
  decompose:
    ticker: "REGEXP_EXTRACT({col}, '([A-Z]{2,5})$', 1)"
    numeric_value: "CAST(REGEXP_REPLACE({col}, '\\s*[A-Z]{2,5}$', '') AS DECIMAL(18,8))"
  validation:
    type: string
    pattern: "^-?[0-9]+\\.?[0-9]*\\s?[A-Z]{2,5}$"
  tier: [VARCHAR, currency]
  aliases: [crypto_amount]
  samples:
    - "0.00123456 BTC"
    - "1.50000000 ETH"
    - "100.00 USDT"
    - "0.025 BTC"
  icon: cash
  references: null
  notes: >
    New in format coverage expansion. Crypto amounts can have up to 18 decimal
    places (ETH). Transform casts to DECIMAL(18,8) for reasonable precision.

finance.currency.amount_multisym:
  title: "Currency Amount (Multi-Character Symbol)"
  description: >
    Currency amounts with multi-character symbols: R$ (Brazilian Real),
    HK$ (Hong Kong Dollar), kr (Nordic Krone), Kč (Czech Koruna),
    zł (Polish Zloty). Separator conventions follow local standard.
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "CAST(REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, '^[^0-9-]+|\\s', '', 'g'), '[.,]([0-9]{1,2})$', 'D\\1'), '[.,]', '', 'g'), 'D', '.') AS DECIMAL(18,2))"
  transform_ext: null
  decompose:
    symbol: "REGEXP_EXTRACT({col}, '^([^0-9\\s-]+)', 1)"
  validation:
    type: string
    pattern: "^(R\\$|HK\\$|NT\\$|S\\$|A\\$|C\\$|NZ\\$|kr|Kč|zł|Ft|lei|Rp|Rs\\.?)\\s?-?[0-9]+([.,\\s][0-9]+)*$"
  tier: [VARCHAR, currency]
  aliases: []
  samples:
    - "R$ 1.234,56"
    - "HK$ 1,234.56"
    - "kr 1 234,56"
    - "Kč 1 234,56"
    - "zł 1 234,56"
  icon: cash
  references: null
  notes: >
    New in format coverage expansion. Multi-character symbols generalize
    locale-specific parsing. Transform strips symbol, normalizes separators.

finance.currency.amount_neg_trailing:
  title: "Currency Amount (Trailing Negative)"
  description: >
    Currency amount with trailing minus sign or CR/DR suffix for negative
    values. Standard in SAP, COBOL/mainframe exports, and bank statement
    formats. The negative indicator follows the amount.
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "CAST(CASE WHEN {col} LIKE '%-' OR {col} LIKE '%CR' THEN '-' || REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, '[-]$|\\s*(CR|DR)$', ''), '[\\$€£¥₹]', ''), ',', '', 'g') ELSE REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE({col}, '[-]$|\\s*(CR|DR)$', ''), '[\\$€£¥₹]', ''), ',', '', 'g') END AS DECIMAL(18,2))"
  transform_ext: null
  decompose:
    is_negative: "CASE WHEN {col} LIKE '%-' OR {col} LIKE '%CR' THEN true ELSE false END"
  validation:
    type: string
    pattern: "^[\\$€£¥₹]?[0-9]{1,3}(,[0-9]{3})*(\\.[0-9]{1,2})?\\s?(-|CR|DR)$"
  tier: [VARCHAR, currency]
  aliases: [sap_amount, cobol_amount]
  samples:
    - "$1,234.56-"
    - "1,234.56 CR"
    - "$999.99-"
    - "10,000.00 DR"
  icon: cash
  references: null
  notes: >
    New in format coverage expansion. SAP, COBOL/mainframe, bank statements.
    CR = credit (negative in expense context), DR = debit.

# ═════════════════════════════════════════════════════════════════════
# CATEGORY: rate
#
# Financial rates and yield measures. Distinct from currency amounts
# in that they represent percentages, basis points, or return rates
# rather than monetary values.
# ═════════════════════════════════════════════════════════════════════

finance.rate.basis_points:
  title: "Basis Points"
  description: >
    Financial rate expressed in basis points (bps or bp). One basis
    point = 0.01% = 0.0001 in decimal. Standard in fixed-income markets,
    central bank data, and debt analysis. Common in Bloomberg, Reuters,
    and central bank feeds.
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "CAST(REGEXP_REPLACE({col}, '\\s*(bps|bp)$', '') AS DECIMAL(18,4)) / 10000"
  transform_ext: null
  decompose:
    raw_bps: "CAST(REGEXP_REPLACE({col}, '\\s*(bps|bp)$', '') AS DECIMAL(10,2))"
    decimal_rate: "CAST(REGEXP_REPLACE({col}, '\\s*(bps|bp)$', '') AS DECIMAL(18,4)) / 10000"
  validation:
    type: string
    pattern: "^-?[0-9]+(\\.[0-9]+)?\\s?(bps|bp)$"
  tier: [VARCHAR, rate]
  aliases: [bps]
  samples:
    - "125 bps"
    - "25.5 bps"
    - "-50 bp"
    - "100 bps"
  icon: chart-bar
  references: null
  notes: >
    New in format coverage expansion. Fixed-income standard unit.
    Transform divides by 10000 to convert to decimal rate.

finance.rate.yield:
  title: "Financial Yield / Return"
  description: >
    Financial return or yield notation with explicit sign and percentage.
    Common in Bloomberg, Yahoo Finance, investment return reports.
    Distinct from percentage by requiring explicit +/- sign prefix.
  designation: universal
  locales: [UNIVERSAL]
  broad_type: VARCHAR
  frictionless:
    type: string
  format_string: null
  transform: "CAST(REGEXP_REPLACE({col}, '%$', '') AS DECIMAL(10,4)) / 100"
  transform_ext: null
  decompose:
    raw_pct: "CAST(REGEXP_REPLACE({col}, '%$', '') AS DECIMAL(10,4))"
    decimal_rate: "CAST(REGEXP_REPLACE({col}, '%$', '') AS DECIMAL(10,4)) / 100"
  validation:
    type: string
    pattern: "^[+-][0-9]+(\\.[0-9]+)?%$"
  tier: [VARCHAR, rate]
  aliases: [return_pct]
  samples:
    - "+2.5%"
    - "-1.2%"
    - "+0.75%"
    - "-3.25%"
    - "+10.0%"
  icon: chart-bar
  references: null
  notes: >
    New in format coverage expansion. Distinct from percentage type by
    mandatory +/- sign prefix. Financial returns, yield notation.