1 files changed, 64 insertions, 0 deletions
diff --git a/database/devices.go b/database/devices.go
new file mode 100644
index 0000000..ab2a372
--- /dev/null
+++ b/database/devices.go
@@ -0,0 +1,64 @@
+package database
+
+import (
+	"database/sql"
+	"time"
+
+	_ "github.com/mattn/go-sqlite3"
+)
+
+// UserDevice is one of a user's MACs joined to its current liveness from the
+// ARP table: IP and Online are populated only when the device was seen within
+// the freshness window.
+type UserDevice struct {
+	Mac      string
+	IP       string
+	Online   bool
+	LastSeen time.Time
+}
+
+// MACOwnedByUsername reports whether a MAC is bound to the user with the given
+// username. Username matching is case-insensitive to suit DNS labels. This
+// gates the resolver's dynamic <mac>.<username> device records.
+func MACOwnedByUsername(db *sql.DB, mac, username string) (bool, error) {
+	var one int
+	err := db.QueryRow(`SELECT 1 FROM macs m JOIN users u ON u.id = m.user_id
+		WHERE m.mac = ? AND u.username = ? COLLATE NOCASE`, mac, username).Scan(&one)
+	if err == sql.ErrNoRows {
+		return false, nil
+	}
+	return err == nil, err
+}
+
+// ListUserMacs returns every MAC bound to a user, newest-seen first, annotated
+// with its current LAN IP when the device is live.
+func ListUserMacs(db *sql.DB, userID string) ([]UserDevice, error) {
+	fresh := time.Now().Add(-connectionFreshness)
+	rows, err := db.Query(`
+		SELECT m.mac, m.last_seen,
+		       (SELECT c.ip FROM connections c
+		        WHERE c.mac = m.mac AND c.updated_at > ?
+		        ORDER BY c.updated_at DESC LIMIT 1) AS ip
+		FROM macs m
+		WHERE m.user_id = ?
+		ORDER BY m.last_seen DESC`,
+		fresh, userID,
+	)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+
+	devices := []UserDevice{}
+	for rows.Next() {
+		var d UserDevice
+		var ip sql.NullString
+		if err := rows.Scan(&d.Mac, &d.LastSeen, &ip); err != nil {
+			return nil, err
+		}
+		d.IP = ip.String
+		d.Online = ip.Valid
+		devices = append(devices, d)
+	}
+	return devices, rows.Err()
+}