"""
Backend connectivity for the memory system.

Provides:
  - Sentence-transformer embedding helpers (_load_embedder, _embed)
  - Supabase connection management (_get_supabase, _using_supabase)
  - SQLite local fallback connector (_get_sqlite)
"""
import os
import sqlite3
import threading
from functools import lru_cache

# ── Embedding helpers ──────────────────────────────────────────────────────────

@lru_cache(maxsize=1)
def _load_embedder():
    """
    Load sentence-transformers model once, cache it.
    all-MiniLM-L6-v2: 80MB, fast, 384-dim vectors — perfect for this.
    Returns None if sentence-transformers not installed.
    """
    try:
        from sentence_transformers import SentenceTransformer
        return SentenceTransformer("all-MiniLM-L6-v2")
    except ImportError:
        return None


def _embed(text: str) -> list[float] | None:
    """
    Convert text to a 384-dimensional vector.
    Returns None if embedding model not available — callers handle this.
    """
    model = _load_embedder()
    if model is None:
        return None
    return model.encode(text[:500], normalize_embeddings=True).tolist()


# ── Supabase connection ────────────────────────────────────────────────────────

_supabase_client = None
_supabase_lock   = threading.Lock()


def _get_supabase():
    """
    Return a Supabase client, or None if not configured.
    Uses a module-level singleton — creates the connection once.
    Thread-safe via lock.
    """
    global _supabase_client
    with _supabase_lock:
        if _supabase_client is not None:
            return _supabase_client

        url = os.getenv("SUPABASE_URL", "")
        key = os.getenv("SUPABASE_KEY", "")

        if not url or not key:
            return None   # not configured — caller falls back to local

        try:
            from supabase import create_client
            _supabase_client = create_client(url, key)
            print("[memory] Connected to Supabase")
            return _supabase_client
        except Exception as e:
            print(f"[memory] Supabase connection failed: {e} — using local fallback")
            return None


def _using_supabase() -> bool:
    return _get_supabase() is not None


# ── Local SQLite fallback paths ────────────────────────────────────────────────

_DATA_DIR    = os.path.join(os.path.dirname(__file__), "..", "data")
_SQLITE_PATH = os.path.join(_DATA_DIR, "memory.db")


def _get_sqlite():
    """Return a SQLite connection, creating the DB and tables if needed."""
    os.makedirs(_DATA_DIR, exist_ok=True)
    conn = sqlite3.connect(_SQLITE_PATH, check_same_thread=False)
    conn.execute("""
        CREATE TABLE IF NOT EXISTS episodes (
            id             INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id        TEXT,
            ticker         TEXT,
            recommendation TEXT,
            confidence     INTEGER,
            price_at_time  REAL,
            report         TEXT,
            outcome        TEXT,
            created_at     TEXT
        )
    """)
    conn.execute("""
        CREATE TABLE IF NOT EXISTS stock_analyses (
            id             INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id        TEXT,
            ticker         TEXT,
            recommendation TEXT,
            confidence     INTEGER,
            report         TEXT,
            created_at     TEXT
        )
    """)
    conn.execute("""
        CREATE TABLE IF NOT EXISTS user_preferences (
            id         INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id    TEXT,
            preference TEXT,
            created_at TEXT
        )
    """)
    conn.commit()
    return conn