import * as XLSX from 'xlsx';
import { ResidentProvider } from '#providers/ResidentProvider';
import { Errors } from '#interfaces/error/ISpecificErrors';
import {
	preprocessRows,
	fetchExistingResidentsMaps,
	fetchBedsMap,
	validateProcessedRow,
} from '#services/validators/residentImportValidatorService';

type ShortRowError = { field: string; code: string; message: { vi: string; en: string } };

type ImportResult =
	| { success: true; resident: any; row: number }
	| { success: false; error: ShortRowError[]; row: number };

type ImportResponse = { results: ImportResult[] };

export async function importResidentsFromExcel(
	filePath: string,
	options?: { userId?: string | null },
): Promise<ImportResponse> {
	const userId = options?.userId || null;
	const results: ImportResult[] = [];

	const workbook = XLSX.readFile(filePath);
	const sheetNames = workbook.SheetNames;
	if (!sheetNames || sheetNames.length === 0) {
		throw Errors.validation([
			{ field: 'file', code: 'INVALID_SHEET', message: { vi: 'Không có sheet hợp lệ', en: 'No valid sheet found' } },
		]);
	}

	const sheetName = sheetNames[0]!;
	const sheet = workbook.Sheets[sheetName];
	if (!sheet) {
		throw Errors.validation([
			{ field: 'file', code: 'INVALID_SHEET', message: { vi: 'Không có sheet hợp lệ', en: 'No valid sheet found' } },
		]);
	}

	const sheetRows = XLSX.utils.sheet_to_json(sheet, { header: 1, defval: null }) as any[];
	if (!Array.isArray(sheetRows) || sheetRows.length < 2) {
		throw Errors.validation([
			{ field: 'file', code: 'EMPTY_FILE', message: { vi: 'File không có dữ liệu', en: 'File has no rows' } },
		]);
	}

	const headerRow = sheetRows[0] as any[];
	const dataRows = sheetRows.slice(1);

	const normalizeHeaderText = (s: any) =>
		String(s || '')
			.toLowerCase()
			.trim()
			.normalize('NFD')
			.replace(/[\u0300-\u036f]/g, '')
			.replace(/[^\w\s]/g, '')
			.replace(/\s+/g, '_');

	const headerPairs: Array<[string, string]> = [
		['bed_codes', 'Mã giường'],
		['full_name', 'Họ và tên'],
		['email', 'Email'],
		['phone', 'Số điện thoại'],
		['id_card', 'Số CCCD/CMND'],
		['date_of_birth', 'Ngày sinh'],
		['nationality', 'Quốc tịch'],
		['job', 'Nghề nghiệp'],
		['emergency_contact_name', 'Người liên hệ khẩn cấp'],
		['emergency_contact_phone', 'Số điện thoại liên hệ khẩn cấp'],
		['emergency_contact_relationship', 'Mối quan hệ với người liên hệ khẩn cấp'],
		['status', 'Trạng thái'],
		['move_in_at', 'Ngày vào ở'],
		['bed_code', 'Mã giường'],
	];

	const normToField = new Map<string, string>();
	for (const [eng, vn] of headerPairs) {
		normToField.set(normalizeHeaderText(eng), eng);
		normToField.set(normalizeHeaderText(vn), eng);
	}

	const fieldNames = (headerRow || []).map((h) => {
		const norm = normalizeHeaderText(h);
		return normToField.get(norm) || norm;
	});

	const rawRows = dataRows.map((arr: any[]) => {
		const obj: Record<string, any> = {};
		fieldNames.forEach((f: string, idx: number) => {
			obj[f] = arr[idx] ?? null;
		});
		return obj;
	});

	const rows = rawRows.map((row) => {
		const normalized: Record<string, any> = {};
		Object.keys(row).forEach((k) => {
			const key = k.toString().toLowerCase().trim().replace(/\s+/g, '_');
			normalized[key] = row[k];
		});
		return normalized;
	});

	const {
		processedRows,
		emailValueRows,
		phoneValueRows,
		idCardValueRows,
		uniqueEmails,
		uniquePhones,
		uniqueIdCards,
		bedCodes: bedCodesFromPreprocess,
	} = preprocessRows(rows);

	const { dbEmailMap, dbPhoneMap, dbIdCardMap } = await fetchExistingResidentsMaps(
		uniqueEmails,
		uniquePhones,
		uniqueIdCards,
	);

	const bedMapLower = await fetchBedsMap(bedCodesFromPreprocess);

	const provider = ResidentProvider.getInstance();

	for (const p of processedRows) {
		const r = p.data;
		try {
			const rowErrors = validateProcessedRow(p, {
				dbEmailMap,
				dbPhoneMap,
				dbIdCardMap,
				emailValueRows,
				phoneValueRows,
				idCardValueRows,
				bedMapLower,
			});

			if (rowErrors.length > 0) {
				const short = rowErrors.map((e) => ({ field: e.field, code: e.code, message: e.message }));
				results.push({ success: false, error: short, row: p.excelRow });
				continue;
			}

			const bedIds = p.bedCodes.map((c) => bedMapLower.get(c.trim().toLowerCase()) as string);

			const date_of_birth = r.date_of_birth ? new Date(r.date_of_birth) : null;
			const move_in_at = r.move_in_at ? new Date(r.move_in_at) : null;

			const residentData: any = {
				full_name: r.full_name || r.name || null,
				email: r.email || null,
				phone: r.phone || null,
				id_card: r.id_card || null,
				date_of_birth: date_of_birth,
				nationality: r.nationality || null,
				job: r.job || null,
				emergency_contact_name: r.emergency_contact_name || null,
				emergency_contact_phone: r.emergency_contact_phone || null,
				emergency_contact_relationship: r.emergency_contact_relationship || null,
				status: r.status || 'active',
				created_by: userId,
				updated_by: userId,
			};

			const created = await provider.createWithResidence({ ...residentData, bed_ids: bedIds, move_in_at });
			results.push({ success: true, resident: created.toJSON ? created.toJSON() : created, row: p.excelRow });
		} catch (err: any) {
			let message = err?.message || JSON.stringify(err);
			if (err && Array.isArray((err as any).errors)) {
				try {
					message = JSON.stringify((err as any).errors);
				} catch {
					/* ignore */
				}
			}
			results.push({
				success: false,
				error: [{ field: '_', code: 'IMPORT_ERROR', message: { vi: message, en: message } }],
				row: p.excelRow,
			});
		}
	}

	return { results };
}

export async function exportResidentsToXlsx(payload?: any): Promise<Buffer> {
	const provider = ResidentProvider.getInstance();
	const data = await provider.getAllWithResidence(payload || {});
	const rows = Array.isArray(data?.rows) ? data.rows : [];

	const headers = [
		'Họ và tên',
		'Email',
		'Số điện thoại',
		'Số CCCD/CMND',
		'Ngày sinh',
		'Quốc tịch',
		'Nghề nghiệp',
		'Người liên hệ khẩn cấp',
		'Số điện thoại liên hệ khẩn cấp',
		'Mối quan hệ với người liên hệ khẩn cấp',
		'Tòa nhà',
		'Mã tòa nhà',
		'Căn hộ',
		'Mã căn hộ',
		'Tầng',
		'Phòng',
		'Mã phòng',
		'Giường',
		'Mã giường',
	];

	const sheetData: Array<Array<any>> = [headers];

	for (const r of rows) {
		if (r === undefined || r === null) continue;
		const residence = r.residence || {};
		const row = [
			r.full_name ?? '',
			r.email ?? '',
			r.phone ?? '',
			r.id_card ?? '',
			r.date_of_birth ? String(r.date_of_birth) : '',
			r.nationality ?? '',
			r.job ?? '',
			r.emergency_contact_name ?? '',
			r.emergency_contact_phone ?? '',
			r.emergency_contact_relationship ?? '',
			residence.building_name ?? '',
			residence.building_code ?? '',
			residence.apartment_name ?? '',
			residence.apartment_code ?? '',
			residence.apartment_floor ?? '',
			residence.room_name ?? '',
			residence.room_code ?? '',
			residence.bed_name ?? '',
			residence.bed_code ?? '',
		];

		sheetData.push(row.map((c) => (c === null || c === undefined ? '' : c)));
	}

	const ws = XLSX.utils.aoa_to_sheet(sheetData);
	ws['!cols'] = headers.map(() => ({ wch: 25 }));

	// Force all cells to string/text to avoid Excel auto-formatting
	Object.keys(ws).forEach((k) => {
		if (k.startsWith('!')) return;
		const cell: any = (ws as any)[k];
		if (cell && cell.v !== undefined && cell.v !== null) {
			cell.t = 's';
			cell.v = String(cell.v);
			cell.z = '@';
		}
	});

	// Highlight header row: bold + background color + center alignment
	for (let c = 0; c < headers.length; c++) {
		const addr = XLSX.utils.encode_cell({ c, r: 0 });
		const cell: any = (ws as any)[addr];
		if (cell) {
			cell.s = {
				font: { bold: true, color: { rgb: '000000' } },
				fill: { fgColor: { rgb: 'FFF2CC' } },
				alignment: { horizontal: 'center', vertical: 'center' },
			};
		}
	}

	const wb = XLSX.utils.book_new();
	XLSX.utils.book_append_sheet(wb, ws, 'Residents');
	const buf = XLSX.write(wb, { bookType: 'xlsx', type: 'buffer' }) as Buffer;
	return buf;
}
