import json
import os
import sqlite3
import time


BASE_DIR = os.path.dirname(os.path.dirname(__file__))
CACHE_DIR = os.path.join(BASE_DIR, ".cache")
DB_PATH = os.path.join(CACHE_DIR, "route_cache.sqlite3")


def _enabled():
    try:
        import config
        return bool(config._cfg.get("route_cache", {}).get("enabled", True))
    except Exception:
        return True


def _ttl_seconds():
    try:
        import config
        days = int(config._cfg.get("route_cache", {}).get("days", 30))
    except Exception:
        days = 30
    return max(days, 1) * 86400


def _connect():
    os.makedirs(CACHE_DIR, exist_ok=True)
    conn = sqlite3.connect(DB_PATH, timeout=10)
    conn.execute("""
        CREATE TABLE IF NOT EXISTS route_cache (
            callsign TEXT PRIMARY KEY,
            data_json TEXT NOT NULL,
            source TEXT NOT NULL DEFAULT '',
            first_seen INTEGER NOT NULL,
            last_seen INTEGER NOT NULL,
            hit_count INTEGER NOT NULL DEFAULT 0
        )
    """)
    conn.execute("CREATE INDEX IF NOT EXISTS idx_route_cache_last_seen ON route_cache(last_seen)")
    return conn


def get_route(callsign):
    if not _enabled():
        return None
    callsign = (callsign or "").strip().upper()
    if not callsign:
        return None
    now = int(time.time())
    try:
        with _connect() as conn:
            row = conn.execute(
                "SELECT data_json, last_seen, hit_count FROM route_cache WHERE callsign = ?",
                (callsign,),
            ).fetchone()
            if not row:
                return None
            data_json, last_seen, hit_count = row
            if now - int(last_seen) > _ttl_seconds():
                conn.execute("DELETE FROM route_cache WHERE callsign = ?", (callsign,))
                return None
            conn.execute(
                "UPDATE route_cache SET last_seen = ?, hit_count = ? WHERE callsign = ?",
                (now, int(hit_count or 0) + 1, callsign),
            )
            return json.loads(data_json)
    except Exception as e:
        print(f"[RouteDB] read failed for {callsign}: {e}")
        return None


def save_route(callsign, data, source=""):
    if not _enabled() or not data:
        return
    callsign = (callsign or "").strip().upper()
    if not callsign:
        return
    now = int(time.time())
    try:
        payload = json.dumps(data, separators=(",", ":"), sort_keys=True)
        with _connect() as conn:
            row = conn.execute(
                "SELECT first_seen, hit_count FROM route_cache WHERE callsign = ?",
                (callsign,),
            ).fetchone()
            first_seen = int(row[0]) if row else now
            hit_count = int(row[1]) if row else 0
            conn.execute(
                """
                INSERT OR REPLACE INTO route_cache
                    (callsign, data_json, source, first_seen, last_seen, hit_count)
                VALUES (?, ?, ?, ?, ?, ?)
                """,
                (callsign, payload, source or "", first_seen, now, hit_count),
            )
    except Exception as e:
        print(f"[RouteDB] save failed for {callsign}: {e}")


def stats():
    try:
        with _connect() as conn:
            count = conn.execute("SELECT COUNT(*) FROM route_cache").fetchone()[0]
            hits = conn.execute("SELECT COALESCE(SUM(hit_count), 0) FROM route_cache").fetchone()[0]
            return {"count": count, "hits": hits, "path": DB_PATH}
    except Exception:
        return {"count": 0, "hits": 0, "path": DB_PATH}

