116 lines
3.6 KiB
TypeScript
116 lines
3.6 KiB
TypeScript
import { defineEventHandler, getQuery } from 'h3'
|
|
import { db } from '~/drizzle/db'
|
|
import {
|
|
schedules,
|
|
songCollaborators,
|
|
songReplayRequests,
|
|
songs,
|
|
systemSettings,
|
|
votes
|
|
} from '~/drizzle/schema'
|
|
import { and, eq, sql } from 'drizzle-orm'
|
|
import crypto from 'crypto'
|
|
|
|
function normalizeTs(value: any) {
|
|
if (!value) return null
|
|
if (typeof value === 'string') return value
|
|
if (value instanceof Date) return value.toISOString()
|
|
try {
|
|
return new Date(value).toISOString()
|
|
} catch {
|
|
return String(value)
|
|
}
|
|
}
|
|
|
|
export default defineEventHandler(async (event) => {
|
|
const query = getQuery(event)
|
|
const semester = (query.semester as string) || ''
|
|
|
|
const schedulesConditions = [eq(schedules.isDraft, false)]
|
|
if (semester) schedulesConditions.push(eq(songs.semester, semester))
|
|
|
|
const schedulesAgg = await db
|
|
.select({
|
|
count: sql<number>`count(${schedules.id})`.mapWith(Number).as('count'),
|
|
maxUpdatedAt: sql<any>`max(${schedules.updatedAt})`.as('maxUpdatedAt')
|
|
})
|
|
.from(schedules)
|
|
.innerJoin(songs, eq(schedules.songId, songs.id))
|
|
.where(and(...schedulesConditions))
|
|
|
|
const songWhere = semester ? eq(songs.semester, semester) : undefined
|
|
const songsAgg = await db
|
|
.select({
|
|
maxUpdatedAt: sql<any>`max(${songs.updatedAt})`.as('maxUpdatedAt')
|
|
})
|
|
.from(songs)
|
|
.where(songWhere)
|
|
|
|
const votesAgg = await db
|
|
.select({
|
|
count: sql<number>`count(${votes.id})`.mapWith(Number).as('count'),
|
|
maxCreatedAt: sql<any>`max(${votes.createdAt})`.as('maxCreatedAt')
|
|
})
|
|
.from(votes)
|
|
.innerJoin(songs, eq(votes.songId, songs.id))
|
|
.where(songWhere)
|
|
|
|
const replayConditions = []
|
|
if (semester) replayConditions.push(eq(songs.semester, semester))
|
|
const replayAgg = await db
|
|
.select({
|
|
count: sql<number>`count(${songReplayRequests.id})`.mapWith(Number).as('count'),
|
|
maxUpdatedAt: sql<any>`max(${songReplayRequests.updatedAt})`.as('maxUpdatedAt')
|
|
})
|
|
.from(songReplayRequests)
|
|
.innerJoin(songs, eq(songReplayRequests.songId, songs.id))
|
|
.where(replayConditions.length ? and(...replayConditions) : undefined)
|
|
|
|
const collaboratorConditions = []
|
|
if (semester) collaboratorConditions.push(eq(songs.semester, semester))
|
|
const collaboratorsAgg = await db
|
|
.select({
|
|
count: sql<number>`count(${songCollaborators.id})`.mapWith(Number).as('count'),
|
|
maxUpdatedAt: sql<any>`max(${songCollaborators.updatedAt})`.as('maxUpdatedAt')
|
|
})
|
|
.from(songCollaborators)
|
|
.innerJoin(songs, eq(songCollaborators.songId, songs.id))
|
|
.where(collaboratorConditions.length ? and(...collaboratorConditions) : undefined)
|
|
|
|
const settingsAgg = await db
|
|
.select({
|
|
maxUpdatedAt: sql<any>`max(${systemSettings.updatedAt})`.as('maxUpdatedAt')
|
|
})
|
|
.from(systemSettings)
|
|
|
|
const payload = {
|
|
semester: semester || null,
|
|
schedules: {
|
|
count: schedulesAgg[0]?.count || 0,
|
|
maxUpdatedAt: normalizeTs(schedulesAgg[0]?.maxUpdatedAt)
|
|
},
|
|
songs: {
|
|
maxUpdatedAt: normalizeTs(songsAgg[0]?.maxUpdatedAt)
|
|
},
|
|
votes: {
|
|
count: votesAgg[0]?.count || 0,
|
|
maxCreatedAt: normalizeTs(votesAgg[0]?.maxCreatedAt)
|
|
},
|
|
replayRequests: {
|
|
count: replayAgg[0]?.count || 0,
|
|
maxUpdatedAt: normalizeTs(replayAgg[0]?.maxUpdatedAt)
|
|
},
|
|
collaborators: {
|
|
count: collaboratorsAgg[0]?.count || 0,
|
|
maxUpdatedAt: normalizeTs(collaboratorsAgg[0]?.maxUpdatedAt)
|
|
},
|
|
systemSettings: {
|
|
maxUpdatedAt: normalizeTs(settingsAgg[0]?.maxUpdatedAt)
|
|
}
|
|
}
|
|
|
|
const revision = crypto.createHash('md5').update(JSON.stringify(payload)).digest('hex')
|
|
return { revision }
|
|
})
|
|
|