Spring Data JPA with Kotlin

import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.Query
import org.springframework.data.domain.Page
import org.springframework.data.domain.Pageable
import javax.persistence.*
import java.time.LocalDateTime

// Entity
@Entity
@Table(name = "products")
data class Product(
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    val id: Long = 0,

    @Column(nullable = false, length = 100)
    val name: String,

    @Column(length = 500)
    val description: String? = null,

    @Column(nullable = false)
    val price: Double,

    @Column(nullable = false)
    val category: String,

    @Column(name = "in_stock")
    val inStock: Boolean = true,

    @Column(name = "created_at")
    val createdAt: LocalDateTime = LocalDateTime.now()
)

// Repository
interface ProductRepository : JpaRepository<Product, Long> {
    // Derived queries
    fun findByCategory(category: String): List<Product>
    fun findByCategoryAndInStock(category: String, inStock: Boolean): List<Product>
    fun findByPriceBetween(min: Double, max: Double): List<Product>
    fun findByNameContainingIgnoreCase(name: String): List<Product>
    fun findTop5ByCategoryOrderByPriceDesc(category: String): List<Product>
    fun existsByName(name: String): Boolean
    fun countByCategory(category: String): Long

    // Custom JPQL
    @Query("SELECT p FROM Product p WHERE p.price < :maxPrice AND p.inStock = true ORDER BY p.price")
    fun findAffordableProducts(maxPrice: Double): List<Product>

    @Query("SELECT p.category, COUNT(p), AVG(p.price) FROM Product p GROUP BY p.category")
    fun getCategoryStats(): List<Array<Any>>

    // Paginated
    fun findByCategoryOrderByPriceAsc(category: String, pageable: Pageable): Page<Product>

    // Native query
    @Query(
        value = "SELECT * FROM products WHERE name ILIKE %:search% LIMIT :limit",
        nativeQuery = true
    )
    fun searchProducts(search: String, limit: Int = 20): List<Product>
}

// Service
import org.springframework.stereotype.Service
import org.springframework.transaction.annotation.Transactional
import org.springframework.data.domain.PageRequest
import org.springframework.data.domain.Sort

@Service
class ProductService(private val repo: ProductRepository) {

    fun findAll(): List<Product> = repo.findAll()

    fun findById(id: Long): Product =
        repo.findById(id).orElseThrow { NoSuchElementException("Product $id not found") }

    fun findByCategory(category: String, page: Int = 0, size: Int = 20): Page<Product> {
        val pageable = PageRequest.of(page, size, Sort.by("price").ascending())
        return repo.findByCategoryOrderByPriceAsc(category, pageable)
    }

    @Transactional
    fun create(product: Product): Product {
        require(!repo.existsByName(product.name)) { "Product '${product.name}' already exists" }
        return repo.save(product)
    }

    @Transactional
    fun update(id: Long, updates: (Product) -> Product): Product {
        val existing = findById(id)
        return repo.save(updates(existing))
    }

    @Transactional
    fun delete(id: Long) {
        check(repo.existsById(id)) { "Product $id not found" }
        repo.deleteById(id)
    }

    fun stats(): Map<String, Any> {
        val stats = repo.getCategoryStats()
        return stats.associate { row ->
            row[0] as String to mapOf(
                "count" to row[1],
                "avgPrice" to row[2]
            )
        }
    }
}