beerpong-elo/internal/repo/sqlRepo.go

package repo

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

	"git.kapelle.org/niklas/beerpong-elo/internal/model"
	_ "github.com/go-sql-driver/mysql"
)

type SQLRepo struct {
	db *sql.DB
}

func NewSQLRepo() (Repo, error) {
	db, err := sql.Open("mysql", "root:hunter2@/beer?parseTime=true")
	if err != nil {
		return nil, err
	}

	return &SQLRepo{
		db: db,
	}, nil
}

func (s *SQLRepo) AddGame(game model.Game) (model.GameID, error) {
	stmt, err := s.db.Prepare("INSERT INTO Games(added,score,overtime,author,team0player0,team0player1,team1player0,team1player1) VALUES (?,?,?,?,?,?,?,?)")
	if err != nil {
		return "0", err
	}

	res, err := stmt.Exec(game.Added, game.Score, game.Overtime, game.Author, game.Team0Player0, game.Team0Player1, game.Team1Player0, game.Team1Player1)
	if err != nil {
		return "", err
	}

	id, err := res.LastInsertId()
	if err != nil {
		return "", err
	}

	return model.GameID(int64ToString(id)), nil
}

func (s *SQLRepo) AddGameResult(gameResult model.GameResult) (model.GameResultID, error) {
  stms, err := s.db.Prepare("INSERT INTO GameResults(game,player,startElo,endElo) VALUES (?,?,?,?)")
  if err != nil {
    return "", err
  }

  res,err := stms.Exec(gameResult.Game,gameResult.Player,gameResult.StartElo,gameResult.EndElo)

  if err != nil {
    return "", err
  }
	
  id, err := res.LastInsertId()
	if err != nil {
		return "", err
	}

  return model.GameResultID(int64ToString(id)),nil

}

func (s *SQLRepo) GetAllPlayers() ([]model.Player, error) {
	panic("unimplemented")
}

func (s *SQLRepo) GetGame(id model.GameID) (*model.Game, error) {
	rows := s.db.QueryRow("SELECT id,added, score,overtime,author,team0player0,team0player1,team1player0,team1player1 FROM Games WHERE id = ? ", id)

	var foundID int64
	var foundAdded time.Time
	var foundScore int64
	var foundOvertime bool
	var foundAuthor int64

	var foundT0P0 int64
	var foundT0P1 int64
	var foundT1P0 int64
	var foundT1P1 int64

	if err := rows.Scan(&foundID, &foundAdded, &foundScore, &foundOvertime, &foundAuthor, &foundT0P0, &foundT0P1, &foundT1P0, &foundT1P1); err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}

	game := model.Game{
		ID:       model.GameID(int64ToString(foundID)),
		Added:    foundAdded,
		Score:    int(foundScore),
		Overtime: foundOvertime,
		Author:   model.PlayerID(int64ToString(foundAuthor)),

		Team0Player0: model.PlayerID(int64ToString(foundT0P0)),
		Team0Player1: model.PlayerID(int64ToString(foundT0P1)),
		Team1Player0: model.PlayerID(int64ToString(foundT1P0)),
		Team1Player1: model.PlayerID(int64ToString(foundT1P1)),
	}

	return &game, nil
}

func (s *SQLRepo) GetGameResult(model.GameResultID) (*model.GameResult, error) {
	panic("unimplemented")
}

func (s *SQLRepo) PlayerWithNameExists(name string) (*model.PlayerID, error) {
	rows := s.db.QueryRow("SELECT id FROM Players WHERE name = ?", name)

	var id int64

	if err := rows.Scan(&id); err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}

	sID := int64ToString(id)

	return (*model.PlayerID)(&sID), nil
}

func (s *SQLRepo) GetOrCreatePlayerID(name string) (model.PlayerID, error) {

	foundID, err := s.PlayerWithNameExists(name)
	if err != nil {
		return "", err
	}

	if foundID != nil {
		return *foundID, nil
	}

	stmt, err := s.db.Prepare("INSERT INTO Players(name,elo) VALUES (?,?)")
	if err != nil {
		return "", err
	}

  newPlayer := model.NewPlayer(name) 

	res, err := stmt.Exec(newPlayer.Name,newPlayer.Elo)
	if err != nil {
		return "", err
	}

	id, err := res.LastInsertId()
	if err != nil {
		return "", err
	}

	return model.PlayerID(strconv.Itoa(int(id))), nil
}

func (s *SQLRepo) GetPlayer(id model.PlayerID) (*model.Player, error) {
	rows := s.db.QueryRow("SELECT id,name,elo from Players WHERE id = ?", id)

	var playerID int64
	var playerName string
  var playerElo int64

	if err := rows.Scan(&playerID, &playerName, &playerElo); err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}

	return &model.Player{
		ID: model.PlayerID(strconv.Itoa(int(playerID))),
    Name: playerName,
    Elo: int(playerElo),
	}, nil
}

func int64ToString(i int64) string {
	return strconv.Itoa(int(i))
}