import {
  BadRequestException,
  ConflictException,
  Injectable,
  NotFoundException,
} from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { DataSource, EntityManager, In, IsNull, Repository } from 'typeorm';
import { uuidV7 } from '../common/uuid-v7';
import { Bed } from '../entities/bed.entity';
import { Locker } from '../entities/locker.entity';
import { Occupancy } from '../entities/occupancy.entity';
import { Organization } from '../entities/organization.entity';
import { Room } from '../entities/room.entity';
import { Student } from '../entities/student.entity';
import { RoomsService } from '../rooms/rooms.service';
import {
  AiReview,
  type AiReviewColumn,
  type AiReviewRow,
  type AiReviewSection,
  type AiReviewSectionType,
} from './entities/ai-review.entity';
import type { ExcelSheetRows } from './ai-excel-reader.service';

const MAX_TITLE = 50;
const MAX_SUMMARY = 500;
const MAX_SECTIONS = 20;
const MAX_SECTION_TITLE = 50;
const MAX_COLUMNS = 30;
const MAX_COLUMN_KEY = 50;
const MAX_COLUMN_TITLE = 50;
const MAX_ROWS = 500;
const MAX_CELL_LENGTH = 200;
const MAX_ISSUES = 50;
const MAX_ISSUE_LENGTH = 200;
const MAX_SECTIONS_JSON_BYTES = 12 * 1024 * 1024;
const MAX_SECTION_JSON_BYTES = Math.floor(MAX_SECTIONS_JSON_BYTES / MAX_SECTIONS);
const MAX_CAPACITY = 200;

const DATE_RE = /^\d{4}-\d{2}-\d{2}$/;
const COLUMN_KEY_RE = /^[a-zA-Z0-9_]{1,50}$/;
const SECTION_KEY_RE = /^[a-zA-Z0-9_]{1,50}$/;
const SECTION_TYPES = new Set<AiReviewSectionType>([
  'students',
  'rooms',
  'transfers',
  'checkins',
]);
const SECTION_ORDER: AiReviewSectionType[] = ['students', 'rooms', 'transfers', 'checkins'];
const SECTION_DEPENDENCIES: Record<AiReviewSectionType, AiReviewSectionType[]> = {
  students: [],
  rooms: [],
  transfers: ['students', 'rooms'],
  checkins: [],
};
const SCHEMA_KEYS = new Set(['title', 'summary', 'sections']);
const SECTION_KEYS_ALLOWED = new Set([
  'key',
  'type',
  'title',
  'kind',
  'sheet',
  'columns',
  'rows',
  'issues',
]);
const COLUMN_KEYS_ALLOWED = new Set(['key', 'title']);

/**
 * Column-key aliases the model may produce when parsing workbooks.
 * Keys are normalized to canonical names per section so the import
 * logic only deals with one vocabulary.
 */
const SECTION_ALIASES: Record<AiReviewSectionType, Record<string, string>> = {
  students: {
    org: 'organization',
    organizationName: 'organization',
    orgName: 'organization',
  },
  rooms: {
    roomNo: 'roomNumber',
    number: 'roomNumber',
  },
  transfers: {
    fromRoom: 'oldRoom',
    currentRoom: 'oldRoom',
    sourceRoom: 'oldRoom',
    toRoom: 'newRoom',
    targetRoom: 'newRoom',
    destRoom: 'newRoom',
    date: 'transferDate',
    changeDate: 'transferDate',
    moveDate: 'transferDate',
    mobile: 'studentPhone',
    phone: 'studentPhone',
  },
  checkins: {
    studentName: 'name',
    mobile: 'phone',
    roomNo: 'roomNumber',
    room: 'roomNumber',
    date: 'checkInDate',
    inDate: 'checkInDate',
    checkinDate: 'checkInDate',
    outDate: 'checkOutDate',
    checkoutDate: 'checkOutDate',
  },
};

/**
 * Excel 表头 → 规范列名。与 SECTION_ALIASES 合并使用；
 * 键会被归一化（去空格/下划线/大小写），因此同时覆盖中文与英文写法。
 */
const SECTION_HEADER_ALIASES: Record<AiReviewSectionType, Record<string, string>> = {
  students: {
    姓名: 'name',
    学生姓名: 'name',
    name: 'name',
    手机号: 'phone',
    电话: 'phone',
    联系电话: 'phone',
    phone: 'phone',
    mobile: 'phone',
    学号: 'studentNo',
    学生编号: 'studentNo',
    studentNo: 'studentNo',
    studentno: 'studentNo',
    性别: 'gender',
    gender: 'gender',
    机构: 'organization',
    所属机构: 'organization',
    校区: 'organization',
    组织: 'organization',
    organization: 'organization',
  },
  rooms: {
    房间号: 'roomNumber',
    宿舍号: 'roomNumber',
    房号: 'roomNumber',
    roomNumber: 'roomNumber',
    roomnumber: 'roomNumber',
    容量: 'capacity',
    床位数: 'capacity',
    床位: 'capacity',
    capacity: 'capacity',
    楼栋: 'building',
    楼号: 'building',
    building: 'building',
    楼层: 'floor',
    floor: 'floor',
    房型: 'roomType',
    房间类型: 'roomType',
    roomType: 'roomType',
  },
  transfers: {
    学号: 'studentNo',
    studentNo: 'studentNo',
    studentno: 'studentNo',
    手机号: 'studentPhone',
    学生手机号: 'studentPhone',
    电话: 'studentPhone',
    phone: 'studentPhone',
    studentPhone: 'studentPhone',
    原宿舍: 'oldRoom',
    原房间: 'oldRoom',
    oldRoom: 'oldRoom',
    目标宿舍: 'newRoom',
    新宿舍: 'newRoom',
    newRoom: 'newRoom',
    换宿日期: 'transferDate',
    日期: 'transferDate',
    transferDate: 'transferDate',
  },
  checkins: {
    姓名: 'name',
    学生姓名: 'name',
    name: 'name',
    手机号: 'phone',
    电话: 'phone',
    phone: 'phone',
    mobile: 'phone',
    学号: 'studentNo',
    studentNo: 'studentNo',
    宿舍号: 'roomNumber',
    房间号: 'roomNumber',
    roomNumber: 'roomNumber',
    楼栋: 'building',
    building: 'building',
    性别: 'gender',
    gender: 'gender',
    入住时间: 'checkInDate',
    入住日期: 'checkInDate',
    checkInDate: 'checkInDate',
    计费起始日: 'billingStartDate',
    计费开始日: 'billingStartDate',
    退宿日期: 'checkOutDate',
    退宿时间: 'checkOutDate',
    离宿时间: 'checkOutDate',
    入住类型: 'stayType',
    住宿类型: 'stayType',
  },
};

const SECTION_CANONICAL_KEYS: Record<AiReviewSectionType, Set<string>> = {
  students: new Set(['name', 'phone', 'studentNo', 'gender', 'organization']),
  rooms: new Set(['roomNumber', 'capacity', 'building', 'floor', 'roomType']),
  transfers: new Set(['studentNo', 'studentPhone', 'oldRoom', 'newRoom', 'transferDate']),
  checkins: new Set([
    'name',
    'phone',
    'studentNo',
    'roomNumber',
    'checkInDate',
    'billingStartDate',
    'checkOutDate',
    'gender',
    'building',
    'stayType',
  ]),
};

export interface AiReviewSubmitResult {
  students: { created: number; skipped: number; issues: string[] };
  rooms: { created: number; skipped: number; issues: string[] };
  transfers: { completed: number; skipped: number; issues: string[] };
  checkins: { completed: number; skipped: number; issues: string[] };
  message: string;
}

export type AiReviewSectionResult =
  | { created: number; skipped: number; issues: string[] }
  | { completed: number; skipped: number; issues: string[] };

interface ValidatedReviewSchema {
  title: string;
  summary: string | null;
  sections: AiReviewSection[];
}

interface AiReviewStepSubmitResult {
  review: AiReview;
  result: AiReviewSectionResult;
  message: string;
}

function isPlainRecord(value: unknown): value is Record<string, unknown> {
  return Boolean(value) && typeof value === 'object' && !Array.isArray(value);
}

function requireString(
  value: unknown,
  label: string,
  max: number,
  optional = false,
): string {
  if (value === undefined || value === null) {
    if (optional) return '';
    throw new BadRequestException(`${label}不能为空`);
  }
  if (typeof value !== 'string' || !value.trim()) {
    throw new BadRequestException(`${label}必须是字符串`);
  }
  const trimmed = value.trim();
  if (trimmed.length > max) {
    throw new BadRequestException(`${label}长度不能超过 ${max}`);
  }
  return trimmed;
}

function assertKeys(raw: Record<string, unknown>, allowed: Set<string>, label: string): void {
  for (const key of Object.keys(raw)) {
    if (!allowed.has(key)) throw new BadRequestException(`${label}包含未知属性: ${key}`);
  }
}

function toDateString(value: unknown): string | null {
  if (typeof value === 'string' && DATE_RE.test(value.trim())) return value.trim();
  return null;
}

function normalizePhone(value: unknown): string | null {
  if (typeof value !== 'string') return null;
  const phone = value.replace(/[\s-]/g, '');
  return /^1[3-9]\d{9}$/.test(phone) ? phone : null;
}

function isSectionType(value: unknown): value is AiReviewSectionType {
  return typeof value === 'string' && SECTION_TYPES.has(value as AiReviewSectionType);
}

function normalizeSectionType(
  key: string,
  rawType: unknown,
): AiReviewSectionType {
  if (rawType !== undefined && rawType !== null && !isSectionType(rawType)) {
    throw new BadRequestException(`分表业务类型不支持: ${String(rawType)}`);
  }
  if (isSectionType(rawType)) return rawType;
  if (isSectionType(key)) return key;
  const prefix = SECTION_ORDER.find((type) => key.startsWith(`${type}_`));
  if (prefix) return prefix;
  throw new BadRequestException(`分表标识无法解析业务类型: ${key}`);
}

function sectionStatus(section: AiReviewSection): AiReviewSection['status'] {
  if (
    section.status === 'submitted' ||
    section.status === 'failed' ||
    section.status === 'skipped'
  ) {
    return section.status;
  }
  return 'pending';
}

function emptySectionResult(key: AiReviewSectionType): AiReviewSectionResult {
  return key === 'transfers' || key === 'checkins'
    ? { completed: 0, skipped: 0, issues: [] }
    : { created: 0, skipped: 0, issues: [] };
}

function sectionResultMessage(
  key: AiReviewSectionType,
  result: AiReviewSectionResult,
): string {
  if (key === 'students') {
    return `成功导入学生 ${(result as { created: number }).created} 人，跳过 ${result.skipped} 条`;
  }
  if (key === 'rooms') {
    return `成功导入宿舍 ${(result as { created: number }).created} 间，跳过 ${result.skipped} 条`;
  }
  if (key === 'transfers') {
    return `成功换宿 ${(result as { completed: number }).completed} 条，跳过 ${result.skipped} 条`;
  }
  return `成功入住 ${(result as { completed: number }).completed} 条，跳过 ${result.skipped} 条`;
}

function withInitialSectionState(section: AiReviewSection): AiReviewSection {
  return {
    ...section,
    status: 'pending',
    resultSummary: null,
    submittedAt: null,
  };
}

/**
 * A2UI batch-import review lifecycle.
 *
 * The model parses an uploaded workbook (students / rooms / transfers),
 * calls `render_review`, and the user inspects per-table preview cards
 * before confirming. Confirmation runs each section in its own
 * transaction in dependency order: students → rooms → transfers →
 * checkins. Per-row problems are collected as issues and the row is
 * skipped instead of failing the whole import.
 */
@Injectable()
export class AiReviewService {
  constructor(
    @InjectRepository(AiReview)
    private readonly reviews: Repository<AiReview>,
    private readonly dataSource: DataSource,
  ) {}

  /**
   * Validate `render_review` arguments and persist a pending review.
   * Throws BadRequestException when the schema is unsafe/invalid.
   */
  async createReview(
    input: { userId: number; conversationId: number; assistantMessageId: number },
    rawArgs: unknown,
  ): Promise<AiReview> {
    const schema = this.validateSchema(rawArgs);
    const sections = (await this.enrichWithIssues(schema.sections)).map(
      withInitialSectionState,
    );
    const sectionsJson = JSON.stringify(sections);
    if (Buffer.byteLength(sectionsJson, 'utf8') > MAX_SECTIONS_JSON_BYTES) {
      throw new BadRequestException('预览数据过大');
    }
    return this.reviews.save(
      this.reviews.create({
        id: uuidV7(),
        userId: input.userId,
        conversationId: input.conversationId,
        assistantMessageId: input.assistantMessageId,
        title: schema.title,
        summary: schema.summary,
        sectionsJson,
        status: 'pending',
        resultSummary: null,
        submittedAt: null,
      }),
    );
  }

  async findOwnedPending(reviewId: string, userId: number): Promise<AiReview> {
    const review = await this.reviews.findOne({
      where: { id: reviewId, userId, status: 'pending' },
    });
    if (!review) throw new NotFoundException('导入预览不存在、已确认或已失效');
    return review;
  }

  /**
   * Mark every other pending review in the same conversation as expired.
   * Called after a new render_review is successfully created so older cards
   * are superseded instead of silently staying confirmable.
   */
  async expirePreviousReviews(
    userId: number,
    conversationId: number,
    exceptReviewId: string,
  ): Promise<AiReview[]> {
    const pending = await this.reviews.find({
      where: { userId, conversationId, status: 'pending' },
    });
    const expired = pending.filter((review) => review.id !== exceptReviewId);
    if (expired.length === 0) return [];
    const ids = expired.map((review) => review.id);
    await this.reviews.update({ id: In(ids) }, { status: 'expired' });
    return expired.map((review) => ({ ...review, status: 'expired' as const }));
  }

  /**
   * Return a review owned by the user regardless of overall status.
   * Used by step confirmation so an already-completed card can respond
   * with a conflict instead of a plain not-found error.
   */
  async findOwned(reviewId: string, userId: number): Promise<AiReview> {
    const review = await this.reviews.findOne({
      where: { id: reviewId, userId },
    });
    if (!review) throw new NotFoundException('导入预览不存在');
    return review;
  }

  /**
   * Return the newest pending review bound to an assistant message, if any.
   * Used to guarantee at most one batch-import preview card per message.
   */
  async findPendingByAssistantMessage(assistantMessageId: number): Promise<AiReview | null> {
    return this.reviews.findOne({
      where: { assistantMessageId, status: 'pending' },
      order: { createdAt: 'DESC' },
    });
  }

  /**
   * 服务端直接解析上传的 Excel 生成审阅分表：行数据来自文件原文，
   * 不经过模型转抄，避免漏行/错值。表头按内置字典自动映射，
   * 模型可通过 sections[].columns[].sourceHeader 显式指定映射。
   */
  async buildSectionsFromWorkbook(
    sheets: ExcelSheetRows[],
    rawArgs: unknown,
  ): Promise<AiReviewSection[]> {
    if (!isPlainRecord(rawArgs)) throw new BadRequestException('导入预览参数必须是对象');
    const rawSections = rawArgs.sections;
    if (!Array.isArray(rawSections) || rawSections.length === 0) {
      throw new BadRequestException('至少需要一个分表');
    }
    if (rawSections.length > MAX_SECTIONS) {
      throw new BadRequestException(`分表不能超过 ${MAX_SECTIONS} 个`);
    }

    const seen = new Set<string>();
    const sections: AiReviewSection[] = [];
    for (let index = 0; index < rawSections.length; index += 1) {
      const raw = rawSections[index];
      if (!isPlainRecord(raw) || typeof raw.key !== 'string') {
        throw new BadRequestException(`第 ${index + 1} 个分表格式无效`);
      }
      const key = raw.key.trim();
      if (!SECTION_KEY_RE.test(key)) {
        throw new BadRequestException(`分表标识 ${key} 只能包含字母、数字、下划线（≤50）`);
      }
      const type = normalizeSectionType(key, raw.type);
      if (seen.has(key)) throw new BadRequestException(`分表标识重复: ${key}`);
      seen.add(key);

      const title = requireString(raw.title, '分表标题', MAX_SECTION_TITLE);
      const sheetName =
        raw.sheet === undefined || raw.sheet === null ? undefined : String(raw.sheet).trim();
      const headerRow = raw.headerRow === undefined || raw.headerRow === null ? 1 : Number(raw.headerRow);
      if (!Number.isInteger(headerRow) || headerRow < 1 || headerRow > 1000) {
        throw new BadRequestException(`分表 ${key} 的 headerRow 无效`);
      }

      const sheet = sheetName
        ? (sheets.find((item) => item.name === sheetName) ??
          sheets.find((item) => item.name.includes(sheetName)))
        : sheets[0];
      if (!sheet) {
        throw new BadRequestException(`找不到工作表「${sheetName}」`);
      }

      sections.push(
        this.buildSectionFromSheet(key, type, title, sheet.name, sheet, headerRow, raw.columns),
      );
    }
    return sections;
  }

  private buildSectionFromSheet(
    key: string,
    type: AiReviewSectionType,
    title: string,
    sheetName: string,
    sheet: ExcelSheetRows,
    headerRow: number,
    rawColumns: unknown,
  ): AiReviewSection {
    const issues: string[] = [];
    const aliasMap = this.buildHeaderAliasMap(type);
    if (sheet.rows.length < headerRow) {
      return {
        key,
        type,
        title,
        kind: 'table',
        sheet: sheetName,
        columns: [],
        rows: [],
        issues: [`工作表「${sheet.name}」没有第 ${headerRow} 行表头`],
      };
    }

    const explicit = new Map<string, string>();
    if (rawColumns !== undefined) {
      if (!Array.isArray(rawColumns)) {
        throw new BadRequestException(`分表 ${key} 的 columns 无效`);
      }
      for (const column of rawColumns) {
        if (!isPlainRecord(column) || typeof column.key !== 'string') {
          throw new BadRequestException(`分表 ${key} 的列定义无效`);
        }
        const canonical = aliasMap.get(this.normalizeHeader(column.key));
        if (!canonical || !SECTION_CANONICAL_KEYS[type].has(canonical)) {
          throw new BadRequestException(`分表 ${key} 的列标识无效: ${column.key}`);
        }
        if (typeof column.sourceHeader === 'string' && column.sourceHeader.trim()) {
          explicit.set(this.normalizeHeader(column.sourceHeader), canonical);
        } else {
          explicit.set(this.normalizeHeader(column.key), canonical);
        }
      }
    }

    const headerCells = sheet.rows[headerRow - 1];
    const dataRows = sheet.rows.slice(headerRow);
    const mapping = new Map<number, string>();
    const columns: AiReviewColumn[] = [];

    for (let colIndex = 0; colIndex < headerCells.length; colIndex += 1) {
      const header = String(headerCells[colIndex] ?? '').trim();
      if (!header) continue;
      const canonical =
        explicit.get(this.normalizeHeader(header)) ?? aliasMap.get(this.normalizeHeader(header));
      if (!canonical) {
        issues.push(`列「${header}」未识别，已忽略`);
        continue;
      }
      if (Array.from(mapping.values()).includes(canonical)) continue;
      mapping.set(colIndex, canonical);
      columns.push({ key: canonical, title: header.slice(0, MAX_COLUMN_TITLE) });
    }

    if (columns.length === 0) {
      return {
        key,
        type,
        title,
        kind: 'table',
        sheet: sheetName,
        columns: [],
        rows: [],
        issues: [...issues, '没有识别到可导入的列'],
      };
    }

    const rows: AiReviewRow[] = [];
    let totalBytes = 0;
    for (const cells of dataRows) {
      const row: AiReviewRow = {};
      for (const [colIndex, canonical] of mapping) {
        const raw = cells[colIndex];
        const text = raw === undefined || raw === null ? '' : String(raw).trim();
        if (!text) continue;
        row[canonical] = text.length > MAX_CELL_LENGTH ? text.slice(0, MAX_CELL_LENGTH) : text;
      }
      if (Object.keys(row).length === 0) continue;
      const rowBytes = Buffer.byteLength(JSON.stringify(row), 'utf8');
      if (totalBytes + rowBytes > MAX_SECTION_JSON_BYTES) {
        issues.push(`「${title}」数据量过大，仅保留前 ${rows.length} 行`);
        break;
      }
      totalBytes += rowBytes;
      rows.push(row);
      if (rows.length >= MAX_ROWS) {
        issues.push(`「${title}」超过 ${MAX_ROWS} 行，仅保留前 ${MAX_ROWS} 行`);
        break;
      }
    }

    return {
      key,
      type,
      title,
      kind: 'table',
      sheet: sheetName,
      columns,
      rows,
      issues: [...new Set(issues)].slice(-MAX_ISSUES),
    };
  }

  private buildHeaderAliasMap(key: AiReviewSectionType): Map<string, string> {
    const merged: Record<string, string> = {
      ...SECTION_HEADER_ALIASES[key],
      ...SECTION_ALIASES[key],
    };
    const map = new Map<string, string>();
    for (const [header, canonical] of Object.entries(merged)) {
      map.set(this.normalizeHeader(header), canonical);
    }
    return map;
  }

  private normalizeHeader(value: string): string {
    return value.trim().toLowerCase().replace(/[\s_-]+/g, '');
  }

  /** Public shape sent via `ui.review` SSE and mirrored into message metadata. */
  serialize(review: AiReview): Record<string, unknown> {
    return {
      id: review.id,
      title: review.title,
      summary: review.summary,
      sections: this.parseSections(review.sectionsJson),
      status: review.status,
      resultSummary: review.resultSummary,
    };
  }

  parseSections(sectionsJson: string): AiReviewSection[] {
    let parsed: unknown;
    try {
      parsed = JSON.parse(sectionsJson);
    } catch {
      return [];
    }
    if (!Array.isArray(parsed)) return [];
    return parsed.map((item) => {
      if (!isPlainRecord(item) || typeof item.key !== 'string') {
        throw new BadRequestException('导入预览分表格式无效');
      }
      const section = item as Partial<AiReviewSection> & { key: string };
      const type = normalizeSectionType(section.key, section.type);
      return {
        ...section,
        key: section.key,
        type,
        title: typeof section.title === 'string' ? section.title : section.key,
        kind: 'table',
        columns: Array.isArray(section.columns) ? section.columns : [],
        rows: Array.isArray(section.rows) ? section.rows : [],
        issues: Array.isArray(section.issues) ? section.issues : [],
        ...(typeof section.sheet === 'string' ? { sheet: section.sheet } : {}),
        status: sectionStatus(section as AiReviewSection),
        resultSummary:
          typeof section.resultSummary === 'string' ? section.resultSummary : null,
        submittedAt:
          typeof section.submittedAt === 'string' ? section.submittedAt : null,
      } as AiReviewSection;
    });
  }

  /**
   * Confirm one section in its own transaction.
   *
   * Row-level problems become section issues and are skipped instead of
   * failing the section. A dependency violation or an already-submitted
   * section throws ConflictException; unexpected import errors mark the
   * section as failed and are rethrown so the caller can retry later.
   */
  async submitSection(
    reviewId: string,
    userId: number,
    sectionKey: string,
  ): Promise<AiReviewStepSubmitResult> {
    if (!SECTION_KEY_RE.test(sectionKey)) {
      throw new BadRequestException(`分表标识无效: ${sectionKey}`);
    }
    try {
      return await this.dataSource.transaction(async (manager) => {
        const review = await manager.findOne(AiReview, {
          where: { id: reviewId, userId },
        });
        if (!review) throw new NotFoundException('导入预览不存在');
        if (review.status === 'submitted') {
          throw new ConflictException('导入已全部确认，无需重复确认');
        }
        if (review.status === 'expired') {
          throw new ConflictException('导入预览已失效，请重新生成预览');
        }
        const sections = this.parseSections(review.sectionsJson);
        const index = sections.findIndex((section) => section.key === sectionKey);
        if (index === -1) throw new NotFoundException(`分表不存在: ${sectionKey}`);
        const section = sections[index];
        const sectionType = section.type;
        if (section.status === 'submitted') {
          throw new ConflictException(`分表「${section.title}」已确认导入`);
        }
        const dependency = this.unmetDependency(sections, sectionType);
        if (dependency) {
          throw new ConflictException(
            dependency.step === -1
              ? `「${dependency.title}」尚未导入，请先确认对应分表`
              : `请先确认第 ${dependency.step + 1} 步「${dependency.title}」`,
          );
        }
        const result = await this.importOneSection(section, manager);
        const message = sectionResultMessage(sectionType, result);
        section.status = 'submitted';
        section.resultSummary = JSON.stringify({ ...result, message });
        section.submittedAt = new Date().toISOString();
        section.issues = this.mergeIssues(section.issues, result.issues);
        review.sectionsJson = JSON.stringify(sections);
        if (sections.every((item) => sectionStatus(item) === 'submitted')) {
          review.status = 'submitted';
          review.resultSummary = JSON.stringify(this.buildAggregateResult(sections));
          review.submittedAt = new Date();
        }
        await manager.save(review);
        return { review, result, message };
      });
    } catch (error) {
      if (
        error instanceof ConflictException ||
        error instanceof NotFoundException ||
        error instanceof BadRequestException
      ) {
        throw error;
      }
      const message =
        error instanceof Error ? error.message.slice(0, 200) : '分表导入失败';
      await this.markSectionFailed(reviewId, userId, sectionKey, message);
      throw error;
    }
  }

  /**
   * Confirm every pending section in dependency order, each inside its
   * own transaction. Unexpected failures are persisted per section and
   * do not stop the remaining sections from being attempted.
   */
  async submitAll(reviewId: string, userId: number): Promise<{
    review: AiReview;
    result: AiReviewSubmitResult;
  }> {
    const initial = await this.findOwned(reviewId, userId);
    if (initial.status === 'expired') {
      throw new ConflictException('导入预览已失效，请重新生成预览');
    }
    const sections = this.parseSections(initial.sectionsJson);
    const result = this.buildAggregateResult(sections);

    for (const type of SECTION_ORDER) {
      for (const section of sections.filter((item) => item.type === type)) {
        if (section.status === 'submitted') continue;
        try {
          const step = await this.submitSection(reviewId, userId, section.key);
          this.mergeStepResult(result, type, step.result);
        } catch (error) {
          if (
            error instanceof ConflictException ||
            error instanceof NotFoundException ||
            error instanceof BadRequestException
          ) {
            const issue = error.message;
            const empty = emptySectionResult(type);
            this.mergeStepResult(result, type, {
              ...empty,
              issues: [...empty.issues, issue],
            });
            continue;
          }
          const empty = emptySectionResult(type);
          this.mergeStepResult(result, type, {
            ...empty,
            issues: [
              ...empty.issues,
              error instanceof Error ? error.message.slice(0, 200) : '分表导入失败',
            ],
          });
        }
      }
    }

    result.message = this.buildAggregateMessage(result);
    const review = await this.findOwned(reviewId, userId);
    return { review, result };
  }

  /**
   * Confirm every sheet of one business type, each in its own transaction.
   * A step that fails is marked `failed` and the remaining sheets still run;
   * the latest review is returned even when some sheets failed. Dependencies
   * are evaluated up front so an unmet prerequisite returns 409 before any
   * import is attempted.
   */
  async submitGroup(
    reviewId: string,
    userId: number,
    type: AiReviewSectionType,
  ): Promise<{ review: AiReview }> {
    if (!isSectionType(type)) throw new BadRequestException(`业务类型不支持: ${String(type)}`);
    const initial = await this.findOwned(reviewId, userId);
    if (initial.status === 'submitted') {
      throw new ConflictException('导入已全部确认，无需重复确认');
    }
    if (initial.status === 'expired') {
      throw new ConflictException('导入预览已失效，请重新生成预览');
    }
    const sections = this.parseSections(initial.sectionsJson);
    const group = sections.filter((section) => section.type === type);
    if (group.length === 0) throw new NotFoundException(`分表类型不存在: ${type}`);
    if (group.every((section) => section.status === 'submitted')) {
      return { review: initial };
    }

    const dependency = this.unmetDependency(sections, type);
    if (dependency) {
      throw new ConflictException(
        dependency.step === -1
          ? `「${dependency.title}」尚未导入，请先确认对应分表`
          : `请先确认第 ${dependency.step + 1} 步「${dependency.title}」`,
      );
    }

    for (const section of group) {
      if (section.status === 'submitted') continue;
      try {
        await this.submitSection(reviewId, userId, section.key);
      } catch {
        // submitSection already marks unexpected failures; expected conflicts
        // (e.g. a concurrent duplicate confirm) are also non-blocking here.
      }
    }
    return { review: await this.findOwned(reviewId, userId) };
  }

  private mergeStepResult(
    target: AiReviewSubmitResult,
    key: AiReviewSectionType,
    value: AiReviewSectionResult,
  ): void {
    if (key === 'students' || key === 'rooms') {
      const created = (value as { created: number }).created;
      target[key].created += created;
      target[key].skipped += value.skipped;
      target[key].issues = this.mergeIssues(target[key].issues, value.issues);
    } else {
      const completed = (value as { completed: number }).completed;
      target[key].completed += completed;
      target[key].skipped += value.skipped;
      target[key].issues = this.mergeIssues(target[key].issues, value.issues);
    }
  }

  private buildAggregateResult(sections: AiReviewSection[]): AiReviewSubmitResult {
    const result: AiReviewSubmitResult = {
      students: { created: 0, skipped: 0, issues: [] },
      rooms: { created: 0, skipped: 0, issues: [] },
      transfers: { completed: 0, skipped: 0, issues: [] },
      checkins: { completed: 0, skipped: 0, issues: [] },
      message: '',
    };
    for (const section of sections) {
      const stored = this.parseStoredSectionResult(section);
      if (!stored) continue;
      this.mergeStepResult(result, section.type, stored);
    }
    result.message = this.buildAggregateMessage(result);
    return result;
  }

  private buildAggregateMessage(result: AiReviewSubmitResult): string {
    const totalSkipped =
      result.students.skipped +
      result.rooms.skipped +
      result.transfers.skipped +
      result.checkins.skipped;
    return (
      `成功导入学生 ${result.students.created} 人、宿舍 ${result.rooms.created} 间、` +
      `换宿 ${result.transfers.completed} 条、入住 ${result.checkins.completed} 条；跳过 ${totalSkipped} 条`
    );
  }

  private parseStoredSectionResult(
    section: AiReviewSection,
  ): AiReviewSectionResult | null {
    if (section.status !== 'submitted' || !section.resultSummary) return null;
    try {
      const parsed = JSON.parse(section.resultSummary) as Record<string, unknown>;
      const skipped = Number(parsed.skipped) || 0;
      const issues = Array.isArray(parsed.issues)
        ? parsed.issues.filter((item): item is string => typeof item === 'string')
        : [];
      if (section.type === 'transfers' || section.type === 'checkins') {
        return {
          completed: Number(parsed.completed) || 0,
          skipped,
          issues,
        };
      }
      return {
        created: Number(parsed.created) || 0,
        skipped,
        issues,
      };
    } catch {
      return null;
    }
  }

  private mergeIssues(existing: string[], incoming: string[]): string[] {
    return [...new Set([...existing, ...incoming])].slice(-MAX_ISSUES);
  }

  private unmetDependency(
    sections: AiReviewSection[],
    sectionType: AiReviewSectionType,
  ): { step: number; title: string } | null {
    const dependencies = SECTION_DEPENDENCIES[sectionType] ?? [];
    for (const dependencyType of dependencies) {
      const matches = sections.filter((section) => section.type === dependencyType);
      if (matches.length === 0) {
        return { step: -1, title: dependencyType };
      }
      for (const section of matches) {
        if (sectionStatus(section) !== 'submitted') {
          return { step: sections.indexOf(section), title: section.title };
        }
      }
    }
    return null;
  }

  private async markSectionFailed(
    reviewId: string,
    userId: number,
    sectionKey: string,
    message: string,
  ): Promise<void> {
    try {
      await this.dataSource.transaction(async (manager) => {
        const review = await manager.findOne(AiReview, {
          where: { id: reviewId, userId },
        });
        if (!review || review.status === 'submitted' || review.status === 'expired') return;
        const sections = this.parseSections(review.sectionsJson);
        const section = sections.find((item) => item.key === sectionKey);
        if (!section || section.status === 'submitted') return;
        section.status = 'failed';
        section.resultSummary = message;
        section.issues = this.mergeIssues(section.issues, [`导入失败：${message}`]);
        review.sectionsJson = JSON.stringify(sections);
        await manager.save(review);
      });
    } catch {
      // Failure recording is best-effort; the original error is more useful.
    }
  }

  /**
   * Preview-time database validation. The AI's parsed rows are checked
   * against the current system (organizations, duplicate students/rooms,
   * occupancy state, transfer targets) and the findings are appended to
   * each section's issues so the user sees them BEFORE confirming.
   * Problems found here do not block preview creation; the import phase
   * re-checks everything and skips problematic rows.
   */
  private async enrichWithIssues(sections: AiReviewSection[]): Promise<AiReviewSection[]> {
    try {
      const organizationRepo = this.dataSource.getRepository(Organization);
      const studentRepo = this.dataSource.getRepository(Student);
      const roomRepo = this.dataSource.getRepository(Room);
      const occupancyRepo = this.dataSource.getRepository(Occupancy);
      const organizations = await organizationRepo.find({ where: { status: 'active' } });

      const roomSections = sections.filter((section) => section.type === 'rooms');
      const incomingRoomNumbers = new Set(
        roomSections.flatMap((section) =>
          (section.rows ?? [])
            .map((row) =>
              row.roomNumber === undefined ? '' : String(row.roomNumber).trim(),
            )
            .filter(Boolean),
        ),
      );

      const enriched: AiReviewSection[] = [];
      for (const section of sections) {
        const issues = [...section.issues];
        if (section.type === 'students') {
          await this.enrichStudentIssues(section, issues, organizations, studentRepo);
        } else if (section.type === 'rooms') {
          await this.enrichRoomIssues(section, issues, roomRepo);
        } else if (section.type === 'transfers') {
          await this.enrichTransferIssues(
            section,
            issues,
            studentRepo,
            roomRepo,
            occupancyRepo,
            incomingRoomNumbers,
          );
        } else if (section.type === 'checkins') {
          await this.enrichCheckinIssues(
            section,
            issues,
            studentRepo,
            roomRepo,
            occupancyRepo,
          );
        }
        enriched.push({
          ...section,
          issues: [...new Set(issues)].slice(-MAX_ISSUES),
        });
      }
      return enriched;
    } catch {
      // Database validation is best-effort; fall back to model-provided issues.
      return sections;
    }
  }

  private async enrichStudentIssues(
    section: AiReviewSection,
    issues: string[],
    organizations: Organization[],
    studentRepo: Repository<Student>,
  ): Promise<void> {
    const seen = new Set<string>();
    for (const row of section.rows) {
      const name = row.name === undefined || row.name === null ? '' : String(row.name).trim();
      const phone = normalizePhone(row.phone);
      const studentNo =
        row.studentNo === undefined || row.studentNo === null
          ? ''
          : String(row.studentNo).trim();
      const organizationId = await this.resolveOrganizationId(row.organization, organizations);
      if (organizationId === null) {
        issues.push(`学生「${name}」的所属机构无法识别，导入时将按本机构处理`);
      }
      const dedupeKey = phone ? `phone:${phone}` : studentNo ? `no:${studentNo}` : '';
      if (dedupeKey && seen.has(dedupeKey)) {
        issues.push(`学生「${name}」与同一批次中的其他学生手机号/学号重复，导入时将跳过`);
      }
      seen.add(dedupeKey);
      if (!dedupeKey) continue;
      const existing = phone
        ? await studentRepo.findOne({ where: { phone } })
        : await studentRepo.findOne({ where: { studentNo } });
      if (existing) {
        issues.push(`学生「${name}」已存在（按手机号/学号匹配），导入时将跳过`);
      }
    }
  }

  private async enrichRoomIssues(
    section: AiReviewSection,
    issues: string[],
    roomRepo: Repository<Room>,
  ): Promise<void> {
    const seen = new Set<string>();
    for (const row of section.rows) {
      const roomNumber =
        row.roomNumber === undefined || row.roomNumber === null
          ? ''
          : String(row.roomNumber).trim();
      if (!roomNumber) continue;
      if (seen.has(roomNumber)) {
        issues.push(`宿舍「${roomNumber}」在同一批次中重复，导入时将跳过`);
        continue;
      }
      seen.add(roomNumber);
      const existing = await roomRepo.findOne({ where: { roomNumber } });
      if (existing) {
        issues.push(`宿舍「${roomNumber}」已存在，导入时将跳过`);
      }
    }
  }

  private async enrichCheckinIssues(
    section: AiReviewSection,
    issues: string[],
    studentRepo: Repository<Student>,
    roomRepo: Repository<Room>,
    occupancyRepo: Repository<Occupancy>,
  ): Promise<void> {
    const seen = new Set<string>();
    for (const row of section.rows) {
      const name = row.name === undefined || row.name === null ? '' : String(row.name).trim();
      const phone = normalizePhone(row.phone);
      const studentNo =
        row.studentNo === undefined || row.studentNo === null
          ? ''
          : String(row.studentNo).trim();
      const roomNumber =
        row.roomNumber === undefined || row.roomNumber === null
          ? ''
          : String(row.roomNumber).trim();
      if (!name || !roomNumber) {
        issues.push('存在姓名或宿舍号为空的入住记录行，导入时将跳过');
        continue;
      }
      if (!phone && !studentNo) {
        issues.push(`学生「${name}」缺少手机号/学号，无法关联或创建学生`);
        continue;
      }
      const dedupeKey = phone ? `phone:${phone}` : `no:${studentNo}`;
      if (seen.has(dedupeKey)) {
        issues.push(`学生「${name}」与同一批次中的其他行手机号/学号重复，导入时将跳过`);
      }
      seen.add(dedupeKey);

      const rawDate =
        row.checkInDate === undefined || row.checkInDate === null
          ? ''
          : String(row.checkInDate).trim();
      if (rawDate && !DATE_RE.test(rawDate)) {
        issues.push(`学生「${name}」的入住日期格式无效（应为 YYYY-MM-DD），导入时按当天处理`);
      }

      const student = phone
        ? await studentRepo.findOne({ where: { phone } })
        : await studentRepo.findOne({ where: { studentNo } });
      if (!student) {
        issues.push(`学生「${name}」不存在，导入时将自动创建并归入本机构`);
      }
      const room = roomNumber
        ? await roomRepo.findOne({ where: { roomNumber } })
        : null;
      if (!room) {
        issues.push(`宿舍「${roomNumber}」不存在，导入时将自动创建`);
      }

      const checkOutDate = toDateString(row.checkOutDate);
      if (student && !checkOutDate) {
        const active = await occupancyRepo.findOne({
          where: { studentId: student.id, checkOutDate: IsNull() },
          order: { id: 'DESC' },
        });
        if (active) {
          issues.push(
            `学生「${name}」当前已在住，导入时将跳过（如为历史记录请填写退宿日期）`,
          );
        }
      }
    }
  }

  private async enrichTransferIssues(
    section: AiReviewSection,
    issues: string[],
    studentRepo: Repository<Student>,
    roomRepo: Repository<Room>,
    occupancyRepo: Repository<Occupancy>,
    incomingRoomNumbers: Set<string>,
  ): Promise<void> {
    for (const row of section.rows) {
      const studentNo =
        row.studentNo === undefined || row.studentNo === null
          ? ''
          : String(row.studentNo).trim();
      const phone = normalizePhone(row.studentPhone);
      const newRoomNumber =
        row.newRoom === undefined || row.newRoom === null
          ? ''
          : String(row.newRoom).trim();
      const student = studentNo
        ? await studentRepo.findOne({ where: { studentNo } })
        : phone
          ? await studentRepo.findOne({ where: { phone } })
          : null;
      if (!student) {
        issues.push(`换宿到「${newRoomNumber}」的学生不存在（缺少手机号/学号），导入时将跳过`);
        continue;
      }
      const active = await occupancyRepo.findOne({
        where: { studentId: student.id, checkOutDate: IsNull() },
        order: { id: 'DESC' },
      });
      if (!active) {
        issues.push(`学生「${student.name}」当前没有在住记录，无法换宿`);
        continue;
      }
      const oldRoom = await roomRepo.findOne({ where: { id: active.roomId } });
      const oldRoomNumber = oldRoom?.roomNumber ?? String(active.roomId);
      const expectedOldRoom =
        row.oldRoom === undefined || row.oldRoom === null
          ? ''
          : String(row.oldRoom).trim();
      if (expectedOldRoom && expectedOldRoom !== oldRoomNumber) {
        issues.push(
          `学生「${student.name}」原宿舍为「${oldRoomNumber}」，与行内填写的「${expectedOldRoom}」不一致`,
        );
      }
      const targetExists =
        incomingRoomNumbers.has(newRoomNumber) ||
        Boolean(await roomRepo.findOne({ where: { roomNumber: newRoomNumber } }));
      if (!newRoomNumber) {
        issues.push('存在目标宿舍为空的行，导入时将跳过');
      } else if (!targetExists) {
        issues.push(
          `学生「${student.name}」的目标宿舍「${newRoomNumber}」不存在，且本次导入未包含该宿舍`,
        );
      }
      if (newRoomNumber && oldRoomNumber === newRoomNumber) {
        issues.push(`学生「${student.name}」的目标宿舍与当前宿舍相同`);
      }
    }
  }

  private async importOneSection(
    section: AiReviewSection,
    manager: EntityManager,
  ): Promise<AiReviewSectionResult> {
    if (section.type === 'students') return this.importStudents(section, manager);
    if (section.type === 'rooms') return this.importRooms(section, manager);
    if (section.type === 'transfers') return this.importTransfers(section, manager);
    return this.importCheckins(section, manager);
  }

  private async importStudents(
    section: AiReviewSection | undefined,
    manager: EntityManager,
  ): Promise<{ created: number; skipped: number; issues: string[] }> {
    let created = 0;
    let skipped = 0;
    const issues: string[] = [];
    if (!section || section.rows.length === 0) return { created, skipped, issues };
    const studentRepo = manager.getRepository(Student);
    const organizations = await manager.getRepository(Organization).find({
      where: { status: 'active' },
    });
    const seen = new Set<string>();

    for (const row of section.rows) {
      const name = row.name === undefined || row.name === null ? '' : String(row.name).trim();
      if (!name) {
        skipped += 1;
        issues.push('存在姓名为空的学生行');
        continue;
      }
      const phone = normalizePhone(row.phone);
      const studentNo =
        row.studentNo === undefined || row.studentNo === null
          ? ''
          : String(row.studentNo).trim();
      let organizationId = await this.resolveOrganizationId(row.organization, organizations);
      if (organizationId === null) {
        const hostOrganization = organizations.find((org) => org.isHost)?.id ?? null;
        if (hostOrganization === null) {
          skipped += 1;
          issues.push(`学生「${name}」的所属机构无法识别且未配置本机构`);
          continue;
        }
        issues.push(
          `学生「${name}」的机构「${String(row.organization ?? '').trim()}」无法识别，已按本机构导入`,
        );
        organizationId = hostOrganization;
      }
      const phoneKey = phone ? `phone:${phone}` : '';
      const noKey = studentNo ? `no:${studentNo}` : '';
      if ((phoneKey && seen.has(phoneKey)) || (noKey && seen.has(noKey))) {
        skipped += 1;
        issues.push(`学生「${name}」与同一批次中的其他学生手机号/学号重复`);
        continue;
      }
      const existing =
        (phone
          ? await studentRepo.findOne({ where: { phone } })
          : null) ||
        (studentNo
          ? await studentRepo.findOne({ where: { studentNo } })
          : null);
      if (existing) {
        skipped += 1;
        issues.push(`学生「${name}」已存在（按手机号/学号匹配），未重复创建`);
        continue;
      }
      if (phoneKey) seen.add(phoneKey);
      if (noKey) seen.add(noKey);
      await studentRepo.save(
        studentRepo.create({
          name,
          phone: phone ?? undefined,
          studentNo: studentNo || undefined,
          gender: row.gender === undefined || row.gender === null ? undefined : String(row.gender).trim().slice(0, 10),
          organizationId,
          status: 'active',
        }),
      );
      created += 1;
    }
    return { created, skipped, issues };
  }

  private async importRooms(
    section: AiReviewSection | undefined,
    manager: EntityManager,
  ): Promise<{ created: number; skipped: number; issues: string[] }> {
    let created = 0;
    let skipped = 0;
    const issues: string[] = [];
    if (!section || section.rows.length === 0) return { created, skipped, issues };
    const roomRepo = manager.getRepository(Room);
    const bedRepo = manager.getRepository(Bed);
    const seen = new Set<string>();

    for (const row of section.rows) {
      const roomNumber =
        row.roomNumber === undefined || row.roomNumber === null
          ? ''
          : String(row.roomNumber).trim();
      if (!roomNumber) {
        skipped += 1;
        issues.push('存在房间号为空的行');
        continue;
      }
      const parsed = RoomsService.parseRoomNumber(roomNumber);
      const capacity = this.normalizeCapacity(row.capacity, parsed.capacity ?? 4);
      if (capacity === null) {
        skipped += 1;
        issues.push(`宿舍「${roomNumber}」的容量无效`);
        continue;
      }
      if (seen.has(roomNumber)) {
        skipped += 1;
        issues.push(`宿舍「${roomNumber}」在同一批次中重复`);
        continue;
      }
      const existing = await roomRepo.findOne({ where: { roomNumber } });
      if (existing) {
        skipped += 1;
        issues.push(`宿舍「${roomNumber}」已存在，未重复创建`);
        continue;
      }
      seen.add(roomNumber);
      const room = await roomRepo.save(
        roomRepo.create({
          roomNumber,
          building:
            row.building === undefined || row.building === null
              ? parsed.building
              : String(row.building).trim().slice(0, 50),
          floor:
            row.floor === undefined || row.floor === null
              ? parsed.floor
              : (this.normalizeFloor(row.floor) ?? undefined),
          roomType:
            row.roomType === undefined || row.roomType === null
              ? parsed.roomType
              : String(row.roomType).trim().slice(0, 20),
          capacity,
          status: 'available',
        }),
      );
      const beds = Array.from({ length: capacity }, (_, index) =>
        bedRepo.create({ roomId: room.id, bedNumber: `${index + 1}号床` }),
      );
      if (beds.length > 0) await bedRepo.save(beds);
      created += 1;
    }
    return { created, skipped, issues };
  }

  private async importTransfers(
    section: AiReviewSection | undefined,
    manager: EntityManager,
  ): Promise<{ completed: number; skipped: number; issues: string[] }> {
    let completed = 0;
    let skipped = 0;
    const issues: string[] = [];
    if (!section || section.rows.length === 0) return { completed, skipped, issues };
    const studentRepo = manager.getRepository(Student);
    const occRepo = manager.getRepository(Occupancy);
    const roomRepo = manager.getRepository(Room);

    for (const row of section.rows) {
      const phone = normalizePhone(row.studentPhone ?? row.phone);
      const studentNo =
        row.studentNo === undefined || row.studentNo === null
          ? ''
          : String(row.studentNo).trim();
      const newRoomNumber =
        row.newRoom === undefined || row.newRoom === null
          ? ''
          : String(row.newRoom).trim();
      const transferDate = toDateString(row.transferDate ?? row.date);
      if (!newRoomNumber) {
        skipped += 1;
        issues.push('存在目标宿舍为空的行');
        continue;
      }
      if (!transferDate) {
        skipped += 1;
        issues.push(`换宿到「${newRoomNumber}」的日期格式无效（应为 YYYY-MM-DD）`);
        continue;
      }
      const student = studentNo
        ? await studentRepo.findOne({ where: { studentNo } })
        : phone
          ? await studentRepo.findOne({ where: { phone } })
          : null;
      if (!student) {
        skipped += 1;
        issues.push(`换宿到「${newRoomNumber}」的学生不存在（缺少手机号/学号）`);
        continue;
      }
      const active = await occRepo.findOne({
        where: { studentId: student.id, checkOutDate: IsNull() },
        order: { id: 'DESC' },
      });
      if (!active) {
        skipped += 1;
        issues.push(`学生「${student.name}」当前没有在住记录，无法换宿`);
        continue;
      }
      const oldRoom = await roomRepo.findOne({ where: { id: active.roomId } });
      const oldRoomNumber = oldRoom?.roomNumber ?? String(active.roomId);
      const expectedOldRoom =
        row.oldRoom === undefined || row.oldRoom === null
          ? ''
          : String(row.oldRoom).trim();
      if (expectedOldRoom && expectedOldRoom !== oldRoomNumber) {
        skipped += 1;
        issues.push(
          `学生「${student.name}」原宿舍为「${oldRoomNumber}」，与行内填写的「${expectedOldRoom}」不一致`,
        );
        continue;
      }
      const newRoom = await roomRepo.findOne({ where: { roomNumber: newRoomNumber } });
      if (!newRoom) {
        skipped += 1;
        issues.push(`学生「${student.name}」的目标宿舍「${newRoomNumber}」不存在`);
        continue;
      }
      if (newRoom.status === 'archived' || newRoom.status === 'maintenance') {
        skipped += 1;
        issues.push(`学生「${student.name}」的目标宿舍「${newRoomNumber}」当前不可入住`);
        continue;
      }
      if (newRoom.id === active.roomId) {
        skipped += 1;
        issues.push(`学生「${student.name}」的目标宿舍与当前宿舍相同`);
        continue;
      }
      if (transferDate < String(active.checkInDate)) {
        skipped += 1;
        issues.push(`学生「${student.name}」的换宿日期早于入住日期`);
        continue;
      }
      const activeCount = await occRepo.count({
        where: { roomId: newRoom.id, checkOutDate: IsNull() },
      });
      if (activeCount >= (newRoom.capacity ?? 0)) {
        skipped += 1;
        issues.push(`学生「${student.name}」的目标宿舍「${newRoomNumber}」已满`);
        continue;
      }

      active.checkOutDate = transferDate;
      active.billingEndDate = transferDate;
      active.checkOutReason = 'Excel 批量导入换宿';
      await occRepo.save(active);
      if (active.bedId) {
        await manager.getRepository(Bed).update(active.bedId, { status: 'available' });
      }
      if (active.lockerId) {
        await manager.getRepository(Locker).update(active.lockerId, { status: 'available' });
      }
      await roomRepo.update(active.roomId, { status: 'available' });

      const nextDay = this.nextDay(transferDate);
      await occRepo.save(
        occRepo.create({
          studentId: student.id,
          roomId: newRoom.id,
          checkInDate: transferDate,
          billingStartDate: nextDay,
          stayType: active.stayType || 'short',
          responsibleOrganizationId: active.responsibleOrganizationId ?? student.organizationId,
          notes: `从${oldRoomNumber}换入（Excel 批量导入）`,
          status: 'active',
        }),
      );
      if (activeCount + 1 >= (newRoom.capacity ?? 0)) {
        await roomRepo.update(newRoom.id, { status: 'full' });
      }
      completed += 1;
    }
    return { completed, skipped, issues };
  }

  /**
   * 入住记录导入：学生不存在时按本机构自动创建，宿舍不存在时自动创建，
   * 然后写入入住记录（与「入住管理」页面的批量导入语义一致）。
   */
  private async importCheckins(
    section: AiReviewSection | undefined,
    manager: EntityManager,
  ): Promise<{ completed: number; skipped: number; issues: string[] }> {
    let completed = 0;
    let skipped = 0;
    const issues: string[] = [];
    if (!section || section.rows.length === 0) return { completed, skipped, issues };
    const studentRepo = manager.getRepository(Student);
    const roomRepo = manager.getRepository(Room);
    const occRepo = manager.getRepository(Occupancy);
    const organizationRepo = manager.getRepository(Organization);
    const seen = new Set<string>();

    for (const row of section.rows) {
      const name = row.name === undefined || row.name === null ? '' : String(row.name).trim();
      const phone = normalizePhone(row.phone);
      const studentNo =
        row.studentNo === undefined || row.studentNo === null
          ? ''
          : String(row.studentNo).trim();
      const roomNumber =
        row.roomNumber === undefined || row.roomNumber === null
          ? ''
          : String(row.roomNumber).trim();
      if (!name || !roomNumber) {
        skipped += 1;
        issues.push('存在姓名或宿舍号为空的入住记录行');
        continue;
      }
      if (!phone && !studentNo) {
        skipped += 1;
        issues.push(`学生「${name}」缺少手机号/学号，无法关联或创建学生`);
        continue;
      }
      const dedupeKey = phone ? `phone:${phone}` : `no:${studentNo}`;
      if (seen.has(dedupeKey)) {
        skipped += 1;
        issues.push(`学生「${name}」与同一批次中的其他行手机号/学号重复`);
        continue;
      }
      seen.add(dedupeKey);

      let student = phone
        ? await studentRepo.findOne({ where: { phone } })
        : await studentRepo.findOne({ where: { studentNo } });
      if (!student) {
        const hostOrganization = await organizationRepo.findOne({
          where: { isHost: true, status: 'active' },
        });
        if (!hostOrganization) {
          skipped += 1;
          issues.push(`学生「${name}」不存在且未配置本机构，无法自动创建`);
          continue;
        }
        student = await studentRepo.save(
          studentRepo.create({
            name,
            phone: phone || undefined,
            studentNo: studentNo || undefined,
            gender:
              row.gender === undefined || row.gender === null
                ? undefined
                : String(row.gender).trim().slice(0, 10),
            organizationId: hostOrganization.id,
            status: 'active',
          }),
        );
      } else if (phone && !student.phone) {
        await studentRepo.update(student.id, { phone });
        student.phone = phone;
      }

      let room = await roomRepo.findOne({ where: { roomNumber } });
      if (!room) {
        const parsed = RoomsService.parseRoomNumber(roomNumber);
        room = await roomRepo.save(
          roomRepo.create({
            roomNumber,
            building:
              row.building === undefined || row.building === null
                ? parsed.building
                : String(row.building).trim().slice(0, 50),
            floor: parsed.floor || undefined,
            capacity: parsed.capacity ?? 4,
            roomType: parsed.roomType || undefined,
            status: 'available',
          }),
        );
      }
      if (room.status === 'archived' || room.status === 'maintenance') {
        skipped += 1;
        issues.push(`学生「${name}」的目标宿舍「${roomNumber}」当前不可入住`);
        continue;
      }

      const checkInDate = toDateString(row.checkInDate) ?? new Date().toISOString().slice(0, 10);
      const billingStartDate = toDateString(row.billingStartDate) ?? checkInDate;
      const checkOutDate = toDateString(row.checkOutDate);
      const isHistoricalRecord = Boolean(checkOutDate);

      const existing = await occRepo.findOne({
        where: { studentId: student.id, checkOutDate: IsNull() },
        order: { id: 'DESC' },
      });
      if (existing && !isHistoricalRecord) {
        skipped += 1;
        issues.push(`学生「${name}」当前已在住，未重复入住`);
        continue;
      }
      const activeCount = await occRepo.count({
        where: { roomId: room.id, checkOutDate: IsNull() },
      });
      if (!isHistoricalRecord && activeCount >= (room.capacity ?? 0)) {
        skipped += 1;
        issues.push(`学生「${name}」的目标宿舍「${roomNumber}」已满`);
        continue;
      }

      await occRepo.save(
        occRepo.create({
          studentId: student.id,
          roomId: room.id,
          checkInDate,
          billingStartDate,
          ...(checkOutDate
            ? { checkOutDate, checkOutReason: 'Excel 批量导入历史入住' }
            : {}),
          stayType:
            row.stayType === undefined || row.stayType === null
              ? 'short'
              : String(row.stayType).trim().slice(0, 10) || 'short',
          responsibleOrganizationId: student.organizationId,
          notes: `Excel 批量导入入住：${roomNumber}`,
          status: 'active',
        }),
      );
      if (!isHistoricalRecord && activeCount + 1 >= (room.capacity ?? 0)) {
        await roomRepo.update(room.id, { status: 'full' });
      }
      completed += 1;
    }
    return { completed, skipped, issues };
  }

  private async resolveOrganizationId(
    raw: unknown,
    organizations: Organization[],
  ): Promise<number | null> {
    if (typeof raw === 'number') {
      return organizations.some((org) => org.id === raw) ? raw : null;
    }
    const text = typeof raw === 'string' ? raw.trim() : '';
    if (!text) {
      return organizations.find((org) => org.isHost)?.id ?? null;
    }
    const match = organizations.find((org) => org.name === text || org.code === text);
    return match?.id ?? null;
  }

  private normalizeCapacity(raw: unknown, fallback: number): number | null {
    let value: number;
    if (typeof raw === 'number') {
      value = raw;
    } else if (typeof raw === 'string' && /^\d+$/.test(raw.trim())) {
      value = Number(raw.trim());
    } else {
      return fallback > 0 ? fallback : null;
    }
    if (!Number.isFinite(value) || value < 1 || value > MAX_CAPACITY) return null;
    return Math.floor(value);
  }

  private normalizeFloor(raw: unknown): number | null {
    if (typeof raw === 'number' && Number.isFinite(raw)) return Math.floor(raw);
    if (typeof raw === 'string' && /^\d+$/.test(raw.trim())) return Number(raw.trim());
    return null;
  }

  private nextDay(date: string): string {
    const parsed = new Date(`${date}T00:00:00+08:00`);
    parsed.setDate(parsed.getDate() + 1);
    const year = parsed.getFullYear();
    const month = String(parsed.getMonth() + 1).padStart(2, '0');
    const day = String(parsed.getDate()).padStart(2, '0');
    return `${year}-${month}-${day}`;
  }

  private validateSchema(rawArgs: unknown): ValidatedReviewSchema {
    if (!isPlainRecord(rawArgs)) throw new BadRequestException('导入预览参数必须是对象');
    assertKeys(rawArgs, SCHEMA_KEYS, '导入预览');
    const title = requireString(rawArgs.title, '预览标题', MAX_TITLE);
    const summary = requireString(rawArgs.summary, '预览说明', MAX_SUMMARY, true) || null;
    if (!Array.isArray(rawArgs.sections) || rawArgs.sections.length === 0) {
      throw new BadRequestException('导入预览至少需要一个分表');
    }
    if (rawArgs.sections.length > MAX_SECTIONS) {
      throw new BadRequestException(`分表数量不能超过 ${MAX_SECTIONS}`);
    }
    const seenKeys = new Set<string>();
    const sections = rawArgs.sections.map((item, index) =>
      this.validateSection(item, index, seenKeys),
    );
    return { title, summary, sections };
  }

  private validateSection(
    raw: unknown,
    index: number,
    seenKeys: Set<string>,
  ): AiReviewSection {
    if (!isPlainRecord(raw)) throw new BadRequestException(`第 ${index + 1} 个分表格式无效`);
    assertKeys(raw, SECTION_KEYS_ALLOWED, `第 ${index + 1} 个分表`);
    const key = requireString(raw.key, `第 ${index + 1} 个分表标识`, MAX_COLUMN_KEY);
    if (!SECTION_KEY_RE.test(key)) {
      throw new BadRequestException(
        `分表标识 ${key} 只能包含字母、数字、下划线（≤50）`,
      );
    }
    const type = normalizeSectionType(key, raw.type);
    if (seenKeys.has(key)) throw new BadRequestException(`分表标识重复: ${key}`);
    seenKeys.add(key);
    const title = requireString(raw.title, `分表「${key}」标题`, MAX_SECTION_TITLE);
    if (raw.kind !== 'table') throw new BadRequestException(`分表「${key}」的 kind 只能是 table`);
    const sheet =
      raw.sheet === undefined || raw.sheet === null
        ? undefined
        : requireString(raw.sheet, `分表「${key}」工作表`, MAX_SECTION_TITLE);
    if (!Array.isArray(raw.columns) || raw.columns.length === 0) {
      throw new BadRequestException(`分表「${key}」至少需要一个列`);
    }
    if (raw.columns.length > MAX_COLUMNS) {
      throw new BadRequestException(`分表「${key}」的列数不能超过 ${MAX_COLUMNS}`);
    }
    const seenColumns = new Set<string>();
    const aliases = SECTION_ALIASES[type] ?? {};
    const columns = raw.columns.map((column, columnIndex) => {
      if (!isPlainRecord(column)) {
        throw new BadRequestException(`分表「${key}」第 ${columnIndex + 1} 列格式无效`);
      }
      assertKeys(column, COLUMN_KEYS_ALLOWED, `分表「${key}」第 ${columnIndex + 1} 列`);
      const rawKey = requireString(column.key, `分表「${key}」列名`, MAX_COLUMN_KEY);
      const columnKey = aliases[rawKey] ?? rawKey;
      if (!COLUMN_KEY_RE.test(columnKey)) {
        throw new BadRequestException(`分表「${key}」列名 ${columnKey} 只能包含字母、数字、下划线`);
      }
      if (seenColumns.has(columnKey)) {
        throw new BadRequestException(`分表「${key}」列名重复: ${columnKey}`);
      }
      seenColumns.add(columnKey);
      const columnTitle = requireString(column.title, `分表「${key}」列「${columnKey}」标题`, MAX_COLUMN_TITLE);
      return { key: columnKey, title: columnTitle };
    });
    if (!Array.isArray(raw.rows) || raw.rows.length > MAX_ROWS) {
      throw new BadRequestException(`分表「${key}」的行数不能超过 ${MAX_ROWS}`);
    }
    const rows = raw.rows.map((row, rowIndex) =>
      this.validateRow(row, type, rowIndex, new Set(seenColumns), aliases),
    );
    let issues: string[] = [];
    if (raw.issues !== undefined) {
      if (!Array.isArray(raw.issues) || raw.issues.length > MAX_ISSUES) {
        throw new BadRequestException(`分表「${key}」的问题数不能超过 ${MAX_ISSUES}`);
      }
      issues = raw.issues.map((issue) =>
        requireString(issue, `分表「${key}」的问题`, MAX_ISSUE_LENGTH),
      );
    }
    return {
      key,
      type,
      title,
      kind: 'table',
      ...(sheet ? { sheet } : {}),
      columns,
      rows,
      issues,
    };
  }

  private validateRow(
    raw: unknown,
    sectionType: AiReviewSectionType,
    index: number,
    knownColumns: Set<string>,
    aliases: Record<string, string>,
  ): AiReviewRow {
    if (!isPlainRecord(raw)) {
      throw new BadRequestException(`分表「${sectionType}」第 ${index + 1} 行格式无效`);
    }
    const row: AiReviewRow = {};
    for (const [key, value] of Object.entries(raw)) {
      const canonicalKey = aliases[key] ?? key;
      if (!knownColumns.has(canonicalKey)) continue;
      if (value === null || typeof value === 'boolean') {
        row[canonicalKey] = value;
        continue;
      }
      if (typeof value === 'number') {
        if (!Number.isFinite(value)) {
          throw new BadRequestException(
            `分表「${sectionType}」第 ${index + 1} 行 ${key} 必须是有效数字`,
          );
        }
        row[canonicalKey] = value;
        continue;
      }
      if (typeof value === 'string') {
        if (value.length > MAX_CELL_LENGTH) {
          throw new BadRequestException(
            `分表「${sectionType}」第 ${index + 1} 行 ${key} 长度超过 ${MAX_CELL_LENGTH}`,
          );
        }
        row[canonicalKey] = value;
        continue;
      }
      throw new BadRequestException(
        `分表「${sectionType}」第 ${index + 1} 行 ${key} 类型不支持`,
      );
    }
    return row;
  }
}
