1 files changed, 101 insertions, 0 deletions
diff --git a/database/users.go b/database/users.go
new file mode 100644
index 0000000..169cce1
--- /dev/null
+++ b/database/users.go
@@ -0,0 +1,101 @@
+package database
+
+import (
+	"database/sql"
+	"errors"
+	"time"
+
+	_ "github.com/mattn/go-sqlite3"
+)
+
+var ErrNotFound = errors.New("not found")
+
+type User struct {
+	ID        string
+	Username  string
+	CreatedAt time.Time
+	LastSeen  time.Time
+}
+
+func scanUser(row *sql.Row) (*User, error) {
+	var u User
+	err := row.Scan(&u.ID, &u.Username, &u.CreatedAt, &u.LastSeen)
+	if errors.Is(err, sql.ErrNoRows) {
+		return nil, ErrNotFound
+	}
+	if err != nil {
+		return nil, err
+	}
+	return &u, nil
+}
+
+const userColumns = "id, username, created_at, last_seen"
+
+func CreateUser(db *sql.DB, id, username string) (*User, error) {
+	now := time.Now()
+	_, err := db.Exec(
+		"INSERT INTO users ("+userColumns+") VALUES (?, ?, ?, ?)",
+		id, username, now, now,
+	)
+	if err != nil {
+		return nil, err
+	}
+	return &User{ID: id, Username: username, CreatedAt: now, LastSeen: now}, nil
+}
+
+func GetUserByID(db *sql.DB, id string) (*User, error) {
+	return scanUser(db.QueryRow("SELECT "+userColumns+" FROM users WHERE id = ?", id))
+}
+
+func RenameUser(db *sql.DB, userID, username string) error {
+	_, err := db.Exec("UPDATE users SET username = ? WHERE id = ?", username, userID)
+	return err
+}
+
+func ListUsers(db *sql.DB) ([]User, error) {
+	rows, err := db.Query("SELECT " + userColumns + " FROM users ORDER BY last_seen DESC")
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+
+	users := []User{}
+	for rows.Next() {
+		var u User
+		if err := rows.Scan(&u.ID, &u.Username, &u.CreatedAt, &u.LastSeen); err != nil {
+			return nil, err
+		}
+		users = append(users, u)
+	}
+	return users, rows.Err()
+}
+
+func GetUserByUsername(db *sql.DB, username string) (*User, error) {
+	return scanUser(db.QueryRow("SELECT "+userColumns+" FROM users WHERE username = ?", username))
+}
+
+// GetUserByMAC finds the account a hardware address currently belongs to.
+// Columns are alias-qualified because macs also has a last_seen column.
+func GetUserByMAC(db *sql.DB, mac string) (*User, error) {
+	return scanUser(db.QueryRow(
+		"SELECT u.id, u.username, u.created_at, u.last_seen FROM users u JOIN macs m ON m.user_id = u.id WHERE m.mac = ?",
+		mac,
+	))
+}
+
+// TouchUser bumps a user's last_seen — this is the timestamp the portal shows
+// when someone tries to reclaim a taken handle.
+func TouchUser(db *sql.DB, id string) error {
+	_, err := db.Exec("UPDATE users SET last_seen = ? WHERE id = ?", time.Now(), id)
+	return err
+}
+
+// AssociateMAC binds a MAC to a user, moving it from any previous owner. This is
+// what "logging back in" from a rotated MAC does.
+func AssociateMAC(db *sql.DB, mac, userID string) error {
+	now := time.Now()
+	_, err := db.Exec(`INSERT INTO macs (mac, user_id, first_seen, last_seen) VALUES (?, ?, ?, ?)
+		ON CONFLICT(mac) DO UPDATE SET user_id = excluded.user_id, last_seen = excluded.last_seen`,
+		mac, userID, now, now)
+	return err
+}