"""
Lead Search Tool - LangChain @tool cho AI goi.
AI sinh keywords + tags -> Tool search -> cascade -> Tra ket qua.
+ Tu dong goi Canifa Stock API -> chi tra SP CON HANG.
+ Doc outfit tu SQLite canifa_ai_dump.sqlite

Architecture:
  CO DINH (luon giu): product_type + color + gender + price + size
  BIEN DOI (fallback):
    Tang 1: CO DINH + keywords (NGRAMBF tren product_name hoac LIKE tren description)
    Tang 2: CO DINH + tags (BITMAP hoac LIKE full phrase)
    Tang 3: CHI CO DINH (product_type + color + gender + size + price)
    Tang 4: Drop gender
    Tang 5-6: Price relaxation (1.5x, 2x)
    Tang 7: Bo price hoan toan
"""

import json
import logging
import os as _os
import sqlite3
import time

import httpx
from pydantic import BaseModel, Field

from common.starrocks_connection import get_db_connection

from .product_mapping import get_related_lines, resolve_product_line

logger = logging.getLogger(__name__)

try:
    from langfuse import get_client as _get_langfuse
    _LANGFUSE_AVAILABLE = True
except ImportError:
    _get_langfuse = None
    _LANGFUSE_AVAILABLE = False

# ═══════════════════════════════════════════════
# Stock API — Kiểm tra tồn kho Canifa
# ═══════════════════════════════════════════════
CANIFA_STOCK_API = "https://canifa.com/v1/middleware/stock_get_stock_list_parent"
_STOCK_TIMEOUT = 1.5  # seconds

async def _fetch_stock_batch(base_codes: list[str]) -> dict[str, list[dict]]:
    if not base_codes:
        return {}, 0.0, False

    sku_string = ",".join(base_codes)
    url = f"{CANIFA_STOCK_API}?skus={sku_string}"
    t0 = time.time()

    try:
        async with httpx.AsyncClient(timeout=_STOCK_TIMEOUT) as client:
            resp = await client.get(url)
            resp.raise_for_status()
            data = resp.json()

        elapsed = round((time.time() - t0) * 1000, 1)

        stock_map: dict[str, list[dict]] = {}
        results = data.get("result", [])
        if isinstance(results, list):
            for item in results:
                sku_full = item.get("sku", "")
                qty = item.get("qty", 0) or 0
                if not sku_full:
                    continue
                parts = sku_full.rsplit("-", 1)
                if len(parts) == 2:
                    color_code = parts[0]
                    size = parts[1]
                    status = "còn hàng" if qty > 0 else "hết hàng"
                    stock_map.setdefault(color_code, []).append({
                        "size": size, "qty": qty, "status": status
                    })

        logger.info("📦 Stock API: %d base_codes -> %d color_codes | %.0fms", len(base_codes), len(stock_map), elapsed)
        return stock_map, elapsed, False

    except httpx.TimeoutException:
        elapsed = round((time.time() - t0) * 1000, 1)
        logger.warning("⏰ Stock API timeout (%.1fs=%.0fms)", _STOCK_TIMEOUT, elapsed)
        return {}, elapsed, True
    except Exception as e:
        elapsed = round((time.time() - t0) * 1000, 1)
        logger.warning("⚠️ Stock API error (%.0fms): %s", elapsed, e)
        return {}, elapsed, False


async def _enrich_with_stock(products: list[dict]) -> tuple[list[dict], bool, float, bool]:
    if not products:
        return [], False, 0.0, False

    base_codes = list({p.get("internal_ref_code", "") for p in products if p.get("internal_ref_code")})
    if not base_codes:
        return products, False, 0.0, False

    stock_map, stock_api_ms, timed_out = await _fetch_stock_batch(base_codes)

    if not stock_map:
        return products, False, stock_api_ms, timed_out

    in_stock = []
    dropped = 0
    for p in products:
        color_code = p.get("product_color_code", "")
        if color_code in stock_map:
            sizes_detail = stock_map[color_code]
            total_qty = sum(s["qty"] for s in sizes_detail)
            if total_qty > 0:
                p["_stock_detail"] = sizes_detail
                p["_total_qty"] = total_qty
                in_stock.append(p)
            else:
                dropped += 1

    logger.info("📦 Stock filter: %d -> %d in-stock, %d dropped | api=%.0fms", len(products), len(in_stock), dropped, stock_api_ms)
    return in_stock if in_stock else products[:5], True, stock_api_ms, timed_out


# ═══════════════════════════════════════════════
# SQLite local DB path
# ═══════════════════════════════════════════════
from common.constants import SQLITE_DB_PATH

TABLE_NAME = "test_db.magento_product_dimension_with_text_embedding"

SELECT_COLUMNS = """
    internal_ref_code,
    magento_ref_code,
    product_color_code,
    product_name,
    master_color,
    product_color_name,
    product_image_url_thumbnail,
    product_web_url,
    sale_price,
    original_price,
    COALESCE(discount_amount, 0) AS discount_amount,
    ROUND(CASE WHEN original_price > 0
        THEN ((original_price - sale_price) / original_price * 100)
        ELSE 0 END, 0) AS discount_percent,
    age_by_product,
    gender_by_product,
    product_line_vn,
    COALESCE(quantity_sold, 0) AS quantity_sold,
    COALESCE(is_new_product, 0) AS is_new_product,
    size_scale,
    description_text,
    suggest_items,
    similar_items
"""

TAGS_TO_OCCASION: dict[str, str] = {
    "occ:di_lam":   "di_lam",
    "occ:di_choi":  "di_choi",
    "occ:di_tiec":  "di_tiec",
    "occ:di_hoc":   "di_choi",
    "occ:mac_nha":  "mac_nha",
    "occ:the_thao": "the_thao",
    "occ:di_bien":  "di_choi",
    "occ:du_lich":  "di_choi",
    "occ:da_ngoai": "di_choi",
    "occ:di_ngu":   "mac_nha",
    "occ:hang_ngay": "hang_ngay",
}

def _resolve_occasion(tags: list[str]) -> str:
    for tag in (tags or []):
        occ = TAGS_TO_OCCASION.get(tag.lower())
        if occ:
            return occ
    return "hang_ngay"


class LiteralSearch(BaseModel):
    """Lane 1: Tìm NGUYÊN VĂN — không filter, chỉ LIKE raw text."""
    model_config = {"extra": "forbid"}

    raw_text: str = Field(
        default="",
        description=(
            "Câu search NGUYÊN VĂN từ user. Copy y hệt phần liên quan đến sản phẩm. "
            "VD: user nói 'tìm áo 30/4' -> raw_text='áo 30/4'. "
            "VD: user nói 'có quần jean ống rộng ko' -> raw_text='quần jean ống rộng'. "
            "KHÔNG suy luận, KHÔNG thêm bớt. Giữ nguyên ý user."
        ),
    )


class InferredSearch(BaseModel):
    """Lane 2: AI SUY LUẬN — đầy đủ filter structured."""
    model_config = {"extra": "ignore"}

    product_line_vn: list[str] = Field(
        default=[],
        description=(
            "Dòng sản phẩm. CHÍNH XÁC lời user nói: 'áo phông', 'váy liền', 'đồ lót', 'quần jean'... "
            "Tool sẽ tự động chuẩn hoá từ đồng nghĩa (VD: 'áo thun' -> 'Áo phông'). "
            "Nếu khách nói chung chung ('đồ mùa đông', 'đồ tập') hoặc không nhắc, để []. "
        ),
    )
    gender_by_product: str | None = Field(
        default=None,
        description="Giới tính. GIÁ TRỊ HỢP LỆ: women, men, boy, girl, unisex, newborn",
    )
    age_by_product: str | None = Field(
        default=None,
        description="Độ tuổi. GIÁ TRỊ HỢP LỆ: adult, kid, others",
    )
    master_color: str | None = Field(
        default=None,
        description=(
            "Màu sắc AI SUY LUẬN từ ngữ cảnh. "
            "VD: '30/4' -> color='đỏ' (cờ đỏ sao vàng). 'áo trắng' -> color='trắng'."
        ),
    )
    tags: list[str] = Field(
        default=[],
        description=(
            "AI suy luận ý định khách -> chọn từ 4 TRỤC CỐ ĐỊNH (BẮT BUỘC có prefix!): "
            "Trục 1 (occ:): occ:di_lam, occ:di_choi, occ:di_tiec, occ:di_hoc, occ:mac_nha, occ:the_thao, occ:di_bien, occ:du_lich, occ:da_ngoai, occ:di_ngu. "
            "Trục 2a (wthr:): wthr:mua_he, wthr:mua_dong, wthr:giao_mua, wthr:troi_mua, wthr:troi_nang. "
            "Trục 2b (func:): func:thoang_mat, func:giu_am, func:tham_hut, func:nhanh_kho, func:chong_uv, func:can_gio. "
            "Trục 3 (style:): style:thanh_lich, style:nang_dong, style:basic, style:ca_tinh, style:de_thuong, style:tre_trung, style:toi_gian, style:smart_casual. "
            "Trục 4 (fit:): fit:oversize, fit:slim, fit:regular, fit:wide_leg, fit:cropped, fit:relaxed. "
            "KHÔNG tự nghĩ tag mới! PHẢI giữ prefix! Tối đa 3."
        )
    )
    keywords: list[str] = Field(
        default=[],
        description=(
            "Từ khóa BỔ TRỢ AI suy luận thêm (chất liệu, tính năng...). "
            "VD: '30/4' -> keywords=['cờ đỏ sao vàng']. 'đi biển' -> keywords=['thoáng mát']. "
            "Tối đa 2."
        )
    )
    price_min: int | None = Field(default=None, description="Giá thấp nhất (VND)")
    price_max: int | None = Field(default=None, description="Giá cao nhất (VND)")
    discount_min: int | None = Field(default=None, description="% giảm giá TỐI THIỂU.")
    discount_max: int | None = Field(default=None, description="% giảm giá TỐI ĐA.")
    discovery_mode: str | None = Field(default=None, description="'new' = hàng mới, 'best_seller' = bán chạy.")
    size: str | None = Field(
        default=None,
        description="Size: XS/S/M/L/XL/XXL/3XL/4XL hoặc trẻ em 80-164cm.",
    )


class LeadSearchInput(BaseModel):
    """
    Dual-Lane Search Input.
    - literal: Lane 1 — raw text NGUYÊN VĂN từ user (LIKE thuần, không filter)
    - inferred: Lane 2 — AI suy luận structured filters
    """
    model_config = {"extra": "ignore"}

    literal: LiteralSearch = Field(
        default_factory=LiteralSearch,
        description="Lane 1: Câu search nguyên văn từ user. Chỉ LIKE, KHÔNG filter.",
    )
    inferred: InferredSearch = Field(
        default_factory=InferredSearch,
        description="Lane 2: AI suy luận — đầy đủ product_line, color, tags, gender, price...",
    )
    magento_ref_code: str | None = Field(default=None, description="Ma SKU cu the.")
    reasoning: str | None = Field(default=None, description="SUY LUẬN TẠI SAO bạn chọn params này.")


# ======================================================
# SQL Builder
# ======================================================
def _dedup(products: list[dict], limit: int = 10) -> list[dict]:
    seen: set[str] = set()
    res: list[dict] = []
    for p in products:
        bc = p.get('internal_ref_code', '')
        if bc and bc not in seen:
            seen.add(bc)
            res.append(p)
    return res[:limit]

def _build_fixed_clauses(inf: InferredSearch, params: list) -> list[str]:
    """Build fixed WHERE clauses from InferredSearch (Lane 2 only)."""
    clauses = []

    if inf.product_line_vn:
        lines = []
        for line in inf.product_line_vn:
            if not line: continue
            resolved = resolve_product_line(line)
            for r in resolved:
                expanded = get_related_lines(r)
                lines.extend(expanded)

        if lines:
            placeholders = ", ".join(["%s"] * len(lines))
            params.extend(lines)
            clauses.append(f"product_line_vn IN ({placeholders})")

    if inf.gender_by_product:
        gender_lower = inf.gender_by_product.lower().strip()
        genders_to_search = []
        if gender_lower in ("women", "nu", "female", "nữ"):
            genders_to_search = ["female", "women", "nu", "nữ", "unisex"]
        elif gender_lower in ("men", "nam", "male"):
            genders_to_search = ["male", "men", "nam", "unisex"]
        elif gender_lower in ("boy", "bé trai", "be trai"):
            genders_to_search = ["boy", "bé trai", "be trai", "unisex"]
        elif gender_lower in ("girl", "bé gái", "be gai"):
            genders_to_search = ["girl", "bé gái", "be gai", "unisex"]
        else:
            genders_to_search = [gender_lower, "unisex"]

        placeholders = ", ".join(["%s"] * len(genders_to_search))
        params.extend(genders_to_search)
        clauses.append(f"gender_by_product IN ({placeholders})")

    if inf.age_by_product:
        age_lower = inf.age_by_product.lower().strip()
        if age_lower in ("baby", "newborn"):
            params.append("kid")
        else:
            params.append(age_lower)
        clauses.append("age_by_product = %s")

    if inf.master_color:
        c_raw = inf.master_color.strip()
        COLOR_EN_MAP = {
            "do": "red", "trang": "white", "den": "black", "xanh": "blue",
            "hong": "pink", "tim": "purple", "cam": "orange", "vang": "yellow",
            "nau": "brown", "xam": "gray", "be": "beige", "kem": "cream",
            "xanh la": "green", "bac": "silver", "vang dong": "gold",
            "đo": "red", "đỏ": "red", "trắng": "white", "đen": "black",
            "xanh lá": "green", "hồng": "pink", "tím": "purple", "nâu": "brown",
            "xám": "gray", "vàng": "yellow", "bạc": "silver"
        }
        c_key = c_raw.lower().strip()
        en_color = COLOR_EN_MAP.get(c_key)
        if not en_color:
            import unicodedata
            c_no_acc = "".join(
                ch for ch in unicodedata.normalize("NFD", c_key)
                if unicodedata.category(ch) != "Mn"
            ).strip()
            en_color = COLOR_EN_MAP.get(c_no_acc)
        c_no_accent = c_key

        color_parts = []
        color_parts.append("(master_color LIKE %s OR product_color_name LIKE %s)")
        params.append(f"%{c_raw}%")
        params.append(f"%{c_raw}%")
        if c_no_accent != c_raw.lower():
            color_parts.append("(LOWER(master_color) LIKE %s OR LOWER(product_color_name) LIKE %s)")
            params.append(f"%{c_no_accent}%")
            params.append(f"%{c_no_accent}%")
        if en_color:
            color_parts.append("(LOWER(master_color) LIKE %s OR LOWER(product_color_name) LIKE %s)")
            params.append(f"%{en_color}%")
            params.append(f"%{en_color}%")
        clauses.append("(" + " OR ".join(color_parts) + ")")

    if inf.price_min is not None:
        params.append(inf.price_min)
        clauses.append("sale_price >= %s")
    if inf.price_max is not None:
        params.append(inf.price_max)
        clauses.append("sale_price <= %s")

    if inf.discount_min is not None:
        params.append(inf.discount_min)
        clauses.append("(original_price > 0 AND ((original_price - sale_price) / original_price * 100) >= %s)")
    if inf.discount_max is not None:
        params.append(inf.discount_max)
        clauses.append("(original_price > 0 AND ((original_price - sale_price) / original_price * 100) <= %s)")

    if inf.size:
        s = inf.size.strip().upper()
        params.append(s)
        clauses.append("FIND_IN_SET(%s, REPLACE(size_scale, '|', ',')) > 0")

    if inf.discovery_mode:
        mode = inf.discovery_mode.lower().strip()
        if mode == "new":
            clauses.append("is_new_product = 1")
        elif mode == "best_seller":
            clauses.append("quantity_sold > 0")

    return clauses

TAG_TO_BITMAP_COL: dict[str, tuple[str, str]] = {
    "thanh lịch":  ("style", "Feminine"),
    "năng động":   ("style", "Dynamic"),
    "cơ bản (basic)": ("style", "Basic"),
    "cá tính":     ("style", "Street"),
    "dễ thương":   ("style", "Cute"),
    "trẻ trung":   ("style", "Trend"),
    "tối giản":    ("style", "Essential"),
    "smart casual":("style", "Smart Casual"),
    "oversize":  ("fitting", "Oversize"),
    "slim":      ("fitting", "Slim"),
    "regular":   ("fitting", "Regular"),
    "wide leg":  ("fitting", "Relax"),
    "cropped":   ("fitting", "Boxy"),
    "relaxed":   ("fitting", "Relax"),
    "mùa hè":   ("season_sale", "Summer"),
    "mùa đông": ("season_sale", "Winter"),
    "cả 2 mùa": ("season_sale", "Basic"),
    "đi biển":   ("season_sale", "Summer"),
    "du lịch":   ("season_sale", "Summer"),
    "tập thể thao":  ("style", "Athleisure"),
}

TAG_TO_SEARCH_TEXT: dict[str, str] = {
    "đi làm / công sở":   "đi làm",
    "đi chơi / dạo phố":  "đi chơi",
    "đi tiệc":  "đi tiệc",
    "đi học":   "đi học",
    "mặc nhà":  "mặc nhà",
    "dã ngoại": "dã ngoại",
    "đi ngủ":   "ngủ",
    "giao mùa":  "giao mùa",
    "thoáng mát": "thoáng mát",
    "giữ ấm":     "giữ ấm",
    "thấm hút":   "thấm hút",
    "nhanh khô":  "nhanh khô",
    "chống uv":   "chống UV",
    "cản gió":    "cản gió",
}

def _build_tag_clauses(tags: list[str], params: list) -> str:
    if not tags: return ""
    bitmap_by_col: dict[str, list[str]] = {}
    text_terms: list[str] = []

    for tag in tags:
        tag_lower = tag.strip().lower()
        if tag_lower in TAG_TO_BITMAP_COL:
            col, val = TAG_TO_BITMAP_COL[tag_lower]
            bitmap_by_col.setdefault(col, []).append(val)
        elif tag_lower in TAG_TO_SEARCH_TEXT:
            text_terms.append(TAG_TO_SEARCH_TEXT[tag_lower])
        else:
            text_terms.append(tag_lower.replace(":", " ").replace("_", " "))

    all_clauses = []
    for col, values in bitmap_by_col.items():
        if len(values) == 1:
            params.append(values[0])
            all_clauses.append(f"{col} = %s")
        else:
            placeholders = ", ".join(["%s"] * len(values))
            params.extend(values)
            all_clauses.append(f"{col} IN ({placeholders})")

    if text_terms:
        term_clauses = []
        for term in text_terms:
            term = term.strip()
            if not term: continue
            params.append(f"%{term}%")
            params.append(f"%{term}%")
            term_clauses.append(f"(LOWER(description_text) LIKE %s OR product_name LIKE %s)")
        if term_clauses:
            all_clauses.append(f"({' OR '.join(term_clauses)})")

    if all_clauses:
        return f"({' OR '.join(all_clauses)})"
    return ""

def _build_search_clause(search_terms: list[str], params: list) -> str:
    term_clauses = []
    for term in search_terms:
        t = term.strip()
        if not t: continue
        words = t.split()
        if not words: continue
        word_clauses = []
        for word in words:
            if len(word) >= 4:
                params.append(f"%{word}%")
                word_clauses.append("product_name LIKE %s")
            else:
                params.append(f"%{word}%")
                params.append(f"%{word}%")
                word_clauses.append("(product_name LIKE %s OR LOWER(description_text) LIKE %s)")
        if word_clauses:
            term_clauses.append(f"({' AND '.join(word_clauses)})")
    if term_clauses:
        return f"({' OR '.join(term_clauses)})"
    return ""

def _build_exclusion_clauses(keywords: list[str], params: list) -> list[str]:
    clauses = []
    kws_str = " ".join(keywords).lower()

    if any(k in kws_str for k in ["đông", "lạnh", "winter", "tuyết", "giữ ấm"]):
        clauses.append("product_line_vn NOT IN (%s, %s)")
        params.extend(["Quần soóc", "Áo ba lỗ"])

    if any(k in kws_str for k in ["đi làm", "công sở", "văn phòng", "office"]):
        forbidden = ["cartoon", "hoạt hình", "manga", "anime", "demon slayer", "naruto", "disney", "marvel"]
        for f in forbidden:
            clauses.append("LOWER(description_text) NOT LIKE %s")
            params.append(f"%{f}%")

    return clauses

def _build_full_query(fixed_clauses: list[str], search_clause: str | None, exclusion_clauses: list[str] = None) -> str:
    all_clauses = list(fixed_clauses)
    if search_clause:
        all_clauses.append(search_clause)
    if exclusion_clauses:
        all_clauses.extend(exclusion_clauses)

    where = " AND ".join(all_clauses) if all_clauses else "1=1"
    return f"""
    SELECT {SELECT_COLUMNS}
    FROM {TABLE_NAME}
    WHERE {where}
    ORDER BY quantity_sold DESC NULLS LAST, sale_price ASC
    LIMIT 150
    """

async def _price_relaxed_search(inf: InferredSearch, db, multiplier: float) -> list:
    if inf.price_max is None:
        return []
    saved_max = inf.price_max
    saved_min = inf.price_min
    inf.price_max = int(inf.price_max * multiplier)
    inf.price_min = None
    params: list = []
    fixed = _build_fixed_clauses(inf, params)
    sql = _build_full_query(fixed, None)
    products = await db.execute_query_async(sql, params=tuple(params))
    inf.price_max = saved_max
    inf.price_min = saved_min
    return products

async def _cascading_search(req: LeadSearchInput, db) -> tuple[list, int, str | None]:
    ex_params = []
    exclusions = _build_exclusion_clauses(req.keywords, ex_params)

    # Tang 1: Fixed + Keywords (NGRAMBF)
    if req.keywords:
        params = []
        fixed = _build_fixed_clauses(req, params)
        search = _build_search_clause(req.keywords, params)
        if search:
            sql = _build_full_query(fixed, search, exclusions)
            products = await db.execute_query_async(sql, params=tuple(params + ex_params))
            if products:
                return products, 1, None

    # Tang 2: Fixed + Tags (BITMAP)
    if req.tags:
        params = []
        fixed = _build_fixed_clauses(req, params)
        search = _build_tag_clauses(req.tags, params)
        if search:
            sql = _build_full_query(fixed, search, exclusions)
            products = await db.execute_query_async(sql, params=tuple(params + ex_params))
            if products:
                return products, 2, None

    # Tang 3: Chỉ cố định
    params = []
    fixed = _build_fixed_clauses(req, params)
    sql = _build_full_query(fixed, None, exclusions)
    products = await db.execute_query_async(sql, params=tuple(params + ex_params))
    if products:
        return products, 3, None

    # Tang 4: Drop gender
    if req.product_line_vn and req.gender_by_product:
        saved_gender = req.gender_by_product
        req.gender_by_product = None
        params = []
        fixed = _build_fixed_clauses(req, params)
        sql = _build_full_query(fixed, None, exclusions)
        products = await db.execute_query_async(sql, params=tuple(params + ex_params))
        req.gender_by_product = saved_gender
        if products:
            return _dedup(products), 4, None

    # Tang 5-7: Price relaxation
    if req.price_max is not None and req.product_line_vn:
        original_max = req.price_max
        product_label = "/".join(req.product_line_vn)

        for multiplier, tier, label in [(1.5, 5, "1.5x"), (2.0, 6, "2x")]:
            new_max = int(original_max * multiplier)
            products = await _price_relaxed_search(req, db, multiplier)
            if products:
                cheapest_p = min(products, key=lambda p: float(p.get("sale_price") or 999_999_999))
                cheapest = float(cheapest_p.get("sale_price") or 0)
                cheapest_name = cheapest_p.get("product_name", "")
                diff = int(cheapest - original_max)
                msg = (
                    f"FALLBACK_PRICE: Khong co {product_label} duoi {original_max:,.0f}d. "
                    f"Mau re nhat: \"{cheapest_name}\" gia {cheapest:,.0f}d "
                    f"(chi them {diff:,}d so voi budget). "
                    f"Tong co {len(products)} mau trong tam {new_max:,.0f}d. "
                    f"-> HAY goi y SP nay cho khach va noi kheo: them chut xiu la co mau rat dep!"
                )
                return products, tier, msg

        # Tang 7: bo price hoan toan
        saved_max = req.price_max
        saved_min = req.price_min
        req.price_max = None
        req.price_min = None
        params = []
        fixed = _build_fixed_clauses(req, params)
        sql = _build_full_query(fixed, None, exclusions)
        products = await db.execute_query_async(sql, params=tuple(params + ex_params))
        req.price_max = saved_max
        req.price_min = saved_min
        if products:
            cheapest_p = min(products, key=lambda p: float(p.get("sale_price") or 999_999_999))
            cheapest = float(cheapest_p.get("sale_price") or 0)
            cheapest_name = cheapest_p.get("product_name", "")
            msg = (
                f"FALLBACK_PRICE: Khong co {product_label} duoi {original_max:,.0f}d. "
                f"Mau re nhat hien co: \"{cheapest_name}\" gia {cheapest:,.0f}d. "
                f"Tong co {len(products)} mau. "
                f"-> HAY goi y SP nay va noi: budget them chut la co mau rat dang!"
            )
            return _dedup(products), 7, msg

    return [], 3, None

async def _enrich_with_outfit(
    products: list[dict],
    db,
    tags: list[str] | None = None,
) -> list[dict]:
    if not products:
        return products

    if not _os.path.exists(SQLITE_DB_PATH):
        return products

    top_products = products[:3]
    anchor_base_codes = [
        (p.get("internal_ref_code") or p.get("magento_ref_code", "").split("-")[0]).strip()
        for p in top_products
    ]
    anchor_base_codes = [c for c in anchor_base_codes if c]
    if not anchor_base_codes:
        return products

    try:
        conn = sqlite3.connect(SQLITE_DB_PATH)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()

        placeholders = ",".join(["?"] * len(anchor_base_codes))
        
        # Lấy outfit matches từ bảng mới
        outfit_rows = cursor.execute(
            f"""
            SELECT anchor_product_code, match_product_code, match_product_name, match_role, ai_reason
            FROM pg__dashboard_canifa__ai_outfit_product_matches
            WHERE anchor_product_code IN ({placeholders})
            """,
            anchor_base_codes,
        ).fetchall()
        
        # Vẫn lấy clean_description từ bảng cũ nếu cần
        desc_rows = cursor.execute(
            f"""
            SELECT base_ref_code, clean_description
            FROM pg__dashboard_canifa__ultra_descriptions
            WHERE base_ref_code IN ({placeholders})
            """,
            anchor_base_codes,
        ).fetchall()
        
        conn.close()
    except Exception as e:
        logger.error("❌ SQLite outfit read error: %s", e)
        return products

    outfit_map: dict[str, list] = {}
    for row in outfit_rows:
        anchor = row["anchor_product_code"]
        if anchor not in outfit_map:
            outfit_map[anchor] = []
        outfit_map[anchor].append({
            "match_product_code": row["match_product_code"],
            "match_product_name": row["match_product_name"],
            "role": row["match_role"],
            "reason": row["ai_reason"]
        })
        
    desc_map: dict[str, str] = {}
    for row in desc_rows:
        desc_map[row["base_ref_code"]] = row["clean_description"]

    for p in top_products:
        anchor_magento = p.get("magento_ref_code", "")
        base = (p.get("internal_ref_code") or anchor_magento.split("-")[0]).strip()
        
        # Gán description
        clean_desc = desc_map.get(base)
        if clean_desc:
            p["ai_description"] = clean_desc

        # Gán outfit matches
        matches = outfit_map.get(base)
        if matches:
            p["ai_matches"] = matches  # Dùng luôn key ai_matches để AI #2 không bị ngợp và tương thích prompt cũ
            p["outfit_recommendations"] = matches # Tên rõ nghĩa hơn

    return products

async def _format_products(products: list, db) -> list[dict]:
    formatted = []

    # ── Fetch Suggest/Similar from DB ──
    all_related_skus: set = set()
    raw_parsed_map: dict = {}

    def parse_item_list(raw_val) -> list[str]:
        if not raw_val: return []
        if isinstance(raw_val, list): return [str(x) for x in raw_val[:3]]
        if isinstance(raw_val, str):
            try:
                parsed = json.loads(raw_val)
                if isinstance(parsed, list): return [str(x) for x in parsed[:3]]
            except Exception: pass
        return []

    for p in products[:3]:
        sku = p.get("magento_ref_code", "")
        sug_skus = parse_item_list(p.get("suggest_items"))[:2]
        sim_skus = parse_item_list(p.get("similar_items"))[:3]
        raw_parsed_map[sku] = {"suggest": sug_skus, "similar": sim_skus}
        all_related_skus.update(sug_skus)
        all_related_skus.update(sim_skus)

    sku_lookup: dict = {}
    if all_related_skus:
        phs = ", ".join(["%s"] * len(all_related_skus))
        q = f"SELECT magento_ref_code, product_name, sale_price FROM {TABLE_NAME} WHERE magento_ref_code IN ({phs})"
        related_products = await db.execute_query_async(q, params=tuple(all_related_skus))
        for rp in related_products:
            rpsku = rp.get("magento_ref_code")
            if rpsku:
                sku_lookup[rpsku] = {
                    "sku": rpsku,
                    "name": rp.get("product_name"),
                    "price": int(rp.get("sale_price") or 0),
                }

    for p in products[:3]:
        sale = float(p.get("sale_price") or 0)
        orig = float(p.get("original_price") or 0)
        has_discount = sale < orig and orig > 0
        disc_pct = int(p.get("discount_percent") or 0)

        main_sku = p.get("magento_ref_code", "")
        parsed_rel = raw_parsed_map.get(main_sku, {"suggest": [], "similar": []})
        rich_suggest = [sku_lookup[s] for s in parsed_rel["suggest"] if s in sku_lookup]
        rich_similar = [sku_lookup[s] for s in parsed_rel["similar"] if s in sku_lookup]

        desc = (p.get("description_text") or "").strip()
        desc_short = desc[:200] + "..." if len(desc) > 200 else desc

        item = {
            "sku": main_sku,
            "name": p.get("product_name", ""),
            "price": int(sale),
            "original_price": int(orig),
            "discount": f"-{disc_pct}%" if has_discount else None,
            "color": p.get("master_color", ""),
            "gender": p.get("gender_by_product", ""),
            "product_line": p.get("product_line_vn", ""),
            "image": p.get("product_image_url_thumbnail", ""),
            "url": p.get("product_web_url", ""),
            "sizes": p.get("size_scale", ""),
            "description": desc_short,
            "suggest_items": rich_suggest,
            "similar_items": rich_similar,
        }

        if "in_stock" in p:
            item["in_stock"] = p["in_stock"]
            item["total_qty"] = p["total_qty"]
            item["stock"] = p["stock"]

        if "ai_description" in p:
            item["ai_description"] = p["ai_description"]
        if "ai_matches" in p:
            item["ai_matches"] = p["ai_matches"]

        formatted.append(item)
    return formatted

def _build_sku_query(code: str) -> tuple[str, list]:
    code = code.strip().upper()
    sql = f"""
    SELECT {SELECT_COLUMNS}
    FROM {TABLE_NAME}
    WHERE (UPPER(magento_ref_code) = %s
           OR UPPER(internal_ref_code) = %s
           OR UPPER(magento_ref_code) LIKE %s)
    ORDER BY quantity_sold DESC NULLS LAST
    LIMIT 20
    """
    return sql, [code, code, f"{code}%"]

class ProductSearchEngine:
    """
    Standalone Product Search Service.

    Dual-Lane Architecture:
      Lane 1 (LITERAL):  raw_text LIKE trực tiếp — KHÔNG filter, giữ nguyên ý user
      Lane 2 (INFERRED): AI suy luận → fixed filters + tags/keywords
      → Merge + Dedup theo SKU → Ưu tiên LITERAL trước
      → Nếu cả 2 rỗng → fallback cascade (tier 3-7)
    """

    # ── Dual-Lane: chạy LITERAL + INFERRED song song, merge kết quả ──
    async def _dual_query_search(
        self, req: LeadSearchInput, db
    ) -> tuple[list, int, str | None]:
        """
        Chạy 2 lane song song:
          Lane 1 (LITERAL): raw_text → LIKE thuần trên product_name + description (KHÔNG filter)
          Lane 2 (INFERRED): fixed clauses + tags/keywords → structured filters
        Merge + dedup, ưu tiên LITERAL trước.
        Nếu cả 2 rỗng → delegate cho _fallback_cascade (tier 3+).
        """
        import asyncio

        inf = req.inferred
        lit = req.literal

        # ── Build Lane 1: LITERAL — chỉ LIKE raw text, KHÔNG có fixed clauses ──
        literal_coro = None
        if lit.raw_text and lit.raw_text.strip():
            from agent.lead_stage_agent.product_mapping import resolve_product_name
            raw_text = resolve_product_name(lit.raw_text.strip())
            
            # Lọc bỏ stop words phổ biến khi khách chat để tránh tịt Literal Search
            stop_words = {
                "tìm", "mua", "muốn", "mình", "có", "không", "ko", "nào", "cần", "cho", "xin", 
                "nhé", "nha", "với", "hỏi", "đồ", "cái", "chiếc", "thế", "ấy", "này", "kia", 
                "đó", "thử", "nhỉ", "vậy", "mà", "chứ", "ạ", "ơi", "đâu", "đây", "rồi", 
                "nữa", "sao", "đi", "được", "ra", "để", "thì", "tầm", "khoảng", "loại"
            }
            # Ép lower-case toàn bộ words
            words = [w.lower() for w in raw_text.split() if w.lower() not in stop_words]
            
            if words:
                params_lit = []
                word_clauses = []
                for word in words:
                    params_lit.extend([f"%{word}%", f"%{word}%", f"%{word}%"])
                    word_clauses.append(
                        "(LOWER(product_name) LIKE %s OR LOWER(description_text) LIKE %s OR LOWER(description_text_full) LIKE %s)"
                    )
                literal_where = " AND ".join(word_clauses)

                # ── GLOBAL FILTERS (TẦNG 1): Áp dụng cứng Gender và Age vào Literal Search ──
                if inf.gender_by_product:
                    literal_where = f"({literal_where}) AND gender_by_product = %s"
                    params_lit.append(inf.gender_by_product)
                if inf.age_by_product:
                    literal_where = f"({literal_where}) AND age_by_product = %s"
                    params_lit.append(inf.age_by_product)

                sql_lit = f"""
                SELECT {SELECT_COLUMNS}
                FROM {TABLE_NAME}
                WHERE {literal_where}
                ORDER BY quantity_sold DESC NULLS LAST, sale_price ASC
                LIMIT 150
                """
                literal_coro = db.execute_query_async(sql_lit, params=tuple(params_lit))

        # ── Build Lane 2: INFERRED — full structured filters ──
        inferred_coro = None
        has_inferred = (
            inf.product_line_vn or inf.gender_by_product or inf.master_color
            or inf.tags or inf.keywords or inf.price_min is not None
            or inf.price_max is not None or inf.size or inf.discovery_mode
        )
        if has_inferred:
            params_inf = []
            fixed = _build_fixed_clauses(inf, params_inf)

            # Thêm tags + keywords vào search clause
            search_parts = []
            if inf.tags:
                tag_clause = _build_tag_clauses(inf.tags, params_inf)
                if tag_clause:
                    search_parts.append(tag_clause)
            if inf.keywords:
                kw_clause = _build_search_clause(inf.keywords, params_inf)
                if kw_clause:
                    search_parts.append(kw_clause)

            search_combined = f"({' OR '.join(search_parts)})" if search_parts else None

            ex_params = []
            exclusions = _build_exclusion_clauses(inf.keywords, ex_params)
            sql_inf = _build_full_query(fixed, search_combined, exclusions)
            inferred_coro = db.execute_query_async(
                sql_inf, params=tuple(params_inf + ex_params)
            )

        # ── Chạy song song ──
        literal_products = []
        inferred_products = []

        if literal_coro and inferred_coro:
            literal_products, inferred_products = await asyncio.gather(
                literal_coro, inferred_coro
            )
        elif literal_coro:
            literal_products = await literal_coro
        elif inferred_coro:
            inferred_products = await inferred_coro

        logger.info(
            "[DUAL LANE] LITERAL=%d results | INFERRED=%d results",
            len(literal_products), len(inferred_products),
        )

        # ── Merge + Dedup (ưu tiên LITERAL trước) ──
        if literal_products or inferred_products:
            seen_base_codes: set[str] = set()
            merged: list[dict] = []

            # INFERRED match = ưu tiên cao hơn → thêm trước (chuẩn giới tính & phân loại)
            for p in inferred_products:
                base_code = p.get("internal_ref_code", "")
                if base_code and base_code not in seen_base_codes:
                    p["_search_source"] = "inferred"
                    seen_base_codes.add(base_code)
                    merged.append(p)

            # LITERAL match = bổ sung thêm (dành cho bộ sưu tập, tên riêng)
            for p in literal_products:
                base_code = p.get("internal_ref_code", "")
                if base_code and base_code not in seen_base_codes:
                    p["_search_source"] = "literal"
                    seen_base_codes.add(base_code)
                    merged.append(p)

            if merged:
                if literal_products and inferred_products:
                    tier = 1  # dual hit
                elif literal_products:
                    tier = 1  # literal only
                else:
                    tier = 2  # inferred only
                return merged[:10], tier, None

        # ── Cả 2 rỗng → fallback cascade tier 3+ ──
        return await self._fallback_cascade(req, db)

    async def _fallback_cascade(
        self, req: LeadSearchInput, db
    ) -> tuple[list, int, str | None]:
        """Cascade fallback khi dual-lane rỗng (tier 3-7). Dùng inferred filters."""
        inf = req.inferred
        ex_params = []
        exclusions = _build_exclusion_clauses(inf.keywords, ex_params)

        # Tier 3: Semantic Vector Search (Fuzzy Match / BM25 Alternative)
        lit = req.literal
        if lit and lit.raw_text and lit.raw_text.strip():
            from common.embedding_service import create_embedding_async
            try:
                query_vector = await create_embedding_async(lit.raw_text.strip())
                if query_vector:
                    v_str = "[" + ",".join(str(v) for v in query_vector) + "]"
                    
                    params_vec = []
                    fixed_vec = _build_fixed_clauses(inf, params_vec)
                    where_str = " AND ".join(fixed_vec) if fixed_vec else "1=1"
                    if exclusions:
                        where_str += f" AND {exclusions}"
                    
                    sql_vec = f"""
                    WITH vector_matches AS (
                        SELECT /*+ SET_VAR(ann_params='{{"ef_search":256}}') */
                            {SELECT_COLUMNS},
                            approx_cosine_similarity(vector, {v_str}) as similarity_score
                        FROM {TABLE_NAME}
                        ORDER BY similarity_score DESC
                        LIMIT 200
                    )
                    SELECT * FROM vector_matches
                    WHERE {where_str}
                    ORDER BY similarity_score DESC, quantity_sold DESC
                    LIMIT 150
                    """
                    vec_products = await db.execute_query_async(sql_vec, params=tuple(params_vec + ex_params))
                    if vec_products:
                        seen_base_codes: set[str] = set()
                        good_vecs = []
                        for p in vec_products:
                            if p.get("similarity_score", 0) > 0.4:
                                base_code = p.get("internal_ref_code", "")
                                if base_code and base_code not in seen_base_codes:
                                    seen_base_codes.add(base_code)
                                    p["_search_source"] = "semantic_fuzzy"
                                    good_vecs.append(p)
                        if good_vecs:
                            return good_vecs[:10], 3, "FALLBACK_SEMANTIC"
            except Exception as e:
                logger.error(f"Semantic Search Fallback Error: {e}")

        # Tier 4: Chỉ fixed clauses
        params = []
        fixed = _build_fixed_clauses(inf, params)
        sql = _build_full_query(fixed, None, exclusions)
        products = await db.execute_query_async(sql, params=tuple(params + ex_params))
        if products:
            seen_base_codes: set[str] = set()
            dedup = []
            for p in products:
                base_code = p.get("internal_ref_code", "")
                if base_code and base_code not in seen_base_codes:
                    seen_base_codes.add(base_code)
                    dedup.append(p)
            return dedup[:10], 4, None

        # Tier 4: Drop gender
        if inf.product_line_vn and inf.gender_by_product:
            saved = inf.gender_by_product
            inf.gender_by_product = None
            params = []
            fixed = _build_fixed_clauses(inf, params)
            sql = _build_full_query(fixed, None, exclusions)
            products = await db.execute_query_async(sql, params=tuple(params + ex_params))
            inf.gender_by_product = saved
            if products:
                return _dedup(products), 4, None

        # Tier 5-7: Price relaxation
        if inf.price_max is not None and inf.product_line_vn:
            original_max = inf.price_max
            product_label = "/".join(inf.product_line_vn)

            for multiplier, tier_num in [(1.5, 5), (2.0, 6)]:
                new_max = int(original_max * multiplier)
                products = await _price_relaxed_search(inf, db, multiplier)
                if products:
                    cheapest_p = min(products, key=lambda p: float(p.get("sale_price") or 999_999_999))
                    cheapest = float(cheapest_p.get("sale_price") or 0)
                    cheapest_name = cheapest_p.get("product_name", "")
                    diff = int(cheapest - original_max)
                    msg = (
                        f"FALLBACK_PRICE: Khong co {product_label} duoi {original_max:,.0f}d. "
                        f'Mau re nhat: "{cheapest_name}" gia {cheapest:,.0f}d '
                        f"(chi them {diff:,}d so voi budget). "
                        f"Tong co {len(products)} mau trong tam {new_max:,.0f}d. "
                        f"-> HAY goi y SP nay cho khach va noi kheo: them chut xiu la co mau rat dep!"
                    )
                    return _dedup(products), tier_num, msg

            # Tier 7: drop price
            saved_max, saved_min = inf.price_max, inf.price_min
            inf.price_max = inf.price_min = None
            params = []
            fixed = _build_fixed_clauses(inf, params)
            sql = _build_full_query(fixed, None, exclusions)
            products = await db.execute_query_async(sql, params=tuple(params + ex_params))
            inf.price_max, inf.price_min = saved_max, saved_min
            if products:
                cheapest_p = min(products, key=lambda p: float(p.get("sale_price") or 999_999_999))
                cheapest = float(cheapest_p.get("sale_price") or 0)
                cheapest_name = cheapest_p.get("product_name", "")
                msg = (
                    f"FALLBACK_PRICE: Khong co {product_label} duoi {original_max:,.0f}d. "
                    f'Mau re nhat hien co: "{cheapest_name}" gia {cheapest:,.0f}d. '
                    f"Tong co {len(products)} mau. "
                    f"-> HAY goi y SP nay va noi: budget them chut la co mau rat dang!"
                )
                return _dedup(products), 7, msg

        return [], 3, None

    async def search(self, req: LeadSearchInput, reasoning: str | None = None) -> dict:
        """
        Entry point: Dual-Lane search + enrichment pipeline.
        """
        start = time.time()
        db = get_db_connection()

        try:
            fallback_msg = None

            if req.magento_ref_code:
                sql, params = _build_sku_query(req.magento_ref_code)
                products = await db.execute_query_async(sql, params=tuple(params))
                tier = 0
            else:
                # ★ DUAL-LANE: literal + inferred song song ★
                products, tier, fallback_msg = await self._dual_query_search(req, db)

            # Enrichment pipeline (giữ nguyên)
            products, stock_checked, stock_api_ms, timed_out = await _enrich_with_stock(products)
            products = await _enrich_with_outfit(products, db, tags=req.inferred.tags)

            for p in products:
                if stock_checked and p.get("_stock_detail"):
                    p["in_stock"] = True
                    p["total_qty"] = p.get("_total_qty")
                    p["stock"] = p.get("_stock_detail")

            formatted = await _format_products(products, db)
            elapsed_ms = round((time.time() - start) * 1000, 2)

            _search_modes = [
                "sku_lookup",
                "literal_like",
                "inferred_structured",
                "hard_filters_only",
                "drop_gender",
                "price_1.5x",
                "price_2x",
                "price_unlimited",
            ]
            inf = req.inferred

            logger.info(
                "[LEAD SEARCH] REQUEST  | literal='%s' | tags=%s | kw=%s | line=%s | price=%s~%s",
                req.literal.raw_text, inf.tags, inf.keywords, inf.product_line_vn, inf.price_min, inf.price_max,
            )
            logger.info(
                "[LEAD SEARCH] RESULT   | tier=%d (%s) | results=%d | time=%.0fms | stock=%.0fms",
                tier, _search_modes[min(tier, 7)], len(products), elapsed_ms, stock_api_ms,
            )

            result = {
                "status": "success",
                "count": len(products),
                "tier": tier,
                "search_mode": _search_modes[min(tier, 7)],
                "elapsed_ms": elapsed_ms,
                "reasoning": reasoning or "",
                "literal_text": req.literal.raw_text or "",
                "keywords_used": inf.keywords or [],
                "tags_used": inf.tags or [],
                "products": formatted,
            }

            if fallback_msg:
                result["fallback_message"] = fallback_msg

            return result

        except Exception as e:
            logger.error("Lead search tool error: %s", e, exc_info=True)
            return {"status": "error", "message": str(e)}

