feat(backend): create submission_results table

5 files changed, 48 insertions, 4 deletions

diff --git a/backend/db/models.go b/backend/db/models.go
index 49e4b52..d4cf98b 100644
--- a/backend/db/models.go
+++ b/backend/db/models.go
@@ -39,6 +39,15 @@ type Submission struct {
 	CreatedAt    pgtype.Timestamp
 }
 
+type SubmissionResult struct {
+	SubmissionResultID int32
+	SubmissionID       int32
+	Status             string
+	Stdout             string
+	Stderr             string
+	CreatedAt          pgtype.Timestamp
+}
+
 type Testcase struct {
 	TestcaseID int32
 	ProblemID  int32
diff --git a/backend/db/query.sql.go b/backend/db/query.sql.go
index 6c78267..cfb97fd 100644
--- a/backend/db/query.sql.go
+++ b/backend/db/query.sql.go
@@ -36,6 +36,28 @@ func (q *Queries) CreateSubmission(ctx context.Context, arg CreateSubmissionPara
 	return submission_id, err
 }
 
+const createSubmissionResult = `-- name: CreateSubmissionResult :exec
+INSERT INTO submission_results (submission_id, status, stdout, stderr)
+VALUES ($1, $2, $3, $4)
+`
+
+type CreateSubmissionResultParams struct {
+	SubmissionID int32
+	Status       string
+	Stdout       string
+	Stderr       string
+}
+
+func (q *Queries) CreateSubmissionResult(ctx context.Context, arg CreateSubmissionResultParams) error {
+	_, err := q.db.Exec(ctx, createSubmissionResult,
+		arg.SubmissionID,
+		arg.Status,
+		arg.Stdout,
+		arg.Stderr,
+	)
+	return err
+}
+
 const createTestcaseResult = `-- name: CreateTestcaseResult :exec
 INSERT INTO testcase_results (submission_id, testcase_id, status, stdout, stderr)
 VALUES ($1, $2, $3, $4, $5)
diff --git a/backend/query.sql b/backend/query.sql
index 493fdb8..0d32944 100644
--- a/backend/query.sql
+++ b/backend/query.sql
@@ -68,6 +68,10 @@ SELECT * FROM testcases
 WHERE testcases.problem_id = (SELECT problem_id FROM games WHERE game_id = $1)
 ORDER BY testcases.testcase_id;
 
+-- name: CreateSubmissionResult :exec
+INSERT INTO submission_results (submission_id, status, stdout, stderr)
+VALUES ($1, $2, $3, $4);
+
 -- name: CreateTestcaseResult :exec
 INSERT INTO testcase_results (submission_id, testcase_id, status, stdout, stderr)
 VALUES ($1, $2, $3, $4, $5);
diff --git a/backend/schema.sql b/backend/schema.sql
index 7188d72..74d1202 100644
--- a/backend/schema.sql
+++ b/backend/schema.sql
@@ -64,6 +64,17 @@ CREATE TABLE submissions (
     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 TABLE testcase_results (
     testcase_result_id SERIAL      PRIMARY KEY,
     submission_id      INT         NOT NULL,
diff --git a/backend/taskqueue/processor.go b/backend/taskqueue/processor.go
index 135a7d2..b080c46 100644
--- a/backend/taskqueue/processor.go
+++ b/backend/taskqueue/processor.go
@@ -70,9 +70,8 @@ func (p *ExecProcessor) ProcessTask(ctx context.Context, t *asynq.Task) error {
 			return fmt.Errorf("json.Decode failed: %v", err)
 		}
 		if resData.Status != "success" {
-			err := p.q.CreateTestcaseResult(ctx, db.CreateTestcaseResultParams{
+			err := p.q.CreateSubmissionResult(ctx, db.CreateSubmissionResultParams{
 				SubmissionID: submissionID,
-				TestcaseID:   nil,
 				Status:       "compile_error",
 				Stdout:       resData.Stdout,
 				Stderr:       resData.Stderr,
@@ -114,9 +113,8 @@ func (p *ExecProcessor) ProcessTask(ctx context.Context, t *asynq.Task) error {
 			return fmt.Errorf("json.Decode failed: %v", err)
 		}
 		if resData.Status != "success" {
-			err := p.q.CreateTestcaseResult(ctx, db.CreateTestcaseResultParams{
+			err := p.q.CreateSubmissionResult(ctx, db.CreateSubmissionResultParams{
 				SubmissionID: submissionID,
-				TestcaseID:   nil,
 				Status:       "compile_error",
 				Stdout:       resData.Stdout,
 				Stderr:       resData.Stderr,