explorer/stats.go

package main

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

// StatsService serves the Blockscout stats-microservice API surface
// (/v1/counters, /v1/lines, /v1/lines/{id}, /v1/pages/*) for the default chain,
// backed directly by the live indexer SQLite DB.
//
// 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 {
	dbPath string
	mu     sync.Mutex
	db     *sql.DB
}

// NewStatsService targets the default chain's indexer DB
// ({dataDir}/{slug}/query/indexer.db).
func NewStatsService(dbPath string) *StatsService {
	return &StatsService{dbPath: dbPath}
}

// conn lazily opens a read-only handle. 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() (*sql.DB, error) {
	s.mu.Lock()
	defer s.mu.Unlock()
	if s.db != nil {
		return s.db, nil
	}
	db, err := sql.Open("sqlite3", "file:"+s.dbPath+"?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.db = 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 ----

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).
func (s *StatsService) avgBlockTimeSec(db *sql.DB) float64 {
	var count, minTS, maxTS int64
	db.QueryRow("SELECT COUNT(*), COALESCE(MIN(timestamp),0), COALESCE(MAX(timestamp),0) FROM evm_blocks").
		Scan(&count, &minTS, &maxTS)
	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.
// timestamp is a unix-epoch integer, so strftime needs the 'unixepoch' modifier.
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()
}

// ---- Handlers ----

func (s *StatsService) handleCounters(w http.ResponseWriter, r *http.Request) {
	db, err := s.conn()
	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()
	if err != nil {
		writeJSON(w, http.StatusOK, statsLineChart{Chart: []statsPoint{}, Info: info})
		return
	}

	var query string
	switch id {
	case "newBlocksPerDay":
		query = `SELECT strftime('%Y-%m-%d', timestamp, 'unixepoch') d, COUNT(*)
			FROM evm_blocks WHERE timestamp > ? GROUP BY d ORDER BY d`
	case "newTxnsPerDay":
		query = `SELECT strftime('%Y-%m-%d', b.timestamp, 'unixepoch') d, COUNT(*)
			FROM evm_transactions t JOIN evm_blocks b ON t.block_number = b.number
			WHERE b.timestamp > ? GROUP BY d ORDER BY d`
	}
	points, err := s.dailySeries(db, query, 365)
	if err != nil {
		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()
	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()
	if err != nil {
		writeJSON(w, http.StatusOK, map[string]any{"blocks": []any{}, "indexed": false})
		return
	}
	// CAST(timestamp AS INTEGER) on purpose: the real schema declares the column
	// TIMESTAMP, and go-sqlite3 converts declared TIMESTAMP/DATETIME columns 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.
	// The CAST makes the wire type independent of the declared type.
	query := `SELECT number, hash, parent_hash, miner, gas_limit, gas_used, base_fee,
		CAST(timestamp AS INTEGER), tx_count FROM evm_blocks`
	args := []any{}
	if b := r.URL.Query().Get("before"); b != "" {
		if n, err := strconv.ParseInt(b, 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()
	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, _ := s.dailySeries(db, `SELECT strftime('%Y-%m-%d', b.timestamp, 'unixepoch') d, COUNT(*)
		FROM evm_transactions t JOIN evm_blocks b ON t.block_number = b.number
		WHERE b.timestamp > ? GROUP BY d ORDER BY d`, 30)

	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()
	if err != nil {
		writeJSON(w, http.StatusOK, map[string]any{})
		return
	}
	var txns24h int64
	db.QueryRow(`SELECT COUNT(*) FROM evm_transactions t JOIN evm_blocks b ON t.block_number = b.number
		WHERE b.timestamp > ?`, time.Now().AddDate(0, 0, -1).Unix()).Scan(&txns24h)
	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{})
}