91 行
3.2 KiB
SQL
91 行
3.2 KiB
SQL
CREATE TABLE `match_sessions` (
|
|
`id` int AUTO_INCREMENT NOT NULL,
|
|
`createdByUserId` int NOT NULL,
|
|
`matchMode` enum('daily','competitive') NOT NULL DEFAULT 'daily',
|
|
`workflowStatus` enum('draft','recording','review_pending','reviewed','finalizing','finalized','cancelled') NOT NULL DEFAULT 'draft',
|
|
`title` varchar(256) NOT NULL,
|
|
`courtName` varchar(128),
|
|
`notes` text,
|
|
`durationMinutes` int NOT NULL DEFAULT 90,
|
|
`scheduledAt` timestamp,
|
|
`startedAt` timestamp,
|
|
`endedAt` timestamp,
|
|
`suggestionStatus` enum('idle','queued','ready','failed') NOT NULL DEFAULT 'idle',
|
|
`suggestionTaskId` varchar(64),
|
|
`suggestedScore` json,
|
|
`suggestedMetrics` json,
|
|
`finalScore` json,
|
|
`finalMetrics` json,
|
|
`reviewNotes` text,
|
|
`reviewSubmittedAt` timestamp,
|
|
`reviewedByUserId` int,
|
|
`reviewedAt` timestamp,
|
|
`finalizedByUserId` int,
|
|
`finalizedAt` timestamp,
|
|
`createdAt` timestamp NOT NULL DEFAULT (now()),
|
|
`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
|
|
CONSTRAINT `match_sessions_id` PRIMARY KEY(`id`)
|
|
);
|
|
--> statement-breakpoint
|
|
|
|
CREATE TABLE `match_participants` (
|
|
`id` int AUTO_INCREMENT NOT NULL,
|
|
`matchId` int NOT NULL,
|
|
`userId` int NOT NULL,
|
|
`playerSlot` enum('player_a','player_b') NOT NULL,
|
|
`cameraSlot` enum('camera_a','camera_b') NOT NULL,
|
|
`cameraStatus` enum('pending','bound','active','completed','failed') NOT NULL DEFAULT 'pending',
|
|
`cameraLabel` varchar(128),
|
|
`cameraVideoId` int,
|
|
`cameraVideoUrl` text,
|
|
`cameraSnapshot` json,
|
|
`isWinner` int NOT NULL DEFAULT 0,
|
|
`suggestedSetsWon` int NOT NULL DEFAULT 0,
|
|
`suggestedGamesWon` int NOT NULL DEFAULT 0,
|
|
`suggestedPointsWon` int NOT NULL DEFAULT 0,
|
|
`finalSetsWon` int NOT NULL DEFAULT 0,
|
|
`finalGamesWon` int NOT NULL DEFAULT 0,
|
|
`finalPointsWon` int NOT NULL DEFAULT 0,
|
|
`suggestedStats` json,
|
|
`finalStats` json,
|
|
`createdAt` timestamp NOT NULL DEFAULT (now()),
|
|
`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
|
|
CONSTRAINT `match_participants_id` PRIMARY KEY(`id`),
|
|
CONSTRAINT `match_participants_match_player_idx` UNIQUE(`matchId`,`playerSlot`),
|
|
CONSTRAINT `match_participants_match_user_idx` UNIQUE(`matchId`,`userId`)
|
|
);
|
|
--> statement-breakpoint
|
|
|
|
CREATE TABLE `match_score_events` (
|
|
`id` int AUTO_INCREMENT NOT NULL,
|
|
`matchId` int NOT NULL,
|
|
`eventIndex` int NOT NULL,
|
|
`source` enum('camera_a','camera_b','system','admin') NOT NULL DEFAULT 'system',
|
|
`eventType` enum('point','game','set','metric','score_suggestion','review_adjustment','finalized') NOT NULL,
|
|
`winnerSlot` enum('player_a','player_b'),
|
|
`matchSecond` int,
|
|
`confidence` float,
|
|
`payload` json,
|
|
`createdByUserId` int,
|
|
`createdAt` timestamp NOT NULL DEFAULT (now()),
|
|
CONSTRAINT `match_score_events_id` PRIMARY KEY(`id`),
|
|
CONSTRAINT `match_score_events_match_event_idx` UNIQUE(`matchId`,`eventIndex`)
|
|
);
|
|
--> statement-breakpoint
|
|
|
|
ALTER TABLE `background_tasks`
|
|
MODIFY COLUMN `type` enum(
|
|
'media_finalize',
|
|
'training_plan_generate',
|
|
'training_plan_adjust',
|
|
'analysis_corrections',
|
|
'pose_correction_multimodal',
|
|
'ntrp_refresh_user',
|
|
'ntrp_refresh_all',
|
|
'market_source_sync',
|
|
'market_watch_refresh',
|
|
'market_push_delivery',
|
|
'match_score_suggest',
|
|
'match_finalize'
|
|
) NOT NULL;
|