from datetime import datetime
from typing import Any, Dict, Optional, Union, List
from sqlalchemy.orm import Session
from sqlalchemy import func, or_

from app.core.security import get_password_hash, verify_password
from app.models.user import User, Admin
from app.schemas.user import UserCreate, UserUpdate, AdminCreate, AdminUpdate


# 用户CRUD操作
class CRUDUser:
    # 根据ID获取用户
    def get(self, db: Session, user_id: int) -> Optional[User]:
        return db.query(User).filter(User.id == user_id).first()
    
    # 根据用户名获取用户
    def get_by_username(self, db: Session, username: str) -> Optional[User]:
        return db.query(User).filter(User.username == username).first()
    
    # 根据邮箱获取用户
    def get_by_email(self, db: Session, email: str) -> Optional[User]:
        return db.query(User).filter(User.email == email).first()
    
    # 根据手机号获取用户
    def get_by_phone(self, db: Session, phone: str) -> Optional[User]:
        return db.query(User).filter(User.phone == phone).first()
    
    # 根据微信OpenID获取用户
    def get_by_wechat_openid(self, db: Session, openid: str) -> Optional[User]:
        return db.query(User).filter(User.wechat_openid == openid).first()
    
    # 创建用户
    def create(self, db: Session, obj_in: UserCreate) -> User:
        db_obj = User(
            username=obj_in.username,
            password_hash=get_password_hash(obj_in.password),
            user_type=obj_in.user_type,
            real_name=obj_in.real_name or obj_in.username,
            nickname=obj_in.nickname or obj_in.username,
            email=obj_in.email,
            phone=obj_in.phone,
            created_at=datetime.now(),
            updated_at=datetime.now()
        )
        db.add(db_obj)
        db.commit()
        db.refresh(db_obj)
        return db_obj
    
    # 更新用户
    def update(
        self, db: Session, *, db_obj: User, obj_in: Union[UserUpdate, Dict[str, Any]]
    ) -> User:
        if isinstance(obj_in, dict):
            update_data = obj_in
        else:
            update_data = obj_in.dict(exclude_unset=True)
        
        # 更新时间
        update_data["updated_at"] = datetime.now()
        
        for field in update_data:
            if field in update_data:
                setattr(db_obj, field, update_data[field])
        
        db.add(db_obj)
        db.commit()
        db.refresh(db_obj)
        return db_obj
    
    # 更新用户密码
    def update_password(self, db: Session, db_obj: User, new_password: str) -> User:
        db_obj.password_hash = get_password_hash(new_password)
        db_obj.updated_at = datetime.now()
        db.add(db_obj)
        db.commit()
        db.refresh(db_obj)
        return db_obj
    
    # 更新用户最后登录时间
    def update_last_login(self, db: Session, db_obj: User) -> User:
        db_obj.last_login = datetime.now()
        db.add(db_obj)
        db.commit()
        db.refresh(db_obj)
        return db_obj
    
    # 验证用户密码
    def authenticate(self, db: Session, username: str, password: str) -> Optional[User]:
        user = self.get_by_username(db, username)
        if not user:
            user = self.get_by_email(db, username)
        if not user:
            user = self.get_by_phone(db, username)
        if not user:
            return None
        if not verify_password(password, user.password_hash):
            return None
        return user
    
    # 检查用户是否活跃
    def is_active(self, user: User) -> bool:
        return user.status == "active"
    
    # 获取用户列表（带分页）
    def get_multi(
        self, db: Session, *, skip: int = 0, limit: int = 100, 
        user_type: Optional[str] = None, status: Optional[str] = None,
        keyword: Optional[str] = None
    ) -> List[User]:
        query = db.query(User)
        
        # 应用过滤条件
        if user_type:
            query = query.filter(User.user_type == user_type)
        if status:
            query = query.filter(User.status == status)
        if keyword:
            query = query.filter(
                or_(
                    User.username.like(f"%{keyword}%"),
                    User.real_name.like(f"%{keyword}%"),
                    User.nickname.like(f"%{keyword}%"),
                    User.email.like(f"%{keyword}%"),
                    User.phone.like(f"%{keyword}%")
                )
            )
        
        return query.offset(skip).limit(limit).all()
    
    # 获取用户总数
    def count(
        self, db: Session, *, user_type: Optional[str] = None, 
        status: Optional[str] = None, keyword: Optional[str] = None
    ) -> int:
        query = db.query(func.count(User.id))
        
        # 应用过滤条件
        if user_type:
            query = query.filter(User.user_type == user_type)
        if status:
            query = query.filter(User.status == status)
        if keyword:
            query = query.filter(
                or_(
                    User.username.like(f"%{keyword}%"),
                    User.real_name.like(f"%{keyword}%"),
                    User.nickname.like(f"%{keyword}%"),
                    User.email.like(f"%{keyword}%"),
                    User.phone.like(f"%{keyword}%")
                )
            )
        
        return query.scalar()
    
    # 批量更新用户状态
    def batch_update_status(
        self, db: Session, user_ids: List[int], status: str
    ) -> int:
        result = db.query(User).filter(User.id.in_(user_ids)).update(
            {"status": status, "updated_at": datetime.now()},
            synchronize_session=False
        )
        db.commit()
        return result
    
    # 删除用户
    def remove(self, db: Session, *, user_id: int) -> Optional[User]:
        user = db.query(User).filter(User.id == user_id).first()
        if user:
            db.delete(user)
            db.commit()
        return user
    
    # 获取用户统计信息
    def get_statistics(self, db: Session) -> Dict[str, Any]:
        total_users = db.query(func.count(User.id)).scalar()
        farmers = db.query(func.count(User.id)).filter(User.user_type == "farmer").scalar()
        merchants = db.query(func.count(User.id)).filter(User.user_type == "merchant").scalar()
        admins = db.query(func.count(User.id)).filter(
            or_(User.user_type == "admin", User.user_type == "super_admin")
        ).scalar()
        active_users = db.query(func.count(User.id)).filter(User.status == "active").scalar()
        inactive_users = db.query(func.count(User.id)).filter(User.status == "inactive").scalar()
        
        return {
            "total_users": total_users,
            "farmers": farmers,
            "merchants": merchants,
            "admins": admins,
            "active_users": active_users,
            "inactive_users": inactive_users,
            "date": datetime.now()
        }


# 管理员CRUD操作
class CRUDAdmin:
    # 根据ID获取管理员
    def get(self, db: Session, admin_id: int) -> Optional[Admin]:
        return db.query(Admin).filter(Admin.id == admin_id).first()
    
    # 根据用户名获取管理员
    def get_by_username(self, db: Session, username: str) -> Optional[Admin]:
        return db.query(Admin).filter(Admin.username == username).first()
    
    # 创建管理员
    def create(self, db: Session, obj_in: AdminCreate) -> Admin:
        db_obj = Admin(
            username=obj_in.username,
            password=get_password_hash(obj_in.password),
            email=obj_in.email,
            nickname=obj_in.nickname or obj_in.username,
            avatar=obj_in.avatar,
            role=obj_in.role,
            created_at=datetime.now(),
            updated_at=datetime.now()
        )
        db.add(db_obj)
        db.commit()
        db.refresh(db_obj)
        return db_obj
    
    # 更新管理员
    def update(
        self, db: Session, *, db_obj: Admin, obj_in: Union[AdminUpdate, Dict[str, Any]]
    ) -> Admin:
        if isinstance(obj_in, dict):
            update_data = obj_in
        else:
            update_data = obj_in.dict(exclude_unset=True)
        
        # 更新时间
        update_data["updated_at"] = datetime.now()
        
        for field in update_data:
            if field in update_data:
                setattr(db_obj, field, update_data[field])
        
        db.add(db_obj)
        db.commit()
        db.refresh(db_obj)
        return db_obj
    
    # 更新管理员密码
    def update_password(self, db: Session, db_obj: Admin, new_password: str) -> Admin:
        db_obj.password = get_password_hash(new_password)
        db_obj.updated_at = datetime.now()
        db.add(db_obj)
        db.commit()
        db.refresh(db_obj)
        return db_obj
    
    # 更新管理员最后登录时间
    def update_last_login(self, db: Session, db_obj: Admin) -> Admin:
        db_obj.last_login = datetime.now()
        db.add(db_obj)
        db.commit()
        db.refresh(db_obj)
        return db_obj
    
    # 验证管理员密码
    def authenticate(self, db: Session, username: str, password: str) -> Optional[Admin]:
        admin = self.get_by_username(db, username)
        if not admin:
            return None
        if not verify_password(password, admin.password):
            return None
        return admin
    
    # 检查管理员是否活跃
    def is_active(self, admin: Admin) -> bool:
        return admin.status == "active"
    
    # 删除管理员
    def remove(self, db: Session, *, admin_id: int) -> Optional[Admin]:
        admin = db.query(Admin).filter(Admin.id == admin_id).first()
        if admin:
            db.delete(admin)
            db.commit()
        return admin


# 实例化CRUD对象
user = CRUDUser()
admin = CRUDAdmin()