metarare_api-main/view/common.go

package view

import (
	"errors"
	"fmt"
	"strings"
	"time"

	"github.com/metarare/metarare_api/common"
	"github.com/metarare/metarare_api/models"
	"gorm.io/gorm"
)

// filter
func userIDCheck(id uint64) func(db *gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		return db.Where("user_id = ?", id)
	}
}
func filterByOwner(ownerAddress string) func(db *gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		return db.Where("owner_address = ?", ownerAddress)
	}
}

func filterByNetwork(network string) func(db *gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		if network == "" {
			fmt.Printf("invalid network(filterByNetwork!!! (%s)\n", network)
			return db
		} else {
			return db.Where("network = ?", network)
		}
	}
}
func filterByStatus(status string) func(db *gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		if status == "" {
			fmt.Printf("invalid status (filterByStatus)!!! (%s)\n", status)
			return db
		} else {
			return db.Where("status = ?", status)
		}
	}
}
func filterByTokenIDs(tokenIDs []uint64) func(db *gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		if len(tokenIDs) == 0 {
			fmt.Printf("invalid status (filterByTokenIDs)!!! len(: zero)\n")
			return db
		} else {
			return db.Where("token_id in (?)", tokenIDs)
		}
	}
}
func filterByUserID(user_id uint64) func(db *gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		if user_id == 0 {
			fmt.Printf("invalid status (filterByIDs)!!! len(: zero)\n")
			return db
		} else {
			return db.Where("user_id = (?)", user_id)
		}
	}
}
func filterByID(tokenIDs []uint64) func(db *gorm.DB) *gorm.DB {
	return func(db *gorm.DB) *gorm.DB {
		if len(tokenIDs) == 0 {
			fmt.Printf("invalid status (filterByIDs)!!! len(: zero)\n")
			return db
		} else {
			return db.Where("id in (?)", tokenIDs)
		}
	}
}
func appendCondition(origin string, data string) string {
	_str := origin
	if len(origin) != 0 {
		_str += " AND "
	}
	_str += data
	return _str
}

func PreloadCollectionWithFilter(_preloadTable string, _db *gorm.DB, _filter common.Filter) *gorm.DB {
	_whereCondition := ""
	var _whereParams []string

	if strings.Compare(_filter.Network, "") != 0 {
		_whereCondition = appendCondition(_whereCondition, "network = (?)")
		_whereParams = append(_whereParams, _filter.SaleType)
	}

	if strings.Compare(_filter.CollectionName, "") != 0 {
		_whereCondition = appendCondition(_whereCondition, "name = (?)")
		_whereParams = append(_whereParams, _filter.CollectionName)
	}

	if len(_whereParams) != 0 {
		return _db.Preload(_preloadTable, _whereCondition, _whereParams)
	} else {
		return _db.Preload(_preloadTable)
	}
}
func PreloadSaleWithFilter(_db *gorm.DB, _filter common.Filter) (string, string, []string) {

	_whereCondition := ""
	var _whereParams []string
	if strings.Compare(_filter.SaleType, "") != 0 {
		_whereCondition = appendCondition(_whereCondition, "sale_type in (?)")
		_whereParams = append(_whereParams, _filter.SaleType)
	}

	if strings.Compare(_filter.Currency, "") != 0 &&
		strings.Compare(_filter.CurrencyAmountStart, "") != 0 &&
		strings.Compare(_filter.CurrencyAmountEnd, "") != 0 {

		_whereCondition = appendCondition(_whereCondition, "currency = (?)")
		_whereParams = append(_whereParams, _filter.Currency)

		_whereCondition = appendCondition(_whereCondition, "price >= (?)")
		_whereParams = append(_whereParams, _filter.CurrencyAmountStart)

		_whereCondition = appendCondition(_whereCondition, "price < (?)")
		_whereParams = append(_whereParams, _filter.CurrencyAmountEnd)
	}

	if strings.Compare(_filter.SaleStatus, "") != 0 {
		_whereCondition = appendCondition(_whereCondition, "status = (?)")
		_whereParams = append(_whereParams, _filter.SaleStatus)

	}
	if len(_whereParams) != 0 {
		return "Sale", _whereCondition, _whereParams
	} else {
		return "Sale", "", nil
	}
}

// data extractor
func getAddressByUserID(db *gorm.DB, id uint64) (error, string) {

	var _user models.User

	var ids []uint64
	ids = append(ids, id)
	err := db.Model(&_user).Preload("UserWallet").Scopes(filterByID(ids)).Find(&_user).Error

	if errors.Is(err, gorm.ErrRecordNotFound) {
		fmt.Println(err.Error())
		return err, ""
	}
	return nil, _user.UserWallet.Address

}

func Filtering(db *gorm.DB, filter common.Filter) *gorm.DB {
	db = db.Offset(filter.Offset)
	db = db.Limit(filter.Limit)
	if filter.Category != "" {

	}
	if filter.CollectionName != "" {
		db = db.Where("collection_profile.name = (?)", filter.CollectionName)
	}

	if filter.Currency != "" && filter.CurrencyAmountEnd != "" && filter.CurrencyAmountStart != "" {
		db = db.Where("(token.lastest_currency = (?) AND token.lastest_price  >= (?)  AND token.lastest_price <= (?)) OR (sale.currency = (?) AND if (sale.sale_type = 'fixed' , sale.price >= (?) AND sale.price <=(?) ,	if( sale.sale_type ='auction' or sale.sale_type = 'time' , (sale.start_price >= (?)  AND sale.start_price <= (?)) or( bid_log.price >= (?) AND bid_log.price <=(?)), null)))",
			filter.Currency,
			filter.CurrencyAmountStart,
			filter.CurrencyAmountEnd,
			filter.Currency,
			filter.CurrencyAmountStart,
			filter.CurrencyAmountEnd,
			filter.CurrencyAmountStart,
			filter.CurrencyAmountEnd,
			filter.CurrencyAmountStart,
			filter.CurrencyAmountEnd,
		)
	}
	if filter.Network != "" {
		db = db.Where("collection.network in (?)", filter.Network)
	}
	if filter.SaleStatus != "" {
		db = db.Where("sale.status = (?)", filter.SaleStatus)
	}
	if filter.SaleType != "" {
		db = db.Where("sale.sale_type = (?)", filter.SaleType)
	}

	return db
}

func GetActivityItemQuery(db *gorm.DB) *gorm.DB {
	return db.Table("log_relation").
		Select(`
	log_relation.id as log_relation_id,
	token.content_url as token_content_url,
	token.name as token_name,
	log.type as log_type,
	log.sale_uid,
	log.to_address,
	log.from_address,
	sale.currency,
	sale.id as sale_id,
	token.id as token_id,
	log.price,
	log.is_cancel,
	log.tx,
	log_relation.created_at,
	from_user_info.thumbnail_image as from_user_profile,
	from_user_info.name as from_user_name, 
	from_user_info.is_artist as from_user_is_artist,
	to_user_info.thumbnail_image as to_user_profile_user,
	to_user_info.name as to_user_name,
	to_user_info.is_artist as to_user_is_artist

`).
		Joins("left join log on log_relation.id = log.log_relation_id").
		Joins("left join (select user_profile.*, user_wallet.address, if (artist_profile.id is null, false, true  ) as is_artist  from user left join user_profile on user.id = user_profile.user_id left join user_wallet on user.id = user_wallet.user_id left join artist_profile on user_profile.user_id = artist_profile.user_id) as from_user_info on log.from_address = from_user_info.address").
		Joins("left join (select user_profile.*, user_wallet.address, if (artist_profile.id is null, false, true  ) as is_artist  from user left join user_profile on user.id = user_profile.user_id left join user_wallet on user.id = user_wallet.user_id left join artist_profile on user_profile.user_id = artist_profile.user_id) as to_user_info on log.to_address = to_user_info.address").
		Joins("left join collection_profile on log_relation.collection_id = collection_profile.collection_id").
		Joins("left join token on log_relation.token_id = token.id").
		Joins("left join sale on log_relation.sale_id = sale.id").
		Order("log_relation.created_at desc").
		Where("log_relation.deleted_at is null")
}
func GetExploreItemQuery(db *gorm.DB, _filter common.Filter, authUserID uint64) *gorm.DB {

	query := Filtering(db, _filter)
	return query.Table("token").Select(`
		collection_profile.collection_id,
		collection_profile.thumbnail_image as collection_thumbnail_image,
		collection_profile.name as collection_name,
		collection.is_official as is_official_collection,
    	user_info.user_id,
		user_info.thumbnail_image as owner_thumbnail_image,
		user_info.name as owner_name,
		user_info.is_artist,
		token.id as token_id,
		token.content_url,
		token.name as content_title,
		(select count(*) bid_log where bid_log.sale_id = sale_id) as bid_count,
		token.lastest_price,
		token.lastest_currency,
		token.index as item_index,
		token.total_count,
		user_like.is_like,
		sale.sale_type as sale_type,
		if(sale.status != "ongoing", null, sale.id) as sale_id,
		if(sale.status != "ongoing", null, sale.currency) as current_currency,
		if(sale.status != "ongoing", null, sale.price) as fixed_price,
		if(sale.status != "ongoing", null, sale.start_price) as start_price,
		if(sale.status != "ongoing", null, sale.end_at) as end_at,
		if(sale.status != "ongoing", null, bid_log.price) as current_price,
		if(sale.status != "ongoing", null, sale.created_at) as created_at ,
		sale.status as sale_status,
		token.like_count as likes
		`).
		Joins("left join (select * from sale where id in (select max(id) from sale group by token_id)) as sale on token.id = sale.token_id").
		Joins("left join collection on token.collection_id = collection.id").
		Joins("left join (select user_profile.*, user_wallet.address as address, if (artist_profile.id is not null, true, false) as is_artist  from user  left join user_profile as user_profile on user.id = user_profile.user_id left join user_wallet  on user_wallet.user_id = user.id left join artist_profile on artist_profile.user_id = user.id) as user_info on token.owner_address = user_info.address").
		Joins("left join collection_profile on token.collection_id = collection_profile.collection_id").
		Joins("left join (select  * from bid_log where is_cancel != true  order by price desc  ) as bid_log on sale.id = bid_log.sale_id").
		Joins("left join (select * from user_like where user_like.user_id =(?))as user_like on user_like.token_id = token.id", authUserID).
		Where("token.deleted_at is null")
}
func getCollectionQuery(db *gorm.DB, _filter common.Filter) *gorm.DB {
	query := Filtering(db, _filter)
	return query.Table("collection").Select(`
	collection.id as collection_id,
	collection_profile.cover_image,
	collection_profile.thumbnail_image,
	collection.is_official,
	collection_profile.name as collection_name,
	user_info.user_id,
	user_info.name as owner_name,
	collection_info.total_volume,
	collection_info.total_item
	`).
		Joins("left join collection_profile on collection.id = collection_profile.collection_id").
		Joins("left join (select user_profile.* , user_wallet.address from user_wallet left join user_profile on user_wallet.user_id = user_profile.user_id) as user_info  on collection.owner_address = user_info.address").
		Joins("left join (select sum(lastest_price) as total_volume, count(*) as total_item , collection_id from token group by collection_id, lastest_currency) as collection_info on collection.id = collection_info.collection_id").
		Where("collection.deleted_at is null")
}

func GetSuggestionSearch(db *gorm.DB, _filter common.Filter) *gorm.DB {
	query := Filtering(db, _filter)
	return query.Table("collection").Select(`
	collection.id as collection_id,
	collection_profile.cover_image,
	collection_profile.thumbnail_image,
	collection.is_official,
	collection_profile.name as collection_name,
	user_info.user_id,
	user_info.name as owner_name,
	collection_info.total_volume,
	collection_info.total_item
	`).
		Joins("left join collection_profile on collection.id = collection_profile.collection_id").
		Joins("left join (select user_profile.* , user_wallet.address from user_wallet left join user_profile on user_wallet.user_id = user_profile.user_id) as user_info  on collection.owner_address = user_info.address").
		Joins("left join (select sum(lastest_price) as total_volume, count(*) as total_item , collection_id from token group by collection_id, lastest_currency) as collection_info on collection.id = collection_info.collection_id").
		Where("collection.deleted_at is null")
}

func GetArtists(db *gorm.DB, limit int) []common.ArtistItem {
	_res := []common.ArtistItem{}

	e := db.Table("artist_profile").Select(`
	artist_profile.id, 
	artist_profile.user_id, 
	user_profile.name as team, 
	artist_profile.category,
	user_profile.thumbnail_image`,
	).
		Joins("join user_profile on artist_profile.user_id = user_profile.user_id").
		Joins("join user_wallet on user_wallet.user_id = user_profile.user_id").
		// Joins("join user on user_wallet.user_id = user.id").
		Limit(limit).
		Find(&_res).Error
	if e != nil {
		return nil
	}

	return _res
}

func GetRankingSaleByArtist(db *gorm.DB, limit int, timeLimit ...time.Time) []common.TopSales {
	_res := []common.TopSales{}

	// create View unpiviot price
	db.Exec(
		`Create OR REPLACE VIEW currency_view  as 
		SELECT 1 as type, (SELECT sum(eth) FROM currency_price) as symbol
		UNION
		SELECT 2 as type, (SELECT sum(mf) FROM currency_price) as symbol
		UNION 
		SELECT 3 as type, (SELECT sum(mr) FROM currency_price) as symbol`,
	)

	db.Exec(`
		Create OR REPLACE VIEW  sales_view as 
		SELECT sale.id, sale.token_id as token_id, currency, price, currency_view.symbol, price * currency_view.symbol  as amount from sale 
		JOIN currency_view on currency_view.type = sale.currency
		ORDER BY price * currency_view.symbol DESC`,
	)

	query := db.Table("sales_view").
		Select("SUM(sales_view.amount) as amount_sales, user_profile.user_id,  user_profile.cover_image, user_profile.thumbnail_image, user_profile.name, artist_profile.category").
		Joins("inner join sale on sale.id = sales_view.id")
	if len(timeLimit) >= 2 {
		start := timeLimit[0]
		end := timeLimit[1]
		query.Where("sale.created_at BETWEEN {ts ?} AND {ts ?}", start, end)
	}

	query.Joins("inner join token on token.id = sales_view.token_id").
		Joins("inner join user_wallet on token.creator_address = user_wallet.address").
		Joins("inner join collection on token.collection_id = collection.id").
		Joins("inner join collection_profile ON collection_profile.collection_id = collection.id").
		Joins("inner join user_profile  ON user_profile.user_id = user_wallet.user_id").
		Joins("left join artist_profile  ON artist_profile.user_id = user_profile.user_id").
		Group("user_profile.user_id, user_profile.thumbnail_image, user_profile.cover_image, artist_profile.team, user_profile.name, artist_profile.category").
		Having("amount_sales != 0").
		Order("amount_sales DESC")

	query.Limit(limit).Find(&_res)

	return _res

}

func GetHintExploreQuery(db *gorm.DB, _filter common.Filter, key string) *gorm.DB {

	query := Filtering(db, _filter)
	return query.Table("token").
		Select("name").
		Joins("join user_like on user_like.token_id = token.id").
		Group("user_like.token_id").
		Having("LOWER(token.name) like  LOWER(?)", key).
		Order("COUNT(*)")
}