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

const WORK_ACTIVITY_PLAN_DOCTYPE_ID = 246;

module.exports = {
	getAll,
	getById,
	create,
	update,
	createWorkflow,
	delete: _delete,
};

async function getAll(params) {
	try {
		const filterConditions = {};
		const andConditions = [];

		// Backward/consumer compatibility: accept single employee_id as well.
		// Preferred param remains employeesId: number[]
		if ((!params?.employeesId || !Array.isArray(params.employeesId) || params.employeesId.length === 0) && params?.employee_id != null) {
			const empId = parseInt(params.employee_id, 10);
			if (Number.isFinite(empId)) params.employeesId = [empId];
		}

		if (params.companiesId && params.companiesId.length > 0) {
			const companyIds = params.companiesId.map((id) => parseInt(id, 10));
			andConditions.push({
				[Op.or]: [{ company_id: { [Op.in]: companyIds } }, { "$employee.company_id$": { [Op.in]: companyIds } }],
			});
		}
		if (params.employeesId && params.employeesId.length > 0) {
			filterConditions.employee_id = {
				[Op.in]: params.employeesId.map((id) => parseInt(id, 10)),
			};
		}
		if (params.period_year != null && params.period_year !== "") {
			filterConditions.period_year = parseInt(params.period_year, 10);
		}
		if (params.period_month != null && params.period_month !== "") {
			filterConditions.period_month = parseInt(params.period_month, 10);
		}
		if (params.doc_id != null && params.is_workflow_direct) {
			const docId = parseInt(params.doc_id, 10);
			if (Number.isFinite(docId)) filterConditions.id = docId;
		}
		if (andConditions.length > 0) {
			filterConditions[Op.and] = andConditions;
		}

		let plans = await dbSAPPS.WorkActivityPlan.findAll({
			where: filterConditions,
			order: [
				["period_year", "DESC"],
				["period_month", "DESC"],
				[{ model: dbSAPPS.WorkActivityPlanItem, as: "workactivityplanitems" }, "plan_date", "ASC"],
			],
			include: [
				{
					model: dbSAPPS.Employee,
					as: "employee",
					attributes: ["id", "employee_name", "company_id"],
					required: !!(params.companiesId?.length || params.q?.trim()),
				},
				{
					model: dbSAPPS.WorkActivityPlanItem,
					as: "workactivityplanitems",
				},
			],
		});

		const ids = plans.map((p) => p.id);
		let workflowsByDocId = {};
		if (ids.length > 0) {
			const workflows = await dbSAPPS.Workflow.findAll({
				where: {
					doctype_id: WORK_ACTIVITY_PLAN_DOCTYPE_ID,
					doc_id: { [Op.in]: ids },
				},
				include: [
					{
						model: dbSAPPS.WorkflowApprover,
						as: "workflowapprovers",
						attributes: ["id", "workflow_id", "designation_id", "territory_id", "status"],
						include: [
							{
								model: dbSAPPS.Designation,
								as: "designation",
								attributes: ["id", "name", "display_name"],
								include: [
									{
										model: dbSAPPS.Employee,
										as: "employee",
										attributes: ["id", "employee_name"],
										include: [{ model: dbSAPPS.User, as: "user", attributes: ["id", "email"] }],
									},
								],
							},
							{
								model: dbSAPPS.Territory,
								as: "territory",
								attributes: ["id", "name", "display_name"],
								include: [
									{
										model: dbSAPPS.Employee,
										as: "employees",
										attributes: ["id", "employee_name"],
										through: { attributes: [] },
										include: [{ model: dbSAPPS.User, as: "user", attributes: ["id", "email"] }],
									},
								],
							},
						],
					},
				],
			});
			workflows.forEach((w) => {
				workflowsByDocId[w.doc_id] = w.toJSON ? w.toJSON() : w;
			});
		}

		plans = plans.map((p) => ({
			...p.dataValues,
			employee_name: p?.employee?.employee_name || null,
			workflow: workflowsByDocId[p.id] || null,
		}));

		return {
			workActivityPlans: plans,
			success: true,
			message: "Loaded successfully!",
		};
	} catch (error) {
		const msg = error?.message || "Failed to load!";
		return { success: false, message: msg };
	}
}

async function getById(id) {
	try {
		const plan = await dbSAPPS.WorkActivityPlan.findByPk(id, {
			include: [
				{ model: dbSAPPS.Employee, as: "employee", attributes: ["id", "employee_name", "company_id"] },
				{ model: dbSAPPS.WorkActivityPlanItem, as: "workactivityplanitems" },
			],
		});
		if (!plan) return { success: false, message: "Plan not found!" };
		const workflow = await dbSAPPS.Workflow.findOne({
			where: { doctype_id: WORK_ACTIVITY_PLAN_DOCTYPE_ID, doc_id: id },
			include: [
				{
					model: dbSAPPS.WorkflowApprover,
					as: "workflowapprovers",
					attributes: ["id", "workflow_id", "designation_id", "territory_id", "status"],
					include: [
						{
							model: dbSAPPS.Designation,
							as: "designation",
							attributes: ["id", "name", "display_name"],
							include: [
								{
									model: dbSAPPS.Employee,
									as: "employee",
									attributes: ["id", "employee_name"],
									include: [{ model: dbSAPPS.User, as: "user", attributes: ["id", "email"] }],
								},
							],
						},
						{
							model: dbSAPPS.Territory,
							as: "territory",
							attributes: ["id", "name", "display_name"],
							include: [
								{
									model: dbSAPPS.Employee,
									as: "employees",
									attributes: ["id", "employee_name"],
									through: { attributes: [] },
									include: [{ model: dbSAPPS.User, as: "user", attributes: ["id", "email"] }],
								},
							],
						},
					],
				},
			],
		});
		return {
			workActivityPlan: { ...plan.toJSON(), workflow: workflow ? workflow.toJSON() : null },
			success: true,
			message: "Loaded successfully!",
		};
	} catch (error) {
		return { success: false, message: error?.message || "Failed to load!" };
	}
}

async function create(params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const { employee_id, company_id, period_month, period_year, workactivityplanitems, ...rest } = params;
		const empId = employee_id != null ? parseInt(employee_id, 10) : null;
		const periodMonth = parseInt(period_month, 10);
		const periodYear = parseInt(period_year, 10);
		if (!empId || !periodMonth || !periodYear) {
			await transaction.rollback();
			return { success: false, message: "employee_id, period_month, period_year are required." };
		}

		const existing = await dbSAPPS.WorkActivityPlan.findOne({
			where: { employee_id: empId, period_year: periodYear, period_month: periodMonth },
			transaction,
		});
		if (existing) {
			await transaction.rollback();
			return { success: false, message: "Plan for this employee and period already exists." };
		}

		let companyId = company_id != null ? parseInt(company_id, 10) : null;
		if (!companyId && empId) {
			const emp = await dbSAPPS.Employee.findByPk(empId, { attributes: ["company_id"], transaction });
			if (emp?.company_id) companyId = emp.company_id;
		}

		const now = new Date();
		const plan = await dbSAPPS.WorkActivityPlan.create(
			{
				employee_id: empId,
				company_id: companyId,
				period_month: periodMonth,
				period_year: periodYear,
				creation: now,
				modified: now,
				modified_by: params.modified_by || 1,
				owner: params.owner || 1,
				enabled: true,
			},
			{ transaction },
		);

		if (Array.isArray(workactivityplanitems) && workactivityplanitems.length > 0) {
			const items = workactivityplanitems.map((it) => ({
				work_activity_plan_id: plan.id,
				plan_date: it.plan_date,
				work_type: (it.work_type || "WFO").toUpperCase() === "WFH" ? "WFH" : "WFO",
				creation: now,
				modified: now,
				modified_by: params.modified_by || 1,
				owner: params.owner || 1,
				enabled: true,
			}));
			await dbSAPPS.WorkActivityPlanItem.bulkCreate(items, { transaction });
		}

		// Buat workflow dengan status Draft agar plan baru langsung tampil status "Draft"
		await dbSAPPS.Workflow.create(
			{
				creation: now,
				modified: now,
				modified_by: params.modified_by || 1,
				owner: params.owner || 1,
				doctype_id: WORK_ACTIVITY_PLAN_DOCTYPE_ID,
				doc_id: plan.id,
				status: "Draft",
			},
			{ transaction },
		);

		await transaction.commit();
		return { success: true, message: "Saved successfully!", id: plan.id };
	} catch (error) {
		await transaction.rollback();
		return { success: false, message: error?.message || "Failed to save!" };
	}
}

async function update(id, params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const plan = await dbSAPPS.WorkActivityPlan.findByPk(id, { transaction });
		if (!plan) {
			await transaction.rollback();
			return { success: false, message: "Plan not found!" };
		}

		// Enforce uniqueness on (employee_id, period_year, period_month) even during update
		// so a user cannot change the period/employee into an existing plan.
		const nextEmployeeId = params.employee_id != null && params.employee_id !== "" ? parseInt(params.employee_id, 10) : plan.employee_id;
		const nextPeriodYear = params.period_year != null && params.period_year !== "" ? parseInt(params.period_year, 10) : plan.period_year;
		const nextPeriodMonth = params.period_month != null && params.period_month !== "" ? parseInt(params.period_month, 10) : plan.period_month;
		if (nextEmployeeId && nextPeriodYear && nextPeriodMonth) {
			const duplicate = await dbSAPPS.WorkActivityPlan.findOne({
				where: {
					employee_id: nextEmployeeId,
					period_year: nextPeriodYear,
					period_month: nextPeriodMonth,
					id: { [Op.ne]: id },
				},
				transaction,
			});
			if (duplicate) {
				await transaction.rollback();
				return { success: false, message: "Plan for this employee and period already exists." };
			}
		}

		const { workactivityplanitems, ...rest } = params;
		if (Object.keys(rest).length) {
			await plan.update({ ...rest, modified: new Date() }, { transaction });
		}

		if (Array.isArray(workactivityplanitems)) {
			await dbSAPPS.WorkActivityPlanItem.destroy({
				where: { work_activity_plan_id: id },
				transaction,
			});
			if (workactivityplanitems.length > 0) {
				const now = new Date();
				const items = workactivityplanitems.map((it) => ({
					work_activity_plan_id: id,
					plan_date: it.plan_date,
					work_type: (it.work_type || "WFO").toUpperCase() === "WFH" ? "WFH" : "WFO",
					creation: now,
					modified: now,
					modified_by: params.modified_by || plan.modified_by,
					owner: params.owner || plan.owner,
					enabled: true,
				}));
				await dbSAPPS.WorkActivityPlanItem.bulkCreate(items, { transaction });
			}
		}

		await transaction.commit();
		return { success: true, message: "Updated successfully!" };
	} catch (error) {
		await transaction.rollback();
		return { success: false, message: error?.message || "Failed to update!" };
	}
}

async function _delete(id) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const plan = await dbSAPPS.WorkActivityPlan.findByPk(id, { transaction });
		if (!plan) {
			await transaction.rollback();
			return { success: false, message: "Plan not found!" };
		}
		await plan.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!" };
	}
}

async function createWorkflow(params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const docId = parseInt(params.doc_id, 10);
		const doctypeId = parseInt(params.doctype_id, 10) || WORK_ACTIVITY_PLAN_DOCTYPE_ID;

		const existingWorkflow = await dbSAPPS.Workflow.findOne({
			where: { doc_id: docId, doctype_id: doctypeId },
			transaction,
		});
		if (existingWorkflow) {
			await dbSAPPS.WorkflowApprover.destroy({ where: { workflow_id: existingWorkflow.id }, transaction });
			await existingWorkflow.destroy({ transaction });
		}

		const workflow = await dbSAPPS.Workflow.create(
			{
				creation: Date.now(),
				modified: Date.now(),
				modified_by: parseInt(params.modified_by, 10) || 1,
				owner: parseInt(params.owner, 10) || 1,
				doctype_id: doctypeId,
				doc_id: docId,
				status: params.workflow_status || "Draft",
			},
			{ transaction, lock: transaction.LOCK.UPDATE },
		);
		if (!workflow) {
			await transaction.rollback();
			return { success: false, message: "Failed to create workflow." };
		}

		const approverPayload = {
			creation: Date.now(),
			modified: Date.now(),
			modified_by: parseInt(params.modified_by, 10) || 1,
			owner: parseInt(params.owner, 10) || 1,
			workflow_id: workflow.id,
			status: params.approver_status || null,
		};
		if (params.territory_id) {
			approverPayload.territory_id = parseInt(params.territory_id, 10);
		} else if (params.designation_id) {
			approverPayload.designation_id = parseInt(params.designation_id, 10);
		} else {
			await transaction.rollback();
			return { success: false, message: "designation_id or territory_id is required for workflow approver." };
		}
		await dbSAPPS.WorkflowApprover.bulkCreate([approverPayload], { transaction, lock: transaction.LOCK.UPDATE });
		await transaction.commit();
		return { success: true, message: "Workflow has been created!" };
	} catch (error) {
		await transaction.rollback();
		return { success: false, message: error?.message || "Failed to create workflow." };
	}
}
