database.py

import os
import hashlib
import secrets
from typing import Optional, Dict, Any, List
from dotenv import load_dotenv
from supabase import create_client, Client

load_dotenv()

SUPABASE_URL = os.getenv("SUPABASE_URL")
SUPABASE_SERVICE_KEY = os.getenv("SUPABASE_SERVICE_KEY")

supabase: Optional[Client] = create_client(SUPABASE_URL, SUPABASE_SERVICE_KEY) if SUPABASE_URL and SUPABASE_SERVICE_KEY else None

def hash_key(api_key: str) -> str:
    """Calculeaza SHA-256 pentru API key."""
    return hashlib.sha256(api_key.strip().encode('utf-8')).hexdigest()

def verify_api_key_db(api_key: str) -> Optional[Dict[str, Any]]:
    """Valideaza cheia, verifica planul si incrementeaza cota de utilizare."""
    if not supabase:
        print("[DB Warning]: Supabase client not initialized. Bypassing auth.")
        return {"quota_exceeded": False, "plan": "free", "org_id": None, "project_id": None}

    if not api_key:
        return None

    hashed_key = hash_key(api_key)

    try:
        response = supabase.table("api_keys") \
            .select("id, org_id, is_active, plan, monthly_limit, current_usage") \
            .eq("key_hash", hashed_key) \
            .eq("is_active", True) \
            .limit(1) \
            .execute()

        if not response.data:
            return None

        key_record = response.data[0]
        current_usage = key_record.get("current_usage", 0)
        monthly_limit = key_record.get("monthly_limit", 500)
        plan = key_record.get("plan", "free")

        # Verificare cota
        if current_usage >= monthly_limit:
            return {
                "quota_exceeded": True,
                "usage": current_usage,
                "limit": monthly_limit,
                "plan": plan
            }

        # Incrementare sigura
        supabase.table("api_keys") \
            .update({"current_usage": current_usage + 1}) \
            .eq("id", key_record["id"]) \
            .execute()

        # Obtinere proiect asociat
        project_id = None
        proj_res = supabase.table("projects") \
            .select("id") \
            .eq("org_id", key_record["org_id"]) \
            .limit(1) \
            .execute()

        if proj_res.data:
            project_id = proj_res.data[0]["id"]

        return {
            "quota_exceeded": False,
            "org_id": key_record["org_id"],
            "project_id": project_id,
            "plan": plan
        }

    except Exception as e:
        print(f"[DB Auth Error]: {e}")
        return None

def save_evaluation_to_db(project_id: str, report_data: Dict[str, Any]) -> Optional[str]:
    """Salveaza asincron raportul complet si incidentele de securitate in Supabase."""
    if not supabase or not project_id or not report_data:
        return None

    try:
        summary = report_data.get("executive_summary", {})
        results = report_data.get("results", [])

        eval_run_payload = {
            "project_id": project_id,
            "health_rating": str(summary.get("health_rating", "F")),
            "success_rate": float(summary.get("success_rate_percentage", 0.0)),
            "mean_drift": float(summary.get("mean_drift", 0.0)) if "mean_drift" in summary else 0.0,
            "total_tokens": sum(r.get("total_tokens", 0) for r in results),
            "estimated_cost_usd": sum(r.get("estimated_cost_usd", 0.0) for r in results),
            "narrative_report": report_data.get("narrative_report", "")
        }

        run_res = supabase.table("eval_runs").insert(eval_run_payload).execute()
        if not run_res.data:
            return None

        eval_run_id = run_res.data[0]["id"]

        for session in results:
            session_payload = {
                "eval_run_id": eval_run_id,
                "session_id": str(session.get("session_id", "unknown")),
                "status": session.get("status", "STABLE"),
                "max_drift": float(session.get("max_drift_detected", 0.0)),
                "mean_drift": float(session.get("mean_drift", 0.0)),
                "enriched_graph": session.get("enriched_graph", {})
            }

            session_res = supabase.table("session_trajectories").insert(session_payload).execute()
            if session_res.data:
                session_db_id = session_res.data[0]["id"]
                failures = session.get("failures", [])
                if failures:
                    failure_payloads = [
                        {
                            "session_trajectory_id": session_db_id,
                            "failure_type": f.get("failure_type", "UNKNOWN"),
                            "from_node": str(f.get("from_node", "")),
                            "to_node": str(f.get("to_node", "")),
                            "reason": str(f.get("reason", "")),
                            "details": str(f.get("details", "")) if f.get("details") else None
                        } for f in failures
                    ]
                    supabase.table("failure_incidents").insert(failure_payloads).execute()

        print(f"[Supabase Sync]: Eval Run [{eval_run_id}] salvat cu succes.")
        return eval_run_id

    except Exception as e:
        print(f"[Supabase Sync Error]: {e}")
        return None