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

module.exports = {
	getAccountsReceivable,
	getAccountsPayable,
	getProfitAndLoss,
	getBalanceSheet,
	getFinancialRatio,
};

function transformAndSumData(input) {
	if (!Array.isArray(input) || input.length === 0) {
		return [];
	}

	return input.reduce((acc, curr) => {
		if (!curr.values || !Array.isArray(curr.values) || curr.values.length !== 5) {
			return acc;
		}

		curr.values.forEach((value, index) => {
			if (!acc[index]) {
				acc[index] = 0;
			}
			acc[index] += value;
		});

		return acc.map((v) => Math.round(v));
	}, []);
}

async function buildNestedAccounts(accounts) {
	const accountMap = new Map();
	accounts.forEach((acc) => {
		acc.subRows = [];
		accountMap.set(acc.account, acc);
	});

	const roots = [];

	accounts.forEach((acc) => {
		if (acc.parent_account && accountMap.has(acc.parent_account)) {
			accountMap.get(acc.parent_account).subRows.push(acc);
		} else {
			roots.push(acc);
		}
	});

	return roots;
}

async function convertFieldFormat(fields) {
	return fields
		.filter((field) => field.width)
		.map((field) => ({
			accessorKey: field.fieldname,
			header: field.label,
			size: field.width,
		}));
}

async function getAccountsReceivable(params) {
	try {
		const accountsReceivable = await axios.get(`${params.api_url}api/method/frappe.desk.query_report.run`, {
			headers: {
				Authorization: `token ${params.api_key}:${params.api_secret}`,
				"Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
				Accept: "application/json",
			},
			params: {
				report_name: "Accounts Receivable",
				filters: JSON.stringify({
					company: params.company,
					report_date: params.report_date,
					ageing_based_on: params.based_on,
					range1: 30,
					range2: 60,
					range3: 90,
					range4: 120,
					based_on_payment_terms: 0,
				}),
			},
		});

		let withinDueBalance = 0;
		let overDueBalance = 0;
		let totalBalance = 0;
		let chartDonutDataAgeing = {};
		let chartColumnDataAgeing = {};
		let top10Data = [];
		if (accountsReceivable && accountsReceivable.data && accountsReceivable.data.message && accountsReceivable.data.message.result && params.report_date) {
			const result = accountsReceivable.data.message.result;
			const reportDateObj = new Date(params.report_date);

			const withinDueItems = result.filter((item) => item.due_date && new Date(item.due_date) > reportDateObj);
			withinDueBalance = withinDueItems.reduce((acc, curr) => acc + Number(curr.outstanding), 0);

			const overDueItems = result.filter((item) => item.due_date && new Date(item.due_date) < reportDateObj);
			overDueBalance = overDueItems.reduce((acc, curr) => acc + Number(curr.outstanding), 0);

			const outstandingBalance = result.filter((item) => item.outstanding);
			totalBalance = outstandingBalance.reduce((acc, curr) => acc + Number(curr.outstanding), 0);

			top10Data = Object.entries(
				result.reduce((acc, curr) => {
					acc[curr.party] = acc[curr.party] || {
						totalBalance: 0,
						withinDueBalance: 0,
						overDueBalance: 0,
					};
					acc[curr.party].totalBalance += Number(curr.outstanding);
					if (curr.due_date && new Date(curr.due_date) > reportDateObj) {
						acc[curr.party].withinDueBalance += Number(curr.outstanding);
					} else {
						acc[curr.party].overDueBalance += Number(curr.outstanding);
					}
					return acc;
				}, {})
			)
				.sort((a, b) => b[1].totalBalance - a[1].totalBalance)
				.slice(0, 10)
				.map(([party, balances]) => ({
					party,
					totalBalance: balances.totalBalance,
					withinDueBalance: balances.withinDueBalance,
					overDueBalance: balances.overDueBalance,
					overDuePercentage: (balances.overDueBalance / balances.totalBalance) * 100,
				}));

			if (accountsReceivable.data.message.chart) {
				chartDonutDataAgeing = {
					height: 328,
					type: "donut",
					series: [withinDueBalance, ...transformAndSumData(accountsReceivable.data.message.chart.data.datasets)],
					options: {
						chart: {
							id: "accounts-receivable-donut-ageing",
						},
						labels: ["Within Due", ...accountsReceivable.data.message.chart.data.labels],
						legend: {
							show: true,
							position: "bottom",
							fontFamily: "inherit",
							itemMargin: {
								horizontal: 10,
								vertical: 10,
							},
						},
					},
				};

				chartColumnDataAgeing = {
					height: 280,
					type: "bar",
					series: [
						{
							name: "Balance",
							data: [withinDueBalance, ...transformAndSumData(accountsReceivable.data.message.chart.data.datasets)],
						},
					],
					options: {
						chart: {
							id: "accounts-receivable-column-ageing",
						},
						plotOptions: {
							bar: {
								borderRadius: 10,
								dataLabels: {
									position: "top",
								},
								distributed: true,
							},
						},
						colors: ["#F7DC6F", "#8BC34A", "#4CAF50", "#E91E63", "#9C27B0", "#03A9F4", "#009688"],
						dataLabels: {
							enabled: true,
							formatter: function (val, opts) {
								return (
									"Rp. " +
									val.toLocaleString("id-ID", {
										style: "currency",
										currency: "IDR",
									})
								);
							},
							offsetY: -20,
							style: {
								fontSize: "12px",
								colors: ["#304758"],
							},
						},
						xaxis: {
							categories: ["Within Due", ...accountsReceivable.data.message.chart.data.labels],
							position: "top",
							axisBorder: {
								show: false,
							},
							axisTicks: {
								show: false,
							},
							crosshairs: {
								fill: {
									type: "gradient",
									gradient: {
										colorFrom: "#D8E3F0",
										colorTo: "#BED1E6",
										stops: [0, 100],
										opacityFrom: 0.4,
										opacityTo: 0.5,
									},
								},
							},
							tooltip: {
								enabled: true,
							},
						},
						yaxis: {
							axisBorder: {
								show: false,
							},
							axisTicks: {
								show: false,
							},
							labels: {
								show: false,
							},
							title: {
								text: "IDR (Rupiah)",
							},
						},
					},
				};
			}
		}

		return {
			accountsReceivable: accountsReceivable.data,
			withinDueBalance,
			overDueBalance,
			totalBalance,
			chartDonutDataAgeing,
			chartColumnDataAgeing,
			top10Data,
			success: true,
			message: "accounts receivables have been loaded!",
		};
	} catch (error) {
		const errorMessage = error.message || "Failed during load accounts receivables!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function getAccountsPayable(params) {
	try {
		const accountsPayable = await axios.get(`${params.api_url}api/method/frappe.desk.query_report.run`, {
			headers: {
				Authorization: `token ${params.api_key}:${params.api_secret}`,
				"Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
				Accept: "application/json",
			},
			params: {
				report_name: "Accounts Payable",
				filters: JSON.stringify({
					company: params.company,
					report_date: params.report_date,
					ageing_based_on: params.based_on,
					range1: 30,
					range2: 60,
					range3: 90,
					range4: 120,
					based_on_payment_terms: 0,
				}),
			},
		});

		let withinDueBalance = 0;
		let overDueBalance = 0;
		let totalBalance = 0;
		let overDuePayablePercentage = 0;
		let chartDonutDataAgeing = {};
		let chartColumnDataAgeing = {};

		if (accountsPayable && accountsPayable.data && accountsPayable.data.message && accountsPayable.data.message.result && params.report_date) {
			const result = accountsPayable.data.message.result;
			const reportDateObj = new Date(params.report_date);

			const withinDueItems = result.filter((item) => item.due_date && new Date(item.due_date) > reportDateObj);
			withinDueBalance = withinDueItems.reduce((acc, curr) => acc + Number(curr.outstanding), 0);

			const overDueItems = result.filter((item) => item.due_date && new Date(item.due_date) < reportDateObj);
			overDueBalance = overDueItems.reduce((acc, curr) => acc + Number(curr.outstanding), 0);

			const outstandingBalance = result.filter((item) => item.outstanding);
			totalBalance = outstandingBalance.reduce((acc, curr) => acc + Number(curr.outstanding), 0);

			overDuePayablePercentage = Math.round((overDueBalance / totalBalance) * 100);

			if (accountsPayable.data.message.chart) {
				chartDonutDataAgeing = {
					height: 328,
					type: "donut",
					series: transformAndSumData(accountsPayable.data.message.chart.data.datasets),
					options: {
						chart: {
							id: "accounts-payable-donut-ageing",
						},
						labels: accountsPayable.data.message.chart.data.labels,
						legend: {
							show: true,
							position: "bottom",
							fontFamily: "inherit",
							labels: {
								colors: "inherit",
							},
							itemMargin: {
								horizontal: 10,
								vertical: 10,
							},
						},
						colors: ["#8BC34A", "#4CAF50", "#E91E63", "#9C27B0", "#03A9F4", "#009688"],
					},
				};

				const dataLabels = accountsPayable.data.message.chart.data.labels.filter(
					(label, index) => transformAndSumData(accountsPayable.data.message.chart.data.datasets)[index] > 0
				);

				chartColumnDataAgeing = {
					height: 280,
					type: "bar",
					series: [
						{
							name: "Balance",
							data: [withinDueBalance, ...transformAndSumData(accountsPayable.data.message.chart.data.datasets).filter((d) => d > 0)],
						},
					],
					options: {
						chart: {
							id: "accounts-payable-column-ageing",
						},
						plotOptions: {
							bar: {
								borderRadius: 10,
								dataLabels: {
									position: "top",
								},
								distributed: true,
							},
						},
						colors: ["#F7DC6F", "#8BC34A", "#4CAF50", "#E91E63", "#9C27B0", "#03A9F4", "#009688"],
						dataLabels: {
							enabled: true,
							formatter: function (val, opts) {
								return (
									"Rp. " +
									val.toLocaleString("id-ID", {
										style: "currency",
										currency: "IDR",
									})
								);
							},
							offsetY: -20,
							style: {
								fontSize: "12px",
								colors: ["#304758"],
							},
						},
						xaxis: {
							categories: ["Within Due", ...dataLabels],
							position: "top",
							axisBorder: {
								show: false,
							},
							axisTicks: {
								show: false,
							},
							crosshairs: {
								fill: {
									type: "gradient",
									gradient: {
										colorFrom: "#D8E3F0",
										colorTo: "#BED1E6",
										stops: [0, 100],
										opacityFrom: 0.4,
										opacityTo: 0.5,
									},
								},
							},
							tooltip: {
								enabled: true,
							},
						},
						yaxis: {
							axisBorder: {
								show: false,
							},
							axisTicks: {
								show: false,
							},
							labels: {
								show: false,
							},
							title: {
								text: "IDR (Rupiah)",
							},
						},
					},
				};
			}
		}

		return {
			accountsPayable: accountsPayable.data,
			withinDueBalance,
			overDueBalance,
			totalBalance,
			chartDonutDataAgeing,
			chartColumnDataAgeing,
			overDuePayablePercentage,
			success: true,
			message: "accounts payables have been loaded!",
		};
	} catch (error) {
		const errorMessage = error.message || "Failed during load accounts payables!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function getProfitAndLoss(params) {
	try {
		if (!params.periodicity) params.periodicity = "Monthly";
		if (!params.selected_view) params.selected_view = "Report";

		console.log("params", params);

		const profitAndLoss = await axios.get(`${params.api_url}api/method/frappe.desk.query_report.run`, {
			headers: {
				Authorization: `token ${params.api_key}:${params.api_secret}`,
				"Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
				Accept: "application/json",
			},
			params: {
				report_name: "Profit and Loss Statement",
				filters: JSON.stringify({
					company: params.company,
					filter_based_on: params.based_on,
					period_start_date: params.period_start_date,
					period_end_date: params.period_end_date,
					from_fiscal_year: params.from_fiscal_year,
					to_fiscal_year: params.to_fiscal_year,
					periodicity: params.periodicity,
					selected_view: params.selected_view,
					include_default_book_entries: 1,
				}),
			},
		});

		console.log("profitAndLoss", profitAndLoss);

		const profitAndLossGrowth = await axios.get(`${params.api_url}api/method/frappe.desk.query_report.run`, {
			headers: {
				Authorization: `token ${params.api_key}:${params.api_secret}`,
				"Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
				Accept: "application/json",
			},
			params: {
				report_name: "Profit and Loss Statement",
				filters: JSON.stringify({
					company: params.company,
					filter_based_on: params.based_on,
					period_start_date: params.period_start_date,
					period_end_date: params.period_end_date,
					from_fiscal_year: params.from_fiscal_year,
					to_fiscal_year: params.to_fiscal_year,
					periodicity: params.periodicity,
					selected_view: "Growth",
					include_default_book_entries: 1,
				}),
			},
		});

		let chartColumnDataMixed = {};
		let chartLineDataGrowth = {};
		let totalIncome = 0;
		let totalExpense = 0;
		let totalNettProfit = 0;
		let totalNettProfitColor = "";
		let growthIncome = [];
		let growthExpense = [];
		let growthNettProfit = [];
		let nestedGrowthAccounts = [];
		let columnGrowthAccounts = [];

		if (profitAndLoss && profitAndLoss.data && profitAndLoss.data.message && profitAndLoss.data.message.result) {
			if (profitAndLoss.data.message.chart) {
				const datasets = profitAndLoss.data.message.chart.data.datasets.map((dataset) => {
					return {
						name: dataset.name,
						data: dataset.values.slice(0, -1),
						type: dataset.name === "Income" || dataset.name === "Expense" ? "column" : "line",
					};
				});

				const labels = profitAndLoss.data.message.chart.data.labels.slice(0, -1);

				chartColumnDataMixed = {
					series: datasets,
					categories: labels,
					options: {
						chart: {
							height: 350,
							type: "line",
							stacked: false,
							zoom: {
								enabled: false,
							},
						},
						dataLabels: {
							enabled: false,
						},
						stroke: {
							width: [1, 1, 4],
						},
						title: {
							show: false,
						},
						xaxis: {
							categories: labels,
						},
						yaxis: [
							{
								seriesName: "Income",
								axisTicks: {
									show: false,
								},
								axisBorder: {
									show: false,
									color: "#008FFB",
								},
								labels: {
									show: false,
									style: {
										colors: "#008FFB",
									},
								},
								title: {
									text: "IDR (Rupiah)",
									style: {
										color: "#008FFB",
									},
								},
								tooltip: {
									enabled: true,
								},
							},
							{
								seriesName: "Expense",
								opposite: true,
								axisTicks: {
									show: true,
								},
								axisBorder: {
									show: false,
									color: "#00E396",
								},
								labels: {
									show: false,
									style: {
										colors: "#00E396",
									},
								},
							},
							{
								seriesName: "Net Profit/Loss",
								opposite: true,
								axisTicks: {
									show: true,
								},
								axisBorder: {
									show: false,
									color: "#FEB019",
								},
								labels: {
									show: false,
									style: {
										colors: "#FEB019",
									},
								},
							},
						],
						tooltip: {
							fixed: {
								enabled: true,
								position: "topLeft", // topRight, topLeft, bottomRight, bottomLeft
								offsetY: 30,
								offsetX: 60,
							},
							y: {
								formatter: function (y) {
									if (typeof y !== "undefined") {
										// Format Rupiah (IDR) menggunakan toLocaleString
										return "Rp " + y.toLocaleString("id-ID"); // Format sebagai IDR
									}
									return y;
								},
							},
						},
						legend: {
							horizontalAlign: "left",
							offsetX: 40,
						},
					},
				};
			}

			if (profitAndLoss.data.message.report_summary) {
				const reportSummary = profitAndLoss.data.message.report_summary.filter(
					(item) => item.label === "Total Income" || item.label === "Total Expense" || item.label === "Net Profit"
				);
				totalIncome = reportSummary.find((item) => item.label === "Total Income").value;
				totalExpense = reportSummary.find((item) => item.label === "Total Expense").value;
				totalNettProfit = reportSummary.find((item) => item.label === "Net Profit").value;
				totalNettProfitColor = reportSummary.find((item) => item.label === "Net Profit").indicator;
			}
		}

		// Growth
		if (profitAndLossGrowth && profitAndLossGrowth.data && profitAndLossGrowth.data.message && profitAndLossGrowth.data.message.result) {
			const totalIncomeData = profitAndLossGrowth.data.message.result.find((data) => data.account_name === "Total Income (Credit)");
			if (totalIncomeData) {
				const { account_name, opening_balance, ...growthIncomeRest } = totalIncomeData;
				growthIncome = growthIncomeRest;
			}

			const totalExpenseData = profitAndLossGrowth.data.message.result.find((data) => data.account_name === "Total Expense (Debit)");
			if (totalExpenseData) {
				const { account_name, opening_balance, ...growthExpenseRest } = totalExpenseData;
				growthExpense = growthExpenseRest;
			}

			const totalNettProfitData = profitAndLossGrowth.data.message.result.find((data) => data.account_name === "'Profit for the year'");
			if (totalNettProfitData) {
				const { account_name, opening_balance, account, warn_if_negative, currency, total, ...growthNettProfitRest } = totalNettProfitData;
				growthNettProfit = growthNettProfitRest;
			}

			const growthIncomeKeys = Object.keys(growthIncome).filter((key) => key.includes("_"));
			const dataIncome = growthIncomeKeys.map((key) => growthIncome[key]);

			const growthExpenseKeys = Object.keys(growthExpense).filter((key) => key.includes("_"));
			const dataExpense = growthExpenseKeys.map((key) => growthExpense[key]);

			const growthNettProfitKeys = Object.keys(growthNettProfit).filter((key) => key.includes("_"));
			const dataNettProfit = growthNettProfitKeys.map((key) => growthNettProfit[key]);

			const labels = profitAndLossGrowth.data.message.chart.data.labels.slice(0, -1);

			chartLineDataGrowth = {
				height: 280,
				series: [
					{
						name: "Income",
						data: [0, ...dataIncome.slice(1)].map((val) => Math.round(val)),
					},
					{
						name: "Expense",
						data: [0, ...dataExpense.slice(1)].map((val) => Math.round(val)),
					},
					{
						name: "Nett Profit/Loss",
						data: [0, ...dataNettProfit.slice(1)].map((val) => Math.round(val)),
					},
				],
				options: {
					chart: {
						type: "line",
						dropShadow: {
							enabled: true,
							color: "#000",
							top: 18,
							left: 7,
							blur: 10,
							opacity: 0.5,
						},
						stacked: false,
						zoom: {
							enabled: false,
						},
					},
					colors: ["#2ECC71", "#FF6384", "#ffa500"],
					dataLabels: {
						enabled: true,
					},
					stroke: {
						curve: "smooth",
					},
					markers: {
						size: 1,
					},
					xaxis: {
						categories: labels,
					},
					yaxis: {
						axisBorder: {
							show: false,
						},
						axisTicks: {
							show: false,
						},
						labels: {
							show: false,
						},
						title: {
							text: "% (Growth)",
						},
					},
					legend: {
						fixed: {
							enabled: true,
							position: "topLeft",
							offsetY: 30,
							offsetX: 60,
						},
					},
				},
			};

			nestedGrowthAccounts = await buildNestedAccounts(profitAndLossGrowth.data.message.result);
			columnGrowthAccounts = await convertFieldFormat(profitAndLossGrowth.data.message.columns);
		}

		return {
			profitAndLoss: profitAndLoss.data,
			chartColumnDataMixed,
			chartLineDataGrowth,
			totalIncome,
			totalExpense,
			totalNettProfit,
			totalNettProfitColor,
			profitAndLossGrowth: profitAndLossGrowth.data,
			nestedGrowthAccounts,
			columnGrowthAccounts,
			success: true,
			message: "profit and loss have been loaded!",
		};
	} catch (error) {
		const errorMessage = error.message || "Failed during load profit and loss!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function getBalanceSheet(params) {
	try {
		if (!params.periodicity) params.periodicity = "Monthly";
		if (!params.selected_view) params.selected_view = "Report";

		const balanceSheet = await axios.get(`${params.api_url}api/method/frappe.desk.query_report.run`, {
			headers: {
				Authorization: `token ${params.api_key}:${params.api_secret}`,
				"Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
				Accept: "application/json",
			},
			params: {
				report_name: "Balance Sheet",
				filters: JSON.stringify({
					company: params.company,
					filter_based_on: params.based_on,
					period_start_date: params.period_start_date,
					period_end_date: params.period_end_date,
					from_fiscal_year: params.from_fiscal_year,
					to_fiscal_year: params.to_fiscal_year,
					periodicity: params.periodicity,
					selected_view: params.selected_view,
					accumulated_values: 1,
					include_default_book_entries: 1,
				}),
			},
		});

		const balanceSheetGrowth = await axios.get(`${params.api_url}api/method/frappe.desk.query_report.run`, {
			headers: {
				Authorization: `token ${params.api_key}:${params.api_secret}`,
				"Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
				Accept: "application/json",
			},
			params: {
				report_name: "Balance Sheet",
				filters: JSON.stringify({
					company: params.company,
					filter_based_on: params.based_on,
					period_start_date: params.period_start_date,
					period_end_date: params.period_end_date,
					from_fiscal_year: params.from_fiscal_year,
					to_fiscal_year: params.to_fiscal_year,
					periodicity: params.periodicity,
					selected_view: "Growth",
					accumulated_values: 1,
					include_default_book_entries: 1,
				}),
			},
		});

		let chartLineData = {};
		let chartLineDataGrowth = {};
		let totalAsset = 0;
		let totalLiability = 0;
		let totalEquity = 0;
		let totalProvisionalProfit = 0;
		let totalProvisionalProfitColor = "";
		let nestedGrowthAccounts = [];
		let columnGrowthAccounts = [];
		let growthAsset = [];
		let growthLiability = [];
		let growthEquity = [];
		let growthProvisionalProfit = [];

		if (balanceSheet && balanceSheet.data && balanceSheet.data.message && balanceSheet.data.message.result) {
			if (balanceSheet.data.message.chart) {
				chartLineData = {
					height: 280,
					series: balanceSheet.data.message.chart.data.datasets.map((dataset) => ({
						name: dataset.name,
						data: dataset.values,
					})),
					options: {
						chart: {
							type: "line",
							dropShadow: {
								enabled: true,
								color: "#000",
								top: 18,
								left: 7,
								blur: 10,
								opacity: 0.5,
							},
							stacked: false,
							zoom: {
								enabled: false,
							},
						},
						colors: ["#2ECC71", "#FF6384", "#ffa500"],
						dataLabels: {
							enabled: false,
						},
						stroke: {
							curve: "smooth",
						},
						markers: {
							size: 1,
						},
						xaxis: {
							categories: balanceSheet.data.message.chart.data.labels,
						},
						yaxis: {
							axisBorder: {
								show: false,
							},
							axisTicks: {
								show: false,
							},
							labels: {
								show: false,
							},
							title: {
								text: "IDR (Rupiah)",
							},
						},
						legend: {
							fixed: {
								enabled: true,
								position: "topLeft",
								offsetY: 30,
								offsetX: 60,
							},
						},
					},
				};
			}

			if (balanceSheet.data.message.report_summary) {
				const reportSummary = balanceSheet.data.message.report_summary.filter(
					(item) =>
						item.label === "Total Asset" ||
						item.label === "Total Liability" ||
						item.label === "Total Equity" ||
						item.label === "Provisional Profit / Loss (Credit)"
				);
				totalAsset = reportSummary.find((item) => item.label === "Total Asset").value;
				totalLiability = reportSummary.find((item) => item.label === "Total Liability").value;
				totalEquity = reportSummary.find((item) => item.label === "Total Equity").value;
				totalProvisionalProfit = reportSummary.find((item) => item.label === "Provisional Profit / Loss (Credit)").value;
				totalProvisionalProfitColor = reportSummary.find((item) => item.label === "Provisional Profit / Loss (Credit)").indicator;
			}
		}

		if (balanceSheetGrowth && balanceSheetGrowth.data && balanceSheetGrowth.data.message && balanceSheetGrowth.data.message.result) {
			const totalAssetData = balanceSheetGrowth.data.message.result.find((data) => data.account_name === "Total Asset (Debit)");
			if (totalAssetData) {
				const { account_name, opening_balance, ...growthAssetRest } = totalAssetData;
				growthAsset = growthAssetRest;
			}

			const totalLiabilityData = balanceSheetGrowth.data.message.result.find((data) => data.account_name === "Total Liability (Credit)");
			if (totalLiabilityData) {
				const { account_name, opening_balance, ...growthLiabilityRest } = totalLiabilityData;
				growthLiability = growthLiabilityRest;
			}

			const totalEquityData = balanceSheetGrowth.data.message.result.find((data) => data.account_name === "Total Equity (Credit)");
			if (totalEquityData) {
				const { account_name, opening_balance, account, warn_if_negative, currency, total, ...growthEquityRest } = totalEquityData;
				growthEquity = growthEquityRest;
			}

			const totalProvisionalProfitData = balanceSheetGrowth.data.message.result.find((data) => data.account_name === "'Provisional Profit / Loss (Credit)'");
			if (totalProvisionalProfitData) {
				const { account_name, opening_balance, account, warn_if_negative, currency, total, ...growthProvisionalProfitRest } = totalProvisionalProfitData;
				growthProvisionalProfit = growthProvisionalProfitRest;
			}

			const growthAssetKeys = Object.keys(growthAsset).filter((key) => key.includes("_"));
			const dataAsset = growthAssetKeys.map((key) => growthAsset[key]);

			const growthLiabilityKeys = Object.keys(growthLiability).filter((key) => key.includes("_"));
			const dataLiability = growthLiabilityKeys.map((key) => growthLiability[key]);

			const growthEquityKeys = Object.keys(growthEquity).filter((key) => key.includes("_"));
			const dataEquity = growthEquityKeys.map((key) => growthEquity[key]);

			const growthProvisionalProfitKeys = Object.keys(growthProvisionalProfit).filter((key) => key.includes("_"));
			const dataProvisionalProfit = growthProvisionalProfitKeys.map((key) => growthProvisionalProfit[key]);

			if (balanceSheetGrowth.data.message.chart) {
				chartLineDataGrowth = {
					height: 280,
					series: [
						{
							name: "Total Asset",
							data: [0, ...dataAsset.slice(1)].map((val) => Math.round(val)),
						},
						{
							name: "Total Liability",
							data: [0, ...dataLiability.slice(1)].map((val) => Math.round(val)),
						},
						{
							name: "Total Equity",
							data: [0, ...dataEquity.slice(1)].map((val) => Math.round(val)),
						},
						{
							name: "Total Provisional Profit/Loss",
							data: [0, ...dataProvisionalProfit.slice(1)].map((val) => Math.round(val)),
						},
					],
					options: {
						chart: {
							type: "line",
							dropShadow: {
								enabled: true,
								color: "#000",
								top: 18,
								left: 7,
								blur: 10,
								opacity: 0.5,
							},
							stacked: false,
							zoom: {
								enabled: false,
							},
						},
						colors: ["#2ECC71", "#FF6384", "#ffa500", "#4682b4"],
						dataLabels: {
							enabled: true,
						},
						stroke: {
							curve: "smooth",
						},
						markers: {
							size: 1,
						},
						xaxis: {
							categories: balanceSheetGrowth.data.message.chart.data.labels,
						},
						yaxis: {
							axisBorder: {
								show: false,
							},
							axisTicks: {
								show: false,
							},
							labels: {
								show: false,
							},
							title: {
								text: "% (Growth)",
							},
						},
						legend: {
							fixed: {
								enabled: true,
								position: "topLeft",
								offsetY: 30,
								offsetX: 60,
							},
						},
					},
				};
			}

			nestedGrowthAccounts = await buildNestedAccounts(balanceSheetGrowth.data.message.result);
			columnGrowthAccounts = await convertFieldFormat(balanceSheetGrowth.data.message.columns);
		}

		return {
			balanceSheet: balanceSheet.data,
			balanceSheetGrowth: balanceSheetGrowth.data,
			chartLineData,
			totalAsset,
			totalLiability,
			totalEquity,
			totalProvisionalProfit,
			totalProvisionalProfitColor,
			chartLineDataGrowth,
			nestedGrowthAccounts,
			columnGrowthAccounts,
			success: true,
			message: "balance sheet have been loaded!",
		};
	} catch (error) {
		const errorMessage = error.message || "Failed during load balance sheet!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}

async function getFinancialRatio(params) {
	try {
		if (!params.periodicity) params.periodicity = "Yearly";
		if (!params.period_start_date) params.period_start_date = new Date().getFullYear() + "-01-01";
		if (!params.period_end_date) params.period_end_date = new Date().getFullYear() + "-12-31";

		const financialRatios = await axios.get(`${params.api_url}api/method/frappe.desk.query_report.run`, {
			headers: {
				Authorization: `token ${params.api_key}:${params.api_secret}`,
				"Content-Type": "application/x-www-form-urlencoded, multipart/form-data",
				Accept: "application/json",
			},
			params: {
				report_name: "Financial Ratios Report",
				filters: JSON.stringify({
					company: params.company,
					period_start_date: params.period_start_date,
					period_end_date: params.period_end_date,
					from_fiscal_year: params.from_fiscal_year,
					to_fiscal_year: params.to_fiscal_year,
					periodicity: params.periodicity,
				}),
			},
		});

		const columns = financialRatios.data.message.columns;
		const fieldFormat = await convertFieldFormat(columns);

		const results = financialRatios.data.message.result || [];

		return {
			financialRatios: financialRatios.data.message || [],
			columns: fieldFormat,
			results,
			success: true,
			message: "Loaded successfully!",
		};
	} catch (error) {
		const errorMessage = error?.message || "Failed to load, please try again!";
		return {
			success: false,
			message: errorMessage,
		};
	}
}
