github.com/DapperCollectives/CAST/backend@v0.0.0-20230921221157-1350c8be7c96/main/models/community_users.go (about) 1 package models 2 3 import ( 4 "sort" 5 "strings" 6 7 "github.com/DapperCollectives/CAST/backend/main/shared" 8 s "github.com/DapperCollectives/CAST/backend/main/shared" 9 "github.com/georgysavva/scany/pgxscan" 10 "github.com/jackc/pgx/v4" 11 "github.com/rs/zerolog/log" 12 ) 13 14 type CommunityUser struct { 15 Community_id int `json:"communityId" validate:"required"` 16 Addr string `json:"addr" validate:"required"` 17 User_type string `json:"userType" validate:"required"` 18 } 19 20 type CommunityUserType struct { 21 Community_id int `json:"communityId" validate:"required"` 22 Addr string `json:"addr" validate:"required"` 23 Is_admin bool `json:"isAdmin" validate:"required"` 24 Is_author bool `json:"isAuthor" validate:"required"` 25 Is_member bool `json:"isMember" validate:"required"` 26 } 27 28 type UserTypes []string 29 30 var USER_TYPES = UserTypes{"member", "author", "admin"} 31 32 type UserCommunity struct { 33 Community 34 Roles string `json:"roles" validate:"required"` 35 } 36 37 type CommunityUserPayload struct { 38 CommunityUser 39 Signing_addr string `json:"signingAddr"` 40 Timestamp string `json:"timestamp"` 41 Composite_signatures *[]s.CompositeSignature `json:"compositeSignatures"` 42 Voucher *s.Voucher `json:"voucher"` 43 } 44 45 type UserAchievements = []struct { 46 Addr string 47 NumVotes int 48 EarlyVotes int 49 Streaks int 50 WinningVotes int 51 } 52 53 type LeaderboardUser struct { 54 Addr string `json:"addr" validate:"required"` 55 Score int `json:"score,omitempty"` 56 Index int `json:"index,omitempty"` 57 } 58 59 type LeaderboardPayload struct { 60 Users []LeaderboardUser `json:"users"` 61 CurrentUser LeaderboardUser `json:"currentUser"` 62 } 63 64 func GetUsersForCommunity(db *s.Database, communityId int, pageParams shared.PageParams) ([]CommunityUserType, int, error) { 65 var users = []CommunityUserType{} 66 err := pgxscan.Select(db.Context, db.Conn, &users, 67 ` 68 SELECT 69 (CASE WHEN 70 (EXISTS (SELECT community_users.addr FROM community_users WHERE community_users.addr = temp_user_addrs.addr AND community_users.user_type = 'admin')) 71 THEN '1' else '0' end)::boolean AS is_admin, 72 (CASE WHEN 73 (EXISTS (SELECT community_users.addr FROM community_users WHERE community_users.addr = temp_user_addrs.addr AND community_users.user_type = 'author')) 74 THEN '1' else '0' end)::boolean AS is_author, 75 (CASE WHEN 76 (EXISTS (SELECT community_users.addr FROM community_users WHERE community_users.addr = temp_user_addrs.addr AND community_users.user_type = 'member')) 77 THEN '1' else '0' end)::boolean AS is_member, 78 temp_user_addrs.addr AS addr, 79 $1 as community_id 80 FROM 81 (SELECT addr FROM community_users WHERE community_id = $1 group BY community_users.addr) 82 AS temp_user_addrs 83 LIMIT $2 OFFSET $3 84 `, communityId, pageParams.Count, pageParams.Start) 85 86 if err != nil && err.Error() != pgx.ErrNoRows.Error() { 87 return nil, 0, err 88 } else if err != nil && err.Error() == pgx.ErrNoRows.Error() { 89 return []CommunityUserType{}, 0, nil 90 } 91 92 var totalUsers int 93 countSql := `SELECT COUNT(*) FROM (SELECT addr FROM community_users WHERE community_id = $1 group BY community_users.addr) as temp_users_addr` 94 _ = db.Conn.QueryRow(db.Context, countSql, communityId).Scan(&totalUsers) 95 96 return users, totalUsers, nil 97 } 98 99 func GetUsersForCommunityByType( 100 db *s.Database, 101 communityId int, 102 user_type string, 103 pageParams shared.PageParams, 104 ) ([]CommunityUser, int, error) { 105 var users = []CommunityUser{} 106 err := pgxscan.Select(db.Context, db.Conn, &users, 107 ` 108 SELECT * FROM community_users WHERE community_id = $1 AND user_type = $2 109 LIMIT $3 OFFSET $4 110 `, communityId, user_type, pageParams.Count, pageParams.Start) 111 112 if err != nil && err.Error() != pgx.ErrNoRows.Error() { 113 return nil, 0, err 114 } else if err != nil && err.Error() == pgx.ErrNoRows.Error() { 115 return []CommunityUser{}, 0, nil 116 } 117 118 var totalUsers int 119 countSql := `SELECT COUNT(*) FROM community_users WHERE community_id = $1 AND user_type = $2` 120 _ = db.Conn.QueryRow(db.Context, countSql, communityId, user_type).Scan(&totalUsers) 121 122 return users, totalUsers, nil 123 } 124 125 func GetCommunityLeaderboard( 126 db *s.Database, 127 communityId int, 128 addr string, 129 pageParams shared.PageParams, 130 ) (LeaderboardPayload, int, error) { 131 var payload = LeaderboardPayload{} 132 133 userAchievements, err := getUserAchievements(db, communityId) 134 135 if err != nil { 136 log.Error().Err(err).Msg("Error Getting User Achievements.") 137 return payload, 0, err 138 } 139 140 if len(userAchievements) == 0 { 141 return payload, 0, nil 142 } 143 144 leaderboardUsers, currentUser := getLeaderboardUsers( 145 userAchievements, 146 addr, 147 pageParams.Start, 148 pageParams.Count, 149 ) 150 151 totalUsers := len(leaderboardUsers) 152 if totalUsers > pageParams.Count { 153 totalUsers = pageParams.Count 154 } 155 156 payload.Users = leaderboardUsers 157 payload.CurrentUser = currentUser 158 159 return payload, totalUsers, nil 160 } 161 162 func GetCommunitiesForUser(db *s.Database, addr string, pageParams shared.PageParams) ([]UserCommunity, int, error) { 163 var communities = []UserCommunity{} 164 165 err := pgxscan.Select(db.Context, db.Conn, &communities, 166 ` 167 SELECT 168 communities.*, 169 community_users.user_type as roles 170 FROM communities 171 JOIN community_users ON community_users.community_id = communities.id 172 WHERE community_users.addr = $1 173 `, addr) 174 175 if err != nil && err.Error() != pgx.ErrNoRows.Error() { 176 return nil, 0, err 177 } else if err != nil && err.Error() == pgx.ErrNoRows.Error() { 178 return []UserCommunity{}, 0, nil 179 } 180 181 mergedCommunities, totalCommunities := mergeUserRolesForCommunities(communities, pageParams.Start, pageParams.Count) 182 183 return mergedCommunities, totalCommunities, nil 184 } 185 186 func (u *CommunityUser) GetCommunityUser(db *s.Database) error { 187 sql := ` 188 SELECT * from community_users as u 189 WHERE u.community_id = $1 AND u.addr = $2 AND u.user_type = $3 190 ` 191 return pgxscan.Get(db.Context, db.Conn, u, sql, u.Community_id, u.Addr, u.User_type) 192 } 193 194 func GetAllRolesForUserInCommunity(db *s.Database, addr string, communityId int) ([]CommunityUser, error) { 195 var users = []CommunityUser{} 196 err := pgxscan.Select(db.Context, db.Conn, &users, 197 ` 198 SELECT * FROM community_users WHERE community_id = $1 AND addr = $2 199 `, communityId, addr) 200 201 if err != nil && err.Error() != pgx.ErrNoRows.Error() { 202 return nil, err 203 } else if err != nil && err.Error() == pgx.ErrNoRows.Error() { 204 return []CommunityUser{}, nil 205 } 206 return users, err 207 } 208 209 func (u *CommunityUser) Remove(db *s.Database) error { 210 _, err := db.Conn.Exec(db.Context, 211 ` 212 DELETE FROM community_users 213 WHERE community_id = $1 AND addr = $2 AND user_type = $3 214 `, u.Community_id, u.Addr, u.User_type) 215 216 return err 217 } 218 219 func GrantAdminRolesToAddress(db *s.Database, communityId int, addr string) error { 220 userTypes := UserTypes{"admin", "author", "member"} 221 for _, role := range userTypes { 222 userRole := CommunityUser{Addr: addr, Community_id: communityId, User_type: role} 223 if err := userRole.GetCommunityUser(db); err != nil { 224 if err := userRole.CreateCommunityUser(db); err != nil { 225 log.Error().Err(err).Msgf("Database error creating role %s for Address: %s and Communuity Id: %d.", role, addr, communityId) 226 return err 227 } 228 } 229 } 230 return nil 231 } 232 233 func GrantAuthorRolesToAddress(db *s.Database, communityId int, addr string) error { 234 userTypes := UserTypes{"author", "member"} 235 for _, role := range userTypes { 236 userRole := CommunityUser{Addr: addr, Community_id: communityId, User_type: role} 237 if err := userRole.GetCommunityUser(db); err != nil { 238 if err := userRole.CreateCommunityUser(db); err != nil { 239 log.Error().Err(err).Msgf("Database error creating role %s for Address: %s and Community Id: %d.", role, addr, communityId) 240 return err 241 } 242 } 243 } 244 return nil 245 } 246 247 func (u *CommunityUser) CreateCommunityUser(db *s.Database) error { 248 err := db.Conn.QueryRow(db.Context, 249 ` 250 INSERT INTO community_users(community_id, addr, user_type) 251 VALUES($1, $2, $3) 252 RETURNING community_id, addr, user_type 253 `, u.Community_id, u.Addr, u.User_type).Scan(&u.Community_id, &u.Addr, &u.User_type) 254 255 return err 256 } 257 258 func GrantRolesToCommunityCreator(db *s.Database, addr string, communityId int) error { 259 for _, userType := range USER_TYPES { 260 communityUser := CommunityUser{Addr: addr, Community_id: communityId, User_type: userType} 261 if err := communityUser.CreateCommunityUser(db); err != nil { 262 return err 263 } 264 log.Debug().Msgf("granted addr %s role %s for community %d", addr, userType, communityId) 265 } 266 return nil 267 } 268 269 func EnsureRoleForCommunity(db *s.Database, addr string, communityId int, userType string) error { 270 user := CommunityUser{Addr: addr, Community_id: communityId, User_type: userType} 271 return user.GetCommunityUser(db) 272 } 273 274 func EnsureValidRole(userType string) bool { 275 for _, t := range USER_TYPES { 276 if t == userType { 277 return true 278 } 279 } 280 return false 281 } 282 283 func getUserAchievements(db *s.Database, communityId int) (UserAchievements, error) { 284 userAchievements := UserAchievements{} 285 286 err := pgxscan.Select(db.Context, db.Conn, &userAchievements, ` 287 SELECT 288 v.addr, 289 COUNT(v.id) AS num_votes, 290 COUNT(v.id) FILTER (WHERE is_early = 'true') AS early_votes, 291 COUNT(v.id) FILTER (WHERE is_winning = 'true') AS winning_votes 292 FROM votes v 293 LEFT JOIN proposals p ON p.id = v.proposal_id 294 WHERE p.community_id = $1 AND v.is_cancelled != 'true' 295 GROUP BY v.addr 296 `, communityId) 297 298 if err != nil && err.Error() != pgx.ErrNoRows.Error() { 299 log.Error().Err(err).Msg("Error Getting User Achievements.") 300 return nil, err 301 } else if err != nil && err.Error() == pgx.ErrNoRows.Error() { 302 return userAchievements, nil 303 } 304 305 // Determine if user has any streaks 306 for i, ua := range userAchievements { 307 streaks, err := getStreakAchievement(db, ua.Addr, communityId) 308 if err != nil { 309 return userAchievements, err 310 } 311 userAchievements[i].Streaks = streaks 312 } 313 314 return userAchievements, nil 315 } 316 317 func getLeaderboardUsers(userAchievements UserAchievements, currentUserAddr string, start, count int) ([]LeaderboardUser, LeaderboardUser) { 318 var leaderboardUsers = []LeaderboardUser{} 319 var currentUser = LeaderboardUser{} 320 var defaultEarlyVoteWeight = 1 321 var defaultStreakWeight = 1 322 var defaultWinningVoteWeight = 1 323 324 for _, user := range userAchievements { 325 score := user.NumVotes + (user.EarlyVotes * defaultEarlyVoteWeight) + (user.Streaks * defaultStreakWeight) + (user.WinningVotes * defaultWinningVoteWeight) 326 327 var leaderboardUser = LeaderboardUser{} 328 leaderboardUser.Addr = user.Addr 329 leaderboardUser.Score = score 330 leaderboardUsers = append(leaderboardUsers, leaderboardUser) 331 if user.Addr == currentUserAddr { 332 currentUser = LeaderboardUser{} 333 currentUser.Addr = user.Addr 334 currentUser.Score = score 335 } 336 } 337 338 // Order by score descending 339 sort.Slice(leaderboardUsers, func(i, j int) bool { 340 return leaderboardUsers[i].Score > leaderboardUsers[j].Score 341 }) 342 343 // Include indexes for ranking 344 for i := range leaderboardUsers { 345 leaderboardUsers[i].Index = i + 1 346 if leaderboardUsers[i].Addr == currentUser.Addr { 347 currentUser.Index = i + 1 348 } 349 } 350 351 // Top users on leaderboard (e.g 10) 352 if start == 0 && len(leaderboardUsers) >= count { 353 leaderboardUsers = leaderboardUsers[0:count] 354 } else { 355 startIndex := start * count 356 endIndex := start*count + count 357 358 // If index invalid, set to last page 359 if startIndex >= len(leaderboardUsers) { 360 if len(leaderboardUsers)-count >= 0 { 361 startIndex = len(leaderboardUsers) - count 362 } else { 363 startIndex = 0 364 } 365 } 366 367 if endIndex <= len(leaderboardUsers) { 368 leaderboardUsers = leaderboardUsers[startIndex:endIndex] 369 } else { 370 leaderboardUsers = leaderboardUsers[startIndex:] 371 } 372 } 373 374 return leaderboardUsers, currentUser 375 } 376 377 func mergeUserRolesForCommunities(communities []UserCommunity, start, count int) ([]UserCommunity, int) { 378 var mergedCommunities = []UserCommunity{} 379 communitiesMap := make(map[int]int) 380 for i := range communities { 381 if index, ok := communitiesMap[communities[i].ID]; ok { 382 mergedCommunities[index].Roles = strings.Join([]string{mergedCommunities[index].Roles, communities[i].Roles}, ",") 383 } else { 384 mergedCommunities = append(mergedCommunities, communities[i]) 385 communitiesMap[communities[i].ID] = len(mergedCommunities) - 1 386 } 387 } 388 389 if start == 0 && len(mergedCommunities) >= count { 390 mergedCommunities = mergedCommunities[0:count] 391 } else { 392 startIndex := start * count 393 endIndex := start*count + count 394 395 // If index invalid, set to last page 396 if startIndex >= len(mergedCommunities) { 397 if len(mergedCommunities)-count >= 0 { 398 startIndex = len(mergedCommunities) - count 399 } else { 400 startIndex = 0 401 } 402 } 403 404 if endIndex <= len(mergedCommunities) { 405 mergedCommunities = mergedCommunities[startIndex:endIndex] 406 } else { 407 mergedCommunities = mergedCommunities[startIndex:] 408 } 409 } 410 411 totalCommunities := len(mergedCommunities) 412 413 return mergedCommunities, totalCommunities 414 }