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