shared.js

import sql from "mssql";
import states from "./states.js";
import path from "node:path";
import { fileURLToPath } from "node:url";
import { Eta } from "eta";

const DEFAULT_DATE_FORMAT = {
	timeZone: "US/Eastern",
	dateStyle: "short",
};
const DATE_FORMAT = new Intl.DateTimeFormat("en-US", DEFAULT_DATE_FORMAT);
const DATETIME_FORMAT = new Intl.DateTimeFormat("en-US", {
	...DEFAULT_DATE_FORMAT,
	timeStyle: "short",
});

try {
	// Load .env file
	let dotenv = await import("dotenv");
	dotenv.config({
		path: "../.env",
	});
} catch (e) {
	// Do nothing if dotenv package isn't available.
	// This will catch on a serverless function because the dependency needs to be present in the functions
	// package.json as well. But it's only needed for local dev, so it's not installed there.
}
const CRM_URL = parseCrmUrl(process.env.CRM_URL);

export async function invalidate_all_stores() {
	try {
		await dbConnect();

		// Update existing records so that they are invalidated.
		await sql.query`
			update CouponLookupsT
			set invalidated = 1
			from CouponLookupsT
			join StoresT on CouponLookupsT.Store = StoresT.Store
			where
				CouponCode in (select CouponCode from ActiveCouponsT where Active = 1)
				and StoresT.Status = 'Open'
		`;

		const queryResult = await sql.query`
			select
				StoresT.Store,
				ActiveCouponsT.CouponCode
			from StoresT
						 cross join ActiveCouponsT
						 left join CouponLookupsT
											 on StoresT.Store = CouponLookupsT.Store
												 and ActiveCouponsT.CouponCode = CouponLookupsT.CouponCode
			where ActiveCouponsT.Active = 1
				and CouponLookupsT.Store is null
				and StoresT.Status = 'Open'
		`;

		if ((queryResult.recordset ?? []).length > 0) {
			const lookupTable = new sql.Table("CouponLookupsT");
			lookupTable.create = false;
			lookupTable.columns.add("Store", sql.Int, { nullable: false });
			lookupTable.columns.add("CouponCode", sql.NVarChar(255), {
				nullable: true,
			});

			for (const record of queryResult.recordset) {
				lookupTable.rows.add(record.Store, record.CouponCode);
			}

			const request = new sql.Request();
			// Insert new records. This only happens when an active coupon code changes.
			await request.bulk(lookupTable);
		}
	} catch (err) {
		console.error(err);
	}
}

export async function invalidate_store(storeId, couponCodes) {
	try {
		await dbConnect();

		const couponCodesFilter = `'${couponCodes.join(`', '`)}'`;
		// Update existing records so that they are invalidated.

		let request = new sql.Request();
		const res = await request.query(`
			update CouponLookupsT
			set invalidated = 1
			where
				Store = ${storeId}
				and CouponCode in (${couponCodesFilter})
		`);
	} catch (err) {
		console.error(err);
	}
}

export async function lookup_invalidated_stores() {
	await dbConnect();

	const lookupsQueryResult = await sql.query`
		update top (1000) CouponLookupsT
		set Invalidated = 0
		output Inserted.Store, Inserted.CouponCode, Inserted.Fulfilled, Inserted.LastFulfilledDate
		from CouponLookupsT
		where Invalidated = 1
	`;

	const statement = await prepareUpdateCouponStatement(); // Prepare just once, and reuse.
	const insertStoreNoteStatement = await prepareInsertStoreNoteStatement();
	let chunks = arrayChunks(lookupsQueryResult.recordset ?? [], 25);
	for (const chunk of chunks) {
		const promises = [];
		for (const record of chunk) {
			promises.push(
				lookupCouponStore(
					record.Store,
					record.CouponCode,
					record.Fulfilled,
					record.LastFulfilledDate
				)
			);
		}

		let results = await Promise.allSettled(promises);
		for (const { status, value } of results) {
			if (status === "fulfilled") {
				await statement.execute({
					Store: value.storeId,
					CouponCode: value.couponCode,
					Fulfilled: value.couponApplied,
					PreviousFulfilled: value.previousFulfilled,
					Menu: value.menu,
					Adjustment: value.adjustment,
					Net: value.net,
					DateUpdated: value.dateUpdated,
					LastFulfilledDate: value.lastFulfilledDate,
				});
				if (value.couponApplied !== value.previousFulfilled) {
					try {
						let storeNoteMessage = "";
						if (value.couponApplied) {
							storeNoteMessage = `Code ${value.couponCode} accepted`;
						} else {
							storeNoteMessage = `Code ${value.couponCode} was not accepted`;
						}
						await insertStoreNoteStatement.execute({
							dateEntered: formatDate(new Date(), true),
							note: storeNoteMessage,
							storeId: value.storeId,
						});
					} catch (e) {
						console.error(e);
					}
				}
			} else {
				console.error("rejected", value);
			}
		}
	}

	await statement.unprepare();
	await insertStoreNoteStatement.unprepare();

	if (chunks.length > 0) {
		// If there was no data returned from the lookup query then do not process batch end. Otherwise, we'll send an
		// email out every time process-batch is run.
		await processBatchesEnded();
	} else {
		console.log("No invalidated stores to process");
	}
}

export async function lookForStoreChanges() {
	// list of "new" stores to ignore
	const IGNORED_NEW_STORE_IDS = [
		"259033467753805017",
		"test-location",
		"test123",
	];
	const reportDate = formatDate(new Date());

	await dbConnect();
	// fetch all of the stores in the db using a prepared statement
	const storeDataPromise = prepareStoreValidationStatement().then(
		async (statement) => {
			// get all of the stores
			const stores = await statement.execute();
			// start un-preparing the statement to release the connection from the DB, but don't block on it until all other prep logic is done
			// this lets us build the map and unprepare in parallel
			const unpreparePromise = statement.unprepare();
			// convert the array of stores to a map for faster lookup
			/**@typedef {import("./shared").StoreValidationDBStore} StoreValidationDBStore
			 * @type {Map<number, StoreValidationDBStore>}
			 */
			const storesMap = new Map(
				stores.recordset?.map((store) => [store.store, store])
			);

			await unpreparePromise;

			return storesMap;
		}
	);
	const [apiStores, dbStores, franchises] = await Promise.all([
		fetchStoresFromDominosApi(),
		storeDataPromise,
		getAllFranchises(),
	]);

	const visitedStores = new Map();
	/**
	 * @type {FranchiseChangeData[]}
	 */
	const storesWithFranchiseChanges = [];
	/**
	 * @type {DominosApiStore[]}
	 */
	const newStores = [];

	for (const store of apiStores) {
		// if the API store doesn't have a franchiseId
		// assign it to a corporate franchise if applicable.
		if (!store.franchiseId) {
			const corporateFranchiseId = getCorporateFranchise(store);
			if (corporateFranchiseId) {
				store.franchiseId = corporateFranchiseId;
				store.assignedToCorporateFranchise = true;
			}
		}
		const matchedStore = dbStores.get(Number(store.storeId));
		if (!matchedStore) {
			if (
				!store.storeId.toLowerCase().includes("test") &&
				!IGNORED_NEW_STORE_IDS.includes(store.storeId.toLowerCase()) &&
				// only include stores with numeric store #'s because we need them to be integers to insert them into the DB.
				Number.parseInt(store.storeId).toString() === store.storeId
			) {
				store.county = await lookupCountyByAddress(store);
				const newStoreAddress = {
					address: store.line1,
					city: store.city,
					region: store.region,
					zip: store.postalCode,
				};

				const verificationResult = await verifyAddress(newStoreAddress);

				// Ensure verified value is added to the store before insertion
				store.AddressVerified = verificationResult.valid ? 1 : 0;

				newStores.push(store);

				// After adding to the database, process the verification
				await processStoreAddress(store.storeId, newStoreAddress, "new", verificationResult);
			}
		} else {
			visitedStores.set(matchedStore.store);
			if (
				store.line1 !== matchedStore.line1 ||
				store.city !== matchedStore.city ||
				store.region !== matchedStore.region ||
				store.postalCode !== matchedStore.postalCode
			) {
				const updatedStoreAddress = {
					address: store.line1,
					city: store.city,
					region: store.region,
					zip: store.postalCode,
				};

				const verificationResult = await verifyAddress(updatedStoreAddress);

				await processStoreAddress(
					store.storeId,
					updatedStoreAddress,
					"update",
					verificationResult
				);
			}
			if (store.franchiseId && matchedStore.franchiseId !== store.franchiseId) {
				const newFranchiseForStore = franchises.get(store.franchiseId);
				const oldFranchiseForStore = franchises.get(matchedStore.franchiseId);
				storesWithFranchiseChanges.push({
					store: matchedStore,
					oldFranchiseId: matchedStore.franchiseId,
					oldFranchiseAgreed: oldFranchiseForStore?.agreed ?? false,
					newFranchiseId: store.franchiseId,
					newFranchiseAssignedToCorporateId: store.assignedToCorporateFranchise,
					newFranchiseName: newFranchiseForStore?.fullName,
					newFranchiseState: newFranchiseForStore?.state,
					newFranchiseAgreed: newFranchiseForStore?.agreed ?? false,
				});
			}
		}
	}

	const closedStores = Array.from(dbStores.entries()).flatMap(
		([id, dbStore]) => {
			// if the store is open in the DB and it wasn't visited while looping through API data, the store is closed
			if (dbStore.status?.toLowerCase() === "open" && !visitedStores.has(id)) {
				return dbStore;
			} else {
				// if the above conditions are false, return a [] so it gets filtered from the map
				return [];
			}
		}
	);

	/** @type {Array<{franchiseCode: string, franchiseName: string, stores: DominosApiStore[]}>} */
	const insertedFranchises = [];

	/**
	 * This set keeps track of any inserted franchises so there are no repeats
	 * @type {Set<string>} */
	const insertedFranchiseIds = new Set();

	/** @type {Array<{franchiseCode: string, franchiseName: string, error: Error}>} */
	const insertedFranchiseErrors = [];

	/**
	 * @type {Array<{store: DominosApiStore, error: Error}>}
	 */
	let newStoreErrors = [];
	if (newStores.length) {
		newStoreErrors = await saveNewStores(newStores);

		// check if the new store's franchise exists. If not, we need to insert it into the db.
		const newStoresWithNewFranchises = newStores.filter(
			(s) => s.franchiseId && !franchises.has(s.franchiseId)
		);
		if (newStoresWithNewFranchises.length) {
			const insertFranchiseStatement =
				await prepareInsertNewFranchiseStatement();
			const insertFranchiseNoteStatement =
				await prepareInsertFranchiseNoteStatement();

			// if the store has a franchise code that's not in the DB, insert it with a placeholder name
			for (const storeInfo of newStoresWithNewFranchises) {
				if (insertedFranchiseIds.has(storeInfo.franchiseId)) {
					const matchingFranchise = insertedFranchises.find(
						(f) => f.franchiseCode === storeInfo.franchiseId
					);
					// add this stores to the matchingFranchise's stores list
					if (matchingFranchise) {
						matchingFranchise.stores ??= [];
						matchingFranchise.stores.push(storeInfo);
					}
					continue;
				}
				let newFranchiseeName = `Franchisee ${storeInfo.franchiseId}`;
				try {
					await insertFranchiseStatement.execute({
						franchiseCode: storeInfo.franchiseId,
						fullName: newFranchiseeName,
						dateUpdated: reportDate,
					});
					insertedFranchises.push({
						franchiseCode: storeInfo.franchiseId,
						franchiseName: newFranchiseeName,
						stores: [storeInfo],
					});
					insertedFranchiseIds.add(storeInfo.franchiseId);
				} catch (e) {
					insertedFranchiseErrors.push({
						franchiseCode: storeInfo.franchiseId,
						franchiseName: newFranchiseeName,
						error: e,
					});
					continue;
				}
				try {
					await insertFranchiseNoteStatement.execute({
						note: `Franchise ${newFranchiseeName} created with Franchise Code ${storeInfo.franchiseId} for Store ${storeInfo.storeId}`,
						dateUpdated: formatDate(new Date(), true),
						franchiseCode: storeInfo.franchiseId,
					});
				} catch (e) {
					console.error("error inserting franchise note for new store", e);
				}
			}
			await insertFranchiseStatement.unprepare();
			await insertFranchiseNoteStatement.unprepare();
		}
	}

	/**
	 *
	 * @type {Array<{franchiseChangeInfo: FranchiseChangeData, error: Error}>}
	 *  */
	let updatedStoreErrors = [];

	if (storesWithFranchiseChanges.length) {
		const franchiseUpdateResults = await updateFranchiseDataForStores(
			storesWithFranchiseChanges,
			insertedFranchiseIds
		);
		updatedStoreErrors = franchiseUpdateResults.updatedStoreErrors;
		for (const insertedFranchise of franchiseUpdateResults.insertedFranchises) {
			// check if we've already included this franchise before
			const matchedFranchise = insertedFranchises.find(
				(f) => f.franchiseCode === insertedFranchise.franchiseCode
			);
			// if we've handled this franchise before had add the stores for this new franchise to the franchise list
			if (matchedFranchise) {
				matchedFranchise.stores.push(...insertedFranchise.stores);
			} else {
				// if we've not inserted this franchise somewhere else, add it to the list of inserted franchises
				insertedFranchises.push(insertedFranchise);
			}
		}
		insertedFranchiseErrors.push(
			...franchiseUpdateResults.insertedFranchiseErrors
		);
	}

	const eta = new Eta({
		views: path.join(path.dirname(fileURLToPath(import.meta.url)), "templates"),
	});

	/**
	 * get only the stores that were successfully saved to send in the report.
	 */
	const insertedNewStores = newStores.filter(
		(s) =>
			!newStoreErrors.some(
				(erroredStores) => erroredStores.store.storeId === s.storeId
			)
	);

	// record only the stores that successfully had their franchises changed to include in the report.
	const updatedStores = storesWithFranchiseChanges.filter(
		(s) =>
			!updatedStoreErrors.some(
				(erroredStore) =>
					erroredStore.franchiseChangeInfo.store.store === s.store.store
			)
	);

	const storeStatusEmails = process.env.EMAIL_TO_STORE_LOOKUPS.split(",").map(
		(email) => ({
			email,
		})
	);
	const [
		newStoresReportEmailResult,
		closedStoresReportEmailResult,
		newFranchiseEmailStatus,
		storeFranchiseChangesEmail,
	] = await Promise.allSettled([
		sendNewStoresEmail({
			eta,
			newStores: insertedNewStores,
			emails: storeStatusEmails,
			runDate: reportDate,
		}),
		sendClosedStoresEmail({
			eta,
			closedStores,
			emails: storeStatusEmails,
			runDate: reportDate,
		}),
		sendNewFranchiseEmail({
			eta,
			newFranchises: insertedFranchises,
			emails: storeStatusEmails,
			runDate: reportDate,
		}),
		sendStoreFranchiseChangesEmail({
			eta,
			storesWithFranchiseChanges: updatedStores,
			emails: storeStatusEmails,
			runDate: reportDate,
		}),
	]);

	// if we couldn't save any stores, send an email to engineering@fostercommerce so they can be reviewed and handled separate from the client.
	if (newStoreErrors.length) {
		const [newStoreErrorReport, newStoreErrorsCsv] = await Promise.all([
			eta.renderAsync("./new-store-errors", {
				storeErrors: newStoreErrors,
				runDate: reportDate,
				formatPhoneNumber,
			}),
			eta.renderAsync("./csv", {
				data: newStoreErrors.map((storeError) => ({
					"Store #": storeError.store.storeId,
					"Franchise Code": storeError.store.franchiseId,
					Phone: formatPhoneNumber(storeError.store.phoneNumber),
					"Address Line 1": `"${storeError.store.line1}"`,
					"Address Line 2": `"${storeError.store.line2}"`,
					City: storeError.store.city,
					State: storeError.store.region,
					Zip: storeError.store.postalCode,
					Latitude: storeError.store?.coordinates?.latitude,
					Longitude: storeError.store?.coordinates?.longitude,
					Timezone: storeError.store?.timezone,
					"Store URL": storeError.store.storeUrl,
					Error: `"${storeError.error.message?.replace(`"`, `'`)}"`,
				})),
			}),
		]);
		await sendEmail(
			[{ email: "[email protected]" }],
			"STP CRM Error: There was an error saving new Domino's stores to the STP DB",
			newStoreErrorReport,
			[{ name: "new-store-errors.csv", data: newStoreErrorsCsv }]
		);
	}
	if (updatedStoreErrors.length) {
		const updatedStoreFranchiseErrorsCsv = await eta.renderAsync("./csv", {
			data: updatedStoreErrors.map((storeError) => ({
				"Store #": storeError.franchiseChangeInfo.store.store,
				"New Franchise Code": storeError.franchiseChangeInfo.newFranchiseId,
				"New Franchise Name":
					storeError.franchiseChangeInfo.newFranchiseName ??
					`Franchisee ${storeError.franchiseChangeInfo.newFranchiseId}`,
				"Old Franchise Id": storeError.franchiseChangeInfo.oldFranchiseId,
				Error: `"${storeError.error.message?.replace(`"`, `'`)}"`,
			})),
		});

		await sendEmail(
			[{ email: "[email protected]" }],
			"STP Status Report Error: There was an error updating stores with new franchise codes",
			`<body><h1>We could not update some stores with new franchise codes. Please see attachment</h1></body>`,
			[
				{
					name: "franchise-change-errors.csv",
					data: updatedStoreFranchiseErrorsCsv,
				},
			]
		);
	}

	if (insertedFranchiseErrors.length) {
		const insertedFranchiseErrorsCsv = await eta.renderAsync("./csv", {
			data: insertedFranchiseErrors.map((franchiseError) => ({
				"New Franchise Code": franchiseError.franchiseCode,
				"New Franchise Name": franchiseError.franchiseName,
				Error: `"${franchiseError.error.message?.replace(`"`, `'`)}"`,
			})),
		});

		await sendEmail(
			[{ email: "[email protected]" }],
			"STP Status Report Error: There was an error inserting new franchises",
			`<body><h1>We could not update some stores with new franchise codes. Please see attachment</h1></body>`,
			[{ name: "new-franchise-errors.csv", data: insertedFranchiseErrorsCsv }]
		);
	}
	// if any of our report email promises are rejected, send an email to engineering@fostercommerce so we can be made aware of it
	if (newStoresReportEmailResult.status === "rejected") {
		console.error(
			"Could not send the new stores report email",
			newStoresReportEmailResult.reason
		);
		await sendEmail(
			"[email protected]",
			`STP Reports: Unable to send 'New Stores' report`,
			String.raw`
		<html>
			<head>
				<title>Error Sending the 'New Stores' report</title>
		    </head>
			<body>
				<h1>We were unable to send the 'New Stores' report for ${reportDate}</h1>
				<p>Error:</p>
				<code>
				   <pre>
					${newStoresReportEmailResult.reason}
					</pre>
				</code>
			</body>
	    </html>`,
			[]
		);
	}
	if (closedStoresReportEmailResult.status === "rejected") {
		console.error(
			"Could not send the closed stores report email",
			closedStoresReportEmailResult.reason
		);

		await sendEmail(
			"[email protected]",
			`STP Reports: Unable to send 'Closed Stores' report`,
			String.raw`
		<html>
			<head>
				<title>Error Sending the 'Closed Stores' report</title>
			</head>
			<body>
				<h1>We were unable to send the 'Closed Stores' report for ${reportDate}</h1>
				<p>Error:</p>
				<code>
					<pre>
						${closedStoresReportEmailResult.reason}
					</pre>
				</code>
			</body>
		</html>
		`
		);
	}
	if (newFranchiseEmailStatus.status === "rejected") {
		console.error(
			"Could not send the new franchises report email",
			newFranchiseEmailStatus.reason
		);
		await sendEmail(
			"[email protected]",
			`STP Reports: Unable to send 'New Franchise' report`,
			String.raw`
			<html>
				<head>
					<title>Error Sending the 'New Franchise' report</title>
				</head>
				<body>
					<h1>We were unable to send the 'New Franchise' report for ${reportDate}</h1>
					<p>Error:</p>
					<code>
						<pre>
							${newFranchiseEmailStatus.reason}
						</pre>
					</code
				</body>
			</html>
			`,
			[]
		);
	}
	if (storeFranchiseChangesEmail.status === "rejected") {
		console.error(
			"Could not send the store franchise change report email",
			storeFranchiseChangesEmail.reason
		);
		await sendEmail(
			"[email protected]",
			`STP Reports: Unable to send 'Store Franchise Changes' report`,
			String.raw`
			<html>
				<head>
					<title>Error sending the 'Store Franchise Changes' report</title>
				</head>
				<body>
					<h1>We were unable to send the 'Store Franchise Changes' report</h1>
					<p>Error:</p>
					<code>
						<pre>${storeFranchiseChangesEmail.reason}</pre>
					</code>
				</body>
			</html>
			`,
			[]
		);
	}
}

/**
 * Send the "new" stores discovered via the Domino's Location API
 * @param {{eta: Eta, newStores: DominosApiStore[], emails: string[], runDate: string}} newStoresEmailArgs
 */
async function sendNewStoresEmail({ eta, newStores, emails, runDate }) {
	if (!newStores.length) {
		return;
	}
	const [newStoresReportEmail, newStoresReportCsv] = await Promise.all([
		await eta.renderAsync("./location-api-new-stores", {
			newStores,
			runDate,
			formatPhoneNumber,
			generateFranchiseCrmUrl,
			generateStoreCrmUrl,
			generateCountyCrmUrl,
		}),
		newStores.length
			? eta.renderAsync("./csv", {
					data: newStores.map((s) => ({
						"Store #": s.storeId,
						"Franchise Code": s.franchiseId,
						"Phone Number": formatPhoneNumber(s.phoneNumber),
						City: s.city,
						State: s.region,
						Address1: s.line1?.replace(",", ""),
						Address2: s.line2?.replace(",", ""),
						Zip: s.postalCode,
						"Store URL": s.storeUrl,
					})),
			  })
			: Promise.resolve(),
	]);
	const emailSubject = newStores.length
		? `New Stores Report ${runDate}: ${newStores.length} New Domino's ${
				newStores.length === 1 ? "Stores" : "Store"
		  } Found`
		: `New Stores Report ${runDate}: No New Stores Found`;
	await sendEmail(
		emails,
		emailSubject,
		newStoresReportEmail,
		newStoresReportCsv
			? [
					{
						name: "new-stores.csv",
						data: newStoresReportCsv,
					},
			  ]
			: []
	);
}

/**
 *
 * @param {{eta: Eta, newStores: DominosApiStore[], emails: string[], runDate: string}} closedStoresEmailParam
 */
async function sendClosedStoresEmail({ eta, closedStores, emails, runDate }) {
	if (!closedStores.length) {
		return;
	}
	const [closedStoresEmailTemplate, closedStoresCsv] = await Promise.all([
		eta.renderAsync("./location-api-closed-stores", {
			closedStores,
			runDate,
			formatPhoneNumber,
			generateFranchiseCrmUrl,
			generateStoreCrmUrl,
		}),
		closedStores.length
			? eta.renderAsync("./csv", {
					data: closedStores.map((store) => ({
						"Store #": store.store,
						City: store.city,
						State: store.state,
						Zip: store.zip,
						Phone: formatPhoneNumber(store.phoneNumber),
						"Franchise Owner": `"${store.franchiseFullName}"`,
					})),
			  })
			: Promise.resolve(),
	]);
	const emailSubject = closedStores.length
		? `Closed Stores Report ${runDate}: ${
				closedStores.length
		  } Potentially Closed ${
				closedStores.length === 1 ? "Store" : "Stores"
		  } Found`
		: `Closed Stores Report ${runDate}: No Stores Closed`;

	await sendEmail(
		emails,
		emailSubject,
		closedStoresEmailTemplate,
		closedStoresCsv
			? [{ name: "closed-stores.csv", data: closedStoresCsv }]
			: []
	);
}

async function sendNewFranchiseEmail({ eta, newFranchises, emails, runDate }) {
	if (!newFranchises?.length) {
		return;
	}
	const [newFranchisesEmailBody, newFranchisesCsv] = await Promise.all([
		eta.renderAsync("./location-api-new-franchises", {
			newFranchises,
			runDate,
			generateFranchiseCrmUrl,
			generateCountyCrmUrl,
			generateStoreCrmUrl,
		}),
		eta.renderAsync("./csv", {
			data: newFranchises.map((franchiseData) => ({
				"New Franchise Name": franchiseData.franchiseName,
				"New Franchise Code": franchiseData.franchiseCode,
			})),
		}),
	]);
	const emailSubject = `New Franchise Report ${runDate}: ${
		newFranchises.length
	} New ${newFranchises.length === 1 ? "Franchise" : "Franchises"} Created`;
	await sendEmail(emails, emailSubject, newFranchisesEmailBody, [
		{ name: "new-franchises.csv", data: newFranchisesCsv },
	]);
}

/**
 *
 * @param {{eta: any, emails: string[], runDate: string, storesWithFranchiseChanges: FranchiseChangeData[]}}
 */
async function sendStoreFranchiseChangesEmail({
	eta,
	storesWithFranchiseChanges,
	emails,
	runDate,
}) {
	if (!storesWithFranchiseChanges.length) {
		return;
	}
	const [franchiseChangeEmailBody, franchiseChangeCsv] = await Promise.all([
		eta.renderAsync("./location-api-store-franchise-changes", {
			storesWithFranchiseChanges,
			runDate,
			generateStoreCrmUrl,
			generateFranchiseCrmUrl,
			generateCountyCrmUrl,
		}),
		storesWithFranchiseChanges.length
			? eta.renderAsync("./csv", {
					data: storesWithFranchiseChanges.map((storeData) => ({
						"Store #": storeData.store.store,
						"Old Franchise Name": storeData.store.franchiseFullName,
						"Old Franchise Code": storeData.oldFranchiseId,
						"New Franchise Name":
							storeData.newFranchiseName ??
							`Franchisee ${storeData.newFranchiseId}`,
						"New Franchise Code": storeData.newFranchiseId,
					})),
			  })
			: Promise.resolve(),
	]);
	const hasAgreementStatusChange = storesWithFranchiseChanges.some(
		(s) => s.newFranchiseAgreed !== s.oldFranchiseAgreed
	);
	const emailSubject = `${
		hasAgreementStatusChange ? "Urgent! " : ""
	}Store Franchise Change Report ${runDate}: ${
		storesWithFranchiseChanges.length
			? `${storesWithFranchiseChanges.length} ${
					storesWithFranchiseChanges.length === 1 ? "Store" : "Stores"
			  } with Franchise Changes`
			: `No Stores with Franchise Changes`
	}`;

	await sendEmail(
		emails,
		emailSubject,
		franchiseChangeEmailBody,
		franchiseChangeCsv
			? [
					{
						name: "stores-with-franchise-changes.csv",
						data: franchiseChangeCsv,
					},
			  ]
			: []
	);
}

/**
 * Save new stores to the db, and return any failures
 * @param {Array<DominosApiStore & {assignedToCorporateFranchise?: true}>} storesList
 * @returns {Promise<Array<{store: DominosApiStore, error: Error}>>} - list of stores not inserted with the associated errors
 */
async function saveNewStores(storesList) {
	const insertStatement = await prepareInsertNewStoresStatement();
	const insertStoreNoteStatement = await prepareInsertStoreNoteStatement();
	const storesNotInserted = [];
	const date = formatDate(new Date(), true);
	for (const store of storesList) {
		try {
			const storeState = states[store.region] ?? null;
			const storeCounty = store.county;
			const storePhoneNumber = trimPhoneNumber(store.phoneNumber);
			await insertStatement.execute({
				storeId: store.storeId,
				address1: store.line1,
				address2: store.line2,
				city: store.city,
				state: storeState,
				zip: store.postalCode,
				phone: storePhoneNumber,
				latitude: store.coordinates?.latitude,
				longitude: store.coordinates?.longitude,
				timezone: store.timezone,
				storeUrl: store.storeUrl,
				franchiseCode: store.franchiseId,
				county: storeCounty,
			});
		} catch (err) {
			console.error(err);
			console.error(
				`[lookForStoreChanges].[saveNewStores] - Skipping inserting new store with id ${store.storeId}`
			);
			storesNotInserted.push({ store, error: err });
		}
		try {
			let note = `Store ${store.storeId} added with no Franchise Code`;
			if (store.assignedToCorporateFranchise && store.franchiseId) {
				note = `Store ${store.storeId} added. Since Domino's API did not have a franchise, it was assigned to ${store.franchiseId}.`;
			} else if (store.franchiseId) {
				note = `Store ${store.storeId} added with Franchise Code ${store.franchiseId}.`;
			}
			await insertStoreNoteStatement.execute({
				dateEntered: date,
				note,
				storeId: store.storeId,
				franchiseCode: store.franchiseId,
			});
		} catch (e) {
			console.error(e);
		}
	}
	// unprepare the statement to make sure the DB connection gets dropped
	await insertStatement.unprepare();
	await insertStoreNoteStatement.unprepare();
	return storesNotInserted;
}

async function processBatchesEnded() {
	const countQueryResult = await sql.query`
		select count(*) as InvalidatedCount
		from CouponLookupsT
		where
			Invalidated = 1
			and CouponCode in (select CouponCode from ActiveCouponsT where Active = 1)
	`;

	let count = countQueryResult.recordset[0].InvalidatedCount;
	if (count === 0) {
		console.log("All batches done");
		const fulfillmentEmailAddresses =
			process.env.EMAIL_TO_ADDRESSES_FULFILLMENT.split(",").map((email) => ({
				email,
			}));
		const reportDate = formatDate(new Date());
		const couponCodes =
			(
				await sql.query`select CouponCode from ActiveCouponsT where Active = 1`
			)?.recordset?.map((c) => c.CouponCode) ?? [];
		const eta = new Eta({
			views: path.join(
				path.dirname(fileURLToPath(import.meta.url)),
				"templates"
			),
		});
		await fulfillmentChangesReport({
			emails: fulfillmentEmailAddresses,
			reportDate,
			eta,
			couponCodes,
		});

		await nonfulfillmentReport({
			emails: fulfillmentEmailAddresses,
			reportDate,
			eta,
			couponCodes,
		});

		await agreedFranchiseeReport({
			eta,
			emails: fulfillmentEmailAddresses,
			reportDate,
		});

		await notAgreedFranchiseeReport({
			eta,
			emails: fulfillmentEmailAddresses,
			reportDate,
		});
		await storesToActivateReport({
			eta,
			emails: fulfillmentEmailAddresses,
			reportDate,
		});
		await triggerDBSync();
	}
}

async function fulfillmentChangesReport({
	eta,
	reportDate,
	emails,
	couponCodes,
}) {
	const couponCodeDiffs = {};
	for (const code of couponCodes) {
		couponCodeDiffs[code] = [];
	}
	const diffQueryResult = await sql.query`
		select
			StoresT.Store,
			FullName,
			FranchiseT.FranchiseID,
			CouponCode,
			Fulfilled,
			PreviousFulfilled,
			CouponLookupsT.DateUpdated
		from CouponLookupsT
			join StoresT on CouponLookupsT.Store = StoresT.Store
			left join FranchiseT on StoresT.FranchiseFK = FranchiseT.FranchiseID
		where
			Fulfilled != PreviousFulfilled
			and StoresT.Status = 'Open'
			and CouponCode in (select CouponCode from ActiveCouponsT where Active = 1)
	`;
	const diffs = diffQueryResult.recordset ?? [];
	for (const record of diffs) {
		// if the coupon code diff value is nullish, initialize it as an array.
		couponCodeDiffs[record.CouponCode] ??= [];
		couponCodeDiffs[record.CouponCode].push(record);
		const fulfilled = record.Fulfilled ? "Yes" : "No";
		const previousFulfilled = record.PreviousFulfilled ? "Yes" : "No";
	}
	const emailPromises = Object.entries(couponCodeDiffs).map(
		async ([couponCode, fulfillmentChanges]) => {
			if (!fulfillmentChanges.length) {
				return;
			}
			const template = await eta.renderAsync(
				"./coupon-fulfillment-changes-report.eta",
				{
					couponCode,
					fulfillmentChanges,
					runDate: reportDate,
					generateStoreCrmUrl,
					generateFranchiseCrmUrl,
					formatDate,
				}
			);
			const csv = fulfillmentChanges.length
				? await eta.renderAsync("./csv", {
						data: fulfillmentChanges.map((c) => ({
							Store: c.Store,
							"Full Name": `"${c.FullName}"`,
							CouponCode: c.CouponCode,
							Fulfilled: c.Fulfilled ? "Yes" : "No",
							"Previously Fulfilled": c.PreviousFulfilled ? "Yes" : "No",
							"Date Updated": c.DateUpdated,
						})),
				  })
				: undefined;
			const emailSubject = `Coupon ${couponCode} Fulfillment Report ${reportDate}: ${
				fulfillmentChanges.length > 1
					? `${fulfillmentChanges.length} changes`
					: fulfillmentChanges.length
					? "1 change"
					: "No changes"
			}`;
			return sendEmail(
				emails,
				emailSubject,
				template,
				csv ? [{ name: `coupon-${couponCode}-changes.csv`, data: csv }] : []
			);
		}
	);

	await Promise.all(emailPromises);
}

async function nonfulfillmentReport({ eta, emails, reportDate, couponCodes }) {
	const nonfulfillmentQueryResult = await sql.query`
		SELECT
			CouponLookupsT.CouponCode, FranchiseT.FullName, FranchiseT.FranchiseID, StoresT.Store, StoresT.Address1, StoresT.City, StoresT.State, StoresT.Zip, StoresT.County
		FROM CouponLookupsT
			LEFT JOIN StoresT ON CouponLookupsT.Store = StoresT.Store
			LEFT JOIN FranchiseT ON StoresT.FranchiseFK = FranchiseT.FranchiseID
			INNER JOIN ActiveCouponsT ON ActiveCouponsT.CouponCode = CouponLookupsT.CouponCode
		WHERE StoresT.Active=1
			AND CouponLookupsT.DateUpdated Is Not Null
			AND CouponLookupsT.Fulfilled = 0
			AND ActiveCouponsT.Active = 1
			AND StoresT.Status = 'Open'
		ORDER BY CouponLookupsT.CouponCode, FranchiseT.FullName
	`;

	const nonFulfillmentsByCoupon = {};
	for (const code of couponCodes) {
		nonFulfillmentsByCoupon[code] = [];
	}

	const nonfulfillments = nonfulfillmentQueryResult.recordset ?? [];
	for (const record of nonfulfillments) {
		nonFulfillmentsByCoupon[record.CouponCode] ??= [];
		nonFulfillmentsByCoupon[record.CouponCode].push(record);
	}

	const emailPromises = Object.entries(nonFulfillmentsByCoupon).map(
		async ([couponCode, couponNonfulfillments]) => {
			if (!couponNonfulfillments.length) {
				return;
			}
			const [emailBody, csv] = await Promise.all([
				eta.renderAsync("./coupon-nonfulfillment-report", {
					couponCode,
					nonfulfillments: couponNonfulfillments,
					runDate: reportDate,
					generateStoreCrmUrl,
					generateFranchiseCrmUrl,
					generateCountyCrmUrl,
					formatDate,
				}),
				couponNonfulfillments.length
					? eta.renderAsync("./csv", {
							data: couponNonfulfillments.map((f) => ({
								"Coupon Code": f.CouponCode,
								"Full Name": f.FullName,
								Store: f.Store,
								"Address 1": f.Address1,
								City: f.City,
								County: f.County,
								State: f.State,
								Zip: f.Zip,
							})),
					  })
					: Promise.resolve(),
			]);
			const emailSubject = `Coupon ${couponCode} Nonfulfillment Report ${reportDate}: ${
				couponNonfulfillments.length === 1
					? `1 store did not accept the coupon`
					: `${couponNonfulfillments.length} stores did not accept the coupon`
			}`;
			return sendEmail(
				emails,
				emailSubject,
				emailBody,
				csv
					? [{ name: `coupon-${couponCode}-nonfulfillments.csv`, data: csv }]
					: []
			);
		}
	);

	await Promise.all(emailPromises);
}

function groupFranchisees(recordset) {
	return (recordset ?? []).reduce((acc, record) => {
		let franchises = acc[record.CouponCode] || {};
		let franchise = franchises[record.FranchiseID] || [];
		franchise.push(record);

		return {
			...acc,
			[record.CouponCode]: {
				...franchises,
				[record.FranchiseID]: franchise,
			},
		};
	}, {});
}

async function agreedFranchiseeReport({ eta, reportDate, emails }) {
	const franchiseeAgreedStoresResult = await sql.query`
		select CouponLookupsT.CouponCode,
			FranchiseT.FranchiseID,
			FranchiseT.FullName,
			FranchiseT.Agreed,
			CouponLookupsT.Fulfilled,
			StoresT.Store,
			StoresT.Address1, StoresT.City, StoresT.State, StoresT.Zip, StoresT.County
		from FranchiseT
			join StoresT on StoresT.FranchiseFK = FranchiseT.FranchiseID
			left join CouponLookupsT on StoresT.Store = CouponLookupsT.Store
			join ActiveCouponsT on CouponLookupsT.CouponCode = ActiveCouponsT.CouponCode
		where
			FranchiseT.Agreed = 1
			and StoresT.Active = 1
			and ActiveCouponsT.Active = 1
			and StoresT.Status = 'Open'
		order by ActiveCouponsT.CouponCode,
			FranchiseT.FranchiseID
	`;

	const couponCodes = groupFranchisees(franchiseeAgreedStoresResult.recordset);

	const emailPromises = Object.entries(couponCodes).map(
		async ([couponCode, agreedFranchises]) => {
			const franchisesWithUnfulfilledCoupons = [];
			for (const [franchise, stores] of Object.entries(agreedFranchises)) {
				let notFulfilledCount = 0;
				const totalStoreCount = stores.length;
				const fullName = stores.at(0)?.FullName;
				const notFulfilledStores = [];
				for (const store of stores) {
					if (!store.Fulfilled) {
						notFulfilledCount++;
						notFulfilledStores.push(store);
					}
				}
				if (notFulfilledCount) {
					franchisesWithUnfulfilledCoupons.push({
						notFulfilledCount,
						fullName,
						franchiseId: franchise,
						totalStoreCount,
						notFulfilledStores,
						stores,
					});
				}
			}
			if (!franchisesWithUnfulfilledCoupons.length) {
				return;
			}
			const [emailMessage, csv] = await Promise.all([
				eta.renderAsync("./agreed-franchises-fulfillment-report", {
					couponCode,
					runDate: reportDate,
					agreedFranchises: franchisesWithUnfulfilledCoupons,
					generateStoreCrmUrl,
					generateFranchiseCrmUrl,
					generateCountyCrmUrl,
				}),
				eta.renderAsync("./csv", {
					data: Object.values(agreedFranchises).map((store) => ({
						"Coupon Code": store.CouponCode,
						"Franchise ID": store.FranchiseID,
						"Full Name": store.FullName,
						Fulfilled: store.fulfilled ? "Yes" : "No",
						Store: store.Store,
						Address1: store.Address1,
						City: store.City,
						County: store.County,
						State: store.State,
						Zip: store.Zip,
					})),
				}),
			]);
			const emailSubject = `Coupon ${couponCode} Agreed Franchisees Report: ${reportDate}`;

			return sendEmail(emails, emailSubject, emailMessage, [
				{ name: `coupon-${couponCode}-agreed-franchises.csv`, data: csv },
			]);
		}
	);

	await Promise.all(emailPromises);
}

/**
 * Sends a report of inactive stores of agreed franchises that had fulfillments
 * These are stores that might be good candidates to mark as active
 * @param {{eta: Eta, reportDate: string, emails: Array<{email: string}>}} args
 * @returns {Promise<void>}
 */
async function storesToActivateReport({ eta, reportDate, emails }) {
	const storesToActivateResult = await sql.query`
		select CouponLookupsT.CouponCode,
			FranchiseT.FranchiseID,
			FranchiseT.FullName,
			FranchiseT.Agreed,
			CouponLookupsT.Fulfilled,
			StoresT.Store,
			StoresT.Address1,
			StoresT.City,
			StoresT.State,
			StoresT.Zip,
			StoresT.County,
			StoresT.Active
		from FranchiseT
			JOIN StoresT on StoresT.FranchiseFK = FranchiseT.FranchiseID
			LEFT JOIN CouponLookupsT on StoresT.Store = CouponLookupsT.Store
			JOIN ActiveCouponsT on CouponLookupsT.CouponCode = ActiveCouponsT.CouponCode
		where
			FranchiseT.Agreed = 1
			AND StoresT.Active = 0
			AND ActiveCouponsT.Active = 1
			AND StoresT.Status = 'Open'
			AND EXISTS (SELECT 1
				FROM CouponLookupsT
				INNER JOIN ActiveCouponsT ON ActiveCouponsT.CouponCode = CouponLookupsT.CouponCode
				WHERE CouponLookupsT.Fulfilled = 1 AND CouponLookupsT.Store = StoresT.Store)
		ORDER BY FranchiseT.FullName,
			ActiveCouponsT.CouponCode
	`;

	if (storesToActivateResult.recordset.length === 0) {
		return;
	}

	const storesToActivate = {};
	for (const store of storesToActivateResult.recordset) {
		if (!storesToActivate[store.Store]) {
			storesToActivate[store.Store] = {
				...store,
				activeCouponCodes: [store.CouponCode],
			};
			delete storesToActivate[store.Store].CouponCode;
		} else if (store.Fulfilled) {
			storesToActivate[store.Store].activeCouponCodes.push(store.CouponCode);
		}
	}
	const sortedStoresToActivate = Object.values(storesToActivate).sort(
		(a, b) => {
			const franchiseNameA = a.FullName;
			const franchiseNameB = b.FullName;
			if (franchiseNameA < franchiseNameB) {
				return -1;
			}
			if (franchiseNameA > franchiseNameB) {
				return 1;
			}

			return a.Store - b.Store;
		}
	);

	const [activatedStoresTemplate, activatedStoresCsv] = await Promise.all([
		eta.renderAsync("./coupon-fulfillment-stores-to-activate-report", {
			stores: sortedStoresToActivate,
			runDate: reportDate,
			generateStoreCrmUrl,
			generateFranchiseCrmUrl,
			generateCountyCrmUrl,
		}),
		eta.renderAsync("/csv", {
			data: storesToActivateResult.recordset.map((s) => ({
				Store: s.Store,
				"Franchise ID": s.FranchiseID,
				"Franchisee Name": `"${s.FullName}"`,
				"Franchisee Agreed": s.Agreed ? "Yes" : "No",
				"Coupon Code": s.CouponCode,
				Active: s.Active ? "Yes" : "No",
				"Coupon Fulfilled": s.Fulfilled ? "Yes" : "No",
				"Address 1": s.Address1,
				City: s.City,
				State: s.State,
				Zip: s.Zip,
				County: s.County,
			})),
		}),
	]);
	await sendEmail(
		emails,
		`Inactive stores with coupon fulfillments report - ${reportDate}`,
		activatedStoresTemplate,
		[{ name: "activated-stores-report.csv", data: activatedStoresCsv }]
	);
}

/**
 * Sends a report of inactive stores of agreed franchises that had fulfillments
 * These are stores that might be good candidates to mark as active
 * @param {{eta: Eta, reportDate: string, emails: Array<{email: string}>}} args
 * @returns {Promise<void>}
 */
async function closedStoresWithFulfillmentsReport({ eta, reportDate, emails }) {
	const closedStoresWithFulfillmentsResult = await sql.query`
		select CouponLookupsT.CouponCode,
			FranchiseT.FranchiseID,
			FranchiseT.FullName,
			FranchiseT.Agreed,
			CouponLookupsT.Fulfilled,
			StoresT.Store,
			StoresT.Address1,
			StoresT.City,
			StoresT.State,
			StoresT.Zip,
			StoresT.County,
			StoresT.Active,
			StoresT.Status
		from FranchiseT
			JOIN StoresT on StoresT.FranchiseFK = FranchiseT.FranchiseID
			LEFT JOIN CouponLookupsT on StoresT.Store = CouponLookupsT.Store
			JOIN ActiveCouponsT on CouponLookupsT.CouponCode = ActiveCouponsT.CouponCode
		where
			ActiveCouponsT.Active = 1
			AND StoresT.Status like 'Closed'
			AND EXISTS (SELECT 1
				FROM CouponLookupsT
				INNER JOIN ActiveCouponsT ON ActiveCouponsT.CouponCode = CouponLookupsT.CouponCode
				WHERE CouponLookupsT.Fulfilled = 1 AND CouponLookupsT.Store = StoresT.Store)
		ORDER BY FranchiseT.FullName,
			ActiveCouponsT.CouponCode
	`;

	if (!closedStoresWithFulfillmentsResult.recordset?.length) {
		return;
	}
	const closedStoresWithFulfillments = {};
	for (const store of closedStoresWithFulfillmentsResult.recordset) {
		if (!closedStoresWithFulfillments[store.Store]) {
			closedStoresWithFulfillments[store.Store] = {
				...store,
				activeCouponCodes: [store.CouponCode],
			};
			delete closedStoresWithFulfillments[store.Store].CouponCode;
		} else if (store.Fulfilled) {
			closedStoresWithFulfillments[store.Store].activeCouponCodes.push(
				store.CouponCode
			);
		}
	}
	const sortedClosedStoresWithFulfillments = Object.values(
		closedStoresWithFulfillments
	).sort((a, b) => {
		const franchiseNameA = a.FullName;
		const franchiseNameB = b.FullName;
		if (franchiseNameA < franchiseNameB) {
			return -1;
		}
		if (franchiseNameA > franchiseNameB) {
			return 1;
		}

		return a.Store - b.Store;
	});

	const [closedStoresTemplate, closedStoresCsv] = await Promise.all([
		eta.renderAsync("./coupon-fulfillment-closed-stores-report", {
			stores: sortedClosedStoresWithFulfillments,
			runDate: reportDate,
			generateStoreCrmUrl,
			generateFranchiseCrmUrl,
			generateCountyCrmUrl,
		}),
		eta.renderAsync("/csv", {
			data: closedStoresWithFulfillmentsResult.recordset.map((s) => ({
				Store: s.Store,
				"Franchise ID": s.FranchiseID,
				"Franchisee Name": `"${s.FullName}"`,
				"Franchisee Agreed": s.Agreed ? "Yes" : "No",
				"Coupon Code": s.CouponCode,
				Active: s.Active ? "Yes" : "No",
				Status: s.Status,
				"Coupon Fulfilled": s.Fulfilled ? "Yes" : "No",
				"Address 1": s.Address1,
				City: s.City,
				State: s.State,
				Zip: s.Zip,
				County: s.County,
			})),
		}),
	]);
	await sendEmail(
		emails,
		`Closed stores with coupon fulfillments report - ${reportDate}`,
		closedStoresTemplate,
		[{ name: "fulfilled-closed-stores-report.csv", data: closedStoresCsv }]
	);
}

async function prepareNearbyStoresZipStatement() {
	const statement = new sql.PreparedStatement();
	statement.input("Store", sql.Int);
	statement.input("Zip", sql.NVarChar(255));
	return statement.prepare(`
		select DISTINCT top(5) FranchiseT.FranchiseID,
			FranchiseT.FullName,
			FranchiseT.Agreed,
			StoresT.Store,
			StoresT.Address1, StoresT.City, StoresT.State, StoresT.Zip, StoresT.County
		from FranchiseT
					 join StoresT on StoresT.FranchiseFK = FranchiseT.FranchiseID
					 left join CouponLookupsT on StoresT.Store = CouponLookupsT.Store
					 join ActiveCouponsT on CouponLookupsT.CouponCode = ActiveCouponsT.CouponCode
		where
			FranchiseT.Agreed = 1
			and StoresT.Active = 1
			and StoresT.Zip = @Zip
			and StoresT.Store != @Store
			and StoresT.Status = 'Open'
	`);
}

async function prepareNearbyStoresCountyStatement() {
	const statement = new sql.PreparedStatement();
	statement.input("Store", sql.Int);
	statement.input("County", sql.NVarChar(255));
	statement.input("State", sql.NVarChar(255));

	await statement.prepare(`
		select DISTINCT top(5) FranchiseT.FranchiseID,
			FranchiseT.FullName,
			FranchiseT.Agreed,
			StoresT.Store,
			StoresT.Address1, StoresT.City, StoresT.State, StoresT.Zip, StoresT.County
		from FranchiseT
					 join StoresT on StoresT.FranchiseFK = FranchiseT.FranchiseID
					 left join CouponLookupsT on StoresT.Store = CouponLookupsT.Store
					 join ActiveCouponsT on CouponLookupsT.CouponCode = ActiveCouponsT.CouponCode
		where
			FranchiseT.Agreed = 1
			and StoresT.Active = 1
			and StoresT.County = @County
			and StoresT.State = @State
			and StoresT.Store != @Store
			and StoresT.Status = 'Open'
	`);
	return statement;
}

/**
 * Prepared statement for fetching all of the store data we need to look for new/closed/sold stores.
 * This is compared to the Domino's API data.
 * @returns {Promise<sql.PreparedStatement>} A Promise with a prepared statement
 */
function prepareStoreValidationStatement() {
	const statement = new sql.PreparedStatement();
	return statement.prepare(
		`SELECT StoresT."Store" as store,
	StoresT."Status" as "status",
	StoresT.Address1 as "address1",
	StoresT.Address2 as "address2",
	StoresT.Zip as "zip",
	StoresT.City as "city",
	StoresT."State" as "state",
	StoresT.County as "county",
	StoresT.Phone_Number as phoneNumber,
	StoresT.FranchiseFK as "franchiseId",
	FranchiseT.FullName as "franchiseFullName",
	FranchiseT.Agreed as "franchiseAgreed"
FROM StoresT
	LEFT JOIN FranchiseT ON FranchiseT.FranchiseID = StoresT.FranchiseFK
WHERE StoresT.State not like 'Puerto%Rico'
	`
	);
}
/**
 *
 * @returns {Promise<sql.PreparedStatement>}
 */
async function prepareInsertNewStoresStatement() {
	const statement = new sql.PreparedStatement();
	statement.input("storeId", sql.Int);
	statement.input("address1", sql.NVarChar(255));
	statement.input("address2", sql.NVarChar(255));
	statement.input("city", sql.NVarChar(255));
	statement.input("state", sql.NVarChar(255));
	statement.input("zip", sql.NVarChar(255));
	statement.input("phone", sql.Char(10));
	statement.input("latitude", sql.Float);
	statement.input("longitude", sql.Float);
	statement.input("timezone", sql.NVarChar(255));
	statement.input("storeUrl", sql.NVarChar(255));
	statement.input("franchiseCode", sql.NVarChar(10));
	statement.input("county", sql.NVarChar(255));
	return statement.prepare(
		`
	INSERT INTO [StoresT] (
							[Store],
							[Address1],
							[Address2],
							[City],
							[State],
							[Zip],
							[County],
							[Phone_Number],
							[Active],
							[StoreLatitude],
							[StoreLongitude],
							[StoreTimezone],
							[StoreURL],
							[FranchiseFK],
							[DateUpdated],
							[Country],
							[Status]
				)
	VALUES (
							@storeId,
							@address1,
							@address2,
							@city,
							@state,
							@zip,
							@county,
							@phone,
							0,
							@latitude,
							@longitude,
							@timezone,
							@storeUrl,
							@franchiseCode,
							GETDATE(),
							'USA',
							'Open'
				)
`
	);
}

/**
 * Gets a list of all of the franchises and converts them to a map.
 * @returns {Promise<Map<string, {franchiseCode: string, fullName: string, state: string, agreed: boolean}>>}
 */
async function getAllFranchises() {
	const allFranchisees = await sql.query`
	SELECT
	[FranchiseT].FranchiseID as franchiseCode,
	[FranchiseT].FullName as fullName,
	[FranchiseT].CompanyState as "state",
	[FranchiseT].Agreed as 'agreed'
	FROM [FranchiseT]
	`;
	return new Map(allFranchisees.recordset.map((f) => [f.franchiseCode, f]));
}

/**
 *
 * Updates the franchise info for stores,
 * inserting any new franchises as necessary and inserting relevant notes into the notes tables.
 *
 * @param {FranchiseChangeData[]} storesWithChangedFranchises
 * @param {Set<string>} insertedFranchiseIds
 * @returns {Promise<FranchiseUpdateResults>} franchiseUpdateResult - contains a list of stores updated,
 * any new franchises, and any errors with this process.
 */
async function updateFranchiseDataForStores(
	storesWithChangedFranchises,
	insertedFranchiseIds
) {
	// wait for each prepared statement sequentially because they all share a connection to the DB
	const updateStoreFranchiseStatement =
		await prepareUpdateStoreFranchiseStatement();
	const insertNewFranchiseStatement =
		await prepareInsertNewFranchiseStatement();
	const insertFranchiseNoteStatement =
		await prepareInsertFranchiseNoteStatement();
	const insertStoreNoteStatement = await prepareInsertStoreNoteStatement();
	let updatedStoreErrors = [];
	let insertedFranchises = [];
	let insertedFranchiseErrors = [];
	// get the current date as a formatted string with the time
	const updateCheckedDateWithTime = formatDate(new Date(), true);
	// get the current date as a formatted string without the time.
	const updateCheckedDateWithoutTime = formatDate(new Date(), false);

	for (const storeInfo of storesWithChangedFranchises) {
		try {
			await updateStoreFranchiseStatement.execute({
				storeId: storeInfo.store.store,
				franchiseCode: storeInfo.newFranchiseId,
				dateUpdated: updateCheckedDateWithoutTime,
			});
		} catch (e) {
			updatedStoreErrors.push({ franchiseChangeInfo: storeInfo, error: e });
			continue;
		}
		// if the new franchise name doesn't exist, use a filler name of `Franchisee ${franchiseCode}`
		let newFranchiseName =
			storeInfo.newFranchiseName ?? `Franchisee ${storeInfo.newFranchiseId}`;

		//insert new franchises for any franchises without a name.
		if (!storeInfo.newFranchiseName) {
			if (insertedFranchiseIds.has(storeInfo.newFranchiseId)) {
				const insertedFranchise = insertedFranchises.find(
					(f) => f.franchiseCode === storeInfo.newFranchiseId
				);
				if (insertedFranchise) {
					insertedFranchise.stores.push(storeInfo.store);
				} else {
					insertedFranchises.push({
						franchiseCode: storeInfo.newFranchiseId,
						franchiseName: newFranchiseName,
						stores: [storeInfo.store],
					});
				}
			} else {
				try {
					await insertNewFranchiseStatement.execute({
						franchiseCode: storeInfo.newFranchiseId,
						fullName: newFranchiseName,
						dateUpdated: updateCheckedDateWithTime,
					});
					insertedFranchises.push({
						franchiseCode: storeInfo.newFranchiseId,
						franchiseName: newFranchiseName,
						stores: [storeInfo.store],
					});
					await insertFranchiseNoteStatement.execute({
						dateEntered: updateCheckedDateWithTime,
						note: `Franchise ${newFranchiseName} with Franchise Code ${storeInfo.newFranchiseId} added for store ${storeInfo.store.store}`,
						franchiseCode: storeInfo.newFranchiseId,
					});
					insertedFranchiseIds.add(storeInfo.newFranchiseId);
				} catch (e) {
					insertedFranchiseErrors.push({
						franchiseCode: storeInfo.newFranchiseId,
						franchiseName: newFranchiseName,
						error: e,
					});
				}
			}
		}
		try {
			// insert a note for the store that the franchise ownership changed.
			let note = `Updated franchisee from ${storeInfo.store.franchiseFullName} to ${newFranchiseName}`;
			if (storeInfo.newFranchiseAssignedToCorporateId) {
				note = `Store ${storeInfo.store} no longer has a franchise in the Domino's Location API. Updated it from ${storeInfo.store.franchiseFullName} to ${newFranchiseName}`;
			}
			await insertStoreNoteStatement.execute({
				dateEntered: updateCheckedDateWithTime,
				note,
				storeId: storeInfo.store.store,
				franchiseCode: storeInfo.newFranchiseId,
			});
		} catch (e) {
			console.error(e);
		}
		try {
			// insert notes for each franchise about the transfer of ownership.
			let note = `${storeInfo.store.franchiseFullName} sold store ${storeInfo.store.store} to ${newFranchiseName}`;
			if (storeInfo.newFranchiseAssignedToCorporateId) {
				note = `${storeInfo.store.franchiseFullName} sold store ${storeInfo.store.store}. The new franchise is unknown, so it was assigned to ${newFranchiseName} based on its location.`;
			}
			await insertFranchiseNoteStatement
				.execute({
					dateEntered: updateCheckedDateWithTime,
					note: `${storeInfo.store.franchiseFullName} sold store ${storeInfo.store.store} to ${newFranchiseName}`,
					franchiseCode: storeInfo.oldFranchiseId,
				})
				.then(() => {
					let note = `${newFranchiseName} bought store ${storeInfo.store.store} from ${storeInfo.store.franchiseFullName}`;
					if (storeInfo.newFranchiseAssignedToCorporateId) {
						note = `Store ${storeInfo.store.store} was sold, but the Domino's Location API had no new franchise to assign it to. Due to the store's location, it has been assigned to ${newFranchiseName}`;
					}
					return insertFranchiseNoteStatement.execute({
						dateEntered: updateCheckedDateWithTime,
						note,
						franchiseCode: storeInfo.newFranchiseId,
					});
				});
		} catch (e) {
			console.error(e);
		}
	}

	// await for each unprepare statement sequentially because they need exclusive DB access to function.
	await updateStoreFranchiseStatement.unprepare();
	await insertNewFranchiseStatement.unprepare();
	await insertFranchiseNoteStatement.unprepare();
	await insertStoreNoteStatement.unprepare();

	return { updatedStoreErrors, insertedFranchises, insertedFranchiseErrors };
}

/**
 *
 * @returns {Promise<sql.PreparedStatement>}
 */
async function prepareUpdateStoreFranchiseStatement() {
	const statement = new sql.PreparedStatement();
	statement.input("storeId", sql.Int);
	statement.input("franchiseCode", sql.NVarChar(10));
	statement.input("dateUpdated", sql.DateTime);

	await statement.prepare(`
		UPDATE StoresT
		SET
			FranchiseFK = @franchiseCode,
			DateUpdated = @dateUpdated
		WHERE Store = @storeId
	`);
	return statement;
}

/**
 * Prepares a statement for entering new franchisees.
 * @returns {Promise<sql.PreparedStatement>} preparedStatement
 */
async function prepareInsertNewFranchiseStatement() {
	const statement = new sql.PreparedStatement();
	statement.input("franchiseCode", sql.NVarChar(10));
	statement.input("fullName", sql.NVarChar(255));
	await statement.prepare(`
		INSERT INTO [FranchiseT] (
			[FranchiseID],
			[FullName]
		)
		VALUES (
			@franchiseCode,
			@fullName
		)
	`);
	return statement;
}

/**
 * Prepares a statement for inserting store notes
 * Used by PF staff to review changes to stores.
 * @returns {Promise<sql.PreparedStatement>} preparedStatement
 */
async function prepareInsertStoreNoteStatement() {
	const statement = new sql.PreparedStatement();
	statement.input("dateEntered", sql.DateTime2(7));
	statement.input("note", sql.NVarChar(sql.MAX));
	statement.input("storeId", sql.Int);
	statement.input("franchiseCode", sql.NChar(10));
	// inserts the notes as AutoBot per Tony's request in a ping
	await statement.prepare(`
		INSERT INTO [StoreNotesT] (
			NotedBy,
			DateEntered,
			Note,
			StoreIDFK,
			NewFranID,
			Source
		)
		VALUES (
			'AutoBot',
			@dateEntered,
			@note,
			@storeId,
			@franchiseCode,
			'sls-function'
		)
	`);

	return statement;
}

/**
 * Prepares a statement for inserting franchise note
 * Used by PF staff to review changes to franchises.
 * @returns {Promise<sql.PreparedStatement>} preparedStatement
 */
async function prepareInsertFranchiseNoteStatement() {
	const statement = new sql.PreparedStatement();
	statement.input("dateEntered", sql.DateTime2(7));
	statement.input("note", sql.NVarChar(sql.MAX));
	statement.input("franchiseCode", sql.NChar(10));
	// inserts the notes as AutoBot per Tony's request in a ping
	await statement.prepare(`
		INSERT INTO [FranNotesT] (
			NotedBy,
			DateEntered,
			Note,
			FranchiseIDFK
			Source
		)
		VALUES (
			'AutoBot',
			@dateEntered,
			@note,
			@franchiseCode
			'sls-function'
		)
	`);

	return statement;
}

async function notAgreedFranchiseeReport({ emails, eta, reportDate }) {
	const franchiseeStoresResult = await sql.query`
		select CouponLookupsT.CouponCode,
					 FranchiseT.FranchiseID,
					 FranchiseT.FullName,
					 FranchiseT.Agreed,
					 StoresT.Store,
					 StoresT.Address1, StoresT.City, StoresT.State, StoresT.Zip, StoresT.County
		from FranchiseT
					 join StoresT on StoresT.FranchiseFK = FranchiseT.FranchiseID
					 left join CouponLookupsT on StoresT.Store = CouponLookupsT.Store
					 join ActiveCouponsT on CouponLookupsT.CouponCode = ActiveCouponsT.CouponCode
		where
			FranchiseT.Agreed = 0
			and StoresT.Active = 1
			and ActiveCouponsT.Active = 1
			and CouponLookupsT.Fulfilled = 1
			and StoresT.Status = 'Open'
		order by ActiveCouponsT.CouponCode,
						 FranchiseT.FranchiseID
	`;

	const couponCodes = groupFranchisees(franchiseeStoresResult.recordset);

	const nearbyStoresZipStatement = await prepareNearbyStoresZipStatement();
	const nearbyStoresCountyStatement =
		await prepareNearbyStoresCountyStatement();

	const fetchNearbyStores = async (store) => {
		const nearbyStoresCountyResponse =
			await nearbyStoresCountyStatement.execute({
				Store: store.Store,
				State: store.State,
				County: store.County,
			});

		const nearbyStoresByCounty = nearbyStoresCountyResponse?.recordset ?? [];

		if (nearbyStoresByCounty.length) {
			return {
				nearbyStores: nearbyStoresByCounty,
				label: `${store.County}, ${store.State}`,
			};
		}

		// if we don't have any in the county, try by the zip code.

		const nearbyStoresByZipResponse = await nearbyStoresZipStatement.execute({
			Store: store.Store,
			Zip: store.Zip,
		});
		const nearbyStoresByZip = nearbyStoresByZipResponse?.recordset ?? [];

		return {
			nearbyStores: nearbyStoresByZip,
			label: store.Zip,
		};
	};
	// Do the not-agreed franchise report synchronously so that there's no collisions with the DB connections while searching for nearby stores
	// the db lib needs each prepared statement to run fully before running another one otherwise it errors
	for (const [couponCode, storesByFranchise] of Object.entries(couponCodes)) {
		const franchises = [];
		for (const [franchiseId, stores] of Object.entries(storesByFranchise)) {
			let storesWithNearbyStores = [];
			for (const store of stores) {
				const { label, nearbyStores } = await fetchNearbyStores(store);
				storesWithNearbyStores.push({
					...store,
					nearbyStores,
					nearbyStoresLabel: label,
				});
			}
			franchises.push({
				franchiseId,
				fullName: stores.at(0)?.FullName,
				stores: storesWithNearbyStores,
			});
		}
		// if there are no fulfilled franchises for this couponCode, go to the next couponCode
		if (!franchises.length) {
			continue;
		}
		const emailBody = await eta.renderAsync(
			"./not-agreed-franchises-fulfillment-report",
			{
				couponCode,
				franchises,
				runDate: reportDate,
				generateStoreCrmUrl,
				generateFranchiseCrmUrl,
				generateCountyCrmUrl,
			}
		);
		const emailSubject = `Not Agreed Franchises Fulfillments for Coupon ${couponCode} - ${reportDate}`;
		await sendEmail(emails, emailSubject, emailBody, []);
	}
	await nearbyStoresZipStatement.unprepare();
	await nearbyStoresCountyStatement.unprepare();
}

async function dbConnect() {
	try {
		const sqlConfig = {
			user: process.env.DB_USER_ID,
			password: process.env.DB_PASSWORD,
			database: process.env.DB_DATABASE,
			server: process.env.DB_SERVER,
			pool: {
				max: 50,
				min: 0,
			},
			options: {
				encrypt: true, // for azure
				trustServerCertificate: true, // change to true for local dev / self-signed certs
			},
		};

		await sql.connect(sqlConfig);
	} catch (err) {
		console.log(`DB Connect error: ${err}`);
		throw err;
	}
}

function sleep(ms) {
	return new Promise((resolve) => setTimeout(resolve, ms));
}

function random(min, max) {
	return Math.floor(Math.random() * (max - min + 1) + min);
}

async function prepareUpdateCouponStatement() {
	const statement = new sql.PreparedStatement();

	statement.input("Store", sql.Int);
	statement.input("CouponCode", sql.NVarChar(255));
	statement.input("Fulfilled", sql.Bit);
	statement.input("PreviousFulfilled", sql.Bit);
	statement.input("Menu", sql.Float);
	statement.input("Adjustment", sql.Float);
	statement.input("Net", sql.Float);
	statement.input("DateUpdated", sql.DateTime);
	statement.input("LastFulfilledDate", sql.DateTime);
	return statement.prepare(`
		UPDATE CouponLookupsT
		SET
			Fulfilled = @Fulfilled,
			PreviousFulfilled = @PreviousFulfilled,
			Menu = @Menu,
			Adjustment = @Adjustment,
			Net = @Net,
			DateUpdated = @DateUpdated,
			LastFulfilledDate = @LastFulfilledDate
		WHERE Store = @Store AND CouponCode = @CouponCode
	`);
}

async function lookupCouponStore(
	storeId,
	couponCode,
	previousFulfilled,
	lastFulfilledDate
) {
	const options = {
		method: "POST",
		headers: {
			Accept: "application/json",
			"Content-Type": "application/json",
		},
		body: JSON.stringify({
			Order: {
				Coupons: [
					{
						Code: couponCode,
					},
				],
				Products: [
					{
						Code: "14SCREEN",
						Qty: 2,
						Options: {
							X: {
								"1/1": "1",
							},
							C: {
								"1/1": "1",
							},
						},
					},
				],
				ServiceMethod: "Carryout",
				SourceOrganizationURI: "order.dominos.com",
				StoreID: storeId,
				Version: "1.0",
			},
		}),
	};

	const res = await fetch(
		"https://order.dominos.com/power/price-order",
		options
	);

	const data = await res.json();
	const couponApplied = data?.Order?.Coupons[0]?.Status === 0;
	const {
		Menu: menu,
		Adjustment: adjustment,
		Net: net,
	} = couponApplied ? data?.Order?.Amounts ?? {} : {};
	const dateUpdated = new Date();
	return {
		storeId,
		couponCode,
		couponApplied,
		previousFulfilled,
		menu,
		adjustment,
		net,
		dateUpdated,
		// if we applied the coupon successfully, set the lastFulfilledDate to now
		// otherwise keep it at the previous value
		lastFulfilledDate: couponApplied ? dateUpdated : lastFulfilledDate,
	};
}

export async function update_store_details() {
	await dbConnect();
	const statement = await prepareUpdateStoreStatement(); // Only prepare once.
	const insertStoreNoteStatement = await prepareInsertStoreNoteStatement();
	let unchangedStoresCount = 0;
	let invalidDataCount = 0;

	let records = arrayChunks(await getStoreRecords(), 5);
	const missingStores = [];
	const changedStores = [];
	for (const chunk of records) {
		const promises = [];
		for (const r of chunk) {
			promises.push(compareAndUpdateStore(r));
		}
		const results = await Promise.allSettled(promises);
		for (const { status, value } of results) {
			if (status === "fulfilled") {
				if (value?.missing) {
					missingStores.push(value.store);
				} else if (value?.invalid) {
					// invalid stores are stores with bad data from the Domino's API, we don't do anything with them for now.
					invalidDataCount += 1;
				} else if (value?.changed) {
					changedStores.push(value);
					await statement.execute(value.data);
					try {
						await insertStoreNoteStatement.execute({
							dateEntered: value.data.DateUpdated,
							note: `Address data updated from the Domino's Order API`,
							storeId: value.data.Store,
						});
					} catch (e) {
						console.error("could not save store note", e);
					}
				} else if (value?.unchanged) {
					unchangedStoresCount += 1;
				} else {
					console.warn("unhandled edge case", value);
				}
			} else {
				console.error("rejected", value);
			}
		}

		await sleep(150);
	}

	const date = formatDate(new Date());
	const eta = new Eta({
		views: path.join(path.dirname(fileURLToPath(import.meta.url)), "templates"),
	});

	const [
		missingStoresEmailBody,
		missingStoresCSV,
		changedStoresEmailBody,
		changedStoresCsv,
	] = await Promise.all([
		missingStores.length
			? eta.renderAsync("./order-api-missing-stores", {
					runDate: date,
					missingStores,
					generateStoreCrmUrl,
					generateFranchiseCrmUrl,
			  })
			: Promise.resolve(),
		missingStores.length
			? eta.renderAsync("./csv", {
					data: missingStores.map((s) => ({
						"Store #": s.Store,
						City: s.City,
						State: s.State,
						Zip: s.Zip,
						Phone: formatPhoneNumber(s.Phone_Number),
						"Franchise Owner": `"${s.FullName}"`,
					})),
			  })
			: Promise.resolve(),
		changedStores.length
			? eta.renderAsync("./order-api-address-validation", {
					runDate: date,
					changedStores,
					generateStoreCrmUrl,
					generateCountyCrmUrl,
					formatPhoneNumber,
			  })
			: Promise.resolve(),
		changedStores.length
			? eta.renderAsync("./csv", {
					data: changedStores.map(({ storeId, oldStore, newStore }) => ({
						Store: storeId,
						"Old Address 1": oldStore.Address1,
						"New Address 1": newStore.Address1,
						"Old City": oldStore.City,
						"New City": newStore.City,
						"Old State": oldStore.State,
						"New State": newStore.State,
						"Old Zip": oldStore.Zip,
						"New Zip": newStore.Zip,
						"Old Phone Number": oldStore.Phone_Number,
						"New Phone Number": newStore.Phone_Number,
						"Old Store URL": oldStore.StoreUrl,
						"New Store URL": newStore.StoreUrl,
					})),
			  })
			: Promise.resolve(),
	]);

	const emailAddresses = process.env.EMAIL_TO_ADDRESSES_STORE_DETAILS.split(
		","
	).map((email) => ({
		email,
	}));

	// only send emails for missing or changed stores if there are values
	await Promise.all([
		missingStores.length
			? sendEmail(
					emailAddresses,
					`Dominos Order API Missing Stores ${date}`,
					missingStoresEmailBody,
					changedStoresCsv
						? [{ name: "missing.csv", data: missingStoresCSV }]
						: []
			  )
			: Promise.resolve(),
		changedStores.length
			? sendEmail(
					emailAddresses,
					`Dominos Order API Changed Addresses ${date}`,
					changedStoresEmailBody,
					changedStoresCsv
						? [{ name: "changed.csv", data: changedStoresCsv }]
						: []
			  )
			: Promise.resolve(),
	]);

	console.table({
		unchanged: unchangedStoresCount,
		changed: changedStores.length,
		invalid: invalidDataCount,
		missing: missingStores.length,
	});
	await statement.unprepare();
	await insertStoreNoteStatement.unprepare();
}

async function compareAndUpdateStore(r) {
	try {
		let p = await lookupStoreProfile(r.Store);
		const crmStoreUrl = generateStoreCrmUrl(r.Store);

		if (!p) {
			console.error(`Store with ID ${r.Store} does not exist`);
			return {
				missing: true,
				store: r,
			};
		} else {
			// Check for invalid data
			if (p.invalidAddressData || p.phone.trim().length < 10) {
				return {
					invalid: `<tr><td><a href="${crmStoreUrl}">${r.Store}</a></td><td>${p.streetName}</td><td>${p.city}</td><td>${p.region}</td><td>${p.postalCode}</td><td>${p.phone}</td></tr>`,
					invalidCsv: `${r.Store},"${p.streetName}",${p.city},${p.region},${p.postalCode},${p.phone}\n`,
				};
			}

			// Check if the county is null
			let county = r.County;
			if (!county) {
				county = await lookupCountyByAddress(r);
				if (!county) {
					console.warn(`Could not determine county for Store ${r.Store}`);
				}
			}

			// Check for data changes
			if (
				r.Address1 !== p.streetName ||
				r.City !== p.city ||
				r.State !== p.region ||
				r.Zip !== p.postalCode ||
				r.Phone_Number !== p.phone ||
				r.County !== county
			) {
				return {
					changed: true,
					newStore: {
						Address1: p.streetName,
						City: p.city,
						State: p.region,
						Zip: p.postalCode,
						Phone_Number: p.phone,
						StoreURL: p.storeUrl,
						County: county,
					},
					oldStore: r,
					storeId: r.Store,
					county,
					data: {
						Store: r.Store,
						Address1: p.streetName,
						City: p.city,
						State: p.region,
						Zip: p.postalCode,
						PhoneNumber: p.phone,
						StoreUrl: p.storeUrl,
						DateUpdated: new Date(),
						County: county,
					},
				};
			}
		}

		return {
			unchanged: true,
		};
	} catch (e) {
		console.error("error", e);
	}
}


async function prepareUpdateStoreStatement() {
	const statement = new sql.PreparedStatement();

	statement.input("Store", sql.Int);
	statement.input("Address1", sql.NVarChar(255));
	statement.input("City", sql.NVarChar(255));
	statement.input("State", sql.NVarChar(255));
	statement.input("Zip", sql.NVarChar(255));
	statement.input("PhoneNumber", sql.Char(10));
	statement.input("StoreUrl", sql.NVarChar(255));
	statement.input("DateUpdated", sql.DateTime);
	statement.input("County", sql.NVarChar(255));

	return statement.prepare(`
		UPDATE StoresT
		SET
			Address1 = @Address1,
			City = @City,
			State = @State,
			Zip = @Zip,
			Phone_Number = @PhoneNumber,
			StoreURL = @StoreUrl,
			DateUpdated = @DateUpdated,
			County = @County
		WHERE Store = @Store
	`);
}

async function getStoreRecords() {
	const queryResult = await sql.query`
		select
			Store,
			Address1,
			City,
			State,
			County,
			Zip,
			Phone_Number,
			StoreLatitude,
			StoreLongitude,
			StoreURL,
			DirectionsURL,
			FranchiseT.FullName,
			FranchiseT.FranchiseID
		from StoresT
		JOIN FranchiseT ON FranchiseT.FranchiseID = StoresT.FranchiseFK
		where Status = 'Open'
	`;

	return queryResult.recordset ?? [];
}

async function lookupStoreProfile(storeId) {
	const options = {
		method: "GET",
		redirect: "manual", // Dominos returns a 302 redirect to their homepage if the storeId is invalid
		headers: {
			Accept: "application/json",
		},
	};

	const res = await fetch(
		`https://order.dominos.com/power/store/${storeId}/profile`,
		options
	);

	if (res.status !== 200) {
		console.warn(await res.text());
		return false;
	} else {
		let {
			Phone,
			StreetName,
			City,
			Region,
			PostalCode,
			StoreCoordinates: { StoreLatitude, StoreLongitude },
		} = await res.json();

		const regionAbbr = Region.toUpperCase().replace(/[^A-Z]/g, "");
		const profile = {
			phone: Phone.replace(/[^0-9 ]/g, ""), // Remove non-digits
			streetName: StreetName,
			city: City,
			regionAbbr,
			region: states[regionAbbr],
			postalCode: formatZipCode(PostalCode),
			storeLatitude: StoreLatitude,
			storeLongitude: StoreLongitude,
			invalidAddressData: false,
		};

		const storeUrl = generateStoreUrl(storeId, profile);
		if (!storeUrl) {
			profile.invalidAddressData = true;
		}
		profile.storeUrl = storeUrl;

		return profile;
	}
}

function getHandle(value) {
	return value
		.replace(/[^a-zA-Z0-9 ]/g, "")
		.replace(/ /g, "-")
		.toLowerCase();
}

function generateStoreUrl(storeId, storeProfile) {
	try {
		return `https://pizza.dominos.com/${getHandle(
			storeProfile.region
		)}/${getHandle(storeProfile.city)}/${getHandle(storeProfile.streetName)}/`;
	} catch (e) {
		console.error(`failed to generate url for ${storeId}`);
		return false;
	}
}

/**
 *
 * @param {string[]} toAddresses
 * @param {string} subject
 * @param {string} message
 * @param {Array<{name: string, data: string}>} csvFiles
 */
async function sendEmail(toAddresses, subject, message, csvFiles = []) {
	const attachments = csvFiles
		.filter((f) => f !== null)
		.map((file) => {
			return {
				filename: file.name,
				type: "text/csv",
				content: asBase64(file.data),
			};
		});

	const body = {
		personalizations: [
			{
				to: toAddresses,
			},
		],
		from: {
			email: process.env.EMAIL_FROM_ADDRESS,
		},
		subject,
		content: [
			{
				type: "text/html",
				value: message,
			},
		],
		attachments: attachments.length > 0 ? attachments : null,
	};

	const options = {
		method: "POST",
		headers: {
			"Content-Type": "application/json",
			Authorization: `Bearer ${process.env.EMAIL_SERVICE_KEY}`,
		},
		body: JSON.stringify(body),
	};

	const res = await fetch("https://api.sendgrid.com/v3/mail/send", options);

	if (res.status > 299) {
		throw new Error(
			`Failed to send email: ${JSON.stringify(await res.text())}`
		);
	}
}

function asBase64(value) {
	return Buffer.from(value).toString("base64");
}

function arrayChunks(arr, chunkSize) {
	const chunked = { length: Math.ceil(arr.length / chunkSize) };
	return Array.from(chunked, (_v, i) =>
		arr.slice(i * chunkSize, i * chunkSize + chunkSize)
	);
}

export function formatDate(date, withTime = false) {
	const formatter = withTime ? DATETIME_FORMAT : DATE_FORMAT;
	return formatter.format(date);
}
/**
 * @typedef {import('./shared').DominosApiStore} DominosApiStore
 * @returns {Promise<DominosApiStore[]>}
 */
export async function fetchStoresFromDominosApi() {
	let offset = 0;
	const limit = 50;
	const url = new URL(
		`https://prod-cdn.us.yextapis.com/v2/accounts/me/search/vertical/query?experienceKey=locator&api_key=db579cbf33dcf239cfae2d4466f5ce59&v=20220511&version=PRODUCTION&locale=en&input=&verticalKey=locations&filters=%7B%22builtin.location%22%3A%7B%22%24near%22%3A%7B%22lat%22%3A39.833333%2C%22lng%22%3A-98.583333%2C%22radius%22%3A214649906%7D%7D%7D&retrieveFacets=false&skipSpellCheck=false&sessionTrackingEnabled=false&sortBys=%5B%5D&source=STANDARD&limit=${limit}`
	);

	const firstResult = await fetch(url).then((r) => r.json());
	// // get the total number of results the API has
	const maxResults = firstResult.response.resultsCount;
	// get just the data that we care about
	let results = firstResult.response.results.map((res) => ({
		storeId: res.data.id,
		franchiseId: res.data.c_franchise,
		closed: res.data.closed,
		phoneNumber: res.data.mainPhone,
		coordinates: res.data.displayCoordinate,
		timezone: res.data.timezone,
		storeUrl: res.data.slug
			? new URL(res.data.slug, "https://pizza.dominos.com").toString()
			: undefined,
		...res.data.address,
		// truncate/format the zip code to be 5 digits
		postalCode: formatZipCode(res.data.address?.postalCode),
		region:
			states[res.data.address?.region.toUpperCase().trim() ?? ""] ??
			res.data.address.region,
	}));
	offset += limit;
	let resultPromises = [];
	const maxConcurrentRequests = 10;
	try {
		while (offset <= maxResults + limit) {
			// update the url to use the current offset
			url.searchParams.set("offset", offset.toString());
			resultPromises.push(
				fetch(url)
					.then((r) => r.json())
					.then((result) => {
						if (!result.response.results) {
							console.error("empty result", result);
						}
						return result.response.results?.map((res) => ({
							storeId: res.data.id,
							franchiseId: res.data.c_franchise,
							closed: res.data.closed,
							phoneNumber: res.data.mainPhone,
							coordinates: res.data.displayCoordinate,
							timezone: res.data.timezone,
							storeUrl: res.data.slug
								? new URL(res.data.slug, "https://pizza.dominos.com").toString()
								: undefined,
							...res.data.address,
							// truncate/format the zip code to be 5 digits
							postalCode: formatZipCode(res.data.address?.postalCode),
						}));
					})
			);
			if (resultPromises.length >= maxConcurrentRequests) {
				const tempResults = (await Promise.all(resultPromises)).flat();
				results = results.concat(tempResults);
				resultPromises = [];
			}
			offset += limit;
		}
	} catch (e) {
		console.error(e);
	}
	if (resultPromises.length) {
		const tempResults = (await Promise.all(resultPromises)).flat();
		results = results.concat(tempResults);
		resultPromises = [];
	}

	return results;
}
/**
 * Use the DO REST API to trigger a DB sync between the MSSQL DB and the CRM DB
 */
async function triggerDBSync() {
	const restApiUrl = process.env.DB_SYNC_FUNCTION_REST_API_URL;
	await fetch(restApiUrl, {
		method: "POST",
		headers: [
			["Content-Type", "application/json"],
			["Authorization", `Basic ${process.env.DB_SYNC_FUNCTION_ACCESS_TOKEN}`],
		],
	});
	console.log("DB Sync triggered");
}

function compareZipCodes(firstZip, secondZip) {
	/** The DB uses USA as a placeholder for unknown zip codes */
	if (!firstZip || !secondZip || firstZip === "USA" || secondZip === "USA") {
		return false;
	}
	const firstZipParts = firstZip.split("-");
	const secondZipParts = secondZip.split("-");
	if (firstZipParts.length === secondZipParts.length) {
		return firstZip === secondZip;
	} else {
		return firstZipParts.at(0) === secondZipParts.at(0);
	}
}

/**
 * Trims a phone number to be 10 digits or less
 * @param {string | undefined | null} phoneNumber
 * @returns {string|null} trimmed phone number
 */
function trimPhoneNumber(phoneNumber) {
	if (!phoneNumber) {
		return null;
	} else if (phoneNumber.length <= 10) {
		return phoneNumber;
	}

	// if the phone number starts with +1, remove it
	if (phoneNumber.length == 12 && phoneNumber.startsWith("+1")) {
		// regex matches +1 at the start of the phone number
		return phoneNumber.replace(/^\+1/, "");
	}

	// for now just return null for all other cases since it means it's a case we haven't handled which could cause issues when inserting into the DB
	return null;
}

/**

 * */

/**
 *
 * @param {{[email: string]: Array<{
 * id: number;
 * franchisee: string | null;
 * franchiseCode: string | null;
 * address1: string | null;
 * address2: string | null;
 * state: string | null;
 * county: string | null;
 * zip: string | null;
 * phone: string | null;
 * }>}} specialistStoreMaps
 */
export async function sendStoreActiveChangedEmail(specialistStoreMaps) {
	const eta = new Eta({
		views: path.join(path.dirname(fileURLToPath(import.meta.url)), "templates"),
	});
	for (const [email, changedStores] of Object.entries(specialistStoreMaps)) {
		if (!changedStores.length) {
			continue;
		}
		const emailBody = await eta.renderAsync(
			"./specialist-stores-marked-inactive-on-map",
			{
				changedStores,
				generateStoreCrmUrl,
				generateCountyCrmUrl,
				generateFranchiseCrmUrl,
			}
		);

		await sendEmail(
			[{ email }],
			`Update: ${changedStores.length} of your Domino's stores were marked inactive`,
			emailBody,
			[]
		);
	}
}

/**
 * Save the mapbox import so we don't have to re-import it for every new store
 * But this way we don't have to include it in every lookup function
 * @typedef {import('@mapbox/search-js-core').GeocodingCore} GeocodingCore
 * @type {GeocodingCore | null}
 * */
let mapboxGeocoder = null;
/**
 *
 * @param {DominosApiStore} store
 * @returns {string} county name
 */
async function lookupCountyByAddress(store) {
	// Check if the Mapbox GeocodingCore instance is already initialized
	if (!mapboxGeocoder) {
		const {
			default: { GeocodingCore },
		} = await import("@mapbox/search-js-core");

		// Initialize the Mapbox GeocodingCore instance
		// The initialization of mapboxGeocoder is now separate from the geocoding and county lookup logic,
		// ensuring the function works correctly on subsequent calls.
		// Previously, the geocoder instance was re-initialized on every call, leading to redundant setups
		// and performance overhead. This refactor avoids unnecessary re-initializations and improves efficiency.
		mapboxGeocoder = new GeocodingCore({
			accessToken: process.env.MAPBOX_CLIENT_TOKEN,
		});
	}

	// Construct the address string from store details
	// Assume city, zip, and region are always available
	const addressString = `${store.line1} ${store.city}, ${store.region} ${store.zip}`;

	try {
		// Use Mapbox Geocoding API to forward geocode the address
		const resp = await mapboxGeocoder.forward(addressString);

		// Search for a feature with a district (county) in the response
		const resultWithCounty = resp.features.find(
			(f) => f?.properties?.context?.district?.name
		);

		if (resultWithCounty) {
			/**@type {string} */
			const mapboxCounty = resultWithCounty.properties.context.district.name;

			// all of the counties in Mapbox are in the form "[NAME] County"
			// the MSSQL DB has them inserted as "[Name]"
			// so we need to remove the word county & trim the whitespace
			const cleanedUpCounty = mapboxCounty.replace(/[Cc]ounty/, "").trim();
			return cleanedUpCounty;
		} else {
			// No county information was found in the response
			console.warn(`No county found for address: ${addressString}`);
			return null;
		}
	} catch (error) {
		// Handle any errors during the Mapbox API call
		console.error("Error during Mapbox geocoding:", error);
		return null;
	}
}

export async function verifyAddress(store) {
	const missingFields = [];
	if (!store.city) missingFields.push('city');
	if (!store.region) missingFields.push('region');
	if (!store.zip) missingFields.push('zip');

	if (missingFields.length > 0) {
		const message = missingFields.length === 3
			? 'Missing city, region, and zip code.'
			: `Missing ${missingFields.join(', ')}.`;

		console.warn(`Incomplete address details for store: ${JSON.stringify(store)}`);
		return {
			valid: false,
			discrepancies: {
				city: null,
				state: null,
				zip: null,
			},
			errors: { message },
		};
	}

	try {
		const addressString = `${store.address}, ${store.city}, ${store.region} ${store.zip}`;
		const url = `https://api.mapbox.com/geocoding/v5/mapbox.places/${encodeURIComponent(addressString)}.json?access_token=${process.env.MAPBOX_ACCESS_TOKEN}`;

		const response = await fetch(url).then(res => res.json());

		if (!response.features || response.features.length === 0) {
			return {
				valid: false,
				discrepancies: {
					city: { dominos: store.city, mapbox: null },
					state: { dominos: store.region, mapbox: null },
					zip: { dominos: store.zip, mapbox: null },
				},
				errors: { message: 'No matching results found.' },
			};
		}

		const highestRelevanceFeature = response.features[0];
		const context = highestRelevanceFeature.context || [];

		const mapboxCity = context.find(c => c.id.startsWith('place'))?.text || null;
		const mapboxState = context.find(c => c.id.startsWith('region'))?.text || null;
		const mapboxZip = context.find(c => c.id.startsWith('postcode'))?.text || null;

		const cityMatches = mapboxCity?.toLowerCase() === store.city.toLowerCase();
		const stateMatches = mapboxState?.toLowerCase() === store.region.toLowerCase();
		const zipMatches = mapboxZip ? mapboxZip === store.zip : true;

		return {
			valid: cityMatches && stateMatches && zipMatches,
			discrepancies: {
				city: cityMatches ? null : { dominos: store.city, mapbox: mapboxCity },
				state: stateMatches ? null : { dominos: store.region, mapbox: mapboxState },
				zip: zipMatches ? null : { dominos: store.zip, mapbox: mapboxZip },
			},
			errors: null,
		};
	} catch (error) {
		console.error('Error during Mapbox geocoding:', error);
		return {
			valid: false,
			discrepancies: {
				city: null,
				state: null,
				zip: null,
			},
			errors: { message: 'Error during geocoding.', details: error.message },
		};
	}
}


async function processStoreAddress(
	storeId,
	storeAddress,
	context,
	verificationResult = null
) {
	if (!verificationResult) {
		verificationResult = await verifyAddress(storeAddress);
	}

	// Update AddressVerified field
	await sql.query`
        UPDATE StoresT
        SET AddressVerified = ${verificationResult.valid ? 1 : 0}
        WHERE StoreId = ${storeId}
    `;

	// Construct a note message
	const noteMessage = context === "new"
		? verificationResult.valid
			? `Address verification successful for new store: ${storeId}.`
			: `Address verification failed for new store: ${storeId}. Discrepancies: ${JSON.stringify(verificationResult.discrepancies)}`
		: verificationResult.valid
			? `Address verification successful after update for store: ${storeId}.`
			: `Address verification failed after update for store: ${storeId}. Discrepancies: ${JSON.stringify(verificationResult.discrepancies)}`;

	// Insert a note
	await sql.query`
        INSERT INTO StoreNotesT (StoreIDFK, NotedBy, Source, Note, DateEntered)
        VALUES (
            ${storeId},
            'AutoBot',
            'sls-function',
            ${noteMessage},
            GETDATE()
        )
    `;
}


/**
 * Gets a corresponding corporate franchise for a store without a franchise
 * But only if it's in a state with a corporate franchise.
 * @param {DominosApiStore} store
 * @returns {string | null} Franchise ID for corporate franchise
 */
export function getCorporateFranchise(store) {
	if (store.franchiseId) {
		return store.franchiseId;
	}

	switch (store.region.toUpperCase()) {
		case "MARYLAND":
		case "MD":
			return "Corp3";
			break;
		case "TEXAS":
		case "TX":
			return "Corp2";
			break;
		case "FLORIDA":
		case "FL":
			return "Corp1";
			break;
		default:
			return "Corp0";
	}
}

/**
 * A very simple phone number parser. Based on the format of phone numbers in the DB.
 * Doesn't handle country codes or numbers without area codes
 * If more formats or international numbers start showing up, find an npm package.
 * @param {string | null | undefined} phoneNumber - A phone Numberinclude the +1
 * @returns {string | null} - a pretty-printed phone number
 */
function formatPhoneNumber(phoneNumber) {
	let trimmedPhoneNumber = trimPhoneNumber(phoneNumber);
	if (!trimmedPhoneNumber) {
		return trimmedPhoneNumber;
	}

	// assume if the number has - in it, we've already formatted it
	if (trimmedPhoneNumber.split("-").length > 1) {
		return trimmedPhoneNumber;
	}
	if (trimmedPhoneNumber.length === 10) {
		const areaCode = trimmedPhoneNumber.slice(0, 3);
		const prefix = trimmedPhoneNumber.slice(3, 6);
		const lineNumber = trimmedPhoneNumber.slice(6);
		return `${areaCode}-${prefix}-${lineNumber}`;
	} else {
		return trimmedPhoneNumber;
	}
}

/**
 *
 * @param {string | number} zipCode - zip code to format
 * @param {boolean} truncate - whether the zip code should be truncated to 5 digits or not. Defaults to true
 */
export function formatZipCode(zipCode, truncate = true) {
	if (!zipCode || !zipCode.toString()) {
		return null;
	}
	let zipCodeString = zipCode.toString().trim();
	const zipCodeRegex = /^(\d|-)+$/g; //matches a string that contains only digits or a "-" character

	// if the string has any characters that are not numeric or "-"
	// just return the string
	// this helps handle some of the filler strings like "USA" or state names
	// in the zip code.
	if (!zipCodeRegex.test(zipCodeString)) {
		return zipCodeString;
	}

	// if the zipCodeString is exactly 5 characters or it's > 5 characters and truncate is false, leave it
	if (zipCodeString.length === 5 || (zipCodeString.length > 5 && !truncate)) {
		return zipCodeString;
	}

	// if the string is < 5 characters, pad it with 0's.
	if (zipCodeString.length < 5) {
		return zipCodeString.padStart(5, "0");
	} else {
		// otherwise the string needs to be truncated
		// slice it to the first 5 characters
		return zipCodeString.slice(0, 5);
	}
}

/**
 * Generate a URL for the CRM's /stores route filtered to the storeId value
 * @param {string} storeId
 * @returns {string} - URL to the CRM
 */
function generateStoreCrmUrl(storeId) {
	const crmUrl = new URL("/stores", CRM_URL);
	crmUrl.searchParams.set("storeId", storeId);
	return crmUrl.toString();
}

/**
 * Generate a URL for the CRM's /stores route filtered to the franchiseCode
 * @param {string} franchiseCode - the Domino's Franchise Code for the store
 * @returns {string}  - A URL string
 */
function generateFranchiseCrmUrl(franchiseCode) {
	const crmUrl = new URL("/stores", CRM_URL);
	crmUrl.searchParams.set("franchiseCode", franchiseCode);
	return crmUrl.toString();
}
/**
 * Generate a URL for the CRM's /stores  route filtered to the county + state
 * @param {string} county - the name of the county
 * @param {string | undefined} state - the state for the county
 * @returns {string}
 */
function generateCountyCrmUrl(county, state) {
	const crmUrl = new URL("/stores", CRM_URL);
	crmUrl.searchParams.set("county", county);
	if (state) {
		crmUrl.searchParams.set("state", state);
	}
	return crmUrl.toString();
}

/**
 * Parse a URL string to use as the CRM URL
 * Or fallback to crm.stpcards.com
 * @param {string} urlString - the full url to use for the CRM
 * @returns {URL} - parsed URL
 */
function parseCrmUrl(urlString) {
	const defaultURL = "https://crm.stpcards.com/";
	let url = new URL(defaultURL);
	try {
		url = new URL(urlString);
	} catch {}
	return url;
}

/** Declare some file types for better intellisense on "private" types */
/**
 * @typedef {import('./shared').StoreValidationDBStore} StoreValidationDBStore
 * @typedef {import('./shared').DominosApiStore} DominosApiStore
 */

/**
 * @typedef {{
 * 		store: StoreValidationDBStore;
 *		oldFranchiseId?: string | null;
 * 		oldFranchiseAgreed: boolean;
 *		newFranchiseId?: string | null;
 *		newFranchiseAssignedToCorporateId?: boolean;
 *		newFranchiseName?: string | null;
 *		newFranchiseState?: string | null;
 *		newFranchiseAgreed: boolean;
 * }} FranchiseChangeData
 *
 * @typedef {{
 * 		updatedStoreErrors: Array<{franchiseChangeInfo: FranchiseChangeData, error: Error}>,
 * 		insertedFranchises: Array<{franchiseCode: string, franchiseName: string, stores: Array<StoreValidationDBStore | DominosApiStore>}>,
 * 		insertedFranchiseErrors: Array<{franchiseCode: string, franchiseName: string, error: Error}>
 * }} FranchiseUpdateResults
 **/