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 loan number: LOAN-YYYYMM-XXXX */
async function generateLoanNumber(transaction) {
	const now = new Date();
	const prefix = `LOAN-${now.getFullYear()}${String(now.getMonth() + 1).padStart(2, "0")}`;
	const lastLoan = await dbSAPPS.AssetLoan.findOne({
		where: { loan_number: { [Op.like]: `${prefix}%` } },
		order: [["id", "DESC"]],
		transaction,
	});
	let seq = 1;
	if (lastLoan?.loan_number) {
		const parts = lastLoan.loan_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 { items = [], ...header } = data;
		const loan_number = await generateLoanNumber(t);
		const loan = await dbSAPPS.AssetLoan.create({
			...header,
			loan_number,
			status: "draft",
			creation: new Date(),
			modified: new Date(),
		}, { transaction: t });

		if (items.length > 0) {
			const loanItems = items.map((item) => ({
				loan_id: loan.id,
				asset_id: item.asset_id,
				notes: item.notes || null,
				creation: new Date(),
				modified: new Date(),
			}));
			await dbSAPPS.AssetLoanItem.bulkCreate(loanItems, { transaction: t });
		}

		await t.commit();
		return { success: true, message: "Loan request created successfully!", id: loan.id, loan_number };
	} catch (error) {
		await t.rollback();
		return { success: false, message: error?.message || "Failed to create loan 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 = {};
		if (params.company_id) where.company_id = params.company_id;
		else if (params.companiesId?.length) where.company_id = { [Op.in]: params.companiesId };
		if (params.status) where.status = params.status;
		if (params.f_loan_number) where.loan_number = { [Op.like]: `%${escapeForLike(params.f_loan_number)}%` };

		if (params.q && String(params.q).trim()) {
			const q = escapeForLike(params.q.trim());
			where[Op.and] = [
				{
					[Op.or]: [
						{ loan_number: { [Op.like]: `%${q}%` } },
						{ purpose: { [Op.like]: `%${q}%` } },
						{ "$borrower.full_name$": { [Op.like]: `%${q}%` } },
					],
				},
			];
		}

		const { count, rows } = await dbSAPPS.AssetLoan.findAndCountAll({
			where,
			order: [["modified", "DESC"]],
			limit: pageSize,
			offset,
			subQuery: false,
			include: [
				{ model: dbSAPPS.Company, as: "company", attributes: ["id", "display_name"] },
				{ model: dbSAPPS.Employee, as: "borrower", attributes: ["id", "full_name", "employee_id_number"], required: false },
				{ model: dbSAPPS.Department, as: "department", attributes: ["id", "name"], required: false },
				{ model: dbSAPPS.Designation, as: "designation", attributes: ["id", "name"], required: false },
				{
					model: dbSAPPS.AssetLoanItem,
					as: "items",
					attributes: ["id", "asset_id", "notes"],
					include: [
						{
							model: dbSAPPS.Asset,
							as: "asset",
							attributes: ["id", "asset_tag", "name"],
						},
					],
				},
			],
		});

		const list = rows.map((r) => ({
			...r.dataValues,
			company_name: r.company?.display_name,
			borrower_name: r.borrower?.full_name,
			borrower_id_number: r.borrower?.employee_id_number,
			department_name: r.department?.name,
			designation_name: r.designation?.name,
			item_count: r.items?.length || 0,
			item_names: r.items?.map((i) => i.asset?.name || i.asset?.asset_tag).filter(Boolean).join(", "),
		}));

		return { data: list, total: count, success: true, message: "Loaded successfully!" };
	} catch (error) {
		return { success: false, message: error?.message || "Failed to load." };
	}
}

async function getById(id) {
	try {
		const loan = await dbSAPPS.AssetLoan.findByPk(id, {
			include: [
				{ model: dbSAPPS.Company, as: "company", attributes: ["id", "display_name"] },
				{ model: dbSAPPS.Employee, as: "borrower", attributes: ["id", "full_name", "employee_id_number"], required: false },
				{ model: dbSAPPS.Department, as: "department", attributes: ["id", "name"], required: false },
				{ model: dbSAPPS.Designation, as: "designation", attributes: ["id", "name"], required: false },
				{
					model: dbSAPPS.AssetLoanItem,
					as: "items",
					include: [
						{
							model: dbSAPPS.Asset,
							as: "asset",
							attributes: ["id", "asset_tag", "name", "serial"],
							include: [
								{ model: dbSAPPS.AssetModel, as: "model", attributes: ["display_name", "model_number"],
									include: [{ model: dbSAPPS.AssetManufacturer, as: "manufacturer", attributes: ["display_name"] }]
								},
							],
						},
					],
				},
			],
		});
		if (!loan) return { success: false, message: "Loan not found!" };

		return { loan: { ...loan.dataValues }, success: true, message: "Loaded successfully!" };
	} catch (error) {
		return { success: false, message: error?.message || "Failed to load." };
	}
}

// Doctype ID for Asset Loan - must match workflow doctype in database
const ASSET_LOAN_DOCTYPE_ID = 250;

async function update(id, data) {
	const t = await dbSAPPS.sequelize.transaction();
	try {
		const loan = await dbSAPPS.AssetLoan.findByPk(id, { transaction: t });
		if (!loan) { await t.rollback(); return { success: false, message: "Loan not found!" }; }
		if (!["draft", "Need Approval", "Rejected"].includes(loan.status)) { 
			await t.rollback(); 
			return { success: false, message: "Only draft or rejected loans can be edited." }; 
		}

		const { items = [], ...header } = data;
		await loan.update({ ...header, modified: new Date() }, { transaction: t });

		// Sync status to workflow if exists
		if (data.status && ["Approved", "Rejected", "Draft", "Need Approval"].includes(data.status)) {
			const workflow = await dbSAPPS.Workflow.findOne({
				where: { doc_id: parseInt(id), doctype_id: ASSET_LOAN_DOCTYPE_ID },
				transaction: t,
			});
			if (workflow) {
				await workflow.update({
					status: data.status,
					modified: new Date(),
					modified_by: data.modified_by || workflow.modified_by,
				}, { transaction: t });
			}
		}

		// Replace items
		await dbSAPPS.AssetLoanItem.destroy({ where: { loan_id: id }, transaction: t });
		if (items.length > 0) {
			const loanItems = items.map((item) => ({
				loan_id: id,
				asset_id: item.asset_id,
				notes: item.notes || null,
				creation: new Date(),
				modified: new Date(),
			}));
			await dbSAPPS.AssetLoanItem.bulkCreate(loanItems, { transaction: t });
		}

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

async function submitToWorkflow(id, userId) {
	try {
		const loan = await dbSAPPS.AssetLoan.findByPk(id);
		if (!loan) return { success: false, message: "Loan not found!" };
		
		await loan.update({
			status: "Need Approval",
			submitted_date: new Date(),
			modified: new Date(),
			modified_by: userId
		});

		return { success: true, message: "Loan request submitted for approval!", status: "Need Approval" };
	} catch (error) {
		return { success: false, message: error?.message };
	}
}

async function approve(id, userId) {
	const t = await dbSAPPS.sequelize.transaction();
	try {
		const loan = await dbSAPPS.AssetLoan.findByPk(id, {
			include: [{ model: dbSAPPS.AssetLoanItem, as: "items" }],
			transaction: t,
		});
		if (!loan) { await t.rollback(); return { success: false, message: "Loan not found!" }; }
		if (loan.status !== "Need Approval") { await t.rollback(); return { success: false, message: "Only loans in 'Need Approval' status can be approved." }; }
		if (!loan.items?.length) { await t.rollback(); return { success: false, message: "Loan has no items to checkout." }; }

		// Checkout each asset to borrower employee
		const now = new Date();
		for (const item of loan.items) {
			const asset = await dbSAPPS.Asset.findByPk(item.asset_id, { transaction: t });
			if (!asset) continue;

			// Robustness Check: Is it already assigned?
			if (asset.assigned_to) {
				await t.rollback();
				return { 
					success: false, 
					message: `Asset "${asset.asset_tag}" (${asset.name}) is already checked out to someone else. Please remove it from the loan or check it in first.` 
				};
			}

			await asset.update({
				assigned_to: loan.borrower_employee_id,
				assigned_type: "Employee",
				assigned_at: now,
				modified: now,
				modified_by: userId,
			}, { transaction: t });

			// Log the action
			await dbSAPPS.AssetLog.create({
				creation: now,
				modified: now,
				modified_by: userId,
				owner: userId,
				item_id: asset.id,
				item_type: "asset",
				action: "checkout",
				target_id: loan.borrower_employee_id,
				target_type: "Employee",
				note: `Loan ${loan.loan_number} approved. Purpose: ${loan.purpose || '-'}`,
				company_id: loan.company_id,
			}, { transaction: t });
		}

		await loan.update({
			status: "approved",
			approver_date: now,
			modified: now,
			modified_by: userId,
		}, { transaction: t });

		// Sync status to workflow
		const workflow = await dbSAPPS.Workflow.findOne({
			where: { doc_id: parseInt(id), doctype_id: ASSET_LOAN_DOCTYPE_ID },
			transaction: t,
		});
		if (workflow) {
			await workflow.update({ status: "Approved", modified: now, modified_by: userId }, { transaction: t });
			await dbSAPPS.WorkflowApprover.update(
				{ status: "Approved", modified: now, modified_by: userId },
				{ where: { workflow_id: workflow.id, status: "Need Approval" }, transaction: t }
			);
		}

		await t.commit();
		return { success: true, message: "Loan approved and assets checked out successfully!" };
	} catch (error) {
		await t.rollback();
		return { success: false, message: error?.message || "Failed to approve loan." };
	}
}

async function revise(id, userId, notes) {
	const t = await dbSAPPS.sequelize.transaction();
	try {
		const loan = await dbSAPPS.AssetLoan.findByPk(id, { transaction: t });
		if (!loan) { await t.rollback(); return { success: false, message: "Loan not found!" }; }

		await loan.update({
			status: "draft",
			revision_notes: notes,
			modified: new Date(),
			modified_by: userId
		}, { transaction: t });

		// Sync status to workflow
		const workflow = await dbSAPPS.Workflow.findOne({
			where: { doc_id: parseInt(id), doctype_id: ASSET_LOAN_DOCTYPE_ID },
			transaction: t
		});
		if (workflow) {
			await workflow.update({ status: "Draft", modified: new Date(), modified_by: userId }, { transaction: t });
			await dbSAPPS.WorkflowApprover.update(
				{ status: "Draft", modified: new Date(), modified_by: userId },
				{ where: { workflow_id: workflow.id, status: "Need Approval" }, transaction: t }
			);
		}

		await t.commit();
		return { success: true, message: "Loan request sent back for revision!" };
	} catch (error) {
		if (t) await t.rollback();
		return { success: false, message: error?.message };
	}
}

async function reject(id, userId, reason) {
	const t = await dbSAPPS.sequelize.transaction();
	try {
		const loan = await dbSAPPS.AssetLoan.findByPk(id, { transaction: t });
		if (!loan) { await t.rollback(); return { success: false, message: "Loan not found!" }; }

		await loan.update({
			status: "Rejected",
			rejection_reason: reason,
			modified: new Date(),
			modified_by: userId
		}, { transaction: t });

		// Sync status to workflow
		const workflow = await dbSAPPS.Workflow.findOne({
			where: { doc_id: parseInt(id), doctype_id: ASSET_LOAN_DOCTYPE_ID },
			transaction: t
		});
		if (workflow) {
			await workflow.update({ status: "Rejected", modified: new Date(), modified_by: userId }, { transaction: t });
			await dbSAPPS.WorkflowApprover.update(
				{ status: "Rejected", modified: new Date(), modified_by: userId },
				{ where: { workflow_id: workflow.id, status: "Need Approval" }, transaction: t }
			);
		}

		await t.commit();
		return { success: true, message: "Loan request rejected!" };
	} catch (error) {
		if (t) await t.rollback();
		return { success: false, message: error?.message };
	}
}

async function returnLoan(id, userId) {
	const t = await dbSAPPS.sequelize.transaction();
	try {
		const loan = await dbSAPPS.AssetLoan.findByPk(id, {
			include: [{ model: dbSAPPS.AssetLoanItem, as: "items" }],
			transaction: t,
		});
		if (!loan) { await t.rollback(); return { success: false, message: "Loan not found!" }; }
		if (loan.status !== "approved") { await t.rollback(); return { success: false, message: "Only approved loans can be returned." }; }

		const now = new Date();
		for (const item of loan.items) {
			const asset = await dbSAPPS.Asset.findByPk(item.asset_id, { transaction: t });
			if (!asset) continue;

			await asset.update({
				assigned_to: null,
				assigned_type: null,
				modified: now,
				modified_by: userId,
			}, { transaction: t });

			await dbSAPPS.AssetLog.create({
				creation: now,
				modified: now,
				modified_by: userId,
				owner: userId,
				item_id: asset.id,
				item_type: "asset",
				action: "checkin",
				target_id: null,
				target_type: null,
				note: `Return from loan ${loan.loan_number}`,
				company_id: loan.company_id,
			}, { transaction: t });
		}

		await loan.update({
			status: "returned",
			actual_return_date: now,
			modified: now,
			modified_by: userId,
		}, { transaction: t });

		await t.commit();
		return { success: true, message: "Assets returned and checked in successfully!" };
	} catch (error) {
		await t.rollback();
		return { success: false, message: error?.message || "Failed to process return." };
	}
}

async function cancel(id, userId, reason) {
	try {
		const loan = await dbSAPPS.AssetLoan.findByPk(id);
		if (!loan) return { success: false, message: "Loan not found!" };
		if (!["draft", "Need Approval"].includes(loan.status)) return { success: false, message: "Only draft or pending loans can be cancelled." };

		await loan.update({ 
			status: "cancelled", 
			cancel_reason: reason,
			modified: new Date(), 
			modified_by: userId 
		});
		return { success: true, message: "Loan cancelled successfully!" };
	} catch (error) {
		return { success: false, message: error?.message || "Failed to cancel." };
	}
}

async function _delete(id) {
	try {
		const loan = await dbSAPPS.AssetLoan.findByPk(id);
		if (!loan) return { success: false, message: "Loan not found!" };
		if (!["draft", "cancelled", "Rejected"].includes(loan.status))
			return { success: false, message: "Only draft, rejected or cancelled loans can be deleted." };

		await dbSAPPS.AssetLoanItem.destroy({ where: { loan_id: id } });
		await loan.destroy();
		return { success: true, message: "Loan deleted successfully!" };
	} catch (error) {
		return { success: false, message: error?.message || "Failed to delete." };
	}
}

async function option(params = {}) {
	try {
		const where = {};
		if (params.companiesId?.length) where.company_id = { [Op.in]: params.companiesId };
		const rows = await dbSAPPS.AssetLoan.findAll({
			where,
			attributes: ["id", "loan_number", "status"],
			order: [["loan_number", "DESC"]],
			limit: 100,
		});
		return { success: true, options: rows };
	} catch (error) {
		return { success: false, message: error?.message };
	}
}

module.exports = { 
	create, 
	getAll, 
	getById, 
	update, 
	submit: submitToWorkflow,
	approve, 
	revise,
	reject,
	return: returnLoan, 
	cancel, 
	delete: _delete, 
	option 
};
