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

function escapeForLike(s) {
	if (s == null || typeof s !== "string") return "";
	return String(s).replace(/\\/g, "\\\\").replace(/%/g, "\\%").replace(/_/g, "\\_");
}

/** Generate request number: SOW/YYYY/MM/XXXX */
async function generateRequestNumber(transaction) {
	const now = new Date();
	const year = now.getFullYear();
	const month = String(now.getMonth() + 1).padStart(2, "0");
	const prefix = `SOW/${year}/${month}`;
	
	const lastRequest = await dbSAPPS.HandoverRequest.findOne({
		where: { request_number: { [Op.like]: `${prefix}%` } },
		order: [["id", "DESC"]],
		transaction,
	});

	let seq = 1;
	if (lastRequest?.request_number) {
		const parts = lastRequest.request_number.split("/");
		seq = (parseInt(parts[parts.length - 1], 10) || 0) + 1;
	}
	return `${prefix}/${String(seq).padStart(4, "0")}`;
}

async function create(data) {
	const t = await dbSAPPS.sequelize.transaction();
	try {
		const request_number = await generateRequestNumber(t);
		const request = await dbSAPPS.HandoverRequest.create({
			...data,
			request_number,
			status: "Draft",
			creation: new Date(),
			modified: new Date(),
		}, { transaction: t });

		await t.commit();
		return { success: true, message: "Handover request (SOW) created successfully!", id: request.id, request_number };
	} catch (error) {
		await t.rollback();
		return { success: false, message: error?.message || "Failed to create handover request." };
	}
}

async function getAll(params = {}) {
	try {
		const pageIndex = parseInt(params.pageIndex, 10) || 0;
		const pageSize = parseInt(params.pageSize, 10) || 25;
		const offset = pageIndex * pageSize;

		const where = { enabled: true };
		if (params.status) where.status = params.status;
		if (params.department_id) where.department_id = params.department_id;
		if (params.applicant_id) where.applicant_id = params.applicant_id;

		if (params.q && String(params.q).trim()) {
			const q = escapeForLike(params.q.trim());
			where[Op.or] = [
				{ request_number: { [Op.like]: `%${q}%` } },
				{ system_description: { [Op.like]: `%${q}%` } },
				{ renewal_reason: { [Op.like]: `%${q}%` } },
			];
		}

		const { count, rows } = await dbSAPPS.HandoverRequest.findAndCountAll({
			where,
			order: [["modified", "DESC"]],
			limit: pageSize,
			offset,
			include: [
				{ 
					model: dbSAPPS.Employee, 
					as: "applicant", 
					attributes: ["id", "employee_name"],
					include: [{ model: dbSAPPS.Designation, as: "designation", attributes: ["id", "name"] }]
				},
				{ model: dbSAPPS.Department, as: "department", attributes: ["id", "name"] },
				{ 
					model: dbSAPPS.Employee, 
					as: "supervisor", 
					attributes: ["id", "employee_name"],
					include: [{ model: dbSAPPS.Designation, as: "designation", attributes: ["id", "name"] }]
				},
			],
		});

		return { success: true, count, data: rows };
	} catch (error) {
		return { success: false, message: error?.message || "Failed to fetch handover requests." };
	}
}

async function getById(id) {
	try {
		const request = await dbSAPPS.HandoverRequest.findByPk(id, {
			include: [
				{ 
					model: dbSAPPS.Employee, 
					as: "applicant", 
					attributes: ["id", "employee_name", "company_email"],
					include: [{ model: dbSAPPS.Designation, as: "designation", attributes: ["id", "name"] }]
				},
				{ model: dbSAPPS.Department, as: "department", attributes: ["id", "name"] },
				{ 
					model: dbSAPPS.Employee, 
					as: "supervisor", 
					attributes: ["id", "employee_name"],
					include: [{ model: dbSAPPS.Designation, as: "designation", attributes: ["id", "name"] }]
				},
				{ model: dbSAPPS.HandoverCompletion, as: "completion" }
			],
		});
		if (!request) return { success: false, message: "Handover request not found." };
		return { success: true, data: request };
	} catch (error) {
		return { success: false, message: error?.message || "Failed to fetch handover request." };
	}
}

async function update(id, data) {
	try {
		const request = await dbSAPPS.HandoverRequest.findByPk(id);
		if (!request) return { success: false, message: "Handover request not found." };
		
		if (request.status !== "Draft") {
			return { success: false, message: "Only Draft requests can be edited." };
		}

		await request.update({
			...data,
			modified: new Date(),
		});

		return { success: true, message: "Handover request updated successfully!" };
	} catch (error) {
		return { success: false, message: error?.message || "Failed to update handover request." };
	}
}

async function approve(id, modifierId) {
	try {
		const request = await dbSAPPS.HandoverRequest.findByPk(id);
		if (!request) return { success: false, message: "Handover request not found." };
		
		if (request.status !== "Draft" && request.status !== "Pending") {
			return { success: false, message: `Cannot approve request with status: ${request.status}` };
		}

		await request.update({
			status: "Approved",
			modified: new Date(),
			modified_by: modifierId
		});

		return { success: true, message: "Handover request approved successfully!" };
	} catch (error) {
		return { success: false, message: error?.message || "Failed to approve handover request." };
	}
}

async function remove(id) {
	const t = await dbSAPPS.sequelize.transaction();
	try {
		const request = await dbSAPPS.HandoverRequest.findByPk(id, {
			include: [{ model: dbSAPPS.HandoverCompletion, as: "completion" }]
		});
		if (!request) return { success: false, message: "Handover request not found." };
		
		if (request.status !== "Draft") {
			return { success: false, message: "Only Draft requests can be deleted." };
		}

		if (request.completion) {
			return { success: false, message: "Cannot delete request that already has a BAST completion." };
		}

		await request.destroy({ transaction: t });
		await t.commit();
		return { success: true, message: "Handover request deleted successfully!" };
	} catch (error) {
		await t.rollback();
		return { success: false, message: error?.message || "Failed to delete handover request." };
	}
}

async function enabled(id, params) {
	try {
		const request = await dbSAPPS.HandoverRequest.findByPk(id);
		if (!request) return { success: false, message: "Handover request not found." };
		
		await request.update({
			enabled: params.enabled,
			modified: new Date(),
			modified_by: params.modified_by
		});

		return { success: true, message: `Handover request ${params.enabled ? "enabled" : "disabled"} successfully!` };
	} catch (error) {
		return { success: false, message: error?.message || "Failed to update status." };
	}
}

module.exports = {
	create,
	getAll,
	getById,
	update,
	approve,
	remove,
	enabled
};
