github.com/DapperCollectives/CAST/backend@v0.0.0-20230921221157-1350c8be7c96/main/models/community.go (about) 1 package models 2 3 ///////////////// 4 // Communities // 5 ///////////////// 6 7 import ( 8 "fmt" 9 "time" 10 11 "github.com/DapperCollectives/CAST/backend/main/shared" 12 s "github.com/DapperCollectives/CAST/backend/main/shared" 13 "github.com/georgysavva/scany/pgxscan" 14 "github.com/jackc/pgx/v4" 15 "github.com/rs/zerolog/log" 16 ) 17 18 type Community struct { 19 ID int `json:"id,omitempty"` 20 Name string `json:"name,omitempty"` 21 Category *string `json:"category,omitempty" validate:"required"` 22 Category_count *int `json:"categoryCount,omitempty"` 23 Logo *string `json:"logo,omitempty"` 24 Body *string `json:"body,omitempty"` 25 Strategies *[]Strategy `json:"strategies,omitempty"` 26 Strategy *string `json:"strategy,omitempty"` 27 Banner_img_url *string `json:"bannerImgUrl,omitempty"` 28 Website_url *string `json:"websiteUrl,omitempty"` 29 Twitter_url *string `json:"twitterUrl,omitempty"` 30 Github_url *string `json:"githubUrl,omitempty"` 31 Discord_url *string `json:"discordUrl,omitempty"` 32 Instagram_url *string `json:"instagramUrl,omitempty"` 33 Terms_and_conditions_url *string `json:"termsAndConditionsUrl,omitempty"` 34 Only_authors_to_submit *bool `json:"onlyAuthorsToSubmit,omitempty"` 35 Proposal_validation *string `json:"proposalValidation,omitempty"` 36 Proposal_threshold *string `json:"proposalThreshold,omitempty"` 37 Slug *string `json:"slug,omitempty" validate:"required"` 38 Is_featured *bool `json:"isFeatured,omitempty"` 39 40 Total *int `json:"total,omitempty"` // for search only 41 42 Contract_name *string `json:"contractName,omitempty"` 43 Contract_addr *string `json:"contractAddr,omitempty"` 44 Contract_type *string `json:"contractType,omitempty"` 45 Public_path *string `json:"publicPath,omitempty"` 46 47 Timestamp string `json:"timestamp" validate:"required"` 48 Composite_signatures *[]s.CompositeSignature `json:"compositeSignatures"` 49 Creator_addr string `json:"creatorAddr" validate:"required"` 50 Signing_addr *string `json:"signingAddr,omitempty"` 51 Voucher *shared.Voucher `json:"voucher,omitempty"` 52 Created_at *time.Time `json:"createdAt,omitempty"` 53 Cid *string `json:"cid,omitempty"` 54 } 55 56 type CreateCommunityRequestPayload struct { 57 Community 58 59 Additional_authors *[]string `json:"additionalAuthors,omitempty"` 60 Additional_admins *[]string `json:"additionalAdmins,omitempty"` 61 } 62 63 type UpdateCommunityRequestPayload struct { 64 Name *string `json:"name,omitempty"` 65 Category *string `json:"category,omitempty"` 66 Body *string `json:"body,omitempty"` 67 Logo *string `json:"logo,omitempty"` 68 Strategies *[]Strategy `json:"strategies,omitempty"` 69 Strategy *string `json:"strategy,omitempty"` 70 Banner_img_url *string `json:"bannerImgUrl,omitempty"` 71 Website_url *string `json:"websiteUrl,omitempty"` 72 Twitter_url *string `json:"twitterUrl,omitempty"` 73 Github_url *string `json:"githubUrl,omitempty"` 74 Discord_url *string `json:"discordUrl,omitempty"` 75 Instagram_url *string `json:"instagramUrl,omitempty"` 76 Terms_and_conditions_url *string `json:"termsAndConditionsUrl,omitempty"` 77 Proposal_validation *string `json:"proposalValidation,omitempty"` 78 Proposal_threshold *string `json:"proposalThreshold,omitempty"` 79 Only_authors_to_submit *bool `json:"onlyAuthorsToSubmit,omitempty"` 80 Voucher *shared.Voucher `json:"voucher,omitempty"` 81 82 //TODO dup fields in Community struct, make sub struct for both to use 83 Contract_name *string `json:"contractName,omitempty"` 84 Contract_addr *string `json:"contractAddr,omitempty"` 85 Contract_type *string `json:"contractType,omitempty"` 86 Public_path *string `json:"publicPath,omitempty"` 87 Threshold *float64 `json:"threshold,omitempty"` 88 89 s.TimestampSignaturePayload 90 } 91 92 type Strategy struct { 93 Name *string `json:"name,omitempty"` 94 shared.Contract `json:"contract,omitempty"` 95 } 96 97 type CommunityType struct { 98 Key string `json:"key" validate:"required"` 99 Name string `json:"name" validate:"required"` 100 Description string `json:"description,omitempty"` 101 } 102 103 const HOMEPAGE_SQL = ` 104 SELECT * FROM communities WHERE (discord_url IS NOT NULL 105 AND twitter_url IS NOT NULL 106 AND id IN ( 107 SELECT community_id 108 FROM community_users 109 GROUP BY community_id 110 HAVING COUNT(*) > 500 111 ) 112 AND id IN ( 113 SELECT community_id FROM proposals 114 WHERE status = 'published' AND end_time < (NOW() AT TIME ZONE 'UTC') 115 GROUP BY community_id 116 HAVING COUNT(*) >= 2 117 )) 118 OR is_featured = 'true' 119 LIMIT $1 OFFSET $2 120 ` 121 const DEFAULT_SEARCH_SQL = ` 122 SELECT id, name, body, logo, category 123 FROM communities 124 WHERE is_featured = 'true' 125 AND category IS NOT NULL 126 ` 127 const INSERT_COMMUNITY_SQL = ` 128 INSERT INTO communities( 129 name, 130 category, 131 logo, 132 slug, 133 strategies, 134 strategy, 135 banner_img_url, 136 website_url, 137 twitter_url, 138 github_url, 139 discord_url, 140 instagram_url, 141 terms_and_conditions_url, 142 proposal_validation, 143 proposal_threshold, 144 body, 145 cid, 146 creator_addr, 147 contract_name, 148 contract_addr, 149 contract_type, 150 public_path, 151 only_authors_to_submit, 152 voucher) 153 VALUES( 154 $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, 155 $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24 156 ) 157 RETURNING id, created_at 158 ` 159 const UPDATE_COMMUNITY_SQL = ` 160 UPDATE communities 161 SET name = COALESCE($1, name), 162 body = COALESCE($2, body), 163 logo = COALESCE($3, logo), 164 strategies = COALESCE($4, strategies), 165 strategy = COALESCE($5, strategy), 166 banner_img_url = COALESCE($6, banner_img_url), 167 website_url = COALESCE($7, website_url), 168 twitter_url = COALESCE($8, twitter_url), 169 github_url = COALESCE($9, github_url), 170 discord_url = COALESCE($10, discord_url), 171 instagram_url = COALESCE($11, instagram_url), 172 proposal_validation = COALESCE($12, proposal_validation), 173 proposal_threshold = COALESCE($13, proposal_threshold), 174 category = COALESCE($14, category), 175 terms_and_conditions_url = COALESCE($15, terms_and_conditions_url), 176 contract_name = COALESCE($16, contract_name), 177 contract_addr = COALESCE($17, contract_addr), 178 contract_type = COALESCE($18, contract_type), 179 public_path = COALESCE($19, public_path), 180 only_authors_to_submit = COALESCE($20, only_authors_to_submit) 181 WHERE id = $21 182 ` 183 const SEARCH_COMMUNITIES_SQL = ` 184 SELECT id, name, body, logo, category, SIMILARITY(name, $1) as score 185 FROM communities 186 WHERE SIMILARITY(name, $1) > 0.1 187 AND category IS NOT NULL 188 ` 189 const COUNT_CATEGORIES_DEFAULT_SQL = ` 190 SELECT category, COUNT(*) as category_count 191 FROM communities 192 WHERE is_featured = 'true' 193 AND category IS NOT NULL 194 GROUP BY category 195 ` 196 const COUNT_CATEGORIES_SEARCH_SQL = ` 197 SELECT category, COUNT(*) as category_count 198 FROM communities 199 WHERE SIMILARITY(name, $1) > 0.1 200 AND category IS NOT NULL 201 GROUP BY category 202 ` 203 204 func GetCommunityTypes(db *s.Database) ([]*CommunityType, error) { 205 var communityTypes []*CommunityType 206 err := pgxscan.Select(db.Context, db.Conn, &communityTypes, 207 ` 208 SELECT * FROM community_types 209 `) 210 211 if err != nil { 212 return nil, err 213 } 214 return communityTypes, nil 215 } 216 217 func (c *Community) GetCommunity(db *s.Database) error { 218 return pgxscan.Get(db.Context, db.Conn, c, 219 `SELECT * from communities WHERE id = $1`, 220 c.ID) 221 } 222 223 func GetCommunities(db *s.Database, pageParams shared.PageParams) ([]*Community, int, error) { 224 var communities []*Community 225 err := pgxscan.Select(db.Context, db.Conn, &communities, 226 ` 227 SELECT * FROM communities 228 LIMIT $1 OFFSET $2 229 `, pageParams.Count, pageParams.Start) 230 231 // If we get pgx.ErrNoRows, just return an empty array 232 // and obfuscate error 233 if err != nil && err.Error() != pgx.ErrNoRows.Error() { 234 return nil, 0, err 235 } else if err != nil && err.Error() == pgx.ErrNoRows.Error() { 236 return []*Community{}, 0, nil 237 } 238 239 // Get total number of communities 240 var totalRecords int 241 countSql := `SELECT COUNT(*) FROM communities` 242 _ = db.Conn.QueryRow(db.Context, countSql).Scan(&totalRecords) 243 244 return communities, totalRecords, nil 245 } 246 247 func (c *Community) GetCommunityByProposalId(db *s.Database, proposalId int) error { 248 return pgxscan.Get(db.Context, db.Conn, c, 249 `SELECT * from communities WHERE id = (SELECT community_id FROM proposals WHERE id = $1)`, 250 proposalId) 251 } 252 253 func GetDefaultCommunities( 254 db *s.Database, 255 params shared.PageParams, 256 filters []string, 257 isSearch bool, 258 ) ([]*Community, int, error) { 259 var sql string 260 261 if !isSearch { 262 var totalRecords int 263 countSql := `SELECT COUNT(*) FROM communities ` 264 265 sql = HOMEPAGE_SQL 266 var communities []*Community 267 268 err := pgxscan.Select( 269 db.Context, 270 db.Conn, 271 &communities, 272 sql, 273 params.Count, 274 params.Start, 275 ) 276 277 // If we get pgx.ErrNoRows, just return an empty array 278 // and obfuscate error 279 if err != nil && err.Error() != pgx.ErrNoRows.Error() { 280 return nil, 0, err 281 } else if err != nil && err.Error() == pgx.ErrNoRows.Error() { 282 return []*Community{}, 0, nil 283 } 284 285 db.Conn.QueryRow(db.Context, countSql).Scan(&totalRecords) 286 return communities, totalRecords, nil 287 } else { 288 sql, err := addFiltersToSql(DEFAULT_SEARCH_SQL, "", filters) 289 if err != nil { 290 return nil, 0, err 291 } 292 293 sql = sql + " LIMIT $1 OFFSET $2" 294 295 rows, err := db.Conn.Query( 296 db.Context, 297 sql, 298 params.Count, 299 params.Start, 300 ) 301 if err != nil { 302 return nil, 0, err 303 } 304 305 defer rows.Close() 306 307 communities, err := scanSearchResults(rows, true) 308 if err != nil { 309 return nil, 0, err 310 } 311 312 //@TODO: Repeating logic here, refactor if checks into a function 313 //if filters are present generate a new sql query to get the total records 314 if filters[0] != "" { 315 countSql, err := generateDefaultFilterCountSql(filters) 316 if err != nil { 317 return nil, 0, err 318 } 319 320 fmt.Printf("count sql: %s \n", countSql) 321 var totalRecords int 322 db.Conn.QueryRow(db.Context, countSql).Scan(&totalRecords) 323 324 return communities, totalRecords, nil 325 } else { 326 countSql := `SELECT COUNT(*) FROM communities 327 WHERE is_featured = 'true' AND category IS NOT NULL` 328 329 var totalRecords int 330 db.Conn.QueryRow(db.Context, countSql).Scan(&totalRecords) 331 332 return communities, totalRecords, nil 333 } 334 } 335 } 336 337 func (c *Community) CreateCommunity(db *s.Database) error { 338 339 err := db.Conn.QueryRow(db.Context, 340 INSERT_COMMUNITY_SQL, 341 c.Name, 342 c.Category, 343 c.Logo, 344 c.Slug, 345 c.Strategies, 346 c.Strategy, 347 c.Banner_img_url, 348 c.Website_url, 349 c.Twitter_url, 350 c.Github_url, 351 c.Discord_url, 352 c.Instagram_url, 353 c.Terms_and_conditions_url, 354 c.Proposal_validation, 355 c.Proposal_threshold, 356 c.Body, 357 c.Cid, 358 c.Creator_addr, 359 c.Contract_name, 360 c.Contract_addr, 361 c.Contract_type, 362 c.Public_path, 363 c.Only_authors_to_submit, 364 c.Voucher). 365 Scan(&c.ID, &c.Created_at) 366 return err 367 } 368 369 func (c *Community) UpdateCommunity(db *s.Database, p *UpdateCommunityRequestPayload) error { 370 _, err := db.Conn.Exec( 371 db.Context, 372 UPDATE_COMMUNITY_SQL, 373 p.Name, 374 p.Body, 375 p.Logo, 376 p.Strategies, 377 p.Strategy, 378 p.Banner_img_url, 379 p.Website_url, 380 p.Twitter_url, 381 p.Github_url, 382 p.Discord_url, 383 p.Instagram_url, 384 p.Proposal_validation, 385 p.Proposal_threshold, 386 p.Category, 387 p.Terms_and_conditions_url, 388 p.Contract_name, 389 p.Contract_addr, 390 p.Contract_type, 391 p.Public_path, 392 p.Only_authors_to_submit, 393 c.ID, 394 ) 395 396 return err 397 } 398 399 func (c *Community) CanUpdateCommunity(db *s.Database, addr string) error { 400 // Check if address has admin role 401 admin := CommunityUser{Addr: addr, Community_id: c.ID, User_type: "admin"} 402 if err := admin.GetCommunityUser(db); err != nil { 403 return fmt.Errorf("address %s does not have permission to update community with ID %d", addr, c.ID) 404 } 405 return nil 406 } 407 408 func (c *Community) GetStrategy(name string) (Strategy, error) { 409 for _, s := range *c.Strategies { 410 if *s.Name == name { 411 return s, nil 412 } 413 } 414 return Strategy{}, fmt.Errorf("Strategy %s does not exist on community", name) 415 } 416 417 func SearchForCommunity( 418 db *s.Database, 419 query string, 420 filters []string, 421 params shared.PageParams, 422 ) ([]*Community, int, error) { 423 424 sql, err := addFiltersToSql(SEARCH_COMMUNITIES_SQL, query, filters) 425 if err != nil { 426 return nil, 0, err 427 } 428 429 sql = sql + " ORDER BY score DESC LIMIT $2 OFFSET $3" 430 431 rows, err := db.Conn.Query( 432 db.Context, 433 sql, 434 query, 435 params.Count, 436 params.Start, 437 ) 438 439 if err != nil { 440 return []*Community{}, 0, fmt.Errorf("error searching for a community with the the query %s", query) 441 } 442 443 defer rows.Close() 444 445 communities, err := scanSearchResults(rows, false) 446 if err != nil { 447 return []*Community{}, 0, fmt.Errorf("error scanning search results for the query %s", query) 448 } 449 450 //@TODO: Repeating logic here, refactor if checks into a function 451 //if filters are present generate a new sql query to get the total records 452 if filters[0] != "" { 453 countSql, err := generateSearchFilterCountSql(filters) 454 if err != nil { 455 return nil, 0, err 456 } 457 var totalRecords int 458 db.Conn.QueryRow(db.Context, countSql, query).Scan(&totalRecords) 459 460 return communities, totalRecords, nil 461 } else { 462 countSql := `SELECT COUNT(*) FROM communities 463 WHERE SIMILARITY(name, $1) > 0.1` 464 var totalRecords int 465 db.Conn.QueryRow(db.Context, countSql, query).Scan(&totalRecords) 466 467 return communities, totalRecords, nil 468 } 469 } 470 471 func scanSearchResults(rows pgx.Rows, isDefault bool) ([]*Community, error) { 472 var communities []*Community 473 474 var err error 475 for rows.Next() { 476 var c Community 477 if isDefault { 478 err = rows.Scan(&c.ID, &c.Name, &c.Body, &c.Logo, &c.Category) 479 } else { 480 // score is required for scanning, but can be ignored. Only used 481 // to order the search results by SQL. 482 var score float32 483 err = rows.Scan(&c.ID, &c.Name, &c.Body, &c.Logo, &c.Category, &score) 484 } 485 if err != nil { 486 log.Error().Err(err) 487 return communities, fmt.Errorf("error scanning community row: %v", err) 488 } 489 communities = append(communities, &c) 490 } 491 return communities, nil 492 } 493 494 func MatchStrategyByProposal(s []Strategy, strategyToMatch string) (Strategy, error) { 495 var match Strategy 496 for _, strategy := range s { 497 if *strategy.Name == strategyToMatch { 498 match = strategy 499 return match, nil 500 } 501 } 502 return match, fmt.Errorf("Community does not have strategy available") 503 } 504 505 /// Generate SQL Functions//// 506 func generateSearchFilterCountSql(filters []string) (string, error) { 507 if len(filters) > 0 { 508 var sql string = ` 509 SELECT COUNT(*) FROM communities 510 WHERE SIMILARITY(name, $1) > 0.1 511 AND category IS NOT NULL 512 AND category IN (` 513 for i, filter := range filters { 514 if i == len(filters)-1 { 515 sql += fmt.Sprintf("'%s')", filter) 516 } else { 517 sql += fmt.Sprintf("'%s',", filter) 518 } 519 } 520 521 return sql, nil 522 } else { 523 return "", fmt.Errorf("No filters provided") 524 } 525 } 526 527 func generateDefaultFilterCountSql(filters []string) (string, error) { 528 if len(filters) > 0 { 529 var sql string = ` 530 SELECT COUNT(*) FROM communities 531 WHERE category IS NOT NULL 532 AND is_featured = true 533 AND category IN (` 534 for i, filter := range filters { 535 if i == len(filters)-1 { 536 sql += fmt.Sprintf("'%s')", filter) 537 } else { 538 sql += fmt.Sprintf("'%s',", filter) 539 } 540 } 541 542 return sql, nil 543 } else { 544 return "", fmt.Errorf("No filters provided") 545 } 546 } 547 func GetCategoryCount(db *s.Database, search string) (map[string]int, error) { 548 var rows pgx.Rows 549 var err error 550 551 if search == "" { 552 rows, err = db.Conn.Query( 553 db.Context, 554 COUNT_CATEGORIES_DEFAULT_SQL, 555 ) 556 } else { 557 rows, err = db.Conn.Query( 558 db.Context, 559 COUNT_CATEGORIES_SEARCH_SQL, 560 search, 561 ) 562 } 563 564 // If we get pgx.ErrNoRows, just return an empty array 565 // and obfuscate error 566 if err != nil && err.Error() != pgx.ErrNoRows.Error() { 567 return nil, err 568 } else if err != nil && err.Error() == pgx.ErrNoRows.Error() { 569 return make(map[string]int), nil 570 } 571 572 defer rows.Close() 573 574 categoryCount := make(map[string]int) 575 for rows.Next() { 576 results := struct { 577 Category string 578 Count int 579 }{} 580 err := rows.Scan(&results.Category, &results.Count) 581 if err != nil { 582 return make(map[string]int), fmt.Errorf("error scanning community row: %v", err) 583 } 584 categoryCount[results.Category] = results.Count 585 } 586 587 return categoryCount, nil 588 } 589 590 func addFiltersToSql(query, search string, filters []string) (string, error) { 591 var sql string 592 if filters[0] != "" { 593 sql = query + " AND (" 594 for i, filter := range filters { 595 if i == 0 { 596 sql += fmt.Sprintf("category = '%s'", filter) 597 } else { 598 sql += fmt.Sprintf(" OR category = '%s'", filter) 599 } 600 } 601 sql += ")" 602 } else { 603 sql = query 604 } 605 606 return sql, nil 607 }