import { DataSource, IsNull, Repository } from 'typeorm';
import { Organization } from '../entities/organization.entity';
import { Room } from '../entities/room.entity';
import { Student } from '../entities/student.entity';
import { Occupancy } from '../entities/occupancy.entity';
import type { AiReviewSection } from './entities/ai-review.entity';
import { DATE_RE, MAX_ISSUES, normalizePhone, toDateString } from './ai-review.shared';

export function resolveOrganizationId(
  raw: unknown,
  organizations: Organization[],
): 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;
}

/**
 * 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.
 */
export async function enrichWithIssues(
  dataSource: DataSource,
  sections: AiReviewSection[],
): Promise<AiReviewSection[]> {
  try {
    const organizationRepo = dataSource.getRepository(Organization);
    const studentRepo = dataSource.getRepository(Student);
    const roomRepo = dataSource.getRepository(Room);
    const occupancyRepo = 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 enrichStudentIssues(section, issues, organizations, studentRepo);
      } else if (section.type === 'rooms') {
        await enrichRoomIssues(section, issues, roomRepo);
      } else if (section.type === 'transfers') {
        await enrichTransferIssues(
          section,
          issues,
          studentRepo,
          roomRepo,
          occupancyRepo,
          incomingRoomNumbers,
        );
      } else if (section.type === 'checkins') {
        await 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;
  }
}

async function 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 = 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}」已存在（按手机号/学号匹配），导入时将跳过`);
    }
  }
}

async function 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}」已存在，导入时将跳过`);
    }
  }
}

async function 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}」当前已在住，导入时将跳过（如为历史记录请填写退宿日期）`,
        );
      }
    }
  }
}

async function 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}」的目标宿舍与当前宿舍相同`);
    }
  }
}
