import hashlib
import sqlite3
import time

from flask import g

DB_PATH = "/data/translations.db"


def _make_key(*parts: str) -> str:
    return hashlib.sha256(":".join(parts).encode()).hexdigest()


def init_db():
    db = sqlite3.connect(DB_PATH)
    db.execute("PRAGMA journal_mode=WAL")
    db.executescript("""
        CREATE TABLE IF NOT EXISTS cache (
            key        TEXT PRIMARY KEY,
            value      TEXT NOT NULL,
            created_at REAL NOT NULL
        );
        CREATE TABLE IF NOT EXISTS feeds (
            key        TEXT PRIMARY KEY,
            content    BLOB NOT NULL,
            fetched_at REAL NOT NULL
        );
    """)
    db.close()


def get_db():
    if "db" not in g:
        conn = sqlite3.connect(DB_PATH)
        conn.row_factory = sqlite3.Row
        g.db = conn
    return g.db


def close_db(exc):
    db = g.pop("db", None)
    if db is not None:
        db.close()


# ── Translation cache ─────────────────────────────────────────────────────────

def cache_key(text: str, lang: str) -> str:
    return _make_key(lang, text)


def get_translation(db, key: str, ttl: float):
    """Return cached translation value if present and fresh, else None."""
    row = db.execute(
        "SELECT value, created_at FROM cache WHERE key=?", (key,)
    ).fetchone()
    if row and (time.time() - row["created_at"]) < ttl:
        return row["value"]
    return None


def set_translation(db, key: str, value: str):
    db.execute(
        "INSERT OR REPLACE INTO cache (key, value, created_at) VALUES (?,?,?)",
        (key, value, time.time()),
    )
    db.commit()


def prune_translations(db, max_age: float):
    db.execute(
        "DELETE FROM cache WHERE (? - created_at) > ?",
        (time.time(), max_age),
    )
    db.commit()


# ── Feed cache ────────────────────────────────────────────────────────────────

def feed_cache_key(lang: str, path: str) -> str:
    return _make_key(lang, path)


def get_feed_cache(db, key: str, ttl: float):
    """Return (content: bytes, fetched_at: float) if fresh, else None."""
    row = db.execute(
        "SELECT content, fetched_at FROM feeds WHERE key=?", (key,)
    ).fetchone()
    if row and (time.time() - row["fetched_at"]) < ttl:
        return bytes(row["content"]), row["fetched_at"]
    return None


def set_feed_cache(db, key: str, content: bytes, fetched_at: float):
    db.execute(
        "INSERT OR REPLACE INTO feeds (key, content, fetched_at) VALUES (?,?,?)",
        (key, content, fetched_at),
    )
    db.commit()