"""
Product Performance API Route
Provides real-time product data from StarRocks for the Product Performance dashboard.
"""

import logging
from typing import Optional

from fastapi import APIRouter, Query
from fastapi.responses import JSONResponse

from agent.tools.product_mapping import SYNONYM_TO_DB, resolve_color, resolve_product_name
from common.starrocks_connection import get_db_connection

logger = logging.getLogger(__name__)
router = APIRouter()

TABLE_NAME = "shared_source.magento_product_dimension_with_text_embedding"


@router.get("/api/products/overview", summary="Product catalog KPI overview")
async def products_overview():
    """Return aggregate KPIs for the product catalog."""
    db = get_db_connection()
    try:
        rows = await db.execute_query_async(f"""
            SELECT
                COUNT(DISTINCT internal_ref_code)  AS total_products,
                COUNT(DISTINCT product_line_vn)     AS total_lines,
                COUNT(DISTINCT gender_by_product)   AS total_genders,
                ROUND(MIN(sale_price))              AS min_price,
                ROUND(MAX(sale_price))              AS max_price,
                ROUND(AVG(sale_price))              AS avg_price,
                SUM(CASE WHEN sale_price < original_price AND original_price > 0 THEN 1 ELSE 0 END) AS discounted_count,
                SUM(CASE WHEN is_new_product = 1 THEN 1 ELSE 0 END) AS new_count,
                SUM(CASE WHEN quantity_sold > 0 THEN 1 ELSE 0 END)  AS selling_count,
                SUM(quantity_sold)                   AS total_sold
            FROM {TABLE_NAME}
        """)

        # Get top product lines breakdown
        lines = await db.execute_query_async(f"""
            SELECT
                product_line_vn,
                COUNT(DISTINCT internal_ref_code) AS count,
                ROUND(AVG(sale_price))            AS avg_price,
                SUM(quantity_sold)                AS total_sold
            FROM {TABLE_NAME}
            WHERE product_line_vn IS NOT NULL AND product_line_vn != ''
            GROUP BY product_line_vn
            ORDER BY count DESC
            LIMIT 15
        """)

        # Gender breakdown
        genders = await db.execute_query_async(f"""
            SELECT
                gender_by_product,
                COUNT(DISTINCT internal_ref_code) AS count
            FROM {TABLE_NAME}
            WHERE gender_by_product IS NOT NULL AND gender_by_product != ''
            GROUP BY gender_by_product
            ORDER BY count DESC
        """)

        stats = rows[0] if rows else {}

        return {
            "status": "success",
            "stats": {
                "total_products": stats.get("total_products", 0),
                "total_lines": stats.get("total_lines", 0),
                "min_price": stats.get("min_price", 0),
                "max_price": stats.get("max_price", 0),
                "avg_price": stats.get("avg_price", 0),
                "discounted_count": stats.get("discounted_count", 0),
                "new_count": stats.get("new_count", 0),
                "selling_count": stats.get("selling_count", 0),
                "total_sold": stats.get("total_sold", 0),
            },
            "product_lines": lines,
            "genders": genders,
        }
    except Exception as e:
        logger.error(f"❌ Product overview error: {e}")
        return JSONResponse(
            status_code=500,
            content={"status": "error", "message": str(e)},
        )


@router.get("/api/products/list", summary="Product listing grouped by internal_ref_code")
async def products_list(
    sort: str = Query("quantity_sold", description="Sort field"),
    order: str = Query("desc", description="Sort order: asc or desc"),
    gender: Optional[str] = Query(None, description="Filter by gender"),
    age: Optional[str] = Query(None, description="Filter by age group"),
    product_line: Optional[str] = Query(None, description="Filter by product line"),
    search: Optional[str] = Query(None, description="Search by name or code"),
    color: Optional[str] = Query(None, description="Filter by master_color (user-friendly term)"),
    is_new: Optional[bool] = Query(None, description="Filter new products"),
    has_discount: Optional[bool] = Query(None, description="Filter discounted"),
    limit: int = Query(50, ge=1, le=500),
    offset: int = Query(0, ge=0),
):
    """Return paginated product listing grouped by internal_ref_code."""
    db = get_db_connection()

    # Build WHERE clauses
    clauses = []
    params = []
    use_relevance = False  # Smart search ranking flag
    search_lower = ""
    resolved_name_lower = ""

    if gender:
        clauses.append("gender_by_product = %s")
        params.append(gender.lower())
    if age:
        clauses.append("age_by_product = %s")
        params.append(age.lower())
    if product_line:
        clauses.append("LOWER(product_line_vn) LIKE %s")
        params.append(f"%{product_line.lower()}%")
    if search:
        # Resolve synonym: "áo thun" → "Áo phông", "quần bò" → "Quần jean"
        resolved_name = resolve_product_name(search)
        search_lower = search.lower()
        resolved_name_lower = resolved_name.lower()
        use_relevance = True  # Enable relevance sorting

        # Check if search matches a product_line synonym
        matched_line = SYNONYM_TO_DB.get(search_lower)

        if matched_line and resolved_name_lower != search_lower:
            clauses.append(
                "(LOWER(product_name) LIKE %s OR LOWER(product_name) LIKE %s "
                "OR LOWER(product_line_vn) = %s "
                "OR internal_ref_code LIKE %s OR magento_ref_code LIKE %s)"
            )
            params.extend([
                f"%{search_lower}%",
                f"%{resolved_name_lower}%",
                matched_line.lower(),
                f"%{search.upper()}%",
                f"%{search.upper()}%",
            ])
            logger.info(f"🔍 Search mapped: '{search}' → '{resolved_name}' (line: {matched_line})")
        else:
            clauses.append(
                "(LOWER(product_name) LIKE %s OR internal_ref_code LIKE %s OR magento_ref_code LIKE %s)"
            )
            params.extend([f"%{search_lower}%", f"%{search.upper()}%", f"%{search.upper()}%"])
    if is_new is True:
        clauses.append("is_new_product = 1")
    if has_discount is True:
        clauses.append("sale_price < original_price AND original_price > 0")
    if color:
        db_color = resolve_color(color)
        if db_color:
            clauses.append("master_color = %s")
            params.append(db_color)
        else:
            clauses.append("LOWER(master_color) LIKE %s")
            params.append(f"%{color.lower()}%")

    where_str = " AND ".join(clauses) if clauses else "1=1"

    # ── Smart Relevance Ranking ──
    # Build a CASE-based relevance score when search is active
    if use_relevance and sort in ("quantity_sold", "relevance"):
        # Tiered scoring: exact SKU > prefix SKU > exact name > startsWith > contains > partial
        relevance_expr = f"""
            MAX(
                CASE
                    WHEN UPPER(internal_ref_code) = %s THEN 100
                    WHEN UPPER(internal_ref_code) LIKE %s THEN 90
                    WHEN LOWER(product_name) = %s THEN 85
                    WHEN LOWER(product_name) LIKE %s THEN 70
                    WHEN LOWER(product_name) LIKE %s THEN 50
                    WHEN LOWER(product_line_vn) = %s THEN 40
                    ELSE 30
                END
            )"""
        relevance_params = [
            search.upper(),                  # exact SKU
            f"{search.upper()}%",            # SKU prefix
            search_lower,                    # exact name
            f"{search_lower}%",              # name starts with
            f"%{search_lower}%",             # name contains
            search_lower,                    # exact product_line
        ]
        order_clause = f"relevance_score DESC, quantity_sold DESC"
        sort_field = "relevance"
    else:
        relevance_expr = None
        relevance_params = []
        allowed_sorts = {
            "quantity_sold", "sale_price", "original_price", "product_name",
            "discount_percent", "internal_ref_code",
        }
        if sort not in allowed_sorts:
            sort = "quantity_sold"
        order_dir = "ASC" if order.lower() == "asc" else "DESC"
        order_clause = f"{sort} {order_dir}"
        sort_field = sort

    try:
        # Get total count (grouped)
        count_rows = await db.execute_query_async(
            f"SELECT COUNT(DISTINCT internal_ref_code) AS total FROM {TABLE_NAME} WHERE {where_str}",
            params=tuple(params) if params else None,
        )
        total = count_rows[0]["total"] if count_rows else 0

        # Build SELECT with optional relevance score
        relevance_col = f",\n                {relevance_expr} AS relevance_score" if relevance_expr else ""
        all_params = params + relevance_params + [limit, offset]

        products = await db.execute_query_async(
            f"""
            SELECT
                internal_ref_code,
                ANY_VALUE(product_name)                  AS product_name,
                ANY_VALUE(product_image_url_thumbnail)    AS product_image_url_thumbnail,
                ANY_VALUE(product_web_url)                AS product_web_url,
                MIN(sale_price)                           AS sale_price,
                MAX(original_price)                       AS original_price,
                ROUND(CASE WHEN MAX(original_price) > 0
                    THEN ((MAX(original_price) - MIN(sale_price)) / MAX(original_price) * 100)
                    ELSE 0 END, 0)                        AS discount_percent,
                ANY_VALUE(age_by_product)                 AS age_by_product,
                ANY_VALUE(gender_by_product)              AS gender_by_product,
                ANY_VALUE(product_line_vn)                AS product_line_vn,
                MAX(quantity_sold)                        AS quantity_sold,
                MAX(is_new_product)                       AS is_new_product,
                ANY_VALUE(size_scale)                     AS size_scale,
                COUNT(DISTINCT product_color_code)        AS color_count,
                GROUP_CONCAT(DISTINCT product_color_code) AS color_codes,
                GROUP_CONCAT(DISTINCT master_color)       AS colors{relevance_col}
            FROM {TABLE_NAME}
            WHERE {where_str}
            GROUP BY internal_ref_code
            ORDER BY {order_clause}
            LIMIT %s OFFSET %s
            """,
            params=tuple(all_params),
        )

        return {
            "status": "success",
            "total": total,
            "limit": limit,
            "offset": offset,
            "sort": sort_field,
            "products": products,
        }
    except Exception as e:
        logger.error(f"❌ Product list error: {e}")
        return JSONResponse(
            status_code=500,
            content={"status": "error", "message": str(e)},
        )


@router.get("/api/products/colors", summary="Color variants for a product")
async def products_colors(
    code: str = Query(..., description="internal_ref_code"),
):
    """Return all color variants for a given internal_ref_code."""
    db = get_db_connection()
    try:
        variants = await db.execute_query_async(
            f"""
            SELECT
                product_color_code,
                master_color,
                product_color_name,
                product_image_url_thumbnail,
                product_web_url,
                sale_price,
                original_price,
                quantity_sold,
                size_scale
            FROM {TABLE_NAME}
            WHERE internal_ref_code = %s
            ORDER BY quantity_sold DESC
            """,
            params=(code,),
        )
        return {"status": "success", "variants": variants}
    except Exception as e:
        logger.error(f"❌ Product colors error: {e}")
        return JSONResponse(
            status_code=500,
            content={"status": "error", "message": str(e)},
        )


@router.get("/api/products/filters", summary="Available filter options")
async def products_filters():
    """Return all available filter values for color, product_line, age, season."""
    db = get_db_connection()
    try:
        colors = await db.execute_query_async(f"""
            SELECT master_color, COUNT(DISTINCT internal_ref_code) AS cnt
            FROM {TABLE_NAME}
            WHERE master_color IS NOT NULL AND master_color != ''
            GROUP BY master_color ORDER BY cnt DESC
        """)
        lines = await db.execute_query_async(f"""
            SELECT product_line_vn, COUNT(DISTINCT internal_ref_code) AS cnt
            FROM {TABLE_NAME}
            WHERE product_line_vn IS NOT NULL AND product_line_vn != ''
            GROUP BY product_line_vn ORDER BY cnt DESC
        """)
        ages = await db.execute_query_async(f"""
            SELECT age_by_product, COUNT(DISTINCT internal_ref_code) AS cnt
            FROM {TABLE_NAME}
            WHERE age_by_product IS NOT NULL AND age_by_product != ''
            GROUP BY age_by_product ORDER BY cnt DESC
        """)
        seasons = await db.execute_query_async(f"""
            SELECT season, COUNT(DISTINCT internal_ref_code) AS cnt
            FROM {TABLE_NAME}
            WHERE season IS NOT NULL AND season != ''
            GROUP BY season ORDER BY cnt DESC
        """)
        return {
            "status": "success",
            "colors": colors,
            "product_lines": lines,
            "ages": ages,
            "seasons": seasons,
        }
    except Exception as e:
        logger.error(f"❌ Product filters error: {e}")
        return JSONResponse(
            status_code=500,
            content={"status": "error", "message": str(e)},
        )


# ─────────────────────────────────────────────────────────────────
# /api/product/lookup?skus=SKU1,SKU2,...
# Dùng cho lead-popup.js: enrich popup với sizes + colors từ StarRocks
# ─────────────────────────────────────────────────────────────────
# Lead agent dùng test_db, product_route dùng shared_source — cần nhất quán
LOOKUP_TABLE = "test_db.magento_product_dimension_with_text_embedding"

@router.get("/api/product/lookup", summary="Lookup product details by SKU(s)")
async def product_lookup(
    skus: str = Query(..., description="Comma-separated SKU(s) e.g. 6PL25W058-SA014 hoặc 6PL25W058"),
):
    """
    Tra cứu thông tin sản phẩm theo SKU (magento_ref_code hoặc internal_ref_code).
    Trả về sizes, colors, price từ StarRocks — dùng cho popup chọn size.
    """
    db = get_db_connection()

    raw_codes = [s.strip().upper() for s in skus.split(",") if s.strip()]
    if not raw_codes:
        return JSONResponse(status_code=400, content={"status": "error", "message": "skus param trống"})

    # Extract internal_ref_code (phần trước dấu '-' đầu tiên, ví dụ 6PL25W058-SA014 → 6PL25W058)
    # Nếu SKU không có '-', dùng nguyên
    internal_refs = []
    for code in raw_codes:
        internal_refs.append(code.split("-")[0] if "-" in code else code)

    # Remove duplicates
    internal_refs = list(dict.fromkeys(internal_refs))

    try:
        placeholders = ", ".join(["%s"] * len(internal_refs))
        rows = await db.execute_query_async(
            f"""
            SELECT
                internal_ref_code,
                magento_ref_code,
                product_color_code,
                product_name,
                master_color,
                product_color_name,
                sale_price,
                original_price,
                size_scale,
                product_image_url_thumbnail,
                product_web_url,
                gender_by_product,
                product_line_vn
            FROM {LOOKUP_TABLE}
            WHERE UPPER(internal_ref_code) IN ({placeholders})
               OR UPPER(magento_ref_code) IN ({placeholders})
            ORDER BY internal_ref_code, sale_price
            """,
            params=tuple(internal_refs + internal_refs),
        )

        if not rows:
            return JSONResponse(
                status_code=404,
                content={"status": "not_found", "message": f"Không tìm thấy SKU: {skus}"},
            )

        # Group by internal_ref_code → build product với sizes + color variants
        family: dict[str, dict] = {}

        for r in rows:
            ref = r["internal_ref_code"]
            if ref not in family:
                family[ref] = {
                    "sku":          ref,
                    "name":         r.get("product_name") or "",
                    "price":        r.get("sale_price") or 0,
                    "original_price": r.get("original_price") or 0,
                    "image":        r.get("product_image_url_thumbnail") or "",
                    "url":          r.get("product_web_url") or "#",
                    "gender":       r.get("gender_by_product") or "",
                    "product_line": r.get("product_line_vn") or "",
                    "sizes":        [],       # parsed từ size_scale
                    "colors":       [],       # list of {sku, color, hex}
                    "variants":     [],       # raw magento variants
                }

            # Parse sizes from size_scale (format: "S|M|L|XL" or "XS|S|M|L|XL")
            size_scale = r.get("size_scale") or ""
            if size_scale and not family[ref]["sizes"]:
                family[ref]["sizes"] = [s.strip() for s in size_scale.split("|") if s.strip()]

            # Collect color variants (unique by magento_ref_code)
            color_code = r.get("product_color_code") or r.get("magento_ref_code") or ""
            if color_code not in [v["variant_sku"] for v in family[ref]["variants"]]:
                family[ref]["variants"].append({
                    "variant_sku":  color_code,
                    "color":        r.get("master_color") or r.get("product_color_name") or "",
                    "color_name":   r.get("product_color_name") or "",
                    "image":        r.get("product_image_url_thumbnail") or "",
                    "price":        r.get("sale_price") or 0,
                })
                # Build colors list for popup swatch
                hex_map = {
                    "Đen": "#1a1a1a", "Trắng": "#ffffff", "Xám": "#808080",
                    "Đỏ": "#dc2626", "Xanh Navy": "#1e3a5f", "Xanh Dương": "#2563eb",
                    "Xanh Lá": "#16a34a", "Vàng": "#ca8a04", "Cam": "#ea580c",
                    "Hồng": "#ec4899", "Tím": "#9333ea", "Nâu": "#92400e",
                    "Kem": "#fef3c7", "Be": "#d4b896",
                }
                color_name = r.get("master_color") or r.get("product_color_name") or ""
                family[ref]["colors"].append({
                    "color":     color_name,
                    "hex":       hex_map.get(color_name, "#888888"),
                    "sku":       color_code,
                })

        products = list(family.values())

        logger.info(f"🔍 /api/product/lookup: skus={skus} → {len(products)} products, {len(rows)} variants")

        return {
            "status":   "success",
            "count":    len(products),
            "products": products,
        }

    except Exception as e:
        logger.error(f"❌ Product lookup error: {e}", exc_info=True)
        return JSONResponse(
            status_code=500,
            content={"status": "error", "message": str(e)},
        )



