import { BadRequestException, NotFoundException } from '@nestjs/common';
import { DataSource } from 'typeorm';
import * as allEntities from '../entities';
import { Bed } from '../entities/bed.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 { AiConversation } from './entities/ai-conversation.entity';
import { AiMessage } from './entities/ai-message.entity';
import { AiReview } from './entities/ai-review.entity';
import { User } from '../entities/user.entity';
import { AiReviewService } from './ai-review.service';
import type { ExcelSheetRows } from './ai-excel-reader.service';

function createService(overrides: Record<string, unknown> = {}) {
  const reviews = {
    findOne: jest.fn(),
    save: jest.fn(async (value) => value),
    create: jest.fn((value) => value),
    ...overrides,
  };
  const dataSource = {
    getRepository: jest.fn(() => ({
      find: jest.fn().mockResolvedValue([]),
      findOne: jest.fn().mockResolvedValue(null),
    })),
  };
  const service = new AiReviewService(reviews as never, dataSource as never);
  return { service, reviews, dataSource };
}

const baseArgs = {
  userId: 7,
  conversationId: 3,
  assistantMessageId: 12,
};

const validSchema = {
  title: '新生入住批量导入',
  summary: '来自报名 Excel',
  sections: [
    {
      key: 'students',
      type: 'students',
      title: '学生',
      kind: 'table',
      columns: [
        { key: 'name', title: '姓名' },
        { key: 'phone', title: '手机号' },
      ],
      rows: [
        { name: '张三', phone: '13800138000' },
        { name: '李四', phone: '13900139000' },
      ],
      issues: [],
    },
  ],
};

describe('AiReviewService', () => {
  describe('createReview', () => {
    function bigSchema(sectionCount: number) {
      const columns = Array.from({ length: 30 }, (_, i) => ({
        key: `c${i + 1}`,
        title: `列${i + 1}`,
      }));
      const cell = '中'.repeat(200);
      const rows = Array.from({ length: 500 }, (_, i) =>
        Object.fromEntries(columns.map((column) => [column.key, cell])),
      );
      return {
        title: '大体积导入',
        summary: null,
        sections: Array.from({ length: sectionCount }, (_, i) => ({
          key: (['students', 'rooms', 'transfers'] as const)[i],
          title: `学生${i + 1}`,
          kind: 'table',
          columns,
          rows,
          issues: [],
        })),
      };
    }

    it('校验通过的 schema 落库并保留完整 sections', async () => {
      const { service, reviews } = createService();
      const review = await service.createReview(baseArgs, validSchema);
      expect(reviews.create).toHaveBeenCalledWith(
        expect.objectContaining({
          userId: 7,
          conversationId: 3,
          assistantMessageId: 12,
          title: '新生入住批量导入',
          status: 'pending',
        }),
      );
      expect(review.id).toBeTruthy();
      const sections = JSON.parse(review.sectionsJson) as unknown[];
      expect(sections).toHaveLength(1);
      expect((sections[0] as { rows: unknown[] }).rows).toHaveLength(2);
    });

    it('允许大体积合法预览（远超 256KB），不再因字节上限失败', async () => {
      const { service } = createService();
      const review = await service.createReview(baseArgs, bigSchema(1));
      expect(review.sectionsJson.length).toBeGreaterThan(256 * 1024);
    });

    it('超过 12MB 总上限的预览仍被拒绝', async () => {
      const { service } = createService();
      await expect(service.createReview(baseArgs, bigSchema(2))).rejects.toThrow('预览数据过大');
    });

    it.each([
      ['标题缺失', { sections: validSchema.sections }, '预览标题'],
      ['未知顶层字段', { ...validSchema, hack: 1 }, '未知属性'],
      ['分表为空', { ...validSchema, sections: [] }, '至少需要一个分表'],
      ['分表超过20个', {
        ...validSchema,
        sections: Array.from({ length: 21 }, (_, i) => ({
          ...validSchema.sections[0],
          key: `students_${i}`,
          title: `分表${i}`,
        })),
      }, '不能超过 20'],
      ['分表类型无法解析', {
        ...validSchema,
        sections: [{ ...validSchema.sections[0], key: 'hackers', type: undefined }],
      }, '无法解析业务类型'],
      ['显式非法 type 被拒绝', {
        ...validSchema,
        sections: [{ ...validSchema.sections[0], type: 'hackers' }],
      }, '分表业务类型不支持'],
      ['分表标识重复', {
        ...validSchema,
        sections: [validSchema.sections[0], validSchema.sections[0]],
      }, '分表标识重复'],
      ['kind 非 table', {
        ...validSchema,
        sections: [{ ...validSchema.sections[0], kind: 'chart' }],
      }, '只能是 table'],
      ['列缺失', {
        ...validSchema,
        sections: [{ ...validSchema.sections[0], columns: [] }],
      }, '至少需要一个列'],
      ['行数超限', {
        ...validSchema,
        sections: [
          {
            ...validSchema.sections[0],
            rows: Array.from({ length: 501 }, (_, i) => ({ name: `学生${i}` })),
          },
        ],
      }, '不能超过 500'],
      ['单元格类型非法', {
        ...validSchema,
        sections: [
          {
            ...validSchema.sections[0],
            rows: [{ name: '张三', phone: { hack: true } }],
          },
        ],
      }, '类型不支持'],
    ])('非法 schema 被拒绝：%s', async (_name, schema, messagePart) => {
      const { service } = createService();
      await expect(service.createReview(baseArgs, schema)).rejects.toBeInstanceOf(
        BadRequestException,
      );
      await expect(service.createReview(baseArgs, schema)).rejects.toThrow(messagePart);
    });

    it('行内未知列被剔除，不写入预览', async () => {
      const { service } = createService();
      const review = await service.createReview(baseArgs, {
        ...validSchema,
        sections: [
          {
            ...validSchema.sections[0],
            rows: [{ name: '张三', phone: '13800138000', __proto_hack: 'x', token: 'abc' }],
          },
        ],
      });
      const sections = JSON.parse(review.sectionsJson) as Array<{ rows: Array<Record<string, unknown>> }>;
      expect(sections[0].rows[0]).toEqual({ name: '张三', phone: '13800138000' });
    });

    it('同一业务类型允许多张 sheet，key 保持唯一', async () => {
      const { service } = createService();
      const review = await service.createReview(baseArgs, {
        title: '多入住 sheet',
        sections: Array.from({ length: 6 }, (_, i) => ({
          key: `checkins_${i + 1}`,
          type: 'checkins',
          title: `入住${i + 1}`,
          kind: 'table',
          sheet: `Sheet${i + 1}`,
          columns: [{ key: 'name', title: '姓名' }],
          rows: [{ name: `学生${i + 1}` }],
          issues: [],
        })),
      });
      const sections = service.parseSections(review.sectionsJson);
      expect(sections).toHaveLength(6);
      expect(sections.map((section) => section.type)).toEqual(
        Array.from({ length: 6 }, () => 'checkins'),
      );
      expect(new Set(sections.map((section) => section.key)).size).toBe(6);
      expect(sections[0].sheet).toBe('Sheet1');
    });

    it('旧格式 key 带类型前缀时自动解析 type', async () => {
      const { service } = createService();
      const review = await service.createReview(baseArgs, {
        ...validSchema,
        sections: [
          {
            key: 'checkins_girls_4',
            title: '四人间女',
            kind: 'table',
            columns: [{ key: 'name', title: '姓名' }],
            rows: [{ name: '张三' }],
            issues: [],
          },
        ],
      });
      const sections = service.parseSections(review.sectionsJson);
      expect(sections[0].type).toBe('checkins');
      expect(sections[0].key).toBe('checkins_girls_4');
    });

    it('模型常见列名别名归一化为规范键名', async () => {
      const { service } = createService();
      const review = await service.createReview(baseArgs, {
        title: '别名测试',
        sections: [
          {
            key: 'students',
            title: '学生',
            kind: 'table',
            columns: [{ key: 'org', title: '机构' }],
            rows: [{ org: '东校区' }],
            issues: [],
          },
          {
            key: 'rooms',
            title: '宿舍',
            kind: 'table',
            columns: [
              { key: 'roomNo', title: '房间号' },
              { key: 'capacity', title: '容量' },
            ],
            rows: [{ roomNo: '4-401', capacity: 4 }],
            issues: [],
          },
          {
            key: 'transfers',
            title: '换宿',
            kind: 'table',
            columns: [
              { key: 'studentNo', title: '学号' },
              { key: 'fromRoom', title: '原宿舍' },
              { key: 'toRoom', title: '目标宿舍' },
              { key: 'date', title: '换宿日期' },
            ],
            rows: [
              {
                studentNo: 'S001',
                fromRoom: '1-101',
                toRoom: '4-401',
                date: '2026-08-10',
              },
            ],
            issues: [],
          },
        ],
      });
      const sections = service.parseSections(review.sectionsJson);
      expect(sections[0].columns[0].key).toBe('organization');
      expect(sections[0].rows[0]).toEqual({ organization: '东校区' });
      expect(sections[1].columns[0].key).toBe('roomNumber');
      expect(sections[1].rows[0]).toEqual({ roomNumber: '4-401', capacity: 4 });
      expect(sections[2].rows[0]).toEqual({
        studentNo: 'S001',
        oldRoom: '1-101',
        newRoom: '4-401',
        transferDate: '2026-08-10',
      });
    });

    it('预览生成时按数据库校验机构、重复与换宿对象并追加 issues', async () => {
      const { service } = createService();
      const review = await service.createReview(baseArgs, {
        title: '预览校验',
        sections: [
          {
            key: 'students',
            title: '学生',
            kind: 'table',
            columns: [
              { key: 'name', title: '姓名' },
              { key: 'phone', title: '手机号' },
              { key: 'organization', title: '机构' },
            ],
            rows: [{ name: '小王', phone: '13800138000', organization: '不存在的机构' }],
            issues: [],
          },
          {
            key: 'rooms',
            title: '宿舍',
            kind: 'table',
            columns: [{ key: 'roomNumber', title: '房间号' }],
            rows: [{ roomNumber: '9-901' }],
            issues: [],
          },
          {
            key: 'transfers',
            title: '换宿',
            kind: 'table',
            columns: [
              { key: 'studentNo', title: '学号' },
              { key: 'newRoom', title: '目标宿舍' },
            ],
            rows: [{ studentNo: 'S001', newRoom: '9-901' }],
            issues: [],
          },
        ],
      });
      const sections = service.parseSections(review.sectionsJson);
      const studentIssues = sections.find((section) => section.key === 'students')?.issues ?? [];
      const transferIssues = sections.find((section) => section.key === 'transfers')?.issues ?? [];
      expect(studentIssues).toEqual(
        expect.arrayContaining([expect.stringContaining('所属机构无法识别')]),
      );
      expect(transferIssues).toEqual(
        expect.arrayContaining([expect.stringContaining('学生不存在')]),
      );
    });
  });

  describe('buildSectionsFromWorkbook', () => {
    const workbook: ExcelSheetRows[] = [
      {
        name: '学生名单',
        rows: [
          ['姓名', '手机号', '学号', '性别', '备注'],
          ['张三', '13800138000', 'S001', '男', ''],
          ['李四', '13900139000', 'S002', '女', '新生'],
          ['', '', '', '', ''],
        ],
      },
      {
        name: '宿舍安排',
        rows: [
          ['宿舍号', '容量', '楼栋', '楼层'],
          ['3-301', '4', '3号楼', '3'],
          ['3-302', '6', '3号楼', '3'],
        ],
      },
    ];

    it('按表头自动映射并保留文件原始行数据，未识别列进入 issues', async () => {
      const { service } = createService();
      const sections = await service.buildSectionsFromWorkbook(workbook, {
        sections: [{ key: 'students', title: '学生', sheet: '学生名单' }],
      });
      expect(sections).toHaveLength(1);
      expect(sections[0].rows).toEqual([
        { name: '张三', phone: '13800138000', studentNo: 'S001', gender: '男' },
        { name: '李四', phone: '13900139000', studentNo: 'S002', gender: '女' },
      ]);
      expect(sections[0].columns.map((column) => column.key)).toEqual([
        'name',
        'phone',
        'studentNo',
        'gender',
      ]);
      expect(sections[0].issues.join('')).toContain('备注');
    });

    it('支持显式 sourceHeader 列映射与 headerRow', async () => {
      const { service } = createService();
      const custom: ExcelSheetRows[] = [
        {
          name: 'Sheet1',
          rows: [
            ['忽略行'],
            ['学生姓名', '联系方式'],
            ['王五', '13700137000'],
          ],
        },
      ];
      const sections = await service.buildSectionsFromWorkbook(custom, {
        sections: [
          {
            key: 'students',
            title: '学生',
            sheet: 'Sheet1',
            headerRow: 2,
            columns: [
              { key: 'name', title: '姓名', sourceHeader: '学生姓名' },
              { key: 'phone', title: '手机号', sourceHeader: '联系方式' },
            ],
          },
        ],
      });
      expect(sections[0].rows).toEqual([{ name: '王五', phone: '13700137000' }]);
    });

    it('入住记录表头自动映射为规范列名', async () => {
      const { service } = createService();
      const custom: ExcelSheetRows[] = [
        {
          name: '入住名单',
          rows: [
            ['宿舍号', '姓名', '手机号', '入住日期'],
            ['5-501', '於嘉丽', '13611112222', '2026-08-01'],
            ['5-502', '刘禹含', '13611113333', '2026/08/02'],
          ],
        },
      ];
      const sections = await service.buildSectionsFromWorkbook(custom, {
        sections: [{ key: 'checkins', title: '入住记录', sheet: '入住名单' }],
      });
      expect(sections[0].rows).toEqual([
        { name: '於嘉丽', phone: '13611112222', roomNumber: '5-501', checkInDate: '2026-08-01' },
        { name: '刘禹含', phone: '13611113333', roomNumber: '5-502', checkInDate: '2026/08/02' },
      ]);
    });

    it('英文表头映射为 camelCase 规范键（rooms/transfers/checkins）', async () => {
      const { service } = createService();
      const custom: ExcelSheetRows[] = [
        {
          name: 'Rooms',
          rows: [
            ['RoomNumber', 'RoomType'],
            ['3-301', '四人间'],
          ],
        },
        {
          name: 'Transfers',
          rows: [
            ['StudentNo', 'StudentPhone', 'OldRoom', 'NewRoom', 'TransferDate'],
            ['S001', '13800138000', '3-301', '3-302', '2026-08-05'],
          ],
        },
        {
          name: 'Checkins',
          rows: [
            ['StudentNo', 'RoomNumber', 'CheckInDate'],
            ['S001', '3-301', '2026-08-01'],
          ],
        },
      ];
      const sections = await service.buildSectionsFromWorkbook(custom, {
        sections: [
          { key: 'rooms', title: '宿舍', sheet: 'Rooms' },
          { key: 'transfers', title: '换宿', sheet: 'Transfers' },
          { key: 'checkins', title: '入住', sheet: 'Checkins' },
        ],
      });
      const byKey = Object.fromEntries(sections.map((section) => [section.key, section]));
      expect(byKey.rooms.rows).toEqual([{ roomNumber: '3-301', roomType: '四人间' }]);
      expect(byKey.transfers.rows).toEqual([
        {
          studentNo: 'S001',
          studentPhone: '13800138000',
          oldRoom: '3-301',
          newRoom: '3-302',
          transferDate: '2026-08-05',
        },
      ]);
      expect(byKey.checkins.rows).toEqual([
        { studentNo: 'S001', roomNumber: '3-301', checkInDate: '2026-08-01' },
      ]);
    });

    it('工作表不存在时抛出明确错误', async () => {
      const { service } = createService();
      await expect(
        service.buildSectionsFromWorkbook(workbook, {
          sections: [{ key: 'rooms', title: '宿舍', sheet: '不存在的表' }],
        }),
      ).rejects.toThrow('找不到工作表');
    });

    it('分表标识重复或非法时拒绝', async () => {
      const { service } = createService();
      await expect(
        service.buildSectionsFromWorkbook(workbook, {
          sections: [
            { key: 'students', title: '学生' },
            { key: 'students', title: '学生2' },
          ],
        }),
      ).rejects.toThrow('分表标识重复');
      await expect(
        service.buildSectionsFromWorkbook(workbook, {
          sections: [{ key: 'hackers', title: '入侵' }],
        }),
      ).rejects.toThrow('无法解析业务类型');
    });

    it('同一类型多张 sheet 合并生成，并保留各自 key/sheet', async () => {
      const { service } = createService();
      const multiSheet: ExcelSheetRows[] = [
        {
          name: '四人间女',
          rows: [
            ['姓名', '手机号', '宿舍号', '入住日期'],
            ['张三', '13800138000', '4-401', '2026-08-01'],
          ],
        },
        {
          name: '四人间男',
          rows: [
            ['姓名', '手机号', '宿舍号', '入住日期'],
            ['李四', '13900139000', '4-402', '2026-08-01'],
          ],
        },
      ];
      const sections = await service.buildSectionsFromWorkbook(multiSheet, {
        sections: [
          {
            key: 'checkins_girls_4',
            type: 'checkins',
            title: '四人间女',
            sheet: '四人间女',
          },
          {
            key: 'checkins_boys_4',
            type: 'checkins',
            title: '四人间男',
            sheet: '四人间男',
          },
        ],
      });
      expect(sections).toHaveLength(2);
      expect(sections.map((section) => [section.key, section.type, section.sheet])).toEqual([
        ['checkins_girls_4', 'checkins', '四人间女'],
        ['checkins_boys_4', 'checkins', '四人间男'],
      ]);
    });
  });

  describe('findOwnedPending', () => {
    it('只返回本人 pending 预览', async () => {
      const review = { id: 'review-1', userId: 7, status: 'pending' };
      const { service, reviews } = createService({ findOne: jest.fn().mockResolvedValue(review) });
      await expect(service.findOwnedPending('review-1', 7)).resolves.toBe(review);
      expect(reviews.findOne).toHaveBeenCalledWith({
        where: { id: 'review-1', userId: 7, status: 'pending' },
      });
    });

    it('已提交或不存在时抛 NotFound', async () => {
      const { service } = createService({ findOne: jest.fn().mockResolvedValue(null) });
      await expect(service.findOwnedPending('review-1', 7)).rejects.toBeInstanceOf(
        NotFoundException,
      );
    });
  });

  describe('findPendingByAssistantMessage', () => {
    it('按 assistant 消息返回最新的 pending 预览', async () => {
      const review = { id: 'review-1', assistantMessageId: 12, status: 'pending' };
      const { service, reviews } = createService({ findOne: jest.fn().mockResolvedValue(review) });
      await expect(service.findPendingByAssistantMessage(12)).resolves.toBe(review);
      expect(reviews.findOne).toHaveBeenCalledWith({
        where: { assistantMessageId: 12, status: 'pending' },
        order: { createdAt: 'DESC' },
      });
    });

    it('没有待确认预览时返回 null', async () => {
      const { service } = createService({ findOne: jest.fn().mockResolvedValue(null) });
      await expect(service.findPendingByAssistantMessage(12)).resolves.toBeNull();
    });
  });

  describe('serialize', () => {
    it('回传前端所需结构', () => {
      const { service } = createService();
      const serialized = service.serialize({
        id: 'review-1',
        title: '批量导入',
        summary: '说明',
        sectionsJson: JSON.stringify(validSchema.sections),
        status: 'submitted',
        resultSummary: '{}',
      } as never);
      expect(serialized).toEqual({
        id: 'review-1',
        title: '批量导入',
        summary: '说明',
        sections: validSchema.sections.map((section) => ({
          ...section,
          status: 'pending',
          resultSummary: null,
          submittedAt: null,
        })),
        status: 'submitted',
        resultSummary: '{}',
      });
    });
  });

});

describe('AiReviewService.submit (real sqlite transaction)', () => {
  let dataSource: DataSource;
  let service: AiReviewService;
  let hostOrg: Organization;
  let namedOrg: Organization;
  let assistantMessageId: number;

  beforeAll(async () => {
    dataSource = new DataSource({
      type: 'better-sqlite3',
      database: ':memory:',
      entities: Object.values(allEntities).filter(
        (value): value is Function => typeof value === 'function',
      ),
      synchronize: true,
    });
    await dataSource.initialize();
    const orgRepo = dataSource.getRepository(Organization);
    hostOrg = await orgRepo.save(
      orgRepo.create({ publicId: 'host', code: 'HOST', name: '恭学总校', isHost: true }),
    );
    namedOrg = await orgRepo.save(
      orgRepo.create({ publicId: 'org-a', code: 'ORG_A', name: '东校区' }),
    );
    const userRepo = dataSource.getRepository(User);
    const user = await userRepo.save(
      userRepo.create({ username: 'review-tester', passwordHash: 'x' }),
    );
    const conversationRepo = dataSource.getRepository(AiConversation);
    const conversation = await conversationRepo.save(
      conversationRepo.create({ userId: user.id, title: '测试会话' }),
    );
    const messageRepo = dataSource.getRepository(AiMessage);
    const assistant = await messageRepo.save(
      messageRepo.create({
        conversationId: conversation.id,
        role: 'assistant',
        content: '',
        status: 'completed',
      }),
    );
    assistantMessageId = assistant.id;
    const reviewRepo = dataSource.getRepository(AiReview);
    service = new AiReviewService(reviewRepo, dataSource);
  });

  afterAll(async () => {
    await dataSource.destroy();
  });

  it('按 学生→宿舍→换宿 顺序事务入库，并收集逐行问题', async () => {
    const studentRepo = dataSource.getRepository(Student);
    const roomRepo = dataSource.getRepository(Room);
    const bedRepo = dataSource.getRepository(Bed);
    const occRepo = dataSource.getRepository(Occupancy);

    const existing = await studentRepo.save(
      studentRepo.create({
        name: '老王',
        phone: '13800138000',
        studentNo: 'S001',
        organizationId: hostOrg.id,
      }),
    );
    const oldRoom = await roomRepo.save(
      roomRepo.create({ roomNumber: '1-101', capacity: 4, status: 'available' }),
    );
    await bedRepo.save(
      Array.from({ length: 4 }, (_, index) =>
        bedRepo.create({ roomId: oldRoom.id, bedNumber: `${index + 1}号床` }),
      ),
    );
    await occRepo.save(
      occRepo.create({
        studentId: existing.id,
        roomId: oldRoom.id,
        checkInDate: '2026-01-05',
        billingStartDate: '2026-01-05',
        stayType: 'short',
        responsibleOrganizationId: hostOrg.id,
      }),
    );

    const review = await service.createReview(
      { ...baseArgs, assistantMessageId },
      {
      title: '开学导入',
      summary: 'Excel 导入',
      sections: [
        {
          key: 'students',
          title: '学生',
          kind: 'table',
          columns: [
            { key: 'name', title: '姓名' },
            { key: 'phone', title: '手机号' },
            { key: 'organization', title: '机构' },
          ],
          rows: [
            { name: '张三', phone: '13900139000', organization: '东校区' },
            { name: '老王', phone: '13800138000', organization: '恭学总校' },
            { name: '李四', phone: '13700137000', organization: '不存在的机构' },
          ],
          issues: [],
        },
        {
          key: 'rooms',
          title: '宿舍',
          kind: 'table',
          columns: [{ key: 'roomNumber', title: '房间号' }],
          rows: [{ roomNumber: '3-301' }, { roomNumber: '1-101' }],
          issues: [],
        },
        {
          key: 'transfers',
          title: '换宿',
          kind: 'table',
          columns: [
            { key: 'studentNo', title: '学号' },
            { key: 'oldRoom', title: '原宿舍' },
            { key: 'newRoom', title: '目标宿舍' },
            { key: 'transferDate', title: '换宿日期' },
          ],
          rows: [
            { studentNo: 'S001', oldRoom: '1-101', newRoom: '3-301', transferDate: '2026-03-01' },
          ],
          issues: [],
        },
      ],
      },
    );

    const { review: submittedReview, result } = await service.submitAll(review.id, 7);
    expect(result.students.created).toBe(2);
    expect(result.students.skipped).toBe(1);
    expect(result.rooms.created).toBe(1);
    expect(result.rooms.skipped).toBe(1);
    expect(result.transfers.completed).toBe(1);
    expect(result.transfers.skipped).toBe(0);

    const createdStudent = await studentRepo.findOne({ where: { phone: '13900139000' } });
    expect(createdStudent?.name).toBe('张三');
    expect(createdStudent?.organizationId).toBe(namedOrg.id);
    const hostFallbackStudent = await studentRepo.findOne({ where: { phone: '13700137000' } });
    expect(hostFallbackStudent?.organizationId).toBe(hostOrg.id);

    const newRoom = await roomRepo.findOne({ where: { roomNumber: '3-301' } });
    expect(newRoom?.capacity).toBe(4);
    expect(await bedRepo.count({ where: { roomId: newRoom!.id } })).toBe(4);

    const oldOcc = await occRepo.findOne({
      where: { studentId: existing.id, roomId: oldRoom.id },
    });
    expect(oldOcc?.checkOutDate).toBe('2026-03-01');
    const newOcc = await occRepo.findOne({
      where: { studentId: existing.id, roomId: newRoom!.id, checkOutDate: null },
    });
    expect(newOcc?.checkInDate).toBe('2026-03-01');
    expect(newOcc?.billingStartDate).toBe('2026-03-02');

    expect(submittedReview.status).toBe('submitted');
    expect(submittedReview.submittedAt).toBeInstanceOf(Date);
    const savedSections = service.parseSections(submittedReview.sectionsJson);
    const studentSection = savedSections.find((section) => section.key === 'students');
    expect(studentSection?.issues).toEqual(
      expect.arrayContaining(['学生「老王」已存在（按手机号/学号匹配），未重复创建']),
    );
    expect(submittedReview.resultSummary).toContain('成功导入学生 2 人');
  });

  it('入住记录分表：学生和宿舍不存在时自动创建后写入住记录', async () => {
    const studentRepo = dataSource.getRepository(Student);
    const roomRepo = dataSource.getRepository(Room);
    const occRepo = dataSource.getRepository(Occupancy);

    const review = await service.createReview(
      { ...baseArgs, assistantMessageId },
      {
        title: '入住导入',
        summary: '宿舍入住记录',
        sections: [
          {
            key: 'checkins',
            title: '入住记录',
            kind: 'table',
            columns: [
              { key: 'name', title: '姓名' },
              { key: 'phone', title: '手机号' },
              { key: 'roomNumber', title: '宿舍号' },
              { key: 'checkInDate', title: '入住日期' },
            ],
            rows: [
              { name: '於嘉丽', phone: '13611112222', roomNumber: '5-501', checkInDate: '2026-08-01' },
              { name: '重复学生', phone: '13611112222', roomNumber: '5-502', checkInDate: '2026-08-01' },
            ],
            issues: [],
          },
        ],
      },
    );

    const { result } = await service.submitAll(review.id, 7);
    expect(result.checkins.completed).toBe(1);
    expect(result.checkins.skipped).toBe(1);

    const created = await studentRepo.findOne({ where: { phone: '13611112222' } });
    expect(created?.name).toBe('於嘉丽');
    expect(created?.organizationId).toBe(hostOrg.id);
    const room = await roomRepo.findOne({ where: { roomNumber: '5-501' } });
    expect(room?.capacity).toBe(4);
    const occupancy = await occRepo.findOne({ where: { studentId: created!.id } });
    expect(occupancy?.checkInDate).toBe('2026-08-01');
    expect(occupancy?.roomId).toBe(room!.id);
  });

  it('分步确认：依赖未满足拒绝，重复确认 409，全部完成后整卡提交', async () => {
    const review = await service.createReview(
      { ...baseArgs, assistantMessageId },
      {
        title: '分步导入',
        sections: [
          {
            key: 'students',
            title: '学生',
            kind: 'table',
            columns: [
              { key: 'name', title: '姓名' },
              { key: 'phone', title: '手机号' },
            ],
            rows: [{ name: '分步学生', phone: '13511112222' }],
            issues: [],
          },
          {
            key: 'rooms',
            title: '宿舍',
            kind: 'table',
            columns: [{ key: 'roomNumber', title: '房间号' }],
            rows: [{ roomNumber: '9-901' }],
            issues: [],
          },
          {
            key: 'transfers',
            title: '换宿',
            kind: 'table',
            columns: [{ key: 'studentNo', title: '学号' }],
            rows: [{ studentNo: 'NOPE' }],
            issues: [],
          },
        ],
      },
    );

    await expect(service.submitSection(review.id, 7, 'transfers')).rejects.toMatchObject({
      message: expect.stringContaining('请先确认第 1 步'),
    });

    const studentsStep = await service.submitSection(review.id, 7, 'students');
    expect(studentsStep.result).toMatchObject({ created: 1, skipped: 0 });
    expect(
      service.parseSections(studentsStep.review.sectionsJson).find(
        (section) => section.key === 'students',
      )?.status,
    ).toBe('submitted');

    await expect(service.submitSection(review.id, 7, 'students')).rejects.toMatchObject({
      message: expect.stringContaining('已确认导入'),
    });

    await service.submitSection(review.id, 7, 'rooms');
    const transferStep = await service.submitSection(review.id, 7, 'transfers');
    expect(service.parseSections(transferStep.review.sectionsJson).map((s) => s.status)).toEqual([
      'submitted',
      'submitted',
      'submitted',
    ]);
    expect(transferStep.review.status).toBe('submitted');
    expect(transferStep.review.submittedAt).toBeInstanceOf(Date);
  });

  it('依赖按类型整组判断：同类型全部 sheet 提交后才允许换宿', async () => {
    const review = await service.createReview(
      { ...baseArgs, assistantMessageId },
      {
        title: '多 sheet 依赖',
        sections: [
          {
            key: 'students_a',
            title: '学生 A',
            kind: 'table',
            columns: [
              { key: 'name', title: '姓名' },
              { key: 'phone', title: '手机号' },
            ],
            rows: [{ name: '甲', phone: '13511112222' }],
            issues: [],
          },
          {
            key: 'students_b',
            title: '学生 B',
            kind: 'table',
            columns: [
              { key: 'name', title: '姓名' },
              { key: 'phone', title: '手机号' },
            ],
            rows: [{ name: '乙', phone: '13511113333' }],
            issues: [],
          },
          {
            key: 'rooms_9',
            title: '9 号楼宿舍',
            kind: 'table',
            columns: [{ key: 'roomNumber', title: '房间号' }],
            rows: [{ roomNumber: '9-901' }],
            issues: [],
          },
          {
            key: 'checkins_active',
            type: 'checkins',
            title: '在住记录',
            kind: 'table',
            columns: [
              { key: 'name', title: '姓名' },
              { key: 'phone', title: '手机号' },
              { key: 'roomNumber', title: '宿舍号' },
              { key: 'checkInDate', title: '入住日期' },
            ],
            rows: [
              {
                name: '丙',
                phone: '13511115555',
                roomNumber: '9-903',
                checkInDate: '2026-08-01',
              },
            ],
            issues: [],
          },
          {
            key: 'transfers_9',
            title: '换宿',
            kind: 'table',
            columns: [
              { key: 'studentPhone', title: '手机号' },
              { key: 'newRoom', title: '目标宿舍' },
              { key: 'transferDate', title: '换宿日期' },
            ],
            rows: [{ studentPhone: '13511115555', newRoom: '9-901', transferDate: '2026-08-10' }],
            issues: [],
          },
        ],
      },
    );

    await expect(service.submitSection(review.id, 7, 'transfers_9')).rejects.toMatchObject({
      message: expect.stringContaining('请先确认第 1 步'),
    });

    await service.submitSection(review.id, 7, 'students_a');
    await expect(service.submitSection(review.id, 7, 'transfers_9')).rejects.toMatchObject({
      message: expect.stringContaining('请先确认第 2 步'),
    });

    await service.submitSection(review.id, 7, 'students_b');
    await service.submitSection(review.id, 7, 'rooms_9');
    await service.submitSection(review.id, 7, 'checkins_active');
    const transferStep = await service.submitSection(review.id, 7, 'transfers_9');
    expect(transferStep.result).toMatchObject({ completed: 1, skipped: 0 });
    const statuses = service
      .parseSections(transferStep.review.sectionsJson)
      .map((section) => section.status);
    expect(statuses).toEqual([
      'submitted',
      'submitted',
      'submitted',
      'submitted',
      'submitted',
    ]);
  });

  it('组确认按 sheet 逐张导入，成功后整组状态已导入', async () => {
    const review = await service.createReview(
      { ...baseArgs, assistantMessageId },
      {
        title: '整组入住确认',
        sections: Array.from({ length: 2 }, (_, i) => ({
          key: `checkins_group_${i + 1}`,
          type: 'checkins',
          title: `入住表${i + 1}`,
          kind: 'table',
          columns: [
            { key: 'name', title: '姓名' },
            { key: 'phone', title: '手机号' },
            { key: 'roomNumber', title: '宿舍号' },
            { key: 'checkInDate', title: '入住日期' },
          ],
          rows: [
            {
              name: `入住学生${i + 1}`,
              phone: `1360000000${i + 1}`,
              roomNumber: `5-50${i + 1}`,
              checkInDate: '2026-08-01',
            },
          ],
          issues: [],
        })),
      },
    );

    const { review: grouped } = await service.submitGroup(review.id, 7, 'checkins');
    const sections = service.parseSections(grouped.sectionsJson);
    expect(sections.map((section) => section.status)).toEqual(['submitted', 'submitted']);
    expect(grouped.status).toBe('submitted');
    expect(
      await dataSource.getRepository(Student).count({
        where: { phone: '13600000001' },
      }),
    ).toBe(1);
    expect(
      await dataSource.getRepository(Student).count({
        where: { phone: '13600000002' },
      }),
    ).toBe(1);
    expect(await dataSource.getRepository(Room).count({ where: { roomNumber: '5-501' } })).toBe(1);
    expect(await dataSource.getRepository(Room).count({ where: { roomNumber: '5-502' } })).toBe(1);
  });

  it('全部确认时按类型合并多张 sheet 的统计数量', async () => {
    const review = await service.createReview(
      { ...baseArgs, assistantMessageId },
      {
        title: '多 sheet 聚合',
        sections: Array.from({ length: 2 }, (_, i) => ({
          key: `students_batch_${i + 1}`,
          type: 'students',
          title: `学生表${i + 1}`,
          kind: 'table',
          columns: [
            { key: 'name', title: '姓名' },
            { key: 'phone', title: '手机号' },
          ],
          rows: [
            {
              name: `批量学生${i + 1}`,
              phone: `1370000000${i + 1}`,
            },
          ],
          issues: [],
        })),
      },
    );

    const { result } = await service.submitAll(review.id, 7);
    expect(result.students.created).toBe(2);
    expect(result.students.skipped).toBe(0);
    expect(result.message).toContain('成功导入学生 2 人');
  });

  it('组确认依赖未满足时返回 409，不导入任何 sheet', async () => {
    const review = await service.createReview(
      { ...baseArgs, assistantMessageId },
      {
        title: '组依赖校验',
        sections: [
          {
            key: 'students_a',
            title: '学生 A',
            kind: 'table',
            columns: [{ key: 'name', title: '姓名' }],
            rows: [{ name: '甲' }],
            issues: [],
          },
          {
            key: 'transfers_a',
            title: '换宿 A',
            kind: 'table',
            columns: [{ key: 'studentPhone', title: '手机号' }],
            rows: [{ studentPhone: '13511114444' }],
            issues: [],
          },
        ],
      },
    );
    await expect(service.submitGroup(review.id, 7, 'transfers')).rejects.toMatchObject({
      message: expect.stringContaining('请先确认第 1 步'),
    });
    const sections = service.parseSections((await service.findOwned(review.id, 7)).sectionsJson);
    expect(sections.map((section) => section.status)).toEqual(['pending', 'pending']);
  });

  it('单步确认部分成功时持久化 resultSummary 与问题', async () => {
    const review = await service.createReview(
      { ...baseArgs, assistantMessageId },
      {
        title: '部分成功',
        sections: [
          {
            key: 'students',
            title: '学生',
            kind: 'table',
            columns: [
              { key: 'name', title: '姓名' },
              { key: 'phone', title: '手机号' },
            ],
            rows: [
              { name: '新学生', phone: '13522223333' },
              { name: '重复学生', phone: '13522223333' },
            ],
            issues: [],
          },
        ],
      },
    );

    const step = await service.submitSection(review.id, 7, 'students');
    expect(step.result).toMatchObject({ created: 1, skipped: 1 });
    const saved = await service.findOwned(review.id, 7);
    const section = service.parseSections(saved.sectionsJson)[0];
    expect(section.status).toBe('submitted');
    expect(section.resultSummary).toContain('成功导入学生 1 人，跳过 1 条');
    expect(section.issues).toEqual(
      expect.arrayContaining([expect.stringContaining('同一批次中的其他学生')]),
    );
    expect(saved.status).toBe('submitted');
  });

  it('全部确认按固定依赖顺序提交，不受 sections 原始顺序影响', async () => {
    const review = await service.createReview(
      { ...baseArgs, assistantMessageId },
      {
        title: '乱序导入',
        sections: [
          {
            key: 'transfers',
            title: '换宿',
            kind: 'table',
            columns: [{ key: 'studentNo', title: '学号' }],
            rows: [{ studentNo: 'NOPE' }],
            issues: [],
          },
          {
            key: 'students',
            title: '学生',
            kind: 'table',
            columns: [
              { key: 'name', title: '姓名' },
              { key: 'phone', title: '手机号' },
            ],
            rows: [{ name: '乱序学生', phone: '13544445555' }],
            issues: [],
          },
          {
            key: 'rooms',
            title: '宿舍',
            kind: 'table',
            columns: [{ key: 'roomNumber', title: '房间号' }],
            rows: [{ roomNumber: '9-902' }],
            issues: [],
          },
        ],
      },
    );

    const { review: completed } = await service.submitAll(review.id, 7);
    expect(completed.status).toBe('submitted');
    expect(service.parseSections(completed.sectionsJson).map((section) => section.status)).toEqual([
      'submitted',
      'submitted',
      'submitted',
    ]);
  });

  it('旧数据缺少 section status 字段时默认 pending 并可继续确认', async () => {
    const review = await service.createReview(
      { ...baseArgs, assistantMessageId },
      {
        title: '旧数据兼容',
        sections: [
          {
            key: 'students',
            title: '学生',
            kind: 'table',
            columns: [
              { key: 'name', title: '姓名' },
              { key: 'phone', title: '手机号' },
            ],
            rows: [{ name: '旧数据学生', phone: '13533334444' }],
            issues: [],
          },
        ],
      },
    );
    const legacySections = service
      .parseSections(review.sectionsJson)
      .map(
        ({
          status: _status,
          resultSummary: _result,
          submittedAt: _at,
          type: _type,
          ...rest
        }) => rest,
      );
    review.sectionsJson = JSON.stringify(legacySections);
    await dataSource.getRepository(AiReview).save(review);

    const step = await service.submitSection(review.id, 7, 'students');
    expect(step.result).toMatchObject({ created: 1, skipped: 0 });
    const reloaded = service.parseSections(
      (await service.findOwned(review.id, 7)).sectionsJson,
    )[0];
    expect(reloaded.status).toBe('submitted');
  });

  it('同会话生成新预览后旧预览过期，且所有确认入口拒绝', async () => {
    const conversationId = 9001;
    const first = await service.createReview(
      { ...baseArgs, conversationId, assistantMessageId },
      {
        title: '旧预览',
        sections: [
          {
            key: 'students',
            title: '学生',
            kind: 'table',
            columns: [
              { key: 'name', title: '姓名' },
              { key: 'phone', title: '手机号' },
            ],
            rows: [{ name: '旧学生', phone: '13711110001' }],
            issues: [],
          },
        ],
      },
    );
    const second = await service.createReview(
      { ...baseArgs, conversationId, assistantMessageId },
      {
        title: '新预览',
        sections: [
          {
            key: 'students',
            title: '学生',
            kind: 'table',
            columns: [
              { key: 'name', title: '姓名' },
              { key: 'phone', title: '手机号' },
            ],
            rows: [{ name: '新学生', phone: '13711110002' }],
            issues: [],
          },
        ],
      },
    );

    const expired = await service.expirePreviousReviews(
      7,
      conversationId,
      second.id,
    );
    expect(expired.map((review) => review.id)).toEqual([first.id]);
    expect((await service.findOwned(first.id, 7)).status).toBe('expired');
    expect((await service.findOwned(second.id, 7)).status).toBe('pending');

    await expect(service.findOwnedPending(first.id, 7)).rejects.toThrow('已失效');
    await expect(service.submitSection(first.id, 7, 'students')).rejects.toMatchObject({
      message: expect.stringContaining('已失效'),
    });
    await expect(service.submitGroup(first.id, 7, 'students')).rejects.toMatchObject({
      message: expect.stringContaining('已失效'),
    });
    await expect(service.submitAll(first.id, 7)).rejects.toMatchObject({
      message: expect.stringContaining('已失效'),
    });
  });

  it('不同会话的旧预览不会被其他会话的新预览过期', async () => {
    const first = await service.createReview(
      { ...baseArgs, assistantMessageId },
      {
        title: 'A 会话预览',
        sections: [
          {
            key: 'students',
            title: '学生',
            kind: 'table',
            columns: [
              { key: 'name', title: '姓名' },
              { key: 'phone', title: '手机号' },
            ],
            rows: [{ name: '跨会话学生', phone: '13711110003' }],
            issues: [],
          },
        ],
      },
    );

    await service.expirePreviousReviews(7, 999, 'other-review');
    expect((await service.findOwned(first.id, 7)).status).toBe('pending');
  });

});
