YetAnotherToDoList/database/user.go

/*
YetAnotherToDoList
Copyright © 2023 gilex-dev gilex-dev@proton.me

This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, version 3.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program. If not, see <http://www.gnu.org/licenses/>.
*/
package database

import (
	"database/sql"
	"errors"
	"strconv"
	"time"

	"somepi.ddns.net/gitea/gilex-dev/YetAnotherToDoList/graph/model"
)

func (db CustomDB) CreateUser(newUser model.NewUser) (*model.User, error) {
	statement, err := db.connection.Prepare("INSERT INTO User (userName, fullName, passwordHash) VALUES (?, NULLIF(?, ''), ?)")
	if err != nil {
		return nil, err
	}

	if ValidateUserName(newUser.UserName); err != nil {
		return nil, err
	}

	if ValidatePassword(newUser.Password); err != nil {
		return nil, err
	}

	passwordHash, err := db.GenerateHashFromPassword(newUser.Password)
	if err != nil {
		return nil, err
	}

	rows, err := statement.Exec(newUser.UserName, newUser.FullName, string(passwordHash))
	if err != nil {
		return nil, err
	}

	num, err := rows.RowsAffected()
	if err != nil {
		return nil, err
	}

	if num < 1 {
		return nil, errors.New("no rows affected")
	}

	insertId, err := rows.LastInsertId()
	if err != nil {
		return nil, err
	}
	return &model.User{ID: strconv.FormatInt(insertId, 10), UserName: newUser.UserName, FullName: newUser.FullName}, nil
}

// GetUser takes a *model.User with at least ID or UserName set and adds the missing fields.
func (db CustomDB) GetUser(user *model.User) (*model.User, error) {
	numUserId, err := strconv.Atoi(user.ID)
	if err != nil {
		return nil, errors.New("malformed userId")
	}
	statement, err := db.connection.Prepare("SELECT userID, userName, fullName FROM User WHERE userId = ? OR userName = ?")
	if err != nil {
		return nil, err
	}

	result := statement.QueryRow(numUserId, user.UserName)
	if err := result.Scan(&user.ID, &user.UserName, &user.FullName); err != nil {
		if err == sql.ErrNoRows {
			return nil, errors.New("invalid user Id")
		}
		return nil, err
	}

	return user, nil
}

func (db CustomDB) GetAllUsers() ([]*model.User, error) {
	rows, err := db.connection.Query("SELECT userId, userName, fullName FROM User")
	if err != nil {
		return nil, err
	}

	defer rows.Close()

	var all []*model.User
	for rows.Next() {
		var user model.User
		if err := rows.Scan(&user.ID, &user.UserName, &user.FullName); err != nil {
			return nil, err
		}
		all = append(all, &user)
	}
	return all, nil
}

func (db CustomDB) UpdateUser(userId string, changes *model.UpdateUser) (*model.User, error) {
	var passwordHash *string
	needAccessTokenRefresh := false

	id, err := strconv.Atoi(userId)
	if err != nil {
		return nil, errors.New("malformed userId")
	}

	statement, err := db.connection.Prepare("UPDATE User SET userName = IFNULL(?, userName), fullName = NULLIF(?, ''), passwordHash = IFNULL(?, passwordHash) WHERE userId = ?")
	if err != nil {
		return nil, err
	}

	if changes.UserName != nil && *changes.UserName != "" { // ignore empty string
		if err := ValidateUserName(*changes.UserName); err != nil {
			return nil, err
		}
		needAccessTokenRefresh = true
	}

	if changes.Password == nil { // interpret empty string as nil
		passwordHash = nil
	} else {
		if err := ValidatePassword(*changes.Password); err != nil {
			return nil, err
		}
		passwordHashByte, err := db.GenerateHashFromPassword(*changes.Password)
		if err != nil {
			return nil, err
		}
		passwordHashString := string(passwordHashByte)
		passwordHash = &passwordHashString
		needAccessTokenRefresh = true
	}

	rows, err := statement.Exec(changes.UserName, changes.FullName, passwordHash, id)
	if err != nil {
		return nil, err
	}

	num, err := rows.RowsAffected()
	if err != nil {
		return nil, err
	}

	if num < 1 {
		return nil, errors.New("no rows affected")
	}

	if needAccessTokenRefresh {
		RevokeAccessToken(&AccessToken{UserId: userId, ExpiryDate: int(time.Now().Add(accessTokenLifetime).Unix())})
	}

	return db.GetUser(&model.User{ID: userId})
}

func (db CustomDB) DeleteUser(userId string) (*string, error) {
	statement, err := db.connection.Prepare("DELETE FROM User WHERE userId = ?")
	if err != nil {
		return nil, err
	}

	rows, err := statement.Exec(userId)
	if err != nil {
		return nil, err
	}

	num, err := rows.RowsAffected()
	if err != nil {
		return nil, err
	}

	if num < 1 {
		return nil, errors.New("no rows affected")
	}

	RevokeAccessToken(&AccessToken{UserId: userId, ExpiryDate: int(time.Now().Add(accessTokenLifetime).Unix())})
	return &userId, nil
}

func (db CustomDB) AddUserRole(userId string, roleId string, isRoleManager bool) (relationId string, err error) {
	encUserId, err := strconv.Atoi(userId)
	if err != nil {
		return "", errors.New("malformed userId")
	}
	encRoleId, err := strconv.Atoi(roleId)
	if err != nil {
		return "", errors.New("invalid roleId")
	}

	statement, err := db.connection.Prepare("INSERT INTO R_User_Role (FK_User_userId, FK_Role_roleId, IS_roleManager) VALUES (?, ?, ?)")
	if err != nil {
		return "", err
	}

	rows, err := statement.Exec(encUserId, encRoleId, isRoleManager)
	if err != nil {
		return "", err
	}

	num, err := rows.RowsAffected()
	if err != nil {
		return "", err
	}

	if num < 1 {
		return "", errors.New("no rows affected")
	}

	insertId, err := rows.LastInsertId()
	if err != nil {
		return "", err
	}

	RevokeAccessToken(&AccessToken{UserId: userId, ExpiryDate: int(time.Now().Add(accessTokenLifetime).Unix())})
	return strconv.FormatInt(insertId, 10), nil
}

func (db CustomDB) UpdateUserRole(userId string, roleId string, isRoleManager bool) (relationId string, err error) {
	encUserId, err := strconv.Atoi(userId)
	if err != nil {
		return "", errors.New("malformed userId")
	}
	encRoleId, err := strconv.Atoi(roleId)
	if err != nil {
		return "", errors.New("invalid roleId")
	}

	statement, err := db.connection.Prepare("UPDATE R_User_Role SET IS_roleManager = ? WHERE FK_User_userId = ? AND FK_Role_roleId = ?")
	if err != nil {
		return "", err
	}

	rows, err := statement.Exec(isRoleManager, encUserId, encRoleId)
	if err != nil {
		return "", err
	}

	num, err := rows.RowsAffected()
	if err != nil {
		return "", err
	}

	if num < 1 {
		return "", errors.New("no rows affected")
	}

	insertId, err := rows.LastInsertId()
	if err != nil {
		return "", err
	}

	RevokeAccessToken(&AccessToken{UserId: userId, ExpiryDate: int(time.Now().Add(accessTokenLifetime).Unix())})
	return strconv.FormatInt(insertId, 10), nil
}

func (db CustomDB) RemoveUserRole(userId string, roleId string) (relationId string, err error) {
	encUserId, err := strconv.Atoi(userId)
	if err != nil {
		return "", errors.New("malformed userId")
	}
	encRoleId, err := strconv.Atoi(roleId)
	if err != nil {
		return "", errors.New("invalid roleId")
	}

	statement, err := db.connection.Prepare("DELETE FROM R_User_Role WHERE FK_User_userId = ? AND FK_Role_roleId = ?")
	if err != nil {
		return "", err
	}

	rows, err := statement.Exec(encUserId, encRoleId)
	if err != nil {
		return "", err
	}

	num, err := rows.RowsAffected()
	if err != nil {
		return "", err
	}

	if num < 1 {
		return "", errors.New("no rows affected")
	}

	RevokeAccessToken(&AccessToken{UserId: userId, ExpiryDate: int(time.Now().Add(accessTokenLifetime).Unix())})
	return strconv.FormatInt(int64(encRoleId), 10), nil
}

func (db CustomDB) GetUserPermissions(userId string) (isAdmin bool, isUserCreator bool, err error) {
	numUserId, err := strconv.Atoi(userId)
	if err != nil {
		return false, false, errors.New("malformed userId")
	}
	statement, err := db.connection.Prepare("SELECT Role.IS_admin, Role.IS_userCreator FROM R_User_Role INNER JOIN Role ON R_User_Role.FK_Role_roleId = Role.roleId WHERE R_User_Role.FK_User_userId = ?")
	if err != nil {
		return false, false, err
	}

	rows, err := statement.Query(numUserId)
	if err != nil {
		return false, false, err
	}

	defer rows.Close()

	var admin, userCreator bool
	for rows.Next() {
		var gotAdmin, gotUserCreator bool
		if err := rows.Scan(&gotAdmin, &gotUserCreator); err != nil {
			return false, false, err
		}
		if gotAdmin {
			admin = true
		}
		if gotUserCreator {
			userCreator = true
		}
	}
	return admin, userCreator, nil
}