| 1 | -- ─── stars ───────────────────────────────────────────────────────── |
| 2 | |
| 3 | -- name: InsertStar :exec |
| 4 | -- ON CONFLICT DO NOTHING is the idempotency guard: re-starring an |
| 5 | -- already-starred repo doesn't double-increment the count (the |
| 6 | -- AFTER INSERT trigger only fires on actual insert). |
| 7 | INSERT INTO stars (user_id, repo_id) VALUES ($1, $2) |
| 8 | ON CONFLICT (user_id, repo_id) DO NOTHING; |
| 9 | |
| 10 | -- name: DeleteStar :exec |
| 11 | DELETE FROM stars WHERE user_id = $1 AND repo_id = $2; |
| 12 | |
| 13 | -- name: HasStar :one |
| 14 | SELECT EXISTS ( |
| 15 | SELECT 1 FROM stars WHERE user_id = $1 AND repo_id = $2 |
| 16 | ) AS has_star; |
| 17 | |
| 18 | -- name: ListStargazersForRepo :many |
| 19 | -- Public-repo stargazer list. Paginated by `starred_at DESC`. |
| 20 | -- Excludes suspended users so they don't taint public lists. The |
| 21 | -- private-repo gate is at the handler layer (policy.IsVisibleTo). |
| 22 | SELECT s.user_id, s.starred_at, u.username, u.display_name |
| 23 | FROM stars s |
| 24 | JOIN users u ON u.id = s.user_id |
| 25 | WHERE s.repo_id = $1 |
| 26 | AND u.suspended_at IS NULL |
| 27 | ORDER BY s.starred_at DESC |
| 28 | LIMIT $2 OFFSET $3; |
| 29 | |
| 30 | -- name: CountStargazersForRepo :one |
| 31 | SELECT COUNT(*) FROM stars s |
| 32 | JOIN users u ON u.id = s.user_id |
| 33 | WHERE s.repo_id = $1 |
| 34 | AND u.suspended_at IS NULL; |
| 35 | |
| 36 | -- name: ListStarsForUser :many |
| 37 | -- The "Stars" profile tab. The handler layer post-filters for repo |
| 38 | -- visibility against the viewer; this query returns everything the |
| 39 | -- user starred and lets the handler decide what to render. Sort axis |
| 40 | -- is the spec's day-1 lean: most-recently-starred first. |
| 41 | SELECT s.repo_id, s.starred_at, |
| 42 | r.name AS repo_name, r.description, r.visibility, |
| 43 | r.star_count, r.primary_language, r.updated_at, |
| 44 | r.owner_user_id, r.owner_org_id, |
| 45 | COALESCE(u.username, o.slug)::text AS owner_slug |
| 46 | FROM stars s |
| 47 | JOIN repos r ON r.id = s.repo_id |
| 48 | LEFT JOIN users u ON u.id = r.owner_user_id |
| 49 | LEFT JOIN orgs o ON o.id = r.owner_org_id |
| 50 | WHERE s.user_id = $1 |
| 51 | AND r.deleted_at IS NULL |
| 52 | ORDER BY s.starred_at DESC |
| 53 | LIMIT $2 OFFSET $3; |
| 54 | |
| 55 | -- name: CountStarsForUser :one |
| 56 | SELECT COUNT(*) FROM stars s |
| 57 | JOIN repos r ON r.id = s.repo_id |
| 58 | WHERE s.user_id = $1 |
| 59 | AND r.deleted_at IS NULL; |
| 60 |