Merge branch 'phperkaigi-2025'

1 files changed, 24 insertions, 27 deletions

diff --git a/backend/schema.sql b/backend/schema.sql
index dc49363..8c63ff2 100644
--- a/backend/schema.sql
+++ b/backend/schema.sql
@@ -17,22 +17,17 @@ CREATE TABLE user_auths (
 );
 CREATE INDEX idx_user_auths_user_id ON user_auths(user_id);
 
-CREATE TABLE registration_tokens (
-    registration_token_id SERIAL   PRIMARY KEY,
-    token                 CHAR(16) NOT NULL
-);
-CREATE INDEX idx_registration_tokens_token ON registration_tokens(token);
-
 CREATE TABLE problems (
     problem_id  SERIAL       PRIMARY KEY,
     title       VARCHAR(255) NOT NULL,
-    description TEXT         NOT NULL
+    description TEXT         NOT NULL,
+    sample_code TEXT         NOT NULL
 );
 
 CREATE TABLE games (
     game_id          SERIAL       PRIMARY KEY,
     game_type        VARCHAR(16)  NOT NULL,
-    state            VARCHAR(32)  NOT NULL,
+    is_public        BOOLEAN      NOT NULL,
     display_name     VARCHAR(255) NOT NULL,
     duration_seconds INT          NOT NULL,
     created_at       TIMESTAMP    NOT NULL DEFAULT NOW(),
@@ -42,7 +37,7 @@ CREATE TABLE games (
 );
 CREATE INDEX idx_games_problem_id ON games(problem_id);
 
-CREATE TABLE game_players (
+CREATE TABLE game_main_players (
     game_id INT NOT NULL,
     user_id INT NOT NULL,
     PRIMARY KEY (game_id, user_id),
@@ -50,6 +45,18 @@ CREATE TABLE game_players (
     CONSTRAINT fk_user_id FOREIGN KEY(user_id) REFERENCES users(user_id)
 );
 
+CREATE TABLE game_states (
+    game_id INT NOT NULL,
+    user_id INT NOT NULL,
+    code TEXT NOT NULL,
+    status VARCHAR(16) NOT NULL,
+    best_score_submission_id INT,
+    PRIMARY KEY (game_id, user_id),
+    CONSTRAINT fk_game_id FOREIGN KEY(game_id) REFERENCES games(game_id),
+    CONSTRAINT fk_user_id FOREIGN KEY(user_id) REFERENCES users(user_id),
+    CONSTRAINT fk_best_score_submission_id FOREIGN KEY(best_score_submission_id) REFERENCES submissions(submission_id)
+);
+
 CREATE TABLE testcases (
     testcase_id SERIAL PRIMARY KEY,
     problem_id  INT    NOT NULL,
@@ -60,27 +67,17 @@ CREATE TABLE testcases (
 CREATE INDEX idx_testcases_problem_id ON testcases(problem_id);
 
 CREATE TABLE submissions (
-    submission_id SERIAL    PRIMARY KEY,
-    game_id       INT       NOT NULL,
-    user_id       INT       NOT NULL,
-    code          TEXT      NOT NULL,
-    code_size     INT       NOT NULL,
-    code_hash     CHAR(32)  NOT NULL,
-    created_at    TIMESTAMP NOT NULL DEFAULT NOW(),
+    submission_id SERIAL      PRIMARY KEY,
+    game_id       INT         NOT NULL,
+    user_id       INT         NOT NULL,
+    code          TEXT        NOT NULL,
+    code_size     INT         NOT NULL,
+    status        VARCHAR(16) NOT NULL,
+    created_at    TIMESTAMP   NOT NULL DEFAULT NOW(),
     CONSTRAINT fk_game_id FOREIGN KEY(game_id) REFERENCES games(game_id),
     CONSTRAINT fk_user_id FOREIGN KEY(user_id) REFERENCES users(user_id)
 );
-
-CREATE TABLE submission_results (
-    submission_result_id SERIAL      PRIMARY KEY,
-    submission_id        INT         NOT NULL UNIQUE,
-    status               VARCHAR(16) NOT NULL,
-    stdout               TEXT        NOT NULL,
-    stderr               TEXT        NOT NULL,
-    created_at           TIMESTAMP   NOT NULL DEFAULT NOW(),
-    CONSTRAINT fk_submission_id FOREIGN KEY(submission_id) REFERENCES submissions(submission_id)
-);
-CREATE INDEX idx_submission_results_submission_id ON submission_results(submission_id);
+CREATE INDEX idx_submissions_game_id_user_id ON submissions(game_id, user_id);
 
 CREATE TABLE testcase_results (
     testcase_result_id SERIAL      PRIMARY KEY,