"""
DB-backed secrets for the pipeline. Reads API keys/tokens from the `app_secret`
table (encrypted at rest) instead of the .env file, and reports auth/expiry
errors back onto the row so the admin panel can surface them.

Bootstrap: MYSQL_URI and SECRETS_ENC_KEY still come from .env (you can't store
the DB connection string inside the DB it connects to).

Migration-safe: if a key has no stored value yet (or the DB/decrypt fails),
get_secret() falls back to os.getenv(key) so nothing breaks mid-migration.
"""

import os
import threading

from sqlalchemy import create_engine, text
from dotenv import load_dotenv

import secret_crypto

load_dotenv()

_engine = create_engine(os.getenv("MYSQL_URI"), pool_pre_ping=True)
_ENC_KEY = os.getenv("SECRETS_ENC_KEY")

_cache = {}
_marked_ok = set()      # report each key's OK/error to the DB at most once per run
_reported_error = set()
_lock = threading.Lock()


def get_secret(key, use_cache=True):
    """Return the secret value for `key` from the DB (decrypted), or fall back
    to the same-named .env variable if it isn't stored/decryptable yet."""
    if use_cache:
        with _lock:
            if key in _cache:
                return _cache[key]

    value = None
    try:
        with _engine.connect() as conn:
            row = conn.execute(
                text("SELECT secret_value FROM app_secret WHERE secret_key = :k"),
                {"k": key},
            ).fetchone()
        if row and row[0] and _ENC_KEY:
            value = secret_crypto.decrypt(row[0], _ENC_KEY)
    except Exception as e:
        print(f"[SECRETS] DB read failed for {key}: {e}")

    if not value:                      # migration / empty / decrypt failure
        value = os.getenv(key)

    with _lock:
        _cache[key] = value
    return value


def report_secret_error(key, message, status="error"):
    """Record an auth/expiry failure against a key (shown in the admin panel).
    Fires at most once per key per process run to avoid hammering the DB."""
    with _lock:
        if key in _reported_error:
            return
        _reported_error.add(key)
        _marked_ok.discard(key)
    try:
        with _engine.begin() as conn:
            conn.execute(text("""
                UPDATE app_secret
                SET status = :s, last_error = :m, last_error_at = NOW()
                WHERE secret_key = :k
            """), {"s": status, "m": str(message)[:2000], "k": key})
        print(f"[SECRETS] Recorded {status} for {key}: {str(message)[:120]}")
    except Exception as e:
        print(f"[SECRETS] Could not record error for {key}: {e}")


def mark_secret_ok(key):
    """Mark a key healthy after a successful call. Once per key per run."""
    with _lock:
        if key in _marked_ok or key in _reported_error:
            return
        _marked_ok.add(key)
    try:
        with _engine.begin() as conn:
            conn.execute(text("""
                UPDATE app_secret
                SET status = 'active', last_ok_at = NOW(),
                    last_error = NULL, last_error_at = NULL
                WHERE secret_key = :k
            """), {"k": key})
    except Exception as e:
        print(f"[SECRETS] Could not mark ok for {key}: {e}")