1package postgres
  2
  3import (
  4	"errors"
  5	"fmt"
  6	"slices"
  7	"strings"
  8
  9	"github.com/picosh/pico/pkg/db"
 10)
 11
 12func (me *PsqlDB) shouldBlockSingup(ip string) error {
 13	blocked := &db.BlockSignups{}
 14	err := me.Db.Get(blocked, `SELECT * FROM block_signups WHERE ip = $1`, ip)
 15	// an error in this case means the result is empty (no record found)
 16	if err != nil {
 17		return nil
 18	}
 19	return fmt.Errorf("your IP address has been blocked: %s", blocked.Reason)
 20}
 21
 22func (me *PsqlDB) RegisterUser(username, pubkey, comment, ip string) (*db.User, error) {
 23	lowerName := strings.ToLower(username)
 24	valid, err := me.validateName(lowerName)
 25	if !valid {
 26		return nil, err
 27	}
 28
 29	me.Logger.Info("checking if ip is in block list", "ip", ip, "username", username)
 30	err = me.shouldBlockSingup(ip)
 31	if err != nil {
 32		me.Logger.Warn("user has been blocked from signing up", "ip", ip, "username", username, "err", err)
 33		return nil, err
 34	}
 35
 36	tx, err := me.Db.Beginx()
 37	if err != nil {
 38		return nil, err
 39	}
 40	defer func() {
 41		_ = tx.Rollback()
 42	}()
 43
 44	var id string
 45	err = tx.QueryRow(`INSERT INTO app_users (name) VALUES($1) returning id`, lowerName).Scan(&id)
 46	if err != nil {
 47		return nil, err
 48	}
 49
 50	err = me.insertPublicKeyWithTx(id, pubkey, comment, tx)
 51	if err != nil {
 52		return nil, err
 53	}
 54
 55	err = tx.Commit()
 56	if err != nil {
 57		return nil, err
 58	}
 59
 60	return me.FindUserByKey(username, pubkey)
 61}
 62
 63func (me *PsqlDB) FindUserByKey(username string, key string) (*db.User, error) {
 64	me.Logger.Info("attempting to find user with only public key", "key", key)
 65	pk, err := me.findPublicKeyByKey(key)
 66	if err == nil {
 67		me.Logger.Info("found pubkey, looking for user", "key", key, "userId", pk.UserID)
 68		user, err := me.FindUser(pk.UserID)
 69		if err != nil {
 70			return nil, err
 71		}
 72		user.PublicKey = pk
 73		return user, nil
 74	}
 75
 76	if errors.Is(err, &db.ErrMultiplePublicKeys{}) {
 77		me.Logger.Info("detected multiple users with same public key", "user", username)
 78		user, err := me.findUserForNameAndKey(username, key)
 79		if err != nil {
 80			me.Logger.Info("could not find user by username and public key", "user", username, "key", key)
 81			// this is a little hacky but if we cannot find a user by name and public key
 82			// then we return the multiple keys detected error so the user knows to specify their
 83			// when logging in
 84			return nil, &db.ErrMultiplePublicKeys{}
 85		}
 86		return user, nil
 87	}
 88
 89	return nil, err
 90}
 91
 92func (me *PsqlDB) FindUserByPubkey(key string) (*db.User, error) {
 93	me.Logger.Info("attempting to find user with only public key", "key", key)
 94	pk, err := me.findPublicKeyByKey(key)
 95	if err != nil {
 96		return nil, err
 97	}
 98
 99	me.Logger.Info("found pubkey, looking for user", "key", key, "userId", pk.UserID)
100	user, err := me.FindUser(pk.UserID)
101	if err != nil {
102		return nil, err
103	}
104	user.PublicKey = pk
105	return user, nil
106}
107
108func (me *PsqlDB) FindUser(userID string) (*db.User, error) {
109	user := &db.User{}
110	err := me.Db.Get(user, `SELECT id, COALESCE(name, '') as name, created_at FROM app_users WHERE id = $1`, userID)
111	if err != nil {
112		return nil, err
113	}
114	return user, nil
115}
116
117func (me *PsqlDB) validateName(name string) (bool, error) {
118	lower := strings.ToLower(name)
119	if slices.Contains(db.DenyList, lower) {
120		return false, fmt.Errorf("%s is on deny list: %w", lower, db.ErrNameDenied)
121	}
122	v := db.NameValidator.MatchString(lower)
123	if !v {
124		return false, fmt.Errorf("%s is invalid: %w", lower, db.ErrNameInvalid)
125	}
126	user, _ := me.FindUserByName(lower)
127	if user == nil {
128		return true, nil
129	}
130	return false, fmt.Errorf("%s already taken: %w", lower, db.ErrNameTaken)
131}
132
133func (me *PsqlDB) FindUserByName(name string) (*db.User, error) {
134	user := &db.User{}
135	err := me.Db.Get(user, `SELECT * FROM app_users WHERE name = $1`, strings.ToLower(name))
136	if err != nil {
137		return nil, err
138	}
139	return user, nil
140}
141
142func (me *PsqlDB) findUserForNameAndKey(name string, key string) (*db.User, error) {
143	user := &db.User{}
144	pk := &db.PublicKey{}
145
146	r := me.Db.QueryRow(`SELECT app_users.id, app_users.name, app_users.created_at, public_keys.id as pk_id, public_keys.public_key, public_keys.created_at as pk_created_at FROM app_users LEFT JOIN public_keys ON public_keys.user_id = app_users.id WHERE app_users.name = $1 AND public_keys.public_key = $2`, strings.ToLower(name), key)
147	err := r.Scan(&user.ID, &user.Name, &user.CreatedAt, &pk.ID, &pk.Key, &pk.CreatedAt)
148	if err != nil {
149		return nil, err
150	}
151
152	user.PublicKey = pk
153	return user, nil
154}
155
156func (me *PsqlDB) FindUsers() ([]*db.User, error) {
157	var users []*db.User
158	err := me.Db.Select(&users, `SELECT id, COALESCE(name, '') as name, created_at FROM app_users ORDER BY name ASC`)
159	if err != nil {
160		return nil, err
161	}
162	return users, nil
163}
164
165func (me *PsqlDB) FindUserStats(userID string) (*db.UserStats, error) {
166	stats := db.UserStats{}
167	rs, err := me.Db.Queryx(`SELECT cur_space, count(id), min(created_at), max(created_at), max(updated_at) FROM posts WHERE user_id=$1 GROUP BY cur_space`, userID)
168	if err != nil {
169		return nil, err
170	}
171	defer func() { _ = rs.Close() }()
172
173	for rs.Next() {
174		stat := db.UserServiceStats{}
175		err := rs.Scan(&stat.Service, &stat.Num, &stat.FirstCreatedAt, &stat.LastestCreatedAt, &stat.LatestUpdatedAt)
176		if err != nil {
177			return nil, err
178		}
179		switch stat.Service {
180		case "prose":
181			stats.Prose = stat
182		case "pastes":
183			stats.Pastes = stat
184		case "feeds":
185			stats.Feeds = stat
186		}
187	}
188
189	if rs.Err() != nil {
190		return nil, rs.Err()
191	}
192
193	pgs, err := me.findPagesStats(userID)
194	if err != nil {
195		return nil, err
196	}
197	stats.Pages = *pgs
198	return &stats, err
199}