explorer/stats.go

package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"log"
	"net/http"
	"path/filepath"
	"strconv"
	"sync"
	"time"
)

// StatsService serves the Blockscout stats-microservice API surface
// (/v1/counters, /v1/lines, /v1/lines/{id}, /v1/pages/*), backed directly by
// the live indexer SQLite DB of whichever chain the request host names.
//
// The Next.js explorer turns on config.features.stats when
// NEXT_PUBLIC_STATS_API_HOST is set (it points at this backend). Its /stats page
// then requests /v1/counters and /v1/lines; without them it shows two error
// banners ("Something went wrong" + "Some of the charts did not load because the
// server didn't respond"). The indexer's own /v1/indexer/{slug}/stats endpoint
// is a different shape and its chart endpoints are empty stubs, so we serve real
// aggregates here, computed from evm_blocks / evm_transactions / evm_addresses.
type StatsService struct {
	dataDir string
	cfg     Config
	mu      sync.Mutex
	dbs     map[string]*sql.DB
}

// NewStatsService reads each chain's indexer DB at {dataDir}/{slug}/query/indexer.db.
func NewStatsService(dataDir string, cfg Config) *StatsService {
	return &StatsService{dataDir: dataDir, cfg: cfg, dbs: make(map[string]*sql.DB)}
}

// conn lazily opens a read-only handle to the DB of the chain the request host
// is served, one handle per chain. The indexer writes in WAL mode, so a
// read-only reader never blocks ingestion. The DB may not exist until the
// indexer has created it, so open is retried per-request until it succeeds.
func (s *StatsService) conn(r *http.Request) (*sql.DB, error) {
	slug := s.cfg.Serves(r.Host).Slug
	s.mu.Lock()
	defer s.mu.Unlock()
	if db, ok := s.dbs[slug]; ok {
		return db, nil
	}
	path := filepath.Join(s.dataDir, slug, "query", "indexer.db")
	db, err := sql.Open("sqlite3", "file:"+path+"?mode=ro&_busy_timeout=3000")
	if err != nil {
		return nil, err
	}
	db.SetMaxOpenConns(2)
	if err := db.Ping(); err != nil {
		db.Close()
		return nil, err
	}
	s.dbs[slug] = db
	return db, nil
}

// Mount registers the stats-service routes on mux.
//
// /v1/stats and /v1/blocks are the CANONICAL names. Everything under /v1 and
// nothing under /api — there is no /api/v2 here and never will be; see the
// house rule (api.* hosts carry /v1/ paths, and v1 is never bumped).
// /v1/counters and /v1/pages/* remain as the chart/page-shaped views the SPA
// already consumes; /v1/stats is the flat summary and is what external callers
// and health checks should use.
func (s *StatsService) Mount(mux *http.ServeMux) {
	mux.HandleFunc("GET /v1/stats", s.handleStats)
	mux.HandleFunc("GET /v1/blocks", s.handleBlocks)
	mux.HandleFunc("GET /v1/counters", s.handleCounters)
	mux.HandleFunc("GET /v1/lines", s.handleLines)
	mux.HandleFunc("GET /v1/lines/{id}", s.handleLine)
	mux.HandleFunc("GET /v1/pages/main", s.handlePagesMain)
	mux.HandleFunc("GET /v1/pages/transactions", s.handlePagesTransactions)
	mux.HandleFunc("GET /v1/pages/contracts", s.handlePagesContracts)
}

// ---- Wire shapes (match @luxfi/stats-types) ----

type statsCounter struct {
	ID          string `json:"id"`
	Value       string `json:"value"`
	Title       string `json:"title"`
	Units       string `json:"units,omitempty"`
	Description string `json:"description"`
}

type statsPoint struct {
	Date          string `json:"date"`
	DateTo        string `json:"date_to"`
	Value         string `json:"value"`
	IsApproximate bool   `json:"is_approximate"`
}

type statsLineInfo struct {
	ID          string   `json:"id"`
	Title       string   `json:"title"`
	Description string   `json:"description"`
	Units       string   `json:"units,omitempty"`
	Resolutions []string `json:"resolutions"`
}

type statsLineChart struct {
	Chart []statsPoint  `json:"chart"`
	Info  statsLineInfo `json:"info"`
}

func writeJSON(w http.ResponseWriter, code int, v any) {
	w.Header().Set("Content-Type", "application/json")
	w.WriteHeader(code)
	json.NewEncoder(w).Encode(v)
}

// ---- Aggregate helpers ----

// evm_blocks.timestamp is a column declared TIMESTAMP that the indexer fills by
// binding a Go time.Time, so SQLite stores a datetime STRING — never an
// integer. Reading it as an epoch is what silently emptied every chart on
// /stats: CAST('2026-08-08 12:55:22+00:00' AS INTEGER) is 2026 (SQLite takes
// the leading numeric prefix), and strftime with the 'unixepoch' modifier
// returns NULL because that modifier wants a number. strftime parses the
// string, applies its timezone, and answers in UTC. These are the only two
// spellings allowed to touch that column.
//
// Both name the column as `b.timestamp`, so every query below aliases
// evm_blocks to b — evm_transactions carries a timestamp too, and an
// unqualified reference is ambiguous the moment the two are joined.
const (
	blockEpoch = `CAST(strftime('%s', b.timestamp) AS INTEGER)`
	blockDay   = `strftime('%Y-%m-%d', b.timestamp)`
)

func (s *StatsService) count(db *sql.DB, table string) int64 {
	var n int64
	// #nosec G201 — table is a fixed internal identifier, never user input.
	db.QueryRow("SELECT COUNT(*) FROM " + table).Scan(&n)
	return n
}

// avgBlockTimeSec returns the lifetime average block interval in seconds,
// derived from the full timestamp span so it stays meaningful on a sparse,
// demand-driven chain (per-window deltas collapse to zero when idle).
//
// The span is measured between the FIRST and LAST block by number, not by
// MIN and MAX of the timestamp column. Timestamps are monotonic in block order
// by consensus rule, so the two agree on a sound index — and disagree loudly
// on a damaged one, where MIN/MAX hands a single stray row the whole history.
// Hanzo carries a block that parses to epoch 0: MIN/MAX anchored the span at
// 1970 and reported 58,370 seconds per block on a chain that builds one every
// ten. Zoo carries one dated 1.76 years before its own first block, and read
// 2,387. Both are one row out of tens of thousands, and neither is the
// interval between blocks.
//
// Blocks with no time at all are skipped: a block that does not say when it
// happened cannot bound how far apart blocks are.
func (s *StatsService) avgBlockTimeSec(db *sql.DB) float64 {
	var count, minTS, maxTS int64
	err := db.QueryRow(`SELECT
		(SELECT COUNT(*) FROM evm_blocks b WHERE `+blockEpoch+` > 0),
		COALESCE((SELECT `+blockEpoch+` FROM evm_blocks b WHERE `+blockEpoch+` > 0 ORDER BY number ASC LIMIT 1), 0),
		COALESCE((SELECT `+blockEpoch+` FROM evm_blocks b WHERE `+blockEpoch+` > 0 ORDER BY number DESC LIMIT 1), 0)`).
		Scan(&count, &minTS, &maxTS)
	if err != nil {
		// Say WHY. Swallowing this is how the tile read "0.0 s" on a chain with
		// 1.1M blocks: the scan failed on every request and the zero value went
		// out as if it were the answer.
		log.Printf("stats: average block time unavailable: %v", err)
		return 0
	}
	if count < 2 || maxTS <= minTS {
		return 0
	}
	return float64(maxTS-minTS) / float64(count-1)
}

// dailySeries buckets a COUNT by UTC day over the trailing `days` window.
func (s *StatsService) dailySeries(db *sql.DB, query string, days int) ([]statsPoint, error) {
	since := time.Now().AddDate(0, 0, -days).Unix()
	rows, err := db.Query(query, since)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	points := make([]statsPoint, 0, days)
	for rows.Next() {
		var day string
		var value int64
		if err := rows.Scan(&day, &value); err != nil {
			return nil, err
		}
		points = append(points, statsPoint{Date: day, DateTo: day, Value: fmt.Sprintf("%d", value)})
	}
	return points, rows.Err()
}

// blocksPerDay and txsPerDay are the two daily aggregates this service serves.
// Both bound the window on the epoch expression: comparing the datetime string
// against an integer bound is not just imprecise, SQLite orders every INTEGER
// below every TEXT, so `timestamp > 1754…` matched all 1.1M rows.
const (
	blocksPerDay = `SELECT ` + blockDay + ` d, COUNT(*) FROM evm_blocks b
		WHERE ` + blockEpoch + ` > ? GROUP BY d ORDER BY d`
	txsPerDay = `SELECT ` + blockDay + ` d, COUNT(*)
		FROM evm_transactions t JOIN evm_blocks b ON t.block_number = b.number
		WHERE ` + blockEpoch + ` > ? GROUP BY d ORDER BY d`
)

// ---- Handlers ----

func (s *StatsService) handleCounters(w http.ResponseWriter, r *http.Request) {
	db, err := s.conn(r)
	if err != nil {
		writeJSON(w, http.StatusOK, map[string]any{"counters": []statsCounter{}})
		return
	}
	blocks := s.count(db, "evm_blocks")
	txns := s.count(db, "evm_transactions")
	addrs := s.count(db, "evm_addresses")
	avgSec := s.avgBlockTimeSec(db)

	counters := []statsCounter{
		{ID: "total_blocks", Value: fmt.Sprintf("%d", blocks), Title: "Total blocks", Description: "All blocks indexed on the chain"},
		{ID: "total_txns", Value: fmt.Sprintf("%d", txns), Title: "Total transactions", Description: "All transactions indexed on the chain"},
		{ID: "completed_txns", Value: fmt.Sprintf("%d", txns), Title: "Completed transactions", Description: "Transactions included in a block"},
		{ID: "total_addresses", Value: fmt.Sprintf("%d", addrs), Title: "Total addresses", Description: "Distinct addresses seen on the chain"},
		{ID: "average_block_time", Value: fmt.Sprintf("%.1f", avgSec), Title: "Average block time", Units: "s", Description: "Average interval between blocks"},
	}
	writeJSON(w, http.StatusOK, map[string]any{"counters": counters})
}

// lineInfos is the catalogue of charts this backend can serve, each backed by a
// real SQLite aggregation.
var lineInfos = map[string]statsLineInfo{
	"newBlocksPerDay": {
		ID: "newBlocksPerDay", Title: "New blocks per day",
		Description: "Number of blocks produced each day", Resolutions: []string{"DAY"},
	},
	"newTxnsPerDay": {
		ID: "newTxnsPerDay", Title: "New transactions per day",
		Description: "Number of transactions each day", Resolutions: []string{"DAY"},
	},
}

func (s *StatsService) handleLines(w http.ResponseWriter, r *http.Request) {
	sections := []map[string]any{
		{
			"id": "blocks", "title": "Blocks",
			"charts": []statsLineInfo{lineInfos["newBlocksPerDay"]},
		},
		{
			"id": "transactions", "title": "Transactions",
			"charts": []statsLineInfo{lineInfos["newTxnsPerDay"]},
		},
	}
	writeJSON(w, http.StatusOK, map[string]any{"sections": sections})
}

func (s *StatsService) handleLine(w http.ResponseWriter, r *http.Request) {
	id := r.PathValue("id")
	info, ok := lineInfos[id]
	if !ok {
		writeJSON(w, http.StatusNotFound, map[string]any{"error": "unknown chart"})
		return
	}
	db, err := s.conn(r)
	if err != nil {
		writeJSON(w, http.StatusOK, statsLineChart{Chart: []statsPoint{}, Info: info})
		return
	}

	query := blocksPerDay
	if id == "newTxnsPerDay" {
		query = txsPerDay
	}
	points, err := s.dailySeries(db, query, 365)
	if err != nil {
		log.Printf("stats: chart %s failed: %v", id, err)
		writeJSON(w, http.StatusOK, statsLineChart{Chart: []statsPoint{}, Info: info})
		return
	}
	writeJSON(w, http.StatusOK, statsLineChart{Chart: points, Info: info})
}

// handleStats is the canonical flat summary: GET /v1/stats. Values are plain
// JSON numbers, not the counter envelopes /v1/counters uses, so a caller can
// compare total_blocks against eth_blockNumber without unwrapping anything.
// That comparison is the only honest way to tell whether the index is current —
// an HTTP 200 from the explorer proves nothing about whether it has the chain.
func (s *StatsService) handleStats(w http.ResponseWriter, r *http.Request) {
	db, err := s.conn(r)
	if err != nil {
		// No DB yet is a real state (fresh chain, indexer still starting), not
		// an error. Report zeroes and say so, rather than 500ing a health check.
		writeJSON(w, http.StatusOK, map[string]any{
			"total_blocks": 0, "total_transactions": 0, "total_addresses": 0,
			"average_block_time": 0, "indexed": false,
		})
		return
	}
	var head int64
	_ = db.QueryRow("SELECT COALESCE(MAX(number),0) FROM evm_blocks").Scan(&head)
	writeJSON(w, http.StatusOK, map[string]any{
		"total_blocks":       s.count(db, "evm_blocks"),
		"total_transactions": s.count(db, "evm_transactions"),
		"total_addresses":    s.count(db, "evm_addresses"),
		"average_block_time": s.avgBlockTimeSec(db),
		"head_block":         head,
		"indexed":            true,
	})
}

// handleBlocks is the canonical block list: GET /v1/blocks?limit=&before=.
// Newest first. `before` pages backwards by block number so a caller never has
// to guess an offset while the head advances underneath it.
func (s *StatsService) handleBlocks(w http.ResponseWriter, r *http.Request) {
	limit := 50
	if v := r.URL.Query().Get("limit"); v != "" {
		if n, err := strconv.Atoi(v); err == nil && n > 0 {
			limit = n
		}
	}
	if limit > 200 {
		limit = 200 // bound the page; an unbounded LIMIT is a DoS on a big index
	}
	db, err := s.conn(r)
	if err != nil {
		writeJSON(w, http.StatusOK, map[string]any{"blocks": []any{}, "indexed": false})
		return
	}
	// blockEpoch, not a bare read: go-sqlite3 converts a column declared
	// TIMESTAMP to time.Time, which will not Scan into an int64 — every row
	// would be skipped and the endpoint would answer an empty list on a fully
	// populated index. It parses the stored datetime rather than casting it,
	// which is what made this field read 2026 for every block on mainnet.
	query := `SELECT number, hash, parent_hash, miner, gas_limit, gas_used, base_fee,
		` + blockEpoch + `, tx_count FROM evm_blocks b`
	args := []any{}
	if before := r.URL.Query().Get("before"); before != "" {
		if n, err := strconv.ParseInt(before, 10, 64); err == nil {
			query += " WHERE number < ?"
			args = append(args, n)
		}
	}
	query += " ORDER BY number DESC LIMIT ?"
	args = append(args, limit)

	rows, err := db.Query(query, args...)
	if err != nil {
		// Say WHY. An empty list that silently means "the query broke" is
		// indistinguishable from "this chain has no blocks", and that ambiguity
		// is exactly how a scan-type bug hides behind a 200.
		log.Printf("v1/blocks: query failed: %v", err)
		writeJSON(w, http.StatusOK, map[string]any{"blocks": []any{}, "indexed": true, "error": err.Error()})
		return
	}
	defer rows.Close()

	blocks := []map[string]any{}
	for rows.Next() {
		var (
			number, gasLimit, gasUsed, ts, txCount int64
			hash, parent, miner, baseFee           sql.NullString
		)
		if err := rows.Scan(&number, &hash, &parent, &miner, &gasLimit, &gasUsed, &baseFee, &ts, &txCount); err != nil {
			log.Printf("v1/blocks: skipping row: %v", err)
			continue
		}
		blocks = append(blocks, map[string]any{
			"number": number, "hash": hash.String, "parent_hash": parent.String,
			"miner": miner.String, "gas_limit": gasLimit, "gas_used": gasUsed,
			"base_fee": baseFee.String, "timestamp": ts, "tx_count": txCount,
		})
	}
	writeJSON(w, http.StatusOK, map[string]any{"blocks": blocks, "indexed": true})
}

func (s *StatsService) handlePagesMain(w http.ResponseWriter, r *http.Request) {
	db, err := s.conn(r)
	if err != nil {
		writeJSON(w, http.StatusOK, map[string]any{})
		return
	}
	blocks := s.count(db, "evm_blocks")
	txns := s.count(db, "evm_transactions")
	addrs := s.count(db, "evm_addresses")
	avgSec := s.avgBlockTimeSec(db)
	daily, err := s.dailySeries(db, txsPerDay, 30)
	if err != nil {
		// A nil slice marshals to `null`, which the SPA renders as "No data" —
		// identical on the wire to a chain that genuinely has no transactions.
		// Log the reason and send an empty series so the two are distinguishable.
		log.Printf("stats: daily transactions failed: %v", err)
		daily = []statsPoint{}
	}

	writeJSON(w, http.StatusOK, map[string]any{
		"total_blocks":           statsCounter{ID: "total_blocks", Value: fmt.Sprintf("%d", blocks), Title: "Total blocks", Description: "All blocks"},
		"total_transactions":     statsCounter{ID: "total_txns", Value: fmt.Sprintf("%d", txns), Title: "Total transactions", Description: "All transactions"},
		"total_addresses":        statsCounter{ID: "total_addresses", Value: fmt.Sprintf("%d", addrs), Title: "Total addresses", Description: "Distinct addresses"},
		"average_block_time":     statsCounter{ID: "average_block_time", Value: fmt.Sprintf("%.1f", avgSec), Title: "Average block time", Units: "s", Description: "Average interval between blocks"},
		"daily_new_transactions": statsLineChart{Chart: daily, Info: lineInfos["newTxnsPerDay"]},
	})
}

func (s *StatsService) handlePagesTransactions(w http.ResponseWriter, r *http.Request) {
	db, err := s.conn(r)
	if err != nil {
		writeJSON(w, http.StatusOK, map[string]any{})
		return
	}
	// Bound on blockEpoch: SQLite orders every INTEGER below every TEXT, so the
	// datetime string was always "greater than" the epoch bound and this
	// reported the lifetime transaction count as the last 24 hours.
	var txns24h int64
	if err := db.QueryRow(`SELECT COUNT(*)
		FROM evm_transactions t JOIN evm_blocks b ON t.block_number = b.number
		WHERE `+blockEpoch+` > ?`, time.Now().AddDate(0, 0, -1).Unix()).Scan(&txns24h); err != nil {
		log.Printf("stats: 24h transactions failed: %v", err)
	}
	writeJSON(w, http.StatusOK, map[string]any{
		"transactions_24h": statsCounter{ID: "new_txns_24h", Value: fmt.Sprintf("%d", txns24h), Title: "Transactions (24h)", Description: "Transactions in the last 24 hours"},
	})
}

func (s *StatsService) handlePagesContracts(w http.ResponseWriter, r *http.Request) {
	writeJSON(w, http.StatusOK, map[string]any{})
}