code_stats.sql

-- ============================================================
-- CodeTrack — per-code stats materialized views + RPCs
-- Run this whole file once in the Supabase SQL Editor.
--
-- Fixes: 7d/30d/gas/chart were computed in the browser from the
-- newest ~100 (stats) / ~1000 (chart, PostgREST cap) raw rows, so
-- mega codes capped at 100 and the chart collapsed to a single spike.
-- These views pre-aggregate on the server so every metric is exact.
--
-- `transactions.codes` is an array column, so we unnest(codes) to get
-- one row per (code, transaction).
-- ============================================================

-- ------------------------------------------------------------
-- 1. code_daily_stats  — per (code, day)
--    Feeds: per-day chart, 7d/30d tx counts, total gas, all-time tx.
-- ------------------------------------------------------------
CREATE MATERIALIZED VIEW IF NOT EXISTS code_daily_stats AS
SELECT
  c                                          AS code,
  (block_timestamp AT TIME ZONE 'UTC')::date AS day,
  count(*)::bigint                           AS tx_count,
  sum(gas_used::numeric)                     AS gas_used
FROM transactions, unnest(codes) AS c
GROUP BY c, (block_timestamp AT TIME ZONE 'UTC')::date;

-- Unique index is REQUIRED for REFRESH ... CONCURRENTLY.
CREATE UNIQUE INDEX IF NOT EXISTS ux_code_daily_stats
  ON code_daily_stats (code, day);
CREATE INDEX IF NOT EXISTS ix_code_daily_stats_code_day
  ON code_daily_stats (code, day DESC);

-- ------------------------------------------------------------
-- 2. code_lifetime_stats  — per code, all-time unique senders
--    (count(distinct) can't be summed across days, so it lives
--     in its own view instead of code_daily_stats.)
-- ------------------------------------------------------------
CREATE MATERIALIZED VIEW IF NOT EXISTS code_lifetime_stats AS
SELECT
  c                                           AS code,
  count(DISTINCT lower(from_address))::bigint AS unique_senders
FROM transactions, unnest(codes) AS c
GROUP BY c;

CREATE UNIQUE INDEX IF NOT EXISTS ux_code_lifetime_stats
  ON code_lifetime_stats (code);

-- ------------------------------------------------------------
-- 3. get_code_stats(p_code)
--    total_tx / tx_7d / tx_30d / total_gas  <- code_daily_stats
--    unique_senders                          <- code_lifetime_stats
--    SECURITY DEFINER so the anon key can call it without direct
--    SELECT grants on the materialized views.
-- ------------------------------------------------------------
CREATE OR REPLACE FUNCTION get_code_stats(p_code text)
RETURNS TABLE(
  total_tx       bigint,
  tx_7d          bigint,
  tx_30d         bigint,
  unique_senders bigint,
  total_gas      numeric
)
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT
    coalesce((SELECT sum(tx_count)
                FROM code_daily_stats
               WHERE code = p_code), 0)::bigint,
    coalesce((SELECT sum(tx_count)
                FROM code_daily_stats
               WHERE code = p_code
                 AND day >= current_date - 6), 0)::bigint,
    coalesce((SELECT sum(tx_count)
                FROM code_daily_stats
               WHERE code = p_code
                 AND day >= current_date - 29), 0)::bigint,
    coalesce((SELECT unique_senders
                FROM code_lifetime_stats
               WHERE code = p_code), 0)::bigint,
    coalesce((SELECT sum(gas_used)
                FROM code_daily_stats
               WHERE code = p_code), 0)::numeric;
$$;

-- ------------------------------------------------------------
-- 4. get_code_daily_chart(p_code, p_days)
--    One row per day for the last p_days days, missing days = 0.
--    generate_series + LEFT JOIN does the gap-filling the frontend
--    used to do in JS. Returns oldest -> newest.
-- ------------------------------------------------------------
CREATE OR REPLACE FUNCTION get_code_daily_chart(p_code text, p_days int DEFAULT 30)
RETURNS TABLE(
  day      date,
  tx_count bigint
)
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT
    d::date                         AS day,
    coalesce(s.tx_count, 0)::bigint AS tx_count
  FROM generate_series(
         current_date - (p_days - 1),
         current_date,
         interval '1 day'
       ) AS d
  LEFT JOIN code_daily_stats s
    ON s.code = p_code
   AND s.day  = d::date
  ORDER BY d;
$$;

-- Allow the public (anon) and logged-in keys to call the RPCs.
GRANT EXECUTE ON FUNCTION get_code_stats(text)            TO anon, authenticated;
GRANT EXECUTE ON FUNCTION get_code_daily_chart(text, int) TO anon, authenticated;

-- ============================================================
-- MANUAL REFRESH
-- CREATE MATERIALIZED VIEW above already populates the views once.
-- After the indexer ingests new transactions, re-run these to update.
-- CONCURRENTLY keeps the views readable during the refresh and relies
-- on the unique indexes created above.
--
--   REFRESH MATERIALIZED VIEW CONCURRENTLY code_daily_stats;
--   REFRESH MATERIALIZED VIEW CONCURRENTLY code_lifetime_stats;
-- ============================================================

-- ============================================================
-- OPTIONAL: scheduled refresh (pg_cron) — hourly, not enabled by default.
-- Uncomment if/when you want automatic refreshes instead of manual ones.
--
--   CREATE EXTENSION IF NOT EXISTS pg_cron;
--
--   SELECT cron.schedule(
--     'refresh_code_daily_stats', '0 * * * *',
--     $$REFRESH MATERIALIZED VIEW CONCURRENTLY code_daily_stats$$
--   );
--   SELECT cron.schedule(
--     'refresh_code_lifetime_stats', '0 * * * *',
--     $$REFRESH MATERIALIZED VIEW CONCURRENTLY code_lifetime_stats$$
--   );
-- ============================================================