"""
手工备注管理服务
支持对各种记录添加备注、标签和任务跟踪
"""

import logging
from datetime import datetime, timedelta
from typing import List, Dict, Optional, Union, Tuple
from dataclasses import dataclass
from enum import Enum

from sqlalchemy.orm import Session
from sqlalchemy import and_, or_, func, desc, asc, text

from app.models import (
    ManualNote, OffsetRelationship, FIFOMatch, 
    ProcurementConfirmation, RefundDetection
)

logger = logging.getLogger(__name__)


class NoteType(Enum):
    """备注类型"""
    ADJUSTMENT = "adjustment"     # 调整说明
    EXPLANATION = "explanation"   # 解释说明
    WARNING = "warning"          # 警告提醒
    REMINDER = "reminder"        # 提醒事项
    ISSUE = "issue"             # 问题记录
    SOLUTION = "solution"        # 解决方案


class NotePriority(Enum):
    """备注优先级"""
    LOW = "low"        # 低优先级
    NORMAL = "normal"  # 普通优先级
    HIGH = "high"      # 高优先级
    URGENT = "urgent"  # 紧急优先级


class NoteStatus(Enum):
    """备注状态"""
    ACTIVE = "active"     # 活跃
    ARCHIVED = "archived" # 已归档
    RESOLVED = "resolved" # 已解决


@dataclass
class NoteFilter:
    """备注过滤条件"""
    note_type: Optional[str] = None
    priority: Optional[str] = None
    status: Optional[str] = None
    created_by: Optional[str] = None
    assigned_to: Optional[str] = None
    tags: Optional[List[str]] = None
    category: Optional[str] = None
    date_from: Optional[datetime] = None
    date_to: Optional[datetime] = None
    is_pinned: Optional[bool] = None
    has_due_date: Optional[bool] = None


class ManualNotesService:
    """手工备注管理服务"""
    
    def __init__(self, db: Session):
        self.db = db
    
    def create_note(
        self,
        note_type: str,
        title: str,
        content: str,
        created_by: str,
        priority: str = "normal",
        tags: Optional[List[str]] = None,
        category: Optional[str] = None,
        assigned_to: Optional[str] = None,
        due_date: Optional[datetime] = None,
        offset_relationship_id: Optional[int] = None,
        fifo_match_id: Optional[int] = None,
        procurement_confirmation_id: Optional[int] = None,
        refund_detection_id: Optional[int] = None,
        is_pinned: bool = False
    ) -> ManualNote:
        """
        创建手工备注
        
        Args:
            note_type: 备注类型
            title: 标题
            content: 内容
            created_by: 创建人
            priority: 优先级
            tags: 标签列表
            category: 分类
            assigned_to: 分配给
            due_date: 截止日期
            offset_relationship_id: 关联的抵扣关系ID
            fifo_match_id: 关联的FIFO匹配ID
            procurement_confirmation_id: 关联的采购确认ID
            refund_detection_id: 关联的退款检测ID
            is_pinned: 是否置顶
            
        Returns:
            ManualNote: 创建的备注记录
        """
        try:
            note = ManualNote(
                note_type=note_type,
                title=title,
                content=content,
                created_by=created_by,
                priority=priority,
                tags=','.join(tags) if tags else None,
                category=category,
                assigned_to=assigned_to,
                due_date=due_date,
                offset_relationship_id=offset_relationship_id,
                fifo_match_id=fifo_match_id,
                procurement_confirmation_id=procurement_confirmation_id,
                refund_detection_id=refund_detection_id,
                is_pinned=is_pinned,
                status='active'
            )
            
            self.db.add(note)
            self.db.flush()
            
            logger.info(f"Created manual note {note.id} by {created_by}")
            
            # 如果是紧急优先级，创建系统提醒
            if priority == 'urgent':
                self._create_urgent_reminder(note)
            
            return note
            
        except Exception as e:
            logger.error(f"Error creating manual note: {e}")
            self.db.rollback()
            raise
    
    def update_note(
        self,
        note_id: int,
        title: Optional[str] = None,
        content: Optional[str] = None,
        priority: Optional[str] = None,
        tags: Optional[List[str]] = None,
        category: Optional[str] = None,
        assigned_to: Optional[str] = None,
        due_date: Optional[datetime] = None,
        status: Optional[str] = None,
        is_pinned: Optional[bool] = None
    ) -> bool:
        """
        更新备注
        
        Args:
            note_id: 备注ID
            title: 新标题
            content: 新内容
            priority: 新优先级
            tags: 新标签列表
            category: 新分类
            assigned_to: 新分配人
            due_date: 新截止日期
            status: 新状态
            is_pinned: 是否置顶
            
        Returns:
            bool: 是否成功
        """
        try:
            note = self.db.query(ManualNote).filter(ManualNote.id == note_id).first()
            
            if not note:
                return False
            
            # 更新字段
            if title is not None:
                note.title = title
            if content is not None:
                note.content = content
            if priority is not None:
                note.priority = priority
            if tags is not None:
                note.tags = ','.join(tags) if tags else None
            if category is not None:
                note.category = category
            if assigned_to is not None:
                note.assigned_to = assigned_to
            if due_date is not None:
                note.due_date = due_date
            if status is not None:
                note.status = status
                if status == 'resolved':
                    note.resolved_at = datetime.utcnow()
            if is_pinned is not None:
                note.is_pinned = is_pinned
            
            note.updated_at = datetime.utcnow()
            
            self.db.commit()
            
            logger.info(f"Updated manual note {note_id}")
            
            return True
            
        except Exception as e:
            logger.error(f"Error updating manual note: {e}")
            self.db.rollback()
            return False
    
    def delete_note(self, note_id: int) -> bool:
        """
        删除备注（实际是归档）
        
        Args:
            note_id: 备注ID
            
        Returns:
            bool: 是否成功
        """
        try:
            note = self.db.query(ManualNote).filter(ManualNote.id == note_id).first()
            
            if not note:
                return False
            
            note.status = 'archived'
            note.updated_at = datetime.utcnow()
            
            self.db.commit()
            
            logger.info(f"Archived manual note {note_id}")
            
            return True
            
        except Exception as e:
            logger.error(f"Error archiving manual note: {e}")
            self.db.rollback()
            return False
    
    def get_notes(
        self,
        filter_conditions: Optional[NoteFilter] = None,
        page: int = 1,
        page_size: int = 50,
        order_by: str = 'created_at',
        order_desc: bool = True
    ) -> Tuple[List[Dict], int]:
        """
        获取备注列表
        
        Args:
            filter_conditions: 过滤条件
            page: 页码
            page_size: 页面大小
            order_by: 排序字段
            order_desc: 是否降序
            
        Returns:
            Tuple[List[Dict], int]: (备注列表, 总数)
        """
        try:
            query = self.db.query(ManualNote)
            
            # 应用过滤条件
            if filter_conditions:
                query = self._apply_filter_conditions(query, filter_conditions)
            
            # 总数查询
            total = query.count()
            
            # 排序
            if order_desc:
                query = query.order_by(desc(getattr(ManualNote, order_by)))
            else:
                query = query.order_by(asc(getattr(ManualNote, order_by)))
            
            # 置顶的备注始终在前面
            query = query.order_by(desc(ManualNote.is_pinned))
            
            # 分页
            offset = (page - 1) * page_size
            notes = query.offset(offset).limit(page_size).all()
            
            # 转换为字典格式
            result = []
            for note in notes:
                note_dict = {
                    'id': note.id,
                    'note_type': note.note_type,
                    'title': note.title,
                    'content': note.content,
                    'priority': note.priority,
                    'tags': note.tags.split(',') if note.tags else [],
                    'category': note.category,
                    'status': note.status,
                    'is_pinned': note.is_pinned,
                    'created_by': note.created_by,
                    'assigned_to': note.assigned_to,
                    'due_date': note.due_date.isoformat() if note.due_date else None,
                    'resolved_at': note.resolved_at.isoformat() if note.resolved_at else None,
                    'created_at': note.created_at.isoformat() if note.created_at else None,
                    'updated_at': note.updated_at.isoformat() if note.updated_at else None,
                    
                    # 关联信息
                    'offset_relationship_id': note.offset_relationship_id,
                    'fifo_match_id': note.fifo_match_id,
                    'procurement_confirmation_id': note.procurement_confirmation_id,
                    'refund_detection_id': note.refund_detection_id
                }
                result.append(note_dict)
            
            return result, total
            
        except Exception as e:
            logger.error(f"Error getting notes: {e}")
            return [], 0
    
    def get_note_by_id(self, note_id: int) -> Optional[Dict]:
        """获取单个备注详情"""
        note = self.db.query(ManualNote).filter(ManualNote.id == note_id).first()
        
        if not note:
            return None
        
        return {
            'id': note.id,
            'note_type': note.note_type,
            'title': note.title,
            'content': note.content,
            'priority': note.priority,
            'tags': note.tags.split(',') if note.tags else [],
            'category': note.category,
            'status': note.status,
            'is_pinned': note.is_pinned,
            'created_by': note.created_by,
            'assigned_to': note.assigned_to,
            'due_date': note.due_date.isoformat() if note.due_date else None,
            'resolved_at': note.resolved_at.isoformat() if note.resolved_at else None,
            'created_at': note.created_at.isoformat() if note.created_at else None,
            'updated_at': note.updated_at.isoformat() if note.updated_at else None,
            'offset_relationship_id': note.offset_relationship_id,
            'fifo_match_id': note.fifo_match_id,
            'procurement_confirmation_id': note.procurement_confirmation_id,
            'refund_detection_id': note.refund_detection_id
        }
    
    def get_notes_for_entity(
        self,
        entity_type: str,
        entity_id: int,
        status: Optional[str] = None
    ) -> List[Dict]:
        """
        获取特定实体的备注
        
        Args:
            entity_type: 实体类型 (offset_relationship, fifo_match, procurement_confirmation, refund_detection)
            entity_id: 实体ID
            status: 状态过滤
            
        Returns:
            List[Dict]: 备注列表
        """
        try:
            query = self.db.query(ManualNote)
            
            # 根据实体类型设置过滤条件
            if entity_type == 'offset_relationship':
                query = query.filter(ManualNote.offset_relationship_id == entity_id)
            elif entity_type == 'fifo_match':
                query = query.filter(ManualNote.fifo_match_id == entity_id)
            elif entity_type == 'procurement_confirmation':
                query = query.filter(ManualNote.procurement_confirmation_id == entity_id)
            elif entity_type == 'refund_detection':
                query = query.filter(ManualNote.refund_detection_id == entity_id)
            else:
                return []
            
            if status:
                query = query.filter(ManualNote.status == status)
            
            notes = query.order_by(desc(ManualNote.is_pinned), desc(ManualNote.created_at)).all()
            
            return [
                {
                    'id': note.id,
                    'note_type': note.note_type,
                    'title': note.title,
                    'content': note.content,
                    'priority': note.priority,
                    'tags': note.tags.split(',') if note.tags else [],
                    'status': note.status,
                    'is_pinned': note.is_pinned,
                    'created_by': note.created_by,
                    'assigned_to': note.assigned_to,
                    'created_at': note.created_at.isoformat() if note.created_at else None
                }
                for note in notes
            ]
            
        except Exception as e:
            logger.error(f"Error getting notes for entity: {e}")
            return []
    
    def get_overdue_notes(self, assigned_to: Optional[str] = None) -> List[Dict]:
        """获取过期备注"""
        query = self.db.query(ManualNote).filter(
            and_(
                ManualNote.due_date < datetime.utcnow(),
                ManualNote.status == 'active'
            )
        )
        
        if assigned_to:
            query = query.filter(ManualNote.assigned_to == assigned_to)
        
        notes = query.order_by(asc(ManualNote.due_date)).all()
        
        return [
            {
                'id': note.id,
                'title': note.title,
                'priority': note.priority,
                'assigned_to': note.assigned_to,
                'due_date': note.due_date.isoformat() if note.due_date else None,
                'days_overdue': (datetime.utcnow() - note.due_date).days if note.due_date else 0
            }
            for note in notes
        ]
    
    def get_notes_summary(self, user: Optional[str] = None) -> Dict:
        """获取备注摘要统计"""
        try:
            base_query = self.db.query(ManualNote)
            
            if user:
                base_query = base_query.filter(
                    or_(
                        ManualNote.created_by == user,
                        ManualNote.assigned_to == user
                    )
                )
            
            # 总数统计
            total_active = base_query.filter(ManualNote.status == 'active').count()
            total_resolved = base_query.filter(ManualNote.status == 'resolved').count()
            
            # 优先级统计
            priority_counts = (
                base_query.filter(ManualNote.status == 'active')
                .with_entities(ManualNote.priority, func.count(ManualNote.id))
                .group_by(ManualNote.priority)
                .all()
            )
            
            # 类型统计
            type_counts = (
                base_query.filter(ManualNote.status == 'active')
                .with_entities(ManualNote.note_type, func.count(ManualNote.id))
                .group_by(ManualNote.note_type)
                .all()
            )
            
            # 过期统计
            overdue_count = base_query.filter(
                and_(
                    ManualNote.due_date < datetime.utcnow(),
                    ManualNote.status == 'active'
                )
            ).count()
            
            # 今日到期
            today_start = datetime.utcnow().replace(hour=0, minute=0, second=0, microsecond=0)
            today_end = today_start + timedelta(days=1)
            
            due_today_count = base_query.filter(
                and_(
                    ManualNote.due_date.between(today_start, today_end),
                    ManualNote.status == 'active'
                )
            ).count()
            
            return {
                'total_active': total_active,
                'total_resolved': total_resolved,
                'overdue_count': overdue_count,
                'due_today_count': due_today_count,
                'priority_breakdown': {priority: count for priority, count in priority_counts},
                'type_breakdown': {note_type: count for note_type, count in type_counts}
            }
            
        except Exception as e:
            logger.error(f"Error getting notes summary: {e}")
            return {}
    
    def _apply_filter_conditions(self, query, filter_conditions: NoteFilter):
        """应用过滤条件"""
        if filter_conditions.note_type:
            query = query.filter(ManualNote.note_type == filter_conditions.note_type)
        
        if filter_conditions.priority:
            query = query.filter(ManualNote.priority == filter_conditions.priority)
        
        if filter_conditions.status:
            query = query.filter(ManualNote.status == filter_conditions.status)
        
        if filter_conditions.created_by:
            query = query.filter(ManualNote.created_by == filter_conditions.created_by)
        
        if filter_conditions.assigned_to:
            query = query.filter(ManualNote.assigned_to == filter_conditions.assigned_to)
        
        if filter_conditions.category:
            query = query.filter(ManualNote.category == filter_conditions.category)
        
        if filter_conditions.tags:
            for tag in filter_conditions.tags:
                query = query.filter(ManualNote.tags.contains(tag))
        
        if filter_conditions.date_from:
            query = query.filter(ManualNote.created_at >= filter_conditions.date_from)
        
        if filter_conditions.date_to:
            query = query.filter(ManualNote.created_at <= filter_conditions.date_to)
        
        if filter_conditions.is_pinned is not None:
            query = query.filter(ManualNote.is_pinned == filter_conditions.is_pinned)
        
        if filter_conditions.has_due_date is not None:
            if filter_conditions.has_due_date:
                query = query.filter(ManualNote.due_date.isnot(None))
            else:
                query = query.filter(ManualNote.due_date.is_(None))
        
        return query
    
    def _create_urgent_reminder(self, note: ManualNote) -> None:
        """为紧急备注创建系统提醒"""
        try:
            reminder_note = ManualNote(
                note_type='reminder',
                title=f'紧急备注提醒: {note.title}',
                content=f'您有一个紧急备注需要处理: {note.content[:100]}...',
                created_by='system',
                assigned_to=note.assigned_to or note.created_by,
                priority='urgent',
                is_system_generated=True,
                status='active'
            )
            
            self.db.add(reminder_note)
            
        except Exception as e:
            logger.error(f"Error creating urgent reminder: {e}")
    
    def bulk_update_status(self, note_ids: List[int], new_status: str) -> int:
        """批量更新备注状态"""
        try:
            updated_count = (
                self.db.query(ManualNote)
                .filter(ManualNote.id.in_(note_ids))
                .update(
                    {
                        'status': new_status,
                        'updated_at': datetime.utcnow(),
                        'resolved_at': datetime.utcnow() if new_status == 'resolved' else None
                    },
                    synchronize_session=False
                )
            )
            
            self.db.commit()
            
            logger.info(f"Bulk updated {updated_count} notes to status {new_status}")
            
            return updated_count
            
        except Exception as e:
            logger.error(f"Error in bulk status update: {e}")
            self.db.rollback()
            return 0