sprintpadawan/lib/room.go

package lib

import (
	"fmt"
	"strings"
	"time"

	"github.com/google/uuid"
)

type Room struct {
	ID            int
	Name          string
	Code          string
	Scale         string
	OwnerID       int
	CreatedAt     time.Time
	ActiveStoryID *int
}

type RoomMember struct {
	RoomID   int
	UserID   int
	JoinedAt time.Time
}

type RoomSummary struct {
	Room
	MemberCount int
}

// generate unique room code
func generateRoomCode() string {
	code := uuid.New().String()[:8]
	return code
}

// create room
func CreateRoom(name string, ownerID int, scale string) (*Room, error) {
	code := generateRoomCode()

	tx, err := DB.Begin()
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()

	createdAt := time.Now()
	res, err := tx.Exec("INSERT INTO rooms (name, code, scale, owner_id, created_at, active_story_id) VALUES (?, ?, ?, ?, ?, NULL)", name, code, scale, ownerID, createdAt)
	if err != nil {
		return nil, err
	}

	roomID64, err := res.LastInsertId()
	if err != nil {
		return nil, err
	}
	roomID := int(roomID64)

	if _, err := tx.Exec("INSERT INTO room_members (room_id, user_id, joined_at) VALUES (?, ?, ?)", roomID, ownerID, createdAt); err != nil {
		return nil, err
	}

	if err := tx.Commit(); err != nil {
		return nil, err
	}

	return &Room{
		ID:            roomID,
		Name:          name,
		Code:          code,
		Scale:         scale,
		OwnerID:       ownerID,
		CreatedAt:     createdAt,
		ActiveStoryID: nil,
	}, nil
}

// get room by id
func GetRoomByID(id int) (*Room, error) {
	row := DB.QueryRow("SELECT id, name, code, scale, owner_id, created_at, active_story_id FROM rooms WHERE id = ?", id)
	r := &Room{}
	err := row.Scan(&r.ID, &r.Name, &r.Code, &r.Scale, &r.OwnerID, &r.CreatedAt, &r.ActiveStoryID)
	if err != nil {
		return nil, err
	}
	return r, nil
}

// get room by code
func GetRoomByCode(code string) (*Room, error) {
	row := DB.QueryRow("SELECT id, name, code, scale, owner_id, created_at, active_story_id FROM rooms WHERE code = ?", code)
	r := &Room{}
	err := row.Scan(&r.ID, &r.Name, &r.Code, &r.Scale, &r.OwnerID, &r.CreatedAt, &r.ActiveStoryID)
	if err != nil {
		return nil, err
	}
	return r, nil
}

// get rooms for user
func GetRoomsForUser(userID int) ([]Room, error) {
	summaries, err := GetRoomSummariesForUser(userID)
	if err != nil {
		return nil, err
	}
	rooms := make([]Room, 0, len(summaries))
	for _, s := range summaries {
		rooms = append(rooms, s.Room)
	}
	return rooms, nil
}

func GetRoomSummariesForUser(userID int) ([]RoomSummary, error) {
	rows, err := DB.Query(`
		SELECT r.id, r.name, r.code, r.scale, r.owner_id, r.created_at, r.active_story_id, COUNT(rm.user_id) as member_count
		FROM rooms r
		LEFT JOIN room_members rm ON r.id = rm.room_id
		WHERE r.owner_id = ?
		GROUP BY r.id
	`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var rooms []RoomSummary
	for rows.Next() {
		var r RoomSummary
		var memberCount int
		err := rows.Scan(&r.ID, &r.Name, &r.Code, &r.Scale, &r.OwnerID, &r.CreatedAt, &r.ActiveStoryID, &memberCount)
		if err != nil {
			return nil, err
		}
		r.MemberCount = memberCount
		rooms = append(rooms, r)
	}
	return rooms, nil
}

// set active story for room
func SetActiveStory(roomID, storyID int) error {
	_, err := DB.Exec("UPDATE rooms SET active_story_id = ? WHERE id = ?", storyID, roomID)
	return err
}

// add user to room
func AddUserToRoom(roomID, userID int) error {
	_, err := DB.Exec("INSERT OR IGNORE INTO room_members (room_id, user_id, joined_at) VALUES (?, ?, ?)", roomID, userID, time.Now())
	return err
}

// get room members
func GetRoomMembers(roomID int) ([]User, error) {
	rows, err := DB.Query(`
		SELECT u.id, u.username, u.password_hash
		FROM users u
		JOIN room_members rm ON u.id = rm.user_id
		WHERE rm.room_id = ?
	`, roomID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var members []User
	for rows.Next() {
		var u User
		err := rows.Scan(&u.ID, &u.Username, &u.PasswordHash)
		if err != nil {
			return nil, err
		}
		members = append(members, u)
	}
	return members, nil
}

// delete room by id
func DeleteRoom(id int) error {
	_, err := DB.Exec("DELETE FROM rooms WHERE id = ?", id)
	return err
}

func GetVotesForStories(storyIDs []int) (map[int][]Vote, error) {
	votesByStory := make(map[int][]Vote)
	if len(storyIDs) == 0 {
		return votesByStory, nil
	}

	placeholders := strings.TrimSuffix(strings.Repeat("?,", len(storyIDs)), ",")
	args := make([]interface{}, 0, len(storyIDs))
	for _, id := range storyIDs {
		args = append(args, id)
	}

	query := fmt.Sprintf("SELECT story_id, user_id, value FROM votes WHERE story_id IN (%s)", placeholders)
	rows, err := DB.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		var v Vote
		if err := rows.Scan(&v.StoryID, &v.UserID, &v.Value); err != nil {
			return nil, err
		}
		votesByStory[v.StoryID] = append(votesByStory[v.StoryID], v)
	}

	return votesByStory, nil
}

func GetVoteViewsForStories(storyIDs []int) (map[int][]VoteView, error) {
	voteViewsByStory := make(map[int][]VoteView)
	if len(storyIDs) == 0 {
		return voteViewsByStory, nil
	}

	placeholders := strings.TrimSuffix(strings.Repeat("?,", len(storyIDs)), ",")
	args := make([]interface{}, 0, len(storyIDs))
	for _, id := range storyIDs {
		args = append(args, id)
	}

	query := fmt.Sprintf(`
		SELECT v.story_id, u.username, v.value
		FROM votes v
		JOIN users u ON v.user_id = u.id
		WHERE v.story_id IN (%s)
	`, placeholders)
	rows, err := DB.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		var (
			storyID int
			vv      VoteView
		)
		if err := rows.Scan(&storyID, &vv.Username, &vv.Value); err != nil {
			return nil, err
		}
		voteViewsByStory[storyID] = append(voteViewsByStory[storyID], vv)
	}

	return voteViewsByStory, nil
}