import { existsSync, mkdirSync } from 'fs';
import { dirname, resolve } from 'path';
import { DatabaseSync } from 'node:sqlite';

function getCacheDbPath() {
	return process.env.EMBY_USER_CACHE_DB_PATH || resolve('.cache', 'emby-users.db');
}

function ensureCacheDir() {
	const dir = dirname(getCacheDbPath());
	if (!existsSync(dir)) {
		mkdirSync(dir, { recursive: true });
	}
}

function openCacheDb() {
	ensureCacheDir();
	const db = new DatabaseSync(getCacheDbPath());
	db.exec(`
		CREATE TABLE IF NOT EXISTS EmbyUsers (
			embyGuid TEXT PRIMARY KEY,
			name TEXT NOT NULL,
			fetchedAt TEXT NOT NULL
		);
		CREATE TABLE IF NOT EXISTS CacheMeta (
			key TEXT PRIMARY KEY,
			value TEXT NOT NULL
		);
	`);
	return db;
}

function normalizeGuid(value) {
	return typeof value === 'string' ? value.replace(/-/g, '').trim().toLowerCase() : '';
}

export function normalizeEmbyUsers(users) {
	if (!Array.isArray(users)) return [];

	return users
		.map((user) => ({
			embyGuid: normalizeGuid(user?.embyGuid ?? user?.Id),
			name: String(user?.name ?? user?.Name ?? '').trim()
		}))
		.filter((user) => user.embyGuid && user.name);
}

export function writeCachedEmbyUsers(users) {
	const normalizedUsers = normalizeEmbyUsers(users);
	const fetchedAt = new Date().toISOString();
	const db = openCacheDb();

	try {
		const clearStmt = db.prepare('DELETE FROM EmbyUsers');
		const insertStmt = db.prepare(
			'INSERT INTO EmbyUsers (embyGuid, name, fetchedAt) VALUES (?, ?, ?)'
		);
		const metaStmt = db.prepare(
			'INSERT INTO CacheMeta (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value'
		);

		db.exec('BEGIN');
		try {
			clearStmt.run();
			for (const user of normalizedUsers) {
				insertStmt.run(user.embyGuid, user.name, fetchedAt);
			}
			metaStmt.run('lastSyncedAt', fetchedAt);
			db.exec('COMMIT');
		} catch (error) {
			db.exec('ROLLBACK');
			throw error;
		}
	} finally {
		db.close();
	}

	return { users: normalizedUsers, lastSyncedAt: fetchedAt };
}

export function readCachedEmbyUsers() {
	if (!existsSync(getCacheDbPath())) {
		return { users: [], lastSyncedAt: null };
	}

	const db = openCacheDb();
	try {
		const users = db
			.prepare('SELECT embyGuid, name, fetchedAt FROM EmbyUsers ORDER BY lower(name), embyGuid')
			.all()
			.map((row) => ({
				embyGuid: normalizeGuid(row.embyGuid),
				name: row.name
			}));
		const meta = db.prepare("SELECT value FROM CacheMeta WHERE key = 'lastSyncedAt'").get();

		return {
			users,
			lastSyncedAt: meta?.value || users[0]?.fetchedAt || null
		};
	} finally {
		db.close();
	}
}

export function getCachedEmbyUserMap() {
	return new Map(readCachedEmbyUsers().users.map((user) => [user.embyGuid, user]));
}

export function applyCachedEmbyNames(users) {
	const cached = readCachedEmbyUsers();
	const lookup = new Map(cached.users.map((user) => [user.embyGuid, user]));
	let matchedCount = 0;

	const hydratedUsers = (users || []).map((user) => {
		const embyGuid = normalizeGuid(user?.embyGuid);
		const cachedUser = lookup.get(embyGuid);
		if (!cachedUser) {
			return user;
		}

		matchedCount++;
		return {
			...user,
			dbName: user.dbName || user.name,
			embyName: cachedUser.name,
			name: cachedUser.name
		};
	});

	return {
		users: hydratedUsers,
		cache: {
			matchedCount,
			totalCachedUsers: lookup.size,
			lastSyncedAt: cached.lastSyncedAt
		}
	};
}