#!/usr/bin/env python3
"""
采购优先级算法
根据销量、库存、利润率、紧急程度等指标自动计算采购优先级
"""

import asyncio
import logging
from datetime import datetime, timedelta
from decimal import Decimal
from typing import Dict, List, Any, Optional
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker
from sqlalchemy import text

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)


class ProcurementPriorityCalculator:
    """采购优先级计算器"""
    
    def __init__(self, db_url: str = 'sqlite+aiosqlite:///./ordersys.db'):
        self.db_url = db_url
        self.engine = create_async_engine(db_url)
        self.async_session = async_sessionmaker(self.engine, expire_on_commit=False)
    
    async def calculate_priority_scores(self) -> List[Dict[str, Any]]:
        """计算所有待采购商品的优先级分数"""
        async with self.async_session() as session:
            logger.info("开始计算采购优先级分数...")
            
            # 获取所有待采购商品的详细信息
            result = await session.execute(text('''
                SELECT 
                    pp.id,
                    pp.product_key,
                    pp.需求数量,
                    pp.建议采购价,
                    pp.order_count,
                    pp.采购备注,
                    pp.procurement_method,
                    p.线上宝贝名称,
                    p.品牌,
                    p.货号,
                    -- 计算历史销售数据
                    (
                        SELECT COUNT(*)
                        FROM order_items_norm oin
                        WHERE oin.线上宝贝名称 = p.线上宝贝名称
                        AND oin.付款时间 >= date('now', '-30 days')
                    ) as sales_30_days,
                    (
                        SELECT COUNT(*)
                        FROM order_items_norm oin
                        WHERE oin.线上宝贝名称 = p.线上宝贝名称
                        AND oin.付款时间 >= date('now', '-7 days')
                    ) as sales_7_days,
                    -- 计算平均订单价格
                    (
                        SELECT AVG(CAST(oin.订单单价 AS REAL))
                        FROM order_items_norm oin
                        WHERE oin.线上宝贝名称 = p.线上宝贝名称
                        AND oin.订单单价 IS NOT NULL
                        AND CAST(oin.订单单价 AS REAL) > 0
                    ) as avg_order_price,
                    -- 获取最早付款时间（判断紧急程度）
                    (
                        SELECT MIN(oin.付款时间)
                        FROM order_items_norm oin
                        WHERE oin.线上宝贝名称 = p.线上宝贝名称
                        AND oin.procurement_status = 'WAITING'
                        AND oin.付款时间 IS NOT NULL
                    ) as earliest_payment_date
                FROM pending_purchases pp
                JOIN products p ON pp.product_id = p.id
                ORDER BY pp.id
            '''))
            
            items = result.fetchall()
            logger.info(f"找到 {len(items)} 个待采购商品，开始计算优先级...")
            
            priority_items = []
            
            for item in items:
                # 计算各项指标分数
                scores = await self._calculate_item_scores(item)
                
                # 计算综合优先级分数
                final_score = self._calculate_final_score(scores)
                
                priority_item = {
                    'id': item.id,
                    'product_key': item.product_key,
                    'product_name': item.线上宝贝名称,
                    'brand': item.品牌,
                    'procurement_method': item.procurement_method,
                    '需求数量': item.需求数量,
                    '建议采购价': float(item.建议采购价),
                    'order_count': item.order_count,
                    'scores': scores,
                    'final_score': final_score,
                    'priority_level': self._get_priority_level(final_score)
                }
                
                priority_items.append(priority_item)
            
            # 按最终分数排序
            priority_items.sort(key=lambda x: x['final_score'], reverse=True)
            
            logger.info(f"优先级计算完成，生成 {len(priority_items)} 个结果")
            return priority_items
    
    async def _calculate_item_scores(self, item) -> Dict[str, float]:
        """计算单个商品的各项评分"""
        scores = {}
        
        # 1. 销量热度分数 (0-100)
        sales_30_days = item.sales_30_days or 0
        sales_7_days = item.sales_7_days or 0
        
        # 基于30天销量
        if sales_30_days >= 50:
            sales_score = 100
        elif sales_30_days >= 20:
            sales_score = 80
        elif sales_30_days >= 10:
            sales_score = 60
        elif sales_30_days >= 5:
            sales_score = 40
        elif sales_30_days >= 1:
            sales_score = 20
        else:
            sales_score = 10
        
        # 7天销量加权（反映最新趋势）
        if sales_7_days > 0:
            trend_factor = min(sales_7_days / max(sales_30_days, 1) * 7, 2.0)  # 最多翻倍
            sales_score *= trend_factor
        
        scores['sales_score'] = min(sales_score, 100)
        
        # 2. 需求紧迫性分数 (0-100)
        demand_quantity = item.需求数量 or 0
        order_count = item.order_count or 1
        
        # 基于订单数量
        if order_count >= 30:
            urgency_score = 100
        elif order_count >= 20:
            urgency_score = 85
        elif order_count >= 10:
            urgency_score = 70
        elif order_count >= 5:
            urgency_score = 55
        else:
            urgency_score = 40
        
        # 基于需求数量调整
        if demand_quantity >= 50:
            urgency_score = min(urgency_score * 1.3, 100)
        elif demand_quantity >= 20:
            urgency_score = min(urgency_score * 1.1, 100)
        
        scores['urgency_score'] = urgency_score
        
        # 3. 等待时间分数 (0-100)
        waiting_score = 50  # 默认分数
        if item.earliest_payment_date:
            try:
                earliest_date = datetime.fromisoformat(item.earliest_payment_date.replace('Z', '+00:00'))
                days_waiting = (datetime.now() - earliest_date).days
                
                if days_waiting >= 7:
                    waiting_score = 100
                elif days_waiting >= 5:
                    waiting_score = 85
                elif days_waiting >= 3:
                    waiting_score = 70
                elif days_waiting >= 1:
                    waiting_score = 55
                else:
                    waiting_score = 30
            except:
                waiting_score = 50
        
        scores['waiting_score'] = waiting_score
        
        # 4. 利润潜力分数 (0-100)
        suggested_price = float(item.建议采购价 or 0)
        avg_order_price = item.avg_order_price or 0
        
        if suggested_price > 0 and avg_order_price > 0:
            # 计算毛利率
            margin_rate = (avg_order_price - suggested_price) / avg_order_price
            
            if margin_rate >= 0.6:
                profit_score = 100
            elif margin_rate >= 0.4:
                profit_score = 80
            elif margin_rate >= 0.2:
                profit_score = 60
            elif margin_rate >= 0:
                profit_score = 40
            else:
                profit_score = 20
        else:
            # 基于价格区间推测
            if suggested_price >= 1000:
                profit_score = 70  # 高价商品通常利润空间大
            elif suggested_price >= 500:
                profit_score = 60
            elif suggested_price >= 200:
                profit_score = 50
            else:
                profit_score = 40
        
        scores['profit_score'] = profit_score
        
        # 5. 品牌权重分数 (0-100)
        brand = (item.品牌 or '').upper()
        if any(luxury in brand for luxury in ['GUCCI', 'PRADA', 'HERMES', 'CHANEL', 'LV', 'LOUIS VUITTON']):
            brand_score = 90
        elif any(premium in brand for premium in ['BURBERRY', 'ALEXANDER', 'BALENCIAGA', 'SAINT LAURENT']):
            brand_score = 75
        elif any(popular in brand for popular in ['NIKE', 'ADIDAS', 'JORDAN']):
            brand_score = 60
        else:
            brand_score = 50
        
        scores['brand_score'] = brand_score
        
        return scores
    
    def _calculate_final_score(self, scores: Dict[str, float]) -> float:
        """计算最终综合分数"""
        # 各指标权重
        weights = {
            'sales_score': 0.25,      # 销量热度：25%
            'urgency_score': 0.30,    # 需求紧迫性：30%
            'waiting_score': 0.20,    # 等待时间：20%
            'profit_score': 0.15,     # 利润潜力：15%
            'brand_score': 0.10       # 品牌权重：10%
        }
        
        final_score = sum(scores[key] * weights[key] for key in weights)
        return round(final_score, 2)
    
    def _get_priority_level(self, score: float) -> str:
        """根据分数确定优先级等级"""
        if score >= 80:
            return 'HIGH'
        elif score >= 60:
            return 'MEDIUM'
        else:
            return 'LOW'
    
    async def update_database_priorities(self, priority_items: List[Dict[str, Any]]):
        """将计算的优先级更新到数据库"""
        async with self.async_session() as session:
            logger.info("开始更新数据库中的采购优先级...")
            
            updated_count = 0
            priority_changes = {'HIGH': 0, 'MEDIUM': 0, 'LOW': 0}
            
            for item in priority_items:
                # 更新优先级和评分信息
                await session.execute(text('''
                    UPDATE pending_purchases 
                    SET priority = :priority,
                        is_urgent = :is_urgent,
                        采购备注 = :new_notes,
                        last_updated = :now
                    WHERE id = :item_id
                '''), {
                    'priority': item['priority_level'],
                    'is_urgent': item['priority_level'] == 'HIGH',
                    'new_notes': f"自动计算优先级({item['final_score']:.1f}分) - 基于销量、紧急性、等待时间、利润、品牌综合评估",
                    'now': datetime.now(),
                    'item_id': item['id']
                })
                
                priority_changes[item['priority_level']] += 1
                updated_count += 1
                
                # 每100个提交一次
                if updated_count % 100 == 0:
                    await session.commit()
                    logger.info(f"已更新 {updated_count} 个采购项优先级...")
            
            # 最终提交
            await session.commit()
            
            logger.info(f"优先级更新完成，总更新：{updated_count} 项")
            logger.info(f"优先级分布：HIGH({priority_changes['HIGH']}) MEDIUM({priority_changes['MEDIUM']}) LOW({priority_changes['LOW']})")
            
            return priority_changes
    
    async def generate_priority_report(self, priority_items: List[Dict[str, Any]]):
        """生成优先级分析报告"""
        print("\n" + "="*70)
        print("📊 采购优先级分析报告")
        print("="*70)
        
        # 1. 总体统计
        total_items = len(priority_items)
        high_priority = len([x for x in priority_items if x['priority_level'] == 'HIGH'])
        medium_priority = len([x for x in priority_items if x['priority_level'] == 'MEDIUM'])
        low_priority = len([x for x in priority_items if x['priority_level'] == 'LOW'])
        
        print(f"\n📈 总体统计:")
        print(f"   总采购项目: {total_items}")
        print(f"   高优先级: {high_priority} ({high_priority/total_items*100:.1f}%)")
        print(f"   中优先级: {medium_priority} ({medium_priority/total_items*100:.1f}%)")
        print(f"   低优先级: {low_priority} ({low_priority/total_items*100:.1f}%)")
        
        # 2. 高优先级商品TOP 10
        high_priority_items = [x for x in priority_items if x['priority_level'] == 'HIGH'][:10]
        
        print(f"\n🔥 TOP 10 高优先级采购项目:")
        print("-" * 70)
        for i, item in enumerate(high_priority_items, 1):
            product_name = item['product_name'][:35] + "..." if len(item['product_name']) > 35 else item['product_name']
            print(f"{i:2d}. {product_name}")
            print(f"    综合得分: {item['final_score']:.1f} | 需求数量: {item['需求数量']} | 订单数: {item['order_count']}")
            print(f"    评分详情: 销量({item['scores']['sales_score']:.0f}) 紧急({item['scores']['urgency_score']:.0f}) " +
                  f"等待({item['scores']['waiting_score']:.0f}) 利润({item['scores']['profit_score']:.0f}) 品牌({item['scores']['brand_score']:.0f})")
            print()
        
        # 3. 按采购方式统计
        method_stats = {}
        for item in priority_items:
            method = item['procurement_method']
            if method not in method_stats:
                method_stats[method] = {'total': 0, 'high': 0, 'medium': 0, 'low': 0, 'avg_score': 0}
            
            method_stats[method]['total'] += 1
            method_stats[method][item['priority_level'].lower()] += 1
            method_stats[method]['avg_score'] += item['final_score']
        
        # 计算平均分
        for method in method_stats:
            method_stats[method]['avg_score'] /= method_stats[method]['total']
        
        print(f"\n📍 按采购方式统计:")
        print("-" * 70)
        method_names = {
            'GN': 'GN(国内现货)', 'MC': 'MC(库存)', 'AT': 'AT(Atelier)',
            'AP': 'AP(昌昌)', 'LA': 'LA(洛杉矶)', 'SS': 'SS(Saks)', 'NY': 'NY(纽约)'
        }
        
        for method, stats in sorted(method_stats.items(), key=lambda x: x[1]['avg_score'], reverse=True):
            method_name = method_names.get(method, method)
            print(f"{method_name}:")
            print(f"   总数: {stats['total']} | 平均分: {stats['avg_score']:.1f}")
            print(f"   HIGH: {stats['high']} | MEDIUM: {stats['medium']} | LOW: {stats['low']}")
            print()


async def main():
    """主函数"""
    print("=" * 70)
    print("🎯 采购优先级算法系统")
    print("=" * 70)
    
    calculator = ProcurementPriorityCalculator()
    
    # 1. 计算优先级分数
    priority_items = await calculator.calculate_priority_scores()
    
    # 2. 更新数据库
    priority_changes = await calculator.update_database_priorities(priority_items)
    
    # 3. 生成报告
    await calculator.generate_priority_report(priority_items)
    
    print("\n" + "="*70)
    print("✅ 采购优先级算法执行完成!")
    print(f"📊 优先级更新统计: HIGH({priority_changes['HIGH']}) MEDIUM({priority_changes['MEDIUM']}) LOW({priority_changes['LOW']})")
    print("\n💡 算法说明:")
    print("   • 销量热度(25%): 基于30天和7天销量趋势")
    print("   • 需求紧迫性(30%): 基于订单数量和需求数量")
    print("   • 等待时间(20%): 基于最早付款时间")
    print("   • 利润潜力(15%): 基于毛利率和价格区间")
    print("   • 品牌权重(10%): 基于品牌价值级别")
    print("\n📍 现在可以访问:")
    print("   待采购管理: http://localhost:3000/procurement/pending")
    print("   订单列表: http://localhost:3000/procurement/orders")
    print("=" * 70)


if __name__ == "__main__":
    asyncio.run(main())