Database Testing

class ProdukRepositoryTest {
    private lateinit var conn: Connection
    private lateinit var repo: ProdukRepository

    @BeforeEach
    fun setup() {
        conn = DriverManager.getConnection("jdbc:sqlite::memory:")
        repo = ProdukRepository(conn)
        repo.createTable()
    }

    @AfterEach
    fun teardown() { conn.close() }

    @Test
    fun `insert returns positive id`() {
        val id = repo.insert("Kopi", 18000)
        assertTrue(id > 0)
    }
}

@Test
fun testInsert() {
    conn.autoCommit = false
    try {
        repo.insert("Kopi", 18000)
        assertEquals(1, repo.count())
    } finally {
        conn.rollback()  // Batalkan semua perubahan
        conn.autoCommit = true
    }
}

// database_testing.kt
// Mendemonstrasikan strategi testing database: SQLite in-memory (pengganti
// H2 bila tidak tersedia) untuk isolasi tes cepat, dan fixture per test
// via setup/teardown yang membersihkan tabel. Memakai SQLite via JDBC.

import java.sql.Connection
import java.sql.DriverManager

// Data class yang akan di-test
data class Produk(val id: Int, val nama: String, val harga: Int)

// Repository dengan operasi CRUD yang akan diuji
class ProdukRepository(private val conn: Connection) {

    fun createTable() {
        conn.createStatement().use { it.execute(
            """CREATE TABLE produk (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                nama TEXT NOT NULL,
                harga INTEGER NOT NULL
            )"""
        ) }
    }

    fun dropTable() {
        conn.createStatement().use { it.execute("DROP TABLE IF EXISTS produk") }
    }

    fun insert(nama: String, harga: Int): Int {
        conn.prepareStatement("INSERT INTO produk(nama, harga) VALUES(?, ?)").use { ps ->
            ps.setString(1, nama)
            ps.setInt(2, harga)
            ps.executeUpdate()
        }
        conn.createStatement().use { stmt ->
            stmt.executeQuery("SELECT last_insert_rowid()").use { rs ->
                if (rs.next()) return rs.getInt(1)
            }
        }
        error("Tidak bisa ambil ID")
    }

    fun findById(id: Int): Produk? {
        conn.prepareStatement("SELECT id, nama, harga FROM produk WHERE id = ?").use { ps ->
            ps.setInt(1, id)
            ps.executeQuery().use { rs ->
                if (rs.next()) return Produk(rs.getInt("id"), rs.getString("nama"), rs.getInt("harga"))
            }
        }
        return null
    }

    fun findAll(): List<Produk> {
        val result = mutableListOf<Produk>()
        conn.createStatement().use { stmt ->
            stmt.executeQuery("SELECT id, nama, harga FROM produk ORDER BY id").use { rs ->
                while (rs.next()) result.add(Produk(rs.getInt("id"), rs.getString("nama"), rs.getInt("harga")))
            }
        }
        return result
    }

    fun updateHarga(id: Int, hargaBaru: Int): Boolean {
        conn.prepareStatement("UPDATE produk SET harga = ? WHERE id = ?").use { ps ->
            ps.setInt(1, hargaBaru)
            ps.setInt(2, id)
            return ps.executeUpdate() > 0
        }
    }

    fun deleteById(id: Int): Boolean {
        conn.prepareStatement("DELETE FROM produk WHERE id = ?").use { ps ->
            ps.setInt(1, id)
            return ps.executeUpdate() > 0
        }
    }

    fun count(): Int {
        conn.createStatement().use { stmt ->
            stmt.executeQuery("SELECT COUNT(*) FROM produk").use { rs ->
                if (rs.next()) return rs.getInt(1)
            }
        }
        return 0
    }
}

// Test runner minimalis dengan assert helper
object Assertions {
    fun assertEquals(expected: Any?, actual: Any?, label: String) {
        if (expected != actual) {
            throw AssertionError("$label: expected=$expected, actual=$actual")
        }
        println("  [PASS] $label")
    }

    fun assertNull(actual: Any?, label: String) {
        if (actual != null) {
            throw AssertionError("$label: expected null, actual=$actual")
        }
        println("  [PASS] $label")
    }

    fun assertTrue(actual: Boolean, label: String) {
        if (!actual) {
            throw AssertionError("$label: expected true, actual=false")
        }
        println("  [PASS] $label")
    }
}

class ProdukRepositoryTest(private val conn: Connection) {
    private val repo = ProdukRepository(conn)

    // Setup: jalankan sebelum tiap test case
    private fun setup() {
        repo.dropTable()   // bersihkan sisa test sebelumnya
        repo.createTable()
    }

    // Teardown: bersihkan setelah tiap test case
    private fun teardown() {
        repo.dropTable()
    }

    fun testInsertAndFindById() {
        setup()
        try {
            val id = repo.insert("Kopi Susu", 18000)
            Assertions.assertTrue(id > 0, "insert returns positive id")
            val produk = repo.findById(id)
            Assertions.assertEquals("Kopi Susu", produk?.nama, "nama sesuai")
            Assertions.assertEquals(18000, produk?.harga, "harga sesuai")
        } finally { teardown() }
    }

    fun testFindAllReturnsEmpty() {
        setup()
        try {
            val all = repo.findAll()
            Assertions.assertEquals(0, all.size, "tabel kosong awalnya")
        } finally { teardown() }
    }

    fun testUpdateHarga() {
        setup()
        try {
            val id = repo.insert("Es Teh", 8000)
            val ok = repo.updateHarga(id, 9000)
            Assertions.assertTrue(ok, "update berhasil")
            val updated = repo.findById(id)
            Assertions.assertEquals(9000, updated?.harga, "harga berubah")
        } finally { teardown() }
    }

    fun testDeleteById() {
        setup()
        try {
            val id = repo.insert("Nasi Goreng", 25000)
            Assertions.assertEquals(1, repo.count(), "count = 1 setelah insert")
            val ok = repo.deleteById(id)
            Assertions.assertTrue(ok, "delete berhasil")
            Assertions.assertEquals(0, repo.count(), "count = 0 setelah delete")
            Assertions.assertNull(repo.findById(id), "findById setelah delete = null")
        } finally { teardown() }
    }

    fun testFindByIdNotFound() {
        setup()
        try {
            Assertions.assertNull(repo.findById(999), "findById id tidak ada = null")
        } finally { teardown() }
    }

    fun runAll() {
        println("=== Test: ProdukRepository ===")
        val tests = listOf(
            "testInsertAndFindById" to ::testInsertAndFindById,
            "testFindAllReturnsEmpty" to ::testFindAllReturnsEmpty,
            "testUpdateHarga" to ::testUpdateHarga,
            "testDeleteById" to ::testDeleteById,
            "testFindByIdNotFound" to ::testFindByIdNotFound
        )
        var passed = 0
        var failed = 0
        for ((name, fn) in tests) {
            print("- $name")
            try {
                fn()
                println("  -> OK")
                passed++
            } catch (e: Throwable) {
                println("  -> FAIL: ${e.message}")
                failed++
            }
        }
        println("\nHasil: $passed passed, $failed failed dari ${tests.size} test")
    }
}

fun main() {
    Class.forName("org.sqlite.JDBC")
    val url = "jdbc:sqlite:file::memory:?cache=shared"
    val keeper: Connection = DriverManager.getConnection(url)

    // In-memory DB per test run = isolasi penuh antar run
    DriverManager.getConnection(url).use { conn ->
        val test = ProdukRepositoryTest(conn)
        test.runAll()

        println("\n=== Strategi testing database ===")
        println("| Strategi          | Kecepatan | Isolasi | Catatan                           |")
        println("|-------------------|-----------|---------|-----------------------------------|")
        println("| SQLite :memory:   | Sangat cepat | Tinggi | Pengganti H2 bila tak tersedia    |")
        println("| H2 in-memory      | Sangat cepat | Tinggi | Default Spring Boot test profile |")
        println("| Testcontainers    | Lambat startup | Tinggi | PostgreSQL asli dalam Docker     |")
        println("| Shared DB dev     | Cepat     | Rendah | Berisiko data lintas test         |")

        println("\n=== Pola fixture ===")
        println("1. @BeforeEach: truncate tabel atau drop+create")
        println("2. @Test: jalankan operasi, assert hasil")
        println("3. @AfterEach: cleanup opsional (drop table)")
        println("4. Transactional test: rollback otomatis di akhir (Spring @Transactional)")

        println("\n=== Praktik baik ===")
        println("- Tiap test case independent: tak bergantung urutan")
        println("- Gunakan data fiktif konsisten: Budi, Siti, @example.com")
        println("- Hindari DateTime.now(); pakai LocalDate.of(2026, 1, 15)")
        println("- Assert state akhir, bukan logs atau side effects")
    }

    keeper.close()
}

=== Test: ProdukRepository ===
- testInsertAndFindById  [PASS] insert returns positive id
  [PASS] nama sesuai
  [PASS] harga sesuai
  -> OK
- testFindAllReturnsEmpty  [PASS] tabel kosong awalnya
  -> OK
- testUpdateHarga  [PASS] update berhasil
  [PASS] harga berubah
  -> OK
- testDeleteById  [PASS] count = 1 setelah insert
  [PASS] delete berhasil
  [PASS] count = 0 setelah delete
  [PASS] findById setelah delete = null
  -> OK
- testFindByIdNotFound  [PASS] findById id tidak ada = null
  -> OK

Hasil: 5 passed, 0 failed dari 5 test

=== Strategi testing database ===
| Strategi          | Kecepatan | Isolasi | Catatan                           |
|-------------------|-----------|---------|-----------------------------------|
| SQLite :memory:   | Sangat cepat | Tinggi | Pengganti H2 bila tak tersedia    |
| H2 in-memory      | Sangat cepat | Tinggi | Default Spring Boot test profile |
| Testcontainers    | Lambat startup | Tinggi | PostgreSQL asli dalam Docker     |
| Shared DB dev     | Cepat     | Rendah | Berisiko data lintas test         |

=== Pola fixture ===
1. @BeforeEach: truncate tabel atau drop+create
2. @Test: jalankan operasi, assert hasil
3. @AfterEach: cleanup opsional (drop table)
4. Transactional test: rollback otomatis di akhir (Spring @Transactional)

=== Praktik baik ===
- Tiap test case independent: tak bergantung urutan
- Gunakan data fiktif konsisten: Budi, Siti, @example.com
- Hindari DateTime.now(); pakai LocalDate.of(2026, 1, 15)
- Assert state akhir, bukan logs atau side effects

@Testcontainers
class ProdukRepositoryPgTest {
    companion object {
        @Container
        val postgres = PostgreSQLContainer<Nothing>("postgres:16-alpine")

        lateinit var conn: Connection

        @BeforeAll
        fun setupConn() {
            conn = DriverManager.getConnection(postgres.jdbcUrl, postgres.username, postgres.password)
        }
    }
}