import type { ResultSetHeader, RowDataPacket } from 'mysql2';
import bcrypt from 'bcryptjs';
import type {
	BackofficeRole,
	BackofficeSession,
	BackofficeUser,
	BackofficeUserListItem,
	CreateBackofficeUserInput,
	UpdateBackofficeUserInput
} from '$lib/types/backoffice-user';
import db from '$lib/server/db';

interface BackofficeUserRow extends RowDataPacket {
	id: number;
	nama: string;
	email: string;
	password: string;
	role: BackofficeRole;
	is_aktif: number;
	created_at: Date;
	updated_at: Date;
}

function mapUser(row: BackofficeUserRow): BackofficeUser {
	return {
		...row,
		is_aktif: row.is_aktif === 1
	};
}

function mapListItem(row: BackofficeUserRow): BackofficeUserListItem {
	return {
		id: row.id,
		nama: row.nama,
		email: row.email,
		role: row.role,
		is_aktif: row.is_aktif === 1,
		created_at: row.created_at,
		updated_at: row.updated_at
	};
}

export async function findBackofficeUserByEmail(email: string): Promise<BackofficeUser | null> {
	const [rows] = await db.query<BackofficeUserRow[]>(
		`SELECT id, nama, email, password, role, is_aktif, created_at, updated_at
		 FROM backoffice_user WHERE email = ? LIMIT 1`,
		[email]
	);

	const row = rows[0];
	return row ? mapUser(row) : null;
}

export async function findBackofficeSessionById(id: number): Promise<BackofficeSession | null> {
	const [rows] = await db.query<RowDataPacket[]>(
		`SELECT id, nama, email, role FROM backoffice_user
		 WHERE id = ? AND is_aktif = 1 LIMIT 1`,
		[id]
	);

	return (rows[0] as BackofficeSession | undefined) ?? null;
}

export async function getAllBackofficeUsers(): Promise<BackofficeUserListItem[]> {
	const [rows] = await db.query<BackofficeUserRow[]>(
		`SELECT id, nama, email, role, is_aktif, created_at, updated_at
		 FROM backoffice_user ORDER BY created_at ASC`
	);

	return rows.map(mapListItem);
}

export async function getBackofficeUserById(id: number): Promise<BackofficeUserListItem | null> {
	const [rows] = await db.query<BackofficeUserRow[]>(
		`SELECT id, nama, email, role, is_aktif, created_at, updated_at
		 FROM backoffice_user WHERE id = ? LIMIT 1`,
		[id]
	);

	const row = rows[0];
	return row ? mapListItem(row) : null;
}

export async function countActiveSuperAdmins(excludeId?: number): Promise<number> {
	const params: number[] = [];
	let sql = `SELECT COUNT(*) AS total FROM backoffice_user
	           WHERE role = 'super_admin' AND is_aktif = 1`;

	if (excludeId) {
		sql += ' AND id != ?';
		params.push(excludeId);
	}

	const [rows] = await db.query<RowDataPacket[]>(sql, params);
	return Number(rows[0]?.total ?? 0);
}

export async function createBackofficeUser(input: CreateBackofficeUserInput): Promise<number | null> {
	const nama = input.nama.trim();
	const email = input.email.trim().toLowerCase();
	if (!nama || !email || !input.password || input.password.length < 6) return null;

	const hashed = await bcrypt.hash(input.password, 10);

	const [result] = await db.query<ResultSetHeader>(
		`INSERT INTO backoffice_user (nama, email, password, role, is_aktif)
		 VALUES (?, ?, ?, ?, 1)`,
		[nama, email, hashed, input.role]
	);

	return result.insertId || null;
}

export async function updateBackofficeUser(input: UpdateBackofficeUserInput): Promise<string | null> {
	const nama = input.nama.trim();
	const email = input.email.trim().toLowerCase();
	if (!input.id || !nama || !email) return 'Data tidak valid';

	const existing = await getBackofficeUserById(input.id);
	if (!existing) return 'Pengguna tidak ditemukan';

	if (existing.role === 'super_admin' && input.role !== 'super_admin') {
		const others = await countActiveSuperAdmins(input.id);
		if (others === 0 && input.is_aktif) {
			return 'Harus ada minimal satu Super Admin aktif';
		}
	}

	if (existing.role === 'super_admin' && !input.is_aktif) {
		const others = await countActiveSuperAdmins(input.id);
		if (others === 0) return 'Tidak bisa menonaktifkan Super Admin terakhir';
	}

	const fields = ['nama = ?', 'email = ?', 'role = ?', 'is_aktif = ?'];
	const params: (string | number)[] = [nama, email, input.role, input.is_aktif ? 1 : 0];

	if (input.password?.trim()) {
		if (input.password.length < 6) return 'Password minimal 6 karakter';
		fields.push('password = ?');
		params.push(await bcrypt.hash(input.password, 10));
	}

	params.push(input.id);

	const [result] = await db.query<ResultSetHeader>(
		`UPDATE backoffice_user SET ${fields.join(', ')} WHERE id = ?`,
		params
	);

	return result.affectedRows > 0 ? null : 'Gagal menyimpan pengguna';
}

export async function deleteBackofficeUser(id: number, actorId: number): Promise<string | null> {
	if (id === actorId) return 'Tidak bisa menghapus akun sendiri';

	const user = await getBackofficeUserById(id);
	if (!user) return 'Pengguna tidak ditemukan';

	if (user.role === 'super_admin') {
		const others = await countActiveSuperAdmins(id);
		if (user.is_aktif && others === 0) return 'Tidak bisa menghapus Super Admin terakhir';
	}

	const [result] = await db.query<ResultSetHeader>('DELETE FROM backoffice_user WHERE id = ?', [id]);
	return result.affectedRows > 0 ? null : 'Gagal menghapus pengguna';
}