"""
Ultra Description Manager — API Route
Generates AI-powered product descriptions from product images + stock data.

Pipeline:
  Phase 1: Vision (Llama 4 Scout) → raw JSON 20 fields from product image
  Phase 2: Enrichment (GPT-OSS 120B) → rewrite with stylist tone + real stock data
"""

import asyncio
import json
import logging
import re
import uuid
from typing import Optional

import httpx
from fastapi import APIRouter, Query, BackgroundTasks
from fastapi.responses import JSONResponse
from pydantic import BaseModel

from common.cache import redis_cache
from common.starrocks_connection import get_db_connection
from common.ultra_desc_db import UltraDescriptionDB, DescFieldConfig

logger = logging.getLogger(__name__)
router = APIRouter(prefix="/api/product-desc", tags=["Ultra Description"])

TABLE_NAME = "test_db.magento_product_dimension_with_text_embedding"

# ═══ Groq Config — Multi-Key Round-Robin ═══
GROQ_API_KEYS = [
    "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",  # Key 1
    "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",  # Key 2
    "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",  # Key 3
    "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",
    "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",
    "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",
]
_groq_key_index = 0  # Round-robin counter

def _next_groq_key() -> str:
    """Get next Groq API key using round-robin rotation."""
    global _groq_key_index
    key = GROQ_API_KEYS[_groq_key_index % len(GROQ_API_KEYS)]
    _groq_key_index += 1
    return key

GROQ_API_KEY = GROQ_API_KEYS[0]  # Backwards compat for imports
VISION_MODEL = "meta-llama/llama-4-scout-17b-16e-instruct"
ENRICH_MODEL = "openai/gpt-oss-120b"

# ═══ Batch Tracking ═══
BATCH_STATE = {
    "is_running": False,
    "total": 0,
    "done": 0,
    "errors": 0,
    "current_code": None
}


# ═══ Prompts ═══
VISION_PROMPT = """Bạn là chuyên gia phân tích sản phẩm thời trang. Nhìn ảnh sản phẩm và trả về JSON với ĐÚNG 28 trường sau.
QUAN TRỌNG: KHÔNG đoán chất liệu nếu không chắc. KHÔNG bỏ trống trường nào. Nếu không chắc, ghi "Không xác định".

{
  "ten_san_pham": "Tên sản phẩm ngắn gọn",
  "tagline": "1 câu slogan hấp dẫn",
  "mo_ta_chinh": "3 câu mô tả: form dáng + thiết kế + tác dụng lên cơ thể",
  "chat_lieu": "Chất liệu vải (VD: Cotton 100%, Polyester pha). Ghi 'Không xác định' nếu không biết",
  "tinh_nang_vai": "Tính năng nổi bật của vải (VD: co giãn 4 chiều, kháng khuẩn, thấm hút)",
  "huong_dan_bao_quan": "Hướng dẫn giặt/bảo quản (VD: Giặt máy ≤30°C, không dùng tẩy)",
  "phong_cach": "1-3 từ khóa phong cách (VD: Smart Casual, Minimalist)",
  "tags": "5 tags phân loại, cách dấu phẩy",
  "do_tuoi": "Khoảng tuổi phù hợp (VD: 20-35)",
  "gioi_tinh": "Nam/Nữ/Unisex",
  "loi_song": "Mô tả lối sống khách hàng mục tiêu",
  "tinh_cach": "Tính cách phù hợp",
  "dip_mac": "4+ dịp mặc cụ thể, ngăn bởi dấu ·",
  "phoi_do": "3 combo phối đồ, ngăn bởi dấu |",
  "nguyen_tac_phoi_do": "Lý do và quy tắc phối đồ (VD: Áo ôm nên phối quần suông để cân đối; Vải lanh hợp không khí biển)",
  "tranh_phoi_cung": "Những item/dòng SP KHÔNG NÊN phối cùng (Negative constraints - VD: Tránh phối nỉ dày mùa hè, tránh quần jogger vì lệch phong cách)",
  "layer": "Gợi ý layering",
  "mua": "Mùa phù hợp",
  "faq_1_q": "Câu hỏi FAQ 1 (về form dáng)",
  "faq_1_a": "Trả lời FAQ 1",
  "faq_2_q": "Câu hỏi FAQ 2 (về size)",
  "faq_2_a": "Trả lời FAQ 2",
  "faq_3_q": "Câu hỏi FAQ 3 (về mix match)",
  "faq_3_a": "Trả lời FAQ 3",
  "hook_quang_cao": "1 câu hook quảng cáo gây tò mò",
  "cross_sell": "2-3 sản phẩm nên mua kèm",
  "ly_do_mua": "3 lý do nên mua, ngăn bởi dấu |",
  "luu_y_size": "Lưu ý chọn size cụ thể"
}

Trả về ĐÚNG JSON, không text thừa."""

CANIFA_PRODUCT_LINES = "Áo phông, Quần soóc, Áo nỉ, Váy liền, Quần nỉ, Tất, Áo len, Bộ mặc nhà, Bộ quần áo, Áo Polo, Chân váy, Áo nỉ có mũ, Áo Sơ mi, Quần jean, Quần dài, Áo khoác chần bông, Quần mặc nhà, Áo Body, Quần Khaki, Áo khoác gió, Áo khoác dáng ngắn, Áo kiểu, Áo len gilet, Áo khoác gilet chần bông, Áo giữ nhiệt, Áo mặc nhà, Quần lót, Pyjama, Áo ba lỗ, Áo khoác nỉ không mũ, Blazer, Cardigan, Khăn, Bộ thể thao, Áo khoác chống nắng, Quần váy, Quần lót đùi, Áo khoác, Khăn mặt, Áo khoác nỉ có mũ, Mũ, Áo khoác lông vũ, Khăn tắm, Áo lót, Quần lót tam giác, Túi xách, Quần leggings mặc nhà, Áo khoác sợi, Quần leggings, Chăn cá nhân, Mũ thể thao, Áo khoác dạ, Quần giữ nhiệt, Găng tay chống nắng, Khăn lau đầu, Áo khoác gilet, Áo hai dây, Khẩu trang, Quần culottes, Quần Body, Áo bra active"

ENRICHMENT_SYSTEM = """Bạn là Stylist cao cấp của Canifa — chuyên viết mô tả sản phẩm thời trang bằng tiếng Việt.
Phong cách: tự tin, chuyên gia, dùng từ ngữ gợi hình (hack dáng, tôn dáng, thoát dáng, che khuyết điểm).
Nếu có thông tin chất liệu từ Magento, PHẢI sử dụng chính xác. KHÔNG bịa chất liệu.
NẾU có description gốc, trích xuất chất liệu, tính năng vải, hướng dẫn giặt từ đó.
KHÔNG nói về giá cả. Luôn trả về JSON chuẩn 28 trường."""

ENRICHMENT_EXAMPLE = """{
  "ten_san_pham": "Áo Polo Nam Cổ Bẻ Tay Ngắn",
  "tagline": "Lịch lãm không cần cố gắng",
  "mo_ta_chinh": "Form regular fit vừa vặn, tôn dáng mà không gò bó. Thiết kế cổ bẻ tối giản nhưng lịch sự, phù hợp cả office lẫn weekend. Phom vai suông giúp che nhẹ bắp tay, hack dáng thanh thoát hơn.",
  "chat_lieu": "Cotton USA 100%",
  "tinh_nang_vai": "Co giãn nhẹ, thấm hút mồ hôi tốt, giữ form sau nhiều lần giặt",
  "huong_dan_bao_quan": "Giặt máy ≤30°C, lộn trái, không dùng tẩy, phơi trong bóng râm",
  "phong_cach": "Smart Casual, Minimalist",
  "tags": "polo, nam, công sở, smart casual, tay ngắn, cotton usa",
  "do_tuoi": "25-40",
  "gioi_tinh": "Nam",
  "loi_song": "Người đàn ông hiện đại, coi trọng phong cách nhưng không cầu kỳ",
  "tinh_cach": "Tự tin, chỉn chu, thực dụng",
  "dip_mac": "Đi làm hàng ngày · Cafe cuối tuần · Gặp đối tác · Đi chơi cùng gia đình",
  "phoi_do": "Quần Chinos Beige + Giày Loafer Da → Thanh lịch cuối tuần | Quần Jean Slim Xanh Đậm + Sneaker Trắng → Năng động công sở | Quần Short Khaki + Slide Da → Relax ngày hè",
  "nguyen_tac_phoi_do": "Polo form regular có phần cổ bẻ lịch sự, nguyên tắc phối là mix CÙNG ĐỘ FIT (quần dáng slim hoặc straight) ở thân dưới. Quần quá rộng sẽ làm mất đi độ thanh lịch của áo.",
  "tranh_phoi_cung": "Tránh phối cùng quần jogger nỉ quá thể thao hoặc quần đùi chạy bộ vì sẽ lệch pha phong cách hoàn toàn.",
  "layer": "Mặc đơn mùa hè. Layer cùng Áo Khoác Bomber hoặc Blazer mỏng cho mùa thu.",
  "mua": "Xuân Hè",
  "faq_1_q": "Áo này có hack dáng không?",
  "faq_1_a": "Phom regular fit vai suông giúp che nhẹ bắp tay, tạo cảm giác thanh thoát hơn — đặc biệt hợp với anh em có phần trên hơi đầy.",
  "faq_2_q": "Cao 1m70 nặng 65kg nên mặc size gì?",
  "faq_2_a": "Nên chọn size M. Nếu thích mặc hơi rộng thoải mái thì lên L — nhưng M sẽ chuẩn form nhất.",
  "faq_3_q": "Phối áo này với quần gì đẹp nhất?",
  "faq_3_a": "Combo kinh điển: Chinos hoặc Jean slim + Loafer. Muốn casual hơn: Short khaki + Sneaker trắng.",
  "hook_quang_cao": "Chiếc polo mà anh em mặc đi làm rồi đi cafe luôn — không cần thay đồ",
  "cross_sell": "Quần Chinos Slim, Giày Loafer Da, Thắt Lưng Da Reversible",
  "ly_do_mua": "Form hack dáng thanh thoát | Mặc đi làm hay đi chơi đều ổn | Cổ bẻ lịch sự không cần phối nhiều",
  "luu_y_size": "Nếu nặng trên 75kg nên lên 1 size. Form regular nên mặc đúng size sẽ vừa nhất."
}"""


# ═══ Helpers ═══

# Label mapping for rendering JSON → readable text
_FIELD_LABELS = {
    "ten_san_pham": "Tên sản phẩm",
    "tagline": "Tagline",
    "mo_ta_chinh": "Mô tả chính",
    "chat_lieu": "Chất liệu",
    "tinh_nang_vai": "Tính năng vải",
    "huong_dan_bao_quan": "Hướng dẫn bảo quản",
    "phong_cach": "Phong cách",
    "tags": "Tags",
    "do_tuoi": "Độ tuổi",
    "gioi_tinh": "Giới tính",
    "loi_song": "Lối sống",
    "tinh_cach": "Tính cách",
    "dip_mac": "Dịp mặc",
    "phoi_do": "Gợi ý phối đồ",
    "nguyen_tac_phoi_do": "Nguyên tắc phối đồ",
    "tranh_phoi_cung": "Tránh phối cùng",
    "layer": "Gợi ý layering",
    "mua": "Mùa phù hợp",
    "faq_1_q": "FAQ 1 - Câu hỏi",
    "faq_1_a": "FAQ 1 - Trả lời",
    "faq_2_q": "FAQ 2 - Câu hỏi",
    "faq_2_a": "FAQ 2 - Trả lời",
    "faq_3_q": "FAQ 3 - Câu hỏi",
    "faq_3_a": "FAQ 3 - Trả lời",
    "hook_quang_cao": "Hook quảng cáo",
    "cross_sell": "Cross-sell",
    "ly_do_mua": "Lý do nên mua",
    "luu_y_size": "Lưu ý chọn size",
}

# Section grouping for rendered output
_SECTIONS = [
    ("📦 THÔNG TIN CƠ BẢN", ["ten_san_pham", "tagline", "mo_ta_chinh", "phong_cach", "tags"]),
    ("🧵 CHẤT LIỆU & BẢO QUẢN", ["chat_lieu", "tinh_nang_vai", "huong_dan_bao_quan"]),
    ("🎯 ĐỐI TƯỢNG", ["do_tuoi", "gioi_tinh", "loi_song", "tinh_cach"]),
    ("📅 DỊP MẶC & STYLING", ["dip_mac", "phoi_do", "nguyen_tac_phoi_do", "tranh_phoi_cung", "layer", "mua"]),
    ("❓ FAQ", ["faq_1_q", "faq_1_a", "faq_2_q", "faq_2_a", "faq_3_q", "faq_3_a"]),
    ("🛒 HỖ TRỢ BÁN HÀNG", ["hook_quang_cao", "cross_sell", "ly_do_mua", "luu_y_size"]),
]


def _render_description_text(data: dict, product_name: str = "") -> str:
    """Render JSON description_data into a single formatted text block."""
    if not data:
        data = {}

    lines = []
    title = data.get("ten_san_pham", product_name or "Sản phẩm")
    lines.append(f"{'=' * 50}")
    lines.append(f"  {title.upper()}")
    lines.append(f"{'=' * 50}")
    lines.append("")

    for section_title, fields in _SECTIONS:
        section_lines = []
        for key in fields:
            val = data.get(key, "")
            if not val:
                continue
            label = _FIELD_LABELS.get(key, key)

            # FAQ: group Q+A together
            if key.endswith("_q"):
                section_lines.append(f"  💬 {val}")
                continue
            elif key.endswith("_a"):
                section_lines.append(f"     → {val}")
                continue

            # Phối đồ: format combos nicely
            if key == "phoi_do" and "|" in str(val):
                section_lines.append(f"  {label}:")
                for combo in str(val).split("|"):
                    section_lines.append(f"    • {combo.strip()}")
                continue

            # Lý do mua: format with bullets
            if key == "ly_do_mua" and "|" in str(val):
                section_lines.append(f"  {label}:")
                for reason in str(val).split("|"):
                    section_lines.append(f"    • {reason.strip()}")
                continue

            # Dịp mặc: keep · separator
            section_lines.append(f"  {label}: {val}")

        if section_lines:
            lines.append(f"── {section_title} ──")
            lines.extend(section_lines)
            lines.append("")
        else:
            lines.append(f"── {section_title} ──")
            lines.append("  Chưa có dữ liệu.")
            lines.append("")

    return "\n".join(lines)

def _extract_json(raw_text: str | list | dict) -> dict | None:
    """Parse JSON safely from LLM output."""
    if not raw_text:
        return None

    # Handle list response (e.g., from Langchain Gemini multimodal fallback)
    if isinstance(raw_text, list):
        text_parts = []
        for part in raw_text:
            if isinstance(part, dict) and "text" in part:
                text_parts.append(part["text"])
            elif isinstance(part, str):
                text_parts.append(part)
        raw_text = "".join(text_parts)

    # If it's a dict somehow, just return it directly or stringify
    if isinstance(raw_text, dict):
        return raw_text

    if not isinstance(raw_text, str):
        raw_text = str(raw_text)

    # Try ```json ... ``` block
    if "```" in raw_text:
        match = re.search(r'```(?:json)?\s*([\s\S]*?)```', raw_text)
        if match:
            try:
                return json.loads(match.group(1).strip())
            except json.JSONDecodeError:
                pass
    # Try { ... } directly
    match = re.search(r'\{[\s\S]*\}', raw_text)
    if match:
        try:
            return json.loads(match.group(0))
        except json.JSONDecodeError:
            pass
    try:
        return json.loads(raw_text.strip())
    except json.JSONDecodeError:
        return None


def _is_groq_model(model_name: str) -> bool:
    """Groq models: openai/gpt-oss-*, meta-llama/*, mixtral/*, etc."""
    m = model_name.lower()
    return any(kw in m for kw in ("gpt-oss", "llama", "mixtral", "gemma", "qwen", "deepseek")) or m.startswith("openai/")


async def _call_ai_with_fallback(messages: list, model: str, max_tokens: int = 3000, temperature: float = 0.7, is_vision: bool = False) -> str:
    """
    Smart Groq-only pipeline with multi-key round-robin rotation.
    3 API keys = 3x throughput. Each model tries all keys before giving up.
    """

    # ── Groq httpx (fast, free, multi-key) ──
    if _is_groq_model(model):
        if is_vision:
            groq_chain = [model]
        else:
            groq_chain = [model]
            for backup in ["openai/gpt-oss-120b", "openai/gpt-oss-20b", "qwen/qwen3-32b"]:
                if backup != model and backup not in groq_chain:
                    groq_chain.append(backup)

        while True:
            for groq_model in groq_chain:
                # Try each key (round-robin) — if one key is rate-limited, try next key instantly
                for key_attempt in range(len(GROQ_API_KEYS)):
                    api_key = _next_groq_key()
                    key_tag = f"K{(_groq_key_index - 1) % len(GROQ_API_KEYS) + 1}"
                    try:
                        async with httpx.AsyncClient(timeout=30) as client:
                            resp = await client.post(
                                "https://api.groq.com/openai/v1/chat/completions",
                                headers={
                                    "Authorization": f"Bearer {api_key}",
                                    "Content-Type": "application/json",
                                },
                                json={
                                    "model": groq_model,
                                    "max_tokens": max_tokens,
                                    "temperature": temperature,
                                    "messages": messages,
                                },
                            )

                            if resp.status_code == 200:
                                result = resp.json()
                                if "error" not in result:
                                    logger.info(f"🏆 Groq [{key_tag}] {groq_model}")
                                    return result["choices"][0]["message"]["content"]
                                logger.warning(f"⚠️ Groq [{key_tag}] {groq_model} API Error: {result['error']}")
                                break  # Go to next model
                            elif resp.status_code == 429:
                                logger.warning(f"⚠️ Groq [{key_tag}] {groq_model} Rate limit → rotating key...")
                                continue  # Try next key instantly!
                            elif resp.status_code >= 500:
                                logger.warning(f"⚠️ Groq [{key_tag}] {groq_model} Server Error {resp.status_code}: {resp.text[:100]}...")
                                continue
                            else:
                                logger.warning(f"⚠️ Groq [{key_tag}] {groq_model} HTTP {resp.status_code}: {resp.text[:200]}")
                                break # Other errors like 400 Bad request -> go to next model

                    except Exception as e:
                        logger.warning(f"⚠️ Groq [{key_tag}] {groq_model} Exception: {e}")
                        break
                else:
                    # All keys exhausted for this model — wait a bit then try next model
                    logger.warning(f"⚠️ All {len(GROQ_API_KEYS)} keys exhausted for {groq_model} → waiting 4s...")
                    await asyncio.sleep(4)
                    continue

            logger.warning("🔄 Toàn bộ model và key đã bị giới hạn, tiếp tục vòng mới sau 5s...")
            await asyncio.sleep(5)


async def _fetch_image_base64(image_url: str) -> str:
    """Download image and convert to base64."""
    import base64
    async with httpx.AsyncClient(timeout=15) as client:
        resp = await client.get(image_url, headers={"User-Agent": "Mozilla/5.0"})
        resp.raise_for_status()
        return base64.b64encode(resp.content).decode("utf-8")


# ═══ 1. OVERVIEW — Stats on descriptions ═══
@router.get("/overview", summary="Overview stats: total products, how many have descriptions")
async def overview():
    """Return count of products with/without ultra descriptions (from Postgres)."""
    db = get_db_connection()
    try:
        # Total = distinct magento_ref_code from StarRocks (per color variant, same basis as Postgres)
        rows = await db.execute_query_async(f"""
            SELECT COUNT(DISTINCT magento_ref_code) AS total
            FROM {TABLE_NAME}
        """)
        total = rows[0]["total"] if rows else 0

        # Saved descriptions from Postgres (source of truth — per magento_ref_code)
        pg_stats = UltraDescriptionDB.get_stats()
        has_desc = pg_stats.get("total", 0)       # rows in Postgres
        approved = pg_stats.get("approved", 0)
        pending = pg_stats.get("pending", 0)

        # Total = StarRocks distinct magento_ref_code (what can still be generated)
        # Clamp missing to 0 (Postgres may have more rows than current SR catalog if old products were removed)
        missing = max(0, total - has_desc)

        missing_clean_desc = UltraDescriptionDB.count_missing_clean_descriptions()
        missing_tags = UltraDescriptionDB.count_missing_tags()

        # Count products with size guide
        has_size_guide = 0
        try:
            from common.pool_wrapper import get_pooled_connection_compat
            pg_conn = get_pooled_connection_compat()
            pg_cur = pg_conn.cursor()
            pg_cur.execute("SELECT COUNT(*) FROM dashboard_canifa.ultra_descriptions WHERE description_data->>'huong_dan_size' IS NOT NULL AND description_data->>'huong_dan_size' != ''")
            has_size_guide = pg_cur.fetchone()[0]
            pg_cur.close()
            pg_conn.close()
        except Exception as sg_err:
            logger.warning(f"Size guide count error: {sg_err}")

        # Use has_desc (Postgres) as the "total displayed" so UI is consistent
        display_total = has_desc

        return {
            "status": "success",
            "total": display_total,          # Postgres total (magento_ref_code per color)
            "total_sr": total,               # StarRocks total (for reference)
            "has_desc": has_desc,
            "approved": approved,
            "pending": pending,
            "missing": missing,
            "has_clean_desc": pg_stats.get("has_clean_desc", 0),
            "missing_clean_desc": missing_clean_desc,
            "missing_tags": missing_tags,
            "has_size_guide": has_size_guide,
            "progress": round(approved / display_total * 100, 1) if display_total > 0 else 0,
            "db_stats": {
                "enriched": pg_stats.get("enriched", 0),
                "raw_only": pg_stats.get("raw_only", 0),
                "last_updated": str(pg_stats.get("last_updated", "")) if pg_stats.get("last_updated") else None,
            },
        }
    except Exception as e:
        logger.error(f"Overview error: {e}")
        return JSONResponse(status_code=500, content={"status": "error", "message": str(e)})


@router.get("/batch-status", summary="Get progress of current batch generation")
async def batch_status():
    """Return the progress of the background batch task."""
    return BATCH_STATE

# ═══ 2. LIST — Products with description status ═══
@router.get("/list", summary="List products with description status")
async def product_desc_list(
    status: Optional[str] = Query(None, description="Filter: 'missing' or 'has_desc'"),
    search: Optional[str] = Query(None, description="Search by name or code"),
    product_line: Optional[str] = Query(None, description="Filter by product line"),
    tag: Optional[str] = Query(None, description="Filter by specific tag in DB"),
    limit: int = Query(30, ge=1, le=200),
    page: int = Query(1, ge=1),
):
    """List products with their description status — grouped by magento_ref_code (per color variant)."""
    db = get_db_connection()
    offset = (page - 1) * limit
    clauses = []
    params = []

    # Tag filter — uses magento_ref_code as key now
    if tag:
        valid_codes = UltraDescriptionDB.get_codes_by_tag(tag.strip())
        if not valid_codes:
            return {"status": "success", "total": 0, "limit": limit, "page": page, "products": []}
        codes_list = list(valid_codes)
        phs = ", ".join([f"'{c}'" for c in codes_list])
        clauses.append(f"magento_ref_code IN ({phs})")

    if search:
        s = search.strip()
        clauses.append("(LOWER(product_name) LIKE %s OR magento_ref_code LIKE %s OR internal_ref_code LIKE %s)")
        params.extend([f"%{s.lower()}%", f"%{s.upper()}%", f"%{s.upper()}%"])
    if product_line:
        clauses.append("LOWER(product_line_vn) LIKE %s")
        params.append(f"%{product_line.lower()}%")

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

    try:
        # Status map: {magento_ref_code: status}
        code_status_map = UltraDescriptionDB.get_all_codes_with_status()

        # Count distinct magento_ref_code
        count_rows = await db.execute_query_async(
            f"SELECT COUNT(DISTINCT magento_ref_code) AS total FROM {TABLE_NAME} WHERE {where}",
            params=tuple(params) if params else None,
        )
        total = count_rows[0]["total"] if count_rows else 0

        # Query per magento_ref_code (1 row per color variant)
        products = await db.execute_query_async(
            f"""
            SELECT
                magento_ref_code,
                internal_ref_code,
                ANY_VALUE(product_name) AS product_name,
                ANY_VALUE(product_image_url_thumbnail) AS product_image_url_thumbnail,
                ANY_VALUE(product_line_vn) AS product_line_vn,
                ANY_VALUE(gender_by_product) AS gender_by_product,
                ANY_VALUE(master_color) AS master_color,
                ANY_VALUE(product_color_code) AS product_color_code,
                MIN(sale_price) AS sale_price,
                MAX(original_price) AS original_price,
                MAX(quantity_sold) AS quantity_sold
            FROM {TABLE_NAME}
            WHERE {where}
            GROUP BY magento_ref_code, internal_ref_code
            ORDER BY quantity_sold DESC
            LIMIT %s OFFSET %s
            """,
            params=tuple(params + [limit, offset]),
        )

        # Enrich with desc status from Postgres
        for p in products:
            magento_code = p.get("magento_ref_code") or p.get("internal_ref_code")
            if magento_code in code_status_map:
                p["has_desc"] = 1
                p["desc_status"] = code_status_map[magento_code]  # 0 = pending, 1 = approved
                row = UltraDescriptionDB.get_by_magento_code(magento_code)
                if row:
                    p["tags"] = row.get("tags") or []
                    dd = row.get("description_data", {})
                    if isinstance(dd, str):
                        try: dd = json.loads(dd)
                        except: dd = {}
                    p["has_size_guide"] = 1 if dd.get("huong_dan_size") else 0
                    ua = row.get("updated_at")
                    p["updated_at"] = ua.isoformat() if hasattr(ua, "isoformat") else str(ua) if ua else None
                else:
                    p["tags"] = []; p["has_size_guide"] = 0; p["updated_at"] = None
            else:
                p["has_desc"] = 0
                p["desc_status"] = -1
                p["tags"] = []; p["has_size_guide"] = 0; p["updated_at"] = None
            # Check ai_matches
            # 0 = chưa chạy bao giờ, 1 = có SP phối, 2 = engine đã chạy nhưng catalog màu chưa đủ
            if magento_code in code_status_map:
                row_full = UltraDescriptionDB.get_by_magento_code(magento_code)
                ai_m = (row_full or {}).get("ai_matches")  # None = chưa chạy
                if ai_m is None:
                    p["has_ai_matches"] = 0  # chưa chạy engine lần nào
                else:
                    if isinstance(ai_m, str):
                        try: ai_m = json.loads(ai_m)
                        except: ai_m = {}
                    # Check xem có occasion nào có items không
                    has_any_item = any(
                        isinstance(v, dict) and any(
                            isinstance(items, list) and len(items) > 0
                            for items in v.values()
                        )
                        for v in ai_m.values()
                    ) if isinstance(ai_m, dict) else False
                    p["has_ai_matches"] = 1 if has_any_item else 2  # 2 = đã chạy, catalog rỗng
            else:
                p["has_ai_matches"] = 0

        # Post-filter by status
        if status == "missing":
            products = [p for p in products if p["desc_status"] == -1]
        elif status == "has_desc":
            products = [p for p in products if p["desc_status"] >= 0]
        elif status == "pending":
            products = [p for p in products if p["desc_status"] == 0]
        elif status == "approved":
            products = [p for p in products if p["desc_status"] == 1]

        # Restore original field names for backward-compat with product-desc.js
        # (renderTable uses magento_ref_code, product_name, product_image_url_thumbnail, etc.)
        # Just add has_ai_matches as an extra field, keep everything else as-is.
        for p in products:
            p.setdefault("has_ai_matches", 0)

        return {
            "status":   "success",
            "total":    total,
            "limit":    limit,
            "page":     page,
            "products": products,         # used by product-desc.js (old field names)
            "items": [                    # used by fashion-matches/app.js (new field names)
                {
                    "code":           p.get("magento_ref_code") or p.get("internal_ref_code"),
                    "name":           p.get("product_name") or "",
                    "image_url":      p.get("product_image_url_thumbnail") or "",
                    "product_line":   p.get("product_line_vn") or "",
                    "color":          p.get("master_color") or "",
                    "gender":         p.get("gender_by_product") or "",
                    "has_desc":       p.get("has_desc", 0),
                    "has_ai_matches": p.get("has_ai_matches", 0),
                    "desc_status":    p.get("desc_status", -1),
                    "updated_at":     p.get("updated_at"),
                }
                for p in products
            ],
        }
    except Exception as e:
        logger.error(f"Product desc list error: {e}")
        return JSONResponse(status_code=500, content={"status": "error", "message": str(e)})



# ═══ 3. GENERATE — Generate description for a single product ═══
class GenerateRequest(BaseModel):
    # Accept either field — frontend may send magento_ref_code (with color suffix)
    internal_ref_code: str | None = None
    magento_ref_code: str | None = None

    def resolve_code(self) -> str:
        """Get the base internal_ref_code, stripping color suffix from magento_ref_code if needed."""
        code = self.internal_ref_code or self.magento_ref_code or ""
        # Strip color suffix: '5TS25S021-SR079' -> '5TS25S021'
        if "-" in code:
            base = code.split("-")[0]
            return base
        return code

    def get_magento_code(self) -> str | None:
        """Return magento_ref_code as-is (with color suffix) for exact lookup."""
        return self.magento_ref_code or self.internal_ref_code

@router.post("/generate", summary="Generate ultra description for a product by code")
async def generate_description(req: GenerateRequest):
    """
    Grounded Generation Pipeline (v2):
    1. Fetch DB ground truth (product_name, gender, age, description_text)
    2. Vision → detect design/motif (anchored by product_name — no hallucination)
    3. Writer → 28-field JSON grounded by DB data
    4. Reviewer → self-critique & fix
    5. Save to PostgreSQL
    """
    from importlib import import_module
    _ultra_agent = import_module("agent.ultra_descriptions_agent.agent")
    run_ultra_description_agent = _ultra_agent.run_ultra_description_agent

    base_code = req.resolve_code()
    magento_code = req.get_magento_code()

    logger.info("[GENERATE] 🚀 New grounded agent: base=%s magento=%s", base_code, magento_code)

    try:
        result = await run_ultra_description_agent(
            internal_ref_code=base_code,
            magento_ref_code=magento_code,
        )
        if result["status"] == "error":
            status_code = 404 if "Không tìm thấy" in result.get("message", "") else 500
            return JSONResponse(status_code=status_code, content=result)

        return {
            "status": "success",
            "phase": "grounded_v2",
            "product": {
                "product_name": result.get("product_name"),
                "internal_ref_code": base_code,
            },
            "data": result.get("description_data", {}),
            "saved": True,
            "elapsed_s": result.get("elapsed_s"),
        }

    except Exception as e:
        logger.error("[GENERATE] ❌ Error: %s", e, exc_info=True)
        return JSONResponse(status_code=500, content={"status": "error", "message": str(e)})



# ═══ 4. PRODUCT LINE FILTERS ═══
@router.get("/filters", summary="Available product lines for filtering")
async def desc_filters():
    db = get_db_connection()
    try:
        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
        """)
        return {"status": "success", "product_lines": lines}
    except Exception as e:
        return JSONResponse(status_code=500, content={"status": "error", "message": str(e)})


# ═══ 5. GET SAVED — Retrieve saved description from Postgres ═══
@router.get("/saved/{internal_ref_code}", summary="Get saved ultra description")
async def get_saved_desc(internal_ref_code: str):
    """Retrieve a previously saved ultra description from Postgres."""
    try:
        row = UltraDescriptionDB.get_by_code(internal_ref_code)
        if not row:
            return JSONResponse(status_code=404, content={
                "status": "not_found",
                "message": f"Chưa có ultra description cho {internal_ref_code}",
            })
        # Convert datetime to string for JSON serialization
        result = {}
        for k, v in row.items():
            if hasattr(v, 'isoformat'):
                result[k] = v.isoformat()
            else:
                result[k] = v

        # Prefer the persisted clean_description from DB.
        # Fallback to generated text only when that column is empty.
        desc_data = result.get("description_data", {})
        if isinstance(desc_data, str):
            import json as _json
            try:
                desc_data = _json.loads(desc_data)
            except Exception:
                desc_data = {}
        result["rendered_text"] = result.get("clean_description") or _render_description_text(
            desc_data,
            result.get("product_name", ""),
        )

        return {"status": "success", "description": result}
    except Exception as e:
        logger.error(f"Get saved desc error: {e}")
        return JSONResponse(status_code=500, content={"status": "error", "message": str(e)})

class UpdateDescRequest(BaseModel):
    description_data: dict

@router.post("/saved/{internal_ref_code}/update", summary="Manually update saved description fields")
async def update_saved_desc(internal_ref_code: str, req: UpdateDescRequest):
    """Update description_data manually from UI and re-render clean_description."""
    logger.info("[UPDATE] Received for code=%s | fields=%s", internal_ref_code, list(req.description_data.keys()))
    try:
        row = UltraDescriptionDB.get_by_code(internal_ref_code)
        if not row:
            logger.warning("[UPDATE] Not found: %s", internal_ref_code)
            return JSONResponse(status_code=404, content={"status": "error", "message": "Not found"})

        # Log changed values
        old_data = row.get("description_data") or {}
        if isinstance(old_data, str):
            try: old_data = json.loads(old_data)
            except: old_data = {}
        for k, v in req.description_data.items():
            old_v = old_data.get(k, "")
            if str(old_v) != str(v):
                logger.info("[UPDATE] CHANGED [%s]: '%s' → '%s'", k, str(old_v)[:60], str(v)[:60])

        # Render clean formatting
        clean_desc = _render_description_text(req.description_data, row.get("product_name", ""))

        # Update DB
        updated = UltraDescriptionDB.update_data(internal_ref_code, req.description_data, clean_desc)
        if updated:
            logger.info("[UPDATE] ✅ Saved successfully: %s", internal_ref_code)
            return {"status": "success", "message": "Đã cập nhật nội dung", "clean_description": clean_desc}
        logger.error("[UPDATE] DB update returned False for: %s", internal_ref_code)
        return JSONResponse(status_code=500, content={"status": "error", "message": "Failed to update DB"})
    except Exception as e:
        logger.error("[UPDATE] Error for %s: %s", internal_ref_code, e, exc_info=True)
        return JSONResponse(status_code=500, content={"status": "error", "message": str(e)})


class UpdateTextRequest(BaseModel):
    clean_description: str

@router.post("/saved/{internal_ref_code}/update-text", summary="Manually update rendered text from popup")
async def update_saved_desc_text(internal_ref_code: str, req: UpdateTextRequest):
    """Save the manually edited HTML/text from the popup back to the database."""
    try:
        updated = UltraDescriptionDB.update_clean_text(internal_ref_code, req.clean_description)
        if updated:
            return {"status": "success", "message": "Đã lưu nội dung text"}
        return JSONResponse(status_code=500, content={"status": "error", "message": "Failed to update DB"})
    except Exception as e:
        logger.error(f"Update text error: {e}")
        return JSONResponse(status_code=500, content={"status": "error", "message": str(e)})



class RewriteFieldRequest(BaseModel):
    field_key: str

@router.post("/saved/{internal_ref_code}/rewrite-field", summary="Rewrite a specific field using AI")
async def rewrite_field_ai(internal_ref_code: str, req: RewriteFieldRequest):
    """Use AI to rewrite a single specific field in the description JSON."""
    try:
        from llm.api_factory import get_llm
        row = UltraDescriptionDB.get_by_code(internal_ref_code)
        if not row:
            return JSONResponse(status_code=404, content={"status": "error", "message": "Not found"})

        desc_data = row.get("description_data", {})
        if isinstance(desc_data, str):
            import json
            desc_data = json.loads(desc_data)

        old_value = desc_data.get(req.field_key, "")

        # Get instructions for this field
        fields = DescFieldConfig.get_active_fields()
        field_instr = ""
        for f in fields:
            if f["field_key"] == req.field_key:
                field_instr = f["field_instruction"]
                break

        prompt = f"""
Bạn là một chuyên gia sáng tạo nội dung thời trang của Canifa.
Nhiệm vụ của bạn là VIẾT LẠI MỘT trường thông tin duy nhất cho chuẩn, hay và tự nhiên hơn.

- Trường cần viết lại: {req.field_key}
- Định hướng (NẾU CÓ): {field_instr}

[Nội dung cũ của trường]:
{old_value}

Yêu cầu:
- Viết lại nội dung cho hấp dẫn hơn, đúng văn phong chuyên nghiệp.
- KHÔNG tạo ra thêm các định dạng markdown gạch đầu dòng nếu nội dung cũ không có.
- CHỈ TRẢ VỀ NỘI DUNG MỚI. KHÔNG GIẢI THÍCH, KHÔNG CHAT.
"""
        llm = get_llm(temperature=0.7)
        resp = await llm.ainvoke(prompt)
        new_text = resp.content.strip()
        new_text = new_text.replace("```json", "").replace("```html", "").replace("```", "").strip()

        # update JSON
        desc_data[req.field_key] = new_text

        # Update clean desc
        clean_desc = _render_description_text(desc_data, row.get("product_name", ""))

        updated = UltraDescriptionDB.update_data(internal_ref_code, desc_data, clean_desc)
        if updated:
            return {"status": "success", "new_value": new_text, "clean_description": clean_desc}
        return JSONResponse(status_code=500, content={"status": "error", "message": "Failed to update DB"})
    except Exception as e:
        logger.error(f"Rewrite error: {e}")
        return JSONResponse(status_code=500, content={"status": "error", "message": str(e)})


# ═══ 6. APPROVE / REJECT ═══
class StatusRequest(BaseModel):
    internal_ref_code: str | None = None
    magento_ref_code: str | None = None
    code: str | None = None           # legacy alias
    status: int  # 0 = pending, 1 = approved

    def resolve_base_code(self) -> str:
        """Strip color suffix, return base internal_ref_code."""
        raw = self.internal_ref_code or self.magento_ref_code or self.code or ""
        return raw.split("-")[0] if "-" in raw else raw

    def resolve_magento_code(self) -> str:
        """Full magento_ref_code with suffix."""
        return self.magento_ref_code or self.internal_ref_code or self.code or ""


@router.post("/approve", summary="Approve or reject a description")
async def approve_desc(req: StatusRequest):
    """Set status: 0 = chờ duyệt, 1 = đã duyệt. Khi duyệt → tự render clean_description."""
    if req.status not in (0, 1):
        return JSONResponse(status_code=400, content={"status": "error", "message": "Status phải là 0 hoặc 1"})
    try:
        base_code = req.resolve_base_code()
        magento_code = req.resolve_magento_code()

        clean_desc = ""
        if req.status == 1:
            # Try magento_ref_code first, fallback to internal_ref_code
            row = UltraDescriptionDB.get_by_code(magento_code) or UltraDescriptionDB.get_by_code(base_code)
            if row:
                clean_desc = (row.get("clean_description") or "").strip()
                if not clean_desc:
                    desc_data = row.get("description_data", {})
                    if isinstance(desc_data, str):
                        try:
                            desc_data = json.loads(desc_data)
                        except Exception:
                            desc_data = {}
                    clean_desc = _render_description_text(desc_data, row.get("product_name", ""))

        # Try magento_ref_code first, then base internal_ref_code
        updated = UltraDescriptionDB.set_status(magento_code, req.status, clean_description=clean_desc)
        if not updated:
            updated = UltraDescriptionDB.set_status(base_code, req.status, clean_description=clean_desc)

        if updated:
            label = "Đã duyệt" if req.status == 1 else "Hủy duyệt"
            return {"status": "success", "message": f"{label}: {magento_code}"}
        return JSONResponse(status_code=404, content={
            "status": "not_found",
            "message": f"Không tìm thấy description cho {magento_code}",
        })
    except Exception as e:
        return JSONResponse(status_code=500, content={"status": "error", "message": str(e)})


# ═══ 6b. SYNC MATERIAL (patch chat_lieu from StarRocks) ═══
def _extract_material(description_text: str) -> str:
    """Extract chất liệu từ Magento description_text."""
    if not description_text:
        return ""
    patterns = [
        r'(?:chất liệu|chất lieau|material|vải)[:\s]+([^\n\.;]{5,100})',
        r'(\d+%\s*(?:cotton|polyester|spandex|modal|tencel|linen|bamboo|viscose|nylon)[^\n\.;]{0,80})',
        r'(?:cotton|polyester|spandex|modal|tencel)[^\n\.;]{0,80}',
    ]
    for pat in patterns:
        m = re.search(pat, description_text, re.IGNORECASE)
        if m:
            material = m.group(1) if m.lastindex else m.group(0)
            return material.strip()[:120]
    return ""


class SyncMaterialRequest(BaseModel):
    magento_ref_code: Optional[str] = None
    internal_ref_code: Optional[str] = None


@router.post("/sync-material", summary="Patch chat_lieu from StarRocks for existing record")
async def sync_material(req: SyncMaterialRequest):
    """
    Quick patch — không cần Sinh lại:
    1. Lấy description_text từ StarRocks
    2. Extract chất liệu
    3. Update description_data.chat_lieu trong Postgres
    """
    db = get_db_connection()
    raw = req.magento_ref_code or req.internal_ref_code or ""
    base_code = raw.split("-")[0] if "-" in raw else raw
    magento_code = req.magento_ref_code or req.internal_ref_code or ""

    # 1. StarRocks: get description_text
    rows = await db.execute_query_async(
        f"SELECT ANY_VALUE(description_text) AS dt FROM {TABLE_NAME} WHERE internal_ref_code = %s GROUP BY internal_ref_code",
        params=(base_code,),
    )
    if not rows and magento_code != base_code:
        rows = await db.execute_query_async(
            f"SELECT ANY_VALUE(description_text) AS dt FROM {TABLE_NAME} WHERE magento_ref_code = %s GROUP BY internal_ref_code",
            params=(magento_code,),
        )
    if not rows:
        return JSONResponse(status_code=404, content={"status": "error", "message": f"Không tìm thấy trong StarRocks: {base_code}"})

    mat = _extract_material(rows[0].get("dt") or "")
    if not mat:
        return JSONResponse(status_code=422, content={"status": "error", "message": "Không extract được chất liệu từ Magento description"})

    # 2. Postgres: fetch & patch
    pg_row = UltraDescriptionDB.get_by_code(magento_code) or UltraDescriptionDB.get_by_code(base_code)
    if not pg_row:
        return JSONResponse(status_code=404, content={"status": "error", "message": f"Không tìm thấy trong Postgres: {magento_code}"})

    desc_data = pg_row.get("description_data") or {}
    if isinstance(desc_data, str):
        try:
            desc_data = json.loads(desc_data)
        except Exception:
            desc_data = {}

    old_mat = desc_data.get("chat_lieu", "")
    desc_data["chat_lieu"] = mat
    clean_desc = _render_description_text(desc_data, pg_row.get("product_name", ""))

    # 3. Write back
    try:
        from common.pool_wrapper import get_pooled_connection_compat
        conn = get_pooled_connection_compat()
        cur = conn.cursor()
        lookup_code = pg_row.get("magento_ref_code") or magento_code
        cur.execute(
            "UPDATE dashboard_canifa.ultra_descriptions SET description_data = %s::jsonb, clean_description = %s, updated_at = NOW() WHERE magento_ref_code = %s RETURNING id",
            (json.dumps(desc_data, ensure_ascii=False), clean_desc, lookup_code),
        )
        row = cur.fetchone()
        if not row:
            cur.execute(
                "UPDATE dashboard_canifa.ultra_descriptions SET description_data = %s::jsonb, clean_description = %s, updated_at = NOW() WHERE internal_ref_code = %s RETURNING id",
                (json.dumps(desc_data, ensure_ascii=False), clean_desc, base_code),
            )
            row = cur.fetchone()
        conn.commit()
        cur.close()
        conn.close()
    except Exception as e:
        logger.error("sync-material PG error: %s", e, exc_info=True)
        return JSONResponse(status_code=500, content={"status": "error", "message": str(e)})

    logger.info("[SYNC-MATERIAL] %s: '%s' → '%s'", magento_code, old_mat, mat)
    return {
        "status": "success",
        "magento_ref_code": magento_code,
        "chat_lieu_old": old_mat,
        "chat_lieu_new": mat,
        "updated": bool(row),
    }





@router.post("/approve-all", summary="Bulk approve ALL pending descriptions")
async def approve_all_desc():
    """Set all status=0 to status=1 in one shot."""
    try:
        count = UltraDescriptionDB.approve_all_pending(render_fn=_render_description_text)
        return {"status": "success", "message": f"✅ Đã duyệt toàn bộ {count} mô tả!", "approved_count": count}
    except Exception as e:
        logger.error(f"Approve all error: {e}")
        return JSONResponse(status_code=500, content={"status": "error", "message": str(e)})


@router.get("/clean-missing-summary", summary="Count rows missing valid clean_description")
async def clean_missing_summary():
    """Return how many rows still need clean_description backfill."""
    try:
        missing_count = UltraDescriptionDB.count_missing_clean_descriptions()
        stats = UltraDescriptionDB.get_stats()
        return {
            "status": "success",
            "missing_clean_desc": missing_count,
            "has_desc": stats.get("total", 0),
            "has_clean_desc": stats.get("has_clean_desc", 0),
        }
    except Exception as e:
        logger.error(f"Clean missing summary error: {e}")
        return JSONResponse(status_code=500, content={"status": "error", "message": str(e)})


@router.post("/backfill-clean-description", summary="Backfill missing clean_description in bulk")
async def backfill_clean_description():
    """Render clean_description in bulk from existing description_data."""
    try:
        updated_count = UltraDescriptionDB.backfill_missing_clean_descriptions(
            render_fn=_render_description_text
        )
        remaining = UltraDescriptionDB.count_missing_clean_descriptions()
        return {
            "status": "success",
            "message": f"Đã render lại clean description cho {updated_count} sản phẩm.",
            "updated_count": updated_count,
            "remaining_missing_clean_desc": remaining,
        }
    except Exception as e:
        logger.error(f"Backfill clean description error: {e}")
        return JSONResponse(status_code=500, content={"status": "error", "message": str(e)})

# ═══ 7. DELETE ═══
@router.delete("/saved/{internal_ref_code}", summary="Delete saved ultra description")
async def delete_saved_desc(internal_ref_code: str):
    """Delete a saved ultra description from Postgres."""
    try:
        deleted = UltraDescriptionDB.delete_by_code(internal_ref_code)
        if deleted:
            return {"status": "success", "message": f"Đã xóa description cho {internal_ref_code}"}
        return JSONResponse(status_code=404, content={
            "status": "not_found",
            "message": f"Không tìm thấy description cho {internal_ref_code}",
        })
    except Exception as e:
        logger.error(f"Delete desc error: {e}")
        return JSONResponse(status_code=500, content={"status": "error", "message": str(e)})


# ═══ 8. BATCH GENERATE (REDIS QUEUE) ═══
class BatchGenerateRequest(BaseModel):
    internal_ref_codes: list[str]

@router.post("/batch-generate", summary="Trigger batch generation via Redis Worker")
async def batch_generate_descriptions(req: BatchGenerateRequest):
    """Start background generation for multiple products via Redis queue."""
    if not req.internal_ref_codes:
        return JSONResponse(status_code=400, content={"status": "error", "message": "List products is empty"})

    # Capping at 50 products per request for safety
    codes = req.internal_ref_codes[:50]

    redis = redis_cache.get_client()
    if not redis:
        return JSONResponse(status_code=500, content={"status": "error", "message": "Redis cache is offline, cannot run batch"})

    job_id = str(uuid.uuid4())
    job_payload = {
        "job_id": job_id,
        "codes": codes
    }

    try:
        await redis.lpush("desc:queue", json.dumps(job_payload))
    except Exception as e:
        logger.error(f"Redis error: {e}")
        return JSONResponse(status_code=500, content={"status": "error", "message": "Failed to enqueue job in Redis"})

    return {
        "status": "success",
        "message": f"Đưa {len(codes)} sản phẩm vào hàng đợi xử lý Background Worker.",
        "job_id": job_id,
        "queued_codes": codes
    }

# ═══ 9. BATCH GENERATE ALL (REDIS QUEUE) ═══
@router.post("/batch-generate-all", summary="Trigger batch generation for ALL missing products up to 500 items")
async def batch_generate_all():
    try:
        # 1. Fetch all existing codes from Postgres
        code_status_map = UltraDescriptionDB.get_all_codes_with_status()

        # 2. Fetch all unique product codes from StarRocks
        db = get_db_connection()
        query = f"SELECT DISTINCT internal_ref_code FROM {TABLE_NAME}"
        rows = await db.execute_query_async(query)

        all_codes = [r["internal_ref_code"] for r in rows if r.get("internal_ref_code")]

        # 3. Filter to find missing codes
        missing_codes = [code for code in all_codes if code not in code_status_map]

        # 4. Limit to 500
        codes = missing_codes[:500]

        if not codes:
            return {"status": "success", "message": "Tuyệt vời! Không có sản phẩm nào thiếu AI Description."}

        redis = redis_cache.get_client()
        if not redis:
            return JSONResponse(status_code=500, content={"status": "error", "message": "Redis cache is offline"})

        job_id = str(uuid.uuid4())
        job_payload = {
            "job_id": job_id,
            "codes": codes
        }
        await redis.lpush("desc:queue", json.dumps(job_payload))

        return {
            "status": "success",
            "message": f"Tìm thấy {len(codes)} sản phẩm thiếu. Đã đưa vào queue cho Background Worker.",
            "job_id": job_id,
            "queued_codes": codes
        }
    except Exception as e:
        logger.error(f"Batch All Error: {e}")
        return JSONResponse(status_code=500, content={"status": "error", "message": str(e)})


@router.get("/batch-status", summary="Get status of latest batch generation job")
@router.get("/batch-status/{job_id}", summary="Get status of a specific batch generation job")
async def get_batch_status(job_id: Optional[str] = None):
    """Retrieve progress status from Redis."""
    try:
        redis = redis_cache.get_client()
        if not redis:
            return JSONResponse(status_code=500, content={"status": "error", "message": "Redis is offline"})

        if not job_id:
            job_id = await redis.get("desc:progress:latest_job")
            if not job_id:
                return {
                    "status": "success",
                    "progress": None
                }
            if isinstance(job_id, bytes):
                job_id = job_id.decode('utf-8')

        progress_key = f"desc:progress:{job_id}"
        data = await redis.hgetall(progress_key)

        if not data:
            return {
                "status": "success",
                "message": "Job not found or already expired",
                "progress": None
            }

        # Since decode_responses=True, data is already string
        decoded_data = data
        return {
            "status": "success",
            "job_id": job_id,
            "progress": {
                "is_running": decoded_data.get("is_running") == "true",
                "total": int(decoded_data.get("total", 0)),
                "done": int(decoded_data.get("done", 0)),
                "errors": int(decoded_data.get("errors", 0)),
                "current_code": decoded_data.get("current_code", "")
            }
        }
    except Exception as e:
        logger.error(f"Batch Status Error: {e}")
        return JSONResponse(status_code=500, content={"status": "error", "message": str(e)})

# ═══ 10. DYNAMIC FIELDS CRUD ═══

class FieldCreateRequest(BaseModel):
    field_key: str
    field_label: str
    field_instruction: str = ""
    sort_order: int = 99

class FieldUpdateRequest(BaseModel):
    field_label: str | None = None
    field_instruction: str | None = None
    is_active: bool | None = None
    sort_order: int | None = None


@router.get("/fields", summary="Get all dynamic fields")
async def get_fields():
    """Get all dynamic fields (active and inactive) for admin UI."""
    try:
        fields = DescFieldConfig.get_all_fields()
        return {"status": "success", "data": fields}
    except Exception as e:
        logger.error(f"Get fields error: {e}")
        return JSONResponse(status_code=500, content={"status": "error", "message": str(e)})


@router.post("/fields", summary="Add a new dynamic field")
async def add_field(req: FieldCreateRequest):
    """Add a new custom field to the description schema."""
    try:
        success = DescFieldConfig.add_field(
            req.field_key, req.field_label, req.field_instruction, req.sort_order
        )
        if success:
            return {"status": "success", "message": f"Đã thêm trường {req.field_key}"}
        return JSONResponse(status_code=400, content={"status": "error", "message": "Field key may already exist"})
    except Exception as e:
        logger.error(f"Add field error: {e}")
        return JSONResponse(status_code=500, content={"status": "error", "message": str(e)})


@router.put("/fields/{field_key}", summary="Update a dynamic field")
async def update_field(field_key: str, req: FieldUpdateRequest):
    """Update a custom field (label, instruction, status, order)."""
    try:
        success = DescFieldConfig.update_field(
            field_key, req.field_label, req.field_instruction, req.is_active, req.sort_order
        )
        if success:
            return {"status": "success", "message": f"Đã cập nhật trường {field_key}"}
        return JSONResponse(status_code=404, content={"status": "error", "message": f"Không tìm thấy trường {field_key}"})
    except Exception as e:
        logger.error(f"Update field error: {e}")
        return JSONResponse(status_code=500, content={"status": "error", "message": str(e)})


@router.delete("/fields/{field_key}", summary="Delete a dynamic field")
async def delete_field(field_key: str):
    """Completely remove a field from the schema."""
    try:
        success = DescFieldConfig.delete_field(field_key)
        if success:
            return {"status": "success", "message": f"Đã xóa trường {field_key}"}
        return JSONResponse(status_code=404, content={"status": "error", "message": f"Không tìm thấy trường {field_key}"})
    except Exception as e:
        logger.error(f"Delete field error: {e}")
        return JSONResponse(status_code=500, content={"status": "error", "message": str(e)})


# ═══ 11. BATCH GENERATE TAGS ═══

TAGS_ALLOWED = [
    "đi học", "đi chơi", "đi tiệc", "dạo phố", "mặc nhà", "đi ngủ",
    "thể thao", "đi dã ngoại", "đi biển", "đi làm", "giữ ấm", "thoáng mát"
]

async def _process_batch_tags(job_id: str, codes: list[str]):
    """Background task to generate tags for products."""
    redis = redis_cache.get_client()
    if not redis:
        return

    progress_key = f"tags:progress:{job_id}"
    await redis.hset(progress_key, mapping={
        "is_running": "true",
        "total": len(codes),
        "done": 0,
        "errors": 0,
        "current_code": ""
    })
    await redis.set("tags:progress:latest_job", job_id)
    await redis.expire(progress_key, 86400) # 24h

    done = 0
    errors = 0

    TAGS_PROMPT = f"""Bạn là chuyên gia phân loại sản phẩm. Hãy đọc phần mô tả sản phẩm và trích xuất đúng các tags phù hợp nhất.
HỆ THỐNG TAGS BẮT BUỘC (Chỉ được chọn từ danh sách này):
{json.dumps(TAGS_ALLOWED, ensure_ascii=False)}

QUY TẮC:
- Trả về 1 mảng JSON chứa tối đa 5 tags.
- KHÔNG sáng tạo thêm tags mới ngoài danh sách.
- Ví dụ trả về: ["đi làm", "thoáng mát"]
"""

    for code in codes:
        await redis.hset(progress_key, "current_code", code)

        row = UltraDescriptionDB.get_by_code(code)
        if not row:
            errors += 1
            continue

        text = row.get("clean_description", "")
        if not text:
            # Try to get from raw description_data if clean is empty
            desc_data = row.get("description_data", {})
            if isinstance(desc_data, str):
                try:
                    desc_data = json.loads(desc_data)
                except:
                    desc_data = {}
            if "mo_ta_chinh" in desc_data:
                text = desc_data.get("mo_ta_chinh", "") + " " + desc_data.get("dip_mac", "")

        if not text:
            errors += 1
            continue

        messages = [
            {"role": "system", "content": TAGS_PROMPT},
            {"role": "user", "content": f"Sản phẩm: {row.get('product_name', '')}\nMô tả:\n{text[:1000]}"}
        ]

        try:
            # Use _call_ai_with_fallback for multi-key rotation and robust calling
            output = await _call_ai_with_fallback(messages, model=ENRICH_MODEL, temperature=0.1, max_tokens=200)
            parsed = _extract_json(output)

            if isinstance(parsed, list):
                # Filter to only allowed tags
                final_tags = [t for t in parsed if t in TAGS_ALLOWED]
                UltraDescriptionDB.update_tags(code, final_tags)
                done += 1
            else:
                errors += 1
        except Exception as e:
            logger.error(f"Error AI tag for {code}: {e}")
            errors += 1

        await redis.hset(progress_key, mapping={"done": done, "errors": errors})
        await asyncio.sleep(1) # Prevent aggressive rate limits

    await redis.hset(progress_key, "is_running", "false")


@router.post("/batch-generate-tags", summary="Generate semantic tags for all missing products")
async def batch_generate_tags_endpoint(background_tasks: BackgroundTasks):
    """Trigger background job to analyze and extract tags for products missing them."""
    try:
        from common.pool_wrapper import get_pooled_connection_compat
        conn = get_pooled_connection_compat()
        cur = conn.cursor()

        # Get codes that have clean_descriptions but missing tags (tags='[]' or tags is null)
        cur.execute(f"SELECT internal_ref_code FROM dashboard_canifa.ultra_descriptions WHERE clean_description IS NOT NULL AND BTRIM(clean_description) != '' AND (tags IS NULL OR tags = '[]'::jsonb)")
        missing_rows = cur.fetchall()
        cur.close()
        conn.close()

        codes = [r[0] for r in missing_rows]

        if not codes:
            return {"status": "success", "message": "Tuyệt vời! Tất cả sản phẩm đều đã được gán tags."}

        job_id = f"tags_{str(uuid.uuid4())[:8]}"
        background_tasks.add_task(_process_batch_tags, job_id, codes)

        return {
            "status": "success",
            "message": f"Tìm thấy {len(codes)} sản phẩm thiếu tags. Đang tiến hành tạo ẩn (Background Task).",
            "job_id": job_id,
            "queued_codes": codes
        }
    except Exception as e:
        logger.error(f"Generate Tags Error: {e}")
        return JSONResponse(status_code=500, content={"status": "error", "message": str(e)})

@router.get("/tags-batch-status", summary="Get status of latest tag batch generation job")
async def get_tags_batch_status():
    """Retrieve tag progress status from Redis."""
    try:
        redis = redis_cache.get_client()
        if not redis:
            return JSONResponse(status_code=500, content={"status": "error", "message": "Redis is offline"})

        job_id = await redis.get("tags:progress:latest_job")
        if not job_id:
            return {"status": "success", "progress": None}

        if isinstance(job_id, bytes):
            job_id = job_id.decode('utf-8')

        progress_key = f"tags:progress:{job_id}"
        data = await redis.hgetall(progress_key)

        if not data:
            return {"status": "success", "progress": None}

        # Decode since decode_responses=True on the connection usually
        def _get_val(key, default):
            v = data.get(key, default)
            if isinstance(v, bytes):
                return v.decode('utf-8')
            return v

        return {
            "status": "success",
            "job_id": job_id,
            "progress": {
                "is_running": _get_val("is_running", "") == "true",
                "total": int(_get_val("total", 0)),
                "done": int(_get_val("done", 0)),
                "errors": int(_get_val("errors", 0)),
                "current_code": _get_val("current_code", "")
            }
        }
    except Exception as e:
        logger.error(f"Tags Batch Status Error: {e}")
        return JSONResponse(status_code=500, content={"status": "error", "message": str(e)})
