6 files changed, 384 insertions, 0 deletions

diff --git a/database/connections.go b/database/connections.go
index e16a17e..1af9de7 100644
--- a/database/connections.go
+++ b/database/connections.go
@@ -60,6 +60,22 @@ func MacForIP(db *sql.DB, ip string) (string, error) {
 	return mac, err
 }
 
+// IPForMac returns the freshest live IP for a MAC, or "" if the device hasn't
+// been seen within the freshness window. This is what the DNS server answers a
+// device's A query with. A MAC can briefly hold more than one IP (a lease
+// change mid-window), so the most recently updated entry wins.
+func IPForMac(db *sql.DB, mac string) (string, error) {
+	var ip string
+	err := db.QueryRow(
+		"SELECT ip FROM connections WHERE mac = ? AND updated_at > ? ORDER BY updated_at DESC LIMIT 1",
+		mac, time.Now().Add(-connectionFreshness),
+	).Scan(&ip)
+	if errors.Is(err, sql.ErrNoRows) {
+		return "", nil
+	}
+	return ip, err
+}
+
 // NetworkUserIDs returns the set of users whose MAC was seen on the LAN within
 // the freshness window, whether or not they're on the chat.
 func NetworkUserIDs(db *sql.DB) (map[string]bool, error) {
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()
+}
diff --git a/database/dns.go b/database/dns.go
new file mode 100644
index 0000000..8372936
--- /dev/null
+++ b/database/dns.go
@@ -0,0 +1,157 @@
+package database
+
+import (
+	"database/sql"
+	"strings"
+	"time"
+
+	_ "github.com/mattn/go-sqlite3"
+)
+
+// DNSRecord is a user-owned record in the LAN zone. Names are stored fully
+// qualified with a trailing dot (e.g. "tux.penguins.lan.") to match the form
+// the resolver queries with.
+type DNSRecord struct {
+	ID        string
+	UserID    string
+	Name      string
+	Type      string
+	Content   string
+	TTL       int
+	CreatedAt time.Time
+}
+
+const dnsColumns = "id, user_id, name, type, content, ttl, created_at"
+
+func scanDNSRecord(s interface{ Scan(...any) error }) (*DNSRecord, error) {
+	var r DNSRecord
+	if err := s.Scan(&r.ID, &r.UserID, &r.Name, &r.Type, &r.Content, &r.TTL, &r.CreatedAt); err != nil {
+		return nil, err
+	}
+	return &r, nil
+}
+
+func CountUserDNSRecords(db *sql.DB, userID string) (int, error) {
+	var count int
+	err := db.QueryRow("SELECT COUNT(*) FROM dns_records WHERE user_id = ?", userID).Scan(&count)
+	return count, err
+}
+
+func GetUserDNSRecords(db *sql.DB, userID string) ([]DNSRecord, error) {
+	rows, err := db.Query("SELECT "+dnsColumns+" FROM dns_records WHERE user_id = ? ORDER BY name, type", userID)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+
+	records := []DNSRecord{}
+	for rows.Next() {
+		r, err := scanDNSRecord(rows)
+		if err != nil {
+			return nil, err
+		}
+		records = append(records, *r)
+	}
+	return records, rows.Err()
+}
+
+// SaveDNSRecord upserts a record. The unique (name, type, content) index means
+// re-saving the same record is a no-op rather than a duplicate.
+func SaveDNSRecord(db *sql.DB, record *DNSRecord) (*DNSRecord, error) {
+	if record.CreatedAt.IsZero() {
+		record.CreatedAt = time.Now()
+	}
+	_, err := db.Exec(
+		"INSERT OR REPLACE INTO dns_records ("+dnsColumns+") VALUES (?, ?, ?, ?, ?, ?, ?)",
+		record.ID, record.UserID, record.Name, record.Type, record.Content, record.TTL, record.CreatedAt,
+	)
+	if err != nil {
+		return nil, err
+	}
+	return record, nil
+}
+
+func GetDNSRecord(db *sql.DB, recordID string) (*DNSRecord, error) {
+	r, err := scanDNSRecord(db.QueryRow("SELECT "+dnsColumns+" FROM dns_records WHERE id = ?", recordID))
+	if err == sql.ErrNoRows {
+		return nil, ErrNotFound
+	}
+	return r, err
+}
+
+func DeleteDNSRecord(db *sql.DB, recordID string) error {
+	_, err := db.Exec("DELETE FROM dns_records WHERE id = ?", recordID)
+	return err
+}
+
+// DeleteUserRecordsByNameType removes a user's records at a name of a given
+// type. Used to keep a single CNAME per name (so re-running the wizard or
+// repointing the primary replaces rather than duplicates).
+func DeleteUserRecordsByNameType(db *sql.DB, userID, name, qtype string) error {
+	_, err := db.Exec("DELETE FROM dns_records WHERE user_id = ? AND name = ? AND type = ?", userID, name, qtype)
+	return err
+}
+
+// RenameUserDNSDomain moves a user's records from oldDomain to newDomain (both
+// of the form "<username>.<zone>", no trailing dot) when their handle changes,
+// rewriting the suffix in both record names and contents so their setup keeps
+// working under the new name. Records that don't reference the domain are left
+// alone.
+func RenameUserDNSDomain(db *sql.DB, userID, oldDomain, newDomain string) error {
+	records, err := GetUserDNSRecords(db, userID)
+	if err != nil {
+		return err
+	}
+	for _, rec := range records {
+		newName := rewriteDomainSuffix(rec.Name, oldDomain, newDomain)
+		newContent := rewriteDomainSuffix(rec.Content, oldDomain, newDomain)
+		if newName == rec.Name && newContent == rec.Content {
+			continue
+		}
+		rec.Name, rec.Content = newName, newContent
+		if _, err := SaveDNSRecord(db, &rec); err != nil {
+			return err
+		}
+	}
+	return nil
+}
+
+// rewriteDomainSuffix replaces a trailing oldDomain (the whole string or a
+// dot-separated suffix) with newDomain, preserving any trailing dot. A value
+// that merely contains oldDomain mid-string is left untouched.
+func rewriteDomainSuffix(s, oldDomain, newDomain string) string {
+	hadDot := strings.HasSuffix(s, ".")
+	bare := strings.TrimSuffix(s, ".")
+	switch {
+	case bare == oldDomain:
+		bare = newDomain
+	case strings.HasSuffix(bare, "."+oldDomain):
+		bare = strings.TrimSuffix(bare, "."+oldDomain) + "." + newDomain
+	default:
+		return s
+	}
+	if hadDot {
+		return bare + "."
+	}
+	return bare
+}
+
+// FindDNSRecords returns every record matching a fully-qualified name and type.
+// This is the resolver's lookup path.
+func FindDNSRecords(db *sql.DB, name, qtype string) ([]DNSRecord, error) {
+	rows, err := db.Query("SELECT "+dnsColumns+" FROM dns_records WHERE name = ? AND type = ?", name, qtype)
+	if err != nil {
+		return nil, err
+	}
+	defer rows.Close()
+
+	records := []DNSRecord{}
+	for rows.Next() {
+		r, err := scanDNSRecord(rows)
+		if err != nil {
+			return nil, err
+		}
+		records = append(records, *r)
+	}
+	return records, rows.Err()
+}
diff --git a/database/dns_test.go b/database/dns_test.go
new file mode 100644
index 0000000..a893021
--- /dev/null
+++ b/database/dns_test.go
@@ -0,0 +1,62 @@
+package database
+
+import (
+	"database/sql"
+	"path/filepath"
+	"testing"
+)
+
+func dnsTestDB(t *testing.T) *sql.DB {
+	t.Helper()
+	db, err := sql.Open("sqlite3", filepath.Join(t.TempDir(), "dns.db"))
+	if err != nil {
+		t.Fatal(err)
+	}
+	t.Cleanup(func() { db.Close() })
+	if _, err := Migrate(db); err != nil {
+		t.Fatal(err)
+	}
+	return db
+}
+
+func TestRenameUserDNSDomain(t *testing.T) {
+	db := dnsTestDB(t)
+	if _, err := CreateUser(db, "u1", "tux"); err != nil {
+		t.Fatal(err)
+	}
+
+	records := []*DNSRecord{
+		{ID: "a", UserID: "u1", Name: "tux.penguins.lan.", Type: "CNAME", Content: "aabbccddeeff.tux.penguins.lan", TTL: 300},
+		{ID: "b", UserID: "u1", Name: "www.tux.penguins.lan.", Type: "A", Content: "192.168.8.9", TTL: 60},
+		// content that merely contains the domain mid-string must NOT be rewritten
+		{ID: "c", UserID: "u1", Name: "note.tux.penguins.lan.", Type: "TXT", Content: "notmytux.penguins.lan", TTL: 60},
+	}
+	for _, r := range records {
+		if _, err := SaveDNSRecord(db, r); err != nil {
+			t.Fatal(err)
+		}
+	}
+
+	if err := RenameUserDNSDomain(db, "u1", "tux.penguins.lan", "gentoo.penguins.lan"); err != nil {
+		t.Fatal(err)
+	}
+
+	got, err := GetUserDNSRecords(db, "u1")
+	if err != nil {
+		t.Fatal(err)
+	}
+	byID := map[string]DNSRecord{}
+	for _, r := range got {
+		byID[r.ID] = r
+	}
+
+	if byID["a"].Name != "gentoo.penguins.lan." || byID["a"].Content != "aabbccddeeff.gentoo.penguins.lan" {
+		t.Errorf("apex CNAME = %q -> %q, want gentoo + device target", byID["a"].Name, byID["a"].Content)
+	}
+	if byID["b"].Name != "www.gentoo.penguins.lan." || byID["b"].Content != "192.168.8.9" {
+		t.Errorf("A record = %q -> %q, want www.gentoo + unchanged ip", byID["b"].Name, byID["b"].Content)
+	}
+	if byID["c"].Name != "note.gentoo.penguins.lan." || byID["c"].Content != "notmytux.penguins.lan" {
+		t.Errorf("TXT = %q -> %q, want name moved but content untouched", byID["c"].Name, byID["c"].Content)
+	}
+}
diff --git a/database/migrate.go b/database/migrate.go
index 24d47b6..13b2cb1 100644
--- a/database/migrate.go
+++ b/database/migrate.go
@@ -3,6 +3,7 @@ package database
 import (
 	"database/sql"
 	"log"
+	"strings"
 
 	_ "github.com/mattn/go-sqlite3"
 )
@@ -15,6 +16,7 @@ func MigrateUsers(dbConn *sql.DB) (*sql.DB, error) {
 	_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS users (
 		id TEXT PRIMARY KEY,
 		username TEXT NOT NULL,
+		primary_mac TEXT,
 		created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 		last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 	);`)
@@ -26,6 +28,46 @@ func MigrateUsers(dbConn *sql.DB) (*sql.DB, error) {
 	return dbConn, err
 }
 
+// MigrateUserPrimaryMac adds primary_mac to pre-existing users tables. It's
+// idempotent: SQLite has no "ADD COLUMN IF NOT EXISTS", so the duplicate-column
+// error (column already present, e.g. on a fresh DB) is swallowed.
+func MigrateUserPrimaryMac(dbConn *sql.DB) (*sql.DB, error) {
+	log.Println("migrating users.primary_mac column")
+
+	_, err := dbConn.Exec(`ALTER TABLE users ADD COLUMN primary_mac TEXT;`)
+	if err != nil && !strings.Contains(err.Error(), "duplicate column name") {
+		return dbConn, err
+	}
+	return dbConn, nil
+}
+
+// MigrateDNSRecords holds the user-editable DNS records served for the LAN zone.
+func MigrateDNSRecords(dbConn *sql.DB) (*sql.DB, error) {
+	log.Println("migrating dns_records table")
+
+	_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS dns_records (
+		id TEXT PRIMARY KEY,
+		user_id TEXT NOT NULL,
+		name TEXT NOT NULL,
+		type TEXT NOT NULL,
+		content TEXT NOT NULL,
+		ttl INTEGER NOT NULL,
+		created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+		FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
+	);`)
+	if err != nil {
+		return dbConn, err
+	}
+
+	_, err = dbConn.Exec(`CREATE UNIQUE INDEX IF NOT EXISTS idx_dns_records_name_content_type ON dns_records (name, type, content);`)
+	if err != nil {
+		return dbConn, err
+	}
+
+	_, err = dbConn.Exec(`CREATE INDEX IF NOT EXISTS idx_dns_records_name_type ON dns_records (name, type);`)
+	return dbConn, err
+}
+
 // MigrateMacs maps hardware addresses to users. One user can own many MACs —
 // that's how a privacy-rotated device "logs back in" to an existing account.
 func MigrateMacs(dbConn *sql.DB) (*sql.DB, error) {
@@ -124,12 +166,14 @@ func Migrate(dbConn *sql.DB) (*sql.DB, error) {
 
 	migrations := []Migrator{
 		MigrateUsers,
+		MigrateUserPrimaryMac,
 		MigrateMacs,
 		MigrateSessions,
 		MigrateChat,
 		MigrateConnections,
 		MigrateMinecraft,
 		MigrateCanvas,
+		MigrateDNSRecords,
 	}
 
 	for _, migration := range migrations {
diff --git a/database/users.go b/database/users.go
index 169cce1..164a631 100644
--- a/database/users.go
+++ b/database/users.go
@@ -99,3 +99,44 @@ func AssociateMAC(db *sql.DB, mac, userID string) error {
 		mac, userID, now, now)
 	return err
 }
+
+// MACBelongsToUser reports whether a MAC is currently bound to the given user.
+func MACBelongsToUser(db *sql.DB, mac, userID string) (bool, error) {
+	var owner string
+	err := db.QueryRow("SELECT user_id FROM macs WHERE mac = ?", mac).Scan(&owner)
+	if errors.Is(err, sql.ErrNoRows) {
+		return false, nil
+	}
+	if err != nil {
+		return false, err
+	}
+	return owner == userID, nil
+}
+
+// SetUserPrimaryMAC sets the user's primary device (the MAC its
+// <username>.<zone> name points at).
+func SetUserPrimaryMAC(db *sql.DB, userID, mac string) error {
+	_, err := db.Exec("UPDATE users SET primary_mac = ? WHERE id = ?", mac, userID)
+	return err
+}
+
+// SetPrimaryMACIfUnset atomically makes mac the primary device only when the
+// user has none yet. The conditional WHERE makes "first device wins" race-free:
+// concurrent associations can't both win, and there's no read-then-write gap.
+func SetPrimaryMACIfUnset(db *sql.DB, userID, mac string) error {
+	_, err := db.Exec("UPDATE users SET primary_mac = ? WHERE id = ? AND primary_mac IS NULL", mac, userID)
+	return err
+}
+
+// GetUserPrimaryMAC returns the user's primary MAC, or "" if unset.
+func GetUserPrimaryMAC(db *sql.DB, userID string) (string, error) {
+	var mac sql.NullString
+	err := db.QueryRow("SELECT primary_mac FROM users WHERE id = ?", userID).Scan(&mac)
+	if errors.Is(err, sql.ErrNoRows) {
+		return "", ErrNotFound
+	}
+	if err != nil {
+		return "", err
+	}
+	return mac.String, nil
+}