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 handover number: BAST/YYYY/MM/XXXX */
async function generateHandoverNumber(transaction) {
	const now = new Date();
	const year = now.getFullYear();
	const month = String(now.getMonth() + 1).padStart(2, "0");
	const prefix = `BAST/${year}/${month}`;
	
	const lastCompletion = await dbSAPPS.HandoverCompletion.findOne({
		where: { handover_number: { [Op.like]: `${prefix}%` } },
		order: [["id", "DESC"]],
		transaction,
	});

	let seq = 1;
	if (lastCompletion?.handover_number) {
		const parts = lastCompletion.handover_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 { features = [], tests = [], ...header } = data;

		// Validate source request
		const request = await dbSAPPS.HandoverRequest.findByPk(header.request_id, { transaction: t });
		if (!request) throw new Error("Source handover request (SOW) not found.");
		if (request.status !== "Approved") {
			throw new Error(`Cannot create BAST. Source request is in status: ${request.status}. Must be Approved.`);
		}

		// Double check if BAST already exists for this request
		const existingBast = await dbSAPPS.HandoverCompletion.findOne({
			where: { request_id: header.request_id, enabled: true },
			transaction: t
		});
		if (existingBast) throw new Error("A BAST completion document already exists for this request.");

		const handover_number = await generateHandoverNumber(t);
		const handover = await dbSAPPS.HandoverCompletion.create({
			...header,
			handover_number,
			status: "Draft",
			creation: new Date(),
			modified: new Date(),
		}, { transaction: t });

		if (features.length > 0) {
			const featureItems = features.map((f) => ({
				handover_id: handover.id,
				feature_name: f.feature_name,
				description: f.description || null,
				modified_by: header.modified_by,
				owner: header.owner,
				creation: new Date(),
				modified: new Date(),
			}));
			await dbSAPPS.HandoverFeature.bulkCreate(featureItems, { transaction: t });
		}

		if (tests.length > 0) {
			const testItems = tests.map((test) => ({
				handover_id: handover.id,
				module_name: test.module_name || null,
				test_scenario: test.test_scenario || null,
				test_type: test.test_type,
				test_result: test.test_result,
				status: test.status || test.test_result,
				notes: test.notes || null,
				modified_by: header.modified_by,
				owner: header.owner,
				creation: new Date(),
				modified: new Date(),
			}));
			await dbSAPPS.HandoverTest.bulkCreate(testItems, { transaction: t });
		}

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

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.app_id) where.app_id = params.app_id;

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

		const { count, rows } = await dbSAPPS.HandoverCompletion.findAndCountAll({
			where,
			order: [["modified", "DESC"]],
			limit: pageSize,
			offset,
			subQuery: false,
			include: [
				{ model: dbSAPPS.HandoverRequest, as: "request", attributes: ["id", "request_number"] },
				{ model: dbSAPPS.App, as: "app", attributes: ["id", "display_name"] },
				{ 
					model: dbSAPPS.Employee, 
					as: "receiver", 
					attributes: ["id", "employee_name"],
					include: [{ model: dbSAPPS.Designation, as: "designation", attributes: ["id", "name"] }]
				},
				{ 
					model: dbSAPPS.Employee, 
					as: "programmer", 
					attributes: ["id", "employee_name"],
					include: [{ model: dbSAPPS.Designation, as: "designation", attributes: ["id", "name"] }]
				},
				{ 
					model: dbSAPPS.Employee, 
					as: "it_manager", 
					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 completions." };
	}
}

async function getById(id) {
	try {
		const handover = await dbSAPPS.HandoverCompletion.findByPk(id, {
			include: [
				{ model: dbSAPPS.HandoverRequest, as: "request" },
				{ model: dbSAPPS.App, as: "app" },
				{ 
					model: dbSAPPS.Employee, 
					as: "receiver", 
					attributes: ["id", "employee_name"],
					include: [{ model: dbSAPPS.Designation, as: "designation", attributes: ["id", "name"] }]
				},
				{ 
					model: dbSAPPS.Employee, 
					as: "programmer", 
					attributes: ["id", "employee_name"],
					include: [{ model: dbSAPPS.Designation, as: "designation", attributes: ["id", "name"] }]
				},
				{ 
					model: dbSAPPS.Employee, 
					as: "it_manager", 
					attributes: ["id", "employee_name"],
					include: [{ model: dbSAPPS.Designation, as: "designation", attributes: ["id", "name"] }]
				},
				{ model: dbSAPPS.HandoverFeature, as: "features" },
				{ model: dbSAPPS.HandoverTest, as: "tests" },
			],
		});
		if (!handover) return { success: false, message: "Handover completion not found." };
		return { success: true, data: handover };
	} catch (error) {
		return { success: false, message: error?.message || "Failed to fetch handover completion." };
	}
}

async function approve(id, modifierId) {
	const t = await dbSAPPS.sequelize.transaction();
	try {
		const handover = await dbSAPPS.HandoverCompletion.findByPk(id, { transaction: t });
		if (!handover) throw new Error("Handover completion not found.");
		
		if (handover.status !== "Draft") {
			throw new Error(`Cannot approve BAST with status: ${handover.status}`);
		}

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

		// Sync back to Request status
		await dbSAPPS.HandoverRequest.update({
			status: "Completed",
			modified: new Date(),
			modified_by: modifierId
		}, {
			where: { id: handover.request_id },
			transaction: t
		});

		await t.commit();
		return { success: true, message: "Handover completion approved and Request marked as Completed!" };
	} catch (error) {
		await t.rollback();
		return { success: false, message: error?.message || "Failed to approve handover completion." };
	}
}

async function getInitialDataFromRequest(requestId) {
	try {
		const request = await dbSAPPS.HandoverRequest.findByPk(requestId, {
			include: [
				{ model: dbSAPPS.Employee, as: "applicant", attributes: ["id", "employee_name"] },
				{ model: dbSAPPS.Department, as: "department", attributes: ["id", "name"] },
			]
		});
		if (!request) return { success: false, message: "Request not found." };
		
		return {
			success: true,
			data: {
				request_id: request.id,
				request_number: request.request_number,
				system_description: request.system_description,
				receiver_id: request.applicant_id,
				receiver_name: request.applicant?.employee_name,
				department_name: request.department?.name,
				doc_no: request.doc_no,
				rev_no: request.rev_no
			}
		};
	} catch (error) {
		return { success: false, message: error?.message || "Failed to fetch source data." };
	}
}

async function remove(id) {
	const t = await dbSAPPS.sequelize.transaction();
	try {
		const handover = await dbSAPPS.HandoverCompletion.findByPk(id);
		if (!handover) return { success: false, message: "Handover completion not found." };
		
		if (handover.status !== "Draft") {
			return { success: false, message: "Only Draft completions can be deleted." };
		}

		// Delete associated features and tests first
		await dbSAPPS.HandoverFeature.destroy({ where: { handover_id: id }, transaction: t });
		await dbSAPPS.HandoverTest.destroy({ where: { handover_id: id }, transaction: t });
		
		await handover.destroy({ transaction: t });
		
		await t.commit();
		return { success: true, message: "Handover completion deleted successfully!" };
	} catch (error) {
		await t.rollback();
		return { success: false, message: error?.message || "Failed to delete handover completion." };
	}
}

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

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

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