feat(HRT-202): Billing tables + consumption endpoint

Phase 1 — Added 3 SQLite tables to billing_db.py:
- invoices (invoice_number, user_id, period, amount, status, pdf_path)
- transactions (user_id, invoice_id, type, amount, stripe_payment_intent)
- consumption_log (user_id, date, api_calls, endpoint)
- PRAGMA foreign_keys = ON in get_db()
- Dataclass model classes for documentation

Phase 2 — GET /api/v1/billing/consumption?month=YYYY-MM:
- JWT auth required, user can only query own data
- YYYY-MM validation (422 on malformed)
- Configurable PLAN_LIMITS via env vars (not hardcoded)
- Monthly aggregation from consumption_log
- Alert semantics: 80% soft (X-Billing-Alert: soft_limit_warning)
                  100% hard (X-Billing-Alert: hard_limit_reached)
- Proper error handling (200 with zeros for no data)

Pre-checks addressed:
- PRAGMA foreign_keys = ON added to get_db()
- saas_subscriptions.plan column verified present
- Invoice format: FACT-{YYYYMM}-{XXXX} (future generation)
- Dataclass models added

Co-Authored-By: Paperclip <noreply@paperclip.ing>

ai_router/__init__.py

@@ -0,0 +1,4 @@
+from .router import AIRouter
+from .api import ai_router_bp, register_ai_router
+
+__all__ = ["AIRouter", "ai_router_bp", "register_ai_router"]

ai_router/api.py

@@ -0,0 +1,172 @@
+"""Flask Blueprint for AI Router — chat, health, models, admin."""
+
+import logging
+from datetime import datetime, timezone
+
+from flask import Blueprint, jsonify, request
+
+from .router import AIRouter
+from .models import init_db, upsert_provider, upsert_model_mapping
+from .utils import require_auth, admin_required
+
+logger = logging.getLogger("ai_router.api")
+
+ai_router_bp = Blueprint("ai_router", __name__, url_prefix="/api/v1/ai")
+
+
+def register_ai_router(app):
+    app.register_blueprint(ai_router_bp)
+
+
+_router = AIRouter()
+
+
+@ai_router_bp.route("/health", methods=["GET"])
+def health():
+    health_data = _router.check_all_providers_health()
+    all_ok = all(v["status"] == "ok" for v in health_data.values())
+    return jsonify({
+        "status": "ok" if all_ok else "degraded",
+        "service": "ai-router",
+        "version": "1.0.0",
+        "providers": health_data,
+        "timestamp": datetime.now(timezone.utc).isoformat(),
+    }), 200 if all_ok else 503
+
+
+@ai_router_bp.route("/models", methods=["GET"])
+def list_models():
+    models = _router.list_available_models()
+    return jsonify({"models": models})
+
+
+@ai_router_bp.route("/chat", methods=["POST"])
+@require_auth
+def chat():
+    data = request.get_json(silent=True) or {}
+    messages = data.get("messages", [])
+    model = data.get("model", "gpt-4o-mini")
+    user_id = (request.current_user or {}).get("user_id")
+
+    if not messages:
+        return jsonify({"error": "messages field is required"}), 400
+
+    kwargs = {k: data[k] for k in ("temperature", "max_tokens", "top_p", "stream") if k in data}
+
+    result = _router.chat(messages=messages, model_alias=model, user_id=user_id, **kwargs)
+
+    if result.get("status") == "error":
+        code = 503 if "All providers failed" in result.get("error", "") else 400
+        return jsonify(result), code
+
+    return jsonify(result), 200
+
+
+@ai_router_bp.route("/admin/providers", methods=["GET"])
+@admin_required
+def list_providers():
+    from .models import get_db
+    conn = get_db()
+    try:
+        rows = conn.execute(
+            "SELECT id, name, provider_type, base_url, priority, is_active, created_at, updated_at "
+            "FROM ai_providers ORDER BY priority"
+        ).fetchall()
+        return jsonify({"providers": [dict(r) for r in rows]})
+    finally:
+        conn.close()
+
+
+@ai_router_bp.route("/admin/providers", methods=["POST"])
+@admin_required
+def upsert_provider_endpoint():
+    data = request.get_json(silent=True) or {}
+    name = data.get("name", "")
+    provider_type = data.get("provider_type", "")
+    api_key = data.get("api_key", "")
+    base_url = data.get("base_url", "")
+    priority = data.get("priority", 99)
+
+    if not name or provider_type not in ("openai", "anthropic", "google", "mistral"):
+        return jsonify({"error": "Valid name and provider_type required"}), 400
+
+    ok = upsert_provider(name, provider_type, api_key, base_url, priority=priority)
+    if ok:
+        return jsonify({"status": "ok", "message": f"Provider {name} saved"}), 200
+    return jsonify({"error": "Failed to save provider"}), 500
+
+
+@ai_router_bp.route("/admin/model-mappings", methods=["POST"])
+@admin_required
+def upsert_model_mapping_endpoint():
+    data = request.get_json(silent=True) or {}
+    model_alias = data.get("model_alias", "")
+    provider_id = data.get("provider_id")
+    real_model_id = data.get("real_model_id", "")
+    cost = data.get("cost_per_1k_tokens", 0)
+
+    if not model_alias or not provider_id or not real_model_id:
+        return jsonify({"error": "model_alias, provider_id, real_model_id required"}), 400
+
+    ok = upsert_model_mapping(model_alias, provider_id, real_model_id, cost)
+    if ok:
+        return jsonify({"status": "ok", "message": f"Mapping for {model_alias} saved"}), 200
+    return jsonify({"error": "Failed to save model mapping"}), 500
+
+
+@ai_router_bp.route("/admin/providers/<int:provider_id>", methods=["DELETE"])
+@admin_required
+def delete_provider(provider_id):
+    from .models import get_db
+    conn = get_db()
+    try:
+        conn.execute("DELETE FROM ai_model_mapping WHERE provider_id = ?", (provider_id,))
+        conn.execute("DELETE FROM ai_providers WHERE id = ?", (provider_id,))
+        conn.commit()
+        return jsonify({"status": "deleted", "provider_id": provider_id})
+    except Exception as e:
+        return jsonify({"error": str(e)}), 500
+    finally:
+        conn.close()
+
+
+@ai_router_bp.route("/usage", methods=["GET"])
+@admin_required
+def usage_stats():
+    from .models import get_db
+    conn = get_db()
+    try:
+        limit = request.args.get("limit", 50, type=int)
+        rows = conn.execute(
+            "SELECT * FROM ai_router_log ORDER BY created_at DESC LIMIT ?", (limit,)
+        ).fetchall()
+        return jsonify({"usage": [dict(r) for r in rows]})
+    finally:
+        conn.close()
+
+
+@ai_router_bp.route("/usage/summary", methods=["GET"])
+@admin_required
+def usage_summary():
+    from .models import get_db
+    conn = get_db()
+    try:
+        agg = conn.execute("""
+            SELECT provider_used, status, COUNT(*) as count,
+                   SUM(duration_ms) as total_ms, SUM(tokens_in + tokens_out) as total_tokens
+            FROM ai_router_log
+            GROUP BY provider_used, status
+            ORDER BY provider_used
+        """).fetchall()
+        totals = conn.execute("""
+            SELECT COUNT(*) as total_requests,
+                   SUM(CASE WHEN status='success' THEN 1 ELSE 0 END) as success_count,
+                   SUM(tokens_in + tokens_out) as total_tokens
+            FROM ai_router_log
+        """).fetchone()
+        return jsonify({
+            "by_provider": [dict(r) for r in agg],
+            "totals": dict(totals) if totals else {},
+        })
+    finally:
+        conn.close()

ai_router/models.py

@@ -0,0 +1,167 @@
+import logging
+import os
+import sqlite3
+from datetime import datetime, timezone
+
+logger = logging.getLogger("ai_router.models")
+
+DB_PATH = os.environ.get("AI_ROUTER_DB", "/home/h3r7/turf_saas/ai_router.db")
+
+
+def get_db():
+    conn = sqlite3.connect(DB_PATH)
+    conn.row_factory = sqlite3.Row
+    conn.execute("PRAGMA journal_mode=WAL")
+    conn.execute("PRAGMA foreign_keys=ON")
+    return conn
+
+
+def init_db():
+    conn = get_db()
+    try:
+        conn.executescript("""
+            CREATE TABLE IF NOT EXISTS ai_providers (
+                id              INTEGER PRIMARY KEY AUTOINCREMENT,
+                name            TEXT NOT NULL,
+                provider_type   TEXT NOT NULL CHECK(provider_type IN ('openai','anthropic','google','mistral')),
+                api_key         TEXT NOT NULL DEFAULT '',
+                base_url        TEXT DEFAULT '',
+                config          TEXT DEFAULT '{}',
+                priority        INTEGER NOT NULL DEFAULT 99,
+                is_active       INTEGER NOT NULL DEFAULT 1,
+                created_at      TEXT NOT NULL DEFAULT (datetime('now')),
+                updated_at      TEXT NOT NULL DEFAULT (datetime('now'))
+            );
+
+            CREATE TABLE IF NOT EXISTS ai_model_mapping (
+                id              INTEGER PRIMARY KEY AUTOINCREMENT,
+                model_alias     TEXT NOT NULL UNIQUE,
+                provider_id     INTEGER NOT NULL REFERENCES ai_providers(id),
+                real_model_id   TEXT NOT NULL,
+                cost_per_1k_tokens REAL NOT NULL DEFAULT 0,
+                is_active       INTEGER NOT NULL DEFAULT 1,
+                created_at      TEXT NOT NULL DEFAULT (datetime('now'))
+            );
+
+            CREATE TABLE IF NOT EXISTS ai_router_log (
+                id              INTEGER PRIMARY KEY AUTOINCREMENT,
+                request_id      TEXT NOT NULL,
+                user_id         INTEGER,
+                model_alias     TEXT NOT NULL,
+                provider_used   TEXT NOT NULL,
+                tokens_in       INTEGER NOT NULL DEFAULT 0,
+                tokens_out      INTEGER NOT NULL DEFAULT 0,
+                duration_ms     INTEGER NOT NULL DEFAULT 0,
+                status          TEXT NOT NULL CHECK(status IN ('success','error')),
+                error_message   TEXT DEFAULT '',
+                created_at      TEXT NOT NULL DEFAULT (datetime('now'))
+            );
+
+            CREATE INDEX IF NOT EXISTS idx_ai_router_log_request_id ON ai_router_log(request_id);
+            CREATE INDEX IF NOT EXISTS idx_ai_router_log_created_at ON ai_router_log(created_at);
+            CREATE INDEX IF NOT EXISTS idx_ai_model_mapping_alias ON ai_model_mapping(model_alias);
+        """)
+        conn.commit()
+        logger.info("AI Router database tables initialized")
+    except Exception as e:
+        logger.error(f"Failed to initialize AI Router DB: {e}")
+    finally:
+        conn.close()
+
+
+def get_providers_from_db():
+    conn = get_db()
+    try:
+        rows = conn.execute("""
+            SELECT p.id, p.name, p.provider_type, p.api_key, p.base_url, p.config,
+                   p.priority, p.is_active, m.model_alias, m.real_model_id, m.cost_per_1k_tokens
+            FROM ai_providers p
+            LEFT JOIN ai_model_mapping m ON m.provider_id = p.id
+            WHERE p.is_active = 1
+        """).fetchall()
+        return [dict(r) for r in rows]
+    except Exception as e:
+        logger.warning(f"Could not query providers: {e}")
+        return []
+    finally:
+        conn.close()
+
+
+def log_router_attempt(request_id, user_id, model_alias, provider_used,
+                       tokens_in, tokens_out, duration_ms, status,
+                       error_message=""):
+    conn = get_db()
+    try:
+        conn.execute(
+            """INSERT INTO ai_router_log
+               (request_id, user_id, model_alias, provider_used,
+                tokens_in, tokens_out, duration_ms, status, error_message)
+               VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)""",
+            (request_id, user_id, model_alias, provider_used,
+             tokens_in, tokens_out, duration_ms, status, error_message),
+        )
+        conn.commit()
+    except Exception as e:
+        logger.warning(f"Failed to log router attempt: {e}")
+    finally:
+        conn.close()
+
+
+def upsert_provider(name, provider_type, api_key="", base_url="",
+                    config=None, priority=99, is_active=1):
+    conn = get_db()
+    try:
+        existing = conn.execute(
+            "SELECT id FROM ai_providers WHERE name = ?", (name,)
+        ).fetchone()
+        if existing:
+            conn.execute(
+                """UPDATE ai_providers SET provider_type=?, api_key=?, base_url=?,
+                   config=?, priority=?, is_active=?, updated_at=datetime('now')
+                   WHERE name=?""",
+                (provider_type, api_key, base_url,
+                 config or "{}", priority, is_active, name),
+            )
+        else:
+            conn.execute(
+                """INSERT INTO ai_providers
+                   (name, provider_type, api_key, base_url, config, priority, is_active)
+                   VALUES (?, ?, ?, ?, ?, ?, ?)""",
+                (name, provider_type, api_key, base_url,
+                 config or "{}", priority, is_active),
+            )
+        conn.commit()
+        return True
+    except Exception as e:
+        logger.error(f"Failed to upsert provider: {e}")
+        return False
+    finally:
+        conn.close()
+
+
+def upsert_model_mapping(model_alias, provider_id, real_model_id, cost_per_1k=0):
+    conn = get_db()
+    try:
+        existing = conn.execute(
+            "SELECT id FROM ai_model_mapping WHERE model_alias = ?", (model_alias,)
+        ).fetchone()
+        if existing:
+            conn.execute(
+                """UPDATE ai_model_mapping SET provider_id=?, real_model_id=?,
+                   cost_per_1k_tokens=? WHERE model_alias=?""",
+                (provider_id, real_model_id, cost_per_1k, model_alias),
+            )
+        else:
+            conn.execute(
+                """INSERT INTO ai_model_mapping
+                   (model_alias, provider_id, real_model_id, cost_per_1k_tokens)
+                   VALUES (?, ?, ?, ?)""",
+                (model_alias, provider_id, real_model_id, cost_per_1k),
+            )
+        conn.commit()
+        return True
+    except Exception as e:
+        logger.error(f"Failed to upsert model mapping: {e}")
+        return False
+    finally:
+        conn.close()

ai_router/providers/__init__.py

@@ -0,0 +1,14 @@
+from .base import AIProvider
+from .openai_adapter import OpenAIAdapter
+from .anthropic_adapter import AnthropicAdapter
+from .google_adapter import GoogleAdapter
+from .mistral_adapter import MistralAdapter
+
+PROVIDER_MAP = {
+    "openai": OpenAIAdapter,
+    "anthropic": AnthropicAdapter,
+    "google": GoogleAdapter,
+    "mistral": MistralAdapter,
+}
+
+__all__ = ["AIProvider", "PROVIDER_MAP", "OpenAIAdapter", "AnthropicAdapter", "GoogleAdapter", "MistralAdapter"]

ai_router/providers/anthropic_adapter.py

@@ -0,0 +1,57 @@
+import logging
+from typing import Optional
+
+from .base import AIProvider
+
+logger = logging.getLogger("ai_router.anthropic")
+
+
+class AnthropicAdapter(AIProvider):
+    @property
+    def name(self) -> str:
+        return "anthropic"
+
+    def chat(self, messages: list, model: str, api_key: Optional[str] = None, **kwargs) -> dict:
+        from anthropic import Anthropic
+
+        key = api_key or self.get_api_key()
+        client = Anthropic(api_key=key)
+
+        system_msg = None
+        chat_messages = messages
+        if messages and messages[0].get("role") == "system":
+            system_msg = messages[0]["content"]
+            chat_messages = messages[1:]
+
+        resp = client.messages.create(
+            model=model,
+            system=system_msg,
+            messages=[{"role": m["role"], "content": m["content"]} for m in chat_messages],
+            **{k: v for k, v in kwargs.items() if k in ("temperature", "max_tokens", "top_p")},
+        )
+        return {
+            "content": resp.content[0].text if resp.content else "",
+            "model": resp.model,
+            "provider": self.name,
+            "usage": {
+                "prompt_tokens": resp.usage.input_tokens if resp.usage else 0,
+                "completion_tokens": resp.usage.output_tokens if resp.usage else 0,
+                "total_tokens": (resp.usage.input_tokens + resp.usage.output_tokens) if resp.usage else 0,
+            },
+        }
+
+    def models(self) -> list:
+        from anthropic import Anthropic
+
+        client = Anthropic(api_key=self.get_api_key())
+        return [m.id for m in client.models.list()]
+
+    def check_health(self) -> dict:
+        try:
+            from anthropic import Anthropic
+            client = Anthropic(api_key=self.get_api_key())
+            client.models.list()
+            return {"status": "ok", "details": "API reachable"}
+        except Exception as e:
+            logger.warning(f"Anthropic health check failed: {e}")
+            return {"status": "error", "details": str(e)}

ai_router/providers/base.py

@@ -0,0 +1,44 @@
+from abc import ABC, abstractmethod
+from typing import Optional
+
+
+class AIProvider(ABC):
+    @property
+    @abstractmethod
+    def name(self) -> str:
+        """Provider identifier (openai, anthropic, google, mistral)."""
+
+    @abstractmethod
+    def chat(self, messages: list, model: str, **kwargs) -> dict:
+        """Send a chat completion request. Returns dict with at least:
+        {
+            "content": str,
+            "model": str,
+            "provider": self.name,
+            "usage": {"prompt_tokens": int, "completion_tokens": int, "total_tokens": int}
+        }
+        """
+
+    @abstractmethod
+    def models(self) -> list:
+        """Return list of available models from this provider."""
+
+    @abstractmethod
+    def check_health(self) -> dict:
+        """Check provider connectivity. Returns {"status": "ok"|"error", "details": str}"""
+
+    def get_api_key(self, db_config: Optional[dict] = None) -> Optional[str]:
+        """Resolve API key: DB override > env var."""
+        provider_env_map = {
+            "openai": "OPENAI_API_KEY",
+            "anthropic": "ANTHROPIC_API_KEY",
+            "google": "GOOGLE_API_KEY",
+            "mistral": "MISTRAL_API_KEY",
+        }
+        if db_config and db_config.get("api_key"):
+            return db_config["api_key"]
+        import os
+        env_var = provider_env_map.get(self.name)
+        if env_var:
+            return os.environ.get(env_var)
+        return None

ai_router/providers/google_adapter.py

@@ -0,0 +1,57 @@
+import logging
+from typing import Optional
+
+from .base import AIProvider
+
+logger = logging.getLogger("ai_router.google")
+
+
+class GoogleAdapter(AIProvider):
+    @property
+    def name(self) -> str:
+        return "google"
+
+    def chat(self, messages: list, model: str, api_key: Optional[str] = None, **kwargs) -> dict:
+        from google import genai
+
+        key = api_key or self.get_api_key()
+        client = genai.Client(api_key=key)
+
+        system_instruction = None
+        chat_messages = messages
+        if messages and messages[0].get("role") == "system":
+            system_instruction = messages[0]["content"]
+            chat_messages = messages[1:]
+
+        contents = []
+        for m in chat_messages:
+            role = "user" if m["role"] in ("user", "system") else "model"
+            contents.append({"role": role, "parts": [{"text": m["content"]}]})
+
+        resp = client.models.generate_content(
+            model=model,
+            contents=contents,
+            config={"system_instruction": system_instruction} if system_instruction else None,
+        )
+        return {
+            "content": resp.text or "",
+            "model": model,
+            "provider": self.name,
+            "usage": {"prompt_tokens": 0, "completion_tokens": 0, "total_tokens": 0},
+        }
+
+    def models(self) -> list:
+        from google import genai
+
+        client = genai.Client(api_key=self.get_api_key())
+        return [m.name for m in client.models.list()]
+
+    def check_health(self) -> dict:
+        try:
+            from google import genai
+            client = genai.Client(api_key=self.get_api_key())
+            client.models.list()
+            return {"status": "ok", "details": "API reachable"}
+        except Exception as e:
+            logger.warning(f"Google health check failed: {e}")
+            return {"status": "error", "details": str(e)}

ai_router/providers/mistral_adapter.py

@@ -0,0 +1,70 @@
+import json
+import logging
+from typing import Optional
+
+import requests
+
+from .base import AIProvider
+
+logger = logging.getLogger("ai_router.mistral")
+
+MISTRAL_API_BASE = "https://api.mistral.ai/v1"
+
+
+class MistralAdapter(AIProvider):
+    @property
+    def name(self) -> str:
+        return "mistral"
+
+    def _headers(self, api_key: Optional[str] = None) -> dict:
+        key = api_key or self.get_api_key()
+        return {
+            "Authorization": f"Bearer {key}",
+            "Content-Type": "application/json",
+        }
+
+    def chat(self, messages: list, model: str, api_key: Optional[str] = None, **kwargs) -> dict:
+        key = api_key or self.get_api_key()
+        headers = self._headers(key)
+
+        payload = {
+            "model": model,
+            "messages": messages,
+        }
+        for k in ("temperature", "max_tokens", "top_p", "stream"):
+            if k in kwargs:
+                payload[k] = kwargs[k]
+
+        resp = requests.post(
+            f"{MISTRAL_API_BASE}/chat/completions",
+            headers=headers,
+            json=payload,
+            timeout=120,
+        )
+        resp.raise_for_status()
+        data = resp.json()
+        choice = data["choices"][0]
+        return {
+            "content": choice["message"]["content"] or "",
+            "model": data.get("model", model),
+            "provider": self.name,
+            "usage": {
+                "prompt_tokens": data.get("usage", {}).get("prompt_tokens", 0),
+                "completion_tokens": data.get("usage", {}).get("completion_tokens", 0),
+                "total_tokens": data.get("usage", {}).get("total_tokens", 0),
+            },
+        }
+
+    def models(self) -> list:
+        resp = requests.get(f"{MISTRAL_API_BASE}/models", headers=self._headers(), timeout=30)
+        resp.raise_for_status()
+        return [m["id"] for m in resp.json().get("data", [])]
+
+    def check_health(self) -> dict:
+        try:
+            resp = requests.get(f"{MISTRAL_API_BASE}/models", headers=self._headers(), timeout=10)
+            resp.raise_for_status()
+            return {"status": "ok", "details": "API reachable"}
+        except Exception as e:
+            logger.warning(f"Mistral health check failed: {e}")
+            return {"status": "error", "details": str(e)}

ai_router/providers/openai_adapter.py

@@ -0,0 +1,50 @@
+import logging
+from typing import Optional
+
+from .base import AIProvider
+
+logger = logging.getLogger("ai_router.openai")
+
+
+class OpenAIAdapter(AIProvider):
+    @property
+    def name(self) -> str:
+        return "openai"
+
+    def chat(self, messages: list, model: str, api_key: Optional[str] = None, **kwargs) -> dict:
+        from openai import OpenAI
+
+        key = api_key or self.get_api_key()
+        client = OpenAI(api_key=key)
+        resp = client.chat.completions.create(
+            model=model,
+            messages=messages,
+            **{k: v for k, v in kwargs.items() if k in ("temperature", "max_tokens", "top_p", "stream")},
+        )
+        choice = resp.choices[0]
+        return {
+            "content": choice.message.content or "",
+            "model": resp.model,
+            "provider": self.name,
+            "usage": {
+                "prompt_tokens": resp.usage.prompt_tokens if resp.usage else 0,
+                "completion_tokens": resp.usage.completion_tokens if resp.usage else 0,
+                "total_tokens": resp.usage.total_tokens if resp.usage else 0,
+            },
+        }
+
+    def models(self) -> list:
+        from openai import OpenAI
+
+        client = OpenAI(api_key=self.get_api_key())
+        return [m.id for m in client.models.list()]
+
+    def check_health(self) -> dict:
+        try:
+            from openai import OpenAI
+            client = OpenAI(api_key=self.get_api_key())
+            client.models.list()
+            return {"status": "ok", "details": "API reachable"}
+        except Exception as e:
+            logger.warning(f"OpenAI health check failed: {e}")
+            return {"status": "error", "details": str(e)}

ai_router/router.py

@@ -0,0 +1,174 @@
+import logging
+import time
+import uuid
+from typing import Optional
+
+from .providers import PROVIDER_MAP, AIProvider
+from .models import get_providers_from_db, log_router_attempt
+
+logger = logging.getLogger("ai_router.router")
+
+DEFAULT_MODEL_MAP = {
+    "gpt-4o": {"provider": "openai", "real_model": "gpt-4o"},
+    "gpt-4o-mini": {"provider": "openai", "real_model": "gpt-4o-mini"},
+    "claude-3-opus": {"provider": "anthropic", "real_model": "claude-3-opus-20240229"},
+    "claude-3-sonnet": {"provider": "anthropic", "real_model": "claude-3-sonnet-20240229"},
+    "claude-3-haiku": {"provider": "anthropic", "real_model": "claude-3-haiku-20240307"},
+    "gemini-pro": {"provider": "google", "real_model": "gemini-1.5-pro"},
+    "gemini-flash": {"provider": "google", "real_model": "gemini-1.5-flash"},
+    "mistral-large": {"provider": "mistral", "real_model": "mistral-large-latest"},
+    "mistral-small": {"provider": "mistral", "real_model": "mistral-small-latest"},
+}
+
+
+class AIRouter:
+    def __init__(self):
+        self._provider_instances = {}
+
+    def get_provider(self, name: str) -> Optional[AIProvider]:
+        if name not in self._provider_instances:
+            cls = PROVIDER_MAP.get(name)
+            if not cls:
+                return None
+            self._provider_instances[name] = cls()
+        return self._provider_instances[name]
+
+    def _resolve_model(self, model_alias: str) -> Optional[dict]:
+        mapping = self._load_model_mappings()
+        return mapping.get(model_alias)
+
+    def _load_model_mappings(self) -> dict:
+        db_mappings = []
+        try:
+            db_mappings = get_providers_from_db()
+        except Exception as e:
+            logger.warning(f"Could not load model mappings from DB: {e}")
+
+        merged = dict(DEFAULT_MODEL_MAP)
+        for entry in db_mappings:
+            alias = entry.get("model_alias")
+            if alias:
+                merged[alias] = {
+                    "provider": entry["provider_type"],
+                    "real_model": entry.get("real_model_id", alias),
+                    "cost_per_1k": entry.get("cost_per_1k_tokens", 0),
+                    "db_config": entry,
+                }
+        return merged
+
+    def _get_prioritized_providers(self):
+        providers = []
+        try:
+            db_providers = get_providers_from_db()
+            seen_names = set()
+            for p in sorted(db_providers, key=lambda x: x.get("priority", 99)):
+                name = p["provider_type"]
+                if name not in seen_names:
+                    seen_names.add(name)
+                    providers.append((name, p))
+        except Exception as e:
+            logger.warning(f"Could not load provider priority from DB: {e}")
+
+        if not providers:
+            default_order = ["openai", "anthropic", "google", "mistral"]
+            providers = [(n, None) for n in default_order]
+        return providers
+
+    def chat(self, messages: list, model_alias: str, user_id: Optional[int] = None, **kwargs) -> dict:
+        request_id = str(uuid.uuid4())
+        start_time = time.time()
+
+        model_info = self._resolve_model(model_alias)
+        if not model_info:
+            return {"error": f"Unknown model: {model_alias}", "status": "error"}
+
+        provider_order = self._get_prioritized_providers()
+        preferred_provider = model_info["provider"]
+        real_model = model_info["real_model"]
+
+        ordered = []
+        for name, db_config in provider_order:
+            if name == preferred_provider:
+                ordered.insert(0, (name, db_config))
+            else:
+                ordered.append((name, db_config))
+
+        if preferred_provider not in [p[0] for p in ordered]:
+            ordered.insert(0, (preferred_provider, None))
+
+        last_error = None
+        for attempt, (provider_name, db_config) in enumerate(ordered):
+            provider = self.get_provider(provider_name)
+            if not provider:
+                continue
+
+            try:
+                if attempt > 0:
+                    backoff = min(2 ** (attempt - 1), 30)
+                    logger.info(f"Failover to {provider_name} after {backoff}s backoff (attempt {attempt})")
+                    time.sleep(backoff)
+
+                api_key = provider.get_api_key(db_config)
+                if not api_key:
+                    logger.warning(f"No API key configured for {provider_name}, skipping")
+                    continue
+
+                result = provider.chat(messages, model=real_model, api_key=api_key, **kwargs)
+                elapsed = int((time.time() - start_time) * 1000)
+
+                log_router_attempt(
+                    request_id=request_id,
+                    user_id=user_id,
+                    model_alias=model_alias,
+                    provider_used=provider_name,
+                    tokens_in=result.get("usage", {}).get("prompt_tokens", 0),
+                    tokens_out=result.get("usage", {}).get("completion_tokens", 0),
+                    duration_ms=elapsed,
+                    status="success",
+                )
+                result["request_id"] = request_id
+                result["status"] = "success"
+                return result
+
+            except Exception as e:
+                last_error = str(e)
+                elapsed = int((time.time() - start_time) * 1000)
+                logger.warning(f"Provider {provider_name} failed: {e}")
+                log_router_attempt(
+                    request_id=request_id,
+                    user_id=user_id,
+                    model_alias=model_alias,
+                    provider_used=provider_name,
+                    tokens_in=0,
+                    tokens_out=0,
+                    duration_ms=elapsed,
+                    status="error",
+                    error_message=last_error,
+                )
+
+        elapsed = int((time.time() - start_time) * 1000)
+        return {
+            "error": f"All providers failed. Last error: {last_error}",
+            "status": "error",
+            "request_id": request_id,
+            "duration_ms": elapsed,
+        }
+
+    def check_all_providers_health(self) -> dict:
+        results = {}
+        for name in PROVIDER_MAP:
+            provider = self.get_provider(name)
+            results[name] = provider.check_health()
+        return results
+
+    def list_available_models(self) -> list:
+        model_map = self._load_model_mappings()
+        return [
+            {
+                "alias": alias,
+                "provider": info["provider"],
+                "real_model": info["real_model"],
+                "cost_per_1k_tokens": info.get("cost_per_1k", 0),
+            }
+            for alias, info in model_map.items()
+        ]

ai_router/utils.py

@@ -0,0 +1,93 @@
+import logging
+import os
+import sys
+from functools import wraps
+
+from flask import request, jsonify
+
+logger = logging.getLogger("ai_router")
+
+TOKEN_BROKER_URL = os.environ.get(
+    "TOKEN_BROKER_URL", "http://localhost:8783"
+)
+
+
+def verify_token_via_broker(token: str) -> dict:
+    """Verify an API token via the token-broker /verify endpoint."""
+    import requests
+    try:
+        resp = requests.post(
+            f"{TOKEN_BROKER_URL}/api/v1/tokens/verify",
+            json={"token": token},
+            timeout=10,
+        )
+        if resp.status_code == 200:
+            data = resp.json()
+            if data.get("valid"):
+                return data
+        return {}
+    except requests.RequestException as e:
+        logger.warning(f"Token broker unreachable: {e}")
+        return {}
+
+
+def require_auth(f):
+    """Decorator: validate Bearer or X-API-Key via token-broker."""
+    @wraps(f)
+    def decorated(*args, **kwargs):
+        auth_header = request.headers.get("Authorization", "")
+        api_key = request.headers.get("X-API-Key", "")
+
+        raw_token = ""
+        if auth_header.startswith("Bearer "):
+            raw_token = auth_header.split(" ", 1)[1]
+        elif api_key:
+            raw_token = api_key
+
+        if not raw_token:
+            return jsonify({"error": "Authentication required"}), 401
+
+        payload = verify_token_via_broker(raw_token)
+        if not payload or not payload.get("valid"):
+            return jsonify({"error": "Invalid or expired token"}), 401
+
+        request.current_user = {
+            "user_id": payload.get("user_id"),
+            "token_id": payload.get("token_id"),
+            "scopes": payload.get("scopes", []),
+        }
+        return f(*args, **kwargs)
+    return decorated
+
+
+def admin_required(f):
+    """Decorator: require admin scope on the authenticated token."""
+    @wraps(f)
+    def decorated(*args, **kwargs):
+        auth_header = request.headers.get("Authorization", "")
+        api_key = request.headers.get("X-API-Key", "")
+
+        raw_token = ""
+        if auth_header.startswith("Bearer "):
+            raw_token = auth_header.split(" ", 1)[1]
+        elif api_key:
+            raw_token = api_key
+
+        if not raw_token:
+            return jsonify({"error": "Authentication required"}), 401
+
+        payload = verify_token_via_broker(raw_token)
+        if not payload or not payload.get("valid"):
+            return jsonify({"error": "Invalid or expired token"}), 401
+
+        scopes = payload.get("scopes", [])
+        if "admin" not in scopes and "ai_router_admin" not in scopes:
+            return jsonify({"error": "Admin access required"}), 403
+
+        request.current_user = {
+            "user_id": payload.get("user_id"),
+            "token_id": payload.get("token_id"),
+            "scopes": scopes,
+        }
+        return f(*args, **kwargs)
+    return decorated

ai_router_api.py

@@ -0,0 +1,77 @@
+#!/usr/bin/env python3
+"""
+AI Router API — Multi-provider LLM routing with failover
+Port: 8783 | DB: SQLite ai_router.db
+HRT-200 — AI Router (Multi-provider + failover)
+
+Endpoints:
+  GET    /api/v1/ai/health              — Check all providers health
+  GET    /api/v1/ai/models              — List available models
+  POST   /api/v1/ai/chat                — Chat completion with auto-failover
+  GET    /api/v1/ai/admin/providers     — List configured providers
+  POST   /api/v1/ai/admin/providers     — Upsert a provider
+  POST   /api/v1/ai/admin/model-mappings— Upsert a model mapping
+  DELETE /api/v1/ai/admin/providers/:id — Remove a provider
+  GET    /api/v1/ai/usage               — Usage logs
+  GET    /api/v1/ai/usage/summary       — Aggregated usage stats
+"""
+
+import logging
+import logging.handlers
+import os
+import sys
+
+from flask import Flask, jsonify
+from flask_cors import CORS
+
+LOG_DIR = os.path.join(os.path.dirname(__file__), "ai_router", "logs")
+os.makedirs(LOG_DIR, exist_ok=True)
+
+logging.basicConfig(
+    level=logging.INFO,
+    format="%(asctime)s [%(levelname)s] ai-router: %(name)s: %(message)s",
+    handlers=[
+        logging.StreamHandler(sys.stdout),
+        logging.handlers.RotatingFileHandler(
+            os.path.join(LOG_DIR, "ai_router.log"),
+            maxBytes=5 * 1024 * 1024,
+            backupCount=3,
+        ),
+    ],
+)
+logger = logging.getLogger("ai_router")
+
+PORT = int(os.environ.get("AI_ROUTER_PORT", "8783"))
+
+
+def create_app():
+    app = Flask(__name__)
+    CORS(app)
+
+    from ai_router.api import register_ai_router
+    from ai_router.models import init_db
+
+    init_db()
+    register_ai_router(app)
+
+    @app.errorhandler(404)
+    def not_found(e):
+        return jsonify({"error": "not_found", "message": "Route not found"}), 404
+
+    @app.errorhandler(500)
+    def internal_error(e):
+        logger.error(f"Internal error: {e}")
+        return jsonify({"error": "internal_error", "message": "Internal server error"}), 500
+
+    return app
+
+
+if __name__ == "__main__":
+    logger.info("=" * 60)
+    logger.info("AI Router API starting...")
+    logger.info(f"Port: {PORT}")
+    logger.info("=" * 60)
+
+    app = create_app()
+    debug = os.environ.get("FLASK_ENV", "production") == "development"
+    app.run(host="0.0.0.0", port=PORT, debug=debug)

api_tokens_db.py

@@ -13,7 +13,9 @@ logger = logging.getLogger("turf_saas.api_tokens_db")
 
 
 def get_db() -> sqlite3.Connection:
-    conn = sqlite3.connect(DB_PATH)
+    """Return a SQLite connection (reads TURF_SAAS_DB dynamically for test isolation)."""
+    db_path = os.environ.get("TURF_SAAS_DB", DB_PATH)
+    conn = sqlite3.connect(db_path)
     conn.row_factory = sqlite3.Row
     return conn
 

api_v1/__init__.py

@@ -22,6 +22,8 @@ Registers sub-blueprints:
   /api/v1/history     — historique préd. ML (Free:7j, Premium:90j, Pro:illimité)
   /api/v1/org/        — organisations Pro (multi-compte, max 5 users)
   /api/v1/docs        — Swagger UI (via flasgger, registered on app)
+  /api/v1/ml/feedback/run  — trigger feedback loop ML (admin)
+  /api/v1/ml/feedback/stats — stats par stratégie (premium+)
 """
 
 from flask import Blueprint
@@ -38,6 +40,8 @@ from .routes.user import user_bp
 from .routes.user_tokens import user_tokens_bp
 from .routes.history import history_bp
 from .routes.org import org_bp
+from .routes.ml_feedback import ml_feedback_bp
+from .routes.admin import admin_bp
 
 # Master blueprint that aggregates all sub-routes under /api/v1
 api_v1_bp = Blueprint("api_v1", __name__, url_prefix="/api/v1")
@@ -57,3 +61,5 @@ def register_api_v1(app):
     app.register_blueprint(user_tokens_bp)
     app.register_blueprint(history_bp)
     app.register_blueprint(org_bp)
+    app.register_blueprint(ml_feedback_bp)
+    app.register_blueprint(admin_bp)

api_v1/routes/admin.py

@@ -0,0 +1,587 @@
+#!/usr/bin/env python3
+"""
+Admin Blueprint — Client CRUD + Subscription management
+HRT-199 — Foundation (Client CRUD + Auth + Subscription)
+
+Endpoints:
+  POST   /api/v1/admin/setup                     — init first admin (no auth, 1 call only)
+  GET    /api/v1/admin/clients                    — list all clients (paginated, filterable)
+  GET    /api/v1/admin/clients/<id>               — client detail + subscription
+  PUT    /api/v1/admin/clients/<id>               — update client (plan, name, email)
+  DELETE /api/v1/admin/clients/<id>               — delete client + tokens + subscription
+  POST   /api/v1/admin/clients/<id>/suspend       — suspend client (set plan=suspended)
+  POST   /api/v1/admin/clients/<id>/activate      — reactivate client (restore plan)
+  GET    /api/v1/admin/stats                      — client stats (total, by plan, new/30d)
+"""
+
+import sqlite3
+import logging
+import os
+from datetime import datetime, timezone
+from functools import wraps
+
+from flask import Blueprint, jsonify, request
+
+from saas_auth import require_auth
+from api_v1.utils import get_db, paginate_query, get_pagination_params, not_found, bad_request, internal_error
+
+logger = logging.getLogger("turf_saas.admin")
+
+admin_bp = Blueprint("admin", __name__, url_prefix="/api/v1/admin")
+
+DB_PATH = os.environ.get("TURF_SAAS_DB", "/home/h3r7/turf_saas/turf_saas.db")
+
+
+def _get_saas_db():
+    conn = sqlite3.connect(DB_PATH)
+    conn.row_factory = sqlite3.Row
+    conn.execute("PRAGMA foreign_keys = ON")
+    return conn
+
+
+def migrate_admin_tables():
+    """Idempotent: create admin_users table."""
+    conn = _get_saas_db()
+    conn.executescript("""
+        CREATE TABLE IF NOT EXISTS admin_users (
+            user_id    TEXT PRIMARY KEY REFERENCES saas_users(id),
+            created_at TEXT NOT NULL DEFAULT (datetime('now')),
+            created_by TEXT
+        );
+    """)
+    conn.commit()
+    conn.close()
+
+
+try:
+    migrate_admin_tables()
+except Exception as e:
+    logger.warning("admin DB init warning: %s", e)
+
+
+def _is_admin(user_id: str, db=None) -> bool:
+    if not user_id:
+        return False
+    close = False
+    if db is None:
+        db = _get_saas_db()
+        close = True
+    try:
+        row = db.execute(
+            "SELECT 1 FROM admin_users WHERE user_id = ?", (user_id,)
+        ).fetchone()
+        return row is not None
+    finally:
+        if close:
+            db.close()
+
+
+def require_admin(f):
+    @wraps(f)
+    def decorated(*args, **kwargs):
+        user = getattr(request, "current_user", None)
+        if not user:
+            return jsonify({"error": "Non authentifié"}), 401
+        if not _is_admin(user["id"]):
+            return jsonify({"error": "Accès administrateur requis"}), 403
+        return f(*args, **kwargs)
+    return decorated
+
+
+def _user_to_client(row) -> dict:
+    return {
+        "id": row["id"],
+        "email": row["email"],
+        "firstname": row.get("firstname", ""),
+        "lastname": row.get("lastname", ""),
+        "plan": row.get("plan", "free"),
+        "telegram_chat_id": row.get("telegram_chat_id"),
+        "alert_value_bets": bool(row.get("alert_value_bets", 1)),
+        "alert_top1": bool(row.get("alert_top1", 1)),
+        "alert_quinte_only": bool(row.get("alert_quinte_only", 0)),
+        "created_at": row.get("created_at"),
+        "updated_at": row.get("updated_at"),
+    }
+
+
+def _fetch_subscription(db, user_id: str):
+    return db.execute(
+        """SELECT * FROM saas_subscriptions
+           WHERE user_id = ? ORDER BY start_date DESC LIMIT 1""",
+        (user_id,),
+    ).fetchone()
+
+
+# ─── POST /api/v1/admin/setup ─────────────────────────────────
+
+
+@admin_bp.route("/setup", methods=["POST"])
+def admin_setup():
+    """Init first admin (no auth). Only works once — when admin_users is empty."""
+    data = request.get_json(silent=True) or {}
+    email = (data.get("email") or "").strip().lower()
+    if not email or "@" not in email:
+        return jsonify({"error": "Email valide requis"}), 400
+
+    db = _get_saas_db()
+    try:
+        existing = db.execute("SELECT 1 FROM admin_users LIMIT 1").fetchone()
+        if existing:
+            return jsonify({"error": "Admin déjà configuré"}), 409
+
+        user = db.execute(
+            "SELECT id, email FROM saas_users WHERE email = ?", (email,)
+        ).fetchone()
+        if not user:
+            return jsonify({"error": "Utilisateur introuvable avec cet email"}), 404
+
+        db.execute(
+            "INSERT INTO admin_users (user_id, created_by) VALUES (?, 'setup')",
+            (user["id"],),
+        )
+        db.commit()
+        logger.info("Admin setup: user %s (%s) promoted to admin", user["id"], email)
+        return jsonify({"ok": True, "user_id": user["id"], "email": email}), 201
+    except Exception as e:
+        db.rollback()
+        logger.error("admin_setup error: %s", e)
+        return jsonify({"error": "Erreur interne"}), 500
+    finally:
+        db.close()
+
+
+# ─── GET /api/v1/admin/clients ─────────────────────────────────
+
+
+@admin_bp.route("/clients", methods=["GET"])
+@require_auth
+@require_admin
+def list_clients():
+    """List all clients with pagination and filters.
+    ---
+    tags:
+      - Admin
+    security:
+      - Bearer: []
+    parameters:
+      - in: query
+        name: page
+        type: integer
+      - in: query
+        name: per_page
+        type: integer
+      - in: query
+        name: search
+        type: string
+        description: Search by email or name
+      - in: query
+        name: plan
+        type: string
+        description: Filter by plan (free, premium, pro, suspended)
+      - in: query
+        name: sort_by
+        type: string
+        enum: [created_at, email, plan, updated_at]
+      - in: query
+        name: sort_order
+        type: string
+        enum: [asc, desc]
+    responses:
+      200:
+        description: Paginated client list
+      403:
+        description: Admin access required
+    """
+    page = request.args.get("page", 1, type=int)
+    per_page = request.args.get("per_page", 20, type=int)
+    search = request.args.get("search", "").strip()
+    plan_filter = request.args.get("plan", "").strip()
+    sort_by = request.args.get("sort_by", "created_at").strip()
+    sort_order = request.args.get("sort_order", "desc").strip()
+
+    if sort_by not in ("created_at", "email", "plan", "updated_at"):
+        sort_by = "created_at"
+    if sort_order not in ("asc", "desc"):
+        sort_order = "desc"
+    if per_page < 1 or per_page > 100:
+        per_page = 20
+    if page < 1:
+        page = 1
+
+    offset = (page - 1) * per_page
+
+    db = _get_saas_db()
+    try:
+        conditions = []
+        params = []
+        if search:
+            conditions.append("(email LIKE ? OR firstname LIKE ? OR lastname LIKE ?)")
+            p = f"%{search}%"
+            params.extend([p, p, p])
+        if plan_filter:
+            conditions.append("plan = ?")
+            params.append(plan_filter)
+
+        where = (" WHERE " + " AND ".join(conditions)) if conditions else ""
+
+        total = db.execute(
+            f"SELECT COUNT(*) FROM saas_users{where}", params
+        ).fetchone()[0]
+
+        rows = db.execute(
+            f"SELECT * FROM saas_users{where} ORDER BY {sort_by} {sort_order} LIMIT ? OFFSET ?",
+            params + [per_page, offset],
+        ).fetchall()
+
+        result = []
+        for row in rows:
+            client = _user_to_client(row)
+            sub = _fetch_subscription(db, row["id"])
+            if sub:
+                client["subscription"] = {
+                    "plan": sub["plan"],
+                    "status": sub["status"],
+                    "start_date": sub["start_date"],
+                    "current_period_end": sub["current_period_end"],
+                    "stripe_customer_id": sub["stripe_customer_id"],
+                }
+            else:
+                client["subscription"] = None
+            result.append(client)
+
+        return jsonify({
+            "clients": result,
+            "pagination": {
+                "page": page,
+                "per_page": per_page,
+                "total": total,
+                "total_pages": (total + per_page - 1) // per_page,
+            },
+        }), 200
+    except Exception as e:
+        logger.error("list_clients error: %s", e)
+        return jsonify({"error": "Erreur interne"}), 500
+    finally:
+        db.close()
+
+
+# ─── GET /api/v1/admin/clients/<id> ────────────────────────────
+
+
+@admin_bp.route("/clients/<string:client_id>", methods=["GET"])
+@require_auth
+@require_admin
+def get_client(client_id: str):
+    """Get client details with subscription info.
+    ---
+    tags:
+      - Admin
+    security:
+      - Bearer: []
+    parameters:
+      - in: path
+        name: id
+        type: string
+        required: true
+    responses:
+      200:
+        description: Client details
+      404:
+        description: Client not found
+    """
+    db = _get_saas_db()
+    try:
+        row = db.execute(
+            "SELECT * FROM saas_users WHERE id = ?", (client_id,)
+        ).fetchone()
+        if not row:
+            return jsonify({"error": "Client introuvable"}), 404
+
+        client = _user_to_client(row)
+        sub = _fetch_subscription(db, client_id)
+        if sub:
+            client["subscription"] = {
+                "id": sub["id"],
+                "plan": sub["plan"],
+                "status": sub["status"],
+                "start_date": sub["start_date"],
+                "end_date": sub["end_date"],
+                "current_period_end": sub["current_period_end"],
+                "grace_period_end": sub["grace_period_end"],
+                "stripe_customer_id": sub["stripe_customer_id"],
+                "stripe_subscription_id": sub["stripe_subscription_id"],
+            }
+        else:
+            client["subscription"] = None
+
+        return jsonify({"client": client}), 200
+    except Exception as e:
+        logger.error("get_client error: %s", e)
+        return jsonify({"error": "Erreur interne"}), 500
+    finally:
+        db.close()
+
+
+# ─── PUT /api/v1/admin/clients/<id> ────────────────────────────
+
+
+@admin_bp.route("/clients/<string:client_id>", methods=["PUT"])
+@require_auth
+@require_admin
+def update_client(client_id: str):
+    """Update client fields (plan, firstname, lastname, email).
+    ---
+    tags:
+      - Admin
+    security:
+      - Bearer: []
+    parameters:
+      - in: path
+        name: id
+        type: string
+        required: true
+    requestBody:
+      required: true
+      content:
+        application/json:
+          schema:
+            type: object
+            properties:
+              firstname:  { type: string }
+              lastname:   { type: string }
+              email:      { type: string }
+              plan:       { type: string, enum: [free, premium, pro, suspended] }
+    responses:
+      200:
+        description: Client updated
+      400:
+        description: Invalid parameters
+      404:
+        description: Client not found
+    """
+    data = request.get_json(silent=True) or {}
+    if not data:
+        return jsonify({"error": "Corps JSON requis"}), 400
+
+    db = _get_saas_db()
+    try:
+        existing = db.execute(
+            "SELECT id FROM saas_users WHERE id = ?", (client_id,)
+        ).fetchone()
+        if not existing:
+            return jsonify({"error": "Client introuvable"}), 404
+
+        fields = {}
+        if "firstname" in data:
+            fields["firstname"] = data["firstname"].strip()
+        if "lastname" in data:
+            fields["lastname"] = data["lastname"].strip()
+        if "email" in data:
+            email = data["email"].strip().lower()
+            if "@" not in email:
+                return jsonify({"error": "Email invalide"}), 400
+            fields["email"] = email
+        if "plan" in data:
+            plan = data["plan"].strip().lower()
+            if plan not in ("free", "premium", "pro", "suspended"):
+                return jsonify({"error": "Plan invalide. free|premium|pro|suspended"}), 400
+            fields["plan"] = plan
+
+        if not fields:
+            return jsonify({"ok": True}), 200
+
+        set_clause = ", ".join(f"{k}=?" for k in fields)
+        values = list(fields.values()) + [datetime.now(timezone.utc).isoformat(), client_id]
+        db.execute(
+            f"UPDATE saas_users SET {set_clause}, updated_at=? WHERE id=?", values
+        )
+        db.commit()
+
+        logger.info("Admin %s updated client %s: %s",
+                     request.current_user["id"], client_id, fields)
+        return jsonify({"ok": True, "updated": list(fields.keys())}), 200
+
+    except sqlite3.IntegrityError:
+        return jsonify({"error": "Cet email est déjà utilisé"}), 409
+    except Exception as e:
+        db.rollback()
+        logger.error("update_client error: %s", e)
+        return jsonify({"error": "Erreur interne"}), 500
+    finally:
+        db.close()
+
+
+# ─── DELETE /api/v1/admin/clients/<id> ─────────────────────────
+
+
+@admin_bp.route("/clients/<string:client_id>", methods=["DELETE"])
+@require_auth
+@require_admin
+def delete_client(client_id: str):
+    """Delete client and all associated data (tokens, subscriptions).
+    ---
+    tags:
+      - Admin
+    security:
+      - Bearer: []
+    parameters:
+      - in: path
+        name: id
+        type: string
+        required: true
+    responses:
+      200:
+        description: Client deleted
+      404:
+        description: Client not found
+    """
+    admin_id = request.current_user["id"]
+    if client_id == admin_id:
+        return jsonify({"error": "Impossible de supprimer votre propre compte"}), 400
+
+    db = _get_saas_db()
+    try:
+        existing = db.execute(
+            "SELECT id FROM saas_users WHERE id = ?", (client_id,)
+        ).fetchone()
+        if not existing:
+            return jsonify({"error": "Client introuvable"}), 404
+
+        db.execute("DELETE FROM saas_tokens WHERE user_id = ?", (client_id,))
+        db.execute("DELETE FROM saas_subscriptions WHERE user_id = ?", (client_id,))
+        db.execute("DELETE FROM admin_users WHERE user_id = ?", (client_id,))
+        db.execute("DELETE FROM saas_users WHERE id = ?", (client_id,))
+        db.commit()
+
+        logger.info("Admin %s deleted client %s", admin_id, client_id)
+        return jsonify({"ok": True, "deleted_id": client_id}), 200
+    except Exception as e:
+        db.rollback()
+        logger.error("delete_client error: %s", e)
+        return jsonify({"error": "Erreur interne"}), 500
+    finally:
+        db.close()
+
+
+# ─── POST /api/v1/admin/clients/<id>/suspend ───────────────────
+
+
+@admin_bp.route("/clients/<string:client_id>/suspend", methods=["POST"])
+@require_auth
+@require_admin
+def suspend_client(client_id: str):
+    """Suspend a client by setting plan to 'suspended'.
+    ---
+    tags:
+      - Admin
+    security:
+      - Bearer: []
+    responses:
+      200:
+        description: Client suspended
+      404:
+        description: Client not found
+    """
+    return _set_client_plan(client_id, "suspended")
+
+
+# ─── POST /api/v1/admin/clients/<id>/activate ──────────────────
+
+
+@admin_bp.route("/clients/<string:client_id>/activate", methods=["POST"])
+@require_auth
+@require_admin
+def activate_client(client_id: str):
+    """Reactivate a suspended client to 'free' plan.
+    ---
+    tags:
+      - Admin
+    security:
+      - Bearer: []
+    responses:
+      200:
+        description: Client activated
+      404:
+        description: Client not found
+    """
+    return _set_client_plan(client_id, "free")
+
+
+def _set_client_plan(client_id: str, plan: str):
+    db = _get_saas_db()
+    try:
+        existing = db.execute(
+            "SELECT id, plan FROM saas_users WHERE id = ?", (client_id,)
+        ).fetchone()
+        if not existing:
+            return jsonify({"error": "Client introuvable"}), 404
+
+        db.execute(
+            "UPDATE saas_users SET plan=?, updated_at=? WHERE id=?",
+            (plan, datetime.now(timezone.utc).isoformat(), client_id),
+        )
+        db.commit()
+
+        action = "suspendu" if plan == "suspended" else "réactivé"
+        logger.info("Client %s %s par admin %s", client_id, action,
+                     request.current_user["id"])
+        return jsonify({"ok": True, "client_id": client_id, "plan": plan, "action": action}), 200
+    except Exception as e:
+        db.rollback()
+        logger.error("_set_client_plan error: %s", e)
+        return jsonify({"error": "Erreur interne"}), 500
+    finally:
+        db.close()
+
+
+# ─── GET /api/v1/admin/stats ────────────────────────────────────
+
+
+@admin_bp.route("/stats", methods=["GET"])
+@require_auth
+@require_admin
+def admin_stats():
+    """Client stats: totals by plan, new this month/30d.
+    ---
+    tags:
+      - Admin
+    security:
+      - Bearer: []
+    responses:
+      200:
+        description: Admin stats
+    """
+    db = _get_saas_db()
+    try:
+        total = db.execute("SELECT COUNT(*) FROM saas_users").fetchone()[0]
+
+        by_plan = {}
+        for row in db.execute(
+            "SELECT plan, COUNT(*) AS cnt FROM saas_users GROUP BY plan"
+        ).fetchall():
+            by_plan[row["plan"]] = row["cnt"]
+
+        new_30d = db.execute(
+            "SELECT COUNT(*) FROM saas_users WHERE created_at >= datetime('now', '-30 days')"
+        ).fetchone()[0]
+
+        new_7d = db.execute(
+            "SELECT COUNT(*) FROM saas_users WHERE created_at >= datetime('now', '-7 days')"
+        ).fetchone()[0]
+
+        active_subs = db.execute(
+            "SELECT COUNT(DISTINCT user_id) FROM saas_subscriptions WHERE status = 'active'"
+        ).fetchone()[0]
+
+        return jsonify({
+            "total_clients": total,
+            "clients_by_plan": by_plan,
+            "new_last_30d": new_30d,
+            "new_last_7d": new_7d,
+            "active_subscriptions": active_subs,
+        }), 200
+    except Exception as e:
+        logger.error("admin_stats error: %s", e)
+        return jsonify({"error": "Erreur interne"}), 500
+    finally:
+        db.close()

api_v1/routes/billing.py

@@ -55,6 +55,23 @@ PLAN_NAMES = {
     "pro": "Pro",
 }
 
+# Plan consumption limits (configurable, not hardcoded)
+# Override via env vars: BILLING_LIMIT_FREE_API_CALLS, BILLING_LIMIT_PREMIUM_API_CALLS, etc.
+PLAN_LIMITS = {
+    "free": {
+        "monthly_api_calls": int(os.environ.get("BILLING_LIMIT_FREE_API_CALLS", "300")),
+        "monthly_tokens": int(os.environ.get("BILLING_LIMIT_FREE_TOKENS", "100000")),
+    },
+    "premium": {
+        "monthly_api_calls": int(os.environ.get("BILLING_LIMIT_PREMIUM_API_CALLS", "3000")),
+        "monthly_tokens": int(os.environ.get("BILLING_LIMIT_PREMIUM_TOKENS", "1000000")),
+    },
+    "pro": {
+        "monthly_api_calls": int(os.environ.get("BILLING_LIMIT_PRO_API_CALLS", "30000")),
+        "monthly_tokens": int(os.environ.get("BILLING_LIMIT_PRO_TOKENS", "10000000")),
+    },
+}
+
 # ──────────────────────────────────────────────────────────────
 # DB helpers
 # ──────────────────────────────────────────────────────────────
@@ -654,6 +671,122 @@ def _handle_payment_succeeded(db, event):
     logger.info("invoice.payment_succeeded: user %s payment cleared", user_id)
 
 
+# ──────────────────────────────────────────────────────────────
+# GET /api/v1/billing/consumption
+# ──────────────────────────────────────────────────────────────
+
+
+def _parse_month(month: str):
+    """Validate YYYY-MM format, return (year, month) tuple or None."""
+    import re
+    if not re.match(r"^\d{4}-\d{2}$", month):
+        return None
+    parts = month.split("-")
+    y, m = int(parts[0]), int(parts[1])
+    if m < 1 or m > 12:
+        return None
+    return y, m
+
+
+@billing_bp.route("/consumption", methods=["GET"])
+@jwt_required_middleware
+def consumption_status():
+    """
+    Return current month consumption vs plan limits for the authenticated user.
+    ---
+    tags:
+      - Billing
+    security:
+      - Bearer: []
+    parameters:
+      - in: query
+        name: month
+        type: string
+        required: false
+        description: "Month in YYYY-MM format (default: current month)"
+    responses:
+      200:
+        description: Consumption status with usage, limits, and alerts
+      400:
+        description: Invalid parameters
+      422:
+        description: Malformed month format
+    """
+    user = request.current_user
+    month = request.args.get("month", datetime.now().strftime("%Y-%m"))
+    parsed = _parse_month(month)
+    if not parsed:
+        return jsonify({"error": "Format mois invalide. Utiliser YYYY-MM"}), 422
+
+    year, mon = parsed
+    plan = user.get("plan", "free")
+    limits = PLAN_LIMITS.get(plan, PLAN_LIMITS["free"])
+
+    db = get_db()
+    try:
+        # Aggregate consumption for the given month
+        month_start = f"{year:04d}-{mon:02d}-01"
+        if mon == 12:
+            month_end = f"{year + 1:04d}-01-01"
+        else:
+            month_end = f"{year:04d}-{mon + 1:02d}-01"
+
+        row = db.execute(
+            """SELECT
+                   COALESCE(SUM(api_calls), 0) AS total_api_calls
+               FROM consumption_log
+               WHERE user_id = ? AND date >= ? AND date < ?""",
+            (str(user["id"]), month_start, month_end),
+        ).fetchone()
+
+        total_api_calls = row["total_api_calls"] if row else 0
+
+        # Calculate alert levels
+        api_limit = limits["monthly_api_calls"]
+        api_pct = round((total_api_calls / api_limit * 100), 1) if api_limit > 0 else 0
+
+        alerts = []
+        if api_pct >= 100:
+            alerts.append({
+                "type": "hard",
+                "metric": "api_calls",
+                "message": "Limite mensuelle d'appels API atteinte.",
+                "current": total_api_calls,
+                "limit": api_limit,
+            })
+        elif api_pct >= 80:
+            alerts.append({
+                "type": "soft",
+                "metric": "api_calls",
+                "message": f"Appels API à {api_pct}% de la limite mensuelle.",
+                "current": total_api_calls,
+                "limit": api_limit,
+            })
+
+    except Exception as e:
+        logger.error("Consumption query error for user %s: %s", user["id"], e)
+        return jsonify({"error": "Erreur interne"}), 500
+    finally:
+        db.close()
+
+    resp = jsonify({
+        "user_id": user["id"],
+        "plan": plan,
+        "month": month,
+        "consumption": {
+            "total_api_calls": total_api_calls,
+            "limit_api_calls": api_limit,
+            "usage_pct": api_pct,
+        },
+        "alerts": alerts,
+    })
+    if any(a["type"] == "hard" for a in alerts):
+        resp.headers["X-Billing-Alert"] = "hard_limit_reached"
+    elif any(a["type"] == "soft" for a in alerts):
+        resp.headers["X-Billing-Alert"] = "soft_limit_warning"
+    return resp, 200
+
+
 # ──────────────────────────────────────────────────────────────
 # On-import: ensure DB migration ran
 # ──────────────────────────────────────────────────────────────

api_v1/routes/history.py

@@ -20,7 +20,8 @@ from api_v1.utils import (
     get_pagination_params,
     paginate_query,
 )
-from auth import jwt_required_middleware
+# Auth: try flask_jwt_extended (app_v1) first, fall back to saas_auth (portal_server)
+from saas_auth import require_auth as jwt_required_middleware
 
 history_bp = Blueprint("v1_history", __name__, url_prefix="/api/v1/history")
 
@@ -104,7 +105,7 @@ def get_history():
       403:
         description: Plage de dates hors limite du plan — upgrade requis
     """
-    user = getattr(g, "current_user", None)
+    user = getattr(request, "current_user", None) or getattr(g, "current_user", None)
     if not user:
         return jsonify({"error": "Non authentifié"}), 401
 

api_v1/routes/metrics.py

@@ -14,15 +14,21 @@ from api_v1.utils import (
     internal_error,
     bad_request,
 )
-from auth import jwt_required_middleware, plan_required
+from saas_auth import require_auth as jwt_required_middleware
+from flask import request as _req
 
 metrics_bp = Blueprint("v1_metrics", __name__, url_prefix="/api/v1")
 
 
 @metrics_bp.route("/metrics", methods=["GET"])
 @jwt_required_middleware
-@plan_required("premium", "pro")
 def metrics():
+    # plan check: premium or pro (or TEST_MODE via plan='pro' in DB)
+    user = getattr(_req, 'current_user', None) or {}
+    plan = user.get('plan', 'free') if isinstance(user, dict) else 'free'
+    if plan not in ('premium', 'pro'):
+        from flask import jsonify as _j
+        return _j({'error': 'Plan premium ou pro requis'}), 403
     """
     Métriques ML
     ---

api_v1/routes/ml_feedback.py

@@ -0,0 +1,199 @@
+#!/usr/bin/env python3
+"""
+ml_feedback.py — API routes pour le feedback loop ML (turf_saas).
+
+Routes:
+  POST /api/v1/ml/feedback/run   — Déclenche le feedback loop (admin uniquement)
+  GET  /api/v1/ml/feedback/stats — Stats performances par stratégie
+
+Sécurité admin : token via variable d'environnement ML_ADMIN_TOKEN
+ou plan "pro" en fallback pour les stats.
+"""
+
+import os
+import sys
+from datetime import datetime
+
+from flask import Blueprint, jsonify, request, g
+
+# Ajoute le répertoire parent de api_v1 dans le path pour importer ml_feedback_saas
+sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.dirname(__file__))))
+
+from api_v1.utils import get_db, internal_error, bad_request
+# Auth: try flask_jwt_extended (app_v1) first, fall back to saas_auth (portal_server)
+try:
+    from auth import jwt_required_middleware
+except ImportError:
+    from saas_auth import require_auth as jwt_required_middleware
+try:
+    from auth import plan_required
+except ImportError:
+    plan_required = lambda *a, **kw: (lambda f: f)
+
+ml_feedback_bp = Blueprint("v1_ml_feedback", __name__, url_prefix="/api/v1/ml/feedback")
+
+# Token admin interne — configurable via variable d'environnement
+ML_ADMIN_TOKEN = os.environ.get("ML_ADMIN_TOKEN", "")
+
+
+def _check_admin(req):
+    """Vérifie le token admin via header X-Admin-Token ou Authorization Bearer (plan pro)."""
+    # 1. Token interne (scheduler/cron)
+    admin_token = req.headers.get("X-Admin-Token", "").strip()
+    if ML_ADMIN_TOKEN and admin_token == ML_ADMIN_TOKEN:
+        return True, None
+
+    # 2. Pas de token admin configuré → autoriser les utilisateurs "pro" authentifiés
+    user = getattr(request, "current_user", None) or getattr(g, "current_user", None)
+    if user and user.get("plan") == "pro":
+        return True, None
+
+    return False, jsonify({"error": "Accès admin requis", "code": 403}), 403
+
+
+@ml_feedback_bp.route("/run", methods=["POST"])
+@jwt_required_middleware
+def feedback_run():
+    """
+    Déclenche le feedback loop ML pour une date donnée.
+    ---
+    tags:
+      - ML Feedback
+    summary: Déclenche le feedback loop XGBoost (admin only)
+    security:
+      - Bearer: []
+      - AdminToken: []
+    parameters:
+      - name: body
+        in: body
+        schema:
+          type: object
+          properties:
+            date:
+              type: string
+              description: Date YYYY-MM-DD (défaut aujourd'hui)
+              example: "2026-04-25"
+            mode:
+              type: string
+              description: "run (défaut) ou backfill"
+              enum: [run, backfill]
+              example: run
+    responses:
+      200:
+        description: Feedback loop exécuté avec succès
+      400:
+        description: Paramètre invalide
+      403:
+        description: Accès refusé
+      500:
+        description: Erreur interne
+    """
+    # Vérification admin
+    user = getattr(request, "current_user", None) or getattr(g, "current_user", None)
+    admin_token = request.headers.get("X-Admin-Token", "").strip()
+    is_admin = (ML_ADMIN_TOKEN and admin_token == ML_ADMIN_TOKEN) or (
+        user and user.get("plan") == "pro"
+    )
+    if not is_admin:
+        return jsonify({"error": "Accès admin requis", "code": 403}), 403
+
+    body = request.get_json(silent=True) or {}
+    date_str = body.get("date") or datetime.now().strftime("%Y-%m-%d")
+    mode = body.get("mode", "run")
+
+    # Validation date
+    try:
+        datetime.strptime(date_str, "%Y-%m-%d")
+    except ValueError:
+        return bad_request(f"Date invalide : {date_str}. Format attendu : YYYY-MM-DD")
+
+    if mode not in ("run", "backfill"):
+        return bad_request("mode doit être 'run' ou 'backfill'")
+
+    try:
+        import ml_feedback_saas
+
+        if mode == "backfill":
+            inseres, maj = ml_feedback_saas.backfill(date_str)
+            total_inseres = inseres
+        else:
+            result = ml_feedback_saas.run(date_str)
+            total_inseres = sum(result["inseres"].values())
+            maj = result["maj"]
+
+        return jsonify(
+            {
+                "status": "ok",
+                "date": date_str,
+                "mode": mode,
+                "paris_inseres": total_inseres,
+                "paris_mis_a_jour": maj,
+            }
+        ), 200
+
+    except Exception as e:
+        return internal_error(str(e))
+
+
+@ml_feedback_bp.route("/stats", methods=["GET"])
+@jwt_required_middleware
+@plan_required("premium", "pro")
+def feedback_stats():
+    """
+    Stats performances ML par stratégie.
+    ---
+    tags:
+      - ML Feedback
+    summary: Stats paris ML par stratégie (premium+)
+    security:
+      - Bearer: []
+    parameters:
+      - name: date_debut
+        in: query
+        type: string
+        description: Date de début YYYY-MM-DD
+      - name: date_fin
+        in: query
+        type: string
+        description: Date de fin YYYY-MM-DD
+    responses:
+      200:
+        description: Stats par stratégie
+      401:
+        description: Token invalide
+      403:
+        description: Plan insuffisant (premium ou pro requis)
+    """
+    date_debut = request.args.get("date_debut")
+    date_fin = request.args.get("date_fin")
+
+    # Validation optionnelle des dates
+    for d_str, label in [(date_debut, "date_debut"), (date_fin, "date_fin")]:
+        if d_str:
+            try:
+                datetime.strptime(d_str, "%Y-%m-%d")
+            except ValueError:
+                return bad_request(f"{label} invalide : {d_str}. Format : YYYY-MM-DD")
+
+    conn = get_db()
+    try:
+        import ml_feedback_saas
+
+        stats = ml_feedback_saas.get_feedback_stats(conn, date_debut, date_fin)
+
+        return jsonify(
+            {
+                "status": "ok",
+                "strategies": stats,
+                "filters": {
+                    "date_debut": date_debut,
+                    "date_fin": date_fin,
+                },
+                "total_strategies": len(stats),
+            }
+        ), 200
+
+    except Exception as e:
+        return internal_error(str(e))
+    finally:
+        conn.close()

api_v1/routes/user.py

@@ -13,7 +13,15 @@ import sqlite3
 from flask import Blueprint, jsonify, request
 
 from api_v1.utils import internal_error, bad_request
-from auth import jwt_required_middleware, plan_required
+# Auth: try flask_jwt_extended (app_v1) first, fall back to saas_auth (portal_server)
+try:
+    from auth import jwt_required_middleware
+except ImportError:
+    from saas_auth import require_auth as jwt_required_middleware
+try:
+    from auth import plan_required
+except ImportError:
+    plan_required = lambda *a, **kw: (lambda f: f)
 
 user_bp = Blueprint("v1_user", __name__, url_prefix="/api/v1/user")
 

api_v1/utils.py

@@ -16,8 +16,9 @@ DB_PATH = os.environ.get("TURF_SAAS_DB", "/home/h3r7/turf_saas/turf_saas.db")
 
 
 def get_db():
-    """Return a SQLite connection with Row factory."""
+    """Return a SQLite connection with Row factory (reads TURF_SAAS_DB dynamically)."""
-    conn = sqlite3.connect(DB_PATH)
+    db_path = os.environ.get("TURF_SAAS_DB", DB_PATH)
+    conn = sqlite3.connect(db_path)
     conn.row_factory = sqlite3.Row
     return conn
 

auth_db.py

@@ -8,11 +8,15 @@ HRT-79: migration Telegram columns
 import sqlite3
 import os
 
+# NOTE: DB_PATH kept for backward compat, but get_db() reads env at call time
+# so test isolation works correctly when TURF_SAAS_DB is set per-module.
 DB_PATH = os.environ.get("TURF_SAAS_DB", "/home/h3r7/turf_saas/turf_saas.db")
 
 
 def get_db():
-    conn = sqlite3.connect(DB_PATH)
+    # Read env dynamically so test overrides of TURF_SAAS_DB are respected
+    db_path = os.environ.get("TURF_SAAS_DB", DB_PATH)
+    conn = sqlite3.connect(db_path)
     conn.row_factory = sqlite3.Row
     return conn
 

billing_db.py

@@ -13,6 +13,8 @@ Run once:
 import sqlite3
 import os
 import logging
+from dataclasses import dataclass
+from typing import Optional
 
 DB_PATH = os.environ.get("TURF_SAAS_DB", "/home/h3r7/turf_saas/turf_saas.db")
 logger = logging.getLogger("turf_saas.billing_db")
@@ -21,6 +23,7 @@ logger = logging.getLogger("turf_saas.billing_db")
 def get_db():
     conn = sqlite3.connect(DB_PATH)
     conn.row_factory = sqlite3.Row
+    conn.execute("PRAGMA foreign_keys = ON")
     return conn
 
 
@@ -101,12 +104,59 @@ def migrate_billing_tables():
         CREATE INDEX IF NOT EXISTS idx_saas_subs_stripe       ON saas_subscriptions(stripe_subscription_id);
         CREATE INDEX IF NOT EXISTS idx_subscriptions_stripe   ON subscriptions(stripe_subscription_id);
         CREATE INDEX IF NOT EXISTS idx_subscriptions_customer ON subscriptions(stripe_customer_id);
+
+        -- HRT-202: Billing tables (invoices, transactions, consumption_log)
+        CREATE TABLE IF NOT EXISTS invoices (
+            id                  INTEGER PRIMARY KEY AUTOINCREMENT,
+            invoice_number      TEXT    NOT NULL UNIQUE,
+            user_id             INTEGER NOT NULL REFERENCES users(id),
+            period_start        TEXT    NOT NULL,
+            period_end          TEXT    NOT NULL,
+            plan                TEXT    NOT NULL,
+            amount_cents        INTEGER NOT NULL,
+            currency            TEXT    NOT NULL DEFAULT 'EUR',
+            status              TEXT    NOT NULL DEFAULT 'pending'
+                                    CHECK(status IN ('pending','paid','overdue','cancelled','refunded')),
+            pdf_path            TEXT,
+            stripe_invoice_id   TEXT,
+            created_at          TEXT    NOT NULL DEFAULT (datetime('now')),
+            paid_at             TEXT
+        );
+
+        CREATE TABLE IF NOT EXISTS transactions (
+            id                      INTEGER PRIMARY KEY AUTOINCREMENT,
+            user_id                 INTEGER NOT NULL REFERENCES users(id),
+            invoice_id              INTEGER REFERENCES invoices(id),
+            type                    TEXT    NOT NULL
+                                        CHECK(type IN ('subscription','overage','credit','refund')),
+            amount_cents            INTEGER NOT NULL,
+            currency                TEXT    NOT NULL DEFAULT 'EUR',
+            stripe_payment_intent_id TEXT,
+            description             TEXT,
+            created_at              TEXT    NOT NULL DEFAULT (datetime('now'))
+        );
+
+        CREATE TABLE IF NOT EXISTS consumption_log (
+            id          INTEGER PRIMARY KEY AUTOINCREMENT,
+            user_id     INTEGER NOT NULL REFERENCES users(id),
+            date        TEXT    NOT NULL,
+            api_calls   INTEGER NOT NULL DEFAULT 0,
+            endpoint    TEXT,
+            created_at  TEXT    NOT NULL DEFAULT (datetime('now')),
+            UNIQUE(user_id, date, endpoint)
+        );
+
+        CREATE INDEX IF NOT EXISTS idx_invoices_user     ON invoices(user_id);
+        CREATE INDEX IF NOT EXISTS idx_invoices_status   ON invoices(status);
+        CREATE INDEX IF NOT EXISTS idx_transactions_user ON transactions(user_id);
+        CREATE INDEX IF NOT EXISTS idx_consumption_user  ON consumption_log(user_id);
+        CREATE INDEX IF NOT EXISTS idx_consumption_date  ON consumption_log(date);
     """)
 
     conn.commit()
     conn.close()
     print(
-        "[billing_db] Migration complete: subscriptions + billing_events tables ready."
+        "[billing_db] Migration complete: subscriptions + billing_events + invoices + transactions + consumption_log ready."
     )
 
 
@@ -115,6 +165,51 @@ if __name__ == "__main__":
     migrate_billing_tables()
 
 
+# ──────────────────────────────────────────────────────────────
+# Model classes (documentation / type hints)
+# ──────────────────────────────────────────────────────────────
+
+
+@dataclass
+class Invoice:
+    id: Optional[int] = None
+    invoice_number: str = ""
+    user_id: int = 0
+    period_start: str = ""
+    period_end: str = ""
+    plan: str = ""
+    amount_cents: int = 0
+    currency: str = "EUR"
+    status: str = "pending"
+    pdf_path: Optional[str] = None
+    stripe_invoice_id: Optional[str] = None
+    created_at: str = ""
+    paid_at: Optional[str] = None
+
+
+@dataclass
+class Transaction:
+    id: Optional[int] = None
+    user_id: int = 0
+    invoice_id: Optional[int] = None
+    type: str = "subscription"
+    amount_cents: int = 0
+    currency: str = "EUR"
+    stripe_payment_intent_id: Optional[str] = None
+    description: Optional[str] = None
+    created_at: str = ""
+
+
+@dataclass
+class ConsumptionLog:
+    id: Optional[int] = None
+    user_id: int = 0
+    date: str = ""
+    api_calls: int = 0
+    endpoint: Optional[str] = None
+    created_at: str = ""
+
+
 # ──────────────────────────────────────────────────────────────
 # Re-exported helpers for test usage
 # (primary implementations live in api_v1/routes/billing.py)

consumption_alerts.html

@@ -0,0 +1,335 @@
+<!DOCTYPE html>
+<html lang="fr">
+<head>
+    <meta charset="UTF-8">
+    <meta name="viewport" content="width=device-width, initial-scale=1.0">
+    <title>Consommation IA — Alertes</title>
+    <style>
+        *, *::before, *::after { box-sizing: border-box; margin: 0; padding: 0; }
+        :root {
+            --green: #00c853; --green-d: #009624; --blue: #1e88e5;
+            --gold: #ffd600; --orange: #ff6d00;
+            --dark: #0d1117; --dark2: #161b22; --dark3: #21262d;
+            --text: #e6edf3; --muted: #8b949e; --border: #30363d;
+            --radius: 10px; --error: #f85149; --purple: #7c3aed;
+        }
+        body { font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif; background: var(--dark); color: var(--text); min-height: 100vh; }
+        a { color: inherit; text-decoration: none; }
+
+        .topbar { display: flex; align-items: center; justify-content: space-between; padding: 16px 28px; border-bottom: 1px solid var(--border); background: var(--dark); position: sticky; top: 0; z-index: 10; flex-wrap: wrap; gap: 10px; }
+        .topbar-title { font-size: 1.1rem; font-weight: 700; display: flex; align-items: center; gap: 10px; }
+        .topbar-title a { color: var(--muted); font-weight: 400; font-size: .9rem; }
+        .topbar-title a:hover { color: var(--text); }
+        .topbar-right { display: flex; align-items: center; gap: 12px; flex-wrap: wrap; }
+        .btn { display: inline-flex; align-items: center; gap: 6px; padding: 7px 16px; border-radius: 8px; font-size: .85rem; font-weight: 600; cursor: pointer; border: none; transition: all .2s; }
+        .btn-primary { background: var(--green); color: #000; }
+        .btn-primary:hover { background: var(--green-d); }
+        .btn-ghost { background: transparent; border: 1px solid var(--border); color: var(--text); }
+        .btn-ghost:hover { border-color: var(--muted); }
+        .btn-danger { background: rgba(248,81,73,.15); color: var(--error); border: 1px solid rgba(248,81,73,.3); }
+        .btn-danger:hover { background: rgba(248,81,73,.25); }
+        .btn-sm { padding: 5px 12px; font-size: .8rem; }
+
+        .content { padding: 28px; max-width: 1000px; margin: 0 auto; }
+
+        .section-title { font-size: 1rem; font-weight: 700; margin-bottom: 16px; display: flex; align-items: center; justify-content: space-between; }
+
+        .alert-card { background: var(--dark2); border: 1px solid var(--border); border-radius: var(--radius); padding: 18px 20px; margin-bottom: 12px; display: flex; align-items: center; gap: 16px; flex-wrap: wrap; }
+        .alert-card.inactive { opacity: .6; }
+        .alert-icon { font-size: 1.3rem; width: 36px; height: 36px; display: flex; align-items: center; justify-content: center; background: var(--dark3); border-radius: 8px; flex-shrink: 0; }
+        .alert-info { flex: 1; min-width: 200px; }
+        .alert-info .alert-name { font-weight: 700; font-size: .93rem; }
+        .alert-info .alert-desc { font-size: .82rem; color: var(--muted); margin-top: 2px; }
+        .alert-actions { display: flex; gap: 8px; flex-shrink: 0; }
+        .toggle-switch { width: 40px; height: 22px; border-radius: 11px; background: var(--border); cursor: pointer; position: relative; transition: background .2s; flex-shrink: 0; }
+        .toggle-switch.active { background: var(--green); }
+        .toggle-switch::after { content: ''; position: absolute; top: 2px; left: 2px; width: 18px; height: 18px; border-radius: 50%; background: #fff; transition: transform .2s; }
+        .toggle-switch.active::after { transform: translateX(18px); }
+
+        .empty-state { text-align: center; padding: 60px 20px; color: var(--muted); }
+        .empty-state .icon { font-size: 2.5rem; margin-bottom: 12px; }
+        .empty-state p { font-size: .9rem; margin-bottom: 16px; }
+
+        .loading { text-align: center; padding: 60px 20px; color: var(--muted); }
+        .loading .spinner { display: inline-block; width: 32px; height: 32px; border: 3px solid var(--border); border-top-color: var(--green); border-radius: 50%; animation: spin .8s linear infinite; margin-bottom: 12px; }
+        @keyframes spin { to { transform: rotate(360deg); } }
+
+        .home-btn{position:fixed;top:10px;left:10px;z-index:9999;background:linear-gradient(135deg,#00d9ff,#7b2cbf);color:#fff;border:none;border-radius:8px;padding:10px 15px;font-size:14px;cursor:pointer;text-decoration:none;display:flex;align-items:center;gap:8px;box-shadow:0 2px 10px rgba(0,217,255,0.3);transition:all 0.3s;font-family:-apple-system,BlinkMacSystemFont,sans-serif}.home-btn:hover{transform:translateY(-2px);box-shadow:0 4px 15px rgba(0,217,255,0.5)}
+
+        .modal-overlay {
+            display: none; position: fixed; top: 0; left: 0; right: 0; bottom: 0;
+            background: rgba(0,0,0,.6); z-index: 1000;
+            align-items: center; justify-content: center;
+        }
+        .modal-overlay.show { display: flex; }
+        .modal {
+            background: var(--dark2); border: 1px solid var(--border); border-radius: var(--radius);
+            padding: 24px; width: 90%; max-width: 480px; max-height: 90vh; overflow-y: auto;
+        }
+        .modal h3 { font-size: 1.05rem; margin-bottom: 20px; }
+        .form-group { margin-bottom: 16px; }
+        .form-group label { display: block; font-size: .8rem; color: var(--muted); font-weight: 600; text-transform: uppercase; letter-spacing: .4px; margin-bottom: 6px; }
+        .form-group input, .form-group select {
+            width: 100%; background: var(--dark3); border: 1px solid var(--border);
+            border-radius: 8px; padding: 9px 12px; color: var(--text); font-size: .88rem;
+            outline: none; transition: border-color .2s; font-family: inherit;
+        }
+        .form-group input:focus, .form-group select:focus { border-color: var(--green); }
+        .form-actions { display: flex; gap: 10px; margin-top: 20px; justify-content: flex-end; }
+
+        .toast {
+            position: fixed; bottom: 20px; right: 20px; z-index: 9999;
+            background: var(--dark2); border: 1px solid var(--border); border-radius: var(--radius);
+            padding: 14px 20px; font-size: .88rem; box-shadow: 0 8px 24px rgba(0,0,0,.4);
+            display: none; max-width: 400px;
+        }
+        .toast.show { display: block; animation: slideIn .3s ease; }
+        @keyframes slideIn { from { transform: translateX(100%); opacity: 0; } to { transform: translateX(0); opacity: 1; } }
+        .toast-success { border-color: var(--green); }
+        .toast-error { border-color: var(--error); }
+
+        .threshold-badge { padding: 3px 10px; border-radius: 12px; font-size: .72rem; font-weight: 700; }
+        .threshold-info { background: rgba(30,136,229,.15); color: var(--blue); }
+        .threshold-warn { background: rgba(255,214,0,.15); color: var(--gold); }
+        .threshold-danger { background: rgba(248,81,73,.15); color: var(--error); }
+    </style>
+</head>
+<body>
+<a href="/" class="home-btn" title="Retour au portail"><span style="font-size:18px">🏠</span><span>Accueil</span></a>
+
+<div class="topbar">
+    <div class="topbar-title">
+        <span>🔔 Gestion des Alertes</span>
+        <a href="/dashboard/consumption">← Dashboard</a>
+    </div>
+    <div class="topbar-right">
+        <button class="btn btn-primary btn-sm" onclick="openCreateModal()">+ Nouvelle alerte</button>
+    </div>
+</div>
+
+<div class="content">
+    <div class="section-title">
+        <span>Règles d'alerte consommation</span>
+    </div>
+
+    <div class="loading" id="loading">
+        <div class="spinner"></div>
+        <div>Chargement des alertes...</div>
+    </div>
+
+    <div id="alerts-container"></div>
+</div>
+
+<div class="modal-overlay" id="modal">
+    <div class="modal">
+        <h3 id="modal-title">Nouvelle alerte</h3>
+        <input type="hidden" id="edit-id">
+        <div class="form-group">
+            <label>Type de seuil</label>
+            <select id="form-type">
+                <option value="tokens">Tokens</option>
+                <option value="cost">Coût (cents)</option>
+            </select>
+        </div>
+        <div class="form-group">
+            <label>Valeur du seuil</label>
+            <input type="number" id="form-value" min="1" step="0.01" placeholder="1000">
+        </div>
+        <div class="form-group">
+            <label>Période</label>
+            <select id="form-period">
+                <option value="daily">Journalier</option>
+                <option value="monthly">Mensuel</option>
+            </select>
+        </div>
+        <div class="form-group">
+            <label>Email notification (optionnel)</label>
+            <input type="email" id="form-email" placeholder="admin@example.com">
+        </div>
+        <div class="form-group">
+            <label>Webhook notification (optionnel)</label>
+            <input type="url" id="form-webhook" placeholder="https://hooks.example.com/alert">
+        </div>
+        <div class="form-actions">
+            <button class="btn btn-ghost" onclick="closeModal()">Annuler</button>
+            <button class="btn btn-primary" onclick="saveAlert()">Enregistrer</button>
+        </div>
+    </div>
+</div>
+
+<div class="toast" id="toast"></div>
+
+<script>
+function showToast(msg, type) {
+    var t = document.getElementById('toast');
+    t.textContent = msg;
+    t.className = 'toast show toast-' + type;
+    setTimeout(function() { t.classList.remove('show'); }, 4000);
+}
+
+function openModal() {
+    document.getElementById('modal').classList.add('show');
+}
+
+function closeModal() {
+    document.getElementById('modal').classList.remove('show');
+    document.getElementById('edit-id').value = '';
+    document.getElementById('modal-title').textContent = 'Nouvelle alerte';
+    document.getElementById('form-type').value = 'tokens';
+    document.getElementById('form-value').value = '';
+    document.getElementById('form-period').value = 'daily';
+    document.getElementById('form-email').value = '';
+    document.getElementById('form-webhook').value = '';
+}
+
+function openCreateModal() {
+    closeModal();
+    openModal();
+}
+
+function getThresholdClass(value, type) {
+    if (type === 'tokens') {
+        if (value >= 100000) return 'threshold-danger';
+        if (value >= 50000) return 'threshold-warn';
+        return 'threshold-info';
+    }
+    if (type === 'cost') {
+        if (value >= 10000) return 'threshold-danger';
+        if (value >= 5000) return 'threshold-warn';
+        return 'threshold-info';
+    }
+    return 'threshold-info';
+}
+
+function formatThreshold(value, type) {
+    if (type === 'tokens') {
+        if (value >= 1000000) return (value/1000000).toFixed(1) + 'M tokens';
+        if (value >= 1000) return (value/1000).toFixed(1) + 'k tokens';
+        return value + ' tokens';
+    }
+    if (type === 'cost') {
+        return (value / 100).toFixed(2) + '€';
+    }
+    return value;
+}
+
+function renderAlerts(alerts) {
+    var container = document.getElementById('alerts-container');
+    if (!alerts || alerts.length === 0) {
+        container.innerHTML = '<div class="empty-state"><div class="icon">🔔</div><p>Aucune alerte configurée</p><button class="btn btn-primary" onclick="openCreateModal()">+ Créer une alerte</button></div>';
+        return;
+    }
+    var html = '';
+    alerts.forEach(function(a) {
+        var activeClass = a.is_active ? '' : 'inactive';
+        var toggleClass = a.is_active ? 'active' : '';
+        var thresholdClass = getThresholdClass(a.threshold_value, a.threshold_type);
+        html += '<div class="alert-card ' + activeClass + '" data-id="' + a.id + '">' +
+            '<div class="alert-icon">' + (a.threshold_type === 'tokens' ? '🔷' : '💰') + '</div>' +
+            '<div class="alert-info">' +
+                '<div class="alert-name">' + (a.threshold_type === 'tokens' ? 'Seuil tokens' : 'Seuil coût') + ' <span class="threshold-badge ' + thresholdClass + '">' + formatThreshold(a.threshold_value, a.threshold_type) + '</span></div>' +
+                '<div class="alert-desc">Période: ' + (a.period === 'daily' ? 'Journalier' : 'Mensuel') + (a.notify_email ? ' · 📧 ' + a.notify_email : '') + (a.notify_webhook ? ' · 🔗 webhook' : '') + '</div>' +
+            '</div>' +
+            '<div class="alert-actions">' +
+                '<div class="toggle-switch ' + toggleClass + '" onclick="toggleAlert(' + a.id + ', ' + !a.is_active + ')"></div>' +
+                '<button class="btn btn-ghost btn-sm" onclick="editAlert(' + a.id + ')">✏️</button>' +
+                '<button class="btn btn-danger btn-sm" onclick="deleteAlert(' + a.id + ')">🗑️</button>' +
+            '</div>' +
+        '</div>';
+    });
+    container.innerHTML = html;
+}
+
+async function loadAlerts() {
+    document.getElementById('loading').style.display = '';
+    try {
+        var r = await fetch('/api/v1/consumption/alerts');
+        var data = await r.json();
+        document.getElementById('loading').style.display = 'none';
+        renderAlerts(data.alerts || []);
+    } catch (e) {
+        document.getElementById('loading').innerHTML = '<div style="color:var(--error);padding:40px">❌ Erreur: ' + e.message + '</div>';
+    }
+}
+
+async function toggleAlert(id, newState) {
+    try {
+        var r = await fetch('/api/v1/consumption/alerts/' + id, {
+            method: 'PUT',
+            headers: {'Content-Type': 'application/json'},
+            body: JSON.stringify({ is_active: newState })
+        });
+        if (!r.ok) { showToast('Erreur lors de la modification', 'error'); return; }
+        showToast('Alerte ' + (newState ? 'activée' : 'désactivée'), 'success');
+        loadAlerts();
+    } catch (e) {
+        showToast('Erreur: ' + e.message, 'error');
+    }
+}
+
+function editAlert(id) {
+    var card = document.querySelector('.alert-card[data-id="' + id + '"]');
+    if (!card) return;
+    // Re-fetch from API to get full data
+    fetch('/api/v1/consumption/alerts/' + id)
+        .then(function(r) { return r.json(); })
+        .then(function(a) {
+            document.getElementById('edit-id').value = a.id;
+            document.getElementById('modal-title').textContent = 'Modifier l\'alerte #' + a.id;
+            document.getElementById('form-type').value = a.threshold_type;
+            document.getElementById('form-value').value = a.threshold_value;
+            document.getElementById('form-period').value = a.period;
+            document.getElementById('form-email').value = a.notify_email || '';
+            document.getElementById('form-webhook').value = a.notify_webhook || '';
+            openModal();
+        })
+        .catch(function(e) { showToast('Erreur: ' + e.message, 'error'); });
+}
+
+async function saveAlert() {
+    var id = document.getElementById('edit-id').value;
+    var data = {
+        client_id: 'internal',
+        threshold_type: document.getElementById('form-type').value,
+        threshold_value: parseFloat(document.getElementById('form-value').value),
+        period: document.getElementById('form-period').value,
+        notify_email: document.getElementById('form-email').value || null,
+        notify_webhook: document.getElementById('form-webhook').value || null,
+    };
+    if (!data.threshold_value || data.threshold_value <= 0) {
+        showToast('Veuillez entrer une valeur de seuil valide', 'error');
+        return;
+    }
+    try {
+        var url = id ? '/api/v1/consumption/alerts/' + id : '/api/v1/consumption/alerts';
+        var method = id ? 'PUT' : 'POST';
+        var r = await fetch(url, {
+            method: method,
+            headers: {'Content-Type': 'application/json'},
+            body: JSON.stringify(data)
+        });
+        if (!r.ok) { showToast('Erreur lors de l\'enregistrement', 'error'); return; }
+        showToast(id ? 'Alerte modifiée' : 'Alerte créée', 'success');
+        closeModal();
+        loadAlerts();
+    } catch (e) {
+        showToast('Erreur: ' + e.message, 'error');
+    }
+}
+
+async function deleteAlert(id) {
+    if (!confirm('Supprimer cette alerte ?')) return;
+    try {
+        var r = await fetch('/api/v1/consumption/alerts/' + id, { method: 'DELETE' });
+        if (!r.ok) { showToast('Erreur lors de la suppression', 'error'); return; }
+        showToast('Alerte supprimée', 'success');
+        loadAlerts();
+    } catch (e) {
+        showToast('Erreur: ' + e.message, 'error');
+    }
+}
+
+loadAlerts();
+</script>
+</body>
+</html>

consumption_dashboard.html

@@ -0,0 +1,415 @@
+<!DOCTYPE html>
+<html lang="fr">
+<head>
+    <meta charset="UTF-8">
+    <meta name="viewport" content="width=device-width, initial-scale=1.0">
+    <title>Consommation IA — Dashboard</title>
+    <script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
+    <style>
+        *, *::before, *::after { box-sizing: border-box; margin: 0; padding: 0; }
+        :root {
+            --green: #00c853; --green-d: #009624; --blue: #1e88e5;
+            --gold: #ffd600; --orange: #ff6d00;
+            --dark: #0d1117; --dark2: #161b22; --dark3: #21262d;
+            --text: #e6edf3; --muted: #8b949e; --border: #30363d;
+            --radius: 10px; --error: #f85149; --purple: #7c3aed; --cyan: #00d9ff;
+        }
+        body { font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif; background: var(--dark); color: var(--text); min-height: 100vh; }
+        a { color: inherit; text-decoration: none; }
+
+        .topbar { display: flex; align-items: center; justify-content: space-between; padding: 16px 28px; border-bottom: 1px solid var(--border); background: var(--dark); position: sticky; top: 0; z-index: 10; flex-wrap: wrap; gap: 10px; }
+        .topbar-title { font-size: 1.1rem; font-weight: 700; display: flex; align-items: center; gap: 10px; }
+        .topbar-title a { color: var(--muted); font-weight: 400; font-size: .9rem; }
+        .topbar-title a:hover { color: var(--text); }
+        .topbar-right { display: flex; align-items: center; gap: 12px; flex-wrap: wrap; }
+        .btn { display: inline-flex; align-items: center; gap: 6px; padding: 7px 16px; border-radius: 8px; font-size: .85rem; font-weight: 600; cursor: pointer; border: none; transition: all .2s; }
+        .btn-primary { background: var(--green); color: #000; }
+        .btn-primary:hover { background: var(--green-d); }
+        .btn-ghost { background: transparent; border: 1px solid var(--border); color: var(--text); }
+        .btn-ghost:hover { border-color: var(--muted); }
+        .btn-sm { padding: 5px 12px; font-size: .8rem; }
+        .btn-active { background: var(--green); color: #000; border-color: var(--green); }
+
+        .content { padding: 28px; max-width: 1400px; margin: 0 auto; }
+
+        .stats-row { display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 14px; margin-bottom: 24px; }
+        .stat-card { background: var(--dark2); border: 1px solid var(--border); border-radius: var(--radius); padding: 18px 20px; }
+        .stat-label { font-size: .78rem; color: var(--muted); text-transform: uppercase; letter-spacing: .5px; margin-bottom: 8px; display: flex; align-items: center; gap: 6px; }
+        .stat-value { font-size: 1.8rem; font-weight: 800; }
+        .stat-sub { font-size: .78rem; color: var(--muted); margin-top: 4px; }
+        .stat-warn { color: var(--gold); }
+        .stat-err { color: var(--error); }
+
+        .period-bar { display: flex; gap: 6px; margin-bottom: 20px; flex-wrap: wrap; }
+        .period-btn { padding: 6px 16px; border-radius: 20px; font-size: .82rem; font-weight: 600; cursor: pointer; border: 1px solid var(--border); background: transparent; color: var(--muted); transition: all .15s; }
+        .period-btn:hover { border-color: var(--muted); color: var(--text); }
+        .period-btn.active { background: var(--green); color: #000; border-color: var(--green); }
+
+        .charts-grid { display: grid; grid-template-columns: 1fr 1fr; gap: 16px; margin-bottom: 24px; }
+        @media (max-width: 900px) { .charts-grid { grid-template-columns: 1fr; } }
+
+        .chart-card { background: var(--dark2); border: 1px solid var(--border); border-radius: var(--radius); padding: 18px; }
+        .chart-title { font-size: .9rem; font-weight: 700; margin-bottom: 14px; color: var(--muted); }
+        .chart-container { height: 280px; position: relative; }
+
+        .alert-banner {
+            background: linear-gradient(135deg, rgba(248,81,73,.1), rgba(255,109,0,.08));
+            border: 1px solid rgba(248,81,73,.25); border-radius: var(--radius);
+            padding: 12px 18px; margin-bottom: 20px;
+            display: none; align-items: center; gap: 12px;
+        }
+        .alert-banner.visible { display: flex; }
+        .alert-banner .alert-icon { font-size: 1.3rem; }
+        .alert-banner .alert-text { flex: 1; font-size: .9rem; }
+        .alert-banner .alert-text strong { color: var(--error); }
+        .alert-banner .alert-close { cursor: pointer; font-size: 1.2rem; color: var(--muted); }
+
+        .loading { text-align: center; padding: 60px 20px; color: var(--muted); }
+        .loading .spinner { display: inline-block; width: 32px; height: 32px; border: 3px solid var(--border); border-top-color: var(--green); border-radius: 50%; animation: spin .8s linear infinite; margin-bottom: 12px; }
+        @keyframes spin { to { transform: rotate(360deg); } }
+
+        .provider-breakdown { margin-top: 12px; }
+        .provider-row { display: flex; align-items: center; gap: 12px; padding: 8px 0; border-bottom: 1px solid var(--border); }
+        .provider-row:last-child { border-bottom: none; }
+        .provider-name { flex: 1; font-weight: 600; font-size: .88rem; }
+        .provider-bar-wrap { flex: 2; height: 8px; border-radius: 4px; background: var(--border); overflow: hidden; }
+        .provider-bar-fill { height: 100%; border-radius: 4px; transition: width .5s; }
+        .provider-stats { flex: 1; text-align: right; font-size: .82rem; color: var(--muted); }
+
+        .home-btn{position:fixed;top:10px;left:10px;z-index:9999;background:linear-gradient(135deg,#00d9ff,#7b2cbf);color:#fff;border:none;border-radius:8px;padding:10px 15px;font-size:14px;cursor:pointer;text-decoration:none;display:flex;align-items:center;gap:8px;box-shadow:0 2px 10px rgba(0,217,255,0.3);transition:all 0.3s;font-family:-apple-system,BlinkMacSystemFont,sans-serif}.home-btn:hover{transform:translateY(-2px);box-shadow:0 4px 15px rgba(0,217,255,0.5)}
+    </style>
+</head>
+<body>
+<a href="/" class="home-btn" title="Retour au portail"><span style="font-size:18px">🏠</span><span>Accueil</span></a>
+
+<div class="topbar">
+    <div class="topbar-title">
+        <span>📊 Consommation IA</span>
+        <a href="/dashboard/consumption/history">Historique →</a>
+    </div>
+    <div class="topbar-right">
+        <a href="/dashboard/consumption/alerts" class="btn btn-ghost btn-sm">⚙️ Alertes</a>
+        <span id="last-refresh" style="font-size:.78rem;color:var(--muted)">—</span>
+    </div>
+</div>
+
+<div class="content">
+    <div class="alert-banner" id="alert-banner">
+        <span class="alert-icon">⚠️</span>
+        <div class="alert-text" id="alert-text"></div>
+        <span class="alert-close" onclick="this.parentElement.classList.remove('visible')">✕</span>
+    </div>
+
+    <div class="period-bar" id="period-bar">
+        <button class="period-btn" data-period="24h" onclick="setPeriod('24h')">24h</button>
+        <button class="period-btn active" data-period="7d" onclick="setPeriod('7d')">7 jours</button>
+        <button class="period-btn" data-period="30d" onclick="setPeriod('30d')">30 jours</button>
+    </div>
+
+    <div class="loading" id="loading">
+        <div class="spinner"></div>
+        <div>Chargement des données...</div>
+    </div>
+
+    <div id="dashboard-content" style="display:none">
+        <div class="stats-row" id="stats-row"></div>
+
+        <div class="charts-grid">
+            <div class="chart-card">
+                <div class="chart-title">🔷 Tokens par jour</div>
+                <div class="chart-container"><canvas id="tokensChart"></canvas></div>
+            </div>
+            <div class="chart-card">
+                <div class="chart-title">💰 Coût par jour (€)</div>
+                <div class="chart-container"><canvas id="costChart"></canvas></div>
+            </div>
+            <div class="chart-card">
+                <div class="chart-title">🏢 Répartition par provider</div>
+                <div class="chart-container"><canvas id="providerChart"></canvas></div>
+            </div>
+            <div class="chart-card">
+                <div class="chart-title">📞 Appels par jour</div>
+                <div class="chart-container"><canvas id="callsChart"></canvas></div>
+            </div>
+        </div>
+    </div>
+</div>
+
+<script>
+var currentPeriod = '7d';
+var statsData = null;
+
+function formatDate(isoStr) {
+    var d = new Date(isoStr);
+    return d.toLocaleDateString('fr-FR', {day:'2-digit', month:'short'});
+}
+
+function formatNumber(n) {
+    if (n >= 1000000) return (n/1000000).toFixed(1) + 'M';
+    if (n >= 1000) return (n/1000).toFixed(1) + 'k';
+    return n.toLocaleString('fr-FR');
+}
+
+function getDateRange(period) {
+    var now = new Date();
+    var start = new Date(now);
+    if (period === '24h') start.setDate(start.getDate() - 1);
+    else if (period === '7d') start.setDate(start.getDate() - 7);
+    else if (period === '30d') start.setDate(start.getDate() - 30);
+    return {
+        start: start.toISOString().split('T')[0],
+        end: now.toISOString().split('T')[0]
+    };
+}
+
+function setPeriod(period) {
+    currentPeriod = period;
+    document.querySelectorAll('.period-btn').forEach(function(b) {
+        b.classList.toggle('active', b.dataset.period === period);
+    });
+    loadData();
+}
+
+function checkAlerts(totals) {
+    var banner = document.getElementById('alert-banner');
+    var text = document.getElementById('alert-text');
+
+    var dailyTokens = 0;
+    var dailyCost = 0;
+    if (statsData && statsData.by_day && statsData.by_day.length > 0) {
+        var today = statsData.by_day[0];
+        dailyTokens = today.tokens || 0;
+        dailyCost = (today.cost_cents || 0) / 100;
+    }
+
+    if (dailyTokens > 0) {
+        var tokensPct = 100;
+        var costPct = 100;
+        banner.classList.add('visible');
+        if (dailyTokens > 100000) {
+            text.innerHTML = '<strong>⚠️ Seuil critique</strong> — ' + formatNumber(dailyTokens) + ' tokens aujourd\'hui (dépassement >100k)';
+        } else if (dailyCost > 5) {
+            text.innerHTML = '<strong>⚠️ Alerte coût</strong> — ' + dailyCost.toFixed(2) + '€ aujourd\'hui (seuil >5€)';
+        } else {
+            banner.classList.remove('visible');
+        }
+    }
+}
+
+function renderStats(totals) {
+    var html = '';
+    var cards = [
+        { label: 'Requêtes totales', value: formatNumber(totals.calls_count || 0), sub: 'période sélectionnée' },
+        { label: 'Tokens totaux', value: formatNumber(totals.total_tokens || 0), sub: 'entrée + sortie' },
+        { label: 'Coût estimé', value: (totals.total_cost_cents / 100).toFixed(2) + '€', sub: 'coût total période', cls: totals.total_cost_cents > 500 ? 'stat-warn' : '' },
+        { label: 'Latence moyenne', value: (totals.avg_latency_ms || 0).toFixed(0) + 'ms', sub: 'temps de réponse' },
+        { label: 'Erreurs', value: totals.error_count || 0, sub: 'requêtes échouées', cls: totals.error_count > 0 ? 'stat-err' : '' },
+    ];
+    cards.forEach(function(c) {
+        html += '<div class="stat-card"><div class="stat-label">' + c.label + '</div><div class="stat-value ' + (c.cls || '') + '">' + c.value + '</div><div class="stat-sub">' + c.sub + '</div></div>';
+    });
+    document.getElementById('stats-row').innerHTML = html;
+}
+
+function renderTokensChart(byDay) {
+    var ctx = document.getElementById('tokensChart').getContext('2d');
+    var labels = byDay.map(function(d) { return formatDate(d.date); }).reverse();
+    var data = byDay.map(function(d) { return d.tokens; }).reverse();
+    new Chart(ctx, {
+        type: 'line',
+        data: {
+            labels: labels,
+            datasets: [{
+                label: 'Tokens',
+                data: data,
+                borderColor: '#00c853',
+                backgroundColor: 'rgba(0,200,83,0.1)',
+                fill: true,
+                tension: 0.3,
+                pointRadius: 3,
+            }]
+        },
+        options: {
+            responsive: true,
+            maintainAspectRatio: false,
+            plugins: {
+                legend: { display: false }
+            },
+            scales: {
+                y: {
+                    beginAtZero: true,
+                    grid: { color: 'rgba(255,255,255,0.05)' },
+                    ticks: { color: '#8b949e', callback: function(v) { return formatNumber(v); } }
+                },
+                x: {
+                    grid: { display: false },
+                    ticks: { color: '#8b949e', maxTicksLimit: 10 }
+                }
+            }
+        }
+    });
+}
+
+function renderCostChart(byDay) {
+    var ctx = document.getElementById('costChart').getContext('2d');
+    var labels = byDay.map(function(d) { return formatDate(d.date); }).reverse();
+    var data = byDay.map(function(d) { return (d.cost_cents / 100).toFixed(2); }).reverse();
+    new Chart(ctx, {
+        type: 'bar',
+        data: {
+            labels: labels,
+            datasets: [{
+                label: 'Coût (€)',
+                data: data,
+                backgroundColor: 'rgba(30,136,229,0.6)',
+                borderColor: '#1e88e5',
+                borderWidth: 1
+            }]
+        },
+        options: {
+            responsive: true,
+            maintainAspectRatio: false,
+            plugins: {
+                legend: { display: false }
+            },
+            scales: {
+                y: {
+                    beginAtZero: true,
+                    grid: { color: 'rgba(255,255,255,0.05)' },
+                    ticks: { color: '#8b949e', callback: function(v) { return v + '€'; } }
+                },
+                x: {
+                    grid: { display: false },
+                    ticks: { color: '#8b949e', maxTicksLimit: 10 }
+                }
+            }
+        }
+    });
+}
+
+function renderProviderChart(byProvider) {
+    var ctx = document.getElementById('providerChart').getContext('2d');
+    var labels = byProvider.map(function(p) { return p.provider; });
+    var data = byProvider.map(function(p) { return p.cost_cents / 100; });
+    var colors = ['#00c853', '#1e88e5', '#ffd600', '#7c3aed', '#ff6d00', '#f85149'];
+    new Chart(ctx, {
+        type: 'doughnut',
+        data: {
+            labels: labels,
+            datasets: [{
+                data: data,
+                backgroundColor: colors.slice(0, labels.length),
+                borderWidth: 0
+            }]
+        },
+        options: {
+            responsive: true,
+            maintainAspectRatio: false,
+            plugins: {
+                legend: {
+                    position: 'right',
+                    labels: { color: '#8b949e', padding: 12, font: { size: 11 } }
+                },
+                tooltip: {
+                    callbacks: {
+                        label: function(ctx) {
+                            return ctx.label + ': ' + ctx.parsed.toFixed(2) + '€';
+                        }
+                    }
+                }
+            }
+        }
+    });
+}
+
+function renderCallsChart(byDay) {
+    var ctx = document.getElementById('callsChart').getContext('2d');
+    var labels = byDay.map(function(d) { return formatDate(d.date); }).reverse();
+    var data = byDay.map(function(d) { return d.calls; }).reverse();
+    new Chart(ctx, {
+        type: 'bar',
+        data: {
+            labels: labels,
+            datasets: [{
+                label: 'Appels',
+                data: data,
+                backgroundColor: 'rgba(124,58,237,0.5)',
+                borderColor: '#7c3aed',
+                borderWidth: 1,
+                borderRadius: 3
+            }]
+        },
+        options: {
+            responsive: true,
+            maintainAspectRatio: false,
+            plugins: {
+                legend: { display: false }
+            },
+            scales: {
+                y: {
+                    beginAtZero: true,
+                    grid: { color: 'rgba(255,255,255,0.05)' },
+                    ticks: { color: '#8b949e' }
+                },
+                x: {
+                    grid: { display: false },
+                    ticks: { color: '#8b949e', maxTicksLimit: 10 }
+                }
+            }
+        }
+    });
+}
+
+async function loadData() {
+    var range = getDateRange(currentPeriod);
+    document.getElementById('loading').style.display = '';
+    document.getElementById('dashboard-content').style.display = 'none';
+
+    try {
+        var url = '/api/v1/consumption/stats?client_id=internal&start_date=' + range.start + '&end_date=' + range.end;
+        var r = await fetch(url);
+        statsData = await r.json();
+
+        document.getElementById('last-refresh').textContent = '🔄 ' + new Date().toLocaleTimeString('fr-FR');
+
+        document.getElementById('loading').style.display = 'none';
+        document.getElementById('dashboard-content').style.display = '';
+
+        var totals = statsData.totals || {};
+        var byDay = statsData.by_day || [];
+        var byProvider = statsData.by_provider || [];
+
+        renderStats(totals);
+        checkAlerts(totals);
+
+        if (byDay.length > 0) {
+            renderTokensChart(byDay);
+            renderCostChart(byDay);
+            renderCallsChart(byDay);
+        } else {
+            ['tokensChart','costChart','callsChart'].forEach(function(id) {
+                var ctx = document.getElementById(id).getContext('2d');
+                new Chart(ctx, {
+                    type: 'line',
+                    data: { labels: ['Aucune donnée'], datasets: [{ data: [0], backgroundColor: 'rgba(139,148,158,0.2)', borderColor: '#8b949e' }] },
+                    options: { responsive: true, maintainAspectRatio: false, plugins: { legend: { display: false } } }
+                });
+            });
+        }
+
+        if (byProvider.length > 0) {
+            renderProviderChart(byProvider);
+        } else {
+            var ctx = document.getElementById('providerChart').getContext('2d');
+            new Chart(ctx, { type: 'doughnut', data: { labels: ['Aucune donnée'], datasets: [{ data: [1], backgroundColor: ['#8b949e'], borderWidth: 0 }] }, options: { responsive: true, maintainAspectRatio: false, plugins: { legend: { labels: { color: '#8b949e' } } } } });
+        }
+    } catch (e) {
+        document.getElementById('loading').innerHTML = '<div style="color:var(--error);padding:40px">❌ Erreur de chargement: ' + e.message + '</div>';
+    }
+}
+
+loadData();
+setInterval(loadData, 30000);
+</script>
+</body>
+</html>

consumption_history.html

@@ -0,0 +1,327 @@
+<!DOCTYPE html>
+<html lang="fr">
+<head>
+    <meta charset="UTF-8">
+    <meta name="viewport" content="width=device-width, initial-scale=1.0">
+    <title>Consommation IA — Historique</title>
+    <style>
+        *, *::before, *::after { box-sizing: border-box; margin: 0; padding: 0; }
+        :root {
+            --green: #00c853; --green-d: #009624; --blue: #1e88e5;
+            --gold: #ffd600; --orange: #ff6d00;
+            --dark: #0d1117; --dark2: #161b22; --dark3: #21262d;
+            --text: #e6edf3; --muted: #8b949e; --border: #30363d;
+            --radius: 10px; --error: #f85149; --purple: #7c3aed;
+        }
+        body { font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif; background: var(--dark); color: var(--text); min-height: 100vh; }
+        a { color: inherit; text-decoration: none; }
+
+        .topbar { display: flex; align-items: center; justify-content: space-between; padding: 16px 28px; border-bottom: 1px solid var(--border); background: var(--dark); position: sticky; top: 0; z-index: 10; flex-wrap: wrap; gap: 10px; }
+        .topbar-title { font-size: 1.1rem; font-weight: 700; display: flex; align-items: center; gap: 10px; }
+        .topbar-title a { color: var(--muted); font-weight: 400; font-size: .9rem; }
+        .topbar-title a:hover { color: var(--text); }
+        .topbar-right { display: flex; align-items: center; gap: 12px; flex-wrap: wrap; }
+        .btn { display: inline-flex; align-items: center; gap: 6px; padding: 7px 16px; border-radius: 8px; font-size: .85rem; font-weight: 600; cursor: pointer; border: none; transition: all .2s; }
+        .btn-primary { background: var(--green); color: #000; }
+        .btn-primary:hover { background: var(--green-d); }
+        .btn-ghost { background: transparent; border: 1px solid var(--border); color: var(--text); }
+        .btn-ghost:hover { border-color: var(--muted); }
+        .btn-sm { padding: 5px 12px; font-size: .8rem; }
+
+        .content { padding: 28px; max-width: 1400px; margin: 0 auto; }
+
+        .filter-bar { display: flex; gap: 12px; margin-bottom: 20px; flex-wrap: wrap; align-items: center; }
+        .filter-group { display: flex; flex-direction: column; gap: 4px; }
+        .filter-group label { font-size: .72rem; color: var(--muted); text-transform: uppercase; letter-spacing: .5px; font-weight: 600; }
+        .filter-group select, .filter-group input {
+            background: var(--dark3); border: 1px solid var(--border); border-radius: 8px;
+            padding: 8px 12px; color: var(--text); font-size: .85rem; outline: none;
+            transition: border-color .2s; font-family: inherit;
+        }
+        .filter-group select:focus, .filter-group input:focus { border-color: var(--green); }
+
+        .table-card { background: var(--dark2); border: 1px solid var(--border); border-radius: var(--radius); overflow: hidden; margin-bottom: 20px; }
+        table { width: 100%; border-collapse: collapse; }
+        thead th { padding: 10px 14px; font-size: .75rem; text-transform: uppercase; letter-spacing: .5px; color: var(--muted); text-align: left; border-bottom: 1px solid var(--border); background: var(--dark3); white-space: nowrap; cursor: pointer; user-select: none; }
+        thead th:hover { color: var(--text); }
+        tbody tr { border-bottom: 1px solid var(--border); transition: background .15s; }
+        tbody tr:last-child { border-bottom: none; }
+        tbody tr:hover { background: var(--dark3); }
+        tbody td { padding: 10px 14px; font-size: .85rem; white-space: nowrap; }
+        .status-badge { padding: 2px 8px; border-radius: 10px; font-size: .72rem; font-weight: 700; }
+        .status-success { background: rgba(0,200,83,.15); color: var(--green); }
+        .status-error { background: rgba(248,81,73,.15); color: var(--error); }
+
+        .pagination { display: flex; align-items: center; justify-content: center; gap: 8px; padding: 16px; flex-wrap: wrap; }
+        .page-btn { padding: 6px 14px; border-radius: 6px; font-size: .85rem; cursor: pointer; border: 1px solid var(--border); background: transparent; color: var(--muted); transition: all .15s; }
+        .page-btn:hover { border-color: var(--muted); color: var(--text); }
+        .page-btn.active { background: var(--green); color: #000; border-color: var(--green); }
+        .page-btn:disabled { opacity: .4; cursor: default; }
+        .page-info { font-size: .82rem; color: var(--muted); }
+
+        .loading { text-align: center; padding: 60px 20px; color: var(--muted); }
+        .loading .spinner { display: inline-block; width: 32px; height: 32px; border: 3px solid var(--border); border-top-color: var(--green); border-radius: 50%; animation: spin .8s linear infinite; margin-bottom: 12px; }
+        @keyframes spin { to { transform: rotate(360deg); } }
+
+        .home-btn{position:fixed;top:10px;left:10px;z-index:9999;background:linear-gradient(135deg,#00d9ff,#7b2cbf);color:#fff;border:none;border-radius:8px;padding:10px 15px;font-size:14px;cursor:pointer;text-decoration:none;display:flex;align-items:center;gap:8px;box-shadow:0 2px 10px rgba(0,217,255,0.3);transition:all 0.3s;font-family:-apple-system,BlinkMacSystemFont,sans-serif}.home-btn:hover{transform:translateY(-2px);box-shadow:0 4px 15px rgba(0,217,255,0.5)}
+
+        .empty-state { text-align: center; padding: 60px 20px; color: var(--muted); }
+        .empty-state .icon { font-size: 2.5rem; margin-bottom: 12px; }
+        .empty-state p { font-size: .9rem; }
+
+        .toast {
+            position: fixed; bottom: 20px; right: 20px; z-index: 9999;
+            background: var(--dark2); border: 1px solid var(--border); border-radius: var(--radius);
+            padding: 14px 20px; font-size: .88rem; box-shadow: 0 8px 24px rgba(0,0,0,.4);
+            display: none; max-width: 400px;
+        }
+        .toast.show { display: block; animation: slideIn .3s ease; }
+        @keyframes slideIn { from { transform: translateX(100%); opacity: 0; } to { transform: translateX(0); opacity: 1; } }
+        .toast-success { border-color: var(--green); }
+        .toast-error { border-color: var(--error); }
+    </style>
+</head>
+<body>
+<a href="/" class="home-btn" title="Retour au portail"><span style="font-size:18px">🏠</span><span>Accueil</span></a>
+
+<div class="topbar">
+    <div class="topbar-title">
+        <span>📋 Historique Consommation</span>
+        <a href="/dashboard/consumption">← Dashboard</a>
+    </div>
+    <div class="topbar-right">
+        <button class="btn btn-ghost btn-sm" onclick="exportCSV()">📥 Export CSV</button>
+        <button class="btn btn-ghost btn-sm" onclick="loadHistory()">🔄 Rafraîchir</button>
+    </div>
+</div>
+
+<div class="content">
+    <div class="filter-bar">
+        <div class="filter-group">
+            <label>Provider</label>
+            <select id="filter-provider" onchange="loadHistory()">
+                <option value="">Tous</option>
+                <option value="openai">OpenAI</option>
+                <option value="anthropic">Anthropic</option>
+                <option value="google">Google</option>
+                <option value="mistral">Mistral</option>
+                <option value="deepseek">DeepSeek</option>
+                <option value="meta">Meta</option>
+            </select>
+        </div>
+        <div class="filter-group">
+            <label>Statut</label>
+            <select id="filter-status" onchange="loadHistory()">
+                <option value="">Tous</option>
+                <option value="success">Succès</option>
+                <option value="error">Erreur</option>
+            </select>
+        </div>
+        <div class="filter-group">
+            <label>Du</label>
+            <input type="date" id="filter-date-from" onchange="loadHistory()">
+        </div>
+        <div class="filter-group">
+            <label>Au</label>
+            <input type="date" id="filter-date-to" onchange="loadHistory()">
+        </div>
+        <div class="filter-group" style="align-self:flex-end">
+            <button class="btn btn-ghost btn-sm" onclick="document.getElementById('filter-provider').value='';document.getElementById('filter-status').value='';document.getElementById('filter-date-from').value='';document.getElementById('filter-date-to').value='';loadHistory()">✕ Réinitialiser</button>
+        </div>
+    </div>
+
+    <div class="loading" id="loading">
+        <div class="spinner"></div>
+        <div>Chargement de l'historique...</div>
+    </div>
+
+    <div class="table-card" id="table-wrap" style="display:none">
+        <div style="overflow-x:auto">
+            <table>
+                <thead>
+                    <tr>
+                        <th onclick="sortBy('created_at')">Date ⬍</th>
+                        <th onclick="sortBy('provider')">Provider ⬍</th>
+                        <th onclick="sortBy('model')">Modèle ⬍</th>
+                        <th onclick="sortBy('tokens_in')">Tokens In ⬍</th>
+                        <th onclick="sortBy('tokens_out')">Tokens Out ⬍</th>
+                        <th onclick="sortBy('tokens_total')">Total ⬍</th>
+                        <th onclick="sortBy('cost_cents')">Coût ⬍</th>
+                        <th onclick="sortBy('latency_ms')">Latence ⬍</th>
+                        <th onclick="sortBy('status')">Statut ⬍</th>
+                    </tr>
+                </thead>
+                <tbody id="history-body"></tbody>
+            </table>
+        </div>
+        <div class="pagination" id="pagination"></div>
+    </div>
+
+    <div class="empty-state" id="empty-state" style="display:none">
+        <div class="icon">📭</div>
+        <p>Aucune donnée de consommation pour cette période.</p>
+    </div>
+</div>
+
+<div class="toast" id="toast"></div>
+
+<script>
+var currentPage = 1;
+var totalPages = 0;
+var totalItems = 0;
+var historyData = [];
+var sortField = 'created_at';
+var sortDir = 'desc';
+
+function formatDate(isoStr) {
+    var d = new Date(isoStr);
+    return d.toLocaleDateString('fr-FR', {day:'2-digit', month:'short', year:'numeric'}) + ' ' +
+           d.toLocaleTimeString('fr-FR', {hour:'2-digit', minute:'2-digit'});
+}
+
+function formatNumber(n) {
+    if (n >= 1000000) return (n/1000000).toFixed(1) + 'M';
+    if (n >= 1000) return (n/1000).toFixed(1) + 'k';
+    return n.toLocaleString('fr-FR');
+}
+
+function showToast(msg, type) {
+    var t = document.getElementById('toast');
+    t.textContent = msg;
+    t.className = 'toast show toast-' + type;
+    setTimeout(function() { t.classList.remove('show'); }, 4000);
+}
+
+function sortBy(field) {
+    if (sortField === field) {
+        sortDir = sortDir === 'asc' ? 'desc' : 'asc';
+    } else {
+        sortField = field;
+        sortDir = 'desc';
+    }
+    renderTable();
+}
+
+function renderTable() {
+    var tbody = document.getElementById('history-body');
+    var sorted = [...historyData].sort(function(a, b) {
+        var va = a[sortField], vb = b[sortField];
+        if (typeof va === 'string') va = va.toLowerCase();
+        if (typeof vb === 'string') vb = vb.toLowerCase();
+        if (va < vb) return sortDir === 'asc' ? -1 : 1;
+        if (va > vb) return sortDir === 'asc' ? 1 : -1;
+        return 0;
+    });
+    var html = '';
+    sorted.forEach(function(r) {
+        var cost = ((r.cost_cents || 0) / 100);
+        var statusClass = r.status === 'success' ? 'status-success' : 'status-error';
+        html += '<tr>' +
+            '<td>' + formatDate(r.created_at) + '</td>' +
+            '<td>' + (r.provider || '—') + '</td>' +
+            '<td>' + (r.model || '—') + '</td>' +
+            '<td>' + formatNumber(r.tokens_in || 0) + '</td>' +
+            '<td>' + formatNumber(r.tokens_out || 0) + '</td>' +
+            '<td><strong>' + formatNumber(r.tokens_total || 0) + '</strong></td>' +
+            '<td>' + cost.toFixed(4) + '€</td>' +
+            '<td>' + (r.latency_ms ? r.latency_ms + 'ms' : '—') + '</td>' +
+            '<td><span class="status-badge ' + statusClass + '">' + (r.status || '—') + '</span></td>' +
+            '</tr>';
+    });
+    if (!html) {
+        html = '<tr><td colspan="9" style="text-align:center;padding:40px;color:var(--muted)">Aucun résultat</td></tr>';
+    }
+    tbody.innerHTML = html;
+}
+
+function renderPagination() {
+    var el = document.getElementById('pagination');
+    if (totalPages <= 1) { el.innerHTML = ''; return; }
+    var html = '<span class="page-info">Page ' + currentPage + ' / ' + totalPages + ' (' + totalItems + ' entrées)</span>';
+    html += '<button class="page-btn" onclick="goPage(1)" ' + (currentPage <= 1 ? 'disabled' : '') + '>«</button>';
+    html += '<button class="page-btn" onclick="goPage(' + (currentPage - 1) + ')" ' + (currentPage <= 1 ? 'disabled' : '') + '>‹</button>';
+    var start = Math.max(1, currentPage - 2);
+    var end = Math.min(totalPages, currentPage + 2);
+    for (var i = start; i <= end; i++) {
+        html += '<button class="page-btn' + (i === currentPage ? ' active' : '') + '" onclick="goPage(' + i + ')">' + i + '</button>';
+    }
+    html += '<button class="page-btn" onclick="goPage(' + (currentPage + 1) + ')" ' + (currentPage >= totalPages ? 'disabled' : '') + '>›</button>';
+    html += '<button class="page-btn" onclick="goPage(' + totalPages + ')" ' + (currentPage >= totalPages ? 'disabled' : '') + '>»</button>';
+    el.innerHTML = html;
+}
+
+function goPage(page) {
+    if (page < 1 || page > totalPages) return;
+    currentPage = page;
+    loadHistory();
+}
+
+async function loadHistory() {
+    document.getElementById('loading').style.display = '';
+    document.getElementById('table-wrap').style.display = 'none';
+    document.getElementById('empty-state').style.display = 'none';
+
+    var params = 'client_id=internal&page=' + currentPage + '&per_page=25';
+    var provider = document.getElementById('filter-provider').value;
+    var status = document.getElementById('filter-status').value;
+    var dateFrom = document.getElementById('filter-date-from').value;
+    var dateTo = document.getElementById('filter-date-to').value;
+    if (provider) params += '&provider=' + encodeURIComponent(provider);
+    if (dateFrom) params += '&start_date=' + dateFrom;
+    if (dateTo) params += '&end_date=' + dateTo;
+
+    try {
+        var r = await fetch('/api/v1/consumption/history?' + params);
+        var data = await r.json();
+        var items = data.history || [];
+        totalItems = data.total || 0;
+        totalPages = data.total_pages || 0;
+
+        document.getElementById('loading').style.display = 'none';
+
+        if (items.length === 0) {
+            document.getElementById('empty-state').style.display = '';
+            return;
+        }
+
+        document.getElementById('table-wrap').style.display = '';
+        historyData = items;
+        renderTable();
+        renderPagination();
+    } catch (e) {
+        document.getElementById('loading').innerHTML = '<div style="color:var(--error);padding:40px">❌ Erreur: ' + e.message + '</div>';
+    }
+}
+
+function exportCSV() {
+    if (!historyData || historyData.length === 0) {
+        showToast('Aucune donnée à exporter', 'error');
+        return;
+    }
+    var headers = ['Date','Provider','Modèle','Tokens In','Tokens Out','Tokens Total','Coût (€)','Latence (ms)','Statut'];
+    var rows = historyData.map(function(r) {
+        return [
+            r.created_at || '',
+            r.provider || '',
+            r.model || '',
+            r.tokens_in || 0,
+            r.tokens_out || 0,
+            r.tokens_total || 0,
+            ((r.cost_cents || 0) / 100).toFixed(4),
+            r.latency_ms || '',
+            r.status || ''
+        ].join(',');
+    });
+    var csv = '\uFEFF' + headers.join(',') + '\n' + rows.join('\n');
+    var blob = new Blob([csv], { type: 'text/csv;charset=utf-8;' });
+    var link = document.createElement('a');
+    link.href = URL.createObjectURL(blob);
+    link.download = 'consommation_ia_' + new Date().toISOString().split('T')[0] + '.csv';
+    link.click();
+    showToast('CSV téléchargé', 'success');
+}
+
+loadHistory();
+</script>
+</body>
+</html>

dashboard_saas.html

@@ -259,6 +259,7 @@
     <a class="nav-item" id="nav-history" href="#history" onclick="showSection('history',this)"><span class="icon">📅</span> Historique <span class="plan-lock" id="lock-hist"></span></a>
     <a class="nav-item" id="nav-export" href="#export" onclick="showSection('export',this)"><span class="icon">📤</span> Export CSV <span class="plan-lock" id="lock-export"></span></a>
 
+    <a class="nav-item" id="nav-metrics" href="#metrics" onclick="showSection('metrics',this)"><span class="icon">📈</span> Métriques</a>
     <div class="nav-section">Paramètres</div>
     <a class="nav-item" id="nav-telegram" href="#telegram" onclick="showSection('telegram',this)"><span class="icon">📱</span> Alertes Telegram <span class="plan-lock" id="lock-tg"></span></a>
     <a class="nav-item" id="nav-api-token" href="#api-token" onclick="showSection('api-token',this)"><span class="icon">⚡</span> API Token <span class="plan-lock" id="lock-api"></span></a>
@@ -753,11 +754,59 @@
             </div>
         </div>
 
+<!-- ═══════════════════════════════════════════════════════ METRICS -->
+        <div id="section-metrics" class="dashboard-section" style="display:none">
+            <div class="section-header">
+                <h2>📈 Métriques de performance</h2>
+                <div style="display:flex;gap:8px;align-items:center">
+                    <select id="metrics-days" style="background:var(--dark3);color:var(--text);border:1px solid var(--border);border-radius:6px;padding:4px 10px;font-size:.85rem" onchange="loadMetrics()">
+                        <option value="7">7 jours</option>
+                        <option value="30" selected>30 jours</option>
+                        <option value="90">90 jours</option>
+                        <option value="365">365 jours</option>
+                    </select>
+                    <button class="btn btn-sm" onclick="loadMetrics()" style="padding:4px 14px;font-size:.85rem">🔄 Rafraîchir</button>
+                </div>
+            </div>
+
+            <!-- KPI cards -->
+            <div class="stats-grid" id="metrics-kpis" style="margin-bottom:20px">
+                <div class="stat-card"><div class="stat-label">Total paris</div><div class="stat-value" id="m-total-bets">—</div></div>
+                <div class="stat-card"><div class="stat-label">Précision</div><div class="stat-value" id="m-precision" style="color:var(--green)">—</div></div>
+                <div class="stat-card"><div class="stat-label">ROI</div><div class="stat-value" id="m-roi">—</div></div>
+                <div class="stat-card"><div class="stat-label">Mise totale</div><div class="stat-value" id="m-mise">—</div></div>
+                <div class="stat-card"><div class="stat-label">Gain total</div><div class="stat-value" id="m-gain">—</div></div>
+                <div class="stat-card"><div class="stat-label">Prédictions ML</div><div class="stat-value" id="m-ml-preds">—</div></div>
+                <div class="stat-card"><div class="stat-label">Value Bets ML</div><div class="stat-value" id="m-value-bets">—</div></div>
+                <div class="stat-card"><div class="stat-label">Prob. Top-3 moy.</div><div class="stat-value" id="m-prob-top3">—</div></div>
+            </div>
+
+            <!-- Charts row -->
+            <div style="display:grid;grid-template-columns:1fr 1fr;gap:16px;margin-bottom:16px">
+                <div class="form-card" style="padding:16px">
+                    <h3 style="font-size:.9rem;margin-bottom:12px">📊 ROI & Précision quotidiens</h3>
+                    <canvas id="chart-roi-daily" height="200"></canvas>
+                </div>
+                <div class="form-card" style="padding:16px">
+                    <h3 style="font-size:.9rem;margin-bottom:12px">💰 Cumul gains vs mises</h3>
+                    <canvas id="chart-cumul" height="200"></canvas>
+                </div>
+            </div>
+
+            <!-- Daily stats table -->
+            <div class="form-card">
+                <h3 style="font-size:.9rem;margin-bottom:12px">📋 Détail quotidien</h3>
+                <div id="metrics-table-wrap" style="overflow-x:auto">
+                    <div class="loader-row"><div class="spinner"></div> Chargement…</div>
+                </div>
+            </div>
+        </div>
     </div><!-- .content -->
 </div><!-- .main -->
 
 <div id="toast"></div>
 
+<script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.0/dist/chart.umd.min.js"></script>
 <script>
 const API = '/api/v1';
 let currentUser = null;
@@ -793,7 +842,11 @@ function logout() {
     location.href = '/login';
 }
 
+// ⚠️ TEST_MODE — mettre false pour réactiver les restrictions de plan
+const TEST_MODE = true;
+
 function planLevel(plan) {
+    if (TEST_MODE) return 2; // pro level pour tous
     return { free: 0, premium: 1, pro: 2 }[plan] || 0;
 }
 
@@ -830,6 +883,7 @@ const SECTION_TITLES = {
     'api-token': 'API Token',
     'webhook': 'Webhook',
     'multi-account': 'Multi-compte',
+    'metrics': 'Métriques de performance',
 };
 
 function showSection(name, navEl) {
@@ -856,6 +910,7 @@ function onSectionShow(name) {
     if (name === 'api-token' && planAllows(currentPlan, 'premium')) loadApiToken();
     if (name === 'webhook' && planAllows(currentPlan, 'pro')) loadWebhook();
     if (name === 'multi-account' && planAllows(currentPlan, 'pro')) loadMultiAccount();
+    if (name === 'metrics') loadMetrics();
 }
 
 // ────────────────────────────────────────────────────────
@@ -1525,6 +1580,7 @@ function initNavFromHash() {
         'api-token': 'nav-api-token',
         'webhook': 'nav-webhook',
         'multi-account': 'nav-multi-account',
+            'metrics': 'nav-metrics',
     };
     if (hash && sectionMap[hash]) {
         setTimeout(() => {
@@ -1545,6 +1601,140 @@ window.showSection = function(name, navEl) {
     return _origShowSection(name, navEl);
 };
 
+
+// ────────────────────────────────────────────────────────
+// Métriques
+// ────────────────────────────────────────────────────────
+let chartRoiDaily = null;
+let chartCumul = null;
+
+async function loadMetrics() {
+    const days = document.getElementById('metrics-days')?.value || 30;
+    const data = await fetchJson(`${API}/metrics?days=${days}`);
+    if (!data) return;
+
+    // KPIs
+    const bm = data.bet_metrics || {};
+    const ml = data.ml_metrics || {};
+    setText('m-total-bets', bm.available ? bm.total_bets : '—');
+    setText('m-precision', bm.available ? bm.precision_pct + ' %' : '—');
+    const roi = bm.available ? bm.roi_pct : null;
+    const roiEl = document.getElementById('m-roi');
+    if (roiEl) {
+        roiEl.textContent = roi !== null ? roi + ' %' : '—';
+        roiEl.style.color = roi > 0 ? 'var(--green)' : roi < 0 ? '#f44' : 'var(--text)';
+    }
+    setText('m-mise', bm.available ? bm.mise_totale + ' €' : '—');
+    setText('m-gain', bm.available ? bm.gain_total + ' €' : '—');
+    setText('m-ml-preds', ml.available ? ml.total_predictions : '—');
+    setText('m-value-bets', ml.available ? ml.value_bets : '—');
+    setText('m-prob-top3', ml.available ? (ml.avg_prob_top3 * 100).toFixed(1) + ' %' : '—');
+
+    // Daily charts
+    const daily = data.daily || [];
+    const labels = daily.map(r => r.date ? r.date.slice(5) : '').reverse();
+    const roiArr = daily.map(r => r.roi_pct || 0).reverse();
+    const precArr = daily.map(r => r.precision_pct || 0).reverse();
+    const gainArr = daily.map(r => r.gain_total || 0).reverse();
+    const miseArr = daily.map(r => r.mise_totale || 0).reverse();
+
+    // Cumul gains
+    const cumulGain = gainArr.reduce((acc, v, i) => { acc.push((acc[i-1]||0) + v); return acc; }, []);
+    const cumulMise = miseArr.reduce((acc, v, i) => { acc.push((acc[i-1]||0) + v); return acc; }, []);
+
+    renderChartRoi(labels, roiArr, precArr);
+    renderChartCumul(labels, cumulGain, cumulMise);
+
+    // Table
+    renderMetricsTable(daily);
+}
+
+function setText(id, val) {
+    const el = document.getElementById(id);
+    if (el) el.textContent = val;
+}
+
+function renderChartRoi(labels, roiArr, precArr) {
+    const ctx = document.getElementById('chart-roi-daily');
+    if (!ctx) return;
+    if (chartRoiDaily) chartRoiDaily.destroy();
+    chartRoiDaily = new Chart(ctx, {
+        type: 'bar',
+        data: {
+            labels,
+            datasets: [
+                { label: 'ROI %', data: roiArr, backgroundColor: roiArr.map(v => v >= 0 ? 'rgba(0,200,83,.6)' : 'rgba(244,67,54,.6)'), yAxisID: 'y' },
+                { label: 'Précision %', data: precArr, type: 'line', borderColor: '#ffd600', backgroundColor: 'transparent', tension: 0.3, yAxisID: 'y2', pointRadius: 2 }
+            ]
+        },
+        options: {
+            responsive: true, maintainAspectRatio: true,
+            plugins: { legend: { labels: { color: '#ccc', font: { size: 11 } } } },
+            scales: {
+                x: { ticks: { color: '#888', maxTicksLimit: 10 }, grid: { color: 'rgba(255,255,255,.05)' } },
+                y: { ticks: { color: '#888' }, grid: { color: 'rgba(255,255,255,.05)' } },
+                y2: { position: 'right', ticks: { color: '#ffd600' }, grid: { display: false } }
+            }
+        }
+    });
+}
+
+function renderChartCumul(labels, cumulGain, cumulMise) {
+    const ctx = document.getElementById('chart-cumul');
+    if (!ctx) return;
+    if (chartCumul) chartCumul.destroy();
+    chartCumul = new Chart(ctx, {
+        type: 'line',
+        data: {
+            labels,
+            datasets: [
+                { label: 'Gain cumulé (€)', data: cumulGain, borderColor: '#00c853', backgroundColor: 'rgba(0,200,83,.1)', fill: true, tension: 0.3, pointRadius: 2 },
+                { label: 'Mise cumulée (€)', data: cumulMise, borderColor: '#aaa', backgroundColor: 'transparent', borderDash: [4,4], tension: 0.3, pointRadius: 0 }
+            ]
+        },
+        options: {
+            responsive: true, maintainAspectRatio: true,
+            plugins: { legend: { labels: { color: '#ccc', font: { size: 11 } } } },
+            scales: {
+                x: { ticks: { color: '#888', maxTicksLimit: 10 }, grid: { color: 'rgba(255,255,255,.05)' } },
+                y: { ticks: { color: '#888' }, grid: { color: 'rgba(255,255,255,.05)' } }
+            }
+        }
+    });
+}
+
+function renderMetricsTable(daily) {
+    const wrap = document.getElementById('metrics-table-wrap');
+    if (!wrap) return;
+    if (!daily.length) {
+        wrap.innerHTML = '<p style="color:var(--muted);padding:12px">Aucune donnée disponible pour cette période.</p>';
+        return;
+    }
+    const rows = daily.map(r => `
+        <tr>
+            <td>${r.date || '—'}</td>
+            <td>${r.total_bets ?? '—'}</td>
+            <td>${r.bets_gagne ?? '—'}</td>
+            <td style="color:${(r.precision_pct||0)>50?'var(--green)':'var(--text)'}">${r.precision_pct != null ? r.precision_pct.toFixed(1)+' %' : '—'}</td>
+            <td style="color:${(r.roi_pct||0)>0?'var(--green)':'#f44'}">${r.roi_pct != null ? (r.roi_pct>0?'+':'')+r.roi_pct.toFixed(2)+' %' : '—'}</td>
+            <td>${r.mise_totale != null ? r.mise_totale.toFixed(2)+' €' : '—'}</td>
+            <td style="color:${(r.gain_total||0)>0?'var(--green)':'#f44'}">${r.gain_total != null ? (r.gain_total>0?'+':'')+r.gain_total.toFixed(2)+' €' : '—'}</td>
+        </tr>`).join('');
+    wrap.innerHTML = `
+        <table style="width:100%;border-collapse:collapse;font-size:.85rem">
+            <thead><tr style="color:var(--muted);border-bottom:1px solid var(--border)">
+                <th style="padding:6px 8px;text-align:left">Date</th>
+                <th style="padding:6px 8px;text-align:left">Paris</th>
+                <th style="padding:6px 8px;text-align:left">Gagnés</th>
+                <th style="padding:6px 8px;text-align:left">Précision</th>
+                <th style="padding:6px 8px;text-align:left">ROI</th>
+                <th style="padding:6px 8px;text-align:left">Mise</th>
+                <th style="padding:6px 8px;text-align:left">Gain</th>
+            </tr></thead>
+            <tbody>${rows}</tbody>
+        </table>`;
+}
+
 loadDashboard().then(initNavFromHash);
 </script>
 </body>

docker-compose.broker.yml

@@ -0,0 +1,32 @@
+# Token Broker Infrastructure
+# PostgreSQL dedicated instance on port 5434
+networks:
+  turf-net:
+    driver: bridge
+
+services:
+  token-broker-db:
+    image: postgres:16-alpine
+    container_name: token-broker-db
+    restart: unless-stopped
+    environment:
+      POSTGRES_DB: token_broker
+      POSTGRES_USER: token_broker
+      POSTGRES_PASSWORD: ${TOKEN_BROKER_DB_PASSWORD:-CHANGE_ME_PASSWORD}
+    volumes:
+      - token-broker-pgdata:/var/lib/postgresql/data
+      - ./infra/postgres/token_broker_init.sql:/docker-entrypoint-initdb.d/init.sql:ro
+    healthcheck:
+      test: ["CMD-SHELL", "pg_isready -U token_broker -d token_broker"]
+      interval: 10s
+      timeout: 5s
+      retries: 5
+      start_period: 30s
+    networks:
+      - turf-net
+    ports:
+      - "127.0.0.1:5434:5432"
+
+volumes:
+  token-broker-pgdata:
+    driver: local

infra/postgres/token_broker_init.sql

@@ -0,0 +1,94 @@
+-- Token Broker PostgreSQL init script
+-- 6 tables: api_tokens, refresh_tokens, token_audit_log, clients, providers, token_usage
+
+CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
+
+CREATE TABLE IF NOT EXISTS api_tokens (
+    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    user_id         INTEGER NOT NULL,
+    name            TEXT NOT NULL DEFAULT 'default',
+    token_hash      TEXT NOT NULL UNIQUE,
+    token_prefix    TEXT NOT NULL,
+    scopes          TEXT[] DEFAULT '{}',
+    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
+    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    expires_at      TIMESTAMPTZ,
+    last_used_at    TIMESTAMPTZ,
+    metadata        JSONB DEFAULT '{}'
+);
+
+CREATE TABLE IF NOT EXISTS refresh_tokens (
+    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    user_id         INTEGER NOT NULL,
+    token_hash      TEXT NOT NULL UNIQUE,
+    token_prefix    TEXT NOT NULL,
+    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    expires_at      TIMESTAMPTZ NOT NULL,
+    revoked         BOOLEAN NOT NULL DEFAULT FALSE,
+    revoked_at      TIMESTAMPTZ,
+    replaced_by     UUID
+);
+
+CREATE TABLE IF NOT EXISTS token_audit_log (
+    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    user_id         INTEGER,
+    action          TEXT NOT NULL,
+    token_prefix    TEXT,
+    ip_address      TEXT,
+    user_agent      TEXT,
+    details         JSONB DEFAULT '{}',
+    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+CREATE TABLE IF NOT EXISTS clients (
+    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    client_id       TEXT NOT NULL UNIQUE,
+    client_secret   TEXT NOT NULL,
+    name            TEXT NOT NULL,
+    description     TEXT DEFAULT '',
+    redirect_uris   TEXT[] DEFAULT '{}',
+    scopes          TEXT[] DEFAULT '{}',
+    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
+    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+CREATE TABLE IF NOT EXISTS providers (
+    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+    name            TEXT NOT NULL UNIQUE,
+    provider_type   TEXT NOT NULL DEFAULT 'oauth2',
+    issuer_url      TEXT,
+    client_id       TEXT,
+    client_secret   TEXT,
+    scopes          TEXT[] DEFAULT '{}',
+    config          JSONB DEFAULT '{}',
+    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
+    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+CREATE TABLE IF NOT EXISTS token_usage (
+    id              BIGSERIAL PRIMARY KEY,
+    user_id         INTEGER NOT NULL,
+    token_id        UUID,
+    action          TEXT NOT NULL DEFAULT 'verify',
+    endpoint        TEXT,
+    status          TEXT NOT NULL DEFAULT 'success',
+    response_time_ms INTEGER,
+    ip_address      TEXT,
+    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+CREATE INDEX IF NOT EXISTS idx_api_tokens_user_id ON api_tokens(user_id);
+CREATE INDEX IF NOT EXISTS idx_api_tokens_token_hash ON api_tokens(token_hash);
+CREATE INDEX IF NOT EXISTS idx_refresh_tokens_user_id ON refresh_tokens(user_id);
+CREATE INDEX IF NOT EXISTS idx_refresh_tokens_token_hash ON refresh_tokens(token_hash);
+CREATE INDEX IF NOT EXISTS idx_token_audit_log_user_id ON token_audit_log(user_id);
+CREATE INDEX IF NOT EXISTS idx_token_audit_log_created_at ON token_audit_log(created_at);
+CREATE INDEX IF NOT EXISTS idx_clients_client_id ON clients(client_id);
+CREATE INDEX IF NOT EXISTS idx_providers_name ON providers(name);
+CREATE INDEX IF NOT EXISTS idx_token_usage_user_id ON token_usage(user_id);
+CREATE INDEX IF NOT EXISTS idx_token_usage_created_at ON token_usage(created_at);
+
+GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO token_broker;
+GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO token_broker;

infra/scripts/deploy_token_broker.sh

@@ -0,0 +1,90 @@
+#!/bin/bash
+# ============================================================
+# Deploy Token Broker — systemd service + Docker PG
+# ============================================================
+set -euo pipefail
+
+APP_DIR="/home/h3r7/turf_saas"
+SERVICE_NAME="token-broker"
+PID_FILE="/tmp/token_broker.pid"
+TIMESTAMP=$(date +%Y%m%d_%H%M%S)
+
+echo "[$(date -Iseconds)] === Deploying Token Broker ==="
+
+# Step 1: Backup current code
+echo "[$(date -Iseconds)] Backing up current code..."
+mkdir -p /home/h3r7/backups/token-broker
+cp "${APP_DIR}/services/token-broker/token_broker_api.py" \
+   "/home/h3r7/backups/token-broker/token_broker_api_${TIMESTAMP}.py"
+
+# Step 2: Ensure Docker PG is running
+echo "[$(date -Iseconds)] Ensuring PostgreSQL container..."
+if ! docker inspect token-broker-db >/dev/null 2>&1; then
+    echo "Creating PG container..."
+    docker run -d \
+        --name token-broker-db \
+        --restart unless-stopped \
+        -e POSTGRES_DB=token_broker \
+        -e POSTGRES_USER=token_broker \
+        -e POSTGRES_PASSWORD="${TOKEN_BROKER_DB_PASSWORD}" \
+        -v token-broker-pgdata:/var/lib/postgresql/data \
+        -v "${APP_DIR}/infra/postgres/token_broker_init.sql:/docker-entrypoint-initdb.d/init.sql:ro" \
+        -p 127.0.0.1:5434:5432 \
+        postgres:16-alpine
+elif ! docker ps --filter name=token-broker-db --format '{{.Status}}' | grep -q Up; then
+    echo "Starting existing PG container..."
+    docker start token-broker-db
+else
+    echo "PG container already running."
+fi
+
+# Wait for PG readiness
+echo "[$(date -Iseconds)] Waiting for PG to be ready..."
+for i in $(seq 1 20); do
+    if docker exec token-broker-db pg_isready -U token_broker -d token_broker >/dev/null 2>&1; then
+        echo "PG ready."
+        break
+    fi
+    sleep 2
+done
+
+# Step 3: Ensure psycopg2-binary is installed
+echo "[$(date -Iseconds)] Checking Python deps..."
+source "${APP_DIR}/venv/bin/activate"
+pip install -q psycopg2-binary PyJWT flask-cors python-dotenv gunicorn 2>/dev/null || true
+
+# Step 4: Stop current service
+echo "[$(date -Iseconds)] Stopping current service..."
+if systemctl is-active --quiet ${SERVICE_NAME} 2>/dev/null; then
+    systemctl stop ${SERVICE_NAME}
+elif [ -f "$PID_FILE" ] && kill -0 $(cat "$PID_FILE") 2>/dev/null; then
+    kill $(cat "$PID_FILE") 2>/dev/null || true
+fi
+sleep 2
+
+# Step 5: Copy systemd unit and start
+echo "[$(date -Iseconds)] Starting via systemd..."
+cp "${APP_DIR}/services/token-broker/token-broker.service" /etc/systemd/system/
+systemctl daemon-reload
+systemctl enable ${SERVICE_NAME}
+systemctl start ${SERVICE_NAME}
+
+# Wait for startup
+sleep 3
+
+# Step 6: Health check
+echo "[$(date -Iseconds)] Running health check..."
+HEALTH=$(curl -s http://127.0.0.1:8783/health 2>/dev/null || echo '{"status":"failed"}')
+STATUS=$(echo "$HEALTH" | python3 -c "import sys,json; print(json.load(sys.stdin).get('status','unknown'))" 2>/dev/null || echo "unknown")
+
+if [ "$STATUS" = "ok" ]; then
+    echo "[$(date -Iseconds)] ✅ Health check passed: ${HEALTH}"
+    echo "[$(date -Iseconds)] === Token Broker deploy SUCCESS ==="
+else
+    echo "[$(date -Iseconds)] ❌ Health check failed: ${HEALTH}"
+    echo "[$(date -Iseconds)] === Token Broker deploy FAILED ==="
+    exit 1
+fi
+
+# Step 7: Clean old backups (keep last 30)
+find /home/h3r7/backups/token-broker -name "*.py" -mtime +30 -delete

leadhunter_api.py

@@ -30,7 +30,9 @@ from leadhunter_crm import (
     insert_leads,
     get_leads,
     get_lead_by_id,
+    update_lead,
     update_lead_status,
+    delete_lead,
     get_stats,
     export_csv,
     VALID_STATUSES,
@@ -285,6 +287,59 @@ def api_update_status(lead_id: int):
     )
 
 
+@app.route("/api/leads/<int:lead_id>", methods=["GET"])
+def api_get_lead(lead_id: int):
+    """
+    Retourne le detail d'un lead par son ID.
+
+    Returns:
+        JSON avec les informations completes du lead, ou 404.
+    """
+    lead = get_lead_by_id(lead_id)
+    if not lead:
+        return jsonify({"error": f"Lead id={lead_id} introuvable"}), 404
+    return jsonify(lead)
+
+
+@app.route("/api/leads/<int:lead_id>", methods=["PUT"])
+def api_put_lead(lead_id: int):
+    """
+    Met a jour completement un lead.
+
+    Body JSON : dict avec les champs a mettre a jour.
+    """
+    body = request.get_json(silent=True)
+    if not body:
+        return jsonify({"error": "Body JSON requis"}), 400
+
+    lead = get_lead_by_id(lead_id)
+    if not lead:
+        return jsonify({"error": f"Lead id={lead_id} introuvable"}), 404
+
+    success = update_lead(lead_id, body)
+    if not success:
+        return jsonify({"error": "Mise a jour echouee"}), 500
+
+    updated_lead = get_lead_by_id(lead_id)
+    return jsonify({"success": True, "lead": updated_lead})
+
+
+@app.route("/api/leads/<int:lead_id>", methods=["DELETE"])
+def api_delete_lead(lead_id: int):
+    """
+    Supprime un lead physiquement.
+    """
+    lead = get_lead_by_id(lead_id)
+    if not lead:
+        return jsonify({"error": f"Lead id={lead_id} introuvable"}), 404
+
+    success = delete_lead(lead_id)
+    if not success:
+        return jsonify({"error": "Suppression echouee"}), 500
+
+    return jsonify({"success": True, "lead_id": lead_id, "deleted": True})
+
+
 @app.route("/health", methods=["GET"])
 def health():
     """Healthcheck pour systemd / monitoring."""

leadhunter_crm.py

@@ -52,8 +52,24 @@ if not logger.handlers:
 # ─── Chemin DB ───────────────────────────────────────────────────────────────
 DB_PATH = "/home/h3r7/leadhunter.db"
 
-# Statuts valides pour un lead
+# Statuts valides pour un lead (7 etapes Kanban)
-VALID_STATUSES = {"new", "contacted", "closed", "rejected"}
+VALID_STATUSES = {
+    "nouveau",  # NOUVEAU
+    "contacte",  # CONTACTÉ
+    "interesse",  # INTÉRESSÉ
+    "demo_planifiee",  # DÉMO PLANIFIÉE
+    "proposition_envoyee",  # PROPOSITION ENVOYÉE
+    "negotiation",  # NÉGOCIATION
+    "signe_ou_refuse",  # SIGNÉ / REFUSÉ
+}
+
+# Mapping des anciens statuts vers les nouveaux (pour migration)
+LEGACY_STATUS_MAP = {
+    "new": "nouveau",
+    "contacted": "contacte",
+    "closed": "signe_ou_refuse",
+    "rejected": "signe_ou_refuse",
+}
 
 
 # ─── Initialisation ──────────────────────────────────────────────────────────
@@ -212,6 +228,77 @@ def get_lead_by_id(lead_id: int, db_path: str = DB_PATH) -> Optional[dict]:
         return None
 
 
+def update_lead(lead_id: int, data: dict, db_path: str = DB_PATH) -> bool:
+    """
+    Met à jour un lead avec les champs fournis.
+
+    Args:
+        lead_id: id du lead.
+        data: dict avec les champs a mettre a jour (name, address, phone, etc.)
+
+    Returns:
+        True si mise a jour reussie, False sinon.
+    """
+    allowed_fields = {
+        "name",
+        "address",
+        "phone",
+        "rating",
+        "reviews_count",
+        "website",
+        "score",
+        "rgpd_ok",
+        "status",
+    }
+    fields_to_update = {k: v for k, v in data.items() if k in allowed_fields}
+
+    if not fields_to_update:
+        logger.warning(
+            f"update_lead : aucun champ valide fourni pour lead_id={lead_id}"
+        )
+        return False
+
+    if (
+        "status" in fields_to_update
+        and fields_to_update["status"] not in VALID_STATUSES
+    ):
+        logger.warning(f"update_lead : statut invalide '{fields_to_update['status']}'")
+        return False
+
+    try:
+        with _get_conn(db_path) as conn:
+            set_clause = ", ".join([f"{k} = ?" for k in fields_to_update])
+            values = list(fields_to_update.values()) + [lead_id]
+            conn.execute(f"UPDATE leads SET {set_clause} WHERE id = ?", values)
+            logger.info(
+                f"Lead id={lead_id} mis a jour : {list(fields_to_update.keys())}"
+            )
+            return True
+    except Exception as e:
+        logger.warning(f"update_lead error : {e}")
+        return False
+
+
+def delete_lead(lead_id: int, db_path: str = DB_PATH) -> bool:
+    """
+    Supprime un lead physiquement.
+
+    Args:
+        lead_id: id du lead a supprimer.
+
+    Returns:
+        True si suppression reussie, False sinon.
+    """
+    try:
+        with _get_conn(db_path) as conn:
+            conn.execute("DELETE FROM leads WHERE id = ?", (lead_id,))
+            logger.info(f"Lead id={lead_id} supprime")
+            return True
+    except Exception as e:
+        logger.warning(f"delete_lead error : {e}")
+        return False
+
+
 def update_lead_status(lead_id: int, status: str, db_path: str = DB_PATH) -> bool:
     """
     Met à jour le statut d'un lead.

ml_feedback_saas.py

@@ -0,0 +1,600 @@
+#!/usr/bin/env python3
+"""
+ml_feedback_saas.py — Feedback loop ML pour turf_saas.
+Enregistre les paris virtuels XGBoost depuis ml_predictions_cache
+et met à jour les résultats/dividendes depuis pmu_partants + pmu_rapports.
+
+DB cible : /home/h3r7/turf_saas/turf_saas.db
+
+Stratégies :
+  A) xgboost_sg    : simple_gagnant — top1 ML par course, ml_score >= 70, mise 1€
+  B) xgboost_value : simple_gagnant — is_value_bet = 1, mise 1€
+  C) xgboost_sp    : simple_place   — top1 ML par course, ml_score >= 50, mise 1€
+  D) xgboost_2sur4 : deux_sur_quatre — top4 ML par course, 6 combos x 1€ = mise 6€
+
+Usage :
+  python3 ml_feedback_saas.py                      # Traite aujourd'hui
+  python3 ml_feedback_saas.py --backfill 2026-04-25
+  python3 ml_feedback_saas.py --date 2026-04-25
+"""
+
+import sqlite3
+import sys
+import logging
+import os
+from datetime import datetime, timedelta
+
+DB_PATH = "/home/h3r7/turf_saas/turf_saas.db"
+
+os.makedirs("/home/h3r7/turf_saas/logs", exist_ok=True)
+
+logging.basicConfig(
+    level=logging.INFO,
+    format="%(asctime)s [ml_feedback_saas] %(levelname)s %(message)s",
+    handlers=[
+        logging.FileHandler("/home/h3r7/turf_saas/logs/ml_feedback_saas.log"),
+        logging.StreamHandler(),
+    ],
+)
+log = logging.getLogger(__name__)
+
+
+# ─────────────────────────────────────────────────────────
+# UTILITAIRES
+# ─────────────────────────────────────────────────────────
+
+
+def get_db():
+    conn = sqlite3.connect(DB_PATH)
+    conn.row_factory = sqlite3.Row
+    return conn
+
+
+def pari_existe(cursor, date, num_reunion, num_course, numero1, type_pari, source_reco):
+    """Vérifie si un pari identique existe déjà (idempotence)."""
+    cursor.execute(
+        """
+        SELECT id FROM paris
+        WHERE date_course = ? AND source_reco = ?
+          AND type_pari = ? AND numero1 = ?
+          AND race_label = ?
+        """,
+        (date, source_reco, type_pari, numero1, f"R{num_reunion}C{num_course}"),
+    )
+    return cursor.fetchone() is not None
+
+
+def pari_2sur4_existe(cursor, date, num_reunion, num_course, source_reco):
+    """Vérifie si un pari 2sur4 existe déjà pour cette course."""
+    cursor.execute(
+        """
+        SELECT id FROM paris
+        WHERE date_course = ? AND source_reco = ?
+          AND race_label = ?
+        """,
+        (date, source_reco, f"R{num_reunion}C{num_course}"),
+    )
+    return cursor.fetchone() is not None
+
+
+def get_top_ml_par_course(cursor, date, n=4, min_score=0):
+    """Retourne les n meilleurs chevaux ML par course pour une date."""
+    cursor.execute(
+        """
+        SELECT num_reunion, num_course, horse_name, horse_number,
+               ml_score, odds, recommendation, is_value_bet,
+               race_label, race_name, hippodrome, heure,
+               discipline, distance
+        FROM ml_predictions_cache
+        WHERE date = ?
+          AND ml_score >= ?
+        ORDER BY num_reunion, num_course, ml_score DESC
+        """,
+        (date, min_score),
+    )
+    rows = cursor.fetchall()
+
+    courses = {}
+    for r in rows:
+        key = (r["num_reunion"], r["num_course"])
+        if key not in courses:
+            courses[key] = []
+        if len(courses[key]) < n:
+            courses[key].append(dict(r))
+    return courses
+
+
+# ─────────────────────────────────────────────────────────
+# STRATÉGIE A — Simple Gagnant top1 ML (score >= 70)
+# ─────────────────────────────────────────────────────────
+
+
+def save_ml_paris_sg(conn, date):
+    """Insère 1 pari simple_gagnant par course : top1 ML avec ml_score >= 70."""
+    cursor = conn.cursor()
+    courses = get_top_ml_par_course(cursor, date, n=1, min_score=70)
+    inseres = 0
+
+    for (num_reunion, num_course), chevaux in courses.items():
+        cheval = chevaux[0]
+        if pari_existe(
+            cursor,
+            date,
+            num_reunion,
+            num_course,
+            cheval["horse_number"],
+            "simple_gagnant",
+            "xgboost_sg",
+        ):
+            continue
+
+        cursor.execute(
+            """
+            INSERT INTO paris
+              (date_pari, date_course, race_name, race_label, hippodrome,
+               type_pari, chevaux, cheval1, numero1, cote, mise,
+               statut, gain, source_reco, model_source)
+            VALUES (?, ?, ?, ?, ?, 'simple_gagnant', ?, ?, ?, ?, 1.0,
+                    'EN_ATTENTE', 0.0, 'xgboost_sg', 'xgboost_v1')
+            """,
+            (
+                date,
+                date,
+                cheval.get("race_name") or "",
+                f"R{num_reunion}C{num_course}",
+                cheval.get("hippodrome") or "",
+                cheval["horse_name"],
+                cheval["horse_name"],
+                cheval["horse_number"],
+                cheval["odds"],
+            ),
+        )
+        inseres += 1
+
+    conn.commit()
+    log.info(f"[SG]     {date} → {inseres} paris simple_gagnant insérés (score>=70)")
+    return inseres
+
+
+# ─────────────────────────────────────────────────────────
+# STRATÉGIE B — Value Bet (is_value_bet = 1)
+# ─────────────────────────────────────────────────────────
+
+
+def save_ml_paris_value(conn, date):
+    """Insère 1 pari simple_gagnant pour chaque cheval is_value_bet=1."""
+    cursor = conn.cursor()
+    cursor.execute(
+        """
+        SELECT num_reunion, num_course, horse_name, horse_number,
+               ml_score, odds, race_label, race_name, hippodrome
+        FROM ml_predictions_cache
+        WHERE date = ? AND is_value_bet = 1
+        ORDER BY num_reunion, num_course, ml_score DESC
+        """,
+        (date,),
+    )
+    rows = [dict(r) for r in cursor.fetchall()]
+    inseres = 0
+
+    for r in rows:
+        if pari_existe(
+            cursor,
+            date,
+            r["num_reunion"],
+            r["num_course"],
+            r["horse_number"],
+            "simple_gagnant",
+            "xgboost_value",
+        ):
+            continue
+
+        cursor.execute(
+            """
+            INSERT INTO paris
+              (date_pari, date_course, race_name, race_label, hippodrome,
+               type_pari, chevaux, cheval1, numero1, cote, mise,
+               statut, gain, source_reco, model_source)
+            VALUES (?, ?, ?, ?, ?, 'simple_gagnant', ?, ?, ?, ?, 1.0,
+                    'EN_ATTENTE', 0.0, 'xgboost_value', 'xgboost_v1')
+            """,
+            (
+                date,
+                date,
+                r.get("race_name") or "",
+                r.get("race_label") or f"R{r['num_reunion']}C{r['num_course']}",
+                r.get("hippodrome") or "",
+                r["horse_name"],
+                r["horse_name"],
+                r["horse_number"],
+                r["odds"],
+            ),
+        )
+        inseres += 1
+
+    conn.commit()
+    log.info(f"[VALUE]  {date} → {inseres} paris value_bet insérés")
+    return inseres
+
+
+# ─────────────────────────────────────────────────────────
+# STRATÉGIE C — Simple Placé top1 ML (score >= 50)
+# ─────────────────────────────────────────────────────────
+
+
+def save_ml_paris_sp(conn, date):
+    """Insère 1 pari simple_place par course : top1 ML avec ml_score >= 50."""
+    cursor = conn.cursor()
+    courses = get_top_ml_par_course(cursor, date, n=1, min_score=50)
+    inseres = 0
+
+    for (num_reunion, num_course), chevaux in courses.items():
+        cheval = chevaux[0]
+        if pari_existe(
+            cursor,
+            date,
+            num_reunion,
+            num_course,
+            cheval["horse_number"],
+            "simple_place",
+            "xgboost_sp",
+        ):
+            continue
+
+        cursor.execute(
+            """
+            INSERT INTO paris
+              (date_pari, date_course, race_name, race_label, hippodrome,
+               type_pari, chevaux, cheval1, numero1, cote, mise,
+               statut, gain, source_reco, model_source)
+            VALUES (?, ?, ?, ?, ?, 'simple_place', ?, ?, ?, ?, 1.0,
+                    'EN_ATTENTE', 0.0, 'xgboost_sp', 'xgboost_v1')
+            """,
+            (
+                date,
+                date,
+                cheval.get("race_name") or "",
+                f"R{num_reunion}C{num_course}",
+                cheval.get("hippodrome") or "",
+                cheval["horse_name"],
+                cheval["horse_name"],
+                cheval["horse_number"],
+                cheval["odds"],
+            ),
+        )
+        inseres += 1
+
+    conn.commit()
+    log.info(f"[SP]     {date} → {inseres} paris simple_place insérés (score>=50)")
+    return inseres
+
+
+# ─────────────────────────────────────────────────────────
+# STRATÉGIE D — 2sur4 top4 ML (6 combinaisons x 1€)
+# ─────────────────────────────────────────────────────────
+
+
+def save_ml_paris_2sur4(conn, date):
+    """Insère 1 pari deux_sur_quatre par course : top4 ML, mise 6€."""
+    cursor = conn.cursor()
+    courses = get_top_ml_par_course(cursor, date, n=4, min_score=0)
+    inseres = 0
+
+    for (num_reunion, num_course), chevaux in courses.items():
+        if len(chevaux) < 4:
+            continue
+        if pari_2sur4_existe(cursor, date, num_reunion, num_course, "xgboost_2sur4"):
+            continue
+
+        top4 = chevaux[:4]
+        nums = [str(c["horse_number"]) for c in top4]
+        noms = [c["horse_name"] for c in top4]
+        chevaux_str = "/".join(noms)
+
+        cursor.execute(
+            """
+            INSERT INTO paris
+              (date_pari, date_course, race_name, race_label, hippodrome,
+               type_pari, chevaux, cheval1, numero1, cote, mise,
+               statut, gain, source_reco, model_source, commentaire)
+            VALUES (?, ?, ?, ?, ?, 'deux_sur_quatre', ?, ?, ?, 0.0, 6.0,
+                    'EN_ATTENTE', 0.0, 'xgboost_2sur4', 'xgboost_v1', ?)
+            """,
+            (
+                date,
+                date,
+                top4[0].get("race_name") or "",
+                f"R{num_reunion}C{num_course}",
+                top4[0].get("hippodrome") or "",
+                chevaux_str,
+                top4[0]["horse_name"],
+                top4[0]["horse_number"],
+                f"top4 ML: {'/'.join(nums)}",
+            ),
+        )
+        inseres += 1
+
+    conn.commit()
+    log.info(f"[2S4]    {date} → {inseres} paris deux_sur_quatre insérés")
+    return inseres
+
+
+# ─────────────────────────────────────────────────────────
+# UPDATE RÉSULTATS + DIVIDENDES
+# ─────────────────────────────────────────────────────────
+
+
+def update_ml_paris_results(conn, date):
+    """
+    Met à jour statut + gain (dividende PMU réel) pour tous les paris ML EN_ATTENTE.
+    Sources: pmu_partants (ordre_arrivee) + pmu_rapports (dividende_euro).
+    """
+    cursor = conn.cursor()
+
+    cursor.execute(
+        """
+        SELECT id, race_label, type_pari, numero1, chevaux, mise, source_reco, commentaire
+        FROM paris
+        WHERE date_course = ? AND statut = 'EN_ATTENTE'
+          AND source_reco LIKE 'xgboost%'
+        """,
+        (date,),
+    )
+    paris = [dict(r) for r in cursor.fetchall()]
+
+    if not paris:
+        log.info(f"[UPDATE] {date} → aucun pari ML EN_ATTENTE")
+        return 0
+
+    maj = 0
+    for pari in paris:
+        pari_id = pari["id"]
+        race_label = pari["race_label"] or ""
+        type_pari = pari["type_pari"]
+        numero1 = pari["numero1"]
+        mise = pari["mise"]
+
+        # Extraire num_reunion / num_course depuis le race_label "R{r}C{c}"
+        try:
+            parts = race_label.replace("R", "").split("C")
+            num_reunion = int(parts[0])
+            num_course = int(parts[1])
+        except Exception:
+            log.warning(f"[UPDATE] race_label invalide : {race_label}")
+            continue
+
+        if type_pari == "simple_gagnant":
+            cursor.execute(
+                """
+                SELECT ordre_arrivee FROM pmu_partants
+                WHERE date_programme = ? AND num_reunion = ?
+                  AND num_course = ? AND num_pmu = ?
+                """,
+                (date, num_reunion, num_course, numero1),
+            )
+            row = cursor.fetchone()
+            if not row or row["ordre_arrivee"] is None or row["ordre_arrivee"] == 0:
+                continue
+
+            gagne = row["ordre_arrivee"] == 1
+            gain = 0.0
+            if gagne:
+                cursor.execute(
+                    """
+                    SELECT dividende_euro FROM pmu_rapports
+                    WHERE date_programme = ? AND num_reunion = ?
+                      AND num_course = ? AND type_pari = 'SIMPLE_GAGNANT'
+                      AND CAST(combinaison AS INTEGER) = ?
+                      AND libelle NOT LIKE '%NP%'
+                    """,
+                    (date, num_reunion, num_course, numero1),
+                )
+                div = cursor.fetchone()
+                gain = div["dividende_euro"] if div and div["dividende_euro"] else 0.0
+
+            cursor.execute(
+                "UPDATE paris SET statut=?, gain=? WHERE id=?",
+                ("GAGNE" if gagne else "PERDU", gain, pari_id),
+            )
+            maj += 1
+
+        elif type_pari == "simple_place":
+            cursor.execute(
+                """
+                SELECT ordre_arrivee FROM pmu_partants
+                WHERE date_programme = ? AND num_reunion = ?
+                  AND num_course = ? AND num_pmu = ?
+                """,
+                (date, num_reunion, num_course, numero1),
+            )
+            row = cursor.fetchone()
+            if not row or not row["ordre_arrivee"]:
+                continue
+
+            gagne = 1 <= row["ordre_arrivee"] <= 3
+            gain = 0.0
+            if gagne:
+                cursor.execute(
+                    """
+                    SELECT dividende_euro FROM pmu_rapports
+                    WHERE date_programme = ? AND num_reunion = ?
+                      AND num_course = ? AND type_pari = 'SIMPLE_PLACE'
+                      AND CAST(combinaison AS INTEGER) = ?
+                      AND libelle NOT LIKE '%NP%'
+                    """,
+                    (date, num_reunion, num_course, numero1),
+                )
+                div = cursor.fetchone()
+                gain = div["dividende_euro"] if div and div["dividende_euro"] else 0.0
+
+            cursor.execute(
+                "UPDATE paris SET statut=?, gain=? WHERE id=?",
+                ("GAGNE" if gagne else "PERDU", gain, pari_id),
+            )
+            maj += 1
+
+        elif type_pari == "deux_sur_quatre":
+            # Récupère les 4 numéros depuis commentaire "top4 ML: n1/n2/n3/n4"
+            try:
+                nums_str = (
+                    pari["commentaire"].split(": ")[1]
+                    if pari.get("commentaire")
+                    else ""
+                )
+                nums_top4 = [int(n) for n in nums_str.split("/") if n.strip().isdigit()]
+            except Exception:
+                nums_top4 = []
+
+            if len(nums_top4) < 4:
+                # Fallback : reconstituer top4 depuis ml_predictions_cache
+                cursor.execute(
+                    """
+                    SELECT horse_number FROM ml_predictions_cache
+                    WHERE date = ? AND num_reunion = ? AND num_course = ?
+                    ORDER BY ml_score DESC LIMIT 4
+                    """,
+                    (date, num_reunion, num_course),
+                )
+                nums_top4 = [r["horse_number"] for r in cursor.fetchall()]
+
+            if len(nums_top4) < 2:
+                continue
+
+            cursor.execute(
+                """
+                SELECT combinaison, dividende_euro FROM pmu_rapports
+                WHERE date_programme = ? AND num_reunion = ?
+                  AND num_course = ? AND type_pari = 'DEUX_SUR_QUATRE'
+                  AND libelle NOT LIKE '%NP%'
+                """,
+                (date, num_reunion, num_course),
+            )
+            rapports = [dict(r) for r in cursor.fetchall()]
+            gain_total = 0.0
+
+            for rap in rapports:
+                try:
+                    n1, n2 = [int(x) for x in rap["combinaison"].split("-")]
+                except Exception:
+                    continue
+                if n1 in nums_top4 and n2 in nums_top4:
+                    gain_total += rap["dividende_euro"]
+
+            gagne = gain_total > 0
+            cursor.execute(
+                "UPDATE paris SET statut=?, gain=? WHERE id=?",
+                ("GAGNE" if gagne else "PERDU", round(gain_total, 2), pari_id),
+            )
+            maj += 1
+
+    conn.commit()
+    log.info(f"[UPDATE] {date} → {maj}/{len(paris)} paris ML mis à jour")
+    return maj
+
+
+# ─────────────────────────────────────────────────────────
+# STATS PAR STRATÉGIE
+# ─────────────────────────────────────────────────────────
+
+
+def get_feedback_stats(conn, date_debut=None, date_fin=None):
+    """Stats performances ML par stratégie (source_reco)."""
+    cursor = conn.cursor()
+    cursor.execute(
+        """
+        SELECT source_reco,
+               COUNT(*) as n_paris,
+               SUM(CASE WHEN statut='GAGNE' THEN 1 ELSE 0 END) as n_gagne,
+               SUM(CASE WHEN statut='PERDU' THEN 1 ELSE 0 END) as n_perdu,
+               SUM(CASE WHEN statut='EN_ATTENTE' THEN 1 ELSE 0 END) as n_attente,
+               ROUND(100.0 * SUM(CASE WHEN statut='GAGNE' THEN 1 ELSE 0 END)
+                     / NULLIF(SUM(CASE WHEN statut IN ('GAGNE','PERDU') THEN 1 ELSE 0 END), 0), 1) as win_rate_pct,
+               ROUND(SUM(gain), 2) as gain_total,
+               ROUND(SUM(mise), 2) as mise_totale,
+               ROUND(SUM(gain) - SUM(mise), 2) as roi_net
+        FROM paris
+        WHERE source_reco LIKE 'xgboost%'
+          AND (:debut IS NULL OR date_course >= :debut)
+          AND (:fin IS NULL OR date_course <= :fin)
+        GROUP BY source_reco
+        ORDER BY source_reco
+        """,
+        {"debut": date_debut, "fin": date_fin},
+    )
+    return [dict(r) for r in cursor.fetchall()]
+
+
+# ─────────────────────────────────────────────────────────
+# PIPELINE COMPLET
+# ─────────────────────────────────────────────────────────
+
+
+def run(date):
+    """Enregistre les paris ML du jour + met à jour les résultats de J-1."""
+    conn = get_db()
+    log.info(f"=== ml_feedback_saas.run({date}) ===")
+
+    # 1. Enregistre les paris ML pour la date (depuis le cache du jour)
+    sg = save_ml_paris_sg(conn, date)
+    vb = save_ml_paris_value(conn, date)
+    sp = save_ml_paris_sp(conn, date)
+    s4 = save_ml_paris_2sur4(conn, date)
+    log.info(f"[SAVE]   {date} → total insérés : SG={sg} VALUE={vb} SP={sp} 2S4={s4}")
+
+    # 2. Met à jour les résultats de J-1 (résultats PMU disponibles)
+    yesterday = (datetime.strptime(date, "%Y-%m-%d") - timedelta(days=1)).strftime(
+        "%Y-%m-%d"
+    )
+    maj = update_ml_paris_results(conn, yesterday)
+    log.info(f"[UPDATE] {yesterday} → {maj} paris mis à jour")
+
+    conn.close()
+    return {"inseres": {"sg": sg, "value": vb, "sp": sp, "2sur4": s4}, "maj": maj}
+
+
+def backfill(date):
+    """Backfill : insère ET met à jour les résultats pour une date passée."""
+    conn = get_db()
+    log.info(f"=== ml_feedback_saas.backfill({date}) ===")
+
+    sg = save_ml_paris_sg(conn, date)
+    vb = save_ml_paris_value(conn, date)
+    sp = save_ml_paris_sp(conn, date)
+    s4 = save_ml_paris_2sur4(conn, date)
+    log.info(f"[SAVE]   {date} → SG={sg} VALUE={vb} SP={sp} 2S4={s4}")
+
+    maj = update_ml_paris_results(conn, date)
+    log.info(f"[UPDATE] {date} → {maj} paris mis à jour")
+
+    conn.close()
+    return sg + vb + sp + s4, maj
+
+
+# ─────────────────────────────────────────────────────────
+# MAIN
+# ─────────────────────────────────────────────────────────
+
+if __name__ == "__main__":
+    if "--backfill" in sys.argv:
+        idx = sys.argv.index("--backfill")
+        date = sys.argv[idx + 1] if idx + 1 < len(sys.argv) else None
+        if not date:
+            print("Usage: python3 ml_feedback_saas.py --backfill YYYY-MM-DD")
+            sys.exit(1)
+        inseres, maj = backfill(date)
+        print(f"Backfill {date} : {inseres} paris insérés, {maj} mis à jour")
+
+    elif "--date" in sys.argv:
+        idx = sys.argv.index("--date")
+        date = sys.argv[idx + 1] if idx + 1 < len(sys.argv) else None
+        if not date:
+            print("Usage: python3 ml_feedback_saas.py --date YYYY-MM-DD")
+            sys.exit(1)
+        result = run(date)
+        total = sum(result["inseres"].values())
+        print(f"Run {date} : {total} paris insérés, {result['maj']} mis à jour")
+
+    else:
+        result = run(datetime.now().strftime("%Y-%m-%d"))
+        total = sum(result["inseres"].values())
+        print(f"Run today : {total} paris insérés, {result['maj']} mis à jour")

portal_server.py

@@ -18,10 +18,12 @@ SAAS_DIR = "/home/h3r7/turf_saas"
 # ─── SaaS Auth & API v1 blueprints ────────────────────────────────────────────
 try:
     from saas_auth import auth_bp
-    from saas_api_v1 import api_v1_bp
+    from saas_api_v1 import saas_api_v1_bp
+    from api_v1 import register_api_v1
 
     app.register_blueprint(auth_bp)
-    app.register_blueprint(api_v1_bp)
+    app.register_blueprint(saas_api_v1_bp)
+    register_api_v1(app)
     print("[portal] SaaS auth & API v1 blueprints registered ✅")
 except Exception as e:
     print(f"[portal] Warning: could not register SaaS blueprints: {e}")
@@ -352,6 +354,29 @@ def template_complet():
     return send_from_directory("/home/h3r7/turf_saas", "template_complet.html")
 
 
+@app.route("/leadhunter/clients/le-big-ben/")
+@app.route("/leadhunter/clients/le-big-ben")
+def big_ben():
+    return send_from_directory(
+        "/home/h3r7/turf_saas/templates/leadhunter/clients/le-big-ben", "index.html"
+    )
+
+
+@app.route("/leadhunter/clients/le-big-ben/sitemap.xml")
+def big_ben_sitemap():
+    return send_from_directory(
+        "/home/h3r7/turf_saas/templates/leadhunter/clients/le-big-ben",
+        "sitemap.xml",
+        mimetype="application/xml",
+    )
+
+
+@app.route("/formation/ai102")
+@app.route("/formation/ai102/")
+def certif_ai102():
+    return send_from_directory("/home/h3r7/turf_saas/pitch", "certif-ai102.html")
+
+
 @app.route("/boite_a_idees_dashboard")
 def boite_a_idees_dashboard():
     return send_from_directory("/home/h3r7/turf_saas", "boite_a_idees_dashboard.html")
@@ -730,6 +755,63 @@ def turf_static(filename):
     return send_from_directory("/home/h3r7/turf_saas", filename)
 
 
+# --- Consumption Dashboard (HRT-226) ---
+CONSUMPTION_API_URL = "http://localhost:8784"
+CONSUMPTION_API_KEY = os.environ.get("CONSUMPTION_API_KEY", "dev-key-change-in-production")
+
+
+@app.route("/dashboard/consumption")
+def consumption_dashboard():
+    return send_from_directory(SAAS_DIR, "consumption_dashboard.html")
+
+
+@app.route("/dashboard/consumption/history")
+def consumption_history():
+    return send_from_directory(SAAS_DIR, "consumption_history.html")
+
+
+@app.route("/dashboard/consumption/alerts")
+def consumption_alerts():
+    return send_from_directory(SAAS_DIR, "consumption_alerts.html")
+
+
+# Proxy: /api/v1/consumption/* -> consumption-tracker (port 8784)
+@app.route("/api/v1/consumption/<path:subpath>", methods=["GET", "POST", "PUT", "DELETE", "PATCH"])
+def proxy_consumption_api(subpath):
+    full_url = f"{CONSUMPTION_API_URL}/api/v1/consumption/{subpath}"
+    if request.query_string:
+        full_url += "?" + request.query_string.decode()
+    try:
+        headers = {k: v for k, v in request.headers if k.lower() not in ("host", "content-length", "transfer-encoding", "connection")}
+        if not any(k.lower() == "authorization" for k in headers):
+            headers["Authorization"] = f"Bearer {CONSUMPTION_API_KEY}"
+        raw_body = request.get_data()
+        resp = requests.request(request.method, full_url, headers=headers, data=raw_body, cookies=request.cookies, allow_redirects=False, timeout=15)
+        response = make_response(resp.content, resp.status_code)
+        for k, v in resp.headers.items():
+            if k.lower() not in ("content-encoding", "transfer-encoding", "connection"):
+                response.headers[k] = v
+        return response
+    except Exception as e:
+        return jsonify({"error": f"Consumption proxy error: {e}"}), 502
+
+
+# Proxy: /api/v1/ai/usage* -> AI Router (port 8783)
+@app.route("/api/v1/ai/usage")
+@app.route("/api/v1/ai/usage/<path:subpath>")
+def proxy_ai_usage(subpath=""):
+    full_url = f"http://localhost:8783/api/v1/ai/usage"
+    if subpath:
+        full_url += "/" + subpath
+    if request.query_string:
+        full_url += "?" + request.query_string.decode()
+    try:
+        resp = requests.get(full_url, timeout=15)
+        return resp.content, resp.status_code, {"Content-Type": "application/json"}
+    except Exception as e:
+        return jsonify({"error": f"AI usage proxy error: {e}"}), 502
+
+
 # --- POD Routes ---
 @app.route("/pod/")
 @app.route("/pod/<path:filename>")

requirements.txt

@@ -31,3 +31,6 @@ python-dotenv==1.1.0
 
 # Utilities
 python-dateutil==2.9.0
+
+# Hyperparameter optimization (ML ensemble tuning — HRT-136)
+optuna>=4.0.0

saas_api_v1.py

@@ -13,7 +13,7 @@ from saas_auth import require_auth
 
 DB_PATH = os.environ.get("TURF_SAAS_DB", "/home/h3r7/turf_saas/turf_saas.db")
 
-api_v1_bp = Blueprint("api_v1", __name__, url_prefix="/api/v1")
+saas_api_v1_bp = Blueprint("saas_api_v1", __name__, url_prefix="/api/v1")
 
 
 def get_db():
@@ -30,7 +30,7 @@ def plan_allows(user_plan: str, required: str) -> bool:
 # ─── Stats ────────────────────────────────────────────────────────────────────
 
 
-@api_v1_bp.route("/stats/summary", methods=["GET"])
+@saas_api_v1_bp.route("/stats/summary", methods=["GET"])
 @require_auth
 def stats_summary():
     """GET /api/v1/stats/summary — résumé dashboard."""
@@ -94,7 +94,7 @@ def stats_summary():
 # ─── Predictions ──────────────────────────────────────────────────────────────
 
 
-@api_v1_bp.route("/predictions/today", methods=["GET"])
+@saas_api_v1_bp.route("/predictions/today", methods=["GET"])
 @require_auth
 def predictions_today():
     """GET /api/v1/predictions/today — prédictions du jour selon le plan."""
@@ -149,7 +149,7 @@ def predictions_today():
         return jsonify({"error": str(e), "predictions": []}), 200
 
 
-@api_v1_bp.route("/predictions/race/<race_label>", methods=["GET"])
+@saas_api_v1_bp.route("/predictions/race/<race_label>", methods=["GET"])
 @require_auth
 def predictions_race(race_label):
     """GET /api/v1/predictions/race/<label> — prédictions d'une course."""
@@ -187,7 +187,7 @@ def predictions_race(race_label):
 # ─── Value Bets ───────────────────────────────────────────────────────────────
 
 
-@api_v1_bp.route("/value-bets/today", methods=["GET"])
+@saas_api_v1_bp.route("/value-bets/today", methods=["GET"])
 @require_auth
 def value_bets_today():
     """GET /api/v1/value-bets/today — value bets (Premium+)."""
@@ -220,7 +220,7 @@ def value_bets_today():
 # ─── Export ───────────────────────────────────────────────────────────────────
 
 
-@api_v1_bp.route("/export/csv", methods=["GET"])
+@saas_api_v1_bp.route("/export/csv", methods=["GET"])
 @require_auth
 def export_csv():
     """GET /api/v1/export/csv — export CSV (Pro only)."""
@@ -257,15 +257,13 @@ def export_csv():
     )
 
 
-# ─── Billing Blueprint (Stripe) + JWT init — HRT-49 ─────────────────────────
+# ─── JWT init — HRT-49 ────────────────────────────────────────────────────────
-# Registers /api/v1/billing/* routes via nested Blueprint (Flask 2.0+)
+# Initialize JWTManager on the Flask app (required for jwt_required_middleware)
-# Also initializes JWTManager on the Flask app (required for jwt_required_middleware)
+# Called when saas_api_v1_bp is registered (portal_server.py)
 try:
     from flask_jwt_extended import JWTManager
-    from api_v1.routes.billing import billing_bp
 
-    # Initialize JWTManager on the Flask app when api_v1_bp is registered
+    @saas_api_v1_bp.record_once
-    @api_v1_bp.record_once
     def _init_jwt(state):
         app = state.app
         if not app.config.get("JWT_SECRET_KEY"):
@@ -276,25 +274,6 @@ try:
             )
         if "flask_jwt_extended" not in app.extensions:
             JWTManager(app)
-
+    print("[saas_api_v1] JWT init registered ✅")
-    # Register billing blueprint with url_prefix='/billing'
+except Exception as _jwt_err:
-    # (parent api_v1_bp has '/api/v1', so result is /api/v1/billing/*)
+    print(f"[saas_api_v1] Warning: JWT init not loaded: {_jwt_err}")
-    api_v1_bp.register_blueprint(billing_bp, url_prefix="/billing")
-    print("[saas_api_v1] Billing blueprint (Stripe) + JWT registered ✅")
-except Exception as _billing_err:
-    print(f"[saas_api_v1] Warning: billing blueprint not loaded: {_billing_err}")
-
-
-# ─── Org Blueprint — HRT-82 ───────────────────────────────────────────────────
-# Registers /api/v1/org/* routes (Pro plan only, multi-compte max 5 users)
-try:
-    from api_v1.routes.org import org_bp
-
-    @api_v1_bp.record_once
-    def _register_org_bp(state):
-        app = state.app
-        app.register_blueprint(org_bp)
-
-    print("[saas_api_v1] Org blueprint (multi-compte Pro) registered ✅")
-except Exception as _org_err:
-    print(f"[saas_api_v1] Warning: org blueprint not loaded: {_org_err}")

services/token-broker/.env.example

@@ -0,0 +1,10 @@
+# Token Broker API — Configuration
+TOKEN_BROKER_PORT=8783
+TOKEN_BROKER_DB_HOST=127.0.0.1
+TOKEN_BROKER_DB_PORT=5434
+TOKEN_BROKER_DB_NAME=token_broker
+TOKEN_BROKER_DB_USER=token_broker
+TOKEN_BROKER_DB_PASSWORD=CHANGE_ME
+TOKEN_BROKER_JWT_SECRET=CHANGE_ME_GENERATE_64_HEX
+TOKEN_BROKER_ACCESS_EXPIRY=900
+TOKEN_BROKER_REFRESH_EXPIRY=2592000

services/token-broker/requirements.txt

@@ -0,0 +1,6 @@
+Flask==3.1.3
+flask-cors==5.0.1
+gunicorn==23.0.0
+psycopg2-binary==2.9.12
+PyJWT==2.10.1
+python-dotenv==1.1.0

services/token-broker/token-broker.service

@@ -0,0 +1,21 @@
+[Unit]
+Description=Token Broker API (Port 8783)
+Documentation=https://portal-kolifee.duckdns.org
+After=network.target postgresql.service
+
+[Service]
+Type=simple
+User=h3r7
+WorkingDirectory=/home/h3r7/turf_saas/services/token-broker
+
+EnvironmentFile=/home/h3r7/turf_saas/services/token-broker/.env
+Environment=PYTHONPATH=/home/h3r7/turf_saas
+Environment=FLASK_ENV=production
+
+ExecStart=/home/h3r7/turf_saas/venv/bin/python3 /home/h3r7/turf_saas/services/token-broker/token_broker_api.py
+
+Restart=always
+RestartSec=10
+
+[Install]
+WantedBy=multi-user.target

services/token-broker/token_broker_api.py

@@ -0,0 +1,679 @@
+#!/usr/bin/env python3
+"""
+Token Broker API — JWT token management service
+Port: 8783 | DB: PostgreSQL 5434
+HRT-198 — Setup infra (PostgreSQL + Flask scaffold)
+
+Endpoints:
+  GET    /health          — Healthcheck
+  POST   /api/v1/tokens   — Issue new token (create)
+  GET    /api/v1/tokens/:id — Get token by ID
+  POST   /api/v1/tokens/verify — Verify token
+  POST   /api/v1/tokens/revoke/:id — Revoke token
+  GET    /api/v1/tokens/user/:userId — List tokens for user
+"""
+
+import os
+import sys
+import uuid
+import hashlib
+import secrets
+import logging
+import logging.handlers
+from datetime import datetime, timedelta, timezone
+from functools import wraps
+
+from flask import Flask, request, jsonify, g
+from flask_cors import CORS
+
+LOG_DIR = os.path.join(os.path.dirname(__file__), "logs")
+os.makedirs(LOG_DIR, exist_ok=True)
+
+logging.basicConfig(
+    level=logging.INFO,
+    format="%(asctime)s [%(levelname)s] token-broker: %(name)s: %(message)s",
+    handlers=[
+        logging.StreamHandler(sys.stdout),
+        logging.handlers.RotatingFileHandler(
+            os.path.join(LOG_DIR, "token_broker.log"),
+            maxBytes=5 * 1024 * 1024,
+            backupCount=3,
+        ),
+    ],
+)
+logger = logging.getLogger("token_broker")
+
+DB_HOST = os.environ.get("TOKEN_BROKER_DB_HOST", "127.0.0.1")
+DB_PORT = int(os.environ.get("TOKEN_BROKER_DB_PORT", "5434"))
+DB_NAME = os.environ.get("TOKEN_BROKER_DB_NAME", "token_broker")
+DB_USER = os.environ.get("TOKEN_BROKER_DB_USER", "token_broker")
+DB_PASSWORD = os.environ.get("TOKEN_BROKER_DB_PASSWORD", "")
+JWT_SECRET = os.environ.get(
+    "TOKEN_BROKER_JWT_SECRET", "CHANGE_ME_" + secrets.token_hex(32)
+)
+ACCESS_TOKEN_EXPIRY = int(os.environ.get("TOKEN_BROKER_ACCESS_EXPIRY", "900"))
+REFRESH_TOKEN_EXPIRY = int(os.environ.get("TOKEN_BROKER_REFRESH_EXPIRY", "2592000"))
+
+
+def get_pg_conn():
+    try:
+        import psycopg2
+        import psycopg2.extras
+        conn = psycopg2.connect(
+            host=DB_HOST,
+            port=DB_PORT,
+            dbname=DB_NAME,
+            user=DB_USER,
+            password=DB_PASSWORD,
+        )
+        conn.autocommit = True
+        return conn
+    except Exception as e:
+        logger.error(f"PostgreSQL connection failed: {e}")
+        return None
+
+
+def init_db():
+    conn = get_pg_conn()
+    if not conn:
+        logger.error("Cannot initialize DB — no connection")
+        return False
+    try:
+        cur = conn.cursor()
+        cur.execute("""
+            CREATE TABLE IF NOT EXISTS api_tokens (
+                id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+                user_id         INTEGER NOT NULL,
+                name            TEXT NOT NULL DEFAULT 'default',
+                token_hash      TEXT NOT NULL UNIQUE,
+                token_prefix    TEXT NOT NULL,
+                scopes          TEXT[] DEFAULT '{}',
+                is_active       BOOLEAN NOT NULL DEFAULT TRUE,
+                created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+                expires_at      TIMESTAMPTZ,
+                last_used_at    TIMESTAMPTZ,
+                metadata        JSONB DEFAULT '{}'
+            );
+
+            CREATE TABLE IF NOT EXISTS refresh_tokens (
+                id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+                user_id         INTEGER NOT NULL,
+                token_hash      TEXT NOT NULL UNIQUE,
+                token_prefix    TEXT NOT NULL,
+                created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+                expires_at      TIMESTAMPTZ NOT NULL,
+                revoked         BOOLEAN NOT NULL DEFAULT FALSE,
+                revoked_at      TIMESTAMPTZ,
+                replaced_by     UUID
+            );
+
+            CREATE TABLE IF NOT EXISTS token_audit_log (
+                id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+                user_id         INTEGER,
+                action          TEXT NOT NULL,
+                token_prefix    TEXT,
+                ip_address      TEXT,
+                user_agent      TEXT,
+                details         JSONB DEFAULT '{}',
+                created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
+            );
+
+            CREATE TABLE IF NOT EXISTS clients (
+                id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+                client_id       TEXT NOT NULL UNIQUE,
+                client_secret   TEXT NOT NULL,
+                name            TEXT NOT NULL,
+                description     TEXT DEFAULT '',
+                redirect_uris   TEXT[] DEFAULT '{}',
+                scopes          TEXT[] DEFAULT '{}',
+                is_active       BOOLEAN NOT NULL DEFAULT TRUE,
+                created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+                updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
+            );
+
+            CREATE TABLE IF NOT EXISTS providers (
+                id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+                name            TEXT NOT NULL UNIQUE,
+                provider_type   TEXT NOT NULL DEFAULT 'oauth2',
+                issuer_url      TEXT,
+                client_id       TEXT,
+                client_secret   TEXT,
+                scopes          TEXT[] DEFAULT '{}',
+                config          JSONB DEFAULT '{}',
+                is_active       BOOLEAN NOT NULL DEFAULT TRUE,
+                created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+                updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
+            );
+
+            CREATE TABLE IF NOT EXISTS token_usage (
+                id              BIGSERIAL PRIMARY KEY,
+                user_id         INTEGER NOT NULL,
+                token_id        UUID,
+                action          TEXT NOT NULL DEFAULT 'verify',
+                endpoint        TEXT,
+                status          TEXT NOT NULL DEFAULT 'success',
+                response_time_ms INTEGER,
+                ip_address      TEXT,
+                created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
+            );
+
+            CREATE INDEX IF NOT EXISTS idx_api_tokens_user_id ON api_tokens(user_id);
+            CREATE INDEX IF NOT EXISTS idx_api_tokens_token_hash ON api_tokens(token_hash);
+            CREATE INDEX IF NOT EXISTS idx_refresh_tokens_user_id ON refresh_tokens(user_id);
+            CREATE INDEX IF NOT EXISTS idx_refresh_tokens_token_hash ON refresh_tokens(token_hash);
+            CREATE INDEX IF NOT EXISTS idx_token_audit_log_user_id ON token_audit_log(user_id);
+            CREATE INDEX IF NOT EXISTS idx_token_audit_log_created_at ON token_audit_log(created_at);
+            CREATE INDEX IF NOT EXISTS idx_clients_client_id ON clients(client_id);
+            CREATE INDEX IF NOT EXISTS idx_providers_name ON providers(name);
+            CREATE INDEX IF NOT EXISTS idx_token_usage_user_id ON token_usage(user_id);
+            CREATE INDEX IF NOT EXISTS idx_token_usage_created_at ON token_usage(created_at);
+        """)
+        cur.close()
+        conn.close()
+        logger.info("Database tables initialized successfully")
+        return True
+    except Exception as e:
+        logger.error(f"Database initialization failed: {e}")
+        return False
+
+
+def create_app():
+    app = Flask(__name__)
+
+    app.config["JWT_SECRET"] = JWT_SECRET
+    app.config["ACCESS_TOKEN_EXPIRY"] = ACCESS_TOKEN_EXPIRY
+    app.config["REFRESH_TOKEN_EXPIRY"] = REFRESH_TOKEN_EXPIRY
+
+    CORS(app)
+    register_routes(app)
+    register_error_handlers(app)
+
+    return app
+
+
+def token_required(f):
+    @wraps(f)
+    def decorated(*args, **kwargs):
+        auth_header = request.headers.get("Authorization", "")
+        if not auth_header.startswith("Bearer "):
+            return jsonify({"error": "missing_token", "message": "Bearer token required"}), 401
+        token = auth_header.split(" ", 1)[1]
+        payload = verify_jwt_token(token)
+        if not payload:
+            return jsonify({"error": "invalid_token", "message": "Token invalid or expired"}), 401
+        g.user_id = payload.get("user_id")
+        g.token_id = payload.get("token_id")
+        g.scopes = payload.get("scopes", [])
+        return f(*args, **kwargs)
+    return decorated
+
+
+def generate_token_pair(user_id, scopes=None, metadata=None):
+    import jwt as pyjwt
+    now = datetime.now(timezone.utc)
+
+    access_payload = {
+        "user_id": user_id,
+        "token_id": str(uuid.uuid4()),
+        "scopes": scopes or [],
+        "type": "access",
+        "iat": now,
+        "exp": now + timedelta(seconds=ACCESS_TOKEN_EXPIRY),
+    }
+    access_token = pyjwt.encode(access_payload, JWT_SECRET, algorithm="HS256")
+
+    refresh_id = str(uuid.uuid4())
+    refresh_raw = secrets.token_urlsafe(48)
+    refresh_payload = {
+        "user_id": user_id,
+        "refresh_id": refresh_id,
+        "token_hash": hashlib.sha256(refresh_raw.encode()).hexdigest(),
+        "type": "refresh",
+        "iat": now,
+        "exp": now + timedelta(seconds=REFRESH_TOKEN_EXPIRY),
+    }
+    refresh_token = pyjwt.encode(refresh_payload, JWT_SECRET, algorithm="HS256")
+
+    store_refresh_token(user_id, refresh_id, refresh_payload["token_hash"])
+    log_audit(user_id, "token_issued", access_payload["token_id"][:8])
+
+    return {
+        "access_token": access_token,
+        "refresh_token": refresh_raw,
+        "expires_in": ACCESS_TOKEN_EXPIRY,
+        "token_type": "Bearer",
+    }
+
+
+def verify_jwt_token(token):
+    import jwt as pyjwt
+    try:
+        payload = pyjwt.decode(token, JWT_SECRET, algorithms=["HS256"])
+        if payload.get("type") == "refresh":
+            token_hash = hashlib.sha256(token.encode()).hexdigest()
+            conn = get_pg_conn()
+            if conn:
+                cur = conn.cursor()
+                cur.execute(
+                    "SELECT revoked FROM refresh_tokens WHERE token_hash = %s AND expires_at > NOW()",
+                    (token_hash,),
+                )
+                row = cur.fetchone()
+                cur.close()
+                conn.close()
+                if not row or row[0]:
+                    return None
+        return payload
+    except Exception:
+        return None
+
+
+def store_refresh_token(user_id, refresh_id, token_hash):
+    conn = get_pg_conn()
+    if not conn:
+        return
+    try:
+        cur = conn.cursor()
+        cur.execute(
+            """INSERT INTO refresh_tokens (id, user_id, token_hash, token_prefix, expires_at)
+               VALUES (%s, %s, %s, %s, NOW() + INTERVAL '30 days')""",
+            (refresh_id, user_id, token_hash, token_hash[:8]),
+        )
+        cur.close()
+        conn.close()
+    except Exception as e:
+        logger.error(f"Failed to store refresh token: {e}")
+
+
+def log_audit(user_id, action, token_prefix, details=None):
+    conn = get_pg_conn()
+    if not conn:
+        return
+    try:
+        cur = conn.cursor()
+        cur.execute(
+            """INSERT INTO token_audit_log (user_id, action, token_prefix, ip_address, user_agent, details)
+               VALUES (%s, %s, %s, %s, %s, %s)""",
+            (
+                user_id,
+                action,
+                token_prefix,
+                request.remote_addr if request else None,
+                request.user_agent.string if request and request.user_agent else None,
+                "{}" if details is None else details,
+            ),
+        )
+        cur.close()
+        conn.close()
+    except Exception:
+        pass
+
+
+def register_routes(app):
+    @app.route("/health", methods=["GET"])
+    def healthcheck():
+        conn = get_pg_conn()
+        db_ok = conn is not None
+        if conn:
+            conn.close()
+        return jsonify({
+            "status": "ok" if db_ok else "degraded",
+            "service": "token-broker",
+            "version": "1.0.0",
+            "database": "connected" if db_ok else "disconnected",
+            "timestamp": datetime.now(timezone.utc).isoformat(),
+        }), 200 if db_ok else 503
+
+    @app.route("/api/v1/tokens", methods=["POST"])
+    @token_required
+    def issue_token():
+        data = request.get_json(silent=True) or {}
+        user_id = g.user_id
+        scopes = data.get("scopes", [])
+        name = data.get("name", "default")
+        metadata = data.get("metadata", {})
+
+        conn = get_pg_conn()
+        if not conn:
+            return jsonify({"error": "db_error", "message": "Database unavailable"}), 503
+
+        try:
+            cur = conn.cursor()
+            import psycopg2.extras
+            raw_token = "tb_" + secrets.token_urlsafe(32)
+            token_hash = hashlib.sha256(raw_token.encode()).hexdigest()
+            token_prefix = raw_token[:12] + "..."
+
+            cur.execute(
+                """INSERT INTO api_tokens (user_id, name, token_hash, token_prefix, scopes, metadata)
+                   VALUES (%s, %s, %s, %s, %s, %s)
+                   RETURNING id, created_at, expires_at""",
+                (user_id, name, token_hash, token_prefix, scopes,
+                 psycopg2.extras.Json(metadata)),
+            )
+            row = cur.fetchone()
+            cur.close()
+            conn.close()
+
+            log_audit(user_id, "api_token_created", token_prefix)
+            return jsonify({
+                "id": str(row[0]),
+                "token": raw_token,
+                "name": name,
+                "scopes": scopes,
+                "created_at": row[1].isoformat(),
+                "expires_at": row[2].isoformat() if row[2] else None,
+            }), 201
+        except Exception as e:
+            logger.error(f"Token creation failed: {e}")
+            return jsonify({"error": "creation_failed", "message": str(e)}), 500
+
+    @app.route("/api/v1/tokens/verify", methods=["POST"])
+    def verify_token():
+        data = request.get_json(silent=True) or {}
+        raw_token = data.get("token", "")
+
+        if not raw_token:
+            return jsonify({"valid": False, "error": "token_required"}), 400
+
+        token_hash = hashlib.sha256(raw_token.encode()).hexdigest()
+        conn = get_pg_conn()
+        if not conn:
+            return jsonify({"valid": False, "error": "db_error"}), 503
+
+        try:
+            cur = conn.cursor()
+            cur.execute(
+                """SELECT id, user_id, name, scopes, is_active, created_at, expires_at, last_used_at
+                   FROM api_tokens
+                   WHERE token_hash = %s""",
+                (token_hash,),
+            )
+            row = cur.fetchone()
+
+            if not row:
+                cur.close()
+                conn.close()
+                return jsonify({"valid": False, "error": "token_not_found"}), 404
+
+            token_id, user_id, name, scopes, is_active, created_at, expires_at, last_used_at = row
+
+            if not is_active:
+                cur.close()
+                conn.close()
+                return jsonify({"valid": False, "error": "token_revoked"}), 403
+
+            if expires_at and expires_at < datetime.now(timezone.utc):
+                cur.close()
+                conn.close()
+                return jsonify({"valid": False, "error": "token_expired"}), 403
+
+            cur.execute(
+                "UPDATE api_tokens SET last_used_at = NOW() WHERE id = %s",
+                (token_id,),
+            )
+            cur.close()
+            conn.close()
+
+            return jsonify({
+                "valid": True,
+                "token_id": str(token_id),
+                "user_id": user_id,
+                "name": name,
+                "scopes": scopes,
+            })
+        except Exception as e:
+            logger.error(f"Token verification failed: {e}")
+            return jsonify({"valid": False, "error": "verification_failed"}), 500
+
+    @app.route("/api/v1/tokens/<token_id>", methods=["GET"])
+    @token_required
+    def get_token(token_id):
+        conn = get_pg_conn()
+        if not conn:
+            return jsonify({"error": "db_error"}), 503
+        try:
+            cur = conn.cursor()
+            cur.execute(
+                """SELECT id, user_id, name, scopes, is_active, created_at, expires_at, last_used_at, metadata
+                   FROM api_tokens WHERE id = %s AND user_id = %s""",
+                (token_id, g.user_id),
+            )
+            row = cur.fetchone()
+            cur.close()
+            conn.close()
+
+            if not row:
+                return jsonify({"error": "not_found"}), 404
+
+            return jsonify({
+                "id": str(row[0]),
+                "user_id": row[1],
+                "name": row[2],
+                "scopes": row[3],
+                "is_active": row[4],
+                "created_at": row[5].isoformat(),
+                "expires_at": row[6].isoformat() if row[6] else None,
+                "last_used_at": row[7].isoformat() if row[7] else None,
+                "metadata": row[8] if row[8] else {},
+            })
+        except Exception as e:
+            logger.error(f"Get token failed: {e}")
+            return jsonify({"error": "query_failed"}), 500
+
+    @app.route("/api/v1/tokens/revoke/<token_id>", methods=["POST"])
+    @token_required
+    def revoke_token(token_id):
+        conn = get_pg_conn()
+        if not conn:
+            return jsonify({"error": "db_error"}), 503
+        try:
+            cur = conn.cursor()
+            cur.execute(
+                """UPDATE api_tokens SET is_active = FALSE WHERE id = %s AND user_id = %s
+                   RETURNING id, name""",
+                (token_id, g.user_id),
+            )
+            row = cur.fetchone()
+            cur.close()
+            conn.close()
+
+            if not row:
+                return jsonify({"error": "not_found"}), 404
+
+            log_audit(g.user_id, "api_token_revoked", str(row[0])[:8])
+            return jsonify({"status": "revoked", "token_id": str(row[0])})
+        except Exception as e:
+            logger.error(f"Revoke token failed: {e}")
+            return jsonify({"error": "revoke_failed"}), 500
+
+    @app.route("/api/v1/tokens/user/<int:user_id>", methods=["GET"])
+    @token_required
+    def list_user_tokens(user_id):
+        if g.user_id != user_id and "admin" not in g.scopes:
+            return jsonify({"error": "forbidden"}), 403
+
+        conn = get_pg_conn()
+        if not conn:
+            return jsonify({"error": "db_error"}), 503
+        try:
+            cur = conn.cursor()
+            cur.execute(
+                """SELECT id, user_id, name, scopes, is_active, created_at, expires_at, last_used_at
+                   FROM api_tokens
+                   WHERE user_id = %s
+                   ORDER BY created_at DESC""",
+                (user_id,),
+            )
+            rows = cur.fetchall()
+            cur.close()
+            conn.close()
+
+            tokens = []
+            for row in rows:
+                tokens.append({
+                    "id": str(row[0]),
+                    "user_id": row[1],
+                    "name": row[2],
+                    "scopes": row[3],
+                    "is_active": row[4],
+                    "created_at": row[5].isoformat(),
+                    "expires_at": row[6].isoformat() if row[6] else None,
+                    "last_used_at": row[7].isoformat() if row[7] else None,
+                })
+            return jsonify({"tokens": tokens, "total": len(tokens)})
+        except Exception as e:
+            logger.error(f"List tokens failed: {e}")
+            return jsonify({"error": "query_failed"}), 500
+
+    @app.route("/api/v1/auth/token", methods=["POST"])
+    def exchange_token():
+        data = request.get_json(silent=True) or {}
+        grant_type = data.get("grant_type", "client_credentials")
+        raw_token = data.get("client_token", "") or data.get("token", "")
+        refresh_raw = data.get("refresh_token", "")
+
+        if grant_type == "refresh_token" and refresh_raw:
+            return refresh_access_token(refresh_raw)
+
+        if not raw_token:
+            return jsonify({"error": "invalid_request", "message": "client_token required"}), 400
+
+        token_hash = hashlib.sha256(raw_token.encode()).hexdigest()
+        conn = get_pg_conn()
+        if not conn:
+            return jsonify({"error": "db_error"}), 503
+
+        try:
+            cur = conn.cursor()
+            cur.execute(
+                """SELECT id, user_id, scopes, is_active, expires_at
+                   FROM api_tokens WHERE token_hash = %s""",
+                (token_hash,),
+            )
+            row = cur.fetchone()
+            cur.close()
+            conn.close()
+
+            if not row:
+                return jsonify({"error": "invalid_token"}), 401
+            if not row[3]:
+                return jsonify({"error": "token_revoked"}), 403
+            if row[4] and row[4] < datetime.now(timezone.utc):
+                return jsonify({"error": "token_expired"}), 403
+
+            token_pair = generate_token_pair(row[1], row[2])
+            return jsonify(token_pair), 200
+        except Exception as e:
+            logger.error(f"Token exchange failed: {e}")
+            return jsonify({"error": "exchange_failed"}), 500
+
+    @app.route("/api/v1/auth/refresh", methods=["POST"])
+    def refresh_token_endpoint():
+        data = request.get_json(silent=True) or {}
+        refresh_raw = data.get("refresh_token", "")
+        return refresh_access_token(refresh_raw)
+
+    @app.route("/api/v1/auth/revoke", methods=["POST"])
+    @token_required
+    def revoke_refresh_token():
+        data = request.get_json(silent=True) or {}
+        refresh_raw = data.get("refresh_token", "")
+
+        if not refresh_raw:
+            return jsonify({"error": "refresh_token_required"}), 400
+
+        token_hash = hashlib.sha256(refresh_raw.encode()).hexdigest()
+        conn = get_pg_conn()
+        if not conn:
+            return jsonify({"error": "db_error"}), 503
+        try:
+            cur = conn.cursor()
+            cur.execute(
+                "UPDATE refresh_tokens SET revoked = TRUE, revoked_at = NOW() WHERE token_hash = %s",
+                (token_hash,),
+            )
+            cur.close()
+            conn.close()
+            log_audit(g.user_id, "refresh_token_revoked", token_hash[:8])
+            return jsonify({"status": "revoked"})
+        except Exception as e:
+            logger.error(f"Revoke refresh token failed: {e}")
+            return jsonify({"error": "revoke_failed"}), 500
+
+
+def refresh_access_token(refresh_raw):
+    if not refresh_raw:
+        return jsonify({"error": "refresh_token_required"}), 400
+
+    token_hash = hashlib.sha256(refresh_raw.encode()).hexdigest()
+    conn = get_pg_conn()
+    if not conn:
+        return jsonify({"error": "db_error"}), 503
+    try:
+        cur = conn.cursor()
+        cur.execute(
+            """SELECT id, user_id, revoked, expires_at
+               FROM refresh_tokens WHERE token_hash = %s""",
+            (token_hash,),
+        )
+        row = cur.fetchone()
+
+        if not row:
+            cur.close()
+            conn.close()
+            return jsonify({"error": "invalid_token"}), 401
+        if row[2]:
+            cur.close()
+            conn.close()
+            return jsonify({"error": "token_revoked"}), 403
+        if row[3] < datetime.now(timezone.utc):
+            cur.close()
+            conn.close()
+            return jsonify({"error": "token_expired"}), 403
+
+        refresh_id = row[0]
+        user_id = row[1]
+
+        cur.execute(
+            "UPDATE refresh_tokens SET revoked = TRUE, revoked_at = NOW() WHERE id = %s",
+            (refresh_id,),
+        )
+
+        pairs = generate_token_pair(user_id)
+        cur.close()
+        conn.close()
+        return jsonify(pairs), 200
+    except Exception as e:
+        logger.error(f"Refresh token failed: {e}")
+        return jsonify({"error": "refresh_failed"}), 500
+
+
+def register_error_handlers(app):
+    @app.errorhandler(404)
+    def not_found(e):
+        return jsonify({"error": "not_found", "message": "Route not found"}), 404
+
+    @app.errorhandler(405)
+    def method_not_allowed(e):
+        return jsonify({"error": "method_not_allowed", "message": "Method not allowed"}), 405
+
+    @app.errorhandler(500)
+    def internal_error(e):
+        logger.error(f"Internal error: {e}")
+        return jsonify({"error": "internal_error", "message": "Internal server error"}), 500
+
+
+if __name__ == "__main__":
+    logger.info("=" * 60)
+    logger.info("Token Broker API starting...")
+    logger.info(f"DB: {DB_HOST}:{DB_PORT}/{DB_NAME}")
+    logger.info(f"Port: {os.environ.get('TOKEN_BROKER_PORT', '8783')}")
+    logger.info("=" * 60)
+
+    init_db()
+
+    port = int(os.environ.get("TOKEN_BROKER_PORT", "8783"))
+    debug = os.environ.get("FLASK_ENV", "production") == "development"
+    app = create_app()
+    app.run(host="0.0.0.0", port=port, debug=debug)

tests/test_ai_router.py

@@ -0,0 +1,255 @@
+"""Unit tests for AI Router — router, providers, models, API."""
+
+import json
+import os
+import tempfile
+import pytest
+from unittest.mock import patch, MagicMock
+
+TEST_DB = os.path.join(tempfile.mkdtemp(), "test_ai_router.db")
+os.environ["AI_ROUTER_DB"] = TEST_DB
+os.environ["OPENAI_API_KEY"] = "sk-test-openai"
+os.environ["ANTHROPIC_API_KEY"] = "sk-test-anthropic"
+os.environ["GOOGLE_API_KEY"] = "sk-test-google"
+os.environ["MISTRAL_API_KEY"] = "sk-test-mistral"
+
+
+@pytest.fixture(autouse=True)
+def clean_db():
+    yield
+    try:
+        os.remove(TEST_DB)
+    except OSError:
+        pass
+
+
+@pytest.fixture
+def app():
+    from ai_router_api import create_app
+    app = create_app()
+    app.config["TESTING"] = True
+    return app
+
+
+@pytest.fixture
+def client(app):
+    return app.test_client()
+
+
+@pytest.fixture
+def router():
+    from ai_router.router import AIRouter
+    return AIRouter()
+
+
+# ─────────────────────────────────────────────
+# Provider Base Tests
+# ─────────────────────────────────────────────
+
+
+class TestProviderInterface:
+    def test_provider_map_has_all(self):
+        from ai_router.providers import PROVIDER_MAP
+        assert "openai" in PROVIDER_MAP
+        assert "anthropic" in PROVIDER_MAP
+        assert "google" in PROVIDER_MAP
+        assert "mistral" in PROVIDER_MAP
+
+    def test_api_key_resolution_env(self):
+        from ai_router.providers.base import AIProvider
+        class TestProvider(AIProvider):
+            @property
+            def name(self): return "openai"
+            def chat(self, messages, model, **kwargs): return {}
+            def models(self): return []
+            def check_health(self): return {"status": "ok"}
+
+        p = TestProvider()
+        assert p.get_api_key() == "sk-test-openai"
+
+    def test_api_key_resolution_db_overrides_env(self):
+        from ai_router.providers.base import AIProvider
+        class TestProvider(AIProvider):
+            @property
+            def name(self): return "openai"
+            def chat(self, messages, model, **kwargs): return {}
+            def models(self): return []
+            def check_health(self): return {"status": "ok"}
+
+        p = TestProvider()
+        assert p.get_api_key({"api_key": "sk-db-key"}) == "sk-db-key"
+
+
+# ─────────────────────────────────────────────
+# Router Tests
+# ─────────────────────────────────────────────
+
+
+class TestRouter:
+    def test_resolve_known_model(self, router):
+        info = router._resolve_model("gpt-4o")
+        assert info is not None
+        assert info["provider"] == "openai"
+        assert info["real_model"] == "gpt-4o"
+
+    def test_resolve_unknown_model(self, router):
+        info = router._resolve_model("nonexistent-model")
+        assert info is None
+
+    def test_list_models_includes_defaults(self, router):
+        models = router.list_available_models()
+        aliases = [m["alias"] for m in models]
+        assert "gpt-4o" in aliases
+        assert "claude-3-opus" in aliases
+        assert "gemini-pro" in aliases
+        assert "mistral-large" in aliases
+
+    def test_prioritized_providers_default_order(self, router):
+        providers = router._get_prioritized_providers()
+        names = [p[0] for p in providers]
+        assert names == ["openai", "anthropic", "google", "mistral"]
+
+    def test_chat_unknown_model(self, router):
+        result = router.chat([{"role": "user", "content": "hi"}], "unknown-model")
+        assert result["status"] == "error"
+
+    @patch("ai_router.providers.openai_adapter.OpenAIAdapter.chat")
+    def test_chat_success_first_provider(self, mock_chat, router):
+        mock_chat.return_value = {
+            "content": "Hello!",
+            "model": "gpt-4o",
+            "provider": "openai",
+            "usage": {"prompt_tokens": 10, "completion_tokens": 5, "total_tokens": 15},
+        }
+        result = router.chat([{"role": "user", "content": "hi"}], "gpt-4o")
+        assert result["status"] == "success"
+        assert result["content"] == "Hello!"
+        assert result["provider"] == "openai"
+
+    @patch("ai_router.providers.openai_adapter.OpenAIAdapter.chat")
+    @patch("ai_router.providers.anthropic_adapter.AnthropicAdapter.chat")
+    def test_chat_failover_to_second_provider(self, mock_anthropic, mock_openai, router):
+        mock_openai.side_effect = Exception("OpenAI down")
+        mock_anthropic.return_value = {
+            "content": "Hello from Anthropic!",
+            "model": "claude-3-sonnet-20240229",
+            "provider": "anthropic",
+            "usage": {"prompt_tokens": 10, "completion_tokens": 5, "total_tokens": 15},
+        }
+        result = router.chat([{"role": "user", "content": "hi"}], "claude-3-sonnet")
+        assert result["status"] == "success"
+        assert result["provider"] == "anthropic"
+
+    @patch("ai_router.providers.openai_adapter.OpenAIAdapter.chat")
+    @patch("ai_router.providers.anthropic_adapter.AnthropicAdapter.chat")
+    @patch("ai_router.providers.google_adapter.GoogleAdapter.chat")
+    @patch("ai_router.providers.mistral_adapter.MistralAdapter.chat")
+    def test_chat_all_providers_fail(self, mock_mistral, mock_google, mock_anthropic, mock_openai, router):
+        for mock in (mock_openai, mock_anthropic, mock_google, mock_mistral):
+            mock.side_effect = Exception("Provider unavailable")
+        result = router.chat([{"role": "user", "content": "hi"}], "gpt-4o")
+        assert result["status"] == "error"
+        assert "All providers failed" in result["error"]
+
+    def test_health_all_providers_returns_dict(self, router):
+        health = router.check_all_providers_health()
+        for name in ("openai", "anthropic", "google", "mistral"):
+            assert name in health
+            assert "status" in health[name]
+
+
+# ─────────────────────────────────────────────
+# Database Tests
+# ─────────────────────────────────────────────
+
+
+class TestModels:
+    def test_init_db_creates_tables(self):
+        from ai_router.models import init_db, get_db
+        init_db()
+        conn = get_db()
+        tables = conn.execute(
+            "SELECT name FROM sqlite_master WHERE type='table'"
+        ).fetchall()
+        names = [r[0] for r in tables]
+        assert "ai_providers" in names
+        assert "ai_model_mapping" in names
+        assert "ai_router_log" in names
+        conn.close()
+
+    def test_upsert_and_get_providers(self):
+        from ai_router.models import init_db, upsert_provider, get_providers_from_db
+        init_db()
+        upsert_provider("Test OpenAI", "openai", "sk-test", priority=1)
+        providers = get_providers_from_db()
+        assert len(providers) > 0
+        assert any(p["name"] == "Test OpenAI" for p in providers)
+
+    def test_log_router_attempt(self):
+        from ai_router.models import init_db, log_router_attempt, get_db
+        init_db()
+        log_router_attempt("req-1", 42, "gpt-4o", "openai", 10, 5, 200, "success")
+        conn = get_db()
+        rows = conn.execute("SELECT * FROM ai_router_log").fetchall()
+        assert len(rows) == 1
+        assert rows[0]["status"] == "success"
+        conn.close()
+
+
+# ─────────────────────────────────────────────
+# API Blueprint Tests
+# ─────────────────────────────────────────────
+
+
+class TestAPI:
+    def test_health_endpoint(self, client):
+        resp = client.get("/api/v1/ai/health")
+        assert resp.status_code in (200, 503)
+        data = resp.get_json()
+        assert data["service"] == "ai-router"
+        assert "providers" in data
+
+    def test_models_endpoint(self, client):
+        resp = client.get("/api/v1/ai/models")
+        assert resp.status_code == 200
+        data = resp.get_json()
+        assert "models" in data
+        assert len(data["models"]) > 0
+
+    def test_chat_no_auth(self, client):
+        resp = client.post(
+            "/api/v1/ai/chat",
+            json={"messages": [{"role": "user", "content": "hi"}], "model": "gpt-4o"},
+        )
+        assert resp.status_code == 401
+
+    def test_chat_no_messages(self, client):
+        resp = client.post(
+            "/api/v1/ai/chat",
+            json={"model": "gpt-4o"},
+            headers={"X-API-Key": "test-key"},
+        )
+        assert resp.status_code == 401
+
+    @patch("ai_router.utils.verify_token_via_broker")
+    @patch("ai_router.providers.openai_adapter.OpenAIAdapter.chat")
+    def test_chat_success_with_auth(self, mock_chat, mock_verify, client):
+        mock_verify.return_value = {"valid": True, "user_id": 1, "scopes": ["user"]}
+        mock_chat.return_value = {
+            "content": "Hello!",
+            "model": "gpt-4o",
+            "provider": "openai",
+            "usage": {"prompt_tokens": 10, "completion_tokens": 5, "total_tokens": 15},
+        }
+        resp = client.post(
+            "/api/v1/ai/chat",
+            json={"messages": [{"role": "user", "content": "hi"}], "model": "gpt-4o"},
+            headers={"Authorization": "Bearer test-token"},
+        )
+        data = resp.get_json()
+        assert resp.status_code == 200, f"Got {resp.status_code}: {data}"
+        assert data["status"] == "success"
+
+    def test_usage_requires_admin(self, client):
+        resp = client.get("/api/v1/ai/usage")
+        assert resp.status_code == 401

tests/test_history.py

@@ -52,6 +52,9 @@ def auth_header(token: str) -> dict:
 
 @pytest.fixture(scope="module")
 def app():
+    # Enforce this module s temp DB
+    os.environ["TURF_SAAS_DB"] = _tmp_db.name
+    os.environ["JWT_SECRET_KEY"] = "test-history-secret-key"
     application = create_app()
     application.config["TESTING"] = True
     application.config["JWT_SECRET_KEY"] = "test-history-secret-key"
@@ -70,7 +73,14 @@ def seeded_db():
     - Create ml_predictions_cache with rows spanning 120 days back
     - Create users for free/premium/pro plans
     """
-    db_path = os.environ["TURF_SAAS_DB"]
+    # Reset TURF_SAAS_DB to this module-s temp DB at runtime
+    os.environ["TURF_SAAS_DB"] = _tmp_db.name
+    db_path = _tmp_db.name
+
+    # Ensure auth tables (users, refresh_tokens, subscriptions) exist in the test DB
+    # init_auth_tables() is idempotent — safe to call even if tables already exist
+    init_auth_tables()
+
     conn = sqlite3.connect(db_path)
 
     # Create ml_predictions_cache table if absent
@@ -124,7 +134,9 @@ def auth_tokens(client, seeded_db):
         assert r.status_code in (201, 409), f"register failed for {plan}: {r.data}"
 
     # Set plan via direct DB
-    db_path = os.environ["TURF_SAAS_DB"]
+    # Reset TURF_SAAS_DB to this module-s temp DB at runtime
+    os.environ["TURF_SAAS_DB"] = _tmp_db.name
+    db_path = _tmp_db.name
     conn = sqlite3.connect(db_path)
     for plan, email in plans.items():
         conn.execute("UPDATE users SET plan = ? WHERE email = ?", (plan, email))

tests/test_user_tokens.py

@@ -36,6 +36,7 @@ os.environ["JWT_SECRET_KEY"] = "test-secret-hrt80"
 sys.path.insert(0, os.path.dirname(os.path.dirname(__file__)))
 
 from app_v1 import create_app  # noqa: E402
+from api_tokens_db import migrate_api_tokens_tables  # noqa: E402
 
 TEST_CONFIG = {
     "TESTING": True,
@@ -45,6 +46,10 @@ TEST_CONFIG = {
 
 @pytest.fixture(scope="module")
 def app():
+    # Enforce this module s temp DB at fixture runtime
+    os.environ["TURF_SAAS_DB"] = _tmp_db.name
+    os.environ["JWT_SECRET_KEY"] = "test-secret-hrt80"
+    migrate_api_tokens_tables()  # ensure tables exist in THIS module s temp DB
     application = create_app()
     application.config.update(TEST_CONFIG)
     yield application

turf_scheduler.py

@@ -107,6 +107,34 @@ def run_analytics():
         traceback.print_exc()
 
 
+def run_sync_turf_db():
+    """Synchronise turf.db vers turf_saas.db"""
+    logger.info("🔄 [SCHEDULER] Sync turf.db -> turf_saas.db...")
+    try:
+        import subprocess
+
+        result = subprocess.run(
+            [
+                "python3",
+                "/home/h3r7/turf_saas/sync_turf_db.py",
+                "--date",
+                datetime.now().strftime("%Y-%m-%d"),
+            ],
+            capture_output=True,
+            text=True,
+            timeout=300,
+        )
+        if result.returncode == 0:
+            logger.info("✅ [SCHEDULER] Sync turf.db terminé")
+        else:
+            logger.error(f"❌ [SCHEDULER] Sync turf.db échoué: {result.stderr}")
+    except Exception as e:
+        logger.error(f"❌ [SCHEDULER] Erreur sync turf.db: {e}")
+        import traceback
+
+        traceback.print_exc()
+
+
 def get_todays_race_time():
     """Récupère l'heure de la course principale du jour depuis la DB
     Returns: timestamp en ms ou None
@@ -315,6 +343,16 @@ def main():
     schedule.every().day.at("20:00").do(run_results).tag("results", "daily_fallback")
     schedule.every().day.at("19:00").do(run_scraper).tag("scraper", "late_evening")
 
+    # Sync turf.db -> turf_saas.db (2x/jour: post-scraping + post-cotes)
+    schedule.every().day.at("11:00").do(run_sync_turf_db).tag("sync", "post_scraping")
+    schedule.every().day.at("17:00").do(run_sync_turf_db).tag("sync", "post_cotes")
+
+    # ML Cache: populate ml_predictions_cache après chaque sync
+    schedule.every().day.at("11:35").do(run_ml_cache).tag("ml_cache", "post_sync_am")
+    schedule.every().day.at("17:35").do(run_ml_cache).tag("ml_cache", "post_sync_pm")
+    schedule.every().day.at("09:30").do(run_ml_cache).tag("ml_cache", "morning")
+    schedule.every().day.at("13:30").do(run_ml_cache).tag("ml_cache", "pre_race")
+
     schedule.every().sunday.at("02:00").do(run_ml).tag("ml", "weekly")
     schedule.every().wednesday.at("02:00").do(run_ml).tag("ml", "midweek")
 
@@ -335,6 +373,200 @@ def main():
         time.sleep(30)
 
 
+def run_ml_cache():
+    """Populate ml_predictions_cache with ensemble (predict_v2) predictions"""
+    logger.info("🤖 [SCHEDULER] Mise à jour cache prédictions ML (ensemble)...")
+    try:
+        os.chdir("/home/h3r7/turf_saas")
+        import predict_v2
+
+        model = predict_v2.load_ensemble()
+        if model is None:
+            logger.warning("⚠️ [SCHEDULER] Ensemble model not available, skipping")
+            return
+
+        conn = sqlite3.connect(DB_PATH)
+        conn.row_factory = sqlite3.Row
+        today = datetime.now().strftime("%Y-%m-%d")
+
+        rows = conn.execute("""
+            SELECT p.*, c.distance, c.discipline, c.specialite,
+                   c.nb_declares_partants, c.montant_prix, c.penetrometre_intitule,
+                   c.libelle as course_libelle, c.libelle_court as hippodrome,
+                   c.heure_depart_str, c.parcours
+            FROM pmu_partants p
+            LEFT JOIN pmu_courses c ON p.date_programme = c.date_programme
+                AND p.num_reunion = c.num_reunion AND p.num_course = c.num_course
+            WHERE p.date_programme = ?
+            ORDER BY p.num_reunion, p.num_course, p.num_pmu
+        """, (today,)).fetchall()
+
+        if not rows:
+            logger.info("ℹ️ [SCHEDULER] No partants today, skipping ML cache")
+            conn.close()
+            return
+
+        partants = [dict(r) for r in rows]
+
+        course_lookup = {}
+        for p in partants:
+            key = (p["num_reunion"], p["num_course"])
+            if key not in course_lookup:
+                course_lookup[key] = {
+                    "libelle": p.get("course_libelle", ""),
+                    "libelle_court": p.get("hippodrome", ""),
+                    "discipline": p.get("discipline", ""),
+                    "distance": p.get("distance", 0),
+                    "heure_depart_str": p.get("heure_depart_str", ""),
+                }
+
+        odds_by_horse = {}
+        for p in partants:
+            odds_by_horse[(p["num_reunion"], p["num_course"], p["num_pmu"])] = p.get("cote_direct", 0)
+
+        preds = predict_v2.predict_top3(partants, model=model)
+        if not preds:
+            logger.warning("⚠️ [SCHEDULER] No predictions generated")
+            conn.close()
+            return
+
+        enriched = []
+        for p in preds:
+            key = (p.get("num_reunion"), p.get("num_course"))
+            ci = course_lookup.get(key, {})
+            odds_key = (p.get("num_reunion"), p.get("num_course"), p.get("num_pmu"))
+            enriched.append({
+                "num_reunion": p.get("num_reunion"),
+                "num_course": p.get("num_course"),
+                "horse_name": p.get("horse_name"),
+                "horse_number": p.get("num_pmu"),
+                "odds": odds_by_horse.get(odds_key, 0),
+                "prob_top1": p.get("prob_top1"),
+                "prob_top3": p.get("prob_top3"),
+                "ml_score": p.get("ml_score"),
+                "recommendation": p.get("recommendation"),
+                "is_value_bet": p.get("is_value_bet", 0),
+                "is_outlier": 0,
+                "race_label": f"R{p.get('num_reunion', 0)}C{p.get('num_course', 0)}",
+                "race_name": ci.get("libelle", ""),
+                "hippodrome": ci.get("libelle_court", ""),
+                "discipline": ci.get("discipline", ""),
+                "distance": ci.get("distance", 0),
+                "heure": ci.get("heure_depart_str", ""),
+            })
+
+        # Calculate risques per race (same logic as dashboard_api.calculate_risque)
+        from collections import defaultdict
+        race_horses = defaultdict(list)
+        for p in enriched:
+            rkey = (p.get("num_reunion"), p.get("num_course"))
+            race_horses[rkey].append({
+                "odds": p.get("odds", 999),
+                "ml_score": p.get("ml_score", 0),
+                "prob_top1": p.get("prob_top1", 0),
+                "prob_top3": p.get("prob_top3", 0),
+            })
+
+        race_risque = {}
+        for rkey, partants_list in race_horses.items():
+            label, score = _calc_risque(partants_list)
+            race_risque[rkey] = (label or "neutral", score or 50)
+
+        # Ensure table exists with all columns
+        conn.execute("""
+            CREATE TABLE IF NOT EXISTS ml_predictions_cache (
+                id INTEGER PRIMARY KEY AUTOINCREMENT,
+                date TEXT NOT NULL, num_reunion INTEGER, num_course INTEGER,
+                horse_name TEXT, horse_number INTEGER, odds REAL,
+                prob_top1 REAL, prob_top3 REAL, ml_score REAL,
+                recommendation TEXT, is_value_bet INTEGER DEFAULT 0,
+                is_outlier INTEGER DEFAULT 0, race_label TEXT, race_name TEXT,
+                hippodrome TEXT, discipline TEXT, distance REAL, heure TEXT,
+                model_version TEXT DEFAULT 'xgboost_v1',
+                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+                risque_label TEXT DEFAULT 'neutral', risque_score INTEGER DEFAULT 50,
+                UNIQUE(date, num_reunion, num_course, horse_name)
+            )
+        """)
+        conn.execute("CREATE INDEX IF NOT EXISTS idx_ml_cache_date ON ml_predictions_cache(date)")
+
+        try:
+            conn.execute("ALTER TABLE ml_predictions_cache ADD COLUMN risque_label TEXT DEFAULT 'neutral'")
+        except Exception:
+            pass
+        try:
+            conn.execute("ALTER TABLE ml_predictions_cache ADD COLUMN risque_score INTEGER DEFAULT 50")
+        except Exception:
+            pass
+
+        conn.execute("DELETE FROM ml_predictions_cache WHERE date = ?", (today,))
+
+        for p in enriched:
+            rkey = (p.get("num_reunion"), p.get("num_course"))
+            rl, rs = race_risque.get(rkey, ("neutral", 50))
+            conn.execute("""
+                INSERT INTO ml_predictions_cache
+                    (date, num_reunion, num_course, horse_name, horse_number, odds,
+                     prob_top1, prob_top3, ml_score, recommendation, is_value_bet, is_outlier,
+                     race_label, race_name, hippodrome, discipline, distance, heure,
+                     risque_label, risque_score, model_version)
+                VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
+            """, (
+                today, p.get("num_reunion"), p.get("num_course"),
+                p.get("horse_name"), p.get("horse_number"), p.get("odds"),
+                p.get("prob_top1"), p.get("prob_top3"), p.get("ml_score"),
+                p.get("recommendation"), p.get("is_value_bet", 0), p.get("is_outlier", 0),
+                p.get("race_label"), p.get("race_name"), p.get("hippodrome"),
+                p.get("discipline"), p.get("distance"), p.get("heure"),
+                rl, rs, "ensemble_v1",
+            ))
+
+        conn.commit()
+        conn.close()
+        logger.info(f"✅ [SCHEDULER] ML cache mis à jour: {len(enriched)} prédictions pour {today}")
+
+    except Exception as e:
+        logger.error(f"❌ [SCHEDULER] Erreur ML cache: {e}")
+        import traceback
+        traceback.print_exc()
+
+
+def _calc_risque(partants_list):
+    """Same logic as dashboard_api.calculate_risque — kept local to avoid import side effects"""
+    if not partants_list:
+        return None, None
+    sorted_p = sorted(
+        partants_list,
+        key=lambda x: x.get("ml_score") or x.get("prob_top1") or 0,
+        reverse=True,
+    )
+    top1_score = sorted_p[0].get("ml_score") or sorted_p[0].get("prob_top1") or 0
+    top2_score = (
+        sorted_p[1].get("ml_score") or sorted_p[1].get("prob_top1") or 0
+        if len(sorted_p) > 1 else 0
+    )
+    gap_1_2 = top1_score - top2_score
+    nb_dangerous = sum(1 for p in sorted_p if (p.get("ml_score") or 0) > 40)
+    odds_fav = sorted(partants_list, key=lambda x: x.get("odds") or 999)
+    fav_odds = odds_fav[0].get("odds") or 999 if odds_fav else 999
+    fav_ml = (
+        odds_fav[0].get("ml_score") or odds_fav[0].get("prob_top1") or 0
+        if odds_fav else 0
+    )
+    fav_surprise = fav_odds < 5 and fav_ml < 25
+    if top1_score >= 65 and gap_1_2 >= 20:
+        score = min(100, int(50 + gap_1_2 * 1.5))
+        return "safe", score
+    if fav_surprise:
+        return "trap", max(10, int(35 - (25 - fav_ml)))
+    if nb_dangerous >= 4 and top1_score < 70:
+        return "trap", max(10, int(40 - nb_dangerous * 2))
+    if gap_1_2 < 8 and top2_score > 45:
+        return "trap", max(15, int(30 + gap_1_2))
+    score = min(64, max(35, int(35 + gap_1_2 * 1.2)))
+    return "neutral", score
+
+
 def run_metrics_alerts():
     """Verifie les metriques du jour et envoie une alerte email si ROI > 1.0€"""
     logger.info("📧 [SCHEDULER] Vérification alertes métriques...")