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

async function getSummary() {
	const rows = await executeQuery({
		query: `
		select
			reactKey,
			itemCategory,
			itemSubCategory,
			itemGroupName,
			itemSubGroupName,
			case
				when itemSubGroupName is null then countSubGroupNull
				else countSubGroupGTZero
			end as qty
		from
			(
				SELECT DISTINCT
					CONCAT(
						IFNULL(items.itemCategoryID, 0),
						'_',
						IFNULL(items.itemSubCategoryID, 0),
						'_',
						IFNULL(items.itemGroupID, 0),
						'_',
						IFNULL(items.itemSubGroupID, 0)
					) as reactKey,
					item_categories.itemCategory,
					item_subcategories.itemSubCategory,
					item_groups.itemGroupName,
					item_subgroups.itemSubGroupName,
					item_categories.sortOrder as icSortOrder,
					item_subcategories.sortOrder as iscSortOrder,
					item_groups.sortOrder as igSortOrder,
					item_subgroups.sortOrder as isgSortOrder,
					(
						select count(itemID)
						from items sub_i
						where sub_i.itemCategoryID = item_categories.itemCategoryID
							and sub_i.itemSubCategoryID = item_subcategories.itemSubCategoryID
							and sub_i.itemGroupID = item_groups.itemGroupID
							and sub_i.itemSubGroupID = 0
					) as countSubGroupNull,
					(
						select count(itemID)
						from items sub_i2
						where sub_i2.itemCategoryID = item_categories.itemCategoryID
							and sub_i2.itemSubCategoryID = item_subcategories.itemSubCategoryID
							and sub_i2.itemGroupID = item_groups.itemGroupID
							and sub_i2.itemSubGroupID = item_subgroups.itemSubGroupID
					) as countSubGroupGTZero
				FROM
					items
				LEFT OUTER JOIN
					item_categories ON items.itemCategoryID = item_categories.itemCategoryID
				LEFT OUTER JOIN
					item_subcategories ON items.itemSubCategoryID = item_subcategories.itemSubCategoryID
				LEFT OUTER JOIN
					item_groups ON items.itemGroupID = item_groups.itemGroupID
				LEFT OUTER JOIN
					item_subgroups ON items.itemSubGroupID = item_subgroups.itemSubGroupID
				WHERE
					items.statusID in (0, 2, 3, 4)
					AND items.itemCategoryID > 0
					AND items.itemSubCategoryID > 0
				GROUP BY
					items.itemCategoryID,
					items.itemSubCategoryID,
					items.itemGroupID,
					items.itemSubGroupID,
					item_categories.sortOrder,
					item_subcategories.sortOrder,
					item_groups.sortOrder,
					item_subgroups.sortOrder,
					itemGroupName,
					itemSubGroupName
			) subQ
		ORDER BY
			icSortOrder,
			iscSortOrder,
			igSortOrder,
			isgSortOrder
	`,
	});

	return helper.emptyOrRows(rows);
}

async function getMakers() {
	const rows = await executeQuery({
		query: `
			SELECT DISTINCT
				vm.makerID,
				vm.makerName,
				(select count(itemID) from items where items.makerID = vm.makerID and items.itemSubCategoryID = 1 and statusID in (0, 2, 3, 4)) as numberMedWeapons,
				(select count(itemID) from items where items.makerID = vm.makerID and items.itemSubCategoryID = 2 and statusID in (0, 2, 3, 4)) as numberMedArmor,
				(select count(itemID) from items where items.makerID = vm.makerID and items.itemSubCategoryID = 3 and statusID in (0, 2, 3, 4)) as numberMedClothing,
				(select count(itemID) from items where items.makerID = vm.makerID and items.itemSubCategoryID = 4 and statusID in (0, 2, 3, 4)) as numberJapWeapons,
				(select count(itemID) from items where items.makerID = vm.makerID and items.itemSubCategoryID = 5 and statusID in (0, 2, 3, 4)) as numberJapArmor,
				(select count(itemID) from items where items.makerID = vm.makerID and items.itemSubCategoryID = 6 and statusID in (0, 2, 3, 4)) as numberJapClothing,
				(select count(itemID) from items where items.makerID = vm.makerID and items.itemSubCategoryID = 7 and statusID in (0, 2, 3, 4)) as numberChinese,
				(select count(itemID) from items where items.makerID = vm.makerID and items.itemSubCategoryID = 8 and statusID in (0, 2, 3, 4)) as numberFantasy
			FROM
				vw_makers vm
			ORDER BY
				vm.makerName
		`,
	});

	return helper.emptyOrRows(rows);
}

export default {
	getSummary,
	getMakers,
};