val riser = RiserEntity(
    brand = "Hoyt",
    model = "Formula X",
    length = "25 inches",
    material = "Carbon"
)

val limbs = LimbsEntity(
    brand = "Hoyt",
    model = "Formula X-Act",
    poundage = "38 lbs",
    limbLength = "Long"
)

val bowString = BowStringEntity(
    brand = "BCY",
    model = "8125G",
    strandCount = 18,
    servingMaterial = "BCY 3D"
)

val sight = SightEntity(
    brand = "Shibuya",
    model = "Ultima RC II",
    material = "Carbon"
)

// Sight marks for 70m Olympic round
val sightMarks = listOf(
    SightMarkEntity(
        sightId = 5L,
        distance = "70 meters",
        markValue = "2.5",
        notes = "Outdoor, calm wind"
    ),
    SightMarkEntity(
        sightId = 5L,
        distance = "50 meters",
        markValue = "5.8",
        notes = "Outdoor"
    ),
    SightMarkEntity(
        sightId = 5L,
        distance = "30 meters",
        markValue = "9.2",
        notes = "Indoor/outdoor"
    )
)

// Get all sight marks for a sight
@Query("SELECT * FROM SightMark WHERE sightId = :sightId ORDER BY distance ASC")
suspend fun getSightMarksForSight(sightId: Long): List<SightMarkEntity>
 
// Find sight mark for specific distance
@Query("SELECT * FROM SightMark WHERE sightId = :sightId AND distance = :distance")
suspend fun getSightMarkForDistance(sightId: Long, distance: String): SightMarkEntity?

val stabilizer = StabilizerEntity(
    brand = "Doinker",
    model = "Platinum",
    length = "30 inches",
    weight = "8 oz",
    straightnessRating = "+/- 0.001"
)

val weight = WeightEntity(
    brand = "Shrewd",
    model = "1 oz weight",
    ounces = 1.0
)

val plunger = PlungerEntity(
    brand = "Beiter",
    model = "DX Plunger",
    adjustment = "Micro-adjust"
)

val rest = RestEntity(
    brand = "Hoyt",
    model = "Super Rest",
    type = "Magnetic"
)

data class ArrowPoint(
    val brand: String,      // e.g., "Easton"
    val model: String,      // e.g., "X10 Point"
    val weight: String      // e.g., "120 grains"
)

data class ArrowNock(
    val brand: String,      // e.g., "Easton"
    val model: String,      // e.g., "G Nock"
    val size: String?       // e.g., "Large", "Small"
)

val arrow = ArrowEntity(
    brand = "Easton",
    model = "X10",
    spine = "500",
    length = "29.5 inches",
    weight = "420 grains",
    diameter = "5.5mm",
    arrowPoint = ArrowPoint(
        brand = "Easton",
        model = "X10 Point",
        weight = "120 grains"
    ),
    arrowNock = ArrowNock(
        brand = "Easton",
        model = "G Nock",
        size = "Large"
    )
)

@TypeConverter
fun fromArrowPoint(point: ArrowPoint?): String? =
    point?.let { Json.encodeToString(it) }
 
@TypeConverter
fun toArrowPoint(value: String?): ArrowPoint? =
    value?.let { Json.decodeFromString(it) }
 
@TypeConverter
fun fromArrowNock(nock: ArrowNock?): String? =
    nock?.let { Json.encodeToString(it) }
 
@TypeConverter
fun toArrowNock(value: String?): ArrowNock? =
    value?.let { Json.decodeFromString(it) }

val accessory = AccessoryEntity(
    brand = "AAE",
    model = "Elite Tab",
    type = "Tab"
)

// Initial setup (version 1)
val setup = BowSetupEntity(
    name = "My Setup",
    version = 1,
    isActive = true,
    riserId = 1L,
    limbsId = 2L
)
 
// Change limbs (version 2)
val updatedSetup = setup.copy(
    version = 2,
    limbsId = 3L  // New limbs
)
 
// Round created with version 2 always references that configuration

// "Delete" a setup (soft delete)
bowSetupDao.updateActive(setupId, isActive = false)
 
// Setup still exists in database, but isActive = false
// Won't appear in active equipment lists
// Historical rounds still reference it

val bowSetup = BowSetupEntity(
    name = "Competition Setup 2025",
    description = "38# Hoyt Formula X, 30\" Doinker stab",
    version = 1,
    isActive = true,
    riserId = 1L,      // Hoyt Formula X riser
    limbsId = 2L,      // Hoyt 38# limbs
    sightId = 3L,      // Shibuya Ultima sight
    stabilizerId = 4L, // Doinker 30" stabilizer
    plungerId = 5L,    // Beiter plunger
    restId = 6L,       // Hoyt Super Rest
    bowStringId = 7L,  // BCY 8125G string
    arrowId = 8L,      // Easton X10 arrows
    weightId = 9L,     // 1oz weights
    accessoryId = 10L  // AAE Elite Tab
)

// Get active bow setups
@Query("SELECT * FROM BowSetup WHERE isActive = 1 ORDER BY name ASC")
suspend fun getActiveSetups(): List<BowSetupEntity>
 
// Get all versions of a setup
@Query("SELECT * FROM BowSetup WHERE name = :name ORDER BY version ASC")
suspend fun getSetupVersions(name: String): List<BowSetupEntity>
 
// Get setup with all equipment (requires JOINs)
@Query("""
    SELECT BowSetup.*, Riser.*, Limbs.*, Sight.*
    FROM BowSetup
    LEFT JOIN Riser ON BowSetup.riserId = Riser.id
    LEFT JOIN Limbs ON BowSetup.limbsId = Limbs.id
    LEFT JOIN Sight ON BowSetup.sightId = Sight.id
    WHERE BowSetup.id = :setupId
""")
suspend fun getSetupWithEquipment(setupId: Long): BowSetupWithEquipment

// 1. Create individual components
val riser = riserDao.insert(RiserEntity(brand = "Hoyt", model = "Formula X", ...))
val limbs = limbsDao.insert(LimbsEntity(brand = "Hoyt", model = "X-Act", ...))
val sight = sightDao.insert(SightEntity(brand = "Shibuya", model = "Ultima", ...))
 
// 2. Create bow setup
val setup = bowSetupDao.insert(BowSetupEntity(
    name = "My Setup",
    version = 1,
    isActive = true,
    riserId = riser,
    limbsId = limbs,
    sightId = sight
))

// Changing equipment creates new version
val currentSetup = bowSetupDao.getById(setupId)
val newVersion = currentSetup.version + 1
 
bowSetupDao.update(currentSetup.copy(
    version = newVersion,
    limbsId = newLimbsId  // Changed limbs
))
 
// Old rounds still reference old version
// New rounds will use new version

// Soft delete (preferred)
bowSetupDao.updateActive(setupId, isActive = false)
// Setup hidden from active lists but preserved
 
// Hard delete component (if not in any setup)
riserDao.delete(riserId)
// Only safe if no BowSetup references it
// Otherwise, BowSetup.riserId will be set to NULL (ON DELETE SET NULL)

// When creating a round
val round = RoundEntity(
    roundName = "Practice",
    bowSetupId = currentSetup.id,
    bowSetupVersion = currentSetup.version,
    ...
)

// Get all completed rounds for a setup
@Query("""
    SELECT * FROM Round
    WHERE bowSetupId = :setupId
    AND status = 'COMPLETED'
    ORDER BY completedAt DESC
""")
suspend fun getRoundsForSetup(setupId: Long): List<RoundEntity>
 
// Calculate average score for setup
@Query("""
    SELECT AVG(totalScore) as avgScore
    FROM Round
    WHERE bowSetupId = :setupId
    AND status = 'COMPLETED'
""")
suspend fun getAverageScoreForSetup(setupId: Long): Double

// Compare performance across versions
@Query("""
    SELECT version, AVG(totalScore) as avgScore, COUNT(*) as roundCount
    FROM Round
    WHERE bowSetupId = :setupId
    AND status = 'COMPLETED'
    GROUP BY bowSetupVersion
    ORDER BY version ASC
""")
suspend fun getPerformanceByVersion(setupId: Long): List<VersionPerformance>

// Round with 2 guests
val round = RoundEntity(
    participants = listOf(
        SessionParticipant("user123", "Chris", isGuest = false, bowSetupId = 5L),
        SessionParticipant("guest1", "Sarah", isGuest = true, bowSetupId = 100L),  // Auto-generated
        SessionParticipant("guest2", "Mike", isGuest = true, bowSetupId = 101L)   // Auto-generated
    )
)
 
// Guest setups created automatically
val guestSetup1 = BowSetupEntity(
    name = "Guest - Sarah (Temp)",
    version = 1,
    isActive = false,  // Not shown in active lists
    riserId = null,    // Minimal data
    limbsId = null
)

// ArrowPoint converter
@TypeConverter
fun fromArrowPoint(point: ArrowPoint?): String? =
    point?.let { Json.encodeToString(it) }
 
@TypeConverter
fun toArrowPoint(value: String?): ArrowPoint? =
    value?.let { Json.decodeFromString(it) }
 
// ArrowNock converter
@TypeConverter
fun fromArrowNock(nock: ArrowNock?): String? =
    nock?.let { Json.encodeToString(it) }
 
@TypeConverter
fun toArrowNock(value: String?): ArrowNock? =
    value?.let { Json.decodeFromString(it) }

-- BowSetup indexes
CREATE INDEX idx_bow_setup_active ON BowSetup(isActive);
CREATE INDEX idx_bow_setup_version ON BowSetup(version);
 
-- Foreign key indexes (for JOINs)
CREATE INDEX idx_bow_setup_riser_id ON BowSetup(riserId);
CREATE INDEX idx_bow_setup_limbs_id ON BowSetup(limbsId);
CREATE INDEX idx_bow_setup_sight_id ON BowSetup(sightId);
-- (and other foreign keys)
 
-- SightMark indexes
CREATE INDEX idx_sight_mark_sight_id ON SightMark(sightId);

// Good: Create components, then setup
val riserId = riserDao.insert(riser)
val limbsId = limbsDao.insert(limbs)
val setup = BowSetupEntity(riserId = riserId, limbsId = limbsId, ...)
 
// Bad: Reference non-existent components
val setup = BowSetupEntity(riserId = 999L, ...)  // FK constraint violation

// Good: Increment version on change
val newVersion = currentSetup.version + 1
val updated = currentSetup.copy(version = newVersion, limbsId = newLimbsId)
 
// Bad: Change equipment without versioning
val updated = currentSetup.copy(limbsId = newLimbsId)  // Version unchanged

// Good: Soft delete
bowSetupDao.updateActive(setupId, false)
 
// Bad: Hard delete
bowSetupDao.delete(setupId)  // Breaks historical round references

// Good: Check nullability
val riser = setup.riserId?.let { riserDao.getById(it) }
 
// Bad: Assume non-null
val riser = riserDao.getById(setup.riserId)  // NPE if null

data class CompleteSetup(
    val setup: BowSetupEntity,
    val riser: RiserEntity?,
    val limbs: LimbsEntity?,
    val sight: SightEntity?,
    // ... other components
)
 
@Transaction
@Query("SELECT * FROM BowSetup WHERE id = :setupId")
suspend fun getCompleteSetup(setupId: Long): CompleteSetup

@Query("SELECT * FROM BowSetup WHERE riserId = :riserId AND isActive = 1")
suspend fun getSetupsUsingRiser(riserId: Long): List<BowSetupEntity>