from __future__ import annotations

import logging
from typing import Any

logger = logging.getLogger(__name__)


class ErpAiModelsQueryMixin:
    def _ensure_stealth_columns(self) -> None:
        """Đảm bảo bảng erp_ai_agent_budgets có đầy đủ các cột quản lý vòng đời mô hình Stealth."""
        sql = """
            ALTER TABLE erp_ai_agent_budgets
            ADD COLUMN IF NOT EXISTS is_stealth_promo BOOLEAN NOT NULL DEFAULT FALSE,
            ADD COLUMN IF NOT EXISTS stealth_expires_at TIMESTAMPTZ,
            ADD COLUMN IF NOT EXISTS fallback_model_name VARCHAR(150) NOT NULL DEFAULT 'gemini-3.8-flash',
            ADD COLUMN IF NOT EXISTS fallback_provider VARCHAR(50) NOT NULL DEFAULT 'antigravity_sdk';
        """
        try:
            with self.get_connection() as conn, conn.cursor() as cur:
                cur.execute(sql)
                conn.commit()
        except Exception:
            pass

    def list_ai_agent_models(self) -> list[dict[str, Any]]:
        """Lấy danh sách cấu hình LLM Model và Provider của tất cả 9 AI Personas kèm thông tin hết hạn Stealth."""
        self._ensure_stealth_columns()
        # Tự động chuyển đổi các model stealth đã hết hạn trước khi trả về
        self.check_and_fallback_expired_stealth_models()

        sql = """
            SELECT 
                agent_code, display_name, role_title, avatar_icon,
                COALESCE(model_name, 'gemini-3.8-flash') as model_name,
                COALESCE(provider, 'antigravity_sdk') as provider,
                COALESCE(temperature, 0.20) as temperature,
                COALESCE(max_tokens, 4096) as max_tokens,
                system_prompt_custom, is_active, updated_at,
                COALESCE(is_stealth_promo, false) as is_stealth_promo,
                stealth_expires_at,
                COALESCE(fallback_model_name, 'gemini-3.8-flash') as fallback_model_name,
                COALESCE(fallback_provider, 'antigravity_sdk') as fallback_provider
            FROM erp_ai_agent_budgets
            ORDER BY agent_code ASC;
        """
        try:
            with self.get_connection() as conn, conn.cursor() as cur:
                cur.execute(sql)
                rows = cur.fetchall()
        except Exception as e:
            logger.warning("Truy vấn erp_ai_agent_budgets cơ bản: %s", e)
            rows = []

        available_presets = [
            {
                "id": "gemini-3.8-flash",
                "provider": "antigravity_sdk",
                "name": "Google Gemini 3.8 Flash (Antigravity SDK - Siêu Tốc Độ 145 TPS & Agentic)",
                "tag": "Antigravity",
            },
            {
                "id": "gemini-3.1-pro",
                "provider": "antigravity_sdk",
                "name": "Google Gemini 3.1 Pro (Antigravity SDK - High Reasoning Pháp Chế & BoQ)",
                "tag": "Antigravity",
            },
            {
                "id": "gemini-3.7-flash",
                "provider": "antigravity_sdk",
                "name": "Google Gemini 3.7 Flash (Antigravity SDK - Suy luận nâng cao)",
                "tag": "Antigravity",
            },
            {
                "id": "gemini-3.6-flash",
                "provider": "gemini",
                "name": "Google Gemini 3.6 Flash (Trực tiếp - Siêu chính xác OCR & Đa phương thức)",
                "tag": "Gemini",
            },
            {
                "id": "gemini-2.5-pro",
                "provider": "gemini",
                "name": "Google Gemini 2.5 Pro (Trực tiếp - Phân tích BoQ & Hợp đồng sâu)",
                "tag": "Gemini",
            },
            {
                "id": "stealth/ox-alpha",
                "provider": "openrouter",
                "name": "Stealth OX-Alpha (Cực mạnh - Miễn phí có thời hạn)",
                "tag": "Stealth",
            },
            {
                "id": "google/gemini-2.0-flash-exp:free",
                "provider": "openrouter",
                "name": "Gemini 2.0 Flash (OpenRouter Free Tier)",
                "tag": "Fast",
            },
            {
                "id": "deepseek/deepseek-r1:free",
                "provider": "openrouter",
                "name": "DeepSeek R1 (Free Tier - Suy luận Luật 2025)",
                "tag": "Reasoning",
            },
            {
                "id": "qwen/qwen-2.5-coder-32b-instruct:free",
                "provider": "openrouter",
                "name": "Qwen 2.5 Coder 32B (Free Tier - Dự toán BOQ)",
                "tag": "Math",
            },
            {
                "id": "meta-llama/llama-3.3-70b-instruct:free",
                "provider": "openrouter",
                "name": "Llama 3.3 70B Instruct (Free Tier)",
                "tag": "Free",
            },
            {
                "id": "mistralai/mistral-7b-instruct:free",
                "provider": "openrouter",
                "name": "Mistral 7B Instruct (Free Tier)",
                "tag": "Light",
            },
            {
                "id": "nvidia/nemotron-3-nano-30b-a3b:free",
                "provider": "openrouter",
                "name": "Nvidia Nemotron 3 Nano 30B (Free Tier)",
                "tag": "Free",
            },
            {
                "id": "qwen2.5-7b-instruct",
                "provider": "lmstudio",
                "name": "Qwen 2.5 7B (LM Studio Local - Offline 95ms)",
                "tag": "Local",
            },
            {
                "id": "qwen2.5-coder-7b",
                "provider": "lmstudio",
                "name": "Qwen 2.5 Coder 7B (LM Studio Local)",
                "tag": "Local",
            },
        ]

        from datetime import datetime, timezone

        now_dt = datetime.now(timezone.utc)
        result = []
        for r in rows:
            expires_at = r.get("stealth_expires_at")
            days_left = None
            is_expired = False
            is_stealth = bool(r.get("is_stealth_promo"))
            if is_stealth and expires_at:
                diff = expires_at - now_dt
                days_left = max(0, diff.days)
                is_expired = diff.total_seconds() <= 0

            result.append(
                {
                    "agent_code": r["agent_code"],
                    "display_name": r["display_name"],
                    "role_title": r["role_title"],
                    "avatar_icon": r["avatar_icon"],
                    "model_name": str(r["model_name"]),
                    "provider": str(r["provider"]),
                    "temperature": float(r["temperature"]),
                    "max_tokens": int(r["max_tokens"]),
                    "system_prompt_custom": r["system_prompt_custom"],
                    "is_active": bool(r["is_active"]),
                    "updated_at": r["updated_at"].isoformat()
                    if r.get("updated_at")
                    else None,
                    "is_stealth_promo": is_stealth,
                    "stealth_expires_at": expires_at.isoformat()
                    if expires_at
                    else None,
                    "fallback_model_name": str(
                        r.get("fallback_model_name", "google/gemini-3.8-flash")
                    ),
                    "fallback_provider": str(r.get("fallback_provider", "openrouter")),
                    "days_until_expiration": days_left,
                    "is_expired": is_expired,
                    "available_presets": available_presets,
                }
            )
        return result

    def get_ai_agent_model(self, agent_code: str) -> dict[str, Any] | None:
        """Lấy cấu hình Model & Provider của một AI Agent cụ thể kèm kiểm tra hạn Stealth."""
        self._ensure_stealth_columns()
        sql = """
            SELECT 
                agent_code, display_name, role_title, avatar_icon,
                COALESCE(model_name, 'stealth/ox-alpha') as model_name,
                COALESCE(provider, 'openrouter') as provider,
                COALESCE(temperature, 0.20) as temperature,
                COALESCE(max_tokens, 2048) as max_tokens,
                system_prompt_custom, is_active, updated_at,
                COALESCE(is_stealth_promo, false) as is_stealth_promo,
                stealth_expires_at,
                COALESCE(fallback_model_name, 'google/gemini-3.8-flash') as fallback_model_name,
                COALESCE(fallback_provider, 'openrouter') as fallback_provider
            FROM erp_ai_agent_budgets
            WHERE agent_code = %s
            LIMIT 1;
        """

        with self.get_connection() as conn:
            with conn.cursor() as cur:
                cur.execute(sql, (agent_code,))
                r = cur.fetchone()
                if not r:
                    return None

                expires_at = r.get("stealth_expires_at")
                from datetime import datetime, timezone

                now_dt = datetime.now(timezone.utc)
                is_expired = False
                is_stealth = bool(r.get("is_stealth_promo"))
                if is_stealth and expires_at and (expires_at <= now_dt):
                    is_expired = True

                return {
                    "agent_code": r["agent_code"],
                    "display_name": r["display_name"],
                    "role_title": r["role_title"],
                    "avatar_icon": r["avatar_icon"],
                    "model_name": str(r["model_name"]),
                    "provider": str(r["provider"]),
                    "temperature": float(r["temperature"]),
                    "max_tokens": int(r["max_tokens"]),
                    "system_prompt_custom": r["system_prompt_custom"],
                    "is_active": bool(r["is_active"]),
                    "updated_at": r["updated_at"].isoformat()
                    if r.get("updated_at")
                    else None,
                    "is_stealth_promo": is_stealth,
                    "stealth_expires_at": expires_at.isoformat()
                    if expires_at
                    else None,
                    "fallback_model_name": str(
                        r.get("fallback_model_name", "google/gemini-3.8-flash")
                    ),
                    "fallback_provider": str(r.get("fallback_provider", "openrouter")),
                    "is_expired": is_expired,
                }