function parseJsonBlob(blob) {
	if (!blob) return null;
	try {
		const text = typeof blob === 'string' ? blob : Buffer.from(blob).toString('utf8');
		return JSON.parse(text);
	} catch {
		return null;
	}
}

/**
 * Emby stores GUIDs in SQLite as 16-byte blobs using Microsoft mixed-endian ordering.
 * Components 1-3 are little-endian; components 4-5 are big-endian.
 */
export function blobToEmbyGuid(blob) {
	if (!blob) return '';
	const b = blob instanceof Uint8Array ? blob : new Uint8Array(blob);
	if (b.length !== 16) return Buffer.from(b).toString('hex').toLowerCase();
	const out = new Uint8Array([
		b[3], b[2], b[1], b[0],
		b[5], b[4],
		b[7], b[6],
		b[8], b[9], b[10], b[11], b[12], b[13], b[14], b[15]
	]);
	return Buffer.from(out).toString('hex').toLowerCase();
}

function hasTable(db, tableName) {
	const row = db
		.prepare("SELECT 1 AS found FROM sqlite_master WHERE type = 'table' AND name = ?")
		.get(tableName);
	return !!row?.found;
}

function normalizeSectionUserId(sectionUserId) {
	return typeof sectionUserId === 'string' ? sectionUserId.trim().toLowerCase() : '';
}

export function normalizeSectionsForUser(sections, expectedEmbyGuid) {
	if (!Array.isArray(sections)) return [];
	if (!expectedEmbyGuid) return sections;

	return sections.map((section) => ({
		...section,
		UserId: expectedEmbyGuid
	}));
}

function loadUsersTableUsers(db) {
	const cols = db.prepare('PRAGMA table_info(Users)').all().map((c) => c.name);
	const nameCol = cols.find((c) => /^username$/i.test(c)) || cols.find((c) => /^name$/i.test(c));
	const guidCol = cols.find((c) => /^guid$/i.test(c));
	const idCol = cols.find((c) => /^id$/i.test(c)) || 'Id';

	if (!nameCol) {
		throw new Error(`Cannot find a name column in Users table. Columns found: ${cols.join(', ')}`);
	}

	const selectCols = [idCol, nameCol, guidCol].filter(Boolean).join(', ');
	const rows = db.prepare(`SELECT ${selectCols} FROM Users`).all();

	return rows.map((row) => {
		const rawId = row[idCol] ?? row.Id ?? row.id;
		const rawGuid = guidCol ? row[guidCol] : null;
		let embyGuid = '';
		let guid = '';

		if (rawGuid) {
			if (rawGuid instanceof Uint8Array || rawGuid instanceof Buffer) {
				const buf = Buffer.from(rawGuid);
				guid = buf.toString('hex').toUpperCase();
				embyGuid = blobToEmbyGuid(rawGuid);
			} else if (typeof rawGuid === 'string') {
				const clean = rawGuid.replace(/-/g, '').toLowerCase();
				embyGuid = clean;
				guid = clean.toUpperCase();
			}
		}

		return {
			id: rawId,
			name: row[nameCol] || `User ${rawId}`,
			guid,
			embyGuid,
			sourceTable: 'Users'
		};
	});
}

function loadLocalUsers(db) {
	const rows = db.prepare('SELECT Id, guid, data FROM LocalUsersv2').all();

	return rows.map((row) => {
		const parsed = parseJsonBlob(row.data);
		const guid = row.guid ? Buffer.from(row.guid).toString('hex').toUpperCase() : '';
		const embyGuidFromBlob = blobToEmbyGuid(row.guid);
		const embyGuidFromJson = normalizeSectionUserId(parsed?.IdString);
		const embyGuid = embyGuidFromJson || embyGuidFromBlob;

		return {
			id: row.Id,
			name: parsed?.Name || `User ${row.Id}`,
			guid,
			embyGuid,
			sourceTable: 'LocalUsersv2',
			profile: parsed || null
		};
	});
}

export function loadCanonicalUsers(db) {
	if (hasTable(db, 'LocalUsersv2')) {
		return loadLocalUsers(db);
	}
	if (hasTable(db, 'Users')) {
		return loadUsersTableUsers(db);
	}
	throw new Error('No supported user table found. Expected LocalUsersv2 or Users.');
}

export function loadHomeScreenUsers(db) {
	const users = loadCanonicalUsers(db);
	const settingsRows = db
		.prepare(
			`SELECT us.UserId, us.Value
		 FROM UserSettings us
		 JOIN UserSettingsKeys usk ON us.UserSettingsKeyId = usk.UserSettingsKeyId
		 WHERE usk.Name = 'homescreensettings'`
		)
		.all();

	const settingsMap = new Map(settingsRows.map((row) => [String(row.UserId), row.Value]));
	const userIds = new Set(users.map((user) => String(user.id)));

	let matchedUsers = 0;
	let mismatchedUsers = 0;
	let normalizedUsers = 0;
	let missingSectionUserIds = 0;

	const hydratedUsers = users.map((user) => {
		const rawValue = settingsMap.get(String(user.id));
		let sections = [];

		try {
			if (rawValue) sections = JSON.parse(rawValue).Sections || [];
		} catch {
			sections = [];
		}

		const actualUserIds = [...new Set(sections.map((section) => normalizeSectionUserId(section?.UserId)).filter(Boolean))];
		const mismatchedSectionUserIds = user.embyGuid
			? actualUserIds.filter((id) => id !== user.embyGuid)
			: actualUserIds;
		const missingIdsForUser = sections.filter((section) => !normalizeSectionUserId(section?.UserId)).length;
		const normalizedSections = normalizeSectionsForUser(sections, user.embyGuid);
		const sectionsWereNormalized =
			user.embyGuid &&
			JSON.stringify(sections) !== JSON.stringify(normalizedSections);

		if (mismatchedSectionUserIds.length === 0) matchedUsers++;
		else mismatchedUsers++;
		if (sectionsWereNormalized) normalizedUsers++;
		missingSectionUserIds += missingIdsForUser;

		return {
			id: user.id,
			name: user.name,
			guid: user.guid,
			embyGuid: user.embyGuid,
			sections: normalizedSections,
			details: {
				sourceTable: user.sourceTable,
				lastLoginDate: user.profile?.LastLoginDate || null,
				lastActivityDate: user.profile?.LastActivityDate || null,
				usesIdForConfigurationPath: user.profile?.UsesIdForConfigurationPath ?? null,
				importedCollectionsCount: Array.isArray(user.profile?.ImportedCollections) ? user.profile.ImportedCollections.length : 0
			},
			match: {
				sourceTable: user.sourceTable,
				settingsUserId: user.id,
				expectedSectionUserId: user.embyGuid,
				actualSectionUserIds: actualUserIds,
				mismatchedSectionUserIds,
				missingSectionUserIds: missingIdsForUser,
				ok: mismatchedSectionUserIds.length === 0
			}
		};
	});

	const orphanedSettingsUserIds = settingsRows
		.map((row) => String(row.UserId))
		.filter((userId, index, all) => all.indexOf(userId) === index && !userIds.has(userId));

	return {
		users: hydratedUsers,
		validation: {
			userSource: users[0]?.sourceTable || null,
			userCount: hydratedUsers.length,
			settingsCount: settingsRows.length,
			matchedUsers,
			mismatchedUsers,
			normalizedUsers,
			missingSectionUserIds,
			orphanedSettingsUserIds
		}
	};
}

export function loadUserLookup(db) {
	return new Map(loadCanonicalUsers(db).map((user) => [String(user.id), user]));
}