3 files changed, 111 insertions, 1 deletions
diff --git a/pkg/database/accounts.go b/pkg/database/accounts.go
index d73fe27..377ca80 100644
--- a/pkg/database/accounts.go
+++ b/pkg/database/accounts.go
@@ -47,6 +47,38 @@ func (db *DB) InitAdminAccount() error {
 	})
 }
 
+func (db *DB) LoadAccountById(id int) (*model.Account, error) {
+	account := model.Account{
+		Id: id,
+	}
+	var (
+		created   int64
+		lastLogin int64
+	)
+	err := db.QueryRow(
+		`SELECT username, salt, password_hash, is_admin, created, last_login, settings
+           FROM accounts
+           WHERE id = ?;`,
+		id,
+	).Scan(&account.Username,
+		&account.Salt,
+		&account.PasswordHash,
+		&account.IsAdmin,
+		&created,
+		&lastLogin,
+		&account.Settings,
+	)
+	if err != nil {
+		if errors.Is(err, sql.ErrNoRows) {
+			return nil, nil
+		}
+		return nil, fmt.Errorf("failed to load account by id %d: %w", id, err)
+	}
+	account.Created = time.Unix(created, 0)
+	account.LastLogin = time.Unix(lastLogin, 0)
+	return &account, nil
+}
+
 func (db *DB) LoadAccountByUsername(username string) (*model.Account, error) {
 	account := model.Account{
 		Username: username,
@@ -72,7 +104,7 @@ func (db *DB) LoadAccountByUsername(username string) (*model.Account, error) {
 		if errors.Is(err, sql.ErrNoRows) {
 			return nil, nil
 		}
-		return nil, err
+		return nil, fmt.Errorf("failed to load account by username %s: %w", username, err)
 	}
 	account.Created = time.Unix(created, 0)
 	account.LastLogin = time.Unix(lastLogin, 0)
diff --git a/pkg/database/sessions.go b/pkg/database/sessions.go
new file mode 100644
index 0000000..decba8e
--- /dev/null
+++ b/pkg/database/sessions.go
@@ -0,0 +1,69 @@
+package database
+
+import (
+	"database/sql"
+	"errors"
+	"fmt"
+	"time"
+
+	"git.adyxax.org/adyxax/tfstated/pkg/model"
+	"go.n16f.net/uuid"
+)
+
+func (db *DB) CreateSession(account *model.Account) (string, error) {
+	var sessionId uuid.UUID
+	if err := sessionId.Generate(uuid.V4); err != nil {
+		return "", fmt.Errorf("failed to generate session id: %w", err)
+	}
+	if _, err := db.Exec(
+		`INSERT INTO sessions(id, account_id, data)
+		   VALUES (?, ?, ?);`,
+		sessionId.String(),
+		account.Id,
+		"",
+	); err != nil {
+		return "", fmt.Errorf("failed insert new session in database: %w", err)
+	}
+	return sessionId.String(), nil
+}
+
+func (db *DB) LoadSessionById(id string) (*model.Session, error) {
+	session := model.Session{
+		Id: id,
+	}
+	var (
+		created int64
+		updated int64
+	)
+	err := db.QueryRow(
+		`SELECT account_id,
+                created,
+                updated,
+                data
+           FROM sessions
+           WHERE id = ?;`,
+		id,
+	).Scan(&session.AccountId,
+		&created,
+		&updated,
+		&session.Data,
+	)
+	if err != nil {
+		if errors.Is(err, sql.ErrNoRows) {
+			return nil, nil
+		}
+		return nil, fmt.Errorf("failed to load session by id %s: %w", id, err)
+	}
+	session.Created = time.Unix(created, 0)
+	session.Updated = time.Unix(updated, 0)
+	return &session, nil
+}
+
+func (db *DB) TouchSession(sessionId string) error {
+	now := time.Now().UTC()
+	_, err := db.Exec(`UPDATE sessions SET updated = ? WHERE id = ?`, now.Unix(), sessionId)
+	if err != nil {
+		return fmt.Errorf("failed to touch updated for session %s: %w", sessionId, err)
+	}
+	return nil
+}
diff --git a/pkg/database/sql/000_init.sql b/pkg/database/sql/000_init.sql
index b635442..e14142b 100644
--- a/pkg/database/sql/000_init.sql
+++ b/pkg/database/sql/000_init.sql
@@ -14,6 +14,15 @@ CREATE TABLE accounts (
 ) STRICT;
 CREATE UNIQUE INDEX accounts_username on accounts(username);
 
+CREATE TABLE sessions (
+  id TEXT PRIMARY KEY,
+  account_id INTEGER NOT NULL,
+  created INTEGER NOT NULL DEFAULT (unixepoch()),
+  updated INTEGER NOT NULL DEFAULT (unixepoch()),
+  data TEXT NOT NULL,
+  FOREIGN KEY(account_id) REFERENCES accounts(id) ON DELETE CASCADE
+) STRICT;
+
 CREATE TABLE states (
   id INTEGER PRIMARY KEY,
   path TEXT NOT NULL,