WITH stablecoin_metrics AS (
    SELECT 
        CASE 
            WHEN code = 'RLUSD' AND meta_issuer_name = 'Ripple' THEN 'RLUSD (Ripple)'
            WHEN code = 'USD' AND meta_issuer_name = 'GateHub' THEN 'USD (GateHub)'
            WHEN code = 'USD' AND meta_issuer_name = 'Bitstamp' THEN 'USD (Bitstamp)'
            WHEN code = 'USDC' AND meta_issuer_name = 'Circle' THEN 'USDC (Circle)'
            WHEN code = 'USDT' THEN 'USDT (Tether)'
            ELSE CONCAT(code, ' (', COALESCE(meta_issuer_name, 'Unknown'), ')')
        END as stablecoin_name,
        holders,
        trustlines,
        supply,
        marketcap,
        price,
        createdat,
        -- Stability metrics (how close to $1 equivalent)
        ABS(price - 0.32) as price_deviation_from_peg,  -- Assuming ~0.32 XRP = $1
        CASE 
            WHEN price BETWEEN 0.30 AND 0.34 THEN 'Stable'
            WHEN price BETWEEN 0.25 AND 0.40 THEN 'Moderate Deviation'
            ELSE 'High Deviation'
        END as peg_stability
    FROM @xrpscan.xrp_ledger.tokens
    WHERE (code LIKE '%USD%' OR code = 'RLUSD')
      AND holders > 1000  -- Filter for significant tokens
)

SELECT 
    stablecoin_name,
    holders,
    ROUND(supply, 0) as supply,
    ROUND(marketcap, 0) as market_cap,
    ROUND(price, 6) as price_in_xrp,
    peg_stability,
    ROUND(price_deviation_from_peg, 6) as deviation_from_usd_peg,
    -- Market share calculations
    ROUND(holders * 100.0 / SUM(holders) OVER(), 2) as holder_market_share_pct,
    ROUND(supply * 100.0 / SUM(supply) OVER(), 2) as supply_market_share_pct,
    createdat as created_date,
    CASE 
        WHEN stablecoin_name LIKE '%RLUSD%' THEN 'Ripple Official Stablecoin'
        WHEN stablecoin_name LIKE '%GateHub%' THEN 'Gateway Provider'
        WHEN stablecoin_name LIKE '%Bitstamp%' THEN 'Exchange Issued'
        WHEN stablecoin_name LIKE '%Circle%' THEN 'Major Stablecoin Provider'
        ELSE 'Other Provider'
    END as issuer_category
FROM stablecoin_metrics
ORDER BY holders DESC;