val round = RoundEntity(
    roundName = "Competition Prep",
    numEnds = 6,
    numArrows = 6,
    distance = Distance.fromString("70 meters"),
    targetSize = TargetSize.fromString("122cm")
)

// Round created with BowSetup id=5, version=3
round.bowSetupId = 5L
round.bowSetupVersion = 3
 
// Later, if BowSetup changes to version 4, this round still references version 3
// Ensures historical accuracy

data class SessionParticipant(
    val id: String,           // Unique participant ID
    val name: String,         // Display name
    val isGuest: Boolean,     // True if guest archer
    val bowSetupId: Long?     // Optional equipment reference
)

val practiceRound = RoundEntity(
    id = 1L,
    roundName = "Morning Practice - 50m",
    numEnds = 6,
    numArrows = 6,
    distance = Distance.fromString("50 meters"),
    targetSize = TargetSize.fromString("122cm"),
    scoringSystem = ScoringSystem.TEN_RING,
    bowSetupId = 5L,
    bowSetupVersion = 3,
    status = RoundStatus.IN_PROGRESS,
    participants = listOf(
        SessionParticipant(
            id = "user123",
            name = "Chris",
            isGuest = false,
            bowSetupId = 5L
        )
    ),
    participantTheme = ParticipantTheme.SINGLE_USER,
    tournamentId = null,
    isLocal = true,
    createdAt = Clock.System.now(),
    startedAt = Clock.System.now(),
    lastModifiedAt = Clock.System.now(),
    totalScore = 0,
    maxPossibleScore = 360, // 6 ends × 6 arrows × 10
    accuracy = 0.0
)

CREATE INDEX idx_end_score_round_id ON EndScore(roundId);
CREATE INDEX idx_end_score_participant_id ON EndScore(participantId);

// Create an end for round 1, end number 1
val end1 = EndScoreEntity(
    roundId = 1L,
    endNumber = 1,
    totalScore = 56,        // Sum of 6 arrows
    isComplete = true,      // All arrows entered
    participantId = "user123"
)
 
// Query all ends for a round
@Query("SELECT * FROM EndScore WHERE roundId = :roundId ORDER BY endNumber ASC")
suspend fun getEndScoresForRound(roundId: Long): List<EndScoreEntity>

// Round with 2 participants
val participant1End1 = EndScoreEntity(
    roundId = 1L,
    endNumber = 1,
    participantId = "user123",
    totalScore = 56
)
 
val participant2End1 = EndScoreEntity(
    roundId = 1L,
    endNumber = 1,
    participantId = "guest1",
    totalScore = 52
)

// Target Face scoring - full coordinates
val arrowWithCoords = ArrowScoreEntity(
    id = 1L,
    endScoreId = 1L,
    roundId = 1L,
    arrowNumber = 1,
    scoreValue = 10,
    isXRing = true,
    targetX = 2.5,
    targetY = -1.2,
    centerX = 0.0,
    centerY = 0.0,
    ringNumber = 10,
    clockPosition = 2,  // 2 o'clock position
    bowSetupId = 5L,
    bowSetupVersion = 3,
    participantId = "user123",
    inputMethod = InputMethod.TARGET_FACE
)
 
// Manual entry - no coordinates
val manualArrow = ArrowScoreEntity(
    id = 2L,
    endScoreId = 1L,
    roundId = 1L,
    arrowNumber = 2,
    scoreValue = 9,
    isXRing = false,
    targetX = null,
    targetY = null,
    centerX = null,
    centerY = null,
    ringNumber = null,
    clockPosition = null,
    bowSetupId = 5L,
    bowSetupVersion = 3,
    participantId = "user123",
    inputMethod = InputMethod.MANUAL
)

CREATE INDEX idx_arrow_score_round_id ON ArrowScore(roundId);
CREATE INDEX idx_arrow_score_end_score_id ON ArrowScore(endScoreId);
CREATE INDEX idx_arrow_score_participant_id ON ArrowScore(participantId);

-- Foreign Keys
EndScore.roundId → Round.id (ON DELETE CASCADE)
ArrowScore.endScoreId → EndScore.id (ON DELETE CASCADE)
ArrowScore.roundId → Round.id (ON DELETE CASCADE)
 
-- Effect
DELETE FROM Round WHERE id = 1;
-- Also deletes:
-- - All EndScore rows where roundId = 1
-- - All ArrowScore rows where roundId = 1 OR endScoreId IN (endScores from round 1)

val multiRound = RoundEntity(
    participants = listOf(
        SessionParticipant("user123", "Chris", isGuest = false, bowSetupId = 5L),
        SessionParticipant("guest1", "Sarah", isGuest = true, bowSetupId = 100L),
        SessionParticipant("guest2", "Mike", isGuest = true, bowSetupId = 101L)
    ),
    participantTheme = ParticipantTheme.MULTI_PARTICIPANT
)

// End 1 has 3 EndScore entries (one per participant)
val chrisEnd1 = EndScoreEntity(roundId = 1L, endNumber = 1, participantId = "user123", totalScore = 56)
val sarahEnd1 = EndScoreEntity(roundId = 1L, endNumber = 1, participantId = "guest1", totalScore = 54)
val mikeEnd1 = EndScoreEntity(roundId = 1L, endNumber = 1, participantId = "guest2", totalScore = 52)

// Each participant's end has 6 arrows
val chrisArrow1 = ArrowScoreEntity(endScoreId = 1L, arrowNumber = 1, participantId = "user123", scoreValue = 10)
val sarahArrow1 = ArrowScoreEntity(endScoreId = 2L, arrowNumber = 1, participantId = "guest1", scoreValue = 9)
val mikeArrow1 = ArrowScoreEntity(endScoreId = 3L, arrowNumber = 1, participantId = "guest2", scoreValue = 9)

@Query("""
    SELECT * FROM ArrowScore
    WHERE roundId = :roundId AND participantId = :participantId
    ORDER BY endScoreId, arrowNumber
""")
suspend fun getArrowScoresForParticipant(roundId: Long, participantId: String): List<ArrowScoreEntity>

@Query("""
    SELECT participantId, SUM(scoreValue) as totalScore
    FROM ArrowScore
    WHERE roundId = :roundId
    GROUP BY participantId
    ORDER BY totalScore DESC
""")
suspend fun getLeaderboard(roundId: Long): List<ParticipantScore>

// Week 1: Create bow setup (version 1)
val mySetup = BowSetup(id = 5L, version = 1, riserId = 1L, limbsId = 2L)
 
// Shoot round 1
val round1 = Round(bowSetupId = 5L, bowSetupVersion = 1)
 
// Week 2: Change limbs (version 2)
mySetup.copy(version = 2, limbsId = 3L)  // New limbs
 
// Shoot round 2
val round2 = Round(bowSetupId = 5L, bowSetupVersion = 2)
 
// Analysis: Compare performance
// Round 1: Setup v1 (old limbs) - Average 8.5
// Round 2: Setup v2 (new limbs) - Average 9.1
// Conclusion: New limbs improved performance!

// Distance
@TypeConverter
fun fromDistance(distance: Distance): String = distance.toString()
 
@TypeConverter
fun toDistance(value: String): Distance = Distance.fromString(value)
 
// ScoringSystem
@TypeConverter
fun fromScoringSystem(system: ScoringSystem): String = system.name
 
@TypeConverter
fun toScoringSystem(value: String): ScoringSystem = ScoringSystem.valueOf(value)
 
// SessionParticipant List
@TypeConverter
fun fromParticipantList(participants: List<SessionParticipant>): String =
    Json.encodeToString(participants)
 
@TypeConverter
fun toParticipantList(value: String): List<SessionParticipant> =
    Json.decodeFromString(value)
 
// RoundStatus
@TypeConverter
fun fromRoundStatus(status: RoundStatus): String = status.name
 
@TypeConverter
fun toRoundStatus(value: String): RoundStatus = RoundStatus.valueOf(value)

// 1. Create Round entity
val round = RoundEntity(
    roundName = "Practice",
    numEnds = 6,
    numArrows = 6,
    distance = Distance.fromString("50 meters"),
    targetSize = TargetSize.fromString("122cm"),
    scoringSystem = ScoringSystem.TEN_RING,
    bowSetupId = userBowSetupId,
    bowSetupVersion = currentSetupVersion,
    status = RoundStatus.PLANNED,
    participants = listOf(currentUser),
    participantTheme = ParticipantTheme.SINGLE_USER,
    createdAt = Clock.System.now(),
    lastModifiedAt = Clock.System.now()
)
 
// 2. Insert into database
val roundId = roundDao.insert(round)
 
// 3. Start scoring (status → IN_PROGRESS)
roundDao.updateStatus(roundId, RoundStatus.IN_PROGRESS)

database.withTransaction {
    // 1. Create EndScore
    val endScore = EndScoreEntity(
        roundId = roundId,
        endNumber = currentEndNumber,
        totalScore = 0,  // Will update after arrows
        isComplete = false,
        participantId = currentParticipantId
    )
    val endScoreId = endScoreDao.insert(endScore)
 
    // 2. Insert arrow scores
    val arrows = listOf(10, 9, 9, 8, 7, 6).mapIndexed { index, score ->
        ArrowScoreEntity(
            endScoreId = endScoreId,
            roundId = roundId,
            arrowNumber = index + 1,
            scoreValue = score,
            isXRing = (score == 10 && index == 0),  // First arrow is X
            participantId = currentParticipantId,
            inputMethod = InputMethod.MANUAL
        )
    }
    arrowScoreDao.insertAll(arrows)
 
    // 3. Update EndScore total
    val endTotal = arrows.sumOf { it.scoreValue }
    endScoreDao.updateTotal(endScoreId, endTotal, isComplete = true)
 
    // 4. Update Round running total
    val newRoundTotal = roundDao.getRoundTotal(roundId) + endTotal
    roundDao.updateTotal(roundId, newRoundTotal)
}

// 1. Verify all ends scored
val expectedEnds = round.numEnds
val scoredEnds = endScoreDao.getCompletedEndCount(roundId)
require(scoredEnds == expectedEnds) { "Not all ends scored" }
 
// 2. Calculate final statistics
val totalScore = arrowScoreDao.getTotalScore(roundId)
val maxPossible = round.numEnds * round.numArrows * 10  // Assuming 10-ring
val accuracy = (totalScore.toDouble() / maxPossible) * 100
 
// 3. Update round
roundDao.updateCompletion(
    roundId = roundId,
    status = RoundStatus.COMPLETED,
    totalScore = totalScore,
    accuracy = accuracy,
    completedAt = Clock.System.now()
)

@Query("""
    SELECT * FROM Round
    WHERE id = :roundId
""")
suspend fun getRound(roundId: Long): RoundEntity
 
@Query("""
    SELECT * FROM EndScore
    WHERE roundId = :roundId
    ORDER BY endNumber ASC
""")
suspend fun getEndScores(roundId: Long): List<EndScoreEntity>
 
@Query("""
    SELECT * FROM ArrowScore
    WHERE roundId = :roundId
    ORDER BY endScoreId, arrowNumber ASC
""")
suspend fun getArrowScores(roundId: Long): List<ArrowScoreEntity>

@Query("""
    SELECT * FROM Round
    WHERE status IN ('IN_PROGRESS', 'PAUSED')
    ORDER BY lastModifiedAt DESC
""")
suspend fun getActiveRounds(): List<RoundEntity>

@Query("""
    SELECT * FROM Round
    WHERE bowSetupId = :setupId
    AND status = 'COMPLETED'
    ORDER BY completedAt DESC
""")
suspend fun getCompletedRoundsForSetup(setupId: Long): List<RoundEntity>

@Query("""
    SELECT AVG(scoreValue) as avgScore,
           SUM(CASE WHEN isXRing = 1 THEN 1 ELSE 0 END) as xCount
    FROM ArrowScore
    WHERE roundId = :roundId
""")
suspend fun getRoundStats(roundId: Long): RoundStats