package de.arxsilex.maptools.converter.desktop

import de.arxsilex.maptools.converter.core.GeoBounds
import de.arxsilex.maptools.converter.core.GeoRectangleRegion
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

class PoiMergeIntegrationTest {
    @Test
    fun `worker clips and merges mixed schema country databases`() {
        val directory = createTempDirectory("poi-merge-integration")
        try {
            val v3 = directory.resolve("west.poi")
            val v4 = directory.resolve("east.poi")
            val output = directory.resolve("countries.poi")
            createPoi(v3, PoiSchemaVersion.V3, 11, listOf(
                Poi(1, 50.0, 8.0, "name=Grenze"),
                Poi(2, 50.2, 8.2, "name=West"),
                Poi(3, 55.0, 13.0, "name=Außerhalb"),
            ))
            createPoi(v4, PoiSchemaVersion.V4, 91, listOf(
                Poi(8, 50.0, 8.0, "name=Grenze"),
                Poi(9, 50.4, 8.4, "name=Ost"),
            ))

            WorkerLauncher.mergePoi(
                sources = listOf(v3 to PoiSchemaVersion.V3, v4 to PoiSchemaVersion.V4),
                output = output,
                targetSchema = PoiSchemaVersion.V4,
                areas = listOf(GeoRectangleRegion(GeoBounds(7.0, 49.0, 9.0, 51.0))),
                temporaryDirectory = directory.resolve("temp"),
                logDirectory = directory.resolve("logs"),
            )

            ExportOutputValidator.validateMapsforgePoi(output, PoiSchemaVersion.V4)
            assertEquals(3L, MapsforgePoiSource.readMetadata(output).poiCount)
            assertEquals(
                listOf("name=Grenze", "name=Ost", "name=West"),
                readData(output).sorted(),
            )
        } finally {
            directory.toFile().deleteRecursively()
        }
    }

    private fun createPoi(path: Path, schema: PoiSchemaVersion, categoryId: Long, entries: List<Poi>) {
        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)")
                if (schema == PoiSchemaVersion.V3) {
                    statement.execute("CREATE TABLE poi_index (id INTEGER PRIMARY KEY, lat REAL, lon REAL)")
                } else {
                    statement.execute("CREATE VIRTUAL TABLE poi_index USING rtree(id, minLat, maxLat, minLon, maxLon)")
                    statement.execute("CREATE VIRTUAL TABLE poi_data_fts USING fts5(data, content='poi_data', content_rowid='id')")
                }
                statement.execute(
                    "INSERT INTO metadata VALUES ('version', '${schema.metadataVersion}'), " +
                        "('bounds', '49.0,7.0,56.0,14.0'), ('writer', 'test')",
                )
                statement.execute("INSERT INTO poi_categories VALUES ($categoryId, 'place', NULL)")
            }
            entries.forEach { poi ->
                connection.prepareStatement("INSERT INTO poi_data VALUES (?, ?)").use {
                    it.setLong(1, poi.id)
                    it.setString(2, poi.data)
                    it.executeUpdate()
                }
                val sql = if (schema == PoiSchemaVersion.V3) {
                    "INSERT INTO poi_index VALUES (?, ?, ?)"
                } else {
                    "INSERT INTO poi_index VALUES (?, ?, ?, ?, ?)"
                }
                connection.prepareStatement(sql).use {
                    it.setLong(1, poi.id)
                    it.setDouble(2, poi.latitude)
                    if (schema == PoiSchemaVersion.V3) {
                        it.setDouble(3, poi.longitude)
                    } else {
                        it.setDouble(3, poi.latitude)
                        it.setDouble(4, poi.longitude)
                        it.setDouble(5, poi.longitude)
                    }
                    it.executeUpdate()
                }
                connection.prepareStatement("INSERT INTO poi_category_map VALUES (?, ?)").use {
                    it.setLong(1, poi.id)
                    it.setLong(2, categoryId)
                    it.executeUpdate()
                }
            }
            if (schema == PoiSchemaVersion.V4) {
                connection.createStatement().use {
                    it.execute("INSERT INTO poi_data_fts(poi_data_fts) VALUES ('rebuild')")
                }
            }
        }
    }

    private fun readData(path: Path): List<String> =
        DriverManager.getConnection("jdbc:sqlite:${path.toAbsolutePath()}").use { connection ->
            connection.createStatement().use { statement ->
                statement.executeQuery("SELECT data FROM poi_data").use { result ->
                    buildList { while (result.next()) add(result.getString(1)) }
                }
            }
        }

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