matrix-bot/persistent/database.go

package database

import (
	"database/sql"
	"fmt"
	"strings"

	_ "modernc.org/sqlite"
)

type DB struct {
	conn *sql.DB
}

type UserStats struct {
	UserID      string
	Wins        int
	Losses      int
	GamesPlayed int
}

// InitDB opens the database and creates tables if needed
func InitDB(path string) (*DB, error) {
	conn, err := sql.Open("sqlite", path)
	if err != nil {
		return nil, err
	}

	createTable := `
	CREATE TABLE IF NOT EXISTS user_scores (
		user_id TEXT PRIMARY KEY,
		wins INTEGER NOT NULL DEFAULT 0,
		losses INTEGER NOT NULL DEFAULT 0,
		games_played INTEGER NOT NULL DEFAULT 0
	);
	`

	_, err = conn.Exec(createTable)
	if err != nil {
		return nil, err
	}

	return &DB{
		conn: conn,
	}, nil
}

// ensureUser creates the user row if it doesn't exist
func (db *DB) ensureUser(userID string) error {
	query := `
	INSERT INTO user_scores (user_id)
	VALUES (?)
	ON CONFLICT(user_id) DO NOTHING;
	`

	_, err := db.conn.Exec(query, userID)
	return err
}

// IncrementWins increments wins and games played
func (db *DB) IncrementWins(userID string) error {
	if err := db.ensureUser(userID); err != nil {
		return err
	}

	query := `
	UPDATE user_scores
	SET wins = wins + 1,
		games_played = games_played + 1
	WHERE user_id = ?;
	`

	_, err := db.conn.Exec(query, userID)
	return err
}

// IncrementLosses increments losses and games played
func (db *DB) IncrementLosses(userID string) error {
	if err := db.ensureUser(userID); err != nil {
		return err
	}

	query := `
	UPDATE user_scores
	SET losses = losses + 1,
		games_played = games_played + 1
	WHERE user_id = ?;
	`

	_, err := db.conn.Exec(query, userID)
	return err
}

// IncrementGamesPlayed increments only games played
func (db *DB) IncrementGamesPlayed(userID string) error {
	if err := db.ensureUser(userID); err != nil {
		return err
	}

	query := `
	UPDATE user_scores
	SET games_played = games_played + 1
	WHERE user_id = ?;
	`

	_, err := db.conn.Exec(query, userID)
	return err
}

// GetUserStats returns stats for a user
func (db *DB) GetUserStats(userID string) (*UserStats, error) {
	if err := db.ensureUser(userID); err != nil {
		return nil, err
	}

	query := `
	SELECT user_id, wins, losses, games_played
	FROM user_scores
	WHERE user_id = ?;
	`

	row := db.conn.QueryRow(query, userID)

	stats := &UserStats{}

	err := row.Scan(
		&stats.UserID,
		&stats.Wins,
		&stats.Losses,
		&stats.GamesPlayed,
	)

	if err != nil {
		return nil, err
	}

	return stats, nil
}
func (db *DB) FormatLeaderboard() string {
	var b strings.Builder

	users, err := db.GetUsersStats()
	if err != nil {
		return "failed to load leaderboard"
	}

	b.WriteString("🏆 WORDLE LEADERBOARD 🏆\n\n")

	for i, u := range users {
		rank := fmt.Sprintf("#%d", i+1)

		switch i {
		case 0:
			rank = "🥇"
		case 1:
			rank = "🥈"
		case 2:
			rank = "🥉"
		}

		username := u.UserID

		row := fmt.Sprintf(
			"%s %s\nWins: %d | Losses: %d | Games: %d\n\n",
			rank,
			username,
			u.Wins,
			u.Losses,
			u.GamesPlayed,
		)

		b.WriteString(row)
	}

	return b.String()
}

func (db *DB) GetUsersStats() ([]UserStats, error) {
	query := `
	SELECT user_id, wins, losses, games_played
	FROM user_scores
	ORDER BY wins DESC;
	`

	rows, err := db.conn.Query(query)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var users []UserStats

	for rows.Next() {
		var user UserStats

		err := rows.Scan(
			&user.UserID,
			&user.Wins,
			&user.Losses,
			&user.GamesPlayed,
		)
		if err != nil {
			return nil, err
		}

		users = append(users, user)
	}

	if err := rows.Err(); err != nil {
		return nil, err
	}

	return users, nil
}

// Close closes the database connection
func (db *DB) Close() error {
	return db.conn.Close()
}

// Optional helper to print stats
func (s UserStats) String() string {
	return fmt.Sprintf(
		"User: %s | Wins: %d | Losses: %d | Games: %d",
		s.UserID,
		s.Wins,
		s.Losses,
		s.GamesPlayed,
	)
}