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

export async function getDashboard() {
	const qry = await executeQuery({
		query: `
            SELECT
                (select count(itemID) from items where itemCategoryID=1 and itemSubCategoryID=1) as countEuropeanWeapons,
                (select count(itemID) from items where itemCategoryID=1 and itemSubCategoryID=4) as countJapaneseWeapons,
                (select count(itemID) from items where itemCategoryID=1 and itemSubCategoryID=7) as countChineseWeapons,
                (select count(itemID) from items where itemCategoryID=1 and itemSubCategoryID=8) as countFantasyWeapons,

                (select count(itemID) from items where itemCategoryID=2 and itemSubCategoryID=2 and itemGroupID=53) as countEuropeanArmorFullSuits,
                (select count(itemID) from items where itemCategoryID=2 and itemSubCategoryID=5 and itemGroupID=52) as countJapaneseArmorFullSuits,
                (select count(itemID) from items where itemCategoryID=2 and itemSubCategoryID=2 and itemGroupID <> 53) as countEuropeanArmorPieces,
                (select count(itemID) from items where itemCategoryID=2 and itemSubCategoryID=5 and itemGroupID <> 52) as countJapaneseArmorPieces,

                (select count(itemID) from items where itemCategoryID=3 and itemSubCategoryID=3) as countEuropeanClothing,
                (select count(itemID) from items where itemCategoryID=3 and itemSubCategoryID=6) as countJapaneseClothing,

                (select count(itemID) from items where itemCategoryID=4 and itemSubCategoryID=9) as countEuropeanDecor,
                (select count(itemID) from items where itemCategoryID=4 and itemSubCategoryID=10) as countJapaneseDecor,

                (select count(libraryBookID) from library) as countLibraryBooks,

                (select count(wishlistID) from wishlist) as countWishlistItems
        `,
	});

	const data = helper.emptyOrRows(qry);

	return data[0];
}