import consola from "consola";
import { and, desc, eq, inArray, sql } from "drizzle-orm";
import { drizzle } from "drizzle-orm/node-postgres";
import {
	globalStats,
	type NewProjectStats,
	projectStats,
} from "~~/server/database/schema";
import { dateToKey, keyToDate } from "~~/shared/utils/date";
import type { ProjectTypes } from "~~/shared/utils/project";
import type { ProjectStatsValue } from "../processing/projects/types";

let _drizzle: ReturnType<typeof drizzle>;
export function useDrizzle() {
	const url = useRuntimeConfig().db.url;

	if (!_drizzle) {
		_drizzle = drizzle(url);
	}

	return _drizzle;
}

//TODO: dont catch in functions or force error handling outside (for functions that dont return values)
//TODO: cache functions again
export const DB = {
	ProjectStats: {
		async set(
			data: ProjectStats,
			type: ProjectTypes,
			versionCategory: VersionCategories,
			exclusive: boolean,
		): Promise<Error | undefined> {
			return DB.ProjectStats.set_for_date(
				new Date(),
				data,
				type,
				versionCategory,
				exclusive,
			);
		},
		async set_for_date(
			date: Date,
			data: ProjectStats,
			type: ProjectTypes,
			versionCategory: VersionCategories,
			exclusive: boolean,
		): Promise<Error | undefined> {
			const db = useDrizzle();

			const dateKey = dateToKey(date);

			const to_insert: NewProjectStats[] = [];
			for (let i = 0; i < data.versions.length; i++) {
				const version = data.versions[i];

				for (const loader of data.data) {
					const values = loader.values[i];

					if (values === undefined || version === undefined) {
						consola.warn(
							`No values for version ${version} in loader ${loader.name}`,
						);
						continue;
					}

					if (
						values.count === 0 &&
						values.downloads === 0 &&
						values.versions === 0
					) {
						continue;
					}

					to_insert.push({
						// Values
						downloads: values.downloads,
						versions: values.versions,
						count: values.count,

						// Version Info
						version: version,
						loader: loader.name,

						// Metadata
						date: dateKey,
						projectType: type,
						versionCategory: versionCategory,
						excluded: exclusive,
					});
				}
			}

			if (to_insert.length === 0) return;

			try {
				await db
					.insert(projectStats)
					.values(to_insert)
					.onConflictDoUpdate({
						target: [
							projectStats.date,
							projectStats.projectType,
							projectStats.versionCategory,
							projectStats.excluded,
							projectStats.version,
							projectStats.loader,
						],
						set: {
							downloads: sql`excluded.downloads`,
							versions: sql`excluded.versions`,
							count: sql`excluded.count`,
						},
					});
			} catch (error) {
				console.error(error);
				return new Error(`Failed to add project stats: ${error}`);
			}
		},
		async get(
			date: Date,
			type: ProjectTypes,
			versionCategory: VersionCategories,
			exclusive: boolean,
		): Promise<ProjectStats | Error> {
			const dateKey = dateToKey(date);
			const db = useDrizzle();

			try {
				const rows = await db
					.select()
					.from(projectStats)
					.where(
						and(
							eq(projectStats.date, dateKey),
							eq(projectStats.projectType, type),
							eq(projectStats.versionCategory, versionCategory),
							eq(projectStats.excluded, exclusive),
						),
					);

				if (rows.length === 0) {
					return new Error(`No data for date ${dateKey}`);
				}

				const versions = await KV.GameVersions.get_for(versionCategory);
				if (versions instanceof Error) {
					return versions;
				}

				const loaderGroups: Record<string, typeof rows> = {};
				for (const row of rows) {
					const loader = loaderGroups[row.loader];
					if (loader === undefined) {
						loaderGroups[row.loader] = [row];
					} else {
						loader.push(row);
					}
				}

				const data = Object.entries(loaderGroups).map(
					([loaderName, loaderRows]) => {
						const values = versions.map((version) => {
							const match = loaderRows.find((r) => r.version === version);
							return {
								downloads: match?.downloads ?? 0,
								versions: match?.versions ?? 0,
								count: match?.count ?? 0,
							};
						});

						return {
							name: loaderName,
							values: values,
						};
					},
				);

				return {
					versions,
					data,
				};
			} catch (error) {
				return new Error(`Failed to retrieve project stats: ${error}`);
			}
		},
		async getBulk_for_version(
			dates: Date[],
			type: ProjectTypes,
			versionCategory: VersionCategories,
			exclusive: boolean,
			version: string,
		): Promise<
			({ loader: string; data: ProjectStatsValue }[] | Error)[] | Error
		> {
			const db = useDrizzle();

			const dateKeys = dates.map(dateToKey);

			try {
				const rows = await db
					.select()
					.from(projectStats)
					.where(
						and(
							inArray(projectStats.date, dateKeys),
							eq(projectStats.projectType, type),
							eq(projectStats.versionCategory, versionCategory),
							eq(projectStats.excluded, exclusive),
							eq(projectStats.version, version),
						),
					);

				const grouped = Object.groupBy(rows, (row) => row.date);

				return dateKeys
					.map((dateKey) => {
						const result = grouped[dateKey];

						if (result === undefined)
							return new Error(`No data for date ${dateKey}`);

						return result.map((row) => ({
							loader: row.loader,
							data: {
								downloads: row.downloads,
								versions: row.versions,
								count: row.count,
							},
						}));
					})
					.filter(Boolean);
			} catch (error) {
				return new Error(`Failed to retrieve project stats: ${error}`);
			}
		},
		async getLatest(
			type: ProjectTypes,
			versionCategory: VersionCategories,
			exclusive: boolean,
		): Promise<ProjectStats | Error> {
			const dateKey = await KV.LatestDate.get();
			if (dateKey instanceof Error) return dateKey;

			return DB.ProjectStats.get(
				keyToDate(dateKey),
				type,
				versionCategory,
				exclusive,
			);
		},
	},
	GlobalStats: {
		async set(data: GlobalStats, date: Date): Promise<Error | undefined> {
			const db = useDrizzle();

			try {
				await db
					.insert(globalStats)
					.values({
						projects: data.projects,
						versions: data.versions,
						files: data.files,
						authors: data.authors,
						date: dateToKey(date),
					})
					.onConflictDoUpdate({
						target: [globalStats.date],
						set: {
							projects: sql`excluded.projects`,
							versions: sql`excluded.versions`,
							files: sql`excluded.files`,
							authors: sql`excluded.authors`,
						},
					});
			} catch (error) {
				console.error(error);
				return new Error(`Failed to add global stats: ${error}`);
			}
		},
		async getBulk(dates: Date[]): Promise<(GlobalStats | Error)[]> {
			const db = useDrizzle();
			const results: (GlobalStats | Error)[] = [];

			for (const date of dates) {
				try {
					const result = await db
						.select()
						.from(globalStats)
						.where(eq(globalStats.date, dateToKey(date)))
						.limit(1);

					if (result[0] === undefined) {
						results.push(new Error(`No global stats found for date ${date}`));
						continue;
					}

					results.push({
						projects: result[0].projects,
						versions: result[0].versions,
						files: result[0].files,
						authors: result[0].authors,
					});
				} catch (error) {
					results.push(
						new Error(`Failed to get global stats for date ${date}: ${error}`),
					);
				}
			}

			return results;
		},
		async getLatest(): Promise<GlobalStats | Error> {
			const db = useDrizzle();
			const result = await db
				.select()
				.from(globalStats)
				.orderBy(desc(globalStats.date))
				.limit(1);

			if (result[0] === undefined) {
				return new Error("No global stats found");
			}

			return {
				projects: result[0].projects,
				versions: result[0].versions,
				files: result[0].files,
				authors: result[0].authors,
			};
		},
	},
};