freedakgmail
41 lines
1.2 KiB
SQL
41 lines
1.2 KiB
SQL
-- name: CreateReview :one
|
|
INSERT INTO app_reviews (app_id, version, submitter_id, submit_comment)
|
|
VALUES ($1, $2, $3, $4)
|
|
RETURNING *;
|
|
|
|
-- name: GetReviewByID :one
|
|
SELECT * FROM app_reviews WHERE id = $1;
|
|
|
|
-- name: ListPendingReviews :many
|
|
SELECT r.*, a.name as app_name, a.description as app_description,
|
|
a.icon_url as app_icon, u.name as submitter_name
|
|
FROM app_reviews r
|
|
JOIN applications a ON r.app_id = a.id
|
|
JOIN users u ON r.submitter_id = u.id
|
|
WHERE r.status = 'pending'
|
|
ORDER BY r.submitted_at ASC
|
|
LIMIT $1 OFFSET $2;
|
|
|
|
-- name: CountPendingReviews :one
|
|
SELECT COUNT(*) FROM app_reviews WHERE status = 'pending';
|
|
|
|
-- name: ApproveReview :exec
|
|
UPDATE app_reviews
|
|
SET status = 'approved', reviewer_id = $2, review_comment = $3, reviewed_at = NOW()
|
|
WHERE id = $1;
|
|
|
|
-- name: RejectReview :exec
|
|
UPDATE app_reviews
|
|
SET status = 'rejected', reviewer_id = $2, review_comment = $3, reviewed_at = NOW()
|
|
WHERE id = $1;
|
|
|
|
-- name: WithdrawReview :exec
|
|
UPDATE app_reviews SET status = 'withdrawn' WHERE id = $1 AND status = 'pending';
|
|
|
|
-- name: ListAppReviews :many
|
|
SELECT r.*, u.name as reviewer_name
|
|
FROM app_reviews r
|
|
LEFT JOIN users u ON r.reviewer_id = u.id
|
|
WHERE r.app_id = $1
|
|
ORDER BY r.created_at DESC;
|