"""
Stylist Engine — Pre-computes fashion matching for all products.
Reads fashion_rules.json → scores pairs → writes ai_matches to Postgres.

Usage:
  from worker.stylist_engine import StylistEngine
  engine = StylistEngine()
  result = engine.run_for_code("5TS25S021-SR079")   # 1 product
  engine.run_batch(limit=None)                        # all products
"""

import json
import logging
import os
import time
from typing import Optional

from common.pool_wrapper import get_pooled_connection_compat
from common.starrocks_connection import get_db_connection

logger = logging.getLogger(__name__)

RULES_PATH = os.path.join(os.path.dirname(__file__), "fashion_rules.json")
TABLE = "dashboard_canifa.ultra_descriptions"
SR_TABLE = "shared_source.magento_product_dimension_with_text_embedding"


# ═══ Batch state (used by API for polling) ═══
BATCH_STATE = {
    "is_running": False,
    "total": 0,
    "done": 0,
    "errors": 0,
    "current_code": None,
    "started_at": None,
    "elapsed_s": None,
}


class StylistEngine:
    """Core fashion matching engine."""

    def __init__(self):
        with open(RULES_PATH, "r", encoding="utf-8") as f:
            self.rules = json.load(f)
        self._catalog: list[dict] = []  # cached product catalog

    # ──────────────────────────────────────────
    # Public API
    # ──────────────────────────────────────────

    def run_for_code(self, code: str) -> dict:
        """Compute ai_matches for a single product code and save to DB."""
        catalog = self._get_catalog()
        source = next((p for p in catalog if p["code"] == code), None)
        if not source:
            logger.warning("[Stylist] Product not found in catalog: %s", code)
            return {}

        matches = self._compute_matches(source, catalog)
        self._save_matches(code, matches)
        logger.info("[Stylist] ✅ %s → %d matches across occasions", code, sum(
            len(items) for occ in matches.values() for items in occ.values()
        ))
        return matches

    def run_batch(self, codes: Optional[list[str]] = None) -> int:
        """Batch run for list of codes (or all if None). Updates BATCH_STATE."""
        global BATCH_STATE
        catalog = self._get_catalog(force_reload=True)

        targets = codes or [p["code"] for p in catalog]
        BATCH_STATE.update({
            "is_running": True,
            "total": len(targets),
            "done": 0,
            "errors": 0,
            "current_code": None,
            "started_at": time.time(),
            "elapsed_s": None,
        })

        count = 0
        for code in targets:
            BATCH_STATE["current_code"] = code
            try:
                source = next((p for p in catalog if p["code"] == code), None)
                if not source:
                    BATCH_STATE["errors"] += 1
                    continue
                matches = self._compute_matches(source, catalog)
                self._save_matches(code, matches)
                count += 1
                BATCH_STATE["done"] += 1
            except Exception as e:
                logger.error("[Stylist] Error for %s: %s", code, e)
                BATCH_STATE["errors"] += 1

        elapsed = round(time.time() - BATCH_STATE["started_at"], 1)
        BATCH_STATE.update({
            "is_running": False,
            "elapsed_s": elapsed,
            "current_code": None,
        })
        logger.info("[Stylist] ✅ Batch done: %d/%d in %.1fs", count, len(targets), elapsed)
        return count

    # ──────────────────────────────────────────
    # Core Matching Logic
    # ──────────────────────────────────────────

    def _compute_matches(self, source: dict, catalog: list[dict]) -> dict:
        """Compute matches for one source product across all occasions + roles based on DB rules."""
        rules = self.rules
        roles = rules["roles"]
        max_items = rules["role_max_items"]
        anchor_cat = source.get("product_line", "")
        gender = source.get("gender", "")

        # Fetch allowed mappings from DB: {occasion_tag: set(target_category)}
        allowed_by_occ = self._fetch_allowed_mappings(anchor_cat, gender)
        if not allowed_by_occ:
            logger.warning("[Stylist] No rules for anchor '%s', returning empty matches", anchor_cat)
            return {}

        # Initialize buckets only for occasions that have rules
        buckets: dict[str, dict[str, list[dict]]] = {
            occ: {role: [] for role in roles} for occ in allowed_by_occ.keys()
        }

        for target in catalog:
            if target["code"] == source["code"]:
                continue
            if not self._pass_hard_filter(source, target):
                continue
            role = self._get_role(target)
            if not role:
                continue
            tgt_pl = target.get("product_line", "")
            if not tgt_pl:
                continue
            tgt_pl_lower = tgt_pl.lower()

            # Check each occasion that has rules for this anchor
            for occ, allowed_targets in allowed_by_occ.items():
                if tgt_pl_lower in allowed_targets:
                    score = self._score(source, target, occ)
                    if score < rules.get("min_score", 35):
                        continue
                    reason = self._build_reason(source, target, occ, score)
                    buckets[occ][role].append({
                        "code": target["code"],
                        "name": target["name"],
                        "color": target.get("color", ""),
                        "product_line": target.get("product_line", ""),
                        "image": target.get("image", ""),
                        "score": score,
                        "reason": reason,
                    })

        # Pick top N per occasion+role, sort by score
        result: dict[str, dict] = {}
        for occ, occ_data in buckets.items():
            final_occ_data = {}
            for role, items in occ_data.items():
                top = sorted(items, key=lambda x: -x["score"])
                top = self._deduplicate(top, max_items.get(role, 3))
                if top:
                    final_occ_data[role] = top
            if final_occ_data:
                result[occ] = final_occ_data

        return result

    def _fetch_allowed_mappings(self, anchor_cat: str, gender: str = "") -> dict:
        """Fetch {occasion_tag: set(target_category)} from chatbot_fashion_rules.
        Filters by gender_target: returns rules matching (gender, 'all').
        """
        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()
            gender_key = self._normalize_gender(gender)
            try:
                cur.execute(
                    """SELECT occasion_tag, target_category FROM dashboard_canifa.chatbot_fashion_rules
                       WHERE UPPER(anchor_category) = UPPER(%s)
                         AND (gender_target = %s OR gender_target = 'all')""",
                    (anchor_cat, gender_key)
                )
            except Exception:
                # Fallback: column may not exist yet
                cur.execute(
                    "SELECT occasion_tag, target_category FROM dashboard_canifa.chatbot_fashion_rules WHERE UPPER(anchor_category) = UPPER(%s)",
                    (anchor_cat,)
                )
            mapping: dict[str, set[str]] = {}
            for row in cur.fetchall():
                occ, tgt = row[0], row[1]
                if occ not in mapping:
                    mapping[occ] = set()
                mapping[occ].add(tgt.lower())
            cur.close()
            return mapping
        except Exception as e:
            logger.error("[Stylist] DB rule fetch error: %s", e)
            return {}
        finally:
            if conn:
                conn.close()

    def compute_dynamic_rule_matches(self, code: str) -> dict:
        """Dynamically compute matches based on chatbot_fashion_rules (DB) with color+material+role scoring."""
        catalog = self._get_catalog()
        source = next((p for p in catalog if p["code"] == code), None)
        if not source:
            logger.warning("[Stylist] Product not found in catalog: %s", code)
            return {}

        anchor_cat = source.get("product_line", "")
        gender = source.get("gender", "")

        # 1. Fetch Rules from DB: list of {occ, role, target_cat, ai_reason}
        db_rules = self._fetch_rules_with_reason(anchor_cat, gender)
        if not db_rules:
            logger.info("[Stylist] No DB rules for %s, falling back to full compute", anchor_cat)
            return self._compute_matches(source, catalog)

        # Group by occasion for easier lookup
        rules_by_occ: dict[str, list[dict]] = {}
        for r in db_rules:
            if r["occ"] not in rules_by_occ:
                rules_by_occ[r["occ"]] = []
            rules_by_occ[r["occ"]].append(r)

        max_items = self.rules.get("role_max_items", {})
        buckets: dict[str, dict[str, list[dict]]] = {}

        # 2. Filter targets by DB rules and score
        for target in catalog:
            if target["code"] == source["code"]:
                continue
            if not self._pass_hard_filter(source, target):
                continue
            tgt_pl = target.get("product_line", "")
            if not tgt_pl:
                continue
            tgt_role = self._get_role(target)
            if not tgt_role:
                continue

            # Check each occasion's rules
            for occ, rules_list in rules_by_occ.items():
                for r in rules_list:
                    if tgt_pl.lower() == r["target_cat"].lower() and tgt_role == r["role"]:
                        # Compute score using new simplified scoring
                        score = self._score(source, target, occ)
                        if score < self.rules.get("min_score", 35):
                            continue

                        # Build reason: combine algorithm reason + DB ai_reason
                        algo_reason = self._build_reason(source, target, occ, score)
                        db_reason = r["ai_reason"] or f"Theo định hướng ({occ})"
                        combined_reason = f"{algo_reason} | {db_reason}"

                        breakdown = self._score_breakdown(source, target, occ)

                        if occ not in buckets:
                            buckets[occ] = {}
                        if tgt_role not in buckets[occ]:
                            buckets[occ][tgt_role] = []
                        buckets[occ][tgt_role].append({
                            "code": target["code"],
                            "name": target["name"],
                            "color": target.get("color", ""),
                            "product_line": target.get("product_line", ""),
                            "image": target.get("image", ""),
                            "score": score,
                            "reason": combined_reason,
                            "breakdown": breakdown,
                        })
                        break  # Found matching rule for this occasion, move to next occasion

        # 3. Sort and Deduplicate
        result: dict[str, dict] = {}
        for occ, occ_data in buckets.items():
            final_occ_data = {}
            for role, items in occ_data.items():
                top = sorted(items, key=lambda x: -x["score"])
                top = self._deduplicate(top, max_items.get(role, 3))
                if top:
                    final_occ_data[role] = top
            if final_occ_data:
                result[occ] = final_occ_data

        return result

    def _normalize_gender(self, gender: str) -> str:
        """Normalize gender string to DB key."""
        g = (gender or "").lower().strip()
        if g in ("nu", "nữ", "women", "female", "woman"):
            return "nu"
        if g in ("nam", "men", "male", "man"):
            return "nam"
        if any(k in g for k in ["bé gái", "be_gai", "girl", "be gai"]):
            return "be_gai"
        if any(k in g for k in ["bé trai", "be_trai", "boy", "be trai"]):
            return "be_trai"
        return "unisex"

    def _fetch_rules_with_reason(self, anchor_cat: str, gender: str = "") -> list[dict]:
        """Fetch full rules including occasion_tag, match_role, target_category, ai_reason.
        Filters by gender_target.
        """
        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()
            gender_key = self._normalize_gender(gender)
            try:
                cur.execute(
                    """SELECT occasion_tag, match_role, target_category, ai_reason
                       FROM dashboard_canifa.chatbot_fashion_rules
                       WHERE UPPER(anchor_category) = UPPER(%s)
                         AND (gender_target = %s OR gender_target = 'all')""",
                    (anchor_cat, gender_key)
                )
            except Exception:
                cur.execute(
                    "SELECT occasion_tag, match_role, target_category, ai_reason FROM dashboard_canifa.chatbot_fashion_rules WHERE UPPER(anchor_category) = UPPER(%s)",
                    (anchor_cat,)
                )
            rules = []
            for row in cur.fetchall():
                rules.append({
                    "occ": row[0],
                    "role": row[1],
                    "target_cat": row[2],
                    "ai_reason": row[3],
                })
            cur.close()
            return rules
        except Exception as e:
            logger.error("[Stylist] DB rule fetch error: %s", e)
            return []
        finally:
            if conn:
                conn.close()

    def compute_super_classifications_sql(self, code: str) -> dict:
        """
        Dynamically fetches top matches directly from DB using custom SQL queries 
        based on the super classification matrix (Color, Occasion, Material).
        """
        catalog = self._get_catalog()
        source = next((p for p in catalog if p["code"] == code), None)
        if not source:
            logger.warning("[Stylist] Product not found for SQL grouping: %s", code)
            return {}

        gender = source.get("gender", "")
        if not gender or gender.lower() == "unisex":
            gender = "women" # Default fallback for classification demo

        # Smart target category filtering based on anchor category to avoid Top-Top or Bottom-Bottom match
        target_cats = {"sơ mi", "chân váy", "quần âu", "quần jean", "áo phông", "quần soóc"}
        src_pl = source.get("product_line", "").lower()
        
        if "quần" in src_pl or "váy" in src_pl or "bộ" in src_pl:
            target_cats = target_cats - {"chân váy", "quần âu", "quần jean", "quần soóc"}
        elif "áo" in src_pl:
            target_cats = target_cats - {"sơ mi", "áo phông"}
        
        if not target_cats:
            target_cats = {"áo phông", "quần soóc"} # Fallback

        like_cond = " OR ".join([f"LOWER(product_line_vn) LIKE '%{c}%'" for c in target_cats])
        
        sr_db = get_db_connection()
        SR_TABLE = "shared_source.magento_product_dimension_with_text_embedding"
        
        # Color Group Queries
        color_groups = {
            "neutral": "%trắng%' OR LOWER(master_color) LIKE '%white%' OR LOWER(master_color) LIKE '%đen%' OR LOWER(master_color) LIKE '%black%' OR LOWER(master_color) LIKE '%xám%' OR LOWER(master_color) LIKE '%grey%' OR LOWER(master_color) LIKE '%be%' OR LOWER(master_color) LIKE '%beige%",
            "light": "%vàng%' OR LOWER(master_color) LIKE '%hồng%' OR LOWER(master_color) LIKE '%xanh da trời%",
            "dark": "%đỏ%' OR LOWER(master_color) LIKE '%cam%' OR LOWER(master_color) LIKE '%xanh lá cây%' OR LOWER(master_color) LIKE '%xanh jeans%' OR LOWER(master_color) LIKE '%xanh than%"
        }
        
        # Material Season Queries
        mat_groups = {
            "summer": "%cotton%' OR LOWER(description_text_full) LIKE '%linen%' OR LOWER(description_text_full) LIKE '%mát%' OR LOWER(description_text_full) LIKE '%hè%",
            "winter": "%nỉ%' OR LOWER(description_text_full) LIKE '%len%' OR LOWER(description_text_full) LIKE '%phao%' OR LOWER(description_text_full) LIKE '%chần bông%' OR LOWER(description_text_full) LIKE '%đông%"
        }

        # Occasion Queries 
        # (Assuming Đi làm: sơ mi/quần âu/váy; Đi chơi: váy/áo phông/soóc)
        occ_groups = {
            "di_lam": "%sơ mi%' OR LOWER(product_line_vn) LIKE '%chân váy%' OR LOWER(product_line_vn) LIKE '%quần âu%",
            "di_choi": "%váy%' OR LOWER(product_line_vn) LIKE '%áo phông%' OR LOWER(product_line_vn) LIKE '%quần soóc%",
            "o_nha": "%bộ mặc nhà%' OR LOWER(product_line_vn) LIKE '%quần lót%' OR LOWER(product_line_vn) LIKE '%áo lót%"
        }

        results = {"color": {}, "occasion": {}, "material": {}}
        
        # Helper inner function to run SQL with deduplication
        def fetch_top_sql(where_sql_extra: str, limit=5):
            query = f"""
                SELECT magento_ref_code, product_name, master_color, product_line_vn, product_image_url_thumbnail
                FROM {SR_TABLE}
                WHERE LOWER(gender_by_product) = '{gender.lower()}' AND ({where_sql_extra})
                ORDER BY is_new_product DESC, quantity_sold DESC
                LIMIT {limit * 4}
            """
            try:
                res = sr_db.execute_query(query)
                out = []
                seen_codes = set()
                for r in res:
                    code = r["magento_ref_code"]
                    if code in seen_codes:
                        continue
                    seen_codes.add(code)
                    out.append({
                        "code": code,
                        "name": r["product_name"] or "",
                        "color": r["master_color"] or "",
                        "product_line": r["product_line_vn"] or "",
                        "image": r["product_image_url_thumbnail"] or ""
                    })
                    if len(out) >= limit:
                        break
                return out
            except Exception as e:
                logger.error("[Stylist] Dynamic SQL failed: %s", e)
                return []

        # 1. Fetch Color
        for k, cond in color_groups.items():
            results["color"][k] = fetch_top_sql(f"({like_cond}) AND (LOWER(master_color) LIKE '{cond}')")
            
        # 2. Fetch Occasion
        for k, cond in occ_groups.items():
            results["occasion"][k] = fetch_top_sql(f"LOWER(product_line_vn) LIKE '{cond}'")
            
        # 3. Fetch Material
        for k, cond in mat_groups.items():
            results["material"][k] = fetch_top_sql(f"({like_cond}) AND (LOWER(description_text_full) LIKE '{cond}')")

        return results

    # ──────────────────────────────────────────
    # Scoring Sub-functions
    # ──────────────────────────────────────────

    def _pass_hard_filter(self, src: dict, tgt: dict) -> bool:
        """Return False if the pair should be excluded."""
        rules = self.rules

        # Exclude blacklisted product lines
        excluded = [pl.lower() for pl in rules.get("exclude_product_lines", [])]
        if tgt.get("product_line", "").lower() in excluded:
            return False

        # ── Gender filter ───────────────────────────────────────────────────
        # "unisex" can pair with anything; kids-only "unisex trẻ em" is handled
        # by age_group below. Here we only check adult-gender mismatch.
        sg = (src.get("gender") or "").lower().strip()
        tg = (tgt.get("gender") or "").lower().strip()

        def _is_unisex(g: str) -> bool:
            return "unisex" in g

        # Both have explicit genders, neither is unisex → must match
        if sg and tg and not _is_unisex(sg) and not _is_unisex(tg) and sg != tg:
            return False

        # ── Age group filter (adult/kids) ────────────────────────────────────
        # Keywords that mark a product as "kids only"
        KID_KEYWORDS = ["kid", "bé", "boy", "girl", "trẻ em"]
        sa = (src.get("age_group") or "").lower()
        ta = (tgt.get("age_group") or "").lower()
        # Also detect kids' items from gender field (unisex trẻ em, bé trai, bé gái …)
        # AND from product NAME in case StarRocks age_group field has wrong data
        src_name = (src.get("name") or "").lower()
        tgt_name = (tgt.get("name") or "").lower()
        src_kid = (any(k in sa for k in KID_KEYWORDS) or any(k in sg for k in KID_KEYWORDS)
                   or any(k in src_name for k in ["bé trai", "bé gái", "trẻ em"]))
        tgt_kid = (any(k in ta for k in KID_KEYWORDS) or any(k in tg for k in KID_KEYWORDS)
                   or any(k in tgt_name for k in ["bé trai", "bé gái", "trẻ em"]))
        if src_kid != tgt_kid:
            return False

        return True

    def _get_role(self, product: dict) -> Optional[str]:
        """Determine which role this product plays in an outfit."""
        pl = product.get("product_line", "")
        return self.rules["product_line_to_role"].get(pl)

    def _score(self, src: dict, tgt: dict, occasion: str) -> int:
        """Compute compatibility score (0-100) for src+tgt pair using only Color, Material, and Role."""
        rules = self.rules
        weights = rules["score_weights"]
        total = 0

        # 1. Color score (0-30)
        color_score = self._color_score(src.get("color", ""), tgt.get("color", ""), tgt.get("gender", ""))
        total += int(color_score * weights["color"] / 30)

        # 2. Style score - REMOVED (NLP tags)

        # 3. Occasion score - REMOVED (filtered by DB rules)

        # 4. Role bonus — Bottom gets priority over accessories (0-15)
        role = self._get_role(tgt)
        role_priority = rules.get("role_priority", {})
        role_bonus = max(0, 16 - role_priority.get(role, 3) * 5)
        total += int(role_bonus * weights["role"] / 15)

        # 5. Material score (0-10) - New dimension based on season compatibility
        if "material" in weights:
            material_score = self._material_score(src, tgt)
            total += int(material_score * weights["material"] / 10)

        return min(total, 100)

    def _color_score(self, src_color: str, tgt_color: str, gender: str = "") -> int:
        """Score color compatibility using group matrix (0-30), fallback to exact key matrix.
        Applies logic boosting/penalizing based on demographic."""
        rules = self.rules
        color_keys = rules["color_keys"]
        
        src_key = self._detect_color_key(src_color, color_keys)
        tgt_key = self._detect_color_key(tgt_color, color_keys)

        # Retrieve groups if available (Neutral, Light, Dark)
        color_groups = rules.get("color_groups", {})
        src_group = color_groups.get(src_key) if src_key else None
        tgt_group = color_groups.get(tgt_key) if tgt_key else None

        base_score = rules.get("default_color_score", 12)

        # Primary logic: Framework Excel styling (Neutral vs Light vs Dark)
        if src_group and tgt_group:
            group_matrix = rules.get("color_group_matrix", {})
            base_score = group_matrix.get(src_group, {}).get(tgt_group, base_score)
        # Fallback: specific color matching (matrix legacy)
        elif src_key and tgt_key:
            matrix = rules["color_matrix"]
            row = matrix.get(src_key, {})
            base_score = row.get(tgt_key, base_score)

        # Demographic specific color boost
        demo_logic = rules.get("demographic_color_logic", {})
        
        # Determine demographic key based on gender
        gl = gender.lower()
        demo_key = "unisex_nguoi_lon"
        if gl in ["nu", "nữ", "women", "female"]:
            demo_key = "nu"
        elif gl in ["nam", "men", "male"]:
            demo_key = "nam"
        elif any(k in gl for k in ["bé gái", "be_gai", "girl"]):
            demo_key = "be_gai"
        elif any(k in gl for k in ["bé trai", "be_trai", "boy"]):
            demo_key = "be_trai"
        elif any(k in gl for k in ["bé", "kid"]):
            demo_key = "unisex_tre_em"
            
        demo_rules = demo_logic.get(demo_key, {})
        
        # Apply boosts
        for b in demo_rules.get("boosts", []):
            if tgt_group == b["group"]:
                base_score += b["points"]
        
        # Apply penalties
        for p in demo_rules.get("penalties", []):
            if tgt_group == p["group"]:
                base_score += p["points"]

        return min(base_score, 30)

    def _detect_color_key(self, color_str: str, color_keys: dict) -> Optional[str]:
        """Map a master_color string to a canonical color key."""
        c = color_str.lower()
        for key, variants in color_keys.items():
            if any(v in c for v in variants):
                return key
        return None

    def _material_score(self, src: dict, tgt: dict) -> int:
        """Score material based on target's materials matching the required season (0-10)."""
        rules = self.rules
        tgt_mat = tgt.get("material_tags", [])
        tgt_season = tgt.get("season_tags", [])
        src_season = src.get("season_tags", [])
        
        # Determine the target season. Combine source season and target season tags for a broader match.
        target_seasons = set(s.lower() for s in tgt_season + src_season)
        
        # If no seasons are provided, default to medium positive compat
        if not target_seasons and not tgt_mat:
            return 5 # Neutral score if missing

        score = 0
        mat_season_matrix = rules.get("material_season_compat", {})
        
        for m in tgt_mat:
            # Map material string to a canonical material key
            matched_key = None
            for key in mat_season_matrix.keys():
                if key in m.lower():
                    matched_key = key
                    break
            
            if matched_key:
                compat_seasons = mat_season_matrix[matched_key]
                # If target season matches compatibility, full points (10/10)
                if any(s in compat_seasons for s in target_seasons):
                    score = max(score, 10)
                else:
                    # Found material but season doesn't ideally match, partial points
                    score = max(score, 4)
            else:
                score = max(score, 5) # Unrecognized material
                
        # If we loop and didn't find any actionable material score, default to 5
        return score if score > 0 else 5

    def _style_score(self, src_styles: list, tgt_styles: list, occasion: str) -> int:
        """Score style compatibility (0-25) with occasion boost."""
        rules = self.rules
        compat = rules["style_compat"]
        boost_styles = rules.get("occasion_style_boost", {}).get(occasion, [])
        best = rules.get("default_style_score", 10)

        for ss in (src_styles or ["Basic"]):
            row = compat.get(ss, {})
            for ts in (tgt_styles or ["Basic"]):
                v = row.get(ts, rules.get("default_style_score", 10))
                if ts in boost_styles:
                    v = min(v + 5, 25)
                best = max(best, v)
        return best

    def _occasion_score(self, src_occ: list, tgt_occ: list, occasion: str) -> int:
        """Score occasion overlap (0-20)."""
        keywords = self.rules.get("occasion_keywords", {}).get(occasion, [])
        src_text = " ".join(src_occ).lower()
        tgt_text = " ".join(tgt_occ).lower()
        both_match = any(kw in src_text for kw in keywords) and any(kw in tgt_text for kw in keywords)
        either_match = any(kw in src_text for kw in keywords) or any(kw in tgt_text for kw in keywords)
        if both_match:
            return 20
        if either_match:
            return 12
        return 6

    def _build_reason(self, src: dict, tgt: dict, occasion: str, score: int) -> str:
        """Generate Vietnamese reason string."""
        parts = []
        sc = self._detect_color_key(src.get("color", ""), self.rules["color_keys"])
        tc = self._detect_color_key(tgt.get("color", ""), self.rules["color_keys"])
        if sc and tc:
            parts.append(f"phối màu {sc.lower()} – {tc.lower()} hài hòa")

        occ_label = self.rules.get("occasion_labels", {}).get(occasion, occasion)
        if occ_label:
            parts.append(f"phù hợp {occ_label.lower()}")

        role = self._get_role(tgt)
        if role:
            role_label = self.rules.get("role_labels", {}).get(role, role)
            parts.append(f"cân đối dáng {role_label.lower()}")

        return ", ".join(parts) if parts else f"Phù hợp phối cùng (điểm {score})"

    def _deduplicate(self, items: list[dict], max_n: int) -> list[dict]:
        """Remove near-duplicates logic (Enforce product diversity + color diversity)."""
        seen_lines: set[str] = set()
        seen_colors: set[str] = set()
        result = []
        for item in items:
            pl = item.get("product_line", item["code"])
            # Canonicalize color slightly for filtering
            color_val = self._detect_color_key(item.get("color", ""), self.rules["color_keys"]) or item.get("color", "")
            
            # Prioritize inserting if we haven't seen this product line OR color yet
            if pl not in seen_lines or color_val not in seen_colors or len(result) < max_n // 2:
                result.append(item)
                seen_lines.add(pl)
                if color_val:
                    seen_colors.add(color_val)
                    
            if len(result) >= max_n:
                break
        return result

    # ──────────────────────────────────────────
    # Data Loading
    # ──────────────────────────────────────────

    def _get_catalog(self, force_reload: bool = False) -> list[dict]:
        """Load product catalog from StarRocks + description_data from Postgres."""
        if self._catalog and not force_reload:
            return self._catalog

        logger.info("[Stylist] Loading catalog...")
        sr_db = get_db_connection()

        # Fetch product basics from StarRocks
        rows = sr_db.execute_query(f"""
            SELECT
                magento_ref_code AS code,
                ANY_VALUE(product_name) AS name,
                ANY_VALUE(master_color) AS color,
                ANY_VALUE(product_line_vn) AS product_line,
                ANY_VALUE(gender_by_product) AS gender,
                ANY_VALUE(product_image_url_thumbnail) AS image
            FROM {SR_TABLE}
            WHERE magento_ref_code IS NOT NULL
              AND product_line_vn IS NOT NULL
            GROUP BY magento_ref_code
        """)

        # Fetch style/occasion tags from Postgres ultra_descriptions
        pg_conn = get_pooled_connection_compat()
        pg_cur = pg_conn.cursor()
        pg_cur.execute(f"""
            SELECT magento_ref_code, description_data
            FROM {TABLE}
            WHERE magento_ref_code IS NOT NULL
        """)
        desc_map: dict[str, dict] = {}
        for r in pg_cur.fetchall():
            code, dd = r
            if isinstance(dd, str):
                try:
                    dd = json.loads(dd)
                except Exception:
                    dd = {}
            if isinstance(dd, dict):
                desc_map[code] = dd
        pg_cur.close()
        pg_conn.close()

        # Build catalog with enriched tags
        catalog = []
        for row in rows:
            code = row["code"]
            dd = desc_map.get(code, {})
            catalog.append({
                "code": code,
                "name": row["name"] or "",
                "color": row["color"] or "",
                "product_line": row["product_line"] or "",
                "gender": row["gender"] or "",
                "age_group": row["gender"] or "",   # re-use gender field for kid/adult
                "image": row["image"] or "",
                "style_tags": self._extract_tags(dd.get("phong_cach", ""), dd.get("tags", "")),
                "occasion_tags": self._extract_occasion(dd.get("dip_mac", ""), dd.get("mua", "")),
                "material_tags": self._extract_tags(dd.get("vat_lieu", "")),
                "season_tags": self._extract_tags(dd.get("mua", "")),
            })

        self._catalog = catalog
        logger.info("[Stylist] Catalog loaded: %d products", len(catalog))
        return catalog

    def _extract_tags(self, *fields: str) -> list[str]:
        """Parse comma/dot/pipe separated style tags."""
        tags = []
        for field in fields:
            if field:
                for sep in [",", "·", "|", ";"]:
                    field = field.replace(sep, ",")
                tags.extend([t.strip() for t in field.split(",") if t.strip()])
        return tags

    def _extract_occasion(self, *fields: str) -> list[str]:
        """Parse occasion strings."""
        return self._extract_tags(*fields)

    # ──────────────────────────────────────────
    # DB Write
    # ──────────────────────────────────────────

    def _save_matches(self, code: str, matches: dict) -> bool:
        """Save ai_matches JSONB to ultra_descriptions."""
        conn = None
        try:
            conn = get_pooled_connection_compat()
            cur = conn.cursor()
            data_json = json.dumps(matches, ensure_ascii=False)
            cur.execute(
                f"""UPDATE {TABLE}
                    SET ai_matches = %s::jsonb, updated_at = NOW()
                    WHERE magento_ref_code = %s OR internal_ref_code = %s""",
                (data_json, code, code),
            )
            if cur.rowcount == 0:
                logger.warning("[Stylist] No row updated for code: %s", code)
            conn.commit()
            cur.close()
            return True
        except Exception as e:
            if conn:
                conn.rollback()
            logger.error("[Stylist] DB save error for %s: %s", code, e)
            return False
        finally:
            if conn:
                conn.close()

    # ── Public aliases for score-test API ───────
    def _load_catalog(self) -> list[dict]:
        """Alias for _get_catalog (used by score-test API)."""
        return self._get_catalog()

    def _score_breakdown(self, src: dict, tgt: dict, occ_target: str = None) -> dict:
        """Return per-criterion score breakdown for a source+target pair (used by DB-based matching)."""
        rules = self.rules
        weights = rules["score_weights"]

        # Color
        raw_color = self._color_score(src.get("color", ""), tgt.get("color", ""), tgt.get("gender", ""))
        color_pts = int(raw_color * weights["color"] / 30)

        # Occasion - REMOVED (filtered by DB rules)
        occ_pts = 0

        # Role
        role = self._get_role(tgt)
        role_priority = rules.get("role_priority", {})
        role_bonus = max(0, 16 - role_priority.get(role, 3) * 5)
        role_pts = int(role_bonus * weights["role"] / 15)

        # Material
        material_pts = 0
        raw_material = 0
        if "material" in weights:
            raw_material = self._material_score(src, tgt)
            material_pts = int(raw_material * weights["material"] / 10)

        sc = self._detect_color_key(src.get("color", ""), rules["color_keys"])
        tc = self._detect_color_key(tgt.get("color", ""), rules["color_keys"])

        cgroups = rules.get("color_groups", {})
        sg = cgroups.get(sc, "?") if sc else "?"
        tg = cgroups.get(tc, "?") if tc else "?"

        # Excel Formulas determination
        formulas = rules.get("formulas", {})
        cong_thuc_name = ""
        cong_thuc_tip = ""

        if sg == "neutral" and tg == "neutral":
            fm = formulas.get("neutral_neutral", {})
            cong_thuc_name = fm.get("name", 'Công thức [An Toàn]')
            cong_thuc_tip = fm.get("tip", "")
        elif (sg == "neutral" and tg in ("light", "dark")) or (tg == "neutral" and sg in ("light", "dark")):
            fm = formulas.get("neutral_light", {})
            if (sg == "dark" or tg == "dark"):
                fm = formulas.get("neutral_dark", fm)
            cong_thuc_name = fm.get("name", 'Công thức [Điểm Nhấn]')
            cong_thuc_tip = fm.get("tip", "")
        elif (sg == "dark" and tg == "dark"):
            cong_thuc_name = 'Công thức [Tone-Sur-Tone Trầm]'
            cong_thuc_tip = "MẸO: Tổng thể sẽ bí ẩn và có chiều sâu, nên pick phụ kiện kim loại."
        elif (sg == "light" and tg == "light"):
            cong_thuc_name = 'Công thức [Tone-Sur-Tone Nhạt]'
            cong_thuc_tip = "MẸO: Nên phối với giày độn đế Trắng hoặc phụ kiện sáng để không bị nhòa."

        # Mùa / Chất liệu
        tgt_mat_raw = " ".join(tgt.get("material_tags", [])) + " " + tgt.get("name_origin", "")
        tgt_mat_raw = tgt_mat_raw.lower()

        tgt_mat_season = "Không rõ mùa / dòng"
        if any(w in tgt_mat_raw for w in ["cotton", "linen", "hè"]):
            tgt_mat_season = "Hợp mùa hè (thoáng/mát)"
        elif any(w in tgt_mat_raw for w in ["nỉ", "len", "chần bông", "dạ", "phao", "đông"]):
            tgt_mat_season = "Hợp mùa đông (giữ nhiệt)"
        elif any(w in tgt_mat_raw for w in ["gió", "kaki", "jean", "denim", "thun"]):
            tgt_mat_season = "Linh hoạt cả Xuân/Thu"

        tgt_materials = tgt.get("material_tags", [])
        tgt_styles = tgt.get("style_tags", [])

        # Color breakdown with Formula tip injected
        color_reason_text = ""
        group_lbl = {
            "neutral": "Trắng/Đen/Trung tính",
            "light": "Nhạt/Pastel",
            "dark": "Đậm/Nổi"
        }
        if cong_thuc_name:
            color_reason_text = f"🪧 {cong_thuc_name}: {group_lbl.get(sg, sg)} ➔ {group_lbl.get(tg, tg)}. {cong_thuc_tip}"
        else:
            color_reason_text = f"Màu {sc} đi với {tc} khá hài hòa."

        color_reason_text += f" [Hệ {tgt.get('gender', 'Unisex')}]"

        return {
            "🎨 Mix Màu":       {"score": color_pts,    "max": weights["color"],            "reason": color_reason_text},
            "👔 Style":         {"score": 0,            "max": 0,                           "reason": "Đã bỏ NLP tags"},
            "🛍️ Dịp Mặc":      {"score": 0,            "max": 0,                           "reason": "Đã lọc theo DB rules"},
            "🧵 Dòng Hàng/Mùa": {"score": material_pts, "max": weights.get("material", 10), "reason": f"{tgt_mat_season} (Vải {', '.join(tgt_materials[:2]) if tgt_materials else 'chính hãng'})"},
            "🎯 Nhóm SP":       {"score": role_pts,     "max": weights["role"],             "reason": f"Dáng {tgt.get('product_line', 'Tiêu chuẩn')} tối ưu tỷ lệ"}
        }
