mini-starter/allin-packages/disability-module/src/services/disabled-person.service.ts

import { GenericCrudService } from '@d8d/shared-crud';
import { Repository, DataSource, Not, In } from 'typeorm';
import { DisabledPerson } from '../entities/disabled-person.entity';
import { DisabledBankCard } from '../entities/disabled-bank-card.entity';
import { DisabledPhoto } from '../entities/disabled-photo.entity';
import { DisabledRemark } from '../entities/disabled-remark.entity';
import { DisabledVisit } from '../entities/disabled-visit.entity';
import { FileService, File } from '@d8d/file-module';
import{ OrderPerson, OrderPersonAsset } from '@d8d/allin-order-module';
import { WorkStatus, getWorkStatusLabel } from '@d8d/allin-enums';

// 前端专用的工作状态中文标签映射（与mini-ui保持一致）
const FrontendWorkStatusLabels: Record<WorkStatus, string> = {
  [WorkStatus.NOT_WORKING]: '未就业',
  [WorkStatus.PRE_WORKING]: '待入职',
  [WorkStatus.WORKING]: '在职',
  [WorkStatus.RESIGNED]: '离职'
};

export class DisabledPersonService extends GenericCrudService<DisabledPerson> {
  private readonly bankCardRepository: Repository<DisabledBankCard>;
  private readonly photoRepository: Repository<DisabledPhoto>;
  private readonly remarkRepository: Repository<DisabledRemark>;
  private readonly visitRepository: Repository<DisabledVisit>;
  private readonly fileRepository: Repository<File>;
  private fileService: FileService;

  constructor(dataSource: DataSource) {
    super(dataSource, DisabledPerson);
    this.bankCardRepository = dataSource.getRepository(DisabledBankCard);
    this.photoRepository = dataSource.getRepository(DisabledPhoto);
    this.remarkRepository = dataSource.getRepository(DisabledRemark);
    this.visitRepository = dataSource.getRepository(DisabledVisit);
    this.fileRepository = dataSource.getRepository(File);
    this.fileService = new FileService(dataSource);
  }

  /**
   * 创建残疾人 - 覆盖父类方法，添加身份证号唯一性检查
   */
  override async create(data: Partial<DisabledPerson>, userId?: string | number): Promise<DisabledPerson> {
    // 检查身份证号是否已存在
    if (data.idCard) {
      const existingPerson = await this.repository.findOne({
        where: { idCard: data.idCard }
      });
      if (existingPerson) {
        throw new Error('身份证号已存在');
      }
    }

    // 检查残疾证号是否已存在
    if (data.disabilityId) {
      const existingPerson = await this.repository.findOne({
        where: { disabilityId: data.disabilityId }
      });
      if (existingPerson) {
        throw new Error('残疾证号已存在');
      }
    }

    return super.create(data, userId);
  }

  /**
   * 更新残疾人 - 覆盖父类方法，添加身份证号唯一性检查
   */
  override async update(id: number, data: Partial<DisabledPerson>, userId?: string | number): Promise<DisabledPerson | null> {
    // 检查残疾人是否存在
    const person = await this.repository.findOne({ where: { id } });
    if (!person) {
      throw new Error('残疾人不存在');
    }

    // 检查身份证号是否与其他残疾人重复
    if (data.idCard && data.idCard !== person.idCard) {
      const existingPerson = await this.repository.findOne({
        where: { idCard: data.idCard, id: Not(id) }
      });
      if (existingPerson) {
        throw new Error('身份证号已存在');
      }
    }

    // 检查残疾证号是否与其他残疾人重复
    if (data.disabilityId && data.disabilityId !== person.disabilityId) {
      const existingPerson = await this.repository.findOne({
        where: { disabilityId: data.disabilityId, id: Not(id) }
      });
      if (existingPerson) {
        throw new Error('残疾证号已存在');
      }
    }

    return super.update(id, data, userId);
  }

  /**
   * 获取单个残疾人（包含关联数据）
   */
  async findOne(id: number): Promise<DisabledPerson | null> {
    const person = await this.repository.findOne({
      where: { id },
      relations: ['bankCards', 'bankCards.bankName', 'bankCards.file', 'bankCards.file.uploadUser', 'photos', 'photos.file', 'photos.file.uploadUser', 'remarks', 'visits']
    });


    return person;
  }

  /**
   * 获取所有残疾人（分页+条件查询） - 返回源服务的格式
   */
  async findAll(query: {
    keyword?: string;
    bankNameId?: number;
    cardType?: string;
    disabilityType?: string;
    disabilityLevel?: string;
    province?: string;
    page?: number;
    limit?: number;
  }): Promise<{ data: DisabledPerson[], total: number }> {
    const {
      keyword,
      bankNameId,
      cardType,
      disabilityType,
      disabilityLevel,
      province,
      page = 1,
      limit = 10
    } = query;

    const queryBuilder = this.repository.createQueryBuilder('person');

    // 支持按关键词搜索（姓名或身份证号）
    if (keyword) {
      queryBuilder.andWhere('(person.name LIKE :keyword OR person.idCard LIKE :keyword)', {
        keyword: `%${keyword}%`
      });
    }

    if (disabilityType) {
      queryBuilder.andWhere('person.disabilityType = :disabilityType', { disabilityType });
    }
    if (disabilityLevel) {
      queryBuilder.andWhere('person.disabilityLevel = :disabilityLevel', { disabilityLevel });
    }
    if (province) {
      queryBuilder.andWhere('person.province = :province', { province });
    }

    // 处理银行卡相关筛选条件
    if (bankNameId || cardType) {
      queryBuilder.innerJoin('person.bankCards', 'bankCard');

      if (bankNameId) {
        queryBuilder.andWhere('bankCard.bankNameId = :bankNameId', { bankNameId });
      }
      if (cardType) {
        queryBuilder.andWhere('bankCard.cardType = :cardType', { cardType });
      }

      // 加载bankCards关系及其关联的bankName
      queryBuilder.leftJoinAndSelect('person.bankCards', 'bankCards')
                  .leftJoinAndSelect('bankCards.bankName', 'bankName');
    }

    const [data, total] = await queryBuilder
      .skip((page - 1) * limit)
      .take(limit)
      .orderBy('person.createTime', 'DESC')
      .getManyAndCount();

    // 加载关联数据
    if (data.length > 0) {
      const personIds = data.map(p => p.id);

      const [bankCards, photos, remarks, visits] = await Promise.all([
        this.bankCardRepository.find({ where: { personId: In(personIds) } }),
        this.photoRepository.find({
          where: { personId: In(personIds) },
          relations: ['file']
        }),
        this.remarkRepository.find({ where: { personId: In(personIds) } }),
        this.visitRepository.find({ where: { personId: In(personIds) } })
      ]);


      // 将关联数据分组到对应的残疾人
      const bankCardsMap = new Map<number, DisabledBankCard[]>();
      const photosMap = new Map<number, DisabledPhoto[]>();
      const remarksMap = new Map<number, DisabledRemark[]>();
      const visitsMap = new Map<number, DisabledVisit[]>();

      for (const card of bankCards) {
        const cards = bankCardsMap.get(card.personId) || [];
        cards.push(card);
        bankCardsMap.set(card.personId, cards);
      }

      for (const photo of photos) {
        const photos = photosMap.get(photo.personId) || [];
        photos.push(photo);
        photosMap.set(photo.personId, photos);
      }

      for (const remark of remarks) {
        const remarks = remarksMap.get(remark.personId) || [];
        remarks.push(remark);
        remarksMap.set(remark.personId, remarks);
      }

      for (const visit of visits) {
        const visits = visitsMap.get(visit.personId) || [];
        visits.push(visit);
        visitsMap.set(visit.personId, visits);
      }

      // 将关联数据附加到每个残疾人
      for (const person of data) {
        person.bankCards = bankCardsMap.get(person.id) || [];
        person.photos = photosMap.get(person.id) || [];
        person.remarks = remarksMap.get(person.id) || [];
        person.visits = visitsMap.get(person.id) || [];
      }
    }

    return { data, total };
  }

  /**
   * 根据身份证号查询残疾人
   */
  async findByIdCard(idCard: string): Promise<DisabledPerson | null> {
    const person = await this.repository.findOne({
      where: { idCard },
      relations: ['bankCards', 'photos', 'photos.file', 'remarks', 'visits']
    });


    return person;
  }

  /**
   * 验证文件ID是否存在
   */
  async validateFileId(fileId: number): Promise<boolean> {
    try {
      const file = await this.fileRepository.findOne({ where: { id: fileId } });
      return !!file;
    } catch (error) {
      console.error('验证文件ID失败:', error);
      return false;
    }
  }

  /**
   * 创建照片记录（包含文件验证）
   */
  async createPhoto(photoData: Partial<DisabledPhoto>): Promise<DisabledPhoto> {
    // 验证文件ID是否存在
    if (photoData.fileId) {
      const fileExists = await this.validateFileId(photoData.fileId);
      if (!fileExists) {
        throw new Error('文件不存在');
      }
    } else {
      throw new Error('文件ID不能为空');
    }

    // 验证残疾人是否存在
    if (photoData.personId) {
      const personExists = await this.repository.findOne({ where: { id: photoData.personId } });
      if (!personExists) {
        throw new Error('残疾人不存在');
      }
    } else {
      throw new Error('残疾人ID不能为空');
    }

    const photo = this.photoRepository.create(photoData);
    return await this.photoRepository.save(photo);
  }

  /**
   * 批量创建照片记录
   */
  async createPhotosBatch(photosData: Partial<DisabledPhoto>[]): Promise<DisabledPhoto[]> {
    // 验证所有文件ID和残疾人ID
    for (const photoData of photosData) {
      if (photoData.fileId) {
        const fileExists = await this.validateFileId(photoData.fileId);
        if (!fileExists) {
          throw new Error(`文件ID ${photoData.fileId} 不存在`);
        }
      } else {
        throw new Error('文件ID不能为空');
      }

      if (photoData.personId) {
        const personExists = await this.repository.findOne({ where: { id: photoData.personId } });
        if (!personExists) {
          throw new Error(`残疾人ID ${photoData.personId} 不存在`);
        }
      } else {
        throw new Error('残疾人ID不能为空');
      }
    }

    const photos = this.photoRepository.create(photosData);
    return await this.photoRepository.save(photos);
  }

  /**
   * 批量创建残疾人
   */
  async batchCreate(persons: Partial<DisabledPerson>[]): Promise<{
    success: boolean;
    createdCount: number;
    failedItems: Array<{ index: number; error: string }>;
  }> {
    const failedItems: Array<{ index: number; error: string }> = [];
    let createdCount = 0;

    // 使用事务处理批量创建
    const queryRunner = this.dataSource.createQueryRunner();
    await queryRunner.connect();
    await queryRunner.startTransaction();

    try {
      for (let i = 0; i < persons.length; i++) {
        const personData = persons[i];
        if (personData === undefined) {
          failedItems.push({
            index: i,
            error: '数据为undefined'
          });
          continue;
        }
        try {
          // 检查身份证号是否已存在
          if (personData.idCard) {
            const existingPerson = await this.repository.findOne({
              where: { idCard: personData.idCard }
            });
            if (existingPerson) {
              throw new Error('身份证号已存在');
            }
          }

          // 检查残疾证号是否已存在
          if (personData.disabilityId) {
            const existingPerson = await this.repository.findOne({
              where: { disabilityId: personData.disabilityId }
            });
            if (existingPerson) {
              throw new Error('残疾证号已存在');
            }
          }

          // 创建残疾人
          const person = this.repository.create(personData);
          await queryRunner.manager.save(person);
          createdCount++;
        } catch (error) {
          failedItems.push({
            index: i,
            error: error instanceof Error ? error.message : '未知错误'
          });
        }
      }

      await queryRunner.commitTransaction();
      return {
        success: failedItems.length === 0,
        createdCount,
        failedItems
      };
    } catch (error) {
      await queryRunner.rollbackTransaction();
      throw error;
    } finally {
      await queryRunner.release();
    }
  }

  /**
   * 获取人才工作历史
   */
  async getWorkHistory(personId: number): Promise<any[]> {
    const orderPersonRepo = this.dataSource.getRepository(OrderPerson);

    const workHistory = await orderPersonRepo.find({
      where: { personId },
      relations: ['order'],
      order: { joinDate: 'DESC' }
    });

    return workHistory.map(item => ({
      订单ID: item.orderId,
      订单名称: item.order?.orderName,
      入职日期: item.joinDate, // z.coerce.date().nullable()会自动转换
      实际入职日期: item.actualStartDate, // z.coerce.date().nullable()会自动转换
      离职日期: item.leaveDate, // z.coerce.date().nullable()会自动转换
      工作状态: FrontendWorkStatusLabels[item.workStatus] || getWorkStatusLabel(item.workStatus), // 使用前端专用映射，后备使用默认映射
      个人薪资: item.salaryDetail // z.coerce.number()会自动转换
    }));
  }

  /**
   * 获取人才薪资历史
   */
  async getSalaryHistory(personId: number): Promise<any[]> {
    const orderPersonRepo = this.dataSource.getRepository(OrderPerson);

    const salaryHistory = await orderPersonRepo.find({
      where: { personId },
      relations: ['order'],
      order: { joinDate: 'DESC' }
    });

    // 假设薪资详情存储在 salaryDetail 字段中
    // 这里可以根据实际业务逻辑解析薪资数据
    return salaryHistory.map(item => ({
      月份: item.joinDate, // z.coerce.date().nullable()会自动转换
      基本工资: item.salaryDetail, // z.coerce.number()会自动转换
      补贴: 0, // 需要根据业务逻辑计算
      扣款: 0, // 需要根据业务逻辑计算
      实发工资: item.salaryDetail // z.coerce.number()会自动转换
    }));
  }

  /**
   * 获取个人征信信息
   */
  async getCreditInfo(personId: number): Promise<any[]> {
    const bankCardRepo = this.dataSource.getRepository(DisabledBankCard);

    const bankCards = await bankCardRepo.find({
      where: { personId },
      relations: ['file']
    });

    return Promise.all(
      bankCards
        .filter(card => card.fileId) // 只返回有文件的记录
        .map(async (card) => ({
          文件ID: card.fileId ? card.fileId.toString() : '',
          文件URL: card.file ? await card.file.fullUrl : null,
          上传时间: card.file?.uploadTime, // z.coerce.date().nullable()会自动转换
          文件类型: card.file?.type,
          银行卡号: card.cardNumber,
          持卡人姓名: card.cardholderName,
          银行名称: card.bankNameId // 这里可能需要关联银行名称表
        }))
    );
  }

  /**
   * 获取个人关联视频
   */
  async getPersonVideos(personId: number): Promise<any[]> {
    const orderPersonAssetRepo = this.dataSource.getRepository(OrderPersonAsset);

    // 视频类型枚举值，根据 order-person-asset.entity.ts 中的定义
    const videoTypes = ['salary_video', 'tax_video', 'checkin_video', 'work_video'];

    const videos = await orderPersonAssetRepo.find({
      where: {
        personId,
        assetType: In(videoTypes)
      },
      relations: ['file']
    });

    return Promise.all(
      videos.map(async (video) => ({
        视频类型: video.assetType,
        文件ID: video.fileId ? video.fileId.toString() : '',
        文件URL: video.file ? await video.file.fullUrl : null,
        上传时间: video.file?.uploadTime, // z.coerce.date().nullable()会自动转换
        文件类型: video.file?.type,
        关联订单ID: video.orderId
      }))
    );
  }

  /**
   * 验证人员是否属于指定企业
   */
  async validatePersonBelongsToCompany(personId: number, companyId: number): Promise<boolean> {
    const orderPersonRepo = this.dataSource.getRepository(OrderPerson);

    // 查询人员是否通过订单关联到该企业
    const count = await orderPersonRepo.count({
      where: {
        personId,
        order: { companyId }
      }
    });

    return count > 0;
  }

  /**
   * 获取企业专用人才列表
   */
  async findAllForCompany(companyId: number, query: {
    search?: string;
    disabilityType?: string;
    jobStatus?: string;
    page?: number | string;
    limit?: number | string;
  }): Promise<{ data: any[], total: number }> {
    const {
      search,
      disabilityType,
      jobStatus,
      page = 1,
      limit = 10
    } = query;

    // 工作状态到中文标签的映射（使用前端专用映射）
    const workStatusLabelMap: Record<string, string> = FrontendWorkStatusLabels;

    // 确保page和limit是数字
    const pageNum = typeof page === 'string' ? parseInt(page, 10) || 1 : page || 1;
    const limitNum = typeof limit === 'string' ? parseInt(limit, 10) || 10 : limit || 10;

    const orderPersonRepo = this.dataSource.getRepository(OrderPerson);
    const queryBuilder = orderPersonRepo.createQueryBuilder('op')
      .innerJoinAndSelect('op.person', 'person')
      .innerJoinAndSelect('op.order', 'order')
      .where('order.companyId = :companyId', { companyId });

    // 支持按关键词搜索（姓名、身份证号、残疾证号）
    if (search) {
      queryBuilder.andWhere('(person.name LIKE :search OR person.idCard LIKE :search OR person.disabilityId LIKE :search)', {
        search: `%${search}%`
      });
    }

    if (disabilityType) {
      queryBuilder.andWhere('person.disabilityType = :disabilityType', { disabilityType });
    }

    if (jobStatus) {
      // 工作状态筛选逻辑 - 支持中文状态和WorkStatus枚举值
      // 映射中文状态到WorkStatus枚举值
      const chineseToWorkStatus: Record<string, WorkStatus> = {
        '在职': WorkStatus.WORKING,
        '待入职': WorkStatus.PRE_WORKING,
        '离职': WorkStatus.RESIGNED,
        '未就业': WorkStatus.NOT_WORKING
      };

      let workStatusValue: WorkStatus | undefined;

      // 检查是否是有效的WorkStatus枚举值
      if (Object.values(WorkStatus).includes(jobStatus as WorkStatus)) {
        workStatusValue = jobStatus as WorkStatus;
      } else if (chineseToWorkStatus[jobStatus]) {
        workStatusValue = chineseToWorkStatus[jobStatus];
      }

      if (workStatusValue) {
        // 使用order_person.work_status进行筛选
        queryBuilder.andWhere('op.workStatus = :workStatus', { workStatus: workStatusValue });
      }
    }

    // 按人员ID去重（同一人员可能关联多个订单），获取最新订单信息
    queryBuilder.select([
      'person.id as personId',
      'person.name as name',
      'person.gender as gender',
      'person.idCard as idCard',
      'person.disabilityType as disabilityType',
      'person.disabilityLevel as disabilityLevel',
      'person.phone as phone',
      'person.birth_date as birthDate',
      'MAX(op.joinDate) as latestJoinDate',
      'MAX(op.salary_detail) as salaryDetail',
      'MAX(op.workStatus) as workStatus', // 获取最新工作状态
      'order.orderName as orderName'
    ])
      .groupBy('person.id, person.name, person.gender, person.idCard, person.disabilityType, person.disabilityLevel, person.phone, person.birth_date, order.orderName');

    // 按最新入职日期排序
    queryBuilder.orderBy('latestJoinDate', 'DESC');

    // 获取总数
    const totalQuery = queryBuilder.clone();
    const total = await totalQuery.getCount();

    // 分页
    queryBuilder.offset((pageNum - 1) * limitNum).limit(limitNum);

    const rawResults = await queryBuilder.getRawMany();

    // 转换结果格式 - 注意：PostgreSQL列名是小写的
    const data = rawResults.map((row) => {
      const workStatus = row.workstatus as WorkStatus | undefined;
      const workStatusLabel = workStatus ? workStatusLabelMap[workStatus] : '未知状态';

      return {
        personId: row.personid,
        name: row.name,
        gender: row.gender,
        idCard: row.idcard,
        disabilityType: row.disabilitytype,
        disabilityLevel: row.disabilitylevel,
        phone: row.phone,
        jobStatus: workStatusLabel, // 保持字段名兼容性，使用中文标签
        workStatus: workStatus, // 新增：枚举值
        workStatusLabel: workStatusLabel, // 新增：中文标签
        birthDate: row.birthdate,
        salaryDetail: row.salarydetail,
        latestJoinDate: row.latestjoindate,
        orderName: row.ordername
      };
    });

    return { data, total };
  }

  /**
   * 获取企业专用人才详情
   */
  async findOneForCompany(personId: number, companyId: number): Promise<any | null> {
    // 首先验证人员是否属于该企业
    const isValid = await this.validatePersonBelongsToCompany(personId, companyId);
    if (!isValid) {
      return null;
    }

    // 工作状态到中文标签的映射（使用前端专用映射）
    const workStatusLabelMap: Record<string, string> = FrontendWorkStatusLabels;

    // 获取人员基本信息
    const person = await this.findOne(personId);
    if (!person) {
      return null;
    }

    // 获取薪资信息（从order_person表获取最新薪资）
    const orderPersonRepo = this.dataSource.getRepository(OrderPerson);
    const latestOrderPerson = await orderPersonRepo.createQueryBuilder('op')
      .innerJoinAndSelect('op.order', 'order')
      .where('op.personId = :personId', { personId })
      .andWhere('order.companyId = :companyId', { companyId })
      .orderBy('op.joinDate', 'DESC')
      .getOne();

    // 获取关联数据
    const [bankCards, photos] = await Promise.all([
      this.bankCardRepository.find({
        where: { personId },
        relations: ['bankName']
      }),
      this.photoRepository.find({
        where: { personId },
        relations: ['file']
      })
    ]);

    // 转换银行卡数据
    const formattedBankCards = bankCards.map(card => ({
      cardId: card.id,
      bankName: card.bankName?.name || '',
      cardNumber: card.cardNumber,
      isDefault: card.isDefault
    }));

    // 转换照片数据
    const formattedPhotos = await Promise.all(photos.map(async photo => ({
      fileId: photo.fileId,
      fileName: photo.file?.name || '',
      fileUrl: photo.file ? await photo.file.fullUrl : ''
    })));

    // 确定工作状态
    const workStatus = latestOrderPerson?.workStatus as WorkStatus | undefined;
    const workStatusLabel = workStatus ? workStatusLabelMap[workStatus] : '未知状态';

    return {
      personId: person.id,
      name: person.name,
      gender: person.gender,
      idCard: person.idCard,
      disabilityType: person.disabilityType,
      disabilityLevel: person.disabilityLevel,
      birthDate: person.birthDate,
      salaryDetail: latestOrderPerson?.salaryDetail || null,
      phone: person.phone,
      jobStatus: workStatusLabel, // 保持字段名兼容性，使用中文标签
      workStatus: workStatus, // 新增：枚举值
      workStatusLabel: workStatusLabel, // 新增：中文标签
      bankCards: formattedBankCards,
      photos: formattedPhotos
    };
  }

  /**
   * 获取人才个人信息（用于人才小程序）
   * 直接返回数据库原始数据，日期字段由 schema 的 z.coerce.date() 自动处理
   */
  async getPersonalInfo(personId: number): Promise<any | null> {
    const person = await this.repository.findOne({
      where: { id: personId }
    });

    if (!person) {
      return null;
    }

    // 直接返回原始数据，不进行日期转换
    // z.coerce.date() 会在 parseWithAwait 时自动处理 Date/string 转换
    return {
      name: person.name,
      gender: person.gender,
      idCard: person.idCard,
      disabilityId: person.disabilityId,
      disabilityType: person.disabilityType,
      disabilityLevel: person.disabilityLevel,
      phone: person.phone,
      province: person.province,
      city: person.city,
      district: person.district,
      detailedAddress: person.detailedAddress,
      birthDate: person.birthDate,
      idAddress: person.idAddress,
      idValidDate: person.idValidDate,
      disabilityValidDate: person.disabilityValidDate,
      canDirectContact: person.canDirectContact,
      isMarried: person.isMarried,
      nation: person.nation,
      jobStatus: person.jobStatus,
      specificDisability: person.specificDisability
    };
  }

  /**
   * 获取人才银行卡列表（用于人才小程序）
   */
  async getBankCardsByPersonId(personId: number): Promise<any[]> {
    const bankCards = await this.bankCardRepository.find({
      where: { personId },
      relations: ['bankName', 'file']
    });

    return Promise.all(
      bankCards.map(async (card) => ({
        id: card.id,
        subBankName: card.subBankName,
        bankName: card.bankName?.name || null,
        cardNumber: this.maskCardNumber(card.cardNumber),
        cardholderName: card.cardholderName,
        cardType: card.cardType,
        isDefault: card.isDefault,
        fileUrl: card.file ? await card.file.fullUrl : null
      }))
    );
  }

  /**
   * 获取人才证件照片列表（用于人才小程序）
   */
  async getPhotosByPersonId(
    personId: number,
    photoType?: string,
    skip: number = 0,
    take: number = 10
  ): Promise<{ data: any[], total: number }> {
    const whereCondition: any = { personId };
    if (photoType) {
      whereCondition.photoType = photoType;
    }

    const [photos, total] = await this.photoRepository.findAndCount({
      where: whereCondition,
      relations: ['file'],
      order: { uploadTime: 'DESC' },
      skip,
      take
    });

    const data = await Promise.all(
      photos.map(async (photo) => ({
        id: photo.id,
        photoType: photo.photoType,
        fileUrl: photo.file ? await photo.file.fullUrl : null,
        fileName: photo.file?.name || null,
        uploadTime: photo.uploadTime.toISOString(),
        canDownload: photo.canDownload
      }))
    );

    return { data, total };
  }

  /**
   * 查询残疾人和企业关联信息
   * 支持多条件筛选：性别、残疾类别、残疾等级、年龄、户籍、残疾证号、公司、区、市
   */
  async findPersonsWithCompany(query: {
    gender?: string;
    disabilityType?: string;
    disabilityLevel?: string;
    minAge?: number;
    maxAge?: number;
    city?: string;
    district?: string;
    disabilityId?: string;
    companyId?: number;
    page?: number;
    limit?: number;
  }): Promise<{ data: any[], total: number }> {
    const {
      gender,
      disabilityType,
      disabilityLevel,
      minAge,
      maxAge,
      city,
      district,
      disabilityId,
      companyId,
      page = 1,
      limit = 10
    } = query;

    const orderPersonRepo = this.dataSource.getRepository(OrderPerson);

    // 构建查询：关联残疾人、订单和企业
    // 使用 leftJoin 确保没有公司的订单记录也能被查询到
    const queryBuilder = orderPersonRepo.createQueryBuilder('op')
      .innerJoin('op.person', 'person')
      .innerJoin('op.order', 'order')
      .leftJoin('order.company', 'company');

    // 基础筛选条件
    if (gender) {
      queryBuilder.andWhere('person.gender = :gender', { gender });
    }
    if (disabilityType) {
      queryBuilder.andWhere('person.disabilityType = :disabilityType', { disabilityType });
    }
    if (disabilityLevel) {
      queryBuilder.andWhere('person.disabilityLevel = :disabilityLevel', { disabilityLevel });
    }
    if (city) {
      queryBuilder.andWhere('person.city = :city', { city });
    }
    if (district) {
      queryBuilder.andWhere('person.district = :district', { district });
    }
    if (disabilityId) {
      queryBuilder.andWhere('person.disabilityId = :disabilityId', { disabilityId });
    }
    if (companyId) {
      queryBuilder.andWhere('order.companyId = :companyId', { companyId });
    }

    // 年龄筛选：根据出生日期计算
    if (minAge !== undefined || maxAge !== undefined) {
      const today = new Date();
      const minBirthDate = maxAge !== undefined
        ? new Date(today.getFullYear() - maxAge - 1, today.getMonth(), today.getDate())
        : undefined;
      const maxBirthDate = minAge !== undefined
        ? new Date(today.getFullYear() - minAge, today.getMonth(), today.getDate())
        : undefined;

      if (minBirthDate) {
        queryBuilder.andWhere('person.birthDate <= :minBirthDate', { minBirthDate });
      }
      if (maxBirthDate) {
        queryBuilder.andWhere('person.birthDate >= :maxBirthDate', { maxBirthDate });
      }
    }

    // 选择查询字段
    queryBuilder.select([
      'person.id as personId',
      'person.name as name',
      'person.gender as gender',
      'person.disabilityType as disabilityType',
      'person.disabilityLevel as disabilityLevel',
      'person.disabilityId as disabilityId',
      'person.city as city',
      'person.district as district',
      'COALESCE(company.companyName, \'\') as companyName',
      'order.id as orderId',
      'op.joinDate as joinDate'
    ]);

    // 分组（避免同一人员在不同订单中重复出现）
    queryBuilder.groupBy('person.id, person.name, person.gender, person.disabilityType, person.disabilityLevel, person.disabilityId, person.city, person.district, company.companyName, order.id, op.joinDate');

    // 排序
    queryBuilder.orderBy('op.joinDate', 'DESC');

    // 获取总数 - 使用单独的查询，因为 getCount() 在有自定义 select 时会失败
    const countQueryBuilder = orderPersonRepo.createQueryBuilder('op')
      .innerJoin('op.person', 'person')
      .innerJoin('op.order', 'order')
      .leftJoin('order.company', 'company');

    // 应用相同的筛选条件
    if (gender) {
      countQueryBuilder.andWhere('person.gender = :gender', { gender });
    }
    if (disabilityType) {
      countQueryBuilder.andWhere('person.disabilityType = :disabilityType', { disabilityType });
    }
    if (disabilityLevel) {
      countQueryBuilder.andWhere('person.disabilityLevel = :disabilityLevel', { disabilityLevel });
    }
    if (city) {
      countQueryBuilder.andWhere('person.city = :city', { city });
    }
    if (district) {
      countQueryBuilder.andWhere('person.district = :district', { district });
    }
    if (disabilityId) {
      countQueryBuilder.andWhere('person.disabilityId = :disabilityId', { disabilityId });
    }
    if (companyId) {
      countQueryBuilder.andWhere('order.companyId = :companyId', { companyId });
    }

    // 年龄筛选：根据出生日期计算
    if (minAge !== undefined || maxAge !== undefined) {
      const today = new Date();
      const minBirthDate = maxAge !== undefined
        ? new Date(today.getFullYear() - maxAge - 1, today.getMonth(), today.getDate())
        : undefined;
      const maxBirthDate = minAge !== undefined
        ? new Date(today.getFullYear() - minAge, today.getMonth(), today.getDate())
        : undefined;

      if (minBirthDate) {
        countQueryBuilder.andWhere('person.birthDate <= :minBirthDate', { minBirthDate });
      }
      if (maxBirthDate) {
        countQueryBuilder.andWhere('person.birthDate >= :maxBirthDate', { maxBirthDate });
      }
    }

    const total = Number(await countQueryBuilder.getCount()) || 0;

    // 分页
    queryBuilder.offset((page - 1) * limit).limit(limit);

    // 执行查询
    const rawResults = await queryBuilder.getRawMany();

    // 转换结果格式，确保字段类型正确
    const data = rawResults.map((row: any) => ({
      personId: Number(row.personid) || 0,
      name: String(row.name || ''),
      gender: String(row.gender || ''),
      disabilityType: String(row.disabilitytype || ''),
      disabilityLevel: String(row.disabilitylevel || ''),
      disabilityId: String(row.disabilityid || ''),
      city: String(row.city || ''),
      district: row.district || null,
      companyName: String(row.companyname || ''),
      orderId: Number(row.orderid) || 0,
      joinDate: row.joindate ? new Date(row.joindate) : new Date()
    }));

    return { data, total };
  }

  /**
   * 卡号脱敏工具函数
   * 保留前4位和后4位，中间用****代替
   */
  private maskCardNumber(cardNumber: string): string {
    if (!cardNumber || cardNumber.length < 8) {
      return cardNumber;
    }
    const prefix = cardNumber.substring(0, 4);
    const suffix = cardNumber.substring(cardNumber.length - 4);
    return `${prefix}****${suffix}`;
  }
}