"""
cache.py — lightweight SQLite cache for scraped Tenor results.

HF Spaces free tier gives you a persistent disk mounted at /data if you
enable "persistent storage", otherwise the filesystem resets on restart.
Either way this works fine — it just won't survive a restart without
persistent storage enabled. Path is overridable via TENOR_CACHE_PATH env var.
"""

import os
import json
import sqlite3
import time
import threading

DB_PATH = os.environ.get("TENOR_CACHE_PATH", "/data/tenor_cache.db")
# fall back to local dir if /data doesn't exist (no persistent storage enabled)
if not os.path.isdir(os.path.dirname(DB_PATH)):
    DB_PATH = os.environ.get("TENOR_CACHE_PATH_FALLBACK", "./tenor_cache.db")

_lock = threading.Lock()


def _connect():
    conn = sqlite3.connect(DB_PATH, check_same_thread=False)
    conn.row_factory = sqlite3.Row
    return conn


def init_db():
    with _lock:
        conn = _connect()
        conn.execute("""
            CREATE TABLE IF NOT EXISTS search_cache (
                query TEXT NOT NULL,
                page INTEGER NOT NULL DEFAULT 1,
                results_json TEXT NOT NULL,
                cached_at REAL NOT NULL,
                PRIMARY KEY (query, page)
            )
        """)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS favorites (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                gif_url TEXT NOT NULL UNIQUE,
                mp4_url TEXT,
                title TEXT,
                tag TEXT,
                added_at REAL NOT NULL
            )
        """)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS request_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                endpoint TEXT NOT NULL,
                query TEXT,
                api_key TEXT,
                ts REAL NOT NULL
            )
        """)
        conn.commit()
        conn.close()


def get_cached_search(query, page=1, max_age_seconds=3600):
    with _lock:
        conn = _connect()
        row = conn.execute(
            "SELECT results_json, cached_at FROM search_cache WHERE query = ? AND page = ?",
            (query.lower().strip(), page),
        ).fetchone()
        conn.close()
    if not row:
        return None
    if time.time() - row["cached_at"] > max_age_seconds:
        return None
    return json.loads(row["results_json"])


def set_cached_search(query, results, page=1):
    with _lock:
        conn = _connect()
        conn.execute(
            "INSERT OR REPLACE INTO search_cache (query, page, results_json, cached_at) VALUES (?, ?, ?, ?)",
            (query.lower().strip(), page, json.dumps(results), time.time()),
        )
        conn.commit()
        conn.close()


def add_favorite(gif_url, mp4_url=None, title="", tag=""):
    with _lock:
        conn = _connect()
        try:
            conn.execute(
                "INSERT OR IGNORE INTO favorites (gif_url, mp4_url, title, tag, added_at) VALUES (?, ?, ?, ?, ?)",
                (gif_url, mp4_url, title, tag, time.time()),
            )
            conn.commit()
        finally:
            conn.close()


def remove_favorite(favorite_id):
    with _lock:
        conn = _connect()
        conn.execute("DELETE FROM favorites WHERE id = ?", (favorite_id,))
        conn.commit()
        conn.close()


def list_favorites(tag=None):
    with _lock:
        conn = _connect()
        if tag:
            rows = conn.execute(
                "SELECT * FROM favorites WHERE tag = ? ORDER BY added_at DESC", (tag,)
            ).fetchall()
        else:
            rows = conn.execute(
                "SELECT * FROM favorites ORDER BY added_at DESC"
            ).fetchall()
        conn.close()
    return [dict(r) for r in rows]


def log_request(endpoint, query=None, api_key=None):
    with _lock:
        conn = _connect()
        conn.execute(
            "INSERT INTO request_log (endpoint, query, api_key, ts) VALUES (?, ?, ?, ?)",
            (endpoint, query, api_key, time.time()),
        )
        conn.commit()
        conn.close()


def clear_expired(max_age_seconds=86400):
    with _lock:
        conn = _connect()
        conn.execute(
            "DELETE FROM search_cache WHERE cached_at < ?",
            (time.time() - max_age_seconds,),
        )
        conn.commit()
        conn.close()