import { Injectable, NotFoundException, BadRequestException } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { DataSource, In, Repository } from 'typeorm';
import { RoomExpense } from '../entities/room-expense.entity';
import { PersonalExpense } from '../entities/personal-expense.entity';
import { Room } from '../entities/room.entity';
import { Student } from '../entities/student.entity';
import {
  CreateRoomExpenseDto,
  CreatePersonalExpenseDto,
  BatchRoomExpenseDto,
  CreateStudentUtilityBillDto,
} from './dto/expense.dto';
import { RoomsService } from '../rooms/rooms.service';
import { BillsService } from '../bills/bills.service';


@Injectable()
export class ExpensesService {
  constructor(
    @InjectRepository(RoomExpense) private roomExpRepo: Repository<RoomExpense>,
    @InjectRepository(PersonalExpense) private personalExpRepo: Repository<PersonalExpense>,
    @InjectRepository(Room) private roomRepo: Repository<Room>,
    @InjectRepository(Student) private studentRepo: Repository<Student>,
    private billsService: BillsService,
    private dataSource: DataSource,
  ) {}

  async getFormLookups() {
    const [rooms, students] = await Promise.all([
      this.roomRepo.find({
        select: ['id', 'roomNumber', 'building'],
        order: { building: 'ASC', roomNumber: 'ASC' },
      }),
      this.studentRepo.find({
        select: ['id', 'name', 'studentNo'],
        where: { status: 'active' },
        order: { name: 'ASC' },
      }),
    ]);
    return { rooms, students };
  }

  // 宿舍费用
  async createRoomExpense(dto: CreateRoomExpenseDto, userId?: number) {
    this.assertValidPeriod(dto.periodStart, dto.periodEnd);
    this.assertPositiveAmount(dto.amount);
    const room = await this.roomRepo.findOne({ where: { id: dto.roomId } });
    if (!room) throw new NotFoundException('宿舍不存在');
    const entity = this.roomExpRepo.create({ ...dto, recordedBy: userId });
    return this.roomExpRepo.save(entity);
  }

  async batchCreateRoomExpenses(dto: BatchRoomExpenseDto, userId?: number) {
    this.assertValidPeriod(dto.periodStart, dto.periodEnd);
    if (!dto.expenses?.length) throw new BadRequestException('请至少填写一条费用');
    dto.expenses.forEach((expense) => this.assertPositiveAmount(expense.amount));
    const roomIds = [...new Set(dto.expenses.map((expense) => expense.roomId))];
    const existingRooms = await this.roomRepo.find({ where: { id: In(roomIds) }, select: ['id'] });
    if (existingRooms.length !== roomIds.length) throw new NotFoundException('部分宿舍不存在');
    const entities = dto.expenses.map((e) => {
      const entity = this.roomExpRepo.create({
        roomId: e.roomId,
        expenseType: e.expenseType,
        amount: e.amount,
        description: e.description,
        periodStart: dto.periodStart,
        periodEnd: dto.periodEnd,
        recordedBy: userId,
      });
      return entity;
    });
    return this.roomExpRepo.save(entities);
  }

  async findRoomExpenses(query?: { roomId?: number; periodStart?: string; periodEnd?: string }) {
    const qb = this.roomExpRepo
      .createQueryBuilder('e')
      .leftJoinAndSelect('e.room', 'room')
      .where('e.status = :status', { status: 'active' })
      .orderBy('e.createdAt', 'DESC');
    if (query?.roomId) qb.andWhere('e.roomId = :roomId', { roomId: query.roomId });
    if (query?.periodStart) qb.andWhere('e.periodStart >= :ps', { ps: query.periodStart });
    if (query?.periodEnd) qb.andWhere('e.periodEnd <= :pe', { pe: query.periodEnd });
    return qb.getMany();
  }

  async deleteRoomExpense(id: number) {
    const e = await this.roomExpRepo.findOne({ where: { id } });
    if (!e) throw new NotFoundException('费用记录不存在');
    const billed = await this.dataSource?.getRepository('bill_items').count({ where: { roomExpenseId: id } });
    if (billed) throw new BadRequestException('已计入账单的宿舍费用不能归档，请先取消账单');
    if (e.status === 'archived') throw new BadRequestException('费用记录已归档');
    await this.roomExpRepo.update(id, { status: 'archived' });
    return { message: '已归档' };
  }

  async batchDeleteRoomExpenses(ids: number[]) {
    const uniqueIds = [...new Set(ids || [])];
    if (uniqueIds.length === 0) throw new BadRequestException('请选择要归档的记录');
    const existing = await this.roomExpRepo.find({ where: { id: In(uniqueIds) }, select: ['id'] });
    const billed = await this.dataSource?.getRepository('bill_items').count({ where: { roomExpenseId: In(uniqueIds) } });
    if (billed) throw new BadRequestException('选中记录包含已计入账单的宿舍费用');
    if (existing.length !== uniqueIds.length) throw new NotFoundException('部分费用记录不存在');
    const result = await this.roomExpRepo
      .createQueryBuilder()
      .update()
      .set({ status: 'archived' })
      .where('id IN (:...ids)', { ids: uniqueIds })
      .execute();
    return { message: `已批量归档 ${result.affected || 0} 条`, archived: result.affected || 0 };
  }

  async updateRoomExpense(id: number, dto: Partial<CreateRoomExpenseDto>) {
    const e = await this.roomExpRepo.findOne({ where: { id } });
    const billed = await this.dataSource?.getRepository('bill_items').count({ where: { roomExpenseId: id } });
    if (billed) throw new BadRequestException('已计入账单的宿舍费用不能修改，请先取消账单');
    if (!e) throw new NotFoundException('费用记录不存在');
    const periodStart = dto.periodStart ?? e.periodStart;
    const periodEnd = dto.periodEnd ?? e.periodEnd;
    this.assertValidPeriod(periodStart, periodEnd);
    if (dto.amount !== undefined) this.assertPositiveAmount(dto.amount);
    if (dto.roomId !== undefined && dto.roomId !== e.roomId) {
      const room = await this.roomRepo.findOne({ where: { id: dto.roomId } });
      if (!room) throw new NotFoundException('宿舍不存在');
    }
    Object.assign(e, dto);
    return this.roomExpRepo.save(e);
  }

  private assertPositiveAmount(amount: number) {
    if (!Number.isFinite(amount) || Math.abs(amount * 100 - Math.round(amount * 100)) > 1e-8) {
      throw new BadRequestException('费用金额最多保留两位小数');
    }
    if (amount <= 0) throw new BadRequestException('费用金额必须大于0');
  }

  private assertValidPeriod(periodStart: string, periodEnd: string) {
    if (!this.isValidDate(periodStart) || !this.isValidDate(periodEnd) || periodEnd < periodStart) {
      throw new BadRequestException('账期无效，结束日期不能早于开始日期');
    }
  }

  private isValidDate(value: string) {
    if (!/^\d{4}-\d{2}-\d{2}$/.test(value || '')) return false;
    const date = new Date(`${value}T00:00:00Z`);
    return !Number.isNaN(date.getTime()) && date.toISOString().slice(0, 10) === value;
  }

  async createStudentUtilityBill(dto: CreateStudentUtilityBillDto, userId?: number) {
    this.assertValidPeriod(dto.periodStart, dto.periodEnd);
    this.assertPositiveAmount(dto.amount);
    const student = await this.studentRepo.findOne({ where: { id: dto.studentId } });
    if (!student) throw new NotFoundException('学生不存在');
    const expense = {
      studentId: dto.studentId,
      expenseType: dto.expenseType,
      amount: dto.amount,
      expenseDate: dto.periodEnd,
      description: dto.description || (dto.expenseType === 'water' ? '学生水费' : '学生电费'),
      recordedBy: userId,
      billId: null,
    } as PersonalExpense;
    return this.billsService.createImmediatePersonalBill(expense, dto.periodStart, dto.periodEnd, userId);
  }

  // 个人附加费
  async createPersonalExpense(dto: CreatePersonalExpenseDto, userId?: number) {
    this.assertPositiveAmount(dto.amount);
    const student = await this.studentRepo.findOne({ where: { id: dto.studentId } });
    if (!student) throw new NotFoundException('学生不存在');
    const entity = this.personalExpRepo.create({ ...dto, recordedBy: userId });
    return this.personalExpRepo.save(entity);
  }

  async findPersonalExpenses(query?: { studentId?: number }) {
    const where: Record<string, unknown> = { status: 'active' };
    if (query?.studentId) where.studentId = query.studentId;
    return this.personalExpRepo.find({
      where,
      relations: ['student'],
      order: { createdAt: 'DESC' },
    });
  }

  async deletePersonalExpense(id: number) {
    const e = await this.personalExpRepo.findOne({ where: { id } });
    if (!e) throw new NotFoundException('费用记录不存在');
    if (e.billId) throw new BadRequestException('已计入账单的个人费用不能归档，请先取消账单');
    if (e.status === 'archived') throw new BadRequestException('费用记录已归档');
    await this.personalExpRepo.update(id, { status: 'archived' });
    return { message: '已归档' };
  }

  async batchDeletePersonalExpenses(ids: number[]) {
    const uniqueIds = [...new Set(ids || [])];
    if (uniqueIds.length === 0) throw new BadRequestException('请选择要归档的记录');
    const existing = await this.personalExpRepo.find({ where: { id: In(uniqueIds) } });
    if (existing.length !== uniqueIds.length) throw new NotFoundException('部分费用记录不存在');
    if (existing.some((expense) => expense.billId)) {
      throw new BadRequestException('选中记录包含已计入账单的个人费用');
    }
    const result = await this.personalExpRepo
      .createQueryBuilder()
      .update()
      .set({ status: 'archived' })
      .where('id IN (:...ids)', { ids: uniqueIds })
      .execute();
    return { message: `已批量归档 ${result.affected || 0} 条`, archived: result.affected || 0 };
  }

  async updatePersonalExpense(id: number, dto: Partial<CreatePersonalExpenseDto>) {
    const e = await this.personalExpRepo.findOne({ where: { id } });
    if (!e) throw new NotFoundException('费用记录不存在');
    if (e.billId) throw new BadRequestException('已计入账单的个人费用不能修改，请先取消账单');
    if (dto.amount !== undefined) this.assertPositiveAmount(dto.amount);
    if (dto.studentId !== undefined && dto.studentId !== e.studentId) {
      const student = await this.studentRepo.findOne({ where: { id: dto.studentId } });
      if (!student) throw new NotFoundException('学生不存在');
    }
    Object.assign(e, dto);
    return this.personalExpRepo.save(e);
  }

  /**
   * 水电费Excel批量导入
   * Excel格式: 序号|时间|房间号|房间电量|电费|冷水用量(吨)|水费|应缴金额
   * 时间格式: "2026-01-21 - 2026-02-08"
   */
  async batchImportUtilityExpenses(
    rows: {
      periodStr: string;
      roomNumber: string;
      electricityAmount: number;
      electricityFee: number;
      waterAmount: number;
      waterFee: number;
      totalFee: number;
    }[],
    userId?: number,
  ) {
    let imported = 0;
    let skipped = 0;
    const errors: string[] = [];

    for (let i = 0; i < rows.length; i++) {
      const row = rows[i];
      const rowNum = i + 2;

      if (!row.roomNumber?.trim()) {
        skipped++;
        continue;
      }

      try {
        // 查找或创建宿舍
        let room = await this.roomRepo.findOne({ where: { roomNumber: row.roomNumber.trim() } });
        if (!room) {
          const parsed = RoomsService.parseRoomNumber(row.roomNumber.trim());
          room = await this.roomRepo.save(
            this.roomRepo.create({
              roomNumber: row.roomNumber.trim(),
              building: parsed.building || undefined,
              floor: parsed.floor || undefined,
              capacity: parsed.capacity || 4,
              roomType: parsed.roomType || undefined,
            }),
          );
        }

        // 解析时间段 "2026-01-21 - 2026-02-08" 或 "2026-01-21~2026-02-08"
        let periodStart = '';
        let periodEnd = '';
        if (row.periodStr) {
          // 先尝试用" - "或" ~ "分割（带空格的分隔符，避免拆分日期内部的连字符）
          let parts = row.periodStr.split(/\s+[-~～]\s+/);
          if (parts.length < 2) {
            // 回退：尝试用正则提取 YYYY-MM-DD 格式的日期
            const dateMatches = row.periodStr.match(/(\d{4}-\d{1,2}-\d{1,2})/g);
            if (dateMatches && dateMatches.length >= 2) {
              parts = [dateMatches[0], dateMatches[1]];
            }
          }
          if (parts.length >= 2) {
            periodStart = this.normalizeDate(parts[0].trim());
            periodEnd = this.normalizeDate(parts[1].trim());
          }
        }
        if (!periodStart || !periodEnd) {
          errors.push(`第${rowNum}行: 时间格式无法解析 "${row.periodStr}"`);
          skipped++;
          continue;
        }
        if (!this.isValidDate(periodStart) || !this.isValidDate(periodEnd) || periodEnd < periodStart) {
          errors.push(`第${rowNum}行: ${row.roomNumber} 账期无效（${periodStart} ~ ${periodEnd}），已跳过`);
          skipped++;
          continue;
        }

        // 关键校验：电费 + 水费 都为 0 时，多半是 Excel 公式未正确计算或字段缺失，
        // 必须给出明确错误，避免出现"提示成功但无数据"的迷之现象。
        if ((row.electricityFee || 0) <= 0 && (row.waterFee || 0) <= 0) {
          errors.push(
            `第${rowNum}行: ${row.roomNumber} 电费和水费均为 0，可能 Excel 中是未生效的公式（请打开文件让公式重算后再保存导入），已跳过`,
          );
          skipped++;
          continue;
        }

        const existing = await this.roomExpRepo.find({
          where: [
            { importKey: `${room.id}:${periodStart}:${periodEnd}:electricity` },
            { importKey: `${room.id}:${periodStart}:${periodEnd}:water` },
          ],
        });
        const byType = new Map(existing.map((expense) => [expense.expenseType, expense]));

        let savedAny = false;
        // 导入电费
        if (row.electricityFee > 0) {
          const expense = byType.get('electricity') || this.roomExpRepo.create({
            roomId: room.id,
            expenseType: 'electricity',
            periodStart,
            periodEnd,
            importKey: `${room.id}:${periodStart}:${periodEnd}:electricity`,
          });
          if (expense.id && await this.dataSource?.getRepository('bill_items').count({ where: { roomExpenseId: expense.id } })) {
            throw new BadRequestException('该周期电费已计入账单，不能覆盖');
          }
          expense.amount = row.electricityFee;
          expense.description = `电量${row.electricityAmount}kWh`;
          expense.recordedBy = userId!;
          await this.roomExpRepo.save(expense);
          savedAny = true;
        }

        // 导入水费
        if (row.waterFee > 0) {
          const expense = byType.get('water') || this.roomExpRepo.create({
            roomId: room.id,
            expenseType: 'water',
            periodStart,
            periodEnd,
            importKey: `${room.id}:${periodStart}:${periodEnd}:water`,
          });
          if (expense.id && await this.dataSource?.getRepository('bill_items').count({ where: { roomExpenseId: expense.id } })) {
            throw new BadRequestException('该周期水费已计入账单，不能覆盖');
          }
          expense.amount = row.waterFee;
          expense.description = `用水${row.waterAmount}吨`;
          expense.recordedBy = userId!;
          await this.roomExpRepo.save(expense);
          savedAny = true;
        }

        if (savedAny) imported++;
        else {
          skipped++;
          errors.push(`第${rowNum}行: ${row.roomNumber} 无有效金额`);
        }
      } catch (e: any) {
        errors.push(`第${rowNum}行: ${row.roomNumber} 导入失败 - ${e.message}`);
        skipped++;
      }
    }

    return {
      message:
        imported > 0
          ? `成功导入 ${imported} 间宿舍水电费${skipped > 0 ? `，跳过 ${skipped} 条` : ''}`
          : `未导入任何记录${skipped > 0 ? `，共 ${skipped} 条被跳过` : ''}`,
      imported,
      skipped,
      errors: errors.length > 0 ? errors : undefined,
    };
  }

  /** 把 2026/4/1、2026-4-1 之类格式归一化为 YYYY-MM-DD */
  private normalizeDate(s: string): string {
    if (!s) return '';
    if (/^\d{4}-\d{2}-\d{2}$/.test(s)) return s;
    const m = s.match(/(\d{4})[\-\/.](\d{1,2})[\-\/.](\d{1,2})/);
    if (m) return `${m[1]}-${m[2].padStart(2, '0')}-${m[3].padStart(2, '0')}`;
    return s;
  }

  /**
   * 个人附加费Excel批量导入
   * Excel格式: 学生姓名|费用类型|金额|费用日期|说明
   */
  async batchImportPersonalExpenses(
    rows: {
      studentName: string;
      expenseType: string;
      amount: number;
      expenseDate: string;
      description?: string;
    }[],
    userId?: number,
  ) {
    let imported = 0;
    let skipped = 0;
    const errors: string[] = [];


    for (let i = 0; i < rows.length; i++) {
      const row = rows[i];
      const rowNum = i + 2;

      if (!row.studentName?.trim()) {
        skipped++;
        continue;
      }

      try {
        // 查找学生
        const student = await this.studentRepo.findOne({ where: { name: row.studentName.trim() } });
        if (!student) {
          errors.push(`第${rowNum}行: 学生"${row.studentName}"未找到`);
          skipped++;
          continue;
        }

        // 解析费用类型
        const expenseType = row.expenseType?.trim() || '';
        if (!expenseType) {
          errors.push(`第${rowNum}行: 费用类型不能为空`);
          skipped++;
          continue;
        }

        // 解析日期
        let expenseDate = row.expenseDate?.trim() || '';
        if (!expenseDate.match(/^\d{4}-\d{2}-\d{2}$/)) {
          // 尝试从各种格式解析
          const dateMatch = expenseDate.match(/(\d{4})[\-\/](\d{1,2})[\-\/](\d{1,2})/);
          if (dateMatch) {
            expenseDate = `${dateMatch[1]}-${dateMatch[2].padStart(2, '0')}-${dateMatch[3].padStart(2, '0')}`;
          } else {
            errors.push(`第${rowNum}行: 日期格式"${row.expenseDate}"无效，需要YYYY-MM-DD`);
            skipped++;
            continue;
          }
        }

        // 校验金额
        try {
          this.assertPositiveAmount(row.amount);
        } catch (e: any) {
          errors.push(`第${rowNum}行: ${row.studentName} ${e.message}`);
          skipped++;
          continue;
        }

        await this.personalExpRepo.save(
          this.personalExpRepo.create({
            studentId: student.id,
            expenseType,
            amount: row.amount,
            expenseDate,
            description: row.description || undefined,
            recordedBy: userId,
          }),
        );

        imported++;
      } catch (e: any) {
        errors.push(`第${rowNum}行: ${row.studentName} 导入失败 - ${e.message}`);
        skipped++;
      }
    }

    return {
      message: `成功导入 ${imported} 条个人附加费，跳过 ${skipped} 条`,
      imported,
      skipped,
      errors: errors.length > 0 ? errors : undefined,
    };
  }
}
