import fs from "fs";
import _ from "lodash";

import { executeQuery } from "~/lib/db";
import helper from "~/functions/helper";
import { responseMessage } from "~/lib/db";
// import { addLogEntry } from '~/functions/log'
import { getImagePathThumb } from "~/functions/isLinuxServer";
// import { mysqlDateFormat } from "~/functions/dates";

// import JapaneseWeaponSwordSvc from "~/services/japanese/weapon/sword";
// import JapaneseArmorFullSuitSvc from "~/services/japanese/armor/fullsuit"
// import JapaneseArmorKabutoSvc from "~/services/japanese/armor/kabuto"
// import JapaneseArmorMempoSvc from "~/services/japanese/armor/mempo"
// import JapaneseArmorDoSvc from "~/services/japanese/armor/do"
// import JapaneseArmorSodeSvc from "~/services/japanese/armor/sode"
// import JapaneseArmorKoteSvc from "~/services/japanese/armor/kote"
// import JapaneseArmorHaidateSvc from "~/services/japanese/armor/haidate"
// import JapaneseArmorSuneateSvc from "~/services/japanese/armor/suneate"

// import EuropeanWeaponSwordSvc from "~/services/european/weapon/sword";

// import cache from "~/functions/cache";

const itemType = "item";

// async function getDetails(itemID) {
// 	const tempItem = await executeQuery({
// 		query: `
// 		SELECT
// 			i.itemID,
// 			i.itemSetID,
// 			i.itemBundleID,
// 			i.itemCategoryID,
// 			i.itemSubCategoryID,
// 			i.itemGroupID,
// 			i.itemSubGroupID,
// 			i.makerID,
// 			i.statusID,
// 			i.sourceID,
// 			i.description,
// 			i.itemNumber,
// 			i.price,
// 			i.priceUnknown,
// 			i.shipping,
// 			i.shippingFree,
// 			i.purchasedDate,
// 			i.purchasedDateNA,
// 			i.shippedDate,
// 			i.shippedDateNA,
// 			i.deliveredDate,
// 			i.deliveredDateNA,
// 			i.lockGeneral,
// 			i.lockPhotos,
// 			i.itemCategory,
// 			i.itemSubCategory,
// 			i.itemGroupName,
// 			i.itemSubGroupName,
// 			i.makerName,
// 			i.statusName,
// 			i.sourceName,
// 			i.primaryPhoto,
// 			ib.itemBundleName
// 		FROM
// 			vw_items i
// 		LEFT OUTER JOIN
// 			vw_itemBundles ib ON i.itemBundleID = ib.itemBundleID
// 		WHERE
// 			i.itemID=${itemID}
// 	`,
// 	});

// 	const item = {
// 		general: tempItem[0],
// 		photos: await getPhotos(itemID),
// 		category: "",
// 	};

// 	// japanese - weapon - sword
// 	if (
// 		item.general.itemCategory.toUpperCase() === "JAPANESE" &&
// 		item.general.itemSubCategory.toUpperCase() === "WEAPONS" &&
// 		item.general.itemGroupName.toUpperCase() === "SWORDS"
// 	) {
// 		// item.category = "japanese/weapon/sword"
// 		item.additionalFields = await JapaneseWeaponSwordSvc.get(itemID);
// 	} else if (
// 		item.general.itemCategory.toUpperCase() === "JAPANESE" &&
// 		item.general.itemSubCategory.toUpperCase() === "ARMOR" &&
// 		item.general.itemGroupName.toUpperCase() === "FULL SUIT"
// 	) {
// 		const qryFullSuit = await executeQuery({
// 			query: `
// 			SELECT
// 				*
// 			FROM
// 				fullsuit
// 			WHERE
// 				itemID=${itemID}
// 		`,
// 		});

// 		if (qryFullSuit.length === 0) {
// 			await executeQuery({
// 				query: `insert into fullsuit (itemID) values (${itemID})`,
// 			});
// 			item.fullsuit = {
// 				itemID: parseInt(itemID, 10),
// 				lockCore: 0,
// 				lockAdditional: 0,
// 			};
// 		} else {
// 			item.fullsuit = qryFullSuit[0];
// 		}

// 		// item.additionalFields = await JapaneseArmorFullSuitSvc.get(itemID)
// 		// item.supplementalItems = await JapaneseArmorFullSuitSvc.getSupplemental(itemID)
// 		// kabuto: await JapaneseArmorKabutoSvc.get(itemID),
// 		// mempo: await JapaneseArmorMempoSvc.get(itemID),
// 		// do: await JapaneseArmorDoSvc.get(itemID),
// 		// sode: await JapaneseArmorSodeSvc.get(itemID),
// 		// kote: await JapaneseArmorKoteSvc.get(itemID),
// 		// haidate: await JapaneseArmorHaidateSvc.get(itemID),
// 		// suneate: await JapaneseArmorSuneateSvc.get(itemID),
// 	} else if (
// 		item.general.itemCategory.toUpperCase() === "EUROPEAN" &&
// 		item.general.itemSubCategory.toUpperCase() === "WEAPONS" &&
// 		item.general.itemGroupName.toUpperCase() === "SWORDS"
// 	) {
// 		// item.category = "japanese/weapon/sword"
// 		item.additionalFields = await EuropeanWeaponSwordSvc.get(itemID);
// 	}

// 	return item;
// }

async function getGeneral(itemID: string) {
	const tempItem = await executeQuery({
		query: `
			SELECT
				i.itemID,
				i.itemSetID,
				i.itemBundleID,
				i.itemCategoryID,
				i.itemSubCategoryID,
				i.itemGroupID,
				i.itemSubGroupID,
				i.makerID,
				i.statusID,
				i.sourceID,
				i.description,
				i.details,
				i.itemNumber,
				i.price,
				i.priceUnknown,
				i.shipping,
				i.shippingFree,
				i.purchasedDate,
				i.purchasedDateNA,
				i.shippedDate,
				i.shippedDateNA,
				i.deliveredDate,
				i.deliveredDateNA,
				i.lockGeneral,
				i.lockPhotos,
				i.itemCategory,
				i.itemSubCategory,
				i.itemGroupName,
				i.itemSubGroupName,
				i.makerName,
				i.statusName,
				i.sourceName,
				i.primaryPhoto,
				ibundle.itemBundleName,
				iset.itemSetName
			FROM
				vw_items i
			LEFT OUTER JOIN
				vw_itembundles ibundle ON i.itemBundleID = ibundle.itemBundleID
			LEFT OUTER JOIN
				vw_itemsets iset ON i.itemSetID = iset.itemSetID
			WHERE
				i.itemID=${itemID}
		`,
	});

	return tempItem[0];
}

// async function getAdditional(itemID: string) {
// 	console.log("services > item > items.ts > getAdditional");
// 	console.log("services > item > items.ts > getAdditional");
// 	console.log("services > item > items.ts > getAdditional");
// 	console.log("services > item > items.ts > getAdditional");
// 	console.log("services > item > items.ts > getAdditional");
// 	console.log("services > item > items.ts > getAdditional");
// 	console.log("services > item > items.ts > getAdditional");
// 	console.log("services > item > items.ts > getAdditional");
// 	console.log("services > item > items.ts > getAdditional");
// 	console.log("services > item > items.ts > getAdditional");
// 	console.log("services > item > items.ts > getAdditional");
// 	console.log("services > item > items.ts > getAdditional");
// 	console.log("services > item > items.ts > getAdditional");
// 	console.log("services > item > items.ts > getAdditional");
// 	console.log("services > item > items.ts > getAdditional");
// 	const query = `
// 		SELECT
// 			itemCategory,
// 			itemSubCategory,
// 			itemGroupName
// 		FROM
// 			vw_items
// 		WHERE
// 			itemID=${itemID}
// 	`;
// 	const tempItem = await executeQuery({ query });
// 	const item = tempItem[0];
// 	const category = item.itemCategory.toUpperCase();
// 	const subcategory = item.itemSubCategory.toUpperCase();
// 	const groupname = item.itemGroupName.toUpperCase();

// 	let returnValue = {};

// 	// japanese - weapon - sword
// 	if (
// 		category === "JAPANESE" &&
// 		subcategory === "WEAPONS" &&
// 		groupname === "SWORDS"
// 	) {
// 		// item.category = "japanese/weapon/sword"
// 		returnValue = await JapaneseWeaponSwordSvc.get(itemID);
// 	} else if (
// 		category === "JAPANESE" &&
// 		subcategory === "ARMOR" &&
// 		groupname === "FULL SUIT"
// 	) {
// 		const qryFullSuit = await executeQuery({
// 			query: `
// 			SELECT
// 				*
// 			FROM
// 				fullsuit
// 			WHERE
// 				itemID=${itemID}
// 		`,
// 		});

// 		if (qryFullSuit.length === 0) {
// 			await executeQuery({
// 				query: `insert into fullsuit (itemID) values (${itemID})`,
// 			});
// 			item.fullsuit = {
// 				itemID: parseInt(itemID, 10),
// 				lockCore: 0,
// 				lockAdditional: 0,
// 			};
// 		} else {
// 			item.fullsuit = qryFullSuit[0];
// 		}

// 		// item.additionalFields = await JapaneseArmorFullSuitSvc.get(itemID)
// 		// item.supplementalItems = await JapaneseArmorFullSuitSvc.getSupplemental(itemID)
// 		// kabuto: await JapaneseArmorKabutoSvc.get(itemID),
// 		// mempo: await JapaneseArmorMempoSvc.get(itemID),
// 		// do: await JapaneseArmorDoSvc.get(itemID),
// 		// sode: await JapaneseArmorSodeSvc.get(itemID),
// 		// kote: await JapaneseArmorKoteSvc.get(itemID),
// 		// haidate: await JapaneseArmorHaidateSvc.get(itemID),
// 		// suneate: await JapaneseArmorSuneateSvc.get(itemID),
// 	} else if (
// 		category === "EUROPEAN" &&
// 		subcategory === "WEAPONS" &&
// 		groupname === "SWORDS"
// 	) {
// 		// item.category = "japanese/weapon/sword"
// 		returnValue = await EuropeanWeaponSwordSvc.get(itemID);
// 	}

// 	return returnValue;
// }

async function getPhotos(itemID: string) {
	// const cacheKey = `itemsPhotos_${itemID}`;

	// console.log('items/getPhotos(', itemID, ')')

	let list = null;

	// if (cache.has(cacheKey)) {
	// console.log('>>>> from cache!')

	// list = cache.get(cacheKey);
	// } else {
	// console.log('>>>> not cached, get fresh data')
	// insert any missing entries where upload worked but SQL insert failed
	const path = getImagePathThumb(itemID);

	if (fs.existsSync(path)) {
		const photos = fs.readdirSync(path);

		photos.forEach(async (file) => {
			await executeQuery({
				query: `
					insert into item_photos (
						itemID, filename
					)
						select ${itemID}, '${file}' from dual

					WHERE NOT EXISTS
					(
						SELECT itemPhotoID
						from item_photos
						where itemID=${itemID}
						and filename='${file}'
						LIMIT 1
					)
				`,
			});
		});
	}

	const rows = await executeQuery({
		query: `
			SELECT
				itemPhotoID,
				itemID,
				filename,
				sortOrder
			FROM
				item_photos
			WHERE
				itemID = ${itemID}
			ORDER BY
				sortOrder asc
		`,
	});

	list = helper.emptyOrRows(rows);

	// cache.set(cacheKey, list);
	// }

	return list;
}

async function getThumbnails() {
	// const cacheKey = `itemsThumbnails`;

	let list = null;

	// if (cache.has(cacheKey)) {
	// list = cache.get(cacheKey);
	// } else {
	const query = `
			SELECT
				itemID,
				filename
			FROM
				vw_itemthumbnails
			WHERE
				filename <> ''
		`;

	const rows = await executeQuery({ query });

	list = helper.emptyOrRows(rows);

	// cache.set(cacheKey, list);
	// }

	return list;
}

async function getList(categoryID = 0, lastVisitDate = "") {
	// console.log("getList(", categoryID, ",", lastVisitDate, ")");

	// console.log("items/getList(), cache.has(itemsList) >>>>", cache.has("itemsList"));
	// const cacheKey = `itemsList_categoryID${categoryID}_lastVisitDate${lastVisitDate.replace("/", "_")}`;
	// let checkDate = "";

	// if (lastVisitDate !== "") {
	// const tempDate = new Date(lastVisitDate);

	// checkDate = mysqlDateFormat(lastVisitDate);
	// tempDate.getFullYear() +
	// "-" +
	// (tempDate.getMonth() + 1).toString().padStart(2, "0") +
	// "-" +
	// tempDate.getDate().toString().padStart(2, "0");
	// }

	// console.log("cache key:", cacheKey);

	let list = null;

	// if (cache.has(cacheKey)) {
	// console.log('>>>> from cache!')

	// list = cache.get(cacheKey);
	// } else {
	// console.log('>>>>hitting the database for data')

	let query = `
			SELECT
				itemID,
				itemSetID,
				itemBundleID,
				itemCategoryID,
				itemCategory,
				itemSubCategoryID,
				itemSubCategory,
				itemGroupID,
				itemGroupName as itemGroup,
				itemSubGroupID,
				itemSubGroupName as itemSubGroup,
				itemType,
				makerID,
				makerName,
				statusID,
				statusName,
				itemNumber,
				description,
				price,
				priceUnknown,
				shipping,
				shippingFree,
				sourceID,
				sourceName,
				primaryPhoto,
				purchasedDate,
				purchasedDateNA,
				shippedDate,
				shippedDateNA,
				deliveredDate,
				deliveredDateNA,
				lockGeneral,
				lockPhotos
			FROM
				vw_items
			WHERE
				1=1
		`;

	if (categoryID > 0) {
		query += ` and itemCategoryID=${categoryID}`;
	}

	if (lastVisitDate !== "") {
		query += ` and (deliveredDate >= '${lastVisitDate}' and deliveredDate != 'N/A')`;
	}

	query += `
			ORDER BY
				categorySortOrder,
				subCategorySortOrder,
				groupSortOrder,
				subGroupSortOrder
		`;

	// console.log("query:", query);

	const rows = await executeQuery({ query });

	list = helper.emptyOrRows(rows);

	// cache.set(cacheKey, list);
	// }

	return list;
}

async function create(itemCategoryID) {
	const sql = `INSERT INTO items (itemCategoryID, createdDate) VALUES (${itemCategoryID}, CURRENT_TIMESTAMP);`;

	const result = await executeQuery({ query: sql });

	// get new cache since we added something
	// console.log('inserted, clear cache')
	// cache.del(`itemsList_${itemCategoryID}`);
	// cache.del("itemsList");

	await getList(itemCategoryID);

	return result.insertId;
}

async function update(id: string, item) {
	const sql = `
		UPDATE
			items
		SET
			itemCategoryID=${item.itemCategoryID},
			itemSubCategoryID=${item.itemSubCategoryID},
			itemGroupID=${item.itemGroupID},
			itemSubGroupID=${item.itemSubGroupID},
			makerID=${item.makerID},
			statusID=${item.statusID},
			sourceID=${item.sourceID},
			description="${item.description}",
			itemNumber="${item.itemNumber}",
			price="${item.price}",
			shipping="${item.shipping}"
		WHERE
			itemID=${item.itemID}
	`;

	const result = await executeQuery({ query: sql });

	// get new cache since we updated something
	// console.log("updated, clear cache");
	// cache.del(`itemsList_${item.itemCategoryID}`);
	// cache.del("itemsList");

	await getList(item.itemCategoryID);

	return responseMessage("update", itemType, result);
}

async function patch(itemID: string, item) {
	let sql = "update items set updatedDate=CURRENT_TIMESTAMP";
	let result = "";

	// console.log("patch item:\n\nitemID", itemID, "\n\nitem", item);

	/*
		TODO: REFACTOR THIS TO HELPER FUNCTION TO TAKE PARAMS AND REDUCE ALL THE COPYPASTA BELOW
	*/

	// common item fields
	if (_.has(item, "itemSetID")) {
		sql += `, itemSetID = ${item.itemSetID}`;
	}
	if (_.has(item, "itemBundleID")) {
		sql += `, itemBundleID = ${item.itemBundleID}`;
	}
	if (_.has(item, "itemCategoryID")) {
		sql += `, itemCategoryID = ${item.itemCategoryID}`;
	}
	if (_.has(item, "itemSubCategoryID")) {
		sql += `, itemSubCategoryID = ${item.itemSubCategoryID}`;
	}
	if (_.has(item, "itemGroupID")) {
		sql += `, itemGroupID = ${item.itemGroupID}`;
	}
	if (_.has(item, "itemSubGroupID")) {
		sql += `, itemSubGroupID = ${item.itemSubGroupID}`;
	}
	if (_.has(item, "makerID")) {
		sql += `, makerID = ${item.makerID}`;
	}
	if (_.has(item, "statusID")) {
		sql += `, statusID = ${item.statusID}`;
	}
	if (_.has(item, "sourceID")) {
		sql += `, sourceID = ${item.sourceID}`;
	}
	if (_.has(item, "description")) {
		sql += `, description='${item.description}'`;
	}
	if (_.has(item, "details")) {
		sql += `, details='${item.details}'`;
	}
	if (_.has(item, "itemNumber")) {
		sql += `, itemNumber='${item.itemNumber}'`;
	}
	if (_.has(item, "price")) {
		sql += `, price = ${item.price}`;
	}
	if (_.has(item, "priceUnknown")) {
		sql += `, priceUnknown = ${item.priceUnknown}`;
	}
	if (_.has(item, "shipping")) {
		sql += `, shipping = ${item.shipping}`;
	}
	if (_.has(item, "shippingFree")) {
		sql += `, shippingFree = ${item.shippingFree}`;
	}

	if (_.has(item, "purchasedDate")) {
		if (item.purchasedDate === null) {
			sql += `, purchasedDate = null`;
		} else {
			sql += `, purchasedDate = '${item.purchasedDate.substr(0, 10)}'`;
		}
	}
	if (_.has(item, "purchasedDateNA")) {
		sql += `, purchasedDateNA = ${item.purchasedDateNA}`;
	}

	if (_.has(item, "shippedDate")) {
		if (item.shippedDate === null) {
			sql += `, shippedDate = null`;
		} else {
			sql += `, shippedDate = '${item.shippedDate.substr(0, 10)}'`;
		}
	}
	if (_.has(item, "shippedDateNA")) {
		sql += `, shippedDateNA = ${item.shippedDateNA}`;
	}

	if (_.has(item, "deliveredDate")) {
		if (item.deliveredDate === null) {
			sql += `, deliveredDate = null`;
		} else {
			sql += `, deliveredDate = '${item.deliveredDate.substr(0, 10)}'`;
		}
	}
	if (_.has(item, "deliveredDateNA")) {
		sql += `, deliveredDateNA = ${item.deliveredDateNA}`;
	}

	if (_.has(item, "lockGeneral")) {
		sql += `, lockGeneral = ${item.lockGeneral}`;
	}
	if (_.has(item, "lockPhotos")) {
		sql += `, lockPhotos = ${item.lockPhotos}`;
	}

	sql += ` where itemID = ${itemID}`;

	// console.log(sql);

	if (sql !== null) {
		result = await executeQuery({ query: sql });
	} else {
		console.log("**************************************************");
		console.log("**************************************************");
		console.log("  UNABLE TO FIND A VALID PATCH METHOD  ");
		console.log("**************************************************");
		console.log("**************************************************");
		result = "No valid parameters found for patch > SERVICES > ITEMS";
	}

	// console.log('item patched, clear the cache')
	// TODO: need to make this intelligent clear cache, not always everything
	// LIKELY SOLUTION: make routing handle for WEAPONS, ARMOR, CLOTHING, and DECOR
	// each gets a specific route, calling shared service
	// then the UI can handle calling each specific one to handle cache invalidation
	// cache.del(`itemsList_0`);
	// cache.del(`itemsList_1`);
	// cache.del(`itemsList_2`);
	// cache.del(`itemsList_3`);
	// cache.del(`itemsList_4`);

	return responseMessage("patch", itemType, result);
}

async function remove(id: string) {
	const result = await executeQuery({
		query: `DELETE FROM items WHERE itemID=${id}`,
	});

	// console.log('item patched, clear the cache')
	// TODO: need to make this intelligent clear cache, not always everything
	// LIKELY SOLUTION: make routing handle for WEAPONS, ARMOR, CLOTHING, and DECOR
	// each gets a specific route, calling shared service
	// then the UI can handle calling each specific one to handle cache invalidation
	// cache.del(`itemsList_0`);
	// cache.del(`itemsList_1`);
	// cache.del(`itemsList_2`);
	// cache.del(`itemsList_3`);
	// cache.del(`itemsList_4`);

	return responseMessage("delete", itemType, result);
}

export default {
	getList,
	getPhotos,
	getThumbnails,
	getGeneral,
	// getAdditional,
	create,
	update,
	remove,
	patch,
};