"""SQLite connection helpers, query/execute wrappers and the perf caches."""
import sqlite3
import threading
import time

from nexora.config import DB_PATH


_ttl_cache = {}


_ttl_cache_lock = threading.Lock()


_public_response_cache = {}


_public_response_cache_lock = threading.Lock()


_db_wal_initialized = False


_db_wal_lock = threading.Lock()


# ────────────────────────────────────────────
# Database helpers
# ────────────────────────────────────────────
def db():
    global _db_wal_initialized
    conn = sqlite3.connect(DB_PATH, timeout=30)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA busy_timeout = 30000")
    conn.execute("PRAGMA synchronous = NORMAL")
    if not _db_wal_initialized:
        with _db_wal_lock:
            if not _db_wal_initialized:
                conn.execute("PRAGMA journal_mode = WAL")
                _db_wal_initialized = True
    return conn


def query(sql, args=(), one=False):
    with db() as conn:
        rows = conn.execute(sql, args).fetchall()
    return (rows[0] if rows else None) if one else rows


def execute(sql, args=()):
    with db() as conn:
        cur = conn.execute(sql, args)
        conn.commit()
        clear_perf_caches()
        return cur.lastrowid


def clear_perf_caches():
    with _ttl_cache_lock:
        _ttl_cache.clear()
    with _public_response_cache_lock:
        _public_response_cache.clear()


def cached_query(cache_key, sql, args=(), one=False, ttl=20):
    if ttl <= 0:
        return query(sql, args, one=one)
    now_ts = time.time()
    key = (cache_key, sql, tuple(args), one)
    with _ttl_cache_lock:
        cached = _ttl_cache.get(key)
        if cached and cached[0] > now_ts:
            return cached[1]
    value = query(sql, args, one=one)
    with _ttl_cache_lock:
        _ttl_cache[key] = (now_ts + ttl, value)
    return value