package de.arxsilex.maptools.converter.worker

import de.arxsilex.maptools.converter.core.PoiSchemaVersion
import java.nio.file.Files
import java.nio.file.Path
import java.sql.DriverManager
import kotlin.io.path.createTempDirectory
import kotlin.test.Test
import kotlin.test.assertEquals
import kotlin.test.assertTrue

class MapsforgePoiMergerTest {
    @Test
    fun `merges categories remaps ids and removes exact cross source duplicates`() {
        val directory = createTempDirectory("poi-merger")
        try {
            val first = directory.resolve("first.poi")
            val second = directory.resolve("second.poi")
            val output = directory.resolve("merged.poi")
            createPoi(first, categoryId = 10, entries = listOf(
                TestPoi(4, 50.0, 8.0, "name=Border POI"),
                TestPoi(5, 50.2, 8.2, "name=West POI"),
            ))
            createPoi(second, categoryId = 77, entries = listOf(
                TestPoi(4, 50.0, 8.0, "name=Border POI"),
                TestPoi(5, 50.4, 8.4, "name=East POI"),
            ))

            val report = MapsforgePoiMerger.merge(listOf(first, second), output, PoiSchemaVersion.V3)

            assertEquals(4, report.sourceCount)
            assertEquals(3, report.outputCount)
            assertEquals(1, report.duplicateCount)
            DriverManager.getConnection("jdbc:sqlite:${output.toAbsolutePath()}").use { connection ->
                connection.createStatement().use { statement ->
                    statement.executeQuery("SELECT COUNT(*) FROM poi_categories WHERE name='restaurant'").use {
                        assertTrue(it.next())
                        assertEquals(1, it.getInt(1))
                    }
                    statement.executeQuery("SELECT COUNT(*) FROM poi_category_map").use {
                        assertTrue(it.next())
                        assertEquals(3, it.getInt(1))
                    }
                    statement.executeQuery("SELECT COUNT(DISTINCT id) FROM poi_data").use {
                        assertTrue(it.next())
                        assertEquals(3, it.getInt(1))
                    }
                }
            }
        } finally {
            directory.toFile().deleteRecursively()
        }
    }

    private fun createPoi(path: Path, categoryId: Long, entries: List<TestPoi>) {
        Files.deleteIfExists(path)
        Class.forName("org.sqlite.JDBC")
        DriverManager.getConnection("jdbc:sqlite:${path.toAbsolutePath()}").use { connection ->
            connection.createStatement().use { statement ->
                statement.execute("CREATE TABLE metadata (name TEXT, value TEXT)")
                statement.execute("CREATE TABLE poi_categories (id INTEGER PRIMARY KEY, name TEXT, parent INTEGER)")
                statement.execute("CREATE TABLE poi_category_map (id INTEGER, category INTEGER, PRIMARY KEY (id, category))")
                statement.execute("CREATE TABLE poi_data (id INTEGER PRIMARY KEY, data TEXT)")
                statement.execute("CREATE TABLE poi_index (id INTEGER PRIMARY KEY, lat REAL NOT NULL, lon REAL NOT NULL)")
                statement.execute("INSERT INTO metadata VALUES ('version', '3'), ('writer', 'test'), ('bounds', '49,7,51,9')")
            }
            connection.prepareStatement("INSERT INTO poi_categories (id, name, parent) VALUES (?, 'restaurant', NULL)").use {
                it.setLong(1, categoryId)
                it.executeUpdate()
            }
            entries.forEach { entry ->
                connection.prepareStatement("INSERT INTO poi_data (id, data) VALUES (?, ?)").use {
                    it.setLong(1, entry.id)
                    it.setString(2, entry.data)
                    it.executeUpdate()
                }
                connection.prepareStatement("INSERT INTO poi_index (id, lat, lon) VALUES (?, ?, ?)").use {
                    it.setLong(1, entry.id)
                    it.setDouble(2, entry.latitude)
                    it.setDouble(3, entry.longitude)
                    it.executeUpdate()
                }
                connection.prepareStatement("INSERT INTO poi_category_map (id, category) VALUES (?, ?)").use {
                    it.setLong(1, entry.id)
                    it.setLong(2, categoryId)
                    it.executeUpdate()
                }
            }
        }
    }

    private data class TestPoi(
        val id: Long,
        val latitude: Double,
        val longitude: Double,
        val data: String,
    )
}
