feat(backend): create table `submissions` and `testcase_execution`

2 files changed, 41 insertions, 0 deletions

diff --git a/backend/db/models.go b/backend/db/models.go
index 51157bc..431d860 100644
--- a/backend/db/models.go
+++ b/backend/db/models.go
@@ -30,6 +30,15 @@ type Problem struct {
 	Description string
 }
 
+type Submission struct {
+	SubmissionID int32
+	GameID       int32
+	UserID       int32
+	Code         string
+	CodeSize     int32
+	CreatedAt    pgtype.Timestamp
+}
+
 type Testcase struct {
 	TestcaseID int32
 	ProblemID  int32
@@ -37,6 +46,15 @@ type Testcase struct {
 	Stdout     string
 }
 
+type TestcaseExecution struct {
+	TestcaseExecutionID int32
+	SubmissionID        int32
+	TestcaseID          *int32
+	Status              string
+	Stdout              string
+	Stderr              string
+}
+
 type User struct {
 	UserID      int32
 	Username    string
diff --git a/backend/schema.sql b/backend/schema.sql
index 4a1d4b0..d0b6c40 100644
--- a/backend/schema.sql
+++ b/backend/schema.sql
@@ -52,3 +52,26 @@ CREATE TABLE testcases (
     CONSTRAINT fk_problem_id FOREIGN KEY(problem_id) REFERENCES problems(problem_id)
 );
 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,
+    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 testcase_executions (
+    testcase_execution_id SERIAL      PRIMARY KEY,
+    submission_id         INT         NOT NULL,
+    testcase_id           INT,
+    status                VARCHAR(16) NOT NULL,
+    stdout                TEXT        NOT NULL,
+    stderr                TEXT        NOT NULL,
+    CONSTRAINT fk_submission_id FOREIGN KEY(submission_id) REFERENCES submissions(submission_id),
+    CONSTRAINT fk_testcase_id FOREIGN KEY(testcase_id) REFERENCES testcases(testcase_id)
+);
+CREATE INDEX idx_testcase_executions_submission_id ON testcase_executions(submission_id);