import sqlite3
import os
import time
import sys
import io

# É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_connections():
    conn_dump = sqlite3.connect(SQLITE_DB_PATH)
    conn_dump.row_factory = sqlite3.Row
    
    # Rules cũng nằm trong cùng DB sau khi migration
    conn_rules = sqlite3.connect(SQLITE_DB_PATH)
    conn_rules.row_factory = sqlite3.Row
    
    return conn_dump, conn_rules

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_dump, conn_123 = get_db_connections()
    cursor_dump = conn_dump.cursor()
    cursor_123 = conn_123.cursor()
    
    # 1. Lấy danh sách Rules
    cursor_123.execute("SELECT * FROM pg__dashboard_canifa__chatbot_fashion_rules")
    rules = cursor_123.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_123.execute("DELETE FROM pg__dashboard_canifa__ai_outfit_product_matches")
        conn_123.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, 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_dump.execute(query)
    anchors = cursor_dump.fetchall()
    
    for anchor in anchors:
        print(f"\n📍 Đang xử lý Anchor: {anchor['magento_ref_code']} - {anchor['product_name']} (Màu: {anchor['master_color']})")
        
        # Tìm rule phù hợp
        applicable_rules = [r for r in rules if r['anchor_category'].lower() == (anchor['product_line_vn'] or '').lower()]
        
        # 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'].lower() for r in applicable_rules]))
            
            # Use dynamically built IN clause
            placeholders = ', '.join(['?'] * len(target_cats))
            query_params = target_cats + [gender]
            
            cursor_dump.execute(f'''
                SELECT magento_ref_code, product_name, master_color, is_new_product, product_line_vn
                FROM sr__test_db__magento_product_dimension_with_text_embedding 
                WHERE LOWER(product_line_vn) IN ({placeholders}) AND gender_by_product = ?
            ''', query_params)
            
            all_targets = cursor_dump.fetchall()
            for t in all_targets:
                cat_key = (t['product_line_vn'] or '').lower()
                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'].lower()
            targets = targets_by_cat.get(target_cat, [])
            
            scored_matches = []
            for target in targets:
                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'],
                    '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']} - Màu {match['color']}) | ĐIỂM: {match['score']}")
                    print(f"          Lý do: {match['reason']}")
            elif top_matches:
                # Insert vào database
                cursor_123.executemany('''
                    INSERT INTO pg__dashboard_canifa__ai_outfit_product_matches 
                    (rule_id, anchor_product_code, match_product_code, match_role, score, ai_reason)
                    VALUES (?, ?, ?, ?, ?, ?)
                ''', [(rule['id'], anchor['magento_ref_code'], match['product_code'], rule['match_role'], match['score'], match['reason']) for match in top_matches])
                    
        if not dry_run:
            conn_123.commit()
                    
    if not dry_run:
        print("\n✅ Đã lưu kết quả vào Database!")
        
    conn_dump.close()
    conn_123.close()
    print("\n🎉 HOÀN THÀNH!")

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