import json
import logging
import re
import time
import httpx
import asyncio
from typing import List, Tuple, Dict, Any, Optional
from pydantic import BaseModel, Field

# Core module imports từ local tool_module
from .db_connector import DBConnector
from .tags_mapping import TAG_TO_BITMAP_COL, TAG_TEXT_MAPPING
from .product_mapping import get_related_lines, resolve_product_line
from .pattern_detector import HardPatternDetector
from .size_message_builder import build_size_message

# Constants

logger = logging.getLogger(__name__)

# Global instances
_hard_pattern_detector = HardPatternDetector()

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,
    tags,
    similar_items,
    outfit_recommendations,
    description_text_full
"""

# ==============================================================================
# AGENTIC DUAL-LANE SEARCH: LOGIC & EVALUATION (ƯU/NHƯỢC ĐIỂM)
# ==============================================================================
"""
Cơ chế Dual-Lane Search (Literal + Inferred) giúp tối ưu hóa khả năng tìm kiếm:
- Ưu điểm:
    1. Inferred Lane: Đảm bảo độ chính xác tuyệt đối với các bộ lọc thuộc tính (màu, giá, giới tính).
    2. Literal Lane: Bắt được các từ khóa cảm xúc, phong cách (đi tiệc, đi chơi) mà DB không có cột riêng.
    3. Fallback Cascade: Giảm thiểu tối đa tình trạng "không tìm thấy kết quả" bằng cách nới lỏng tiêu chí.
- Nhược điểm:
    1. Keyword Noise: Có thể bắt nhầm từ khóa trong mô tả sản phẩm (ví dụ: "không dành cho...")
    2. Latency: Cần tối ưu hóa query StarRocks khi thực hiện nhiều tầng fallback.
"""

# ==============================================================================
# LOGIC BỔ TRỢ (DI CƯ TỪ LEAD STAGE)
# ==============================================================================

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

EVENT_FALLBACKS: list[dict] = [
    {
        "patterns": ["30/4", "30-4", "giai phong", "giải phóng"],
        "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"],
        "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 _apply_event_fallback(inf: Any, text: str) -> bool:
    if not text: return False
    norm = _normalize_text(text)
    for rule in EVENT_FALLBACKS:
        if any(pat in norm 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

# ==============================================================================
# SEARCH MODELS
# ==============================================================================

class LiteralSearch(BaseModel):
    raw_text: str = ""

class InferredSearch(BaseModel):
    product_line_vn: List[str] = Field(default_factory=list)
    gender_by_product: Optional[str] = None
    age_by_product: Optional[str] = None
    master_color: Optional[str] = None
    tags: List[str] = Field(default_factory=list)
    keywords: List[str] = Field(default_factory=list)
    price_min: Optional[int] = None
    price_max: Optional[int] = None
    discount_min: Optional[int] = None
    discount_max: Optional[int] = None
    discovery_mode: Optional[str] = None
    size: Optional[str] = None

# ==============================================================================
# SQL BUILDER HELPERS
# ==============================================================================

def _build_fixed_clauses(inf: InferredSearch, params: list) -> list[str]:
    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: lines.extend(get_related_lines(r))
        if lines:
            placeholders = ", ".join(["%s"] * len(lines))
            params.extend(lines)
            clauses.append(f"product_line_vn IN ({placeholders})")

    if inf.gender_by_product:
        g = inf.gender_by_product.lower()
        # DB values: men, women, boy, girl, unisex, others
        if g in ["women", "nu", "female", "nữ", "woman"]:
            genders = ["women", "unisex"]
        elif g in ["men", "nam", "male", "man"]:
            genders = ["men", "unisex"]
        elif g in ["boy", "bé trai", "be_trai"]:
            genders = ["boy", "unisex"]
        elif g in ["girl", "bé gái", "be_gai"]:
            genders = ["girl", "unisex"]
        else:
            genders = [g, "unisex"]
        params.extend(genders)
        clauses.append(f"gender_by_product IN ({', '.join(['%s']*len(genders))})")

    if inf.age_by_product:
        # DB values: adult, kid, others
        age = inf.age_by_product.lower()
        if age in ["children", "child", "kid", "kids", "trẻ em", "tre_em", "bé"]:
            age = "kid"
        elif age in ["adult", "adults", "người lớn"]:
            age = "adult"
        params.append(age)
        clauses.append("age_by_product = %s")

    if inf.master_color:
        # ── Robust Color Filter ──
        # AI có thể output "Màu đen", "đen", "black" — DB lưu "Đen/ Black"
        # Cần strip prefix + map VN→EN + bỏ dấu → thử tất cả patterns
        import unicodedata as _ucd
        COLOR_VN_TO_EN = {
            "đỏ": "red", "do": "red", "trắng": "white", "trang": "white",
            "đen": "black", "den": "black", "xanh": "blue", "xanh dương": "blue",
            "xanh da trời": "blue", "xanh lá": "green", "xanh la": "green",
            "hồng": "pink", "hong": "pink", "tím": "purple", "tim": "purple",
            "cam": "orange", "vàng": "yellow", "vang": "yellow",
            "nâu": "brown", "nau": "brown", "xám": "gray", "xam": "gray",
            "be": "beige", "kem": "cream", "bạc": "silver", "bac": "silver",
            "vàng đồng": "gold", "vang dong": "gold",
        }
        c_raw = inf.master_color.strip()
        # Strip "Màu " / "màu " prefix (AI hay thêm)
        import re as _re
        c_clean = _re.sub(r'^[Mm]àu\s+', '', c_raw)
        # Bỏ dấu tiếng Việt
        c_no_accent = "".join(
            ch for ch in _ucd.normalize("NFD", c_clean.lower())
            if _ucd.category(ch) != "Mn"
        ).strip()
        # Map sang English
        en_color = COLOR_VN_TO_EN.get(c_clean.lower()) or COLOR_VN_TO_EN.get(c_no_accent)

        color_or_parts = []
        # Pattern 1: cleaned Vietnamese (e.g. "đen" matches "Đen/ Black")
        color_or_parts.append("(LOWER(master_color) LIKE %s OR LOWER(product_color_name) LIKE %s)")
        params.extend([f"%{c_clean.lower()}%", f"%{c_clean.lower()}%"])
        # Pattern 2: no-accent fallback (e.g. "den" matches)
        if c_no_accent != c_clean.lower():
            color_or_parts.append("(LOWER(master_color) LIKE %s OR LOWER(product_color_name) LIKE %s)")
            params.extend([f"%{c_no_accent}%", f"%{c_no_accent}%"])
        # Pattern 3: English color (e.g. "black" matches "Đen/ Black")
        if en_color:
            color_or_parts.append("(LOWER(master_color) LIKE %s OR LOWER(product_color_name) LIKE %s)")
            params.extend([f"%{en_color}%", f"%{en_color}%"])

        clauses.append("(" + " OR ".join(color_or_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.size:
        params.append(inf.size.upper())
        clauses.append("FIND_IN_SET(%s, REPLACE(size_scale, '|', ',')) > 0")

    return clauses

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_TEXT_MAPPING:
            text_terms.append(TAG_TEXT_MAPPING[tag_lower])
        else:
            # Fallback for hallucinated tags
            clean_tag = tag_lower.replace("occ:", "").replace("style:", "").replace("fit:", "").replace("wthr:", "").replace("func:", "")
            text_terms.append(clean_tag.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}%")
            params.append(f"%{term}%")
            term_clauses.append(f"(LOWER(description_text) LIKE %s OR LOWER(product_name) LIKE %s OR LOWER(tags) 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_exclusion_clauses(keywords: list, params: list) -> list[str]:
    clauses = []
    kws = " ".join(keywords).lower()
    if any(x in kws for x in ["đông", "lạnh", "winter"]):
        clauses.append("product_line_vn NOT IN (%s, %s)")
        params.extend(["Quần soóc", "Áo ba lỗ"])
    return clauses

def _build_full_query(fixed: list, search: str = None, exclusions: list = None) -> str:
    all_c = list(fixed)
    if search: all_c.append(search)
    if exclusions: all_c.extend(exclusions)
    where = " AND ".join(all_c) if all_c else "1=1"
    return f"SELECT {SELECT_COLUMNS} FROM {TABLE_NAME} WHERE {where} ORDER BY quantity_sold DESC LIMIT 150"

# ==============================================================================
# MAIN SEARCH ENGINE
# ==============================================================================

class SearchEngine:
    def __init__(self, use_sqlite: bool = False):
        self.db = DBConnector(use_sqlite=use_sqlite)

    async def _dual_lane_search(self, literal: str, inf: InferredSearch) -> Tuple[List[Dict], int, str]:
        # Lane 1: Literal LIKE Search
        literal_products = []
        if literal:
            stop_words = {"tìm", "mua", "có", "không", "nào", "cho", "xin", "nhé", "combo", "bộ", "set"}
            words = [w.lower() for w in literal.split() if w.lower() not in stop_words]
            if words:
                params_lit = []
                clauses_lit = []
                for w in words:
                    params_lit.extend([f"%{w}%", f"%{w}%"])
                    clauses_lit.append("(LOWER(product_name) LIKE %s OR LOWER(description_text) LIKE %s)")

                # Áp dụng cứng Gender/Age cho Literal (sử dụng mapping giống Inferred)
                where_lit = " AND ".join(clauses_lit)
                
                if inf.gender_by_product:
                    g = inf.gender_by_product.lower()
                    if g in ["women", "nu", "female", "nữ", "woman"]:
                        genders = ["women", "unisex"]
                    elif g in ["men", "nam", "male", "man"]:
                        genders = ["men", "unisex"]
                    elif g in ["boy", "bé trai", "be_trai"]:
                        genders = ["boy", "unisex"]
                    elif g in ["girl", "bé gái", "be_gai"]:
                        genders = ["girl", "unisex"]
                    else:
                        genders = [g, "unisex"]
                    params_lit.extend(genders)
                    where_lit += f" AND gender_by_product IN ({', '.join(['%s']*len(genders))})"
                
                if inf.age_by_product:
                    age = inf.age_by_product.lower()
                    if age in ["children", "child", "kid", "kids", "trẻ em", "tre_em", "bé"]:
                        age = "kid"
                    elif age in ["adult", "adults", "người lớn"]:
                        age = "adult"
                    params_lit.append(age)
                    where_lit += " AND age_by_product = %s"

                sql_lit = f"SELECT {SELECT_COLUMNS} FROM {TABLE_NAME} WHERE {where_lit} ORDER BY quantity_sold DESC LIMIT 50"
                literal_products = await self.db.execute_query(sql_lit, tuple(params_lit))

        # Lane 2: Inferred Structured Search
        params_inf = []
        fixed_inf = _build_fixed_clauses(inf, params_inf)
        tag_search_clause = _build_tag_clauses(inf.tags, params_inf)
        ex_params = []
        exclusions = _build_exclusion_clauses(inf.keywords, ex_params)
        sql_inf = _build_full_query(fixed_inf, tag_search_clause, exclusions)
        inferred_products = await self.db.execute_query(sql_inf, tuple(params_inf + ex_params))

        # Merge & Dedup (Ưu tiên Inferred trước để chuẩn phân loại)
        seen = set()
        merged = []
        for p in inferred_products + literal_products:
            code = p.get("internal_ref_code")
            if code and code not in seen:
                seen.add(code); merged.append(p)

        if merged: return merged[:5], 1, "Success (Dual-Lane)"
        return await self._fallback_cascade(inf)

    async def _fallback_cascade(self, inf: InferredSearch) -> Tuple[List[Dict], int, str]:
        ex_params = []
        exclusions = _build_exclusion_clauses(inf.keywords, ex_params)

        # Tier 4: Drop Gender
        if inf.gender_by_product:
            saved = inf.gender_by_product; inf.gender_by_product = None
            params = []
            fixed = _build_fixed_clauses(inf, params)
            products = await self.db.execute_query(_build_full_query(fixed, None, exclusions), tuple(params + ex_params))
            inf.gender_by_product = saved
            if products: return products[:5], 4, "Fallback (Dropped Gender)"

        # Tier 5-6: Price Relaxation
        if inf.price_max:
            orig_max = inf.price_max
            for mult, tier in [(1.5, 5), (2.0, 6)]:
                inf.price_max = int(orig_max * mult)
                params = []
                fixed = _build_fixed_clauses(inf, params)
                products = await self.db.execute_query(_build_full_query(fixed, None, exclusions), tuple(params + ex_params))
                if products:
                    inf.price_max = orig_max
                    return products[:5], tier, f"Fallback (Price relaxed {mult}x)"
            inf.price_max = orig_max

        # Tier 7: Drop Price entirely
        saved_max, saved_min = inf.price_max, inf.price_min
        inf.price_max = inf.price_min = None
        params = []
        fixed = _build_fixed_clauses(inf, params)
        products = await self.db.execute_query(_build_full_query(fixed, None, exclusions), tuple(params + ex_params))
        inf.price_max, inf.price_min = saved_max, saved_min
        if products: return products[:5], 7, "Fallback (Unlimited Price)"

        return [], 0, "No results found"

    @staticmethod
    def _parse_similar_items(products: list) -> list:
        """Parse similar_items JSON string from One Big Table into list[dict]."""
        for p in products:
            raw = p.get("similar_items")
            if raw and isinstance(raw, str):
                try:
                    parsed = json.loads(raw)
                    p["similar_items"] = parsed[:5] if isinstance(parsed, list) else []
                except (json.JSONDecodeError, TypeError):
                    p["similar_items"] = []
            elif not raw:
                p["similar_items"] = []
        return products

    @staticmethod
    def _parse_outfit_recommendations(products: list, inf=None) -> list:
        """Parse outfit_recommendations JSON string from One Big Table into list[dict]."""
        # Determine occasion from inferred tags
        occasion_context = ""
        if inf and hasattr(inf, "tags") and inf.tags:
            from agent.tools.tool_module.tags_mapping import TAG_TEXT_MAPPING
            occasions = [TAG_TEXT_MAPPING.get(t) for t in inf.tags if t.startswith("occ:") and TAG_TEXT_MAPPING.get(t)]
            if occasions:
                occasion_context = f" Phù hợp mặc đi {', '.join(occasions)}."

        for p in products:
            raw = p.get("outfit_recommendations")

            # Fallback to product DB tags if no inferred occasion
            prod_occ = occasion_context
            if not prod_occ:
                prod_tags_raw = p.get("tags")
                if prod_tags_raw:
                    try:
                        import json
                        if isinstance(prod_tags_raw, str):
                            try:
                                t_list = json.loads(prod_tags_raw)
                            except:
                                t_list = [t.strip() for t in prod_tags_raw.split(",")]
                        elif isinstance(prod_tags_raw, list):
                            t_list = prod_tags_raw
                        else:
                            t_list = []

                        found_occ = [t for t in t_list if any(o in str(t).lower() for o in ["công sở", "đi làm", "đi chơi", "dạo phố", "mặc nhà", "mặc ngủ", "thể thao", "đi tiệc"])]
                        if found_occ:
                            prod_occ = f" Rất hợp để {found_occ[0].lower()}."
                    except Exception:
                        pass

            if raw and isinstance(raw, str):
                try:
                    import json
                    parsed = json.loads(raw)
                    # Giới hạn max 5 outfit để tránh hàng trăm SP
                    parsed = parsed[:5]

                    # Thêm context dịp mặc vào reason
                    if prod_occ:
                        for outfit in parsed:
                            if "reason" in outfit and prod_occ not in outfit["reason"]:
                                outfit["reason"] += prod_occ

                    p["outfit_recommendations"] = parsed
                except (json.JSONDecodeError, TypeError):
                    p["outfit_recommendations"] = []
            elif not raw:
                p["outfit_recommendations"] = []
        return products

    @staticmethod
    def _parse_description_data_full(products: list) -> list:
        """Parse description_text_full JSON string into structured metadata."""
        for p in products:
            raw = p.get("description_text_full")
            p["styling_metadata"] = {}
            if raw and isinstance(raw, str):
                try:
                    data = json.loads(raw)
                    metadata = {}
                    if "phong_cach" in data: metadata["phong_cach"] = data["phong_cach"]
                    if "dip_mac" in data: metadata["dip_mac"] = data["dip_mac"]
                    if "cross_sell" in data: metadata["cross_sell"] = data["cross_sell"]
                    if "tinh_nang_vai" in data: metadata["tinh_nang_vai"] = data["tinh_nang_vai"]
                    if "tags" in data: metadata["tags"] = data["tags"]

                    if "mo_ta_chinh" in data and data["mo_ta_chinh"]:
                        p["description_text"] = data["mo_ta_chinh"]

                    p["styling_metadata"] = metadata
                except (json.JSONDecodeError, TypeError):
                    pass
            p.pop("description_text_full", None)
        return products

    async def search(self, literal: str, inferred: Dict[str, Any]) -> Dict[str, Any]:
        inf = InferredSearch(**inferred)
        _apply_event_fallback(inf, literal)

        # Hard Pattern
        pattern = _hard_pattern_detector.detect(literal)
        if pattern.pattern_type == "cheaper_than" and pattern.target_price_max:
            inf.price_max = int(pattern.target_price_max)

        # Underwear Override
        is_underwear = any(line in ["Quần lót", "Áo lót", "Sịp", "Bra"] for line in inf.product_line_vn)
        if is_underwear: inf.gender_by_product = inf.age_by_product = None

        products, tier, message = await self._dual_lane_search(literal, inf)

        if products:
            products = self._parse_similar_items(products)
            products = self._parse_outfit_recommendations(products, inf)
            products = self._parse_description_data_full(products)
            for p in products:
                raw_size = p.get("size_scale", "")
                parsed = [s.strip() for s in str(raw_size).replace("[", "").replace("]", "").replace('"', '').replace('|', ',').split(",") if s.strip()]
                p["size_message"] = build_size_message(p.get("gender_by_product", ""), p.get("product_line_vn", ""), parsed, p.get("description_text", ""))

        return {
            "status": "success" if products else "no_results",
            "tier": tier, "message": message, "count": len(products), "results": products
        }
