import type {
	MainWeaponId,
	ModeShort,
	StageId,
} from "@sendou/in-game-lists/types";
import { weaponIdToArrayWithAlts } from "@sendou/in-game-lists/weapon-ids";
import invariant from "@sendou/utils/invariant";
import { sql } from "kysely";
import * as R from "remeda";
import { db } from "~/db/sql";
import type { Tables } from "~/db/tables";
import {
	dateToDatabaseTimestamp,
	dayMonthYearToDatabaseTimestamp,
} from "~/utils/dates";
import {
	type CommonUser,
	commonUserJsonObject,
	commonUserSelect,
	jsonArrayFrom,
} from "~/utils/kysely.server";
import { VODS_PAGE_BATCH_SIZE } from "./vods-constants";
import type { VideoBeingAdded, Vod } from "./vods-types";
import {
	extractYoutubeIdFromVideoUrl,
	hoursMinutesSecondsStringToSeconds,
} from "./vods-utils";

export async function findByUserId(userId: Tables["User"]["id"], limit = 100) {
	return findVods({ userId, limit });
}

export async function findVods({
	weapon,
	mode,
	stageId,
	type,
	userId,
	limit = VODS_PAGE_BATCH_SIZE,
	offset = 0,
}: {
	weapon?: MainWeaponId;
	mode?: ModeShort;
	stageId?: StageId;
	type?: Tables["Video"]["type"];
	userId?: number;
	limit?: number;
	offset?: number;
}) {
	let query = db
		.selectFrom("Video")
		.leftJoin("VideoMatch", "VideoMatch.videoId", "Video.id")
		.leftJoin(
			"VideoMatchPlayer",
			"VideoMatch.id",
			"VideoMatchPlayer.videoMatchId",
		)
		.selectAll("Video")
		.select(({ fn, ref, eb }) => [
			sql<
				Array<number>
			>`json_group_array(distinct ${ref("VideoMatchPlayer.weaponSplId")})`
				.$castTo<MainWeaponId[]>()
				.as("weapons"),
			fn
				.agg("json_group_array", ["VideoMatchPlayer.playerName"])
				.$castTo<string[]>()
				.as("playerNames"),
			jsonArrayFrom(
				eb
					.selectFrom("User")
					.select((playerEb) => commonUserSelect(playerEb))
					.whereRef("User.id", "=", "VideoMatchPlayer.playerUserId"),
			).as("players"),
		]);
	if (userId) {
		query = query.where("VideoMatchPlayer.playerUserId", "=", userId);
	} else {
		if (type) {
			query = query.where("Video.type", "=", type);
		}
		if (mode) {
			query = query.where("VideoMatch.mode", "=", mode);
		}
		if (stageId) {
			query = query.where("VideoMatch.stageId", "=", stageId);
		}
	}
	if (weapon) {
		query = query.where(
			"VideoMatchPlayer.weaponSplId",
			"in",
			weaponIdToArrayWithAlts(weapon),
		);
	}
	const result = await query
		.groupBy("Video.id")
		.orderBy("Video.youtubePublishedAt", "desc")
		.limit(limit)
		.offset(offset)
		.execute();

	const vods = result.map((value) => {
		const { playerNames, players, ...vod } = value;
		return {
			...vod,
			pov: playerNames[0] ?? players[0],
		};
	});
	return vods;
}

export async function countVods({
	weapon,
	mode,
	stageId,
	type,
	userId,
}: {
	weapon?: MainWeaponId;
	mode?: ModeShort;
	stageId?: StageId;
	type?: Tables["Video"]["type"];
	userId?: number;
}) {
	let query = db
		.selectFrom("Video")
		.leftJoin("VideoMatch", "VideoMatch.videoId", "Video.id")
		.leftJoin(
			"VideoMatchPlayer",
			"VideoMatch.id",
			"VideoMatchPlayer.videoMatchId",
		)
		.select(({ fn }) => fn.count<number>("Video.id").distinct().as("count"));
	if (userId) {
		query = query.where("VideoMatchPlayer.playerUserId", "=", userId);
	} else {
		if (type) {
			query = query.where("Video.type", "=", type);
		}
		if (mode) {
			query = query.where("VideoMatch.mode", "=", mode);
		}
		if (stageId) {
			query = query.where("VideoMatch.stageId", "=", stageId);
		}
	}
	if (weapon) {
		query = query.where(
			"VideoMatchPlayer.weaponSplId",
			"in",
			weaponIdToArrayWithAlts(weapon),
		);
	}

	const result = await query.executeTakeFirstOrThrow();
	return result.count;
}

export async function findVodById(id: Tables["Video"]["id"]) {
	const videoQuery = db
		.selectFrom("Video")
		.select([
			"id",
			"title",
			"youtubePublishedAt",
			"youtubeId",
			"type",
			"submitterUserId",
		])
		.where("Video.id", "=", id);

	const video = await videoQuery.executeTakeFirst();

	if (video) {
		const videoMatchQuery = db
			.selectFrom("VideoMatch")
			.select([
				"VideoMatch.id",
				"VideoMatch.mode",
				"VideoMatch.stageId",
				"VideoMatch.startsAt",
			])
			.leftJoin(
				"VideoMatchPlayer",
				"VideoMatch.id",
				"VideoMatchPlayer.videoMatchId",
			)
			.leftJoin("User", "VideoMatchPlayer.playerUserId", "User.id")
			.select(({ fn, eb }) => [
				fn
					.agg("json_group_array", ["VideoMatchPlayer.weaponSplId"])
					.$castTo<MainWeaponId[]>()
					.as("weapons"),
				fn
					.agg("json_group_array", ["VideoMatchPlayer.playerName"])
					.filterWhere("VideoMatchPlayer.playerName", "is not", null)
					.$castTo<string[]>()
					.as("playerNames"),
				fn
					.agg("json_group_array", [commonUserJsonObject(eb)])
					.filterWhere("User.username", "is not", null)
					.$castTo<CommonUser[]>()
					.as("players"),
			])
			.where("VideoMatch.videoId", "=", id)
			.groupBy("VideoMatch.id")
			.orderBy("VideoMatch.startsAt", "asc")
			.orderBy("VideoMatchPlayer.player", "asc");

		const matches = await videoMatchQuery.execute();

		const pov = resolvePov(matches);
		const povUserId = typeof pov === "string" ? undefined : pov?.id;

		return {
			...video,
			pov,
			matches: R.map(matches, R.omit(["players", "playerNames"])),
			permissions: {
				EDIT:
					povUserId === undefined
						? [video.submitterUserId]
						: [video.submitterUserId, povUserId],
			},
		};
	}
	return null;
}

function resolvePov(
	matches: Array<{ playerNames: string[]; players: CommonUser[] }>,
): Vod["pov"] {
	for (const match of matches) {
		if (match.playerNames.length > 0) {
			return match.playerNames[0];
		}

		if (match.players.length > 0) {
			return match.players[0];
		}
	}

	return;
}

export async function update(
	args: VideoBeingAdded & {
		isValidated: boolean;
		id: number;
	},
) {
	return save(args);
}

export async function insert(
	args: VideoBeingAdded & {
		submitterUserId: number;
		isValidated: boolean;
	},
) {
	return save(args);
}

async function save(
	args: VideoBeingAdded & {
		submitterUserId?: number;
		isValidated: boolean;
		id?: number;
	},
) {
	const youtubeId = extractYoutubeIdFromVideoUrl(args.youtubeUrl);
	invariant(youtubeId, "Invalid YouTube URL");
	return db.transaction().execute(async (trx) => {
		let videoId: number;
		const video = {
			title: args.title,
			type: args.type,
			youtubePublishedAt: dayMonthYearToDatabaseTimestamp(args.date),
			eventId: args.eventId ?? null,
			youtubeId,
			validatedAt: args.isValidated
				? dateToDatabaseTimestamp(new Date())
				: null,
		};
		if (args.id) {
			await trx
				.deleteFrom("VideoMatch")
				.where("videoId", "=", args.id)
				.execute();

			// editing keeps the video's original submitter
			await trx
				.updateTable("UnvalidatedVideo")
				.set(video)
				.where("id", "=", args.id)
				.execute();
			videoId = args.id;
		} else {
			invariant(
				typeof args.submitterUserId === "number",
				"Submitter is required to add a video",
			);
			const result = await trx
				.insertInto("UnvalidatedVideo")
				.values({ ...video, submitterUserId: args.submitterUserId })
				.returning("UnvalidatedVideo.id")
				.executeTakeFirstOrThrow();
			videoId = result.id;
		}

		const insertedMatches = await trx
			.insertInto("VideoMatch")
			.values(
				args.matches.map((match) => ({
					videoId,
					startsAt: hoursMinutesSecondsStringToSeconds(match.startsAt),
					stageId: match.stageId,
					mode: match.mode,
				})),
			)
			.returning("VideoMatch.id")
			.execute();

		// SQLite assigns ids in insertion order, but RETURNING makes no ordering
		// promise, so sort to line the new ids back up with args.matches
		const matchIds = insertedMatches
			.map((match) => match.id)
			.sort((a, b) => a - b);

		const players = args.matches.flatMap((match, matchIdx) =>
			match.weapons.map((weaponSplId, weaponIdx) => ({
				videoMatchId: matchIds[matchIdx],
				playerUserId: args.pov?.type === "USER" ? args.pov.userId : null,
				playerName: args.pov?.type === "NAME" ? args.pov.name : null,
				weaponSplId,
				player: weaponIdx + 1,
			})),
		);

		await trx.insertInto("VideoMatchPlayer").values(players).execute();

		return { ...video, id: videoId };
	});
}

export function deleteById(id: number) {
	return db.deleteFrom("UnvalidatedVideo").where("id", "=", id).execute();
}