import logging
import re
from datetime import datetime

import httpx
from fastapi import APIRouter, HTTPException, Query
from pydantic import BaseModel, Field

from common.starrocks_connection import get_db_connection

logger = logging.getLogger(__name__)
router = APIRouter(tags=["n8n-agent-tools"])

TABLE_NAME = "shared_source.magento_product_dimension_with_text_embedding"
STORE_TABLE = "shared_source.chatbot_rsa_store_schedule_with_text_embedding"
KNOWLEDGE_TABLE = "shared_source.chatbot_rsa_knowledge"
PROMOTION_TABLE = "shared_source.chatbot_rsa_salerule_with_text_embedding"
CANIFA_STOCK_API = "https://canifa.com/v1/middleware/stock_get_stock_list_parent"
SAFE_TEXT_PATTERN = re.compile(r"[^a-zA-Z0-9À-ỹ\s-]")


def _sanitize_text(value: str | None) -> str:
    if not value:
        return ""
    return SAFE_TEXT_PATTERN.sub("", value).strip()


def _name_contains(product_name: str | None, query: str) -> bool:
    return query.lower() in (product_name or "").lower()


def _build_verify_result(candidates: list[dict], sku: str, product_name: str) -> tuple[bool, str, list[dict]]:
    sku_matches = [item for item in candidates if sku and (item.get("magento_ref_code") == sku)]
    name_matches = [item for item in candidates if product_name and _name_contains(item.get("product_name"), product_name)]

    if sku and product_name:
        matched_products = [item for item in sku_matches if _name_contains(item.get("product_name"), product_name)]
        if matched_products:
            return True, "sku_name_match", matched_products
        if sku_matches:
            return False, "sku_found_name_mismatch", sku_matches
        if name_matches:
            return False, "name_found_sku_mismatch", name_matches
        return False, "no_match", []

    if sku:
        is_valid = bool(sku_matches)
        return is_valid, ("sku_match" if is_valid else "sku_not_found"), sku_matches

    is_valid = bool(name_matches)
    return is_valid, ("name_match" if is_valid else "name_not_found"), name_matches


class ProductVerifyRequest(BaseModel):
    sku: str | None = Field(default=None, description="Magento reference code, ví dụ: 6ST24S001")
    product_name: str | None = Field(default=None, description="Tên sản phẩm cần đối chiếu")
    limit: int = Field(default=5, ge=1, le=20, description="Số bản ghi trả về để agent đối chiếu")


@router.get("/api/agent/n8n/products", summary="N8N Specific: Get Sample Products or Search")
async def n8n_get_sample_products(
    limit: int = Query(default=10, ge=1, le=50),
    q: str | None = Query(default=None, max_length=120),
):
    """
    API DÀNH RIÊNG CHO N8N để lấy danh sách sản phẩm hoặc tìm kiếm (phục vụ AI Agent verify).
    - Có thể truyền ?q=SKU hoặc từ khóa để tìm sản phẩm cụ thể.
    - Code hoàn toàn tách biệt khỏi hệ thống cũ (không đụng vào logic SearchItem/Embedding)
    """
    try:
        db = get_db_connection()
        mode = "sample"
        normalized_q = None

        if q is not None:
            normalized_q = _sanitize_text(q)
            if not normalized_q:
                raise HTTPException(status_code=400, detail="q không hợp lệ sau khi sanitize.")
            mode = "search"

        # Lấy các sản phẩm có hiển thị trên web, có giá
        if normalized_q:
            query = """
                SELECT
                    magento_ref_code,
                    product_name,
                    description_text,
                    original_price,
                    sale_price,
                    master_color,
                    gender_by_product,
                    product_web_url,
                    product_image_url_thumbnail
                FROM shared_source.magento_product_dimension_with_text_embedding
                WHERE sale_price > 0
                  AND quantity_sold > 0
                  AND (magento_ref_code = %s OR LOWER(product_name) LIKE LOWER(%s))
                LIMIT %s
            """
            params: tuple[object, ...] = (normalized_q, f"%{normalized_q}%", limit)
        else:
            query = """
                SELECT
                    magento_ref_code,
                    product_name,
                    description_text,
                    original_price,
                    sale_price,
                    master_color,
                    gender_by_product,
                    product_web_url,
                    product_image_url_thumbnail
                FROM shared_source.magento_product_dimension_with_text_embedding
                WHERE sale_price > 0
                  AND quantity_sold > 0
                ORDER BY rand()
                LIMIT %s
            """
            params = (limit,)

        products = await db.execute_query_async(query, params=params)

        return {
            "status": "success",
            "mode": mode,
            "query": q,
            "normalized_query": normalized_q,
            "total": len(products),
            "products": products,
        }
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"❌ Error in N8N Dedicated Product Fetch API: {e!s}", exc_info=True)
        raise HTTPException(status_code=500, detail=f"N8N API Error: {e!s}") from e


@router.post("/api/agent/n8n/products/verify", summary="N8N Specific: Verify product by SKU/name")
async def n8n_verify_product(req: ProductVerifyRequest):
    """
    Endpoint verify cho AI Agent (n8n):
    - Nhận SKU và/hoặc tên sản phẩm.
    - Trả về cờ is_valid + reason để agent quyết định.
    """
    try:
        db = get_db_connection()

        sku = _sanitize_text(req.sku)
        product_name = _sanitize_text(req.product_name)

        if not sku and not product_name:
            raise HTTPException(status_code=400, detail="Cần truyền ít nhất một trong hai: sku hoặc product_name.")

        if req.sku is not None and not sku:
            raise HTTPException(status_code=400, detail="sku không hợp lệ sau khi sanitize.")

        if req.product_name is not None and not product_name:
            raise HTTPException(status_code=400, detail="product_name không hợp lệ sau khi sanitize.")

        if sku and product_name:
            query = """
                SELECT
                    magento_ref_code,
                    product_name,
                    description_text,
                    original_price,
                    sale_price,
                    master_color,
                    gender_by_product,
                    product_web_url,
                    product_image_url_thumbnail
                FROM shared_source.magento_product_dimension_with_text_embedding
                WHERE sale_price > 0
                  AND quantity_sold > 0
                  AND (
                      magento_ref_code = %s
                      OR LOWER(product_name) LIKE LOWER(%s)
                  )
                ORDER BY quantity_sold DESC
                LIMIT %s
            """
            params: tuple[object, ...] = (sku, f"%{product_name}%", req.limit)
        elif sku:
            query = """
                SELECT
                    magento_ref_code,
                    product_name,
                    description_text,
                    original_price,
                    sale_price,
                    master_color,
                    gender_by_product,
                    product_web_url,
                    product_image_url_thumbnail
                FROM shared_source.magento_product_dimension_with_text_embedding
                WHERE sale_price > 0
                  AND quantity_sold > 0
                  AND magento_ref_code = %s
                ORDER BY quantity_sold DESC
                LIMIT %s
            """
            params = (sku, req.limit)
        else:
            query = """
                SELECT
                    magento_ref_code,
                    product_name,
                    description_text,
                    original_price,
                    sale_price,
                    master_color,
                    gender_by_product,
                    product_web_url,
                    product_image_url_thumbnail
                FROM shared_source.magento_product_dimension_with_text_embedding
                WHERE sale_price > 0
                  AND quantity_sold > 0
                  AND LOWER(product_name) LIKE LOWER(%s)
                ORDER BY quantity_sold DESC
                LIMIT %s
            """
            params = (f"%{product_name}%", req.limit)

        candidates = await db.execute_query_async(query, params=params)
        is_valid, reason, matched_products = _build_verify_result(candidates, sku, product_name)

        return {
            "status": "success",
            "is_valid": is_valid,
            "reason": reason,
            "input": {
                "sku": req.sku,
                "product_name": req.product_name,
            },
            "normalized_input": {
                "sku": sku or None,
                "product_name": product_name or None,
            },
            "candidate_count": len(candidates),
            "candidates": candidates,
            "matched_count": len(matched_products),
            "matched_products": matched_products,
        }
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"❌ Error in N8N Product Verify API: {e!s}", exc_info=True)
        raise HTTPException(status_code=500, detail=f"N8N Verify API Error: {e!s}") from e


# =====================================================================
# TOOL 3: CHECK STOCK  (Mirror of check_is_stock tool)
# =====================================================================
@router.get("/api/agent/n8n/stock", summary="N8N: Check product stock via Canifa API")
async def n8n_check_stock(
    sku: str = Query(..., max_length=120, description="Mã sản phẩm (base code hoặc product_color_code). Nhiều mã cách nhau bằng dấu phẩy."),
):
    """
    Proxy tới API tồn kho của canifa.com.
    Nhận SKU (VD: 6TS25S018 hoặc 5TS25S023-SY322), trả về thông tin còn hàng theo size.
    """
    try:
        safe_sku = _sanitize_text(sku)
        if not safe_sku:
            raise HTTPException(status_code=400, detail="SKU không hợp lệ.")

        sku_list = [s.strip() for s in safe_sku.split(",") if s.strip()]
        if not sku_list:
            raise HTTPException(status_code=400, detail="Không có mã sản phẩm hợp lệ.")

        sku_string = ",".join(sku_list)
        url = f"{CANIFA_STOCK_API}?skus={sku_string}"

        async with httpx.AsyncClient(timeout=8.0) as client:
            resp = await client.get(url)
            resp.raise_for_status()
            stock_data = resp.json()

        return {
            "status": "success",
            "input_skus": sku_list,
            "stock_data": stock_data,
        }
    except HTTPException:
        raise
    except httpx.TimeoutException:
        raise HTTPException(status_code=504, detail="Canifa Stock API timeout.")
    except Exception as e:
        logger.error(f"❌ Error in N8N Stock API: {e!s}", exc_info=True)
        raise HTTPException(status_code=500, detail=f"N8N Stock API Error: {e!s}") from e


# =====================================================================
# TOOL 4: PROMOTIONS  (Mirror of canifa_get_promotions tool)
# =====================================================================
@router.get("/api/agent/n8n/promotions", summary="N8N: Get active promotions")
async def n8n_get_promotions(
    date: str | None = Query(default=None, max_length=10, description="Ngày kiểm tra (YYYY-MM-DD). Mặc định: hôm nay."),
):
    """
    Tra cứu các chương trình khuyến mãi đang diễn ra theo ngày.
    """
    try:
        target_date = date or datetime.now().strftime("%Y-%m-%d")
        db = get_db_connection()

        query = f"""
            SELECT
                name,
                description,
                description_full,
                from_date,
                to_date,
                applied_channel
            FROM {PROMOTION_TABLE}
            WHERE %s >= DATE(from_date)
              AND %s <= DATE(to_date)
            ORDER BY
                CASE applied_channel
                    WHEN 'only_online' THEN 0
                    WHEN 'both' THEN 1
                    WHEN 'unknown' THEN 2
                    WHEN 'only_offline' THEN 3
                    ELSE 4
                END,
                to_date ASC
            LIMIT 20
        """
        results = await db.execute_query_async(query, params=(target_date, target_date))

        return {
            "status": "success",
            "check_date": target_date,
            "total": len(results),
            "promotions": results,
        }
    except Exception as e:
        logger.error(f"❌ Error in N8N Promotions API: {e!s}", exc_info=True)
        raise HTTPException(status_code=500, detail=f"N8N Promotions API Error: {e!s}") from e


# =====================================================================
# TOOL 5: STORE SEARCH  (Mirror of canifa_store_search tool)
# =====================================================================
@router.get("/api/agent/n8n/stores", summary="N8N: Search Canifa stores by location")
async def n8n_search_stores(
    location: str = Query(..., max_length=120, description="Tên quận/huyện/tỉnh/thành phố."),
):
    """
    Tìm kiếm cửa hàng CANIFA theo địa điểm/khu vực.
    """
    try:
        clean = location.lower().strip()
        for prefix in ["quận ", "huyện ", "tỉnh ", "thành phố ", "tp. ", "tp "]:
            clean = clean.replace(prefix, "")
        clean = clean.strip()

        if not clean:
            raise HTTPException(status_code=400, detail="Location không hợp lệ.")

        # Tách thành tokens, deduplicate (giữ thứ tự)
        tokens = list(dict.fromkeys(
            t for t in clean.replace(',', ' ').split() if t.strip()
        ))
        if not tokens:
            raise HTTPException(status_code=400, detail="Location không hợp lệ.")

        db = get_db_connection()

        # AND logic + bigram OR fallback
        text_col = "LOWER(concat_ws(' ', store_name, address, city, state))"

        def _q(where: str) -> str:
            return f"""
                SELECT store_name, address, city, state, phone_number,
                       schedule_name, time_open_today, time_close_today
                FROM {STORE_TABLE}
                WHERE {where}
                ORDER BY state, city, store_name
                LIMIT 20
            """

        # 1. AND strict
        and_conds = [f"{text_col} LIKE '%{t}%'" for t in tokens]
        results = await db.execute_query_async(_q(' AND '.join(and_conds)))

        # 2. Fallback: Reverse LIKE — DB làm từ điển địa danh
        if not results and len(tokens) >= 2:
            city_stripped = """LOWER(TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                city, 'Quận ', ''), 'Huyện ', ''), 'Thành phố ', ''), 'Thị xã ', ''), 'TP. ', '')))"""
            state_lower = "LOWER(TRIM(state))"
            fallback_where = f"""
                (LOCATE({city_stripped}, '{clean}') > 0 AND LENGTH({city_stripped}) > 1)
                OR
                (LOCATE({state_lower}, '{clean}') > 0 AND LENGTH({state_lower}) > 1)
            """
            results = await db.execute_query_async(_q(fallback_where))

        return {
            "status": "success",
            "location": location,
            "total": len(results),
            "stores": results,
        }
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"❌ Error in N8N Store Search API: {e!s}", exc_info=True)
        raise HTTPException(status_code=500, detail=f"N8N Store Search API Error: {e!s}") from e


# =====================================================================
# TOOL 6: KNOWLEDGE SEARCH  (Mirror of canifa_knowledge_search tool)
# =====================================================================
@router.get("/api/agent/n8n/knowledge", summary="N8N: Search brand knowledge (policies, size chart, etc.)")
async def n8n_search_knowledge(
    q: str = Query(..., max_length=200, description="Câu hỏi về chính sách, bảng size, KHTT, v.v."),
    limit: int = Query(default=5, ge=1, le=20),
):
    """
    Tra cứu thông tin thương hiệu Canifa (chính sách đổi trả, bảng size, KHTT, v.v.).
    Sử dụng semantic search (vector similarity) trên kho kiến thức.
    """
    try:
        from common.embedding_service import create_embedding_async

        query_vector = await create_embedding_async(q)
        if not query_vector:
            raise HTTPException(status_code=500, detail="Không tạo được embedding cho câu hỏi.")

        import json as _json
        v_str = _json.dumps(query_vector)

        db = get_db_connection()
        query = f"""
            SELECT content, metadata
            FROM {KNOWLEDGE_TABLE}
            ORDER BY approx_cosine_similarity(embedding, {v_str}) DESC
            LIMIT %s
        """
        results = await db.execute_query_async(query, params=(limit,))

        return {
            "status": "success",
            "query": q,
            "total": len(results),
            "knowledge": results,
        }
    except HTTPException:
        raise
    except Exception as e:
        logger.error(f"❌ Error in N8N Knowledge Search API: {e!s}", exc_info=True)
        raise HTTPException(status_code=500, detail=f"N8N Knowledge API Error: {e!s}") from e

