/**
 * each endpoint will be something like
 * /api/datamart/{name}?{criteria}
 *
 * when getting the current queries we will need to map through the available queries we currently have and send back.
 * example
 *{
 *      "name": "getopenorders",
 *      "endpoint": "/api/datamart/getopenorders",
 *      "description": "Returns open orders based on day count sent over, sDay 15 days in the past eDay 5 days in the future, can be left empty for this default days",
 *      "options": "sDay,eDay"
 *    },
 *
 * when a criteria is password over we will handle it by counting how many were passed up to 3 then deal with each one respectively
 */

import { and, between, inArray, notInArray } from "drizzle-orm";
import { db } from "../db/db.controller.js";
import { invHistoricalData } from "../db/schema/historicalInv.schema.js";
import { prodQuery } from "../prodSql/prodSqlQuery.controller.js";
import {
	type SqlQuery,
	sqlQuerySelector,
} from "../prodSql/prodSqlQuerySelector.utils.js";
import { returnFunc } from "../utils/returnHelper.utils.js";
import { tryCatch } from "../utils/trycatch.utils.js";
import { datamartData } from "./datamartData.utlis.js";

type Data = {
	name: string;
	options: any;
	optionsRequired?: boolean;
	howManyOptionsRequired?: number;
};

const lstDbRun = async (data: Data) => {
	if (data.options) {
		if (data.name === "psiInventory") {
			const ids = data.options.articles.split(",").map((id: any) => id.trim());
			const whse = data.options.whseToInclude
				? data.options.whseToInclude
						.split(",")
						.map((w: any) => w.trim())
						.filter(Boolean)
				: [];

			const locations = data.options.exludeLanes
				? data.options.exludeLanes
						.split(",")
						.map((l: any) => l.trim())
						.filter(Boolean)
				: [];

			const conditions = [
				inArray(invHistoricalData.article, ids),
				between(
					invHistoricalData.histDate,
					data.options.startDate,
					data.options.endDate,
				),
			];

			// only add the warehouse condition if there are any whse values
			if (whse.length > 0) {
				conditions.push(inArray(invHistoricalData.whseId, whse));
			}

			// locations we dont want in the system
			if (locations.length > 0) {
				conditions.push(notInArray(invHistoricalData.location, locations));
			}

			return await db
				.select()
				.from(invHistoricalData)
				.where(and(...conditions));
		}
	}
	return [];
};
export const runDatamartQuery = async (data: Data) => {
	// search the query db for the query by name
	const considerLstDBRuns = ["psiInventory"];

	if (considerLstDBRuns.includes(data.name)) {
		const lstDB = await lstDbRun(data);

		return returnFunc({
			success: true,
			level: "info",
			module: "datamart",
			subModule: "lstDBrn",
			message: `Data for: ${data.name}`,
			data: lstDB,
			notify: false,
		});
	}

	const featureQ = sqlQuerySelector(`featureCheck`) as SqlQuery;

	const { data: fd, error: fe } = await tryCatch(
		prodQuery(featureQ.query, `Running feature check`),
	);

	if (fe) {
		return returnFunc({
			success: false,
			level: "error",
			module: "datamart",
			subModule: "query",
			message: `feature check failed`,
			data: fe as any,
			notify: false,
		});
	}

	// for queries that will need to be ran on legacy until we get the plant updated need to go in here
	const doubleQueries = ["inventory"];
	const sqlQuery = sqlQuerySelector(
		`datamart.${fd.data[0].activated > 0 && !doubleQueries.includes(data.name) ? data.name : `legacy.${data.name}`}`,
	) as SqlQuery;

	// checking if warehousing is as it will start to effect a lot of queries for plants that are not on 2.

	const getDataMartInfo = datamartData.filter((x) => x.endpoint === data.name);

	// const optionsMissing =
	// 	!data.options || Object.keys(data.options).length === 0;

	const isValid =
		Object.keys(data.options ?? {}).length >=
		(getDataMartInfo[0]?.howManyOptionsRequired ?? 0);

	if (getDataMartInfo[0]?.optionsRequired && !isValid) {
		return returnFunc({
			success: false,
			level: "error",
			module: "datamart",
			subModule: "query",
			message: `This query is required to have ${getDataMartInfo[0]?.howManyOptionsRequired} option(s) set in order use it, please add in your option(s) data and try again.`,
			data: [getDataMartInfo[0].options],
			notify: false,
		});
	}

	if (!sqlQuery.success) {
		return returnFunc({
			success: false,
			level: "error",
			module: "datamart",
			subModule: "query",
			message: `Error getting ${data.name} info`,
			data: [sqlQuery.message],
			notify: false,
		});
	}

	// create the query with no changed just to have it here
	let datamartQuery = sqlQuery?.query || "";

	// split the criteria by "," then and then update the query
	if (data.options) {
		switch (data.name) {
			case "activeArticles":
				break;
			case "deliveryByDateRange":
				datamartQuery = datamartQuery
					.replace("[startDate]", `${data.options.startDate}`)
					.replace("[endDate]", `${data.options.endDate}`)
					.replace(
						"--and r.ArticleHumanReadableId in ([articles]) ",
						data.options.articles
							? `and r.ArticleHumanReadableId in (${data.options.articles})`
							: "--and r.ArticleHumanReadableId in ([articles]) ",
					);

				break;
			case "customerInventory":
				datamartQuery = datamartQuery
					.replace(
						"--and IdAdressen",
						`and IdAdressen in (${data.options.customer})`,
					)
					.replace(
						"--and x.IdWarenlager in (0)",
						`${data.options.whseToInclude ? `and x.IdWarenlager in (${data.options.whseToInclude})` : `--and x.IdWarenlager in (0)`}`,
					);
				break;
			case "openOrders":
				datamartQuery = datamartQuery
					.replace("[startDay]", `${data.options.startDay}`)
					.replace("[endDay]", `${data.options.endDay}`);
				break;
			case "inventory":
				datamartQuery = datamartQuery
					.replaceAll(
						"--,l.RunningNumber",
						`${data.options.includeRunningNumbers ? `,l.RunningNumber` : `--,l.RunningNumber`}`,
					)
					.replaceAll(
						"--,l.MachineLocation,l.MachineName,l.ProductionLotRunningNumber as lot",
						`${data.options.lots ? `,l.MachineLocation,l.MachineName,l.ProductionLotRunningNumber as lot` : `--,l.MachineLocation,l.MachineName,l.ProductionLotRunningNumber as lot`}`,
					)
					.replaceAll(
						"--,l.WarehouseDescription,l.LaneDescription",
						`${data.options.locations ? `,l.WarehouseDescription,l.LaneDescription` : `--,l.WarehouseDescription,l.LaneDescription`}`,
					);
				break;
			case "fakeEDIUpdate":
				datamartQuery = datamartQuery.replace(
					"--AND h.CustomerHumanReadableId in (0)",
					`${data.options.address ? `AND h.CustomerHumanReadableId in (${data.options.address})` : `--AND h.CustomerHumanReadableId in (0)`}`,
				);

				break;
			case "forecast":
				datamartQuery = datamartQuery.replace(
					"where DeliveryAddressHumanReadableId in ([customers])",
					data.options.customers
						? `where DeliveryAddressHumanReadableId in (${data.options.customers})`
						: "--where DeliveryAddressHumanReadableId in ([customers])",
				);

				break;
			case "activeArticles2":
				datamartQuery = datamartQuery.replace(
					"and a.HumanReadableId in ([articles])",
					data.options.articles
						? `and a.HumanReadableId in (${data.options.articles})`
						: "--and a.HumanReadableId in ([articles])",
				);

				break;

			case "psiDeliveryData":
				datamartQuery = datamartQuery
					.replace("[startDate]", `${data.options.startDate}`)
					.replace("[endDate]", `${data.options.endDate}`)
					.replace(
						"[articles]",
						data.options.articles ? `${data.options.articles}` : "[articles]",
					);
				break;
			case "productionData":
				datamartQuery = datamartQuery
					.replace("[startDate]", `${data.options.startDate}`)
					.replace("[endDate]", `${data.options.endDate}`)
					.replace(
						"and ArticleHumanReadableId in ([articles])",
						data.options.articles
							? `and ArticleHumanReadableId in (${data.options.articles})`
							: "--and ArticleHumanReadableId in ([articles])",
					);
				break;
			case "psiPlanningData":
				datamartQuery = datamartQuery
					.replace("[startDate]", `${data.options.startDate}`)
					.replace("[endDate]", `${data.options.endDate}`)
					.replace(
						"and p.IdArtikelvarianten in ([articles])",
						data.options.articles
							? `and p.IdArtikelvarianten in (${data.options.articles})`
							: "--and p.IdArtikelvarianten in ([articles])",
					);
				break;
			default:
				return returnFunc({
					success: false,
					level: "error",
					module: "datamart",
					subModule: "query",
					message: `${data.name} encountered an error as it might not exist in LST please contact support if this continues to happen`,
					data: [sqlQuery.message],
					notify: true,
				});
		}
	}

	const { data: queryRun, error } = await tryCatch(
		prodQuery(datamartQuery, `Running datamart query: ${data.name}`),
	);

	if (error) {
		return returnFunc({
			success: false,
			level: "error",
			module: "datamart",
			subModule: "query",
			message: `Data for: ${data.name} encountered an error while trying to get it`,
			data: [error],
			notify: false,
		});
	}

	if (!queryRun.success) {
		return returnFunc({
			success: false,
			level: "error",
			module: "datamart",
			subModule: "query",
			message: queryRun.message,
			data: queryRun.data,
			notify: false,
		});
	}

	return returnFunc({
		success: true,
		level: "info",
		module: "datamart",
		subModule: "query",
		message: `Data for: ${data.name}`,
		data: queryRun.data,
		notify: false,
	});
};