"""
i2i - 内容相似索引
基于商品属性（分类、供应商、属性等）计算物品相似度
"""
import sys
import os
sys.path.append(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))))

import pandas as pd
import numpy as np
import argparse
from datetime import datetime
from collections import defaultdict
from sklearn.feature_extraction.text import TfidfVectorizer
from sklearn.metrics.pairwise import cosine_similarity
from db_service import create_db_connection
from offline_tasks.config.offline_config import (
    DB_CONFIG, OUTPUT_DIR, DEFAULT_I2I_TOP_N
)
from offline_tasks.scripts.debug_utils import (
    setup_debug_logger, log_dataframe_info, log_dict_stats,
    save_readable_index, fetch_name_mappings, log_algorithm_params,
    log_processing_step
)


def fetch_product_features(engine):
    """
    获取商品特征数据
    """
    sql_query = """
    SELECT 
        pgs.id as item_id,
        pgs.name as item_name,
        pg.supplier_id,
        ss.name as supplier_name,
        pg.category_id,
        pc_1.id as category_level1_id,
        pc_1.name as category_level1,
        pc_2.id as category_level2_id,
        pc_2.name as category_level2,
        pc_3.id as category_level3_id,
        pc_3.name as category_level3,
        pc_4.id as category_level4_id,
        pc_4.name as category_level4,
        pgs.capacity,
        pgs.factory_no,
        po.name as package_type,
        po2.name as package_mode,
        pgs.fir_on_sell_time,
        pgs.status
    FROM prd_goods_sku pgs
    INNER JOIN prd_goods pg ON pg.id = pgs.goods_id
    INNER JOIN sup_supplier ss ON ss.id = pg.supplier_id
    LEFT JOIN prd_category as pc ON pc.id = pg.category_id
    LEFT JOIN prd_category AS pc_1 ON pc_1.id = SUBSTRING_INDEX(SUBSTRING_INDEX(pc.path, '.', 2), '.', -1)
    LEFT JOIN prd_category AS pc_2 ON pc_2.id = SUBSTRING_INDEX(SUBSTRING_INDEX(pc.path, '.', 3), '.', -1)
    LEFT JOIN prd_category AS pc_3 ON pc_3.id = SUBSTRING_INDEX(SUBSTRING_INDEX(pc.path, '.', 4), '.', -1)
    LEFT JOIN prd_category AS pc_4 ON pc_4.id = SUBSTRING_INDEX(SUBSTRING_INDEX(pc.path, '.', 5), '.', -1)
    LEFT JOIN prd_goods_sku_attribute pgsa ON pgs.id = pgsa.goods_sku_id 
        AND pgsa.attribute_id = (SELECT id FROM prd_attribute WHERE code = 'PKG' LIMIT 1)
    LEFT JOIN prd_option po ON po.id = pgsa.option_id
    LEFT JOIN prd_goods_sku_attribute pgsa2 ON pgs.id = pgsa2.goods_sku_id 
        AND pgsa2.attribute_id = (SELECT id FROM prd_attribute WHERE code = 'pkg_mode' LIMIT 1)
    LEFT JOIN prd_option po2 ON po2.id = pgsa2.option_id
    WHERE pgs.status IN (2, 4, 5)
        AND pgs.is_delete = 0
    """
    
    print("Executing SQL query...")
    df = pd.read_sql(sql_query, engine)
    print(f"Fetched {len(df)} products")
    return df


def build_feature_text(row):
    """
    构建商品的特征文本
    """
    features = []
    
    # 添加分类信息（权重最高，重复多次）
    if pd.notna(row['category_level1']):
        features.extend([str(row['category_level1'])] * 5)
    if pd.notna(row['category_level2']):
        features.extend([str(row['category_level2'])] * 4)
    if pd.notna(row['category_level3']):
        features.extend([str(row['category_level3'])] * 3)
    if pd.notna(row['category_level4']):
        features.extend([str(row['category_level4'])] * 2)
    
    # 添加供应商信息
    if pd.notna(row['supplier_name']):
        features.extend([str(row['supplier_name'])] * 2)
    
    # 添加包装信息
    if pd.notna(row['package_type']):
        features.append(str(row['package_type']))
    if pd.notna(row['package_mode']):
        features.append(str(row['package_mode']))
    
    # 添加商品名称的关键词（简单分词）
    if pd.notna(row['item_name']):
        name_words = str(row['item_name']).split()
        features.extend(name_words)
    
    return ' '.join(features)


def calculate_content_similarity(df, top_n=50):
    """
    基于内容计算相似度
    """
    print("Building feature texts...")
    df['feature_text'] = df.apply(build_feature_text, axis=1)
    
    print("Calculating TF-IDF...")
    vectorizer = TfidfVectorizer(max_features=1000)
    tfidf_matrix = vectorizer.fit_transform(df['feature_text'])
    
    print("Calculating cosine similarity...")
    # 分批计算相似度以节省内存
    batch_size = 1000
    result = {}
    
    for i in range(0, len(df), batch_size):
        end_i = min(i + batch_size, len(df))
        batch_similarity = cosine_similarity(tfidf_matrix[i:end_i], tfidf_matrix)
        
        for j, idx in enumerate(range(i, end_i)):
            item_id = df.iloc[idx]['item_id']
            similarities = batch_similarity[j]
            
            # 获取最相似的top_n个（排除自己）
            similar_indices = np.argsort(similarities)[::-1][1:top_n+1]
            similar_items = []
            
            for sim_idx in similar_indices:
                if similarities[sim_idx] > 0:  # 只保留有相似度的
                    similar_items.append((
                        df.iloc[sim_idx]['item_id'],
                        float(similarities[sim_idx])
                    ))
            
            if similar_items:
                result[item_id] = similar_items
        
        print(f"Processed {end_i}/{len(df)} products...")
    
    return result


def calculate_category_based_similarity(df):
    """
    基于分类的相似度（同类目下的商品）
    """
    result = defaultdict(list)
    
    # 按四级类目分组
    for cat4_id, group in df.groupby('category_level4_id'):
        if pd.isna(cat4_id) or len(group) < 2:
            continue
        
        items = group['item_id'].tolist()
        for item_id in items:
            other_items = [x for x in items if x != item_id]
            # 同四级类目的商品相似度设为0.9
            result[item_id].extend([(x, 0.9) for x in other_items[:50]])
    
    # 按三级类目分组（补充）
    for cat3_id, group in df.groupby('category_level3_id'):
        if pd.isna(cat3_id) or len(group) < 2:
            continue
        
        items = group['item_id'].tolist()
        for item_id in items:
            if item_id not in result or len(result[item_id]) < 50:
                other_items = [x for x in items if x != item_id]
                # 同三级类目的商品相似度设为0.7
                existing = {x[0] for x in result[item_id]}
                new_items = [(x, 0.7) for x in other_items if x not in existing]
                result[item_id].extend(new_items[:50 - len(result[item_id])])
    
    return result


def merge_similarities(sim1, sim2, weight1=0.7, weight2=0.3):
    """
    融合两种相似度
    """
    result = {}
    all_items = set(sim1.keys()) | set(sim2.keys())
    
    for item_id in all_items:
        similarities = defaultdict(float)
        
        # 添加第一种相似度
        if item_id in sim1:
            for similar_id, score in sim1[item_id]:
                similarities[similar_id] += score * weight1
        
        # 添加第二种相似度
        if item_id in sim2:
            for similar_id, score in sim2[item_id]:
                similarities[similar_id] += score * weight2
        
        # 排序并取top N
        sorted_sims = sorted(similarities.items(), key=lambda x: -x[1])[:50]
        if sorted_sims:
            result[item_id] = sorted_sims
    
    return result


def main():
    parser = argparse.ArgumentParser(description='Calculate content-based item similarity')
    parser.add_argument('--top_n', type=int, default=DEFAULT_I2I_TOP_N,
                       help=f'Top N similar items to output (default: {DEFAULT_I2I_TOP_N})')
    parser.add_argument('--method', type=str, default='hybrid',
                       choices=['tfidf', 'category', 'hybrid'],
                       help='Similarity calculation method')
    parser.add_argument('--output', type=str, default=None,
                       help='Output file path')
    parser.add_argument('--debug', action='store_true',
                       help='Enable debug mode with detailed logging and readable output')

    args = parser.parse_args()
    
    # 设置logger
    logger = setup_debug_logger('i2i_content_similar', debug=args.debug)
    
    # 记录算法参数
    params = {
        'top_n': args.top_n,
        'method': args.method,
        'debug': args.debug
    }
    log_algorithm_params(logger, params)
    
    # 创建数据库连接
    logger.info("连接数据库...")
    engine = create_db_connection(
        DB_CONFIG['host'],
        DB_CONFIG['port'],
        DB_CONFIG['database'],
        DB_CONFIG['username'],
        DB_CONFIG['password']
    )
    
    # 获取商品特征
    log_processing_step(logger, "获取商品特征")
    df = fetch_product_features(engine)
    logger.info(f"获取到 {len(df)} 个商品的特征数据")
    log_dataframe_info(logger, df, "商品特征数据")
    
    # 计算相似度
    log_processing_step(logger, f"计算相似度 (方法: {args.method})")
    if args.method == 'tfidf':
        logger.info("使用 TF-IDF 方法...")
        result = calculate_content_similarity(df, args.top_n)
    elif args.method == 'category':
        logger.info("使用基于分类的方法...")
        result = calculate_category_based_similarity(df)
    else:  # hybrid
        logger.info("使用混合方法 (TF-IDF 70% + 分类 30%)...")
        tfidf_sim = calculate_content_similarity(df, args.top_n)
        category_sim = calculate_category_based_similarity(df)
        result = merge_similarities(tfidf_sim, category_sim, weight1=0.7, weight2=0.3)
    
    logger.info(f"为 {len(result)} 个物品生成了相似度")
    
    # 输出结果
    log_processing_step(logger, "保存结果")
    output_file = args.output or os.path.join(
        OUTPUT_DIR, 
        f'i2i_content_{args.method}_{datetime.now().strftime("%Y%m%d")}.txt'
    )
    
    # 获取name mappings
    name_mappings = {}
    if args.debug:
        logger.info("获取物品名称映射...")
        name_mappings = fetch_name_mappings(engine, debug=True)
    
    logger.info(f"写入结果到 {output_file}...")
    with open(output_file, 'w', encoding='utf-8') as f:
        for item_id, sims in result.items():
            # 使用name_mappings获取名称
            item_name = name_mappings.get(item_id, 'Unknown')
            if item_name == 'Unknown' and 'item_name' in df.columns:
                item_name = df[df['item_id'] == item_id]['item_name'].iloc[0] if len(df[df['item_id'] == item_id]) > 0 else 'Unknown'
            
            if not sims:
                continue
            
            # 格式：item_id \t item_name \t similar_item_id1:score1,similar_item_id2:score2,...
            sim_str = ','.join([f'{sim_id}:{score:.4f}' for sim_id, score in sims])
            f.write(f'{item_id}\t{item_name}\t{sim_str}\n')
    
    logger.info(f"完成！为 {len(result)} 个物品生成了基于内容的相似度")
    logger.info(f"输出保存到：{output_file}")
    
    # 如果启用debug模式，保存可读格式
    if args.debug:
        log_processing_step(logger, "保存Debug可读格式")
        save_readable_index(
            output_file,
            result,
            name_mappings,
            index_type=f'i2i:content:{args.method}',
            logger=logger
        )


if __name__ == '__main__':
    main()