2 files changed, 130 insertions, 5 deletions

diff --git a/database/migrate.go b/database/migrate.go
index f10e03b..b75c123 100644
--- a/database/migrate.go
+++ b/database/migrate.go
@@ -13,8 +13,10 @@ func MigrateUsers(dbConn *sql.DB) (*sql.DB, error) {
 	log.Println("migrating users table")
 
 	_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS users (
-		id INTEGER PRIMARY KEY AUTOINCREMENT,
+		id TEXT PRIMARY KEY,
+                mail TEXT NOT NULL,
 		username TEXT NOT NULL,
+                display_name TEXT NOT NULL,
 		created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 	);`)
 	if err != nil {
@@ -37,7 +39,7 @@ func MigrateApiKeys(dbConn *sql.DB) (*sql.DB, error) {
                 key TEXT PRIMARY KEY,
 		user_id INTEGER NOT NULL,
 		created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
-		FOREIGN KEY (user_id) REFERENCES users (id)
+		FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
 	);`)
 	if err != nil {
 		return dbConn, err
@@ -49,18 +51,33 @@ 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 INTEGER PRIMARY KEY,
+		id TEXT PRIMARY KEY,
 		user_id INTEGER 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)
+		FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE);`)
+	if err != nil {
+		return dbConn, err
+	}
+	return dbConn, nil
+}
+
+func MigrateUserSessions(dbConn *sql.DB) (*sql.DB, error) {
+	log.Println("migrating user_sessions table")
+
+	_, err := dbConn.Exec(`CREATE TABLE IF NOT EXISTS user_sessions (
+		id TEXT PRIMARY KEY,
+		user_id TEXT NOT NULL,
+		expire_at TIMESTAMP NOT NULL,
+		FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
 	);`)
 	if err != nil {
 		return dbConn, err
 	}
+
 	return dbConn, nil
 }
 
@@ -69,6 +86,7 @@ func Migrate(dbConn *sql.DB) (*sql.DB, error) {
 
 	migrations := []Migrator{
 		MigrateUsers,
+		MigrateUserSessions,
 		MigrateApiKeys,
 		MigrateDNSRecords,
 	}
diff --git a/database/users.go b/database/users.go
index 6fb2601..1ba4ebb 100644
--- a/database/users.go
+++ b/database/users.go
@@ -1,5 +1,112 @@
 package database
 
-func getUsers() {
+import (
+	"database/sql"
+	"log"
+	"time"
 
+	_ "github.com/mattn/go-sqlite3"
+)
+
+const (
+	ExpiryDuration = time.Hour * 24
+)
+
+type User struct {
+	ID          string    `json:"sub"`
+	Mail        string    `json:"email"`
+	Username    string    `json:"preferred_username"`
+	DisplayName string    `json:"name"`
+	CreatedAt   time.Time `json:"created_at"`
+}
+
+type UserSession struct {
+	ID       string    `json:"id"`
+	UserID   string    `json:"user_id"`
+	ExpireAt time.Time `json:"expire_at"`
+}
+
+func GetUser(dbConn *sql.DB, id string) (*User, error) {
+	row := dbConn.QueryRow(`SELECT id, mail, username, display_name, created_at FROM users WHERE id = ?;`, id)
+
+	var user User
+	err := row.Scan(&user.ID, &user.Mail, &user.Username, &user.DisplayName, &user.CreatedAt)
+	if err != nil {
+		log.Println(err)
+		return nil, err
+	}
+
+	return &user, nil
+}
+
+func FindOrSaveUser(dbConn *sql.DB, user *User) (*User, error) {
+	_, err := dbConn.Exec(`INSERT OR REPLACE INTO users (id, mail, username, display_name) VALUES (?, ?, ?, ?);`, user.ID, user.Mail, user.Username, user.DisplayName)
+	if err != nil {
+		return nil, err
+	}
+
+	return user, nil
+}
+
+func MakeUserSessionFor(dbConn *sql.DB, user *User) (*UserSession, error) {
+	expireAt := time.Now().Add(time.Hour * 12)
+
+	_, err := dbConn.Exec(`INSERT OR REPLACE INTO user_sessions (id, user_id, expire_at) VALUES (?, ?, ?);`, user.ID, user.ID, time.Now().Add(ExpiryDuration))
+
+	if err != nil {
+		log.Println(err)
+		return nil, err
+	}
+
+	return &UserSession{
+		ID:       user.ID,
+		UserID:   user.ID,
+		ExpireAt: expireAt,
+	}, nil
+}
+
+func GetSession(dbConn *sql.DB, sessionId string) (*UserSession, error) {
+	row := dbConn.QueryRow(`SELECT id, user_id, expire_at FROM user_sessions WHERE id = ?;`, sessionId)
+
+	var id, userId string
+	var expireAt time.Time
+	err := row.Scan(&id, &userId, &expireAt)
+	if err != nil {
+		log.Println(err)
+		return nil, err
+	}
+
+	return &UserSession{
+		ID:       id,
+		UserID:   userId,
+		ExpireAt: expireAt,
+	}, nil
+}
+
+func DeleteSession(dbConn *sql.DB, sessionId string) error {
+	_, err := dbConn.Exec(`DELETE FROM user_sessions WHERE id = ?;`, sessionId)
+	if err != nil {
+		log.Println(err)
+		return err
+	}
+
+	return nil
+}
+
+func RefreshSession(dbConn *sql.DB, sessionId string) (*UserSession, error) {
+	newExpireAt := time.Now().Add(ExpiryDuration)
+
+	_, err := dbConn.Exec(`UPDATE user_sessions SET expire_at = ? WHERE id = ?;`, newExpireAt, sessionId)
+	if err != nil {
+		log.Println(err)
+		return nil, err
+	}
+
+	session, err := GetSession(dbConn, sessionId)
+	if err != nil {
+		log.Println(err)
+		return nil, err
+	}
+
+	return session, nil
 }