sendou.ink/apps/web-react/app/features/sendouq-streams/QStreamsRepository.server.ts

import type { Unwrapped } from "@sendou/utils/types";
import { db } from "~/db/sql";
import { dateToDatabaseTimestamp } from "~/utils/dates";
import { commonUserSelect, jsonObjectFrom } from "~/utils/kysely.server";

export type ActiveMatchPlayersItem = Unwrapped<
	typeof findAllActiveMatchPlayers
>;
export function findAllActiveMatchPlayers() {
	const oneHourAgo = new Date(Date.now() - 1000 * 60 * 60);

	return db
		.selectFrom("Group")
		.innerJoin("GroupMatch", (join) =>
			join.on((eb) =>
				eb.or([
					eb("GroupMatch.alphaGroupId", "=", eb.ref("Group.id")),
					eb("GroupMatch.bravoGroupId", "=", eb.ref("Group.id")),
				]),
			),
		)
		.innerJoin("GroupMember", "GroupMember.groupId", "Group.id")
		.innerJoin("LiveStream", "LiveStream.userId", "GroupMember.userId")
		.select(({ eb }) => [
			"GroupMatch.id as groupMatchId",
			"GroupMatch.createdAt as groupMatchCreatedAt",
			"LiveStream.twitch as streamTwitch",
			"LiveStream.viewerCount as streamViewerCount",
			"LiveStream.thumbnailUrl as streamThumbnailUrl",
			jsonObjectFrom(
				eb
					.selectFrom("User")
					.select((eb) => [...commonUserSelect(eb), "User.twitch"])
					.whereRef("GroupMember.userId", "=", "User.id"),
			).as("user"),
		])
		.where("Group.status", "=", "ACTIVE")
		.where("GroupMatch.createdAt", ">", dateToDatabaseTimestamp(oneHourAgo))
		.execute();
}