"""Two-tier cache for the revenue-forecast endpoint.

L1: in-process dict (per uvicorn worker), 60 s TTL — sub-ms.
L2: fpna_config.revenue_forecast_cache table, configurable TTL (default 30 min)
    — survives reloads and is shared across workers/instances.

Why both layers?
  - Cold hit → L1 miss → L2 hit (~3 ms) → return.
  - Hot hit → L1 hit (~5 us) → return. Avoids hammering Postgres in tight polls.

Usage:
    payload = await cache_get(db, key)
    if payload is None:
        payload = expensive_build(...)
        await cache_set(db, key, payload, ttl_seconds=1800)
"""
from __future__ import annotations

import json
import time
from datetime import datetime, timedelta, timezone
from typing import Any, Optional

from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession


# ── L1: in-process dict ──────────────────────────────────────────────────────
# key -> (expires_monotonic, payload)
_L1: dict[str, tuple[float, Any]] = {}
_L1_TTL_SECONDS = 60.0
_L1_MAX_ENTRIES = 256  # simple cap to avoid unbounded growth


def _l1_get(key: str) -> Optional[Any]:
    item = _L1.get(key)
    if not item:
        return None
    expires, payload = item
    if time.monotonic() >= expires:
        _L1.pop(key, None)
        return None
    return payload


def _l1_set(key: str, payload: Any) -> None:
    if len(_L1) >= _L1_MAX_ENTRIES:
        # Cheap eviction: drop the oldest by sort. Fine at this scale.
        oldest = min(_L1, key=lambda k: _L1[k][0])
        _L1.pop(oldest, None)
    _L1[key] = (time.monotonic() + _L1_TTL_SECONDS, payload)


# ── L2: Postgres-backed cache ────────────────────────────────────────────────
async def cache_get(db: AsyncSession, key: str) -> Optional[Any]:
    """Return cached payload (parsed JSON) for ``key``, or None on miss/expired."""
    hit = _l1_get(key)
    if hit is not None:
        return hit

    row = (await db.execute(
        text("""
            SELECT payload
            FROM fpna_config.revenue_forecast_cache
            WHERE cache_key = :k AND expires_at > now()
        """),
        {"k": key},
    )).first()
    if not row:
        return None
    payload = row[0]
    # JSONB columns come back as Python objects already (asyncpg), but in
    # some configurations they arrive as a string. Handle both.
    if isinstance(payload, (str, bytes, bytearray)):
        payload = json.loads(payload)
    _l1_set(key, payload)
    return payload


async def cache_set(
    db: AsyncSession,
    key: str,
    payload: Any,
    ttl_seconds: int = 1800,
) -> None:
    """Upsert ``payload`` into the cache with the given TTL."""
    expires_at = datetime.now(timezone.utc) + timedelta(seconds=ttl_seconds)
    # JSONB column; we pass a JSON string and cast.
    await db.execute(
        text("""
            INSERT INTO fpna_config.revenue_forecast_cache (cache_key, payload, expires_at)
            VALUES (:k, CAST(:p AS JSONB), :e)
            ON CONFLICT (cache_key) DO UPDATE
              SET payload = EXCLUDED.payload,
                  expires_at = EXCLUDED.expires_at,
                  created_at = now()
        """),
        {"k": key, "p": json.dumps(payload, default=str), "e": expires_at},
    )
    await db.commit()
    _l1_set(key, payload)


async def cache_invalidate(db: AsyncSession, key_prefix: str) -> int:
    """Delete L2 rows whose key starts with ``key_prefix``. Returns row count.

    Call this after data changes (e.g. adjustment save, GL upload) so the next
    request rebuilds. Also clears matching L1 entries.
    """
    result = await db.execute(
        text("DELETE FROM fpna_config.revenue_forecast_cache WHERE cache_key LIKE :pat"),
        {"pat": key_prefix + "%"},
    )
    await db.commit()
    # Also purge L1
    for k in list(_L1.keys()):
        if k.startswith(key_prefix):
            _L1.pop(k, None)
    return result.rowcount or 0


async def cache_sweep_expired(db: AsyncSession) -> int:
    """Delete expired rows. Cheap because of ix_rf_cache_expires."""
    result = await db.execute(
        text("DELETE FROM fpna_config.revenue_forecast_cache WHERE expires_at <= now()")
    )
    await db.commit()
    return result.rowcount or 0


def build_cache_key(
    *,
    company_id: str,
    year: int,
    currency: str,
    quarter: str,
    currency_filter: str,
    forecast_type: str,
    view_type: str,
    rolling_start_month: Optional[str] = None,
    customer_name: Optional[str] = None,
    forecast_year: Optional[int] = None,
    page: int = 1,
    page_size: int = 0,
) -> str:
    """Build a deterministic cache key that includes every filter that
    affects the response. Any new filter added to the endpoint MUST be
    appended here, or it will silently return stale data."""
    fy = forecast_year or year
    return (
        f"rf:v1:{company_id}:{year}:{currency}:{quarter}:{currency_filter}"
        f":{forecast_type}:{view_type}:{rolling_start_month or '-'}"
        f":{(customer_name or '-').lower()}:{fy}:{page}:{page_size}"
    )
