package db import ( "context" "encoding/json" "time" "github.com/jackc/pgx/v5/pgtype" ) const getUserQ = `-- name: GetUser :one SELECT id, COALESCE(email, '') as email, password, username, COALESCE(google_sign_in_payload, '') as google_sign_in_payload, COALESCE(about, '') as about, COALESCE(website, '') as website, COALESCE(avatar_picture, '') as avatar_picture, banned_at, banned_until, COALESCE(ban_reason, '') as ban_reason, is_permaban, is_admin, is_critics, is_verified, COALESCE(social_media, '[]'), created_at, updated_at FROM USERS WHERE username = $1 ` type GetUserRow struct { ID int32 `json:"id"` Email string `json:"email"` Password string `json:"-"` About string `json:"about"` Website string `json:"website"` Username string `json:"username"` GoogleSignInPayload string `json:"google_sign_in_payload"` AvatarPicture string `json:"avatar_picture"` BannedAt pgtype.Timestamp `json:"banned_at"` BannedUntil pgtype.Timestamp `json:"banned_until"` BanReason string `json:"ban_reason"` IsPermaban bool `json:"is_permaban"` IsAdmin bool `json:"is_admin"` IsCritics bool `json:"is_critics"` IsVerified bool `json:"is_verified"` SocialMedia json.RawMessage `json:"social_media"` CreatedAt time.Time `json:"created_at"` UpdatedAt time.Time `json:"updated_at"` } func (q *Queries) GetUser(ctx context.Context, username string) (GetUserRow, error) { row := q.db.QueryRow(ctx, getUserQ, username) var i GetUserRow err := row.Scan( &i.ID, &i.Email, &i.Password, &i.Username, &i.GoogleSignInPayload, &i.About, &i.Website, &i.AvatarPicture, &i.BannedAt, &i.BannedUntil, &i.BanReason, &i.IsPermaban, &i.IsAdmin, &i.IsCritics, &i.IsVerified, &i.SocialMedia, &i.CreatedAt, &i.UpdatedAt, ) return i, err } const getUserStatsQ = ` SELECT json_agg(ur.*) AS reviews, ( SELECT COUNT(id) FROM user_follow u WHERE u.followee_id = $1) as followers, ( SELECT COUNT(id) FROM reviews r WHERE r.submitted_by = $1 ) as score_count, ( SELECT json_agg(r1.*) AS scores_distribution FROM ( SELECT * FROM ( SELECT COUNT(id) as "0" FROM reviews r WHERE r.score >= 0 AND r.score <= 9 AND r.submitted_by = $1) as score0, ( SELECT COUNT(id) as "1" FROM reviews r WHERE r.score >= 10 AND r.score <= 19 AND r.submitted_by = $1) as score1, ( SELECT COUNT(id) as "2" FROM reviews r WHERE r.score >= 20 AND r.score <= 29 AND r.submitted_by = $1) as score2, ( SELECT COUNT(id) as "3" FROM reviews r WHERE r.score >= 30 AND r.score <= 39 AND r.submitted_by = $1) as score3, ( SELECT COUNT(id) as "4" FROM reviews r WHERE r.score >= 40 AND r.score <= 49 AND r.submitted_by = $1) as score4, ( SELECT COUNT(id) as "5" FROM reviews r WHERE r.score >= 50 AND r.score <= 59 AND r.submitted_by = $1) as score5, ( SELECT COUNT(id) as "6" FROM reviews r WHERE r.score >= 60 AND r.score <= 69 AND r.submitted_by = $1) as score6, ( SELECT COUNT(id) as "7" FROM reviews r WHERE r.score >= 70 AND r.score <= 79 AND r.submitted_by = $1) as score7, ( SELECT COUNT(id) as "8" FROM reviews r WHERE r.score >= 80 AND r.score <= 89 AND r.submitted_by = $1) as score8, ( SELECT COUNT(id) as "9" FROM reviews r WHERE r.score >= 90 AND r.score <= 99 AND r.submitted_by = $1) as score9, ( SELECT COUNT(id) as "99" FROM reviews r WHERE r.score = 100 AND r.submitted_by = $1) as score10 ) r1 ) FROM ( SELECT reviews.id, reviews.comments, score, l.name, p.province_name, COALESCE(l.thumbnail, '') as thumbnail FROM reviews JOIN locations l on reviews.location_id = l.id JOIN regencies r on l.regency_id = r.id JOIN provinces p on r.province_id = p.id WHERE reviews.submitted_by = $1 ORDER BY score ASC LIMIT 10 ) ur ` type GetUserStatsRow struct { Reviews []byte `json:"-"` Followers int32 `json:"followers"` ScoreCount int32 `json:"score_count"` ScoresDistribution []byte `json:"-"` } func (q *Queries) GetUserStats(ctx context.Context, user_id int32) (GetUserStatsRow, error) { var i GetUserStatsRow row := q.db.QueryRow(ctx, getUserStatsQ, user_id) err := row.Scan( &i.Reviews, &i.Followers, &i.ScoreCount, &i.ScoresDistribution, ) return i, err } const updateUser = `-- name: UpdateUser :one UPDATE users SET about = $1, social_media = $2, website = $3 WHERE id = $4 RETURNING id, COALESCE(email, ''), username, COALESCE(avatar_picture, ''), COALESCE(about, ''), COALESCE(website, ''), COALESCE(google_sign_in_payload, ''), banned_at, banned_until, COALESCE(ban_reason, ''), is_permaban, is_admin, is_critics, is_verified, is_active, COALESCE(social_media, '[]'), created_at, updated_at ` type UpdateUserParams struct { About pgtype.Text `json:"about"` SocialMedia interface{} `json:"social_media"` Website pgtype.Text `json:"website"` ID int32 `json:"id"` } type UpdateUserRow struct { ID int32 `json:"id"` Email string `json:"email"` Username string `json:"username"` AvatarPicture string `json:"avatar_picture"` About string `json:"about"` Website string `json:"website"` GoogleSignInPayload string `json:"google_sign_in_payload"` BannedAt pgtype.Timestamp `json:"banned_at"` BannedUntil pgtype.Timestamp `json:"banned_until"` BanReason string `json:"ban_reason"` IsPermaban bool `json:"is_permaban"` IsAdmin bool `json:"is_admin"` IsCritics bool `json:"is_critics"` IsVerified bool `json:"is_verified"` IsActive bool `json:"is_active"` SocialMedia json.RawMessage `json:"social_media"` CreatedAt time.Time `json:"created_at"` UpdatedAt time.Time `json:"updated_at"` } func (q *Queries) UpdateUser(ctx context.Context, arg UpdateUserParams) (UpdateUserRow, error) { row := q.db.QueryRow(ctx, updateUser, arg.About, arg.SocialMedia, arg.Website, arg.ID, ) var i UpdateUserRow err := row.Scan( &i.ID, &i.Email, &i.Username, &i.AvatarPicture, &i.About, &i.Website, &i.GoogleSignInPayload, &i.BannedAt, &i.BannedUntil, &i.BanReason, &i.IsPermaban, &i.IsAdmin, &i.IsCritics, &i.IsVerified, &i.IsActive, &i.SocialMedia, &i.CreatedAt, &i.UpdatedAt, ) return i, err }