"""
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 re
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
from .hard_patterns import HardPatternDetector

logger = logging.getLogger(__name__)

# Global hard pattern detector
_hard_pattern_detector = HardPatternDetector()

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"

EVENT_FALLBACKS: list[dict] = [
    {
        "patterns": [
            "30/4", "30-4", "giai phong", "giải phóng", "giai phong mien nam", "giải phóng miền nam",
        ],
        "master_color": "Màu đỏ",
        "tags": ["Màu đỏ", "Đi chơi / dạo phố"],
        "keywords": ["cờ đỏ sao vàng", "30/4"],
    },
    {
        "patterns": [
            "2/9", "2-9", "quoc khanh", "quốc khánh", "le quoc khanh", "lễ quốc khánh",
        ],
        "master_color": "Màu đỏ",
        "tags": ["Màu đỏ", "Đi chơi / dạo phố"],
        "keywords": ["quốc khánh", "lễ quốc khánh"],
    },
]

def _normalize_text(text: str) -> str:
    import unicodedata
    lowered = text.lower().strip()
    return "".join(
        ch for ch in unicodedata.normalize("NFD", lowered)
        if unicodedata.category(ch) != "Mn"
    )

def _text_contains_pattern(text: str, pattern: str) -> bool:
    if not text or not pattern:
        return False
    if any(ch.isdigit() for ch in pattern) or "/" in pattern or "-" in pattern:
        return pattern in text
    import re
    escaped = re.escape(pattern)
    return re.search(rf"(?<!\w){escaped}(?!\w)", text) is not None

def _apply_event_fallback(inf: "InferredSearch", text: str) -> bool:
    if not text:
        return False
    normalized = _normalize_text(text)
    for rule in EVENT_FALLBACKS:
        if any(_text_contains_pattern(normalized, pat) for pat in rule["patterns"]):
            if not inf.master_color:
                inf.master_color = rule["master_color"]
            if not inf.tags:
                inf.tags = list(rule["tags"])
            if not inf.keywords:
                inf.keywords = list(rule["keywords"])
            return True
    return False


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.")


# ======================================================
# UNDERWEAR / ĐỒ LÓT — Hard Override (Tool-level)
# Khi detect product_line_vn chứa đồ lót → drop gender + age
# để tìm cả nam/nữ, cả người lớn/trẻ em.
# Logic này BẮT BUỘC nằm ở tool, KHÔNG phụ thuộc prompt.
# ======================================================
UNDERWEAR_PRODUCT_LINES: set[str] = {
    "Quần lót", "Quần lót đùi", "Quần lót tam giác",
    "Áo lót", "Áo bra active",
}

def _is_underwear_request(product_lines: list[str]) -> bool:
    """Check nếu TẤT CẢ product_line_vn đều là đồ lót."""
    if not product_lines:
        return False
    resolved_lines: set[str] = set()
    for line in product_lines:
        if not line:
            continue
        resolved = resolve_product_line(line)
        for r in resolved:
            expanded = get_related_lines(r)
            resolved_lines.update(expanded)
    if not resolved_lines:
        return False
    return resolved_lines.issubset(UNDERWEAR_PRODUCT_LINES)

def _apply_underwear_override(req: LeadSearchInput) -> str | None:
    """
    Hard override cho đồ lót: drop gender + age để tìm ALL.
    Returns system_message nếu đã override, None nếu không.
    """
    inf = req.inferred
    if not _is_underwear_request(inf.product_line_vn):
        return None

    overrides = []
    if inf.gender_by_product:
        overrides.append(f"gender={inf.gender_by_product}")
        inf.gender_by_product = None
    if inf.age_by_product:
        overrides.append(f"age={inf.age_by_product}")
        inf.age_by_product = None

    if overrides:
        logger.info(
            "🩲 UNDERWEAR OVERRIDE: Dropped filters [%s] → search ALL genders/ages",
            ", ".join(overrides),
        )

    return (
        "[SYSTEM] Đây là yêu cầu tìm ĐỒ LÓT/SỊP. Tool đã tự động tìm TẤT CẢ giới tính "
        "(nam + nữ) và TẤT CẢ độ tuổi (người lớn + trẻ em). "
        "Hãy gợi ý sản phẩm phù hợp nhất dựa trên ngữ cảnh hội thoại."
    )


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 _extract_size_table_only(huong_dan_size: str) -> str:
    """Extract only the size table markdown (without guidance text)."""
    if not huong_dan_size:
        return ""
    lines = huong_dan_size.strip().split('\n')
    table_lines = []
    in_table = False

    for line in lines:
        if '|' in line:
            if 'Size' in line or 'size' in line.lower():
                in_table = True
                table_lines.append(line)
            elif in_table:
                if '---' not in line:
                    table_lines.append(line)
                else:
                    table_lines.append(line)
            if in_table and line.strip() == '':
                break
        elif in_table:
            break

    return '\n'.join(table_lines[:10])  # Max 10 rows


def _extract_compact_description(desc_data_cut: dict, product_count: int = 1) -> dict:
    """
    Extract compact description from pre-cut description_data_cut JSON.
    Data is already compressed (only 16 core fields) — no need to filter here.
    
    product_count: Controls detail level
      - 1-2 products: Full detail (all fields)
      - 3+  products: Ultra compact (name + desc + material + tagline only)
    """
    if not desc_data_cut:
        return {}

    # Field name mapping: Vietnamese key -> English output key
    FIELD_MAP = {
        "ten_san_pham": "name",
        "mo_ta_chinh": "desc",
        "chat_lieu": "material",
        "tinh_nang_vai": "fabric_features",
        "gioi_tinh": "gender",
        "phong_cach": "style",
        "mua": "season",
        "dip_mac": "occasions",
        "tags": "tags",
        "do_tuoi": "age_range",
        "loi_song": "lifestyle",
        "tinh_cach": "personality",
        "ly_do_mua": "why_buy",
        "cross_sell": "cross_sell",
        "hook_quang_cao": "hook",
        "tagline": "tagline",
    }

    # Ultra compact mode for multi-product results (3+ products)
    COMPACT_FIELDS = ["ten_san_pham", "mo_ta_chinh", "chat_lieu", "tagline", "tags", "phong_cach"]

    result = {}
    fields_to_use = COMPACT_FIELDS if product_count >= 3 else list(FIELD_MAP.keys())

    for vn_key in fields_to_use:
        en_key = FIELD_MAP.get(vn_key, vn_key)
        val = desc_data_cut.get(vn_key)
        if val:
            # Truncate long strings in multi-product mode
            if product_count >= 3 and isinstance(val, str) and len(val) > 100:
                val = val[:100] + "…"
            result[en_key] = val

    return result


# ======================================================
# SQL Builder
# ======================================================

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()
        
        # Lấy clean_description + description_data_cut từ bảng ultra_descriptions
        desc_rows = cursor.execute(
            f"""
            SELECT base_ref_code, clean_description, description_data_cut
            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

    import random
    
    # Nhóm theo anchor -> role -> unique matches
    temp_outfit_map: dict[str, dict[str, dict[str, dict]]] = {}
    for row in outfit_rows:
        anchor = row["anchor_product_code"]
        role = row["match_role"]
        code = row["match_product_code"]
        
        if anchor not in temp_outfit_map:
            temp_outfit_map[anchor] = {}
        if role not in temp_outfit_map[anchor]:
            temp_outfit_map[anchor][role] = {}
            
        temp_outfit_map[anchor][role][code] = {
            "match_product_code": code,
            "match_product_name": row["match_product_name"],
            "role": role,
            "reason": row["ai_reason"]
        }

    outfit_map: dict[str, list] = {}
    for anchor, roles in temp_outfit_map.items():
        outfit_map[anchor] = []
        all_items = []
        
        # Bước 1: Lấy ít nhất 1 item cho mỗi role để đảm bảo đa dạng
        for role, unique_items in roles.items():
            items_list = list(unique_items.values())
            picked = random.choice(items_list)
            outfit_map[anchor].append(picked)
            # Lưu các item còn lại vào all_items
            all_items.extend([item for item in items_list if item["match_product_code"] != picked["match_product_code"]])
            
        # Bước 2: Nếu chưa đủ 10 items, lấy thêm ngẫu nhiên từ all_items
        random.shuffle(all_items)
        needed = 10 - len(outfit_map[anchor])
        if needed > 0 and all_items:
            outfit_map[anchor].extend(all_items[:needed])
        
    desc_map: dict[str, dict] = {}  # base_ref -> {clean_description, description_data_cut}
    for row in desc_rows:
        desc_map[row["base_ref_code"]] = {
            "clean_description": row["clean_description"],
            "description_data_cut": row["description_data_cut"],
        }

    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 + description_data_cut
        desc_info = desc_map.get(base)
        if desc_info:
            if desc_info.get("clean_description"):
                p["ai_description"] = desc_info["clean_description"]
            if desc_info.get("description_data_cut"):
                p["description_data_cut"] = desc_info["description_data_cut"]

        # 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 = []

    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", "")

        # Parse description_data_cut (pre-compressed from SQLite)
        desc_data_cut = None
        raw_cut = p.get("description_data_cut")
        if raw_cut:
            try:
                desc_data_cut = json.loads(raw_cut) if isinstance(raw_cut, str) else raw_cut
            except Exception:
                desc_data_cut = None

        # Build compact item
        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", ""),
        }

        # --- Inject Size Message ---
        raw_size = p.get("size_scale", "")
        parsed_sizes = []
        if isinstance(raw_size, str) and raw_size.strip():
            if "[" in raw_size:
                try:
                    parsed_sizes = json.loads(raw_size)
                except Exception:
                    parsed_sizes = [s.strip() for s in raw_size.replace("[", "").replace("]", "").replace('"', '').replace("'", '').split(",") if s.strip()]
            else:
                parsed_sizes = [s.strip() for s in raw_size.replace(",", "|").split("|") if s.strip()]
        elif isinstance(raw_size, list):
            parsed_sizes = [str(s) for s in raw_size]

        from .size_message_builder import build_size_message
        desc_text = p.get("description_text", "")
        size_msg = build_size_message(
            gender=p.get("gender_by_product", ""),
            product_line=p.get("product_line_vn", ""),
            sizes=parsed_sizes,
            description=desc_text
        )
        
        item["sizes_available"] = parsed_sizes
        if size_msg:
            item["size_message"] = size_msg
        # ---------------------------

        # Add compact description from description_data_cut
        product_count = len(products[:3])
        if desc_data_cut:
            compact_desc = _extract_compact_description(
                desc_data_cut,
                product_count=product_count,
            )
            # Merge compact description fields into item
            item.update(compact_desc)
        else:
            # Fallback to short description_text
            desc = (p.get("description_text") or "").strip()
            item["description"] = (desc[:200] + "...") if len(desc) > 200 else desc

        # Inject outfit recommendations (ai_matches)
        if p.get("ai_matches"):
            item["outfit_recommendations"] = p["ai_matches"]

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

        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

        # Event fallback: set master_color/tags/keywords if missing (30/4, 2/9, ...)
        fallback_text = " ".join(
            [
                lit.raw_text or "",
                " ".join(inf.keywords or []),
            ]
        ).strip()
        _apply_event_fallback(inf, fallback_text)

        # ── SAFETY VALVE: Force 'adult' for office/work intent if not inferred ──
        office_tags = {"Đi làm / Công sở", "Thanh lịch", "Chỉnh chu", "Lịch sự"}
        if not inf.age_by_product and any(tag in office_tags for tag in (inf.tags or [])):
            inf.age_by_product = "adult"
            
        # ── 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",
                "combo", "bộ", "set"
            }
            # É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)

            # Lọc bỏ từ khóa gây nhiễu (blacklist) khỏi Inferred Search (vì Literal Search đã xử lý rồi)
            blacklist = {"combo", "set", "bộ"}
            filtered_tags = [t for t in (inf.tags or []) if t.lower() not in blacklist]
            filtered_keywords = [k for k in (inf.keywords or []) if k.lower() not in blacklist]

            # Thêm tags + keywords vào search clause
            search_parts = []
            if filtered_tags:
                tag_clause = _build_tag_clauses(filtered_tags, params_inf)
                if tag_clause:
                    search_parts.append(tag_clause)
            if filtered_keywords:
                kw_clause = _build_search_clause(filtered_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(filtered_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 {' AND '.join(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,
                     user_insight: dict = None) -> dict:
        """
        Entry point: Dual-Lane search + enrichment pipeline.

        Args:
            user_insight: User insight dict (chứa LATEST_PRODUCT_INTEREST, TARGET, ...)
        """
        start = time.time()
        db = get_db_connection()

        try:
            fallback_msg = None

            # ★★★ PRE-SEARCH: HARD PATTERN DETECTION & ADAPTATION ★★★
            # Detect nếu query có pattern "rẻ hơn", "tương tự", ...
            context_products = []
            if user_insight:
                # Lấy LATEST_PRODUCT_INTEREST và lookup thành product dict
                latest = user_insight.get("LATEST_PRODUCT_INTEREST")
                if latest:
                    # Parse SKU từ format "Áo Polo Nam Basic (8TP25A002)" hoặc "8TP25A002"
                    sku = self._parse_sku_from_insight(latest)
                    if sku:
                        # Lookup product từ DB để có giá
                        product = await self._lookup_product_by_sku(sku, db)
                        if product:
                            context_products = [product]

            pattern = _hard_pattern_detector.detect(req.literal.raw_text, context_products)

            if pattern.pattern_type == "cheaper_than" and pattern.target_price_max:
                # Override price_max từ pattern
                orig_price_max = req.inferred.price_max
                req.inferred.price_max = int(pattern.target_price_max)
                logger.info(
                    f"[HARD PATTERN] 'Rẻ hơn' detected: ref_price={pattern.reference_price:,.0f}đ "
                    f"→ set price_max={req.inferred.price_max:,.0f}đ (was {orig_price_max})"
                )
            elif pattern.pattern_type == "more_expensive_than" and pattern.target_price_min:
                orig_price_min = req.inferred.price_min
                req.inferred.price_min = int(pattern.target_price_min)
                logger.info(
                    f"[HARD PATTERN] 'Đắt hơn' detected: ref_price={pattern.reference_price:,.0f}đ "
                    f"→ set price_min={req.inferred.price_min:,.0f}đ (was {orig_price_min})"
                )
            # Các pattern khác có thể xử lý sau...

            # ★ UNDERWEAR HARD OVERRIDE — drop gender/age trước khi search ★
            underwear_msg = _apply_underwear_override(req)

            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)
            stock_api_ms = 0.0
            products = await _enrich_with_outfit(products, db, tags=req.inferred.tags)

            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": " ".join([w for w in (req.literal.raw_text or "").split() if w.lower() not in {"combo", "bộ", "set"}]),
                "keywords_used": inf.keywords or [],
                "tags_used": inf.tags or [],
                "products": formatted,
            }

            if fallback_msg:
                result["fallback_message"] = fallback_msg
            if underwear_msg:
                result["system_message"] = underwear_msg
                
            has_sizes = any("size_message" in p for p in formatted)
            if has_sizes:
                result["size_tools_message"] = (
                    "CHÚ Ý QUAN TRỌNG TỪ HỆ THỐNG: Mỗi sản phẩm trả về đã đính kèm `size_message` "
                    "chứa các size CÒN HÀNG và Bảng đo tương ứng. BẠN PHẢI ƯU TIÊN lấy đúng "
                    "`size_message` của sản phẩm đó để tư vấn cho khách dựa trên chiều cao cân nặng "
                    "của họ, và chú ý áp dụng các Mẹo nâng/hạ size (nếu có). KHÔNG tự bịa size."
                )

            has_outfits = any("outfit_recommendations" in p for p in formatted)
            if has_outfits:
                result["outfit_tools_message"] = (
                    "GỢI Ý PHỐI ĐỒ: Một số sản phẩm có kèm `outfit_recommendations` — danh sách "
                    "các items phối cùng (vai trò: top/bottom/layer/accessory) kèm lý do. "
                    "Hãy gợi ý khách mua combo nếu phù hợp, ưu tiên các sản phẩm match_role "
                    "bổ trợ cho item chính. Luôn nêu mã SKU khi gợi ý."
                )

            has_desc = any("desc" in p or "why_buy" in p for p in formatted)
            if has_desc:
                result["description_tools_message"] = (
                    "MÔ TẢ SẢN PHẨM: Mỗi sản phẩm có kèm thông tin mô tả ngắn gọn "
                    "(desc, material, why_buy, hook, tagline...). Hãy dùng các thông tin "
                    "này để tư vấn khách hàng một cách tự nhiên, KHÔNG copy nguyên văn, "
                    "mà hãy diễn đạt lại phù hợp với ngữ cảnh hội thoại."
                )

            return result

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

    # ═══════════════════════════════════════════════════════════════
    # HELPER: Hard Pattern Support
    # ═══════════════════════════════════════════════════════════════

    def _parse_sku_from_insight(self, insight_value: str) -> str | None:
        """Parse SKU từ LATEST_PRODUCT_INTEREST."""
        if not insight_value:
            return None
        # Format: "Áo Polo Nam Basic (8TP25A002)" hoặc "8TP25A002"
        match = re.search(r"\(?([A-Z0-9]{4,}-[A-Z0-9]{3,})\)?", str(insight_value))
        return match.group(1) if match else None

    async def _lookup_product_by_sku(self, sku: str, db) -> dict | None:
        """Lookup product từ DB bằng SKU."""
        sql = f"""
        SELECT {SELECT_COLUMNS}
        FROM {TABLE_NAME}
        WHERE magento_ref_code = %s OR internal_ref_code = %s
        LIMIT 1
        """
        result = await db.execute_query_async(sql, params=(sku, sku))
        return result[0] if result else None

