646 行
27 KiB
TypeScript
646 行
27 KiB
TypeScript
import { int, mysqlEnum, mysqlTable, text, timestamp, varchar, json, float, uniqueIndex } from "drizzle-orm/mysql-core";
|
|
|
|
/**
|
|
* Core user table - supports both OAuth and simple username login
|
|
*/
|
|
export const users = mysqlTable("users", {
|
|
id: int("id").autoincrement().primaryKey(),
|
|
openId: varchar("openId", { length: 64 }).notNull().unique(),
|
|
name: text("name"),
|
|
email: varchar("email", { length: 320 }),
|
|
loginMethod: varchar("loginMethod", { length: 64 }),
|
|
role: mysqlEnum("role", ["user", "admin"]).default("user").notNull(),
|
|
/** Tennis skill level */
|
|
skillLevel: mysqlEnum("skillLevel", ["beginner", "intermediate", "advanced"]).default("beginner"),
|
|
/** User's training goals */
|
|
trainingGoals: text("trainingGoals"),
|
|
/** NTRP rating (1.0 - 5.0) */
|
|
ntrpRating: float("ntrpRating").default(1.5),
|
|
/** Manual NTRP baseline before automated rating is established */
|
|
manualNtrpRating: float("manualNtrpRating"),
|
|
manualNtrpCapturedAt: timestamp("manualNtrpCapturedAt"),
|
|
/** Training assessment profile */
|
|
heightCm: float("heightCm"),
|
|
weightKg: float("weightKg"),
|
|
sprintSpeedScore: int("sprintSpeedScore"),
|
|
explosivePowerScore: int("explosivePowerScore"),
|
|
agilityScore: int("agilityScore"),
|
|
enduranceScore: int("enduranceScore"),
|
|
flexibilityScore: int("flexibilityScore"),
|
|
coreStabilityScore: int("coreStabilityScore"),
|
|
shoulderMobilityScore: int("shoulderMobilityScore"),
|
|
hipMobilityScore: int("hipMobilityScore"),
|
|
assessmentNotes: text("assessmentNotes"),
|
|
/** Total training sessions completed */
|
|
totalSessions: int("totalSessions").default(0),
|
|
/** Total training minutes */
|
|
totalMinutes: int("totalMinutes").default(0),
|
|
/** Current consecutive check-in streak */
|
|
currentStreak: int("currentStreak").default(0),
|
|
/** Longest ever streak */
|
|
longestStreak: int("longestStreak").default(0),
|
|
/** Total shots across all analyses */
|
|
totalShots: int("totalShots").default(0),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
|
|
lastSignedIn: timestamp("lastSignedIn").defaultNow().notNull(),
|
|
});
|
|
|
|
export type User = typeof users.$inferSelect;
|
|
export type InsertUser = typeof users.$inferInsert;
|
|
|
|
/**
|
|
* Simple username-based login accounts
|
|
*/
|
|
export const usernameAccounts = mysqlTable("username_accounts", {
|
|
id: int("id").autoincrement().primaryKey(),
|
|
username: varchar("username", { length: 64 }).notNull().unique(),
|
|
userId: int("userId").notNull(),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
});
|
|
|
|
export type UsernameAccount = typeof usernameAccounts.$inferSelect;
|
|
|
|
/**
|
|
* Training plans generated for users
|
|
*/
|
|
export const trainingPlans = mysqlTable("training_plans", {
|
|
id: int("id").autoincrement().primaryKey(),
|
|
userId: int("userId").notNull(),
|
|
title: varchar("title", { length: 256 }).notNull(),
|
|
skillLevel: mysqlEnum("skillLevel", ["beginner", "intermediate", "advanced"]).notNull(),
|
|
/** Plan duration in days */
|
|
durationDays: int("durationDays").notNull().default(7),
|
|
/** JSON array of training exercises */
|
|
exercises: json("exercises").notNull(),
|
|
/** Whether this plan is currently active */
|
|
isActive: int("isActive").notNull().default(1),
|
|
/** Auto-adjustment notes from AI analysis */
|
|
adjustmentNotes: text("adjustmentNotes"),
|
|
/** Plan generation version for tracking adjustments */
|
|
version: int("version").notNull().default(1),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
|
|
});
|
|
|
|
export type TrainingPlan = typeof trainingPlans.$inferSelect;
|
|
export type InsertTrainingPlan = typeof trainingPlans.$inferInsert;
|
|
|
|
/**
|
|
* Training videos uploaded by users
|
|
*/
|
|
export const trainingVideos = mysqlTable("training_videos", {
|
|
id: int("id").autoincrement().primaryKey(),
|
|
userId: int("userId").notNull(),
|
|
title: varchar("title", { length: 256 }).notNull(),
|
|
/** S3 file key */
|
|
fileKey: varchar("fileKey", { length: 512 }).notNull(),
|
|
/** CDN URL for the video */
|
|
url: text("url").notNull(),
|
|
/** Video format: webm or mp4 */
|
|
format: varchar("format", { length: 16 }).notNull(),
|
|
/** File size in bytes */
|
|
fileSize: int("fileSize"),
|
|
/** Duration in seconds */
|
|
duration: float("duration"),
|
|
/** Type of exercise in the video */
|
|
exerciseType: varchar("exerciseType", { length: 64 }),
|
|
/** Analysis status */
|
|
analysisStatus: mysqlEnum("analysisStatus", ["pending", "analyzing", "completed", "failed"]).default("pending"),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
|
|
});
|
|
|
|
export type TrainingVideo = typeof trainingVideos.$inferSelect;
|
|
export type InsertTrainingVideo = typeof trainingVideos.$inferInsert;
|
|
|
|
/**
|
|
* Pose analysis results from MediaPipe - enhanced with tennis_analysis features
|
|
*/
|
|
export const poseAnalyses = mysqlTable("pose_analyses", {
|
|
id: int("id").autoincrement().primaryKey(),
|
|
videoId: int("videoId").notNull(),
|
|
userId: int("userId").notNull(),
|
|
/** Overall pose score (0-100) */
|
|
overallScore: float("overallScore"),
|
|
/** JSON object with detailed joint angles and metrics */
|
|
poseMetrics: json("poseMetrics"),
|
|
/** JSON array of detected issues */
|
|
detectedIssues: json("detectedIssues"),
|
|
/** JSON array of correction suggestions */
|
|
corrections: json("corrections"),
|
|
/** Exercise type analyzed */
|
|
exerciseType: varchar("exerciseType", { length: 64 }),
|
|
/** Number of frames analyzed */
|
|
framesAnalyzed: int("framesAnalyzed"),
|
|
/** --- tennis_analysis inspired fields --- */
|
|
/** Number of swings/shots detected */
|
|
shotCount: int("shotCount").default(0),
|
|
/** Average swing speed (estimated from keypoint displacement, px/frame) */
|
|
avgSwingSpeed: float("avgSwingSpeed"),
|
|
/** Max swing speed detected */
|
|
maxSwingSpeed: float("maxSwingSpeed"),
|
|
/** Total body movement distance in pixels */
|
|
totalMovementDistance: float("totalMovementDistance"),
|
|
/** Stroke consistency score (0-100) */
|
|
strokeConsistency: float("strokeConsistency"),
|
|
/** Footwork score (0-100) */
|
|
footworkScore: float("footworkScore"),
|
|
/** Fluidity/smoothness score (0-100) */
|
|
fluidityScore: float("fluidityScore"),
|
|
/** JSON array of key moments [{frame, type, description}] */
|
|
keyMoments: json("keyMoments"),
|
|
/** JSON array of movement trajectory points [{x, y, frame}] */
|
|
movementTrajectory: json("movementTrajectory"),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
});
|
|
|
|
export type PoseAnalysis = typeof poseAnalyses.$inferSelect;
|
|
export type InsertPoseAnalysis = typeof poseAnalyses.$inferInsert;
|
|
|
|
/**
|
|
* Training session records for progress tracking
|
|
*/
|
|
export const trainingRecords = mysqlTable("training_records", {
|
|
id: int("id").autoincrement().primaryKey(),
|
|
userId: int("userId").notNull(),
|
|
planId: int("planId"),
|
|
/** Exercise name/type */
|
|
exerciseName: varchar("exerciseName", { length: 128 }).notNull(),
|
|
exerciseType: varchar("exerciseType", { length: 64 }),
|
|
/** Source of the training fact */
|
|
sourceType: varchar("sourceType", { length: 32 }).default("manual"),
|
|
/** Reference id from source system */
|
|
sourceId: varchar("sourceId", { length: 64 }),
|
|
/** Optional linked video */
|
|
videoId: int("videoId"),
|
|
/** Optional linked plan match */
|
|
linkedPlanId: int("linkedPlanId"),
|
|
matchConfidence: float("matchConfidence"),
|
|
actionCount: int("actionCount").default(0),
|
|
metadata: json("metadata"),
|
|
/** Duration in minutes */
|
|
durationMinutes: int("durationMinutes"),
|
|
/** Completion status */
|
|
completed: int("completed").notNull().default(0),
|
|
/** Optional notes */
|
|
notes: text("notes"),
|
|
/** Pose score if video was analyzed */
|
|
poseScore: float("poseScore"),
|
|
/** Date of training */
|
|
trainingDate: timestamp("trainingDate").defaultNow().notNull(),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
});
|
|
|
|
export type TrainingRecord = typeof trainingRecords.$inferSelect;
|
|
export type InsertTrainingRecord = typeof trainingRecords.$inferInsert;
|
|
|
|
/**
|
|
* Live analysis sessions captured from the realtime camera workflow.
|
|
*/
|
|
export const liveAnalysisSessions = mysqlTable("live_analysis_sessions", {
|
|
id: int("id").autoincrement().primaryKey(),
|
|
userId: int("userId").notNull(),
|
|
title: varchar("title", { length: 256 }).notNull(),
|
|
sessionMode: mysqlEnum("sessionMode", ["practice", "pk"]).default("practice").notNull(),
|
|
status: mysqlEnum("status", ["active", "completed", "aborted"]).default("completed").notNull(),
|
|
startedAt: timestamp("startedAt").defaultNow().notNull(),
|
|
endedAt: timestamp("endedAt"),
|
|
durationMs: int("durationMs").default(0).notNull(),
|
|
dominantAction: varchar("dominantAction", { length: 64 }),
|
|
overallScore: float("overallScore"),
|
|
postureScore: float("postureScore"),
|
|
balanceScore: float("balanceScore"),
|
|
techniqueScore: float("techniqueScore"),
|
|
footworkScore: float("footworkScore"),
|
|
consistencyScore: float("consistencyScore"),
|
|
unknownActionRatio: float("unknownActionRatio"),
|
|
totalSegments: int("totalSegments").default(0).notNull(),
|
|
effectiveSegments: int("effectiveSegments").default(0).notNull(),
|
|
totalActionCount: int("totalActionCount").default(0).notNull(),
|
|
videoId: int("videoId"),
|
|
videoUrl: text("videoUrl"),
|
|
summary: text("summary"),
|
|
feedback: json("feedback"),
|
|
metrics: json("metrics"),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
|
|
});
|
|
|
|
export type LiveAnalysisSession = typeof liveAnalysisSessions.$inferSelect;
|
|
export type InsertLiveAnalysisSession = typeof liveAnalysisSessions.$inferInsert;
|
|
|
|
/**
|
|
* Per-user runtime state for the current live-camera analysis lock.
|
|
*/
|
|
export const liveAnalysisRuntime = mysqlTable("live_analysis_runtime", {
|
|
id: int("id").autoincrement().primaryKey(),
|
|
userId: int("userId").notNull(),
|
|
ownerSid: varchar("ownerSid", { length: 96 }),
|
|
status: mysqlEnum("status", ["idle", "active", "ended"]).default("idle").notNull(),
|
|
title: varchar("title", { length: 256 }),
|
|
sessionMode: mysqlEnum("sessionMode", ["practice", "pk"]).default("practice").notNull(),
|
|
mediaSessionId: varchar("mediaSessionId", { length: 96 }),
|
|
startedAt: timestamp("startedAt"),
|
|
endedAt: timestamp("endedAt"),
|
|
lastHeartbeatAt: timestamp("lastHeartbeatAt"),
|
|
snapshot: json("snapshot"),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
|
|
}, (table) => ({
|
|
userIdUnique: uniqueIndex("live_analysis_runtime_user_idx").on(table.userId),
|
|
}));
|
|
|
|
export type LiveAnalysisRuntime = typeof liveAnalysisRuntime.$inferSelect;
|
|
export type InsertLiveAnalysisRuntime = typeof liveAnalysisRuntime.$inferInsert;
|
|
|
|
/**
|
|
* Action segments extracted from a realtime analysis session.
|
|
*/
|
|
export const liveActionSegments = mysqlTable("live_action_segments", {
|
|
id: int("id").autoincrement().primaryKey(),
|
|
sessionId: int("sessionId").notNull(),
|
|
actionType: varchar("actionType", { length: 64 }).notNull(),
|
|
isUnknown: int("isUnknown").default(0).notNull(),
|
|
startMs: int("startMs").notNull(),
|
|
endMs: int("endMs").notNull(),
|
|
durationMs: int("durationMs").notNull(),
|
|
confidenceAvg: float("confidenceAvg"),
|
|
score: float("score"),
|
|
peakScore: float("peakScore"),
|
|
frameCount: int("frameCount").default(0).notNull(),
|
|
issueSummary: json("issueSummary"),
|
|
keyFrames: json("keyFrames"),
|
|
clipLabel: varchar("clipLabel", { length: 128 }),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
}, (table) => ({
|
|
sessionIndex: uniqueIndex("live_action_segments_session_start_idx").on(table.sessionId, table.startMs),
|
|
}));
|
|
|
|
export type LiveActionSegment = typeof liveActionSegments.$inferSelect;
|
|
export type InsertLiveActionSegment = typeof liveActionSegments.$inferInsert;
|
|
|
|
/**
|
|
* Daily training aggregate used for streaks, achievements and daily NTRP refresh.
|
|
*/
|
|
export const dailyTrainingAggregates = mysqlTable("daily_training_aggregates", {
|
|
id: int("id").autoincrement().primaryKey(),
|
|
dayKey: varchar("dayKey", { length: 32 }).notNull().unique(),
|
|
userId: int("userId").notNull(),
|
|
trainingDate: varchar("trainingDate", { length: 10 }).notNull(),
|
|
totalMinutes: int("totalMinutes").default(0).notNull(),
|
|
sessionCount: int("sessionCount").default(0).notNull(),
|
|
analysisCount: int("analysisCount").default(0).notNull(),
|
|
liveAnalysisCount: int("liveAnalysisCount").default(0).notNull(),
|
|
recordingCount: int("recordingCount").default(0).notNull(),
|
|
pkCount: int("pkCount").default(0).notNull(),
|
|
totalActions: int("totalActions").default(0).notNull(),
|
|
effectiveActions: int("effectiveActions").default(0).notNull(),
|
|
unknownActions: int("unknownActions").default(0).notNull(),
|
|
totalScore: float("totalScore").default(0).notNull(),
|
|
averageScore: float("averageScore").default(0).notNull(),
|
|
metadata: json("metadata"),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
|
|
});
|
|
|
|
export type DailyTrainingAggregate = typeof dailyTrainingAggregates.$inferSelect;
|
|
export type InsertDailyTrainingAggregate = typeof dailyTrainingAggregates.$inferInsert;
|
|
|
|
/**
|
|
* NTRP Rating history - tracks rating changes over time
|
|
*/
|
|
export const ratingHistory = mysqlTable("rating_history", {
|
|
id: int("id").autoincrement().primaryKey(),
|
|
userId: int("userId").notNull(),
|
|
/** NTRP rating at this point */
|
|
rating: float("rating").notNull(),
|
|
/** What triggered this rating update */
|
|
reason: varchar("reason", { length: 256 }),
|
|
/** JSON breakdown of dimension scores */
|
|
dimensionScores: json("dimensionScores"),
|
|
/** Reference analysis ID if applicable */
|
|
analysisId: int("analysisId"),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
});
|
|
|
|
export type RatingHistory = typeof ratingHistory.$inferSelect;
|
|
export type InsertRatingHistory = typeof ratingHistory.$inferInsert;
|
|
|
|
/**
|
|
* Daily NTRP snapshots generated by async refresh jobs.
|
|
*/
|
|
export const ntrpSnapshots = mysqlTable("ntrp_snapshots", {
|
|
id: int("id").autoincrement().primaryKey(),
|
|
snapshotKey: varchar("snapshotKey", { length: 64 }).notNull().unique(),
|
|
userId: int("userId").notNull(),
|
|
snapshotDate: varchar("snapshotDate", { length: 10 }).notNull(),
|
|
rating: float("rating").notNull(),
|
|
triggerType: mysqlEnum("triggerType", ["analysis", "daily", "manual"]).default("daily").notNull(),
|
|
taskId: varchar("taskId", { length: 64 }),
|
|
dimensionScores: json("dimensionScores"),
|
|
sourceSummary: json("sourceSummary"),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
});
|
|
|
|
export type NtrpSnapshot = typeof ntrpSnapshots.$inferSelect;
|
|
export type InsertNtrpSnapshot = typeof ntrpSnapshots.$inferInsert;
|
|
|
|
/**
|
|
* Daily check-in records for streak tracking
|
|
*/
|
|
export const dailyCheckins = mysqlTable("daily_checkins", {
|
|
id: int("id").autoincrement().primaryKey(),
|
|
userId: int("userId").notNull(),
|
|
/** Check-in date (YYYY-MM-DD stored as string for easy comparison) */
|
|
checkinDate: varchar("checkinDate", { length: 10 }).notNull(),
|
|
/** Current streak at the time of check-in */
|
|
streakCount: int("streakCount").notNull().default(1),
|
|
/** Optional notes for the day */
|
|
notes: text("notes"),
|
|
/** Training minutes logged this day */
|
|
minutesTrained: int("minutesTrained").default(0),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
});
|
|
|
|
export type DailyCheckin = typeof dailyCheckins.$inferSelect;
|
|
export type InsertDailyCheckin = typeof dailyCheckins.$inferInsert;
|
|
|
|
/**
|
|
* Achievement badges earned by users
|
|
*/
|
|
export const userBadges = mysqlTable("user_badges", {
|
|
id: int("id").autoincrement().primaryKey(),
|
|
userId: int("userId").notNull(),
|
|
/** Badge identifier key */
|
|
badgeKey: varchar("badgeKey", { length: 64 }).notNull(),
|
|
/** When the badge was earned */
|
|
earnedAt: timestamp("earnedAt").defaultNow().notNull(),
|
|
});
|
|
|
|
export type UserBadge = typeof userBadges.$inferSelect;
|
|
export type InsertUserBadge = typeof userBadges.$inferInsert;
|
|
|
|
/**
|
|
* Achievement definitions that can scale beyond the legacy badge system.
|
|
*/
|
|
export const achievementDefinitions = mysqlTable("achievement_definitions", {
|
|
id: int("id").autoincrement().primaryKey(),
|
|
key: varchar("key", { length: 64 }).notNull().unique(),
|
|
name: varchar("name", { length: 128 }).notNull(),
|
|
description: text("description"),
|
|
category: varchar("category", { length: 32 }).notNull(),
|
|
rarity: varchar("rarity", { length: 16 }).default("common").notNull(),
|
|
icon: varchar("icon", { length: 16 }).default("🎾").notNull(),
|
|
metricKey: varchar("metricKey", { length: 64 }).notNull(),
|
|
targetValue: float("targetValue").notNull(),
|
|
tier: int("tier").default(1).notNull(),
|
|
isHidden: int("isHidden").default(0).notNull(),
|
|
isActive: int("isActive").default(1).notNull(),
|
|
sortOrder: int("sortOrder").default(0).notNull(),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
|
|
});
|
|
|
|
export type AchievementDefinition = typeof achievementDefinitions.$inferSelect;
|
|
export type InsertAchievementDefinition = typeof achievementDefinitions.$inferInsert;
|
|
|
|
/**
|
|
* User achievement progress and unlock records.
|
|
*/
|
|
export const userAchievements = mysqlTable("user_achievements", {
|
|
id: int("id").autoincrement().primaryKey(),
|
|
progressKey: varchar("progressKey", { length: 96 }).notNull().unique(),
|
|
userId: int("userId").notNull(),
|
|
achievementKey: varchar("achievementKey", { length: 64 }).notNull(),
|
|
currentValue: float("currentValue").default(0).notNull(),
|
|
progressPct: float("progressPct").default(0).notNull(),
|
|
unlockedAt: timestamp("unlockedAt"),
|
|
lastEvaluatedAt: timestamp("lastEvaluatedAt").defaultNow().notNull(),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
|
|
});
|
|
|
|
export type UserAchievement = typeof userAchievements.$inferSelect;
|
|
export type InsertUserAchievement = typeof userAchievements.$inferInsert;
|
|
|
|
/**
|
|
* Tutorial video library - professional coaching reference videos
|
|
*/
|
|
export const tutorialVideos = mysqlTable("tutorial_videos", {
|
|
id: int("id").autoincrement().primaryKey(),
|
|
slug: varchar("slug", { length: 128 }),
|
|
title: varchar("title", { length: 256 }).notNull(),
|
|
category: varchar("category", { length: 64 }).notNull(),
|
|
skillLevel: mysqlEnum("skillLevel", ["beginner", "intermediate", "advanced"]).default("beginner"),
|
|
topicArea: varchar("topicArea", { length: 32 }).default("tennis_skill"),
|
|
contentFormat: varchar("contentFormat", { length: 16 }).default("video"),
|
|
sourcePlatform: varchar("sourcePlatform", { length: 16 }).default("none"),
|
|
description: text("description"),
|
|
heroSummary: text("heroSummary"),
|
|
keyPoints: json("keyPoints"),
|
|
commonMistakes: json("commonMistakes"),
|
|
videoUrl: text("videoUrl"),
|
|
externalUrl: text("externalUrl"),
|
|
platformVideoId: varchar("platformVideoId", { length: 64 }),
|
|
thumbnailUrl: text("thumbnailUrl"),
|
|
duration: int("duration"),
|
|
estimatedEffortMinutes: int("estimatedEffortMinutes"),
|
|
prerequisites: json("prerequisites"),
|
|
learningObjectives: json("learningObjectives"),
|
|
stepSections: json("stepSections"),
|
|
deliverables: json("deliverables"),
|
|
relatedDocPaths: json("relatedDocPaths"),
|
|
viewCount: int("viewCount"),
|
|
commentCount: int("commentCount"),
|
|
metricsFetchedAt: timestamp("metricsFetchedAt"),
|
|
completionAchievementKey: varchar("completionAchievementKey", { length: 64 }),
|
|
isFeatured: int("isFeatured").default(0),
|
|
featuredOrder: int("featuredOrder").default(0),
|
|
sortOrder: int("sortOrder").default(0),
|
|
isPublished: int("isPublished").default(1),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
|
|
});
|
|
|
|
export type TutorialVideo = typeof tutorialVideos.$inferSelect;
|
|
export type InsertTutorialVideo = typeof tutorialVideos.$inferInsert;
|
|
|
|
/**
|
|
* User tutorial progress tracking
|
|
*/
|
|
export const tutorialProgress = mysqlTable("tutorial_progress", {
|
|
id: int("id").autoincrement().primaryKey(),
|
|
userId: int("userId").notNull(),
|
|
tutorialId: int("tutorialId").notNull(),
|
|
watched: int("watched").default(0),
|
|
completed: int("completed").default(0),
|
|
completedAt: timestamp("completedAt"),
|
|
comparisonVideoId: int("comparisonVideoId"),
|
|
selfScore: float("selfScore"),
|
|
notes: text("notes"),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
|
|
});
|
|
|
|
export type TutorialProgress = typeof tutorialProgress.$inferSelect;
|
|
export type InsertTutorialProgress = typeof tutorialProgress.$inferInsert;
|
|
|
|
/**
|
|
* Training reminders
|
|
*/
|
|
export const trainingReminders = mysqlTable("training_reminders", {
|
|
id: int("id").autoincrement().primaryKey(),
|
|
userId: int("userId").notNull(),
|
|
reminderType: varchar("reminderType", { length: 32 }).notNull(),
|
|
title: varchar("title", { length: 256 }).notNull(),
|
|
message: text("message"),
|
|
timeOfDay: varchar("timeOfDay", { length: 5 }).notNull(),
|
|
daysOfWeek: json("daysOfWeek").notNull(),
|
|
isActive: int("isActive").default(1),
|
|
lastTriggered: timestamp("lastTriggered"),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
|
|
});
|
|
|
|
export type TrainingReminder = typeof trainingReminders.$inferSelect;
|
|
export type InsertTrainingReminder = typeof trainingReminders.$inferInsert;
|
|
|
|
/**
|
|
* Notification log
|
|
*/
|
|
export const notificationLog = mysqlTable("notification_log", {
|
|
id: int("id").autoincrement().primaryKey(),
|
|
userId: int("userId").notNull(),
|
|
reminderId: int("reminderId"),
|
|
notificationType: varchar("notificationType", { length: 32 }).notNull(),
|
|
title: varchar("title", { length: 256 }).notNull(),
|
|
message: text("message"),
|
|
isRead: int("isRead").default(0),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
});
|
|
|
|
export type NotificationLogEntry = typeof notificationLog.$inferSelect;
|
|
export type InsertNotificationLog = typeof notificationLog.$inferInsert;
|
|
|
|
/**
|
|
* Background task queue for long-running or retryable work.
|
|
*/
|
|
export const backgroundTasks = mysqlTable("background_tasks", {
|
|
id: varchar("id", { length: 36 }).primaryKey(),
|
|
userId: int("userId").notNull(),
|
|
type: mysqlEnum("type", [
|
|
"media_finalize",
|
|
"training_plan_generate",
|
|
"training_plan_adjust",
|
|
"analysis_corrections",
|
|
"pose_correction_multimodal",
|
|
"ntrp_refresh_user",
|
|
"ntrp_refresh_all",
|
|
]).notNull(),
|
|
status: mysqlEnum("status", ["queued", "running", "succeeded", "failed"]).notNull().default("queued"),
|
|
title: varchar("title", { length: 256 }).notNull(),
|
|
message: text("message"),
|
|
progress: int("progress").notNull().default(0),
|
|
payload: json("payload").notNull(),
|
|
result: json("result"),
|
|
error: text("error"),
|
|
attempts: int("attempts").notNull().default(0),
|
|
maxAttempts: int("maxAttempts").notNull().default(3),
|
|
workerId: varchar("workerId", { length: 96 }),
|
|
runAfter: timestamp("runAfter").defaultNow().notNull(),
|
|
lockedAt: timestamp("lockedAt"),
|
|
startedAt: timestamp("startedAt"),
|
|
completedAt: timestamp("completedAt"),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
|
|
});
|
|
|
|
export type BackgroundTask = typeof backgroundTasks.$inferSelect;
|
|
export type InsertBackgroundTask = typeof backgroundTasks.$inferInsert;
|
|
|
|
/**
|
|
* Admin audit trail for privileged actions.
|
|
*/
|
|
export const adminAuditLogs = mysqlTable("admin_audit_logs", {
|
|
id: int("id").autoincrement().primaryKey(),
|
|
adminUserId: int("adminUserId").notNull(),
|
|
actionType: varchar("actionType", { length: 64 }).notNull(),
|
|
entityType: varchar("entityType", { length: 64 }).notNull(),
|
|
entityId: varchar("entityId", { length: 96 }),
|
|
targetUserId: int("targetUserId"),
|
|
payload: json("payload"),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
});
|
|
|
|
export type AdminAuditLog = typeof adminAuditLogs.$inferSelect;
|
|
export type InsertAdminAuditLog = typeof adminAuditLogs.$inferInsert;
|
|
|
|
/**
|
|
* App settings editable from the admin console.
|
|
*/
|
|
export const appSettings = mysqlTable("app_settings", {
|
|
id: int("id").autoincrement().primaryKey(),
|
|
settingKey: varchar("settingKey", { length: 64 }).notNull().unique(),
|
|
label: varchar("label", { length: 128 }).notNull(),
|
|
description: text("description"),
|
|
value: json("value"),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
|
|
});
|
|
|
|
export type AppSetting = typeof appSettings.$inferSelect;
|
|
export type InsertAppSetting = typeof appSettings.$inferInsert;
|
|
|
|
/**
|
|
* Vision reference library - canonical public tennis images used for multimodal evaluation
|
|
*/
|
|
export const visionReferenceImages = mysqlTable("vision_reference_images", {
|
|
id: int("id").autoincrement().primaryKey(),
|
|
slug: varchar("slug", { length: 128 }).notNull().unique(),
|
|
title: varchar("title", { length: 256 }).notNull(),
|
|
exerciseType: varchar("exerciseType", { length: 64 }).notNull(),
|
|
imageUrl: text("imageUrl").notNull(),
|
|
sourcePageUrl: text("sourcePageUrl").notNull(),
|
|
sourceLabel: varchar("sourceLabel", { length: 128 }).notNull(),
|
|
author: varchar("author", { length: 128 }),
|
|
license: varchar("license", { length: 128 }),
|
|
expectedFocus: json("expectedFocus"),
|
|
tags: json("tags"),
|
|
notes: text("notes"),
|
|
sortOrder: int("sortOrder").default(0).notNull(),
|
|
isPublished: int("isPublished").default(1).notNull(),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
|
|
});
|
|
|
|
export type VisionReferenceImage = typeof visionReferenceImages.$inferSelect;
|
|
export type InsertVisionReferenceImage = typeof visionReferenceImages.$inferInsert;
|
|
|
|
/**
|
|
* Vision test run history - records each multimodal evaluation against the standard library
|
|
*/
|
|
export const visionTestRuns = mysqlTable("vision_test_runs", {
|
|
id: int("id").autoincrement().primaryKey(),
|
|
taskId: varchar("taskId", { length: 64 }).notNull().unique(),
|
|
userId: int("userId").notNull(),
|
|
referenceImageId: int("referenceImageId"),
|
|
title: varchar("title", { length: 256 }).notNull(),
|
|
exerciseType: varchar("exerciseType", { length: 64 }).notNull(),
|
|
imageUrl: text("imageUrl").notNull(),
|
|
status: mysqlEnum("status", ["queued", "succeeded", "failed"]).default("queued").notNull(),
|
|
visionStatus: mysqlEnum("visionStatus", ["pending", "ok", "fallback", "failed"]).default("pending").notNull(),
|
|
configuredModel: varchar("configuredModel", { length: 128 }),
|
|
expectedFocus: json("expectedFocus"),
|
|
summary: text("summary"),
|
|
corrections: text("corrections"),
|
|
report: json("report"),
|
|
warning: text("warning"),
|
|
error: text("error"),
|
|
createdAt: timestamp("createdAt").defaultNow().notNull(),
|
|
updatedAt: timestamp("updatedAt").defaultNow().onUpdateNow().notNull(),
|
|
});
|
|
|
|
export type VisionTestRun = typeof visionTestRuns.$inferSelect;
|
|
export type InsertVisionTestRun = typeof visionTestRuns.$inferInsert;
|