const dbSAPPS = require("../../helpers/db");
require("dotenv").config();
const { Op } = require("sequelize");

module.exports = {
	create,
	getAll,
	getUnassignedDocTypesToRole,
	count,
	update,
	delete: _delete,
};

/**
 * Get all
 */
async function getAll(params) {
	try {
		// 1) Ambil roles dulu (lebih ringan daripada join Permission->Role pada setiap baris)
		const roleFilterConditions = {};

		if (params.role_id) {
			roleFilterConditions.id = { [Op.eq]: params.role_id };
		}
		if (params.rolesId) {
			roleFilterConditions.id = { [Op.in]: params.rolesId };
		}
		if (params.company_id) {
			roleFilterConditions.company_id = { [Op.eq]: params.company_id };
		}
		if (params.companiesId) {
			roleFilterConditions.company_id = { [Op.in]: params.companiesId };
		}

		if (params.f_role_name) {
			roleFilterConditions.display_name = { [Op.like]: `%${params.f_role_name}%` };
		}

		const andConditions = [];
		if (params.q) {
			andConditions.push({
				[Op.or]: [{ display_name: { [Op.like]: `%${params.q}%` } }],
			});
		}

		const roleWhere = {
			...roleFilterConditions,
			...(andConditions.length ? { [Op.and]: andConditions } : {}),
		};

		const roles = await dbSAPPS.Role.findAll({
			where: roleWhere,
			attributes: ["id", "display_name", "createdAt", "updatedAt"],
			raw: true,
		});

		if (!roles || roles.length === 0) {
			return { permissions: [], success: true, message: "Loaded successfully!" };
		}

		const roleById = new Map(roles.map((r) => [r.id, r]));
		const roleIds = roles.map((r) => r.id);

		// 2) Ambil permissions untuk roleIds (tanpa join role untuk mengurangi payload dan kerja ORM)
		const permissions = await dbSAPPS.Permission.findAll({
			attributes: ["id", "role_id", "doctype_id", "can_create", "can_read", "can_update", "can_delete", "scope", "createdAt", "updatedAt"],
			where: {
				role_id: { [Op.in]: roleIds },
			},
			include: [
				{
					model: dbSAPPS.DocType,
					attributes: ["id", "name", "display_name"],
					as: "doctype",
				},
			],
			raw: true,
			nest: true,
		});

		const groupedPermissions = permissions.reduce((acc, permission) => {
			const roleId = permission.role_id;
			const role = roleById.get(roleId);
			if (!role) return acc;

			const roleDisplayName = role.display_name;
			const doctypeName = permission?.doctype?.name;
			if (!doctypeName) return acc;

			const doctypeDisplayName = permission?.doctype?.display_name;
			const scope = permission.scope || "ALL";

			// Tracking: ambil timestamp terbaru (permission atau role) sebagai acuan sorting & updatedAt
			const permUpdatedAt = permission.updatedAt ? new Date(permission.updatedAt).getTime() : 0;
			const permCreatedAt = permission.createdAt ? new Date(permission.createdAt).getTime() : 0;
			const roleUpdatedAt = role.updatedAt ? new Date(role.updatedAt).getTime() : 0;
			const roleCreatedAt = role.createdAt ? new Date(role.createdAt).getTime() : 0;

			// Gunakan timestamp terbesar dari semua (permission & role) sebagai marker aktivitas terakhir
			const maxActivity = Math.max(permUpdatedAt, permCreatedAt, roleUpdatedAt, roleCreatedAt);

			if (!acc[roleId]) {
				acc[roleId] = {
					role_id: roleId,
					role_display_name: roleDisplayName,
					role_name: roleDisplayName,
					display_name: roleDisplayName,
					doctypes: {},
					// Simpan timestamp ISO terbaru untuk ditampilkan di tabel
					createdAt: role.createdAt,
					updatedAt: new Date(maxActivity).toISOString(),
					__maxActivity: maxActivity,
				};
			} else if (maxActivity > (acc[roleId].__maxActivity || 0)) {
				// Update updatedAt dan __maxActivity jika ada permission yang lebih baru
				acc[roleId].__maxActivity = maxActivity;
				acc[roleId].updatedAt = new Date(maxActivity).toISOString();
			}

			// Cast DB integer (0/1) to boolean explicitly
			acc[roleId].doctypes[doctypeName] = {
				display_name: doctypeDisplayName,
				can_create: permission.can_create === true || permission.can_create === 1,
				can_read: permission.can_read === true || permission.can_read === 1,
				can_update: permission.can_update === true || permission.can_update === 1,
				can_delete: permission.can_delete === true || permission.can_delete === 1,
				scope,
			};

			return acc;
		}, {});

		const result = Object.values(groupedPermissions);
		// Sort DESC: yang paling baru diubah/dibuat tampil di atas
		result.sort((a, b) => (b.__maxActivity || 0) - (a.__maxActivity || 0));
		result.forEach((r) => delete r.__maxActivity);

		return {
			permissions: result,
			success: true,
			message: "Loaded successfully!",
		};
	} catch (error) {
		const errorMessage = error?.message || "Failed to load, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

/**
 * Retrieves all
 */
async function getUnassignedDocTypesToRole(params) {
	try {
		// filter
		const filterConditions = {};

		if (params.role_id) {
			filterConditions.id = {
				[Op.eq]: params.role_id,
			};
		}

		if (params.rolesId) {
			filterConditions.id = {
				[Op.in]: params.rolesId,
			};
		}

		if (params.company_id) {
			filterConditions.company_id = {
				[Op.eq]: params.company_id,
			};
		}

		if (params.companiesId) {
			filterConditions.company_id = {
				[Op.in]: params.companiesId,
			};
		}

		// column filter by role name
		if (params.f_role_name) {
			filterConditions.display_name = {
				[Op.like]: `%${params.f_role_name}%`,
			};
		}

		// global search q on role display_name
		const andConditions = [];
		if (params.q) {
			andConditions.push({
				[Op.or]: [{ display_name: { [Op.like]: `%${params.q}%` } }],
			});
		}

		// must be enabled
		filterConditions.enabled = {
			[Op.eq]: true,
		};

		const roleWhere = {
			...filterConditions,
			...(andConditions.length
				? {
						[Op.and]: andConditions,
					}
				: {}),
		};

		// Optimized: LEFT JOIN permissions dan pilih role yang belum punya permission
		let result;
		result = await dbSAPPS.Role.findAll({
			where: {
				...roleWhere,
				"$permissions.role_id$": null,
			},
			attributes: ["id", "display_name", "createdAt", "updatedAt"],
			include: [
				{
					model: dbSAPPS.Permission,
					as: "permissions",
					required: false,
					attributes: [],
				},
			],
			subQuery: false,
			raw: true,
		});

		// Grouping dengan reduce untuk kecepatan
		const groupedPermissions = result.reduce((acc, role) => {
			const roleId = role.id;
			const roleDisplayName = role.display_name;
			// const doctypeName = null;
			// const doctypeDisplayName = null;

			if (!acc[roleId]) {
				acc[roleId] = {
					role_id: roleId,
					role_display_name: roleDisplayName,
					role_name: roleDisplayName,
					display_name: roleDisplayName,
					doctypes: {},
					createdAt: role.createdAt,
					updatedAt: role.updatedAt,
				};
			}

			// acc[roleId].doctypes[doctypeName] = {
			// 	display_name: doctypeDisplayName,
			// 	can_create: null,
			// 	can_read: null,
			// 	can_update: null,
			// 	can_delete: null,
			// };

			return acc;
		}, {});

		result = Object.values(groupedPermissions); // Hasil sudah diurutkan dari query

		// Pengurutan eksplisit berdasarkan updatedAt untuk memastikan urutan
		result.sort((a, b) => {
			if (!a.updatedAt || !b.updatedAt) return 0; // Handle null cases
			return new Date(b.updatedAt) - new Date(a.updatedAt); // DESC: Terbaru di atas
		});

		return {
			permissions: result,
			success: true,
			message: "Loaded successfully!",
		};
	} catch (error) {
		const errorMessage = error?.message || "Failed to load, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function count(params) {
	try {
		// filter
		const filterConditions = {};

		if (params.role_id) {
			filterConditions.role_id = {
				[Op.eq]: params.role_id,
			};
		}

		if (params.rolesId) {
			filterConditions.role_id = {
				[Op.in]: params.rolesId,
			};
		}

		if (params.doctype_id) {
			filterConditions.doctype_id = {
				[Op.eq]: params.doctype_id,
			};
		}

		if (params.doctypesId) {
			filterConditions.doctype_id = {
				[Op.in]: params.doctypesId,
			};
		}

		if (params.company_id) {
			filterConditions.company_id = {
				[Op.eq]: params.company_id,
			};
		}

		if (params.companiesId) {
			filterConditions.company_id = {
				[Op.in]: params.companiesId,
			};
		}

		const count = await dbSAPPS.Permission.count({
			where: filterConditions,
		});

		return {
			count,
			success: true,
			message: "Loaded successfully!",
		};
	} catch (error) {
		const errorMessage = error?.message || "Failed to load, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

/**
 * Creates a new
 */
async function create(params) {
	const { role_id, doctypes } = params;
	if (!role_id) {
		return { success: false, message: "Role is required!" };
	}

	// Cek existing tanpa transaksi untuk menghindari leak koneksi saat return awal
	const existingRole = await dbSAPPS.Permission.findOne({ where: { role_id: role_id }, attributes: ["role_id"] });
	if (existingRole) {
		return { success: false, message: `Role '${role_id}' already exists, you must update!` };
	}

	if (!doctypes || Object.keys(doctypes).length === 0) {
		return { success: true, message: "No changes needed!" };
	}

	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const doctypeNames = Object.keys(doctypes);
		const found = await dbSAPPS.DocType.findAll({
			where: { name: doctypeNames },
			attributes: ["id", "name"],
			transaction,
		});

		const idByName = new Map(found.map((d) => [d.name, d.id]));
		const missing = doctypeNames.filter((name) => !idByName.has(name));
		if (missing.length > 0) {
			throw new Error(`DocType not found: ${missing.join(", ")}`);
		}

		const bulkData = doctypeNames.map((doctypeName) => {
			const permissions = doctypes[doctypeName] || {};
			return {
				role_id,
				doctype_id: idByName.get(doctypeName),
				can_create: permissions.can_create === true || permissions.can_create === 1,
				can_read: permissions.can_read !== undefined ? (permissions.can_read === true || permissions.can_read === 1) : true,
				can_update: permissions.can_update === true || permissions.can_update === 1,
				can_delete: permissions.can_delete === true || permissions.can_delete === 1,
				scope: permissions.scope || "ALL",
			};
		});

		await dbSAPPS.Permission.bulkCreate(bulkData, {
			transaction,
			updateOnDuplicate: ["can_create", "can_read", "can_update", "can_delete", "scope"],
		});

		await transaction.commit();
		return {
			id: role_id,
			success: true,
			message: "Saved successfully!",
		};
	} catch (error) {
		try {
			if (transaction && !transaction.finished) await transaction.rollback();
		} catch (_) {
			// ignore rollback errors (e.g., connection closed)
		}
		const errorMessage = error.message || "Failed to save, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

/**
 * Update an existing
 */
async function update(params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const { role_id, doctypes } = params;
		if (!role_id) {
			throw new Error("Role ID is required!");
		}

		// FULL REPLACE STRATEGY:
		// Hapus semua permission lama untuk role ini, kemudian insert ulang.
		// Ini memastikan doctype yang dihapus user (unchecked) benar-benar terhapus dari DB.
		await dbSAPPS.Permission.destroy({
			where: { role_id },
			transaction,
		});

		if (!doctypes || Object.keys(doctypes).length === 0) {
			// Tidak ada permission yang di-set, cukup kosongkan (sudah di-destroy di atas)
			await transaction.commit();
			return { success: true, message: "Saved successfully! (all permissions cleared)" };
		}

		const doctypeNames = Object.keys(doctypes);
		const found = await dbSAPPS.DocType.findAll({
			where: { name: doctypeNames },
			attributes: ["id", "name"],
			transaction,
		});
		const idByName = new Map(found.map((d) => [d.name, d.id]));
		const missing = doctypeNames.filter((name) => !idByName.has(name));
		if (missing.length > 0) {
			throw new Error(`DocType not found: ${missing.join(", ")}`);
		}

		// Cast semua nilai boolean secara eksplisit agar tidak ada data palsu masuk DB
		const bulkData = doctypeNames.map((doctypeName) => {
			const permissions = doctypes[doctypeName] || {};
			return {
				role_id,
				doctype_id: idByName.get(doctypeName),
				can_create: permissions.can_create === true || permissions.can_create === 1,
				can_read: permissions.can_read !== undefined ? (permissions.can_read === true || permissions.can_read === 1) : true,
				can_update: permissions.can_update === true || permissions.can_update === 1,
				can_delete: permissions.can_delete === true || permissions.can_delete === 1,
				scope: permissions.scope || "ALL",
			};
		});

		await dbSAPPS.Permission.bulkCreate(bulkData, { transaction });

		await transaction.commit();
		return {
			success: true,
			message: "Saved successfully!",
		};
	} catch (error) {
		try {
			if (transaction && !transaction.finished) await transaction.rollback();
		} catch (_) {
			// ignore rollback errors (e.g., connection closed)
		}
		const errorMessage = error.message || "An error occurred during permission update.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

/**
 * Delete
 */
async function _delete(id) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		if (!id) {
			return res.status(400).json({
				success: false,
				message: "Role ID is required.",
			});
		}

		const deleted = await dbSAPPS.Permission.destroy({
			where: { role_id: id },
			transaction,
		});

		if (deleted === 0) {
			await transaction.rollback();
			throw new Error(`No permissions found for role_id = ${id}`);
		}

		await transaction.commit();
		return {
			success: true,
			message: "Deleted successfully!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error.message || "An error occurred during permission deletion.";
		return { success: false, message: errorMessage };
	}
}
