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  }