1 files changed, 143 insertions, 0 deletions
diff --git a/database/migrate.go b/database/migrate.go
new file mode 100644
index 0000000..24d47b6
--- /dev/null
+++ b/database/migrate.go
@@ -0,0 +1,143 @@
+package database
+
+import (
+	"database/sql"
+	"log"
+
+	_ "github.com/mattn/go-sqlite3"
+)
+
+type Migrator func(*sql.DB) (*sql.DB, error)
+
+func MigrateUsers(dbConn *sql.DB) (*sql.DB, error) {
+	log.Println("migrating users table")
+
+	_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS users (
+		id TEXT PRIMARY KEY,
+		username TEXT NOT NULL,
+		created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+		last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+	);`)
+	if err != nil {
+		return dbConn, err
+	}
+
+	_, err = dbConn.Exec(`CREATE UNIQUE INDEX IF NOT EXISTS idx_users_username ON users (username);`)
+	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) {
+	log.Println("migrating macs table")
+
+	_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS macs (
+		mac TEXT PRIMARY KEY,
+		user_id TEXT NOT NULL,
+		first_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+		last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+		FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
+	);`)
+	return dbConn, err
+}
+
+func MigrateSessions(dbConn *sql.DB) (*sql.DB, error) {
+	log.Println("migrating sessions table")
+
+	_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS sessions (
+		id TEXT PRIMARY KEY,
+		user_id TEXT NOT NULL,
+		created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+		expire_at TIMESTAMP NOT NULL,
+		FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
+	);`)
+	return dbConn, err
+}
+
+func MigrateChat(dbConn *sql.DB) (*sql.DB, error) {
+	log.Println("migrating chat table")
+
+	_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS chat (
+		id TEXT PRIMARY KEY,
+		user_id TEXT NOT NULL,
+		body TEXT 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 INDEX IF NOT EXISTS idx_chat_created_at ON chat (created_at);`)
+	return dbConn, err
+}
+
+func MigrateConnections(dbConn *sql.DB) (*sql.DB, error) {
+	log.Println("migrating connections table")
+
+	_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS connections (
+		ip TEXT PRIMARY KEY,
+		mac TEXT NOT NULL,
+		updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+	);`)
+	return dbConn, err
+}
+
+func MigrateMinecraft(dbConn *sql.DB) (*sql.DB, error) {
+	log.Println("migrating minecraft_status table")
+
+	_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS minecraft_status (
+		id INTEGER PRIMARY KEY CHECK (id = 1),
+		status TEXT NOT NULL,
+		updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+	);`)
+	return dbConn, err
+}
+
+func MigrateCanvas(dbConn *sql.DB) (*sql.DB, error) {
+	log.Println("migrating canvas tables")
+
+	_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS canvas_pixels (
+		id INTEGER PRIMARY KEY AUTOINCREMENT,
+		user_id TEXT,
+		x INTEGER NOT NULL,
+		y INTEGER NOT NULL,
+		color INTEGER NOT NULL,
+		created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+		FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL
+	);`)
+	if err != nil {
+		return dbConn, err
+	}
+
+	_, err = dbConn.Exec(`CREATE TABLE IF NOT EXISTS canvas_snapshots (
+		id INTEGER PRIMARY KEY AUTOINCREMENT,
+		data BLOB NOT NULL,
+		through_id INTEGER NOT NULL,
+		created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+	);`)
+	return dbConn, err
+}
+
+func Migrate(dbConn *sql.DB) (*sql.DB, error) {
+	log.Println("migrating database")
+
+	migrations := []Migrator{
+		MigrateUsers,
+		MigrateMacs,
+		MigrateSessions,
+		MigrateChat,
+		MigrateConnections,
+		MigrateMinecraft,
+		MigrateCanvas,
+	}
+
+	for _, migration := range migrations {
+		var err error
+		if dbConn, err = migration(dbConn); err != nil {
+			return dbConn, err
+		}
+	}
+
+	return dbConn, nil
+}