api/database_router.py

"""
database_router.py — Router Query Intelligente per Separazione Workload Supabase A/B/C/D

Architettura:
  A: Analytics/Cache/Read-Heavy (reporting, dashboard, cache distribuito)
  B: Sync/State/Transazioni (stato globale, sincronizzazione cluster — PRIMARY)
  C: Memory/RAG/Embeddings (backend memoria, vector search, skill index)
  D: Audit/Logging/Compliance (event log, audit trail, compliance records)

Routing Logic:
  1. Query di LETTURA (SELECT) → Preferisci A (read replica), fallback a B
  2. Query di SCRITTURA (INSERT/UPDATE) → Usa B (PRIMARY)
  3. Query su MEMORIA/RAG (skill_memory, embeddings, conversations) → Usa C
  4. Query su AUDIT/LOG (audit_events, compliance_log) → Usa D
  5. Query di SINCRONIZZAZIONE (cluster_state, global_state) → Usa B
"""

import asyncio
import os
import logging
import re as _re
from typing import Optional, Literal
from enum import Enum
from fastapi import APIRouter, HTTPException, Request, Depends
from .auth_guard import require_role, AuthRole
from pydantic import BaseModel

router = APIRouter(prefix="/api/database", tags=["database"])
_logger = logging.getLogger("database_router")

# ─── Enumerazione Nodi Supabase ───────────────────────────────────────────
class SupabaseNode(str, Enum):
    A = "A"  # Analytics/Cache
    B = "B"  # PRIMARY (Sync/State)
    C = "C"  # Memory/RAG
    D = "D"  # Audit/Logging


# ─── Configurazione Nodi ──────────────────────────────────────────────────
SUPABASE_CONFIG = {
    "A": {
        "url": os.getenv("SUPABASE_URL_A", ""),
        "key": os.getenv("SUPABASE_KEY_A", ""),
        "role": "Analytics/Cache (Read-Heavy)",
        "priority": 1,  # Preferito per letture
    },
    "B": {
        "url": os.getenv("SUPABASE_URL", ""),  # PRIMARY
        "key": os.getenv("SUPABASE_KEY", ""),
        "role": "Sync/State (PRIMARY)",
        "priority": 0,  # Fallback universale
    },
    "C": {
        "url": os.getenv("SUPABASE_URL_C", ""),
        "key": os.getenv("SUPABASE_KEY_C", ""),
        "role": "Memory/RAG/Embeddings",
        "priority": 2,
    },
    "D": {
        "url": os.getenv("SUPABASE_URL_D", ""),
        "key": os.getenv("SUPABASE_KEY_D", ""),
        "role": "Audit/Logging/Compliance",
        "priority": 3,
    },
}

# ─── Keyword Pericolosi (per read-only) ───────────────────────────────────
_DANGEROUS = frozenset(
    {"drop", "truncate", "delete", "update", "insert", "alter", "create", "grant", "revoke"}
)
_MAX_ROWS = 500


# ─── Modelli Pydantic ─────────────────────────────────────────────────────
class QueryRequest(BaseModel):
    sql: str
    params: list = []
    read_only: bool = True
    preferred_node: Optional[SupabaseNode] = None  # Override routing logic


class QueryResponse(BaseModel):
    ok: bool
    rows: list = []
    columns: list = []
    count: int = 0
    truncated: bool = False
    node_used: Optional[str] = None
    error: Optional[str] = None


# ─── Funzioni Utility ─────────────────────────────────────────────────────
def _is_dangerous(sql: str) -> Optional[str]:
    """Rilevamento keyword pericolose robusto contro CTE e multi-spazio."""
    s_norm = " ".join(sql.strip().lower().split())
    tokens = _re.split(r"[\s\(\),;]+", s_norm)
    for tok in tokens:
        if tok in _DANGEROUS:
            return tok
    for m in _re.finditer(r"\bas\s*\(\s*(\w+)", s_norm):
        first_word = m.group(1).lower()
        if first_word in _DANGEROUS:
            return first_word
    return None


def _detect_query_type(sql: str) -> Literal["SELECT", "INSERT", "UPDATE", "DELETE", "OTHER"]:
    """Rileva il tipo di query (SELECT, INSERT, UPDATE, DELETE, OTHER)."""
    s_norm = " ".join(sql.strip().upper().split())
    if s_norm.startswith("SELECT"):
        return "SELECT"
    elif s_norm.startswith("INSERT"):
        return "INSERT"
    elif s_norm.startswith("UPDATE"):
        return "UPDATE"
    elif s_norm.startswith("DELETE"):
        return "DELETE"
    return "OTHER"


def _detect_table_context(sql: str) -> Optional[str]:
    """Rileva il contesto della tabella per routing intelligente."""
    sql_lower = sql.lower()
    
    # Tabelle di memoria/RAG → Nodo C
    if any(t in sql_lower for t in ["skill_memory", "embeddings", "conversations", "rag_index", "vector_store"]):
        return "C"
    
    # Tabelle di audit/logging → Nodo D
    if any(t in sql_lower for t in ["audit_events", "audit_log", "compliance_log", "event_log", "activity_log"]):
        return "D"
    
    # Tabelle di stato globale → Nodo B
    if any(t in sql_lower for t in ["cluster_state", "global_state", "sync_state", "agent_state", "daemon_status"]):
        return "B"
    
    return None


def _choose_node(
    query_type: Literal["SELECT", "INSERT", "UPDATE", "DELETE", "OTHER"],
    table_context: Optional[str],
    preferred_node: Optional[SupabaseNode],
) -> SupabaseNode:
    """
    Logica di routing intelligente per scegliere il nodo Supabase.
    
    Priorità:
    1. preferred_node (override esplicito)
    2. table_context (rilevamento tabella)
    3. query_type (tipo di query)
    4. Fallback a B (PRIMARY)
    """
    # 1. Override esplicito
    if preferred_node:
        return preferred_node
    
    # 2. Routing per contesto tabella
    if table_context:
        return SupabaseNode(table_context)
    
    # 3. Routing per tipo query
    if query_type == "SELECT":
        # Preferisci A (read replica) se disponibile, altrimenti B
        if SUPABASE_CONFIG["A"]["url"]:
            return SupabaseNode.A
        return SupabaseNode.B
    elif query_type in ("INSERT", "UPDATE", "DELETE"):
        # Sempre su B (PRIMARY)
        return SupabaseNode.B
    
    # 4. Fallback a B (PRIMARY)
    return SupabaseNode.B


# ─── Endpoint Principale ──────────────────────────────────────────────────
@router.post("/query", response_model=QueryResponse)
async def database_query(
    req: QueryRequest,
    role: AuthRole = Depends(require_role(AuthRole.MACHINE)),
):
    """
    Endpoint query con routing intelligente tra nodi Supabase A/B/C/D.
    
    Parametri:
      - sql: query SQL
      - params: parametri query
      - read_only: blocca query pericolose (default: true)
      - preferred_node: forza un nodo specifico (opzionale)
    
    Ritorna:
      - ok: successo
      - rows: righe risultato
      - columns: nomi colonne
      - count: numero righe
      - truncated: se risultato è stato troncato
      - node_used: nodo Supabase utilizzato
    """
    
    # Rileva tipo query e contesto
    query_type = _detect_query_type(req.sql)
    table_context = _detect_table_context(req.sql)
    
    # Scegli nodo
    chosen_node = _choose_node(query_type, table_context, req.preferred_node)
    
    # Verifica configurazione nodo
    node_config = SUPABASE_CONFIG.get(chosen_node.value)
    if not node_config or not node_config["url"]:
        # Fallback a B se nodo non configurato
        if chosen_node != SupabaseNode.B:
            _logger.warning(
                f"Nodo {chosen_node.value} non configurato, fallback a B. "
                f"Configura SUPABASE_URL_{chosen_node.value} e SUPABASE_KEY_{chosen_node.value}."
            )
            chosen_node = SupabaseNode.B
            node_config = SUPABASE_CONFIG["B"]
    
    if not node_config["url"]:
        return QueryResponse(
            ok=False,
            error=f"Nodo {chosen_node.value} non configurato. Imposta SUPABASE_URL_{chosen_node.value}.",
            node_used=chosen_node.value,
        )
    
    # ── GAP-DB-QUERY-PUBLIC fix: forziamo read_only basandoci sul contenuto ──
    # Non ci fidiamo di req.read_only dal client per la sicurezza.
    _sql_upper = req.sql.upper()
    _is_write = any(kw in _sql_upper for kw in ["INSERT", "UPDATE", "DELETE", "DROP", "ALTER", "CREATE", "TRUNCATE"])
    
    # Se la query contiene keyword di scrittura, richiediamo esplicitamente permessi OPERATOR o superiore
    # In questo endpoint MACHINE (default) permettiamo solo SELECT.
    if _is_write and role < AuthRole.OPERATOR:
        raise HTTPException(403, "Permessi insufficienti per query di scrittura (richiesto OPERATOR)")
        
    # Verifica read-only
    if req.read_only or not _is_write:
        kw = _is_dangerous(req.sql)
        if kw:
            return QueryResponse(
                ok=False,
                error=f"Query bloccata (read-only): '{kw.upper()}' non consentito.",
                node_used=chosen_node.value,
            )
    
    # Esegui query
    try:
        result = await _execute_query(
            node_config["url"],
            node_config["key"],
            req.sql,
            req.params,
        )
        result["node_used"] = chosen_node.value
        return QueryResponse(**result)
    except Exception as e:
        _logger.error(f"Errore query su nodo {chosen_node.value}: {str(e)}")
        return QueryResponse(
            ok=False,
            error=str(e)[:400],
            node_used=chosen_node.value,
        )


# ─── Esecuzione Query (Supabase PostgreSQL) ───────────────────────────────
async def _execute_query(url: str, key: str, sql: str, params: list) -> dict:
    """Esegue query su Supabase PostgreSQL."""
    try:
        import psycopg2
        import psycopg2.extras
    except ImportError:
        return {
            "ok": False,
            "error": "psycopg2 non installato. Aggiungi 'psycopg2-binary' a requirements.txt.",
        }
    
    def _run():
        conn = psycopg2.connect(url)
        try:
            cur = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
            cur.execute(sql, params or None)
            try:
                rows = [dict(r) for r in cur.fetchmany(_MAX_ROWS)]
                cols = [d.name for d in (cur.description or [])]
            except psycopg2.ProgrammingError:
                rows, cols = [], []
            conn.commit()
        finally:
            conn.close()
        return rows, cols
    
    rows, cols = await asyncio.to_thread(_run)
    return {
        "ok": True,
        "rows": rows,
        "columns": cols,
        "count": len(rows),
        "truncated": len(rows) == _MAX_ROWS,
    }


# ─── Endpoint Debug (info nodi) ───────────────────────────────────────────
@router.get("/nodes/status")
async def nodes_status():
    """Ritorna lo stato di configurazione di tutti i nodi Supabase."""
    status = {}
    for node_id, config in SUPABASE_CONFIG.items():
        status[node_id] = {
            "role": config["role"],
            "configured": bool(config["url"]),
            "url_preview": config["url"][:30] + "..." if config["url"] else "NOT SET",
        }
    return {"nodes": status}