"""
采购清单服务
负责从procurement_orders生成和同步purchase_lists
实时保持数据一致性
"""

import logging
from datetime import datetime
from decimal import Decimal
from typing import Dict, List, Optional, Any, Tuple
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, update, delete, func, and_, or_, text, distinct
from sqlalchemy.orm import selectinload

from app.models.procurement_orders import ProcurementOrder, ProcurementStatus
from app.models.products import PurchaseList, PurchaseListItem

logger = logging.getLogger(__name__)


class PurchaseListService:
    """采购清单服务 - 管理procurement_orders到purchase_lists的同步"""
    
    async def sync_all_purchase_lists(
        self,
        db: AsyncSession,
        force_rebuild: bool = False
    ) -> Dict[str, Any]:
        """
        完整同步所有采购清单
        按采购方式分组生成
        """
        logger.info("开始完整同步采购清单")
        
        try:
            # 清空现有数据（如果强制重建）
            if force_rebuild:
                await db.execute(delete(PurchaseListItem))
                await db.execute(delete(PurchaseList))
                await db.commit()
                logger.info("已清空现有采购清单")
            
            # 获取所有待采购订单（排除忽略的订单）
            orders_result = await db.execute(
                select(ProcurementOrder)
                .where(ProcurementOrder.procurement_status == ProcurementStatus.PENDING)
                .where((ProcurementOrder.忽略采购 == False) | (ProcurementOrder.忽略采购.is_(None)))  # 排除忽略的订单
                .order_by(ProcurementOrder.procurement_method, ProcurementOrder.线上宝贝名称)
            )
            orders = orders_result.scalars().all()
            
            if not orders:
                return {
                    'success': True,
                    'message': '没有待采购订单需要同步',
                    'stats': {'total_orders': 0, 'created_lists': 0}
                }
            
            # 按采购方式分组
            orders_by_method = {}
            for order in orders:
                method = order.procurement_method or 'NY'
                if method not in orders_by_method:
                    orders_by_method[method] = []
                orders_by_method[method].append(order)
            
            stats = {
                'total_orders': len(orders),
                'created_lists': 0,
                'created_items': 0,
                'total_amount': Decimal('0')
            }
            
            # 为每个采购方式创建清单
            for method, method_orders in orders_by_method.items():
                list_result = await self._create_purchase_list_for_method(
                    db, method, method_orders
                )
                stats['created_lists'] += 1
                stats['created_items'] += list_result['items_count']
                stats['total_amount'] += list_result['total_amount']
            
            await db.commit()
            
            return {
                'success': True,
                'message': f'成功同步{stats["created_lists"]}个采购清单',
                'stats': stats
            }
            
        except Exception as e:
            logger.error(f"同步采购清单失败: {e}")
            await db.rollback()
            raise
    
    async def _create_purchase_list_for_method(
        self,
        db: AsyncSession,
        method: str,
        orders: List[ProcurementOrder]
    ) -> Dict[str, Any]:
        """为特定采购方式创建采购清单"""
        
        # 生成采购单号
        today = datetime.now().strftime("%Y%m%d")
        list_no = f"PO{today}{method}"
        
        # 计算汇总信息
        total_quantity = sum(o.数量 or 0 for o in orders)
        total_amount = sum(Decimal(str(o.订单金额 or 0)) for o in orders)
        
        # 创建采购清单主表
        purchase_list = PurchaseList(
            采购单号=list_no,
            采购标题=f"{method}采购清单-{datetime.now().strftime('%m月%d日')}",
            供应商名称=f"{method}默认供应商",
            状态="待提交",
            商品总数量=total_quantity,
            预估总金额=total_amount,
            创建时间=datetime.now(),
            创建人="系统",
            采购备注=f"包含{len(orders)}个订单"
        )
        db.add(purchase_list)
        await db.flush()
        
        # 按商品聚合订单
        product_groups = self._group_orders_by_product(orders)
        
        # 创建采购清单明细
        items_count = 0
        for product_key, product_orders in product_groups.items():
            item = await self._create_purchase_list_item(
                db, purchase_list.id, product_key, product_orders
            )
            items_count += 1
        
        return {
            'items_count': items_count,
            'total_amount': total_amount
        }
    
    def _group_orders_by_product(
        self,
        orders: List[ProcurementOrder]
    ) -> Dict[str, List[ProcurementOrder]]:
        """按商品聚合订单"""
        groups = {}
        for order in orders:
            # 生成聚合键：商品名称_品牌_货号_颜色_尺寸
            key = f"{order.线上宝贝名称}_{order.品牌}_{order.货号}_{order.颜色}_{order.尺寸}"
            if key not in groups:
                groups[key] = []
            groups[key].append(order)
        return groups
    
    async def _create_purchase_list_item(
        self,
        db: AsyncSession,
        list_id: int,
        product_key: str,
        orders: List[ProcurementOrder]
    ) -> PurchaseListItem:
        """创建采购清单明细项"""
        
        # 取第一个订单的信息作为代表
        first_order = orders[0]
        
        # 计算汇总数据
        total_quantity = sum(o.数量 or 0 for o in orders)
        total_amount = sum(Decimal(str(o.订单金额 or 0)) for o in orders)
        avg_price = total_amount / total_quantity if total_quantity > 0 else Decimal('0')
        
        # 收集所有相关订单号
        order_ids = [o.原始订单编号 for o in orders]
        
        item = PurchaseListItem(
            purchase_list_id=list_id,
            product_id=0,  # 暂不关联products表
            product_key=product_key,
            品牌=first_order.品牌,
            货号=first_order.货号,
            线上宝贝名称=first_order.线上宝贝名称,
            颜色=first_order.颜色,
            尺寸=first_order.尺寸,
            采购数量=total_quantity,
            单价=avg_price,
            小计=total_amount,
            待收货数量=total_quantity,
            已到货数量=0,
            is_received=False,
            商品备注=f"包含{len(orders)}个订单: {', '.join(order_ids[:3])}{'...' if len(order_ids) > 3 else ''}",
            created_at=datetime.now(),
            updated_at=datetime.now()
        )
        
        db.add(item)
        return item
    
    async def sync_on_order_change(
        self,
        db: AsyncSession,
        order_id: int,
        change_type: str = 'update'
    ) -> Dict[str, Any]:
        """
        当订单发生变化时触发的增量同步
        change_type: 'update', 'delete', 'status_change'
        """
        try:
            # 获取变更的订单
            result = await db.execute(
                select(ProcurementOrder).where(ProcurementOrder.id == order_id)
            )
            order = result.scalar_one_or_none()
            
            if not order:
                return {'success': False, 'message': '订单不存在'}
            
            # 找到该订单所属的采购方式
            method = order.procurement_method or 'NY'
            
            # 重新生成该采购方式的清单
            # 这里简化处理：删除旧的，创建新的
            await self._rebuild_list_for_method(db, method)
            
            await db.commit()
            
            return {
                'success': True,
                'message': f'已同步更新{method}采购清单',
                'method': method
            }
            
        except Exception as e:
            logger.error(f"增量同步失败: {e}")
            await db.rollback()
            return {'success': False, 'message': str(e)}
    
    async def _rebuild_list_for_method(
        self,
        db: AsyncSession,
        method: str
    ):
        """重建特定采购方式的清单"""
        
        # 删除旧清单
        old_lists = await db.execute(
            select(PurchaseList).where(
                PurchaseList.采购标题.like(f"{method}采购清单%")
            )
        )
        for old_list in old_lists.scalars():
            await db.execute(
                delete(PurchaseListItem).where(
                    PurchaseListItem.purchase_list_id == old_list.id
                )
            )
            await db.delete(old_list)
        
        # 获取该方式的所有待采购订单
        orders_result = await db.execute(
            select(ProcurementOrder)
            .where(
                and_(
                    ProcurementOrder.procurement_method == method,
                    ProcurementOrder.procurement_status == ProcurementStatus.PENDING
                )
            )
        )
        orders = orders_result.scalars().all()
        
        if orders:
            await self._create_purchase_list_for_method(db, method, orders)
    
    async def get_purchase_lists(
        self,
        db: AsyncSession,
        procurement_method: Optional[str] = None,
        status: Optional[str] = None,
        page: int = 1,
        page_size: int = 20
    ) -> Dict[str, Any]:
        """获取采购清单列表"""
        
        # 构建查询
        query = select(PurchaseList)
        
        if procurement_method:
            query = query.where(PurchaseList.采购标题.like(f"{procurement_method}%"))
        if status:
            query = query.where(PurchaseList.状态 == status)
        
        # 计算总数
        count_result = await db.execute(
            select(func.count()).select_from(query.subquery())
        )
        total = count_result.scalar() or 0
        
        # 分页查询
        query = query.order_by(PurchaseList.创建时间.desc())
        query = query.offset((page - 1) * page_size).limit(page_size)
        
        result = await db.execute(query)
        lists = result.scalars().all()
        
        # 转换为字典
        items = []
        for pl in lists:
            items.append({
                'id': pl.id,
                '采购单号': pl.采购单号,
                '采购标题': pl.采购标题,
                '供应商名称': pl.供应商名称,
                '状态': pl.状态,
                '商品总数量': pl.商品总数量,
                '预估总金额': float(pl.预估总金额) if pl.预估总金额 else 0,
                '实际总金额': float(pl.实际总金额) if pl.实际总金额 else 0,
                '创建时间': pl.创建时间.isoformat() if pl.创建时间 else None,
                '创建人': pl.创建人
            })
        
        return {
            'items': items,
            'pagination': {
                'page': page,
                'page_size': page_size,
                'total': total,
                'total_pages': (total + page_size - 1) // page_size
            }
        }
    
    async def get_purchase_list_items(
        self,
        db: AsyncSession,
        list_id: int
    ) -> List[Dict[str, Any]]:
        """获取采购清单明细"""
        
        result = await db.execute(
            select(PurchaseListItem)
            .where(PurchaseListItem.purchase_list_id == list_id)
            .order_by(PurchaseListItem.品牌, PurchaseListItem.线上宝贝名称)
        )
        items = result.scalars().all()
        
        items_data = []
        for item in items:
            items_data.append({
                'id': item.id,
                'product_key': item.product_key,
                '品牌': item.品牌,
                '货号': item.货号,
                '线上宝贝名称': item.线上宝贝名称,
                '颜色': item.颜色,
                '尺寸': item.尺寸,
                '采购数量': item.采购数量,
                '单价': float(item.单价) if item.单价 else 0,
                '小计': float(item.小计) if item.小计 else 0,
                '已到货数量': item.已到货数量,
                '待收货数量': item.待收货数量,
                'is_received': item.is_received,
                '商品备注': item.商品备注
            })
        
        return items_data