import io
import sqlite3
import sys

# Ép kiểu stdout thành utf-8 trên Windows để tránh lỗi charmap khi in emoji
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8')

# Color matching logic (simplified for demonstration)
COLOR_HARMONY = {
    'Be': ['Xanh Than', 'Đen', 'Be'],
    'Trắng': ['Đen', 'Xanh Than', 'Xám', 'Xanh'],
    'Đen': ['Trắng', 'Xám', 'Be'],
}

from common.constants import SQLITE_DB_PATH


def get_db_connection():
    conn = sqlite3.connect(SQLITE_DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def score_product(anchor, match):
    score = 50.0 # Base score
    
    # 1. Color Harmony (+0 to +30)
    anchor_color = anchor['master_color'] or ''
    match_color = match['master_color'] or ''
    
    if anchor_color in COLOR_HARMONY and match_color in COLOR_HARMONY[anchor_color]:
        if match_color == 'Xanh Than': score += 30
        elif match_color == 'Đen': score += 20
        else: score += 10

    # 2. Business Value (+0 to +20)
    if match['is_new_product'] == 1:
        score += 20
        
    return score

def generate_ai_reason(anchor, match, rule):
    # Dùng Cách 1: Template Ghép chuỗi
    anchor_color = anchor['master_color'] or 'cơ bản'
    match_color = match['master_color'] or 'cơ bản'
    
    rule_reason = rule['ai_reason'] or 'Tạo phong cách hài hòa, phù hợp cho nhiều dịp.'
    
    return f"Màu {match_color} của {match['product_line_vn']} phối cùng màu {anchor_color} tạo điểm nhấn hoàn hảo. {rule_reason}"

def run_worker(dry_run=True, anchor_limit=5):
    print("==================================================")
    print(f"👕 CANIFA AI STYLIST WORKER (DRY_RUN={dry_run})")
    print("==================================================")
    
    conn = get_db_connection()
    cursor = conn.cursor()
    
    # 1. Lấy danh sách Rules
    cursor.execute("SELECT rowid as id, * FROM pg__dashboard_canifa__chatbot_fashion_rules")
    rules = cursor.fetchall()
    print(f"✅ Đã tải {len(rules)} Fashion Rules.")
    
    if not dry_run:
        print("🧹 Đang dọn dẹp dữ liệu cũ trong bảng matches...")
        cursor.execute("DELETE FROM pg__dashboard_canifa__ai_outfit_product_matches")
        conn.commit()
    
    # Lấy TẤT CẢ sản phẩm gốc (Anchor) để chạy full
    query = '''
        SELECT magento_ref_code, product_line_vn, master_color, gender_by_product, age_by_product, product_name 
        FROM sr__test_db__magento_product_dimension_with_text_embedding 
        WHERE gender_by_product IS NOT NULL AND product_line_vn IS NOT NULL
    '''
    if anchor_limit and anchor_limit > 0:
        query += f" LIMIT {anchor_limit}"
        
    cursor.execute(query)
    anchors = cursor.fetchall()
    
    for anchor in anchors:
        print(f"\n📍 Đang xử lý Anchor: {anchor['magento_ref_code']} - {anchor['product_name']} (Màu: {anchor['master_color']} | Tuổi: {anchor['age_by_product']})")
        
        # Tìm rule phù hợp (exact match, no LOWER — SQLite LOWER() breaks Vietnamese Unicode)
        applicable_rules = [r for r in rules if r['anchor_category'] == (anchor['product_line_vn'] or '')]
        
        # Pre-fetch all targets for applicable rules to avoid N+1 SELECT
        targets_by_cat = {}
        if applicable_rules:
            gender = anchor['gender_by_product']
            target_cats = list(set([r['target_category'] for r in applicable_rules]))
            
            # Use dynamically built IN clause
            placeholders = ', '.join(['?'] * len(target_cats))
            query_params = target_cats + [gender]
            
            # Allow ALL anchors to match with unisex targets too
            if gender == 'unisex':
                cursor.execute(f'''
                    SELECT magento_ref_code, product_name, master_color, is_new_product, product_line_vn, age_by_product, gender_by_product
                    FROM sr__test_db__magento_product_dimension_with_text_embedding 
                    WHERE product_line_vn IN ({placeholders})
                ''', target_cats)
            else:
                cursor.execute(f'''
                    SELECT magento_ref_code, product_name, master_color, is_new_product, product_line_vn, age_by_product, gender_by_product
                    FROM sr__test_db__magento_product_dimension_with_text_embedding 
                    WHERE product_line_vn IN ({placeholders}) AND (gender_by_product = ? OR gender_by_product = 'unisex')
                ''', query_params)
            
            all_targets = cursor.fetchall()
            for t in all_targets:
                cat_key = t['product_line_vn'] or ''
                if cat_key not in targets_by_cat:
                    targets_by_cat[cat_key] = []
                targets_by_cat[cat_key].append(t)
        
        for rule in applicable_rules:
            print(f"   ⚙️ Áp dụng Rule ID {rule['id']}: Cần tìm '{rule['target_category']}' (Vai trò: {rule['match_role']})")
            
            target_cat = rule['target_category']
            targets = targets_by_cat.get(target_cat, [])
            
            scored_matches = []
            for target in targets:
                # 🛑 NGHIÊM CẤM PHỐI LỘN TUỔI (Trẻ em vs Người lớn)
                anchor_age = (anchor['age_by_product'] or '').strip().lower()
                target_age = (target['age_by_product'] or '').strip().lower()
                if anchor_age and target_age and anchor_age != target_age:
                    continue
                    
                score = score_product(anchor, target)
                reason = generate_ai_reason(anchor, target, rule)
                scored_matches.append({
                    'product_code': target['magento_ref_code'],
                    'name': target['product_name'],
                    'color': target['master_color'],
                    'gender': target['gender_by_product'],
                    'age': target['age_by_product'],
                    'score': score,
                    'reason': reason
                })
            
            # Xếp hạng và cắt top 3
            scored_matches.sort(key=lambda x: x['score'], reverse=True)
            top_matches = scored_matches[:3]
            
            if dry_run:
                for rank, match in enumerate(top_matches, 1):
                    print(f"      [{rank}] ➔ {match['product_code']} ({match['name']} - {match['gender']} {match['age']}) | ĐIỂM: {match['score']}")
                    print(f"          Lý do: {match['reason']}")
            elif top_matches:
                # Insert vào database
                cursor.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)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                ''', [(
                    rule['id'], 
                    anchor['magento_ref_code'], 
                    match['product_code'], 
                    match['name'],
                    match['gender'],
                    match['age'],
                    rule['match_role'], 
                    match['score'], 
                    match['reason']
                ) for match in top_matches])
                    
        if not dry_run:
            conn.commit()
                    
    if not dry_run:
        print("\n✅ Đã lưu kết quả vào Database!")
        
    conn.close()
    print("\n🎉 HOÀN THÀNH!")

if __name__ == '__main__':
    run_worker(dry_run=False, anchor_limit=None)
