import sqlite3
from datetime import datetime, timedelta, timezone
from typing import Optional

from storage.metrics_db import DB_PATH

_TABLE = "news_cache"


def _init() -> None:
    DB_PATH.parent.mkdir(parents=True, exist_ok=True)
    with sqlite3.connect(DB_PATH) as conn:
        conn.execute(f"""
            CREATE TABLE IF NOT EXISTS {_TABLE} (
                ticker     TEXT NOT NULL,
                query      TEXT NOT NULL,
                days       INTEGER NOT NULL,
                payload    TEXT NOT NULL,
                fetched_at TEXT NOT NULL,
                PRIMARY KEY (ticker, query, days)
            )
        """)


def get(ticker: str, query: str, days: int, ttl_seconds: int = 3600) -> Optional[str]:
    if not DB_PATH.exists():
        return None
    try:
        with sqlite3.connect(DB_PATH) as conn:
            row = conn.execute(
                f"SELECT payload, fetched_at FROM {_TABLE} WHERE ticker=? AND query=? AND days=?",
                (ticker, query, days),
            ).fetchone()
        if row is None:
            return None
        payload_str, fetched_at_str = row
        fetched_at = datetime.fromisoformat(fetched_at_str)
        if datetime.now(timezone.utc) - fetched_at > timedelta(seconds=ttl_seconds):
            return None
        return payload_str
    except Exception:
        return None


def put(ticker: str, query: str, days: int, payload: str) -> None:
    _init()
    try:
        with sqlite3.connect(DB_PATH) as conn:
            conn.execute(
                f"""INSERT INTO {_TABLE} (ticker, query, days, payload, fetched_at)
                    VALUES (?, ?, ?, ?, ?)
                    ON CONFLICT(ticker, query, days) DO UPDATE SET
                        payload=excluded.payload,
                        fetched_at=excluded.fetched_at""",
                (ticker, query, days, payload, datetime.now(timezone.utc).isoformat()),
            )
    except Exception:
        pass