1package db
  2
  3import (
  4	"context"
  5	"database/sql"
  6	"errors"
  7	"fmt"
  8	"time"
  9
 10	"postern/internal/model"
 11
 12	"modernc.org/sqlite"
 13	sqlite3 "modernc.org/sqlite/lib"
 14)
 15
 16func InsertUser(ctx context.Context, tx *sql.Tx, username string, hash []byte) (int, error) {
 17	res, err := tx.ExecContext(ctx,
 18		`INSERT INTO users (name, password_hash, created_at) VALUES (?, ?, ?)`,
 19		username, hash, time.Now().Unix(),
 20	)
 21	if err != nil {
 22		if insertErr, ok := errors.AsType[*sqlite.Error](err); ok {
 23			if insertErr.Code() == sqlite3.SQLITE_CONSTRAINT_UNIQUE {
 24				return 0, fmt.Errorf("user %s already exists", username)
 25			}
 26			return 0, fmt.Errorf("inserting user: %w", err)
 27		}
 28	}
 29
 30	userID, err := res.LastInsertId()
 31	return int(userID), err
 32}
 33
 34func (db *DB) GetUser(ctx context.Context, username string) (model.User, error) {
 35	rows, err := db.read.QueryContext(
 36		ctx, `SELECT users.id, users.name, users.password_hash, addresses.name FROM users LEFT JOIN addresses ON users.id = addresses.user_id WHERE users.name = ?`, username)
 37	if err != nil {
 38		return model.User{}, err
 39	}
 40	defer func(rows *sql.Rows) {
 41		_ = rows.Close()
 42	}(rows)
 43
 44	var u model.User
 45	var pw []byte
 46	var address sql.NullString
 47	found := false
 48	for rows.Next() {
 49		err := rows.Scan(&u.ID, &u.Name, &pw, &address)
 50		if err != nil {
 51			return model.User{}, err
 52		}
 53		found = true
 54		if address.Valid {
 55			u.Addresses = append(u.Addresses, address.String)
 56		}
 57	}
 58	if rows.Err() != nil {
 59		return model.User{}, rows.Err()
 60	}
 61
 62	if !found {
 63		return model.User{}, errors.New("user not found")
 64	}
 65
 66	u.SetPassword(pw)
 67	return u, nil
 68}
 69
 70func (db *DB) UserExists(ctx context.Context, username string) (bool, error) {
 71	var count int
 72	err := db.read.QueryRowContext(ctx,
 73		`SELECT COUNT(*) FROM users WHERE name = ?`,
 74		username,
 75	).Scan(&count)
 76	if err != nil {
 77		return false, fmt.Errorf("checking user existence: %w", err)
 78	}
 79	return count > 0, nil
 80}
 81
 82func InsertDefaultMailboxes(ctx context.Context, tx *sql.Tx, userID int) error {
 83	uidvalidity := uint32(time.Now().UnixMilli())
 84
 85	for _, mb := range model.DefaultMailboxes() {
 86		_, err := tx.ExecContext(ctx, `
 87            INSERT INTO mailboxes
 88                (user_id, name, special_use, uidvalidity, uidnext, highest_modseq)
 89            VALUES
 90                (?, ?, ?, ?, 1, 0)`,
 91			userID, mb.Name, mb.SpecialUse, uidvalidity,
 92		)
 93		if err != nil {
 94			return fmt.Errorf("creating mailbox %q: %w", mb.Name, err)
 95		}
 96	}
 97
 98	return nil
 99}
100
101func (db *DB) ListUsers(ctx context.Context) ([]model.User, error) {
102	query := `
103        SELECT users.id, users.name, users.password_hash, addresses.name 
104        FROM users 
105        LEFT JOIN addresses ON users.id = addresses.user_id 
106        ORDER BY users.name
107    `
108
109	rows, err := db.read.QueryContext(ctx, query)
110	if err != nil {
111		return nil, err
112	}
113	defer func(rows *sql.Rows) {
114		_ = rows.Close()
115	}(rows)
116
117	var users []model.User
118
119	for rows.Next() {
120		var id int
121		var name string
122		var passwordHash []byte
123		var addressName sql.NullString
124
125		if err := rows.Scan(&id, &name, &passwordHash, &addressName); err != nil {
126			return nil, err
127		}
128
129		// If the slice is empty, OR the last appended user has a different ID,
130		// we have encountered a new user.
131		if len(users) == 0 || users[len(users)-1].ID != id {
132			nextUser := model.User{
133				ID:        id,
134				Name:      name,
135				Addresses: []string{},
136			}
137			nextUser.SetPassword(passwordHash)
138			users = append(users, nextUser)
139		}
140
141		if addressName.Valid {
142			lastIdx := len(users) - 1
143			users[lastIdx].Addresses = append(users[lastIdx].Addresses, addressName.String)
144		}
145	}
146
147	if err = rows.Err(); err != nil {
148		return nil, err
149	}
150
151	return users, nil
152}
153
154func (db *DB) RemoveUser(ctx context.Context, username string) error {
155	_, err := db.write.ExecContext(ctx, "DELETE FROM users WHERE name = ?", username)
156	return err
157}