890 行
29 KiB
TypeScript
890 行
29 KiB
TypeScript
import { and, asc, desc, eq, inArray, sql } from "drizzle-orm";
|
|
import {
|
|
matchParticipants,
|
|
matchScoreEvents,
|
|
matchSessions,
|
|
users,
|
|
type InsertMatchParticipant,
|
|
type InsertMatchScoreEvent,
|
|
type InsertMatchSession,
|
|
} from "../drizzle/schema";
|
|
import {
|
|
createNotification,
|
|
getDateKey,
|
|
getDb,
|
|
refreshAchievementsForUser,
|
|
refreshUserTrainingSummary,
|
|
upsertDailyTrainingAggregate,
|
|
upsertTrainingRecordBySource,
|
|
} from "./db";
|
|
import {
|
|
MATCH_PLAYER_SLOTS,
|
|
buildParticipantSettlement,
|
|
deriveSuggestedMatchState,
|
|
normalizeMatchMetrics,
|
|
normalizeMatchScoreboard,
|
|
type MatchPlayerSlot,
|
|
} from "./match";
|
|
|
|
type MatchMode = "daily" | "competitive";
|
|
type WorkflowStatus = "draft" | "recording" | "review_pending" | "reviewed" | "finalizing" | "finalized" | "cancelled";
|
|
type SuggestionStatus = "idle" | "queued" | "ready" | "failed";
|
|
type CameraStatus = "pending" | "bound" | "active" | "completed" | "failed";
|
|
type EventSource = "camera_a" | "camera_b" | "system" | "admin";
|
|
type EventType = "point" | "game" | "set" | "metric" | "score_suggestion" | "review_adjustment" | "finalized";
|
|
|
|
type ParticipantRow = Awaited<ReturnType<typeof listMatchParticipants>>[number];
|
|
|
|
function orderByIds<T extends { id: number }>(rows: T[], ids: number[]) {
|
|
const order = new Map(ids.map((id, index) => [id, index]));
|
|
return [...rows].sort((a, b) => (order.get(a.id) ?? 0) - (order.get(b.id) ?? 0));
|
|
}
|
|
|
|
function indexBySlot(rows: ParticipantRow[]) {
|
|
return {
|
|
player_a: rows.find((row) => row.playerSlot === "player_a") ?? null,
|
|
player_b: rows.find((row) => row.playerSlot === "player_b") ?? null,
|
|
};
|
|
}
|
|
|
|
async function loadParticipantsMap(matchIds: number[]) {
|
|
if (matchIds.length === 0) {
|
|
return new Map<number, ParticipantRow[]>();
|
|
}
|
|
|
|
const db = await getDb();
|
|
if (!db) return new Map<number, ParticipantRow[]>();
|
|
|
|
const rows = await db.select({
|
|
id: matchParticipants.id,
|
|
matchId: matchParticipants.matchId,
|
|
userId: matchParticipants.userId,
|
|
userName: users.name,
|
|
playerSlot: matchParticipants.playerSlot,
|
|
cameraSlot: matchParticipants.cameraSlot,
|
|
cameraStatus: matchParticipants.cameraStatus,
|
|
cameraLabel: matchParticipants.cameraLabel,
|
|
cameraVideoId: matchParticipants.cameraVideoId,
|
|
cameraVideoUrl: matchParticipants.cameraVideoUrl,
|
|
cameraSnapshot: matchParticipants.cameraSnapshot,
|
|
isWinner: matchParticipants.isWinner,
|
|
suggestedSetsWon: matchParticipants.suggestedSetsWon,
|
|
suggestedGamesWon: matchParticipants.suggestedGamesWon,
|
|
suggestedPointsWon: matchParticipants.suggestedPointsWon,
|
|
finalSetsWon: matchParticipants.finalSetsWon,
|
|
finalGamesWon: matchParticipants.finalGamesWon,
|
|
finalPointsWon: matchParticipants.finalPointsWon,
|
|
suggestedStats: matchParticipants.suggestedStats,
|
|
finalStats: matchParticipants.finalStats,
|
|
createdAt: matchParticipants.createdAt,
|
|
updatedAt: matchParticipants.updatedAt,
|
|
}).from(matchParticipants)
|
|
.leftJoin(users, eq(users.id, matchParticipants.userId))
|
|
.where(inArray(matchParticipants.matchId, matchIds))
|
|
.orderBy(asc(matchParticipants.matchId), asc(matchParticipants.id));
|
|
|
|
const grouped = new Map<number, ParticipantRow[]>();
|
|
for (const row of rows) {
|
|
const list = grouped.get(row.matchId) ?? [];
|
|
list.push(row);
|
|
grouped.set(row.matchId, list);
|
|
}
|
|
return grouped;
|
|
}
|
|
|
|
async function loadEventCounts(matchIds: number[]) {
|
|
if (matchIds.length === 0) {
|
|
return new Map<number, number>();
|
|
}
|
|
|
|
const db = await getDb();
|
|
if (!db) return new Map<number, number>();
|
|
|
|
const rows = await db.select({
|
|
matchId: matchScoreEvents.matchId,
|
|
}).from(matchScoreEvents).where(inArray(matchScoreEvents.matchId, matchIds));
|
|
|
|
const counts = new Map<number, number>();
|
|
for (const row of rows) {
|
|
counts.set(row.matchId, (counts.get(row.matchId) ?? 0) + 1);
|
|
}
|
|
return counts;
|
|
}
|
|
|
|
async function hydrateMatches(sessionIds: number[]) {
|
|
const db = await getDb();
|
|
if (!db || sessionIds.length === 0) return [];
|
|
|
|
const sessions = await db.select().from(matchSessions)
|
|
.where(inArray(matchSessions.id, sessionIds));
|
|
const participantsMap = await loadParticipantsMap(sessionIds);
|
|
const eventCounts = await loadEventCounts(sessionIds);
|
|
|
|
return orderByIds(sessions, sessionIds).map((session) => ({
|
|
...session,
|
|
participants: participantsMap.get(session.id) ?? [],
|
|
eventCount: eventCounts.get(session.id) ?? 0,
|
|
}));
|
|
}
|
|
|
|
export async function getMatchSessionById(matchId: number) {
|
|
const db = await getDb();
|
|
if (!db) return undefined;
|
|
const result = await db.select().from(matchSessions).where(eq(matchSessions.id, matchId)).limit(1);
|
|
return result[0];
|
|
}
|
|
|
|
export async function getMatchParticipantBySlot(matchId: number, playerSlot: MatchPlayerSlot) {
|
|
const db = await getDb();
|
|
if (!db) return undefined;
|
|
const result = await db.select().from(matchParticipants)
|
|
.where(and(eq(matchParticipants.matchId, matchId), eq(matchParticipants.playerSlot, playerSlot)))
|
|
.limit(1);
|
|
return result[0];
|
|
}
|
|
|
|
export async function listMatchParticipants(matchId: number) {
|
|
const db = await getDb();
|
|
if (!db) return [];
|
|
return db.select({
|
|
id: matchParticipants.id,
|
|
matchId: matchParticipants.matchId,
|
|
userId: matchParticipants.userId,
|
|
userName: users.name,
|
|
playerSlot: matchParticipants.playerSlot,
|
|
cameraSlot: matchParticipants.cameraSlot,
|
|
cameraStatus: matchParticipants.cameraStatus,
|
|
cameraLabel: matchParticipants.cameraLabel,
|
|
cameraVideoId: matchParticipants.cameraVideoId,
|
|
cameraVideoUrl: matchParticipants.cameraVideoUrl,
|
|
cameraSnapshot: matchParticipants.cameraSnapshot,
|
|
isWinner: matchParticipants.isWinner,
|
|
suggestedSetsWon: matchParticipants.suggestedSetsWon,
|
|
suggestedGamesWon: matchParticipants.suggestedGamesWon,
|
|
suggestedPointsWon: matchParticipants.suggestedPointsWon,
|
|
finalSetsWon: matchParticipants.finalSetsWon,
|
|
finalGamesWon: matchParticipants.finalGamesWon,
|
|
finalPointsWon: matchParticipants.finalPointsWon,
|
|
suggestedStats: matchParticipants.suggestedStats,
|
|
finalStats: matchParticipants.finalStats,
|
|
createdAt: matchParticipants.createdAt,
|
|
updatedAt: matchParticipants.updatedAt,
|
|
}).from(matchParticipants)
|
|
.leftJoin(users, eq(users.id, matchParticipants.userId))
|
|
.where(eq(matchParticipants.matchId, matchId))
|
|
.orderBy(asc(matchParticipants.id));
|
|
}
|
|
|
|
export async function listMatchScoreEvents(matchId: number) {
|
|
const db = await getDb();
|
|
if (!db) return [];
|
|
return db.select().from(matchScoreEvents)
|
|
.where(eq(matchScoreEvents.matchId, matchId))
|
|
.orderBy(asc(matchScoreEvents.eventIndex), asc(matchScoreEvents.id));
|
|
}
|
|
|
|
export async function getMatchDetail(matchId: number) {
|
|
const session = await getMatchSessionById(matchId);
|
|
if (!session) return undefined;
|
|
const participants = await listMatchParticipants(matchId);
|
|
const events = await listMatchScoreEvents(matchId);
|
|
return {
|
|
...session,
|
|
participants,
|
|
events,
|
|
eventCount: events.length,
|
|
};
|
|
}
|
|
|
|
export async function listAccessibleMatchSessions(params: {
|
|
viewerUserId: number;
|
|
isAdmin: boolean;
|
|
limit?: number;
|
|
workflowStatus?: WorkflowStatus | "all";
|
|
matchMode?: MatchMode | "all";
|
|
}) {
|
|
const db = await getDb();
|
|
if (!db) return [];
|
|
|
|
const limit = params.limit ?? 50;
|
|
const filters = [];
|
|
|
|
if (params.workflowStatus && params.workflowStatus !== "all") {
|
|
filters.push(eq(matchSessions.workflowStatus, params.workflowStatus));
|
|
}
|
|
if (params.matchMode && params.matchMode !== "all") {
|
|
filters.push(eq(matchSessions.matchMode, params.matchMode));
|
|
}
|
|
|
|
if (params.isAdmin) {
|
|
const sessions = await db.select({
|
|
id: matchSessions.id,
|
|
}).from(matchSessions)
|
|
.where(filters.length > 0 ? and(...filters) : undefined)
|
|
.orderBy(desc(matchSessions.updatedAt), desc(matchSessions.id))
|
|
.limit(limit);
|
|
return hydrateMatches(sessions.map((row) => row.id));
|
|
}
|
|
|
|
const conditions = [
|
|
eq(matchParticipants.userId, params.viewerUserId),
|
|
...filters,
|
|
];
|
|
|
|
const sessions = await db.select({
|
|
id: matchSessions.id,
|
|
}).from(matchParticipants)
|
|
.innerJoin(matchSessions, eq(matchSessions.id, matchParticipants.matchId))
|
|
.where(and(...conditions))
|
|
.orderBy(desc(matchSessions.updatedAt), desc(matchSessions.id))
|
|
.limit(limit);
|
|
|
|
return hydrateMatches(sessions.map((row) => row.id));
|
|
}
|
|
|
|
export async function createMatchSession(input: {
|
|
createdByUserId: number;
|
|
matchMode: MatchMode;
|
|
title: string;
|
|
courtName?: string | null;
|
|
notes?: string | null;
|
|
durationMinutes: number;
|
|
scheduledAt?: Date | null;
|
|
participantUserIds: [number, number];
|
|
}) {
|
|
if (input.participantUserIds[0] === input.participantUserIds[1]) {
|
|
throw new Error("两位参赛用户必须不同");
|
|
}
|
|
|
|
const db = await getDb();
|
|
if (!db) throw new Error("Database not available");
|
|
|
|
const matchId = await db.transaction(async (tx) => {
|
|
const sessionValues: InsertMatchSession = {
|
|
createdByUserId: input.createdByUserId,
|
|
matchMode: input.matchMode,
|
|
workflowStatus: "draft",
|
|
title: input.title,
|
|
courtName: input.courtName ?? null,
|
|
notes: input.notes ?? null,
|
|
durationMinutes: input.durationMinutes,
|
|
scheduledAt: input.scheduledAt ?? null,
|
|
suggestionStatus: "idle",
|
|
suggestionTaskId: null,
|
|
suggestedScore: null,
|
|
suggestedMetrics: null,
|
|
finalScore: null,
|
|
finalMetrics: null,
|
|
reviewNotes: null,
|
|
reviewSubmittedAt: null,
|
|
reviewedByUserId: null,
|
|
reviewedAt: null,
|
|
finalizedByUserId: null,
|
|
finalizedAt: null,
|
|
};
|
|
|
|
const inserted = await tx.insert(matchSessions).values(sessionValues);
|
|
const createdMatchId = inserted[0].insertId;
|
|
|
|
const participantValues: InsertMatchParticipant[] = [
|
|
{
|
|
matchId: createdMatchId,
|
|
userId: input.participantUserIds[0],
|
|
playerSlot: "player_a",
|
|
cameraSlot: "camera_a",
|
|
cameraStatus: "pending",
|
|
cameraLabel: null,
|
|
cameraVideoId: null,
|
|
cameraVideoUrl: null,
|
|
cameraSnapshot: null,
|
|
isWinner: 0,
|
|
suggestedSetsWon: 0,
|
|
suggestedGamesWon: 0,
|
|
suggestedPointsWon: 0,
|
|
finalSetsWon: 0,
|
|
finalGamesWon: 0,
|
|
finalPointsWon: 0,
|
|
suggestedStats: null,
|
|
finalStats: null,
|
|
},
|
|
{
|
|
matchId: createdMatchId,
|
|
userId: input.participantUserIds[1],
|
|
playerSlot: "player_b",
|
|
cameraSlot: "camera_b",
|
|
cameraStatus: "pending",
|
|
cameraLabel: null,
|
|
cameraVideoId: null,
|
|
cameraVideoUrl: null,
|
|
cameraSnapshot: null,
|
|
isWinner: 0,
|
|
suggestedSetsWon: 0,
|
|
suggestedGamesWon: 0,
|
|
suggestedPointsWon: 0,
|
|
finalSetsWon: 0,
|
|
finalGamesWon: 0,
|
|
finalPointsWon: 0,
|
|
suggestedStats: null,
|
|
finalStats: null,
|
|
},
|
|
];
|
|
|
|
await tx.insert(matchParticipants).values(participantValues);
|
|
return createdMatchId;
|
|
});
|
|
|
|
return getMatchDetail(matchId);
|
|
}
|
|
|
|
export async function updateMatchSession(matchId: number, patch: Partial<InsertMatchSession>) {
|
|
const db = await getDb();
|
|
if (!db) return;
|
|
await db.update(matchSessions).set(patch).where(eq(matchSessions.id, matchId));
|
|
}
|
|
|
|
export async function bindMatchCamera(input: {
|
|
matchId: number;
|
|
playerSlot: MatchPlayerSlot;
|
|
cameraStatus: CameraStatus;
|
|
cameraLabel?: string | null;
|
|
cameraVideoId?: number | null;
|
|
cameraVideoUrl?: string | null;
|
|
cameraSnapshot?: unknown;
|
|
}) {
|
|
const db = await getDb();
|
|
if (!db) throw new Error("Database not available");
|
|
|
|
const participant = await getMatchParticipantBySlot(input.matchId, input.playerSlot);
|
|
if (!participant) {
|
|
throw new Error("Match participant not found");
|
|
}
|
|
|
|
await db.update(matchParticipants).set({
|
|
cameraStatus: input.cameraStatus,
|
|
cameraLabel: input.cameraLabel === undefined ? participant.cameraLabel : input.cameraLabel,
|
|
cameraVideoId: input.cameraVideoId === undefined ? participant.cameraVideoId : input.cameraVideoId,
|
|
cameraVideoUrl: input.cameraVideoUrl === undefined ? participant.cameraVideoUrl : input.cameraVideoUrl,
|
|
cameraSnapshot: input.cameraSnapshot === undefined ? participant.cameraSnapshot : input.cameraSnapshot,
|
|
}).where(eq(matchParticipants.id, participant.id));
|
|
|
|
const session = await getMatchSessionById(input.matchId);
|
|
if (session?.workflowStatus === "draft" && input.cameraStatus !== "pending") {
|
|
await updateMatchSession(input.matchId, {
|
|
workflowStatus: "recording",
|
|
});
|
|
}
|
|
|
|
return getMatchDetail(input.matchId);
|
|
}
|
|
|
|
async function getNextEventIndex(matchId: number) {
|
|
const db = await getDb();
|
|
if (!db) return 1;
|
|
const result = await db.select({
|
|
maxEventIndex: sql<number>`coalesce(max(${matchScoreEvents.eventIndex}), 0)`,
|
|
}).from(matchScoreEvents).where(eq(matchScoreEvents.matchId, matchId));
|
|
return (result[0]?.maxEventIndex ?? 0) + 1;
|
|
}
|
|
|
|
export async function insertMatchScoreEvent(input: {
|
|
matchId: number;
|
|
source: EventSource;
|
|
eventType: EventType;
|
|
winnerSlot?: MatchPlayerSlot | null;
|
|
matchSecond?: number | null;
|
|
confidence?: number | null;
|
|
payload?: unknown;
|
|
createdByUserId?: number | null;
|
|
}) {
|
|
const db = await getDb();
|
|
if (!db) throw new Error("Database not available");
|
|
|
|
const eventIndex = await getNextEventIndex(input.matchId);
|
|
const values: InsertMatchScoreEvent = {
|
|
matchId: input.matchId,
|
|
eventIndex,
|
|
source: input.source,
|
|
eventType: input.eventType,
|
|
winnerSlot: input.winnerSlot ?? null,
|
|
matchSecond: input.matchSecond ?? null,
|
|
confidence: input.confidence ?? null,
|
|
payload: input.payload ?? null,
|
|
createdByUserId: input.createdByUserId ?? null,
|
|
};
|
|
|
|
const inserted = await db.insert(matchScoreEvents).values(values);
|
|
const rows = await db.select().from(matchScoreEvents).where(eq(matchScoreEvents.id, inserted[0].insertId)).limit(1);
|
|
|
|
const session = await getMatchSessionById(input.matchId);
|
|
if (session && session.workflowStatus === "draft") {
|
|
await updateMatchSession(input.matchId, {
|
|
workflowStatus: "recording",
|
|
});
|
|
}
|
|
|
|
return rows[0];
|
|
}
|
|
|
|
export async function saveMatchSuggestion(matchId: number, params: {
|
|
suggestionStatus: SuggestionStatus;
|
|
suggestionTaskId?: string | null;
|
|
suggestedScore?: unknown;
|
|
suggestedMetrics?: unknown;
|
|
}) {
|
|
const session = await getMatchSessionById(matchId);
|
|
if (!session) {
|
|
throw new Error("Match session not found");
|
|
}
|
|
|
|
const score = params.suggestedScore ? normalizeMatchScoreboard(params.suggestedScore) : normalizeMatchScoreboard(session.suggestedScore);
|
|
const metrics = params.suggestedMetrics ? normalizeMatchMetrics(params.suggestedMetrics) : normalizeMatchMetrics(session.suggestedMetrics);
|
|
const settlement = buildParticipantSettlement(score, metrics);
|
|
const participants = await listMatchParticipants(matchId);
|
|
|
|
await updateMatchSession(matchId, {
|
|
suggestionStatus: params.suggestionStatus,
|
|
suggestionTaskId: params.suggestionTaskId === undefined ? session.suggestionTaskId : params.suggestionTaskId,
|
|
suggestedScore: params.suggestedScore === undefined ? session.suggestedScore : score,
|
|
suggestedMetrics: params.suggestedMetrics === undefined ? session.suggestedMetrics : metrics,
|
|
workflowStatus: session.workflowStatus === "draft" || session.workflowStatus === "recording"
|
|
? (params.suggestionStatus === "ready" ? "review_pending" : session.workflowStatus)
|
|
: session.workflowStatus,
|
|
});
|
|
|
|
const rowsBySlot = indexBySlot(participants);
|
|
for (const slot of MATCH_PLAYER_SLOTS) {
|
|
const row = rowsBySlot[slot];
|
|
if (!row) continue;
|
|
const player = settlement.players[slot];
|
|
const dbConn = await getDb();
|
|
if (!dbConn) continue;
|
|
await dbConn.update(matchParticipants).set({
|
|
suggestedSetsWon: player.setsWon,
|
|
suggestedGamesWon: player.gamesWon,
|
|
suggestedPointsWon: player.pointsWon,
|
|
suggestedStats: player.stats,
|
|
}).where(eq(matchParticipants.id, row.id));
|
|
}
|
|
|
|
return getMatchDetail(matchId);
|
|
}
|
|
|
|
export async function submitMatchReview(input: {
|
|
matchId: number;
|
|
reviewedByUserId: number;
|
|
reviewNotes?: string | null;
|
|
finalScore?: unknown;
|
|
finalMetrics?: unknown;
|
|
}) {
|
|
const session = await getMatchSessionById(input.matchId);
|
|
if (!session) {
|
|
throw new Error("Match session not found");
|
|
}
|
|
|
|
const score = normalizeMatchScoreboard(input.finalScore ?? session.suggestedScore);
|
|
const metrics = normalizeMatchMetrics(input.finalMetrics ?? session.suggestedMetrics);
|
|
const settlement = buildParticipantSettlement(score, metrics);
|
|
const participants = await listMatchParticipants(input.matchId);
|
|
const db = await getDb();
|
|
if (!db) throw new Error("Database not available");
|
|
|
|
await updateMatchSession(input.matchId, {
|
|
finalScore: score,
|
|
finalMetrics: metrics,
|
|
reviewNotes: input.reviewNotes ?? session.reviewNotes,
|
|
reviewSubmittedAt: new Date(),
|
|
reviewedByUserId: input.reviewedByUserId,
|
|
reviewedAt: new Date(),
|
|
workflowStatus: "reviewed",
|
|
});
|
|
|
|
const rowsBySlot = indexBySlot(participants);
|
|
for (const slot of MATCH_PLAYER_SLOTS) {
|
|
const row = rowsBySlot[slot];
|
|
if (!row) continue;
|
|
const player = settlement.players[slot];
|
|
await db.update(matchParticipants).set({
|
|
isWinner: player.isWinner ? 1 : 0,
|
|
finalSetsWon: player.setsWon,
|
|
finalGamesWon: player.gamesWon,
|
|
finalPointsWon: player.pointsWon,
|
|
finalStats: player.stats,
|
|
}).where(eq(matchParticipants.id, row.id));
|
|
}
|
|
|
|
await insertMatchScoreEvent({
|
|
matchId: input.matchId,
|
|
source: "admin",
|
|
eventType: "review_adjustment",
|
|
winnerSlot: settlement.winnerSlot,
|
|
confidence: 1,
|
|
payload: {
|
|
score,
|
|
metrics,
|
|
reviewNotes: input.reviewNotes ?? null,
|
|
},
|
|
createdByUserId: input.reviewedByUserId,
|
|
});
|
|
|
|
return getMatchDetail(input.matchId);
|
|
}
|
|
|
|
export async function markMatchSuggestionQueued(matchId: number, taskId: string) {
|
|
const session = await getMatchSessionById(matchId);
|
|
if (!session) {
|
|
throw new Error("Match session not found");
|
|
}
|
|
await updateMatchSession(matchId, {
|
|
suggestionStatus: "queued",
|
|
suggestionTaskId: taskId,
|
|
workflowStatus: session.workflowStatus === "draft" ? "recording" : session.workflowStatus,
|
|
});
|
|
}
|
|
|
|
export async function markMatchFinalizing(matchId: number) {
|
|
const session = await getMatchSessionById(matchId);
|
|
if (!session) {
|
|
throw new Error("Match session not found");
|
|
}
|
|
await updateMatchSession(matchId, {
|
|
workflowStatus: "finalizing",
|
|
});
|
|
}
|
|
|
|
export async function cancelMatchSession(matchId: number, notes?: string | null) {
|
|
const session = await getMatchSessionById(matchId);
|
|
if (!session) {
|
|
throw new Error("Match session not found");
|
|
}
|
|
|
|
await updateMatchSession(matchId, {
|
|
workflowStatus: "cancelled",
|
|
notes: notes === undefined ? session.notes : notes,
|
|
});
|
|
return getMatchDetail(matchId);
|
|
}
|
|
|
|
export async function generateSuggestedMatchState(matchId: number) {
|
|
const detail = await getMatchDetail(matchId);
|
|
if (!detail) {
|
|
throw new Error("Match session not found");
|
|
}
|
|
|
|
const suggestion = deriveSuggestedMatchState(detail.events.map((event) => ({
|
|
eventType: event.eventType,
|
|
winnerSlot: event.winnerSlot,
|
|
confidence: event.confidence,
|
|
payload: event.payload,
|
|
source: event.source,
|
|
eventIndex: event.eventIndex,
|
|
})));
|
|
|
|
await saveMatchSuggestion(matchId, {
|
|
suggestionStatus: "ready",
|
|
suggestionTaskId: detail.suggestionTaskId,
|
|
suggestedScore: suggestion.score,
|
|
suggestedMetrics: suggestion.metrics,
|
|
});
|
|
|
|
await insertMatchScoreEvent({
|
|
matchId,
|
|
source: "system",
|
|
eventType: "score_suggestion",
|
|
winnerSlot: suggestion.score.winnerSlot,
|
|
confidence: suggestion.score.confidence,
|
|
payload: {
|
|
score: suggestion.score,
|
|
metrics: suggestion.metrics,
|
|
eventCount: suggestion.eventCount,
|
|
sourceCount: suggestion.sourceCount,
|
|
},
|
|
});
|
|
|
|
return {
|
|
score: suggestion.score,
|
|
metrics: suggestion.metrics,
|
|
eventCount: suggestion.eventCount,
|
|
sourceCount: suggestion.sourceCount,
|
|
};
|
|
}
|
|
|
|
export async function finalizeMatchSettlement(matchId: number, finalizedByUserId: number) {
|
|
const detail = await getMatchDetail(matchId);
|
|
if (!detail) {
|
|
throw new Error("Match session not found");
|
|
}
|
|
|
|
if (detail.workflowStatus === "finalized") {
|
|
return detail;
|
|
}
|
|
|
|
if (detail.workflowStatus === "cancelled") {
|
|
throw new Error("已取消的比赛不能结算");
|
|
}
|
|
|
|
const score = normalizeMatchScoreboard(detail.finalScore ?? detail.suggestedScore);
|
|
const metrics = normalizeMatchMetrics(detail.finalMetrics ?? detail.suggestedMetrics);
|
|
const settlement = buildParticipantSettlement(score, metrics);
|
|
const occurredAt = detail.endedAt ?? detail.reviewSubmittedAt ?? detail.startedAt ?? detail.scheduledAt ?? new Date();
|
|
const trainingDateKey = getDateKey(occurredAt);
|
|
const durationMinutes = Math.max(1, detail.durationMinutes || 90);
|
|
const participantsBySlot = indexBySlot(detail.participants);
|
|
const db = await getDb();
|
|
if (!db) {
|
|
throw new Error("Database not available");
|
|
}
|
|
|
|
await updateMatchSession(matchId, {
|
|
finalScore: score,
|
|
finalMetrics: metrics,
|
|
workflowStatus: "finalizing",
|
|
reviewedByUserId: detail.reviewedByUserId ?? finalizedByUserId,
|
|
reviewedAt: detail.reviewedAt ?? new Date(),
|
|
reviewSubmittedAt: detail.reviewSubmittedAt ?? new Date(),
|
|
});
|
|
|
|
for (const slot of MATCH_PLAYER_SLOTS) {
|
|
const participant = participantsBySlot[slot];
|
|
if (!participant) continue;
|
|
|
|
const opponent = MATCH_PLAYER_SLOTS
|
|
.filter((item) => item !== slot)
|
|
.map((item) => participantsBySlot[item])
|
|
.find(Boolean) ?? null;
|
|
const player = settlement.players[slot];
|
|
const matchLabel = detail.matchMode === "competitive" ? "竞赛双人比赛" : "日常双人比赛";
|
|
const sourceType = detail.matchMode === "competitive" ? "match_competitive" : "match_daily";
|
|
const resultLabel = settlement.winnerSlot == null ? "已确认" : player.isWinner ? "获胜" : "失利";
|
|
|
|
await db.update(matchParticipants).set({
|
|
isWinner: player.isWinner ? 1 : 0,
|
|
finalSetsWon: player.setsWon,
|
|
finalGamesWon: player.gamesWon,
|
|
finalPointsWon: player.pointsWon,
|
|
finalStats: player.stats,
|
|
cameraStatus: participant.cameraStatus === "active" ? "completed" : participant.cameraStatus,
|
|
}).where(eq(matchParticipants.id, participant.id));
|
|
|
|
const upsertResult = await upsertTrainingRecordBySource({
|
|
userId: participant.userId,
|
|
planId: null,
|
|
linkedPlanId: null,
|
|
matchConfidence: detail.suggestionStatus === "ready" ? score.confidence : null,
|
|
exerciseName: matchLabel,
|
|
exerciseType: "match",
|
|
sourceType,
|
|
sourceId: `${sourceType}:${matchId}:${slot}`,
|
|
videoId: participant.cameraVideoId ?? null,
|
|
actionCount: player.pointsWon,
|
|
durationMinutes,
|
|
completed: 1,
|
|
notes: `${detail.title} · ${resultLabel}`,
|
|
poseScore: null,
|
|
trainingDate: occurredAt,
|
|
metadata: {
|
|
matchId,
|
|
matchMode: detail.matchMode,
|
|
playerSlot: slot,
|
|
opponentUserId: opponent?.userId ?? null,
|
|
winnerSlot: settlement.winnerSlot,
|
|
finalScore: score,
|
|
finalMetrics: metrics,
|
|
courtName: detail.courtName ?? null,
|
|
cameraStatus: participant.cameraStatus,
|
|
},
|
|
});
|
|
|
|
if (detail.matchMode === "daily" && upsertResult.isNew) {
|
|
await upsertDailyTrainingAggregate({
|
|
userId: participant.userId,
|
|
trainingDate: trainingDateKey,
|
|
deltaMinutes: durationMinutes,
|
|
deltaSessions: 1,
|
|
deltaTotalActions: player.pointsWon,
|
|
deltaEffectiveActions: player.pointsWon,
|
|
metadata: {
|
|
latestDailyMatchId: matchId,
|
|
latestDailyMatchResult: resultLabel,
|
|
},
|
|
});
|
|
}
|
|
|
|
if (detail.matchMode === "competitive") {
|
|
await refreshUserTrainingSummary(participant.userId);
|
|
}
|
|
|
|
await createNotification({
|
|
userId: participant.userId,
|
|
notificationType: "match_settlement",
|
|
title: `比赛已入库 · ${detail.title}`.slice(0, 256),
|
|
message: `${matchLabel} 已完成审核入库，结果：${resultLabel}，比分 ${player.setsWon}:${settlement.players[slot === "player_a" ? "player_b" : "player_a"].setsWon} 盘 / ${player.gamesWon}:${settlement.players[slot === "player_a" ? "player_b" : "player_a"].gamesWon} 局。`,
|
|
isRead: 0,
|
|
});
|
|
|
|
await refreshAchievementsForUser(participant.userId);
|
|
}
|
|
|
|
await insertMatchScoreEvent({
|
|
matchId,
|
|
source: "admin",
|
|
eventType: "finalized",
|
|
winnerSlot: settlement.winnerSlot,
|
|
confidence: 1,
|
|
payload: {
|
|
score,
|
|
metrics,
|
|
finalizedByUserId,
|
|
finalizedAt: new Date().toISOString(),
|
|
},
|
|
createdByUserId: finalizedByUserId,
|
|
});
|
|
|
|
await updateMatchSession(matchId, {
|
|
workflowStatus: "finalized",
|
|
finalizedByUserId,
|
|
finalizedAt: new Date(),
|
|
});
|
|
|
|
return getMatchDetail(matchId);
|
|
}
|
|
|
|
export async function getAccessibleMatchSummary(params: {
|
|
viewerUserId: number;
|
|
isAdmin: boolean;
|
|
}) {
|
|
const rows = await listAccessibleMatchSessions({
|
|
viewerUserId: params.viewerUserId,
|
|
isAdmin: params.isAdmin,
|
|
limit: 200,
|
|
});
|
|
|
|
let competitiveWins = 0;
|
|
let camerasBound = 0;
|
|
|
|
for (const match of rows) {
|
|
for (const participant of match.participants) {
|
|
if (participant.cameraStatus !== "pending") {
|
|
camerasBound += 1;
|
|
}
|
|
if (!params.isAdmin && participant.userId !== params.viewerUserId) {
|
|
continue;
|
|
}
|
|
if (match.matchMode === "competitive" && match.workflowStatus === "finalized" && participant.isWinner === 1) {
|
|
competitiveWins += 1;
|
|
}
|
|
}
|
|
}
|
|
|
|
return {
|
|
total: rows.length,
|
|
draft: rows.filter((row) => row.workflowStatus === "draft").length,
|
|
reviewPending: rows.filter((row) => row.workflowStatus === "review_pending" || row.workflowStatus === "reviewed").length,
|
|
finalized: rows.filter((row) => row.workflowStatus === "finalized").length,
|
|
daily: rows.filter((row) => row.matchMode === "daily").length,
|
|
competitive: rows.filter((row) => row.matchMode === "competitive").length,
|
|
competitiveWins,
|
|
camerasBound,
|
|
};
|
|
}
|
|
|
|
export async function getCompetitiveLeaderboard(sortBy: "wins" | "winRate" | "setsWon" | "pointsWon" | "matches" = "wins", limit = 50) {
|
|
const db = await getDb();
|
|
if (!db) return [];
|
|
|
|
const rows = await db.select({
|
|
matchId: matchParticipants.matchId,
|
|
userId: matchParticipants.userId,
|
|
userName: users.name,
|
|
skillLevel: users.skillLevel,
|
|
ntrpRating: users.ntrpRating,
|
|
isWinner: matchParticipants.isWinner,
|
|
finalSetsWon: matchParticipants.finalSetsWon,
|
|
finalGamesWon: matchParticipants.finalGamesWon,
|
|
finalPointsWon: matchParticipants.finalPointsWon,
|
|
}).from(matchParticipants)
|
|
.innerJoin(matchSessions, eq(matchSessions.id, matchParticipants.matchId))
|
|
.innerJoin(users, eq(users.id, matchParticipants.userId))
|
|
.where(and(
|
|
eq(matchSessions.matchMode, "competitive"),
|
|
eq(matchSessions.workflowStatus, "finalized"),
|
|
))
|
|
.orderBy(desc(matchParticipants.matchId), asc(matchParticipants.id));
|
|
|
|
const matchRows = new Map<number, typeof rows>();
|
|
for (const row of rows) {
|
|
const list = matchRows.get(row.matchId) ?? [];
|
|
list.push(row);
|
|
matchRows.set(row.matchId, list);
|
|
}
|
|
|
|
const leaderboard = new Map<number, {
|
|
id: number;
|
|
name: string | null;
|
|
ntrpRating: number | null;
|
|
skillLevel: string | null;
|
|
matches: number;
|
|
wins: number;
|
|
losses: number;
|
|
winRate: number;
|
|
setsWon: number;
|
|
setsLost: number;
|
|
gamesWon: number;
|
|
gamesLost: number;
|
|
pointsWon: number;
|
|
pointsLost: number;
|
|
}>();
|
|
|
|
for (const rowsOfMatch of Array.from(matchRows.values())) {
|
|
const byUser = rowsOfMatch;
|
|
for (const row of byUser) {
|
|
const opponent = byUser.find((item: (typeof rows)[number]) => item.userId !== row.userId) ?? null;
|
|
const current = leaderboard.get(row.userId) ?? {
|
|
id: row.userId,
|
|
name: row.userName ?? null,
|
|
ntrpRating: row.ntrpRating ?? 1.5,
|
|
skillLevel: row.skillLevel ?? "beginner",
|
|
matches: 0,
|
|
wins: 0,
|
|
losses: 0,
|
|
winRate: 0,
|
|
setsWon: 0,
|
|
setsLost: 0,
|
|
gamesWon: 0,
|
|
gamesLost: 0,
|
|
pointsWon: 0,
|
|
pointsLost: 0,
|
|
};
|
|
|
|
current.matches += 1;
|
|
current.wins += row.isWinner === 1 ? 1 : 0;
|
|
current.losses += row.isWinner === 1 ? 0 : 1;
|
|
current.setsWon += row.finalSetsWon || 0;
|
|
current.setsLost += opponent?.finalSetsWon || 0;
|
|
current.gamesWon += row.finalGamesWon || 0;
|
|
current.gamesLost += opponent?.finalGamesWon || 0;
|
|
current.pointsWon += row.finalPointsWon || 0;
|
|
current.pointsLost += opponent?.finalPointsWon || 0;
|
|
current.winRate = current.matches > 0 ? Math.round((current.wins / current.matches) * 1000) / 10 : 0;
|
|
|
|
leaderboard.set(row.userId, current);
|
|
}
|
|
}
|
|
|
|
const sorted = Array.from(leaderboard.values()).sort((a, b) => {
|
|
const primary = {
|
|
wins: [b.wins - a.wins, b.winRate - a.winRate, b.setsWon - a.setsWon, b.pointsWon - a.pointsWon],
|
|
winRate: [b.winRate - a.winRate, b.wins - a.wins, b.setsWon - a.setsWon, b.pointsWon - a.pointsWon],
|
|
setsWon: [b.setsWon - a.setsWon, b.wins - a.wins, b.winRate - a.winRate, b.pointsWon - a.pointsWon],
|
|
pointsWon: [b.pointsWon - a.pointsWon, b.wins - a.wins, b.winRate - a.winRate, b.setsWon - a.setsWon],
|
|
matches: [b.matches - a.matches, b.wins - a.wins, b.winRate - a.winRate, b.pointsWon - a.pointsWon],
|
|
}[sortBy];
|
|
|
|
for (const value of primary) {
|
|
if (value !== 0) return value;
|
|
}
|
|
return a.id - b.id;
|
|
});
|
|
|
|
return sorted.slice(0, limit);
|
|
}
|