tripwire/db.go

package main

import (
	"database/sql"

	"github.com/google/uuid"
	_ "github.com/mattn/go-sqlite3"
)

var DB *sql.DB

func initDB() error {
	db, err := sql.Open("sqlite3", "./db.sqlite")
	if err != nil {
		return err
	}
	DB = db
	err = createDatabase()
	if err != nil {
		return err
	}
	return nil
}

func createDatabase() error {
	sqlStatement := `
	CREATE TABLE IF NOT EXISTS users (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		username VARCHAR(255) NOT NULL,
		password VARCHAR(255) NOT NULL,
		uuid VARCHAR(255) NOT NULL,
		client_token VARCHAR(255) NOT NULL,
		auth_token VARCHAR(255) NOT NULL,
		web_token VARCHAR(255) NOT NULL,
		created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
	);
	`
	_, err := DB.Exec(sqlStatement)
	if err != nil {
		return err
	}
	return nil
}

func insertUser(username string, password string) error {
	playeruuid := uuid.New().String()
	sqlStatement := `
	INSERT INTO users (username, password, uuid, client_token, auth_token, web_token) VALUES (?, ?, ?, ?, ?, ?);
	`
	_, err := DB.Exec(sqlStatement, username, password, playeruuid, "", "", "")
	if err != nil {
		return err
	}
	return nil
}

// todo: deduplicate these functions
func getAuthToken(username string, password string) (string, error) {
	sqlStatement := `
	SELECT auth_token FROM users WHERE username = ? AND password = ?;
	`
	row := DB.QueryRow(sqlStatement, username, password)

	// get auth token
	var authToken string
	err := row.Scan(&authToken)
	if err != nil {
		if err != sql.ErrNoRows {
			return "", err
		} else {
			return "", &NotFoundError{}
		}
	}
	if authToken == "" {
		// generate new authToken
		authToken = uuid.New().String()
		// update authToken
		sqlStatement := `
		UPDATE users SET auth_token = ? WHERE username = ? AND password = ?;
		`
		_, err := DB.Exec(sqlStatement, authToken, username, password)
		if err != nil {
			return "", err
		}
	}
	return authToken, nil
}

func getWebToken(username string, password string) (string, error) {
	sqlStatement := `
	SELECT web_token FROM users WHERE username = ? AND password = ?;
	`
	row := DB.QueryRow(sqlStatement, username, password)

	// get web token
	var webToken string
	err := row.Scan(&webToken)
	if err != nil {
		if err != sql.ErrNoRows {
			return "", err
		} else {
			return "", &NotFoundError{}
		}
	}
	if webToken == "" {
		// generate new webToken
		webToken = uuid.New().String()
		// update authToken
		sqlStatement := `
		UPDATE users SET web_token = ? WHERE username = ? AND password = ?;
		`
		_, err := DB.Exec(sqlStatement, webToken, username, password)
		if err != nil {
			return "", err
		}
	}
	return webToken, nil
}

// func checkClientToken(clientToken string, userName string) (string, error) {
// 	// assumes user is already logged in
// 	sqlStatement := `
// 	SELECT id FROM users WHERE client_token = ? AND username = ?;
// 	`
// 	var x string
// 	err := DB.QueryRow(sqlStatement, clientToken, userName).Scan(&x)

// 	// check if row exists
// 	if err != nil && err != sql.ErrNoRows {
// 		return "", err
// 	}
// 	if err == nil {
// 		return clientToken, nil
// 	} else {
// 		clientToken = uuid.New().String()
// 		sqlStatement := `
// 		UPDATE users SET client_token = ? WHERE username = ?;
// 		`
// 		_, err := DB.Exec(sqlStatement, clientToken, userName)
// 		if err != nil {
// 			return "", err
// 		}
// 		clearAuthToken(userName)
// 		return clientToken, nil
// 	}
// }

func checkClientToken(clientToken string, userName string) (string, error) {
	// actually just stores the token instead of checking if it's valid
	sqlStatement := `
	UPDATE users SET client_token = ? WHERE username = ?;
	`
	_, err := DB.Exec(sqlStatement, clientToken, userName)
	if err != nil {
		return "", err
	}
	return clientToken, nil
}

// func clearAuthToken(username string) error {
// 	// runs when user logs out
// 	sqlStatement := `
// 	UPDATE users SET auth_token = ? WHERE username = ?;
// 	`
// 	_, err := DB.Exec(sqlStatement, "", username)
// 	if err != nil {
// 		return err
// 	}
// 	return nil
// }

// func insertAuthToken(authToken string, userName string) error {
// 	sqlStatement := `
// 	UPDATE users SET auth_token = ? WHERE username = ?;
// 	`
// 	_, err := DB.Exec(sqlStatement, authToken, userName)
// 	if err != nil {
// 		return err
// 	}
// 	return nil
// }

func resetTokens(username string, password string) error {
	sqlStatement := `
	UPDATE users SET web_token = "", auth_token = "" WHERE username = ? AND password = ?;
	`
	_, err := DB.Exec(sqlStatement, username, password)
	if err != nil {
		return err
	}
	return nil
}

func createUser(username string, adminToken string) (string, error) {
	// check if adminToken is valid
	if validateAdminToken(adminToken) {
		exists, err := playerExistsByUsername(username)
		if err != nil {
			return "", err
		}
		if !exists {
			// shrunk so it fits into Auth Me login
			password := shrinkUUID(uuid.New().String())
			insertUser(username, password)
			return password, nil
		} else {
			return "", &AlreadyExistsError{}
		}
	} else {
		return "", &InvalidCredentialsError{}
	}
}

func refreshTokens(refresh RefreshPayload) (RefreshPayload, error) {
	sqlStatement := `
	SELECT id FROM users WHERE auth_token = ? and client_token = ?;
	`
	row := DB.QueryRow(sqlStatement, refresh.AccessToken, refresh.ClientToken)

	// get id
	var id int
	err := row.Scan(&id)

	if err != nil {
		return RefreshPayload{}, err
	}
	// generate new authToken
	authToken := uuid.New().String()
	// update authToken
	sqlStatement = `
	UPDATE users SET auth_token = ? WHERE id = ?;
	`
	_, err = DB.Exec(sqlStatement, authToken, id)
	if err != nil {
		return RefreshPayload{}, err
	}
	// generate new clientToken
	clientToken := uuid.New().String()
	// update clientToken
	sqlStatement = `
	UPDATE users SET client_token = ? WHERE id = ?;
	`
	_, err = DB.Exec(sqlStatement, clientToken, id)
	if err != nil {
		return RefreshPayload{}, err
	}
	refresh.AccessToken = authToken
	refresh.ClientToken = clientToken
	return refresh, nil
}

func validateTokens(authToken string, clientToken string) (bool, error) {
	sqlStatement := `
	SELECT id FROM users WHERE auth_token = ? and client_token = ?;
	`
	var x string
	err := DB.QueryRow(sqlStatement, authToken, clientToken).Scan(&x)
	if err != nil {
		if err == sql.ErrNoRows {
			return false, nil
		}
		return false, err
	}
	return true, nil
}

func invalidateTokens(authToken string, clientToken string) error {
	sqlStatement := `
	UPDATE users SET auth_token = ?, client_token = ? WHERE auth_token = ? and client_token = ?;
	`
	_, err := DB.Exec(sqlStatement, "", "", authToken, clientToken)
	if err != nil {
		return err
	}
	return nil
}

func invalidateTokensWithLogin(username string, password string) error {
	sqlStatement := `
	UPDATE users SET auth_token = ?, client_token = ? WHERE username = ? AND password = ?;
	`
	_, err := DB.Exec(sqlStatement, "", "", username, password)
	if err != nil {
		return err
	}
	return nil
}