"""
待采购订单服务 - 基于原始订单和产品主表
直接从原始订单获取待采购数据，结合产品主表的标准化信息展示

⚠️ 注意：此服务仅用于查询展示，不用于数据生成
建议统一使用 ProcurementOrderServiceV2 以保持一致性
"""

import logging
import warnings
from typing import Dict, List, Optional, Any
from datetime import datetime
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, func, and_, or_, desc, asc, text
from sqlalchemy.orm import selectinload

from app.models.raw_orders import RawOrder
from app.models.products_master import ProductMaster
from app.utils.sku_generator import generate_sku_key

logger = logging.getLogger(__name__)


class ProcurementOrderServiceRaw:
    """基于原始订单的待采购订单服务 - 仅用于查询"""
    
    def __init__(self):
        """初始化时发出提示"""
        logger.info("使用 ProcurementOrderServiceRaw 进行查询。建议考虑统一使用 ProcurementOrderServiceV2")
    
    async def get_procurement_orders(
        self,
        db: AsyncSession,
        procurement_method: Optional[str] = None,
        brand: Optional[str] = None,
        status: Optional[str] = None,
        urgent_only: bool = False,
        page: int = 1,
        page_size: int = 50,
        sort_by: Optional[str] = None,
        sort_order: Optional[str] = None
    ) -> Dict[str, Any]:
        """
        获取待采购订单列表
        从原始订单获取数据，结合产品主表的标准化信息
        """
        
        # 基础查询条件 - 待采购订单的条件
        base_conditions = [
            RawOrder.付款时间.isnot(None),
            or_(
                RawOrder.交易状态 == '已付款',
                RawOrder.交易状态 == '部分发货'
            ),
            RawOrder.退款状态.in_(['正常', '退款中', None, ''])
        ]
        
        # 品牌筛选
        if brand:
            # 先从产品主表找到该品牌的所有产品名称
            brand_products_query = select(ProductMaster.线上宝贝名称).where(
                ProductMaster.品牌 == brand
            ).distinct()
            brand_products_result = await db.execute(brand_products_query)
            product_names = [row[0] for row in brand_products_result.fetchall()]
            
            if product_names:
                base_conditions.append(RawOrder.线上宝贝名称.in_(product_names))
            else:
                # 如果产品主表没有该品牌，返回空结果
                return {
                    'orders': [],
                    'pagination': {
                        'page': page,
                        'page_size': page_size,
                        'total': 0,
                        'total_pages': 0
                    }
                }
        
        # 采购方式筛选
        if procurement_method:
            # 从产品主表找到该采购方式的所有SKU
            method_products_query = select(
                ProductMaster.线上宝贝名称,
                ProductMaster.线上销售属性
            ).where(
                ProductMaster.procurement_method == procurement_method
            ).distinct()
            method_products_result = await db.execute(method_products_query)
            
            # 构建SKU匹配条件
            sku_conditions = []
            for product_name, sales_attr in method_products_result.fetchall():
                sku_conditions.append(
                    and_(
                        RawOrder.线上宝贝名称 == product_name,
                        RawOrder.线上销售属性 == sales_attr
                    )
                )
            
            if sku_conditions:
                base_conditions.append(or_(*sku_conditions))
            else:
                return {
                    'orders': [],
                    'pagination': {
                        'page': page,
                        'page_size': page_size,
                        'total': 0,
                        'total_pages': 0
                    }
                }
        
        # 构建计数查询
        count_query = select(func.count(RawOrder.id)).where(and_(*base_conditions))
        total_result = await db.execute(count_query)
        total = total_result.scalar() or 0
        
        # 计算分页
        total_pages = (total + page_size - 1) // page_size
        offset = (page - 1) * page_size
        
        # 构建主查询
        query = select(RawOrder).where(and_(*base_conditions))
        
        # 排序
        if sort_by:
            sort_column = getattr(RawOrder, sort_by, None)
            if sort_column:
                if sort_order == 'asc':
                    query = query.order_by(asc(sort_column))
                else:
                    query = query.order_by(desc(sort_column))
        else:
            # 默认按付款时间倒序
            query = query.order_by(desc(RawOrder.付款时间))
        
        # 分页
        query = query.offset(offset).limit(page_size)
        
        # 执行查询
        result = await db.execute(query)
        raw_orders = result.scalars().all()
        
        # 获取所有相关的产品主表数据
        product_data_map = await self._get_product_master_data(db, raw_orders)
        
        # 构建返回数据
        orders = []
        for raw_order in raw_orders:
            # 生成SKU键
            sku_key = generate_sku_key(
                raw_order.线上宝贝名称,
                raw_order.线上销售属性
            )
            
            # 获取产品主表的标准化数据
            product_info = product_data_map.get(sku_key, {})
            
            order_data = {
                # 原始订单数据
                '原始订单编号': raw_order.原始订单编号,
                '线上宝贝名称': raw_order.线上宝贝名称,
                '线上销售属性': raw_order.线上销售属性,
                '付款时间': raw_order.付款时间.isoformat() if raw_order.付款时间 else None,
                '交易状态': raw_order.交易状态,
                '退款状态': raw_order.退款状态,
                '数量': raw_order.数量 or 0,
                '订单单价': float(raw_order.订单单价) if raw_order.订单单价 else 0,
                '订单金额': float(raw_order.订单金额) if raw_order.订单金额 else 0,
                '网店名称': raw_order.网店名称,
                
                # 产品主表的标准化数据
                '品牌': product_info.get('品牌', ''),
                '货号': product_info.get('货号', ''),
                '颜色': product_info.get('颜色', ''),
                '尺寸': product_info.get('尺寸', ''),
                'procurement_method': product_info.get('procurement_method', '其他'),
                'is_urgent': product_info.get('is_urgent', False),
                'priority_score': product_info.get('priority_score', 50),
                'sku_key': sku_key,
                
                # 图片数据
                'image_urls': product_info.get('image_urls', []),
                'has_local_images': product_info.get('has_local_images', False)
            }
            
            orders.append(order_data)
        
        return {
            'orders': orders,
            'pagination': {
                'page': page,
                'page_size': page_size,
                'total': total,
                'total_pages': total_pages
            }
        }
    
    async def _get_product_master_data(
        self, 
        db: AsyncSession, 
        raw_orders: List[RawOrder]
    ) -> Dict[str, Dict[str, Any]]:
        """
        获取原始订单对应的产品主表数据
        """
        product_data_map = {}
        
        # 收集所有SKU键
        sku_keys = []
        for order in raw_orders:
            sku_key = generate_sku_key(
                order.线上宝贝名称,
                order.线上销售属性
            )
            sku_keys.append(sku_key)
        
        if not sku_keys:
            return product_data_map
        
        # 批量查询产品主表
        query = select(ProductMaster).where(
            ProductMaster.sku_key.in_(sku_keys)
        )
        result = await db.execute(query)
        products = result.scalars().all()
        
        # 构建映射
        for product in products:
            # 处理图片URL
            image_urls = []
            if product.main_image_path and product.image_download_status == 'completed':
                if product.main_image_path.startswith('images/'):
                    filename = product.main_image_path.replace('images/', '')
                    image_urls = [f"/api/v1/static/images/{filename}"]
                else:
                    image_urls = [f"/api/v1/static/images/{product.main_image_path}"]
            elif product.local_image_paths:
                # 解析本地图片路径
                try:
                    import json
                    paths = json.loads(product.local_image_paths)
                    if isinstance(paths, list):
                        image_urls = [f"/api/v1/static/images/{path}" for path in paths if path]
                except:
                    pass
            elif product.original_image_urls:
                # 使用原始图片URL作为备选
                try:
                    import json
                    urls = json.loads(product.original_image_urls)
                    if isinstance(urls, list):
                        image_urls = urls
                except:
                    pass
            
            product_data_map[product.sku_key] = {
                '品牌': product.品牌,
                '货号': product.货号,
                '颜色': product.颜色,
                '尺寸': product.尺寸,
                'procurement_method': product.procurement_method,
                'is_urgent': product.is_urgent,
                'priority_score': product.priority_score,
                'image_urls': image_urls,
                'has_local_images': product.image_download_status == 'completed'
            }
        
        return product_data_map
    
    async def get_procurement_orders_stats(
        self,
        db: AsyncSession
    ) -> Dict[str, Any]:
        """
        获取待采购订单统计
        基于原始订单和产品主表
        """
        
        # 基础统计 - 从procurement_orders表获取准确统计
        stats_query = text("""
            SELECT 
                COUNT(*) as total_orders,
                COUNT(DISTINCT 原始订单编号) as unique_order_numbers,
                SUM(数量) as total_quantity,
                SUM(订单金额) as total_amount,
                AVG(订单单价) as avg_price
            FROM procurement_orders
            WHERE procurement_status = 'PENDING'
        """)
        
        basic_stats = await db.execute(stats_query)
        stats_row = basic_stats.fetchone()
        
        # 品牌统计 - 从procurement_orders表获取品牌信息
        brand_stats_query = text("""
            SELECT 
                品牌 as brand,
                COUNT(DISTINCT 原始订单编号) as order_count,
                SUM(数量) as total_quantity,
                SUM(订单金额) as total_amount
            FROM procurement_orders
            WHERE procurement_status = 'PENDING'
            AND 品牌 IS NOT NULL
            GROUP BY 品牌
            ORDER BY order_count DESC
            LIMIT 10
        """)
        
        brand_result = await db.execute(brand_stats_query)
        brand_rows = brand_result.fetchall()
        
        # 采购方式统计
        method_stats_query = text("""
            SELECT 
                procurement_method as method,
                COUNT(DISTINCT 原始订单编号) as order_count,
                SUM(数量) as total_quantity,
                SUM(订单金额) as total_amount
            FROM procurement_orders
            WHERE procurement_status = 'PENDING'
            AND procurement_method IS NOT NULL
            GROUP BY procurement_method
            ORDER BY order_count DESC
        """)
        
        method_result = await db.execute(method_stats_query)
        method_rows = method_result.fetchall()
        
        # 获取总品牌数
        total_brands_query = text("""
            SELECT COUNT(DISTINCT 品牌) as total_brands
            FROM procurement_orders
            WHERE procurement_status = 'PENDING'
            AND 品牌 IS NOT NULL
        """)
        
        brands_result = await db.execute(total_brands_query)
        brands_row = brands_result.fetchone()
        
        return {
            'basic_stats': {
                'total_orders': stats_row.total_orders or 0,
                'unique_order_numbers': stats_row.unique_order_numbers or 0,
                'total_quantity': stats_row.total_quantity or 0,
                'total_amount': float(stats_row.total_amount) if stats_row.total_amount else 0,
                'avg_price': float(stats_row.avg_price) if stats_row.avg_price else 0,
                'total_brands': brands_row.total_brands or 0
            },
            'brand_distribution': [
                {
                    'brand': row.brand,
                    'order_count': row.order_count,
                    'total_quantity': row.total_quantity or 0,
                    'total_amount': float(row.total_amount) if row.total_amount else 0
                }
                for row in brand_rows
            ],
            'method_distribution': [
                {
                    'method': row.method,
                    'order_count': row.order_count,
                    'total_quantity': row.total_quantity or 0,
                    'total_amount': float(row.total_amount) if row.total_amount else 0
                }
                for row in method_rows
            ]
        }
    
    async def get_brands(
        self,
        db: AsyncSession,
        procurement_method: Optional[str] = None,
        include_counts: bool = False
    ) -> List[Any]:
        """
        获取所有待采购订单涉及的品牌列表
        综合 products_master 和 procurement_orders 两个表的数据

        Args:
            db: 数据库会话
            procurement_method: 可选的采购方式筛选
            include_counts: 是否包含品牌计数

        Returns:
            品牌列表（字符串列表或带计数的字典列表）
        """
        # 构建采购方式筛选条件
        method_condition_pm = "1=1"
        method_condition_po = "1=1"
        params = {}

        if procurement_method:
            method_condition_pm = "pm.procurement_method = :procurement_method"
            method_condition_po = "po.procurement_method = :procurement_method"
            params["procurement_method"] = procurement_method

        if include_counts:
            # 带计数的查询 - 合并两个表的品牌数据
            query = text(f"""
                SELECT brand, SUM(cnt) as count FROM (
                    -- 从 products_master 获取激活产品的品牌
                    SELECT pm.品牌 as brand, COUNT(*) as cnt
                    FROM products_master pm
                    WHERE pm.品牌 IS NOT NULL AND pm.品牌 != ''
                    AND pm.is_active = 1
                    AND {method_condition_pm}
                    GROUP BY pm.品牌

                    UNION ALL

                    -- 从 procurement_orders 获取待采购订单的品牌
                    SELECT po.品牌 as brand, COUNT(*) as cnt
                    FROM procurement_orders po
                    WHERE po.品牌 IS NOT NULL AND po.品牌 != ''
                    AND po.procurement_status = 'PENDING'
                    AND {method_condition_po}
                    GROUP BY po.品牌
                ) combined
                GROUP BY brand
                ORDER BY count DESC, brand
            """)

            result = await db.execute(query, params)
            rows = result.fetchall()

            return [{"brand": row[0], "count": row[1]} for row in rows if row[0]]
        else:
            # 不带计数的查询 - 仅获取去重品牌列表
            query = text(f"""
                SELECT DISTINCT brand FROM (
                    -- 从 products_master 获取
                    SELECT pm.品牌 as brand
                    FROM products_master pm
                    WHERE pm.品牌 IS NOT NULL AND pm.品牌 != ''
                    AND pm.is_active = 1
                    AND {method_condition_pm}

                    UNION

                    -- 从 procurement_orders 获取
                    SELECT po.品牌 as brand
                    FROM procurement_orders po
                    WHERE po.品牌 IS NOT NULL AND po.品牌 != ''
                    AND po.procurement_status = 'PENDING'
                    AND {method_condition_po}
                ) combined
                ORDER BY brand
            """)

            result = await db.execute(query, params)
            rows = result.fetchall()

            return [row[0] for row in rows if row[0]]