A community based topic aggregation platform built on atproto
bretton.dev
1package postgres
2
3import (
4 "context"
5 "database/sql"
6 "fmt"
7 "strings"
8
9 "Coves/internal/core/communities"
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
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 rows.Close()
124
125 result := []*communities.Membership{}
126 for rows.Next() {
127 membership := &communities.Membership{}
128
129 err := rows.Scan(
130 &membership.ID,
131 &membership.UserDID,
132 &membership.CommunityDID,
133 &membership.ReputationScore,
134 &membership.ContributionCount,
135 &membership.JoinedAt,
136 &membership.LastActiveAt,
137 &membership.IsBanned,
138 &membership.IsModerator,
139 )
140 if err != nil {
141 return nil, fmt.Errorf("failed to scan member: %w", err)
142 }
143
144 result = append(result, membership)
145 }
146
147 if err = rows.Err(); err != nil {
148 return nil, fmt.Errorf("error iterating members: %w", err)
149 }
150
151 return result, nil
152}
153
154// CreateModerationAction records a moderation action
155func (r *postgresCommunityRepo) CreateModerationAction(ctx context.Context, action *communities.ModerationAction) (*communities.ModerationAction, error) {
156 query := `
157 INSERT INTO community_moderation (
158 community_did, action, reason, instance_did, broadcast, created_at, expires_at
159 )
160 VALUES ($1, $2, $3, $4, $5, $6, $7)
161 RETURNING id, created_at`
162
163 err := r.db.QueryRowContext(ctx, query,
164 action.CommunityDID,
165 action.Action,
166 nullString(action.Reason),
167 action.InstanceDID,
168 action.Broadcast,
169 action.CreatedAt,
170 action.ExpiresAt,
171 ).Scan(&action.ID, &action.CreatedAt)
172
173 if err != nil {
174 if strings.Contains(err.Error(), "foreign key") {
175 return nil, communities.ErrCommunityNotFound
176 }
177 return nil, fmt.Errorf("failed to create moderation action: %w", err)
178 }
179
180 return action, nil
181}
182
183// ListModerationActions retrieves moderation actions for a community
184func (r *postgresCommunityRepo) ListModerationActions(ctx context.Context, communityDID string, limit, offset int) ([]*communities.ModerationAction, error) {
185 query := `
186 SELECT id, community_did, action, reason, instance_did, broadcast, created_at, expires_at
187 FROM community_moderation
188 WHERE community_did = $1
189 ORDER BY created_at DESC
190 LIMIT $2 OFFSET $3`
191
192 rows, err := r.db.QueryContext(ctx, query, communityDID, limit, offset)
193 if err != nil {
194 return nil, fmt.Errorf("failed to list moderation actions: %w", err)
195 }
196 defer rows.Close()
197
198 result := []*communities.ModerationAction{}
199 for rows.Next() {
200 action := &communities.ModerationAction{}
201 var reason sql.NullString
202
203 err := rows.Scan(
204 &action.ID,
205 &action.CommunityDID,
206 &action.Action,
207 &reason,
208 &action.InstanceDID,
209 &action.Broadcast,
210 &action.CreatedAt,
211 &action.ExpiresAt,
212 )
213 if err != nil {
214 return nil, fmt.Errorf("failed to scan moderation action: %w", err)
215 }
216
217 action.Reason = reason.String
218 result = append(result, action)
219 }
220
221 if err = rows.Err(); err != nil {
222 return nil, fmt.Errorf("error iterating moderation actions: %w", err)
223 }
224
225 return result, nil
226}
227
228// Statistics methods
229func (r *postgresCommunityRepo) IncrementMemberCount(ctx context.Context, communityDID string) error {
230 query := `UPDATE communities SET member_count = member_count + 1 WHERE did = $1`
231 _, err := r.db.ExecContext(ctx, query, communityDID)
232 if err != nil {
233 return fmt.Errorf("failed to increment member count: %w", err)
234 }
235 return nil
236}
237
238func (r *postgresCommunityRepo) DecrementMemberCount(ctx context.Context, communityDID string) error {
239 query := `UPDATE communities SET member_count = GREATEST(0, member_count - 1) WHERE did = $1`
240 _, err := r.db.ExecContext(ctx, query, communityDID)
241 if err != nil {
242 return fmt.Errorf("failed to decrement member count: %w", err)
243 }
244 return nil
245}
246
247func (r *postgresCommunityRepo) IncrementSubscriberCount(ctx context.Context, communityDID string) error {
248 query := `UPDATE communities SET subscriber_count = subscriber_count + 1 WHERE did = $1`
249 _, err := r.db.ExecContext(ctx, query, communityDID)
250 if err != nil {
251 return fmt.Errorf("failed to increment subscriber count: %w", err)
252 }
253 return nil
254}
255
256func (r *postgresCommunityRepo) DecrementSubscriberCount(ctx context.Context, communityDID string) error {
257 query := `UPDATE communities SET subscriber_count = GREATEST(0, subscriber_count - 1) WHERE did = $1`
258 _, err := r.db.ExecContext(ctx, query, communityDID)
259 if err != nil {
260 return fmt.Errorf("failed to decrement subscriber count: %w", err)
261 }
262 return nil
263}
264
265func (r *postgresCommunityRepo) IncrementPostCount(ctx context.Context, communityDID string) error {
266 query := `UPDATE communities SET post_count = post_count + 1 WHERE did = $1`
267 _, err := r.db.ExecContext(ctx, query, communityDID)
268 if err != nil {
269 return fmt.Errorf("failed to increment post count: %w", err)
270 }
271 return nil
272}