"""
Simple test: insert matches for 1 product using only SQLite catalog.
Avoids StarRocks dependency.
"""

import sys
import io
if sys.platform == "win32":
    sys.stdout.reconfigure(encoding='utf-8')

import sqlite3
from pathlib import Path

sys.path.insert(0, str(Path(__file__).parent.parent.parent))

from common.constants import SQLITE_DB_PATH, TABLE_OUTFIT_RULES, TABLE_PRODUCTS

def get_catalog_from_sqlite() -> list[dict]:
    """Load catalog trực tiếp từ SQLite."""
    conn = sqlite3.connect(SQLITE_DB_PATH)
    conn.row_factory = sqlite3.Row
    cur = conn.cursor()

    query = f"""
        SELECT magento_ref_code as code,
               product_name as name,
               master_color as color,
               product_line_vn as product_line,
               gender_by_product as gender,
               age_by_product as age_group,
               product_image_url_thumbnail as image
        FROM {TABLE_PRODUCTS}
        WHERE magento_ref_code IS NOT NULL
          AND product_line_vn IS NOT NULL
        LIMIT 100
    """
    cur.execute(query)
    rows = cur.fetchall()
    conn.close()

    catalog = []
    for r in rows:
        catalog.append({
            "code": r["code"],
            "name": r["name"],
            "color": r["color"] or "",
            "product_line": r["product_line"] or "",
            "gender": r["gender"] or "",
            "age_group": r["age_group"] or "",
            "image": r["image"] or "",
            "style_tags": [],
            "occasion_tags": [],
            "material_tags": [],
            "season_tags": [],
        })
    return catalog

def fetch_rules_for_anchor(anchor_cat: str, gender: str = "") -> list[dict]:
    """Fetch rules from SQLite."""
    from common.constants import normalize_gender, SQLITE_GENDER_MAP, TABLE_OUTFIT_RULES
    conn = sqlite3.connect(SQLITE_DB_PATH)
    conn.row_factory = sqlite3.Row
    cur = conn.cursor()

    gender_key = normalize_gender(gender)
    target_gender = SQLITE_GENDER_MAP.get(gender_key, "unisex")

    cur.execute(
        f"""SELECT id as rule_id, occasion_tag as occasion, match_role, target_category, ai_reason
           FROM {TABLE_OUTFIT_RULES}
           WHERE UPPER(anchor_category) = UPPER(?)
             AND (gender_target = ? OR gender_target = 'unisex' OR gender_target = 'all')""",
        (anchor_cat, target_gender)
    )
    rows = cur.fetchall()
    conn.close()

    rules = []
    for r in rows:
        rules.append({
            "rule_id": r["rule_id"],
            "occ": r["occasion"],
            "role": r["match_role"],
            "target_cat": r["target_category"],
            "ai_reason": r["ai_reason"] or "",
        })
    return rules

def simple_score(anchor: dict, target: dict) -> int:
    """Simplified score for testing."""
    score = 50
    anchor_color = anchor["color"] or ""
    target_color = target["color"] or ""

    # Simple color harmony
    if anchor_color and target_color and anchor_color.lower() == target_color.lower():
        score += 20
    elif target_color and "đen" in target_color.lower():
        score += 10

    if target.get("is_new_product"):
        score += 20
    return score

def generate_reason(anchor: dict, target: dict, rule: dict) -> str:
    return f"Màu {target['color']} của {target['product_line']} phối cùng {anchor['color']}. {rule['ai_reason']}"

def test_insert_one_product():
    """Test insert matches for first adult product with rules."""
    catalog = get_catalog_from_sqlite()
    if not catalog:
        print("❌ No catalog found")
        return False

    # Find first ADULT anchor with rules (avoid kid anchors)
    anchor = None
    for p in catalog:
        age = (p["age_group"] or "").lower()
        gender = p["gender"] or ""
        # Skip kids
        if any(k in age for k in ["kid", "trẻ em", "be_trai", "be_gai"]):
            continue
        if any(k in gender for k in ["boy", "girl", "bé trai", "bé gái"]):
            continue
        rules = fetch_rules_for_anchor(p["product_line"], p["gender"])
        if rules:
            anchor = p
            break

    if not anchor:
        print("❌ No adult anchor with rules found")
        return False

    print(f"🎯 Testing with anchor: {anchor['code']} - {anchor['product_line']} ({anchor['gender']})")

    rules = fetch_rules_for_anchor(anchor["product_line"], anchor["gender"])
    print(f"📜 Found {len(rules)} rules")
    for r in rules:
        print(f"   • Rule {r['rule_id']}: {anchor['product_line']} → {r['target_cat']} [{r['occ']}]")

    # Build targets by category
    targets_by_cat = {}
    for t in catalog:
        if t["code"] == anchor["code"]:
            continue
        cat = t["product_line"]
        if cat not in targets_by_cat:
            targets_by_cat[cat] = []
        targets_by_cat[cat].append(t)

    print(f"\n📦 Target categories found: {list(targets_by_cat.keys())[:10]}")

    # Compute matches
    rows = []
    for rule in rules:
        target_cat = rule["target_cat"]
        targets = targets_by_cat.get(target_cat, [])
        print(f"   🔍 Looking for target category '{target_cat}': {len(targets)} items")
        for target in targets:
            # Age filter
            anchor_age = (anchor["age_group"] or "").strip().lower()
            target_age = (target["age_group"] or "").strip().lower()
            print(f"      → Candidate: {target['code']} ({target['product_line']}), age_group='{target_age}'")
            if anchor_age and target_age and anchor_age != target_age:
                print(f"         ✗ Age filter: anchor='{anchor_age}' != target='{target_age}'")
                continue

            score = simple_score(anchor, target)
            print(f"         Score: {score}")
            if score < 35:
                print(f"         ✗ Score too low (<35)")
                continue

            reason = generate_reason(anchor, target, rule)
            print(f"         ✓ MATCH! score={score}")

            rows.append((
                rule["rule_id"],
                anchor["code"],
                target["code"],
                target["name"],
                target["gender"],
                target["age_group"],
                rule["role"],
                score,
                reason,
                rule["occ"],
            ))
        for target in targets:
            # Age filter
            anchor_age = (anchor["age_group"] or "").strip().lower()
            target_age = (target["age_group"] or "").strip().lower()
            if anchor_age and target_age and anchor_age != target_age:
                continue

            score = simple_score(anchor, target)
            reason = generate_reason(anchor, target, rule)

            rows.append((
                rule["rule_id"],
                anchor["code"],
                target["code"],
                target["name"],
                target["gender"],
                target["age_group"],
                rule["role"],
                score,
                reason,
                rule["occ"],
            ))

    # Insert to DB
    if rows:
        conn = sqlite3.connect(SQLITE_DB_PATH)
        cur = conn.cursor()
        cur.executemany('''
            INSERT INTO pg__dashboard_canifa__ai_outfit_product_matches
            (rule_id, anchor_product_code, match_product_code, match_product_name, match_gender, match_age, match_role, score, ai_reason, occasion)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', rows)
        conn.commit()
        conn.close()
        print(f"✅ Inserted {len(rows)} matches for {anchor['code']}")
        return True
    else:
        print("⚠️ No matches generated")
        return False

if __name__ == "__main__":
    test_insert_one_product()
