"""
Product Search Helpers — Unified search logic cho CANIFA.

3 search modes:
  CASE 1: CODE SEARCH  — CTE resolved_family (internal_ref_code / magento_ref_code / product_color_code)
  CASE 2: DISCOVERY     — Hàng mới / Bán chạy (Direct SQL, no embedding)
  CASE 3: SEMANTIC      — Vector cosine similarity (HNSW index)
"""

import logging

from common.embedding_service import create_embedding_async

logger = logging.getLogger(__name__)

PRODUCT_SEARCH_TABLE = "shared_source.magento_product_dimension_with_text_embedding"


# ══════════════════════════════════════════════════════════════
# 1. CODE PARSING HELPERS
# ══════════════════════════════════════════════════════════════

def _parse_code_search_input(raw_code: str) -> tuple[str, str | None]:
    """
    Chuẩn hóa mã user gửi và tách internal_ref_code / suffix.
    VD: "6OT24S001-SG055" → ("6OT24S001", "SG055")
        "6OT24S001"       → ("6OT24S001", None)
    """
    normalized = str(raw_code or "").strip().upper().replace(" ", "")
    if "-" not in normalized:
        return normalized, None
    internal_ref_code, suffix_code = normalized.split("-", 1)
    return internal_ref_code, suffix_code or None





# ══════════════════════════════════════════════════════════════
# 2. SQL FILTER BUILDERS
# ══════════════════════════════════════════════════════════════

def _get_price_clauses(params, sql_params: list) -> list[str]:
    """Lọc theo giá (Parameterized)."""
    clauses = []
    p_min = getattr(params, "price_min", None)
    if p_min is not None:
        clauses.append("sale_price >= %s")
        sql_params.append(p_min)
    p_max = getattr(params, "price_max", None)
    if p_max is not None:
        clauses.append("sale_price <= %s")
        sql_params.append(p_max)
    return clauses


def _get_discount_params(params) -> tuple[int | None, int | None]:
    """Lấy discount_min và discount_max từ params."""
    discount_min = getattr(params, "discount_min", None)
    discount_max = getattr(params, "discount_max", None)

    if discount_min is not None or discount_max is not None:
        if discount_min and discount_max:
            logger.info("🏷️ [DISCOUNT] %d%% - %d%%", discount_min, discount_max)
        elif discount_min:
            logger.info("🏷️ [DISCOUNT] >= %d%%", discount_min)
        else:
            logger.info("🏷️ [DISCOUNT] <= %d%%", discount_max)

    return discount_min, discount_max


def _get_metadata_clauses(params, sql_params: list) -> list[str]:
    """
    HARD FILTER ở SQL level: Gender, Age, Color, Product name.
    Gender tự động include 'unisex' fallback cho men/women.
    """
    clauses = []

    # ── Gender ──
    gender_val = getattr(params, "gender_by_product", None)
    if gender_val:
        gender_lower = gender_val.lower().strip()
        if gender_lower in ("men", "women"):
            clauses.append("gender_by_product IN (%s, %s)")
            sql_params.extend([gender_lower, "unisex"])
        else:
            clauses.append("gender_by_product = %s")
            sql_params.append(gender_lower)
        logger.info("👫 [SQL FILTER] Gender: %s", gender_val)

    # ── Age ──
    age_val = getattr(params, "age_by_product", None)
    if age_val:
        clauses.append("age_by_product = %s")
        sql_params.append(age_val.lower().strip())
        logger.info("🎂 [SQL FILTER] Age: %s", age_val)

    # ── Color ──
    color_val = getattr(params, "master_color", None)
    if color_val:
        color_lower = color_val.lower().strip()
        clauses.append("(LOWER(master_color) LIKE %s OR LOWER(product_color_name) LIKE %s)")
        sql_params.extend([f"%{color_lower}%", f"%{color_lower}%"])
        logger.info("🎨 [SQL FILTER] Color: %s", color_val)

    # ── Product name (synonym resolve + related lines) ──
    from agent.tools.product_mapping import PRODUCT_LINE_MAP
    GENERIC_WORDS = {key.split()[0].lower() for key in PRODUCT_LINE_MAP.keys()}

    name_val = getattr(params, "product_name", None)
    if name_val:
        from agent.tools.product_mapping import get_related_lines, resolve_product_name

        name_parts = [p.strip() for p in name_val.split("/") if p.strip()]
        all_phrases = set()
        for part in name_parts:
            resolved = resolve_product_name(part)
            for rname in get_related_lines(resolved):
                words = rname.strip().split()
                phrase = " ".join(w for w in words if w.lower() not in GENERIC_WORDS)
                if phrase:
                    all_phrases.add(phrase.lower())

        if all_phrases:
            like_parts = []
            for phrase in all_phrases:
                like_parts.append("(LOWER(product_name) LIKE %s OR LOWER(product_line_vn) LIKE %s)")
                sql_params.extend([f"%{phrase}%", f"%{phrase}%"])
            clauses.append(f"({' OR '.join(like_parts)})")
            logger.info("🏷️ [SQL FILTER] Product name: '%s' → %s", name_val, all_phrases)

    return clauses


# ══════════════════════════════════════════════════════════════
# 3. MAIN QUERY BUILDER
# ══════════════════════════════════════════════════════════════

async def build_starrocks_query(params, query_vector: list[float] | None = None) -> tuple[str, list]:
    """
    Build SQL query với Parameterized Query.
    Returns: (sql_string, params_list)
    """

    # ──────────────────────────────────────────────────────────
    # CASE 1: CODE SEARCH — magento_ref_code + product_color_code
    # Logic Y HỆT sku_search_tool.py:
    #   base_code = phần trước dấu '-'
    #   normalized = full code user gửi
    # ──────────────────────────────────────────────────────────
    magento_code = getattr(params, "magento_ref_code", None)
    if magento_code:
        normalized = str(magento_code).strip().upper().replace(" ", "")
        base_code, suffix_code = _parse_code_search_input(normalized)

        sql_params = [
            base_code,              # UPPER(magento_ref_code) = %s      (base code)
            normalized,             # UPPER(magento_ref_code) = %s      (full code)
            normalized,             # UPPER(product_color_code) = %s    (exact match)
            f"{base_code}-%",       # UPPER(product_color_code) LIKE %s (all variants)
        ]

        logger.info("🏷️ [CODE SEARCH] input=%s, base=%s, suffix=%s", normalized, base_code, suffix_code)

        sql = f"""
        SELECT
            magento_ref_code, product_color_code, product_name,
            master_color, product_color_name,
            product_image_url_thumbnail, product_web_url,
            description_text_full, sale_price, original_price, discount_amount,
            ROUND(((original_price - sale_price) / original_price * 100), 0) as discount_percent,
            age_by_product, gender_by_product, product_line_vn,
            quantity_sold, size_scale,
            1.0 as similarity_score
        FROM {PRODUCT_SEARCH_TABLE}
        WHERE UPPER(magento_ref_code) = %s
           OR UPPER(magento_ref_code) = %s
           OR UPPER(product_color_code) = %s
           OR UPPER(product_color_code) LIKE %s
        """
        return sql, sql_params

    # ──────────────────────────────────────────────────────────
    # CASE 2: DISCOVERY — Hàng mới / Bán chạy (Direct SQL)
    # ──────────────────────────────────────────────────────────
    discovery_mode = getattr(params, "discovery_mode", None)
    if discovery_mode:
        discovery_mode = discovery_mode.lower().strip()
        sql_params: list = []

        where_clauses = _get_metadata_clauses(params, sql_params)
        where_clauses.extend(_get_price_clauses(params, sql_params))

        if discovery_mode == "new":
            where_clauses.append("is_new_product = 1")
            order_by = "quantity_sold DESC, magento_ref_code"
            logger.info("🆕 [DISCOVERY] New products")
        elif discovery_mode == "best_seller":
            where_clauses.append("quantity_sold > 0")
            order_by = "quantity_sold DESC, magento_ref_code"
            logger.info("🔥 [DISCOVERY] Best sellers")
        else:
            discovery_mode = None

        if discovery_mode:
            where_str = " AND ".join(where_clauses) if where_clauses else "1=1"
            sql = f"""
            SELECT
                internal_ref_code, magento_ref_code, product_color_code,
                product_name, master_color, product_image_url_thumbnail,
                product_web_url, sale_price, original_price, discount_amount,
                ROUND(((original_price - sale_price) / original_price * 100), 0) as discount_percent,
                age_by_product, gender_by_product, product_line_vn,
                quantity_sold, description_text_full, size_scale
            FROM {PRODUCT_SEARCH_TABLE}
            WHERE {where_str}
            ORDER BY {order_by}
            LIMIT 20
            """
            logger.info("⚡ [DISCOVERY] Direct SQL — no embedding")
            return sql, sql_params

    # ──────────────────────────────────────────────────────────
    # CASE 3: SEMANTIC VECTOR SEARCH
    # ──────────────────────────────────────────────────────────
    query_text = getattr(params, "description", None)
    if query_text and query_vector is None:
        query_vector = await create_embedding_async(query_text)

    if not query_vector:
        return "", []

    v_str = "[" + ",".join(str(v) for v in query_vector) + "]"
    sql_params: list = []

    # Collect all filters
    all_clauses = _get_price_clauses(params, sql_params) + _get_metadata_clauses(params, sql_params)

    discovery_mode = getattr(params, "discovery_mode", None)
    if discovery_mode:
        discovery_mode = discovery_mode.lower().strip()
        if discovery_mode == "new":
            all_clauses.append("is_new_product = 1")
        elif discovery_mode == "best_seller":
            all_clauses.append("quantity_sold > 0")

    # Discount filters
    discount_min, discount_max = _get_discount_params(params)
    post_filter = list(all_clauses)

    if discount_min is not None or discount_max is not None:
        post_filter.append("sale_price < original_price")
        if discount_min is not None:
            post_filter.append("discount_percent >= %s")
            sql_params.append(discount_min)
        if discount_max is not None:
            post_filter.append("discount_percent <= %s")
            sql_params.append(discount_max)

    post_filter_where = (" WHERE " + " AND ".join(post_filter)) if post_filter else ""

    # Sort order
    if discovery_mode == "best_seller":
        final_order = "ORDER BY max_sold DESC, max_score DESC"
        extra_agg = ",\n        MAX(quantity_sold) as max_sold"
    else:
        final_order = "ORDER BY max_score DESC"
        extra_agg = ""

    sql = f"""
    WITH vector_matches AS (
        SELECT /*+ SET_VAR(ann_params='{{"ef_search":256}}') */
            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, discount_amount,
            ROUND(((original_price - sale_price) / original_price * 100), 0) as discount_percent,
            age_by_product, gender_by_product,
            product_line_vn, product_line_en,
            description_text_full, size_scale,
            quantity_sold, is_new_product,
            approx_cosine_similarity(vector, {v_str}) as similarity_score
        FROM {PRODUCT_SEARCH_TABLE}
        ORDER BY similarity_score DESC
        LIMIT 200
    ),
    filtered_matches AS (
        SELECT * FROM vector_matches
        {post_filter_where}
        ORDER BY similarity_score DESC
        LIMIT 150
    )
    SELECT
        internal_ref_code,
        MAX_BY(magento_ref_code, similarity_score)              as magento_ref_code,
        product_color_code,
        MAX_BY(product_name, similarity_score)                   as product_name,
        MAX_BY(master_color, similarity_score)                   as master_color,
        MAX_BY(product_image_url_thumbnail, similarity_score)    as product_image_url_thumbnail,
        MAX_BY(product_web_url, similarity_score)                as product_web_url,
        MAX_BY(sale_price, similarity_score)                     as sale_price,
        MAX_BY(original_price, similarity_score)                 as original_price,
        MAX_BY(discount_amount, similarity_score)                as discount_amount,
        MAX_BY(discount_percent, similarity_score)               as discount_percent,
        MAX_BY(description_text_full, similarity_score)          as description_text_full,
        MAX_BY(gender_by_product, similarity_score)              as gender_by_product,
        MAX_BY(age_by_product, similarity_score)                 as age_by_product,
        MAX_BY(product_line_vn, similarity_score)                as product_line_vn,
        MAX_BY(quantity_sold, similarity_score)                  as quantity_sold,
        MAX_BY(size_scale, similarity_score)                     as size_scale,
        MAX(similarity_score) as max_score{extra_agg}
    FROM filtered_matches
    GROUP BY product_color_code, internal_ref_code
    {final_order}
    LIMIT 80
    """

    # try:
    #     import os
    #     query_log_path = os.path.join(os.path.dirname(__file__), "note/query.txt")

    #     executable_sql = sql
    #     for param in sql_params:
    #         if isinstance(param, str):
    #             escaped = param.replace("'", "''")
    #             executable_sql = executable_sql.replace("%s", f"'{escaped}'", 1)
    #         else:
    #             executable_sql = executable_sql.replace("%s", str(param), 1)

    #     with open(query_log_path, "w", encoding="utf-8") as f:
    #         f.write(f"-- [SEARCH] Full Executable Query\n-- Original Params: {sql_params}\n{executable_sql}")
    # except Exception as e:
    #     logger.error("Error writing to query.txt: %s", e)

    return sql, sql_params
