import { Op } from 'sequelize';
import { Resident } from '#models/Resident';
import { Bed } from '#models/Bed';
import { normalizePhone } from '#utils/stringUtils';

export type RowError = {
	field: string;
	code: string;
	message: { vi: string; en: string };
	value?: any;
	otherRows?: number[];
	conflictWith?: { type: 'db' | 'file'; residentId?: string };
};

export type ProcessedRow = {
	index: number;
	excelRow: number;
	data: Record<string, any>;
	emailNorm: string | null;
	phoneNorm: string | null;
	idCardNorm: string | null;
	bedCodes: string[];
};

const CHUNK_SIZE = 1000;

export function normalizeEmail(v: any) {
	return v ? String(v).trim().toLowerCase() : null;
}

export function normalizeIdCard(v: any) {
	return v ? String(v).trim().toLowerCase() : null;
}

export function buildValueToRowsMap(vals: Array<{ v: string | null; row: number }>) {
	const map = new Map<string, number[]>();
	for (const { v, row } of vals) {
		if (!v) continue;
		const k = String(v).toLowerCase();
		const arr = map.get(k) || [];
		arr.push(row);
		map.set(k, arr);
	}
	return map;
}

export function preprocessRows(rows: Record<string, any>[]) {
	const processedRows: ProcessedRow[] = rows.map((r, i) => {
		const excelRow = i + 2;
		const emailNorm = normalizeEmail(r.email);
		const phoneRaw = normalizePhone(r.phone) as string | null;
		const phoneNorm = phoneRaw ? String(phoneRaw).toLowerCase() : null;
		const idCardNorm = normalizeIdCard(r.id_card);
		const bedCodesForRow =
			(r.bed_codes ?? r.bed_code ?? '')
				? String(r.bed_codes ?? r.bed_code)
						.split(/[;,|\/\n]+/)
						.map((s) => s.trim())
						.filter(Boolean)
				: [];
		return { index: i, excelRow, data: r, emailNorm, phoneNorm, idCardNorm, bedCodes: bedCodesForRow };
	});

	const emailValueRows = buildValueToRowsMap(processedRows.map((p) => ({ v: p.emailNorm, row: p.excelRow })));
	const phoneValueRows = buildValueToRowsMap(processedRows.map((p) => ({ v: p.phoneNorm, row: p.excelRow })));
	const idCardValueRows = buildValueToRowsMap(processedRows.map((p) => ({ v: p.idCardNorm, row: p.excelRow })));

	const uniqueEmails = Array.from(emailValueRows.keys());
	const uniquePhones = Array.from(phoneValueRows.keys());
	const uniqueIdCards = Array.from(idCardValueRows.keys());

	const bedCodes = [...new Set(processedRows.flatMap((p) => p.bedCodes))];

	return {
		processedRows,
		emailValueRows,
		phoneValueRows,
		idCardValueRows,
		uniqueEmails,
		uniquePhones,
		uniqueIdCards,
		bedCodes,
	};
}

function chunkArray<T>(arr: T[], size = CHUNK_SIZE) {
	const chunks: T[][] = [];
	for (let i = 0; i < arr.length; i += size) chunks.push(arr.slice(i, i + size));
	return chunks;
}

export async function fetchExistingResidentsMaps(
	uniqueEmails: string[],
	uniquePhones: string[],
	uniqueIdCards: string[],
) {
	const existingById = new Map<string, any>();

	// Helper to query by single field in chunks
	async function queryChunks(field: 'email_normalized' | 'phone_normalized' | 'id_card_normalized', values: string[]) {
		if (!values || values.length === 0) return;
		const parts = chunkArray(values, CHUNK_SIZE);
		for (const chunk of parts) {
			try {
				const found = await Resident.scope('withDeleted').findAll({
					where: { [field]: { [Op.in]: chunk } },
					attributes: ['id', 'email_normalized', 'phone_normalized', 'id_card_normalized'],
				});
				for (const f of found) existingById.set(f.id, f);
			} catch {
				// ignore errors per-chunk
			}
		}
	}

	await Promise.all([
		queryChunks('email_normalized', uniqueEmails),
		queryChunks('phone_normalized', uniquePhones),
		queryChunks('id_card_normalized', uniqueIdCards),
	]);

	const dbEmailMap = new Map<string, any>();
	const dbPhoneMap = new Map<string, any>();
	const dbIdCardMap = new Map<string, any>();
	for (const r of existingById.values()) {
		if (r.email_normalized) dbEmailMap.set(String(r.email_normalized).toLowerCase(), r);
		if (r.phone_normalized) dbPhoneMap.set(String(r.phone_normalized).toLowerCase(), r);
		if (r.id_card_normalized) dbIdCardMap.set(String(r.id_card_normalized).toLowerCase(), r);
	}

	return { dbEmailMap, dbPhoneMap, dbIdCardMap };
}

export async function fetchBedsMap(bedCodes: string[]) {
	let bedsFound: any[] = [];
	if (!bedCodes || bedCodes.length === 0) return new Map<string, string>();
	try {
		bedsFound = await Bed.findAll({
			where: { code: { [Op.in]: bedCodes }, deleted_at: null },
			attributes: ['id', 'code'],
		});
	} catch {
		bedsFound = [];
	}
	if (bedsFound.length < bedCodes.length) {
		try {
			bedsFound = await Bed.findAll({ where: { deleted_at: null }, attributes: ['id', 'code'] });
		} catch {
			bedsFound = bedsFound || [];
		}
	}
	return new Map(bedsFound.map((b: any) => [String(b.code).trim().toLowerCase(), b.id]));
}

export function validateProcessedRow(
	p: ProcessedRow,
	maps: {
		dbEmailMap: Map<string, any>;
		dbPhoneMap: Map<string, any>;
		dbIdCardMap: Map<string, any>;
		emailValueRows: Map<string, number[]>;
		phoneValueRows: Map<string, number[]>;
		idCardValueRows: Map<string, number[]>;
		bedMapLower: Map<string, string>;
	},
) {
	const { dbEmailMap, dbPhoneMap, dbIdCardMap, emailValueRows, phoneValueRows, idCardValueRows, bedMapLower } = maps;
	const r = p.data;
	const errs: RowError[] = [];

	if (p.emailNorm && dbEmailMap.has(p.emailNorm)) {
		const conflict = dbEmailMap.get(p.emailNorm);
		errs.push({
			field: 'email',
			code: 'DUPLICATE_EMAIL_DB',
			message: { vi: 'Email đã tồn tại', en: 'Email already exists' },
			value: r.email,
			conflictWith: { type: 'db', residentId: conflict.id },
		});
	}
	if (p.phoneNorm && dbPhoneMap.has(p.phoneNorm)) {
		const conflict = dbPhoneMap.get(p.phoneNorm);
		errs.push({
			field: 'phone',
			code: 'DUPLICATE_PHONE_DB',
			message: { vi: 'Số điện thoại đã tồn tại', en: 'Phone already exists' },
			value: r.phone,
			conflictWith: { type: 'db', residentId: conflict.id },
		});
	}
	if (p.idCardNorm && dbIdCardMap.has(p.idCardNorm)) {
		const conflict = dbIdCardMap.get(p.idCardNorm);
		errs.push({
			field: 'id_card',
			code: 'DUPLICATE_ID_CARD_DB',
			message: { vi: 'Số CCCD/CMND đã tồn tại', en: 'ID card already exists' },
			value: r.id_card,
			conflictWith: { type: 'db', residentId: conflict.id },
		});
	}

	if (p.emailNorm && (emailValueRows.get(p.emailNorm) || []).length > 1) {
		const other = (emailValueRows.get(p.emailNorm) || []).filter((n) => n !== p.excelRow);
		errs.push({
			field: 'email',
			code: 'DUPLICATE_EMAIL_FILE',
			message: { vi: 'Email bị trùng trong file', en: 'Email duplicated in file' },
			value: r.email,
			otherRows: other,
			conflictWith: { type: 'file' },
		});
	}
	if (p.phoneNorm && (phoneValueRows.get(p.phoneNorm) || []).length > 1) {
		const other = (phoneValueRows.get(p.phoneNorm) || []).filter((n) => n !== p.excelRow);
		errs.push({
			field: 'phone',
			code: 'DUPLICATE_PHONE_FILE',
			message: { vi: 'Số điện thoại bị trùng trong file', en: 'Phone duplicated in file' },
			value: r.phone,
			otherRows: other,
			conflictWith: { type: 'file' },
		});
	}
	if (p.idCardNorm && (idCardValueRows.get(p.idCardNorm) || []).length > 1) {
		const other = (idCardValueRows.get(p.idCardNorm) || []).filter((n) => n !== p.excelRow);
		errs.push({
			field: 'id_card',
			code: 'DUPLICATE_ID_CARD_FILE',
			message: { vi: 'Số CCCD/CMND bị trùng trong file', en: 'ID card duplicated in file' },
			value: r.id_card,
			otherRows: other,
			conflictWith: { type: 'file' },
		});
	}

	if (!p.bedCodes || p.bedCodes.length === 0) {
		errs.push({
			field: 'bed_codes',
			code: 'MISSING_BED_CODE',
			message: { vi: 'Thiếu mã giường', en: 'Missing bed_code(s)' },
		});
	} else {
		const missing = p.bedCodes.filter((c) => !bedMapLower.has(c.trim().toLowerCase()));
		if (missing.length > 0) {
			errs.push({
				field: 'bed_codes',
				code: 'BED_NOT_FOUND',
				message: {
					vi: `Không tìm thấy mã giường: ${missing.join(', ')}`,
					en: `Bed code(s) not found: ${missing.join(', ')}`,
				},
				value: missing,
			});
		}
	}

	return errs;
}

export default {
	preprocessRows,
	fetchExistingResidentsMaps,
	fetchBedsMap,
	validateProcessedRow,
};
