"""
列映射处理器

提供列名映射和转换功能，支持不同供应商的列名标准化
"""

import re
import pandas as pd
from typing import Dict, Any, Optional, List, Union
from app.core.utils.log_utils import get_logger

logger = get_logger(__name__)


class ColumnMapper:
    """列映射处理器
    
    提供列名标准化功能，将不同供应商的列名映射到标准列名
    """
    
    # 标准列名定义（所有列名别名的唯一来源）
    STANDARD_COLUMNS = {
        'barcode': [
            '条码', '条形码', '商品条码', '商品条形码', '产品条码', '商品编码',
            '商品编号', '条码（必填）', '电脑条码', '条码ID', '单品条码',
            'barcode', 'Barcode', 'BarCode', 'code', '编码',
        ],
        'name': [
            '商品名称', '产品名称', '名称', '商品', '产品', '商品名', '品名',
            '品项名', '商品或服务名称', '品项', '名 称',
            'name', 'product_name',
        ],
        'specification': [
            '规格', '规格型号', '型号', '商品规格', '产品规格', '包装规格', '规 格',
            'specification', 'spec', 'model',
        ],
        'quantity': [
            '数量', '采购量', '订货数量', '订单量', '需求量', '采购数量', '购买数量',
            '订单数量', '数量（必填）', '采购量（必填）', '入库数', '入库数量', '数 量',
            'quantity', 'qty',
        ],
        'unit': [
            '单位', '计量单位', '采购单位', '单位（必填）', '单位名称', '计价单位', '单 位',
            'unit', 'units',
        ],
        'unit_price': [
            '单价', '价格', '采购单价', '进货价', '销售价', '采购价', '参考价',
            '入库单价', '单价（必填）', '采购单价（必填）', '价格（必填）', '单 价',
            'unit_price', 'price',
        ],
        'total_price': [
            '总价', '金额', '小计', '合计金额', '小计金额', '金额（元）',
            '金额合计', '合计', '总额',
            'total_price', 'total', 'amount',
        ],
        'gift_quantity': [
            '赠送量', '赠品数量', '赠送数量', '赠品',
        ],
        'category': ['类别', '分类', '商品类别', 'category', 'type'],
        'brand': ['品牌', '商标', 'brand'],
        'supplier': ['供应商', '供货商', 'supplier', 'vendor'],
    }
    
    def __init__(self, mapping_config: Optional[Dict[str, Any]] = None):
        """初始化列映射器
        
        Args:
            mapping_config: 映射配置
        """
        self.mapping_config = mapping_config or {}
        self.custom_mappings = {}
        self._build_reverse_mapping()
    
    def _build_reverse_mapping(self):
        """构建反向映射表"""
        self.reverse_mapping = {}
        
        # 添加标准列的反向映射
        for standard_name, variations in self.STANDARD_COLUMNS.items():
            for variation in variations:
                self.reverse_mapping[variation.lower()] = standard_name
        
        # 添加自定义映射
        for standard_name, custom_names in self.mapping_config.items():
            if isinstance(custom_names, str):
                custom_names = [custom_names]
            
            for custom_name in custom_names:
                self.reverse_mapping[custom_name.lower()] = standard_name
                self.custom_mappings[custom_name.lower()] = standard_name
    
    def map_columns(self, df: pd.DataFrame, target_columns: Optional[List[str]] = None) -> pd.DataFrame:
        """映射列名
        
        Args:
            df: 输入数据
            target_columns: 目标列名列表，如果为None则使用所有标准列
            
        Returns:
            列名映射后的数据
        """
        if target_columns is None:
            target_columns = list(self.STANDARD_COLUMNS.keys())
        
        logger.info(f"开始列名映射，目标列: {target_columns}")
        logger.info(f"原始列名: {list(df.columns)}")
        
        # 创建列名映射
        column_mapping = {}
        used_columns = set()
        
        for target_col in target_columns:
            # 查找匹配的原始列名
            matched_column = self._find_matching_column(df.columns, target_col)
            if matched_column:
                column_mapping[matched_column] = target_col
                used_columns.add(matched_column)
                logger.debug(f"列名映射: {matched_column} -> {target_col}")
        
        # 重命名列
        if column_mapping:
            df_mapped = df.rename(columns=column_mapping)
            
            # 添加缺失的目标列
            for target_col in target_columns:
                if target_col not in df_mapped.columns:
                    df_mapped[target_col] = self._get_default_value(target_col)
                    logger.debug(f"添加缺失列: {target_col}")
            
            # 只保留目标列
            existing_target_columns = [col for col in target_columns if col in df_mapped.columns]
            df_result = df_mapped[existing_target_columns]
            
            logger.info(f"列名映射完成，结果列名: {list(df_result.columns)}")
            return df_result
        else:
            logger.warning("没有找到可映射的列名")
            return df
    
    def _find_matching_column(self, columns: List[str], target_column: str) -> Optional[str]:
        """查找匹配的列名
        
        Args:
            columns: 原始列名列表
            target_column: 目标标准列名
            
        Returns:
            匹配的原始列名或None
        """
        # 获取目标列的所有可能变体
        possible_names = []
        
        # 标准列名变体
        if target_column in self.STANDARD_COLUMNS:
            possible_names.extend(self.STANDARD_COLUMNS[target_column])
        
        # 自定义映射
        for standard_name, custom_names in self.mapping_config.items():
            if standard_name == target_column:
                if isinstance(custom_names, str):
                    possible_names.append(custom_names)
                else:
                    possible_names.extend(custom_names)
        
        # 查找匹配
        for possible_name in possible_names:
            # 精确匹配（忽略大小写）
            for column in columns:
                if column.lower() == possible_name.lower():
                    return column
            
            # 模糊匹配
            for column in columns:
                if possible_name.lower() in column.lower() or column.lower() in possible_name.lower():
                    return column
        
        return None
    
    def _get_default_value(self, column_name: str) -> Any:
        """获取列的默认值
        
        Args:
            column_name: 列名
            
        Returns:
            默认值
        """
        # 根据列名类型返回合适的默认值
        if column_name in ['quantity', 'unit_price', 'total_price']:
            return 0
        elif column_name in ['barcode', 'name', 'specification', 'unit', 'category', 'brand', 'supplier']:
            return ''
        else:
            return None
    
    def add_custom_mapping(self, standard_name: str, custom_names: Union[str, List[str]]):
        """添加自定义列名映射
        
        Args:
            standard_name: 标准列名
            custom_names: 自定义列名或列名列表
        """
        if isinstance(custom_names, str):
            custom_names = [custom_names]
        
        # 更新配置
        self.mapping_config[standard_name] = custom_names
        
        # 更新反向映射
        for custom_name in custom_names:
            self.reverse_mapping[custom_name.lower()] = standard_name
            self.custom_mappings[custom_name.lower()] = standard_name
        
        logger.info(f"添加自定义映射: {standard_name} <- {custom_names}")
    
    def detect_column_types(self, df: pd.DataFrame) -> Dict[str, str]:
        """检测列的数据类型
        
        Args:
            df: 数据
            
        Returns:
            列类型字典
        """
        column_types = {}
        
        for column in df.columns:
            if pd.api.types.is_numeric_dtype(df[column]):
                column_types[column] = 'numeric'
            elif pd.api.types.is_datetime64_any_dtype(df[column]):
                column_types[column] = 'datetime'
            elif pd.api.types.is_bool_dtype(df[column]):
                column_types[column] = 'boolean'
            else:
                column_types[column] = 'text'
        
        return column_types
    
    def suggest_column_mapping(self, df: pd.DataFrame) -> Dict[str, List[str]]:
        """建议列名映射
        
        Args:
            df: 数据
            
        Returns:
            建议的映射关系
        """
        suggestions = {}
        
        for column in df.columns:
            column_lower = column.lower()
            suggestions[column] = []
            
            # 检查标准列名
            for standard_name, variations in self.STANDARD_COLUMNS.items():
                for variation in variations:
                    if column_lower in variation.lower() or variation.lower() in column_lower:
                        suggestions[column].append(standard_name)
            
            # 检查自定义映射
            for custom_name, standard_name in self.custom_mappings.items():
                if column_lower in custom_name or custom_name in column_lower:
                    suggestions[column].append(standard_name)
            
            # 去重
            suggestions[column] = list(set(suggestions[column]))
        
        # 只返回有建议的列
        return {k: v for k, v in suggestions.items() if v}
    
    def validate_mapping(self, df: pd.DataFrame, required_columns: List[str]) -> Dict[str, Any]:
        """验证列映射结果
        
        Args:
            df: 映射后的数据
            required_columns: 必需的列名列表
            
        Returns:
            验证结果
        """
        result = {
            'valid': True,
            'missing_columns': [],
            'empty_columns': [],
            'warnings': []
        }
        
        # 检查缺失列
        for col in required_columns:
            if col not in df.columns:
                result['missing_columns'].append(col)
                result['valid'] = False
        
        # 检查空列
        for col in df.columns:
            if df[col].isnull().all():
                result['empty_columns'].append(col)
                result['warnings'].append(f"列 '{col}' 全部为空值")
        
        # 检查数值列
        numeric_columns = ['quantity', 'unit_price', 'total_price']
        for col in numeric_columns:
            if col in df.columns and not pd.api.types.is_numeric_dtype(df[col]):
                result['warnings'].append(f"列 '{col}' 不是数值类型")
        
        return result

    @classmethod
    def find_column(cls, columns: List[str], standard_name: str) -> Optional[str]:
        """在列名列表中查找匹配标准列名的列

        匹配策略: 精确匹配 → 忽略空白匹配 → 子串匹配

        Args:
            columns: 实际列名列表
            standard_name: 标准列名 (STANDARD_COLUMNS 的键)

        Returns:
            匹配到的实际列名，未找到返回 None
        """
        candidates = cls.STANDARD_COLUMNS.get(standard_name, [])
        if not candidates:
            return None

        columns_str = [str(c) for c in columns]

        # 精确匹配
        for col in columns_str:
            col_clean = col.strip()
            for candidate in candidates:
                if col_clean == candidate:
                    return col

        # 忽略空白匹配
        for col in columns_str:
            col_clean = re.sub(r'\s+', '', col.strip())
            for candidate in candidates:
                if col_clean == re.sub(r'\s+', '', candidate):
                    return col

        # 子串匹配 (候选名包含在列名中)
        for col in columns_str:
            col_lower = col.strip().lower()
            for candidate in candidates:
                if candidate.lower() in col_lower:
                    return col

        return None

    @staticmethod
    def detect_header_row(df: pd.DataFrame, max_rows: int = 10, min_matches: int = 3) -> int:
        """检测表头所在行

        扫描前 max_rows 行，返回包含最多关键词匹配的行索引。

        Args:
            df: 数据框
            max_rows: 最大扫描行数
            min_matches: 最少关键词匹配数

        Returns:
            表头行索引，未找到返回 -1
        """
        header_keywords = [
            '条码', '条形码', '商品条码', '商品名称', '名称', '规格',
            '单价', '数量', '金额', '单位', '必填', '编码', '单品条码', '序号',
        ]

        best_row = -1
        best_matches = 0

        for row_idx in range(min(max_rows, len(df))):
            row_values = df.iloc[row_idx].astype(str)
            matches = sum(
                1 for kw in header_keywords
                if any(kw in str(val) for val in row_values.values)
            )
            if matches >= min_matches and matches > best_matches:
                best_matches = matches
                best_row = row_idx

        return best_row