import { BadRequestException } from '@nestjs/common';
import * as XLSX from 'xlsx';
import { normalizePhone } from 'src/modules/utils/phone.util';
import { ImportUserRow } from '../interfaces/import-users.interface';

export const MAX_IMPORT_ROWS = 500;

const ALLOWED_MIME_TYPES = new Set([
  'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
  'application/vnd.ms-excel',
  'text/csv',
  'application/csv',
]);

function normalizeHeader(header: string): string {
  return String(header).trim();
}

function cellToString(value: unknown): string {
  if (value === null || value === undefined) {
    return '';
  }
  return String(value).trim();
}

export function parseGender(value: unknown): boolean | undefined {
  if (value === null || value === undefined || value === '') {
    return undefined;
  }
  if (typeof value === 'boolean') {
    return value;
  }
  if (typeof value === 'number') {
    return value === 1;
  }
  const str = String(value).trim().toLowerCase();
  if (['true', '1', 'male', 'm', 'man', 'مرد'].includes(str)) {
    return true;
  }
  if (['false', '0', 'female', 'f', 'woman', 'زن'].includes(str)) {
    return false;
  }
  return undefined;
}

function getField(row: Record<string, unknown>, name: string): unknown {
  const key = Object.keys(row).find(k => normalizeHeader(k).toLowerCase() === name.toLowerCase());
  return key ? row[key] : undefined;
}

function isRowEmpty(row: Record<string, unknown>): boolean {
  const phone = cellToString(getField(row, 'phone'));
  const firstName = cellToString(getField(row, 'firstName'));
  const lastName = cellToString(getField(row, 'lastName'));
  const gender = getField(row, 'gender');
  return !phone && !firstName && !lastName && (gender === undefined || gender === null || gender === '');
}

export function assertExcelMimeType(mimetype: string | undefined): void {
  if (!mimetype || !ALLOWED_MIME_TYPES.has(mimetype)) {
    throw new BadRequestException('File must be an Excel (.xlsx, .xls) or CSV file');
  }
}

export function parseUsersExcel(buffer: Buffer): ImportUserRow[] {
  const workbook = XLSX.read(buffer, { type: 'buffer' });
  if (!workbook.SheetNames.length) {
    throw new BadRequestException('Excel file has no sheets');
  }

  const sheet = workbook.Sheets[workbook.SheetNames[0]];
  const rawRows = XLSX.utils.sheet_to_json<Record<string, unknown>>(sheet, { defval: '' });

  if (rawRows.length > MAX_IMPORT_ROWS) {
    throw new BadRequestException(`Excel file contains more than ${MAX_IMPORT_ROWS} rows`);
  }

  if (rawRows.length === 0) {
    return [];
  }

  const hasPhoneColumn = Object.keys(rawRows[0]).some(
    key => normalizeHeader(key).toLowerCase() === 'phone',
  );
  if (!hasPhoneColumn) {
    throw new BadRequestException('Excel file must have a "phone" column');
  }

  const rows: ImportUserRow[] = [];

  rawRows.forEach((row, index) => {
    if (isRowEmpty(row)) {
      return;
    }

    rows.push({
      rowNumber: index + 2,
      phone: normalizePhone(cellToString(getField(row, 'phone'))),
      firstName: cellToString(getField(row, 'firstName')) || undefined,
      lastName: cellToString(getField(row, 'lastName')) || undefined,
      gender: parseGender(getField(row, 'gender')),
    });
  });

  if (rows.length > MAX_IMPORT_ROWS) {
    throw new BadRequestException(`Excel file contains more than ${MAX_IMPORT_ROWS} data rows`);
  }

  return rows;
}
