import { executeQuery } from "~/lib/db";
import helper from "~/functions/helper";

async function getDashboard() {
	const qry = await executeQuery({
		query: `
		SELECT
			i.itemCategoryID,
			i.itemSubCategoryID,
			CASE
				WHEN ic.itemCategory='' THEN 'Uncategorized'
				ELSE ic.itemCategory
			END AS itemCategory,
			CASE
				WHEN isc.itemSubCategory='' THEN 'Uncategorized'
				ELSE isc.itemSubCategory
			END AS itemSubCategory,
			sum(i.price) as sumPrice
		FROM
			items i
		LEFT OUTER JOIN
			item_categories ic on i.itemCategoryID = ic.itemCategoryID
		LEFT OUTER JOIN
			item_subcategories isc on i.itemSubCategoryID = isc.itemSubCategoryID
		GROUP BY
			i.itemCategoryID,
			i.itemSubCategoryID,
			ic.itemCategory,
			isc.itemSubCategory
		WITH ROLLUP
	`,
	});
	const data = helper.emptyOrRows(qry);

	return data;
}

async function getDetails() {
	const qry = await executeQuery({
		query: `
		SELECT
			i.itemID,
			i.itemCategoryID,
			i.itemSubCategoryID,
			i.itemGroupID,
			i.itemSubGroupID,
			IFNULL(ic.itemCategory, '(Uncategorized)') as itemCategory,
			IFNULL(isc.itemSubCategory, '(Uncategorized)') as itemSubCategory,
			IFNULL(ig.itemGroupName, '(Uncategorized)') as itemGroupName,
			IFNULL(isg.itemSubGroupName, '(Uncategorized)') as itemSubGroupName,
			m.makerName as maker,
			s.statusName as status,
			i.description,
			i.itemNumber,
			i.price,
			i.priceUnknown
		FROM
			items i
		LEFT OUTER JOIN
			item_categories ic on i.itemCategoryID = ic.itemCategoryID
		LEFT OUTER JOIN
			item_subcategories isc on i.itemSubCategoryID = isc.itemSubCategoryID
		LEFT OUTER JOIN
			item_groups ig on i.itemGroupID = ig.itemGroupID
		LEFT OUTER JOIN
			item_subgroups isg on i.itemSubGroupID = isg.itemSubGroupID
		LEFT OUTER JOIN
			makers m on i.makerID = m.makerID
		LEFT OUTER JOIN
			status s on i.statusID = s.statusID
		ORDER BY
			ic.sortOrder,
			isc.sortOrder,
			ig.sortOrder,
			isg.sortOrder,
			m.makerName,
			i.description,
			i.price
	`,
	});
	const data = helper.emptyOrRows(qry);

	return data;
}

export default {
	getDashboard,
	getDetails,
};