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

/** Doctype ID for Work Activity — must exist in doc_types table */
const WORK_ACTIVITY_DOCTYPE_ID = 230;

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

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

			return {
				...item,
				modifier: modifier?.full_name,
			};
		}),
	);
};

/**
 * Get all workActivities with filters (companiesId, employeesId, q) and attach workflow.
 */
async function getAll(params) {
	try {
		const filterConditions = {};
		const andConditions = [];
		const hasPeriodFilter = params.period_year != null && params.period_year !== "" && params.period_month != null && params.period_month !== "";

		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.q) {
			andConditions.push({
				[Op.or]: [{ work_type: { [Op.like]: `%${params.q}%` } }, { "$employee.employee_name$": { [Op.like]: `%${params.q}%` } }],
			});
		}
		// Workflow preview: filter by single doc_id when opened from workflows
		if (params.doc_id != null && params.is_workflow_direct) {
			const docId = parseInt(params.doc_id, 10);
			if (Number.isFinite(docId)) {
				filterConditions.id = docId;
			}
		}

		// Monthly docs filter (preferred): 1 doc = 1 bulan. We intentionally only list monthly docs here.
		if (hasPeriodFilter) {
			filterConditions.period_year = parseInt(params.period_year, 10);
			filterConditions.period_month = parseInt(params.period_month, 10);
		} else {
			// Legacy date range filtering removed as activity_date is no longer used in workactivities
		}
		if (andConditions.length > 0) {
			filterConditions[Op.and] = andConditions;
		}

		let workActivities = await dbSAPPS.WorkActivity.findAll({
			where: filterConditions,
			order: hasPeriodFilter
				? [
						["period_year", "DESC"],
						["period_month", "DESC"],
						[{ model: dbSAPPS.WorkActivityItem, as: "workactivityitems" }, "activity_date", "ASC"],
						[{ model: dbSAPPS.WorkActivityItem, as: "workactivityitems" }, "sequence_number", "ASC"],
					]
				: [
						["modified", "DESC"],
						[{ model: dbSAPPS.WorkActivityItem, as: "workactivityitems" }, "sequence_number", "ASC"],
					],
			include: [
				{
					model: dbSAPPS.Employee,
					as: "employee",
					attributes: ["id", "employee_name", "company_id"],
					required: !!((params.companiesId && params.companiesId.length > 0) || (params.q && params.q.trim())),
				},
				{
					model: dbSAPPS.WorkActivityItem,
					as: "workactivityitems",
					include: [
						{
							model: dbSAPPS.WorkActivityItemFile,
							as: "workactivityitemfiles",
							required: false,
						},
					],
				},
			],
		});

		const ids = workActivities.map((wa) => wa.id);
		let workflowsByDocId = {};
		if (ids.length > 0) {
			const workflows = await dbSAPPS.Workflow.findAll({
				where: {
					doctype_id: WORK_ACTIVITY_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;
			});
		}

		workActivities = workActivities.map((wa) => {
			const items = (wa.workactivityitems || []).map((item) => {
				const itemData = item.dataValues ? { ...item.dataValues } : { ...item };
				itemData.files = item.workactivityitemfiles || [];
				return itemData;
			});
			const row = {
				...wa.dataValues,
				workactivityitems: items,
				employee_name: wa?.employee?.employee_name || null,
				total_items: items.length,
				workflow: workflowsByDocId[wa.id] || null,
			};
			return row;
		});

		workActivities = await formatWithModifier(workActivities);

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

/**
 * Get workActivities with their respective settings (options for dropdowns).
 */
async function option(params) {
	try {
		const filterConditions = { enabled: { [Op.eq]: true } };
		if (params.companiesId && params.companiesId.length > 0) {
			filterConditions["$employee.company_id$"] = {
				[Op.in]: params.companiesId.map((id) => parseInt(id, 10)),
			};
		}

		let options = [];
		options = await dbSAPPS.WorkActivity.findAll({
			where: filterConditions,
			attributes: ["id", "employee_id"],
			include: [
				{
					model: dbSAPPS.Employee,
					as: "employee",
					attributes: ["id", "employee_name", "company_id"],
					required: !!(params.companiesId && params.companiesId.length > 0),
				},
				{
					model: dbSAPPS.WorkActivityItem,
					as: "workactivityitems",
				},
			],
			order: [["period_year", "DESC"], ["period_month", "DESC"]],
		});

		options = options.map((opt) => ({
			...opt.dataValues,
		}));

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

/**
 * Count active workActivities
 */
async function count(params) {
	try {
		// filter
		const filterConditions = {};

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

		const count = await dbSAPPS.WorkActivity.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 workActivity
 *
 */
async function create(params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		const isMonthly = params.period_year != null && params.period_month != null;
		if (isMonthly) {
			const existingMonthly = await dbSAPPS.WorkActivity.findOne({
				where: {
					period_year: parseInt(params.period_year, 10),
					period_month: parseInt(params.period_month, 10),
					employee_id: params.employee_id,
				},
				transaction,
			});
			if (existingMonthly) {
				await transaction.rollback();
				return {
					success: false,
					message: `"Work Activities ${params.period_year}-${String(params.period_month).padStart(2, "0")}" is already created.`,
				};
			}
		} else {
			// Legacy checking removed as docs are now monthly only
		}

		// Clean up empty string IDs to null for FK constraint
		let createPayload = { ...params };
		if (createPayload.company_id === "") createPayload.company_id = null;
		if (createPayload.employee_id === "") createPayload.employee_id = null;

		// Set company_id from employee if not provided
		if (!createPayload.company_id && createPayload.employee_id) {
			const emp = await dbSAPPS.Employee.findByPk(createPayload.employee_id, {
				attributes: ["company_id"],
				transaction,
			});
			if (emp?.company_id) createPayload.company_id = emp.company_id;
		}

		// For monthly docs, we map the periods into the table
		if (isMonthly) {
			createPayload.period_year = parseInt(createPayload.period_year, 10);
			createPayload.period_month = parseInt(createPayload.period_month, 10);
		}

		const workActivity = await dbSAPPS.WorkActivity.create(createPayload, {
			transaction,
		});

		if (params.workactivityitems && Array.isArray(params.workactivityitems)) {
			const now = new Date();
			const itemsPayload = params.workactivityitems.map((item, index) => {
				const { files, workactivityitemfiles, ...rest } = item;
				return {
					...rest,
					workactivity_id: workActivity.id,
					activity_date: item.activity_date ?? null,
					sequence_number: item.sequence_number != null ? item.sequence_number : index + 1,
					creation: now,
					modified: now,
					modified_by: params.modified_by,
					owner: params.owner,
					enabled: item.enabled ?? true,
				};
			});

			const createdItems = await dbSAPPS.WorkActivityItem.bulkCreate(itemsPayload, { transaction });

			for (let i = 0; i < createdItems.length; i++) {
				const itemFiles = params.workactivityitems[i].files;
				if (Array.isArray(itemFiles) && itemFiles.length > 0) {
					const validFiles = itemFiles.filter((f) => f && f.file_path != null && String(f.file_path).trim() !== "");
					if (validFiles.length > 0) {
						const fileRows = validFiles.map((f, fi) => ({
							workactivityitem_id: createdItems[i].id,
							file_name: f.file_name ?? f.name ?? null,
							file_path: String(f.file_path).trim(),
							sort_order: fi,
							creation: now,
							modified: now,
							modified_by: params.modified_by ?? null,
							owner: params.owner ?? null,
							enabled: true,
						}));
						await dbSAPPS.WorkActivityItemFile.bulkCreate(fileRows, {
							transaction,
						});
					}
				}
			}
		}

		// Buat workflow dengan status Draft agar dokumen baru langsung tampil status "Draft"
		const now = new Date();
		await dbSAPPS.Workflow.create(
			{
				creation: now,
				modified: now,
				modified_by: createPayload.modified_by ?? 1,
				owner: createPayload.owner ?? 1,
				doctype_id: WORK_ACTIVITY_DOCTYPE_ID,
				doc_id: workActivity.id,
				status: "Draft",
			},
			{ transaction },
		);

		await transaction.commit();
		return {
			success: true,
			message: "Saved successfully!",
			id: workActivity.id,
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error?.message || "Failed to save, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

/**
 * Updates an existing workActivity
 *
 */
async function update(id, params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		// Cek apakah work activity dengan ID tersebut ada
		const existingWorkActivity = await dbSAPPS.WorkActivity.findOne({
			where: { id },
			transaction,
		});

		if (!existingWorkActivity) {
			return {
				success: false,
				message: "Work activity not found.",
			};
		}

		const isMonthly = params.period_year != null && params.period_month != null;
		// Duplicate check
		if (isMonthly && params.employee_id) {
			const duplicateMonthly = await dbSAPPS.WorkActivity.findOne({
				where: {
					period_year: parseInt(params.period_year, 10),
					period_month: parseInt(params.period_month, 10),
					employee_id: params.employee_id,
					id: { [dbSAPPS.Sequelize.Op.ne]: id },
				},
				transaction,
			});
			if (duplicateMonthly) {
				await transaction.rollback();
				return {
					success: false,
					message: `"Work Activities ${params.period_year}-${String(params.period_month).padStart(2, "0")}" is already created.`,
				};
			}
		} else if (!isMonthly && params.employee_id) {
			// Legacy checking removed
		}

		// Update work activity
		const updatePayload = { ...params };
		if (updatePayload.company_id === "") updatePayload.company_id = null;
		if (updatePayload.employee_id === "") updatePayload.employee_id = null;
        
		if (isMonthly) {
			updatePayload.period_year = parseInt(updatePayload.period_year, 10);
			updatePayload.period_month = parseInt(updatePayload.period_month, 10);
		}

		await dbSAPPS.WorkActivity.update(
			{
				...updatePayload,
				modified: new Date(),
			},
			{
				where: { id },
				transaction,
			},
		);

		// Handle work activity items
		if (params.workactivityitems && Array.isArray(params.workactivityitems)) {
			await dbSAPPS.WorkActivityItem.destroy({
				where: { workactivity_id: id },
				transaction,
			});

			const now = new Date();
			const itemsPayload = params.workactivityitems.map((item, index) => {
				const { files, workactivityitemfiles, ...rest } = item;
				return {
					...rest,
					workactivity_id: id,
					activity_date: item.activity_date ?? null,
					sequence_number: item.sequence_number != null ? item.sequence_number : index + 1,
					creation: now,
					modified: now,
					modified_by: params.modified_by,
					owner: params.owner,
					enabled: item.enabled ?? true,
				};
			});

			const createdItems = await dbSAPPS.WorkActivityItem.bulkCreate(itemsPayload, { transaction });

			for (let i = 0; i < createdItems.length; i++) {
				const itemFiles = params.workactivityitems[i].files;
				if (Array.isArray(itemFiles) && itemFiles.length > 0) {
					const validFiles = itemFiles.filter((f) => f && f.file_path != null && String(f.file_path).trim() !== "");
					if (validFiles.length > 0) {
						const fileRows = validFiles.map((f, fi) => ({
							workactivityitem_id: createdItems[i].id,
							file_name: f.file_name ?? f.name ?? null,
							file_path: String(f.file_path).trim(),
							sort_order: fi,
							creation: now,
							modified: now,
							modified_by: params.modified_by ?? null,
							owner: params.owner ?? null,
							enabled: true,
						}));
						await dbSAPPS.WorkActivityItemFile.bulkCreate(fileRows, {
							transaction,
						});
					}
				}
			}
		}

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

/**
 * Enable or disable a workActivity
 * @param {number} id - the id of the workActivity
 * @param {object} params - the update data
 * @param {boolean} params.enabled - the enabled status of the workActivity
 * @param {number} [params.modified_by] - the modified by of the workActivity
 * @param {Date} [params.modified] - the modified of the workActivity
 * @returns {Promise<object>} - the result of the update
 */
async function enabled(id, params) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		// validate existing
		const workActivity = await dbSAPPS.WorkActivity.findByPk(id);
		if (!workActivity) return { success: false, message: "ID not found!" };

		// update params
		const updateData = {};
		updateData.enabled = params.enabled;
		if (params.modified_by) updateData.modified_by = params.modified_by;
		if (params.modified) updateData.modified = params.modified;

		// save
		await workActivity.update(updateData, { transaction });

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

/**
 * Delete a workActivity
 * @param {number} id - the id of the workActivity
 * @returns {Promise<object>} - the result of the delete
 */
async function _delete(id) {
	const transaction = await dbSAPPS.sequelize.transaction();
	try {
		// validate existing
		const workActivity = await dbSAPPS.WorkActivity.findByPk(id, {
			transaction,
		});
		if (!workActivity) return { success: false, message: "ID not found!" };

		// delete
		await workActivity.destroy({ transaction });

		await transaction.commit();
		return {
			success: true,
			message: "Deleted successfully!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error?.message || "Failed to delete, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

/**
 * Create workflow for an existing Work Activity (single-layer: designation or territory).
 * Same pattern as absenceClarification/attendanceRequest createWorkflow.
 */
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_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 workflowData = {
			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",
		};

		const workflow = await dbSAPPS.Workflow.create(workflowData, {
			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);
			const approvers = await dbSAPPS.WorkflowApprover.bulkCreate([approverPayload], {
				transaction,
				lock: transaction.LOCK.UPDATE,
			});
			if (!approvers || approvers.length !== 1) {
				await transaction.rollback();
				return {
					success: false,
					message: "Failed to create workflow approver.",
				};
			}
		} else if (params.designation_id) {
			approverPayload.designation_id = parseInt(params.designation_id, 10);
			const approvers = await dbSAPPS.WorkflowApprover.bulkCreate([approverPayload], {
				transaction,
				lock: transaction.LOCK.UPDATE,
			});
			if (!approvers || approvers.length !== 1) {
				await transaction.rollback();
				return {
					success: false,
					message: "Failed to create workflow approver.",
				};
			}
		} else {
			await transaction.rollback();
			return {
				success: false,
				message: "At least one of designation_id or territory_id is required for workflow approver.",
			};
		}

		await transaction.commit();
		return {
			success: true,
			message: "Workflow has been created!",
		};
	} catch (error) {
		await transaction.rollback();
		const errorMessage = error?.message || "Failed to create workflow.";
		return {
			success: false,
			message: errorMessage,
		};
	}
}
