src/db.py

# src/db.py — SQLite helpers (HF Spaces-friendly)
from __future__ import annotations
import os, shutil, sqlite3
from pathlib import Path

# Canonical schema lives in repo
REPO_DIR = Path(__file__).resolve().parents[1]
CANONICAL_SCHEMA = REPO_DIR / "src" / "schema.sql"

# Writable defaults on HF Spaces
DEFAULT_DB = Path("/tmp/pol_indexer.sqlite")
DEFAULT_SCHEMA = Path("/tmp/schema.sql")

def _is_writable(p: Path) -> bool:
    try:
        p.parent.mkdir(parents=True, exist_ok=True)
        test = p.parent / ".write_test"
        test.write_text("ok", encoding="utf-8")
        test.unlink(missing_ok=True)
        return True
    except Exception:
        return False

# Resolve DB_PATH
_env_db = os.getenv("DB_PATH")
DB_PATH = Path(_env_db).resolve() if _env_db else DEFAULT_DB
if not _is_writable(DB_PATH):
    DB_PATH = DEFAULT_DB

# Resolve SCHEMA_PATH
_env_schema = os.getenv("SCHEMA_PATH")
SCHEMA_PATH = Path(_env_schema).resolve() if _env_schema else DEFAULT_SCHEMA
if not _is_writable(SCHEMA_PATH):
    SCHEMA_PATH = DEFAULT_SCHEMA

# Ensure runtime schema exists in the chosen writable location
def _ensure_runtime_schema():
    if SCHEMA_PATH.exists():
        return
    src = CANONICAL_SCHEMA if CANONICAL_SCHEMA.exists() else (REPO_DIR / "schema.sql")
    if not src.exists():
        raise FileNotFoundError(f"Canonical schema not found at {src}")
    SCHEMA_PATH.parent.mkdir(parents=True, exist_ok=True)
    shutil.copyfile(src, SCHEMA_PATH)

def _log_paths():
    print("[DB paths]")
    print(f"  REPO_DIR      = {REPO_DIR}")
    print(f"  CANON_SCHEMA  = {CANONICAL_SCHEMA} (exists={CANONICAL_SCHEMA.exists()})")
    print(f"  RUNTIME_SCHEMA= {SCHEMA_PATH} (exists={SCHEMA_PATH.exists()})")
    print(f"  DB_PATH       = {DB_PATH} (exists={DB_PATH.exists()})")

# Call once on import
_ensure_runtime_schema()
_log_paths()

def get_connection() -> sqlite3.Connection:
    conn = sqlite3.connect(str(DB_PATH), timeout=30, check_same_thread=False)
    conn.row_factory = sqlite3.Row
    with conn:
        conn.execute("PRAGMA journal_mode=WAL;")
        conn.execute("PRAGMA foreign_keys=ON;")
        conn.execute("PRAGMA synchronous=NORMAL;")
    return conn

def init_db(w3=None, confirmations: int = 12) -> None:
    """
    Initialize or verify DB schema.
    w3 + confirmations are accepted for compatibility with indexer,
    but not used directly here. This way you won’t get argument errors.
    """
    sql = SCHEMA_PATH.read_text(encoding="utf-8")
    with get_connection() as conn:
        conn.executescript(sql)
    print(f"[DB] Schema verified. Using DB at {DB_PATH}")

def get_latest_netflow():
    with get_connection() as conn:
        cur = conn.cursor()
        cur.execute("""
            SELECT created_at, cumulative_value
            FROM netflow
            ORDER BY created_at DESC
            LIMIT 1
        """)
        row = cur.fetchone()
        if not row:
            return None
        return {"created_at": row["created_at"], "cumulative_value": str(row["cumulative_value"])}

def table_counts() -> dict:
    out = {}
    with get_connection() as conn:
        c = conn.cursor()
        for t in ("transfers", "netflow", "raw_logs", "meta"):
            try:
                c.execute(f"SELECT COUNT(*) AS n FROM {t}")
                out[t] = int(c.fetchone()["n"])
            except Exception:
                out[t] = -1
    return out


# do not refer

# import sqlite3
# from pathlib import Path

# DB_PATH = Path(__file__).resolve().parent.parent / "data" / "netflow.db"

# def get_connection():
#     conn = sqlite3.connect(DB_PATH)
#     conn.row_factory = sqlite3.Row  # lets us fetch rows like dictionary
#     return conn

# def init_db():
#     conn = get_connection()
#     cur = conn.cursor()

#     # table for raw transactions we care about
#     cur.execute("""
#     CREATE TABLE IF NOT EXISTS transactions (
#         tx_hash TEXT PRIMARY KEY,
#         block_number INTEGER,
#         from_address TEXT,
#         to_address TEXT,
#         value REAL,
#         timestamp INTEGER
#     );
#     """)

#     # table to track cumulative netflow / running netflow values
#     cur.execute("""
#     CREATE TABLE IF NOT EXISTS netflow (
#         id INTEGER PRIMARY KEY AUTOINCREMENT,
#         timestamp INTEGER,
#         cumulative_value REAL
#     );
#     """)

#     conn.commit()
#     conn.close()
#     print(f"Database initialized at {DB_PATH}")