-- CHAIN4663 RWA WATCH / Dune complement
-- Source and methodology: https://chain4663.com/metrics
-- Open snapshot: https://chain4663.com/data/classification.json
--
-- This query uses Dune LiveFetch because Robinhood Chain is not assumed to have
-- a native Dune decoded-table contract. It preserves CHAIN4663's exact-address
-- taxonomy and explicit UNCLASSIFIED bucket instead of relabeling the remainder.

WITH source AS (
  SELECT json_parse(http_get('https://chain4663.com/data/classification.json')) AS payload
),
days AS (
  SELECT day
  FROM source
  CROSS JOIN UNNEST(CAST(json_extract(payload, '$.history') AS ARRAY(JSON))) AS t(day)
),
categories(category) AS (
  VALUES 'CANONICAL_RWA', 'CLASSIFIED_MEMECOIN', 'UNCLASSIFIED'
)
SELECT
  CAST(json_extract_scalar(day, '$.observedDate') AS DATE) AS observed_date,
  category,
  TRY_CAST(json_extract_scalar(day, concat('$.', category, '.tokenCount')) AS BIGINT) AS token_count,
  TRY_CAST(json_extract_scalar(day, concat('$.', category, '.catalogMeasuredTokens')) AS BIGINT) AS catalog_measured_tokens,
  TRY_CAST(json_extract_scalar(day, concat('$.', category, '.transferMeasuredTokens')) AS BIGINT) AS transfer_measured_tokens,
  TRY_CAST(json_extract_scalar(day, concat('$.', category, '.holderPositions')) AS BIGINT) AS holder_positions,
  TRY_CAST(json_extract_scalar(day, concat('$.', category, '.indexedTransfersCumulative')) AS BIGINT) AS indexed_transfers_cumulative,
  TRY_CAST(json_extract_scalar(day, concat('$.', category, '.explorerVolume24hUsd')) AS DOUBLE) AS explorer_volume_24h_usd,
  TRY_CAST(json_extract_scalar(day, concat('$.', category, '.catalogComplete')) AS BOOLEAN) AS catalog_complete,
  'https://chain4663.com/metrics' AS methodology_url
FROM days
CROSS JOIN categories
WHERE json_extract(day, concat('$.', category)) IS NOT NULL
ORDER BY observed_date, category;
