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

export async function getSuitStyles() {
	const query = `
		select
			fullsuitStyleID,
			fullsuitStyle,
			itemSubCategoryID
		from
			fullsuit_styles
		order by
			itemSubCategoryID,
			fullsuitStyle
	`;

	const results = await executeQuery({ query });

	return results;
}

export async function getFinancials(fullsuitID) {
	const queryItemSet = `
		select
			price
		from
			item_sets
		where
			itemSetID=(
				select itemSetID
				from fullsuit
				where fullsuitID=${fullsuitID}
			)
	`;
	const queryCoreItems = `
		select
			sum(price) as price
		from
			items
		where
			itemID in (
				select itemID
				from fullsuit_items
				where fullsuitID=${fullsuitID}
					and itemType='C'
			)
	`;
	const queryAdditionalItems = `
		select
			sum(price) as price
		from
			items
		where
			itemID in (
				select itemID
				from fullsuit_items
				where fullsuitID=${fullsuitID}
					and itemType='A'
			)
	`;

	const itemSet = await executeQuery({ query: queryItemSet });
	const coreItems = await executeQuery({ query: queryCoreItems });
	const additionalItems = await executeQuery({ query: queryAdditionalItems });

	return {
		itemSet: itemSet[0]?.price || 0,
		coreItems: coreItems[0]?.price,
		additionalItems: additionalItems[0]?.price,
	};
}

export async function getList(id = null) {
	let query = `
		select
			fullsuit.fullsuitID,
			fullsuit.hasPhoto,
			fullsuit.itemSetID,
			itemSets.itemSetName,
			fullsuit.armorType,
			case fullsuit.armorType
				when "J" then "Japanese"
				when "E" then "European"
				else "(empty)"
			end as armorTypeLabel,
			fullsuit.bundleID,
			fullsuit.statusID,
			fullsuit.sourceID,
			fullsuit.suitName,
			fullsuit.fullsuitStyleID,
			fullsuit_styles.fullsuitStyle,
			fullsuit.century,
			fullsuit.heraldryClanMon,
			fullsuit.description,
			fullsuit.details,
			fullsuit.lockGeneral,
			fullsuit.lockCore,
			fullsuit.lockAdditional,
			fullsuit.createdDate,
			fullsuit.updatedDate,
			status.statusName,
			makers.makerName,
			sources.sourceName,
			(
				SELECT
					count(itemID)
				FROM
					items
				WHERE
					items.itemSetID > 0
					AND items.itemSetID = fullsuit.itemSetID
			) as countSetItems,
			(
				SELECT
					count(itemID)
				FROM
					fullsuit_items subCCI
				WHERE
					itemType = 'C'
					and subCCI.fullsuitID = fullsuit.fullsuitID
			) as countCoreItems,
			(
				SELECT
					count(itemID)
				FROM
					fullsuit_items subCCA
				WHERE
					itemType = 'A'
					and subCCA.fullsuitID = fullsuit.fullsuitID
			) as countAdditionalItems
		from
			fullsuit
		left outer join
			status on fullsuit.statusID = status.statusID
		left outer join
			makers on fullsuit.makerID = makers.makerID
		left outer join
			sources on fullsuit.sourceID = sources.sourceID
		left outer join
			vw_itemsets itemSets on fullsuit.itemSetID = itemSets.itemSetID
		left outer join
			fullsuit_styles on fullsuit.fullsuitStyleID = fullsuit_styles.fullsuitStyleID
	`;

	if (id !== null) {
		query += ` where fullsuitID=${id}`;
	}

	query += `
		order by
			fullsuit.armorType asc,
			status.statusName asc,
			makers.makerName asc,
			fullsuit.suitName asc
	`;

	const results = await executeQuery({ query });

	return results;
}

export async function getListItemsOnAnySuit() {
	const query = `
		select
			distinct itemID
		from
			fullsuit_items
	`;

	const results = await executeQuery({ query });
	const list = [];

	results.map((x) => list.push(x.itemID));

	return list;
}

export async function getByID(id: string) {
	const suit = await getList(id);

	return suit[0] || {};
}