1package db
  2
  3import (
  4	"context"
  5	"database/sql"
  6	"errors"
  7	"fmt"
  8	"time"
  9
 10	"postern/internal/model"
 11)
 12
 13func (db *DB) InsertUserAddress(ctx context.Context, username, address string) error {
 14	tx, err := db.write.BeginTx(ctx, nil)
 15	if err != nil {
 16		return err
 17	}
 18	defer txRollback(tx)
 19
 20	// Get userID
 21	var userID int
 22	row := tx.QueryRowContext(ctx, `SELECT id FROM users WHERE name = ?`, username)
 23	if err = row.Scan(&userID); err != nil {
 24		if errors.Is(err, sql.ErrNoRows) {
 25			return errors.New("user not found")
 26		}
 27		return err
 28	}
 29
 30	// Insert address
 31	_, err = tx.ExecContext(ctx, `INSERT INTO addresses (user_id, name, created_at) VALUES (?, ?, ?)`, userID, address, time.Now().Unix())
 32	if err != nil {
 33		return err
 34	}
 35	return tx.Commit()
 36}
 37
 38// RemoveUserAddress removes a specific address from a user. It returns
 39// model.ErrNotFound wrapped error if the user or address does not exist.
 40func (db *DB) RemoveUserAddress(ctx context.Context, username, address string) error {
 41	tx, err := db.write.BeginTx(ctx, nil)
 42	if err != nil {
 43		return err
 44	}
 45	defer txRollback(tx)
 46
 47	// Get userID
 48	var userID int
 49	row := tx.QueryRowContext(ctx, `SELECT id FROM users WHERE name = ?`, username)
 50	if err = row.Scan(&userID); err != nil {
 51		if errors.Is(err, sql.ErrNoRows) {
 52			return fmt.Errorf("%w: user %q", model.ErrNotFound, username)
 53		}
 54		return err
 55	}
 56
 57	res, err := tx.ExecContext(ctx, `DELETE FROM addresses WHERE user_id = ? AND name = ?`, userID, address)
 58	if err != nil {
 59		return err
 60	}
 61
 62	n, err := res.RowsAffected()
 63	if err != nil {
 64		return err
 65	}
 66	if n == 0 {
 67		return fmt.Errorf("%w: address %q", model.ErrNotFound, address)
 68	}
 69	return tx.Commit()
 70}
 71
 72// ListUserAddresses returns all addresses for a user, ordered by name.
 73func (db *DB) ListUserAddresses(ctx context.Context, username string) ([]string, error) {
 74	rows, err := db.read.QueryContext(
 75		ctx, `SELECT addresses.name FROM addresses INNER JOIN users ON users.id = addresses.user_id WHERE users.name = ? ORDER BY addresses.name`, username)
 76	if err != nil {
 77		return nil, err
 78	}
 79	defer func(rows *sql.Rows) {
 80		_ = rows.Close()
 81	}(rows)
 82
 83	var addresses []string
 84	for rows.Next() {
 85		var name string
 86		if err := rows.Scan(&name); err != nil {
 87			return nil, err
 88		}
 89		addresses = append(addresses, name)
 90	}
 91	if err := rows.Err(); err != nil {
 92		return nil, err
 93	}
 94	return addresses, nil
 95}
 96
 97func (db *DB) GetUserForAddress(ctx context.Context, address string) (model.User, error) {
 98	res := db.read.QueryRowContext(ctx, `SELECT users.id, users.name, users.password_hash FROM users INNER JOIN addresses ON users.id = addresses.user_id WHERE addresses.name = ?`, address)
 99
100	var u model.User
101	var pw []byte
102	err := res.Scan(&u.ID, &u.Name, &pw)
103	u.SetPassword(pw)
104	return u, err
105}
106
107func (db *DB) GetGatekeepDecision(ctx context.Context, userID int, fromAddress string) (string, error) {
108	res := db.read.QueryRowContext(ctx, `SELECT destination FROM gatekeepers WHERE from_address = ? and user_id = ?`, fromAddress, userID)
109
110	var mailbox string
111	err := res.Scan(&mailbox)
112	if err != nil {
113		return "", err
114	}
115
116	return mailbox, nil
117}