"""
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 time
from datetime import datetime, timezone
import os as _os
import sqlite3

import httpx
from pydantic import BaseModel, Field

from common.starrocks_connection import get_db_connection
from .product_mapping import SYNONYM_TO_DB, 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 as _SQLITE_DB_PATH

_DB_123_PATH = _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 LeadSearchInput(BaseModel):
    model_config = {"extra": "forbid"}

    keywords: list[str] = Field(
        default=[],
        description=(
            "Từ khoá NGUYÊN VĂN từ câu hỏi khách. "
            "VD: khách nói '30/4' -> keywords=['30/4']. "
            "Khách nói 'cờ đỏ sao vàng' -> keywords=['cờ đỏ sao vàng']. "
            "Khách nói 'cotton ống rộng' -> keywords=['cotton', 'ống rộng']. "
            "Tối đa 2. Để [] nếu khách không nhắc gì đặc biệt."
        )
    )

    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. "
            "VD: 'đi tiệc' -> tags=['occ:di_tiec']. 'mùa đông' -> tags=['wthr:mua_dong', 'func:giu_am']. "
            "KHÔNG tự nghĩ tag mới! PHẢI giữ prefix! Tối đa 3."
        )
    )

    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. Ghi CHÍNH XÁC từ khách nói: 'trắng', 'đen', 'đỏ'... "
            "VD: 'áo cờ đỏ sao vàng' -> color='đỏ'."
        ),
    )
    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 san pham khach yeu cau. "
            "Nguoi lon: XS, S, M, L, XL, XXL, 3XL, 4XL. "
            "Tre em (chieu cao cm): 80, 86, 92, 98, 104, 110, 116, 122, 128, 134, 140, 152, 164. "
            "Tre so sinh (thang): 1/3, 4/6, 7/9, 10/12, 13/14. "
            "VD: 'ao size L' -> size='L'."
        ),
    )
    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 _build_fixed_clauses(req: LeadSearchInput, params: list) -> list[str]:
    clauses = []

    if req.product_line_vn:
        lines = []
        for line in req.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 req.gender_by_product:
        gender_lower = req.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 req.age_by_product:
        age_lower = req.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 req.master_color:
        c_raw = req.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 req.price_min is not None:
        params.append(req.price_min)
        clauses.append("sale_price >= %s")
    if req.price_max is not None:
        params.append(req.price_max)
        clauses.append("sale_price <= %s")

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

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

    if req.discovery_mode:
        mode = req.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]] = {
    "style:thanh_lich":  ("style", "Feminine"),
    "style:nang_dong":   ("style", "Dynamic"),
    "style:basic":       ("style", "Basic"),
    "style:ca_tinh":     ("style", "Street"),
    "style:de_thuong":   ("style", "Cute"),
    "style:tre_trung":   ("style", "Trend"),
    "style:toi_gian":    ("style", "Essential"),
    "style:smart_casual":("style", "Smart Casual"),
    "fit:oversize":  ("fitting", "Oversize"),
    "fit:slim":      ("fitting", "Slim"),
    "fit:regular":   ("fitting", "Regular"),
    "fit:wide_leg":  ("fitting", "Relax"),
    "fit:cropped":   ("fitting", "Boxy"),
    "fit:relaxed":   ("fitting", "Relax"),
    "wthr:mua_he":   ("season_sale", "Summer"),
    "wthr:mua_dong": ("season_sale", "Winter"),
    "occ:di_bien":   ("season_sale", "Summer"),
    "occ:du_lich":   ("season_sale", "Summer"),
    "occ:the_thao":  ("style", "Athleisure"),
}

TAG_TO_SEARCH_TEXT: dict[str, str] = {
    "occ:di_lam":   "đi làm",
    "occ:di_choi":  "đi chơi",
    "occ:di_tiec":  "đi tiệc",
    "occ:di_hoc":   "đi học",
    "occ:mac_nha":  "mặc nhà",
    "occ:da_ngoai": "dã ngoại",
    "occ:di_ngu":   "ngủ",
    "wthr:giao_mua":  "giao mùa",
    "wthr:troi_mua":  "mưa",
    "wthr:troi_nang": "nắng",
    "func:thoang_mat": "thoáng mát",
    "func:giu_am":     "giữ ấm",
    "func:tham_hut":   "thấm hút",
    "func:nhanh_kho":  "nhanh khô",
    "func:chong_uv":   "chống UV",
    "func:can_gio":    "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 20
    """

async def _price_relaxed_search(req: LeadSearchInput, db, multiplier: float) -> list:
    if req.price_max is None:
        return []
    saved_max = req.price_max
    saved_min = req.price_min
    req.price_max = int(req.price_max * multiplier)
    req.price_min = None
    params: list = []
    fixed = _build_fixed_clauses(req, params)
    sql = _build_full_query(fixed, None)
    products = await db.execute_query_async(sql, params=tuple(params))
    req.price_max = saved_max
    req.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 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 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(_DB_123_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

    occasion = _resolve_occasion(tags or [])

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

        placeholders = ",".join(["?"] * len(anchor_base_codes))
        ultra_rows = cursor.execute(
            f"""
            SELECT base_ref_code, magento_ref_code, ai_matches, 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 ultra_desc read error: %s", e)
        return products

    ultra_map: dict[str, dict] = {}
    for row in ultra_rows:
        ultra_map[row["base_ref_code"]] = {
            "ai_matches": row["ai_matches"],
            "clean_description": 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()
        ultra = ultra_map.get(base)

        if not ultra or not ultra["ai_matches"]:
            continue

        if ultra["clean_description"]:
            p["ai_description"] = ultra["clean_description"]

        try:
            ai_matches_json = json.loads(ultra["ai_matches"])
            p["ai_matches"] = ai_matches_json
        except (json.JSONDecodeError, TypeError):
            continue

    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-Query Architecture:
      Query 1 (RAW):  keywords LIKE trực tiếp — giữ nguyên ý user
      Query 2 (AI):   tags BITMAP/LIKE — AI suy luận mở rộng
      → Merge + Dedup theo SKU → Ưu tiên RAW trước
      → Nếu cả 2 rỗng → fallback cascade (tier 3-7)
    """

    # ── Dual-Query: chạy RAW + AI song song, merge kết quả ──
    async def _dual_query_search(
        self, req: LeadSearchInput, db
    ) -> tuple[list, int, str | None]:
        """
        Chạy 2 query song song:
          1) RAW keywords → LIKE trên product_name/description
          2) AI tags     → BITMAP index + LIKE
        Merge + dedup, ưu tiên RAW match trước.
        Nếu cả 2 rỗng → delegate cho _cascading_search (tier 3+).
        """
        import asyncio

        ex_params_raw = []
        exclusions_raw = _build_exclusion_clauses(req.keywords, ex_params_raw)
        ex_params_ai = list(ex_params_raw)  # copy

        # ── Build Query 1: RAW keywords ──
        raw_coro = None
        if req.keywords:
            params_raw = []
            fixed_raw = _build_fixed_clauses(req, params_raw)
            search_raw = _build_search_clause(req.keywords, params_raw)
            if search_raw:
                sql_raw = _build_full_query(fixed_raw, search_raw, exclusions_raw)
                raw_coro = db.execute_query_async(
                    sql_raw, params=tuple(params_raw + ex_params_raw)
                )

        # ── Build Query 2: AI tags ──
        ai_coro = None
        if req.tags:
            params_ai = []
            fixed_ai = _build_fixed_clauses(req, params_ai)
            search_ai = _build_tag_clauses(req.tags, params_ai)
            if search_ai:
                sql_ai = _build_full_query(fixed_ai, search_ai, exclusions_raw)
                ai_coro = db.execute_query_async(
                    sql_ai, params=tuple(params_ai + ex_params_ai)
                )

        # ── Chạy song song ──
        raw_products = []
        ai_products = []

        if raw_coro and ai_coro:
            raw_products, ai_products = await asyncio.gather(raw_coro, ai_coro)
        elif raw_coro:
            raw_products = await raw_coro
        elif ai_coro:
            ai_products = await ai_coro

        logger.info(
            "[DUAL QUERY] RAW=%d results | AI=%d results",
            len(raw_products), len(ai_products),
        )

        # ── Merge + Dedup (ưu tiên RAW trước) ──
        if raw_products or ai_products:
            seen_skus: set[str] = set()
            merged: list[dict] = []

            # RAW match = ưu tiên cao hơn → thêm trước
            for p in raw_products:
                sku = p.get("magento_ref_code", "")
                if sku and sku not in seen_skus:
                    p["_search_source"] = "raw"
                    seen_skus.add(sku)
                    merged.append(p)

            # AI match = bổ sung thêm
            for p in ai_products:
                sku = p.get("magento_ref_code", "")
                if sku and sku not in seen_skus:
                    p["_search_source"] = "ai"
                    seen_skus.add(sku)
                    merged.append(p)

            if merged:
                # Xác định tier dựa trên nguồn kết quả
                if raw_products and ai_products:
                    tier = 1  # dual hit
                elif raw_products:
                    tier = 1  # raw only
                else:
                    tier = 2  # ai only
                return merged[:20], 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-query rỗng (tier 3-7)."""
        ex_params = []
        exclusions = _build_exclusion_clauses(req.keywords, ex_params)

        # Tier 3: Chỉ fixed clauses
        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

        # Tier 4: Drop gender
        if req.product_line_vn and req.gender_by_product:
            saved = 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
            if products:
                return products, 4, None

        # Tier 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_num in [(1.5, 5), (2.0, 6)]:
                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_num, msg

            # Tier 7: drop price
            saved_max, saved_min = req.price_max, req.price_min
            req.price_max = 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, req.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 products, 7, msg

        return [], 3, None

    async def search(self, req: LeadSearchInput, reasoning: str | None = None) -> dict:
        """
        Entry point: Dual-Query 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-QUERY thay vì cascade tuần tự ★
                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.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",
                "keywords_ngrambf",
                "tags_bitmap",
                "hard_filters_only",
                "drop_gender",
                "price_1.5x",
                "price_2x",
                "price_unlimited",
            ]

            logger.info(
                "[LEAD SEARCH] REQUEST  | tags=%s | kw=%s | line=%s | price=%s~%s | size=%s",
                req.tags, req.keywords, req.product_line_vn, req.price_min, req.price_max, req.size,
            )
            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 "",
                "keywords_used": req.keywords or [],
                "tags_used": req.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)}

