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

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

/** Escape string for safe use in LIKE (avoid % _ matching everything/single char) */
function escapeForLike(s) {
	if (s == null || typeof s !== "string") return "";
	return String(s).replace(/\\/g, "\\\\").replace(/%/g, "\\%").replace(/_/g, "\\_");
}

const formatWithModifier = async (rows) => {
	return Promise.all(
		rows.map(async (row) => {
			const modifier = await dbSAPPS.User.findOne({
				where: { id: row.modified_by },
				attributes: ["full_name"],
			});
			return { ...row, modifier: modifier?.full_name };
		})
	);
};

async function getAll(params) {
	try {
		const filterConditions = {};
		if (params.company_id) filterConditions.company_id = { [Op.eq]: params.company_id };
		else if (params.companiesId?.length) filterConditions.company_id = { [Op.in]: params.companiesId };

		if (params.f_name) filterConditions.name = { [Op.like]: `%${escapeForLike(params.f_name)}%` };
		if (params.f_display_name) filterConditions.display_name = { [Op.like]: `%${escapeForLike(params.f_display_name)}%` };
		if (params.f_company_name) filterConditions["$company.display_name$"] = { [Op.like]: `%${escapeForLike(params.f_company_name)}%` };

		const andConditions = [];
		if (params.q && String(params.q).trim()) {
			const q = escapeForLike(params.q.trim());
			andConditions.push({
				[Op.or]: [
					{ name: { [Op.like]: `%${q}%` } },
					{ display_name: { [Op.like]: `%${q}%` } },
					{ "$company.display_name$": { [Op.like]: `%${q}%` } },
				],
			});
		}
		const whereClause = {
			...filterConditions,
			...(andConditions.length ? { [Op.and]: andConditions } : {}),
		};

		let list = await dbSAPPS.AssetCategory.findAll({
			where: whereClause,
			order: [["modified", "DESC"]],
			include: [{ model: dbSAPPS.Company, as: "company", attributes: ["id", "display_name"] }],
		});
		list = list.map((r) => ({ ...r.dataValues, company_name: r?.company?.display_name }));
		list = await formatWithModifier(list);
		return { assetCategories: list, success: true, message: "Loaded successfully!" };
	} catch (error) {
		return { success: false, message: error?.message || "Failed to load, please try again!" };
	}
}

async function option(params) {
	try {
		const filterConditions = { enabled: { [Op.eq]: true } };
		if (params.company_id) filterConditions.company_id = { [Op.eq]: params.company_id };
		if (params.companiesId) filterConditions.company_id = { [Op.in]: params.companiesId };

		let options = await dbSAPPS.AssetCategory.findAll({
			where: filterConditions,
			attributes: ["id", "name", "display_name"],
			include: [{ model: dbSAPPS.Company, as: "company", attributes: ["id", "name", "display_name"] }],
			order: [["name", "ASC"]],
		});
		options = options.map((o) => ({ ...o.dataValues, company_name: o?.company?.display_name }));
		return { options, success: true, message: "Loaded successfully!" };
	} catch (error) {
		return { success: false, message: error?.message || "Failed to load, please try again!" };
	}
}

async function count(params) {
	try {
		const count = await dbSAPPS.AssetCategory.count({ where: { enabled: { [Op.eq]: true } } });
		return { count, success: true, message: "Loaded successfully!" };
	} catch (error) {
		return { success: false, message: error?.message || "Failed to load, please try again!" };
	}
}

async function create(params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const existing = await dbSAPPS.AssetCategory.findOne({
			where: { name: params.name, company_id: params.company_id },
			transaction,
		});
		if (existing) return { success: false, message: `"${params.name}" is already taken.` };

		const existingDisplay = await dbSAPPS.AssetCategory.findOne({
			where: { display_name: params.display_name, company_id: params.company_id },
			transaction,
		});
		if (existingDisplay) return { success: false, message: `"${params.display_name}" is already taken.` };

		await dbSAPPS.AssetCategory.create(params, { transaction });
		await transaction.commit();
		return { success: true, message: "Saved successfully!" };
	} catch (error) {
		await transaction.rollback();
		return { success: false, message: error?.message || "Failed to save, please try again!" };
	}
}

async function update(id, params) {
	const pk = parseInt(id, 10);
	if (Number.isNaN(pk)) return { success: false, message: "Invalid ID!" };
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const row = await dbSAPPS.AssetCategory.findByPk(pk);
		if (!row) return { success: false, message: "ID not found!" };

		const existing = await dbSAPPS.AssetCategory.findOne({
			where: { name: params.name, company_id: params.company_id, id: { [Op.ne]: pk } },
			transaction,
		});
		if (existing) return { success: false, message: `"${params.name}" is already taken.` };

		const existingDisplay = await dbSAPPS.AssetCategory.findOne({
			where: { display_name: params.display_name, company_id: params.company_id, id: { [Op.ne]: pk } },
			transaction,
		});
		if (existingDisplay) return { success: false, message: `"${params.display_name}" is already taken.` };

		await row.update(params, { transaction });
		await transaction.commit();
		return { success: true, message: "Saved successfully!" };
	} catch (error) {
		await transaction.rollback();
		return { success: false, message: error?.message || "Failed to save, please try again!" };
	}
}

async function enabled(id, params) {
	const pk = parseInt(id, 10);
	if (Number.isNaN(pk)) return { success: false, message: "Invalid ID!" };
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const row = await dbSAPPS.AssetCategory.findByPk(pk);
		if (!row) return { success: false, message: "ID not found!" };
		await row.update({ enabled: params.enabled, ...(params.modified_by && { modified_by: params.modified_by }), ...(params.modified && { modified: params.modified }) }, { transaction });
		await transaction.commit();
		return { success: true, message: "Saved successfully!" };
	} catch (error) {
		await transaction.rollback();
		return { success: false, message: error?.message || "Failed to save, please try again!" };
	}
}

async function _delete(id) {
	const pk = parseInt(id, 10);
	if (Number.isNaN(pk)) return { success: false, message: "Invalid ID!" };
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const row = await dbSAPPS.AssetCategory.findByPk(pk, { transaction });
		if (!row) return { success: false, message: "ID not found!" };
		await row.destroy({ transaction });
		await transaction.commit();
		return { success: true, message: "Deleted successfully!" };
	} catch (error) {
		await transaction.rollback();
		return { success: false, message: error?.message || "Failed to delete, please try again!" };
	}
}
