Yimaru-BackEnd/db/query/initial_assessment.sql

-- name: CreateAssessmentQuestion :one
INSERT INTO assessment_questions (
    title,
    description,
    question_type,
    difficulty_level,
    points,
    is_active
)
VALUES (
    $1, -- title
    $2, -- description
    $3, -- question_type
    $4, -- difficulty_level
    $5, -- points
    $6  -- is_active
)
RETURNING *;

-- name: GetAssessmentQuestionByID :one
SELECT *
FROM assessment_questions
WHERE id = $1;

-- name: GetActiveAssessmentQuestions :many
SELECT *
FROM assessment_questions
WHERE is_active = true
ORDER BY created_at DESC;

-- name: GetAssessmentQuestionsPaginated :many
SELECT
    COUNT(*) OVER () AS total_count,
    id,
    title,
    description,
    question_type,
    difficulty_level,
    points,
    is_active,
    created_at,
    updated_at
FROM assessment_questions
WHERE ($1 IS NULL OR question_type = $1)
  AND ($2 IS NULL OR difficulty_level = $2)
  AND ($3 IS NULL OR is_active = $3)
LIMIT $4
OFFSET $5;

-- name: UpdateAssessmentQuestion :exec
UPDATE assessment_questions
SET
    title            = COALESCE($1, title),
    description      = COALESCE($2, description),
    question_type    = COALESCE($3, question_type),
    difficulty_level = COALESCE($4, difficulty_level),
    points           = COALESCE($5, points),
    is_active        = COALESCE($6, is_active),
    updated_at       = CURRENT_TIMESTAMP
WHERE id = $7;

-- name: DeleteAssessmentQuestion :exec
DELETE FROM assessment_questions
WHERE id = $1;

-- name: CreateQuestionOption :one
INSERT INTO assessment_question_options (
    question_id,
    option_text,
    option_order,
    is_correct
)
VALUES (
    $1, -- question_id
    $2, -- option_text
    $3, -- option_order
    $4  -- is_correct
)
RETURNING *;

-- name: GetQuestionOptions :many
SELECT *
FROM assessment_question_options
WHERE question_id = $1
ORDER BY option_order;

-- name: DeleteQuestionOptionsByQuestionID :exec
DELETE FROM assessment_question_options
WHERE question_id = $1;

-- name: CreateShortAnswer :one
INSERT INTO assessment_short_answers (
    question_id,
    correct_answer
)
VALUES (
    $1, -- question_id
    $2  -- correct_answer
)
RETURNING *;

-- name: GetShortAnswersByQuestionID :many
SELECT *
FROM assessment_short_answers
WHERE question_id = $1;

--------------------------------------------------------------------------------------

-- name: CreateAssessmentAttempt :one
INSERT INTO assessment_attempts (
    user_id,
    total_questions,
    total_points,
    status
)
VALUES (
    $1, -- user_id
    $2, -- total_questions
    $3, -- total_points
    'IN_PROGRESS'
)
RETURNING *;

-- name: GetAssessmentAttemptByID :one
SELECT *
FROM assessment_attempts
WHERE id = $1;

-- name: GetUserAssessmentAttempts :many
SELECT
    id,
    user_id,
    total_questions,
    total_points,
    score,
    percentage,
    status,
    started_at,
    submitted_at,
    evaluated_at
FROM assessment_attempts
WHERE user_id = $1
ORDER BY started_at DESC;

-- name: SubmitAssessmentAttempt :exec
UPDATE assessment_attempts
SET
    status = 'SUBMITTED',
    submitted_at = CURRENT_TIMESTAMP,
    updated_at = CURRENT_TIMESTAMP
WHERE id = $1;

-- name: AddAttemptQuestion :exec
INSERT INTO assessment_attempt_questions (
    attempt_id,
    question_id,
    question_type,
    points
)
VALUES (
    $1, -- attempt_id
    $2, -- question_id
    $3, -- question_type
    $4  -- points
);

-- name: GetAttemptQuestions :many
SELECT
    aq.question_id,
    aq.question_type,
    aq.points,
    q.title,
    q.description
FROM assessment_attempt_questions aq
JOIN assessment_questions q ON q.id = aq.question_id
WHERE aq.attempt_id = $1;

-- name: UpsertAttemptAnswer :exec
INSERT INTO assessment_attempt_answers (
    attempt_id,
    question_id,
    selected_option_id,
    submitted_text
)
VALUES (
    $1, -- attempt_id
    $2, -- question_id
    $3, -- selected_option_id
    $4  -- submitted_text
)
ON CONFLICT (attempt_id, question_id)
DO UPDATE SET
    selected_option_id = EXCLUDED.selected_option_id,
    submitted_text     = EXCLUDED.submitted_text;

-- name: GetAttemptAnswers :many
SELECT *
FROM assessment_attempt_answers
WHERE attempt_id = $1;

-- name: EvaluateMCQAnswer :exec
UPDATE assessment_attempt_answers a
SET
    is_correct = o.is_correct,
    awarded_points = CASE WHEN o.is_correct THEN q.points ELSE 0 END
FROM assessment_question_options o
JOIN assessment_questions q ON q.id = a.question_id
WHERE a.selected_option_id = o.id
  AND a.attempt_id = $1;

-- name: EvaluateShortAnswer :exec
UPDATE assessment_attempt_answers a
SET
    is_correct = EXISTS (
        SELECT 1
        FROM assessment_short_answers s
        WHERE s.question_id = a.question_id
          AND LOWER(TRIM(s.correct_answer)) = LOWER(TRIM(a.submitted_text))
    ),
    awarded_points = CASE
        WHEN EXISTS (
            SELECT 1
            FROM assessment_short_answers s
            WHERE s.question_id = a.question_id
              AND LOWER(TRIM(s.correct_answer)) = LOWER(TRIM(a.submitted_text))
        )
        THEN q.points
        ELSE 0
    END
FROM assessment_questions q
WHERE a.question_id = q.id
  AND a.attempt_id = $1;

-- name: FinalizeAssessmentAttempt :exec
UPDATE assessment_attempts
SET
    score = sub.total_score,
    percentage = ROUND((sub.total_score::NUMERIC / total_points) * 100, 2),
    status = 'EVALUATED',
    evaluated_at = CURRENT_TIMESTAMP,
    updated_at = CURRENT_TIMESTAMP
FROM (
    SELECT attempt_id, SUM(awarded_points) AS total_score
    FROM assessment_attempt_answers
    WHERE attempt_id = $1
    GROUP BY attempt_id
) sub
WHERE assessment_attempts.id = sub.attempt_id;