264 lines
8.2 KiB
Go
264 lines
8.2 KiB
Go
package repos
|
|
|
|
import (
|
|
"context"
|
|
"gralias/models"
|
|
"testing"
|
|
|
|
"github.com/jmoiron/sqlx"
|
|
_ "github.com/mattn/go-sqlite3"
|
|
"github.com/stretchr/testify/assert"
|
|
)
|
|
|
|
func setupPlayersTestDB(t *testing.T) (*sqlx.DB, func()) {
|
|
db, err := sqlx.Connect("sqlite3", ":memory:")
|
|
assert.NoError(t, err)
|
|
|
|
// Load schema from migration files
|
|
schema001 := `
|
|
-- migrations/001_initial_schema.up.sql
|
|
|
|
CREATE TABLE rooms (
|
|
id TEXT PRIMARY KEY,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
creator_name TEXT NOT NULL,
|
|
team_turn TEXT NOT NULL DEFAULT '',
|
|
this_turn_limit INTEGER NOT NULL DEFAULT 0,
|
|
opened_this_turn INTEGER NOT NULL DEFAULT 0,
|
|
blue_counter INTEGER NOT NULL DEFAULT 0,
|
|
red_counter INTEGER NOT NULL DEFAULT 0,
|
|
red_turn BOOLEAN NOT NULL DEFAULT FALSE,
|
|
mime_done BOOLEAN NOT NULL DEFAULT FALSE,
|
|
is_running BOOLEAN NOT NULL DEFAULT FALSE,
|
|
is_over BOOLEAN NOT NULL DEFAULT FALSE,
|
|
team_won TEXT NOT NULL DEFAULT '',
|
|
room_link TEXT NOT NULL DEFAULT ''
|
|
);
|
|
|
|
CREATE TABLE players (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
room_id TEXT, -- nullable
|
|
username TEXT NOT NULL UNIQUE,
|
|
password TEXT NOT NULL DEFAULT '',
|
|
team TEXT NOT NULL DEFAULT '', -- 'red' or 'blue'
|
|
role TEXT NOT NULL DEFAULT '', -- 'guesser' or 'mime'
|
|
is_bot BOOLEAN NOT NULL DEFAULT FALSE,
|
|
FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE word_cards (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
room_id TEXT NOT NULL,
|
|
word TEXT NOT NULL,
|
|
color TEXT NOT NULL DEFAULT '',
|
|
revealed BOOLEAN NOT NULL DEFAULT FALSE,
|
|
mime_view BOOLEAN NOT NULL DEFAULT FALSE,
|
|
FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE card_marks (
|
|
card_id INTEGER NOT NULL,
|
|
username TEXT NOT NULL,
|
|
FOREIGN KEY (card_id) REFERENCES word_cards(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (username) REFERENCES players(username) ON DELETE CASCADE,
|
|
PRIMARY KEY (card_id, username)
|
|
);
|
|
|
|
CREATE TABLE actions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
room_id TEXT NOT NULL,
|
|
actor TEXT NOT NULL,
|
|
actor_color TEXT NOT NULL DEFAULT '',
|
|
action_type TEXT NOT NULL,
|
|
word TEXT NOT NULL DEFAULT '',
|
|
word_color TEXT NOT NULL DEFAULT '',
|
|
number_associated TEXT NOT NULL DEFAULT '', -- for clues
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE settings (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
room_id TEXT NOT NULL,
|
|
language TEXT NOT NULL DEFAULT 'en',
|
|
room_pass TEXT NOT NULL DEFAULT '',
|
|
turn_time INTEGER NOT NULL DEFAULT 60, -- seconds
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE sessions(
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
lifetime INTEGER NOT NULL DEFAULT 3600,
|
|
token_key TEXT NOT NULL DEFAULT '' UNIQUE, -- encoded value
|
|
username TEXT NOT NULL,
|
|
FOREIGN KEY (username) REFERENCES players(username) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE journal(
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
entry TEXT NOT NULL DEFAULT '',
|
|
username TEXT NOT NULL,
|
|
room_id TEXT NOT NULL,
|
|
FOREIGN KEY (username) REFERENCES players(username) ON DELETE CASCADE,
|
|
FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE
|
|
);
|
|
|
|
CREATE TABLE player_stats (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT NOT NULL UNIQUE,
|
|
games_played INTEGER NOT NULL DEFAULT 0,
|
|
games_won INTEGER NOT NULL DEFAULT 0,
|
|
games_lost INTEGER NOT NULL DEFAULT 0,
|
|
opened_opposite_words INTEGER NOT NULL DEFAULT 0,
|
|
opened_white_words INTEGER NOT NULL DEFAULT 0,
|
|
opened_black_words INTEGER NOT NULL DEFAULT 0,
|
|
mime_winrate REAL NOT NULL DEFAULT 0.0,
|
|
guesser_winrate REAL NOT NULL DEFAULT 0.0,
|
|
played_as_mime INTEGER NOT NULL DEFAULT 0,
|
|
played_as_guesser INTEGER NOT NULL DEFAULT 0,
|
|
FOREIGN KEY (username) REFERENCES players(username) ON DELETE CASCADE
|
|
);
|
|
`
|
|
_, err = db.Exec(schema001)
|
|
assert.NoError(t, err)
|
|
|
|
schema002 := `
|
|
ALTER TABLE player_stats
|
|
ADD COLUMN rating REAL NOT NULL DEFAULT 1000.0;
|
|
|
|
CREATE TRIGGER update_player_rating
|
|
AFTER UPDATE OF games_played, games_won ON player_stats
|
|
WHEN NEW.games_played = OLD.games_played + 1
|
|
BEGIN
|
|
UPDATE player_stats
|
|
SET rating = OLD.rating +
|
|
32.0 * (
|
|
CASE
|
|
WHEN NEW.games_won = OLD.games_won + 1
|
|
THEN 1.0 - 0.5 -- Win term: 0.5
|
|
ELSE 0.0 - 0.5 -- Loss term: -0.5
|
|
END
|
|
) +
|
|
0.05 * (1000.0 - OLD.rating)
|
|
WHERE id = OLD.id;
|
|
END;
|
|
`
|
|
_, err = db.Exec(schema002)
|
|
assert.NoError(t, err)
|
|
|
|
return db, func() {
|
|
db.Close()
|
|
}
|
|
}
|
|
|
|
func TestPlayerStatsRatingUpdate(t *testing.T) {
|
|
db, teardown := setupPlayersTestDB(t)
|
|
defer teardown()
|
|
|
|
username := "test_player_rating"
|
|
_, err := db.Exec(`INSERT INTO players (username) VALUES (?)`, username)
|
|
assert.NoError(t, err)
|
|
|
|
_, err = db.Exec(`INSERT INTO player_stats (username, games_played, games_won, rating) VALUES (?, 0, 0, 1000.0)`, username)
|
|
assert.NoError(t, err)
|
|
|
|
// Simulate a win
|
|
_, err = db.Exec(`UPDATE player_stats SET games_played = 1, games_won = 1 WHERE username = ?`, username)
|
|
assert.NoError(t, err)
|
|
|
|
var ratingAfterWin float64
|
|
err = db.Get(&ratingAfterWin, `SELECT rating FROM player_stats WHERE username = ?`, username)
|
|
assert.NoError(t, err)
|
|
// Expected: 1000 + 32 * (1 - 0.5) + 0.05 * (1000 - 1000) = 1000 + 16 = 1016
|
|
assert.InDelta(t, 1016.0, ratingAfterWin, 0.001)
|
|
|
|
// Simulate a loss
|
|
_, err = db.Exec(`UPDATE player_stats SET games_played = 2, games_won = 1 WHERE username = ?`, username)
|
|
assert.NoError(t, err)
|
|
|
|
var ratingAfterLoss float64
|
|
err = db.Get(&ratingAfterLoss, `SELECT rating FROM player_stats WHERE username = ?`, username)
|
|
assert.NoError(t, err)
|
|
// Expected: 1016 + 32 * (0 - 0.5) + 0.05 * (1000 - 1016) = 1016 - 16 + 0.05 * (-16) = 1000 - 0.8 = 999.2
|
|
assert.InDelta(t, 999.2, ratingAfterLoss, 0.001)
|
|
}
|
|
|
|
|
|
func TestPlayersRepo_AddPlayer(t *testing.T) {
|
|
db, teardown := setupPlayersTestDB(t)
|
|
defer teardown()
|
|
|
|
repo := &RepoProvider{DB: db}
|
|
|
|
roomID := "test_room_player_1"
|
|
player := &models.Player{
|
|
RoomID: &roomID,
|
|
Username: "test_player_1",
|
|
Team: "blue",
|
|
Role: "player",
|
|
IsBot: false,
|
|
}
|
|
|
|
err := repo.PlayerAdd(context.Background(), player)
|
|
assert.NoError(t, err)
|
|
|
|
var retrievedPlayer models.Player
|
|
err = db.Get(&retrievedPlayer, "SELECT * FROM players WHERE room_id = ? AND username = ?", player.RoomID, player.Username)
|
|
assert.NoError(t, err)
|
|
assert.Equal(t, player.Username, retrievedPlayer.Username)
|
|
}
|
|
|
|
func TestPlayersRepo_GetPlayer(t *testing.T) {
|
|
db, teardown := setupPlayersTestDB(t)
|
|
defer teardown()
|
|
|
|
repo := &RepoProvider{DB: db}
|
|
|
|
roomID := "test_room_player_2"
|
|
player := &models.Player{
|
|
RoomID: &roomID,
|
|
Username: "test_player_2",
|
|
Team: "red",
|
|
Role: "player",
|
|
IsBot: false,
|
|
}
|
|
|
|
_, err := db.Exec(`INSERT INTO players (room_id, username, team, role, is_bot) VALUES (?, ?, ?, ?, ?)`, player.RoomID, player.Username, player.Team, player.Role, player.IsBot)
|
|
assert.NoError(t, err)
|
|
|
|
retrievedPlayer, err := repo.PlayerGetByName(context.Background(), player.Username)
|
|
assert.NoError(t, err)
|
|
assert.NotNil(t, retrievedPlayer)
|
|
assert.Equal(t, player.Username, retrievedPlayer.Username)
|
|
}
|
|
|
|
func TestPlayersRepo_DeletePlayer(t *testing.T) {
|
|
db, teardown := setupPlayersTestDB(t)
|
|
defer teardown()
|
|
|
|
repo := &RepoProvider{DB: db}
|
|
|
|
roomID := "test_room_player_3"
|
|
player := &models.Player{
|
|
RoomID: &roomID,
|
|
Username: "test_player_3",
|
|
Team: "blue",
|
|
Role: "player",
|
|
IsBot: false,
|
|
}
|
|
|
|
_, err := db.Exec(`INSERT INTO players (room_id, username, team, role, is_bot) VALUES (?, ?, ?, ?, ?)`, player.RoomID, player.Username, player.Team, player.Role, player.IsBot)
|
|
assert.NoError(t, err)
|
|
|
|
err = repo.PlayerDelete(context.Background(), player.Username)
|
|
assert.NoError(t, err)
|
|
|
|
var count int
|
|
err = db.Get(&count, "SELECT COUNT(*) FROM players WHERE room_id = ? AND username = ?", player.RoomID, player.Username)
|
|
assert.NoError(t, err)
|
|
assert.Equal(t, 0, count)
|
|
}
|