A community based topic aggregation platform built on atproto
bretton.dev
1package postgres
2
3import (
4 "Coves/internal/core/users"
5 "context"
6 "database/sql"
7 "fmt"
8 "strings"
9
10 "github.com/lib/pq"
11)
12
13type postgresUserRepo struct {
14 db *sql.DB
15}
16
17// NewUserRepository creates a new PostgreSQL user repository
18func NewUserRepository(db *sql.DB) users.UserRepository {
19 return &postgresUserRepo{db: db}
20}
21
22// Create inserts a new user into the users table
23func (r *postgresUserRepo) Create(ctx context.Context, user *users.User) (*users.User, error) {
24 query := `
25 INSERT INTO users (did, handle, pds_url)
26 VALUES ($1, $2, $3)
27 RETURNING did, handle, pds_url, created_at, updated_at`
28
29 err := r.db.QueryRowContext(ctx, query, user.DID, user.Handle, user.PDSURL).
30 Scan(&user.DID, &user.Handle, &user.PDSURL, &user.CreatedAt, &user.UpdatedAt)
31 if err != nil {
32 // Check for unique constraint violations
33 if strings.Contains(err.Error(), "duplicate key") {
34 if strings.Contains(err.Error(), "users_pkey") {
35 return nil, fmt.Errorf("user with DID already exists")
36 }
37 if strings.Contains(err.Error(), "users_handle_key") {
38 return nil, fmt.Errorf("handle already taken")
39 }
40 }
41 return nil, fmt.Errorf("failed to create user: %w", err)
42 }
43
44 return user, nil
45}
46
47// GetByDID retrieves a user by their DID
48func (r *postgresUserRepo) GetByDID(ctx context.Context, did string) (*users.User, error) {
49 user := &users.User{}
50 query := `SELECT did, handle, pds_url, created_at, updated_at FROM users WHERE did = $1`
51
52 err := r.db.QueryRowContext(ctx, query, did).
53 Scan(&user.DID, &user.Handle, &user.PDSURL, &user.CreatedAt, &user.UpdatedAt)
54
55 if err == sql.ErrNoRows {
56 return nil, fmt.Errorf("user not found")
57 }
58 if err != nil {
59 return nil, fmt.Errorf("failed to get user by DID: %w", err)
60 }
61
62 return user, nil
63}
64
65// GetByHandle retrieves a user by their handle
66func (r *postgresUserRepo) GetByHandle(ctx context.Context, handle string) (*users.User, error) {
67 user := &users.User{}
68 query := `SELECT did, handle, pds_url, created_at, updated_at FROM users WHERE handle = $1`
69
70 err := r.db.QueryRowContext(ctx, query, handle).
71 Scan(&user.DID, &user.Handle, &user.PDSURL, &user.CreatedAt, &user.UpdatedAt)
72
73 if err == sql.ErrNoRows {
74 return nil, fmt.Errorf("user not found")
75 }
76 if err != nil {
77 return nil, fmt.Errorf("failed to get user by handle: %w", err)
78 }
79
80 return user, nil
81}
82
83// UpdateHandle updates the handle for a user with the given DID
84func (r *postgresUserRepo) UpdateHandle(ctx context.Context, did, newHandle string) (*users.User, error) {
85 user := &users.User{}
86 query := `
87 UPDATE users
88 SET handle = $2, updated_at = NOW()
89 WHERE did = $1
90 RETURNING did, handle, pds_url, created_at, updated_at`
91
92 err := r.db.QueryRowContext(ctx, query, did, newHandle).
93 Scan(&user.DID, &user.Handle, &user.PDSURL, &user.CreatedAt, &user.UpdatedAt)
94
95 if err == sql.ErrNoRows {
96 return nil, fmt.Errorf("user not found")
97 }
98 if err != nil {
99 // Check for unique constraint violation on handle
100 if strings.Contains(err.Error(), "duplicate key") && strings.Contains(err.Error(), "users_handle_key") {
101 return nil, fmt.Errorf("handle already taken")
102 }
103 return nil, fmt.Errorf("failed to update handle: %w", err)
104 }
105
106 return user, nil
107}
108
109// GetByDIDs retrieves multiple users by their DIDs in a single query
110// Returns a map of DID -> User for efficient lookups
111// Missing users are not included in the result map (no error for missing users)
112func (r *postgresUserRepo) GetByDIDs(ctx context.Context, dids []string) (map[string]*users.User, error) {
113 if len(dids) == 0 {
114 return make(map[string]*users.User), nil
115 }
116
117 // Build parameterized query with IN clause
118 // Use ANY($1) for PostgreSQL array support with pq.Array() for type conversion
119 query := `SELECT did, handle, pds_url, created_at, updated_at FROM users WHERE did = ANY($1)`
120
121 rows, err := r.db.QueryContext(ctx, query, pq.Array(dids))
122 if err != nil {
123 return nil, fmt.Errorf("failed to query users by DIDs: %w", err)
124 }
125 defer rows.Close()
126
127 // Build map of results
128 result := make(map[string]*users.User, len(dids))
129 for rows.Next() {
130 user := &users.User{}
131 err := rows.Scan(&user.DID, &user.Handle, &user.PDSURL, &user.CreatedAt, &user.UpdatedAt)
132 if err != nil {
133 return nil, fmt.Errorf("failed to scan user row: %w", err)
134 }
135 result[user.DID] = user
136 }
137
138 if err = rows.Err(); err != nil {
139 return nil, fmt.Errorf("error iterating user rows: %w", err)
140 }
141
142 return result, nil
143}