package com.davos.assessment.admin;

import com.davos.assessment.assessment.*;
import com.davos.assessment.audit.*;
import com.davos.assessment.auth.UserPrincipal;
import com.davos.assessment.common.ApiException;
import com.davos.assessment.invitation.*;
import com.davos.assessment.profile.*;
import com.davos.assessment.project.*;
import com.davos.assessment.report.*;
import com.davos.assessment.user.*;
import jakarta.validation.Valid;
import jakarta.validation.constraints.Min;
import jakarta.validation.constraints.NotBlank;
import java.time.Instant;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.awt.image.BufferedImage;
import java.nio.charset.StandardCharsets;
import java.time.LocalDate;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.time.ZoneOffset;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;
import java.util.Locale;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;
import org.apache.pdfbox.pdmodel.PDDocument;
import org.apache.pdfbox.rendering.PDFRenderer;
import org.apache.fontbox.ttf.TrueTypeCollection;
import org.apache.fontbox.ttf.TrueTypeFont;
import org.apache.pdfbox.pdmodel.PDPage;
import org.apache.pdfbox.pdmodel.PDPageContentStream;
import org.apache.pdfbox.pdmodel.common.PDRectangle;
import org.apache.pdfbox.pdmodel.font.PDFont;
import org.apache.pdfbox.pdmodel.font.PDType0Font;
import org.apache.pdfbox.pdmodel.font.PDType1Font;
import org.apache.pdfbox.pdmodel.graphics.image.PDImageXObject;
import org.apache.pdfbox.multipdf.PDFMergerUtility;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.Document;
import org.apache.poi.xwpf.usermodel.ParagraphAlignment;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableCell;
import org.apache.poi.xwpf.usermodel.XWPFTableRow;
import org.apache.poi.util.Units;
import javax.imageio.ImageIO;
import org.springframework.core.io.ClassPathResource;
import org.springframework.http.HttpStatus;
import org.springframework.http.HttpHeaders;
import org.springframework.http.ContentDisposition;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.security.core.annotation.AuthenticationPrincipal;
import org.springframework.security.crypto.password.PasswordEncoder;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;

@RestController
@RequestMapping({"/api/admin", "/api/projects/{slug}/admin"})
public class AdminController {
  private static final String COVER_PERSONAL = "report-templates/cover-personal.png";
  private static final String TAIL_IMAGE = "report-templates/tail.jpg";
  private static final ZoneId SHANGHAI = ZoneId.of("Asia/Shanghai");
  private static final DateTimeFormatter DATE_FORMAT = DateTimeFormatter.ofPattern("yyyy-MM-dd");
  private static final Pattern INT_PATTERN = Pattern.compile("-?\\d+");
  private static final Pattern JSON_INT_FIELD = Pattern.compile("\"%s\"\\s*:\\s*(-?\\d+)");

  private final UserRepository userRepository;
  private final ProfileRepository profileRepository;
  private final SubmissionRepository submissionRepository;
  private final AssessmentRepository assessmentRepository;
  private final UserAssessmentAccessRepository userAssessmentAccessRepository;
  private final AnswerRepository answerRepository;
  private final ReportRepository reportRepository;
  private final ReportController reportController;
  private final InvitationCodeRepository invitationCodeRepository;
  private final AuditLogRepository auditLogRepository;
  private final AuditService auditService;
  private final PasswordEncoder passwordEncoder;
  private final ProjectAccessService projectAccessService;
  private final ProjectMembershipRepository membershipRepository;
  private final ProjectAssessmentRepository projectAssessmentRepository;
  private final ProjectDataExportService projectDataExportService;

  public AdminController(
      UserRepository userRepository,
      ProfileRepository profileRepository,
      SubmissionRepository submissionRepository,
      AssessmentRepository assessmentRepository,
      UserAssessmentAccessRepository userAssessmentAccessRepository,
      AnswerRepository answerRepository,
      ReportRepository reportRepository,
      ReportController reportController,
      InvitationCodeRepository invitationCodeRepository,
      AuditLogRepository auditLogRepository,
      AuditService auditService,
      PasswordEncoder passwordEncoder,
      ProjectAccessService projectAccessService,
      ProjectMembershipRepository membershipRepository,
      ProjectAssessmentRepository projectAssessmentRepository,
      ProjectDataExportService projectDataExportService) {
    this.userRepository = userRepository;
    this.profileRepository = profileRepository;
    this.submissionRepository = submissionRepository;
    this.assessmentRepository = assessmentRepository;
    this.userAssessmentAccessRepository = userAssessmentAccessRepository;
    this.answerRepository = answerRepository;
    this.reportRepository = reportRepository;
    this.reportController = reportController;
    this.invitationCodeRepository = invitationCodeRepository;
    this.auditLogRepository = auditLogRepository;
    this.auditService = auditService;
    this.passwordEncoder = passwordEncoder;
    this.projectAccessService = projectAccessService;
    this.membershipRepository = membershipRepository;
    this.projectAssessmentRepository = projectAssessmentRepository;
    this.projectDataExportService = projectDataExportService;
  }

  @GetMapping("/dashboard")
  public DashboardResponse dashboard(@AuthenticationPrincipal UserPrincipal principal) {
    Project project = requireAdminRole(principal);
    long userCount = membershipRepository.countByProjectAndRole(project, ProjectMembershipRole.PARTICIPANT);
    long submissionCount = submissionRepository.countByProject(project);
    long reportCount = reportRepository.countByProject(project);
    long inviteCount = invitationCodeRepository.findByProject(project).size();
    return new DashboardResponse(userCount, submissionCount, reportCount, inviteCount);
  }

  @GetMapping("/users")
  @Transactional(readOnly = true)
  public List<UserRow> users(@AuthenticationPrincipal UserPrincipal principal, @RequestParam(required = false) String organization) {
    Project project = requireAdminRole(principal);
    List<Assessment> activeAssessments = activeAssessments(project);
    return projectUsers(project).stream()
        .map(user -> {
          Profile profile = projectProfile(project, user);
          CompositeResult composite = buildCompositeResultForUser(user);
          String riskLevel = composite.completedAll ? composite.level : "未完成";
          return UserRow.from(user, profile, completionSummary(user, activeAssessments), riskLevel);
        })
        .filter(row -> blank(organization) || row.organization().toLowerCase(Locale.ROOT).contains(organization.toLowerCase(Locale.ROOT)))
        .toList();
  }

  @GetMapping("/users/{id}")
  @Transactional(readOnly = true)
  public UserDetail userDetail(@AuthenticationPrincipal UserPrincipal principal, @PathVariable Long id) {
    Project project = requireAdminRole(principal);
    AppUser user = requireProjectUser(project, id);
    auditService.record(project, principal.user(), "VIEW_USER_DETAIL", "USER", id.toString());
    return UserDetail.from(user, projectProfile(project, user));
  }

  @PostMapping("/users/admin")
  @Transactional
  public UserRow createAdmin(@AuthenticationPrincipal UserPrincipal principal, @Valid @RequestBody CreateAdminRequest request) {
    Project project = requireOperationalAdmin(principal);
    String email = normalizeEmail(request.email());
    if (request.password() == null || request.password().length() < 8) {
      throw new ApiException(HttpStatus.BAD_REQUEST, "管理员密码至少 8 位");
    }
    if (userRepository.existsByEmailIgnoreCase(email)) {
      throw new ApiException(HttpStatus.CONFLICT, "该邮箱已存在");
    }

    AppUser user = new AppUser();
    user.setEmail(email);
    user.setPasswordHash(passwordEncoder.encode(request.password()));
    user.setRole(UserRole.USER);
    userRepository.save(user);

    ProjectMembership membership = new ProjectMembership();
    membership.setProject(project);
    membership.setUser(user);
    membership.setRole(ProjectMembershipRole.PROJECT_ADMIN);
    membership.setEnabled(true);
    membershipRepository.save(membership);
    auditService.record(project, principal.user(), "CREATE_PROJECT_ADMIN", "USER", user.getId().toString());
    return UserRow.from(user, null, "", "项目管理员");
  }

  @PatchMapping("/users/{id}/toggle")
  @Transactional
  public UserRow toggleUser(@AuthenticationPrincipal UserPrincipal principal, @PathVariable Long id) {
    Project project = requireOperationalAdmin(principal);
    AppUser user = requireProjectUser(project, id);
    if (user.getId().equals(principal.user().getId())) {
      throw new ApiException(HttpStatus.BAD_REQUEST, "不能禁用当前登录管理员");
    }
    ProjectMembership membership = membershipRepository.findByProjectAndUserId(project, id).orElseThrow();
    membership.setEnabled(!membership.isEnabled());
    membershipRepository.save(membership);
    auditService.record(project, principal.user(), "TOGGLE_USER", "USER", id.toString());
    CompositeResult composite = buildCompositeResultForUser(user);
    String riskLevel = composite.completedAll ? composite.level : "未完成";
    return UserRow.from(user, projectProfile(project, user), completionSummary(user, activeAssessments(project)), riskLevel);
  }

  @PatchMapping("/users/{id}/password")
  @Transactional
  public UserRow resetPassword(@AuthenticationPrincipal UserPrincipal principal, @PathVariable Long id, @Valid @RequestBody ResetPasswordRequest request) {
    Project project = requireOperationalAdmin(principal);
    AppUser user = requireProjectUser(project, id);
    user.setPasswordHash(passwordEncoder.encode(request.password()));
    userRepository.save(user);
    auditService.record(project, principal.user(), "RESET_USER_PASSWORD", "USER", id.toString());
    CompositeResult composite = buildCompositeResultForUser(user);
    String riskLevel = composite.completedAll ? composite.level : "未完成";
    return UserRow.from(user, projectProfile(project, user), completionSummary(user, activeAssessments(project)), riskLevel);
  }

  @GetMapping("/users/{id}/assessments")
  @Transactional(readOnly = true)
  public List<UserAssessmentStatusRow> userAssessmentStatus(@AuthenticationPrincipal UserPrincipal principal, @PathVariable Long id) {
    Project project = requireAdminRole(principal);
    AppUser user = requireProjectUser(project, id);
    List<Assessment> activeAssessments = activeAssessments(project);
    return activeAssessments.stream().map(assessment -> {
      long doneCount = submissionRepository.countByProjectAndUserAndAssessment(project, user, assessment);
      boolean completed = doneCount > 0;
      UserAssessmentAccess access = userAssessmentAccessRepository.findByProjectAndUserAndAssessment(project, user, assessment).orElse(null);
      boolean canRetake = access != null && access.isCanRetake();
      String lastSubmittedAt = submissionRepository.findByProjectAndUserAndAssessmentOrderBySubmittedAtDesc(project, user, assessment).stream().findFirst()
          .map(item -> item.getSubmittedAt().toString()).orElse("");
      return new UserAssessmentStatusRow(assessment.getTestPaperId(), assessment.getTitle(), completed, canRetake, doneCount, lastSubmittedAt);
    }).toList();
  }

  @GetMapping("/users/{id}/assessment-reports")
  @Transactional(readOnly = true)
  public UserAssessmentReportOverview userAssessmentReports(@AuthenticationPrincipal UserPrincipal principal, @PathVariable Long id) {
    Project project = requireAdminRole(principal);
    AppUser user = requireProjectUser(project, id);
    Profile profile = projectProfile(project, user);
    List<UserAssessmentReportRow> rows = activeAssessments(project).stream()
        .map(assessment -> {
          List<Submission> list = submissionRepository.findByProjectAndUserAndAssessmentOrderBySubmittedAtDesc(project, user, assessment);
          if (list.isEmpty()) {
            return new UserAssessmentReportRow(assessment.getTestPaperId(), assessment.getTitle(), false, 0, "", null, null, "", "");
          }
          Submission latest = list.get(0);
          Report report = reportRepository.findBySubmission(latest).orElse(null);
          return new UserAssessmentReportRow(
              assessment.getTestPaperId(),
              assessment.getTitle(),
              true,
              list.size(),
              latest.getSubmittedAt().toString(),
              latest.getId(),
              report == null ? null : report.getId(),
              report == null ? "" : report.getSummary(),
              report == null ? "" : report.getSuggestions());
        })
        .toList();
    CompositeResult composite = buildCompositeResult(rows);
    String comprehensiveText = composite.completedAll
        ? "综合等级：" + composite.level + "（综合分 " + composite.score + "）"
        : "尚未完成全部试卷（" + composite.progress + "）";
    return new UserAssessmentReportOverview(
        UserDetail.from(user, profile),
        rows,
        comprehensiveText,
        composite.completedAll,
        composite.progress,
        composite.score,
        composite.level,
        composite.triggers);
  }

  @PatchMapping("/users/{id}/assessments/{testPaperId}/reopen")
  @Transactional
  public UserAssessmentStatusRow reopenAssessment(
      @AuthenticationPrincipal UserPrincipal principal,
      @PathVariable Long id,
      @PathVariable String testPaperId,
      @RequestBody(required = false) ReopenAssessmentRequest request) {
    Project project = requireOperationalAdmin(principal);
    AppUser user = requireProjectUser(project, id);
    Assessment assessment = assessmentRepository.findByTestPaperId(testPaperId)
        .orElseThrow(() -> new ApiException(HttpStatus.NOT_FOUND, "量表不存在"));
    if (submissionRepository.countByProjectAndUserAndAssessment(project, user, assessment) == 0) {
      throw new ApiException(HttpStatus.BAD_REQUEST, "该用户尚未完成该量表，无需重开");
    }
    UserAssessmentAccess access = userAssessmentAccessRepository.findByProjectAndUserAndAssessment(project, user, assessment).orElseGet(() -> {
      UserAssessmentAccess item = new UserAssessmentAccess();
      item.setProject(project);
      item.setUser(user);
      item.setAssessment(assessment);
      return item;
    });
    access.setCanRetake(true);
    access.setReopenedAt(Instant.now());
    access.setReopenNote(request == null ? "" : request.note());
    userAssessmentAccessRepository.save(access);
    auditService.record(project, principal.user(), "REOPEN_ASSESSMENT", "USER_ASSESSMENT", user.getId() + ":" + assessment.getTestPaperId());
    long doneCount = submissionRepository.countByProjectAndUserAndAssessment(project, user, assessment);
    String lastSubmittedAt = submissionRepository.findByProjectAndUserAndAssessmentOrderBySubmittedAtDesc(project, user, assessment).stream().findFirst()
        .map(item -> item.getSubmittedAt().toString()).orElse("");
    return new UserAssessmentStatusRow(assessment.getTestPaperId(), assessment.getTitle(), true, true, doneCount, lastSubmittedAt);
  }

  @PatchMapping("/assessments/reopen")
  @Transactional
  public BatchReopenResponse reopenAssessmentsBatch(
      @AuthenticationPrincipal UserPrincipal principal,
      @Valid @RequestBody BatchReopenRequest request) {
    Project project = requireOperationalAdmin(principal);
    Assessment assessment = assessmentRepository.findByTestPaperId(request.testPaperId())
        .orElseThrow(() -> new ApiException(HttpStatus.NOT_FOUND, "量表不存在"));
    int opened = 0;
    int skipped = 0;
    for (Long userId : request.userIds()) {
      AppUser user = membershipRepository.findByProjectAndUserId(project, userId).map(ProjectMembership::getUser).orElse(null);
      if (user == null) {
        skipped++;
        continue;
      }
      if (submissionRepository.countByProjectAndUserAndAssessment(project, user, assessment) == 0) {
        skipped++;
        continue;
      }
      UserAssessmentAccess access = userAssessmentAccessRepository.findByProjectAndUserAndAssessment(project, user, assessment).orElseGet(() -> {
        UserAssessmentAccess item = new UserAssessmentAccess();
        item.setProject(project);
        item.setUser(user);
        item.setAssessment(assessment);
        return item;
      });
      access.setCanRetake(true);
      access.setReopenedAt(Instant.now());
      access.setReopenNote(request.note() == null ? "" : request.note());
      userAssessmentAccessRepository.save(access);
      opened++;
    }
    auditService.record(project, principal.user(), "BATCH_REOPEN_ASSESSMENT", "USER_ASSESSMENT", assessment.getTestPaperId() + ":" + opened);
    return new BatchReopenResponse(assessment.getTestPaperId(), opened, skipped);
  }

  @GetMapping("/submissions")
  @Transactional(readOnly = true)
  public List<SubmissionRow> submissions(
      @AuthenticationPrincipal UserPrincipal principal,
      @RequestParam(required = false) String assessment,
      @RequestParam(required = false) String organization,
      @RequestParam(required = false) String level,
      @RequestParam(required = false) String from,
      @RequestParam(required = false) String to) {
    Project project = requireAdminRole(principal);
    return filteredSubmissions(assessment, organization, level, from, to).stream().map(item -> SubmissionRow.from(item, projectProfile(project, item.getUser()))).toList();
  }

  @GetMapping("/submissions/{id}")
  @Transactional(readOnly = true)
  public SubmissionDetail submission(@AuthenticationPrincipal UserPrincipal principal, @PathVariable Long id) {
    Project project = requireAdminRole(principal);
    Submission submission = submissionRepository.findById(id)
        .orElseThrow(() -> new ApiException(HttpStatus.NOT_FOUND, "答卷不存在"));
    if (!sameProject(project, submission)) throw new ApiException(HttpStatus.NOT_FOUND, "答卷不存在");
    List<AnswerRow> answers = answerRepository.findBySubmission(submission).stream().map(AnswerRow::from).toList();
    Report report = reportRepository.findBySubmission(submission).orElse(null);
    auditService.record(project, principal.user(), "VIEW_SUBMISSION_DETAIL", "SUBMISSION", id.toString());
    return SubmissionDetail.from(submission, answers, report, projectProfile(project, submission.getUser()));
  }

  @GetMapping("/submissions/export.csv")
  @Transactional(readOnly = true)
  public ResponseEntity<String> exportSubmissions(@AuthenticationPrincipal UserPrincipal principal) {
    Project project = requireAdminRole(principal);
    String header = "id,email,organization,assessment,rawScore,level,submittedAt\n";
    String body = submissionRepository.findByProjectOrderBySubmittedAtDesc(project).stream()
        .map(item -> SubmissionRow.from(item, projectProfile(project, item.getUser())))
        .map(row -> csv(row.id()) + "," + csv(row.email()) + "," + csv(row.organization()) + "," + csv(row.assessment()) + "," + row.rawScore() + "," + csv(row.level()) + "," + csv(row.submittedAt()))
        .collect(Collectors.joining("\n"));
    auditService.record(project, principal.user(), "EXPORT_SUBMISSIONS_CSV", "SUBMISSION", "all");
    return ResponseEntity.ok()
        .contentType(MediaType.parseMediaType("text/csv; charset=UTF-8"))
        .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + project.getSlug() + "-submissions.csv")
        .body(header + body);
  }

  @GetMapping("/submissions/export.xlsx")
  @Transactional(readOnly = true)
  public ResponseEntity<byte[]> exportSubmissionsExcel(
      @AuthenticationPrincipal UserPrincipal principal,
      @RequestParam(required = false) String assessment,
      @RequestParam(required = false) String organization,
      @RequestParam(required = false) String level,
      @RequestParam(required = false) String from,
      @RequestParam(required = false) String to) throws IOException {
    Project project = requireAdminRole(principal);
    try (Workbook workbook = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream()) {
      Sheet sheet = workbook.createSheet("答卷记录");
      Row header = sheet.createRow(0);
      String[] columns = {"ID", "邮箱", "公司/机构", "量表", "分数", "报告等级", "提交时间"};
      for (int i = 0; i < columns.length; i++) header.createCell(i).setCellValue(columns[i]);
      int rowIndex = 1;
      for (Submission submission : filteredSubmissions(assessment, organization, level, from, to)) {
        SubmissionRow item = SubmissionRow.from(submission, projectProfile(project, submission.getUser()));
        Row row = sheet.createRow(rowIndex++);
        row.createCell(0).setCellValue(item.id());
        row.createCell(1).setCellValue(item.email());
        row.createCell(2).setCellValue(item.organization());
        row.createCell(3).setCellValue(item.assessment());
        row.createCell(4).setCellValue(item.rawScore());
        row.createCell(5).setCellValue(item.level());
        row.createCell(6).setCellValue(item.submittedAt());
      }
      for (int i = 0; i < columns.length; i++) sheet.autoSizeColumn(i);
      workbook.write(out);
      auditService.record(project, principal.user(), "EXPORT_SUBMISSIONS_EXCEL", "SUBMISSION", "filtered");
      return ResponseEntity.ok()
          .contentType(MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"))
          .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + project.getSlug() + "-submissions.xlsx")
          .body(out.toByteArray());
    }
  }

  @GetMapping("/exports/users-summary.csv")
  @Transactional(readOnly = true)
  public ResponseEntity<String> exportUsersSummaryCsv(@AuthenticationPrincipal UserPrincipal principal) {
    Project project = requireAdminRole(principal);
    String header = "姓名,邮箱,公司/机构,SCL90完成,SCL90时间,SDS完成,SDS时间,SAS完成,SAS时间,总完成数,账号创建时间\n";
    String body = projectUsers(project).stream()
        .map(this::userSummaryRow)
        .collect(Collectors.joining("\n"));
    auditService.record(project, principal.user(), "EXPORT_USERS_SUMMARY_CSV", "USER", "all");
    return ResponseEntity.ok()
        .contentType(MediaType.parseMediaType("text/csv; charset=UTF-8"))
        .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + project.getSlug() + "-users-summary.csv")
        .body(header + body);
  }

  @GetMapping("/exports/users-summary.xlsx")
  @Transactional(readOnly = true)
  public ResponseEntity<byte[]> exportUsersSummaryExcel(@AuthenticationPrincipal UserPrincipal principal) throws IOException {
    Project project = requireAdminRole(principal);
    try (Workbook workbook = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream()) {
      Sheet sheet = workbook.createSheet("用户总览");
      String[] columns = {"姓名", "邮箱", "公司/机构", "SCL90完成", "SCL90时间", "SDS完成", "SDS时间", "SAS完成", "SAS时间", "总完成数", "账号创建时间"};
      Row header = sheet.createRow(0);
      for (int i = 0; i < columns.length; i++) header.createCell(i).setCellValue(columns[i]);
      int rowIndex = 1;
      for (AppUser user : projectUsers(project)) {
        Profile profile = projectProfile(project, user);
        SubmissionStatusCell scl = latestStatus(user, "SCL90-2078");
        SubmissionStatusCell sds = latestStatus(user, "SDS-2082");
        SubmissionStatusCell sas = latestStatus(user, "SAS-2083");
        Row row = sheet.createRow(rowIndex++);
        row.createCell(0).setCellValue(profile == null ? "" : profile.getName());
        row.createCell(1).setCellValue(user.getEmail());
        row.createCell(2).setCellValue(profile == null ? "" : profile.getOrganization());
        row.createCell(3).setCellValue(scl.done ? "是" : "否");
        row.createCell(4).setCellValue(scl.time);
        row.createCell(5).setCellValue(sds.done ? "是" : "否");
        row.createCell(6).setCellValue(sds.time);
        row.createCell(7).setCellValue(sas.done ? "是" : "否");
        row.createCell(8).setCellValue(sas.time);
        row.createCell(9).setCellValue((scl.done ? 1 : 0) + (sds.done ? 1 : 0) + (sas.done ? 1 : 0));
        row.createCell(10).setCellValue(user.getCreatedAt().toString());
      }
      for (int i = 0; i < columns.length; i++) sheet.autoSizeColumn(i);
      workbook.write(out);
      auditService.record(project, principal.user(), "EXPORT_USERS_SUMMARY_EXCEL", "USER", "all");
      return ResponseEntity.ok()
          .contentType(MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"))
          .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + project.getSlug() + "-users-summary.xlsx")
          .body(out.toByteArray());
    }
  }

  @GetMapping("/exports/submissions-detail.csv")
  @Transactional(readOnly = true)
  public ResponseEntity<String> exportSubmissionsDetailCsv(@AuthenticationPrincipal UserPrincipal principal) {
    Project project = requireAdminRole(principal);
    String header = "姓名,邮箱,公司/机构,量表,分数,等级,提交时间,报告ID\n";
    String body = submissionRepository.findByProjectOrderBySubmittedAtDesc(project).stream()
        .map(this::submissionDetailRow)
        .collect(Collectors.joining("\n"));
    auditService.record(project, principal.user(), "EXPORT_SUBMISSIONS_DETAIL_CSV", "SUBMISSION", "all");
    return ResponseEntity.ok()
        .contentType(MediaType.parseMediaType("text/csv; charset=UTF-8"))
        .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + project.getSlug() + "-submissions-detail.csv")
        .body(header + body);
  }

  @GetMapping("/exports/submissions-detail.xlsx")
  @Transactional(readOnly = true)
  public ResponseEntity<byte[]> exportSubmissionsDetailExcel(@AuthenticationPrincipal UserPrincipal principal) throws IOException {
    Project project = requireAdminRole(principal);
    try (Workbook workbook = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream()) {
      Sheet sheet = workbook.createSheet("答卷明细");
      String[] columns = {"姓名", "邮箱", "公司/机构", "量表", "分数", "等级", "提交时间", "报告ID"};
      Row header = sheet.createRow(0);
      for (int i = 0; i < columns.length; i++) header.createCell(i).setCellValue(columns[i]);
      int rowIndex = 1;
      for (Submission submission : submissionRepository.findByProjectOrderBySubmittedAtDesc(project)) {
        Profile profile = projectProfile(project, submission.getUser());
        Report report = reportRepository.findBySubmission(submission).orElse(null);
        Row row = sheet.createRow(rowIndex++);
        row.createCell(0).setCellValue(profile == null ? "" : profile.getName());
        row.createCell(1).setCellValue(submission.getUser().getEmail());
        row.createCell(2).setCellValue(profile == null ? "" : profile.getOrganization());
        row.createCell(3).setCellValue(submission.getAssessment().getTitle());
        row.createCell(4).setCellValue(submission.getRawScore());
        row.createCell(5).setCellValue(submission.getLevel());
        row.createCell(6).setCellValue(submission.getSubmittedAt().toString());
        row.createCell(7).setCellValue(report == null ? "" : report.getId().toString());
      }
      for (int i = 0; i < columns.length; i++) sheet.autoSizeColumn(i);
      workbook.write(out);
      auditService.record(project, principal.user(), "EXPORT_SUBMISSIONS_DETAIL_EXCEL", "SUBMISSION", "all");
      return ResponseEntity.ok()
          .contentType(MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"))
          .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + project.getSlug() + "-submissions-detail.xlsx")
          .body(out.toByteArray());
    }
  }

  @GetMapping("/exports/preview")
  @Transactional(readOnly = true)
  public ExportPreviewResponse exportPreview(
      @AuthenticationPrincipal UserPrincipal principal,
      @RequestParam String template,
      @RequestParam(required = false) String sortBy,
      @RequestParam(required = false) String sortDir,
      @RequestParam(required = false) String assessment,
      @RequestParam(required = false) String organization,
      @RequestParam(required = false) String level,
      @RequestParam(required = false, defaultValue = "false") boolean latestOnly,
      @RequestParam(required = false) String from,
      @RequestParam(required = false) String to) {
    requireAdminRole(principal);
    ExportDataset dataset = buildExportDataset(template, sortBy, sortDir, assessment, organization, level, latestOnly, from, to);
    List<List<String>> preview = dataset.rows().stream().limit(20).toList();
    return new ExportPreviewResponse(dataset.headers(), preview, dataset.rows().size());
  }

  @GetMapping("/exports/download")
  @Transactional
  public ResponseEntity<byte[]> exportDownload(
      @AuthenticationPrincipal UserPrincipal principal,
      @RequestParam String template,
      @RequestParam String format,
      @RequestParam(required = false) String sortBy,
      @RequestParam(required = false) String sortDir,
      @RequestParam(required = false) String assessment,
      @RequestParam(required = false) String organization,
      @RequestParam(required = false) String level,
      @RequestParam(required = false, defaultValue = "false") boolean latestOnly,
      @RequestParam(required = false) String from,
      @RequestParam(required = false) String to) throws IOException {
    Project project = requireAdminRole(principal);
    ExportDataset dataset = buildExportDataset(template, sortBy, sortDir, assessment, organization, level, latestOnly, from, to);
    String safeTemplate = template.toLowerCase(Locale.ROOT).replaceAll("[^a-z0-9_-]", "");
    if ("xlsx".equalsIgnoreCase(format)) {
      byte[] file = buildXlsx(dataset);
      auditService.record(project, principal.user(), "EXPORT_CENTER_XLSX", "EXPORT", safeTemplate);
      return ResponseEntity.ok()
          .contentType(MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"))
          .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + project.getSlug() + "-" + safeTemplate + ".xlsx")
          .body(file);
    }
    if ("docx".equalsIgnoreCase(format)) {
      byte[] file = buildDocx("导出中心-" + safeTemplate, dataset);
      auditService.record(project, principal.user(), "EXPORT_CENTER_DOCX", "EXPORT", safeTemplate);
      return ResponseEntity.ok()
          .contentType(MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.wordprocessingml.document"))
          .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + project.getSlug() + "-" + safeTemplate + ".docx")
          .body(file);
    }
    byte[] file = buildCsv(dataset).getBytes(java.nio.charset.StandardCharsets.UTF_8);
    auditService.record(project, principal.user(), "EXPORT_CENTER_CSV", "EXPORT", safeTemplate);
    return ResponseEntity.ok()
        .contentType(MediaType.parseMediaType("text/csv; charset=UTF-8"))
        .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + project.getSlug() + "-" + safeTemplate + ".csv")
        .body(file);
  }

  @GetMapping("/exports/all-data")
  @Transactional(readOnly = true)
  public ResponseEntity<byte[]> exportAllData(@AuthenticationPrincipal UserPrincipal principal) throws IOException {
    Project project = requireAdminRole(principal);
    byte[] file = projectDataExportService.exportProject(project);
    auditService.record(project, principal.user(), "EXPORT_PROJECT_ALL_DATA", "PROJECT", project.getSlug());
    HttpHeaders headers = new HttpHeaders();
    headers.setContentType(MediaType.parseMediaType("application/zip"));
    headers.setContentDisposition(ContentDisposition.attachment().filename(projectDataExportService.filename(project), StandardCharsets.UTF_8).build());
    return ResponseEntity.ok().headers(headers).body(file);
  }

  @GetMapping("/reports")
  @Transactional(readOnly = true)
  public List<ReportRow> reports(
      @AuthenticationPrincipal UserPrincipal principal,
      @RequestParam(required = false) String assessment,
      @RequestParam(required = false) String organization,
      @RequestParam(required = false) String level,
      @RequestParam(required = false) String from,
      @RequestParam(required = false) String to) {
    Project project = requireAdminRole(principal);
    return reportRepository.findByProjectOrderByCreatedAtDesc(project).stream()
        .filter(report -> matches(report.getSubmission(), assessment, organization, level, from, to))
        .map(ReportRow::from)
        .toList();
  }

  @GetMapping("/invitation-codes")
  public List<InviteRow> invitationCodes(@AuthenticationPrincipal UserPrincipal principal) {
    Project project = requireAdminRole(principal);
    return invitationCodeRepository.findByProject(project).stream().map(InviteRow::from).toList();
  }

  @PostMapping("/invitation-codes")
  @Transactional
  public InviteRow createInvitation(@AuthenticationPrincipal UserPrincipal principal, @Valid @RequestBody InviteRequest request) {
    Project project = requireOperationalAdmin(principal);
    if (invitationCodeRepository.existsByProjectAndCodeIgnoreCase(project, request.code())) {
      throw new ApiException(HttpStatus.CONFLICT, "邀请码已存在");
    }
    InvitationCode code = new InvitationCode();
    code.setProject(project);
    code.setCode(request.code().trim().toUpperCase());
    code.setMaxUses(request.maxUses());
    code.setExpiresAt(request.expiresAt() == null || request.expiresAt().isBlank() ? null : Instant.parse(request.expiresAt()));
    invitationCodeRepository.save(code);
    auditService.record(project, principal.user(), "CREATE_INVITATION", "INVITATION_CODE", code.getId().toString());
    return InviteRow.from(code);
  }

  @PatchMapping("/invitation-codes/{id}/toggle")
  @Transactional
  public InviteRow toggleInvitation(@AuthenticationPrincipal UserPrincipal principal, @PathVariable Long id) {
    Project project = requireOperationalAdmin(principal);
    InvitationCode code = invitationCodeRepository.findById(id)
        .orElseThrow(() -> new ApiException(HttpStatus.NOT_FOUND, "邀请码不存在"));
    if (code.getProject() == null || !code.getProject().getId().equals(project.getId())) {
      throw new ApiException(HttpStatus.NOT_FOUND, "邀请码不存在");
    }
    code.setEnabled(!code.isEnabled());
    auditService.record(project, principal.user(), "TOGGLE_INVITATION", "INVITATION_CODE", id.toString());
    return InviteRow.from(invitationCodeRepository.save(code));
  }

  @GetMapping("/audit-logs")
  @Transactional(readOnly = true)
  public List<AuditRow> auditLogs(@AuthenticationPrincipal UserPrincipal principal) {
    Project project = requireAdminRole(principal);
    return auditLogRepository.findTop100ByProjectOrderByCreatedAtDesc(project).stream().map(AuditRow::from).toList();
  }

  @GetMapping("/users/{id}/exports/download")
  @Transactional(readOnly = true)
  public ResponseEntity<byte[]> exportUserDetail(
      @AuthenticationPrincipal UserPrincipal principal,
      @PathVariable Long id,
      @RequestParam String format,
      @RequestParam(required = false) Long reportId) throws IOException {
    Project project = requireAdminRole(principal);
    AppUser user = requireProjectUser(project, id);
    String ext = normalizeFormat(format);
    byte[] body;
    String filename;
    Profile profile = projectProfile(project, user);
    String person = filenameSafe((profile == null ? "" : profile.getName()).isBlank() ? user.getEmail() : profile.getName());
    if (reportId != null) {
      Report report = reportRepository.findById(reportId).orElseThrow(() -> new ApiException(HttpStatus.NOT_FOUND, "报告不存在"));
      if (!report.getUser().getId().equals(id) || !sameProject(project, report)) {
        throw new ApiException(HttpStatus.BAD_REQUEST, "报告不属于该用户");
      }
      String paperId = report.getSubmission() != null
          && report.getSubmission().getAssessment() != null
          ? filenameSafe(report.getSubmission().getAssessment().getTestPaperId())
          : "unknown-paper";
      body = "pdf".equals(ext)
          ? buildReportPdfUsingReportController(principal, report)
          : ("docx".equals(ext)
          ? buildReportDocxUsingReportController(principal, report)
          : buildSubmissionTabular(report.getSubmission(), report, ext));
      filename = person + "-user" + id + "-" + paperId + "-report" + reportId + "." + ext;
    } else {
      body = "pdf".equals(ext) ? buildUserPdf(user) : buildUserTabular(user, ext);
      filename = person + "-user" + id + "-profile." + ext;
    }
    MediaType type = mediaTypeForFormat(ext);
    auditService.record(project, principal.user(), "EXPORT_USER_DETAIL", "USER", id + ":" + ext + (reportId == null ? "" : (":report-" + reportId)));
    HttpHeaders headers = new HttpHeaders();
    headers.setContentType(type);
    headers.setContentDisposition(ContentDisposition.attachment().filename(filename, StandardCharsets.UTF_8).build());
    return ResponseEntity.ok().headers(headers).body(body);
  }

  @GetMapping("/users/exports/batch")
  @Transactional(readOnly = true)
  public ResponseEntity<byte[]> exportUsersBatch(
      @AuthenticationPrincipal UserPrincipal principal,
      @RequestParam String format,
      @RequestParam List<Long> userIds) throws IOException {
    Project project = requireAdminRole(principal);
    String ext = normalizeFormat(format);
    Set<Long> allowedIds = projectUsers(project).stream().map(AppUser::getId).collect(Collectors.toSet());
    List<AppUser> selectedUsers = userRepository.findByIdIn(userIds).stream()
        .filter(user -> allowedIds.contains(user.getId()))
        .toList();
    if (selectedUsers.isEmpty()) {
      throw new ApiException(HttpStatus.BAD_REQUEST, "未找到可导出的用户");
    }
    List<Long> selectedIds = selectedUsers.stream().map(AppUser::getId).toList();
    Map<Long, Profile> profileByUserId = profileRepository.findByProjectAndUserIdIn(project, selectedIds).stream()
        .collect(Collectors.toMap(item -> item.getUser().getId(), item -> item, (a, b) -> a));
    List<Submission> allSubmissions = submissionRepository.findByProjectAndUserIdInOrderBySubmittedAtDesc(project, selectedIds);
    Map<Long, List<Submission>> submissionsByUserId = allSubmissions.stream()
        .collect(Collectors.groupingBy(item -> item.getUser().getId()));
    Map<Long, Report> reportBySubmissionId = reportRepository.findByProjectAndSubmissionIdIn(project,
            allSubmissions.stream().map(Submission::getId).toList())
        .stream()
        .collect(Collectors.toMap(item -> item.getSubmission().getId(), item -> item, (a, b) -> a));

    try (ByteArrayOutputStream out = new ByteArrayOutputStream(); ZipOutputStream zip = new ZipOutputStream(out, StandardCharsets.UTF_8)) {
      for (AppUser user : selectedUsers) {
        Long userId = user.getId();
        Profile profile = profileByUserId.get(userId);
        String person = filenameSafe((profile == null ? "" : profile.getName()).isBlank() ? user.getEmail() : profile.getName());
        String folder = "user-" + userId + "-" + person + "/";
        List<Submission> submissions = submissionsByUserId.getOrDefault(userId, List.of());
        if (submissions.isEmpty()) {
          zip.putNextEntry(new ZipEntry(folder + "no-submissions.txt"));
          zip.write("暂无答题记录".getBytes(StandardCharsets.UTF_8));
          zip.closeEntry();
          continue;
        }
        int idx = 1;
        for (Submission submission : submissions) {
          Report report = reportBySubmissionId.get(submission.getId());
          String baseName = idx + "-" + filenameSafe(submission.getAssessment().getTestPaperId()) + "-submission-" + submission.getId();
          byte[] file;
          if ("pdf".equals(ext)) {
            if (report == null) {
              zip.putNextEntry(new ZipEntry(folder + baseName + "-no-report.txt"));
              zip.write("该次答题暂无报告".getBytes(StandardCharsets.UTF_8));
              zip.closeEntry();
              idx++;
              continue;
            }
            file = buildReportPdfUsingReportController(principal, report);
          } else if ("docx".equals(ext)) {
            if (report == null) {
              zip.putNextEntry(new ZipEntry(folder + baseName + "-no-report.txt"));
              zip.write("该次答题暂无报告".getBytes(StandardCharsets.UTF_8));
              zip.closeEntry();
              idx++;
              continue;
            }
            file = buildReportDocxUsingReportController(principal, report);
          } else {
            file = buildSubmissionTabular(submission, report, ext);
          }
          zip.putNextEntry(new ZipEntry(folder + baseName + "." + ext));
          zip.write(file);
          zip.closeEntry();
          idx++;
        }
      }
      zip.finish();
      auditService.record(project, principal.user(), "EXPORT_USERS_BATCH", "USER", "count=" + selectedUsers.size() + ":" + ext);
      return ResponseEntity.ok()
          .contentType(MediaType.parseMediaType("application/zip"))
          .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + project.getSlug() + "-users-batch.zip")
          .body(out.toByteArray());
    }
  }

  @GetMapping("/risk-export.csv")
  @Transactional
  public ResponseEntity<byte[]> exportUsersRiskCsv(
      @AuthenticationPrincipal UserPrincipal principal,
      @RequestParam(required = false) List<Long> userIds) {
    Project project = requireAdminRole(principal);
    List<AppUser> users = projectUsers(project);
    if (userIds != null && !userIds.isEmpty()) {
      Set<Long> filterSet = Set.copyOf(userIds);
      users = users.stream().filter(user -> filterSet.contains(user.getId())).toList();
    }
    if (users.isEmpty()) {
      String header = "姓名,邮箱,机构,SCL90原始分,SCL90等级,SCL90时间,SDS原始分,SDS等级,SDS时间,SAS原始分,SAS等级,SAS时间,综合分,综合风险等级,完成进度\n";
      return ResponseEntity.ok()
          .contentType(MediaType.parseMediaType("text/csv; charset=UTF-8"))
          .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + project.getSlug() + "-risk-summary.csv")
          .body(header.getBytes(StandardCharsets.UTF_8));
    }

    List<Long> uidList = users.stream().map(AppUser::getId).toList();
    Map<Long, Profile> profileByUserId = profileRepository.findByProjectAndUserIdIn(project, uidList).stream()
        .collect(Collectors.toMap(item -> item.getUser().getId(), item -> item, (a, b) -> a));
    List<Submission> latestFirstSubmissions = submissionRepository.findByProjectAndUserIdInOrderBySubmittedAtDesc(project, uidList);
    Map<Long, Submission> latestScl = new HashMap<>();
    Map<Long, Submission> latestSds = new HashMap<>();
    Map<Long, Submission> latestSas = new HashMap<>();
    for (Submission submission : latestFirstSubmissions) {
      Long uid = submission.getUser().getId();
      String paperId = submission.getAssessment().getTestPaperId();
      if ("SCL90-2078".equals(paperId) && !latestScl.containsKey(uid)) {
        latestScl.put(uid, submission);
      } else if ("SDS-2082".equals(paperId) && !latestSds.containsKey(uid)) {
        latestSds.put(uid, submission);
      } else if ("SAS-2083".equals(paperId) && !latestSas.containsKey(uid)) {
        latestSas.put(uid, submission);
      }
    }

    String header = "姓名,邮箱,机构,SCL90原始分,SCL90等级,SCL90时间,SDS原始分,SDS等级,SDS时间,SAS原始分,SAS等级,SAS时间,综合分,综合风险等级,完成进度\n";
    StringBuilder body = new StringBuilder(users.size() * 220);
    for (AppUser user : users) {
      Long uid = user.getId();
      Profile profile = profileByUserId.get(uid);
      Submission scl = latestScl.get(uid);
      Submission sds = latestSds.get(uid);
      Submission sas = latestSas.get(uid);
      CompositeResult composite = buildCompositeResultFromLatestSubmissions(scl, sds, sas);
      String riskLevel = composite.completedAll ? composite.level : "未完成";
      String score = composite.completedAll && composite.score != null ? String.valueOf(composite.score) : "";

      body.append(csv(profile == null ? "" : profile.getName())).append(",")
          .append(csv(user.getEmail())).append(",")
          .append(csv(profile == null ? "" : profile.getOrganization())).append(",")
          .append(csv(scl == null ? "" : String.valueOf(scl.getRawScore()))).append(",")
          .append(csv(scl == null ? "" : nullSafe(scl.getLevel()))).append(",")
          .append(csv(scl == null ? "" : scl.getSubmittedAt().toString())).append(",")
          .append(csv(sds == null ? "" : String.valueOf(sds.getRawScore()))).append(",")
          .append(csv(sds == null ? "" : nullSafe(sds.getLevel()))).append(",")
          .append(csv(sds == null ? "" : sds.getSubmittedAt().toString())).append(",")
          .append(csv(sas == null ? "" : String.valueOf(sas.getRawScore()))).append(",")
          .append(csv(sas == null ? "" : nullSafe(sas.getLevel()))).append(",")
          .append(csv(sas == null ? "" : sas.getSubmittedAt().toString())).append(",")
          .append(csv(score)).append(",")
          .append(csv(riskLevel)).append(",")
          .append(csv(composite.progress))
          .append("\n");
    }
    auditService.record(project, principal.user(), "EXPORT_RISK_CSV", "USER", "count=" + users.size());
    return ResponseEntity.ok()
        .contentType(MediaType.parseMediaType("text/csv; charset=UTF-8"))
        .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + project.getSlug() + "-risk-summary.csv")
        .body((header + body).getBytes(StandardCharsets.UTF_8));
  }

  private CompositeResult buildCompositeResultFromLatestSubmissions(Submission scl, Submission sds, Submission sas) {
    int completed = (scl == null ? 0 : 1) + (sds == null ? 0 : 1) + (sas == null ? 0 : 1);
    String progress = completed + "/3";
    if (completed < 3) return new CompositeResult(false, progress, null, "", List.of());

    int sdsScore = sds.getRawScore();
    int sasScore = sas.getRawScore();
    double sclAvg = parseSclAverageFromSubmission(scl);
    double sdsRisk = clamp((sdsScore - 53) / 20.0 * 100.0, 0, 100);
    double sasRisk = clamp((sasScore - 50) / 20.0 * 100.0, 0, 100);
    double sclRisk = clamp((sclAvg - 1.5) / 3.0 * 100.0, 0, 100);
    int score = (int) Math.round(0.30 * sdsRisk + 0.30 * sasRisk + 0.40 * sclRisk);
    String level = fromScore(score);
    List<String> triggers = new ArrayList<>();

    if (sdsScore >= 73 || sasScore >= 70) {
      level = "高风险";
      triggers.add("SDS/SAS重度阈值触发");
    }
    boolean sclPositive = !blank(scl.getLevel()) && scl.getLevel().trim().startsWith("阳性");
    if (sclPositive && (sdsScore >= 63 || sasScore >= 60)) {
      level = maxLevel(level, "中风险");
      triggers.add("SCL阳性 + SDS/SAS中度阈值触发");
    }
    if (sdsScore >= 63 && sasScore >= 60) {
      level = maxLevel(level, "中风险");
      triggers.add("SDS与SAS双中度阈值触发");
    }
    if (sclPositive) {
      level = maxLevel(level, "低风险");
      triggers.add("SCL阳性保底触发");
    }
    return new CompositeResult(true, progress, score, level, triggers);
  }

  private double parseSclAverageFromSubmission(Submission submission) {
    if (submission == null || blank(submission.getDimensionScores())) return 1.5;
    Integer scaled = parseJsonIntField(submission.getDimensionScores(), "总均分");
    if (scaled == null) return 1.5;
    return scaled / 100.0;
  }

  @PostMapping("/reports/batch-merge")
  @Transactional
  public ResponseEntity<byte[]> exportBatchMergedReports(
      @AuthenticationPrincipal UserPrincipal principal,
      @Valid @RequestBody BatchMergedPdfRequest request) throws IOException {
    Project project = requireAdminRole(principal);
    if (request.userIds() == null || request.userIds().isEmpty()) {
      throw new ApiException(HttpStatus.BAD_REQUEST, "请先选择用户");
    }
    final int chunkSize = 20;
    List<AppUser> users = request.userIds().stream()
        .map(id -> membershipRepository.findByProjectAndUserId(project, id).map(ProjectMembership::getUser).orElse(null))
        .filter(user -> user != null)
        .toList();
    if (users.isEmpty()) {
      throw new ApiException(HttpStatus.BAD_REQUEST, "未找到可导出的用户");
    }

    List<BatchPdfChunk> chunks = new ArrayList<>();
    for (int i = 0; i < users.size(); i += chunkSize) {
      int end = Math.min(i + chunkSize, users.size());
      List<AppUser> group = users.subList(i, end);
      List<byte[]> pages = new ArrayList<>();
      for (AppUser user : group) {
        appendLatestReportPdf(pages, principal, user, "SCL90-2078");
        appendLatestReportPdf(pages, principal, user, "SDS-2082");
        appendLatestReportPdf(pages, principal, user, "SAS-2083");
      }
      if (!pages.isEmpty()) {
        byte[] merged = mergePdfDocuments(pages);
        chunks.add(new BatchPdfChunk(project.getSlug() + "-reports-batch-" + String.format(Locale.ROOT, "%02d", chunks.size() + 1) + ".pdf", merged));
      }
    }

    if (chunks.isEmpty()) {
      throw new ApiException(HttpStatus.BAD_REQUEST, "所选用户暂无可导出报告");
    }

    auditService.record(project, principal.user(), "EXPORT_BATCH_REPORT_PDF", "REPORT", "users=" + users.size() + ",chunks=" + chunks.size());

    if (chunks.size() == 1) {
      return ResponseEntity.ok()
          .contentType(MediaType.APPLICATION_PDF)
          .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + chunks.get(0).filename())
          .body(chunks.get(0).data());
    }

    try (ByteArrayOutputStream out = new ByteArrayOutputStream();
         ZipOutputStream zip = new ZipOutputStream(out, StandardCharsets.UTF_8)) {
      for (BatchPdfChunk chunk : chunks) {
        zip.putNextEntry(new ZipEntry(chunk.filename()));
        zip.write(chunk.data());
        zip.closeEntry();
      }
      zip.finish();
      return ResponseEntity.ok()
          .contentType(MediaType.parseMediaType("application/zip"))
          .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + project.getSlug() + "-reports-batches.zip")
          .body(out.toByteArray());
    }
  }

  @GetMapping("/monitor/exports/download")
  @Transactional(readOnly = true)
  public ResponseEntity<byte[]> exportMonitorBundle(
      @AuthenticationPrincipal UserPrincipal principal,
      @RequestParam String items,
      @RequestParam String format,
      @RequestParam(required = false) String assessment,
      @RequestParam(required = false) String organization,
      @RequestParam(required = false) String level,
      @RequestParam(required = false) String from,
      @RequestParam(required = false) String to) throws IOException {
    Project project = requireAdminRole(principal);
    String ext = normalizeFormat(format);
    List<String> selected = List.of(items.split(",")).stream().map(String::trim).filter(v -> !v.isBlank()).toList();
    try (ByteArrayOutputStream out = new ByteArrayOutputStream(); ZipOutputStream zip = new ZipOutputStream(out, StandardCharsets.UTF_8)) {
      for (String item : selected) {
        ExportDataset dataset = switch (item) {
          case "submissions" -> datasetSubmissionsDetail("time", "desc", assessment, organization, level, false, from, to);
          case "reports" -> datasetReportsSummary("time", "desc", assessment, organization, level, false, from, to);
          case "logs" -> datasetAuditLogs("time", "desc", from, to);
          default -> null;
        };
        if (dataset == null) continue;
        byte[] data = "xlsx".equals(ext)
            ? buildXlsx(dataset)
            : ("pdf".equals(ext)
            ? buildSimplePdf(item + " 导出", dataset)
            : ("docx".equals(ext) ? buildDocx(item + " 导出", dataset) : buildCsv(dataset).getBytes(StandardCharsets.UTF_8)));
        zip.putNextEntry(new ZipEntry(item + "." + ext));
        zip.write(data);
        zip.closeEntry();
      }
      if ("pdf".equals(ext)) {
        ExportDataset dataset = new ExportDataset(
            List.of("导出项", "状态"),
            selected.stream().map(v -> List.of(v, "已包含在ZIP中")).toList());
        zip.putNextEntry(new ZipEntry("monitor-summary.pdf"));
        zip.write(buildSimplePdf("数据监控导出汇总", dataset));
        zip.closeEntry();
      }
      zip.finish();
      auditService.record(project, principal.user(), "EXPORT_MONITOR_BUNDLE", "EXPORT", items + ":" + ext);
      return ResponseEntity.ok()
          .contentType(MediaType.parseMediaType("application/zip"))
          .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + project.getSlug() + "-monitor-export.zip")
          .body(out.toByteArray());
    }
  }

  @GetMapping("/ops/exports/download")
  @Transactional(readOnly = true)
  public ResponseEntity<byte[]> exportOps(
      @AuthenticationPrincipal UserPrincipal principal,
      @RequestParam String type,
      @RequestParam String format) throws IOException {
    Project project = requireAdminRole(principal);
    String ext = normalizeFormat(format);
    ExportDataset dataset = switch (type) {
      case "invites" -> new ExportDataset(
          List.of("ID", "邀请码", "最大次数", "已使用", "状态", "过期时间"),
          invitationCodeRepository.findByProject(project).stream().map(v -> List.of(
              String.valueOf(v.getId()), v.getCode(), String.valueOf(v.getMaxUses()), String.valueOf(v.getUsedCount()),
              v.isEnabled() ? "启用" : "停用", v.getExpiresAt() == null ? "" : v.getExpiresAt().toString())).toList());
      case "admins" -> new ExportDataset(
          List.of("ID", "邮箱", "角色", "启用", "创建时间"),
          membershipRepository.findByProjectAndRole(project, ProjectMembershipRole.PROJECT_ADMIN).stream().map(m -> List.of(
              String.valueOf(m.getUser().getId()), m.getUser().getEmail(), m.getRole().name(), String.valueOf(m.isEnabled()), m.getCreatedAt().toString())).toList());
      case "ops_snapshot" -> new ExportDataset(
          List.of("指标", "值"),
          List.of(
              List.of("用户总数", String.valueOf(membershipRepository.countByProjectAndRole(project, ProjectMembershipRole.PARTICIPANT))),
              List.of("答卷总数", String.valueOf(submissionRepository.countByProject(project))),
              List.of("报告总数", String.valueOf(reportRepository.countByProject(project))),
              List.of("邀请码数量", String.valueOf(invitationCodeRepository.findByProject(project).size()))));
      default -> throw new ApiException(HttpStatus.BAD_REQUEST, "不支持的运维导出类型");
    };
    byte[] file = switch (ext) {
      case "csv" -> buildCsv(dataset).getBytes(StandardCharsets.UTF_8);
      case "xlsx" -> buildXlsx(dataset);
      case "pdf" -> buildSimplePdf("系统运维导出", dataset);
      case "docx" -> buildDocx("系统运维导出", dataset);
      default -> throw new ApiException(HttpStatus.BAD_REQUEST, "不支持的导出格式");
    };
    MediaType typeMedia = mediaTypeForFormat(ext);
    auditService.record(project, principal.user(), "EXPORT_OPS", "EXPORT", type + ":" + ext);
    return ResponseEntity.ok()
        .contentType(typeMedia)
        .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + project.getSlug() + "-ops-" + type + "." + ext)
        .body(file);
  }

  @GetMapping("/audit-logs/export.csv")
  @Transactional(readOnly = true)
  public ResponseEntity<String> exportAuditLogs(@AuthenticationPrincipal UserPrincipal principal) {
    Project project = requireAdminRole(principal);
    String header = "id,actorEmail,action,targetType,targetId,createdAt\n";
    String body = auditLogRepository.findTop1000ByProjectOrderByCreatedAtDesc(project).stream()
        .map(AuditRow::from)
        .map(row -> csv(row.id()) + "," + csv(row.actorEmail()) + "," + csv(row.action()) + "," + csv(row.targetType()) + "," + csv(row.targetId()) + "," + csv(row.createdAt()))
        .collect(Collectors.joining("\n"));
    auditService.record(project, principal.user(), "EXPORT_AUDIT_LOG_CSV", "AUDIT_LOG", "top1000");
    return ResponseEntity.ok()
        .contentType(MediaType.parseMediaType("text/csv; charset=UTF-8"))
        .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + project.getSlug() + "-audit-logs.csv")
        .body(header + body);
  }

  @GetMapping("/audit-logs/export-all.csv")
  @Transactional(readOnly = true)
  public ResponseEntity<String> exportAuditLogsAll(@AuthenticationPrincipal UserPrincipal principal) {
    Project project = requireAdminRole(principal);
    String header = "id,actorEmail,action,targetType,targetId,createdAt\n";
    String body = auditLogRepository.findByProjectOrderByCreatedAtDesc(project).stream()
        .map(AuditRow::from)
        .map(row -> csv(row.id()) + "," + csv(row.actorEmail()) + "," + csv(row.action()) + "," + csv(row.targetType()) + "," + csv(row.targetId()) + "," + csv(row.createdAt()))
        .collect(Collectors.joining("\n"));
    auditService.record(project, principal.user(), "EXPORT_AUDIT_LOG_CSV_ALL", "AUDIT_LOG", "all");
    return ResponseEntity.ok()
        .contentType(MediaType.parseMediaType("text/csv; charset=UTF-8"))
        .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + project.getSlug() + "-audit-logs-all.csv")
        .body(header + body);
  }

  public record DashboardResponse(long users, long submissions, long reports, long invitationCodes) {}
  public record CreateAdminRequest(@NotBlank String email, @NotBlank String password, UserRole role) {}
  public record ResetPasswordRequest(@NotBlank String password) {}
  public record ReopenAssessmentRequest(String note) {}
  public record BatchReopenRequest(@NotBlank String testPaperId, Set<Long> userIds, String note) {}
  public record BatchReopenResponse(String testPaperId, int openedCount, int skippedCount) {}
  public record ExportPreviewResponse(List<String> headers, List<List<String>> rows, int totalRows) {}
  public record InviteRequest(@NotBlank String code, @Min(1) int maxUses, String expiresAt) {}
  public record UserDetail(
      Long id,
      String email,
      String role,
      boolean enabled,
      String createdAt,
      String name,
      String gender,
      String idCard,
      String phone,
      String birthMonth,
      String education,
      String schoolMajor,
      String organization,
      String title,
      String industry,
      String city,
      String attendeeType) {
    static UserDetail from(AppUser user, Profile profile) {
      return new UserDetail(user.getId(), user.getEmail(), user.getRole().name(), user.isEnabled(), user.getCreatedAt().toString(),
          profile == null ? "" : profile.getName(), profile == null ? "" : profile.getGender(),
          profile == null ? "" : profile.getIdCard(), profile == null ? "" : profile.getPhone(),
          profile == null ? "" : profile.getBirthMonth(), profile == null ? "" : profile.getEducation(),
          profile == null ? "" : profile.getSchoolMajor(),
          profile == null ? "" : profile.getOrganization(), profile == null ? "" : profile.getTitle(),
          profile == null ? "" : profile.getIndustry(), profile == null ? "" : profile.getCity(),
          profile == null ? "" : profile.getAttendeeType());
    }
  }
  public record UserRow(Long id, String email, String role, boolean enabled, String name, String riskLevel, String organization, String title, String createdAt, String completion) {
    static UserRow from(AppUser user, Profile profile, String completion, String riskLevel) {
      return new UserRow(user.getId(), user.getEmail(), user.getRole().name(), user.isEnabled(),
          profile == null ? "" : profile.getName(), riskLevel, profile == null ? "" : profile.getOrganization(),
          profile == null ? "" : profile.getTitle(), user.getCreatedAt().toString(), completion);
    }
  }
  public record UserAssessmentStatusRow(String testPaperId, String title, boolean completed, boolean canRetake, long submissionCount, String lastSubmittedAt) {}
  public record UserAssessmentReportRow(
      String testPaperId,
      String title,
      boolean completed,
      long submissionCount,
      String latestSubmittedAt,
      Long latestSubmissionId,
      Long reportId,
      String summary,
      String suggestions) {}
  public record UserAssessmentReportOverview(
      UserDetail user,
      List<UserAssessmentReportRow> assessments,
      String comprehensiveEvaluation,
      boolean comprehensiveReady,
      String completionProgress,
      Integer comprehensiveScore,
      String comprehensiveLevel,
      List<String> comprehensiveTriggers) {}
  public record SubmissionRow(Long id, String email, String organization, String assessment, int rawScore, String level, String submittedAt) {
    static SubmissionRow from(Submission submission, Profile profile) {
      return new SubmissionRow(submission.getId(), submission.getUser().getEmail(), profile == null ? "" : profile.getOrganization(),
          submission.getAssessment().getTitle(), submission.getRawScore(), submission.getLevel(), submission.getSubmittedAt().toString());
    }
  }
  public record AnswerRow(String questionCode, String prompt, int value) {
    static AnswerRow from(Answer answer) {
      return new AnswerRow(answer.getQuestion().getCode(), answer.getQuestion().getPrompt(), answer.getAnswerValue());
    }
  }
  public record SubmissionDetail(SubmissionRow submission, UserDetail user, List<AnswerRow> answers, Long reportId, String reportSummary, String suggestions) {
    static SubmissionDetail from(Submission submission, List<AnswerRow> answers, Report report, Profile profile) {
      return new SubmissionDetail(SubmissionRow.from(submission, profile), UserDetail.from(submission.getUser(), profile), answers, report == null ? null : report.getId(),
          report == null ? "" : report.getSummary(), report == null ? "" : report.getSuggestions());
    }
  }
  public record ReportRow(Long id, Long submissionId, String email, int rawScore, String level, String createdAt) {
    static ReportRow from(Report report) {
      return new ReportRow(report.getId(), report.getSubmission().getId(), report.getUser().getEmail(),
          report.getSubmission().getRawScore(), report.getSubmission().getLevel(), report.getCreatedAt().toString());
    }
  }
  public record InviteRow(Long id, String code, int maxUses, int usedCount, boolean enabled, String expiresAt) {
    static InviteRow from(InvitationCode code) {
      return new InviteRow(code.getId(), code.getCode(), code.getMaxUses(), code.getUsedCount(), code.isEnabled(),
          code.getExpiresAt() == null ? "" : code.getExpiresAt().toString());
    }
  }
  public record AuditRow(Long id, String actorEmail, String action, String targetType, String targetId, String createdAt) {
    static AuditRow from(AuditLog log) {
      return new AuditRow(log.getId(), log.getActor() == null ? "system" : log.getActor().getEmail(),
          log.getAction(), log.getTargetType(), log.getTargetId(), log.getCreatedAt().toString());
    }
  }

  private static String csv(Object value) {
    String text = value == null ? "" : value.toString();
    return "\"" + text.replace("\"", "\"\"") + "\"";
  }

  private List<Submission> filteredSubmissions(String assessment, String organization, String level, String from, String to) {
    Project project = currentProject();
    return submissionRepository.findByProjectOrderBySubmittedAtDesc(project).stream()
        .filter(item -> matches(item, assessment, organization, level, from, to))
        .toList();
  }

  private boolean matches(Submission item, String assessment, String organization, String level, String from, String to) {
    Profile profile = projectProfile(currentProject(), item.getUser());
    boolean assessmentOk = blank(assessment) || item.getAssessment().getTitle().toLowerCase(Locale.ROOT).contains(assessment.toLowerCase(Locale.ROOT)) || item.getAssessment().getTestPaperId().toLowerCase(Locale.ROOT).contains(assessment.toLowerCase(Locale.ROOT));
    boolean orgOk = blank(organization) || (profile != null && profile.getOrganization().toLowerCase(Locale.ROOT).contains(organization.toLowerCase(Locale.ROOT)));
    boolean levelOk = blank(level) || item.getLevel().toLowerCase(Locale.ROOT).contains(level.toLowerCase(Locale.ROOT));
    boolean fromOk = blank(from) || !item.getSubmittedAt().isBefore(LocalDate.parse(from).atStartOfDay().toInstant(ZoneOffset.UTC));
    boolean toOk = blank(to) || item.getSubmittedAt().isBefore(LocalDate.parse(to).plusDays(1).atStartOfDay().toInstant(ZoneOffset.UTC));
    return assessmentOk && orgOk && levelOk && fromOk && toOk;
  }

  private static boolean blank(String value) {
    return value == null || value.isBlank();
  }

  private CompositeResult buildCompositeResultForUser(AppUser user) {
    Project project = currentProject();
    List<UserAssessmentReportRow> rows = activeAssessments(project).stream()
        .map(assessment -> {
          List<Submission> list = submissionRepository.findByProjectAndUserAndAssessmentOrderBySubmittedAtDesc(project, user, assessment);
          if (list.isEmpty()) {
            return new UserAssessmentReportRow(assessment.getTestPaperId(), assessment.getTitle(), false, 0, "", null, null, "", "");
          }
          Submission latest = list.get(0);
          Report report = reportRepository.findBySubmission(latest).orElse(null);
          return new UserAssessmentReportRow(
              assessment.getTestPaperId(),
              assessment.getTitle(),
              true,
              list.size(),
              latest.getSubmittedAt().toString(),
              latest.getId(),
              report == null ? null : report.getId(),
              report == null ? "" : report.getSummary(),
              report == null ? "" : report.getSuggestions());
        })
        .toList();
    return buildCompositeResult(rows);
  }

  private CompositeResult buildCompositeResult(List<UserAssessmentReportRow> rows) {
    UserAssessmentReportRow scl = rows.stream().filter(r -> r.completed() && isScl90(r)).findFirst().orElse(null);
    UserAssessmentReportRow sds = rows.stream().filter(r -> r.completed() && isSds(r)).findFirst().orElse(null);
    UserAssessmentReportRow sas = rows.stream().filter(r -> r.completed() && isSas(r)).findFirst().orElse(null);
    int completed = (scl == null ? 0 : 1) + (sds == null ? 0 : 1) + (sas == null ? 0 : 1);
    String progress = completed + "/3";
    if (completed < 3) return new CompositeResult(false, progress, null, "", List.of());

    int sdsScore = parseIntLoose(sds.summary());
    int sasScore = parseIntLoose(sas.summary());
    double sclAvg = parseSclAverage(scl);
    double sdsRisk = clamp((sdsScore - 53) / 20.0 * 100.0, 0, 100);
    double sasRisk = clamp((sasScore - 50) / 20.0 * 100.0, 0, 100);
    double sclRisk = clamp((sclAvg - 1.5) / 3.0 * 100.0, 0, 100);
    int score = (int) Math.round(0.30 * sdsRisk + 0.30 * sasRisk + 0.40 * sclRisk);
    String level = fromScore(score);
    List<String> triggers = new ArrayList<>();

    if (sdsScore >= 73 || sasScore >= 70) {
      level = "高风险";
      triggers.add("SDS/SAS重度阈值触发");
    }
    String sclSummary = scl.summary() == null ? "" : scl.summary().trim();
    boolean sclPositive = sclSummary.startsWith("阳性");
    if (sclPositive && (sdsScore >= 63 || sasScore >= 60)) {
      level = maxLevel(level, "中风险");
      triggers.add("SCL阳性 + SDS/SAS中度阈值触发");
    }
    if (sdsScore >= 63 && sasScore >= 60) {
      level = maxLevel(level, "中风险");
      triggers.add("SDS与SAS双中度阈值触发");
    }
    if (sclPositive) {
      level = maxLevel(level, "低风险");
      triggers.add("SCL阳性保底触发");
    }
    return new CompositeResult(true, progress, score, level, triggers);
  }

  private boolean isScl90(UserAssessmentReportRow row) {
    return containsAny(row.testPaperId(), "2078", "scl90", "scl-90") || containsAny(row.title(), "scl90", "scl-90", "症状自评量表");
  }

  private boolean isSds(UserAssessmentReportRow row) {
    return containsAny(row.testPaperId(), "2082", "sds") || containsAny(row.title(), "sds", "抑郁自评量表");
  }

  private boolean isSas(UserAssessmentReportRow row) {
    return containsAny(row.testPaperId(), "2083", "sas") || containsAny(row.title(), "sas", "焦虑自评量表");
  }

  private boolean containsAny(String text, String... keys) {
    if (blank(text)) return false;
    String source = text.toLowerCase(Locale.ROOT);
    for (String key : keys) {
      if (source.contains(key.toLowerCase(Locale.ROOT))) return true;
    }
    return false;
  }

  private double parseSclAverage(UserAssessmentReportRow row) {
    if (row == null || row.latestSubmissionId() == null) return 1.5;
    Submission submission = submissionRepository.findById(row.latestSubmissionId()).orElse(null);
    if (submission == null || blank(submission.getDimensionScores())) return 1.5;
    Integer scaled = parseJsonIntField(submission.getDimensionScores(), "总均分");
    if (scaled == null) return 1.5;
    return scaled / 100.0;
  }

  private Integer parseJsonIntField(String json, String key) {
    Pattern p = Pattern.compile(String.format(Locale.ROOT, JSON_INT_FIELD.pattern(), Pattern.quote(key)));
    Matcher m = p.matcher(json);
    if (!m.find()) return null;
    try {
      return Integer.parseInt(m.group(1));
    } catch (Exception ignored) {
      return null;
    }
  }

  private int parseIntLoose(String text) {
    if (blank(text)) return 0;
    Matcher m = INT_PATTERN.matcher(text);
    if (!m.find()) return 0;
    try {
      return Integer.parseInt(m.group());
    } catch (Exception ignored) {
      return 0;
    }
  }

  private double clamp(double value, double min, double max) {
    return Math.max(min, Math.min(max, value));
  }

  private String fromScore(int score) {
    if (score <= 19) return "正常";
    if (score <= 44) return "低风险";
    if (score <= 69) return "中风险";
    return "高风险";
  }

  private String maxLevel(String current, String target) {
    return levelRank(current) >= levelRank(target) ? current : target;
  }

  private int levelRank(String level) {
    return switch (level) {
      case "高风险" -> 4;
      case "中风险" -> 3;
      case "低风险" -> 2;
      default -> 1;
    };
  }

  private record CompositeResult(boolean completedAll, String progress, Integer score, String level, List<String> triggers) {}

  private String normalizeEmail(String email) {
    return email == null ? "" : email.trim().toLowerCase(Locale.ROOT);
  }

  private Project requireAdminRole(UserPrincipal principal) {
    return projectAccessService.requireProjectAdmin(projectAccessService.currentSlugOrDefault(), principal);
  }

  private Project requireOperationalAdmin(UserPrincipal principal) {
    return requireAdminRole(principal);
  }

  private Project requireSuperAdmin(UserPrincipal principal) {
    projectAccessService.requirePlatformSuper(principal);
    return currentProject();
  }

  private Project currentProject() {
    return projectAccessService.findEnabledProject(projectAccessService.currentSlugOrDefault());
  }

  private List<AppUser> projectUsers(Project project) {
    return membershipRepository.findByProject(project).stream()
        .filter(ProjectMembership::isEnabled)
        .filter(membership -> membership.getRole() == ProjectMembershipRole.PARTICIPANT)
        .map(ProjectMembership::getUser)
        .toList();
  }

  private AppUser requireProjectUser(Project project, Long userId) {
    return membershipRepository.findByProjectAndUserId(project, userId)
        .filter(ProjectMembership::isEnabled)
        .map(ProjectMembership::getUser)
        .orElseThrow(() -> new ApiException(HttpStatus.NOT_FOUND, "用户不存在或不属于当前项目"));
  }

  private Profile projectProfile(Project project, AppUser user) {
    return profileRepository.findByProjectAndUser(project, user).orElse(null);
  }

  private List<Assessment> activeAssessments(Project project) {
    return projectAssessmentRepository.findByProjectAndActiveTrue(project).stream()
        .map(ProjectAssessment::getAssessment)
        .filter(Assessment::isActive)
        .toList();
  }

  private boolean sameProject(Project project, Submission submission) {
    return submission != null && submission.getProject() != null && submission.getProject().getId().equals(project.getId());
  }

  private boolean sameProject(Project project, Report report) {
    return report != null && report.getProject() != null && report.getProject().getId().equals(project.getId());
  }

  private String completionSummary(AppUser user, List<Assessment> assessments) {
    Project project = currentProject();
    if (assessments.isEmpty()) return "0/0";
    long done = assessments.stream()
        .filter(item -> submissionRepository.countByProjectAndUserAndAssessment(project, user, item) > 0)
        .count();
    return done + "/" + assessments.size();
  }

  private String userSummaryRow(AppUser user) {
    Project project = currentProject();
    Profile profile = projectProfile(project, user);
    SubmissionStatusCell scl = latestStatus(user, "SCL90-2078");
    SubmissionStatusCell sds = latestStatus(user, "SDS-2082");
    SubmissionStatusCell sas = latestStatus(user, "SAS-2083");
    int totalDone = (scl.done ? 1 : 0) + (sds.done ? 1 : 0) + (sas.done ? 1 : 0);
    return csv(profile == null ? "" : profile.getName()) + ","
        + csv(user.getEmail()) + ","
        + csv(profile == null ? "" : profile.getOrganization()) + ","
        + csv(scl.done ? "是" : "否") + ","
        + csv(scl.time) + ","
        + csv(sds.done ? "是" : "否") + ","
        + csv(sds.time) + ","
        + csv(sas.done ? "是" : "否") + ","
        + csv(sas.time) + ","
        + csv(totalDone) + ","
        + csv(user.getCreatedAt().toString());
  }

  private String submissionDetailRow(Submission submission) {
    Project project = currentProject();
    Profile profile = projectProfile(project, submission.getUser());
    Report report = reportRepository.findBySubmission(submission).orElse(null);
    return csv(profile == null ? "" : profile.getName()) + ","
        + csv(submission.getUser().getEmail()) + ","
        + csv(profile == null ? "" : profile.getOrganization()) + ","
        + csv(submission.getAssessment().getTitle()) + ","
        + csv(submission.getRawScore()) + ","
        + csv(submission.getLevel()) + ","
        + csv(submission.getSubmittedAt().toString()) + ","
        + csv(report == null ? "" : report.getId());
  }

  private SubmissionStatusCell latestStatus(AppUser user, String testPaperId) {
    Project project = currentProject();
    Assessment assessment = assessmentRepository.findByTestPaperId(testPaperId).orElse(null);
    if (assessment == null) return new SubmissionStatusCell(false, "");
    List<Submission> list = submissionRepository.findByProjectAndUserAndAssessmentOrderBySubmittedAtDesc(project, user, assessment);
    if (list.isEmpty()) return new SubmissionStatusCell(false, "");
    return new SubmissionStatusCell(true, list.get(0).getSubmittedAt().toString());
  }

  private record SubmissionStatusCell(boolean done, String time) {}

  private ExportDataset buildExportDataset(
      String template,
      String sortBy,
      String sortDir,
      String assessment,
      String organization,
      String level,
      boolean latestOnly,
      String from,
      String to) {
    String t = template == null ? "" : template.trim().toLowerCase(Locale.ROOT);
    return switch (t) {
      case "users_summary" -> datasetUsersSummary(sortBy, sortDir, organization);
      case "submissions_detail" -> datasetSubmissionsDetail(sortBy, sortDir, assessment, organization, level, latestOnly, from, to);
      case "questions_detail" -> datasetQuestionsDetail(sortBy, sortDir, assessment, organization, level, latestOnly, from, to);
      case "reports_summary" -> datasetReportsSummary(sortBy, sortDir, assessment, organization, level, latestOnly, from, to);
      case "audit_logs" -> datasetAuditLogs(sortBy, sortDir, from, to);
      case "reports_package" -> datasetReportsPackage(sortBy, sortDir, organization, level, from, to);
      default -> throw new ApiException(HttpStatus.BAD_REQUEST, "不支持的导出模板");
    };
  }

  private ExportDataset datasetUsersSummary(String sortBy, String sortDir, String organization) {
    Project project = currentProject();
    List<Map<String, String>> rows = new ArrayList<>();
    for (AppUser user : projectUsers(project)) {
      Profile profile = projectProfile(project, user);
      if (!blank(organization) && (profile == null || !profile.getOrganization().toLowerCase(Locale.ROOT).contains(organization.toLowerCase(Locale.ROOT)))) continue;
      SubmissionStatusCell scl = latestStatus(user, "SCL90-2078");
      SubmissionStatusCell sds = latestStatus(user, "SDS-2082");
      SubmissionStatusCell sas = latestStatus(user, "SAS-2083");
      int totalDone = (scl.done ? 1 : 0) + (sds.done ? 1 : 0) + (sas.done ? 1 : 0);
      Map<String, String> item = new java.util.HashMap<>();
      item.put("name", profile == null ? "" : profile.getName());
      item.put("email", user.getEmail());
      item.put("organization", profile == null ? "" : profile.getOrganization());
      item.put("scl90_done", scl.done ? "是" : "否");
      item.put("scl90_time", scl.time);
      item.put("sds_done", sds.done ? "是" : "否");
      item.put("sds_time", sds.time);
      item.put("sas_done", sas.done ? "是" : "否");
      item.put("sas_time", sas.time);
      item.put("done_total", String.valueOf(totalDone));
      item.put("created_at", user.getCreatedAt().toString());
      rows.add(item);
    }
    rows.sort(buildComparator(sortBy, sortDir, Map.of(
        "time", "created_at",
        "person", "name",
        "score", "done_total"
    )));
    List<String> headers = List.of("姓名", "邮箱", "公司/机构", "SCL90完成", "SCL90时间", "SDS完成", "SDS时间", "SAS完成", "SAS时间", "总完成数", "账号创建时间");
    List<List<String>> values = rows.stream().map(item -> List.of(
        item.get("name"), item.get("email"), item.get("organization"),
        item.get("scl90_done"), item.get("scl90_time"), item.get("sds_done"), item.get("sds_time"),
        item.get("sas_done"), item.get("sas_time"), item.get("done_total"), item.get("created_at")
    )).toList();
    return new ExportDataset(headers, values);
  }

  private ExportDataset datasetSubmissionsDetail(String sortBy, String sortDir, String assessment, String organization, String level, boolean latestOnly, String from, String to) {
    Project project = currentProject();
    List<Submission> base = filteredSubmissions(assessment, organization, level, from, to);
    List<Submission> source = latestOnly ? latestSubmissions(base) : base;
    List<Map<String, String>> rows = source.stream().map(submission -> {
      Profile profile = projectProfile(project, submission.getUser());
      Report report = reportRepository.findBySubmission(submission).orElse(null);
      return Map.of(
          "name", profile == null ? "" : profile.getName(),
          "email", submission.getUser().getEmail(),
          "organization", profile == null ? "" : profile.getOrganization(),
          "assessment", submission.getAssessment().getTitle(),
          "score", String.valueOf(submission.getRawScore()),
          "level", submission.getLevel(),
          "time", submission.getSubmittedAt().toString(),
          "report_id", report == null ? "" : report.getId().toString());
    }).collect(Collectors.toCollection(ArrayList::new));
    rows.sort(buildComparator(sortBy, sortDir, Map.of(
        "time", "time",
        "person", "name",
        "score", "score"
    )));
    List<String> headers = List.of("姓名", "邮箱", "公司/机构", "量表", "分数", "等级", "提交时间", "报告ID");
    List<List<String>> values = rows.stream().map(item -> List.of(
        item.get("name"), item.get("email"), item.get("organization"), item.get("assessment"),
        item.get("score"), item.get("level"), item.get("time"), item.get("report_id")
    )).toList();
    return new ExportDataset(headers, values);
  }

  private ExportDataset datasetQuestionsDetail(String sortBy, String sortDir, String assessment, String organization, String level, boolean latestOnly, String from, String to) {
    Project project = currentProject();
    List<Map<String, String>> rows = new ArrayList<>();
    List<Submission> base = filteredSubmissions(assessment, organization, level, from, to);
    List<Submission> source = latestOnly ? latestSubmissions(base) : base;
    for (Submission submission : source) {
      Profile profile = projectProfile(project, submission.getUser());
      for (Answer answer : answerRepository.findBySubmission(submission)) {
        rows.add(Map.of(
            "name", profile == null ? "" : profile.getName(),
            "email", submission.getUser().getEmail(),
            "organization", profile == null ? "" : profile.getOrganization(),
            "assessment", submission.getAssessment().getTitle(),
            "question_code", answer.getQuestion().getCode(),
            "question_prompt", answer.getQuestion().getPrompt(),
            "answer", String.valueOf(answer.getAnswerValue()),
            "time", submission.getSubmittedAt().toString()));
      }
    }
    rows.sort(buildComparator(sortBy, sortDir, Map.of(
        "time", "time",
        "person", "name",
        "question", "question_code"
    )));
    List<String> headers = List.of("姓名", "邮箱", "公司/机构", "量表", "题号", "题目", "答案", "提交时间");
    List<List<String>> values = rows.stream().map(item -> List.of(
        item.get("name"), item.get("email"), item.get("organization"), item.get("assessment"),
        item.get("question_code"), item.get("question_prompt"), item.get("answer"), item.get("time")
    )).toList();
    return new ExportDataset(headers, values);
  }

  private ExportDataset datasetReportsSummary(String sortBy, String sortDir, String assessment, String organization, String level, boolean latestOnly, String from, String to) {
    Project project = currentProject();
    List<Submission> allowed = filteredSubmissions(assessment, organization, level, from, to);
    Set<Long> allowedIds = allowed.stream().map(Submission::getId).collect(Collectors.toSet());
    Set<Long> latestAllowedIds = latestOnly ? latestSubmissions(allowed).stream().map(Submission::getId).collect(Collectors.toSet()) : Set.of();
    List<Map<String, String>> rows = reportRepository.findByProjectOrderByCreatedAtDesc(project).stream()
        .filter(report -> allowedIds.contains(report.getSubmission().getId()))
        .filter(report -> !latestOnly || latestAllowedIds.contains(report.getSubmission().getId()))
        .map(report -> {
          Submission submission = report.getSubmission();
          Profile profile = projectProfile(project, submission.getUser());
          return Map.of(
              "name", profile == null ? "" : profile.getName(),
              "email", submission.getUser().getEmail(),
              "organization", profile == null ? "" : profile.getOrganization(),
              "assessment", submission.getAssessment().getTitle(),
              "score", String.valueOf(submission.getRawScore()),
              "level", submission.getLevel(),
              "summary", report.getSummary(),
              "suggestions", report.getSuggestions(),
              "time", report.getCreatedAt().toString());
        }).collect(Collectors.toCollection(ArrayList::new));
    rows.sort(buildComparator(sortBy, sortDir, Map.of(
        "time", "time",
        "person", "name",
        "score", "score"
    )));
    List<String> headers = List.of("姓名", "邮箱", "公司/机构", "量表", "分数", "等级", "结论摘要", "建议", "报告时间");
    List<List<String>> values = rows.stream().map(item -> List.of(
        item.get("name"), item.get("email"), item.get("organization"), item.get("assessment"),
        item.get("score"), item.get("level"), item.get("summary"), item.get("suggestions"), item.get("time")
    )).toList();
    return new ExportDataset(headers, values);
  }

  private ExportDataset datasetAuditLogs(String sortBy, String sortDir, String from, String to) {
    Project project = currentProject();
    List<Map<String, String>> rows = auditLogRepository.findTop1000ByProjectOrderByCreatedAtDesc(project).stream()
        .filter(log -> blank(from) || !log.getCreatedAt().isBefore(LocalDate.parse(from).atStartOfDay().toInstant(ZoneOffset.UTC)))
        .filter(log -> blank(to) || log.getCreatedAt().isBefore(LocalDate.parse(to).plusDays(1).atStartOfDay().toInstant(ZoneOffset.UTC)))
        .map(log -> {
          Map<String, String> item = new java.util.HashMap<>();
          item.put("time", log.getCreatedAt().toString());
          item.put("actor_email", log.getActor() == null ? "" : log.getActor().getEmail());
          item.put("action", log.getAction());
          item.put("target_type", log.getTargetType());
          item.put("target_id", log.getTargetId() == null ? "" : log.getTargetId());
          return item;
        }).collect(Collectors.toCollection(ArrayList::new));
    rows.sort(buildComparator(sortBy, sortDir, Map.of(
        "time", "time",
        "person", "actor_email"
    )));
    List<String> headers = List.of("操作时间", "操作人邮箱", "操作动作", "目标类型", "目标ID");
    List<List<String>> values = rows.stream().map(item -> List.of(
        item.get("time"), item.get("actor_email"), item.get("action"), item.get("target_type"), item.get("target_id")
    )).toList();
    return new ExportDataset(headers, values);
  }

  private ExportDataset datasetReportsPackage(String sortBy, String sortDir, String organization, String level, String from, String to) {
    Project project = currentProject();
    List<Map<String, String>> rows = new ArrayList<>();
    for (AppUser user : projectUsers(project)) {
      Profile profile = projectProfile(project, user);
      if (!blank(organization) && (profile == null || !profile.getOrganization().toLowerCase(Locale.ROOT).contains(organization.toLowerCase(Locale.ROOT)))) continue;

      ReportCell scl90 = latestReportCell(user, "SCL90-2078");
      ReportCell sds = latestReportCell(user, "SDS-2082");
      ReportCell sas = latestReportCell(user, "SAS-2083");

      if (!blank(level)) {
        String lv = level.toLowerCase(Locale.ROOT);
        boolean anyMatch = scl90.level.toLowerCase(Locale.ROOT).contains(lv)
            || sds.level.toLowerCase(Locale.ROOT).contains(lv)
            || sas.level.toLowerCase(Locale.ROOT).contains(lv);
        if (!anyMatch) continue;
      }
      if (!blank(from) || !blank(to)) {
        Instant latestTime = latestOf(scl90.time, sds.time, sas.time);
        if (latestTime == null) continue;
        if (!blank(from) && latestTime.isBefore(LocalDate.parse(from).atStartOfDay().toInstant(ZoneOffset.UTC))) continue;
        if (!blank(to) && !latestTime.isBefore(LocalDate.parse(to).plusDays(1).atStartOfDay().toInstant(ZoneOffset.UTC))) continue;
      }

      Map<String, String> item = new java.util.HashMap<>();
      item.put("name", profile == null ? "" : profile.getName());
      item.put("gender", profile == null ? "" : profile.getGender());
      item.put("id_card", profile == null ? "" : profile.getIdCard());
      item.put("phone", profile == null ? "" : profile.getPhone());
      item.put("birth_month", profile == null ? "" : profile.getBirthMonth());
      item.put("education", profile == null ? "" : profile.getEducation());
      item.put("school_major", profile == null ? "" : profile.getSchoolMajor());
      item.put("organization", profile == null ? "" : profile.getOrganization());
      item.put("title", profile == null ? "" : profile.getTitle());
      item.put("industry", profile == null ? "" : profile.getIndustry());
      item.put("city", profile == null ? "" : profile.getCity());
      item.put("attendee_type", profile == null ? "" : profile.getAttendeeType());
      item.put("email", user.getEmail());
      item.put("created_at", user.getCreatedAt().toString());

      item.put("scl90_level", scl90.level);
      item.put("scl90_time", scl90.time == null ? "" : scl90.time.toString());
      item.put("scl90_summary", scl90.summary);
      item.put("scl90_suggestions", scl90.suggestions);
      item.put("sds_level", sds.level);
      item.put("sds_time", sds.time == null ? "" : sds.time.toString());
      item.put("sds_summary", sds.summary);
      item.put("sds_suggestions", sds.suggestions);
      item.put("sas_level", sas.level);
      item.put("sas_time", sas.time == null ? "" : sas.time.toString());
      item.put("sas_summary", sas.summary);
      item.put("sas_suggestions", sas.suggestions);
      rows.add(item);
    }

    rows.sort(buildComparator(sortBy, sortDir, Map.of(
        "time", "created_at",
        "person", "name"
    )));

    List<String> headers = List.of(
        "姓名", "性别", "身份证号", "联系方式", "出生年月", "学历", "院校/专业", "公司/机构", "职位", "行业", "城市/国家", "参会身份", "邮箱", "注册时间",
        "SCL90等级", "SCL90完成时间", "SCL90结论", "SCL90建议",
        "SDS等级", "SDS完成时间", "SDS结论", "SDS建议",
        "SAS等级", "SAS完成时间", "SAS结论", "SAS建议"
    );

    List<List<String>> values = rows.stream().map(item -> List.of(
        item.get("name"), item.get("gender"), item.get("id_card"), item.get("phone"), item.get("birth_month"),
        item.get("education"), item.get("school_major"), item.get("organization"), item.get("title"), item.get("industry"),
        item.get("city"), item.get("attendee_type"), item.get("email"), item.get("created_at"),
        item.get("scl90_level"), item.get("scl90_time"), item.get("scl90_summary"), item.get("scl90_suggestions"),
        item.get("sds_level"), item.get("sds_time"), item.get("sds_summary"), item.get("sds_suggestions"),
        item.get("sas_level"), item.get("sas_time"), item.get("sas_summary"), item.get("sas_suggestions")
    )).toList();
    return new ExportDataset(headers, values);
  }

  private ReportCell latestReportCell(AppUser user, String testPaperId) {
    Project project = currentProject();
    Assessment assessment = assessmentRepository.findByTestPaperId(testPaperId).orElse(null);
    if (assessment == null) return new ReportCell("", "", null, "");
    List<Submission> submissions = submissionRepository.findByProjectAndUserAndAssessmentOrderBySubmittedAtDesc(project, user, assessment);
    if (submissions.isEmpty()) return new ReportCell("", "", null, "");
    Submission latest = submissions.get(0);
    Report report = reportRepository.findBySubmission(latest).orElse(null);
    return new ReportCell(
        latest.getLevel() == null ? "" : latest.getLevel(),
        report == null ? "" : report.getSummary(),
        latest.getSubmittedAt(),
        report == null ? "" : report.getSuggestions());
  }

  private Instant latestOf(Instant... values) {
    Instant latest = null;
    for (Instant value : values) {
      if (value == null) continue;
      if (latest == null || value.isAfter(latest)) latest = value;
    }
    return latest;
  }

  private Comparator<Map<String, String>> buildComparator(String sortBy, String sortDir, Map<String, String> aliasMap) {
    String key = aliasMap.getOrDefault(sortBy == null ? "" : sortBy.toLowerCase(Locale.ROOT), aliasMap.values().stream().findFirst().orElse(""));
    Comparator<Map<String, String>> comparator = Comparator.comparing(item -> item.getOrDefault(key, ""), String::compareToIgnoreCase);
    if ("desc".equalsIgnoreCase(sortDir)) return comparator.reversed();
    return comparator;
  }

  private String buildCsv(ExportDataset dataset) {
    String header = dataset.headers().stream().map(AdminController::csv).collect(Collectors.joining(","));
    String body = dataset.rows().stream()
        .map(row -> row.stream().map(AdminController::csv).collect(Collectors.joining(",")))
        .collect(Collectors.joining("\n"));
    return header + "\n" + body;
  }

  private String normalizeFormat(String format) {
    String value = format == null ? "" : format.trim().toLowerCase(Locale.ROOT);
    if ("excel".equals(value)) value = "xlsx";
    if (!Set.of("csv", "xlsx", "pdf", "docx").contains(value)) {
      throw new ApiException(HttpStatus.BAD_REQUEST, "不支持的导出格式");
    }
    return value;
  }

  private byte[] buildUserTabular(AppUser user, String format) throws IOException {
    Profile profile = projectProfile(currentProject(), user);
    ExportDataset dataset = new ExportDataset(
        List.of("字段", "值"),
        List.of(
            List.of("用户ID", String.valueOf(user.getId())),
            List.of("邮箱", user.getEmail()),
            List.of("姓名", profile == null ? "" : profile.getName()),
            List.of("性别", profile == null ? "" : profile.getGender()),
            List.of("联系方式", profile == null ? "" : profile.getPhone()),
            List.of("机构", profile == null ? "" : profile.getOrganization()),
            List.of("职位", profile == null ? "" : profile.getTitle()),
            List.of("行业", profile == null ? "" : profile.getIndustry()),
            List.of("城市", profile == null ? "" : profile.getCity()),
            List.of("参会身份", profile == null ? "" : profile.getAttendeeType()),
            List.of("账号创建时间", user.getCreatedAt().toString())
        ));
    if ("xlsx".equals(format)) return buildXlsx(dataset);
    if ("docx".equals(format)) return buildDocx("用户详情导出", dataset);
    return buildCsv(dataset).getBytes(StandardCharsets.UTF_8);
  }

  private byte[] buildUserPdf(AppUser user) throws IOException {
    Profile profile = projectProfile(currentProject(), user);
    ExportDataset dataset = new ExportDataset(
        List.of("字段", "值"),
        List.of(
            List.of("用户ID", String.valueOf(user.getId())),
            List.of("邮箱", user.getEmail()),
            List.of("姓名", profile == null ? "" : profile.getName()),
            List.of("性别", profile == null ? "" : profile.getGender()),
            List.of("联系方式", profile == null ? "" : profile.getPhone()),
            List.of("机构", profile == null ? "" : profile.getOrganization()),
            List.of("账号创建时间", user.getCreatedAt().toString())
        ));
    return buildSimplePdf("用户详情导出", dataset);
  }

  private byte[] buildSubmissionTabular(Submission submission, Report report, String format) throws IOException {
    Profile profile = projectProfile(currentProject(), submission.getUser());
    List<List<String>> rows = new ArrayList<>();
    rows.add(List.of("用户ID", String.valueOf(submission.getUser().getId())));
    rows.add(List.of("邮箱", submission.getUser().getEmail()));
    rows.add(List.of("姓名", profile == null ? "" : nullSafe(profile.getName())));
    rows.add(List.of("性别", profile == null ? "" : nullSafe(profile.getGender())));
    rows.add(List.of("身份证号", profile == null ? "" : nullSafe(profile.getIdCard())));
    rows.add(List.of("联系方式", profile == null ? "" : nullSafe(profile.getPhone())));
    rows.add(List.of("出生年月", profile == null ? "" : nullSafe(profile.getBirthMonth())));
    rows.add(List.of("学历", profile == null ? "" : nullSafe(profile.getEducation())));
    rows.add(List.of("院校/专业", profile == null ? "" : nullSafe(profile.getSchoolMajor())));
    rows.add(List.of("公司/机构", profile == null ? "" : nullSafe(profile.getOrganization())));
    rows.add(List.of("职位", profile == null ? "" : nullSafe(profile.getTitle())));
    rows.add(List.of("行业", profile == null ? "" : nullSafe(profile.getIndustry())));
    rows.add(List.of("城市/国家", profile == null ? "" : nullSafe(profile.getCity())));
    rows.add(List.of("参会身份", profile == null ? "" : nullSafe(profile.getAttendeeType())));
    rows.add(List.of("量表", submission.getAssessment().getTitle()));
    rows.add(List.of("量表编号", submission.getAssessment().getTestPaperId()));
    rows.add(List.of("提交时间", submission.getSubmittedAt().toString()));
    rows.add(List.of("原始分", String.valueOf(submission.getRawScore())));
    rows.add(List.of("等级", nullSafe(submission.getLevel())));
    rows.add(List.of("报告ID", report == null ? "" : String.valueOf(report.getId())));
    rows.add(List.of("报告结论", report == null ? "" : nullSafe(report.getSummary())));
    rows.add(List.of("报告建议", report == null ? "" : nullSafe(report.getSuggestions())));
    List<Answer> answers = answerRepository.findBySubmission(submission);
    for (Answer answer : answers) {
      rows.add(List.of("题目 " + answer.getQuestion().getCode(), nullSafe(answer.getQuestion().getPrompt()) + " | 答案=" + answer.getAnswerValue()));
    }
    ExportDataset dataset = new ExportDataset(List.of("字段", "内容"), rows);
    if ("xlsx".equals(format)) return buildXlsx(dataset);
    if ("docx".equals(format)) return buildDocx("报告详情导出", dataset);
    return buildCsv(dataset).getBytes(StandardCharsets.UTF_8);
  }

  private byte[] buildReportPdfWithCover(Report report) throws IOException {
    ensureReportTemplatesReady();
    try (PDDocument document = new PDDocument(); ByteArrayOutputStream output = new ByteArrayOutputStream()) {
      PDFont bodyFont = loadChineseFont(document);
      renderPersonalCoverPage(document, report, bodyFont);
      renderReportBodyPage(document, report, bodyFont);
      renderTailPage(document);
      document.save(output);
      return output.toByteArray();
    }
  }

  private void ensureReportTemplatesReady() {
    if (!new ClassPathResource(COVER_PERSONAL).exists()) {
      throw new ApiException(HttpStatus.INTERNAL_SERVER_ERROR, "缺少封面模板: cover-personal.png");
    }
    if (!new ClassPathResource(TAIL_IMAGE).exists()) {
      throw new ApiException(HttpStatus.INTERNAL_SERVER_ERROR, "缺少尾页模板: tail.jpg");
    }
  }

  private void renderPersonalCoverPage(PDDocument document, Report report, PDFont font) throws IOException {
    PDPage page = new PDPage(PDRectangle.A4);
    document.addPage(page);
    ClassPathResource coverResource = new ClassPathResource(COVER_PERSONAL);
    try (InputStream input = coverResource.getInputStream();
         PDPageContentStream stream = new PDPageContentStream(document, page)) {
      PDImageXObject cover = PDImageXObject.createFromByteArray(document, input.readAllBytes(), "cover");
      stream.drawImage(cover, 0, 0, page.getMediaBox().getWidth(), page.getMediaBox().getHeight());
    }
    Project project = report.getProject() == null ? currentProject() : report.getProject();
    Profile profile = projectProfile(project, report.getUser());
    try (PDPageContentStream textStream = new PDPageContentStream(document, page, PDPageContentStream.AppendMode.APPEND, true, true)) {
      drawSingleLine(textStream, font, profile == null ? "" : nullSafe(profile.getName()), 113, 94, 190, 13f, 10.5f);
      drawSingleLine(textStream, font, profile == null ? "" : nullSafe(profile.getPhone()), 113, 68, 190, 13f, 10.5f);
      drawSingleLine(textStream, font, profile == null ? "" : nullSafe(profile.getGender()), 113, 42, 190, 13f, 10.5f);
    }
  }

  private void renderReportBodyPage(PDDocument document, Report report, PDFont bodyFont) throws IOException {
    PDPage page = new PDPage(PDRectangle.A4);
    document.addPage(page);
    try (PDPageContentStream stream = new PDPageContentStream(document, page)) {
      stream.beginText();
      stream.setFont(bodyFont, 18);
      stream.newLineAtOffset(56, 760);
      String reportTitle = report.getProject() == null ? "心理测评报告" : report.getProject().getReportTitle();
      stream.showText(safePdfText(bodyFont, reportTitle));
      stream.newLineAtOffset(0, -34);
      stream.setFont(bodyFont, 11);
      writeLine(stream, bodyFont, "报告编号：" + report.getId());
      writeLine(stream, bodyFont, "报告时间：" + LocalDate.now(SHANGHAI).format(DATE_FORMAT));
      writeLine(stream, bodyFont, "量表：" + report.getSubmission().getAssessment().getTitle());
      writeLine(stream, bodyFont, "报告类型：" + nullSafe(report.getSubmission().getLevel()));
      writeLine(stream, bodyFont, "总分：" + report.getSubmission().getRawScore());
      writeLine(stream, bodyFont, "核心结论：" + nullSafe(report.getSummary()));
      writeLine(stream, bodyFont, "参会建议：" + nullSafe(report.getSuggestions()));
      stream.endText();
    }
  }

  private void renderTailPage(PDDocument document) throws IOException {
    PDPage page = new PDPage(PDRectangle.A4);
    document.addPage(page);
    ClassPathResource tailResource = new ClassPathResource(TAIL_IMAGE);
    try (InputStream input = tailResource.getInputStream();
         PDPageContentStream stream = new PDPageContentStream(document, page)) {
      PDImageXObject tail = PDImageXObject.createFromByteArray(document, input.readAllBytes(), "tail");
      stream.drawImage(tail, 0, 0, page.getMediaBox().getWidth(), page.getMediaBox().getHeight());
    }
  }

  private void drawSingleLine(PDPageContentStream stream, PDFont font, String text, float x, float y, float maxWidth, float size, float minSize) throws IOException {
    String value = nullSafe(text);
    if (value.isBlank()) return;
    float fontSize = size;
    while (fontSize > minSize && textWidth(font, value, fontSize) > maxWidth) fontSize -= 0.5f;
    if (textWidth(font, value, fontSize) > maxWidth) value = ellipsize(font, value, fontSize, maxWidth);
    stream.beginText();
    stream.setFont(font, fontSize);
    stream.newLineAtOffset(x, y);
    stream.showText(safePdfText(font, value));
    stream.endText();
  }

  private float textWidth(PDFont font, String text, float size) throws IOException {
    return font.getStringWidth(text) / 1000f * size;
  }

  private String ellipsize(PDFont font, String text, float size, float maxWidth) throws IOException {
    String ellipsis = "...";
    String value = text;
    while (!value.isEmpty() && textWidth(font, value + ellipsis, size) > maxWidth) value = value.substring(0, value.length() - 1);
    return value.isEmpty() ? ellipsis : value + ellipsis;
  }

  private void writeLine(PDPageContentStream stream, PDFont font, String text) throws IOException {
    for (String line : wrapText(nullSafe(text), 86).split("\n")) {
      stream.showText(safePdfText(font, line));
      stream.newLineAtOffset(0, -16);
    }
  }

  private String wrapText(String text, int size) {
    StringBuilder builder = new StringBuilder();
    String value = text.length() > 900 ? text.substring(0, 900) : text;
    int index = 0;
    while (index < value.length()) {
      int end = Math.min(index + size, value.length());
      builder.append(value, index, end);
      if (end < value.length()) builder.append('\n');
      index = end;
    }
    return builder.toString();
  }

  private String nullSafe(String value) {
    return value == null ? "" : value.trim();
  }

  private String filenameSafe(String value) {
    String v = nullSafe(value);
    if (v.isBlank()) return "unknown";
    return v.replaceAll("[\\\\/:*?\"<>|\\s]+", "_");
  }

  private byte[] buildSimplePdf(String title, ExportDataset dataset) throws IOException {
    try (PDDocument doc = new PDDocument(); ByteArrayOutputStream out = new ByteArrayOutputStream()) {
      PDPage page = new PDPage(PDRectangle.A4);
      doc.addPage(page);
      PDFont font = loadChineseFont(doc);
      try (PDPageContentStream stream = new PDPageContentStream(doc, page)) {
        stream.beginText();
        stream.setFont(font, 14);
        stream.newLineAtOffset(42, 800);
        stream.showText(safePdfText(font, title));
        stream.newLineAtOffset(0, -24);
        stream.setFont(font, 10);
        for (List<String> row : dataset.rows()) {
          String line = (row.isEmpty() ? "" : row.get(0)) + "： " + (row.size() > 1 ? row.get(1) : "");
          stream.showText(safePdfText(font, line.length() > 110 ? line.substring(0, 110) : line));
          stream.newLineAtOffset(0, -14);
        }
        stream.endText();
      }
      doc.save(out);
      return out.toByteArray();
    }
  }

  private Submission latestSubmissionByTestPaperId(AppUser user, String testPaperId) {
    Project project = currentProject();
    Assessment assessment = assessmentRepository.findByTestPaperId(testPaperId).orElse(null);
    if (assessment == null) return null;
    return submissionRepository.findByProjectAndUserAndAssessmentOrderBySubmittedAtDesc(project, user, assessment).stream().findFirst().orElse(null);
  }

  private void appendLatestReportPdf(List<byte[]> pages, UserPrincipal principal, AppUser user, String testPaperId) throws IOException {
    Submission submission = latestSubmissionByTestPaperId(user, testPaperId);
    if (submission == null) return;
    Report report = reportRepository.findBySubmission(submission).orElse(null);
    if (report == null) return;
    pages.add(buildReportPdfUsingReportController(principal, report));
  }

  private byte[] buildReportPdfUsingReportController(UserPrincipal principal, Report report) throws IOException {
    ResponseEntity<byte[]> response = reportController.pdf(principal, report.getId(), "personal", null);
    byte[] body = response.getBody();
    if (body == null || body.length == 0) {
      throw new ApiException(HttpStatus.INTERNAL_SERVER_ERROR, "报告导出结果为空");
    }
    return body;
  }

  private byte[] buildReportDocxUsingReportController(UserPrincipal principal, Report report) throws IOException {
    return buildStructuredReportDocx(report);
  }

  private byte[] convertPdfToDocxSnapshot(byte[] pdf) throws IOException {
    try (PDDocument pdfDoc = PDDocument.load(pdf);
         XWPFDocument docx = new XWPFDocument();
         ByteArrayOutputStream out = new ByteArrayOutputStream()) {
      PDFRenderer renderer = new PDFRenderer(pdfDoc);
      for (int i = 0; i < pdfDoc.getNumberOfPages(); i++) {
        BufferedImage image = renderer.renderImageWithDPI(i, 150);
        ByteArrayOutputStream imageOut = new ByteArrayOutputStream();
        ImageIO.write(image, "png", imageOut);
        byte[] img = imageOut.toByteArray();

        XWPFParagraph p = docx.createParagraph();
        XWPFRun run = p.createRun();
        try (ByteArrayInputStream in = new ByteArrayInputStream(img)) {
          int widthEmu = Units.toEMU(520);
          int heightEmu = (int) ((double) image.getHeight() / (double) image.getWidth() * widthEmu);
          run.addPicture(in, Document.PICTURE_TYPE_PNG, "page-" + (i + 1) + ".png", widthEmu, heightEmu);
        } catch (Exception e) {
          throw new IOException("Word 快照插图失败: " + e.getMessage(), e);
        }
        if (i < pdfDoc.getNumberOfPages() - 1) {
          run.addBreak(org.apache.poi.xwpf.usermodel.BreakType.PAGE);
        }
      }
      docx.write(out);
      return out.toByteArray();
    }
  }

  private byte[] buildStructuredReportDocx(Report report) throws IOException {
    Submission submission = report.getSubmission();
    if (submission == null || submission.getAssessment() == null) {
      throw new ApiException(HttpStatus.INTERNAL_SERVER_ERROR, "报告缺少量表信息");
    }
    String paperId = nullSafe(submission.getAssessment().getTestPaperId());
    try (XWPFDocument doc = new XWPFDocument(); ByteArrayOutputStream out = new ByteArrayOutputStream()) {
      writeReportTitle(doc, submission.getAssessment().getTitle());
      writeMetaLine(doc, "导出时间：" + LocalDate.now(SHANGHAI).format(DATE_FORMAT));
      writeMetaLine(doc, "量表编号：" + paperId);
      if (paperId.contains("2082")) {
        buildSdsReportDocx(doc, report);
      } else if (paperId.contains("2083")) {
        buildSasReportDocx(doc, report);
      } else if (paperId.contains("2078")) {
        buildSclReportDocx(doc, report);
      } else {
        buildGenericReportDocx(doc, report);
      }
      writeReportNote(doc, "注：本报告仅为心理测评结果的参考，不作为临床诊断依据，如有疑问请咨询专业心理咨询师或精神科医生。");
      doc.write(out);
      return out.toByteArray();
    }
  }

  private void buildSdsReportDocx(XWPFDocument doc, Report report) {
    Submission submission = report.getSubmission();
    int score = submission.getRawScore();
    String level = nullSafe(submission.getLevel());
    writeSectionTitle(doc, "一、量表简介");
    writeBodyParagraph(doc, "抑郁自评量表(Self-rating depression scale, 简称 SDS)，是由美国杜克大学教授庄 (William W.K.Zung) 于1965年编制的。包括20个项目，每个项目按症状出现的频度分为四级评分，其中10个为正向评分，10个为反向评分。主要适用于具有抑郁症状的成年人。本测试应用范围颇广，使用简单，能相当直观地反映抑郁状态的有关症状及其严重程度，适用于抑郁症患者的粗筛以及一般人群的情绪状态评定。评定的时间范围是现在或者过去一周的实际感觉。");

    writeSectionTitle(doc, "二、测评结果");
    writeBodyParagraph(doc, "测评等级：" + level);
    writeBodyParagraph(doc, "标准分：" + score);
    if (!nullSafe(report.getSummary()).isBlank()) {
      writeBodyParagraph(doc, "结果说明：" + nullSafe(report.getSummary()));
    }

    writeSectionTitle(doc, "三、等级划分参考");
    writeTable(doc,
        List.of("等级", "标准分区间", "说明"),
        List.of(
            List.of("正常", "53以下", "未见明显抑郁症状"),
            List.of("轻度抑郁", "53-62", "存在轻度抑郁症状"),
            List.of("中度抑郁", "63-72", "存在中度抑郁症状"),
            List.of("重度抑郁", "73以上", "存在重度抑郁症状")));

    writeSectionTitle(doc, "四、指导建议");
    writeAdviceParagraphs(doc, resolveSdsAdvice(score, report));
  }

  private void buildSasReportDocx(XWPFDocument doc, Report report) {
    Submission submission = report.getSubmission();
    int score = submission.getRawScore();
    String level = nullSafe(submission.getLevel());
    writeSectionTitle(doc, "一、量表简介");
    writeBodyParagraph(doc, "焦虑自评量表 (Self - raing Anxiety Scale, 简称 SAS) 是由美国杜克大学教授庄 (William W.K.Zung) 于1971年编制的。包括20个项目，每个项目按症状出现的频度分为四级评分，其中15个正向评分，5个反向评分。主要用于评定焦虑的主观感受及其在治疗中的变化。本测验应用范围颇广，适用于各种职业，文化阶层及年龄段的正常人或各类焦虑症患者的筛查。可作为心理咨询门诊中了解焦虑症状的自评工具。评定的时间范围是现在或者\"过去一周\"的实际感觉。");

    writeSectionTitle(doc, "二、测评结果");
    writeBodyParagraph(doc, "测评等级：" + level);
    writeBodyParagraph(doc, "标准分：" + score);
    if (!nullSafe(report.getSummary()).isBlank()) {
      writeBodyParagraph(doc, "结果说明：" + nullSafe(report.getSummary()));
    }

    writeSectionTitle(doc, "三、等级划分参考");
    writeTable(doc,
        List.of("等级", "标准分区间", "说明"),
        List.of(
            List.of("正常", "50以下", "未见明显焦虑症状"),
            List.of("轻度焦虑", "50-59", "存在轻度焦虑症状"),
            List.of("中度焦虑", "60-69", "存在中度焦虑症状"),
            List.of("重度焦虑", "70以上", "存在重度焦虑症状")));

    writeSectionTitle(doc, "四、指导建议");
    writeAdviceParagraphs(doc, resolveSasAdvice(score, report));
  }

  private void buildSclReportDocx(XWPFDocument doc, Report report) {
    Submission submission = report.getSubmission();
    Map<String, Integer> scores = parseDimensionScoresSafe(submission.getDimensionScores());
    String level = nullSafe(submission.getLevel());
    writeSectionTitle(doc, "一、量表简介");
    writeBodyParagraph(doc, "症状自评量表（Self-reporting Inventory，简称 SCL-90），是世界上较为常用的心理健康测试量表之一。由德若伽提斯（L.R.Derogatis）于1975年编制。共有90个项目，分别是躯体化、强迫状态、人际关系敏感、抑郁、焦虑、敌对、恐怖、偏执、精神病性、其他项目。包含较广泛的精神病症状学内容，从感觉、情感、思维、意识、行为直至生活习惯、人际关系、饮食睡眠等均有涉及，用于从10个方面了解心理健康程度。");
    writeBodyParagraph(doc, "当总分超过160分，或阳性项目数超过43项，或任一因子分超过2分，或者自觉症状较为严重时，建议进一步进行专业评估。");

    writeSectionTitle(doc, "二、评定结果");
    writeTable(doc,
        List.of("评定结果", "总分", "阳性项目数", "判定阈值"),
        List.of(List.of(
            level.isBlank() ? "阴性" : level,
            valueOrDash(scores, "总分"),
            valueOrDash(scores, "阳性项目数"),
            "总分>160 或阳性项目数>43 提示异常")));

    writeSectionTitle(doc, "三、测评结果");
    writeTable(doc,
        List.of("项目", "原始分", "平均分", "参考诊断", "均分标准差"),
        buildSclTableRows(scores));

    writeSectionTitle(doc, "四、指导意见");
    writeAdviceParagraphs(doc, resolveSclAdvice(report));
  }

  private void buildGenericReportDocx(XWPFDocument doc, Report report) {
    Submission submission = report.getSubmission();
    writeSectionTitle(doc, "一、报告结果");
    writeBodyParagraph(doc, "量表：" + submission.getAssessment().getTitle());
    writeBodyParagraph(doc, "等级：" + nullSafe(submission.getLevel()));
    writeBodyParagraph(doc, "原始分：" + submission.getRawScore());
    if (!nullSafe(report.getSummary()).isBlank()) {
      writeBodyParagraph(doc, "结论：" + nullSafe(report.getSummary()));
    }
    writeSectionTitle(doc, "二、指导建议");
    writeAdviceParagraphs(doc, splitAdviceLines(nullSafe(report.getSuggestions())));
  }

  private void writeReportTitle(XWPFDocument doc, String title) {
    XWPFParagraph p = doc.createParagraph();
    p.setAlignment(ParagraphAlignment.CENTER);
    XWPFRun r = p.createRun();
    r.setBold(true);
    r.setFontSize(18);
    r.setText(nullSafe(title) + "报告");
  }

  private void writeMetaLine(XWPFDocument doc, String text) {
    XWPFParagraph p = doc.createParagraph();
    p.setAlignment(ParagraphAlignment.CENTER);
    XWPFRun r = p.createRun();
    r.setFontSize(10);
    r.setColor("666666");
    r.setText(text);
  }

  private void writeSectionTitle(XWPFDocument doc, String text) {
    XWPFParagraph p = doc.createParagraph();
    p.setSpacingBefore(180);
    p.setSpacingAfter(80);
    XWPFRun r = p.createRun();
    r.setBold(true);
    r.setFontSize(13);
    r.setColor("C9751A");
    r.setText(text);
  }

  private void writeBodyParagraph(XWPFDocument doc, String text) {
    if (nullSafe(text).isBlank()) return;
    XWPFParagraph p = doc.createParagraph();
    p.setAlignment(ParagraphAlignment.BOTH);
    p.setSpacingAfter(40);
    XWPFRun r = p.createRun();
    r.setFontSize(11);
    r.setText(text);
  }

  private void writeAdviceParagraphs(XWPFDocument doc, List<String> lines) {
    if (lines.isEmpty()) {
      writeBodyParagraph(doc, "暂无指导建议。");
      return;
    }
    for (String line : lines) {
      if (nullSafe(line).isBlank()) continue;
      XWPFParagraph p = doc.createParagraph();
      p.setAlignment(ParagraphAlignment.BOTH);
      p.setSpacingAfter(40);
      XWPFRun r = p.createRun();
      r.setFontSize(11);
      r.setText(line);
    }
  }

  private void writeReportNote(XWPFDocument doc, String text) {
    XWPFParagraph p = doc.createParagraph();
    p.setSpacingBefore(180);
    XWPFRun r = p.createRun();
    r.setFontSize(10);
    r.setColor("666666");
    r.setText(text);
  }

  private void writeTable(XWPFDocument doc, List<String> headers, List<List<String>> rows) {
    XWPFTable table = doc.createTable(Math.max(rows.size(), 1) + 1, headers.size());
    table.setWidth("100%");
    XWPFTableRow header = table.getRow(0);
    for (int i = 0; i < headers.size(); i++) {
      XWPFTableCell cell = header.getCell(i);
      cell.setColor("F7EBDD");
      setCellText(cell, headers.get(i), true);
    }
    for (int r = 0; r < rows.size(); r++) {
      XWPFTableRow row = table.getRow(r + 1);
      List<String> values = rows.get(r);
      for (int c = 0; c < headers.size(); c++) {
        setCellText(row.getCell(c), c < values.size() ? values.get(c) : "", false);
      }
    }
  }

  private void setCellText(XWPFTableCell cell, String text, boolean bold) {
    cell.removeParagraph(0);
    XWPFParagraph p = cell.addParagraph();
    p.setAlignment(ParagraphAlignment.CENTER);
    XWPFRun r = p.createRun();
    r.setBold(bold);
    r.setFontSize(10);
    r.setText(nullSafe(text));
  }

  private List<List<String>> buildSclTableRows(Map<String, Integer> scores) {
    return List.of(
        List.of("总分", valueOrDash(scores, "总分"), "", "", "141.42 ± 45.34"),
        List.of("总均分", valueOrDash(scores, "总均分"), "", "", "1.57 ± 0.50"),
        List.of("阴性项目数", valueOrDash(scores, "阴性项目数"), "", "", "58.51 ± 21.96"),
        List.of("阳性项目数", valueOrDash(scores, "阳性项目数"), "", "", "82.91 ± 65.97"),
        List.of("阳性项目均分", valueOrDash(scores, "阳性均分"), "", "", "2.45 ± 0.57"),
        List.of("躯体化", valueOrDash(scores, "躯体化"), avgOrBlank(scores, "躯体化均分"), degreeOrBlank(scores, "躯体化均分"), "1.39 ± 0.49"),
        List.of("强迫状态", valueOrDash(scores, "强迫症状"), avgOrBlank(scores, "强迫症状均分"), degreeOrBlank(scores, "强迫症状均分"), "1.94 ± 0.66"),
        List.of("人际关系敏感", valueOrDash(scores, "人际关系敏感"), avgOrBlank(scores, "人际关系敏感均分"), degreeOrBlank(scores, "人际关系敏感均分"), "1.72 ± 0.65"),
        List.of("抑郁", valueOrDash(scores, "抑郁"), avgOrBlank(scores, "抑郁均分"), degreeOrBlank(scores, "抑郁均分"), "1.56 ± 0.61"),
        List.of("焦虑", valueOrDash(scores, "焦虑"), avgOrBlank(scores, "焦虑均分"), degreeOrBlank(scores, "焦虑均分"), "1.57 ± 0.58"),
        List.of("敌对", valueOrDash(scores, "敌对"), avgOrBlank(scores, "敌对均分"), degreeOrBlank(scores, "敌对均分"), "1.52 ± 0.59"),
        List.of("恐怖", valueOrDash(scores, "恐怖"), avgOrBlank(scores, "恐怖均分"), degreeOrBlank(scores, "恐怖均分"), "1.43 ± 0.53"),
        List.of("偏执", valueOrDash(scores, "偏执"), avgOrBlank(scores, "偏执均分"), degreeOrBlank(scores, "偏执均分"), "1.55 ± 0.59"),
        List.of("精神病性", valueOrDash(scores, "精神病性"), avgOrBlank(scores, "精神病性均分"), degreeOrBlank(scores, "精神病性均分"), "1.51 ± 0.53"),
        List.of("其他项目", valueOrDash(scores, "其他"), avgOrBlank(scores, "其他均分"), degreeOrBlank(scores, "其他均分"), "1.16 ± 0.41"));
  }

  private Map<String, Integer> parseDimensionScoresSafe(String json) {
    Map<String, Integer> result = new HashMap<>();
    if (blank(json)) return result;
    String[] keys = {
        "总分", "总均分", "阴性项目数", "阳性项目数", "阳性均分",
        "躯体化", "躯体化均分", "强迫症状", "强迫症状均分", "人际关系敏感", "人际关系敏感均分",
        "抑郁", "抑郁均分", "焦虑", "焦虑均分", "敌对", "敌对均分", "恐怖", "恐怖均分", "偏执", "偏执均分",
        "精神病性", "精神病性均分", "其他", "其他均分"
    };
    for (String key : keys) {
      Integer value = parseJsonIntField(json, key);
      if (value != null) result.put(key, value);
    }
    return result;
  }

  private String valueOrDash(Map<String, Integer> scores, String key) {
    Integer value = scores.get(key);
    return value == null ? "-" : String.valueOf(value);
  }

  private String avgOrBlank(Map<String, Integer> scores, String key) {
    Integer value = scores.get(key);
    if (value == null) return "";
    return String.format(Locale.ROOT, "%.2f", value / 100.0);
  }

  private String degreeOrBlank(Map<String, Integer> scores, String key) {
    Integer value = scores.get(key);
    if (value == null) return "";
    double score = value / 100.0;
    if (score < 1.5) return "无";
    if (score < 2.5) return "轻";
    if (score < 3.5) return "中";
    if (score < 4.5) return "重";
    return "严重";
  }

  private List<String> resolveSdsAdvice(int score, Report report) {
    String advice = nullSafe(report.getSuggestions());
    if (advice.isBlank()) {
      if (score < 53) advice = "根据测评得分情况来看，您的情绪平和，没有抑郁症状，自身情绪基本处于愉快、开心的境况，对自己和生活感到满意，不会经常被不开心的事情困扰。即使是偶尔遇到挫折和不幸，也比较容易自我调节，或从他人那里得到支持帮助，从而克服难关。希望您能继续保持这种快乐明朗的心态，积极面对生活中的挑战。";
      else if (score <= 62) advice = "根据测评得分情况来看，您有时情绪低落，睡眠不佳、容易疲劳，经常犹疑不决、有空虚、无望等感觉。您此时的抑郁程度为轻度。建议先自我调整，通过运动，逛街，跟朋友聊天，听音乐等体育和娱乐活动调整一下情绪状态。如无好转请及时求助专业人员。";
      else if (score < 72) advice = "根据测评得分情况来看，您经常有情绪低落的时候，特别是早晨比较严重傍晚稍好一些。总想哭，经常失眠或者总是睡不醒。食欲不振，体重减轻，感觉四肢无力，对什么事情都提不起兴趣，觉得自己什么都做不好。时常自责，有自杀的念头。您此时的抑郁程度为中度。建议您积极进行自我调整，改变不合理认知，寻求支持的社会资源，适当转移注意力。而且要与专业人士保持联系，以便获得及时的帮助，协助您抵御不良情绪。";
      else advice = "根据测评得分情况来看，您总有情绪低落的状况，特别是早晨比较严重傍晚稍好一些。经常不由自主的泪流满面，严重的失眠，不想吃东西，不想动，性欲减退，反应迟钝，有很强的无望感，自罪自责，有强烈的自杀意念甚至已经有了自杀的行为表现。您此时为重度抑郁。建议您在家人的协助下寻求医生的帮助，按医嘱服用药物或住院治疗，辅助定期的心理咨询。让自己尽快摆脱抑郁症的困扰。";
    }
    return splitAdviceLines(advice);
  }

  private List<String> resolveSasAdvice(int score, Report report) {
    String advice = nullSafe(report.getSuggestions());
    if (advice.isBlank()) {
      if (score < 50) advice = "根据测评得分情况来看，您过去一周内情绪比较稳定，心态平和很少焦虑，您很少感到有压力感，较少感觉到交际、烦躁的情绪或者对未来事件不确定性的担心和忧虑。这可能是出于您对生活中的各种挫折和压力，能够以比较平和的心态去面对，善于用适当的方式去化解。";
      else if (score <= 59) advice = "根据测评得分情况来看，您过去一周内偶尔会感到紧张、害怕、恐惧，但焦虑程度较轻。建议你先通过适量有氧运动、冥想以及其他娱乐活动进行自我调节。如无好转，请及时求助专业人士。";
      else if (score <= 69) advice = "根据测评得分情况来看，您过去一周内经常感到恐惧、担心，甚至会有不幸的预感以及心悸等躯体症状。您此时的焦虑程度为中度。建议您可以通过转移注意力、做有氧运动、调整认知、换个角度思考问题等方式进行自我调整。同时寻求专业人士的帮助，进行系统的身心调整。";
      else advice = "根据测评得分情况来看，您过去一周绝大部分时间内都感到害怕、坐立不安，甚至会有胃痛、头晕、呼吸困难等躯体症状。您此时为重度焦虑。建议您尽快寻求专业人士的帮助，到医院、心理诊所等正规机构进行系统的身心调整，按照医嘱服用药物并辅助定期的心理咨询。让自己尽快摆脱焦虑症的困扰。";
    }
    return splitAdviceLines(advice);
  }

  private List<String> resolveSclAdvice(Report report) {
    String advice = nullSafe(report.getSuggestions());
    if (!advice.isBlank()) return splitAdviceLines(advice);
    return List.of("暂无指导建议。");
  }

  private List<String> splitAdviceLines(String advice) {
    if (blank(advice)) return List.of();
    return List.of(advice.split("\\r?\\n"))
        .stream()
        .map(this::nullSafe)
        .filter(item -> !item.isBlank())
        .toList();
  }

  private byte[] mergePdfDocuments(List<byte[]> pdfFiles) throws IOException {
    PDFMergerUtility merger = new PDFMergerUtility();
    try (ByteArrayOutputStream out = new ByteArrayOutputStream()) {
      for (byte[] pdf : pdfFiles) {
        merger.addSource(new ByteArrayInputStream(pdf));
      }
      merger.setDestinationStream(out);
      merger.mergeDocuments(null);
      return out.toByteArray();
    }
  }

  private PDFont loadChineseFont(PDDocument document) throws IOException {
    String[] fontPaths = {
        "/app/fonts/NotoSansCJK-Regular.ttc",
        "/Library/Fonts/Arial Unicode.ttf",
        "/System/Library/Fonts/Supplemental/Arial Unicode.ttf",
        "/System/Library/Fonts/Hiragino Sans GB.ttc",
        "/System/Library/Fonts/Supplemental/Songti.ttc",
        "/usr/share/fonts/opentype/noto/NotoSansCJK-Regular.ttc",
        "/usr/share/fonts/truetype/noto/NotoSansCJK-Regular.ttc",
        "/usr/share/fonts/noto-cjk/NotoSansCJK-Regular.ttc",
        "/usr/share/fonts/noto/NotoSansCJK-Regular.ttc",
        "/System/Library/Fonts/PingFang.ttc",
        "/System/Library/Fonts/STHeiti Light.ttc"
    };
    for (String path : fontPaths) {
      java.io.File font = new java.io.File(path);
      if (!font.exists()) continue;
      try {
        if (path.toLowerCase(Locale.ROOT).endsWith(".ttc")) {
          PDFont loaded = loadTtcFont(document, font);
          if (loaded != null) return loaded;
          continue;
        }
        return PDType0Font.load(document, font);
      } catch (IOException ignored) {
        // Continue trying next font candidate.
      }
    }
    return PDType1Font.HELVETICA;
  }

  private PDFont loadTtcFont(PDDocument document, java.io.File fontFile) throws IOException {
    try (TrueTypeCollection collection = new TrueTypeCollection(fontFile)) {
      String[] preferredNames = {
          "NotoSansCJKsc-Regular",
          "NotoSansCJKSC-Regular",
          "NotoSansCJK-Regular",
          "PingFangSC-Regular",
          "HiraginoSansGB-W3",
          "STHeitiSC-Light"
      };
      for (String name : preferredNames) {
        TrueTypeFont font = collection.getFontByName(name);
        if (font != null) {
          return PDType0Font.load(document, font, true);
        }
      }
      final PDFont[] loaded = new PDFont[1];
      collection.processAllFonts(font -> {
        if (loaded[0] != null) return;
        String name = font.getName();
        if (name != null && (name.toLowerCase(Locale.ROOT).contains("cjk")
            || name.toLowerCase(Locale.ROOT).contains("pingfang")
            || name.toLowerCase(Locale.ROOT).contains("hiragino")
            || name.toLowerCase(Locale.ROOT).contains("heiti")
            || name.toLowerCase(Locale.ROOT).contains("songti"))) {
          loaded[0] = PDType0Font.load(document, font, true);
        }
      });
      return loaded[0];
    }
  }

  private String safePdfText(PDFont font, String value) {
    if (value == null || value.isEmpty()) return "";
    try {
      font.encode(value);
      return value;
    } catch (Exception ignored) {
      return value.replaceAll("[^\\x20-\\x7E]", "?");
    }
  }

  private byte[] buildXlsx(ExportDataset dataset) throws IOException {
    try (Workbook workbook = new XSSFWorkbook(); ByteArrayOutputStream out = new ByteArrayOutputStream()) {
      Sheet sheet = workbook.createSheet("导出结果");
      Row header = sheet.createRow(0);
      for (int i = 0; i < dataset.headers().size(); i++) {
        header.createCell(i).setCellValue(dataset.headers().get(i));
      }
      for (int i = 0; i < dataset.rows().size(); i++) {
        Row row = sheet.createRow(i + 1);
        List<String> values = dataset.rows().get(i);
        for (int j = 0; j < values.size(); j++) row.createCell(j).setCellValue(values.get(j));
      }
      for (int i = 0; i < dataset.headers().size(); i++) sheet.autoSizeColumn(i);
      workbook.write(out);
      return out.toByteArray();
    }
  }

  private MediaType mediaTypeForFormat(String ext) {
    return switch (ext) {
      case "pdf" -> MediaType.APPLICATION_PDF;
      case "xlsx" -> MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
      case "docx" -> MediaType.parseMediaType("application/vnd.openxmlformats-officedocument.wordprocessingml.document");
      default -> MediaType.parseMediaType("text/csv; charset=UTF-8");
    };
  }

  private byte[] buildDocx(String title, ExportDataset dataset) throws IOException {
    try (XWPFDocument doc = new XWPFDocument(); ByteArrayOutputStream out = new ByteArrayOutputStream()) {
      XWPFParagraph titleP = doc.createParagraph();
      XWPFRun titleR = titleP.createRun();
      titleR.setBold(true);
      titleR.setFontSize(16);
      titleR.setText(title);

      XWPFParagraph timeP = doc.createParagraph();
      XWPFRun timeR = timeP.createRun();
      timeR.setFontSize(10);
      timeR.setText("导出时间: " + LocalDate.now(SHANGHAI).format(DATE_FORMAT));

      for (List<String> row : dataset.rows()) {
        XWPFParagraph p = doc.createParagraph();
        XWPFRun r = p.createRun();
        r.setFontSize(11);
        String key = row.isEmpty() ? "" : row.get(0);
        String value = row.size() > 1 ? row.get(1) : "";
        r.setText(key + "： " + value);
      }
      doc.write(out);
      return out.toByteArray();
    }
  }

  private record ExportDataset(List<String> headers, List<List<String>> rows) {}
  public record BatchMergedPdfRequest(List<Long> userIds) {}
  private record BatchPdfChunk(String filename, byte[] data) {}
  private record ReportCell(String level, String summary, Instant time, String suggestions) {}

  private List<Submission> latestSubmissions(List<Submission> submissions) {
    Map<String, Submission> latest = new java.util.LinkedHashMap<>();
    for (Submission item : submissions) {
      String key = item.getUser().getId() + ":" + item.getAssessment().getId();
      Submission existing = latest.get(key);
      if (existing == null || item.getSubmittedAt().isAfter(existing.getSubmittedAt())) {
        latest.put(key, item);
      }
    }
    return new ArrayList<>(latest.values());
  }
}
