A community based topic aggregation platform built on atproto
bretton.dev
1package postgres
2
3import (
4 "context"
5 "database/sql"
6 "fmt"
7 "log"
8 "strings"
9
10 "Coves/internal/core/communities"
11)
12
13// CreateMembership creates a new membership record
14func (r *postgresCommunityRepo) CreateMembership(ctx context.Context, membership *communities.Membership) (*communities.Membership, error) {
15 query := `
16 INSERT INTO community_memberships (
17 user_did, community_did, reputation_score, contribution_count,
18 joined_at, last_active_at, is_banned, is_moderator
19 )
20 VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
21 RETURNING id, joined_at, last_active_at`
22
23 err := r.db.QueryRowContext(ctx, query,
24 membership.UserDID,
25 membership.CommunityDID,
26 membership.ReputationScore,
27 membership.ContributionCount,
28 membership.JoinedAt,
29 membership.LastActiveAt,
30 membership.IsBanned,
31 membership.IsModerator,
32 ).Scan(&membership.ID, &membership.JoinedAt, &membership.LastActiveAt)
33 if err != nil {
34 if strings.Contains(err.Error(), "duplicate key") {
35 return nil, fmt.Errorf("membership already exists")
36 }
37 if strings.Contains(err.Error(), "foreign key") {
38 return nil, communities.ErrCommunityNotFound
39 }
40 return nil, fmt.Errorf("failed to create membership: %w", err)
41 }
42
43 return membership, nil
44}
45
46// GetMembership retrieves a specific membership
47func (r *postgresCommunityRepo) GetMembership(ctx context.Context, userDID, communityDID string) (*communities.Membership, error) {
48 membership := &communities.Membership{}
49 query := `
50 SELECT id, user_did, community_did, reputation_score, contribution_count,
51 joined_at, last_active_at, is_banned, is_moderator
52 FROM community_memberships
53 WHERE user_did = $1 AND community_did = $2`
54
55 err := r.db.QueryRowContext(ctx, query, userDID, communityDID).Scan(
56 &membership.ID,
57 &membership.UserDID,
58 &membership.CommunityDID,
59 &membership.ReputationScore,
60 &membership.ContributionCount,
61 &membership.JoinedAt,
62 &membership.LastActiveAt,
63 &membership.IsBanned,
64 &membership.IsModerator,
65 )
66
67 if err == sql.ErrNoRows {
68 return nil, communities.ErrMembershipNotFound
69 }
70 if err != nil {
71 return nil, fmt.Errorf("failed to get membership: %w", err)
72 }
73
74 return membership, nil
75}
76
77// UpdateMembership updates an existing membership
78func (r *postgresCommunityRepo) UpdateMembership(ctx context.Context, membership *communities.Membership) (*communities.Membership, error) {
79 query := `
80 UPDATE community_memberships
81 SET reputation_score = $3,
82 contribution_count = $4,
83 last_active_at = $5,
84 is_banned = $6,
85 is_moderator = $7
86 WHERE user_did = $1 AND community_did = $2
87 RETURNING last_active_at`
88
89 err := r.db.QueryRowContext(ctx, query,
90 membership.UserDID,
91 membership.CommunityDID,
92 membership.ReputationScore,
93 membership.ContributionCount,
94 membership.LastActiveAt,
95 membership.IsBanned,
96 membership.IsModerator,
97 ).Scan(&membership.LastActiveAt)
98
99 if err == sql.ErrNoRows {
100 return nil, communities.ErrMembershipNotFound
101 }
102 if err != nil {
103 return nil, fmt.Errorf("failed to update membership: %w", err)
104 }
105
106 return membership, nil
107}
108
109// ListMembers retrieves members of a community ordered by reputation
110func (r *postgresCommunityRepo) ListMembers(ctx context.Context, communityDID string, limit, offset int) ([]*communities.Membership, error) {
111 query := `
112 SELECT id, user_did, community_did, reputation_score, contribution_count,
113 joined_at, last_active_at, is_banned, is_moderator
114 FROM community_memberships
115 WHERE community_did = $1
116 ORDER BY reputation_score DESC, joined_at ASC
117 LIMIT $2 OFFSET $3`
118
119 rows, err := r.db.QueryContext(ctx, query, communityDID, limit, offset)
120 if err != nil {
121 return nil, fmt.Errorf("failed to list members: %w", err)
122 }
123 defer func() {
124 if closeErr := rows.Close(); closeErr != nil {
125 log.Printf("Failed to close rows: %v", closeErr)
126 }
127 }()
128
129 result := []*communities.Membership{}
130 for rows.Next() {
131 membership := &communities.Membership{}
132
133 scanErr := rows.Scan(
134 &membership.ID,
135 &membership.UserDID,
136 &membership.CommunityDID,
137 &membership.ReputationScore,
138 &membership.ContributionCount,
139 &membership.JoinedAt,
140 &membership.LastActiveAt,
141 &membership.IsBanned,
142 &membership.IsModerator,
143 )
144 if scanErr != nil {
145 return nil, fmt.Errorf("failed to scan member: %w", scanErr)
146 }
147
148 result = append(result, membership)
149 }
150
151 if err = rows.Err(); err != nil {
152 return nil, fmt.Errorf("error iterating members: %w", err)
153 }
154
155 return result, nil
156}
157
158// CreateModerationAction records a moderation action
159func (r *postgresCommunityRepo) CreateModerationAction(ctx context.Context, action *communities.ModerationAction) (*communities.ModerationAction, error) {
160 query := `
161 INSERT INTO community_moderation (
162 community_did, action, reason, instance_did, broadcast, created_at, expires_at
163 )
164 VALUES ($1, $2, $3, $4, $5, $6, $7)
165 RETURNING id, created_at`
166
167 err := r.db.QueryRowContext(ctx, query,
168 action.CommunityDID,
169 action.Action,
170 nullString(action.Reason),
171 action.InstanceDID,
172 action.Broadcast,
173 action.CreatedAt,
174 action.ExpiresAt,
175 ).Scan(&action.ID, &action.CreatedAt)
176 if err != nil {
177 if strings.Contains(err.Error(), "foreign key") {
178 return nil, communities.ErrCommunityNotFound
179 }
180 return nil, fmt.Errorf("failed to create moderation action: %w", err)
181 }
182
183 return action, nil
184}
185
186// ListModerationActions retrieves moderation actions for a community
187func (r *postgresCommunityRepo) ListModerationActions(ctx context.Context, communityDID string, limit, offset int) ([]*communities.ModerationAction, error) {
188 query := `
189 SELECT id, community_did, action, reason, instance_did, broadcast, created_at, expires_at
190 FROM community_moderation
191 WHERE community_did = $1
192 ORDER BY created_at DESC
193 LIMIT $2 OFFSET $3`
194
195 rows, err := r.db.QueryContext(ctx, query, communityDID, limit, offset)
196 if err != nil {
197 return nil, fmt.Errorf("failed to list moderation actions: %w", err)
198 }
199 defer func() {
200 if closeErr := rows.Close(); closeErr != nil {
201 log.Printf("Failed to close rows: %v", closeErr)
202 }
203 }()
204
205 result := []*communities.ModerationAction{}
206 for rows.Next() {
207 action := &communities.ModerationAction{}
208 var reason sql.NullString
209
210 scanErr := rows.Scan(
211 &action.ID,
212 &action.CommunityDID,
213 &action.Action,
214 &reason,
215 &action.InstanceDID,
216 &action.Broadcast,
217 &action.CreatedAt,
218 &action.ExpiresAt,
219 )
220 if scanErr != nil {
221 return nil, fmt.Errorf("failed to scan moderation action: %w", scanErr)
222 }
223
224 action.Reason = reason.String
225 result = append(result, action)
226 }
227
228 if err = rows.Err(); err != nil {
229 return nil, fmt.Errorf("error iterating moderation actions: %w", err)
230 }
231
232 return result, nil
233}
234
235// Statistics methods
236func (r *postgresCommunityRepo) IncrementMemberCount(ctx context.Context, communityDID string) error {
237 query := `UPDATE communities SET member_count = member_count + 1 WHERE did = $1`
238 _, err := r.db.ExecContext(ctx, query, communityDID)
239 if err != nil {
240 return fmt.Errorf("failed to increment member count: %w", err)
241 }
242 return nil
243}
244
245func (r *postgresCommunityRepo) DecrementMemberCount(ctx context.Context, communityDID string) error {
246 query := `UPDATE communities SET member_count = GREATEST(0, member_count - 1) WHERE did = $1`
247 _, err := r.db.ExecContext(ctx, query, communityDID)
248 if err != nil {
249 return fmt.Errorf("failed to decrement member count: %w", err)
250 }
251 return nil
252}
253
254func (r *postgresCommunityRepo) IncrementSubscriberCount(ctx context.Context, communityDID string) error {
255 query := `UPDATE communities SET subscriber_count = subscriber_count + 1 WHERE did = $1`
256 _, err := r.db.ExecContext(ctx, query, communityDID)
257 if err != nil {
258 return fmt.Errorf("failed to increment subscriber count: %w", err)
259 }
260 return nil
261}
262
263func (r *postgresCommunityRepo) DecrementSubscriberCount(ctx context.Context, communityDID string) error {
264 query := `UPDATE communities SET subscriber_count = GREATEST(0, subscriber_count - 1) WHERE did = $1`
265 _, err := r.db.ExecContext(ctx, query, communityDID)
266 if err != nil {
267 return fmt.Errorf("failed to decrement subscriber count: %w", err)
268 }
269 return nil
270}
271
272func (r *postgresCommunityRepo) IncrementPostCount(ctx context.Context, communityDID string) error {
273 query := `UPDATE communities SET post_count = post_count + 1 WHERE did = $1`
274 _, err := r.db.ExecContext(ctx, query, communityDID)
275 if err != nil {
276 return fmt.Errorf("failed to increment post count: %w", err)
277 }
278 return nil
279}