"""
CANIFA Data Retrieval Tool - Tối giản cho Agentic Workflow.
Hỗ trợ Hybrid Search: Semantic (Vector) + Metadata Filter.

Tracing:
- LangChain CallbackHandler auto-traces tool call (name, input, output)
- Manual span 'db-query-search' in _execute_single_search for SQL timing & product count
  (nested under parent LangChain trace via start_as_current_observation)
"""

import asyncio
import json
import logging
import time
import unicodedata

from langchain_core.tools import tool
from langfuse import get_client as get_langfuse
from pydantic import BaseModel, Field

from agent.helper import format_product_results
from agent.prompt_utils import read_tool_prompt
from agent.tools.product_search_helpers import build_starrocks_query
from common.starrocks_connection import get_db_connection

# Setup Logger
logger = logging.getLogger(__name__)


class SearchItem(BaseModel):
    model_config = {"extra": "forbid"}  # STRICT MODE

    # ====== SEMANTIC SEARCH ======
    description: str = Field(
        description=(
            "Mô tả sản phẩm cho semantic search. "
            "FORMAT: 'product_name: [tên]. description_text: [mô tả]. "
            "material_group: [chất liệu]. style: [phong cách]. fitting: [dáng]. "
            "form_sleeve: [tay]. form_neckline: [cổ]. product_line_vn: [dòng SP]'. "
            "🚨 product_name = CHÍNH XÁC lời user nói, TUYỆT ĐỐI KHÔNG tự đổi sang tên khác. "
            "VD: user nói 'áo ngọ nguậy' → product_name: Áo ngọ nguậy (KHÔNG đổi thành 'Áo phông'!). "
            "VD: user nói 'áo cá sấu' → product_name: Áo cá sấu (KHÔNG đổi!). "
            "Chỉ chuẩn hóa từ đồng nghĩa RÕ RÀNG: 'áo thun'→'Áo phông', 'quần bò'→'Quần jean'. "
            "⚠️ KHÔNG đưa master_color vào description — dùng field master_color riêng! "
            "product_line_vn trong description = broad category (Áo phông, Áo Polo, Quần jean...). "
            "VD: 'áo ngọ nguậy' → 'product_name: Áo ngọ nguậy. product_line_vn: Áo phông'. "
            "VD: 'áo cá sấu polo' → 'product_name: Áo cá sấu polo. product_line_vn: Áo Polo'."
        )
    )
    product_name: str | None = Field(
        description="Tên sản phẩm. Chỉ điền khi khách cung cấp tên cụ thể.",
    )

    # ====== SKU LOOKUP ======
    magento_ref_code: str | None = Field(
        description="Mã sản phẩm chính xác (SKU). Chỉ điền khi khách cung cấp mã cụ thể.",
    )

    # ====== SQL HARD FILTERS (lọc trực tiếp trong DB query) ======
    gender_by_product: str | None = Field(
        description="[SQL FILTER] Giới tính. GIÁ TRỊ HỢP LỆ: women, men, boy, girl, unisex, newborn",
    )
    age_by_product: str | None = Field(
        description="[SQL FILTER] Độ tuổi. GIÁ TRỊ HỢP LỆ: adult, kid, others",
    )
    master_color: str | None = Field(
        description=(
            "[SQL FILTER] Màu sắc sản phẩm. Gửi CHÍNH XÁC từ khách nói (VD: 'trắng', 'đen', 'xanh', 'đỏ'). "
            "Tool sẽ tự match trong DB bằng LIKE. KHÔNG đưa màu vào description!"
        ),
    )
    price_min: int | None = Field(
        description="[SQL FILTER] Giá thấp nhất (VND)",
    )
    price_max: int | None = Field(
        description="[SQL FILTER] Giá cao nhất (VND)",
    )
    discount_min: int | None = Field(
        description="[SQL FILTER] % giảm giá tối thiểu. VD: 50 → chỉ lấy SP giảm >= 50%. Nếu khách hỏi 'đồ sale' chung → đặt = 1.",
    )
    discount_max: int | None = Field(
        description="[SQL FILTER] % giảm giá tối đa. VD: 70 → chỉ lấy SP giảm <= 70%.",
    )
    discovery_mode: str | None = Field(
        description=(
            "[SQL FILTER] Chế độ khám phá. 'new' = hàng mới (is_new_product=1), "
            "'best_seller' = bán chạy nhất (ORDER BY quantity_sold). "
            "Chỉ dùng khi khách NÓI RÕ 'mới nhất'/'hàng mới'/'bán chạy'/'best seller'/'hot'. "
            "Nếu khách KHÔNG nói → để null."
        ),
    )


class MultiSearchParams(BaseModel):
    model_config = {"extra": "forbid"}  # STRICT MODE
    searches: list[SearchItem] = Field(description="Danh sách các truy vấn tìm kiếm")


def _normalize_text(value: str | None) -> str:
    """Normalize free text for robust case/accent-insensitive matching."""
    if not value:
        return ""
    normalized = unicodedata.normalize("NFD", str(value))
    no_diacritics = "".join(ch for ch in normalized if unicodedata.category(ch) != "Mn")
    return " ".join(no_diacritics.lower().strip().split())


def _attach_variant_skus(formatted_products: list[dict], raw_products: list[dict]) -> None:
    """Attach full variant SKUs to grouped products for downstream stock lookup."""
    by_base_sku: dict[str, list[dict]] = {}

    for row in raw_products:
        base_sku = str(row.get("magento_ref_code"))
        variant_sku = str(row.get("product_color_code") or "").strip()
        if not base_sku or not variant_sku:
            continue

        by_base_sku.setdefault(base_sku, []).append(
            {
                "sku_color": variant_sku,
                "color": row.get("master_color") or "",
                "url": row.get("product_web_url") or "",
                "thumbnail_image_url": row.get("product_image_url_thumbnail") or "",
                "thumbnail": row.get("product_image_url_thumbnail") or "",
                "price": int(row.get("original_price") or 0),
                "sale_price": int(row.get("sale_price") or row.get("original_price") or 0),
            }
        )

    for product in formatted_products:
        base_sku = str(product.get("sku") or "").strip()
        if not base_sku:
            continue

        variants = by_base_sku.get(base_sku, [])
        if not variants:
            continue

        deduped: list[dict] = []
        seen_variant_skus: set[str] = set()
        for variant in variants:
            sku_color = str(variant.get("sku_color") or "").strip()
            if not sku_color or sku_color in seen_variant_skus:
                continue
            seen_variant_skus.add(sku_color)
            deduped.append(variant)

        if deduped:
            product["variants"] = deduped


def _resolve_stock_skus(searches: list[SearchItem], products: list[dict]) -> list[str]:
    """Resolve full stock SKUs from returned flat products, expanding by size when possible."""
    resolved: list[str] = []
    seen: set[str] = set()

    for item in searches:
        base_code = str(item.magento_ref_code or "").strip().upper()
        if not base_code:
            continue

        target_color = _normalize_text(item.master_color)
        candidates = []
        for product in products:
            sku = str(product.get("sku") or "").strip().upper()
            sku_color = str(product.get("sku_color") or "").strip().upper()
            if not sku and not sku_color:
                continue
            if base_code and (sku == base_code or sku_color == base_code or sku.startswith(f"{base_code}-")):
                candidates.append(product)

        for product in candidates:
            sku_color = str(product.get("sku_color") or "").strip()
            product_color = _normalize_text(product.get("color"))
            sizes = [str(size).strip().upper() for size in (product.get("sizes") or []) if str(size).strip()]

            expanded_skus = [f"{sku_color}-{size}" for size in sizes] if sku_color and sizes else ([sku_color] if sku_color else [])

            if target_color:
                if target_color in product_color or product_color in target_color:
                    for full_sku in expanded_skus:
                        if full_sku and full_sku not in seen:
                            seen.add(full_sku)
                            resolved.append(full_sku)
                continue

            for full_sku in expanded_skus:
                if full_sku and full_sku not in seen:
                    seen.add(full_sku)
                    resolved.append(full_sku)

    return resolved


async def _execute_single_search(
    db, item: SearchItem, query_vector: list[float] | None = None
) -> tuple[list[dict], dict]:
    """
    Thực thi một search query đơn lẻ (Async).
    Creates a manual 'db-query-search' span under the current Langfuse context.

    Returns:
        Tuple of (products, filter_info)
    """
    try:
        short_query = (
            (item.description[:60] + "...") if item.description and len(item.description) > 60 else item.description
        )
        logger.debug(
            "_execute_single_search started, query=%r, code=%r",
            short_query,
            item.magento_ref_code,
        )

        # Timer: build query (sử dụng vector đã có hoặc build mới)
        query_build_start = time.time()
        sql, params = await build_starrocks_query(item, query_vector=query_vector)
        query_build_time = (time.time() - query_build_start) * 1000  # Convert to ms
        logger.debug("SQL built, length=%s, build_time_ms=%.2f", len(sql), query_build_time)

        if not sql:
            return [], {"fallback_used": False}

        # Timer: execute DB query
        db_start = time.time()
        products = await db.execute_query_async(sql, params=params)
        db_time = (time.time() - db_start) * 1000  # Convert to ms
        logger.info(
            "🔍 _execute_single_search done: query=%r, products=%s, build_ms=%.2f, db_ms=%.2f, total_ms=%.2f",
            short_query,
            len(products),
            query_build_time,
            db_time,
            query_build_time + db_time,
        )

        # Debug: Log first product to see fields
        if products:
            first_p = products[0]
            logger.debug("🔍 [DEBUG] First product keys: %s", list(first_p.keys()))
            logger.debug(
                "🔍 [DEBUG] First product price: %s, sale_price: %s",
                first_p.get("original_price"),
                first_p.get("sale_price"),
            )
            logger.debug(
                "🔍 [DEBUG] size_scale: %r, description_text: %r",
                first_p.get("size_scale"),
                (first_p.get("description_text") or "")[:100],
            )

        # ✅ Nest 'db-query-search' span dưới chat-request trace hiện tại trên Langfuse
        try:
            lf = get_langfuse()
            if lf:
                with lf.start_as_current_observation(
                    as_type="span",
                    name="db-query-search",
                ) as span:
                    span.update(
                        input={
                            "description": item.description[:200] if item.description else None,
                            "product_name": item.product_name,
                            "gender": item.gender_by_product,
                            "age": item.age_by_product,
                            "color": item.master_color,
                            "price_range": f"{item.price_min or ''}-{item.price_max or ''}",
                            "magento_ref_code": item.magento_ref_code,
                            "discovery_mode": item.discovery_mode,
                        },
                        output={
                            "product_count": len(products),
                            "build_ms": round(query_build_time, 2),
                            "db_ms": round(db_time, 2),
                            "total_ms": round(query_build_time + db_time, 2),
                            "sample_products": [
                                {
                                    "name": p.get("product_name", "?"),
                                    "code": p.get("magento_ref_code", "?"),
                                    "price": float(p["sale_price"]) if p.get("sale_price") else None,
                                }
                                for p in products[:3]
                            ]
                            if products
                            else [],
                        },
                        metadata={"sql_length": len(sql)},
                    )
        except Exception as trace_err:
            logger.warning("⚠️ Langfuse db-query-search span failed: %s", trace_err, exc_info=True)

        formatted_products = format_product_results(products)
        return formatted_products, {"fallback_used": False}
    except Exception as e:
        logger.exception("Single search error for item %r: %s", item, e)
        return [], {"fallback_used": False, "error": str(e)}


@tool(args_schema=MultiSearchParams)
async def data_retrieval_tool(searches: list[SearchItem]) -> str:
    """
    Công cụ tìm kiếm sản phẩm CANIFA.
    Hỗ trợ tìm kiếm Semantic và lọc theo Metadata.
    """
    tool_start = time.time()
    logger.info("🔧 data_retrieval_tool called with %d items", len(searches))

    # Log search params cho debugging
    for i, s in enumerate(searches):
        logger.info(
            "🔧 Search[%d]: desc=%r, name=%r, gender=%s, age=%s, color=%s, price=%s-%s, code=%s, mode=%s",
            i,
            (s.description[:80] + "...") if s.description and len(s.description) > 80 else s.description,
            s.product_name,
            s.gender_by_product,
            s.age_by_product,
            s.master_color,
            s.price_min,
            s.price_max,
            s.magento_ref_code,
            s.discovery_mode,
        )

    # Get DB Connection
    db = get_db_connection()
    if not db:
        return json.dumps({"status": "error", "message": "Database connection failed"})

    combined_results = []
    all_filter_infos = []

    tasks = [_execute_single_search(db, item) for item in searches]

    results_list = await asyncio.gather(*tasks)

    for products, filter_info in results_list:
        combined_results.extend(products)
        if filter_info:
            all_filter_infos.append(filter_info)

    # Aggregate filter info from first result for simplicity in response
    final_info = all_filter_infos[0] if all_filter_infos else {}

    # Include search input so LLM knows its own reasoning
    search_inputs = [
        {
            "description": item.description,
            "gender_by_product": item.gender_by_product,
            "age_by_product": item.age_by_product,
            "discovery_mode": item.discovery_mode,
        }
        for item in searches
    ]

    allowed_skus = sorted({str(p.get("sku") or "").strip() for p in combined_results if p.get("sku")})

    output = {
        "status": "success",
        "message_for_ai": (
            f"Quy tắc bắt buộc: `product_ids` CHỈ được lấy từ trường `sku`. "
            f"Các giá trị `sku` hợp lệ trong lần search này là: {allowed_skus}. "
            f"Tuyệt đối cấm dùng `sku_color` cho `product_ids`. "
            f"`sku_color` chỉ là mã biến thể màu để tham chiếu nội bộ."
        ),
        "search_input": search_inputs,
        "results": combined_results,
        "stock_skus": _resolve_stock_skus(searches, combined_results),
        "filter_info": final_info,
    }

    total_ms = (time.time() - tool_start) * 1000

    logger.info(
        "🎁 Final result: %d products, total_ms=%.2f. Fallback used: %s",
        len(combined_results),
        total_ms,
        final_info.get("fallback_used", False),
    )

    return json.dumps(output, ensure_ascii=False, default=str)


# Load dynamic docstring
dynamic_prompt = read_tool_prompt("data_retrieval_tool")
if dynamic_prompt:
    data_retrieval_tool.__doc__ = dynamic_prompt
    data_retrieval_tool.description = dynamic_prompt
