import json
import logging
import os as _os
import re
import sqlite3
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 .stock_provider import enrich_with_stock
from .product_mapping import get_related_lines, resolve_product_line
from .pattern_detector import HardPatternDetector
from .size_message_builder import build_size_message

# Constants
from common.constants import SQLITE_DB_PATH

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

# ==============================================================================
# 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:
        params.append(f"%{inf.master_color}%"); params.append(f"%{inf.master_color}%")
        clauses.append("(master_color LIKE %s OR product_color_name LIKE %s)")

    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_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
                where_lit = " AND ".join(clauses_lit)
                if inf.gender_by_product:
                    params_lit.append(inf.gender_by_product); where_lit += " AND gender_by_product = %s"
                if inf.age_by_product:
                    params_lit.append(inf.age_by_product); 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)
        ex_params = []
        exclusions = _build_exclusion_clauses(inf.keywords, ex_params)
        sql_inf = _build_full_query(fixed_inf, None, 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[:10], 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[:10], 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[:10], 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[:10], 7, "Fallback (Unlimited Price)"

        return [], 0, "No results found"

    async def _enrich_with_outfit(self, products: list) -> list:
        if not products or not _os.path.exists(SQLITE_DB_PATH): return products
        top_codes = [p.get("internal_ref_code") for p in products[:3] if p.get("internal_ref_code")]
        if not top_codes: return products
        try:
            conn = sqlite3.connect(SQLITE_DB_PATH)
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            placeholders = ",".join(["?"] * len(top_codes))
            outfits = cursor.execute(f"SELECT * FROM pg__dashboard_canifa__ai_outfit_product_matches WHERE anchor_product_code IN ({placeholders})", top_codes).fetchall()
            conn.close()
            
            outfit_map = {}
            for row in outfits:
                anchor = row["anchor_product_code"]
                outfit_map.setdefault(anchor, []).append({
                    "code": row["match_product_code"],
                    "name": row["match_product_name"],
                    "role": row["match_role"],
                    "reason": row["ai_reason"]
                })
            for p in products:
                p["outfit_recommendations"] = outfit_map.get(p.get("internal_ref_code"), [])
        except Exception as e: logger.error(f"Outfit error: {e}")
        return products

    async def search(self, literal: str, inferred: Dict[str, Any], check_stock: bool = True) -> 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:
            if check_stock: products = await enrich_with_stock(products)
            products = await self._enrich_with_outfit(products)
            for p in products:
                raw_size = p.get("size_scale", "")
                parsed = [s.strip() for s in str(raw_size).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
        }
