junma-api/src/services/user.service.ts

import { In, Repository } from 'typeorm'
import { FastifyInstance } from 'fastify'
import { User, UserRole } from '../entities/user.entity'
import bcrypt from 'bcryptjs'
import { PaginationResponse } from '../dto/common.dto'

export class UserService {
  private userRepository: Repository<User>

  constructor(app: FastifyInstance) {
    this.userRepository = app.dataSource.getRepository(User)
  }

  async create(
    password: string | null,
    name: string,
    role: UserRole = UserRole.USER,
    parentId?: number
  ): Promise<User> {
    const userData: Partial<User> = {
      name,
      role,
      parentId
    }

    if (password) {
      userData.password = await bcrypt.hash(password, 10)
    }

    const user = this.userRepository.create(userData)
    return this.userRepository.save(user)
  }

  async findByName(name: string): Promise<User | null> {
    return this.userRepository.findOne({ where: { name } })
  }

  async findById(id: number): Promise<User> {
    return this.userRepository.findOneOrFail({ where: { id } })
  }

  async validatePassword(user: User, password: string): Promise<boolean> {
    if (!user.password) {
      return false
    }
    return bcrypt.compare(password, user.password)
  }

  async resetPassword(userId: number, newPassword: string): Promise<void> {
    const user = await this.findById(userId)
    const hashedPassword = await bcrypt.hash(newPassword, 10)
    await this.userRepository.update(user.id, { password: hashedPassword })
  }

  async list(page: number, size: number, role?: UserRole | UserRole[]): Promise<PaginationResponse<Partial<User>>> {
    const [users, total] = await this.userRepository.findAndCount({
      skip: (Number(page) || 0) * (Number(size) || 20),
      take: Number(size) || 20,
      where: {
        role: role ? (role instanceof Array ? In(role) : role) : undefined
      }
    })
    return {
      content: users.map(user => ({
        id: user.id,
        name: user.name,
        role: user.role,
        createdAt: user.createdAt,
        updatedAt: user.updatedAt
      })),
      metadata: {
        total: Number(total),
        page: Number(page),
        size: Number(size)
      }
    }
  }

  async findAllUsers(): Promise<Partial<User>[]> {
    return this.userRepository.find({
      select: ['id', 'name']
    })
  }

  async updateUser(id: number, data: Partial<User>): Promise<User> {
    if (data.password) {
      data.password = await bcrypt.hash(data.password, 10)
    }

    await this.userRepository.update(id, data)
    return this.findById(id)
  }

  async findChildChannelUsers(parentId: number): Promise<Partial<User>[]> {
    const result: Partial<User>[] = []

    const findChildren = async (pid: number) => {
      const users = await this.userRepository.find({
        select: ['id', 'name', 'role'],
        where: { parentId: pid }
      })

      result.push(...users)

      for (const user of users) {
        await findChildren(user.id)
      }
    }

    await findChildren(parentId)
    return result
  }

  async findAllChildUsers(
    parentId: number,
    page: number = 0,
    size: number = 20
  ): Promise<PaginationResponse<Partial<User>>> {
    const pageNum = Number(page) || 0
    const sizeNum = Number(size) || 20
    const offset = pageNum * sizeNum

    const queryRunner = this.userRepository.manager.connection.createQueryRunner()
    
    try {
      // 获取表名（使用反引号包裹，因为 user 可能是 MySQL 保留字）
      const tableName = this.userRepository.metadata.tableName
      const escapedTableName = `\`${tableName}\``

      // 检测MySQL版本，MySQL 8.0+支持递归CTE
      const versionResult = await queryRunner.query('SELECT VERSION() as version')
      const version = versionResult[0]?.version || ''
      const majorVersion = parseInt(version.split('.')[0]) || 0
      const minorVersion = parseInt(version.split('.')[1]) || 0
      const supportsRecursiveCTE = majorVersion > 8 || (majorVersion === 8 && minorVersion >= 0)

      if (supportsRecursiveCTE) {
        // MySQL 8.0+ 使用递归CTE
        return await this.findAllChildUsersWithRecursiveCTE(queryRunner, escapedTableName, parentId, pageNum, sizeNum, offset)
      } else {
        // MySQL 5.7及以下使用递归查询替代方案
        return await this.findAllChildUsersWithIteration(queryRunner, escapedTableName, parentId, pageNum, sizeNum, offset)
      }
    } catch (error) {
      // 如果递归CTE失败，回退到迭代方案
      try {
        const tableName = this.userRepository.metadata.tableName
        const escapedTableName = `\`${tableName}\``
        return await this.findAllChildUsersWithIteration(queryRunner, escapedTableName, parentId, pageNum, sizeNum, offset)
      } catch (fallbackError) {
        throw new Error(`查询用户列表失败: ${error instanceof Error ? error.message : String(error)}`)
      }
    } finally {
      await queryRunner.release()
    }
  }

  private async findAllChildUsersWithRecursiveCTE(
    queryRunner: any,
    escapedTableName: string,
    parentId: number,
    pageNum: number,
    sizeNum: number,
    offset: number
  ): Promise<PaginationResponse<Partial<User>>> {
    // 先获取总数
    const totalQuery = `
      WITH RECURSIVE user_tree AS (
        -- 基础查询：当前用户
        SELECT id, name, role, parentId, createdAt, updatedAt
        FROM ${escapedTableName}
        WHERE id = ?
        
        UNION ALL
        
        -- 递归查询：所有子用户
        SELECT u.id, u.name, u.role, u.parentId, u.createdAt, u.updatedAt
        FROM ${escapedTableName} u
        INNER JOIN user_tree ut ON u.parentId = ut.id
      )
      SELECT COUNT(*) as total FROM user_tree
    `
    
    const totalResult = await queryRunner.query(totalQuery, [parentId])
    const total = parseInt(totalResult[0]?.total || '0', 10)

    // 获取分页数据
    const dataQuery = `
      WITH RECURSIVE user_tree AS (
        -- 基础查询：当前用户
        SELECT id, name, role, parentId, createdAt, updatedAt
        FROM ${escapedTableName}
        WHERE id = ?
        
        UNION ALL
        
        -- 递归查询：所有子用户
        SELECT u.id, u.name, u.role, u.parentId, u.createdAt, u.updatedAt
        FROM ${escapedTableName} u
        INNER JOIN user_tree ut ON u.parentId = ut.id
      )
      SELECT id, name, role, parentId, createdAt, updatedAt
      FROM user_tree
      ORDER BY id ASC
      LIMIT ? OFFSET ?
    `
    
    const users = await queryRunner.query(dataQuery, [parentId, sizeNum, offset])

    return {
      content: users.map((user: any) => ({
        id: user.id,
        name: user.name,
        role: user.role,
        parentId: user.parentId,
        createdAt: user.createdAt,
        updatedAt: user.updatedAt
      })),
      metadata: {
        total,
        page: pageNum,
        size: sizeNum
      }
    }
  }

  private async findAllChildUsersWithIteration(
    queryRunner: any,
    escapedTableName: string,
    parentId: number,
    pageNum: number,
    sizeNum: number,
    offset: number
  ): Promise<PaginationResponse<Partial<User>>> {
    // 使用迭代方式收集所有子用户ID（兼容MySQL 5.7）
    const allUserIds: number[] = [parentId] // 先添加父用户本身
    const processedIds = new Set<number>([parentId])
    let currentLevelIds = [parentId]

    while (currentLevelIds.length > 0) {
      const placeholders = currentLevelIds.map(() => '?').join(',')
      const query = `
        SELECT id, name, role, parentId, createdAt, updatedAt
        FROM ${escapedTableName}
        WHERE parentId IN (${placeholders})
      `
      
      const children = await queryRunner.query(query, currentLevelIds)
      
      // 准备下一层级
      const nextLevelIds: number[] = []
      for (const child of children) {
        if (!processedIds.has(child.id)) {
          allUserIds.push(child.id)
          processedIds.add(child.id)
          nextLevelIds.push(child.id)
        }
      }
      
      currentLevelIds = nextLevelIds
    }

    const total = allUserIds.length

    // 分页处理
    const paginatedIds = allUserIds.slice(offset, offset + sizeNum)
    
    if (paginatedIds.length === 0) {
      return {
        content: [],
        metadata: {
          total,
          page: pageNum,
          size: sizeNum
        }
      }
    }

    // 根据ID获取用户详细信息
    const placeholders = paginatedIds.map(() => '?').join(',')
    const dataQuery = `
      SELECT id, name, role, parentId, createdAt, updatedAt
      FROM ${escapedTableName}
      WHERE id IN (${placeholders})
      ORDER BY id ASC
    `
    
    const users = await queryRunner.query(dataQuery, paginatedIds)

    return {
      content: users.map((user: any) => ({
        id: user.id,
        name: user.name,
        role: user.role,
        parentId: user.parentId,
        createdAt: user.createdAt,
        updatedAt: user.updatedAt
      })),
      metadata: {
        total,
        page: pageNum,
        size: sizeNum
      }
    }
  }
}